Excel如何基于指定列实现横向排序并去除横向重复值

需求梳理 #

现有需求为对键值对数据按关联键横向排列，在此基础上新增两个规则：

• 按数量列降序排序取值
• 去除横向排列的重复取值

现有实现公式 #

当前已实现按匹配键横向排列的公式如下：

=IFERROR(INDEX($B:$B,AGGREGATE(15,7,ROW($A$2:$A$14)/($A$2:$A$14=$E2),COLUMN(A1))),"")

优化后实现方案 #

适用Excel 365/2021及以上版本（动态数组公式，输入一次自动溢出） #

在目标单元格直接输入以下公式即可自动完成去重、排序、横向排列：

=TOROW(SORT(FILTER(UNIQUE($B$2:$C$14),$A$2:$A$14=$E2),2,-1),1,1)

公式逻辑说明：

• UNIQUE($B$2:$C$14)：先对取值+数量的组合去重，从根源避免重复项
• FILTER(..., $A$2:$A$14=$E2)：筛选匹配当前行对应键的所有有效组合
• SORT(...,2,-1)：按第二列（数量列）做降序排序
• TOROW(...,1,1)：将二维结果转为横向行格式，自动忽略空值

适用旧版本Excel的兼容公式 #

在首个目标单元格输入以下公式，向右、向下填充即可生效：

=IFERROR(INDEX($B:$B,AGGREGATE(15,7,ROW($A$2:$A$14)/(($A$2:$A$14=$E2)*ISNA(MATCH($B$2:$B$14,$F2:F2,0))),1)),"")

如果需要同步实现按数量降序排序，使用调整后的公式：

=IFERROR(INDEX($B:$B,AGGREGATE(15,7,ROW($A$2:$A$14)/(($A$2:$A$14=$E2)*ISNA(MATCH($B$2:$B$14,$F2:F2,0)))*($C$2:$C$14=AGGREGATE(14,7,$C$2:$C$14/(($A$2:$A$14=$E2)*ISNA(MATCH($B$2:$B$14,$F2:F2,0))),1))),1)),"")

效果参考 #

• 基础数据集示例
  
• 原有实现效果
  
• 优化后期望效果
  

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