提问人：Rob Manger 提问时间：11/17/2023 更新时间：11/19/2023 访问量：71
Excel 跨多个索引交叉引用多个数据集
Excel cross referencing multiple data sets across multiple indices
问：
我正在尝试半自动地识别分布式小队成员何时有介于两个特定日期之间的公共假期（或其他活动）。我有几个数据集正在使用：
- 我有一个公共假期列表，其中 DATE 和 LOCATION 分为两列，如下所示：
| 一个 | B | |
|---|---|---|
| 2 | 日期 | 位置 |
| 3 | 19/09/2023 | 印度 |
| 4 | 29/09/2023 | 澳大利亚 |
| 5 | 02/10/2023 | 印度 |
| 6 | 23/10/2023 | 新西兰 |
| 7 | 等 | 等 |
- 我有一个跨列的小队成员列表，他们的位置在第二行，如下所示：
| 一个 | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 名字 | 抢 | 山 姆 | 提姆 | 恩典 | 埃莉 诺 |
| 2 | 位置 | 澳大利亚 | 印度 | 澳大利亚 | 新西兰 | 印度 |
- 我还有一个开始和结束日期：
| 一个 | B | |
|---|---|---|
| 1 | 开始日期 | 结束日期 |
| 2 | 02/09/2023 | 16/10/2023 |
我想做的是在每个团队成员下面有一行，该行将自动指示国家/地区是否有公共假期落在开始日期和结束日期之间的日期，如果有多个，则提供计数。
我有几个非常笨拙的想法，可能涉及一堆“辅助”列，但我想找到一个更整洁的解决方案（希望在单个公式中），如果有的话。有什么想法吗？
答：
1赞
user11222393
11/17/2023
#1
=SUM((FILTER($A$2:$A$5,$B$2:$B$5=B14)>=$A$8)*(FILTER($A$2:$A$5,$B$2:$B$5=B14)<=$B$8))
或
=LET(z,FILTER($A$2:$A$5,$B$2:$B$5=B14),SUM((z>=$A$8)*(z<=$B$8)))
或溢出：
=MAP(B14:F14,LAMBDA(a,LET(z,FILTER($A$2:$A$5,$B$2:$B$5=a),SUM((z>=$A$8)*(z<=$B$8)))))
结果：
评论
1赞
Rob Manger
11/18/2023
再次辉煌！非常感谢您的回复。有很多很酷的选择可以尝试。
1赞
Mayukh Bhattacharya
11/17/2023
#2
尝试将以下公式与MMULT()
• 细胞中使用的配方E3
=LET(
α, --REPT(PublicHolidays[DATE],N(E2:I2=PublicHolidays[LOCATION])),
φ, IFERROR((α>=Dates[Start Date])*(α<=Dates[End Date]),0),
MMULT( SEQUENCE(,ROWS(φ),,0),φ))
或者，使用帮助程序函数LAMBDA()BYCOL()
• 细胞中使用的配方E3
=LET(
α, --REPT(PublicHolidays[DATE],N(E2:I2=PublicHolidays[LOCATION])),
BYCOL((α>=Dates[Start Date])*(α<=Dates[End Date]),LAMBDA(φ, SUM(IFERROR(φ,0)))))
请注意，在最近的更新中，用户可以避免使用如下所示的构造。MS365Beta ChannelLAMBDA()BYCOL()
=LET(
α, --REPT(PublicHolidays[DATE],N(E2:I2=PublicHolidays[LOCATION])),
BYCOL(IFERROR((α>=Dates[Start Date])*(α<=Dates[End Date]),0),SUM))
另请注意：
其中：指表结构化范围：PublicHolidaysA1:B5
其中：指表结构化范围：DatesA8:B9
顺便说一句，您也可以使用 IN 代替IF()REPT()
=LET(
α, IF(E2:I2=PublicHolidays[LOCATION],PublicHolidays[DATE],""),
BYCOL(IFERROR((α>=Dates[Start Date])*(α<=Dates[End Date]),0),SUM))
评论
1赞
Rob Manger
11/18/2023
太棒了！，非常感谢您的及时回复。我会试一试。
1赞
DjC
11/19/2023
#3
对于这样的事情，您也可以保持简单并使用该功能。例如：COUNTIFS
=COUNTIFS(A2:A5, ">="&D2, A2:A5, "<="&E2, B2:B5, E5:I5)
注意：单元格中只需要一个公式，因为范围中的所有位置都传递给第三个条件参数，导致结果动态溢出。干杯！E6E5:I5
评论