Excel 分類匯總按年、月和周且用數(shù)據(jù)透視表與公式實現(xiàn)，并把匯總結果提取出來

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

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

一、excel 分類匯總用數(shù)據(jù)透視表匯總

（一）按年和月匯總

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

圖1

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

圖2

（二）按周匯總

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

圖3

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

（三）對匯總結果排序

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

圖4

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

圖5

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

二、Excel 分類匯總用公式

（一）按年匯總

1、假如要匯總各種服裝每年的營業(yè)額。雙擊 F2 單元格，把公式 =SUM(($A:$A=F)*(YEAR($B:$B)=$E2)*$C:$C) 復制到F2，按 Ctrl + Shift + 回車，返回“羽絨服”2018 年的營業(yè)額 21232；把鼠標移到 F2 右下角的單元格填充柄上，鼠標變?yōu)楹谏犹柡螅醋∽箧I，往右拖，一直拖到 I2，則匯總出剩余服裝 2018 年的營業(yè)額；再把鼠標移到 I2 右下角的單元格填充柄上，同樣方法往下拖，則匯總出 2019 年各種服裝的營業(yè)額；操作過程步驟，如圖6所示：

圖6

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

A、$A 表示對列和行都絕對引用，當往右拖時，A2 不會變?yōu)?B2、C2 等；當往下拖時，A2 不會變?yōu)?A3、A4 等，其它的有兩個 $ 的與$A 是一個意思。

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

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

D、$B:$B 以數(shù)組形式返回 B2 至 B25 的日期。YEAR($B:$B) 用于取 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:$B)=$E2 變?yōu)?{2018;2018;...;2019}=2018，然后依次從數(shù)組中取出每個元素與 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:$C)，接著，把兩個數(shù)組中對應元素相乘，相乘時，TRUE被轉(zhuǎn)為 1、FALSE 被轉(zhuǎn)為 0，則公式變?yōu)?=SUM({1;0;0;...;0;0;0}*$C:$C)。

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

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

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

（二）按月匯總

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

圖7

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

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

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

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

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

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

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

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

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

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

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

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

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

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

（三）按周匯總

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

圖8

2、公式說明：

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

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

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

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

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

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

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

F、則公式變?yōu)?=TEXT(44-44+1,"第[dbnum1]0周")，進一步計算返回“第一周”；當公式在 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)解釋過，當公式在 F2 時，它返回 44；當公式在 F3 時，它返回 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ù)組中的每個元素與 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)，進一步計算，把 C2:C17 中的每個數(shù)值與數(shù)組中的對應元素相乘，相乘時，TRUE 和 FALSE 的取值，上面已經(jīng)解釋。

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

F、當公式在 F3 時，公式變?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，也就是對第二周的營業(yè)額求和。

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

版權聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權內(nèi)容。

版權聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權內(nèi)容。