google-sheets - 建立两个日期之间不工作的员工报告的公式
问题描述
我试图找到一种方法来查看哪些员工在两个日期之间没有工作。
我应该使用 vlookup、索引和匹配、过滤器、查询还是其他方式?
- 表 1 包含员工详细信息和开始/结束日期。
- 表 2 接受用户输入以选择 2 个日期,它将自动显示未工作的可用员工列表。
表 1 - 所有员工和工作天数的数据库/日志。
# | 一个 | 乙 | C | D |
---|---|---|---|---|
1 | ID | 姓名 | 开始日期 | 结束日期 |
2 | 12345 | 约翰 | 2021 年 1 月 1 日 | 2021 年 1 月 8 日 |
3 | 54321 | 莎拉 | 2021 年 1 月 24 日 | 2021 年 1 月 29 日 |
4 | 00731 | 詹姆士 | 2021 年 2 月 5 日 | 2021 年 2 月 15 日 |
5 | 00731 | 约翰 | 2021 年 2 月 10 日 | 2021 年 2 月 30 日 |
表 2(第 1-2 行)- 手动输入两个日期。
# | 一个 | 乙 |
---|---|---|
1 | 开始日期(手动输入) | 结束日期(手动输入) |
2 | 2021 年 1 月 1 日 | 2021 年 1 月 30 日 |
表 2(第 3 行+)- 在表 2 中输入的两个日期之间未工作的所有员工的列表!A2:B2(预期结果)
# | 一个 | 乙 |
---|---|---|
3 | ID | 姓名 |
4 | 00731 | 詹姆士 |
解决方案
尝试:
=INDEX(SUBSTITUTE(UNIQUE(QUERY(""&SPLIT(FLATTEN(IF(SEQUENCE(1, MAX(D2:D-C2:C))<=D2:D-C2:C,
"♥"&A2:A&"♦"&B2:B&"♦"&C2:C+SEQUENCE(1, MAX(D2:D-C2:C), 0), )), "♦"),
"select Col1,Col2 where not Col3 matches '"&JOIN("|", "^$",
IF(SEQUENCE(1, G2-F2)<=G2-F2, F2+SEQUENCE(1, G2-F2, 0), ))&"'", 0)), "♥", ))
演示表
推荐阅读
- python - Buildbot:工人空闲
- node.js - Mongodb $lookup 不返回值
- python - Python/Matplotlib 中是否有选项可以在图中设置标记线?(每周、每月、每年)
- c# - tftp' 不是内部或外部命令、可运行程序或批处理文件
- sql - 处理缺失值,同时使用 postgresql 将每个组的滞后值相乘
- python - 我现在正在使用 django,但我不知道如何将数据直接从管理面板解析到主页模板
- reactjs - pl上传库参考
- java - 以特定模式为矩形设置动画
- python - Scapy tcp校验和被wireshark标记为不正确
- appium - appium中的系统警报识别