跳到主要内容

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

添加一名作者 最后修改时间:2021-10-28

本教程逐步演示公式,以帮助您根据 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 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项  |  删除空白行  |  合并列或单元格而不丢失数据  |  不使用公式进行四舍五入 ...
超级VLookup: 多重标准  |  多重价值  |  跨多页  |  模糊查询...
副词。 下拉列表: 简易下拉列表  |  依赖下拉列表  |  多选下拉列表...
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  将列与 选择相同和不同的单元格 ...
特色功能: 网格焦点  |  设计图  |  大方程式酒吧  |  工作簿和工作表管理器 | 资源库 (自动文本)  |  日期选择器  |  合并工作表  |  加密/解密单元格  |  按列表发送电子邮件  |  超级筛选  |  特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符 ...)  |  50+ 图表 类型 (甘特图 ...)  |  40+ 实用 公式 (根据生日计算年龄 ...)  |  19 插入 工具 (插入二维码, 从路径插入图片 ...)  |  12 转化 工具 (小写金额转大写, 货币兑换 ...)  |  7 合并与拆分 工具 (高级组合行, 拆分 Excel 单元格 ...)  |  ... 和更多

Kutools for Excel 拥有超过 300 个功能, 确保只需点击一下即可获得您所需要的...

描述


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

  • 一秒钟即可在数十个打开的文档之间切换!
  • 每天为您减少数百次鼠标单击,告别鼠标手。
  • 查看和编辑多个文档时,可将您的工作效率提高50%。
  • 为 Office(包括 Excel)带来高效的选项卡,就像 Chrome、Edge 和 Firefox 一样。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations