16.9 数组 - 数据排序技术

一、根据产品产量进行排序

例 按产品产量降序排列

如下图所示的是某企业各生产车间钢铁生产的产量明细表，需要按产量降序排列产量明细表。

方法1 产量附加行号排序法。选中G2:G8单元格区域，在编辑栏中输入以下数组公式，按<Ctrl+Shift+Enter>组合键。

{=INDEX(C:C，MOD(SMALL(ROW(2:8)-D2:D8/1%%%，ROW(1:7)),100))}

该公式使用ROW函数产生的行号序列与产量的1 000 000倍组合生成新的内存数组，再使用SMALL函数从小到大逐个提取，使用MOD函数返回排序后的行号，最后使用INDEX函数返回产品类别。

在H2单元格中输入以下公式计算产量，并向下复制到H8单元格。

=VLOOKUP(G2,C:D,2，)

方法2 RANK函数化零为整排序法。选中K2:K8单元格区域，在编辑栏中输入以下数组公式，按< Ctrl+Shift+Enter>组合键。

{=INDEX(C:C,RIGHT(SMALL(RANK(D2:D8,D2:D8)/1%+ROW(2:8),ROW()-1),2))}

使用RANK函数将产量按降序排名，与ROW函数产生的行号数组组合生成新的数组，再使用SMALL函数从小到大逐个提取，RIGHT函数返回排序后的行号，最后使用INDEX函数返回产品类别。

在L2单元格中输入以下公式计算产量，并向下复制到L8单元格。

=SUMIF(C:C,K2,D:D)

方法3 SMALL函数结合COUNTIF函数排名法。在P2单元格中使用以下公式先将产量按降序排列。

=LARGE(D$2:D$8，ROW(A1))

在O2单元格中输入以下数组公式,按<Ctrl+Shift+Enter>组合键,并向下复制到O8单元格。

{=INDEX(C:C,SMALL(IF(P2=D$2:D$8,ROW($2:$8)),COUNTIF(P$1:P2,P2)))}

根据产量返回对应的产品类别。当存在相同产量时，使用COUNTF函数统计当前产量出现的次数，分别返回不同的产品类别。

提示：当产量数值较大或小数位数较多时，方法1受到Excel的15位有效数字的限制，而不能返回正确排序结果。方法2使用RANK函数将数值化零为整，转换为数值排名，可有效应对大数值和小数位数多的数值，避免15位有效数字的限制，返回正确的排序结果。

二、多关键字排序技巧

例 按各奖牌数量降序排列奖牌榜

如下图所示的是2014年仁川亚运会奖牌榜的部分内容，需要依次按金、银、铜牌数量对各个国家或地区进行降序排列。

由于各个奖牌数量都为数值，且都不超过3位数，因此可以通过“*10^N”的方式将金、银、铜牌3个排序条件整合在一起。

选中G2:G10单元格区域，在编辑栏中输入以下数组公式，按<Ctrl+Shift+Enter>组合键。

{=INDEX(A:A,RIGHT(LARGE(MMULT(B2:D10,10^{8;5;2})+ROW(2:10),ROW()-1),2))}

公式使用MMULT函数将金、银、铜牌数量分别乘以10^8、10^5、10^2后求和，把3个排序条件整合在一起形成一个新数组。该数组再与行号构成的序数数组组合，确保数组元素大小按奖牌数量排序的前提下最后两位数为对应的行号。

然后使用LARGE函数从大到小逐个提取，完成降序排列。再使用RIGHT函数返回对应的行号，最后使用INDEX函数返回对应的国家或地区。

在H2单元格中输入以下公式，查询各国家或地区的奖牌总数。

=VLOOKUP(G2,A:E,5,)