求基于聚类维度的Data Value列最高/次高计数对应值及公式

需求概述 #

• 基于Master Cluster和Mini Cluster双维度，获取Data Value列中计数最高的单元格数据
• 仅基于Master Cluster维度，获取Data Value列中计数最高的单元格数据
• 同时需要获取次高计数对应Data Value的公式

参考数据表 #

公式实现（Excel） #

假设数据范围为 A2:B27（Data Value列）、C2:C27（Mini Cluster）、D2:D27（Master Cluster），指定的Master Cluster值在F2，Mini Cluster值在G2。

1. 双维度（Master + Mini）最高计数Data Value #

兼容所有Excel版本（数组公式，需按Ctrl+Shift+Enter输入）

=INDEX($B$2:$B$27,MODE(IF(($D$2:$D$27=F2)*($C$2:$C$27=G2),MATCH($B$2:$B$27,$B$2:$B$27,0))))

Excel 365/2021版本（无需数组输入）

=XLOOKUP(MAX(COUNTIFS($B$2:$B$27,$B$2:$B$27,$C$2:$C$27,G2,$D$2:$D$27=F2)),COUNTIFS($B$2:$B$27,$B$2:$B$27,$C$2:$C$27,G2,$D$2:$D$27=F2),$B$2:$B$27,,0,1)

示例结果：当F2=10001、G2=TEM-102时，返回101。

2. 仅Master Cluster维度最高计数Data Value #

兼容所有Excel版本（数组公式）

=INDEX($B$2:$B$27,MODE(IF($D$2:$D$27=F2,MATCH($B$2:$B$27,$B$2:$B$27,0))))

Excel 365/2021版本

=XLOOKUP(MAX(COUNTIFS($B$2:$B$27,$B$2:$B$27,$D$2:$D$27=F2)),COUNTIFS($B$2:$B$27,$B$2:$B$27,$D$2:$D$27=F2),$B$2:$B$27,,0,1)

示例结果：当F2=10001时，返回ABCDEF1。

3. 双维度次高计数Data Value #

兼容所有Excel版本（数组公式）

=INDEX($B$2:$B$27,MODE(IF(($D$2:$D$27=F2)*($C$2:$C$27=G2)*($B$2:$B$27<>INDEX($B$2:$B$27,MODE(IF(($D$2:$D$27=F2)*($C$2:$C$27=G2),MATCH($B$2:$B$27,$B$2:$B$27,0))))),MATCH($B$2:$B$27,$B$2:$B$27,0))))

Excel 365/2021版本

=XLOOKUP(LARGE(UNIQUE(COUNTIFS($B$2:$B$27,$B$2:$B$27,$C$2:$C$27,G2,$D$2:$D$27=F2)),2),COUNTIFS($B$2:$B$27,$B$2:$B$27,$C$2:$C$27,G2,$D$2:$D$27=F2),$B$2:$B$27,,0,1)

示例结果：当F2=10001、G2=TEM-102时，返回ABCDEF1。

4. 仅Master Cluster维度次高计数Data Value #

兼容所有Excel版本（数组公式）

=INDEX($B$2:$B$27,MODE(IF(($D$2:$D$27=F2)*($B$2:$B$27<>INDEX($B$2:$B$27,MODE(IF($D$2:$D$27=F2,MATCH($B$2:$B$27,$B$2:$B$27,0))))),MATCH($B$2:$B$27,$B$2:$B$27,0))))

Excel 365/2021版本

=XLOOKUP(LARGE(UNIQUE(COUNTIFS($B$2:$B$27,$B$2:$B$27,$D$2:$D$27=F2)),2),COUNTIFS($B$2:$B$27,$B$2:$B$27,$D$2:$D$27=F2),$B$2:$B$27,,0,1)

示例结果：当F2=10001时，返回101。

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