新函數(shù)GROUPBY和PIVOTBY函數(shù)，它們不但能對數(shù)據(jù)分類匯總，還能進行排序與篩選，其中PIVOTBY函數(shù)還可以制作成交叉表。
一、GROUPBY函數(shù)
GROUPBY：根據(jù)指定的列對數(shù)據(jù)進行分組、聚合、排序和篩選
語法：=GROUPBY (row_fields，values，function，[field_headers]，[total_depth]，[sort_order]，[filter_array])
參數(shù)
說明
row_fields
（必需）
一個面向列的數(shù)組或區(qū)域，其中包含用于對行進行分組和生成行標題的值。
數(shù)組或區(qū)域可以包含多個列。 如果是這樣，輸出將具有多個行組級別。
values
（必需）
要聚合計算的數(shù)據(jù)的面向列的數(shù)組或區(qū)域。
數(shù)組或區(qū)域可以包含多個列。 如果是這樣，輸出將具有多個聚合。
函數(shù)
（必需）
用于聚合值的顯式或 eta 縮減 lambda (SUM、PERCENTOF、AVERAGE、COUNT 等 )
可以提供 lambda 的向量。 如果是這樣，輸出將具有多個聚合。 矢量的方向?qū)⒋_定
它們是按行還是按列布局。
field_headers
表一個數(shù)字，指定 row_fields 和 值是否具有標頭，以及是否應在結(jié)果中返回字段
標頭。 可能的值為：
缺失：自動 (默認)
0：否
1：是，不顯示
2：否，但生成
3：是并顯示
注意: 自動假定數(shù)據(jù)包含基于 values 參數(shù)的標頭。 如果第 1 個值為文本，第 2 個值為數(shù)字，則假定數(shù)據(jù)具有標頭。 如果有多個行或列組級別，則顯示字段標頭。
total_depth
確定行標題是否應包含總計。 可能的值為：
缺失：自動：總計，如果可能，小計 (默認)
0：無總計
1：總計，總計行顯示在最后一行
2：總計和小計，其中總計行顯示在最后一行
-1：總計行顯示在第一行
-2： 總計和小計，其中總計行顯示在第一行
注意: 對于小計， 字段 必須至少包含 2 列。 如果 字段具有足夠的列，
則支持大于 2 的數(shù)字。
sort_order
用來對行排序的新數(shù)組的列號。 如果數(shù)字為負數(shù)，則行按降序/反向排序。
僅基于 row_fields進行排序時，可以提供數(shù)字向量。
filter_array
篩選條件。一個面向列的 1D 布爾值數(shù)組，指示是否應考慮相應的數(shù)據(jù)行。
注意: 數(shù)組的長度必須與提供給 row_fields的長度匹配。條件用法可參數(shù)FILTER函數(shù)
的第二個參數(shù)。
field_relationship
指定向row_fields提供多個列時的關系字段。 可能的值為：
0：層次結(jié)構(gòu) (默認)
1：表
使用“層次結(jié)構(gòu)”字段關系 (0) ，對后續(xù)字段列進行排序會考慮早期列的層次結(jié)構(gòu)。
如果表字段關系 (1) ，則每個字段列的排序是獨立完成的。 不支持小計，因為它們依賴于具有層次結(jié)構(gòu)的數(shù)據(jù)。
這個函數(shù)參數(shù)一共有7個，雖然看起來多，但是也并不算復雜，只有第一到第三參數(shù)是必選參數(shù)，其余的參數(shù)都是可以省略的。下面舉例說明：
1、分類匯總
=GROUPBY(A1:A10,E1:E10,SUM)
參數(shù)1：A1:A10 采購方式所在的列
參數(shù)2：E1:E10 采購總價所在的列
參數(shù)3：SUM表示求和
第三參數(shù)是有很多匯總方式的，詳見下圖，可以選擇自己需要的，這個就是它的基本用法
2、多層匯總
公式：=GROUPBY(A1:B10,D1:E10,AVERAGE)
這個函數(shù)不是僅僅只能對一列進行分類匯總，還能設置多個匯總字段。
下圖就是根據(jù)采購方式以及商品名稱的數(shù)據(jù)用戶金額的平均值
其實在這里我們可以簡單的把第一參數(shù)看作是透視表的行區(qū)域，第二參數(shù)看做是透視表的值區(qū)域。第三參數(shù)說計算方式。
3、是否包含表頭
上面的案例中都有沒有表頭的，如果你想顯示表頭，就需要設置第四參數(shù)，輸入對應的代碼即可
0：數(shù)據(jù)源中沒有表頭
1：數(shù)據(jù)源中有表頭但是不顯示
2：數(shù)據(jù)源中沒有表頭，但是需要生成1個新的表頭
3：數(shù)據(jù)源中有表頭，并且顯示表頭
在這個參數(shù)中0，1，3都比較容易理解，關鍵是參數(shù)2，如果數(shù)據(jù)源中沒有表頭，就會生成新的表頭，以字段1，值1這樣的命名方式來添加新表頭，這個表頭也是無法修改的，效果如下圖所示
4、是否需要顯示總計與小計
GROUPBY不但能顯示總計還能顯示小計，就需要設置第五參數(shù)，也是需要輸入對應的代碼來設置小計與總計
0：沒有總計
1：顯示總計
2：顯示總計與小計
-1：在頂部顯示總計
-2：在頂部顯示總計與小計
具體的效果如下圖所示，關鍵點需要明白，如果你想顯示小計，第一參數(shù)至少需要設置2列數(shù)據(jù)，否則的話就會顯示為錯誤值 -1與-2是將小計與總計放在表格上方的就不再演示了，大家可以操作這試一下
5、是否需要排序
在這里正數(shù)表示升序，負數(shù)表示降序，只能根據(jù)1列數(shù)據(jù)來做升序或者降序的排序。排序列數(shù)是根據(jù)函數(shù)的結(jié)果來指定的
如下圖，將第六參數(shù)設置為2，就表示根據(jù)結(jié)果表的第2列進行升序或者降序的排序
6、同時匯總列示各銷售人員的銷售總額和銷售額平均值
想知道每個銷售人員的銷售總額，還想看看平均銷售額是多少，可以用這個公式：
=GROUPBY (B1:B201,E1:E201,HSTACK (SUM,AVERAGE),,0)
這里的 HSTACK (SUM,AVERAGE) 表示對 “銷售額” 這一列同時進行求和以及求平均值的操作。
7、匯總各銷售人員的銷售總量和銷售額平均值
要是想對不同列進行不同的匯總操作，比如對 “數(shù)量” 求和，對 “銷售額” 求平均值，公式可以這樣寫：
=GROUPBY (B2:B201,D2:E201,HSTACK (SUM,AVERAGE))
這樣就能得到每個銷售人員的銷售總量和銷售額平均值啦。
8、按部門匯總?cè)藛T姓名
GROUPBY 函數(shù)不僅能處理數(shù)字，還能處理文本呢。要是想按部門把人員姓名匯總起來，可以用這個公式：
=GROUPBY (A1:A20,B1:B20,ARRAYTOTEXT,3,0)
這里的 ARRAYTOTEXT 表示把人員姓名的數(shù)組轉(zhuǎn)換成文本形式進行匯總。
以上公式與以下公式結(jié)果是一樣的。
=GROUPBY(A1:A20,B1:B20,LAMBDA(x,TEXTJOIN(",",1,x)),3,0)
9、按條件篩選的匯總表
如果只想?yún)R總符合某些條件的數(shù)據(jù)，比如只匯總不同部門男士的姓名，公式可以這么寫：
=GROUPBY (A1:A20,B1:B20,ARRAYTOTEXT,3,0,,C1:C20="男")
最后一個參數(shù) C1:C20="男" 就是篩選條件，表示只對性別為 “男” 的數(shù)據(jù)進行匯總。
二、PIVOTBY函數(shù)
PIVOTBY函數(shù)，它其實就是透視表的函數(shù)版，功能非常非常的強大，搭配新函數(shù)還能實現(xiàn)多表統(tǒng)計，相較于透視表最大的優(yōu)點就是能自動更新結(jié)果。
PIVOTBY函數(shù)：根據(jù)指定的行列字段，對數(shù)據(jù)進行分組、聚合、排序、篩選
語法：PIVOTBY (row_fields，col_fields，values，function，[field_headers]，[row_total_depth]，[row_sort_order]，[col_total_depth]，[col_sort_order]，[filter_array]，[relative_to])
參數(shù)
說明
row_fields
（必需）
一個面向列的數(shù)組或區(qū)域，其中包含用于對行進行分組和生成行標題的值。
數(shù)組或區(qū)域可以包含多個列。 如果是這樣，輸出將具有多個行組級別。
col_fields
（可選）
一個面向列的數(shù)組或區(qū)域，其中包含用于對列進行分組和生成列標題的值。
數(shù)組或區(qū)域可以包含多個列。 如果是這樣，輸出將具有多個列組級別。
此參數(shù)若忽略，則功能與GROUPBY函數(shù)相似。
values
（必需）
要聚合的數(shù)據(jù)的面向列的數(shù)組或區(qū)域。
數(shù)組或區(qū)域可以包含多個列。 如果是這樣，輸出將具有多個聚合。
函數(shù)
（必需）
定義如何聚合值的 lambda 函數(shù)或 eta-reduced lambda (SUM、AVERAGE、COUNT
等) 。
可以提供 lambda 的向量。 如果是這樣，輸出將具有多個聚合。 矢量的方向?qū)⒋_定
它們是按行還是按列布局。
field_headers
一個數(shù)字，指定 row_fields、col_fields 和 值是否具有標頭，以及是否應在結(jié)果中
返回字段標頭。 可能的值為：
缺失：自動。
0：否
1：是且不顯示
2：否，但生成
3：是并顯示
注意: 自動假定數(shù)據(jù)包含基于 values 參數(shù)的標頭。 如果第 1 個值為文本，第 2 個值
為數(shù)字，則假定數(shù)據(jù)具有標頭。 如果有多個行或列組級別，則顯示字段標頭。
row_total_depth
確定行標題是否應包含總計。 可能的值為：
缺失：自動：總計和小計（如果可能）。
0：無總計
1：總計，總計行顯示在最后一行
2：總計和小計，其中總計行顯示在最后一行
-1：總計行顯示在第一行
-2： 總計和小計，其中總計行顯示在第一行
注意: 對于小計， row_fields 必須至少包含 2 列。 如果row_field有足夠的列，
則支持大于 2 的數(shù)字。
row_sort_order
一個數(shù)字，指示應如何對列進行排序。 數(shù)字對應于 row_fields 中的列，后跟 值中
的列。 如果數(shù)字為負數(shù)，則行按降序/反向排序。
僅基于 row_fields進行排序時，可以提供數(shù)字向量。
col_total_depth
確定列標題是否應包含總計。 可能的值為：
缺失：自動：總計和小計（如果可能）。
0：無總計
1：總計，總計行顯示在最后一列
2：總計和小計，其中總計行顯示在最后一列
-1：總計行顯示在數(shù)值區(qū)域的第一列
-2： 總計和小計，其中總計行顯示在數(shù)值區(qū)域的第一列
注意: 對于小計， col_fields 必須至少包含 2 列。 如果col_field有足夠的列，則支持
大于 2 的數(shù)字。
col_sort_order
一個數(shù)字，指示應如何對行進行排序。 數(shù)字對應于 col_fields 中的列，后跟 值中的
列。 如果數(shù)字為負數(shù)，則行按降序/反向排序。
僅基于 col_fields進行排序時，可以提供數(shù)字向量。
filter_array
篩選條件。一個面向列的 1D 布爾值數(shù)組，指示是否應考慮相應的數(shù)據(jù)行。
注意: 數(shù)組的長度必須與提供給 row_fields 和 col_fields的長度匹配。
條件用法可參數(shù)FILTER函數(shù)的第二個參數(shù)。
relative_to
使用需要兩個參數(shù)的聚合函數(shù)時，relative_to 控制向聚合函數(shù)的第二個參數(shù)提供哪些
值。 這通常在為函數(shù)提供PERCENTOF時使用。
可能的值為：
0：列匯總 (默認)
1：行總計
2：總計
3：父總計
4：父行總計
注意: 僅當 函數(shù) 需要兩個參數(shù)時，此參數(shù)才會產(chǎn)生影響。 如果向 函數(shù)提供自定義 lambda 函數(shù)，則它應遵循以下模式：LAMBDA (子集、totalset、SUM (子集) /SUM (totalset) )
PIVOTBY函數(shù)的用法與GROUPBY函數(shù)差不多，只是多將源表列值轉(zhuǎn)換為行標題的的參數(shù)的使用。具體用法如下：
1、常規(guī)用法
比如現(xiàn)在我們想要根據(jù)商品名稱來計算每種采購方式的總數(shù)。
公式：=PIVOTBY(B1:B10,A1:A10,D1:D10,SUM)
第一參數(shù)：商品名稱列
第二參數(shù)：采購方式列
第三參數(shù)：采購的數(shù)量列
第四參數(shù)：SUM統(tǒng)計方式，求和
這個函數(shù)它其實就是透視表的函數(shù)版，第一參數(shù)可以看做行區(qū)域，第二參數(shù)看做列區(qū)域，第三參數(shù)看做值區(qū)域，第四參數(shù)是統(tǒng)計的方式。
第四參數(shù)是可以設置多種統(tǒng)計方式的，大家可以根據(jù)自己的需要來設置
2、分類統(tǒng)計
第一跟第二參數(shù)僅僅只能設置一列，還能設置多列數(shù)據(jù)，如下圖，我們就統(tǒng)計了，每種采購方式下采購商品的總數(shù)與金額
公式：=PIVOTBY(A1:B10,,D1:E10,SUM)
第一參數(shù)：A1:B10列字段區(qū)域
第二參數(shù)：忽略
第三參數(shù)：統(tǒng)計區(qū)域
第四參數(shù)：統(tǒng)計方式，sum求和
PIVOTBY進行分類統(tǒng)計，一般要求數(shù)據(jù)區(qū)域是連續(xù)的，如果你的數(shù)據(jù)區(qū)域不是聯(lián)系的可以考慮使用HSTACK來構(gòu)建連續(xù)的區(qū)域，將其放入對應的參數(shù)即可
3、結(jié)果是否包含表頭
設置是否需要包含表頭，主要是設置第五參數(shù)，參數(shù)一共有4個
參數(shù)為0：所選區(qū)域不包含表頭，如果所選區(qū)域存在表頭，表頭會參與計算，如下圖右上角演示
參數(shù)為1：所選區(qū)域包含表頭，但是不顯示，如下入坐上第一個
參數(shù)為2：所選區(qū)域不包含表頭，但是自動生成表頭，一般都是值1、值2、行字段1、列字段1這樣的表頭，效果如下圖右下鍵中間位置
參數(shù)為3：所選區(qū)域包含表頭，并顯示表頭，效果如下圖左下角
4、行標題是否需要包含總計
第6參數(shù)，主要是用來設計當前的結(jié)果是否包含小計與總計的
參數(shù)為0：不需要匯總行
參數(shù)為1：在下方顯示總計
參數(shù)為2：在下方顯示總計與小計
參數(shù)為-1：在上方顯示總計
參數(shù)為-2：在上方顯示總計與小計
具體效果大家可以參考下圖，我們需要注意的是，如果你想要顯示小計列字段至少需要選擇2列，才能顯示小計，列字段僅僅選擇1列，是不會顯示小計的，會顯示為錯誤值。
5、列區(qū)域的排序方式
第7參數(shù)是用來設置列字段的排序方式的。我們需要輸入其對應的列數(shù)來進行排序，正數(shù)表示升序，負數(shù)表示降序
公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-1)
在這里參數(shù)為-1，就是表結(jié)果會根據(jù)【采購方式】這一列進行降序【排序】，第二列【商品名稱】默認升序排序
公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-2)
在這里我們參數(shù)為-2就表示根據(jù)第二列【商品名稱】來進行【降序排序】，第一列【采購方式】默認升序排序，
公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,{-1,2})
也可以先對第一列排序，然后在對第二列排序，在這里就需要構(gòu)建一個數(shù)組，具體效果如下圖所示
第8與第9參數(shù)的使用方法是一模一樣的，只不過是針對的列方向，我們就不再演示了，大家可以試著做一下。
6、篩選
第10個參數(shù)是用來進行數(shù)據(jù)篩選的。如下圖，我們想要篩選【采購方式】是APP的數(shù)據(jù)
公式：=PIVOTBY(A1:B10,,D1:D10,SUM,,1,-1,,,A1:A10="APP")
