如何在Excel中生成各列Top3高频值（兼容旧版本）

假设Sheet1的C、D、E、F列分别存储时间、地点、姓氏、名字数据，以下是在Sheet2生成各列独立Top3高频值的方法，完全兼容不支持动态数组的旧版Excel（如2007及更早版本）：

单列Top3公式实现 #

以Sheet2的A列对应Sheet1的C列（时间）为例，所有公式需按Ctrl+Shift+Enter确认（旧版数组公式的输入要求，公式会自动添加大括号{}，请勿手动输入）：

Top1（最高频值） #

沿用你已验证的公式：

=INDEX(Sheet1!$C$2:$C$999,MATCH(MAX(COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)),COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999),0))

Top2（第二高频值） #

通过过滤已获取的Top1值，统计剩余数据的最高频：

=INDEX(Sheet1!$C$2:$C$999,MATCH(MAX(COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1)),COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1),0))

其中*(Sheet1!$C$2:$C$999<>A1)用于排除Top1的重复统计。

Top3（第三高频值） #

继续过滤Top1和Top2的值：

=INDEX(Sheet1!$C$2:$C$999,MATCH(MAX(COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1)*(Sheet1!$C$2:$C$999<>A2)),COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1)*(Sheet1!$C$2:$C$999<>A2),0))

多列批量复用 #

• 地点列（Sheet1的D列）：在Sheet2的B1-B3单元格，将公式中的Sheet1!$C$2:$C$999替换为Sheet1!$D$2:$D$999，同时将过滤条件中的单元格改为对应列的Top1、Top2（如B2公式用<>B1）。
• 姓氏列（Sheet1的E列）、名字列（Sheet1的F列）以此类推，分别对应Sheet2的C列、D列修改公式范围即可。

优化细节 #

如果某列不同值不足3个，公式会返回#N/A，可使用IFERROR包裹公式优化显示：

=IFERROR(INDEX(Sheet1!$C$2:$C$999,MATCH(MAX(COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1)*(Sheet1!$C$2:$C$999<>A2)),COUNTIF(Sheet1!$C$2:$C$999,Sheet1!$C$2:$C$999)*(Sheet1!$C$2:$C$999<>A1)*(Sheet1!$C$2:$C$999<>A2),0)),"")

确保数据范围Sheet1!$C$2:$C$999覆盖所有有效数据，避免遗漏空行或未包含的记录。

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