在Excel中用數(shù)據(jù)透視表按年、月、日、周進行小計比較方便，只需簡單設(shè)置即可返回其小計結(jié)果；按年、月、日進行小計，可以直接顯示在小計結(jié)果中，但按周計算，周只能用日期表示；使用公式進行小計可以將屬于該周的日期轉(zhuǎn)換為前幾周。

使用公式按年、月、周進行小計，主要使用Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row、Text等函數(shù)，例如前四個函數(shù)按年、月進行小計，按周小計比較復(fù)雜，需要用Sum(或SumProduct)+Value+WeekNum+Row來實現(xiàn)。

一、 帶數(shù)據(jù)透視表的Excel小計(如何在Excel數(shù)據(jù)透視表中添加小計)

(一) 年、月小計

1、創(chuàng)建數(shù)據(jù)透視表。選擇表格中的一個單元格，選擇“插入”選項卡，單擊屏幕左上角的“數(shù)據(jù)透視表”，打開“創(chuàng)建數(shù)據(jù)透視表”對話框，保留默認設(shè)置，單擊“確定”，然后在新工作表中創(chuàng)建數(shù)據(jù)透視表；選中“衣服，“日期和成交量”，自動按“年”對每件服裝的成交量進行小計，操作步驟如圖1所示：

圖1

2、按月小計。選擇其中一個文本為年(如A5)的單元格，選擇“分析”選項卡，單擊“分組選擇”，打開“分組”對話框，“步驟”只選擇“年和月”(單擊“季度”取消選擇)，單擊“確定”，則按年和月對每件服裝的成交量進行小計；如果要將年顯示到列中，則從“行”中拖動“年”進入“列”列表框，操作流程步驟如圖2所示：

圖2

(二) 按周小計

1、例如，將上面按年和月列出的小計更改為按周列出的小計。選擇其中一個包含年份的單元格(如B4)，選擇“分析”選項卡，單擊“分組選擇”，打開“分組”對話框，將“起始日期”從2018/11/1更改為2018/10/29，單擊“按”下的“年和月”取消選擇，單擊“天”選擇，“天數(shù)”從灰色更改為可選，更改1到7，點擊“確定”，每件衣服按“周”小計，操作步驟如圖3所示：

圖3

2、“起始日期”自動填入日期2018/11/1是表的起始日期，但不是星期一，因此將其更改為“星期一”日期2018/10/29，以便您可以按周實現(xiàn)小計。

(三) 對小計結(jié)果排序

1、不同服裝的成交量按“周”分類。右鍵點擊其中一件衣服的周轉(zhuǎn)率，如B5，在彈出的菜單中選擇“排序→從最小到最大排序”，然后按“升序”對每件衣服的周周轉(zhuǎn)率進行排序，操作步驟如圖4所示：

圖4

2、按“升序”對每件衣服的“周轉(zhuǎn)量”進行排序。右鍵點擊B4等任意一件服裝周轉(zhuǎn)小計，在彈出的菜單中選擇“排序→排序從小到大”，則每件服裝的“周轉(zhuǎn)量”按升序排序，每件服裝的周周轉(zhuǎn)量也按升序排序；操作流程步驟如圖5所示：

圖5

有關(guān)數(shù)據(jù)透視表操作的詳細信息，請參閱“如何在excel中創(chuàng)建數(shù)據(jù)透視表(15個示例，具有不同的計數(shù)、百分比和四個區(qū)域)”，“如何從一個數(shù)據(jù)透視表生成多個報表(自動創(chuàng)建月度報表，以及“將多個excel工作表合并到一個透視表中，并在excel中包含多個合并范圍”。

二、 excel中的小計公式(帶公式的小計)

(一) 按年度列出的Excel小計

1、如果你想小計各種服裝的年營業(yè)額。雙擊單元格F2，將公式=SUM(($A$2:$A$152=F$1)*(YEAR($B$2:$B$152)=$E2)*$C$2:$C$152)復(fù)制到F2，按Ctrl+Shift+Enter返回到2018年的“羽絨服”成交量129967；將鼠標(biāo)移到F2右側(cè)底部的單元格填充手柄上，鼠標(biāo)變?yōu)楹隗w加號后，按住左鍵并向右拖動，拖動到H2，然后對2018年剩余服裝的成交量進行小計；然后將鼠標(biāo)移到H2的單元格填充手柄上，用相同的方法向下拖動，對2019年各類服裝的成交量進行小計；操作過程步驟，如圖6所示：

圖6

2、公式=總和($A$2:$A$152=F$1)*(年份($B$2:$B$152)=$E2)*$C$2:$C$152)說明：

A、 $A$2表示對列和行的絕對引用。向右拖動時，A2不會變成B2、C2等；向下拖動時，A2不會變成A3、A4等，另一個帶有雙$和$A2是一種含義。

B、 $A$2:$A$152將A2的“衣服名稱”作為數(shù)組返回到A152。之所以A2和152都被絕對引用，是為了確保在向右或向下拖動時，始終返回A2:A152中的“衣服名稱”。

C、 $A$2:$A$152=F$1是求和的第一個標(biāo)準(zhǔn)，這意味著A2:A152中的每個“服裝名稱”都與F1中的“羽絨服”進行比較；如果它們相等，則返回True，否則返回False；第一次取A2(即“羽絨服”)，它們相等，返回True；第二次取A3(即“休閑服”)，它們不相等，返回False；others等等，最后返回數(shù)組{TRUE；False；TRUE；…；TRUE}。

D、 $B$2:$B$152以數(shù)組形式返回從B2到B152的日期。年份($B$2:$B$152)用于取B2:B152中日期的年份；取B2(即2018年11月1日)第一次，年份(B2)返回2018；第二次返回B3(ie 11/2/2018)，年份(B3)返回2018；其他等等，最后返回{2018；2018；2018；…；2019}。

E、 E2中的值是2018，YEAR($B$2:$B$152)=$E2變?yōu)閧2018；2018；2018；…；2019}=2018，然后依次從數(shù)組中取出每個元素并與2018進行比較；如果相等，則返回True，否則返回False，最后返回{True；True；…；False}。

F、 然后公式變?yōu)?SUM({TRUE；FALSE；TRUE；..；TRUE}*{TRUE；TRUE；TRUE；..；FALSE}*$C$2:$C$152)，然后相乘兩個數(shù)組中的相應(yīng)元素；相乘時，TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0，然后公式變?yōu)?SUM({1；0；1；..；0}*$C$2:$C$152)。

G、 $C$2:$C$152是要求和的范圍，它以數(shù)組的形式返回C2:C152中的營業(yè)額，該數(shù)組返回{5499；6527；6060；…；6182}。

H、 公式進一步變?yōu)?和({1；0；1；…；0}*{5499；6527；6060；…；6182})，然后兩個數(shù)組的相應(yīng)元素相乘，公式變?yōu)?和({5221；0；6060；…；0})，最后對數(shù)組求和，結(jié)果為129967。

提示：上述公式也可以通過SumIfs、SumProduct等函數(shù)實現(xiàn)。有了這兩個功能，您不需要按“Ctrl+Shift+Enter”來計算，只需按Enter。按Ctrl+Shift+Enter和Sum的原因是公式是數(shù)組公式。要將上面的公式更改為SumProduct，可以寫入：=SumProduct(($A$2:$A$152=F$1)*(YEAR($B$2:$B$152)=$E2)*$C$2:$C$152)，將其更改為SumIfs，請參見下面的每月小計。

(二) Excel按月小計

1、如果你想小計每年每件衣服的月成交量。雙擊單元格G2，將公式=SUM(($A$2:$A$152=G$1)*(YEAR($B$2:$B$152)=$E$2)*(MONTH($B$2:$B$152)=$F2)*$C$2:$C$152)復(fù)制到G2，按Ctrl+Shift+Enter返回2018年11月的“羽絨服”成交量；向右拖動返回2018年11月的其他服裝成交量，返回每件服裝的成交量2018年剩余幾個月采用拖累的方式。E2將2018更改為2019，F(xiàn)2和F3將11和12分別更改為1和2，G2:I3中的值將自動更改為2019年1月和2月的每件服裝的調(diào)色；選擇G2:I2，下拉返回2019年3月的每件服裝的成交量。操作步驟如圖7所示：

圖7

2、公式=總和($A$2:$A$152=G$1)*(年份($B$2:$B$152)=$E$2)*(月份($B$2:$B$152)=$F2)*$C$2:$C$152)說明：上面的公式和“按年小計”是一個意思，這里只是添加了一個標(biāo)準(zhǔn)年($B$2:$B$152)=$E$2。

3、上面的公式也可以用SumIfs和SumProduct函數(shù)來實現(xiàn)，它們?nèi)缦滤荆?/span>

=SUMPRODUCT(($A$2:$A$152=G$1)*(年($B$2:$B$152)=$E$2)*(月($B$2:$B$152)=$F2)*$C$2:$C$152)=SUMIFS(2加元：$152加元，$2加元：$152加元，G加元，B加元：$152加元，“>=”&DATE(2加元，$F2,0)+1加元，B加元2:$152加元，“<=”&DATE(2加元，$F2+1,0))按回車鍵可直接執(zhí)行這兩個公式。SumProduct公式和Sum公式是一個含義，不再被解析，只有SumIfs公式被解析如下：

A、 $C$2:$C$152是總和范圍；$A$2:$A$152，G$1是第一個標(biāo)準(zhǔn)范圍/標(biāo)準(zhǔn)對，A$2:$A$152是標(biāo)準(zhǔn)范圍，G$1是標(biāo)準(zhǔn)范圍。意思是：在A2:A152的G1中找到“羽絨服”。

B、 $B$2:$B$152，“>=”&DATE($E$2，$F2,0)+1是第二個標(biāo)準(zhǔn)范圍/標(biāo)準(zhǔn)對，用于查找大于或等于2018年11月1日的所有日期在B2:B152；E2中的年份是2018，然后“>=”&DATE($E$2，$F2,0)+1變?yōu)椤?gt;=”&DATE(2018，“11”，0)+1，然后DATE函數(shù)將“11”轉(zhuǎn)換為值11，并返回2018年11月0日指示的數(shù)字43404；然后“>=”&DATE(2018，“11”，0)+1變?yōu)椤?gt;=”&43404+1，然后“>=”與43405連接，&，即“>=43405”，意思是大于等于11月1日，因為2018年11月0日是2018年10月31日，加1，正好是2018年11月1日。

C、 $B$2:$B$152，“<=”&DATE($E$2，$F2+1,0)是第三個標(biāo)準(zhǔn)范圍/標(biāo)準(zhǔn)對，與第二個標(biāo)準(zhǔn)范圍/標(biāo)準(zhǔn)對相同，用于查找B2:B152中小于或等于2018年11月30日的日期；F2中的月份為11，日期($E$2，$F2+1,0)變?yōu)槿掌?2018，“11”+1,0)，進一步計算變?yōu)槿掌?2018,12,0)，即2018年12月0日，即2018年11月30日。

D、 公式變成=SUMIFS($C$2:$C$152，$A$2:$A$152，G$1，$B$2:$B$152，“>=2018/11/1”，$B$2:$B$152，“<=2018/11/30”)，意思是：A2:A152中“羽絨服”和“日期”大于或等于2018/11/1且小于或等于2018/11/30的所有營業(yè)額小計。

(三) 按周列出的Excel小計

1、如果你想按周小計11月份各種服裝的成交量。將11月1日的日期11/1/2018輸入單元格E2，雙擊F2，將公式=WEEKNUM(E$2+7*(行(A1)-1)，2)-WEEKNUM(--TEXT(E$2，“E-m”)，2)+1復(fù)制到F2，按回車鍵，返回11月的第一周；將鼠標(biāo)移到F2右下角的單元格填充句柄，返回11月的剩余周數(shù)下拉方式，雙擊G2，將公式=SUM((WEEKNUM(-B$2:B$152,2)=WEEKNUM(E$2+7*(ROW(A1)-1)，2))*C$2:C$152)復(fù)制到G2，按Ctrl+Shift+Enter返回11月第一周的營業(yè)額，并用下拉方式返回剩余一周的營業(yè)額；操作過程步驟，如圖8所示：

圖8

2、公式說明：

(1) =周數(shù)(E$2+7*(行(A1)-1)，2)-周數(shù)(--TEXT(E$2，“E-m”)，2)+1

A、 行(A1)用于返回A1的行號1，7*(行(A1)-1用于每次向下拖動單元格，將日期添加到下一周；當(dāng)公式位于F2時，它將變?yōu)?*(1-1)=0，當(dāng)公式位于F3時，A1將自動更改為A2，行(A2)返回2，它將變?yōu)?*(2-1)=7；其他等等。

B、 =WEEKNUM(E$2+7*(行(A1)-1)，2)用于返回指定日期所在年份的前幾周，E$2+7*(行(A1)-1)是日期，參數(shù)2表示“星期一”是一周的第一天；以F2中的公式為例：E2是2018年11月1日，7*(行(A1)-1)返回0，然后=周數(shù)(E$2+7*(行(A1)-1)，2)變成=周數(shù)(E$2+0,2)，返回44，即2018年11月1日是一年中的第44周。

C、 e-m是指年和月的顯示日期，e是指年，相當(dāng)于yyyy；m是指月，相當(dāng)于mm；e和m的位置可以交換；TEXT(e$2，“e-m”)是指在E2中按年和月返回到2018年11月1日，即返回“11/2018”；--in--“11/2018”是指將文本轉(zhuǎn)換為日期，相當(dāng)于值函數(shù)。

D、 然后WEEKNUM(--TEXT(E$2，“E-m”)，2)變?yōu)閛mesweeknum(--“11/2018”，2)，并進一步計算，返回44。

E、 公式變?yōu)?44-44+1，進一步計算，返回“第一周”；當(dāng)公式在F3中時，公式變?yōu)?45-44+1，返回“第二周”，依此類推。

(2) =總和((周數(shù)(-B$2:B$152,2)=周數(shù)(E$2+7*(行(A1)-1)，2))*C$2:C$152)

A、 WEEKNUM(--B$2:B$152,2)用于返回一年中B2:B152中日期的前幾周作為數(shù)組；首先，取出B2(即11/1/2018)，WEEKNUM(--B2,2)，返回44；其次，取出B3(即11/2/2018)，WEEKNUM(--B3,2)，返回44；其他等等，最后返回{44；44；44；44；45；…；46}。

B、 上面已經(jīng)解釋了周數(shù)(E$2+7*(第(A1)-1行)。當(dāng)公式在G2中時，返回44；當(dāng)公式在G3中時，返回45。

C、 然后公式變?yōu)?SUM(({44；44；44；44；45；…；46}=44)*C$2:C$152)，然后將數(shù)組中的每個元素與44進行比較，如果相等，則返回True，否則返回False。

D、 公式變?yōu)?SUM({TRUE；TRUE；TRUE；TRUE；FALSE；..；FALSE}*C$2:C$152)，進一步計算，將C2:C152中的每個值乘以數(shù)組中的相應(yīng)元素，值是多少？？當(dāng)被乘以時是真是假，上面已經(jīng)解釋過了。

E、 公式進一步變?yōu)?SUM({5499；6527；6060；7032；…；0})，最后對數(shù)組求和，返回25118。

F、 當(dāng)公式在F3中時，公式變?yōu)?SUM(({44；44；44；44；45；…；46}=45)*C$2:C$17)，這恰好是數(shù)組中所有45個的True的返回，這是第二周營業(yè)額的總和。

此外，Sum公式可以代替SumProduct，公式的編寫方式如下：=SumProduct((WEEKNUM(-B$2:B$152,2)=WEEKNUM(E$2+7*(行(A1)-1)，2))*C$2:C$152)。