用公式實(shí)現(xiàn)按年、月和周分類匯總，主要用 Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row 和 Text 等函數(shù)，例如按年和月匯總用前四個(gè)函數(shù)，而按周匯總復(fù)雜一些，需要用 Sum（或 SumProduct) + Value + WeekNum + Row 實(shí)現(xiàn)。
（一）按年和月匯總
1、創(chuàng)建數(shù)據(jù)透視表。選中表格其中一個(gè)單元格，選擇“插入”選項(xiàng)卡，單擊屏幕左上角的“數(shù)據(jù)透視表”，打開“創(chuàng)建數(shù)據(jù)透視表”窗口，保持默認(rèn)設(shè)置，單擊“確定”，則在新工作表創(chuàng)建一個(gè)數(shù)據(jù)透視表；勾選字段“服裝名稱、日期和營業(yè)額”，已經(jīng)自動(dòng)按“年”匯總好了每種衣服的營業(yè)額；操作過程步驟，如圖1所示：
圖1
2、按月匯總。選中其中一個(gè)年份的單元格（如 A5），選擇“分析”選項(xiàng)卡，單擊“組字節(jié)”，打開“組合”窗口，“步長”僅選擇“年和月”（單擊“季度”取消選擇），單擊“確定”，則按年和月匯總出了每種服裝的營業(yè)額；如果年份要顯示到列，把“年”從“行區(qū)間”拖到“列區(qū)間”；操作過程步驟，如圖2所示：
圖2
（二）按周匯總
1、以把上例的按年和月分類匯總改為按周為例。選中年份的其中一個(gè)單元格（如 B4），選擇“分析”選項(xiàng)卡，單擊“組字節(jié)”，打開“組合”窗口，把“起始于”由 2018/11/1 改為 2018/10/29，單擊“步長”下的“年和月”取消選擇，單擊“日”選中它，“天數(shù)”由灰色變?yōu)榭蛇x，把 1 改為 7，單擊“確定”，則每件衣服按“周”匯總；操作過程步驟，如圖3所示：
圖3
2、“起始于”自動(dòng)填入的日期 2018/11/1 是表格的起始日期，但不是周一，因此要把它改為“周一”的日期 2018/10/29，這樣就能實(shí)現(xiàn)按周匯總。
（三）對(duì)匯總結(jié)果排序
1、對(duì)不同服裝的營業(yè)額按“周”排序。右鍵其中一件衣服的營業(yè)額，如 B5，在彈出的菜單中，依次選擇“排序”→ 升序，則每件衣服每周的營業(yè)額都按“升序”排列；操作過程步驟，如圖4所示：
圖4
2、對(duì)每件衣服的“營業(yè)額”按“升序”排序。右鍵任意一件衣服的匯總營業(yè)額，如 B4，在彈出的菜單中，同樣依次選擇“排序”→ 升序，則每件衣服按“營業(yè)額”升序排列，每件衣服的周營業(yè)額也隨之排序；操作過程步驟，如圖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)生成分頁報(bào)表和把日?qǐng)?bào)表合成月報(bào)表及顯示報(bào)表篩選頁為灰色的解決辦法》和《Excel多重合并計(jì)算數(shù)據(jù)區(qū)域用于合并多個(gè)表格到數(shù)據(jù)透視表，含把地區(qū)和城市的銷量與營業(yè)額用多頁字段合并》。
（一）按年匯總
1、假如要匯總各種服裝每年的營業(yè)額。雙擊 F2 單元格，把公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 復(fù)制到F2，按 Ctrl + Shift + 回車，返回“羽絨服”2018 年的營業(yè)額 21232；把鼠標(biāo)移到 F2 右下角的單元格填充柄上，鼠標(biāo)變?yōu)楹谏犹?hào)后，按住左鍵，往右拖，一直拖到 I2，則匯總出剩余服裝 2018 年的營業(yè)額；再把鼠標(biāo)移到 I2 右下角的單元格填充柄上，同樣方法往下拖，則匯總出 2019 年各種服裝的營業(yè)額；操作過程步驟，如圖6所示：
圖6
2、公式 =SUM(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25) 說明：
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 的“服裝名稱”，之所以 A2 和 A25 都用絕對(duì)引用，是要確保往右或往下拖時(shí)，始終返回A2:A25 中的“服裝名稱”。
C、$A$2:$A$25=F$1 為 Sum 的第一個(gè)條件，意思是把 A2:A25 中的每種“服裝名稱”與 F1 中的“羽絨服”比較，如果相等，返回 True，否則返回 False；第一次取 A2（即“羽絨服”），它們相等，返回 True；第二次取 A3（即“風(fēng)衣”），它們不相等，返回 False；其它的以此類推，最后返回?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；其它的以此類推，最后返回 {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è)額，即返回 {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 + 回車”求值，只需按回車即可，之所以用 Sum 要按“Ctrl + Shift + 回車”，是因?yàn)楣綖閿?shù)組公式。把上面的公式改為用 SumProduct 實(shí)現(xiàn)，可以這樣寫： =SUMPRODUCT(($A$2:$A$25=F$1)*(YEAR($B$2:$B$25)=$E2)*$C$2:$C$25)，用 SumIfs 實(shí)現(xiàn)見下面的按月匯總。
（二）按月匯總
1、以匯總每種服裝每年每月的營業(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 + 回車，返回“羽絨服”2018 年 11 月的營業(yè)額；同樣用往右的方法，返回 2018 年 11 月其它服裝的營業(yè)額，用往下拖的方法返回每種服裝 2018 年剩余月份的營業(yè)額；操作過程步驟，如圖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) 說明：
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)，它們的寫法如下：
=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è)公式直接按回車即可。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 連接起來，即 ">=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è)額。
（三）按周匯總
1、假如要把 11 月各種服裝的營業(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，按回車，返回 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 + 回車，返回 11 月第一周的營業(yè)額，同樣用往下拖的辦法返回剩余周的營業(yè)額；操作過程步驟，如圖8所示：
圖8
2、公式說明：
（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；其它的以此類推。
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)為大寫漢字。
F、則公式變?yōu)?=TEXT(44-44+1,"第[dbnum1]0周")，進(jìn)一步計(jì)算返回“第一周”；當(dāng)公式在 E3 是，公式變?yōu)?=TEXT(45-44+1,"第[dbnum1]0周")，返回“第二周”，其它的以此類推。
（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；其它的以此類推，最后返回{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)解釋過，當(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è)額求和。
另外，Sum 公式可以改為用 SumProduct 實(shí)現(xiàn)，公式可以這樣寫：=SUMPRODUCT((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)。
聯(lián)系客服
