在 Excel 中如何设计公式来执行多字段的排序?
以下图为例,字段:国文、英文、数学、社会、自然在总分相同时的比序顺序为:国文>数学>英文>自然>社会,要设计公式以得到这样的结果。(本例假设有 5 个字段的数据要比序,且每一个数据为 0~100 之间的数值。)
下图是利用 RANK 函数在H栏中产生的排序结果:
下图是以排序字段由小至大的排序结果。(先观察其中排序相同者,目前尚未依指定比序来排序。)
本例需要一个辅助字段:
单元格H3:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
复制单元格H3,贴至单元格H3:H27。
排序结果如下图,排序已依指定的比序:国文>数学>英文>自然>社会,产生不同的排序结果。
公式:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
其中 0.01^$B$1:$F$1 在 SUMPRODUCT会产生:
国文18, 英文19, 数学18, 社会19, 自然19
=93+0.01*18+0.000001*19+0.0001*18+0.0000000001*19+0.00000001*19
=93.1818191919(小)
国文19, 英文18, 数学19, 社会18, 自然19
=93+0.01*19+0.000001*18+0.0001*19+0.0000000001*18+0.00000001*19
=93.1919181918(大)
辅助字段和原始数据的对应关系如下图:
本例每笔数据均为0~100之间,所以设计方式如此。如果你的数据是更大的数值,或是比较字段为更多笔时,该如何处理?先自己想一想。
閱讀更多 老徐漫談 的文章