![根据值在两列中匹配的次数返回特定值](https://linux22.com/image/1487712/%E6%A0%B9%E6%8D%AE%E5%80%BC%E5%9C%A8%E4%B8%A4%E5%88%97%E4%B8%AD%E5%8C%B9%E9%85%8D%E7%9A%84%E6%AC%A1%E6%95%B0%E8%BF%94%E5%9B%9E%E7%89%B9%E5%AE%9A%E5%80%BC.png)
我在另一列中获得了与最多两个值(SYS-A 和 SYS-B)关联的 ID 号,但每个 ID 可能会在 SYS-A 和/或 SYS-B 下出现多次。我需要 Excel 查看每个 ID,并告诉我它是否与两个系统都关联,或者是否只与一个系统关联,如果只与一个系统关联,则与哪个系统关联。我可以将“结果”列放在另一个选项卡上吗?因为我有一个选项卡包含社交付款,另一个选项卡包含每个 ID 关联的系统?“结果”应依赖于选项卡 1。
TAB 1
ID------SYS
1--------A
1
--------A 1--------B
1--------A
2--------B
3--------A
3--------A
标签 2
ID-----付款---结果
1---------5---------------两者
2---------3-----------------B
3---------2-----------------A
答案1
理想的:
- 添加一个列作为查找的键,键是ID和SYS的连接;
- 用于
countif
检查ID+“A”和ID+“B”是否存在,将结果放入位图中 - 位0表示B存在,位1表示A存在,结果如下图:0 - 无,1 - B,2 - A,3 - 两者皆有; - 用于
lookup
根据上述映射将值 0、1、2、3 转换为预期文本;
脚步:
假设表 1 ID 1 位于 A2,表 2 ID 1 位于单元格 A11:
- 表1中C2单元格的公式为
=A2&B2
,则向下填写; - 表2中C11的分子式为
=(COUNTIF($C$2:$C$8,A11&"A")<>0)*2+(COUNTIF($C$2:$C$8,A11&"B")<>0)
,则往下填; - 表2中D11的公式为
=LOOKUP(C11,{0,1,2,3},{"NONE","B","A","BOTH"})
,则往下填;
例子:
ID 系统密钥 1 1A 1 1A 1 乙 1乙 1 1A 2B 2B 3A 3A 3A 3A ID 付款值 结果 1 不适用 3 两者 2 不适用 1 B 3 不适用 2 一个
答案2
基于 SUMPRODUCT、IF 和 AND 函数以及辅助列的可能解决方案
H4 单元格中的公式将被拖拽下去。
=IF(AND(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="A"),$D$4:$D$13)>=1,SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="B"),$D$4:$D$13)>=1),"BOTH",IF(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="A"),$D$4:$D$13)>=1,"A",IF(SUMPRODUCT(($B$4:$B$13=G4)*($C$4:$C$13="B"),$D$4:$D$13)>=1,"B","Invalid")))
答案3
在新标签页中使用此公式
=IF(SUMPRODUCT(('TAB 1'!$A$2:$A$8='TAB 3'!A2)*('TAB 1'!$B$2:$B$8="A"))=COUNTIF('TAB 1'!$A$2:$A$8,'TAB 3'!A2),"A",IF(SUMPRODUCT(('TAB 1'!$A$2:$A$8='TAB 3'!A2)*('TAB 1'!$B$2:$B$8="B"))=COUNTIF('TAB 1'!$A$2:$A$8,'TAB 3'!A2),"B","BOTH"))
例如,在 TAB 1 中,您有从 A2 到 B8 的无标题数据,在 TAB 3 中,您可以复制粘贴所有 ID,然后从 A2 开始删除重复的 ID 值,并在 B2 中粘贴上述公式