如何在已筛选的外部Excel工作表中统计多条件匹配记录数

场景说明 #

• 在output工作簿中，需对已应用自动筛选（如时间范围）的外部input工作簿，统计同时满足以下条件的记录数：
  1. input指定列需包含目标字符串（支持任意位置匹配，包括带前导空格的情况，例如搜索kat时， kat也需被计入）
  2. input另一列等于指定固定值

原公式的局限 #

最初使用的荷兰版Excel公式（对应中文函数见注释）仅支持左侧前缀匹配，无法处理前导空格或字符串出现在中间/末尾的情况，导致部分符合条件的记录被遗漏：

=SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E;RIJ('[online test file input.xlsx]Blad1'!$E:$E)-MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1));--(LINKS('[online test file input.xlsx]Blad1'!$E:$E;3)=LINKS($A6;3))*(('[online test file input.xlsx]Blad1'!$C:$C)=B$5))+SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E;RIJ('[online test file input.xlsx]Blad1'!$E:$E)-MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1));--(LINKS('[online test file input.xlsx]Blad1'!$E:$E;6)=RECHTS($A6;6))*(('[online test file input.xlsx]Blad1'!$C:$C)=B$5))

荷兰版→中文函数映射：SOMPRODUCT=SUMPRODUCT，SUBTOTAAL=SUBTOTAL，VERSCHUIVING=OFFSET，RIJ=ROW，LINKS=LEFT
该公式通过两个SUMPRODUCT相加，分别匹配kat（左3位）和katige（右6位），但仅支持精确前缀/后缀匹配，无法覆盖带空格或字符串位置不固定的场景。

错误公式的问题分析 #

尝试用ZOEKEN（对应中文SEARCH）实现模糊匹配的公式返回错误结果（应返回4却返回10，即返回了该筛选组的总记录数）：

=SOMPRODUCT( SUBTOTAAL(3; VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E; RIJ('[online test file input.xlsx]Blad1'!$E:$E) - MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1)); --(ZOEKEN(LINKS($A6; 4); ('[online test file input.xlsx]Blad1'!$E:$E)) > 0) * (('[online test file input.xlsx]Blad1'!$C:$C) = C$5) )

问题核心：ZOEKEN函数处理整列数据时，会对隐藏行也执行匹配计算，而SUBTOTAL生成的可见行标记未与模糊匹配条件正确关联，导致筛选后的隐藏行结果未被抵消，最终统计了整个筛选组的总记录数。

最终解决公式 #

使用ISGETAL(VIND.SPEC(...))（对应中文ISNUMBER(SEARCH(...))）替代直接的位置判断，确保仅统计筛选后可见行中满足模糊匹配的记录：

=SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C;RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C)-MIN(RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C));;1))*('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C=LINKS(B$4;3))*ISGETAL(VIND.SPEC(LINKS(E7;4);'[weekupdate.xlsx]Vluchten richting Nederland'!$G:$G)))

荷兰版→中文函数映射：ISGETAL=ISNUMBER，VIND.SPEC=SEARCH

公式逻辑： #

1. SUBTOTAL(3, OFFSET(...))：生成与数据行一一对应的数组，可见行返回1，隐藏行返回0，用于标记筛选后的有效行
2. '[...]$C:$C=LINKS(B$4;3)：判断目标列是否等于指定前缀值
3. ISGETAL(VIND.SPEC(...))：判断指定列单元格是否包含目标字符串（LINKS(E7;4)提取的搜索值），包含则返回TRUE（等价于1），否则返回FALSE（等价于0）
4. SUMPRODUCT将三个条件的数组相乘后求和，仅统计同时满足所有条件的可见行数量

原左侧匹配公式有效的原因 #

原使用LEFT的公式能正常工作，是因为LEFT的精确前缀判断与SUBTOTAL的可见行标记正确关联——隐藏行的匹配结果会被SUBTOTAL返回的0值抵消，仅可见行的有效匹配会被计入统计。但该逻辑仅能匹配前缀，无法处理前导空格或字符串位置不固定的情况。

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