Excel 分类汇总按年、月和周且用数据透视表与公式实现，并把汇总结果提取出来

在 excel 中，按年、月、日和周分类汇总用数据透视表比较简单方便，进行简单的设置就可以返回它们的汇总结果；按年、月和日汇总可以直接显示它们，但按周汇总只能用日期表示周；而用公式汇总可以实现把属于每周的日期转为第几周。

用公式实现按年、月和周分类汇总，主要用 Sum、SumIfs、SumProduct、Value、WeekNum、Date、Row 和 Text 等函数，例如按年和月汇总用前四个函数，而按周汇总复杂一些，需要用 Sum（或 SumProduct) + Value + WeekNum + Row 实现。

一、Excel 分类汇总用数据透视表汇总

（一）按年和月汇总

1、创建数据透视表。选中表格其中一个单元格，选择“插入”选项卡，单击屏幕左上角的“数据透视表”，打开“创建数据透视表”窗口，保持默认设置，单击“确定”，则在新工作表创建一个数据透视表；勾选字段“服装名称、日期和营业额”，已经自动按“年”汇总好了每种衣服的营业额；操作过程步骤，如图1所示：

图1

2、按月汇总。选中其中一个年份的单元格（如 A5），选择“分析”选项卡，单击“组字节”，打开“组合”窗口，“步长”仅选择“年和月”（单击“季度”取消选择），单击“确定”，则按年和月汇总出了每种服装的营业额；如果年份要显示到列，把“年”从“行区间”拖到“列区间”；操作过程步骤，如图2所示：

图2

（二）按周汇总

1、以把上例的按年和月分类汇总改为按周为例。选中年份的其中一个单元格（如 B4），选择“分析”选项卡，单击“组字节”，打开“组合”窗口，把“起始于”由 2018/11/1 改为 2018/10/29，单击“步长”下的“年和月”取消选择，单击“日”选中它，“天数”由灰色变为可选，把 1 改为 7，单击“确定”，则每件衣服按“周”汇总；操作过程步骤，如图3所示：

图3

2、“起始于”自动填入的日期 2018/11/1 是表格的起始日期，但不是周一，因此要把它改为“周一”的日期 2018/10/29，这样就能实现按周汇总。

（三）对汇总结果排序

1、对不同服装的营业额按“周”排序。右键其中一件衣服的营业额，如 B5，在弹出的菜单中，依次选择“排序”→ 升序，则每件衣服每周的营业额都按“升序”排列；操作过程步骤，如图4所示：

图4

2、对每件衣服的“营业额”按“升序”排序。右键任意一件衣服的汇总营业额，如 B4，在弹出的菜单中，同样依次选择“排序”→ 升序，则每件衣服按“营业额”升序排列，每件衣服的周营业额也随之排序；操作过程步骤，如图5所示：

图5

有关更多数据透视表的操作，请参考《Excel数据透视表怎么做的15个操作方法，含非重复计数和百分比统计及四个区域间的用途》、《Excel数据透视表自动生成分页报表和把日报表合成月报表及显示报表筛选页为灰色的解决办法》和《Excel多重合并计算数据区域用于合并多个表格到数据透视表，含把地区和城市的销量与营业额用多页字段合并》。

二、Excel 分类汇总用公式

（一）按年汇总

1、假如要汇总各种服装每年的营业额。双击 F2 单元格，把公式 =SUM(($A:$A=F)*(YEAR($B:$B)=$E2)*$C:$C) 复制到F2，按 Ctrl + Shift + 回车，返回“羽绒服”2018 年的营业额 21232；把鼠标移到 F2 右下角的单元格填充柄上，鼠标变为黑色加号后，按住左键，往右拖，一直拖到 I2，则汇总出剩余服装 2018 年的营业额；再把鼠标移到 I2 右下角的单元格填充柄上，同样方法往下拖，则汇总出 2019 年各种服装的营业额；操作过程步骤，如图6所示：

图6

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

A、$A 表示对列和行都绝对引用，当往右拖时，A2 不会变为 B2、C2 等；当往下拖时，A2 不会变为 A3、A4 等，其它的有两个 $ 的与$A 是一个意思。

B、$A:$A 以数组形式返回 A2 至 A25 的“服装名称”，之所以 A2 和 A25 都用绝对引用，是要确保往右或往下拖时，始终返回A2:A25 中的“服装名称”。

C、$A:$A=F 为 Sum 的第一个条件，意思是把 A2:A25 中的每种“服装名称”与 F1 中的“羽绒服”比较，如果相等，返回 True，否则返回 False；第一次取 A2（即“羽绒服”），它们相等，返回 True；第二次取 A3（即“风衣”），它们不相等，返回 False；其它的以此类推，最后返回数组 {TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}。

D、$B:$B 以数组形式返回 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 变为 {2018;2018;...;2019}=2018，然后依次从数组中取出每个元素与 2018 比较，如果相等，返回 True，否则返回 False，最后返回 {TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}。

F、则公式变为 =SUM({TRUE;FALSE;FALSE;...;FALSE;FALSE;TRUE}*{TRUE;TRUE;TRUE;...;FALSE;FALSE;FALSE}*$C:$C)，接着，把两个数组中对应元素相乘，相乘时，TRUE被转为 1、FALSE 被转为 0，则公式变为 =SUM({1;0;0;...;0;0;0}*$C:$C)。

G、$C:$C 为求和区域，它以数组形式返回 C2:C25 中的营业额，即返回 {5221;5202;4622;...;5889;5198;5299}。

H、公式进一步变为 =SUM({1;0;0;...;0;0;0}*{5221;5202;4622;...;5889;5198;5299})，接着再把两个数组对应元素相乘，公式变为=SUM({5221;0;0;...;0;0;0})，最后对数组求和，结果为 21232。

提示：上面的公式也可以用 SumIfs、SumProduct 等函数实现，用这两个函数不需按“Ctrl + Shift + 回车”求值，只需按回车即可，之所以用 Sum 要按“Ctrl + Shift + 回车”，是因为公式为数组公式。把上面的公式改为用 SumProduct 实现，可以这样写： =SUMPRODUCT(($A:$A=F)*(YEAR($B:$B)=$E2)*$C:$C)，用 SumIfs 实现见下面的按月汇总。

（二）按月汇总

1、以汇总每种服装每年每月的营业额为例。双击 G2 单元格，把公式 =SUM(($A:$A=G)*(YEAR($B:$B)=$E)*(MONTH($B:$B)=--SUBSTITUTE($F2,"月",""))*$C:$C) 复制到 G2，按 Ctrl + Shift + 回车，返回“羽绒服”2018 年 11 月的营业额；同样用往右的方法，返回 2018 年 11 月其它服装的营业额，用往下拖的方法返回每种服装 2018 年剩余月份的营业额；操作过程步骤，如图7所示：

图7

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

A、公式与上面的“按年汇总”是一个意思，这里只是多加了一个“年份”的条件 YEAR($B:$B)=$E 和用 Substitute 函数取代月份中的“月”字，以获取数字用于计算。

B、--SUBSTITUTE($F2,"月","") 的意思是：用空 "" 取代 F2 中“11月”的“月”字，它返回文本 "11"，再用 -- 把 "11" 转为数值 11。

3、以上公式也可以用 SumIfs 和 SumProduct 函数实现，它们的写法如下：

=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))

这两个公式直接按回车即可。SumProduct 公式与 Sum 公式是一个意思，不再解析，下面只解析 SumIfs 公式：

A、$C$2:$C$25 为求和区域；$A$2:$A$25,G$1 为第一个条件区域/条件对，$A$2:$A$25 为条件区域，G$1 为条件，意思是：在 A2:A25 中查找 G1 中的“羽绒服”。

B、$B$2:$B$25,">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 为第二个条件区域/条件对，用于在 B2:B25 中查找大于等于 2018/11/1 的所有日期；SUBSTITUTE($F2,"月","") 用于用空 "" 取代 F2 中“11月”的“月”字，它返回文本 "11"；

E2 为 2018，则 ">="&DATE($E$2,SUBSTITUTE($F2,"月",""),0)+1 变为 ">="&DATE(2018,"11",0)+1，接着 Date 函数把 "11" 转为数值 11，并返回 2018 年 11 月 0 天表示的序号 43404；

则 ">="&DATE(2018,"11",0)+1 变为 ">="&43404+1，再用 & 把 ">=" 与 43405 连接起来，即 ">=43405"，意思是大于等于 11 月的第一天，因为 2018 年 11 月的第 0 天为 2018 年 10 月 31 日，再加 1，恰好是 2018年 11 月 1 日。

C、$B$2:$B$25,"<="&DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 为第三个条件区域/条件对，与第二个条件区域/条件对是一个意思，用于在 B2:B25 中查找小于等于 2018/11/30 的所日期；

SUBSTITUTE($F2,"月","") 返回 "11"，DATE($E$2,SUBSTITUTE($F2,"月","")+1,0) 变为 DATE(2018,"11"+1,0)，进一步计算变为 DATE(2018,12,0)，意思是 2018 年 12 月第 0 天，即 2018 年 11 月 30 日。

D、则公式变为 =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 的所有营业额。

（三）按周汇总

1、假如要把 11 月各种服装的营业额按周汇总。把 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周") 复制到 E2，按回车，返回 11 月第一周；把鼠标移到 E2 右下角的单元格填充柄上，用往下拖的方法返回 11 月剩余的周数；双击 F2，把公式 =SUM((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17) 复制到 F2，按 Ctrl + Shift + 回车，返回 11 月第一周的营业额，同样用往下拖的办法返回剩余周的营业额；操作过程步骤，如图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 的行号 1，7*(ROW(A1)-1 用于实现每往下拖一个单元格，日期累加到下一周；当公式在 E2 时，7*(1-1)=0,当公式在 E3 时，A1 变 A2，ROW(A2) 返回 2，7*(2-1)=7；其它的以此类推。

B、WEEKNUM(E$1 + 7*(ROW(A1)-1),2) 用于返回指定日期在一年中是第几周，E$1 + 7*(ROW(A1)-1) 为日期，参数 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 表示年，相当于 yyyy；m 表示月，相当 mm；e 和 m 的位置可以调换；TEXT(E$1,"e-m") 表示以年月返回 E1 中的 2018/11/1，即返回 "2018-11"；--"2018-11" 中 -- 表示把文本转为日期，它相当于 Value 函数。

D、则 WEEKNUM(--TEXT(E$1,"e-m"),2) 变为 WEEKNUM(--"2018-11",2)，进一步计算返回 44。

E、"第[dbnum1]0周" 用于返回汉字表示的第几周，dbnum1 表示把数字转为汉字，0 表示把 [dbnum1] 显示为数字。另外，dbnum2 表示把数字转为大写汉字。

F、则公式变为 =TEXT(44-44+1,"第[dbnum1]0周")，进一步计算返回“第一周”；当公式在 E3 是，公式变为 =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) 用于以数组形式返回 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) 上面已经解释过，当公式在 F2 时，它返回 44；当公式在 F3 时，它返回 45。

C、则公式变为 =SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=44)*C$2:C$17)，接着把数组中的每个元素与 44 比较，如果相等返回 True，否则返回 False。

D、则公式变为 =SUM({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*C$2:C$17)，进一步计算，把 C2:C17 中的每个数值与数组中的对应元素相乘，相乘时，TRUE 和 FALSE 的取值，上面已经解释。

E、公式进一步变为 =SUM({5221;5202;4622;5140;0;0;0;0;0;0;0;0;0;0;0;0})，最后对数组求和，最后返回 20185。

F、当公式在 F3 时，公式变为 =SUM(({44;44;44;44;45;45;45;45;45;45;46;46;46;46;46;46}=45)*C$2:C$17)，恰好是数组中所有为 45 的返回 True，也就是对第二周的营业额求和。

另外，Sum 公式可以改为用 SumProduct 实现，公式可以这样写：=SUMPRODUCT((WEEKNUM(--B$2:B$17,2)=WEEKNUM(E$1+7*(ROW(A1)-1),2))*C$2:C$17)。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。