如何在Excel中通过多匹配条件查找缺失的PID

需求说明 #

在包含PID、Customer_number、AccountSeqNum、Transaction Date的数据集里，筛选出满足以下条件的记录：

• PID为空（排除已填充PID的行）
• Customer_number匹配指定值
• AccountSeqNum匹配指定值
• 忽略Transaction Date列的缺失值

示例数据集 #

尝试过的公式 #

• =INDEX(A:A,MATCH(A2,A:A,0), MATCH(A2,B:B,1))
• =IF(COUNTIFS($B$2:$B$13,D2,$C$2:$C$13,E2,$A$2:$A$13,"")>0,"Missing",""
• =IFERROR(INDEX($A$2:$A$13, SMALL(IF(($A$2:$A$13="")*(ISNUMBER(--$B$2:$B$13))*(C2=$C$2:$C$13), ROW($A$2:$A$13)-ROW($A$2)+1), ROW(A1))), "")
• =IFERROR(INDEX($A$2:$A$10, MATCH(0, IF($A$2:$A$10<>", COUNTIFS($A$2:$A$10, $A$2:$A$10, $B$2:$B$10, $B$2:$B$10, $C$2:$C$10, $C$2:$C$10), ""), 0)), "")
• =IFERROR(INDEX($A$2:INDEX($A:$A, MATCH(REPT("z", 255), $A:$A)), MATCH(1, ($B$2:$B$12=Agent_number)*(C2=$C$2:$C$12)*(D2=$D$2:$D$12), 0)), "")

解决方案 #

方法1：动态数组公式（Excel 365/2021） #

适合支持动态数组的Excel版本，假设指定的Customer_number存放在F2，AccountSeqNum存放在G2，直接用以下公式提取所有符合条件的记录：

=FILTER(A2:D13, (A2:A13="")*(B2:B13=F2)*(C2:C13=G2), "无匹配结果")

公式逻辑：

• A2:A13="" 筛选PID为空的行
• B2:B13=F2 匹配指定的Customer_number
• C2:C13=G2 匹配指定的AccountSeqNum
• 最后参数为无匹配时的提示文本，可按需修改

方法2：传统数组公式（旧版Excel） #

针对不支持动态数组的旧版Excel，使用数组公式（输入后需按Ctrl+Shift+Enter确认），下拉填充获取结果：

PID列提取公式：

=IFERROR(INDEX(A$2:A$13, SMALL(IF((A$2:A$13="")*(B$2:B$13=$F$2)*(C$2:C$13=$G$2), ROW(A$2:A$13)-ROW(A$2)+1), ROW(A1))), "")

对应其他列的公式：

• Customer_number列：

  =IFERROR(INDEX(B$2:B$13, SMALL(IF((A$2:A$13="")*(B$2:B$13=$F$2)*(C$2:C$13=$G$2), ROW(A$2:A$13)-ROW(A$2)+1), ROW(A1))), "")
  

• AccountSeqNum列：

  =IFERROR(INDEX(C$2:C$13, SMALL(IF((A$2:A$13="")*(B$2:B$13=$F$2)*(C$2:C$13=$G$2), ROW(A$2:A$13)-ROW(A$2)+1), ROW(A1))), "")
  

• Transaction Date列：

  =IFERROR(INDEX(D$2:D$13, SMALL(IF((A$2:A$13="")*(B$2:B$13=$F$2)*(C$2:C$13=$G$2), ROW(A$2:A$13)-ROW(A$2)+1), ROW(A1))), "")
  

方法3：手动筛选法（无需公式） #

如果不需要公式自动化，可直接通过筛选功能快速定位：

1. 选中整个数据区域，点击「数据」选项卡→「筛选」
2. 点击PID列的筛选箭头，勾选「空白」选项
3. 点击Customer_number列的筛选箭头，输入指定值并确认
4. 点击AccountSeqNum列的筛选箭头，输入指定值并确认
5. 筛选后显示的行即为符合条件的缺失PID记录，Transaction Date的缺失不会影响筛选结果

内容的提问来源于stack exchange，提问作者Sweetcorn