|
在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)。
|
