Excel获取指定区域内出现频次TOP20的产品码方法求助

适用于Excel 365/2021（支持动态数组） #

直接用以下公式，输入后会自动生成前20个结果，无需手动下拉：

=TAKE(SORTBY(UNIQUE(A2:A368),COUNTIF(A2:A368,UNIQUE(A2:A368)),-1),20)

公式拆解：

• UNIQUE(A2:A368)：提取A列中所有不重复的产品码
• COUNTIF(A2:A368,UNIQUE(A2:A368))：计算每个不重复产品码的出现频次
• SORTBY(..., -1)：按频次从高到低排序产品码
• TAKE(...,20)：截取排序后的前20个结果

适用于旧版Excel（无动态数组支持） #

方法1：数组公式逐行获取 #

1. 第一个高频产品码（B2单元格）：

=INDEX(A2:A368,MODE(MATCH(A2:A368,A2:A368,0)))

2. 第二个高频产品码（B3单元格），输入后按Ctrl+Shift+Enter作为数组公式，再下拉到B21：

=INDEX(A2:A368,MODE(IF(A2:A368<>B2,MATCH(A2:A368,A2:A368,0),"")))

注：下拉时公式会自动相对引用，后续单元格会自动排除上一个已选出的产品码。若存在频次并列的情况，该方法优先返回列表中先出现的产品码。

方法2：辅助列+数组公式（避免重复） #

若要避免并列频次导致的重复显示，可添加辅助列：

1. 在B2单元格输入=COUNTIF(A$2:A$368,A2)，下拉填充到B368，计算每个产品码的频次
2. 在C2单元格输入以下数组公式（按Ctrl+Shift+Enter），下拉到C21：

=INDEX(A$2:A$368,MATCH(1,(B$2:B$368=LARGE(B$2:B$368,ROW(A1)))*(COUNTIF(C$1:C1,A$2:A$368)=0),0))

公式拆解：

• LARGE(B$2:B$368,ROW(A1))：依次提取第1、2...20高的频次值
• (B$2:B$368=...)：匹配对应频次的产品码
• (COUNTIF(C$1:C1,A$2:A$368)=0)：排除已在C列上方出现过的产品码
• MATCH(1,(...) * (...),0)：找到同时满足两个条件的第一个产品码位置

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