Excel Power Query分割加總:取消資料行樞紐及分組依據

目錄

Excel Power BI中的Power Query是新一代的資料處理工具。本文以簡單的數字分割加總為例,示範如何複製、分割、移除資料行,以及取消資料行樞紐、分組依據等指令。

一、簡單範例

如圖所示,儲存格內是一連串的數字,

二、從表格取得資料

上方功能區「資料>取得及轉換資料>從表格/範圍」。

三、建立表格

Power Query處理資料前,先自動先建立表格,所選取範圍並沒有標題列,所以「我的表格有標題」毋須勾選。

四、複製資料行

進入「Power Query編輯器」後,可以看到剛才所建立表格,在第一欄標題的位置滑鼠右鍵,快捷選單執行「複製資料行」。

五、分割資料行

如圖所示,成功複製資料行後,於「Power Query編輯器」的上方功能區「常用>轉換」將「分割資料行」下拉,選單中執行「依字元數」。

六、指定字元數目

因為想將原始一連串的數字一個一個分割,這裡的「字元數」輸入「1」,「分割」選項設定為「一再重複」,表示要將「1234」分割為「1」、「2」、「3」、「4」的意思,最後按「確定」。

七、取消資料行樞紐

回到「Power Query編輯器」,可以看到上個步驟執行分割的結果,同時也能理解在第四步驟為何要先複製資料行,等於是保留第一欄原始資料的作用。
上方功能區「轉換>任何資料行」中將「取消資料行樞紐」下拉,執行「取消其他資料行樞紐」。這指令是將依照項目平行排列的報表,還原為單欄單值的資料表形式,其作用到了下個步驟會更為具體。

八、移除其他資料行

取消資料行樞紐後,可以看到是把原本分散的屬性及值都回歸到一列一列的狀態,熟悉Excel樞紐分析表的讀者,應該知道此時如果建立樞紐分析表,可以彙總成上個步驟的報表,所以它的作用等於逆是樞紐,或者如指令名稱所言:取消資料行樞紐。
這裡因為第二欄「屬性」並不需要,可以在選擇「欄1」和「值」這兩欄的狀態下,上方功能區「常用>管理資料行」將「移除資料行」下拉,執行「移除其他資料行」。

九、分組依據

移除「屬性」欄之後,上方功能區「常用>轉換」中執行「分組依據」。

十、分組設定

於「分組依據」視窗中,準備依照「欄1」進行分組,將左邊的「新資料行名稱」設定為「合計」,中間的「作業」方式下拉選擇「加總」,表示單純加總數值,最右邊的「欄」下拉選擇「值」,表示要針對「值」這一欄執行加總作業,設定好了按「確定」。

十一、分割加總

成功完成任務,將一長串數字中的每個位數作為單一值,加總合計成為另外一個資料欄位。

十二、Excel報表

關閉Power Query編輯器,回到Excel工作表,得到很乾淨的、處理過的一份報表。

Power Query:強大的Power BI資料處理工具

這篇文章的範例雖然簡單,但是剛好用到了Power Query一些核心的資料處理指令,可以作為入門的參考。在過程中也包含了運用Power Query時的基本思惟,例如複製資料行進行分割處理、以逆行樞紐的方式翻轉整份資料表、最後再利用分組依據執行條件彙總。以簡馭繁,讀者可以想見當報表資料很多很複雜,當有需要進行整體調整時,Power Query等於Excel開外掛進行資料整理。
注意到本範例第八步驟也可以使用「移除資料行」方式,直接將「屬性」欄刪除,之所以進行「移除其他資料行」,是為了方便日後如果報表有更新或Power Query步驟需要調整,比較不會有問題,因為是選擇想保留的資料行方式進行刪除。對此日後如果有更多相關的介紹,讀者應該會有更進一步的體會。
本文由贊贊小屋討論區Power BI版主設計,有任何Power BI問題歡迎於討論區提出。
贊贊小屋Power BI YouTube清單:

贊贊小屋Power BI線上課程:

相關文章