-
分類彙總
鎖定
分類彙總基本概念
對所有資料分類進行彙總。
分類彙總彙總方案
分類彙總（一）
下面我們針對三種不同的分類彙總需求，為大家介紹不同的解決方案。這三種需求分別是：既想分類彙總又想分類打印、不想分類打印只是想隨時查看各類數據的明細和統計情況、不想打亂正常的流水式數據表格的數據順序而是想隨時查看各類數據的統計結果(此處假定將統計結果保存在另外一個工作表中)。
需求一、既想分類彙總，又想分類打印
解決方案：直接利用Excel內置的“分類彙總”功能來實現。
注意：使用“分類彙總”功能時，一定要按分類對象進行排序！
2、執行“數據→分類彙總”命令，打開“分類彙總”對話框。
圖2
3、將“分類字段”設置為“工程類型”；“彙總方式”設置為“求和”；“選定彙總項”為“面積”和“造價”；再選中“每組數據分頁”選項。最後，確定返回。
4、分類彙總完成。
需求二、不想分類打印，只是想隨時查看各類數據的明細和統計情況
解決方案：利用Excel自身的“自動篩選”功能來實現。
1、任意選中數據表格中的某個單元格，執行“數據→篩選→自動篩選”命令，進入“自動篩選”狀態。
2、分別選中F203、G203單元格（此處假定表格中共有200條數據），輸入公式：=SUBTOTAL(9,F3:F202)和=SUBTOTAL(9,G3:G202)。
小提示：此函數有一個特殊的功能，就是後面進行自動篩選後，被隱藏行的數據不會被統計到其中，達到分類統計的目的。
3、以後需要隨時查看某類（如“經濟住宅”）數據的明細和統計情況時，點擊“工程類型”右側的下拉按鈕，在隨後彈出的快捷菜單中即可。
需求三、如果我們不想打亂正常的流水式數據表格的數據順序，而是想隨時查看各類數據的統計結果（此處假定將統計結果保存在另外一個工作表中）
解決方案：利用Excel的函數來實現。
1、切換到Sheet2工作表中，仿照圖5的樣式，製作好一個統計表格。
2、分別選中B3、C3、D3單元格，輸入公式：=COUNTIF(Sheet1。$E:$E2,A3)、=SUMIF(Sheet1!$E:$E2,A3,Sheet1!$F:$F2)、=SUMIF(Sheet1!$E:$E2,A3,Sheet1!$G:$G2)。
確認以後，各項統計數據即刻呈現在我們的面前。
如果要顯示外部行或列字段的分類彙總，請單擊“分類彙總”下的“自動”選項。
如果要刪除分類彙總，可單擊“分類彙總”下的“無”選項。
如果要使用其他彙總函數，或是要顯示多種類型的分類彙總，可單擊“自定義”右面框中所需的彙總函數。請參閲可用匯總函數的説明。
在基於 OLAP 數據庫中源數據的數據透視表報表中，不能更改分類彙總的彙總函數。
分類彙總（二）
假設您要建立一個其中每個帳户代碼均可出現多次的月度事務列表。在月末，您需要按帳户代碼對數據進行組織和分類彙總。
執行此任務的方法至少有5種。以下是這5種方法的教程。
註釋示例數據集中的帳户號碼在列A中，金額在列B中。數據區域是A2:B100，數據未排序。
方法1：使用創造性的IF語句和“選擇性粘貼”。
步驟如下：
按帳户（列A）對數據進行排序。
在列C中創建一個公式，以持續對每個帳户進行動態彙總。例如，在單元格C2中創建公式：
=IF(A2=A1,C1+B2,B2)
在列D中創建一個公式，以標識特定帳户的最後一個條目，例如，在單元格D2中創建公式：
=IF(A2=A3,FALSE,TRUE)
將C2:D2中的公式向下複製到所有行中。
複製C2:D100。在此區域仍處於選中狀態時，單擊“編輯”菜單上的“選擇性粘貼”，單擊“數值”，然後單擊“確定”，將C2:D100中的公式改為數值。
按列D排序，降序排列。
對於在列D中值為TRUE的行，列A中是帳户號碼的唯一列表，列C中是帳户的最終動態彙總。
優點速度快。只需對寫IF語句感覺敏鋭。
缺點還有更好的方法。
方法2：使用“高級篩選”獲得唯一帳户列表。
這是一種獲得唯一帳户號碼列表的方法：
突出顯示區域A1:A100。
在“數據”菜單上，指向“篩選”，然後單擊“高級篩選”。
單擊“將篩選結果複製到其他位置”。
選中“選擇不重複的記錄”複選框。
選擇要在其中顯示唯一列表的工作表空白部分。將此位置鍵入“複製到”框中。
註釋單擊“將篩選結果複製到其他位置”之前，“複製到”框顯示為灰色。
單擊“確定”。唯一帳户號碼將顯示在輸入的位置。
輸入獲得結果所需的所有進一步操作、數組公式等。
優點比方法1快。無需排序。
缺點此後所需輸入的數組公式將使您頭暈。
方法3：使用“合併計算”命令。
此方法使用“合併計算”命令，這有幾項要求：帳户號碼必須在要彙總的數值字段的左側。每列上方必須有標題。需要對其中包括左列中的帳户號碼和頂部標題的單元格矩形塊指定區域名稱。在本例中，該區域為A1:B100。
突出顯示區域A1:B100。
通過在名稱框（在編輯欄左側）中單擊並鍵入TotalMe之類的名稱，對此區域指定區域名稱。（也可以在“插入”菜單上單擊“名稱”。）
在“數據”菜單上，單擊“合併計算”。
在“引用位置”框中，鍵入區域名稱(TotalMe)。
在“標誌位置”部分，選中“首行”和“最左列”。
單擊“確定”。
優點無需排序。可用一系列鍵盤快捷鍵將其實現：Alt+D+N（區域名稱）、ALT+T、ALT+L、Enter。易於縮放。如果區域包括12個月份列，則結果將是每月的彙總。
缺點如果在同一工作表上再次使用“合併計算”功能，則需要通過使用Delete鍵從“所有引用位置”中清除舊區域名稱。帳户號碼必須位於數值數據的左側。這要比數據透視表稍慢，對於具有超過10,000個記錄的數據集，這會變得很明顯。
方法4：使用“分類彙總”命令。
這是一種很棒的功能。但因為得出的數據處理起來很陌生，所以與“合併計算”相比，您可能不常使用此功能。
按列A排序，降序排列。
選擇數據區域內的任一單元格。
在“數據”菜單上，單擊“分類彙總”。
默認情況下，Excel支持對最後一列數據的分類彙總。這在此例中有效，但您往往必須在“選定彙總項”列表中滾動才能選擇正確的字段。
單擊“確定”。Excel將在每當更改帳户號碼時插入一個新行，並進行分類彙總。
添加彙總後，您將看到小按鈕“1”、“2”和“3”顯示在名稱框下，單擊“2”只查看每個帳户其中有彙總的一行。單擊“3”查看所有行。
優點很棒的功能。極適於打印有彙總和每節後都有彙總的報告。
缺點必須先對數據進行排序。對於大量數據，這可能會很慢。必須使用“定位”命令（“編輯”菜單），然後單擊“定位條件”才能只選擇可見的單元格，將彙總移動到其他位置。必須使用“分類彙總”命令（“數據”菜單），然後單擊“全部刪除”才能恢復原始數據。
方法5：使用“數據透視表”
“數據透視表”是所有解決方案中最全面的。不必對數據進行排序。數值列可位於帳户號碼的左側或右側。可輕易使帳户號碼向下或跨頁排列。
選擇數據區域內的任一單元格。
在“數據”菜單上，單擊“數據透視表和數據透視圖”。
單擊“下一步”接受步驟1中的默認設置。
確保步驟2中的數據區域是正確的（通常是正確的），然後單擊“下一步”。
單擊步驟3中的“佈局”按鈕。（Excel97用户會自動轉到“佈局”作為步驟3。）
在“佈局”對話框中，將“帳户”按鈕從右側拖放到“行”區域。
將“金額”按鈕從右側拖放到“數據”區域。
單擊“確定”。（Excel97用户單擊“下一步”。）
指定是要將結果放在新工作表中還是放在現有工作表的特定部分內，然後單擊“完成”。
優點快速、靈活、強大。即使對於大量數據也很快。
缺點有些令人望而卻步。
分類彙總彙總方法
分類彙總是統計中常用,舉例來説如統計學生成績,及格不及格的歸類,分優良中差等級歸類等,每個單項代碼很好寫,但是如果分類彙總的項目多了,能一種彙總寫一個函數嗎?比如説有些科目60分才算及格,有些科目50分就算;有些老師喜歡分優良中差四等,有些老師卻喜歡分ABCD;不一而足,如果每個都寫一個函數無疑是個編寫和維護惡夢.如果我們用匿名類把分類彙總的規則和分類彙總的過程分別抽象出來,代碼就清晰靈活多了,以下代碼講述了這個過程,代碼比較簡單,這裏就不贅述了,相信大家都能看明白。
首先是數據的基本類Student:
publicclassStudent{
privateStringname;
privateintscore;
publicStudent(Stringname,intscore){
this.name=name;
this.score=score;
}
publicStringgetName(){
returnname;
}
publicvoidsetName(Stringname){
this.name=name;
}
publicintgetScore(){
returnscore;
}
publicvoidsetScore(intscore){
this.score=score;
}
}
然後是用於分類彙總的類,它強制子類實現getKey和getvalue兩個方法：
publicabstractclassClassifyRule{
publicStudentstudent;
publicClassifyRule(){
}
publicvoidsetStudent(Studentstudent){
this.student=student;
}
abstractpublicStringgetKey();
abstractpublicintgetValue();
}
接下來是對Student進行CRUD處理的StudentService類,注意getSum方法,它保留了篩選過程,篩選規則則不在其中:
importjava.util.ArrayList;
importjava.util.Hashtable;
importjava.util.List;
publicclassStudentService{
privateListstudents;
publicStudentService(){
students=newArrayList();
}
publicvoidadd(Studentstudent){
students.add(student);
}
publicHashtablegetSum(ClassifyRulerule){
Hashtableht=newHashtable();
for(Studentstudent:students){
rule.setStudent(student);
Stringkey=rule.getKey();
intvalue=rule.getValue();
if(ht.containsKey(key)){
IntegeroldValue=ht.remove(key);
oldValue+=value;
ht.put(key,oldValue);
}else{
ht.put(key,value);
}
}
returnht;
}
}
最後是測試代碼,注意其中篩選規則的創建:
importjava.util.Hashtable;
importjava.util.Iterator;
publicclassTest{
publicstaticvoidmain(String[]args){
//初始化
StudentServiceservice=newStudentService();
service.add(newStudent("Andy",90));
service.add(newStudent("Bill",95));
service.add(newStudent("Cindy",70));
service.add(newStudent("Dural",85));
service.add(newStudent("Edin",60));
service.add(newStudent("Felix",55));
service.add(newStudent("Green",15));
//60分及格篩選
ClassifyRulerule60=newClassifyRule(){
publicStringgetKey(){
returnstudent.getScore()>=60?"及格":"不及格";
}
publicintgetValue(){
return1;
}
};
System.out.println("60分及格篩選");
printHt(service.getSum(rule60));
//50分及格篩選
ClassifyRulerule50=newClassifyRule(){
publicStringgetKey(){
returnstudent.getScore()>=50?"及格":"不及格";
}
publicintgetValue(){
return1;
}
};
System.out.println("\n50分及格篩選");
printHt(service.getSum(rule50));
//分"優良中差"等級
ClassifyRuleruleCn=newClassifyRule(){
publicStringgetKey(){
Stringretval="";
intscore=student.getScore();
if(score>=90){
retval="優";
}elseif(score>=80){
retval="良";
}elseif(score>=60){
retval="中";
}elseif(score>0){
retval="差";
}
returnretval;
}
publicintgetValue(){
return1;
}
};
System.out.println("\n分優良中差等級篩選");
printHt(service.getSum(ruleCn));
//分"ABCD"等級
ClassifyRuleruleWest=newClassifyRule(){
publicStringgetKey(){
Stringretval="";
intscore=student.getScore();
if(score>=90){
retval="A";
}elseif(score>=80){
retval="B";
}elseif(score>=60){
retval="C";
}elseif(score>0){
retval="D";
}
returnretval;
}
publicintgetValue(){
return1;
}
};
System.out.println("\n分ABCD等級篩選");
printHt(service.getSum(ruleWest));
}
privatestaticvoidprintHt(Hashtableht){
for(Iteratorit=ht.keySet().iterator();it.hasNext();){
Stringkey=(String)it.next();
Integervalue=(Integer)ht.get(key);
System.out.println("Key="+key+"Value="+value);
}
}
}
測試結果如下:
60分及格篩選
Key=及格Value=5
Key=不及格Value=2
50分及格篩選
Key=及格Value=6
Key=不及格Value=1
分優良中差等級篩選
Key=優Value=2
Key=良Value=1
Key=中Value=2
Key=差Value=2
分ABCD等級篩選
Key=AValue=2
Key=DValue=2
Key=CValue=2
Key=BValue=1
