今天給大家聊一下這段時間微軟在MS365測試版本中推出的一個新函數(shù)：groupby。如果你經(jīng)常身體在公司上班靈魂卻在excel圈子里快活的話應該對這個函數(shù)的名字比較熟悉。圈子里熱鬧和傳言都沒有錯，這家伙確實是數(shù)據(jù)分組統(tǒng)計的函數(shù)神器，在未來，也必然屬于人人必會的Excel最常用的函數(shù)之一。
GROUPBY ( row_fields， values， function， [field_headers]， [total_depth]， [sort_order]， [filter_array] )
嘿~它居然一共有7個參數(shù)，前3個必需，剩下4個可選。參數(shù)雖然有點多，不過每個參數(shù)的功能性都很明確，并不難理解，除了第3參數(shù)。
第3參數(shù)的類型是function。在迭代函數(shù)體系里，凡類型是function的參數(shù)，都不是什么溫柔的東西，攤手，它會十分強大但也可以萬分復雜。下面舉幾個例子，給大家解釋一下GROUPBY函數(shù)的基本用法和各個參數(shù)的計算順序及相關作用。
假設有一份成績表，如下圖所示。
F2單元格輸入以下公式，可以統(tǒng)計各個班級每個學生的成績總分=GROUPBY(A1:B13,D1:D13,SUM)
GROUPBY第1參數(shù)是分組依據(jù)的區(qū)域或數(shù)組，第2參數(shù)是需要聚合的值區(qū)域或數(shù)組，第3參數(shù)是內(nèi)置的lambda聚合表達式。本例中，我們需要按A1:B13的班級和姓名作為分組依據(jù)，對D1:D13區(qū)域的成績聚合，聚合的方式是求和，也就是SUM。除了SUM之外，系統(tǒng)還內(nèi)置了MAX/MIN/COUNTA/CONCAT等快捷聚合方式。GROUPBY的第4參數(shù)表示第1參數(shù)的分組數(shù)據(jù)是否有表頭，可以根據(jù)實際需要進行設置，一共有4種類型。0表示沒有表頭；1表示有表頭，但不顯示。2表示沒有表頭，但需要生成默認表頭。3表示有表頭且顯示。我們將上述公式中的第4參數(shù)設置為2，表示1參沒有表頭，但需要生成默認，返回結果如下：=GROUPBY(A1:B13,D1:D13,SUM,2)
GROUPBY的第5參數(shù)表示是否顯示總計或小計行，它有5種類型可選，如下圖所示。 我們將上述公式中的第5參數(shù)設置為2，就可以顯示總計和小計行。
=GROUPBY(A1:B13,D1:D13,SUM,,2)
4丨 排序GROUPBY的第6參數(shù)可以對返回的結果表進行基于分組依據(jù)列內(nèi)部的數(shù)據(jù)排序，用一個數(shù)字對應結果表中的列數(shù)，當數(shù)字為正數(shù)時表示升序，負數(shù)時表示降序。例如，數(shù)字2，表示對結果表中的第2列數(shù)據(jù)執(zhí)行升序排序，數(shù)字-3，表示對結果表中第3列的數(shù)據(jù)執(zhí)行降序排序。將上述公式中的第6參數(shù)設置為-3，可以對各個班級內(nèi)部的成績，也就是結果表第3列的數(shù)據(jù)，執(zhí)行降序排序，結果如下：=GROUPBY(A1:B13,D1:D13,SUM,,0,-3)
需要注意的是，它這里是對分組內(nèi)部的數(shù)據(jù)執(zhí)行分類排序，而不是整張結果表。如果需要對整張結果表的成績降序排序，可以嵌套SORT函數(shù)。=SORT( GROUPBY(A1:B13,D1:D13,SUM,,0), 3,-1)
 5丨 篩選GROUPBY的第7參數(shù)可以對第1參數(shù)的分組依據(jù)執(zhí)行篩選操作，它是一個由邏輯值構成的數(shù)組，這個數(shù)組的尺寸大小需要和第1參數(shù)保持一致。如果只需要對一班的學生統(tǒng)計總分，同時降序排序，參考代碼如下：
=GROUPBY(A1:B13,D1:D13,SUM,,0,-3, A1:A13='一班')
函數(shù)的第7參數(shù)為A1:A13='一班'，先篩選A1:A13區(qū)域是否等于一班，然后再執(zhí)行聚合等操作。以上是7個參數(shù)的基本功能，這里需要補充說一下7個參數(shù)的運算順序，它并不是按照出現(xiàn)的先后順序作運算的，就像你的他/她…們。Excel首先運行的是第1和第2參數(shù)，讀取分組依據(jù)和對應值，然后運行第7參數(shù)，對第1步的讀取結果進行篩選，接著是第3參數(shù)，執(zhí)行聚合運算，再排序，添加總計和小計行，最后設置表頭。
打個響指，接下來重點說一下第3參數(shù)，也就是function。
看我小眼神，看懂了吧？——不管是洋文還是譯文總之講的都不是人話。攤手，總結起來，這個第3參數(shù)主要有兩個規(guī)則或者說特點。首先，它有內(nèi)置的LAMBDA表達式，存在一個默認的參數(shù)，指向第1參數(shù)分組后的每塊值區(qū)域。其次，它的結果可以是由多個內(nèi)置的lambda表達式聚合后的元素構成的數(shù)組，數(shù)組的方向將決定結果是按行還是按列展開。
以下代碼可以計算每個班級每個學員的成績總分。這是你已經(jīng)知道的??
 =GROUPBY(A1:B13,D1:D13,SUM)
但你可能不知道的是，這個公式的第3參數(shù)SUM，是一種系統(tǒng)內(nèi)置的語法糖，所謂語法糖就是語法的簡寫形式，完整的形式是一個LAMBDA表達式，如下：
=GROUPBY(A1:B13,D1:D13, LAMBDA(x,SUM(x)))
換而言之，這里的SUM是LAMBDA(x,SUM(x))的簡寫形式。LAMBDA是GROUPBY內(nèi)置的一個匿名函數(shù)，它有一個默認的參數(shù)，指向分組后的每組成績。這里設置其名為x(x的名字不是固定的，你可以自由選擇，叫阿貓阿狗也行)，然后使用SUM函數(shù)對其聚合。
同樣的道理，以下代碼可以將每個班級的人名去重后合并成一個字符串，彼此之間用短橫杠相連。
=GROUPBY(A1:A13,B1:B13, LAMBDA(阿貓, TEXTJOIN('-',1,UNIQUE(阿貓)) ), 1,0)
公式的第3參數(shù)是LAMBDA(阿貓,TEXTJOIN('-',1,UNIQUE(阿貓))，先使用unique函數(shù)對分組后的人名去重，再使用TEXTJOIN函數(shù)聚合成一個字符串。假設不但需要統(tǒng)計每個班級每個學員的總分，同時還需要統(tǒng)計平均分。
=GROUPBY(A1:B13,D1:D13, VSTACK(SUM,AVERAGE), 1,0)
公式的第3參數(shù)是VSTACK(SUM,AVERAGE)，它包含了兩種內(nèi)置的lambda聚合函數(shù)語法糖，分別執(zhí)行SUM求和與AVERAGE求平均的聚合運算，最后使用VSTACK縱向按行合并。由于它是縱向按行合并的，返回的是一維垂直數(shù)組，根據(jù)第2個規(guī)則，計算結果也是按行展開。展開后的名字默認為SUM/AVERAGE，看起來有點奇怪，對此，我們可以做一個修改，改成中文名稱總分和平均分，參考公式如下：=GROUPBY(A1:B13,D1:D13, HSTACK( VSTACK(SUM,AVERAGE), {'總分';'平均分'} ) ,1,0)
公式中，使用了HSTACK函數(shù)將由多個lambda表達式聚合的元素 構成縱向數(shù)組和常量數(shù)組{'總分';'平均分'}橫向合并，數(shù)組的第2列值即為值對應的名稱，注意常量數(shù)組中元素分隔使用的是分號，而不是逗號，兩者之間的區(qū)別，我們在「什么是函數(shù)數(shù)組」里詳細講過了，希望你還有印象以上公式是將總分和平均分按行縱向展開，如果你需要橫向按列展開，只需要將多個lambda表達式聚合的元素橫向合并即可:=GROUPBY(A1:B13,D1:D13, VSTACK( HSTACK(SUM,AVERAGE), {'總分','平均分'} ), 3,0)
這就是第3參數(shù)的第2個特點，它的結果可以是由多個內(nèi)置的lambda表達式的聚合元素構成的數(shù)組，數(shù)組的方向將決定結果是按行還是按列展開。除此之外，它還有一些其它小特點甚至是毛病，篇幅原因，咱們就不展開說了——打這么多字，我倦了，倦的像一朵被風折斷的野花。如下圖所示，A:C列是數(shù)據(jù)源，包含了姓名、月份和銷售額。需要統(tǒng)計每個人每個季度的在個人總銷售額的占比情況，并橫向展開。=GROUPBY( A1:A13,C1:C13, VSTACK( MAP( {1,2,3,4}, LAMBDA(_m,LAMBDA(_x,@INDEX(_x,_m)/SUM(_x))) ), {1,2,3,4}&'季度' ), 3,0)
第3行到第9行代碼是GROUPBY函數(shù)的第3參數(shù)，它先使用迭代函數(shù)MAP+三參內(nèi)置的LAMBDA表達式，迭代聚合每個人銷售額占自身總銷售額的占比，返回一個由內(nèi)置LAMBDA表達式聚合生成的多個元素的水平數(shù)組。LAMBDA(_m,LAMBDA(_x,@INDEX(_x,_m)/SUM(_x)))
第1個LAMBDA的變量_m指向MAP的第1參數(shù) {1,2,3,4}，第2個LAMBDA的變量_x，指向GROUPBY分組后的值區(qū)域塊。@INDEX(_x,_m)/SUM(_x)依次取每季度銷售額和總銷售額做占比運算，由于它返回的是單值，可以使用@表示聚合運算。這里你也可以使用SUM等函數(shù)替代@實現(xiàn)聚合的要求：LAMBDA(_m,LAMBDA(_x,SUM(INDEX(_x,_m)/SUM(_x))))
|
