如何在Excel筛选状态下统计列中唯一值（VBA实现）

需求：通过VBA在单元格中写入公式，统计指定列的唯一值数量，且该公式需支持筛选状态——仅统计筛选后可见的唯一值。

之前尝试的无效方案 #

曾尝试使用SUMPRODUCT+COUNTIF的组合公式，但筛选状态下无法正确统计可见值：

cell.Formula = "=SUMPRODUCT(1/COUNTIF(" & TARGET_COLUMN_RANGE_ADDRESS & "," & TARGET_COLUMN_RANGE_ADDRESS & "))"

可行解决方案 #

以下是经过验证的VBA代码，可生成支持筛选的统计公式：

' 获取目标列的列字母
Dim columnLetter As String
columnLetter = Split(columnRange.Address, "$")(1)

' columnRange：目标列的单元格区域
' columnNamesRowNumber：调整行号的偏移值（因表格起始行不是第1行）
cell.Formula = "=SUM((FREQUENCY(SUBTOTAL(3, INDIRECT(""""" & columnLetter & """" & "ROW(" & columnRange.Row & ":" & columnNamesRowNumber + columnRange.Rows.Count + 1 & "))) * (" & columnLetter & columnRange.Row & ":" & columnLetter & columnNamesRowNumber + columnRange.Rows.Count + 1 & "), SUBTOTAL(3, INDIRECT(""""" & columnLetter & """" & "ROW(" & columnRange.Row & ":" & columnNamesRowNumber + columnRange.Rows.Count + 1 & "))) * (" & columnLetter & columnRange.Row & ":" & columnLetter & columnNamesRowNumber + columnRange.Rows.Count + 1 & ")) > 0) * 1) - 1"

公式原理说明 #

1. SUBTOTAL(3, INDIRECT(...))：通过SUBTOTAL(3)（即COUNTA函数）判断对应行是否可见，可见行返回1，隐藏行返回0；INDIRECT用于动态生成单元格引用。
2. 与目标列数值相乘：仅保留可见行的数值，隐藏行的数值会被置为0。
3. FREQUENCY函数：统计处理后数值的出现频率，唯一值会返回大于0的结果。
4. SUM(...) -1：统计所有大于0的频率结果数量，最后减1是为了排除空值对应的统计项。

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