通過我的第 289 篇Excel原創文章
你好，我是小必老師，感謝與你在這裡相遇，以Excel會友。
在Excel中，相信使用過數據透視表與分類彙總的朋友們都知道。分組聚合（求和、平均值、最大值、最小值、計數等）是Excel中最常見的操作。在工作表中這些功能只是一些最常見的功能。
在Excel Power Query中的【分組依據】功能同樣是十分地很強大。但是眾所周知，要實現高級功能往往離不開函數。那麼今天的主角就是——Table.Group函數。（關於圖形操作的分組依據小夥伴們可以自行練習，這裡限於篇幅再不做過多的表述）
Table.Group
功能：就是指定關鍵字對錶進行分組並對列值進行聚合運算。
語法：function(table as table,key as any,aggratedColumns as list ,optional groupKind as nullbale GroupKind.Type,optional comparer as nullable function) as table
看完上面的語法其實是一頭霧水，那麼簡單的解釋就是：
第1參數為要對那個表進行分組；
第2個參數是分組的關鍵列，為一個list，但是當只有一個關鍵字的時候可以直接寫成文本型；
第3個參數是分組聚合的列，該參數必須是一個list，並且如果對多個列進行聚合，那第每個list都需要一對大括號，即構成每一個list.
第4個參數是一個可選參數，是分組的類型，共有兩個參數。一個是GroupKind.Local與GroupKind.Global,也可以使用邏輯值代替，即0與1.
第5個參數是同樣一個可選參數，具有比較功能，是對關鍵字進行邏輯判斷後再分組的一個參數。此參數是一個fuction，那麼可擴展性非常強。
看了上面的參數的介紹，更覺得深的沒邊了，其實不然。通過下面幾個實例，你將會有更加深刻地理解，也就不會覺得這上函數有多高深了。
案例-1
如圖所示，將數據源按“使用方”進行分組，對“員工姓名”進行不重複計數，對“訂單數量”與“訂單總金額”進行求和。
此時M公式可以寫成:
= Table.Group(源, "使用方" ,
{
{"員工數量", each List.Count(List.Distinct(_[員工姓名]))},
{"訂單量", each List.Sum([訂單數量])},
{"總金額", each List.Sum([訂單總金額])}
}
)
在上面的例子，分組的關鍵字只有一個即“使用方”，此時可以不寫大括號，也可以寫成｛“使用方”｝,如果有多個關鍵字的時候則一定要寫成list，即｛“使用方”,"關聯流程"｝。在第三個參數中的第一個值是生成的新列的列名。
除此之外，還可以對文本進行操作，比如最常見的將多個文本值連接起來。如將上面的例子中的關聯流程按“使用方”去除重複項後連接下來起來。
= Table.Group(源, "使用方" ,
{
{"流程", each Text.Combine(
List.Distinct([關聯流程]),",")}
}
)
案例-2
上面的例子主要講述了Table.Group函數 常規用法。那麼第三參數到底有什麼用途呢？
關於第三個參數1表示全局分組，0表示局部分組。舉一個簡單的例子給大家看看，將下面的數據對“使用方”為關鍵字進行分組，對“訂單數量”進行求和。
A.全局分組（GroupKind.Global）
全局分組了正常的分組功能。
= Table.Group(源,"使用方",
{
{"訂單量",each List.Sum([訂單數量])}
},1)
或者即上面的1還可以省略。結果下圖所示。
B.局部分組（GroupKind.Local）
局部分組的第4個參數寫成0就是局部分組。
= Table.Group(源,"使用方",
{
{"訂單量",each List.Sum([訂單數量])}
},0)
結果如下圖所示。
通過上面的全局分組與局部分組的案例可以看出，全局分組是將表中所有相同的類別進行歸類；而局部分組是隻對連續相同的類別進行歸類。
這個功能有什麼用呢，可以用來判斷連續性的問題，比如判斷員工的連續上班遲到的天數，商品是否斷碼以及球隊的最大連勝場次的問題。
下面就舉一個員工連續遲到的次數的問題。如圖所示。
對於這個問題，分步來看每個步驟的結果。
首先對數據進行局部分組，即對“考勤類型”進行局部分組，即將連續相同的進行分組。如果如下圖所示。
分組完成後對“考勤類型”進行篩選，篩選出“遲到”的記錄。如圖所示。
最後再進行一次分組即可完成。分組的條件為“次數里面的最大值即可”。
let
源 = Excel.CurrentWorkbook(){[Name="表5"]}[Content],
局部分組 = Table.Group(源,{"員工姓名","考勤類型"},
{
{"次數",each List.Count([考勤類型])}
},0),
篩選 = Table.SelectRows(局部分組,each [考勤類型]="遲到"),
連續最大 = Table.Group(篩選,"員工姓名",
{
{"連續遲到最大次數",each List.Max([次數])}
}
)
in
連續最大
當然對於上面的案例，還有其他更中簡單的方法。這裡只對Table.Group函數進行講解與說明。
案例-3
講了前面的兩個例子後，對於分組的基本的用法有一個更加深刻地理解。那第緊接著再講第5個參數，第5個參數是對第二個參數，即關鍵字的一個判斷，判斷值是一個邏輯值。
通常用(x,y)=>Number.From()固定格式來處理所判斷後的條件值。x代表每一個分組的第一行，y為x當前行及下面的每一行。
如圖所示，還是上面的例子，只是做了一個更改，就是每個姓名下面的都是空白，即null值。如何進行分組時，可向下填充。但這裡為了講解Table.Group函數 第5個參數的用法。如何對姓名及以下記錄中的null值歸納在一起進行分組。
此時可以利用該第5個參數為function的屬性去擴展。
= Table.Group(源,"員工姓名",
{"次數",each List.Count([考勤類型])},0,
(x,y)=>Number.From((y is text)))
對於這個用法相比前面的用法來說就比較地複雜了。第5個參數的屬性是對第2個參數，即關鍵字進行判斷。對於上面的題目中的原理可以理解成遍歷判斷。
過程可以理解為：
第1次分組：
x="安原"時，y為"安原"以下的值，即y={null,null,null,null,null,null,null,null,安丁紅,null,null,null,null,null,null,null,楊玉梅,null,null,null,null,null,null,null,null}.所以判斷y裡的每個元素是否為文本。即：
第1個null值判斷，y=null，結果為FALSE，不滿足條件，為第1組，即"安原組"；
第2個null值判斷，y=null，結果為FALSE，不滿足條件，為第1組，即"安原組"；
……
第8個null值判斷，y=null，結果為FALSE，不滿足條件，為第1組，即"安原組"；
第9個null值判斷，y="安丁紅"，結果為TRUE，此時滿足條件，完成第一次分組，再以出現的文本值的位置為第2個分組的開始，繼續進行第二次分組。
第2次分組：
x="安丁紅"時，分組的邏輯與上面的是一樣的，此時的y為"安丁紅"以下的值，即y={安丁紅,null,null,null,null,null,null,null,楊玉梅,null,null,null,null,null,null,null,null}。
依據以上的邏輯進行分組，直到所有的數據被判斷完成。如果用圖可以表示為：
(x,y)=>的形式還可以寫成其他的，不拘泥這一種。但是目前Table.Group函數的第5個參數還不是特別地明朗。期待大家更多地去挖掘與研究。
閱讀更多 Excel聚焦 的文章
