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

###### tags: `工務局` `關` `Excel` # 工務局_Excel資料分析與視覺化應用 <style> /* 加入Logo */ .toc:{position: relative;} .toc:before{ content: url(https://zoego.tech/img/logo.svg); width:200px; position:fixed; top:0 ; left:0; } /* 調整左側NAV */ .markdown-body { max-width: 900px !important;} .markdown-body h1,.markdown-body h2{border-bottom: none;} .ui-toc-dropdown .nav>li>a{ color: #000;} .ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a{ color:#C00000;} /* h1~H5 修改 */ h1{ color:#4C4C6D; } .h2{ color:#52006A; background-color:#A2DBFA; line-height:1.8em;font-size:.9em;display:block; padding-left:10px;border-radius:5px;} h3{ color:#BF1363; } .h4{color:#000099; font-size:1.2em;} .h5{color:#7030A0; font-size:1.2em;} /* 講師資訊美化 */ .alert-warning { border-width: 2px; width: 60%; margin: auto;} /* 指令縮圖 ICON圖設定 */ .img3{ width:30px; display:inline-block;margin:0 5px;} .img4{ width:40px; display:inline-block;margin:0 5px;} .img5{ width:50px; display:inline-block;margin:0 5px;} .img6{ width:60px; display:inline-block;margin:0 5px;} /* 圖片尺寸設定 */ .img20{ width:20%; margin:20px auto; display:block; text-align: center;} .img30{ width:30%; margin:20px auto; display:block; text-align: center;} .img40{ width:40%; margin:20px auto; display:block; text-align: center;} .img50{ width:50%; margin:20px auto; display:block; text-align: center;} .img60{ width:60%; margin:20px auto; display:block; text-align: center;} .img70{ width:70%; margin:20px auto; display:block; text-align: center;} .img80{ width:80%; margin:20px auto; display:block; text-align: center;} .img90{ width:90%; margin:20px auto; display:block; text-align: center;} .img100{ width:100%; margin:20px auto; display:block; } /* 連結URL 調整 */ .urlr{ text-align:right; line-height:1.5em;display:block;} .urll{ line-height:1.5em;display:block;} /* 提供講義標示頁碼 */ .page{display:flex; justify-content: right;} .page span{ background:#ffdd75;border:1px solid #d1a128;border-radius:5px; padding:5px;color:#000;text-align:right; float:right; } /* Slider 外掛美化 */ iframe{ width:850px; height:478px; display:block; margin:auto; border-width: 0;border:2px solid #000;} /* 設定 pre 及快速鍵 */ .markdown-body code { border: 1px solid #000; margin:0 5px; } .markdown-body pre>code { font-size: 1.2em; } .markdown-body pre { border: 1px solid #ab0505 !important; margin:10px 0; padding:5px 10px;} /* 快速鍵 > 語法於P中 */ .markdown-body p>code { color:#BF1363 !important; border:none; background:none} /* 表格設定 */ .markdown-body table { width: fit-content; margin: auto; } .markdown-body table th, .markdown-body table td { border: 1px solid #7F7F7F; } .tableTitle{text-align:center; display:block;} .red{color:#BF1363;font-weight:bold;} .blue{color:#265F92;font-weight:bold;} .step{ padding-left:1.5em;color:blue;font-size:1.1em; } .textc{text-align:center; display:block;} .markdown-body p{ margin-bottom: 0;} .ex{color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;} /* 置中、圓角框、項目 */ .title{ text-align:center; display:block;color:#766161;font-size:1.2em;font-weight:bold; border:1px solid;line-height:2em;border-radius:5px} .youtube:before{ content:""; display:block; position:absolute; top:50%; left:50%; z-index:1; transform:translate(-50%,-50%); width:80px; height:80px; background:url(https://zoego.tech/img/icon_youtube2.png); background-size: contain; border-radius: 10px; box-shadow: 1px 1px 3px #000; } .youtube{ border:1px solid #333; } </style> # 課程開場介紹 :::warning 課程講師資訊巨匠電腦講師：呂心怡 E-mail：zoego99@gmail.com 上課日期：2022/8/2、4 及 2022/9/12、14[課程練習下載區：https://bit.ly/3mlZcXH](https://bit.ly/3mlZcXH) ::: ## A、適用對象 適用所有Excel使用者，尤其是需要經常對原始資料進行手工合併、追加、去除重複數據等操作的資料分析，或者工作中經常運用Excel的樞紐分析工具進行數據分析人員。 ## B、課程概述 ### 1、 Power Query(PQ) 在工作中經常使用Excel手工合併多張表格；經常需要對來源資料進行刪除、分列、去除重複數據等操作，然而做過資料分析相關工作的你應該深有體會 – 我們需要製作一份數據分析報告的時候，80%的時間要用來處理不規範的來源資料。 想要擺脫重覆、手工、枯燥的整理工作的苦惱嗎？進而大幅度提升工作效率，節省寶貴時間嗎？Excel提供內建好用的Power Query插件，提供親和的介面化操作，讓很多重複工作進行簡化。 很可惜多數人使用Excel卻都忽略Power Query插件，而學習這項工具已經是未來趨勢，藉由本次課程將列舉實務性範例，解決許多惱人且複雜的報表整理，進而提高工作效率。 :::success (A) 何謂Excel表格及其使用優點介紹 (B) 將多個Excel、CSV檔案或網頁，不同來源類型數據的輕鬆獲取 (C) 分割、合併與提取儲存格資料 (D) 一維 / 二維資料表轉換 (E) 利用合併查詢進行兩表比對，比Vlookup查詢更加簡單及靈活 (F) 產品明細快速拆分【中/英】文  ::: ### 2、 Power Pivot (PP) 只要進行數據分析工作者，都會使用【樞紐分析表(Pivot Table)】，也都看過/聽過【資料管理模型(PowerPivot)】卻混淆不知道如何應用，其實這兩項套件是相輔相成的工具，差別在PowerPivot可匯整多張關係工作表，更加結構化進行數據分析。 在工作中愈來愈多數人都運用樞紐工具進行數據分析，基本樞紐操作已經難不倒，本次課程將藉由【Power Pivot】增益集解決Vlookup連結多報表困擾，並提出執行樞紐分析可能遇到的困擾，及製作樞紐時經常忽略的功能，讓樞紐分析運用更加靈活，得心應手。 :::success (A) 樞紐分析前置作業 　　(1) 解決日期欄位無法產生年/季/月報困擾 　　(2) 利用【Power Pivot】增益集解決Vlookup連結多報表困擾 (B) 樞紐分析進階應用—經常忽略技巧分享 　　(1) 如何有效率美化樞紐分析表 　　(2) 數據顯示依值顯示方式，從而更清晰的看出數據之間的關係 　　(3) 藉助樞紐分析表中的計算欄位功能產生欄位 　　(4) 利用交叉分析篩選器動態數據分析 　　(5) 利用群組特性，彙整年報、季報、月報 ::: ## C、未來展望 <iframe title="PBI_咖啡銷售數據 - 儀表板" style="width:100%; height:100% ;" src="https://app.powerbi.com/view?r=eyJrIjoiMzBmODhhMjgtNDBlYy00ODBjLThkY2MtZDFhMTRjNjM2MWU4IiwidCI6ImNkNWRmNDkxLTZhMTYtNDkzZS1iNTVhLTkzOGYzYWE5YWRlYiIsImMiOjEwfQ%3D%3D" frameborder="0" allowFullScreen="true"></iframe> 在工作任務中，經常需要利用Excel樞紐分析功但不了解Power BI使用者，Power BI Desktop是安裝在本機電腦的免費商業分析工具，可從多種檔案類型匯入資料後，快速建立互動圖表的工具，輕鬆完成大數據的分析！ ### 1、各項軟體間的分責運用 P.1 整體性概觀了解PowerBI各項軟體間的分責運用。 <a href="https://hackmd.io/_uploads/ryLSpv2rY.png">![](https://hackmd.io/_uploads/ryLSpv2rY.png)</a> ### 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安裝啟動 <a href="https://hackmd.io/_uploads/BkrkuU2SF.png">![](https://hackmd.io/_uploads/BkrkuU2SF.png)</a> ### 2、指令位置 <a href="https://hackmd.io/_uploads/ByLspUhrY.png">![](https://hackmd.io/_uploads/ByLspUhrY.png)</a> # Excel 應用技巧 ## 1. Excel批次貼入圖片方法 補充 🔶 範例結果 ![](https://hackmd.io/_uploads/S1U_CZ8VK.png) S1：複製圖片路徑，將所有照片置於同一資料夾中，並複製其路徑 ![](https://hackmd.io/_uploads/rJ3bJGU4Y.png) S2：語法說明複製下方法語法至Excel中，修改其內容 ``` ="<table><img src='圖片路徑資料夾\" & 檔名 & ".副檔名' width='50'>" ``` 語法說明 ![](https://hackmd.io/_uploads/BJ8Lyz8VF.png) S3：貼入Excel調整情形參考 ![](https://hackmd.io/_uploads/HyhqyfLVY.png) S4:路徑結果貼入記事本中，將路徑結果貼入記事本中，將其Excel設定移除 ![](https://hackmd.io/_uploads/SJ_CyML4t.png) S5:再將記事本路徑貼回Excel中，再將記事本路徑貼回Excel中，調整圖片大小即可 ## 2. Indirect與【名稱定義】結合應用 補充 ### A. 函數 #### (1) Vlookup查表函數 依查詢值進行資料比對輸出相關資料之函數 :::success = Vlookup ( 查詢值 , 查表範圍 , 回傳指定欄數 , 選項) 說明 * 查詢值若為『文字』，選項為False 查詢值若為『數值』，選項通常為True，且查表範圍應遞增 * 查詢值應為查表範圍的第一欄 / 列 * 查表範圍若為『縱』向查詢，即為 Vlookup 函數 查表範圍若為『橫』向查詢，即為 Hlookup 函數 * 選項： * False，必須比對完合符合才回傳，查無資料以 “#N/A” 表示 True 或省略，比對回傳接近不超過查詢值的資料 * 查表函數，可搭配 Iferror 函數 ::: #### (2) Indirect 間接抓取範圍函數 ``` = Indirect ( 定義名稱 ) ``` ### B. 範例 <a href="https://hackmd.io/_uploads/H10VUU3B5.png">![](https://hackmd.io/_uploads/H10VUU3B5.png)</a> S1：以C2儲存格為例： ``` =VLOOKUP( $A2 , 銷售01月 !$A$2:$F$13 , 6 , FALSE ) =VLOOKUP( $A2 , indirect( C$1 & "!$A$2:$F$13" ) , 6 , FALSE ) ``` 以上公式完成複製於其他儲存格中即可。 S2：選取【員工編號】及【圖片】範圍，建立名稱定義，如下圖 <a href="https://hackmd.io/_uploads/HkCXYLnr5.png">![](https://hackmd.io/_uploads/HkCXYLnr5.png)</a> S3：將被查詢圖片的員工編號，設定為圖片的名稱定義來源，如下圖建立： ``` 定義名稱：員工圖片公式：=INDIRECT(總表!$L$3)&"_" ``` <a href="https://hackmd.io/_uploads/SyX0WvnH9.png">![](https://hackmd.io/_uploads/SyX0WvnH9.png)</a> S4：選取第一名員工圖片的儲存格，利用複製，並點選【貼上】>【連結的圖片】，並將公式改為【=員工圖片】定義名稱 <a href="https://hackmd.io/_uploads/BJKtzw3Hc.png">![](https://hackmd.io/_uploads/BJKtzw3Hc.png)</a> ## 3. 取得數據無法計算的困擾 補充 ### A. 日期輸入原則的重要性 輸入結構【年/月/日】，定義為西元年份 日期以 `/` 或 `-` 區隔，時間以 `:` 區隔 今年年份輸入時可省略 Win系統為台灣版本時，民國年份之日期前方加入`R` 例`R30/3/5`，Excel自動轉換為1941/3/5。 ### B. 情境說明：使用乘積函數困擾（權重計算） ![](https://hackmd.io/_uploads/S1T931MNY.png) ### C. 善用【資料剖析】進行轉換 指令：【資料】>【資料剖析】 ### D. 特殊目標..介紹 善用【常用】>【尋找與選取】>【特殊目標】 ![](https://hackmd.io/_uploads/H1hvWzfEt.png) [線上銷售明細](https://zoego.tech/salelist.html) #### 📌【課程範例】 🔶【範例】![](https://hackmd.io/_uploads/Bkr6XfGVt.png) 🔶【步驟】 練習1：空格向下填滿 S1：選取數據範圍 S2：【常用】>【尋找與選取】 S3：【特殊目標】> 點選【空格】選項 S4：輸入`=`，方向鍵`↑` S5：輸入完成`Ctrl`+`Enter` S6：移除公式 全選範圍>【複製】>【選擇性貼上】>【值】123 練習2：依篩選條件進行美化 快速鍵：【Alt】+【；】 ## 4. 解決總和遇上篩選困擾 補充 ### A. 名稱定義 <a href="https://hackmd.io/_uploads/HJeIbBHEt.png">![](https://hackmd.io/_uploads/HJeIbBHEt.png)</a> ### B. 小計函數：Subtotal ![](https://hackmd.io/_uploads/BJePGHrNK.png) ### C. 會除錯的小計函數：Aggregate Aggregate函數比Subtotal函數還要強大，因面對忽略錯誤值、隱藏行和分類匯總嵌套，滿足Subtotal無法處理，但Aggregate滿足各種情況下的統計需求！ ![](https://hackmd.io/_uploads/SkycGSSNY.png) ![](https://hackmd.io/_uploads/SJxjGSrVt.png) ## 5. 排序與自訂清單 ### A. 排序 <a href="https://hackmd.io/_uploads/HJXQKgf4F.png">![](https://hackmd.io/_uploads/HJXQKgf4F.png)</a> ### B. 自訂清單 #### (1) 目的 :::info ※資料排序與自訂清單結合在排序完成後以使用者角度來看，例如：職務、考績或地區，有自行的排序順序，但電腦只能依筆劃或數值排序時，則需運用【自訂清單】要求順序。 ::: 填滿序列 資料排序 樞紐分析 #### (2) 指令 【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 <a href="https://hackmd.io/_uploads/SkhtieMNY.png">![](https://hackmd.io/_uploads/SkhtieMNY.png)</a><a href="https://hackmd.io/_uploads/SJlZJMGEF.png">![](https://hackmd.io/_uploads/SJlZJMGEF.png)</a> ### C. 自訂清單 :::info ※【格式化為表格】無法使用小計功能，利用【轉換為範圍】移除格式化為表格前，應確認顏色是否移除 ::: ### S1：小計之前先排序 指令：【資料】>【排序】 ### S2：先排序的欄位，先執行小計工作 指令：【資料】>【小計】 S2-1：第一階層排序欄位【經銷商】 <a href="https://hackmd.io/_uploads/r1DdTvgPK.png">![](https://hackmd.io/_uploads/r1DdTvgPK.png)</a> S2-2：第二階層排序欄位【商品名稱】 <a href="https://hackmd.io/_uploads/S1eh6wgwF.png">![](https://hackmd.io/_uploads/S1eh6wgwF.png)</a> ## 6. 使用Excel表格重要性 P.3 ### A. Excel表格介紹 可以快速地建立、格式化及展開 Excel 表格，組織工作表上的資料，讓其更易於使用。 #### (1) 指令介紹 📌 建立 ![](https://hackmd.io/_uploads/BJko6I7NK.png) 📌 表格化指令 <a href="https://hackmd.io/_uploads/Synd8KY4Y.png">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</a> #### (2) 表格化選取方法 🔶 左鍵一下選取內容、連二下選取內容及標題 ![](https://hackmd.io/_uploads/HkW-xKY4t.png) #### (C) 表格化後，公式表示結構 =表格名稱〔＠欄位名稱〕 有＠：相對性欄位 沒有＠：整欄計算 ### B. 使用優點 #### (1) 容易選取資料範圍且數據自動縮放 #### (2) 公式容易辨識 #### (3) 交叉分析篩選器 交叉分析篩選器為一視覺化控制項，以互動且直覺的方式，快速篩選僅顯示所需的資料的資料。 <a href="https://hackmd.io/_uploads/ryV6FFKVt.png">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </a> ### C. 移除表格注意事項 #### (1) 移除表格化前，先確認是否要移除色彩 為了取得帶狀列的色彩，直接移除表格化 因反悔表格化功能，而表格會動用排序時，必須要移除色彩 #### (2) 移除方法 ![](https://hackmd.io/_uploads/ryMkItYEt.png) ## 7. 當下時間記錄 S0：列表輸入情形 S1：在E2儲存格輸入公式 ``` =IF(E2="",NOW(),E2) ``` 公式輸入思考原則：繳交日期E2顯示當下時間，否則顯示空白 <a href="https://hackmd.io/_uploads/SyGAy7rac.png">![](https://hackmd.io/_uploads/SyGAy7rac.png)</a> S2：在E2儲存格修改公式 ``` =IF(D2="","",IF(E2="",NOW(),E2)) ``` 公式輸入思考原則：當D2輸入已繳(V)，繳交日期E2顯示當下時間，否則顯示空白 <a href="https://hackmd.io/_uploads/H18xZXSac.png">![](https://hackmd.io/_uploads/H18xZXSac.png)</a> S3：在進行S2公式建立時，會造成公式循環問題時，利用【檔案】>【選項】(設定如下圖) [參考資訊：移除或允許循環參照，並了解反覆運算用意](https://support.microsoft.com/zh-tw/office/%E7%A7%BB%E9%99%A4%E6%88%96%E5%85%81%E8%A8%B1%E5%BE%AA%E7%92%B0%E5%8F%83%E7%85%A7-8540bd0f-6e97-4483-bcf7-1b49cd50d123) <a href="https://hackmd.io/_uploads/HkciSwpHc.png">![](https://hackmd.io/_uploads/HkciSwpHc.png)</a> ## 8. 每月各項收支報表 ### A. 二維查表函數介紹 #### (1) Index函數 根據欄列號索引回傳查表範圍的值 ``` = Index ( 查表範圍 , 指定列數 , 指定欄數 ) ``` <a href="https://hackmd.io/_uploads/BkEql5r4F.png">![](https://hackmd.io/_uploads/BkEql5r4F.png)</a> #### (2) Match函數 傳回搜尋項目於搜尋範圍中的相對位 :::info Match 函數：目的：傳回搜尋項目於搜尋範圍中的相對位置公式：=Match ( 1️⃣ 查詢值, 2️⃣ 查表範圍 , 3️⃣ 比對方式類型 ) 　3️⃣ 比對方式類型： 　　1或省略：查詢範圍須『遞增』 　　0：查詢範圍不須事前排序，找到完全符合查詢值 　　-1：查詢範圍須『遞減』 ::: 🔶 公式比擬 <a href="https://hackmd.io/_uploads/rk3dE5B4t.png">![](https://hackmd.io/_uploads/rk3dE5B4t.png)</a> ### B. 範例 <a href="https://hackmd.io/_uploads/rJX66-UEt.png">![](https://hackmd.io/_uploads/rJX66-UEt.png)</a> ## 9. 符合條件計算函數 註：課程範例中，先行針對【職員薪資明細】的表格化及名稱定義，在『總表』工作表中，依各職務進行計算 符合條件計算函數：Countif、Sumif、CountifS、SumifS、Averageif、AverageifS ![](https://hackmd.io/_uploads/BkjfkRBNY.png) ![](https://hackmd.io/_uploads/H1oD0PxE5.png) ## 10. 年資 ![](https://hackmd.io/_uploads/H1RhqCBVY.png) #### (1) 公式說明：計算兩日期間的天數、月數或年數 ``` = Datedif ( 開始日 , 結束日 , " 單位 " ) ※ 說明：單位：決定要傳回的單位，必須前後加上 " " Y代表滿幾年、M代表滿幾月、D代表滿幾日 MD代表兩日期間的天數差，忽略月和年 YM代表兩日期間的月數差，忽略日和年 YD代表兩日期間的天數差，忽略年要求兩日期實際差距幾年?幾月?幾日應使用Y、YM、MD單位 ``` #### (2) 範例結果：年資計算 ``` = Datedif ( [@起聘日期] , TODAY() , "Y" ) ``` ## 11. 星期計算 ### A. 【日期】格式設定 <a href="https://hackmd.io/_uploads/S1msN9N4t.png">![](https://hackmd.io/_uploads/S1msN9N4t.png)</a> ### B. 星期計算公式 🔶 星期(中) ![](https://hackmd.io/_uploads/BkGWMJ8Vt.png)🔶 星期(英) ![](https://hackmd.io/_uploads/ryg5GyLNY.png) ## 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 >[color=red][微軟官網介紹：Concat函數](https://support.microsoft.com/zh-tw/office/concat-%E5%87%BD%E6%95%B8-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2) [微軟官網介紹：TextJoin函數](https://support.microsoft.com/zh-tw/office/textjoin-%E5%87%BD%E6%95%B8-357b449a-ec91-49d0-80c3-0e8fc845691c) ### A. 新函數動態數組公式寫法 📌 雙負號【- -】 說明： 1. 此非新舊函數會造成的影響，僅在條件判斷時，將邏輯值（True/False）轉換為數字（1/0） 1. 多條件篩選時，【 * 】條件為【且】，【 + 】條件為【或】 📌 A1＃引用法 使用A1#來表示引用動態數組，可以隨著數據的變化無縫調整大小。相當於引用A1中動態數組的整個填充範圍。 ### B. Concat 函數 :::info Concat 函數：目的：合併多範圍 / 字串，但不提供分隔符號公式：= Concat ( 1️⃣ 範圍1/文字1, 2️⃣ 範圍2/文字2 ... ) 備註： (1) CONCAT 會取代 CONCATENATE 函數。 (2) 合併文字間，需要加上分隔符號，可使用TextJoin ::: 📌 Concat 範例 ``` =CONCAT(IF(名單[組別]=$I4,名單[姓名],"")) ``` <a href="https://hackmd.io/_uploads/S1Tw8rxNc.png">![](https://hackmd.io/_uploads/S1Tw8rxNc.png) </a> ### C. TextJoin 函數 :::info TextJoin 函數：目的：合併多範圍 / 字串，但並提供分隔符號公式：= TextJoin ( 1️⃣ "分隔符號", 2️⃣ 是否忽略空白儲存格 , 3️⃣ 加入的文字項目/範圍 ) 　2️⃣ 是否忽略空白儲存格 　　 True：忽略空白儲存格 　　 Fasle：不忽略空白儲存格 ::: 📌 TextJoin 範例 ``` 人員列表： =TEXTJOIN("、",TRUE,IF(名單[組別]=$L4,名單[姓名],"")) 人數： =COUNTA(UNIQUE(IF(名單[組別]=L4,名單[姓名]),FALSE,TRUE)) ``` <a href="https://hackmd.io/_uploads/Bk8t_BeEq.png">![](https://hackmd.io/_uploads/Bk8t_BeEq.png) </a> ## 14. VBA 【逐欄】合併儲存格 🔶啟動【開發人員】巨集工具 【開發人員】工具在軟體安裝時，並未啟動必須要另行啟動，其指令為【檔案】>【選項】，如下圖勾選： <a href="https://hackmd.io/_uploads/H1FNvy7Zi.png">![](https://hackmd.io/_uploads/H1FNvy7Zi.png) </a> 🔶 範例結果 ![](https://hackmd.io/_uploads/B1wtZRIlj.png) 🔶 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 取消合併儲存格並填值 🔶 範例結果 ![](https://hackmd.io/_uploads/HJmZ5RIei.png) 🔶 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功能，否則無需取消勾選。 ![](https://i.imgur.com/NCSbcXx.jpg) 　(2)【資料】>【從表格/範圍】載入至PQ中 ![](https://i.imgur.com/N86W9lE.png) S2：【常用】>【使用第一個資料列作為標頭】>移除合計列【移除資料列】 ![](https://i.imgur.com/2QKPlYO.png) S3：選取『職務』及『考績』資料行，填空【轉換】>【填滿】 ![](https://i.imgur.com/yC0rgue.png) ### 2、欄位拆分 S4：地址拆分： ![](https://i.imgur.com/yF3t0OB.png) 【新增資料行】>【複製資料行】>【常用】>【分割資料行】 Splitter.SplitTextByDelimiter說明：[連結1：微軟官網](https://docs.microsoft.com/zh-tw/powerquery-m/splitter-functions)、[連結2：參考網站](https://kknews.cc/zh-tw/code/b5ljor9.html)、[連結3：參考網站](https://pqfans.com/1529.html) ``` = Table.SplitColumn(已重覆資料行, "全地址 - 複製", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"居住縣市", "居住分區", "居住地址"}) 指令說明： Splitter.SplitTextByDelimiter( 1️⃣ , 2️⃣ ) 用途：分隔器函式，這個函式會根據分隔符號來分割文字。 1️⃣分割依據的關鍵詞 2️⃣需對文本類型裡"csv"的處理 ``` ![](https://i.imgur.com/abDupmU.jpg) S5：右側，查詢設定中：刪除【已變更類型】 ※非必要性步驟：其目的課程了解PQ界面。 　(1)因課程範例格式不複雜，故刪除全部【已變更類型】，也不會影響步驟　(2)【轉換】>【偵測資料類型】　(3)起聘日期調整格式（說明欄位格式如何確認） <hr> ### 3、新增部門 P.6 S6：新增部門： ![](https://i.imgur.com/iEHCSx3.png) 方法1：部門1：【新增資料行】>【來自範例的資料行】>利用輸入方法找輸入特性 ![](https://i.imgur.com/rQ7Sg9o.png) 方法2：部門：【新增資料行】>【條件資料行】 ![](https://i.imgur.com/YJyop66.png) ``` = Table.AddColumn(col部門1, "部門", each if Text.Contains([職務], "業務") then "業務部" else "行政部" ) ``` S7：步驟更名：目的為未來查看時快速查看【col_部門】 ## 第二階段：計算 P.6 【業績成效呈現原則】 <a href="https://hackmd.io/_uploads/HykTbBDLt.png">![](https://hackmd.io/_uploads/HykTbBDLt.png)</a> ### 1、業績目標_低標 ``` Excel計算業績目標_低標 = Round ( 業績目標 * 0.9 , 0 ) ``` #### 方法1：利用界面提供計算 業績目標_低標1：【新增資料行】>【標準】>【乘】0.9>【轉換】>【進位】>【捨入】0 <a href="https://hackmd.io/_uploads/BJmWZSP8Y.png">![](https://hackmd.io/_uploads/BJmWZSP8Y.png)</a> #### 方法2：自訂公式 業績目標_低標：【新增資料行】>【自訂資料行】> =Number.Round( [業績目標]*0.9,0) ``` = Table.AddColumn(col_業績目標_低標1, "業績目標_低標", each Number.Round([業績目標]*0.9,0)) ``` <a href="https://hackmd.io/_uploads/SJaONsHp5.png">![](https://hackmd.io/_uploads/SJaONsHp5.png)</a> ### 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業績]) ``` <a href="https://hackmd.io/_uploads/r1LIIsS6q.png">![](https://hackmd.io/_uploads/r1LIIsS6q.png)</a> ※ 注意事項： <a href="https://hackmd.io/_uploads/SkbymBwUK.png">![](https://hackmd.io/_uploads/SkbymBwUK.png)</a> [📌List.Sum..等，語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/list-functions#ordering)  ### 3、達成率 【計算目的】:討論分母為0的問題 P.7 <a href="https://hackmd.io/_uploads/B1ryaoHpc.png">![](https://hackmd.io/_uploads/B1ryaoHpc.png)</a> 變更為百分比格式 ``` = Table.AddColumn(已插入加總, "達成率", each if [業績目標]<>0 then [業績總和]/[業績目標] else 0) ```  ### 4、業績成效 P.8 【計算目的】練習多條件語法 【加入業績成效呈現原則】 <a href="https://hackmd.io/_uploads/HykTbBDLt.png">![](https://hackmd.io/_uploads/HykTbBDLt.png)</a> ``` = Table.AddColumn(已變更類型3, "業績成效", each if [達成率] >= 1.2 then "高標" else if [達成率] >= 1 then "達成" else if [業績總和] >= [業績目標_低標] then "低標" else "未達成") ``` ## 第二階段：合併 / 附加查詢 P.8 何謂【合併/附加】查詢 <a href="https://hackmd.io/_uploads/r18NGY_pq.png">![](https://hackmd.io/_uploads/r18NGY_pq.png)</a> S1：【業績情形_原】新增索引資料行：【新增資料行】>【索引資料行】 　　目的：方便日後查看 S2：載入『地區』、『行政部標準表』、『業務部標準表』表格 S3：地區連結： <a href="https://hackmd.io/_uploads/H1vZmBwUF.png">![](https://hackmd.io/_uploads/H1vZmBwUF.png)</a> (1) 【常用】>【合併查詢】>【合併查詢】 <a href="https://hackmd.io/_uploads/![](https://hackmd.io/_uploads/B1lrXSv8F.png).png">![](https://hackmd.io/_uploads/B1lrXSv8F.png)</a> (2) 展開【地區】欄位 ※ 但因有的分區有二筆以上，導致新增重覆列 <a href="https://hackmd.io/_uploads/![](https://hackmd.io/_uploads/B1S8QrvLY.png).png">![](https://hackmd.io/_uploads/B1S8QrvLY.png)</a> (3) 利用【索引】欄，移除重覆列：【常用】>【移除資料列】>【移除重複項目】>進行排序 S4：左側查詢區，將【業績情形_原】利用【參考】新增為『業績情形_明細』※ 說明其差異性，例如：刪除【索引】欄、調整欄位順序…<a href="https://hackmd.io/_uploads/Bk4YPBPIt.png">![](https://hackmd.io/_uploads/Bk4YPBPIt.png)</a> S5：移除未來不必要的欄位 "工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1", "業績總和1", "業績目標_低標1" <a href="https://hackmd.io/_uploads/HkWWcrwUY.png">![](https://hackmd.io/_uploads/HkWWcrwUY.png)</a> ``` = Table.RemoveColumns(來源,{"工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1"}) ``` S6：業績標準處理： P.10 (1) 利用【附加查詢】為新查詢，合併【行政部/業務部標準表】，命名為『部門業績標準表』 ※ 說明：基本原則欄位名稱必須相同<a href="https://hackmd.io/_uploads/r1y07rv8Y.png">![](https://hackmd.io/_uploads/r1y07rv8Y.png)</a> (2) 同S3步驟，『業績情形_明細』及『部門業績標準表』利用【合併查詢】合併兩表，加入『乘數比例』 ※ 說明：兩欄以上比對欄位 <a href="https://hackmd.io/_uploads/B1LyNSPLt.png">![](https://hackmd.io/_uploads/B1LyNSPLt.png)</a> (3) 展開欄位，取得『乘數比例』欄位<a href="https://hackmd.io/_uploads/By4BgB_LY.png">![](https://hackmd.io/_uploads/By4BgB_LY.png)</a> ## 第三階段：分組依據 P.10 【工作目的】合併同類項目：將同一類型的內容放在同一個儲存格中 【顯示結果】 <a href="https://hackmd.io/_uploads/SysQBHdIt.png">![](https://hackmd.io/_uploads/SysQBHdIt.png)</a> S1：將【業績情形_明細】利用【參考】新增為【業績情形_分組依據】 S2：留下{ "員工姓名", "職務", "業績目標", "業績成效", "部門", "業績總和" }，移除不必要的欄位 <a href="https://hackmd.io/_uploads/ryt4NrwUY.png">![](https://hackmd.io/_uploads/ryt4NrwUY.png)</a> ``` = Table.SelectColumns(來源,{"部門","職務", "業績目標", "業績總和","員工姓名","業績成效"}) ``` S3：【常用】>【分組依據】依下圖新增【人數】、【總業績目標】、【總業績總和】、【人員列表】欄位 <a href="https://hackmd.io/_uploads/HyYINSDIF.png">![](https://hackmd.io/_uploads/HyYINSDIF.png)</a> 【結果】 <a href="https://hackmd.io/_uploads/B1GdNBDUY.png">![](https://hackmd.io/_uploads/B1GdNBDUY.png)</a> ``` = 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([員工姓名], "/")}}) ``` <a href="https://hackmd.io/_uploads/HkpdxLDLF.png">![](https://hackmd.io/_uploads/HkpdxLDLF.png)</a> [📌Text.Combine語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/text-combine) 📌課後練習：試著員工姓名+職務 📌例如： <a href="https://hackmd.io/_uploads/SJqjOsdUt.png">![](https://hackmd.io/_uploads/SJqjOsdUt.png)</a> ## 第四階段：查詢表整理(補充) 補充 藉由【新群組】方式，將查詢表進行分類，以利日後查看。 S1. 在左側『查詢區』，按右鍵 > 新增群組 > 輸入資料夾名稱 S2. 將相同查詢表推入其內即可 <a href="https://hackmd.io/_uploads/H1n2vr_UF.png">![](https://hackmd.io/_uploads/H1n2vr_UF.png)</a> ## 第五階段：樞紐 / 取消樞紐資料行 ### 練習1：了解各【分區】及【績效成效】的【業績總和】 P.11 📌【工作目標】<a href="https://hackmd.io/_uploads/SJofSLvUt.png">![](https://hackmd.io/_uploads/SJofSLvUt.png)</a> S1：【分區】及【績效成效】的【業績總和】樞紐情形 S2：將【業績情形_明細】利用【參考】新增為【業績情形_樞紐1】 S3：留下{ "分區", "業績總和", "業績成效" }，移除不必要的欄位 ``` = Table.SelectColumns(來源,{"分區", "業績總和", "業績成效"}) ``` <a href="https://hackmd.io/_uploads/SypXXLvUF.png">![](https://hackmd.io/_uploads/SypXXLvUF.png)</a> S4： 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/r1ROXIv8t.png">![](https://hackmd.io/_uploads/r1ROXIv8t.png)</a>【顯示結果】<a href="https://hackmd.io/_uploads/BywZN8DUY.png">![](https://hackmd.io/_uploads/BywZN8DUY.png)</a> ### 練習2：顯示各【分區】及【績效成效】的【員工姓名】情形 P.12 【顯示結果】 <a href="https://hackmd.io/_uploads/SyJGeLOUt.png">![](https://hackmd.io/_uploads/SyJGeLOUt.png)</a> S1. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】 :::info S2-1. 留下 { "地區", "員工姓名", "業績成效" }，移除不必要的欄位 <a href="https://hackmd.io/_uploads/ryqT9IvLK.png">![](https://hackmd.io/_uploads/ryqT9IvLK.png)</a>S2-2. 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/r1WE7UOUK.png">![](https://hackmd.io/_uploads/r1WE7UOUK.png)</a>🔻筆數多無法顯示於儲存格內<a href="https://hackmd.io/_uploads/SJThNIuLt.png">![](https://hackmd.io/_uploads/SJThNIuLt.png)</a> (4) 查詢表更名為【業績情形_樞紐2_錯誤】 ::: S2. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】 S3. 留下 { "地區", "員工姓名", "業績成效","達成率" }，移除不必要的欄位 ``` = Table.SelectColumns(來源,{"員工姓名", "分區", "業績成效", "達成率"}) ``` <a href="https://hackmd.io/_uploads/H1G3rLvLY.png">![](https://hackmd.io/_uploads/H1G3rLvLY.png)</a>S4. 【新增資料行】>【自訂資料行】 <a href="https://hackmd.io/_uploads/ryFP_8OUt.png">![](https://hackmd.io/_uploads/ryFP_8OUt.png)</a> [📌 Text.From 語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/text-functions#text-comparisons) S5. 先前說明進行樞紐錯誤情形時，因多筆同資料無法全部顯示，故必須先利用【分組依據】合併之 【目的】 <a href="https://hackmd.io/_uploads/SJpysU_IF.png">![](https://hackmd.io/_uploads/SJpysU_IF.png)</a> 【步驟】 <a href="https://hackmd.io/_uploads/ryEJ1vPLY.png">![](https://hackmd.io/_uploads/ryEJ1vPLY.png)</a> ``` = Table.Group(Col_員工達成率, {"分區", "業績成效"}, {{"計數", each List.Sum([員工達成率]), type text}}) ``` (4) 修改語法 ``` = Table.Group(Col_員工達成率, {"分區", "業績成效"}, {{"計數", each Text.Combine( [員工達成率]," / ")}}) ``` <a href="https://hackmd.io/_uploads/r1D0kwDLt.png">![](https://hackmd.io/_uploads/r1D0kwDLt.png)</a> (3) 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ，【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/Sk0piI_IY.png">![](https://hackmd.io/_uploads/Sk0piI_IY.png)</a> 📌 例如 <a href="https://hackmd.io/_uploads/S150ooOLK.png">![](https://hackmd.io/_uploads/S150ooOLK.png)</a> # 二、PQ 範例 ## 1. 找出每個人的最高業績及季別 P.14 S1：將【業績情形_明細】利用【參考】新增為【ex最高業績及季別】 S2：留下{ "員工姓名", "Q1業績", "Q2業績", "Q3業績", "Q4業績" }，移除不必要的欄位 <a href="https://hackmd.io/_uploads/HJgB6UdIF.png">![](https://hackmd.io/_uploads/HJgB6UdIF.png)</a>S3：選取【"Q1業績", "Q2業績", "Q3業績", "Q4業績"】欄位，【取消資料行樞紐】，並利用【取代值】移除『業績』字樣 <a href="https://hackmd.io/_uploads/By1nC8OIt.png">![](https://hackmd.io/_uploads/By1nC8OIt.png)</a> S4：針對【員工姓名】設定為遞增排序 / 【業績值】設定為遞減排序 <a href="https://hackmd.io/_uploads/ByOpJDdUK.png">![](https://hackmd.io/_uploads/ByOpJDdUK.png)</a> S5：選取【員工姓名】，【常用】>【移除資料列】>【重覆項目】刪除重覆項目 目的：利用移除重覆項目，保留第一筆記錄的工作特性 <a href="https://hackmd.io/_uploads/HkYFbvd8t.png">![](https://hackmd.io/_uploads/HkYFbvd8t.png)</a> S6：利用【合併查詢】，加入人員部門、職務，並還原其名單順序 ``` Keyword："索引","部門","職務", ``` <a href="https://hackmd.io/_uploads/Hk1xVDdLt.png">![](https://hackmd.io/_uploads/Hk1xVDdLt.png)</a> ## 2. 中文排名【總排名】 P.15  何謂中式排名？ <a href="https://hackmd.io/_uploads/HJqlRuO6q.png">![](https://hackmd.io/_uploads/HJqlRuO6q.png)</a> S1：在左側『查詢區』，按右鍵 > 新增群組 > 【ex中式排名】 S2：將【業績總和】欄位移除重覆後，新增排名 (1) 將【業績情形_明細】利用【參考】新增為【ex中式排名S1】 (2) 選取【業績總和】> 移除其他欄位 > 進行遞減排序 > 移除重覆值 > 新增【排名】索引欄 <a href="https://hackmd.io/_uploads/S1UHdP_UK.png">![](https://hackmd.io/_uploads/S1UHdP_UK.png)</a> S3：利用合併查詢方式，將【業績情形_明細】，新增排名 (1) 【常用】>【合併查詢】<【將查詢合併為追加查詢】 <a href="https://hackmd.io/_uploads/Hy5ShDu8Y.png">![](https://hackmd.io/_uploads/Hy5ShDu8Y.png)</a> (2) 展開【排名】欄位 <a href="https://hackmd.io/_uploads/r1IN6Du8K.png">![](https://hackmd.io/_uploads/r1IN6Du8K.png)</a> ## 3. 利用合併查詢進行兩表比對 P.16 利用合併查詢進行兩表比對，比Vlookup查詢更加簡單及靈活 ### A. 說明：六種查詢理論 <a href="https://hackmd.io/_uploads/SJsT6d_IK.png">![](https://hackmd.io/_uploads/SJsT6d_IK.png)</a> ### B. 資料比對：找出新/舊/離職員工 #### (1) 工作目標 <a href="https://hackmd.io/_uploads/ryQJjuu8F.png">![](https://hackmd.io/_uploads/ryQJjuu8F.png)</a> #### (2) 說明 <a href="https://hackmd.io/_uploads/SJOgkKuUF.png">![](https://hackmd.io/_uploads/SJOgkKuUF.png)</a> #### (3) 步驟 S1：載入『表7月』、『表8月』表格 S2：【常用】>【合併查詢】>【將查詢合併為新查詢】，並展開其合併欄位 <a href="https://hackmd.io/_uploads/HkCS2O_UF.png">![](https://hackmd.io/_uploads/HkCS2O_UF.png)</a><a href="https://hackmd.io/_uploads/Syr_3_uUK.png">![](https://hackmd.io/_uploads/Syr_3_uUK.png)</a> S3：【新增資料行】>【條件資料行】，進行新舊員工類型判斷 <a href="https://hackmd.io/_uploads/rkQ2kt_Ut.png">![](https://hackmd.io/_uploads/rkQ2kt_Ut.png)</a> S4：最後整理 <a href="https://hackmd.io/_uploads/Byx3lt_IK.png">![](https://hackmd.io/_uploads/Byx3lt_IK.png)</a> ## 4. 資料分割 / 合併 P.18 ### A. 資料分割 S1：【常用】>【分割資料行】 <a href="https://hackmd.io/_uploads/rJGiMYOIK.png">![](https://hackmd.io/_uploads/rJGiMYOIK.png)</a> S12：【常用】>【分割資料行】 <a href="https://hackmd.io/_uploads/ByC4GtO8F.png">![](https://hackmd.io/_uploads/ByC4GtO8F.png)</a> S3：選取【訂單資訊.1】欄位，【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/HyvTzKd8F.png">![](https://hackmd.io/_uploads/HyvTzKd8F.png)</a> ### B. 資料合併 P.19 S1：將(1)利用左側窗格點選【參考】新增查詢後，選取【姓名、電話、產品】，再【轉換】>【取消資料行樞紐】>【合併資料行】合併結果，如下圖<a href="https://hackmd.io/_uploads/Hy7b7tOUF.png">![](https://hackmd.io/_uploads/Hy7b7tOUF.png)</a> S2：選取【訂單編號】欄位，【轉換】>【分組依據】 <a href="https://hackmd.io/_uploads/HJQBXFOIK.png">![](https://hackmd.io/_uploads/HJQBXFOIK.png)</a> S3：修改語法 ``` = Table.Group(已合併資料行, {"訂單編號"}, {{"項目", each Text.Combine([項目],"#(lf)"), type text}}) ``` <a href="https://hackmd.io/_uploads/ry3WVtuLY.png">![](https://hackmd.io/_uploads/ry3WVtuLY.png)</a> ![](https://hackmd.io/_uploads/ry3WVtuLY.png) ## 5. 一維 / 二維資料表轉換 P.20 ### A. 【二維】轉為【一維】 選取各『縣市』欄位 > 【轉換】>【取消資料行樞紐】 <a href="https://hackmd.io/_uploads/BkHS_Y_LK.png">![](https://hackmd.io/_uploads/BkHS_Y_LK.png)</a> ### B. 【一維】轉為【二維】選取各『地區』欄位 > 【轉換】>【資料行樞紐】 <a href="https://hackmd.io/_uploads/BJUItFOIY.png">![](https://hackmd.io/_uploads/BJUItFOIY.png)</a> 📌 例如： <a href="https://hackmd.io/_uploads/H19K3odLt.png">![](https://hackmd.io/_uploads/H19K3odLt.png)</a> ## 6. { List } 應用 P.20 ### A. 廠商及訂單編號列表 #### (1) 說明：【笛卡爾積 , 笛卡兒積】應用 笛卡爾乘積是指在數學中，兩個集合X和Y的笛卡爾積（Cartesian product），又稱直積，表示為X×Y，第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員。簡單來說就是2組資料互相交集.<a href="https://hackmd.io/_uploads/rkfJaFdUt.png">![](https://hackmd.io/_uploads/rkfJaFdUt.png)</a> #### (2) 步驟 S1：利用分割資料切割新增【訂單編號_起】、【訂單編號_迄】欄位 <a href="https://hackmd.io/_uploads/HyKX2FdIK.png">![](https://hackmd.io/_uploads/HyKX2FdIK.png)</a> S2：【新增資料行】>【自訂資料行】 ``` 資料行名稱list = {[#"訂單編號 -起"]..[#"訂單編號 - 迄"]} ``` <a href="https://hackmd.io/_uploads/BJZb0FuLt.png">![](https://hackmd.io/_uploads/BJZb0FuLt.png)</a> ### 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) 練習：中英文拆分 【目的】 <a href="https://hackmd.io/_uploads/r1Rce5dLY.png">![](https://hackmd.io/_uploads/r1Rce5dLY.png)</a> 【步驟】 S1：抓取中文文字語法，新增【自訂資料行】>公式為【 = Text.Select([產品],{″一″..″龜″})】 ``` = Text.Select([商品],{"一".."龜"}) ``` <a href="https://hackmd.io/_uploads/XBTKEMG.png">![](https://hackmd.io/_uploads/XBTKEMG.png)</a> S2：抓取英文，新增【自訂資料行】> 公式為【 = Text.Trim ( Text.Select ( [產品] , {″ a ″…″ z ″,″ A ″…″ Z ″,″ ″,″ . ″,″ ‘ ″}))】 移除前後多餘空格，因範例中英文有【’】及【.】 ``` = Text.Trim ( Text.Select ( [商品] , {"a".."z","A".."Z"," ",".","'"})) ``` <a href="https://hackmd.io/_uploads/HyHIZqdIY.png">![](https://hackmd.io/_uploads/HyHIZqdIY.png)</a> S3：也可以移除中文字，來取得英文 ``` = Text.Remove([商品],{"一".."龜"}) ``` ## 7. 多檔案匯入 P.22 透過Power Query將多個資料來源的資料合併、追加到一起，任意組合資料、將資料進行分組、透視等整理操作方法快速整併成同一個，以利數據分析。 <a href="https://hackmd.io/_uploads/BJDRN5uIY.png">![](https://hackmd.io/_uploads/BJDRN5uIY.png)</a> S1：開啟Excel新檔 > 載入資料 > 活頁簿【2016版Excle】 <a href="https://hackmd.io/_uploads/H1zthc_UY.png">![](https://hackmd.io/_uploads/H1zthc_UY.png)</a> 【365版Excle】 <a href="https://hackmd.io/_uploads/BkRKrq_LK.png">![](https://hackmd.io/_uploads/BkRKrq_LK.png)</a> S2：顯示載入資料情形 > 點選【轉換資料】 <a href="https://hackmd.io/_uploads/S1NH89uIK.png">![](https://hackmd.io/_uploads/S1NH89uIK.png)</a> S3：保留【Content】欄位，其他欄位移除，其內容如下： <a href="https://hackmd.io/_uploads/S1jAwq_UY.png">![](https://hackmd.io/_uploads/S1jAwq_UY.png)</a> S4：【新增資料行】>【自訂資料行】 <a href="https://hackmd.io/_uploads/BkGYi9uLK.png">![](https://hackmd.io/_uploads/BkGYi9uLK.png)</a> :::danger (1) 載入CSV檔案至PQ： 　　 Csv.Document ( ) (2) 本身Excel檔案載入至PQ： 　　 Excel.CurrentWorkbook ( ) { [ Name="表格名稱" ] } [Content] (3) 由其他Excel檔案載入至PQ： 　　 Excel.Workbook ( [Content] , true ) ::: ``` = Excel.Workbook([Content],true) ``` <a href="https://hackmd.io/_uploads/B1au59_Ut.png">![](https://hackmd.io/_uploads/B1au59_Ut.png)</a> S6：依序展開其欄位 ## 8. 巧用Power Query輕鬆管理文件 P.22 ### A. 例如 <a href="https://hackmd.io/_uploads/Bk5QKoOLK.png">![](https://hackmd.io/_uploads/Bk5QKoOLK.png)</a> ### B. 步驟 S1：與上個個單元作法相同載入檔案，開新檔案>【資料】>【取得資料】>【從檔案】>【從資料夾】>選取想要載入的資料夾 >【轉換資料】 S2：依步驟完成，建立公式進行串接組合，並刪除不必要欄位： <a href="https://hackmd.io/_uploads/SkhgC5dLK.png">![](https://hackmd.io/_uploads/SkhgC5dLK.png)</a> S3：完成後，回填至Excel，建立連結公式： <a href="https://hackmd.io/_uploads/HkDIA9_IY.png">![](https://hackmd.io/_uploads/HkDIA9_IY.png)</a> ## 9. Google表單結果連結至Excel方法 S1：開啟Google表單回報檔案，點選【檔案】>【共用】>【發佈至網路】 <a href="https://hackmd.io/_uploads/rkQgmLO69.png">![](https://hackmd.io/_uploads/rkQgmLO69.png)</a> S2：選擇連結的檔案類型 > 複製其網址 <a href="https://hackmd.io/_uploads/B1KqmL_a5.png">![](https://hackmd.io/_uploads/B1KqmL_a5.png)</a> S3：開啟Excel新檔案，【資料】>【從Web】，將S2網址貼入，依步驟載入即可 ``` https://docs.google.com/spreadsheets/d/e/2PACX-1vTWau93NwnhTXACiMUDYp-Y-G-_5ZYF_rluokCaxRKPQeDzWp1uBklWVQpqtOeOUbv6NH7-E_w628TR/pub?output=xlsx ``` <a href="https://hackmd.io/_uploads/HyqFNIdTc.png">![](https://hackmd.io/_uploads/HyqFNIdTc.png)</a> # 三、PQ-M函數 ## list.sum [📌List.Sum..等，語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/list-functions#ordering) 計算說明： |公式|結果| |-|-| |= 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]})) ``` <a href="https://hackmd.io/_uploads/Skw9Zz6_q.png">![](https://hackmd.io/_uploads/Skw9Zz6_q.png)</a> ## 分隔欄位 分隔欄位：Splitter.SplitTextByDelimiter  # Power Pivot(PP) P.26 【範例說明】 <a href="https://hackmd.io/_uploads/ryp_TA-Lt.png">![](https://hackmd.io/_uploads/ryp_TA-Lt.png)</a> ## 1. 啟動【Power Pivot】增益集 [※ 詳前面章節](https://hackmd.io/@zoego/工務局excel#D、指令位置) ## 2. 數據載入至【Power Pivot】 P.26 S1. 【主資料夾】>【從其他來源】> 點選【Excel檔案】 <a href="https://hackmd.io/_uploads/rkzdXa-8t.png">![](https://hackmd.io/_uploads/rkzdXa-8t.png)</a> S2. 點選欲載入數據的Excel檔案 <a href="https://hackmd.io/_uploads/H1nRFAZ8F.png">![](https://hackmd.io/_uploads/H1nRFAZ8F.png)</a> S3. 勾選欲載入數據的工作表來源 <a href="https://hackmd.io/_uploads/HkNdCCWUY.png">![](https://hackmd.io/_uploads/HkNdCCWUY.png)</a> <a href="https://hackmd.io/_uploads/SJ9ekyzLt.png">![](https://hackmd.io/_uploads/SJ9ekyzLt.png)</a> ## 3. 建立數據間的關聯圖 P.28 <a href="https://hackmd.io/_uploads/BkZa7kMLF.png">![](https://hackmd.io/_uploads/BkZa7kMLF.png)</a> ## 4. 建立計算公式及載入關聯欄位數據 ### A. 名詞介紹 補充 🔶 兩軟體之間名詞差異 使用PBI計算必須抛棄Excel儲存格計算的思維模式，在PBI中的資料表是以【資料行】方式儲存。 |Excel|PP、PQ、PBI| |-|-| |工作表|資料表| |欄|資料行| ||量值：即以【資料表】或【資料行】進行計算| 🔶 建議 |新增資料行：| |-| |缺點：使用時浪費記憶體 優點：使用Excel公式習慣的人，傾向使用容易理解 使用時機： 　▷ 將列內容放入篩選器、交叉分析篩選器、行、列 　▷ 需要利用新增的資料行與其他表建立關係時 | |量值：| |缺點： 　▷ 公式書寫較難以理解 　▷ 不可使用於交叉分析篩選器 優點： 　▷ 不浪費記憶體，只有被拖到【報告】中才會執行運算 　▷ 可以重覆使用的計算式 使用時機： 　▷ 需要重覆使用的計算式 　▷ 是否根據上下文動態運算| ### B. 工作表載入關聯欄位之值 P.28 點選【訂單明細】工作表：建立公式 |欄位|公式| |-|-| |單價|單價:=RELATED('商品明細'[銷售單價])| |出貨金額|出貨金額:=[出貨數量] * RELATED ( '商品明細'[銷售單價] )| <a href="https://hackmd.io/_uploads/SJD3I1fUF.png">![](https://hackmd.io/_uploads/SJD3I1fUF.png)</a> ``` 單價:=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 ) )| <a href="https://hackmd.io/_uploads/SyjZgefLK.png">![](https://hackmd.io/_uploads/SyjZgefLK.png)</a> ``` 出貨總金額_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 工具| |-|-| |指令：【插入】>【樞紐分析表】<a href="https://hackmd.io/_uploads/r18k9LGUF.png">![](https://hackmd.io/_uploads/r18k9LGUF.png)</a>|<a href="https://hackmd.io/_uploads/SyJz5LM8Y.png">![](https://hackmd.io/_uploads/SyJz5LM8Y.png)</a>| ### B. 樞紐設定原則 ![](https://hackmd.io/_uploads/S1DOR1UNF.png) ### C. 選項 ![](https://hackmd.io/_uploads/rJicfx8Nt.png) ### D. 欄位排序 ##### (1) 手動 欄位直接拖曳 ##### (2) 自動 指令：【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目 ![](https://hackmd.io/_uploads/SkhtieMNY.png) ### E. 群組類型 文字 數值 日期 #### 【練習說明】 P.30 <a href="https://hackmd.io/_uploads/SkqLEE7LK.png">![](https://hackmd.io/_uploads/SkqLEE7LK.png)</a> <a href="https://hackmd.io/_uploads/S1_CENQLF.png">![](https://hackmd.io/_uploads/S1_CENQLF.png)</a> ## 6. 建立大綱階層 P.31 S1：建立大綱階層，如欄位不在同一工作表則利用Related函數載入 <a href="https://hackmd.io/_uploads/ryypwMX8K.png">![](https://hackmd.io/_uploads/ryypwMX8K.png)</a> ``` 商品類別:=RELATED('商品主類別'[商品類別]) ``` S2：啟動【圖表檢視】 <a href="https://hackmd.io/_uploads/B1NkYfQIF.png">![](https://hackmd.io/_uploads/B1NkYfQIF.png)</a> S3：加入樞紐分析表 <a href="https://hackmd.io/_uploads/H1_h9MQ8Y.png">![](https://hackmd.io/_uploads/H1_h9MQ8Y.png)</a> ## 7. 建立【日期表】 P.32 【顯示結果】<a href="https://hackmd.io/_uploads/BkzLK7X8t.png">![](https://hackmd.io/_uploads/BkzLK7X8t.png)</a> S1：新增【日期表】 <a href="https://hackmd.io/_uploads/B1da5XmLK.png">![](https://hackmd.io/_uploads/B1da5XmLK.png)</a> ``` 季:=INT(([月]+2)/3) ``` S2：日期表與訂單明細，建立關聯 <a href="https://hackmd.io/_uploads/HJxNiQ7Ut.png">![](https://hackmd.io/_uploads/HJxNiQ7Ut.png)</a> ## 8.【群組】建立及說明【值的顯示方式】 P.33 <a href="https://hackmd.io/_uploads/SkjGx4XLt.png">![](https://hackmd.io/_uploads/SkjGx4XLt.png)</a> ## 9. 結合【交叉分析篩選器】 S1：點選【插入交叉分析篩選器】及【插入時間表】 <a href="https://hackmd.io/_uploads/H15RxEQUt.png">![](https://hackmd.io/_uploads/H15RxEQUt.png)</a> S2：針對樞紐分析表進行命名 <a href="https://hackmd.io/_uploads/Sk2nWVQIF.png">![](https://hackmd.io/_uploads/Sk2nWVQIF.png)</a> S3：以上兩步驟，點選【篩選連線】進行篩選器與樞紐分析表之結合確認 <a href="https://hackmd.io/_uploads/H1wCMEmIY.png">![](https://hackmd.io/_uploads/H1wCMEmIY.png)</a>