Excel 使用Excel求交集

阅： 454 | 回： 5

使用Excel求交集全部 , 编号:06423

niaja_007

发表于2025/6/16 20:18:06 楼主

回复楼主

等级:学有小成

积分:10

财富值:2.00

身份:普通用户

08,09,12,13,14,15,16,17,18,19,20,21,22,23,26,27
08,10,11,12,13,14,15,16,18,19,20,21,22,23,24,26,27,28
08,10,11,12,13,14,15,16,18,19,20,22,23,24,26,27,28
08,10,12,14,15,16,17,19,20,21,22,24,25,27,28
08,09,10,11,12,13,15,17,18,19,20,21,22,23,25,26
08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28
08,09,11,12,13,15,16,17,18,19,20,22,25,26,27,28
08,10,11,12,13,14,16,17,18,19,20,21,22,23,24,26,28
08,09,10,11,12,14,17,18,19,20,21,23,24,25,26,28
09,10,11,13,14,15,16,17,18,21,22,23,25,26,27
09,10,11,12,13,14,16,17,18,19,21,23,24,25,26,27,28
08,09,10,11,12,14,15,17,20,21,22,23,24,25,26,28
08,09,12,13,14,15,16,18,19,20,21,22,24,26
08,10,11,12,14,18,19,22,23,24,25,26,27,28
09,10,11,12,13,14,15,16,18,19,22,23,24,25,27

以上数据，如何可以一次就求出其中的交集？

我的个性签名

JOYblue

发表于 2025/6/17 9:26:44

回复2楼

等级:大师

积分:267

财富值:3

身份:普通用户

提取唯一值试试提取唯一值这个功能

我的个性签名

niaja_007

发表于 2025/6/18 10:27:06

回复3楼

等级:学有小成

积分:10

财富值:2

身份:普通用户

回复：2楼

你所讲方法不可取，请勿乱回答。

我的个性签名

W147369

发表于 2025/6/18 13:33:31

回复4楼

等级:学者

积分:44

财富值:2

身份:普通用户

23333

人家是官方，还有就是你这个没有交集吧

我的个性签名

海蓝天

发表于 2025/9/1 20:18:05

回复5楼

等级:初学者

积分:8

财富值:2

身份:普通用户

回复：楼主

【交集为空】公式如下：

=LET(
A,{08,09,12,13,14,15,16,17,18,19,20,21,22,23,26,27},
B,{08,10,11,12,13,14,15,16,18,19,20,21,22,23,24,26,27,28},
C,{08,10,11,12,13,14,15,16,18,19,20,22,23,24,26,27,28},
D,{08,10,12,14,15,16,17,19,20,21,22,24,25,27,28},
E,{08,09,10,11,12,13,15,17,18,19,20,21,22,23,25,26},
F,{08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28},
G,{08,09,11,12,13,15,16,17,18,19,20,22,25,26,27,28},
H,{08,10,11,12,13,14,16,17,18,19,20,21,22,23,24,26,28},
I,{08,09,10,11,12,14,17,18,19,20,21,23,24,25,26,28},
J,{09,10,11,13,14,15,16,17,18,21,22,23,25,26,27},
K,{09,10,11,12,13,14,16,17,18,19,21,23,24,25,26,27,28},
L,{08,09,10,11,12,14,15,17,20,21,22,23,24,25,26,28},
M,{08,09,12,13,14,15,16,18,19,20,21,22,24,26},
N,{08,10,11,12,14,18,19,22,23,24,25,26,27,28},
O,{09,10,11,12,13,14,15,16,18,19,22,23,24,25,27},
FILTER(A,
ISNUMBER(MATCH(A,B,0))*
ISNUMBER(MATCH(A,C,0))*
ISNUMBER(MATCH(A,D,0))*
ISNUMBER(MATCH(A,E,0))*
ISNUMBER(MATCH(A,F,0))*
ISNUMBER(MATCH(A,G,0))*
ISNUMBER(MATCH(A,H,0))*
ISNUMBER(MATCH(A,I,0))*
ISNUMBER(MATCH(A,J,0))*
ISNUMBER(MATCH(A,K,0))*
ISNUMBER(MATCH(A,L,0))*
ISNUMBER(MATCH(A,M,0))*
ISNUMBER(MATCH(A,N,0))*
ISNUMBER(MATCH(A,O,0))
))

HHH

海蓝天

发表于 2025/9/2 20:38:34

回复6楼

等级:初学者

积分:8

财富值:2

身份:普通用户

回复：楼主

有更简洁的公式【公式是通用的，注意本例计算结果交集为空】

=LET(
A,{08,09,12,13,14,15,16,17,18,19,20,21,22,23,26,27},
B,{08,10,11,12,13,14,15,16,18,19,20,21,22,23,24,26,27,28},
C,{08,10,11,12,13,14,15,16,18,19,20,22,23,24,26,27,28},
D,{08,10,12,14,15,16,17,19,20,21,22,24,25,27,28},
E,{08,09,10,11,12,13,15,17,18,19,20,21,22,23,25,26},
F,{08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,27,28},
G,{08,09,11,12,13,15,16,17,18,19,20,22,25,26,27,28},
H,{08,10,11,12,13,14,16,17,18,19,20,21,22,23,24,26,28},
I,{08,09,10,11,12,14,17,18,19,20,21,23,24,25,26,28},
J,{09,10,11,13,14,15,16,17,18,21,22,23,25,26,27},
K,{09,10,11,12,13,14,16,17,18,19,21,23,24,25,26,27,28},
L,{08,09,10,11,12,14,15,17,20,21,22,23,24,25,26,28},
M,{08,09,12,13,14,15,16,18,19,20,21,22,24,26},
N,{08,10,11,12,14,18,19,22,23,24,25,26,27,28},
O,{09,10,11,12,13,14,15,16,18,19,22,23,24,25,27},
SCAN,SEQUENCE(14),
RESULT,REDUCE(TRUE,SCAN,LAMBDA(SAVE,SCANS,SAVE*ISNUMBER(MATCH(A,CHOOSE(SCANS,B,C,D,E,F,G,H,I,J,K,L,M,N,O),0)))),
FILTER(A,RESULT)
)

HHH

快速回复