在 Excel 中分析資料時，往往會用到資料透視表。透過透視表，可以對資料從不同維度、不同類別來彙總和分析。但是透視表功能很簡單，分組依據也很固化。需要將部分資料合併、分割槽間或者允許重複的特殊條件來分組彙總時便沒法實現。針對 Excel 透視表的分組難點，本文列出一些常用的分組示例，解析難點並提供 SPL 解決程式碼。SPL 是專業計算引擎 esProc 使用的語言，用於處理結構化資料的運算非常方便。

一、常規完全分組

這裡有一份 2019 年的產品銷售表，表中記錄了每個銷售對某產品的銷售明細。現在想要按銷售員工統計出每人的銷售總量，並報告出銷售量的前三名予以嘉獎。

Excel 檔案中資料如下：

期望結果：

使用 Excel 的透視表來做這種常規分析，能用但不好用。之所以說能用，是因為使用透視表畢竟還是可以將所有資料彙總出來，然後透過排序得出名次，再複製出前三名的資料得到期望結果。但這麼多的手工操作，當資料量大，或者待保留的名次比較多時，使用起來就不那麼方便。

SPL 能遵循自然思路，分步計算：

	A	B
1	=clipboard().import@t()	/從剪貼簿匯入帶標題的銷售資料
2	=A1.groups(Sale;sum(Amount):Total)	/根據銷售彙總，統計出每個銷售的銷售總量 Total
3	=A2.top(-3;Total)	/只取銷售總量前三名
4	=A3.derive(#:Rank)	/增加一個排名欄位
5	=clipboard(A4.export@t())	/將彙總結果放回到剪貼簿

程式碼執行完成後，只需在Excel 中貼上便可得到期望結果。

本文中 SPL 示例，都透過剪貼簿跟 Excel 進行資料互動。首先從 Excel 中將要分析的資料複製到剪貼簿，然後在集算器設計器裡編輯 SPL 指令碼，最後執行程式碼，並在 Excel 中貼上計算結果。

二、併入“其它”

有時候，並不希望統計每一明細資料。而是隻關注其中一部分，其餘的全部合併。比如上例中按產品分組統計時，只關注每種水果的銷量，其他的蔬菜則全部合併統計。

期望結果：

 

這種依賴具體資料的合併要求，使用透視表沒法做出。只能去寫 VBA 宏，用程式針對特定資料分組，麻煩且有一定難度。

SPL 可以採用具體列舉值來進行劃分：

	A	B
1	=clipboard().import@t()	/從剪貼簿匯入銷售記錄
2	=A1.align@an(["Orange","Grape","Apple","Banana","Strawberry"],Product)	/根據水果列表，將銷售記錄分組，並且使用 n 選項，將其他歸為一組
3	=A2.new(Product,~.sum(Amount):Total)	/計算每組總銷量，產生新的序表
4	=A3(A3.len()).Product="Others"	/將最後一項的其他產品名稱改為 Others
5	=clipboard(A3.export@t())	/將結果匯出後放置到剪貼簿

執行結束後，在Excel 中貼上，便可得到期望結果。

三、補充對齊

透視表只能根據資料做常規分組，對於空缺的資料，不會產生相應分組。比如對銷量表按日期分組時，由於部分月份沒有銷售記錄，因此使用透視表分組後得到的結果為：

其中 3 月等沒有資料的月份，也沒有相應分組，結果看起來不整齊，現在想要這樣的結果：

這種需求，透視表仍然沒法做到。寫 VBA 程式碼實現的難度跟上一節類似。

 

SPL 可以使用條件分組，事先預置出分組類別：

	A	B
1	=clipboard().import@t()	/從剪貼簿匯入銷售記錄
2	=A1.run(Date=date(Date,"yyyy/MM/dd"))	/將串型日期欄位轉換為日期型別
3	=A2.align@a(12,month(Date))	/將日期按照固定的 12 個月來分組
4	=A3.new(#:Month,~.sum(Amount):Total)	/統計分組後的資料，產生新的序表
5	=clipboard(A4.export@t())	/將結果匯出後放置到剪貼簿

 

四、按區間分組

如下為學生的考試成績表，現在需要根據分數所屬區間，比如 60 分以下評為 C，90 分以下評為 B，90 以上評為 A，然後按照區間評級統計各分數段的人次。

考生的成績表：

期望結果：

按照成績劃分割槽間段來分組時，定義好值的劃分割槽間後，只需用 pseg 函式便可以將分數分好段，然後統計出各段人次即可。

SPL 程式碼如下：

	A	B
1	=clipboard().import@t()	/從剪貼簿匯入分數表
2	=[0,60,90]	/定義分數分段區間
3	=["C","B","A"]	/定義對應區間名稱
4	=A1.align@a(A2.len(),A2.pseg(Score))	/算出成績所在段，按照段分組
5	=A4.new(A3(#):Level,~.count():Count)	/統計各段人次，產生新序表
6	=clipboard(A5.export@t())	/將匯出結果放置到剪貼簿

 

五、可重疊分組

如下為 2019 年部分國家的 GDP 產值。現在想按已開發國家，發展中國家以及金磚五國分類，計算一下各類的平均 GDP。

2019 年部分國家 GDP 資料 (單位：億美元)：

期望結果：

可以看到，其中的發展中國家跟金磚五國，是有重複資料的。像這種重複劃分的需求，可以使用 enum 列舉函式，透過對數值的列舉，可以很自由地定義分組條件。

SPL 程式碼：

	A	B
1	=clipboard().import@t()	/從剪貼簿匯入國家 GDP 產值表
2	["America","Japan","Germany","Britain","France","Italy","Canada","Korea","Australia"].pos(?)>0	/列舉出已開發國家
3	["China","India","Mexico","SouthAfrica"].pos(?)>0	/列舉發展中國家
4	["Brazil","Russia","India","China","South   Africa"].pos(?)>0	/列舉金磚五國
5	=[A2:A4]	/構造列舉條件序列
6	=["Developed","Developing","BRICS"]	/列舉條件的對應名稱
7	=A1.enum@r(A5,Country)	/使用列舉函式對國家分組，注意如果允許重複數值的分組，要帶上選項 r
8	=A7.new(A6(#):Countries,~.avg(GDP):Average)	/對分組後的數值計算平均 GDP，併產生新序表
9	=clipboard(A8.export@t())	/將結果匯出並放置到剪貼簿

 

 

《 》中還有更多敏捷計算示例。