第9章Excel自動(dòng)化報(bào)表Excel辦公應(yīng)用高級(jí)教程目錄Contents9.1

PowerQuery的基礎(chǔ)操作9.2

使用PowerPivot分析數(shù)據(jù)的優(yōu)勢(shì)9.4

疑難解答9.3

實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列9.1.1轉(zhuǎn)換表結(jié)構(gòu)PowerQuery從Excel2016開(kāi)始，已經(jīng)不僅僅是個(gè)插件，而是內(nèi)嵌到Excel中，微軟已經(jīng)把其內(nèi)置為2016以上版本的一個(gè)功能模塊，只需單擊“數(shù)據(jù)”選項(xiàng)卡中的“獲取和轉(zhuǎn)換”功能集即可使用。9.1.1轉(zhuǎn)換表結(jié)構(gòu)將二維表秒變一維表二維表的特點(diǎn)就是根據(jù)行、列來(lái)確定具體的內(nèi)容，而一維表則是將二維表中的具體內(nèi)容轉(zhuǎn)換為一個(gè)分類字段，下面將介紹如何將二維表轉(zhuǎn)換為一維表。9.1.1轉(zhuǎn)換表結(jié)構(gòu)（1）選擇數(shù)據(jù)區(qū)域中任意單元格，在“數(shù)據(jù)”選項(xiàng)卡中單擊“從表格”按鈕。（2）打開(kāi)“創(chuàng)建表”對(duì)話框，直接單擊“確定”按鈕，即可啟動(dòng)了“查詢編輯器”并加載了數(shù)據(jù)。（3）選中所有需要作為一個(gè)字段中分類項(xiàng)的列，這里選擇除“姓名”外的所有其他列，然后單擊“轉(zhuǎn)換”選項(xiàng)卡中的“逆透視列”按鈕。（4）即可得到一張表格。（5）在“開(kāi)始”選項(xiàng)卡中單擊“關(guān)閉并上載”按鈕，即可得到一維表格。（6）最后在“表格工具-設(shè)計(jì)”選項(xiàng)卡中單擊“轉(zhuǎn)換為區(qū)域”按鈕，將表格轉(zhuǎn)換為普通區(qū)域，然后修改標(biāo)題名稱即可。9.1.2多個(gè)工作表的合并匯總使用PowerQuery可以將多張工作表中的數(shù)據(jù)合并匯總到一張工作表中，如果要匯總的工作表是在同一個(gè)工作簿內(nèi)，此時(shí)的匯總并不復(fù)雜，但要先弄清楚這些工作表數(shù)據(jù)的匯總操作，是純粹將數(shù)據(jù)堆積匯總到一張表，還是根據(jù)各張表之間的關(guān)聯(lián)字段來(lái)進(jìn)行匯總。多張工作表的關(guān)聯(lián)匯總例如，工作簿中有4個(gè)工作表，它們都有共同的列“姓名”，且4個(gè)工作表中的員工都是一致的。現(xiàn)在要求把這4個(gè)表的數(shù)據(jù)，依據(jù)姓名進(jìn)行關(guān)聯(lián)，全部匯總到一個(gè)新的工作表中。9.1.2多個(gè)工作表的合并匯總（1）首先，打開(kāi)“部門”、“工資”、“保險(xiǎn)”和“個(gè)稅”4個(gè)工作表。（2）在“數(shù)據(jù)”選項(xiàng)卡中單擊“新建查詢”下拉按鈕，從列表中選擇“從文件”選項(xiàng)，并從其級(jí)聯(lián)菜單中選擇“從工作簿”選項(xiàng)。（3）打開(kāi)“導(dǎo)入數(shù)據(jù)”對(duì)話框，從中選擇要匯總的工作簿，單擊“導(dǎo)入”按鈕。（4）打開(kāi)“導(dǎo)航器”窗格，勾選“選擇多項(xiàng)”復(fù)選框，并勾選四張工作表，單擊“編輯”按鈕。（5）打開(kāi)“查詢編輯器”，在“開(kāi)始”選項(xiàng)卡中單擊“將第一行用作標(biāo)題”按鈕，提升標(biāo)題。（6）在“開(kāi)始”選項(xiàng)卡中單擊“合并查詢”按鈕，打開(kāi)“合并”窗格，上半部分是“部門”表格，保持默認(rèn)設(shè)置。下半部分選擇“工資”。9.1.2多個(gè)工作表的合并匯總（7）然后在上下兩張表中分別選擇“姓名”列，單擊“確定”按鈕，即可得到一個(gè)“部門”和“工資”合并起來(lái)的新列。（8）按照同樣的方法，依次將“部門”和“保險(xiǎn)”、“個(gè)稅”分別合并，完成后。（9）單擊“NewColumn”列標(biāo)題右側(cè)的展開(kāi)按鈕，打開(kāi)篩選窗格。取消勾選“姓名”和“使用原始列名作為前綴”復(fù)選框，單擊“確定”按鈕。（10）按照上述方法，分別打開(kāi)“NewColumn.1”和“NewColumn.2”列右側(cè)的篩選窗格，取消勾選“姓名”和“使用原始列名作為前綴”復(fù)選框，單擊“確定”按鈕即可。（11）最后，在“開(kāi)始”選項(xiàng)卡中單擊“關(guān)閉并上載”按鈕，即可在新的工作表中生成一個(gè)匯總數(shù)據(jù)。9.1.3多個(gè)工作簿的合并匯總使用PowerQuery除了可以合并匯總多個(gè)工作表外，還可以合并匯總多個(gè)工作簿，無(wú)論是各個(gè)工作簿內(nèi)有一張工作表，還是各個(gè)工作簿內(nèi)有多張工作表，使用PowerQuery來(lái)匯總都是輕而易舉。匯總多個(gè)只有一張工作表的工作簿例如，有4個(gè)工作簿，保存了各部門的工資數(shù)據(jù)，每個(gè)工作簿中有1個(gè)工作表，現(xiàn)在需要將4個(gè)工作簿中的數(shù)據(jù)匯總到一個(gè)工作簿中。9.1.3多個(gè)工作簿的合并匯總（1）首先，打開(kāi)“財(cái)務(wù)部”、“采購(gòu)部”、“生產(chǎn)部”和“銷售部”4個(gè)工作簿，查看數(shù)據(jù)。并將4個(gè)工作簿放在一個(gè)文件夾中。（2）新建一個(gè)工作簿，在“數(shù)據(jù)”選項(xiàng)卡中，單擊“新建查詢”下拉按鈕，從列表中選擇“從文件”選項(xiàng)，并從其級(jí)聯(lián)菜單中選擇“從文件夾”選項(xiàng)。（3）打開(kāi)“文件夾”窗格，單擊“瀏覽”按鈕，選擇相應(yīng)的文件夾，單擊“確定”按鈕。（4）打開(kāi)“查詢編輯器”，可以看到要合并的幾個(gè)工作簿文件。（5）保留前兩列Content和Name，其余的列全部刪除。（6）在“添加列”選項(xiàng)卡中單擊“添加自定義列”按鈕。9.1.3多個(gè)工作簿的合并匯總（7）打開(kāi)“添加自定義列”窗格，輸入自定義列公式“=Excel.Workbook([Content])”，單擊“確定”按鈕，可以看到在查詢結(jié)果的右側(cè)多了一列“Custom”，要匯總的工作簿數(shù)據(jù)都在這個(gè)自定義列中。（8）單擊“Custom”右邊的展開(kāi)按鈕，展開(kāi)一個(gè)下拉列表，然后僅僅勾選Data復(fù)選框，取消勾選其它所有選項(xiàng)。（9）再次單擊Data列右側(cè)的展開(kāi)按鈕，展開(kāi)一個(gè)下拉清單，取消勾選“使用原始列名作為前綴”復(fù)選框，其它設(shè)置保持默認(rèn)，單擊“確定”按鈕。（10）接下來(lái)刪除Content和Name這兩列，并將第一行提升標(biāo)題。（11）最后，通過(guò)對(duì)“基本工資”進(jìn)行篩選，來(lái)清除不需要的數(shù)據(jù)。確定后執(zhí)行“關(guān)閉并上載”命令，就得到了4張工作簿合并后的總表。目錄Contents9.1

PowerQuery的基礎(chǔ)操作9.2

使用PowerPivot分析數(shù)據(jù)的優(yōu)勢(shì)9.4

疑難解答9.3

實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列9.2.1輕松解決非重復(fù)計(jì)數(shù)難題PowerPivot在Excel2016及之后的版本中，已經(jīng)是Excel的內(nèi)置功能，用戶只需要打開(kāi)“Excel選項(xiàng)”對(duì)話框，選擇“加載項(xiàng)”選項(xiàng)，將“管理”設(shè)置為“COM加載項(xiàng)”，單擊“轉(zhuǎn)到”按鈕，打開(kāi)“COM加載項(xiàng)”對(duì)話框，勾選“MicrosoftPowerPivotforExcel”復(fù)選框，單擊“確定”按鈕。就可以將PowerPivot添加到功能區(qū)。9.2.1輕松解決非重復(fù)計(jì)數(shù)難題統(tǒng)計(jì)“客戶數(shù)”例如，在訂單統(tǒng)計(jì)表中，需要根據(jù)“客戶ID”統(tǒng)計(jì)購(gòu)買商品的客戶數(shù)，但有的客戶ID是重復(fù)的，因此統(tǒng)計(jì)起來(lái)比較麻煩，此時(shí)，可以使用PowerPivot在數(shù)據(jù)透視表中進(jìn)行統(tǒng)計(jì)。9.2.1輕松解決非重復(fù)計(jì)數(shù)難題（1）選擇表格內(nèi)任意單元格，打開(kāi)“PowerPivot”選項(xiàng)卡，單擊“添加到數(shù)據(jù)模型”按鈕，打開(kāi)“創(chuàng)建表”對(duì)話框，勾選“我的表具有標(biāo)題”復(fù)選框，單擊“確定”按鈕，即可進(jìn)入PowerPivot窗口界面。（2）在“開(kāi)始”選項(xiàng)卡中單擊“數(shù)據(jù)透視表”下拉按鈕，選擇“數(shù)據(jù)透視表”選項(xiàng)，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，單擊“確定”按鈕。（3）即可創(chuàng)建一個(gè)空白數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中將不同的字段拖拽到相應(yīng)的行、值位置。（4）在“值”區(qū)域，單擊“客戶ID”字段，從列表中選擇“值字段設(shè)置”選項(xiàng)。（5）打開(kāi)“值字段設(shè)置”對(duì)話框，在“計(jì)算類型”列表框中選擇“非重復(fù)計(jì)數(shù)”選項(xiàng)，并自定義名稱，單擊“確定”按鈕。（6）在數(shù)據(jù)透視表中統(tǒng)計(jì)出“客戶數(shù)”，最后更改其他標(biāo)題字段名稱即可。9.2.2多表關(guān)聯(lián)分析PowerPivot有一個(gè)巨大的優(yōu)勢(shì)，就是可以集成多數(shù)據(jù)源進(jìn)行數(shù)據(jù)透視表或數(shù)據(jù)透視圖的操作，來(lái)匯總、分析、瀏覽摘要數(shù)據(jù)。用戶使用PowerPivot可以建立兩表之間的關(guān)聯(lián)關(guān)系，把兩表根據(jù)關(guān)鍵字段關(guān)聯(lián)起來(lái)。根據(jù)“客戶ID”字段創(chuàng)建關(guān)系例如，用戶可以將“訂購(gòu)明細(xì)”表與“客戶明細(xì)”表根據(jù)“客戶ID”字段創(chuàng)建關(guān)系，來(lái)分析哪個(gè)地區(qū)購(gòu)買的客戶最多，哪個(gè)地區(qū)購(gòu)買的客戶最少。9.2.2多表關(guān)聯(lián)分析（1）首先，查看“訂購(gòu)明細(xì)”表和“客戶明細(xì)”表中的數(shù)據(jù)。（2）新建一個(gè)工作簿，在“PowerPivot”選項(xiàng)卡中單擊“管理”按鈕。（3）進(jìn)入PowerPivot界面，在“開(kāi)始”選項(xiàng)卡中單擊“從其他源”按鈕。（4）打開(kāi)“表導(dǎo)入向?qū)А睂?duì)話框，選擇“Excel文件”選項(xiàng)，單擊“下一步”按鈕。（5）在彈出的對(duì)話框中單擊“瀏覽”按鈕，選擇“訂購(gòu)明細(xì)”表，并勾選“使用第一行作為列標(biāo)題”復(fù)選框，單擊“下一步”按鈕。（6）在彈出的對(duì)話框中勾選源表，單擊“完成”按鈕。即可將“訂購(gòu)明細(xì)”表導(dǎo)入成功。（7）按照上述方法，導(dǎo)入“客戶明細(xì)”表。9.2.2多表關(guān)聯(lián)分析（8）在“訂購(gòu)明細(xì)”中，選擇“客戶ID”任意單元格，在“設(shè)計(jì)”選項(xiàng)卡中單擊“創(chuàng)建關(guān)系”按鈕。（9）打開(kāi)“創(chuàng)建關(guān)系”對(duì)話框，將“表2”設(shè)置為“客戶明細(xì)”，并在“列”中選擇“客戶ID”選項(xiàng)，單擊“確定”按鈕。（10）即可完成兩表關(guān)系的創(chuàng)建，關(guān)系創(chuàng)建成功后，字段“客戶ID”上會(huì)顯示一個(gè)小圖標(biāo)。（11）在“開(kāi)始”選項(xiàng)卡中單擊“數(shù)據(jù)透視表”按鈕，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，直接單擊“確定”按鈕。（12）創(chuàng)建一個(gè)數(shù)據(jù)透視表，將“訂購(gòu)明細(xì)”表中的“客戶ID”字段拖至“值”區(qū)域，并進(jìn)行計(jì)數(shù)，將“客戶明細(xì)”表中的“省份”字段拖至“行”區(qū)域。（13）對(duì)數(shù)據(jù)透視表中的“客戶數(shù)”字段進(jìn)行降序排序，即可得到分析結(jié)果。9.2.3快速合并同類項(xiàng)除了前面介紹的PowerPivot功能，用戶通過(guò)數(shù)據(jù)模型數(shù)據(jù)透視表中的CONCATENATEX函數(shù)，還可以合并表格中的同類項(xiàng)。按照“省份”合并“客戶ID”例如，用戶需要將相同省份下的“客戶ID”合并在一個(gè)單元格中，下面將介紹具體操作方法。9.2.3快速合并同類項(xiàng)（1）選擇表格中任意單元格，按【Ctrl+T】組合鍵，打開(kāi)“創(chuàng)建表”對(duì)話框，直接單擊“確定”按鈕。（2）在“PowerPivot”選項(xiàng)卡中單擊“添加到數(shù)據(jù)模型”按鈕。將此表【添加到數(shù)據(jù)模型】。（3）在“PowerPivot”選項(xiàng)卡中單擊“度量值”下拉按鈕，選擇“新建度量”選項(xiàng)。（4）打開(kāi)“度量值”對(duì)話框，輸入公式“=CONCATENATEX('表1','表1'[客戶ID],"、")”，單擊“確定”按鈕。（5）選擇數(shù)據(jù)區(qū)域任意單元格，在“插入”選項(xiàng)卡中單擊“數(shù)據(jù)透視表”按鈕。（6）打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”復(fù)選框，單擊“確定”按鈕。（7）在“數(shù)據(jù)透視表字段”窗格中，將“省份”字段添加至“行”區(qū)域，將“度量值1”添加到“值”區(qū)域。（8）最后，刪除“總計(jì)”行，并修改標(biāo)題字段名稱即可。目錄Contents9.1

PowerQuery的基礎(chǔ)操作9.2

使用PowerPivot分析數(shù)據(jù)的優(yōu)勢(shì)9.4

疑難解答9.3

實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列本章實(shí)戰(zhàn)演練為將多列轉(zhuǎn)成一列，運(yùn)用前面所學(xué)知識(shí)，以熟練掌握和鞏固PowerQuery的使用。1.案例效果本章實(shí)戰(zhàn)演練為將多列姓名轉(zhuǎn)換成一列。實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列2.操作思路掌握PowerQuery的應(yīng)用，下面將進(jìn)行簡(jiǎn)單介紹。（1）將數(shù)據(jù)導(dǎo)入PowerQuery。（2）在“添加列”選項(xiàng)卡中單擊“添加索引列”下拉按鈕，選擇“從0”選項(xiàng)，添加索引列。（3）選中索引列，逆透視其他列。（4）得到逆透視的結(jié)果。用戶可以根據(jù)需要對(duì)索引和屬性列排序。（5）刪除索引和屬性列，單擊“關(guān)閉并上載”按鈕即可。目錄Contents9.1

PowerQuery的基礎(chǔ)操作9.2

使用PowerPivot分析數(shù)據(jù)的優(yōu)勢(shì)9.4

疑難解答9.3

實(shí)戰(zhàn)演練：將多列轉(zhuǎn)成一列疑難解答A:選擇列，在“開(kāi)始”選項(xiàng)卡中單擊“刪除列”下拉按鈕，從列表中選擇“刪除列”選項(xiàng)即可。Q:如何刪除查詢列？Q:如何對(duì)列數(shù)據(jù)進(jìn)行排序？A:選擇列，單擊其右側(cè)下拉按鈕，從展開(kāi)的列表中選擇“升序排序”或“降序排序”即可。疑難解答A:選擇列，在“轉(zhuǎn)換”選項(xiàng)卡中單擊“重命名”按鈕，列標(biāo)題處于可編輯狀態(tài)，重新輸入名稱即可。Q:如何