如何按匹配的人员列提取对应水果列中频次最高的前两项？

问题背景 #

现有数据源工作表（假设名为Entry）的数据如下：

需要在另一张工作表生成按人员分组，提取对应水果的频次最高项和频次次高项的结果：

现有公式 =INDEX(Entry!D:D,MATCH(MAX(COUNTIF(Entry!D:D,Entry!D:D)),COUNTIF(Entry!D:D,Entry!D:D),0)) 只能提取整列高频值，无法按人员分组处理，以下是可行解决方案：

解决方案 #

方法1：Excel动态数组公式（适用于Excel 365/2021及以上版本）

假设结果表A2单元格为目标人员名称，在B2（频次最高项）输入：

=INDEX(Entry!$B:$B,MATCH(MAX(COUNTIFS(Entry!$A:$A,$A2,Entry!$B:$B,Entry!$B:$B)),COUNTIFS(Entry!$A:$A,$A2,Entry!$B:$B,Entry!$B:$B),0))

在C2（频次次高项）输入：

=INDEX(Entry!$B:$B,MATCH(LARGE(COUNTIFS(Entry!$A:$A,$A2,Entry!$B:$B,Entry!$B:$B),2),COUNTIFS(Entry!$A:$A,$A2,Entry!$B:$B,Entry!$B:$B),0))

说明：

• 用COUNTIFS替代COUNTIF，增加人员列的匹配条件，实现分组统计频次
• LARGE(...,2)提取第二高的频次值，再通过MATCH+INDEX定位对应水果
• 可先用UNIQUE(Entry!$A:$A)自动提取不重复人员列表，再批量套用公式

方法2：Power Query（适用所有带Power Query的Excel版本）

适合批量处理和后续数据刷新，步骤如下：

1. 选中数据源区域，点击数据→从表格/区域，导入Power Query编辑器
2. 编辑器内操作：
   • 点击转换→分组依据，分组列选「人员」，新列名设为「明细行」，操作选「所有行」
   • 添加自定义列，公式：Table.Group([明细行], {"水果"}, {{"频次", each Table.RowCount(_)}})
   • 展开自定义列，按「人员」分组后对「频次」列降序排序
   • 再次分组，分组列「人员」，新增「频次最高项」（取第一行水果）、「频次次高项」（取第二行水果）
3. 点击关闭上载，将结果加载到新工作表，后续数据更新可一键刷新

方法3：传统数组公式（适用于Excel 2019及以下版本，需按Ctrl+Shift+Enter确认）

在B2输入（输入后按组合键确认）：

=INDEX(Entry!$B$2:$B$7,MATCH(MAX(IF(Entry!$A$2:$A$7=$A2,COUNTIFS(Entry!$A$2:$A$7,$A2,Entry!$B$2:$B$7,Entry!$B$2:$B$7))),IF(Entry!$A$2:$A$7=$A2,COUNTIFS(Entry!$A$2:$A$7,$A2,Entry!$B$2:$B$7,Entry!$B$2:$B$7)),0))

C2公式（同样按组合键确认）：

=INDEX(Entry!$B$2:$B$7,MATCH(LARGE(IF(Entry!$A$2:$A$7=$A2,COUNTIFS(Entry!$A$2:$A$7,$A2,Entry!$B$2:$B$7,Entry!$B$2:$B$7)),2),IF(Entry!$A$2:$A$7=$A2,COUNTIFS(Entry!$A$2:$A$7,$A2,Entry!$B$2:$B$7,Entry!$B$2:$B$7)),0))

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