excel-formula - 计算当前日历年每位员工在 Excel 轮班工作名册中工作的完整周末(周六和周日)的数量
问题描述
我有一个用于轮班工人的 Excel 排班电子表格,需要计算每个工作人员在当前日历年中开/关的完整周末(周六和周日)的数量。
我尝试了几个公式,尝试了命名范围、countif、countifs 等,但在计算同一行(即星期六和星期日)的两个连续列(天)中的相同值时遇到问题。单日计数(例如工作的星期天数)没问题,但我对如何计算两天都是休息日的周末数感到困惑,例如
以下适用于星期天的单个休息日(RD)的数量,但我需要确定两天(周六和周日)都是休息日的周末数量。
=SUM(COUNTIFS(ROSTER!$M$17:$PM$17,"Sun",ROSTER!M28:PM28,{"RD"}))
以上是从包含完整年度名册的工作表中提取的一个小样本。每个花名册是一个为期两周的区块,从周日开始,在接下来的两周结束和开始的周六和周日之间有一个标题为“RDFN”的栏目。
解决方案
编辑 2
经确认,您想计算在给定时间范围内周六和周日都是 RD (休息日)的周末数量。
我已经修改了我的SUMPRODUCT公式(在这种情况下用作COUNTIF)以找到Jane的结果。只需将其向下拖动即可应用于其他员工:
=SUMPRODUCT(((B5:AH5&C5:AI5)="RDRD")*((B$2:AH$2&C$2:AI$2)="SatSun"))+SUMPRODUCT(((B5:AH5&E5:AK5)="RDRD")*((B$2:AH$2&E$2:AK$2)="SatSun"))
此公式使用两个SUMPRODUCT 公式并将两者的结果相加。两个 SUMPRODUCT 公式都遵循相同的逻辑。
它适用于每两周之间有两列的情况,实际上它比我之前的解决方案略短。
我们以Jane的数据为例。假设您插入了四个辅助行。
第一个辅助行中的公式是B$2&C$2
, C$2&D$2
,依此类推,直到AF2&AG2
代表SunMon、MonTue、TueWed等。
第二个辅助行中的公式是B5&C5
, C5&D5
, 以此类推直到AF5&AG5
, 代表RD8 M , 8 M14 A , 14 A14 A等。
因此,当第一个辅助行中的相应位置返回SatSun时,第一个 SUMPRODUCT 公式计算第二个辅助行中的RDRD数。
First Part =SUMPRODUCT(((B5:AH5&C5:AI5)="RDRD")*((B$2:AH$2&C$2:AI$2)="SatSun"))
但是,上述公式不适用于第一个两周的最后一个星期六和下一个两周的第一个星期日都是RD的情况,因为中间有两列不会在第二个辅助行中返回RDRD,因此匹配将失败.
为了克服这个问题,我必须在另外两个虚构的辅助行的帮助下使用第二个 SUMPRODUCT 公式。
第三个辅助行中的公式是B$2&E$2
, C$2&F$2
,依此类推,直到AF$2&AI$2
代表SunWed、MonThu、TueFri等。
第四辅助行中的公式是B5&E5
, C5&F5
, 等等直到AF5&AI5
, 代表RD14 A , 8 M9 M , 14 A6 M , 等等。
当第三个辅助行中的相应位置返回SatSun时,第二个 SUMPRODUCT 公式也在第四个辅助行中计算RDRD。
Second Part =SUMPRODUCT(((B5:AH5&E5:AK5)="RDRD")*((B$2:AH$2&E$2:AK$2)="SatSun"))
将这两个部分的结果相加将为您提供周六和周日都是 RD 的周末总数,如下所示:
推荐阅读
- laravel-5.4 - 无需登录 Laravel 5.4 即可访问站点页面
- spring - Spring Kafka 批处理选项:使用 SeekToCurrentBatchErrorHandler 的 ClassCastException
- r - 用于观星者提取的标签变量
- spring-boot - 当我尝试更新记录时,它会被重复并调用添加新记录的函数
- wso2 - 如何在 WSO2 上配置 Comodo SSL
- master-slave - 组复制与主从复制
- java - 从 java 中预定义的 Calendar 函数中得到错误的答案
- html - CSS 如何制作高度与?
- python - 如何在 Jinja2 的子模板中设置变量?
- spring - 如何根据我在多个下拉列表中选择的内容创建过滤器?