Excel批量使用COUNTA UNIQUE公式卡顿问题及替代方案咨询

一、这种卡顿情况完全正常 #

你的公式存在两个核心性能隐患：

1. 整列引用：$C:$C、$Q:$Q这类整列引用会让Excel遍历数十万行（哪怕大部分是空行），每个公式都要做一次全列扫描，200个单元格就是200次重复的海量数据遍历。
2. 多层嵌套函数：OR、多层IF、FILTER、UNIQUE、COUNTA的组合，每一步都要处理大量数据，计算量呈指数级叠加。
   所以文件近乎停止响应是完全符合逻辑的结果。

二、优化/替代方案 #

1. 限制数据范围（最快速的优化） #

把整列引用改成实际数据的有效范围，比如假设你的full data表数据到第10000行，就把所有$C:$C改成$C$2:$C$10000（跳过表头），避免Excel浪费算力遍历空行。修改后的公式示例：

=COUNTA(UNIQUE(FILTER(IF(OR('full data'!$C$2:$C$10000=$A3,'full data'!$Q$2:$Q$10000="Sold"),
IF('full data'!$K$2:$K$10000>=F3,IF('full data'!$K$2:$K$10000<=H3,TEXT('full data'!$K$2:$K$10000,"m")))),
TEXT('full data'!$K$2:$K$10000,"")<>"")))-1

如果数据会动态增加，直接把full data区域转成结构化表格（选中数据→Ctrl+T），之后用表格引用（比如Table1[C列]），它会自动适配数据范围，比整列引用高效得多。

2. 简化函数逻辑，用更高效的组合 #

原公式可以用COUNTUNIQUE直接替代COUNTA(UNIQUE(...))，同时用乘法代替嵌套IF，减少函数层级，计算效率大幅提升：

=COUNTUNIQUE(FILTER('full data'!$K$2:$K$10000,
(OR('full data'!$C$2:$C$10000=$A3,'full data'!$Q$2:$Q$10000="Sold"))*
('full data'!$K$2:$K$10000>=F3)*
('full data'!$K$2:$K$10000<=H3)*
('full data'!$K$2:$K$10000<>"")
))-1

注：COUNTUNIQUE仅在Excel 365/2021及以上版本可用，旧版本可以保留COUNTA(UNIQUE(...))的组合，只替换嵌套IF为乘法逻辑。

3. 用Power Query预处理数据（一劳永逸） #

如果数据更新不频繁，用Power Query把筛选、去重、计数的操作一次性完成，后续直接引用结果即可：

1. 选中full data表数据→点击「数据」选项卡→「从表格/区域」导入Power Query
2. 添加条件列：设置规则为OR([C列]=对应条件值, [Q列]="Sold") and [K列]>=开始日期 and [K列]<=结束日期 and [K列]<>""
3. 提取K列的月份（用「添加列」→「格式」→「月份」→「月份数字」）
4. 去重计数：选中月份列→点击「转换」选项卡→「分组依据」，设置分组后计算「计数（不同）」
5. 点击「关闭并上载」，把结果加载到Excel，之后直接引用这些结果，无需再写复杂公式，计算只在刷新Power Query时进行。

4. 用动态数组批量计算（Excel 365专属） #

如果是Excel 365，用BYROW+LAMBDA一次性计算200行的结果，只执行一次核心筛选逻辑，比200个单独公式效率高几倍：
假设你的条件在A3:A202，日期区间在F3:H202，在J3输入以下公式，自动生成所有行的结果：

=BYROW(A3:A202,LAMBDA(current_val,
COUNTUNIQUE(FILTER('full data'!$K$2:$K$10000,
(OR('full data'!$C$2:$C$10000=current_val,'full data'!$Q$2:$Q$10000="Sold"))*
('full data'!$K$2:$K$10000>=INDEX(F:F,ROW(current_val)))*
('full data'!$K$2:$K$10000<=INDEX(H:H,ROW(current_val)))*
('full data'!$K$2:$K$10000<>"")
))-1))

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