Try   HackMD
tags: 工務局 關 Excel

工務局_Excel資料分析與視覺化應用

課程開場介紹

課程講師資訊巨匠電腦講師：呂心怡
E-mail：zoego99@gmail.com
上課日期：2022/8/2、4 及 2022/9/12、14課程練習下載區：https://bit.ly/3mlZcXH

A、適用對象

適用所有Excel使用者，尤其是需要經常對原始資料進行手工合併、追加、去除重複數據等操作的資料分析，或者工作中經常運用Excel的樞紐分析工具進行數據分析人員。

B、課程概述

1、 Power Query(PQ)

在工作中經常使用Excel手工合併多張表格；經常需要對來源資料進行刪除、分列、去除重複數據等操作，然而做過資料分析相關工作的你應該深有體會 – 我們需要製作一份數據分析報告的時候，80%的時間要用來處理不規範的來源資料。
想要擺脫重覆、手工、枯燥的整理工作的苦惱嗎？進而大幅度提升工作效率，節省寶貴時間嗎？Excel提供內建好用的Power Query插件，提供親和的介面化操作，讓很多重複工作進行簡化。
很可惜多數人使用Excel卻都忽略Power Query插件，而學習這項工具已經是未來趨勢，藉由本次課程將列舉實務性範例，解決許多惱人且複雜的報表整理，進而提高工作效率。

(A) 何謂Excel表格及其使用優點介紹
(B) 將多個Excel、CSV檔案或網頁，不同來源類型數據的輕鬆獲取
(C) 分割、合併與提取儲存格資料
(D) 一維 / 二維 資料表轉換
(E) 利用合併查詢進行兩表比對，比Vlookup查詢更加簡單及靈活
(F) 產品明細快速拆分【中/英】文

2、 Power Pivot (PP)

只要進行數據分析工作者，都會使用【樞紐分析表(Pivot Table)】，也都看過/聽過【資料管理模型(PowerPivot)】卻混淆不知道如何應用，其實這兩項套件是相輔相成的工具，差別在PowerPivot可匯整多張關係工作表，更加結構化進行數據分析。
在工作中愈來愈多數人都運用樞紐工具進行數據分析，基本樞紐操作已經難不倒，本次課程將藉由【Power Pivot】增益集解決Vlookup連結多報表困擾，並提出執行樞紐分析可能遇到的困擾，及製作樞紐時經常忽略的功能，讓樞紐分析運用更加靈活，得心應手。

(A) 樞紐分析前置作業
　　(1) 解決日期欄位無法產生年/季/月報困擾
　　(2) 利用【Power Pivot】增益集解決Vlookup連結多報表困擾
(B) 樞紐分析進階應用—經常忽略技巧分享
　　(1) 如何有效率美化樞紐分析表
　　(2) 數據顯示依值顯示方式，從而更清晰的看出數據之間的關係
　　(3) 藉助樞紐分析表中的計算欄位功能產生欄位
　　(4) 利用交叉分析篩選器動態數據分析
　　(5) 利用群組特性，彙整年報、季報、月報

C、未來展望


在工作任務中，經常需要利用Excel樞紐分析功但不了解Power BI使用者，Power BI Desktop是安裝在本機電腦的免費商業分析工具，可從多種檔案類型匯入資料後，快速建立互動圖表的工具，輕鬆完成大數據的分析！



1、 各項軟體間的分責運用

P.1
整體性概觀了解PowerBI各項軟體間的分責運用。

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

2、 參考網站

(1) Power BI官網：

　　了解PowerBI及軟體下載：
　　　👆 https://powerbi.microsoft.com/zh-tw/
　　　👆 https://powerbi.microsoft.com/zh-tw/desktop/

　　PowerBI 文件：👆 https://docs.microsoft.com/zh-tw/power-bi/

(2) Power Query M 公式語言：

　　👆 https://docs.microsoft.com/zh-tw/powerquery-m/

M語言是Power Query的後台函數式編程語言，在Power Query界面的操作過程都會記錄下來並翻譯成M語言，將查詢和轉換的結果返回到Excel表或Excel或Power BI數據模型。

Power Query上手十分容易，通過圖形界面操作可以解決大部分需求，一般情況下，我們不用去深入學習M語言，但是，如果對M語言有了一定的理解可以對操作步驟進行優化，讓其更加智能，同時也能解決一些圖形界面操作不能完成的任務。

(3) Power BI Desktop使用DAX函數：

　　👆 https://docs.microsoft.com/zh-tw/dax/

DAX是Power Pivot和Power BI Desktop中使用的公式語言。 DAX使用函數處理存儲在表中的數據。一些DAX函數與Excel工作表函數相同，但DAX具有更多功能來匯總，切片和切割複雜的數據場景。

D、指令位置

P.2

1、 PP安裝啟動

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

2、 指令位置

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

Excel 應用技巧

1. Excel批次貼入圖片方法

補充
🔶 範例結果

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

S1：複製圖片路徑，將所有照片置於同一資料夾中，並複製其路徑
Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

S2：語法說明複製下方法語法至Excel中，修改其內容

="<table><img src='圖片路徑資料夾\" & 檔名 & ".副檔名' width='50'>"

語法說明

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

S3：貼入Excel調整情形參考

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

S4:路徑結果貼入記事本中，將路徑結果貼入記事本中，將其Excel設定移除
Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

S5:再將記事本路徑貼回Excel中，再將記事本路徑貼回Excel中，調整圖片大小即可

2. Indirect與【名稱定義】結合應用

補充

A. 函數

(1) Vlookup查表函數

依查詢值進行資料比對輸出相關資料之函數

= Vlookup ( 查詢值 , 查表範圍 , 回傳指定欄數 , 選項)

說明

  • 查詢值若為『文字』，選項為False
    查詢值若為『數值』，選項通常為True，且查表範圍應遞增
  • 查詢值應為查表範圍的第一欄 / 列
  • 查表範圍若為『縱』向查詢，即為 Vlookup 函數
    查表範圍若為『橫』向查詢，即為 Hlookup 函數
  • 選項：
    • False，必須比對完合符合才回傳，查無資料以 “#N/A” 表示
      True 或省略，比對回傳接近不超過查詢值的資料
  • 查表函數，可搭配 Iferror 函數

(2) Indirect 間接抓取範圍函數

= Indirect ( 定義名稱 )

B. 範例

S1：以C2儲存格為例：

=VLOOKUP( $A2 ,           銷售01月       !$A$2:$F$13   , 6 , FALSE )
=VLOOKUP( $A2 , indirect(   C$1      & "!$A$2:$F$13" ) , 6 , FALSE )

以上公式完成複製於其他儲存格中即可。

S2：選取【員工編號】及【圖片】範圍，建立名稱定義，如下圖

S3：將被查詢圖片的員工編號，設定為圖片的名稱定義來源，如下圖建立：

定義名稱：員工圖片
公式：=INDIRECT(總表!$L$3)&"_"

S4：選取第一名員工圖片的儲存格，利用複製，並點選【貼上】>【連結的圖片】，並將公式改為【=員工圖片】定義名稱

3. 取得數據無法計算的困擾

補充

A. 日期輸入原則的重要性

輸入結構【年/月/日】，定義為西元年份
日期以 / 或 - 區隔，時間以 : 區隔
今年年份輸入時可省略
Win系統為台灣版本時，民國年份之日期前方加入R
例R30/3/5，Excel自動轉換為1941/3/5。

B. 情境說明：使用乘積函數困擾（權重計算）

C. 善用【資料剖析】進行轉換

指令：【資料】>【資料剖析】

D. 特殊目標..介紹

善用【常用】>【尋找與選取】>【特殊目標】

線上銷售明細

📌【課程範例】

🔶【範例】
🔶【步驟】
練習1：空格向下填滿
S1：選取數據範圍
S2：【常用】>【尋找與選取】
S3：【特殊目標】> 點選【空格】選項
S4：輸入=，方向鍵↑
S5：輸入完成Ctrl+Enter
S6：移除公式
全選範圍>【複製】>【選擇性貼上】>【值】123

練習2：依篩選條件進行美化
快速鍵：【Alt】+【；】

4. 解決總和遇上篩選困擾

補充

A. 名稱定義

B. 小計函數：Subtotal

C. 會除錯的小計函數：Aggregate

Aggregate函數比Subtotal函數還要強大，因面對忽略錯誤值、隱藏行和分類匯總嵌套，滿足Subtotal無法處理，但Aggregate滿足各種情況下的統計需求！

5. 排序與自訂清單

A. 排序

B. 自訂清單

(1) 目的

※資料排序與自訂清單結合
在排序完成後以使用者角度來看，例如：職務、考績或地區，有自行的排序順序，但電腦只能依筆劃或數值排序時，則需運用【自訂清單】要求順序。

填滿序列
資料排序
樞紐分析

(2) 指令

【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目

C. 自訂清單

※【格式化為表格】無法使用小計功能，利用【轉換為範圍】移除格式化為表格前，應確認顏色是否移除

S1：小計之前先排序

指令：【資料】>【排序】

S2：先排序的欄位，先執行小計工作

指令：【資料】>【小計】
S2-1：第一階層排序欄位【經銷商】

S2-2：第二階層排序欄位【商品名稱】

6. 使用Excel表格重要性

P.3

A. Excel表格介紹

可以快速地建立、格式化及展開 Excel 表格，組織工作表上的資料，讓其更易於使用。

(1) 指令介紹

📌 建立

📌 表格化指令

(2) 表格化選取方法

🔶 左鍵一下選取內容、連二下選取內容及標題

(C) 表格化後，公式表示結構

=表格名稱〔＠欄位名稱〕
有＠：相對性欄位
沒有＠：整欄計算

B. 使用優點

(1) 容易選取資料範圍且數據自動縮放

(2) 公式容易辨識

(3) 交叉分析篩選器

交叉分析篩選器為一視覺化控制項，以互動且直覺的方式，快速篩選僅顯示所需的資料的資料。

C. 移除表格注意事項

(1) 移除表格化前，先確認是否要移除色彩

為了取得帶狀列的色彩，直接移除表格化
因反悔表格化功能，而表格會動用排序時，必須要移除色彩

(2) 移除方法

7. 當下時間記錄

S0：列表輸入情形

S1：在E2儲存格輸入公式

=IF(E2="",NOW(),E2)

公式輸入思考原則：繳交日期E2顯示當下時間，否則顯示空白

S2：在E2儲存格修改公式

=IF(D2="","",IF(E2="",NOW(),E2))

公式輸入思考原則：當D2輸入已繳(V)，繳交日期E2顯示當下時間，否則顯示空白

S3：在進行S2公式建立時，會造成公式循環問題時，利用【檔案】>【選項】(設定如下圖)

參考資訊：移除或允許循環參照，並了解反覆運算用意

8. 每月各項收支報表

A. 二維查表函數介紹

(1) Index函數

根據欄列號索引回傳查表範圍的值

= Index ( 查表範圍 , 指定列數 , 指定欄數 )

(2) Match函數

傳回搜尋項目於搜尋範圍中的相對位

Match 函數：
目的：傳回搜尋項目於搜尋範圍中的相對位置
公式：=Match ( 1️⃣ 查詢值, 2️⃣ 查表範圍 , 3️⃣ 比對方式類型 )

　3️⃣ 比對方式類型：
　　1或省略：查詢範圍須『遞增』
　　0：查詢範圍不須事前排序，找到完全符合查詢值
　　-1：查詢範圍須『遞減』

🔶 公式比擬

B. 範例


9. 符合條件計算函數

註：課程範例中，先行針對【職員薪資明細】的表格化及名稱定義，在『總表』工作表中，依各職務進行計算
符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS

10. 年資

(1) 公式說明：計算兩日期間的天數、月數或年數

 = Datedif ( 開始日 , 結束日 , " 單位 " )

※ 說明：
   單位：決定要傳回的單位，必須前後加上 " "
   Y代表滿幾年、M代表滿幾月、D代表滿幾日
   MD代表兩日期間的天數差，忽略月和年
   YM代表兩日期間的月數差，忽略日和年
   YD代表兩日期間的天數差，忽略年
   要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位

(2) 範例結果：年資計算

 = Datedif ( [@起聘日期] , TODAY() , "Y" )

11. 星期計算

A. 【日期】格式設定

B. 星期計算公式

🔶 星期(中)
🔶 星期(英)

12. 常用日期類函數

A. 傳回日期的年 / 月 / 日值

 = Year ( 日期 )
 = Month( 日期 )
 = Day ( 日期 )

B. 組合年、月、日值為日期

  = Date ( 年 , 月 , 日 )

※ 最終抓取值為數值

C. 計算兩日期間的天數、月數或年數

 = Datedif ( 開始日 , 結束日 , " 單位 " )

※ 說明：
   單位：決定要傳回的單位，必須前後加上 " "
   Y代表滿幾年、M代表滿幾月、D代表滿幾日
   MD代表兩日期間的天數差，忽略月和年
   YM代表兩日期間的月數差，忽略日和年
   YD代表兩日期間的天數差，忽略年
   要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位

D. 計算前後月的某一天

  = EDATE (開始日期,月數)

E. 計算當月的月底日期

  = EOMONTH (開始日期,月數)

F. 傳回自指定工作天數的日期

  = Workday ( 開始日期 , 日數 , 假日列表 )
  
  ※ 延伸學習函數 Workday.intl

G. 傳回兩日期間的工作天數

  = Networkdays ( 開始日期 , 結束日期 , 假日列表 )
  
  ※ 延伸學習函數 Networkdays.intl

H. Workday.intl 及 Networkdays.intl 注意事項

以Workday.intl函數為例：

  = Workday.intl ( 開始日期 , 日數, 週末代號 , 假日列表 )

其中，週末代號：
　a. 若非為預設代號，其字串值為七個字元，
　　且字串代表一週內的一天，從星期一開始。
　b. 1：代表非工作日 / 0：代表工作日。
　c. 字串中僅允許字元 1 / 0，1111111 是無效字串。

※ 例如，0000011 代表週末為星期六和星期日。

13. 文字串接函數：Concat / TextJoin

微軟官網介紹：Concat函數 微軟官網介紹：TextJoin函數

A. 新函數動態數組公式寫法

📌 雙負號【- -】

說明：

  1. 此非新舊函數會造成的影響，僅在條件判斷時，將邏輯值（True/False）轉換為數字（1/0）
  2. 多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】

📌 A1＃引用法

使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。

B. Concat 函數

Concat 函數：
目的：合併多範圍 / 字串，但不提供分隔符號
公式：= Concat ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 … )

備註：
(1) CONCAT 會取代 CONCATENATE 函數。
(2) 合併文字間，需要加上分隔符號，可使用TextJoin

📌 Concat 範例

=CONCAT(IF(名單[組別]=$I4,名單[姓名],""))


C. TextJoin 函數

TextJoin 函數：
目的：合併多範圍 / 字串，但並提供分隔符號
公式：= TextJoin ( 1️⃣ "分隔符號", 2️⃣ 是否忽略空白儲存格 , 3️⃣ 加入的文字項目/範圍 )


　2️⃣ 是否忽略空白儲存格
　　 True：忽略空白儲存格
　　 Fasle：不忽略空白儲存格

📌 TextJoin 範例

人員列表：
=TEXTJOIN("、",TRUE,IF(名單[組別]=$L4,名單[姓名],""))

人數：
=COUNTA(UNIQUE(IF(名單[組別]=L4,名單[姓名]),FALSE,TRUE))


14. VBA 【逐欄】合併儲存格

🔶啟動【開發人員】巨集工具


【開發人員】工具在軟體安裝時，並未啟動必須要另行啟動，其指令為【檔案】>【選項】，如下圖勾選：


🔶 範例結果

🔶 VBA語法
利用【F8】進行逐一步驟執行

Sub MergeCell()
  Dim rng As Range
  Application.DisplayAlerts = False     '解除Excel警訊訊息
  Set rng = Selection                   '設定想要合併儲存格的範圍
  k = rng.Count                         '計數想要合併儲存格的個數
  For i = k To 2 Step -1
    If rng(i) = rng(i - 1) Then         '從後向前確認,當後方儲存格與上一個儲存格相同時
      Range(rng(i - 1), rng(i)).Merge   '則合併儲存格
    End If
  Next
  Application.DisplayAlerts = True      '恢復Excel警訊訊息
End Sub

15. VBA 取消合併儲存格並填值

🔶 範例結果

🔶 VBA語法
利用【F8】進行逐一步驟執行

Sub UnMerge()
  Dim rng As Range
  Selection.UnMerge                             '選取範圍進行取消合併儲存格
  Selection.Borders.LineStyle = xlContinuous    '繪製框線
  For Each rng In Selection                     '針對上一步驟選取範圍進行內容循環提取
    If rng = "" Then                            '判斷是否為空值，若是
      rng.Value = rng.Offset(-1, 0)             '上一儲存格值填入
    End If
  Next
End Sub






PowerQuery(PQ)

一、 PQ界面及基本功能介紹

第一階段：基本資料整理

P.5

1、 資料整理

Note：何謂資料行/列

S1：表格化，命名為『業績情形_原』>並載入至PowerQuery(PQ)中
　(1)【常用】>【格式化為表格】> 取消【我的表格有標題】，其目的於課程後續了解其PQ功能，否則無需取消勾選。

(2)【資料】>【從表格/範圍】載入至PQ中

S2：【常用】>【使用第一個資料列作為標頭】>移除合計列【移除資料列】

S3：選取『職務』及『考績』資料行，填空【轉換】>【填滿】

2、欄位拆分

S4：地址拆分：

【新增資料行】>【複製資料行】>【常用】>【分割資料行】
Splitter.SplitTextByDelimiter說明：連結1：微軟官網、連結2：參考網站、連結3：參考網站

= Table.SplitColumn(已重覆資料行, "全地址 - 複製", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
        {"居住縣市", "居住分區", "居住地址"})
        
        
指令說明：
Splitter.SplitTextByDelimiter( 1️⃣ , 2️⃣ )
用途：分隔器函式，這個函式會根據分隔符號來分割文字。
1️⃣分割依據的關鍵詞
2️⃣需對文本類型裡"csv"的處理

S5：右側，查詢設定中：刪除【已變更類型】
※非必要性步驟：其目的課程了解PQ界面。
　(1)因課程範例格式不複雜，故刪除全部【已變更類型】 ，也不會影響步驟
　(2)【轉換】>【偵測資料類型】
　(3)起聘日期調整格式（說明欄位格式如何確認）

3、 新增部門

P.6
S6：新增部門：

方法1：
部門1：【新增資料行】>【來自範例的資料行】>利用輸入方法找輸入特性

方法2：
部門：【新增資料行】>【條件資料行】

= Table.AddColumn(col部門1, "部門", each 
    if Text.Contains([職務], "業務") 
        then "業務部" 
        else "行政部"
)

S7：步驟更名：目的為未來查看時快速查看【col_部門】

第二階段：計算

P.6

【業績成效呈現原則】

1、 業績目標_低標

Excel計算
業績目標_低標 = Round ( 業績目標 * 0.9 , 0 )

方法1：利用界面提供計算

業績目標_低標1：【新增資料行】>【標準】>【乘】0.9>【轉換】>【進位】>【捨入】0

方法2：自訂公式

業績目標_低標：【新增資料行】>【自訂資料行】> =Number.Round( [業績目標]*0.9,0)

= Table.AddColumn(col_業績目標_低標1, "業績目標_低標", each Number.Round([業績目標]*0.9,0))

2、 業績總和

P.7

方法1：利用界面提供計算

業績總和：選取『Q1業績』 、『Q2業績』、『Q3業績』、『Q4業績』>【新增資料行】>【標準】>【加】

= Table.AddColumn(已新增自訂, "業績總和", 
    each List.Sum({[Q1業績], [Q2業績], [Q3業績], [Q4業績]}), Int64.Type)

方法2：自訂公式

業績總和1：業績目標_低標：【新增資料行】>【自訂資料行】>

= Table.AddColumn(已插入加總, "業績總和1", each [Q1業績]+[Q2業績]+[Q3業績]+[Q4業績])

※ 注意事項：

📌List.Sum..等，語法參考

3、 達成率

【計算目的】:討論分母為0的問題
P.7


變更為百分比格式

= Table.AddColumn(已插入加總, "達成率", 
    each if [業績目標]<>0 then [業績總和]/[業績目標]
        else 0)

4、 業績成效

P.8
【計算目的】練習多條件語法
【加入業績成效呈現原則】

= Table.AddColumn(已變更類型3, "業績成效", each 
  if [達成率] >= 1.2 then "高標" 
    else if [達成率] >= 1 then "達成" 
    else if [業績總和] >= [業績目標_低標] then "低標" 
    else "未達成")

第二階段：合併 / 附加查詢

P.8
何謂【合併/附加】查詢


S1：【業績情形_原】新增索引資料行：【新增資料行】>【索引資料行】
　　目的：方便日後查看
S2：載入『地區』、『行政部標準表』、『業務部標準表』表格
S3：地區連結：

(1) 【常用】>【合併查詢】>【合併查詢】

(2) 展開【地區】欄位
※ 但因有的分區有二筆以上，導致新增重覆列

(3) 利用【索引】欄，移除重覆列：
【常用】>【移除資料列】>【移除重複項目】>進行排序


S4：左側查詢區，將【業績情形_原】利用【參考】新增為『業績情形_明細』※ 說明其差異性，例如：刪除【索引】欄、調整欄位順序…
S5：移除未來不必要的欄位
"工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1", "業績總和1", "業績目標_低標1"

= Table.RemoveColumns(來源,{"工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1"})



S6：業績標準處理：
P.10
(1) 利用【附加查詢】為新查詢，合併【行政部/業務部標準表】，命名為『部門業績標準表』

※ 說明：基本原則欄位名稱必須相同
(2) 同S3步驟，『業績情形_明細』及『部門業績標準表』利用【合併查詢】合併兩表，加入『乘數比例』
※ 說明：兩欄以上比對欄位


(3) 展開欄位，取得『乘數比例』欄位

第三階段：分組依據

P.10
【工作目的】合併同類項目：將同一類型的內容放在同一個儲存格中
【顯示結果】

S1：將【業績情形_明細】利用【參考】新增為【業績情形_分組依據】
S2：留下{ "員工姓名", "職務", "業績目標", "業績成效", "部門", "業績總和" }，移除不必要的欄位

= Table.SelectColumns(來源,{"部門","職務", "業績目標", "業績總和","員工姓名","業績成效"})

S3：【常用】>【分組依據】
依下圖新增【人數】、【總業績目標】、【總業績總和】、【人員列表】欄位

【結果】

= Table.Group(已移除其他資料行, {"部門", "業績成效"},
    {{"人數", each Table.RowCount(_), Int64.Type}, 
    {"總業績目標", each List.Sum([業績目標]), type nullable number},
    {"總業績總和", each List.Sum([業績總和]), type number},
    {"人員列表", each List.Sum([員工姓名]), type nullable text}})

S4：修改語法

= Table.Group(已移除其他資料行, {"部門", "業績成效"}, 
    {{"人數", each Table.RowCount(_), Int64.Type}, 
    {"總業績目標", each List.Sum([業績目標]), type nullable number}, 
    {"總業績總和", each List.Sum([業績總和]), type number}, 
    {"人員列表", each Text.Combine([員工姓名], "/")}})

📌Text.Combine語法參考
📌課後練習：試著員工姓名+職務
📌例如：

第四階段：查詢表整理(補充)

補充
藉由【新群組】方式，將查詢表進行分類，以利日後查看。
S1. 在左側『查詢區』，按右鍵 > 新增群組 > 輸入資料夾名稱
S2. 將相同查詢表推入其內即可

第五階段：樞紐 / 取消樞紐資料行

練習1：了解各【分區】及【績效成效】的【業績總和】

P.11
📌【工作目標】
S1：【分區】及【績效成效】的【業績總和】樞紐情形
S2： 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐1】
S3：留下{ "分區", "業績總和", "業績成效" }，移除不必要的欄位

= Table.SelectColumns(來源,{"分區", "業績總和", "業績成效"})

S4： 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】
【顯示結果】

練習2：顯示各【分區】及【績效成效】的【員工姓名】情形

P.12
【顯示結果】

S1. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】


S2-1. 留下 { "地區", "員工姓名", "業績成效" }，移除不必要的欄位
S2-2. 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】
🔻筆數多無法顯示於儲存格內
(4) 查詢表更名為【業績情形_樞紐2_錯誤】

S2. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】
S3. 留下 { "地區", "員工姓名", "業績成效","達成率" }，移除不必要的欄位

= Table.SelectColumns(來源,{"員工姓名", "分區", "業績成效", "達成率"})

S4. 【新增資料行】>【自訂資料行】

📌 Text.From 語法參考
S5. 先前說明進行樞紐錯誤情形時，因多筆同資料無法全部顯示，故必須先利用【分組依據】合併之
【目的】

【步驟】

= Table.Group(Col_員工達成率, 
                {"分區", "業績成效"}, 
                {{"計數", each List.Sum([員工達成率]), type text}})

(4) 修改語法

= Table.Group(Col_員工達成率, 
                {"分區", "業績成效"}, 
                {{"計數", each Text.Combine( [員工達成率]," / ")}})


(3) 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】

📌 例如









二、PQ 範例

1. 找出每個人的最高業績及季別

P.14
S1：將【業績情形_明細】利用【參考】新增為【ex最高業績及季別】
S2：留下{ "員工姓名", "Q1業績", "Q2業績", "Q3業績", "Q4業績" }，移除不必要的欄位
S3：選取【"Q1業績", "Q2業績", "Q3業績", "Q4業績"】欄位，【取消資料行樞紐】，並利用【取代值】移除『業績』字樣

S4：針對【員工姓名】設定為遞增排序 / 【業績值】設定為遞減排序

S5：選取【員工姓名】，【常用】>【移除資料列】>【重覆項目】刪除重覆項目
目的：利用移除重覆項目，保留第一筆記錄的工作特性

S6：利用【合併查詢】，加入人員部門、職務，並還原其名單順序

Keyword："索引","部門","職務",

2. 中文排名【總排名】

P.15

何謂中式排名？

S1：在左側『查詢區』，按右鍵 > 新增群組 > 【ex中式排名】
S2：將【業績總和】欄位移除重覆後，新增排名
(1) 將【業績情形_明細】利用【參考】新增為【ex中式排名S1】
(2) 選取【業績總和】> 移除其他欄位 > 進行遞減排序 > 移除重覆值 > 新增【排名】索引欄

S3：利用合併查詢方式，將【業績情形_明細】，新增排名
(1) 【常用】>【合併查詢】<【將查詢合併為追加查詢】

(2) 展開【排名】欄位

3. 利用合併查詢進行兩表比對

P.16
利用合併查詢進行兩表比對，比Vlookup查詢更加簡單及靈活

A. 說明：六種查詢理論

B. 資料比對：找出新/舊/離職員工

(1) 工作目標

(2) 說明

(3) 步驟

S1：載入『表7月』、『表8月』表格

S2：【常用】>【合併查詢】>【將查詢合併為新查詢】，並展開其合併欄位

S3：【新增資料行】>【條件資料行】，進行新舊員工類型判斷

S4：最後整理

4. 資料分割 / 合併

P.18

A. 資料分割

S1：【常用】>【分割資料行】

S12：【常用】>【分割資料行】

S3：選取【訂單資訊.1】欄位，【轉換】>【樞紐資料行】

B. 資料合併

P.19
S1：將(1)利用左側窗格點選【參考】新增查詢後，選取【姓名、電話、產品】，再【轉換】>【取消資料行樞紐】>【合併資料行】合併結果，如下圖
S2：選取【訂單編號】欄位，【轉換】>【分組依據】

S3：修改語法

= Table.Group(已合併資料行, {"訂單編號"},
 {{"項目", each Text.Combine([項目],"#(lf)"), type text}})

5. 一維 / 二維 資料表轉換

P.20

A. 【二維】轉為【一維】

選取各『縣市』欄位 > 【轉換】>【取消資料行樞紐】

B. 【一維】轉為【二維】

選取各『地區』欄位 > 【轉換】>【資料行樞紐】

📌 例如：

6. { List } 應用

P.20

A. 廠商及訂單編號列表

(1) 說明：【笛卡爾積 , 笛卡兒積】應用

笛卡爾乘積是指在數學中，兩個集合X和Y的笛卡爾積（Cartesian product），又稱直積，表示為X×Y，第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員。簡單來說就是2組資料互相交集.

(2) 步驟

S1：利用分割資料切割新增【訂單編號_起】、【訂單編號_迄】欄位

S2：【新增資料行】>【自訂資料行】

資料行名稱list
= {[#"訂單編號 -起"]..[#"訂單編號 - 迄"]}

B. 文本 移除 / 提取

P.20

(1) 說明：文本 移除 / 提取函式

📌 Text.Select ( ) 和Text.Remove ( )

提取項目 M函數
各種特殊符號、0-9數值、
所有大小寫英文字母		 Text.Select ( [文本資料行] , { ″ ″ .. ″~″})
數值0-9 Text.Select ( [文本資料行] , { 0 .. 9})
文字0-9 Text.Select ( [文本資料行] , { ″0″ .. ″9″})
英文大寫 Text.Select ( [文本資料行] , { ″A″ .. ″Z″})
英文小寫 Text.Select ( [文本資料行] , { ″a″ .. ″z″})
英文大/小寫 Text.Select ( [文本資料行] , { ″A″ .. ″z″})
中文 Text.Select ( [文本資料行] , { ″一″ .. ″龜″})

(2) 練習：中英文拆分

【目的】

【步驟】
S1：抓取中文文字語法，新增【自訂資料行】>公式為【 = Text.Select([產品],{″一″..″龜″})】

= Text.Select([商品],{"一".."龜"})

S2：抓取英文，新增【自訂資料行】>
公式為【 = Text.Trim ( Text.Select ( [產品] , {″ a ″…″ z ″,″ A ″…″ Z ″,″ ″,″ . ″,″ ‘ ″}))】
移除前後多餘空格，因範例中英文有【’】及【.】

= Text.Trim ( Text.Select ( [商品] , {"a".."z","A".."Z"," ",".","'"}))

S3：也可以移除中文字，來取得英文

= Text.Remove([商品],{"一".."龜"})

7. 多檔案匯入

P.22

透過Power Query將多個資料來源的資料合併、追加到一起，任意組合資料、將資料進行分組、透視等整理操作方法快速整併成同一個，以利數據分析。

S1：開啟Excel新檔 > 載入資料 > 活頁簿
【2016版Excle】

【365版Excle】

S2：顯示載入資料情形 > 點選【轉換資料】

S3：保留【Content】欄位，其他欄位移除，其內容如下：

S4：【新增資料行】>【自訂資料行】

(1) 載入CSV檔案至PQ：
　　 Csv.Document ( )

(2) 本身Excel檔案載入至PQ：
　　 Excel.CurrentWorkbook ( ) { [ Name="表格名稱" ] } [Content]

(3) 由其他Excel檔案載入至PQ：
　　 Excel.Workbook ( [Content] , true )

= Excel.Workbook([Content],true)


S6：依序展開其欄位

8. 巧用Power Query輕鬆管理文件

P.22

A. 例如

B. 步驟

S1：與上個個單元作法相同載入檔案，開新檔案>【資料】>【取得資料】>【從檔案】>【從資料夾】>選取想要載入的資料夾 >【轉換資料】


S2：依步驟完成，建立公式進行串接組合，並刪除不必要欄位：

S3：完成後，回填至Excel，建立連結公式：

9. Google表單結果連結至Excel方法

S1：開啟Google表單回報檔案，點選【檔案】>【共用】>【發佈至網路】

S2：選擇連結的檔案類型 > 複製其網址

S3：開啟Excel新檔案，【資料】>【從Web】，將S2網址貼入，依步驟載入即可

https://docs.google.com/spreadsheets/d/e/2PACX-1vTWau93NwnhTXACiMUDYp-Y-G-_5ZYF_rluokCaxRKPQeDzWp1uBklWVQpqtOeOUbv6NH7-E_w628TR/pub?output=xlsx

三、PQ-M函數

list.sum

📌List.Sum..等，語法參考
計算說明：

公式 結果
= 1 + 2 + 3 + 4 + null null
= List.Sum ( { 1 , 2 , 3 , 4 , null } ) 10

情境說明：
PQ表中，若其值有【null】時，加總無法計算其值，可改用List.Sum

S1：建立查詢表
= #table({"欄1","欄2","欄3"},{{1,2,3},{2,null,8}})

S2：新增加總，表中有【null】時，發生異常
= Table.AddColumn(來源, "總和", each [欄1]+[欄2]+[欄3])

S3：改由List.Sum計算
= Table.AddColumn(已新增自訂, "List.Sum", each List.Sum({[欄1], [欄2], [欄3]}))

分隔欄位

分隔欄位：Splitter.SplitTextByDelimiter























Power Pivot(PP)

P.26
【範例說明】

1. 啟動【Power Pivot】增益集

※ 詳前面章節

2. 數據載入至【Power Pivot】

P.26
S1. 【主資料夾】>【從其他來源】> 點選【Excel檔案】

S2. 點選欲載入數據的Excel檔案

S3. 勾選欲載入數據的工作表來源

3. 建立數據間的關聯圖

P.28

4. 建立計算公式及載入關聯欄位數據

A. 名詞介紹

補充
🔶 兩軟體之間名詞差異
使用PBI計算必須抛棄Excel儲存格計算的思維模式，在PBI中的資料表是以【資料行】方式儲存。

Excel PP、PQ、PBI
工作表 資料表
欄 資料行
量值：即以【資料表】或【資料行】進行計算

🔶 建議

新增資料行：
缺點：使用時浪費記憶體
優點：使用Excel公式習慣的人，傾向使用容易理解
使用時機：
　▷ 將列內容放入篩選器、交叉分析篩選器、行、列
　▷ 需要利用新增的資料行與其他表建立關係時
量值：
缺點：
　▷ 公式書寫較難以理解
　▷ 不可使用於交叉分析篩選器
優點：
　▷ 不浪費記憶體，只有被拖到【報告】中才會執行運算
　▷ 可以重覆使用的計算式
使用時機：
　▷ 需要重覆使用的計算式
　▷ 是否根據上下文動態運算

B. 工作表載入關聯欄位之值

P.28
點選【訂單明細】工作表：建立公式

欄位 公式
單價 單價:=RELATED('商品明細'[銷售單價])
出貨金額 出貨金額:=[出貨數量] * RELATED ( '商品明細'[銷售單價] ) 

單價:=RELATED('商品明細'[銷售單價])

出貨金額:=[出貨數量]*RELATED('商品明細'[銷售單價])

C. 新增計算公式

P.29

欄位 公式
出貨總金額_SUM 出貨總金額_SUM:=SUM( '訂單明細'[出貨金額] )
出貨總金額 出貨總金額:=SUMx('訂單明細',
'訂單明細'[出貨數量] * RELATED ('商品明細'[銷售單價])
)
訂單明細列數 訂單明細列數:= COUNTROWS ( '訂單明細' )
訂單筆數 訂單筆數:= DISTINCTCOUNT ( '訂單明細'[訂單編號] )
總出貨量 總出貨量:=SUM ( '訂單明細'[出貨數量] )
銷售獎金 銷售獎金:=ROUND ( SUMx ( '訂單明細' , '訂單明細'[出貨金額]* 0.1 ) , 0 )
銷售獎金_每筆round 銷售獎金_每筆round:=SUMx ( '訂單明細' , ROUND( '訂單明細'[出貨金額]*0.1 , 0 ) ) 

出貨總金額_SUM:=SUM('訂單明細'[出貨金額])

出貨總金額:=SUMX('訂單明細','訂單明細'[出貨數量]*RELATED('商品明細'[銷售單價]))
訂單明細列數:=COUNTROWS('訂單明細')
訂單筆數:=DISTINCTCOUNT('訂單明細'[訂單編號])
總出貨量:=SUM('訂單明細'[出貨數量])
銷售獎金:=ROUND(SUMX('訂單明細','訂單明細'[出貨金額]*0.1),0)
銷售獎金_每筆round:=SUMX('訂單明細',ROUND('訂單明細'[出貨金額]*0.1,0))

5. 建立樞紐分析表

P.29

A. 指令

Excel原生環境 Power Pivot 工具
指令：【插入】>【樞紐分析表】

B. 樞紐設定原則

C. 選項

D. 欄位排序

(1) 手動

欄位直接拖曳

(2) 自動

指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目

E. 群組類型

文字
數值
日期

【練習說明】

P.30

6. 建立大綱階層

P.31
S1：建立大綱階層，如欄位不在同一工作表則利用Related函數載入

商品類別:=RELATED('商品主類別'[商品類別])

S2：啟動【圖表檢視】

S3：加入樞紐分析表

7. 建立【日期表】

P.32
【顯示結果】

S1：新增【日期表】

季:=INT(([月]+2)/3)

S2：日期表與訂單明細，建立關聯

8.【群組】建立及說明【值的顯示方式】

P.33

9. 結合【交叉分析篩選器】

S1：點選【插入交叉分析篩選器】及【插入時間表】

S2：針對樞紐分析表進行命名

S3：以上兩步驟，點選【篩選連線】進行篩選器與樞紐分析表之結合確認

Last changed by 
 
心怡zoego
800
Published on HackMD
Expand allBack to topGo to bottom