Mac版Excel能否实现多维度子串去重计数？

需求明确 #

在Mac版Excel中处理数万条记录，需反复统计同时满足以下两个条件的唯一记录数（FreeTextNotes含多个指定子串时仅计数1次）：

• 条件1：FreeTextNotes列包含指定子串列表中的任意一个（子串最多20个，随分析类别动态调整）
• 条件2：Category与SubCategory列匹配选定的多组类别/子类别组合（例如同时匹配「Category 1+Subcategory 1.1」「Category 2+Subcategory 2.1」）

你作为进阶用户已尝试数据透视表、切片器、COUNTIFS均未解决，当前仅能实现2个子串的查找但无法结合类别筛选，所用公式为：
=SUMPRODUCT(--((ISNUMBER(FIND(SubstringCellRef1,_FreeTextNotes)) + ISNUMBER(FIND(SubstringCellRef2,_FreeTextNotes)))>0))

解决方案 #

预设区域定义 #

假设你的数据与参数区域如下（可根据实际调整）：

• 原始数据：A2:D10000（A=Category，B=SubCategory，C=FreeTextNotes）
• 选定的类别/子类别组合：F2:G5（多行，每行一组Category+SubCategory）
• 指定子串列表：I2:I21（最多20个，空单元格自动忽略）

方法1：SUMPRODUCT+数组（兼容旧版Mac Excel） #

适合未升级到365版本的用户，公式兼顾兼容性与效率：

=SUMPRODUCT(
    --(MMULT(--((A2:A10000=F2:F5)*(B2:B10000=G2:G5)),ROW(F2:F5)^0)>0),
    --(MMULT(--ISNUMBER(FIND(I2:I21,C2:C10000)),ROW(I2:I21)^0)>0)
)

公式逻辑：

1. 类别匹配判断：MMULT(--((A:A=F:F)*(B:B=G:G)),ROW(F:F)^0)>0
   • 逐行判断记录的Category+SubCategory是否匹配任意一组选定组合，通过MMULT求和后判断是否大于0，得到「是否匹配类别」的1/0数组。
2. 子串匹配判断：MMULT(--ISNUMBER(FIND(I:I,C:C)),ROW(I:I)^0)>0
   • 逐行判断FreeTextNotes是否包含任意一个指定子串，同样通过MMULT求和去重（即使包含多个子串也仅记为1），得到「是否含目标子串」的1/0数组。
3. 最终统计：SUMPRODUCT将两个数组相乘后求和，得到同时满足双条件的记录总数。

方法2：LET函数简化（Mac Excel 365/2021+） #

用LET函数定义变量，让公式更易读和维护，适合新版本用户：

=LET(
    CatSubMatch, MMULT(--((A2:A10000=F2:F5)*(B2:B10000=G2:G5)),ROW(F2:F5)^0)>0,
    SubStrMatch, MMULT(--ISNUMBER(FIND(I2:I21,C2:C10000)),ROW(I2:I21)^0)>0,
    SUMPRODUCT(CatSubMatch*SubStrMatch)
)

方法3：动态数组+筛选（Mac Excel 365） #

若需要直观查看符合条件的记录，可先用FILTER筛选再统计行数：

=ROWS(
    FILTER(
        C2:C10000,
        (MMULT(--((A2:A10000=F2:F5)*(B2:B10000=G2:G5)),ROW(F2:F5)^0)>0)*
        (MMULT(--ISNUMBER(FIND(I2:I21,C2:C10000)),ROW(I2:I21)^0)>0)
    )
)

关键注意事项 #

• 性能优化：数万条记录下，务必用实际数据区域（如A2:A10000）替代整列引用（如A:A），大幅提升计算速度。
• 大小写设置：FIND函数区分大小写，若需不区分，替换为SEARCH函数。
• 空值处理：子串列表中的空单元格会被自动忽略，无需额外清理。

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