Excel溢出公式场景下COUNTIF计算筛选列表出现#VALUE!错误如何解决

问题原因 #

COUNTIF函数的第一个参数必须是实体单元格区域引用，不能是内存数组。你修改公式后用FILTER返回的FilteredKeyColumn是运算生成的内存数组，不属于实体单元格引用，不符合COUNTIF的参数要求，因此返回#VALUE!错误。
原公式可以正常运行，是因为第一个参数直接使用了Table1[Winner]这个表格列的实体引用，满足COUNTIF的参数规则。

修复方案 #

将COUNTIF替换为支持内存数组计算的SUM+布尔逻辑写法即可，不需要改动整体的LET结构，修改后的公式如下：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    Categories, SORT(UNIQUE(KeyColumn)),

    BoolFilterColumn, ISNUMBER(XMATCH(FilterColumn,FilterList)),
    FilteredKeyColumn, FILTER(KeyColumn,BoolFilterColumn),

    // 替换COUNTIF为逐行遍历统计的写法
    Frequency, BYROW(Categories, LAMBDA(c, SUM(--(FilteredKeyColumn = c)))),
    Frequency
)

你也可以把筛选逻辑直接合并到统计逻辑里，省去单独生成FilteredKeyColumn的步骤，写法更简洁：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    Categories, SORT(UNIQUE(KeyColumn)),

    MatchFilter, ISNUMBER(XMATCH(FilterColumn,FilterList)),
    Frequency, BYROW(Categories, LAMBDA(c, SUM(--(KeyColumn = c)*MatchFilter))),
    Frequency
)

结果验证 #

用你提供的参考数据计算，非体育类游戏（tictactoe、chess）的获胜次数为：

• 我：4次
• 你：3次
  和公式返回的溢出数组结果一致。

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