excel透视表怎么分年汇总（Excel用透视表和公式按年）
excel透视表怎么分年汇总（Excel用透视表和公式按年）1、例如，将上面按年和月列出的小计更改为按周列出的小计。选择其中一个包含年份的单元格（如B4），选择“分析”选项卡，单击“分组选择”，打开“分组”对话框，将“起始日期”从2018/11/1更改为2018/10/29，单击“按”下的“年和月”取消选择，单击“天”选择，“天数”从灰色更改为可选，更改1到7，点击“确定”，每件衣服按“周”小计，操作步骤如图3所示：（二） 按周小计图12、按月小计。选择其中一个文本为年（如A5）的单元格，选择“分析”选项卡，单击“分组选择”，打开“分组”对话框，“步骤”只选择“年和月”（单击“季度”取消选择），单击“确定”，则按年和月对每件服装的成交量进行小计；如果要将年显示到列中，则从“行”中拖动“年”进入“列”列表框，操作流程步骤如图2所示：图2
在Excel中用数据透视表按年、月、日、周进行小计比较方便，只需简单设置即可返回其小计结果；按年、月、日进行小计，可以直接显示在小计结果中，但按周计算，周只能用日期表示；使用公式进行小计可以将属于该周的日期转换为前几周。
使用公式按年、月、周进行小计，主要使用SUM、SumIfs、SumProduct、Value、WeekNum、Date、Row、Text等函数，例如前四个函数按年、月进行小计，按周小计比较复杂，需要用Sum（或SumProduct） Value WeekNum Row来实现。
一、 带数据透视表的Excel小计（如何在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中的小计公式（带公式的小计）（一） 按年度列出的Excel小计
1、如果你想小计各种服装的年营业额。双击单元格F2，将公式=SUM（（$A$2:$A$152=F$1）*（YEAR（$B$2:$B$152）=$E2）*$C$2:$C$152）复制到F2，按Ctrl Shift Enter返回到2018年的“羽绒服”成交量129967；将鼠标移到F2右侧底部的单元格填充手柄上，鼠标变为黑体加号后，按住左键并向右拖动，拖动到H2，然后对2018年剩余服装的成交量进行小计；然后将鼠标移到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的“衣服名称”作为数组返回到A152。之所以A2和152都被绝对引用，是为了确保在向右或向下拖动时，始终返回A2:A152中的“衣服名称”。
C、 $A$2:$A$152=F$1是求和的第一个标准，这意味着A2:A152中的每个“服装名称”都与F1中的“羽绒服”进行比较；如果它们相等，则返回True，否则返回False；第一次取A2（即“羽绒服”），它们相等，返回True；第二次取A3（即“休闲服”），它们不相等，返回False；others等等，最后返回数组{TRUE；False；TRUE；…；TRUE}。
D、 $B$2:$B$152以数组形式返回从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变为{2018；2018；2018；…；2019}=2018，然后依次从数组中取出每个元素并与2018进行比较；如果相等，则返回True，否则返回False，最后返回{True；True；…；False}。
F、 然后公式变为=SUM（{TRUE；FALSE；TRUE；..；TRUE}*{TRUE；TRUE；TRUE；..；FALSE}*$C$2:$C$152），然后相乘两个数组中的相应元素；相乘时，TRUE转换为1，FALSE转换为0，然后公式变为=SUM（{1；0；1；..；0}*$C$2:$C$152）。
G、 $C$2:$C$152是要求和的范围，它以数组的形式返回C2:C152中的营业额，该数组返回{5499；6527；6060；…；6182}。
H、 公式进一步变为=和（{1；0；1；…；0}*{5499；6527；6060；…；6182}），然后两个数组的相应元素相乘，公式变为=和（{5221；0；6060；…；0}），最后对数组求和，结果为129967。
提示：上述公式也可以通过SumIfs、SumProduct等函数实现。有了这两个功能，您不需要按“Ctrl Shift Enter”来计算，只需按Enter。按Ctrl Shift Enter和Sum的原因是公式是数组公式。要将上面的公式更改为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）复制到G2，按Ctrl Shift Enter返回2018年11月的“羽绒服”成交量；向右拖动返回2018年11月的其他服装成交量，返回每件服装的成交量2018年剩余几个月采用拖累的方式。E2将2018更改为2019，F2和F3将11和12分别更改为1和2，G2:I3中的值将自动更改为2019年1月和2月的每件服装的调色；选择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）说明：上面的公式和“按年小计”是一个意思，这里只是添加了一个标准年（$B$2:$B$152）=$E$2。
3、上面的公式也可以用SumIfs和SumProduct函数来实现，它们如下所示：
=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））按回车键可直接执行这两个公式。SumProduct公式和Sum公式是一个含义，不再被解析，只有SumIfs公式被解析如下：
A、 $C$2:$C$152是总和范围；$A$2:$A$152，G$1是第一个标准范围/标准对，A$2:$A$152是标准范围，G$1是标准范围。意思是：在A2:A152的G1中找到“羽绒服”。
B、 $B$2:$B$152，“>=”&DATE（$E$2，$F2 0） 1是第二个标准范围/标准对，用于查找大于或等于2018年11月1日的所有日期在B2:B152；E2中的年份是2018，然后“>=”&DATE（$E$2，$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月1日，因为2018年11月0日是2018年10月31日，加1，正好是2018年11月1日。
C、 $B$2:$B$152，“<=”&DATE（$E$2，$F2 1 0）是第三个标准范围/标准对，与第二个标准范围/标准对相同，用于查找B2:B152中小于或等于2018年11月30日的日期；F2中的月份为11，日期（$E$2，$F2 1 0）变为日期（2018，“11” 1 0），进一步计算变为日期（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的所有营业额小计。
（三） 按周列出的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复制到F2，按回车键，返回11月的第一周；将鼠标移到F2右下角的单元格填充句柄，返回11月的剩余周数下拉方式，双击G2，将公式=SUM（（WEEKNUM（-B$2:B$152 2）=WEEKNUM（E$2 7*（ROW（A1）-1），2））*C$2:C$152）复制到G2，按Ctrl Shift Enter返回11月第一周的营业额，并用下拉方式返回剩余一周的营业额；操作过程步骤，如图8所示：
图8
2、公式说明：
（1） =周数（E$2 7*（行（A1）-1），2）-周数（--TEXT（E$2，“E-m”），2） 1
A、 行（A1）用于返回A1的行号1，7*（行（A1）-1用于每次向下拖动单元格，将日期添加到下一周；当公式位于F2时，它将变为7*（1-1）=0，当公式位于F3时，A1将自动更改为A2，行（A2）返回2，它将变为7*（2-1）=7；其他等等。
B、 =WEEKNUM（E$2 7*（行（A1）-1），2）用于返回指定日期所在年份的前几周，E$2 7*（行（A1）-1）是日期，参数2表示“星期一”是一周的第一天；以F2中的公式为例：E2是2018年11月1日，7*（行（A1）-1）返回0，然后=周数（E$2 7*（行（A1）-1），2）变成=周数（E$2 0 2），返回44，即2018年11月1日是一年中的第44周。
C、 e-m是指年和月的显示日期，e是指年，相当于yyyy；m是指月，相当于mm；e和m的位置可以交换；TEXT（e$2，“e-m”）是指在E2中按年和月返回到2018年11月1日，即返回“11/2018”；--in--“11/2018”是指将文本转换为日期，相当于值函数。
D、 然后WEEKNUM（--TEXT（E$2，“E-m”），2）变为omesweeknum（--“11/2018”，2），并进一步计算，返回44。
E、 公式变为=44-44 1，进一步计算，返回“第一周”；当公式在F3中时，公式变为=45-44 1，返回“第二周”，依此类推。
（2） =总和（（周数（-B$2:B$152 2）=周数（E$2 7*（行（A1）-1），2））*C$2:C$152）
A、 WEEKNUM（--B$2:B$152 2）用于返回一年中B2:B152中日期的前几周作为数组；首先，取出B2（即11/1/2018），WEEKNUM（--B2 2），返回44；其次，取出B3（即11/2/2018），WEEKNUM（--B3 2），返回44；其他等等，最后返回{44；44；44；44；45；…；46}。
B、 上面已经解释了周数（E$2 7*（第（A1）-1行）。当公式在G2中时，返回44；当公式在G3中时，返回45。
C、 然后公式变为=SUM（（{44；44；44；44；45；…；46}=44）*C$2:C$152），然后将数组中的每个元素与44进行比较，如果相等，则返回True，否则返回False。
D、 公式变为=SUM（{TRUE；TRUE；TRUE；TRUE；FALSE；..；FALSE}*C$2:C$152），进一步计算，将C2:C152中的每个值乘以数组中的相应元素，值是多少？？当被乘以时是真是假，上面已经解释过了。
E、 公式进一步变为=SUM（{5499；6527；6060；7032；…；0}），最后对数组求和，返回25118。
F、 当公式在F3中时，公式变为=SUM（（{44；44；44；44；45；…；46}=45）*C$2:C$17），这恰好是数组中所有45个的True的返回，这是第二周营业额的总和。
此外，Sum公式可以代替SumProduct，公式的编写方式如下：=SumProduct（（WEEKNUM（-B$2:B$152 2）=WEEKNUM（E$2 7*（行（A1）-1），2））*C$2:C$152）。
