摘? 要：文中對Excel數(shù)據(jù)分類匯總進行探索，運用7個Excel內(nèi)置函數(shù)組合成數(shù)據(jù)篩選公式和匯總公式，實現(xiàn)數(shù)據(jù)分類匯總的自動化應用，計算生成統(tǒng)計匯總表，解決數(shù)據(jù)的多級分類匯總應用。此外，這種函數(shù)組合方式也帶來一種思考：函數(shù)組合應用有類似編程的效果。文章對上述思考進行了細致的分析與驗證，在不借助編程的情況下，實現(xiàn)了數(shù)據(jù)分類匯總的自動化應用。

關鍵詞：數(shù)據(jù)匯總;數(shù)據(jù)分類匯總;數(shù)據(jù)多級分類匯總

Abstract：In this paper，Excel data classification and aggregation are explored. Seven excel built-in functions are combined to form data filtering formula and aggregation formula. The automatic application of data classification and aggregation is realized，and the statistical aggregation table is calculated and generated to solve the multi-level classification and aggregation application of data. In addition，this kind of function combination method also brings a kind of thinking：the function combination application has the similar programming effect. This paper makes a detailed analysis and verification of the above thinking，and realizes the automatic application of data classification and aggregation without the aid of programming.

Keywords：data aggregation;data classification and aggregation;data multi-level classification and aggregation

0? 引? 言

Excel是常用的電子表辦公軟件，它表面是制作電子表格的工具，其實還有強大的計算功能，通過內(nèi)置的函數(shù)擴展了其功能應用，可以完成許多復雜的數(shù)據(jù)運算，是管理公司用戶和個人財務統(tǒng)計數(shù)據(jù)、繪制各種專業(yè)化表格等工作的得力助手。筆者長期從事與林業(yè)相關的數(shù)據(jù)處理工作，如二類調(diào)查、編制森林經(jīng)營方案、項目規(guī)劃、調(diào)查設計和檢查驗收等數(shù)據(jù)的處理工作，工作中發(fā)現(xiàn)Excel只提供了簡單的數(shù)據(jù)分類匯總功能，而對數(shù)據(jù)的多級分類匯總，卻沒有提供直接支持，只能通過一步步篩選記錄來統(tǒng)計數(shù)據(jù)或用編程方式解決該問題。筆者經(jīng)過一段時間的摸索，對Excel內(nèi)置函數(shù)進行分析研究，終于利用相關函數(shù)組合成篩選公式和匯總公式，生成統(tǒng)計匯總表，實現(xiàn)數(shù)據(jù)分類匯總的自動化應用。現(xiàn)在把函數(shù)組合匯總數(shù)據(jù)的方法整理，提供一種解決問題的思路，以期對同行或有數(shù)據(jù)多級分類匯總需求的用戶有所幫助。下文對數(shù)據(jù)分類匯總的概念做陳述，以及介紹公式的函數(shù)組合方式和錄入步驟，最后驗證數(shù)據(jù)匯總的正確性。各函數(shù)的詳細語法和舉例應用，限于篇幅，文中未做詳述，可以通過百度搜索或相關書籍中查看，當然，該方法可能也存在一定缺陷，在今后的工作中會進一步對該方法存在的不足進行研究。

1? 提出問題

Excel數(shù)據(jù)匯總：對表數(shù)據(jù)進行累加或匯總;

Excel數(shù)據(jù)分類匯總：對表數(shù)據(jù)的某個字段或列，按類別來統(tǒng)計數(shù)據(jù);

Excel數(shù)據(jù)多級分類匯總：涉及表數(shù)據(jù)的多個字段（或列）、字段（或列）的多個類別，進行數(shù)據(jù)分類統(tǒng)計匯總，舉例：

一級分類匯總（簡稱“一級匯總”）：如某林場要統(tǒng)計各[工區(qū)][面積];

二級分類匯總（簡稱“二級匯總”）：如某林場要統(tǒng)計各[工區(qū)]分[起源]面積;

三級分類匯總（簡稱“三級匯總”）：如某林場要統(tǒng)計各[工區(qū)]分[起源]分[齡級]面積;

四級分類匯總（簡稱“四級匯總”）：如某林場要統(tǒng)計各[工區(qū)]分[起源]分[齡級]分[立地質(zhì)量等級]面積;

五級分類匯總、數(shù)據(jù)六級分類匯總，依次類推。

用自動篩選的方法，統(tǒng)計數(shù)據(jù)所需次數(shù)的情況為（以工區(qū)10個，起源2種，齡級5個，立地質(zhì)量等級4個為例），一級匯總統(tǒng)計次數(shù)：10次;二級匯總統(tǒng)計次數(shù)：10×2=20次;三級匯總統(tǒng)計次數(shù)：10×2+2×5=30次;四級匯總面積的統(tǒng)計次數(shù)：10×2+2×5+5×4=50次;五級、六級統(tǒng)計的次數(shù)將會更多。

從上文統(tǒng)計次數(shù)據(jù)可以看出，統(tǒng)計數(shù)據(jù)量少時，一級匯總的工作量尚可接受，二級匯總時，統(tǒng)計次數(shù)明顯增多，三級匯總時統(tǒng)計次數(shù)便達到100次，工作量過大;以上列出的統(tǒng)計次數(shù)只是獲得結(jié)果的次數(shù)，其實操作過程中步驟更加煩瑣，并且有的數(shù)據(jù)不能直接生成表，效率低下。由此，筆者產(chǎn)生了一個設想：能否通過Excel函數(shù)組合成公式，在單元格中輸入公式，實現(xiàn)數(shù)據(jù)的分類匯總？

2? 分析問題

2.1? 數(shù)據(jù)分類匯總的過程分析

Excel內(nèi)置函數(shù)功能非常強大，通過組合方式可以方便、快速、有效地解決數(shù)據(jù)的多級分類匯總的問題。以三級匯總為例，對比匯總數(shù)據(jù)前的表和匯總數(shù)據(jù)后的表，進行分析實現(xiàn)過程。

2.1.1? 必須先篩選出符合多條件的不重復記錄

如表1所示，同時具有相同字段“鄉(xiāng)鎮(zhèn)”“培育樹種”“作業(yè)類型”類別的各有2條記錄，分類匯總之后，只需要保留各一行記錄，如表2所示。

2.1.2? 要對符合多條件的記錄進行面積匯總

如表1所列的塊號A、B的作業(yè)面積“3.3”“2.0”匯總到表2所列的序號1的作業(yè)面積“5.3”中，表1所列的塊號C、D的作業(yè)面積“4.0”“3.3”匯總到表2所列的序號2的作業(yè)面積“7.3”中。

通過對上面數(shù)據(jù)多級分類匯總過程簡單分析，匯總過程分為兩個步驟：第一步，先用函數(shù)組合公式篩選出符合多條件的不重復記錄;第二步，用函數(shù)組合公式對符合多條件的記錄進行數(shù)據(jù)匯總。

2.2? 函數(shù)組合應用

2.2.1? 函數(shù)組合應用將用到7個函數(shù)

（1）index（）函數(shù)，語法：INDEX（array，row_num，column_num）;

（2）small（）函數(shù)，語法：SMALL（array，k）;

（3）match（）函數(shù)，語法：MATCH（lookup_value，lookup _array，match_type）;

（4）sum（）函數(shù)，語法：SUM（number1，number2，

（5）offset（）函數(shù)，語法：OFFSET（reference，rows，cols，height，width）;

（6）if（）函數(shù)，語法：IF（logical_test，value_if_true，value_if_false）;

（7）row（）函數(shù)，語法：ROW（reference）。

2.2.2? 函數(shù)組合應用：篩選公式和匯總公式

（1）篩選公式：“=INDEX（xyz0，SMALL（IF（MATCH（xyza&"|"&xyzb&"|"&xyzc，xyza&"|"&xyzb&"|"&xyzc，0）=ROW（xyzb），4^8），ROW（xyz1）））”;用于篩選記錄，篩選出符合多條件的不重復記錄，其中，xyz0、xyza、xyzb、xyzc、xyz1為變量。

說明：SMALL（）、IF（）、MATCH（）組合返回同時符合三條件：yza&"|"&xyzb&"|"&xyzc的最小當前值，用于篩選不重復記錄，如得到表1所列的相同名稱（“鄉(xiāng)鎮(zhèn)”“培育樹種”“作業(yè)類型”）的排序的第一條記錄：“AAA鎮(zhèn)、香樟、改培”，向下填充得到第二條記錄：“BBB鎮(zhèn)、火力楠、新造”，直到出現(xiàn)“0”為止，完成所有不重復記錄篩選，此公式功能是篩選出不重復記錄;

（2）匯總公式：“=SUM（IF（（xyza2=三級匯總！A2）* （xyzb2=三級匯總！B2）*（xyzc2=三級匯總！C2），（xyzd2）， 0））”;用于第二步數(shù)據(jù)匯總，對符合多條件的記錄進行數(shù)據(jù)匯總，其xyza2、xyzb2、xyzc2、xyzd2為變量，而“三級匯總！A2”“三級匯總！B2”“三級匯總！C2”為單元格引用。

說明：對同時符合三條件：（xyza2=三級匯總！A2）、（xyzb2=三級匯總！B2）、（xyzc2=三級匯總！C2）的（xyzd2）列數(shù)據(jù)進行自動匯總，如表2所列匯總面積數(shù)據(jù)“5.3”，向下填充直到完成所有數(shù)據(jù)匯總，如“7.3”，此公式功能是匯總符合條件的數(shù)據(jù)。

小結(jié)：用篩選公式可以篩選出符合三個條件的不重復記錄;用匯總公式可以對符合條件的數(shù)據(jù)進行匯總;二個公式可以實現(xiàn)數(shù)據(jù)分類匯總，匯總結(jié)果生成新表。

3? 解決問題

上面公式看起來，奧斯簡單，其實是為了公式的函數(shù)組合直觀明了，把部分中間計算值或引用，采用變量的方法來分解處理了，下面用定義名稱的方式輸入公式，也以三級匯總為例。

3.1? 新建命名表

建立兩個Excel空表：命名為表一、三級匯總，如圖1所示。

3.2? 定義名稱及輸入引用

3.2.1? “定義名稱”輸入公式的方法、步驟

以Excel2003為例，講解“定義名稱”輸入公式的方法、步驟（其他版本的相應操作差異不大）：

第一步：按順序點擊Excel主窗口菜單→插入→名稱→定義，打開定義名稱窗口;

第二步：“在當前工作簿中的名稱”，輸入定義名稱，如：“xyz0”;

第三步：“引用位置”，輸入公式，如：“=表一！A：A”;

第四步：點擊添加，完成一項定義名稱添加，最后點擊確定，繼續(xù)下一名稱輸入。

需要注意的是：輸定義名稱“unabc”前，先單擊"三級匯總"表A1單元格，目的是與公式引用相對應。輸定義名稱“sum”前，先單擊"三級匯總"表D2單元格，目的是與公式引用相對應;

3.2.2? 定義名稱及引用輸入

（1）錄入數(shù)據(jù)篩選公式：

B11，定義名稱：“unabc”;引用位置輸入：“=INDEX（xyz0，SMALL（IF（MATCH（xyza&"|"&xyzb&"|"&xyzc，xyza&"|"&xyzb&"|"&xyzc，0）=ROW（xyzb），4^8），ROW（xyz1）））”;

B12，定義名稱：“xyz0”;引用位置輸入：“=表一！A：A”;

B13，定義名稱：“xyz1”;引用位置輸入：“=表一！A1”;

B14，定義名稱：“xyza”;引用位置輸入：“=OFFSET（（表一！$A$1），0，0，COUNTA（表一！$A：$A），1）”;

B15，定義名稱：“xyzb”;引用位置輸入：“=OFFSET（（表一！$B$1），0，0，COUNTA（表一！$B：$B），1）”;

B16，定義名稱：“xyzc”;引用位置輸入：“=OFFSET（（表一！$C$1），0，0，COUNTA（表一！$C：$C），1）”;

需要注意：unabc為多條件篩選不重復記錄公式（如"鄉(xiāng)鎮(zhèn)"、"培育樹種"、"作業(yè)類型"）。

（2）錄入數(shù)據(jù)匯總公式：

B21，定義名稱：“sum”;引用位置輸入：“=SUM （IF（（xyza2=三級匯總！A2）*（xyzb2=三級匯總！B2）*（xyzc2=三級匯總！C2），（xyzd2），0））”;

B22，定義名稱：“xyza2”;引用位置輸入：“=OFFSET （（表一！$A$2），0，0，COUNTA（表一！$A：$A），1）”;

B23，定義名稱：“xyzb2”;引用位置輸入：“=OFFSET （（表一！$B$2），0，0，COUNTA（表一！$B：$B），1）”;

B24，定義名稱：“xyzc2”;引用位置輸入：“=OFFSET （（表一！$C$2），0，0，COUNTA（表一！$C：$C），1）”;

B25，定義名稱：“xyzd2”;引用位置輸入：“=OFFSET （（表一！$D$2），0，0，COUNTA（表一！$D：$D），1）”;

公式輸入完成后，如圖2所示，可檢查、更正輸入的錯誤。

3.3? “三級匯總”表單元格輸入公式

在“三級匯總”表中的A1、B1、C1、D1單元格中分別輸入：“=unabc”，D2單元格輸入：“=sum”，并按回車鍵，A1、B1、C1和D2單元格分別下拉（暫定30行，計算時，篩選或匯總結(jié)果出現(xiàn)值為“0”時為止），表中單元格輸入公式后的情況如圖3所示（不是按回車鍵后的結(jié)果）。小結(jié)：當“表一”輸入完數(shù)據(jù)后，在“三級匯總”表中會自動匯總數(shù)據(jù)。

4? 數(shù)據(jù)分類匯總結(jié)果驗證和應用

在“表一”表中A1、B1、C1、D1單元格開始，各列添加數(shù)據(jù)，如表3所示。

“表一”輸入完數(shù)據(jù)后（也可以復制粘貼數(shù)據(jù)到表中），打開“三級匯總”表，可以看到自動完成了數(shù)據(jù)的分類匯總，如表4所示。

5? 函數(shù)組合擴展應用

5.1? “一級匯總”應用

使用“一級匯總”，比Excel內(nèi)置的分類匯總功能更加方便實用，可直接生成統(tǒng)計表。

5.2? “二級匯總”應用

使用“三級匯總”的篩選條件改為2個即可。

5.3? “四級匯總”應用

“四級匯總”用4個條件，“五級匯總”用5個條件。簡單的理解就是幾級匯總就改為幾個篩選條件就可以完成。

5.4? 多列數(shù)據(jù)要求同時分別匯總應用

多列數(shù)據(jù)要求同時分別匯總應用可以使一列數(shù)據(jù)匯總做一個匯總公式，如“=sum”，多列要匯總數(shù)據(jù)做多個匯總公式，如“=sum1”“=sum2”“=sum3”等以此類推。

6? 結(jié)? 論

Excel只提供了簡單的數(shù)據(jù)分類匯總功能，而對數(shù)據(jù)的多級分類匯總，沒有提供直接支持，在工作中通過一步步篩選的方法，獲得統(tǒng)計結(jié)果，效率低下。但同時，Excel提供了豐富的內(nèi)置函數(shù)，運用函數(shù)組合方式，在不借助編程的情況下，完全可以解決復雜的數(shù)據(jù)分類匯總應用難題，實現(xiàn)數(shù)據(jù)分類匯總的自動化應用。本文驗證了應用函數(shù)組合實現(xiàn)類似于編程功能的思路，希望可以為從事數(shù)據(jù)統(tǒng)計工作的相關人員提供借鑒。

參考文獻：

[1] 凌弓創(chuàng)作室.妙“技”輕松學：Excel公式與函數(shù)實戰(zhàn)經(jīng)典技巧 [M].北京：科學出版社，2012.

[2] 鄧芳.Excel高效辦公：數(shù)據(jù)處理與分析：修訂版 [M].北京：人民郵電出版社，2012.

[3] 周慶麟，胡子平.Excel數(shù)據(jù)分析思維、技術(shù)與實踐 [M].北京：北京大學出版社，2019.

[4] 李東博.中文版EXCEL2007寶典 [M].北京：電子工業(yè)出版社，2008.

[5] 姬麗霞，張麗君.ExcelVBA高效辦公全能手冊 [M].北京：中國鐵道出版社，2009.

作者簡介：連春來（1970—），男，漢族，福建武平人，工程師，研究方向：數(shù)據(jù)處理（Visual FoxPro、Excel、ArcGIS等）。