###### tags: `Excel` `PP` `PQ` # Excel進階 - 數據整理 及 分析 <style> /* 加入Logo */ .toc:{position: relative;} .toc:before{ content: url(https://zoego.tech/img/logo.svg); width:80%; height:80% position:fixed; top:0 ; left:0; } body{} .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{ 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;} .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; } .urlr{ text-align:right; line-height:1.5em;display:block;} .urll{ line-height:1.5em;display:block;} .step{ padding-left:1.5em;color:blue;font-size:1.1em; } .flex{ display:flex; border:2px solid #000; padding:20px; margin:20px; } .ex{color:#333399 ; font-size:1.2em; font-weight:bold; line-height:1em;} 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;} .red{color:#BF1363;font-weight:bold;} .blue{color:#265F92;font-weight:bold;} .markdown-body table th, .markdown-body table td { border: 1px solid #7F7F7F; } .alert-warning { border-width: 2px; width: 60%; margin: auto;} .textc{text-align:center; display:block;} .markdown-body p{ margin-bottom: 0;} .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; } .title{ text-align:center; border:3px solid #000; display:block; border-radius:5px; padding:5px} </style> :::warning <span class="textc" style="font-weight:bold; font-size:1.2em">課程講師資訊</span>巨匠電腦講師:呂心怡 E-mail:zoego99@gmail.com ::: ## <span class="h2">A、適用對象</span> <span style="text-indent:2em;display:block;">適用所有Excel使用者,尤其是需要經常對原始資料進行手工合併、追加、去除重複數據等操作的資料分析,或者工作中經常運用Excel的樞紐分析工具進行數據分析人員。</span> ## <span class="h2">B、課程概述</span> ### 1、 Power Query(PQ) <span style="text-indent:2em;display:block;">在工作中經常使用Excel手工合併多張表格;經常需要對來源資料進行刪除、分列、去除重複數據等操作,然而做過資料分析相關工作的你應該深有體會 – 我們需要製作一份數據分析報告的時候,80%的時間要用來處理不規範的來源資料。</span> <span style="text-indent:2em;display:block;">想要擺脫重覆、手工、枯燥的整理工作的苦惱嗎?進而大幅度提升工作效率,節省寶貴時間嗎?Excel提供內建好用的Power Query插件,提供親和的介面化操作,讓很多重複工作進行簡化。</span> <span style="text-indent:2em;display:block;">很可惜多數人使用Excel卻都忽略Power Query插件,而學習這項工具已經是未來趨勢,藉由本次課程將列舉實務性範例,解決許多惱人且複雜的報表整理,進而提高工作效率。</span> :::success (A) 何謂Excel表格及其使用優點介紹 (B) 將多個Excel、CSV檔案或網頁,不同來源類型數據的輕鬆獲取 (&#067;) 分割、合併與提取儲存格資料 (D) 一維 / 二維 資料表轉換 (E) 利用合併查詢進行兩表比對,比Vlookup查詢更加簡單及靈活 (F) 產品明細快速拆分【中/英】文 <!-- (G) 創建動態參數查詢,讓條件篩選更加機動性 --> ::: ### 2、 Power Pivot (PP) <span style="text-indent:2em;display:block;">只要進行數據分析工作者,都會使用【樞紐分析表(Pivot Table)】,也都看過/聽過【資料管理模型(PowerPivot)】卻混淆不知道如何應用,其實這兩項套件是相輔相成的工具,差別在PowerPivot可匯整多張關係工作表,更加結構化進行數據分析。</span> <span style="text-indent:2em;display:block;">在工作中愈來愈多數人都運用樞紐工具進行數據分析,基本樞紐操作已經難不倒,本次課程將藉由【Power Pivot】增益集解決Vlookup連結多報表困擾,並提出執行樞紐分析可能遇到的困擾,及製作樞紐時經常忽略的功能,讓樞紐分析運用更加靈活,得心應手。</span> :::success (A) 樞紐分析前置作業 &#12288;&#12288;(1) 解決日期欄位無法產生年/季/月報困擾 &#12288;&#12288;(2) 利用【Power Pivot】增益集解決Vlookup連結多報表困擾 (B) 樞紐分析進階應用—經常忽略技巧分享 &#12288;&#12288;(1) 如何有效率美化樞紐分析表 &#12288;&#12288;(2) 數據顯示依值顯示方式,從而更清晰的看出數據之間的關係 &#12288;&#12288;(3) 藉助樞紐分析表中的計算欄位功能產生欄位 &#12288;&#12288;(4) 利用交叉分析篩選器動態數據分析 &#12288;&#12288;(5) 利用群組特性,彙整年報、季報、月報 ::: ## <span class="h2">C、未來展望</span> <iframe width="1024" height="612" src="https://app.powerbi.com/view?r=eyJrIjoiOGIzMTg3ZDktMjg5My00OTVlLWI3NDAtNWQ3NzFhMWVkN2I0IiwidCI6ImNkNWRmNDkxLTZhMTYtNDkzZS1iNTVhLTkzOGYzYWE5YWRlYiIsImMiOjEwfQ%3D%3D" frameborder="0" allowFullScreen="true"></iframe> 在工作任務中,經常需要利用Excel樞紐分析功但不了解Power BI使用者,Power BI Desktop是安裝在本機電腦的免費商業分析工具,可從多種檔案類型匯入資料後,快速建立互動圖表的工具,輕鬆完成大數據的分析! <br><br> ### 1、 各項軟體間的分責運用 整體性概觀了解PowerBI各項軟體間的分責運用。 <a href="https://hackmd.io/_uploads/ryLSpv2rY.png"><span class="img100">![](https://hackmd.io/_uploads/ryLSpv2rY.png)</span></a> ### 2、 參考網站 #### <span style="color:#5E00BC; display:block;font-weight:blod;line-height:2em;">(1) Power BI官網:</span> &#12288;&#12288;了解PowerBI及軟體下載: &#12288;&#12288;&#12288;👆 https://powerbi.microsoft.com/zh-tw/ &#12288;&#12288;&#12288;👆 https://powerbi.microsoft.com/zh-tw/desktop/ &#12288;&#12288;PowerBI 文件:👆 https://docs.microsoft.com/zh-tw/power-bi/ #### <span style="color:#5E00BC; display:block;font-weight:blod;line-height:2em;">(2) Power Query M 公式語言:</span> &#12288;&#12288;👆 https://docs.microsoft.com/zh-tw/powerquery-m/ <span style="display:block; padding-left:2em;">M語言是Power Query的後台函數式編程語言,在Power Query界面的操作過程都會記錄下來並翻譯成M語言,將查詢和轉換的結果返回到Excel表或Excel或Power BI數據模型。</span> <span style="display:block; padding-left:2em;">Power Query上手十分容易,通過圖形界面操作可以解決大部分需求,一般情況下,我們不用去深入學習M語言,但是,如果對M語言有了一定的理解可以對操作步驟進行優化,讓其更加智能,同時也能解決一些圖形界面操作不能完成的任務。</span> #### <span style="color:#5E00BC; display:block;font-weight:blod;line-height:2em;">(3) Power BI Desktop使用DAX函數:</span> &#12288;&#12288;👆 https://docs.microsoft.com/zh-tw/dax/ <span style="display:block; padding-left:2em;">DAX是Power Pivot和Power BI Desktop中使用的公式語言。 DAX使用函數處理存儲在表中的數據。一些DAX函數與Excel工作表函數相同,但DAX具有更多功能來匯總,切片和切割複雜的數據場景。</span> ## <span class="h2">D、指令位置</span> ### 1、 PP安裝啟動 <a href="https://hackmd.io/_uploads/BkrkuU2SF.png"><span class="img100">![](https://hackmd.io/_uploads/BkrkuU2SF.png)</span></a> ### 2、 指令位置 <a href="https://hackmd.io/_uploads/ByLspUhrY.png"><span class="img100">![](https://hackmd.io/_uploads/ByLspUhrY.png)</span></a> ## <span class="h2">E、使用Excel表格重要性</span> ### 1、 Excel表格介紹 <span class="step">可以快速地建立、格式化及展開 Excel 表格,組織工作表上的資料,讓其更易於使用。</span> #### <span class="h4">(A) 指令介紹</span> <span class="step">📌 建立</span> <span class="img70">![](https://hackmd.io/_uploads/BJko6I7NK.png)</span> <span class="step">📌 表格化指令</span> <a href="https://hackmd.io/_uploads/Synd8KY4Y.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/Synd8KY4Y.png)</span></a> #### <span class="h4">(B) 表格化選取方法</span> <span class="step">🔶 左鍵一下選取內容、連二下選取內容及標題</span> <span class="img70">![](https://hackmd.io/_uploads/HkW-xKY4t.png)</span> #### <span class="h4">(&#067;) 表格化後,公式表示結構</span> <span class="red" style="font-size:1.5em; text-align:center;display:block; border:1px solid; border-radius:5px; width:60%; margin:auto; background:#FEF7DB;line-height:2.1em">=表格名稱〔@欄位名稱〕</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 有@:相對性欄位</span> <span style="color:blue;font-size:1.2em;padding-left:2em;line-height:2em"><i class="fa fa-hand-o-right"></i> 沒有@:整欄計算</span> ### 2、 使用優點 #### <span class="h4">(A) 容易選取資料範圍且數據自動縮放</span> #### <span class="h4">(B) 公式容易辨識</span> #### <span class="h4">(&#067;) 交叉分析篩選器</span> <span class="step">交叉分析篩選器為一視覺化控制項,以互動且直覺的方式,快速篩選僅顯示所需的資料的資料。</span> <a href="https://hackmd.io/_uploads/ryV6FFKVt.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/ryV6FFKVt.png) </span></a> ### 3、 移除表格注意事項 #### <span class="h4">(1) 移除表格化前,<span class="red">先確認是否要移除色彩</span></span> <span class="step" style="padding-left:2em"><i class="fa fa-hand-o-right"></i> 為了取得帶狀列的色彩,直接移除表格化</span> <span class="step" style="padding-left:2em"><i class="fa fa-hand-o-right"></i> 因反悔表格化功能,而<span class="red">表格會動用排序時</span>,<span class="red">必須</span>要移除色彩</span> #### <span class="h4">(2) 移除方法</span> <span class="img40">![](https://hackmd.io/_uploads/ryMkItYEt.png)</span> # PowerQuery(PQ) # 一、 PQ界面及基本功能介紹 ## <span class="h2">第一階段:基本資料整理</span> ### 1、 資料整理 <span class="red">Note:何謂資料行/列</span> 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:右側,查詢設定中:刪除【已變更類型】 <span style="color:#b71c1c; ">※非必要性步驟:其目的課程了解PQ界面。</span>  (1)因課程範例格式不複雜,故刪除全部【已變更類型】 ,也不會影響步驟  (2)【轉換】>【偵測資料類型】  (3)起聘日期調整格式(說明欄位格式如何確認) <hr> ### 3、 新增部門 S6:新增部門: <span style="display:block; text-align: center">![](https://i.imgur.com/iEHCSx3.png)</span> 方法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:步驟更名:目的為未來查看時快速查看 ## <span class="h2">第二階段:計算</span> ### 1、 業績目標_低標 ``` Excel計算 業績目標_低標 = Round ( 業績目標 * 0.9 , 0 ) ``` #### <span class="h4" style="text-decoration: underline;">方法1:利用界面提供計算</span> 業績目標_低標1:【新增資料行】>【標準】>【乘】0.9>【轉換】>【進位】>【捨入】0 <a href="https://hackmd.io/_uploads/BJmWZSP8Y.png"><span class="img100">![](https://hackmd.io/_uploads/BJmWZSP8Y.png)</span></a> #### <span class="h4" style="text-decoration: underline;">方法2:自訂公式</span> 業績目標_低標:【新增資料行】>【自訂資料行】> =Number.Round( [業績目標]*0.9,0) ``` = Table.AddColumn(捨去, "業績目標_低標", each Number.Round([業績目標]*0.9,0)) ``` <a href="https://hackmd.io/_uploads/5Wg47si.png"><span class="img100">![](https://hackmd.io/_uploads/5Wg47si.png)</span></a> ### 2、 業績總和 #### <span class="h4" style="text-decoration: underline;">方法1:利用界面提供計算</span> 業績總和:選取『Q1業績』 、『Q2業績』、『Q3業績』、『Q4業績』>【新增資料行】>【標準】>【加】 ``` = Table.AddColumn(已新增自訂, "業績總和", each List.Sum({[Q1業績], [Q2業績], [Q3業績], [Q4業績]}), Int64.Type) ``` <a href="https://hackmd.io/_uploads/3YLbum8.png"><span class="img100">![](https://hackmd.io/_uploads/3YLbum8.png)</span></a> #### <span class="h4" style="text-decoration: underline;">方法2:自訂公式</span> 業績總和1:業績目標_低標:【新增資料行】>【自訂資料行】> ``` = Table.AddColumn(已插入加總, "業績總和1", each [Q1業績]+[Q2業績]+[Q3業績]+[Q4業績]) ``` <a href="https://hackmd.io/_uploads/YHGL5Gl.png"><span class="img100">![](https://hackmd.io/_uploads/YHGL5Gl.png)</span></a> ※ 注意事項: <a href="https://hackmd.io/_uploads/SkbymBwUK.png"><span class="img100">![](https://hackmd.io/_uploads/SkbymBwUK.png)</span></a> <span class="urll">[📌List.Sum..等,語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/list-functions#ordering) </span> <!-- :::danger <span style="color:#FFF; background:#C00000; text-align:center;display:block; line-height:2em">**123456新增講義缺漏部分**</span> ※ 討論 0 與 Null 差異 【連結】、【連結】【+100】產生問題再解決 ※ try otherwise ※ 了解加總函數 ::: --> ### 3、 達成率 <span style="color:#b71c1c; display:block;">【計算目的】:討論分母為0的問題</span> <span class="red">變更為百分比格式</span> ``` = Table.AddColumn(已插入加總, "達成率", each if [業績目標]<>0 then [業績總和]/[業績目標] else 0) ``` <!-- ::: warning <span style="color:#FFF; background:#C00000; text-align:center;display:block; line-height:2em">**123456加入IF 語法**</span> ::: --> ### 4、 業績成效 <span style="color:#b71c1c; display:block;">【計算目的】練習多條件語法</span> <span class="red">【加入業績成效呈現原則】</span> <a href="https://hackmd.io/_uploads/HykTbBDLt.png"><span class="img100">![](https://hackmd.io/_uploads/HykTbBDLt.png)</span></a> ``` = Table.AddColumn(已變更類型3, "業績成效", each if [達成率] >= 1.2 then "高標" else if [達成率] >= 1 then "達成" else if [業績總和] >= [業績目標_低標] then "低標" else "未達成") ``` ## <span class="h2">第二階段:合併 / 附加查詢</span> S1:<span class="red">新增索引資料行:【新增資料行】>【索引資料行】</span>   <span class="red">目的:方便日後查看</span> S2:載入『地區』、『行政部標準表』、『業務部標準表』表格 S3:地區連結: <a href="https://hackmd.io/_uploads/H1vZmBwUF.png"><span class="img100">![](https://hackmd.io/_uploads/H1vZmBwUF.png)</span></a> (1) 【常用】>【合併查詢】>【合併查詢】 <a href="https://hackmd.io/_uploads/![](https://hackmd.io/_uploads/B1lrXSv8F.png).png"><span class="img100">![](https://hackmd.io/_uploads/B1lrXSv8F.png)</span></a> (2) 展開【地區】欄位 ※ 但因有的分區有二筆以上,導致新增重覆列 <a href="https://hackmd.io/_uploads/![](https://hackmd.io/_uploads/B1S8QrvLY.png).png"><span class="img100">![](https://hackmd.io/_uploads/B1S8QrvLY.png)</span></a> (3) 利用【索引】欄,移除重覆列: 【常用】>【移除資料列】>【移除重複項目】>進行排序 <br> S4:左側查詢區,將【業績情形_原】利用<span class="red">【參考】</span>新增為『業績情形_明細』<span class="red">※ 說明其差異性,例如:刪除【索引】欄、調整欄位順序…</span><a href="https://hackmd.io/_uploads/Bk4YPBPIt.png"><span class="img40">![](https://hackmd.io/_uploads/Bk4YPBPIt.png)</span></a> S5:移除未來不必要的欄位 "工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1" <a href="https://hackmd.io/_uploads/HkWWcrwUY.png"><span class="img100">![](https://hackmd.io/_uploads/HkWWcrwUY.png)</span></a> ``` = Table.RemoveColumns(來源,{"工作地點", "起聘日期", "全地址", "部門1", "居住縣市", "居住分區", "居住地址", "業績目標_低標1"}) ``` S5:業績標準處理: (1) <span class="red">利用【附加查詢】為新查詢</span>,合併【行政部/業務部標準表】,命名為『部門業績標準表』 <span class="red">※ 說明:基本原則欄位名稱必須相同</span><a href="https://hackmd.io/_uploads/r1y07rv8Y.png"><span class="img80">![](https://hackmd.io/_uploads/r1y07rv8Y.png)</span></a> (2) 同S3步驟,『業績情形_明細』及『部門業績標準表』利用【合併查詢】合併兩表,加入『乘數比例』 <span class="red">※ 說明:兩欄以上比對欄位</span> <a href="https://hackmd.io/_uploads/B1LyNSPLt.png"><span class="img60">![](https://hackmd.io/_uploads/B1LyNSPLt.png)</span></a> (3) 展開欄位,取得『乘數比例』欄位<a href="https://hackmd.io/_uploads/By4BgB_LY.png"><span class="img100">![](https://hackmd.io/_uploads/By4BgB_LY.png)</span></a> ## <span class="h2">第三階段:分組依據</span> 【工作目的】合併同類項目:<span class="red">將同一類型的內容放在同一個儲存格中</span> 【顯示結果】 <a href="https://hackmd.io/_uploads/SysQBHdIt.png"><span class="img100">![](https://hackmd.io/_uploads/SysQBHdIt.png)</span></a> S1:將【業績情形_明細】<span class="red">利用【參考】新增</span>為【業績情形_分組依據】 S2:留下{ "部門","職務", "業績目標", "業績總和","員工姓名","業績成效" },移除不必要的欄位 <a href="https://hackmd.io/_uploads/ryt4NrwUY.png"><span class="img100">![](https://hackmd.io/_uploads/ryt4NrwUY.png)</span></a> ``` = Table.SelectColumns(來源,{"部門","職務", "業績目標", "業績總和","員工姓名","業績成效"}) ``` S3:【常用】>【分組依據】 <a href="https://hackmd.io/_uploads/HyYINSDIF.png"><span class="img100">![](https://hackmd.io/_uploads/HyYINSDIF.png)</span></a> 【結果】 <a href="https://hackmd.io/_uploads/B1GdNBDUY.png"><span class="img100">![](https://hackmd.io/_uploads/B1GdNBDUY.png)</span></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"><span class="img100">![](https://hackmd.io/_uploads/HkpdxLDLF.png)</span></a> <span class="urll">[📌Text.Combine語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/text-combine) </span> 📌課後練習:試著員工姓名+職務 📌例如: <a href="https://hackmd.io/_uploads/SJqjOsdUt.png"><span class="img100">![](https://hackmd.io/_uploads/SJqjOsdUt.png)</span></a> ## <span class="h2">第四階段:查詢表整理(補充)</span> 藉由【新群組】方式,將查詢表進行分類,以利日後查看。 S1. 在左側『查詢區』,按右鍵 > 新增群組 > 輸入資料夾名稱 S2. 將相同查詢表推入其內即可 <a href="https://hackmd.io/_uploads/H1n2vr_UF.png"><span class="img30">![](https://hackmd.io/_uploads/H1n2vr_UF.png)</span></a> ## <span class="h2">第五階段:樞紐 / 取消樞紐資料行</span> ### 練習1:了解各【分區】及【績效成效】的【業績總和】 <span class="red">📌【工作目標】</span><a href="https://hackmd.io/_uploads/SJofSLvUt.png"><span class="img100">![](https://hackmd.io/_uploads/SJofSLvUt.png)</span></a> S1:【分區】及【績效成效】的【業績總和】樞紐情形 S2: 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐1】 S3:留下{ "分區", "業績總和", "業績成效" },移除不必要的欄位 ``` = Table.SelectColumns(來源,{"分區", "業績總和", "業績成效"}) ``` <a href="https://hackmd.io/_uploads/SypXXLvUF.png"><span class="img80">![](https://hackmd.io/_uploads/SypXXLvUF.png)</span></a> S4: <span class="red">選取【業績成效】欄 ( 選取欲排列為『欄/行』者 )</span> ,【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/r1ROXIv8t.png"><span class="img90">![](https://hackmd.io/_uploads/r1ROXIv8t.png)</span></a>【顯示結果】<a href="https://hackmd.io/_uploads/BywZN8DUY.png"><span class="img100">![](https://hackmd.io/_uploads/BywZN8DUY.png)</span></a> ### 練習2:顯示各【分區】及【績效成效】的【員工姓名】情形 【顯示結果】 <a href="https://hackmd.io/_uploads/SyJGeLOUt.png"><span class="img100">![](https://hackmd.io/_uploads/SyJGeLOUt.png)</span></a> S1. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】 <br><br> :::info S2-1. 留下 { "地區", "員工姓名", "業績成效" },移除不必要的欄位 <a href="https://hackmd.io/_uploads/ryqT9IvLK.png"><span class="img70">![](https://hackmd.io/_uploads/ryqT9IvLK.png)</span></a>S2-2. 選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) ,【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/r1WE7UOUK.png"><span class="img40" >![](https://hackmd.io/_uploads/r1WE7UOUK.png)</span></a><span class="textc">🔻筆數多無法顯示於儲存格內</span><a href="https://hackmd.io/_uploads/SJThNIuLt.png"><span class="img60">![](https://hackmd.io/_uploads/SJThNIuLt.png)</span></a> (4) 查詢表更名為【業績情形_樞紐2_錯誤】 ::: S2. 將【業績情形_明細】利用【參考】新增為【業績情形_樞紐2】 S3. 留下 { "地區", "員工姓名", "業績成效","達成率" },移除不必要的欄位 <a href="https://hackmd.io/_uploads/H1G3rLvLY.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/H1G3rLvLY.png)</span></a>S4. 【新增資料行】>【自訂資料行】 <a href="https://hackmd.io/_uploads/ryFP_8OUt.png"><span class="img100">![](https://hackmd.io/_uploads/ryFP_8OUt.png)</span></a> <span class="urll">[📌 Text.From 語法參考](https://docs.microsoft.com/zh-tw/powerquery-m/text-functions#text-comparisons) </span> <span class="red">S5. 先前說明進行樞紐錯誤情形時,因多筆同資料無法全部顯示,故必須先利用【分組依據】合併之</span> 【目的】 <a href="https://hackmd.io/_uploads/SJpysU_IF.png"><span class="img80">![](https://hackmd.io/_uploads/SJpysU_IF.png)</span></a> 【步驟】 <a href="https://hackmd.io/_uploads/ryEJ1vPLY.png"><span class="img100">![](https://hackmd.io/_uploads/ryEJ1vPLY.png)</span></a> ``` = Table.Group(Col_員工達成率, {"分區", "業績成效"}, {{"計數", each List.Sum([員工達成率]), type text}}) ``` (4) 修改語法 ``` = Table.Group(Col_員工達成率, {"分區", "業績成效"}, {{"計數", each Text.Combine( [員工達成率],"/")}}) ``` <a href="https://hackmd.io/_uploads/r1D0kwDLt.png"><span class="img100">![](https://hackmd.io/_uploads/r1D0kwDLt.png)</span></a> (3) <span class="red">選取【業績成效】欄 ( 選取欲排列為『欄/行』者 ) </span>,【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/Sk0piI_IY.png"><span class="img100">![](https://hackmd.io/_uploads/Sk0piI_IY.png)</span></a> <span class="blue">📌 例如</span> <a href="https://hackmd.io/_uploads/S150ooOLK.png"><span class="img100">![](https://hackmd.io/_uploads/S150ooOLK.png)</span></a> <br><br><br><br><br><br><br><br> # 二、PQ 範例 ## <span class="h2">A、 找出每個人的最高業績及季別</span> S1:將【業績情形_明細】利用【參考】新增為【ex最高業績及季別】 S2:留下{ "員工姓名", "Q1業績", "Q2業績", "Q3業績", "Q4業績" },移除不必要的欄位 <a href="https://hackmd.io/_uploads/HJgB6UdIF.png"><span class="img100">![](https://hackmd.io/_uploads/HJgB6UdIF.png)</span></a>S3:選取【員工姓名】欄位,【取消資料行樞紐】,並利用【取代值】移除『業績』字樣 <a href="https://hackmd.io/_uploads/By1nC8OIt.png"><span class="img100">![](https://hackmd.io/_uploads/By1nC8OIt.png)</span></a> S4:針對【員工姓名】設定為遞增排序 / 【業績值】設定為遞減排序 <a href="https://hackmd.io/_uploads/ByOpJDdUK.png"><span class="img100">![](https://hackmd.io/_uploads/ByOpJDdUK.png)</span></a> S5:選取【員工姓名】,【常用】>【移除資料列】>【重覆項目】刪除重覆項目 <span class="red">目的:利用移除重覆項目,保留第一筆記錄的工作特性</span> <a href="https://hackmd.io/_uploads/HkYFbvd8t.png"><span class="img100">![](https://hackmd.io/_uploads/HkYFbvd8t.png)</span></a> S6:自行想辦法,加入人員部門、職務,並還原其名單順序 ``` Keyword:"索引","部門","職務", ``` <a href="https://hackmd.io/_uploads/Hk1xVDdLt.png"><span class="img100">![](https://hackmd.io/_uploads/Hk1xVDdLt.png)</span></a> ## <span class="h2">B、 中文排名【總排名】</span> <!-- ### 1、 中文排名【總排名】 --> S1:在左側『查詢區』,按右鍵 > 新增群組 > 【ex中式排名】 S2:將【業績總和】欄位移除重覆後,新增排名 (1) 將【業績情形_明細】利用【參考】新增為【ex中式排名S1】 (2) 選取【業績總和】> 移除其他欄位 > 進行遞減排序 > 移除重覆值 > 新增【排名】索引欄 <a href="https://hackmd.io/_uploads/S1UHdP_UK.png"><span class="img100">![](https://hackmd.io/_uploads/S1UHdP_UK.png)</span></a> S3:利用合併查詢方式,將【業績情形_明細】,新增排名 (1) 【常用】>【合併查詢】<【將查詢合併為追加查詢】 <a href="https://hackmd.io/_uploads/Hy5ShDu8Y.png"><span class="img70">![](https://hackmd.io/_uploads/Hy5ShDu8Y.png)</span></a> (2) 展開【排名】欄位 <a href="https://hackmd.io/_uploads/r1IN6Du8K.png"><span class="img100">![](https://hackmd.io/_uploads/r1IN6Du8K.png)</span></a> ## <span class="h2">C、 利用合併查詢進行兩表比對</span> 利用合併查詢進行兩表比對,比Vlookup查詢更加簡單及靈活 ### 1、 說明:六種查詢理論 <a href="https://hackmd.io/_uploads/SJsT6d_IK.png"><span class="img100">![](https://hackmd.io/_uploads/SJsT6d_IK.png)</span></a> ### 2、 資料比對:找出新/舊/離職員工 #### <span class="h4">(1) 工作目標</span> <a href="https://hackmd.io/_uploads/ryQJjuu8F.png"><span class="img70">![](https://hackmd.io/_uploads/ryQJjuu8F.png)</span></a> #### <span class="h4">(2) 說明</span> <a href="https://hackmd.io/_uploads/SJOgkKuUF.png"><span class="img100">![](https://hackmd.io/_uploads/SJOgkKuUF.png)</span></a> #### <span class="h4">(3) 步驟</span> S1:載入『表7月』、『表8月』表格 S2:【常用】>【合併查詢】>【將查詢合併為新查詢】,並展開其合併欄位 <a href="https://hackmd.io/_uploads/HkCS2O_UF.png"><span class="img80">![](https://hackmd.io/_uploads/HkCS2O_UF.png)</span></a><a href="https://hackmd.io/_uploads/Syr_3_uUK.png"><span class="img80">![](https://hackmd.io/_uploads/Syr_3_uUK.png)</span></a> S3:【新增資料行】>【條件資料行】,進行新舊員工類型判斷 <a href="https://hackmd.io/_uploads/rkQ2kt_Ut.png"><span class="img80">![](https://hackmd.io/_uploads/rkQ2kt_Ut.png)</span></a> S4:最後整理 <a href="https://hackmd.io/_uploads/Byx3lt_IK.png"><span class="img100">![](https://hackmd.io/_uploads/Byx3lt_IK.png)</span></a> ## <span class="h2">D、 資料分割 / 合併</span> ### 1、 資料分割 S1:【常用】>【分割資料行】 <a href="https://hackmd.io/_uploads/rJGiMYOIK.png"><span class="img100">![](https://hackmd.io/_uploads/rJGiMYOIK.png)</span></a> S12:【常用】>【分割資料行】 <a href="https://hackmd.io/_uploads/ByC4GtO8F.png"><span class="img80">![](https://hackmd.io/_uploads/ByC4GtO8F.png)</span></a> S3:選取【訂單資訊.1】欄位,【轉換】>【樞紐資料行】 <a href="https://hackmd.io/_uploads/HyvTzKd8F.png"><span class="img100">![](https://hackmd.io/_uploads/HyvTzKd8F.png)</span></a> ### 1、 資料合併 S1:將(1)【參考】後,略步驟,再【合併資料行】>【取消資料行樞組】,如下圖<a href="https://hackmd.io/_uploads/Hy7b7tOUF.png"><span class="img50">![](https://hackmd.io/_uploads/Hy7b7tOUF.png)</span></a> S2:選取【訂單編號】欄位,【轉換】>【分組依據】 <a href="https://hackmd.io/_uploads/HJQBXFOIK.png"><span class="img80">![](https://hackmd.io/_uploads/HJQBXFOIK.png)</span></a> S3:修改語法 ``` = Table.Group(已合併資料行, {"訂單編號"}, {{"項目", each Text.Combine([項目],"#(lf)"), type text}}) ``` <a href="https://hackmd.io/_uploads/ry3WVtuLY.png"><span class="img100">![](https://hackmd.io/_uploads/ry3WVtuLY.png)</span></a> ![](https://hackmd.io/_uploads/ry3WVtuLY.png) ## <span class="h2">E、 一維 / 二維 資料表轉換</span> ### 1、 【二維】轉為【一維】 選取各『縣市』欄位 > 【轉換】>【取消資料行樞紐】 <a href="https://hackmd.io/_uploads/BkHS_Y_LK.png"><span class="img100">![](https://hackmd.io/_uploads/BkHS_Y_LK.png)</span></a> ### 2、 【一維】轉為【二維】 選取各『地區』欄位 > 【轉換】>【資料行樞紐】 <a href="https://hackmd.io/_uploads/BJUItFOIY.png"><span class="img100">![](https://hackmd.io/_uploads/BJUItFOIY.png)</span></a> <span class="blue">📌 例如:</span> <a href="https://hackmd.io/_uploads/H19K3odLt.png"><span class="img100">![](https://hackmd.io/_uploads/H19K3odLt.png)</span></a> ## <span class="h2">F、 { List } 應用</span> ### 1、 廠商及訂單編號列表 #### <span class="h4">(1) 說明:【笛卡爾積 , 笛卡兒積】應用</span> 笛卡爾乘積是指在數學中,兩個集合X和Y的笛卡爾積(Cartesian product),又稱直積,表示為X×Y,第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員。簡單來說就是2組資料互相交集.<a href="https://hackmd.io/_uploads/rkfJaFdUt.png"><span class="img70">![](https://hackmd.io/_uploads/rkfJaFdUt.png)</span></a> #### <span class="h4">(2) 步驟</span> S1:利用分割資料切割新增【訂單編號_起】、【訂單編號_迄】欄位 <a href="https://hackmd.io/_uploads/HyKX2FdIK.png"><span class="img100">![](https://hackmd.io/_uploads/HyKX2FdIK.png)</span></a> S2:【新增資料行】>【自訂資料行】 ``` 資料行名稱list = {[#"訂單編號 -起"]..[#"訂單編號 - 迄"]} ``` <a href="https://hackmd.io/_uploads/BJZb0FuLt.png"><span class="img100">![](https://hackmd.io/_uploads/BJZb0FuLt.png)</span></a> ### 2、 文本 移除 / 提取 #### <span class="h4">(1) 說明:文本 移除 / 提取函式</span> <span class="red">📌 Text.Select ( ) 和Text.Remove ( )</span> <br> |提取項目| M函數| |-|-| |各種特殊符號、0-9數值、<br>所有大小寫英文字母|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 ( [文本資料行] , { ″一″ .. ″龜″})| #### <span class="h4">(2) 練習:中英文拆分</span> <span class="red">【目的】</span> <a href="https://hackmd.io/_uploads/r1Rce5dLY.png"><span class="img60">![](https://hackmd.io/_uploads/r1Rce5dLY.png)</span></a> <span class="red">【步驟】</span> S1:抓取中文文字語法,新增【自訂資料行】>公式為【 = Text.Select([產品],{″一″..″龜″})】 <br> ``` = Text.Select([商品],{"一".."龜"}) ``` <a href="https://hackmd.io/_uploads/XBTKEMG.png"><span class="img100">![](https://hackmd.io/_uploads/XBTKEMG.png)</span></a> S2:抓取英文,新增【自訂資料行】><br>公式為【 = Text.Trim ( Text.Select ( [產品] , {″ a ″…″ z ″,″ A ″…″ Z ″,″ ″,″ . ″,″ ‘ ″}))】<br>移除前後多餘空格,因範例中英文有【’】及【.】 <br> ``` = Text.Trim ( Text.Select ( [商品] , {"a".."z","A".."Z"," ",".","'"})) ``` <a href="https://hackmd.io/_uploads/HyHIZqdIY.png"><span class="img100">![](https://hackmd.io/_uploads/HyHIZqdIY.png)</span></a> S3:也可以移除中文字,來取得英文 <br> ``` = Text.Remove([商品],{"一".."龜"}) ``` ## <span class="h2">G、 多檔案匯入</span> 透過Power Query將多個資料來源的資料合併、追加到一起,任意組合資料、將資料進行分組、透視等整理操作方法快速整併成同一個,以利數據分析。 <a href="https://hackmd.io/_uploads/BJDRN5uIY.png"><span class="img80">![](https://hackmd.io/_uploads/BJDRN5uIY.png)</span></a> S1:開啟Excel新檔 > 載入資料 【2016版Excle】 <a href="https://hackmd.io/_uploads/H1zthc_UY.png"><span class="img60">![](https://hackmd.io/_uploads/H1zthc_UY.png)</span></a> 【365版Excle】 <a href="https://hackmd.io/_uploads/BkRKrq_LK.png"><span class="img50">![](https://hackmd.io/_uploads/BkRKrq_LK.png)</span></a> S2:顯示載入資料情形 > 點選【轉換資料】 <a href="https://hackmd.io/_uploads/S1NH89uIK.png"><span class="img100">![](https://hackmd.io/_uploads/S1NH89uIK.png)</span></a> S3:保留【Content】欄位,其他欄位移除,其內容如下: <a href="https://hackmd.io/_uploads/S1jAwq_UY.png"><span class="img100">![](https://hackmd.io/_uploads/S1jAwq_UY.png)</span></a> S4:【新增資料行】>【自訂資料行】 <a href="https://hackmd.io/_uploads/BkGYi9uLK.png"><span class="img60 textc">![](https://hackmd.io/_uploads/BkGYi9uLK.png)</span></a> :::danger (1) 載入CSV檔案至PQ: &#12288;&#12288; Csv.Document ( ) (2) 本身Excel檔案載入至PQ: &#12288;&#12288; Excel.CurrentWorkbook ( ) { [ Name="表格名稱" ] } [Content] (3) 由其他Excel檔案載入至PQ: &#12288;&#12288; Excel.Workbook ( [Content] , true ) ::: ``` = Excel.Workbook([Content],true) ``` <a href="https://hackmd.io/_uploads/B1au59_Ut.png"><span class="img100">![](https://hackmd.io/_uploads/B1au59_Ut.png)</span></a> S6:依序展開其欄位 ## <span class="h2">H、 巧用Power Query輕鬆管理文件</span> ### (1) 例如 <a href="https://hackmd.io/_uploads/Bk5QKoOLK.png"><span class="img80">![](https://hackmd.io/_uploads/Bk5QKoOLK.png)</span></a> ### (2) 步驟 S1:與上個個單元作法相同載入檔案,開新檔案>【資料】>【取得資料】>【從檔案】>【從資料夾】>選取想要載入的資料夾 >【轉換資料】 <br> S2:依步驟完成,建立公式進行串接組合,並刪除不必要欄位: <a href="https://hackmd.io/_uploads/SkhgC5dLK.png"><span class="img100">![](https://hackmd.io/_uploads/SkhgC5dLK.png)</span></a> S3:完成後,回填至Excel,建立連結公式: <a href="https://hackmd.io/_uploads/HkDIA9_IY.png"><span class="img40">![](https://hackmd.io/_uploads/HkDIA9_IY.png)</span></a> <!-- # 三、PQ-M函數 ## <span class="h2">分隔欄位</span> 分隔欄位:Splitter.SplitTextByDelimiter --> <!-- <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> --> <br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br> # Power Pivot(PP) <span class="red" style="font-size:1.2em;">【範例說明】</span> <a href="https://hackmd.io/_uploads/ryp_TA-Lt.png"><span class="img100">![](https://hackmd.io/_uploads/ryp_TA-Lt.png)</span></a> ## <span class="h2">A、 啟動【Power Pivot】增益集</span> <span style="font-size:1.3em; font-weight:bold;">[※ 詳前面章節](https://hackmd.io/@zoego/工務局excel#D、指令位置) </span> ## <span class="h2">B、 數據載入至【Power Pivot】</span> S1. 【主資料夾】>【從其他來源】> 點選【Excel檔案】 <a href="https://hackmd.io/_uploads/rkzdXa-8t.png"><span class="img100">![](https://hackmd.io/_uploads/rkzdXa-8t.png)</span></a> S2. 點選欲載入數據的Excel檔案 <a href="https://hackmd.io/_uploads/H1nRFAZ8F.png"><span class="img100">![](https://hackmd.io/_uploads/H1nRFAZ8F.png)</span></a> S3. 勾選欲載入數據的工作表來源 <a href="https://hackmd.io/_uploads/HkNdCCWUY.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/HkNdCCWUY.png)</span></a> <a href="https://hackmd.io/_uploads/SJ9ekyzLt.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/SJ9ekyzLt.png)</span></a> ## <span class="h2">C、 建立數據間的關聯圖</span> <a href="https://hackmd.io/_uploads/BkZa7kMLF.png"><span class="img100">![](https://hackmd.io/_uploads/BkZa7kMLF.png)</span></a> ## <span class="h2">D、 建立計算公式及載入關聯欄位數據</span> ### 1、 名詞介紹 <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 兩軟體之間名詞差異</span> <span style="color:#bf0303;">使用PBI計算必須抛棄Excel儲存格計算的思維模式,在PBI中的資料表是以【資料行】方式儲存。</span> |Excel|PP、PQ、PBI| |-|-| |工作表|資料表| |欄|資料行| ||量值:即以【資料表】或【資料行】進行計算| <span style="color:#5E00BC; display:block;font-weight:bold;line-height:2em;font-size:1.1em">🔶 建議</span> |新增資料行:| |-| |<span style="font-weight:bold;">缺點</span>:使用時浪費記憶體 <br><span style="font-weight:bold;">優點:</span>使用Excel公式習慣的人,傾向使用容易理解<br><span style="font-weight:bold;">使用時機:</span><br>&#12288;▷ 將列內容放入篩選器、交叉分析篩選器、行、列<br>&#12288;▷ 需要利用新增的資料行與其他表建立關係時<br>| |量值:| |<span style="font-weight:bold;">缺點:</span><br>&#12288;▷ 公式書寫較難以理解<br>&#12288;▷ 不可使用於交叉分析篩選器<br><span style="font-weight:bold;">優點:</span><br>&#12288;▷ 不浪費記憶體,只有被拖到【報告】中才會執行運算<br>&#12288;▷ 可以重覆使用的計算式<br><span style="font-weight:bold;">使用時機:</span><br>&#12288;▷ 需要重覆使用的計算式<br>&#12288;▷ 是否根據上下文動態運算| ### 1、 工作表載入關聯欄位之值 點選【訂單明細】工作表:建立公式 |<span class="textc">欄位</span>|<span class="textc">公式</span>| |-|-| |單價|單價:=RELATED('商品明細'[銷售單價])| |出貨金額|出貨金額:=[出貨數量] * RELATED ( '商品明細'[銷售單價] )| <a href="https://hackmd.io/_uploads/SJD3I1fUF.png"><span class="img100" style="border:1px solid #000">![](https://hackmd.io/_uploads/SJD3I1fUF.png)</span></a> ``` 單價:=RELATED('商品明細'[銷售單價]) 出貨金額:=[出貨數量]*RELATED('商品明細'[銷售單價]) ``` ### 2、 新增計算公式 |<span class="textc">欄位</span>|<span class="textc">公式</span>| |-|-| |出貨總金額_SUM|出貨總金額_SUM:=SUM( '訂單明細'[出貨金額] )| |出貨總金額|出貨總金額:=SUMx('訂單明細',<br>'訂單明細'[出貨數量] * RELATED ('商品明細'[銷售單價])<br>)| |訂單明細列數|訂單明細列數:= COUNTROWS ( '訂單明細' )| |訂單筆數|訂單筆數:= DISTINCTCOUNT ( '訂單明細'[訂單編號] )| |總出貨量|總出貨量:=SUM ( '訂單明細'[出貨數量] )| |銷售獎金|銷售獎金:=ROUND ( SUMx ( '訂單明細' , '訂單明細'[出貨金額]&#42; 0.1 ) , 0 )| |銷售獎金_每筆round|銷售獎金_每筆round:=SUMx ( '訂單明細' , ROUND( '訂單明細'[出貨金額]&#42;0.1 , 0 ) )| <a href="https://hackmd.io/_uploads/SyjZgefLK.png"><span class="img100">![](https://hackmd.io/_uploads/SyjZgefLK.png)</span></a> ``` 出貨總金額_SUM:=SUM('訂單明細'[出貨金額]) 出貨總金額:=SUMX('訂單明細','訂單明細'[出貨數量]*RELATED('商品明細'[銷售單價])) 訂單明細列數:=COUNTROWS('訂單明細') 訂單筆數:=DISTINCTCOUNT('訂單明細'[訂單編號]) 總出貨量:=SUM('訂單明細'[出貨數量]) 銷售獎金:=ROUND(SUMX('訂單明細','訂單明細'[出貨金額]*0.1),0) 銷售獎金_每筆round:=SUMX('訂單明細',ROUND('訂單明細'[出貨金額]*0.1,0)) ``` ## <span class="h2">E、 建立樞紐分析表</span> ### 1、 指令 |<span class="textc">Excel原生環境</span>|<span class="textc">Power Pivot 工具</span>| |-|-| |指令:【插入】>【樞紐分析表】<a href="https://hackmd.io/_uploads/r18k9LGUF.png"><span class="img80">![](https://hackmd.io/_uploads/r18k9LGUF.png)</span></a>|<a href="https://hackmd.io/_uploads/SyJz5LM8Y.png"><span class="img60">![](https://hackmd.io/_uploads/SyJz5LM8Y.png)</span></a>| ### 2、 樞紐設定原則 <span class="img40">![](https://hackmd.io/_uploads/S1DOR1UNF.png)</span> ### 3、 選項 <span class="img60">![](https://hackmd.io/_uploads/rJicfx8Nt.png)</span> ### 4、 欄位排序 ##### <span class="h4">a. 手動</span> <span class="step">欄位直接拖曳</span> ##### <span class="h4">b. 自動</span> <span class="step">指令:【檔案】>【選項】>【進階】之下>【編輯自訂清單】> 加入清單項目</span> <span class="img100">![](https://hackmd.io/_uploads/SkhtieMNY.png)</span> ### 5、 群組類型 <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 文字</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 數值</span> <span class="step" style="line-height:2em"><i class="fa fa-hand-o-right"></i> 日期</span> #### <span class="h4">【練習說明】</span> <a href="https://hackmd.io/_uploads/SkqLEE7LK.png"><span class="img100">![](https://hackmd.io/_uploads/SkqLEE7LK.png)</span></a> <a href="https://hackmd.io/_uploads/S1_CENQLF.png"><span class="img100">![](https://hackmd.io/_uploads/S1_CENQLF.png)</span></a> ## <span class="h2">F、 建立大綱階層</span> S1:建立大綱階層,如欄位不在同一工作表則利用Related函數載入 <a href="https://hackmd.io/_uploads/ryypwMX8K.png"><span class="img90">![](https://hackmd.io/_uploads/ryypwMX8K.png)</span></a> ``` 商品類別:=RELATED('商品主類別'[商品類別]) ``` S2:啟動【圖表檢視】 <a href="https://hackmd.io/_uploads/B1NkYfQIF.png"><span class="img30">![](https://hackmd.io/_uploads/B1NkYfQIF.png)</span></a> S3:加入樞紐分析表 <a href="https://hackmd.io/_uploads/H1_h9MQ8Y.png"><span class="img100">![](https://hackmd.io/_uploads/H1_h9MQ8Y.png)</span></a> ## <span class="h2">G、 建立【日期表】</span> <span class="red textc" style="font-size:1.3em">【顯示結果】</span><a href="https://hackmd.io/_uploads/BkzLK7X8t.png"><span class="img100">![](https://hackmd.io/_uploads/BkzLK7X8t.png)</span></a> S1:新增【日期表】 <a href="https://hackmd.io/_uploads/B1da5XmLK.png"><span class="img100">![](https://hackmd.io/_uploads/B1da5XmLK.png)</span></a> ``` 季:=INT(([月]+2)/3) ``` S2:日期表與訂單明細,建立關聯 <a href="https://hackmd.io/_uploads/HJxNiQ7Ut.png"><span class="img60">![](https://hackmd.io/_uploads/HJxNiQ7Ut.png)</span></a> ## <span class="h2">H、【群組】建立及說明【值的顯示方式】</span> <a href="https://hackmd.io/_uploads/SkjGx4XLt.png"><span class="img100">![](https://hackmd.io/_uploads/SkjGx4XLt.png)</span></a> ## <span class="h2">I、結合【交叉分析篩選器】</span> S1:點選【插入交叉分析篩選器】及【插入時間表】 <a href="https://hackmd.io/_uploads/H15RxEQUt.png"><span class="img90">![](https://hackmd.io/_uploads/H15RxEQUt.png)</span></a> S2:針對樞紐分析表進行命名 <a href="https://hackmd.io/_uploads/Sk2nWVQIF.png"><span class="img60">![](https://hackmd.io/_uploads/Sk2nWVQIF.png)</span></a> S3:以上兩步驟,點選【篩選連線】進行篩選器與樞紐分析表之結合確認 <a href="https://hackmd.io/_uploads/H1wCMEmIY.png"><span class="img100">![](https://hackmd.io/_uploads/H1wCMEmIY.png)</span></a>