你會超等透視表嗎？比Excel透視表好用10倍都不止

各位好，

前方我們講過使用常規(guī)的辦法，制造二級下拉列表幫助表。

今天我們就來先容一下，Dax 度量值（Power Pivot）和 Power Query 的 M 函數(shù)做法，主要帶各位拓展思緒。

如下圖，我們如今必要將左表轉換右方的形式。

01 使用 Dax 度量值

我們先來看看利用：

? 制造一個幫助列，公式為：

=COUNTIF($B$2:B2,B2)

? 插進數(shù)據(jù)透視表，并勾選「將此數(shù)據(jù)添加到數(shù)據(jù)模子」。

PS.我們想要使用 Dax，只必要在創(chuàng)建數(shù)據(jù)透視表時，勾選【將此數(shù)據(jù)添加到數(shù)據(jù)模子】即可。

? 單擊數(shù)據(jù)透視表地區(qū)，在【Power Pivot】選項卡下，單擊【度量值】-【新建度量值】。

? 在公式欄中輸入公式，度量值稱呼為度量值 1。

=CONCATENATEX('地區(qū)','地區(qū)'[小類])

公式中，地區(qū)是我們的數(shù)據(jù)源表格，地區(qū)[小類]是地區(qū)表中小類列。

CONCATENATEX 函數(shù)的作用，就是將多個文本兼并到一同，相似于 Excel 中的 TEXTJOIN 函數(shù)。

CONCATENATEX 函數(shù)的布局如下：

=CONCATENATEX(表，表達式，分開符)

=CONCATENATEX('地區(qū)','地區(qū)'[小類])

以是外表 Dax 函數(shù)公式的涵義，就是對地區(qū)表中的小類列舉行文本兼并。

? 將幫助列放行家地區(qū)，將大類放在列地區(qū)，將度量值 1 放在值地區(qū)。

? 將總計行和列禁用。

? 到這里，就制造完成了。

關于 Dax，各位約莫有些疑惑，底下我來簡便的先容一下。

傳統(tǒng)的數(shù)據(jù)透視表無法對文本舉行透視，但是由于超等透視表（Power Pivot）的顯現(xiàn)，使用 Dax 度量值我們就可以完成這一功效。

Power 是超等的意思，以是 Power Pivot 就是超等數(shù)據(jù)透視表。

DAX 是 Data Analysis Expression 的縮寫，即數(shù)據(jù)分析表達式，Dax 是在 Power Pivot 的基本上使用的數(shù)據(jù)統(tǒng)計函數(shù)。

使用 DAX 的利益是：

? 可以補償數(shù)據(jù)透視表中的【盤算字段】的諸多缺陷。

? Dax 函數(shù)可以修正聚算盤算的辦法。

在平凡數(shù)據(jù)透視表中，值匯總辦法，僅有求和，計數(shù)……等幾種辦法。

而在 Power Pivot 中，可以經(jīng)過多種 Dax 函數(shù)到達更機動的匯總。

好比這個案例中，我們使用 CONCATENATEX 函數(shù)對文本舉行兼并。

02 使用 PowerQuery

PowerQuery 是數(shù)據(jù)算賬和數(shù)據(jù)轉換的利器，如今我們就來看看，使用它，是怎樣到達所想要的后果的。

具體步調：

? 將數(shù)據(jù)導入到 PQ 編纂器中。

選擇數(shù)據(jù)地區(qū)-在【數(shù)據(jù)】選項卡下，選擇【來自事情表】-【確定】，進入 PQ 編纂器中。

? 選擇大類列，在【主頁】選項卡下，單擊【分組依據(jù)】-一切行-【確定】。

PS：分組依據(jù)功效是對數(shù)據(jù)舉行分組統(tǒng)計的，這里我們想要的是對大類舉行分組，同時，匯總項必要的是，大類中的小類構成的 list。

? 將 M 函數(shù)公式后方改成 each [小類]。

? 單擊【fx】新增一個步調，輸入公式：

= Table.FromColumns(分組的行[計數(shù)],分組的行[大類])

Table.FromColumns 函數(shù)可以將各列構成 list 轉換為各列的表格。

= Table.FromColumns(lists,標題構成的list)

案例中：

= Table.FromColumns(分組后的小類構成的lists,標題大類的list)

以是公式為，

= Table.FromColumns(分組的行[計數(shù)],分組的行[大類])

? 關閉并上載表格。

到這里，PQ 辦法就完成了。

03 總結一下

本文先容了二級下拉列表幫助表的延伸拓展辦法：

使用 Dax 度量值：

• 使用 Countif 函數(shù)幫助列，統(tǒng)計第多次顯現(xiàn)。
• 新建度量值，CONCATENCEX 函數(shù)將文本舉行兼并。
• 大類為列地區(qū)，幫助列為行地區(qū)，度量值為值地區(qū)。

使用 PowerQuery：

• 分組依據(jù)。
• Table.FromColumns。

關于二級下拉列表的幫助表的制造辦法你學會了嘛~

假如各位還想要曉得 PQ 和 PP 其他干系的知識，接待留言區(qū)報告我哦~（以為太難大概不必要，也可以在留言區(qū)中聊聊）