求公式将表1、表2、表3的日期符合条件合部列出C列中

橒♂蝣

=UNIQUE(FILTER(VSTACK(表1!D7:D52,表2!E7:E52,表3!C6:C51),(IFERROR(YEAR(VSTACK(表1!D7:D52,表2!E7:E52,表3!C6:C51)),0)=C2)*(VSTACK(表1!I7:I52,表2!L7:L52,表3!F6:F51)=G2)))

mykitami

乄.壊脾氣.爺ゞ 发表于 2023-11-10 17:12
大神，如果要增加条件值的话，如何修改
如：
原来：G2单元格有“是”或“否”这2个值，“是”和“否” ...

C6公式改为：

=LET(a,HSTACK(VSTACK(表1!D7:D500,表2!E7:E500,表3!C6:C500),VSTACK(表1!I7:I500,表2!L7:L500,表3!F6:F500)),ay,TAKE(a,,1),FILTER(TAKE(a,,1),(ay>0)*(YEAR(ay)=C2)*(SUBSTITUTE(TAKE(a,,-1),"Y","是")=G2)))

D6公式改为：
实际上就是上面的公式外面嵌套一个unique去重函数而已
=UNIQUE(LET(a,HSTACK(VSTACK(表1!D7:D500,表2!E7:E500,表3!C6:C500),VSTACK(表1!I7:I500,表2!L7:L500,表3!F6:F500)),ay,TAKE(a,,1),FILTER(TAKE(a,,1),(ay>0)*(YEAR(ay)=C2)*(SUBSTITUTE(TAKE(a,,-1),"Y","是")=G2))))

如果想合并，也可以仅在C6输入以下公式
=LET(a,HSTACK(VSTACK(表1!D7:D500,表2!E7:E500,表3!C6:C500),VSTACK(表1!I7:I500,表2!L7:L500,表3!F6:F500)),ay,TAKE(a,,1),bqc,FILTER(TAKE(a,,1),(ay>0)*(YEAR(ay)=C2)*(SUBSTITUTE(TAKE(a,,-1),"Y","是")=G2)),IFNA(HSTACK(bqc,UNIQUE(bqc)),""))