基本介紹
基本概念,分類匯總,匯總方法,分類匯總,
基本概念
對所有資料分類進行匯總。
分類匯總
Excel數據分類匯總滿足多種數據整理需求。
下面我們針對三種不同的分類匯總需求，為大家介紹不同的解決方案。這三種需求分別是：既想分類匯總又想分類列印、不想分類列印只是想隨時查看各類數據的明細和統計情況、不想打亂正常的流水式數據表格的數據順序而是想隨時查看各類數據的統計結果(此處假定將統計結果保存在另外一個工作表中)。
需求一、既想分類匯總，又想分類列印
解決方案：直接利用Excel內置的“分類匯總”功能來實現。
注意：使用“分類匯總”功能時，一定要按分類對象進行排序！
2、執行“數據→分類匯總”命令，打開“分類匯總”對話框。
圖2
3、將“分類欄位”設定為“工程類型”；“匯總方式”設定為“求和”；“選定匯總項”為“面積”和“造價”；再選中“每組數據分頁”選項。最後，確定返回。
4、分類匯總完成。
需求二、不想分類列印，只是想隨時查看各類數據的明細和統計情況
解決方案：利用Excel自身的“自動篩選”功能來實現。
1、任意選中數據表格中的某個單元格，執行“數據→篩選→自動篩選”命令，進入“自動篩選”狀態。
小提示：此函式有一個特殊的功能，就是後面進行自動篩選後，被隱藏行的數據不會被統計到其中，達到分類統計的目的。
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)。
3、同時選中B3、C3、D3單元格，將滑鼠移至D3單元格右下角成細十字線狀時，按住左鍵向下拖拉至D10單元格，將上述公式複製至B4至D10單元格區域中。
4、選中B11單元格，輸入公式：=SUM(B3:B10)，並仿照上面的操作，將此公式複製到C11和D11單元格中。
確認以後，各項統計數據即刻呈現在我們的面前。
如果要顯示外部行或列欄位的分類匯總，請單擊“分類匯總”下的“自動”選項。
如果要顯示內部行或列欄位的分類匯總，請單擊“分類匯總”下的“自定義”選項，然後單擊右面框中的某個匯總函式。而對基於 OLAP 資料庫中源數據的數據透視表報表而言，“自定義”選項無效。
如果要刪除分類匯總，可單擊“分類匯總”下的“無”選項。
如果要使用其他匯總函式，或是要顯示多種類型的分類匯總，可單擊“自定義”右面框中所需的匯總函式。請參閱可用匯總函式的說明。
在基於 OLAP 資料庫中源數據的數據透視表報表中，不能更改分類匯總的匯總函式。
匯總方法
分類匯總是統計中常用,舉例來說如統計學生成績,及格不及格的歸類,分優良中差等級歸類等,每個單項代碼很好寫,但是如果分類匯總的項目多了,能一種匯總寫一個函式嗎?比如說有些科目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{
privateList<Student>students;
publicStudentService(){
students=newArrayList<Student>();
}
publicvoidadd(Studentstudent){
students.add(student);
}
publicHashtable<String,Integer>getSum(ClassifyRulerule){
Hashtable<String,Integer>ht=newHashtable<String,Integer>();
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
分類匯總
假設您要建立一個其中每個帳戶代碼均可出現多次的月度事務列表。在月末，您需要按帳戶代碼對數據進行組織和分類匯總。
執行此任務的方法至少有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用戶單擊“下一步”。）
指定是要將結果放在新工作表中還是放在現有工作表的特定部分內，然後單擊“完成”。
優點快速、靈活、強大。即使對於大量數據也很快。
缺點有些令人望而卻步。
