求兼容Excel 2010的多列Top(N)唯一文本值排序纯工作表公式

核心方案 #

通过组合INDEX、SUMPRODUCT、COUNTIF、LARGE、SMALL等Excel 2010原生函数，在不使用辅助列、无需数组输入的前提下，从指定多区域提取按出现次数排序的前N个唯一文本值。

公式代码 #

假设：

• 要提取的Top数量N放在A1单元格
• 结果从A2单元格开始输出

在A2单元格输入以下公式（直接按回车即可，无需Ctrl+Shift+Enter）：

=IF(ROW(A1)>$A$1,"",INDEX($B$3:$H$12,SUMPRODUCT(SMALL(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),ROW($B$3:$H$12)*100+COLUMN($B$3:$H$12),""),MATCH(LARGE(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),1),IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),0)))/100,SUMPRODUCT(SMALL(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),ROW($B$3:$H$12)*100+COLUMN($B$3:$H$12),""),MATCH(LARGE(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),1),IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),0)))-SUMPRODUCT(SMALL(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),ROW($B$3:$H$12)*100+COLUMN($B$3:$H$12),""),MATCH(LARGE(IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),1),IF(AND(COUNTIF($A$1:A1,$B$3:$H$12)=0,$B$3:$H$12<>""),COUNTIF($B$3:$H$12,$B$3:$H$12),0),0)))/100*100))

使用步骤 #

1. 在A1单元格输入需要提取的Top数量（例如5）。
2. 在A2单元格粘贴上述公式，按回车确认。
3. 选中A2单元格，向下拖动填充柄至A2+N-1行（例如N=5时拖到A6），即可得到排序后的Top(N)唯一文本。

公式逻辑说明 #

• 去重控制：COUNTIF($A$1:A1,$B$3:$H$12)=0 排除已提取的文本，确保结果唯一。
• 空值过滤：$B$3:$H$12<>"" 忽略原区域中的空单元格。
• 次数统计：COUNTIF($B$3:$H$12,$B$3:$H$12) 计算每个文本在目标区域的出现次数。
• 排序定位：LARGE(...,1) 获取当前未提取文本中的最高出现次数，MATCH 定位该次数对应的位置序号。
• 坐标打包与拆分：用ROW*100+COLUMN将单元格行/列号打包为单个数值，后续拆分后通过INDEX提取对应文本。
• 普通公式兼容：SUMPRODUCT 将数组运算结果转换为单个值，无需数组输入即可运行。

额外说明 #

• 若多个文本出现次数相同，将按它们在B3:H12区域中首次出现的顺序提取。
• 公式完全兼容Excel 2010及以上版本，无辅助列、无VBA依赖。

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