Note: The other languages of the website are Google-translated. Back to English

在 Excel 中按周数或工作日求和值

本教程逐步演示公式,以帮助您根据 Excel 中指定的周数或工作日对特定范围内的值进行求和。

如何在Excel中按周数求和值?
如何在Excel中按工作日求和值?


如何在Excel中按周数求和值?

如下面的屏幕截图所示,为了根据给定周数所属的日期对 Amount 列中的值求和,我们提供了两个公式。

通用公式

=SUMIFS( value_range, helper_column_range, week_number)

=SUMPRODUCT(--(WEEKNUM(date_range+0,1)=week_number),value_range)

参数

值_范围:要根据周数求和的值范围;
Helper_column_range:从日期中提取的周数范围;
周数:用作求和值标准的周数。

如何使用这个公式?

使用 SUMIFS 公式和辅助列

要使用 SUMIFS 公式,首先,您需要应用 WEEKNUM 函数创建一个辅助列来计算 Date 列中日期的相应周数。

=WEEKNUM (serial_num, [return_type])

WEEKNUM 函数返回给定日期在一年中的周数。

1. 在本例中,我选择金额列中第一个值旁边的单元格 (F5)。

2.在其中输入以下公式,然后按 输入 获取第一个日期的周数的键。 选择结果单元格,然后向下拖动其自动填充句柄以获取其他周数。 看截图:

=WEEKNUM(C5)

3. 选择要输出总金额的单元格,在其中输入以下公式,然后按 输入 钥匙。 选择此结果单元格,向下拖动其自动填充句柄以获取其他结果。

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

使用 SUMPRODUCT 公式

使用 SUMPROFUCT 公式解决此问题时,无需创建辅助列。

1.选择一个空白单元格,在其中输入以下公式,然后按 输入 键。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

2. 选择结果单元格,拖动它 自动填充句柄 下来根据您的需要获得其他结果。

公式说明

=SUMIFS($E$5:$E$14, $F$5:$F$14, H6)

使用 WEEKNUM 函数计算周数后,如果辅助列 F5:F14 中的周数与 H5 中的给定周匹配,则 SUMIFS 函数求和范围 E14:E6 中的值。

=SUMPRODUCT(--(WEEKNUM($C$5:$C$14+0,1)=G6),$E$5:$E$14)

周数($C$5:$C$14+0,1): WEEKNUM 函数计算 C5:C14 范围内日期的每个星期数,并返回这样的数组:{1;1;5;6;6;9;11;11;13;14};
{1;1;5;6;6;9;11;11;13;14}=G6: 这里将数组中的每个周数与 G6 中给定的周数进行比较,并返回一个新数组:{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE};
--{真;真;假;假;假;假;假;假;假;假}:这两个减号将“TRUE”和“FALSE”转换为 1 和 0。 在这里你会得到一个新数组 {1;1;0;0;0;0;0;0;0;0};
{1;1;0;0;0;0;0;0;0;0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}: 数组中的每对条目相乘并返回 {1428;2010;0;0;0;0;0;0;0;0};
SUMPRODUCT({1428;2010;0;0;0;0;0;0;0;0}):SUMPRODUCT 函数对数组中的所有数字求和并返回最终结果 1428+2010=3438。

如何在Excel中按工作日求和值?

如下面的屏幕截图所示,要在 Excel 中按特定工作日对 Amount 列中的值求和,您可以将 SUMPRODUCT 函数与 WEEKDAY 函数一起应用来完成。

在这种情况下,由于我们需要对每个工作日的总金额求和,因此在应用公式之前,我们创建了一个数字 1 到 7 的辅助列,以分别表示星期一到星期日。

通用公式

=SUMPRODUCT((WEEKDAY(date_range,2)=weekday_number)*value_range)

参数

日期范围:要评估给定工作日的日期范围;
值_范围:要基于工作日求和的值范围;
2:数字2代表数字1(星期一)到7(星期日);
工作日号码:用作求和值标准的工作日数。 它可以是单元格引用或 1 到 7 之间的数字。

如何使用这个公式?

1. 选择数字 1 单元格旁边的一个单元格。 这里我选择I6。

2. 复制或输入以下公式,然后按 输入 钥匙。 选择结果单元格,然后向下拖动其自动填充句柄以获取其他工作日的结果。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

注意:如果您只需要根据一个指定的工作日(例如星期五)对值进行求和,则可以直接在公式中输入数字 5,如下所示。

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=5)*$E$5:$E$14)

公式说明

=SUMPRODUCT((WEEKDAY($C$5:$C$14,2)=H6)*$E$5:$E$14)

工作日(5 加元:14,2 加元): WEEKDAY 函数返回 1 到 7 之间的整数来表示从星期一到星期日的星期几,范围 C5:C14: {5;7;1;3;6;7;1;5;5;6};
{5;7;1;3;6;7;1;5;5;6}=H6: 这里将数组中的每一天与 H6 中给定的星期几进行比较,并返回一个新数组:{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE};
{假;假;真;假;假;假;真;假;假;假}*$E$5:$E$14:这里将TRUE和False转换为1和0,然后乘以E5:E14中的值返回数组:{0;0;2288;0;0;0;1325;0;0;0};
SUMPRODUCT{0;0;2288;0;0;0;1325;0;0;0}:SUMPRODUCT 函数对数组中的所有数字求和并返回最终结果 2288+1325=3613。

相关功能

Excel WEEKNUM 函数
Excel SUMIFS 函数返回一年中给定日期的周数,从 1 月 XNUMX 日开始计算周数。

Excel SUMPRODUCT函数
Excel SUMPRODUCT 函数可用于将两个或多个列或数组相乘,然后得到乘积的总和。

Excel WEEKDAY 函数
Excel EOMONTH 函数返回一个从 1 到 7 的整数,以表示 Excel 中给定日期的星期几。


相关公式

按月汇总值(带或不带年份)
本教程详细演示了两个公式,以帮助在 Excel 中按特定月份和年份汇总值或按特定月份汇总值忽略年份。

按给定日期的周对值求和
本教程讨论如何按给定日期所在的星期对值求和

对某个范围内的每 n 行或每列求和
本教程解释了基于 SUM 和 OFFSET 函数的两个公式,用于对 Excel 中指定范围内的每 n 行或每列求和。

对范围内的每第 n 行或每列求和
本教程将向您展示如何创建基于 SUMPRODUCT、MOD 和 COLUMN 函数的公式,以对 Excel 中某个范围内的每第 n 行或每列求和。


最佳办公效率工具

Kutools for Excel-帮助您从人群中脱颖而出

您想快速,完美地完成日常工作吗? Kutools for Excel具有300项强大的高级功能(合并工作簿,按颜色求和,拆分单元格内容,转换日期等),并为您节省80%的时间。

  • 专为1500个工作方案而设计,可帮助您解决80%的Excel问题。
  • 每天减少成千上万的键盘和鼠标点击,减轻您疲倦的眼睛和手部的疲劳。
  • 在3分钟内成为一名Excel专家。 不再需要记住任何痛苦的公式和VBA代码。
  • 30天无限制免费试用。 60天退款保证。 免费升级和支持2年。
Excel功能区(已安装Kutools for Excel)

Office选项卡-在Microsoft Office(包括Excel)中启用选项卡式阅读和编辑

  • 一秒钟即可在数十个打开的文档之间切换!
  • 每天为您减少数百次鼠标单击,告别鼠标手。
  • 查看和编辑多个文档时,可将您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一样,为Office(包括Excel)带来高效选项卡。
Excel的屏幕截图(已安装Office选项卡)
按评论排序
注释 (0)
还没有评分。 成为第一位评论!
这里还没有评论
留下你的意见
以访客身份发帖
×
评价此帖子:
0   产品特性
建议地点