在 Excel 中，按年、月、日和周分類(lèi)匯總用數(shù)據(jù)透視表比較簡(jiǎn)單方便，進(jìn)行簡(jiǎn)單的設(shè)置就可以返回它們的匯總結(jié)果；按年、月和日匯總可以直接顯示它們，但按周匯總只能用日期表示周；而用公式匯總可以實(shí)現(xiàn)把屬于每周的日期轉(zhuǎn)為第幾周。

用公式實(shí)現(xiàn)按年、月和周分類(lèi)匯總，主要用 Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row 和 Text 等函數(shù)，例如按年和月匯總用前四個(gè)函數(shù)，而按周匯總復(fù)雜一些，需要用 Sum（或 SumProduct) + Value + WeekNum + Row 實(shí)現(xiàn)。

一、Excel 分類(lèi)匯總用數(shù)據(jù)透視表匯總

（一）按年和月匯總

1、創(chuàng)建數(shù)據(jù)透視表。選中表格其中一個(gè)單元格，選擇“插入”選項(xiàng)卡，單擊屏幕左上角的“數(shù)據(jù)透視表”，打開(kāi)“創(chuàng)建數(shù)據(jù)透視表”窗口，保持默認(rèn)設(shè)置，單擊“確定”，則在新工作表創(chuàng)建一個(gè)數(shù)據(jù)透視表；勾選字段“服裝名稱(chēng)、日期和營(yíng)業(yè)額”，已經(jīng)自動(dòng)按“年”匯總好了每種衣服的營(yíng)業(yè)額；操作過(guò)程步驟，如圖1所示：

圖1

2、按月匯總。選中其中一個(gè)年份的單元格（如 A5），選擇“分析”選項(xiàng)卡，單擊“組字節(jié)”，打開(kāi)“組合”窗口，“步長(zhǎng)”僅選擇“年和月”（單擊“季度”取消選擇），單擊“確定”，則按年和月匯總出了每種服裝的營(yíng)業(yè)額；如果年份要顯示到列，把“年”從“行區(qū)間”拖到“列區(qū)間”；操作過(guò)程步驟，如圖2所示：

圖2

（二）按周匯總

1、以把上例的按年和月分類(lèi)匯總改為按周為例。選中年份的其中一個(gè)單元格（如 B4），選擇“分析”選項(xiàng)卡，單擊“組字節(jié)”，打開(kāi)“組合”窗口，把“起始于”由 2018/11/1 改為 2018/10/29，單擊“步長(zhǎng)”下的“年和月”取消選擇，單擊“日”選中它，“天數(shù)”由灰色變?yōu)榭蛇x，把 1 改為 7，單擊“確定”，則每件衣服按“周”匯總；操作過(guò)程步驟，如圖3所示：

圖3

2、“起始于”自動(dòng)填入的日期 2018/11/1 是表格的起始日期，但不是周一，因此要把它改為“周一”的日期 2018/10/29，這樣就能實(shí)現(xiàn)按周匯總。

（三）對(duì)匯總結(jié)果排序

1、對(duì)不同服裝的營(yíng)業(yè)額按“周”排序。右鍵其中一件衣服的營(yíng)業(yè)額，如 B5，在彈出的菜單中，依次選擇“排序”→ 升序，則每件衣服每周的營(yíng)業(yè)額都按“升序”排列；操作過(guò)程步驟，如圖4所示：

圖4

2、對(duì)每件衣服的“營(yíng)業(yè)額”按“升序”排序。右鍵任意一件衣服的匯總營(yíng)業(yè)額，如 B4，在彈出的菜單中，同樣依次選擇“排序”→ 升序，則每件衣服按“營(yíng)業(yè)額”升序排列，每件衣服的周營(yíng)業(yè)額也隨之排序；操作過(guò)程步驟，如圖5所示：

圖5

有關(guān)更多數(shù)據(jù)透視表的操作，請(qǐng)參考《Excel數(shù)據(jù)透視表怎么做的15個(gè)操作方法，含非重復(fù)計(jì)數(shù)和百分比統(tǒng)計(jì)及四個(gè)區(qū)域間的用途》、《Excel數(shù)據(jù)透視表自動(dòng)生成分頁(yè)報(bào)表和把日?qǐng)?bào)表合成月報(bào)表及顯示報(bào)表篩選頁(yè)為灰色的解決辦法》和《Excel多重合并計(jì)算數(shù)據(jù)區(qū)域用于合并多個(gè)表格到數(shù)據(jù)透視表，含把地區(qū)和城市的銷(xiāo)量與營(yíng)業(yè)額用多頁(yè)字段合并》。

二、Excel 分類(lèi)匯總用公式

（一）按年匯總

1、假如要匯總各種服裝每年的營(yíng)業(yè)額。雙擊 F2 單元格，把公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 復(fù)制到F2，按 Ctrl + Shift + 回車(chē)，返回“羽絨服”2018 年的營(yíng)業(yè)額 21232；把鼠標(biāo)移到 F2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)楹谏�加�?hào)后，按住左鍵，往右拖，一直拖到 I2，則匯總出剩余服裝 2018 年的營(yíng)業(yè)額；再把鼠標(biāo)移到 I2 右下角的單元格填充柄上，同樣方法往下拖，則匯總出 2019 年各種服裝的營(yíng)業(yè)額；操作過(guò)程步驟，如圖6所示：

圖6

2、公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 說(shuō)明：

A、$A$2 表示對(duì)列和行都絕對(duì)引用，當(dāng)往右拖時(shí)，A2 不會(huì)變?yōu)?B2、C2 等；當(dāng)往下拖時(shí)，A2 不會(huì)變?yōu)?A3、A4 等，其它的有兩個(gè) $ 的與$A$2 是一個(gè)意思。

B、$A$2:$A$25 以數(shù)組形式返回 A2 至 A25 的“服裝名稱(chēng)”，之所以 A2 和 A25 都用絕對(duì)引用，是要確保往右或往下拖時(shí)，始終返回A2:A25 中的“服裝名稱(chēng)”。

C、$A$2:$A$25=F$1 為 Sum 的第一個(gè)條件，意思是把 A2:A25 中的每種“服裝名稱(chēng)”與 F1 中的“羽絨服”比較，如果相等，返回 True，否則返回 False；第一次取 A2（即“羽絨服”），它們相等，返回 True；第二次取 A3（即“風(fēng)衣”），它們不相等，返回 False；其它的以此類(lèi)推，最后返回?cái)?shù)組 {TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}。

D、$B$2:$B$25 以數(shù)組形式返回 B2 至 B25 的日期。YEAR($B$2:$B$25) 用于取 B2:B25 日期中的年份；第一次取 B2（即 2018/11/1），YEAR(B2) 返回 2018；第二次取 B3（即 2018/11/2），YEAR(B3) 返回 2018；其它的以此類(lèi)推，最后返回 {2018;2018;2018;...;2019;2019;2019}。

E、E2 中的值為 2018，YEAR($B$2:$B$25)=$E2 變?yōu)?{2018;2018;...;2019}=2018，然后依次從數(shù)組中取出每個(gè)元素與 2018 比較，如果相等，返回 True，否則返回 False，最后返回 {TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}。

F、則公式變?yōu)?=SUM({TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}*{TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}*$C$2:$C$25)，接著，把兩個(gè)數(shù)組中對(duì)應(yīng)元素相乘，相乘時(shí)，TRUE被轉(zhuǎn)為 1、FALSE 被轉(zhuǎn)為 0，則公式變?yōu)?=SUM({1;0;0;...;0;0;0}*$C$2:$C$25)。

G、$C$2:$C$25 為求和區(qū)域，它以數(shù)組形式返回 C2:C25 中的營(yíng)業(yè)額，即返回 {5221;5202;4622;...;5889;5198;5299}。

H、公式進(jìn)一步變?yōu)?=SUM({1;0;0;...;0;0;0}*{5221;5202;4622;...;5889;5198;5299})，接著再把兩個(gè)數(shù)組對(duì)應(yīng)元素相乘，公式變?yōu)?SUM({5221;0;0;...;0;0;0})，最后對(duì)數(shù)組求和，結(jié)果為 21232。

提示：上面的公式也可以用 SumIfs、SumProduct 等函數(shù)實(shí)現(xiàn)，用這兩個(gè)函數(shù)不需按“Ctrl + Shift + 回車(chē)”求值，只需按回車(chē)即可，之所以用 Sum 要按“Ctrl + Shift + 回車(chē)”，是因?yàn)楣�式為�?shù)組公式。把上面的公式改為用 SumProduct 實(shí)現(xiàn)，可以這樣寫(xiě)： =SUMPRODUCT(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25)，用 SumIfs 實(shí)現(xiàn)見(jiàn)下面的按月匯總。

（二）按月匯總

1、以匯總每種服裝每年每月的營(yíng)業(yè)額為例。雙擊 G2 單元格，把公式 =SUM(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25) 復(fù)制到 G2，按 Ctrl + Shift + 回車(chē)，返回“羽絨服”2018 年 11 月的營(yíng)業(yè)額；同樣用往右的方法，返回 2018 年 11 月其它服裝的營(yíng)業(yè)額，用往下拖的方法返回每種服裝 2018 年剩余月份的營(yíng)業(yè)額；操作過(guò)程步驟，如圖7所示：

圖7

2、公式 =SUM(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25) 說(shuō)明：

A、公式與上面的“按年匯總”是一個(gè)意思，這里只是多加了一個(gè)“年份”的條件 YEAR($B$2:$B$25)=$E$2 和用 Substitute 函數(shù)取代月份中的“月”字，以獲取數(shù)字用于計(jì)算。

B、--SUBSTITUTE($F2,"月","") 的意思是：用空 "" 取代 F2 中“11月”的“月”字，它返回文本 "11"，再用 -- 把 "11" 轉(zhuǎn)為數(shù)值 11。

3、以上公式也可以用 SumIfs 和 SumProduct 函數(shù)實(shí)現(xiàn)，它們的寫(xiě)法如下：

=SUMPRODUCT(($A$2:$A$25=G$1)*(YEAR($B$2:$B$25)=$E$2)*(MONTH($B$2:$B$25)=--SUBSTITUTE($F2,"月",""))*$C$2:$C$25)

=SUMIFS($C$2:$C$25,$A$2:$A$25,G$1,$B$2:$B$25,">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1,$B$2:$B$25,"<="&DATE($E$2,SUBSTITUTE($F2,"月","")+1,0))

這兩個(gè)公式直接按回車(chē)即可。SumProduct 公式與 Sum 公式是一個(gè)意思，不再解析，下面只解析 SumIfs 公式：

A、$C$2:$C$25 為求和區(qū)域；$A$2:$A$25,G$1 為第一個(gè)條件區(qū)域/條件對(duì)，$A$2:$A$25 為條件區(qū)域，G$1 為條件，意思是：在 A2:A25 中查找 G1 中的“羽絨服”。

B、$B$2:$B$25,">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 為第二個(gè)條件區(qū)域/條件對(duì)，用于在 B2:B25 中查找大于等于 2018/11/1 的所有日期；SUBSTITUTE($F2,"月","") 用于用空 "" 取代 F2 中“11月”的“月”字，它返回文本 "11"；

E2 為 2018，則 ">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 變?yōu)?">="&DATE(2018,"11",0)+1，接著 Date 函數(shù)把 "11" 轉(zhuǎn)為數(shù)值 11，并返回 2018 年 11 月 0 天表示的序號(hào) 43404；

則 ">="&DATE(2018,"11",0)+1 變?yōu)?">="&43404+1，再用 & 把 ">=" 與 43405 連接起來(lái)，即 ">=43405"，意思是大于等于 11 月的第一天，因?yàn)?2018 年 11 月的第 0 天為 2018 年 10 月 31 日，再加 1，恰好是 2018年 11 月 1 日。

C、$B$2:$B$25,"<="&DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 為第三個(gè)條件區(qū)域/條件對(duì)，與第二個(gè)條件區(qū)域/條件對(duì)是一個(gè)意思，用于在 B2:B25 中查找小于等于 2018/11/30 的所日期；

SUBSTITUTE($F2,"月","") 返回 "11"，DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 變?yōu)?DATE(2018,"11"+1,0)，進(jìn)一步計(jì)算變?yōu)?DATE(2018,12,0)，意思是 2018 年 12 月第 0 天，即 2018 年 11 月 30 日。

D、則公式變?yōu)?=SUMIFS($C$2:$C$25,$A$2:$A$25,G$1,$B$2:$B$25,">=2018/11/1",$B$2:$B$25,"<=2018/11/30")，意思是：匯總 A2:A25中為“羽絨服”與“日期”大于等于 2018/11/1 且小于等于 2018/11/30 的所有營(yíng)業(yè)額。

（三）按周匯總

1、假如要把 11 月各種服裝的營(yíng)業(yè)額按周匯總。把 11 月第一天的日期 2018/11/1 輸入到 E1 單元格，雙擊 E2，把公式 =TEXT(WEEKNUM(E$1 + 7*(ROW(A1)-1),2)-WEEKNUM(--TEXT(E$1,"e-m"),2)+1,"第[dbnum1]0周") 復(fù)制到 E2，按回車(chē)，返回 11 月第一周；把鼠標(biāo)移到 E2 右下角的單元格填充柄上，用往下拖的方法返回 11 月剩余的周數(shù)；雙擊 F2，把公式 =SUM((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17) 復(fù)制到 F2，按 Ctrl + Shift + 回車(chē)，返回 11 月第一周的營(yíng)業(yè)額，同樣用往下拖的辦法返回剩余周的營(yíng)業(yè)額；操作過(guò)程步驟，如圖8所示：

圖8

2、公式說(shuō)明：

（1）=TEXT(WEEKNUM(E$1 + 7*(ROW(A1)-1),2)-WEEKNUM(--TEXT(E$1,"e-m"),2)+1,"第[dbnum1]0周")

A、ROW(A1) 用于 返回 A1 的行號(hào) 1，7*(ROW(A1)-1 用于實(shí)現(xiàn)每往下拖一個(gè)單元格，日期累加到下一周；當(dāng)公式在 E2 時(shí)，7*(1-1)=0,當(dāng)公式在 E3 時(shí)，A1 變 A2，ROW(A2) 返回 2，7*(2-1)=7；其它的以此類(lèi)推。

B、WEEKNUM(E$1 + 7*(ROW(A1)-1),2) 用于返回指定日期在一年中是第幾周，E$1 + 7*(ROW(A1)-1) 為日期，參數(shù) 2 表示以“周一”為每周的第一天；

以公式在 E2 為例：E1 為 2018/11/1，7*(ROW(A1)-1) 返回 0，則 WEEKNUM(E$1 + 7*(ROW(A1)-1),2) 變 WEEKNUM(E$1 + 0,2)，它返回44，即 2018/11/1 是一年中的第 44 周。

C、e-m 表示以年月顯示日期，e 表示年，相當(dāng)于 yyyy；m 表示月，相當(dāng) mm；e 和 m 的位置可以調(diào)換；TEXT(E$1,"e-m") 表示以年月返回 E1 中的 2018/11/1，即返回 "2018-11"；--"2018-11" 中 -- 表示把文本轉(zhuǎn)為日期，它相當(dāng)于 Value 函數(shù)。

D、則 WEEKNUM(--TEXT(E$1,"e-m"),2) 變?yōu)?WEEKNUM(--"2018-11",2)，進(jìn)一步計(jì)算返回 44。

E、"第[dbnum1]0周" 用于返回漢字表示的第幾周，dbnum1 表示把數(shù)字轉(zhuǎn)為漢字，0 表示把 [dbnum1] 顯示為數(shù)字。另外，dbnum2 表示把數(shù)字轉(zhuǎn)為大寫(xiě)漢字。

F、則公式變?yōu)?=TEXT(44-44+1,"第[dbnum1]0周")，進(jìn)一步計(jì)算返回“第一周”；當(dāng)公式在 E3 是，公式變?yōu)?=TEXT(45-44+1,"第[dbnum1]0周")，返回“第二周”，其它的以此類(lèi)推。

（2）=SUM((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)

A、WEEKNUM(--B$2:B$17,2) 用于以數(shù)組形式返回 B2:B17 的日期在一年中是第幾周；第一次取出 B2（即 2018/11/1），WEEKNUM(--B2,2)，返回 44；第二次取出 B3（即 2018/11/2），WEEKNUM(--B3,2)，返回 44；其它的以此類(lèi)推，最后返回{44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}。

B、WEEKNUM(E$1+7*(ROW(A1)-1) 上面已經(jīng)解釋過(guò)，當(dāng)公式在 F2 時(shí)，它返回 44；當(dāng)公式在 F3 時(shí)，它返回 45。

C、則公式變?yōu)?=SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=44)*C$2:C$17)，接著把數(shù)組中的每個(gè)元素與 44 比較，如果相等返回 True，否則返回 False。

D、則公式變?yōu)?=SUM({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*C$2:C$17)，進(jìn)一步計(jì)算，把 C2:C17 中的每個(gè)數(shù)值與數(shù)組中的對(duì)應(yīng)元素相乘，相乘時(shí)，TRUE 和 FALSE 的取值，上面已經(jīng)解釋。

E、公式進(jìn)一步變?yōu)?=SUM({5221;5202;4622;5140;0;0;0;0;0;0;0;0;0;0;0;0})，最后對(duì)數(shù)組求和，最后返回 20185。

F、當(dāng)公式在 F3 時(shí)，公式變?yōu)?=SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=45)*C$2:C$17)，恰好是數(shù)組中所有為 45 的返回 True，也就是對(duì)第二周的營(yíng)業(yè)額求和。

另外，Sum 公式可以改為用 SumProduct 實(shí)現(xiàn)，公式可以這樣寫(xiě)：=SUMPRODUCT((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)。