大家好，前面我們講過利用常規的方法，制作二級下拉列表輔助表。

今天我們就來介紹一下，Dax 度量值（Power Pivot）和 Power Query 的 M 函數做法，主要帶大家拓展思路。

如下圖，我們現在需要将左表轉換右邊的模式。

我們先來看看操作：

❶ 制作一個輔助列，公式為：

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

❷ 插入數據透視表，并勾選「将此數據添加到數據模型」。

PS.我們想要使用 Dax，隻需要在創建數據透視表時，勾選【将此數據添加到數據模型】即可。

❸ 單擊數據透視表區域，在【Power Pivot】選項卡下，單擊【度量值】-【新建度量值】。

❹ 在公式欄中輸入公式，度量值名稱為度量值 1。

=CONCATENATEX('區域','區域'[小類])

公式中，區域是我們的數據源表格，區域[小類]是區域表中小類列。

CONCATENATEX 函數的作用，就是将多個文本合并到一起，類似于 Excel 中的 TEXTJOIN 函數。

CONCATENATEX 函數的結構如下：

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

=CONCATENATEX('區域','區域'[小類])

所以上面 Dax 函數公式的含義，就是對區域表中的小類列進行文本合并。

❺ 将輔助列放在行區域，将大類放在列區域，将度量值 1 放在值區域。

❻ 将總計行和列禁用。

❼ 到這裡，就制作完成了。

關于 Dax，大家可能有些疑惑，下面我來簡單的介紹一下。

傳統的數據透視表無法對文本進行透視，但是由于超級透視表（Power Pivot）的出現，利用 Dax 度量值我們就可以實現這一功能。

Power 是超級的意思，所以 Power Pivot 就是超級數據透視表。

DAX 是 Data Analysis Expression 的縮寫，即數據分析表達式，Dax 是在 Power Pivot 的基礎上使用的數據統計函數。

使用 DAX 的好處是：

❶ 可以彌補數據透視表中的【計算字段】的諸多缺陷。

❷ Dax 函數可以修改聚合計算的方法。

在普通數據透視表中，值彙總方式，隻有求和，計數……等幾種方式。

而在 Power Pivot 中，可以通過多種 Dax 函數達到更靈活的彙總。

比如這個案例中，我們使用 CONCATENATEX 函數對文本進行合并。

PowerQuery 是數據清理和數據轉換的利器，現在我們就來看看，利用它，是如何達到所想要的效果的。

具體步驟：

❶ 将數據導入到 PQ 編輯器中。

選擇數據區域-在【數據】選項卡下，選擇【來自工作表】-【确定】，進入 PQ 編輯器中。

❷ 選擇大類列，在【主頁】選項卡下，單擊【分組依據】-所有行-【确定】。

PS：分組依據功能是對數據進行分組統計的，這裡我們想要的是對大類進行分組，同時，彙總項需要的是，大類中的小類形成的 list。

❸ 将 M 函數公式後面改成 each [小類]。

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

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

Table.FromColumns 函數能夠将各列形成 list 轉換為各列的表格。

= Table.FromColumns(lists,标題形成的list)

案例中：

= Table.FromColumns(分組後的小類形成的lists,标題大類的list)

所以公式為，

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

❺ 關閉并上載表格。

到這裡，PQ 方法就完成了。

本文介紹了二級下拉列表輔助表的延伸拓展方法：

使用 Dax 度量值：

• 利用 Countif 函數輔助列，統計第幾次出現。
• 新建度量值，CONCATENCEX 函數将文本進行合并。
• 大類為列區域，輔助列為行區域，度量值為值區域。

使用 PowerQuery：

• 分組依據。
• Table.FromColumns。

關于二級下拉列表的輔助表的制作方法你學會了嘛~

如果大家還想要知道 PQ 和 PP 其他相關的知識，歡迎留言區告訴我哦~（覺得太難或者不需要，也可以在留言區中聊聊）

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!