你好,我是小必老師,感謝與你在這裡相遇,以Excel會友。
在Excel中,相信使用過資料透視表與分類彙總的朋友們都知道。分組聚合(求和、平均值、最大值、最小值、計數等)是Excel中最常見的操作。在工作表中這些功能只是一些最常見的功能。
在Excel Power Query中的【分組依據】功能同樣是十分地很強大。但是眾所周知,要實現高階功能往往離不開函式。那麼今天的主角就是——
Table.Group
函式。(關於圖形操作的分組依據小夥伴們可以自行練習,這裡限於篇幅再不做過多的表述)
Table.Group
功能
:就是指定關鍵字對錶進行分組並對列值進行聚合運算。
語法
:function(table
as table
,key
as any
,aggratedColumns
as list
,optionalgroupKind
as nullbale GroupKind.Type
,optionalcomparer
as nullable function
) as table
看完上面的語法其實是一頭霧水,那麼簡單的解釋就是:
第1引數為要對那個表進行分組;
第2個引數是分組的關鍵列,為一個list,但是當只有一個關鍵字的時候可以直接寫成文字型;
第3個引數是分組聚合的列,該引數必須是一個list,並且如果對多個列進行聚合,那第每個list都需要一對大括號,即構成每一個list。
第4個引數是一個可選引數,是分組的型別,共有兩個引數。一個是GroupKind。Local與GroupKind。Global,也可以使用邏輯值代替,即0與1。
第5個引數是同樣一個可選引數,具有比較功能,是對關鍵字進行邏輯判斷後再分組的一個引數。此引數是一個fuction,那麼可擴充套件性非常強。
看了上面的引數的介紹,更覺得深的沒邊了,其實不然。透過下面幾個例項,你將會有更加深刻地理解,也就不會覺得這上函式有多高深了。
案例-
1
如圖所示,將資料來源按“使用方”進行分組,對“員工姓名”進行不重複計數,對“訂單數量”與“訂單總金額”進行求和。
此時M公式可以寫成:
在上面的例子,分組的關鍵字只有一個即“使用方”,此時可以不寫大括號,也可以寫成{“使用方”},如果有多個關鍵字的時候則一定要寫成list,即{“使用方”,“關聯流程”}。在第三個引數中的第一個值是生成的新列的列名。
除此之外,還可以對文字進行操作,比如最常見的將多個文字值連線起來。如將上面的例子中的關聯流程按“使用方”去除重複項後連線下來起來。
案例-
2
上面的例子主要講述了Table。Group函式 常規用法。那麼第三引數到底有什麼用途呢?
關於第三個引數1表示全域性分組,0表示區域性分組。舉一個簡單的例子給大家看看,將下面的資料對“使用方”為關鍵字進行分組,對“訂單數量”進行求和。
A.全域性分組(GroupKind.Global)
全域性分組了正常的分組功能。
或者即上面的1還可以省略。結果下圖所示。
B.區域性分組(GroupKind.Local)
區域性分組的第4個引數寫成0就是區域性分組。
結果如下圖所示。
透過上面的全域性分組與區域性分組的案例可以看出,全域性分組是將表中所有相同的類別進行歸類;而區域性分組是隻對連續相同的類別進行歸類。
這個功能有什麼用呢,可以用來判斷連續性的問題,比如判斷員工的連續上班遲到的天數,商品是否斷碼以及球隊的最大連勝場次的問題。
下面就舉一個員工連續遲到的次數的問題。如圖所示。
對於這個問題,分步來看每個步驟的結果。
首先對資料進行區域性分組,即對“考勤型別”進行區域性分組,即將連續相同的進行分組。如果如下圖所示。
分組完成後對“考勤型別”進行篩選,篩選出“遲到”的記錄。如圖所示。
最後再進行一次分組即可完成。分組的條件為“次數里面的最大值即可”。
當然對於上面的案例,還有其他更中簡單的方法。這裡只對Table。Group函式進行講解與說明。
案例-
3
講了前面的兩個例子後,對於分組的基本的用法有一個更加深刻地理解。那第緊接著再講第5個引數,第5個引數是對第二個引數,即關鍵字的一個判斷,判斷值是一個邏輯值。
通常用(x,y)=>Number。From()固定格式來處理所判斷後的條件值。x代表每一個分組的第一行,y為x當前行及下面的每一行。
如圖所示,還是上面的例子,只是做了一個更改,就是每個姓名下面的都是空白,即null值。如何進行分組時,可向下填充。但這裡為了講解Table。Group函式 第5個引數的用法。如何對姓名及以下記錄中的null值歸納在一起進行分組。
此時可以利用該第5個引數為function的屬性去擴充套件。
對於這個用法相比前面的用法來說就比較地複雜了。第5個引數的屬性是對第2個引數,即關鍵字進行判斷。對於上面的題目中的原理可以理解成遍歷判斷。
過程可以理解為:
第1次分組:
x=“安原”時,y為“安原”以下的值,即y=。所以判斷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個引數還不是特別地明朗。期待大家更多地去挖掘與研究。
宣告:以上文章經學習與整理完成,部分內容有bug的部分,還請大家在留言中批評指正。