☰

根据值在两列中匹配的次数返回特定值

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
根据值在两列中匹配的次数返回特定值

我在另一列中获得了与最多两个值（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

理想的：

  1. 添加一个列作为查找的键，键是ID和SYS的连接；
  2. 用于countif检查ID+“A”和ID+“B”是否存在，将结果放入位图中 - 位0表示B存在，位1表示A存在，结果如下图：0 - 无，1 - B，2 - A，3 - 两者皆有；
  3. 用于lookup根据上述映射将值 0、1、2、3 转换为预期文本；

脚步：

假设表 1 ID 1 位于 A2，表 2 ID 1 位于单元格 A11：

  1. 表1中C2单元格的公式为=A2&B2，则向下填写；
  2. 表2中C11的分子式为=(COUNTIF($C$2:$C$8,A11&"A")<>0)*2+(COUNTIF($C$2:$C$8,A11&"B")<>0)，则往下填；
  3. 表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 中粘贴上述公式

相关内容