Excel函數公式數據重新排列的14種公式

如下圖所示，左邊成績表需要用函數公式按分數從高到低重新排列。如果分數相同，所在行號較大的排在前面。右表是完成效果，填充顏色區域由一個公式完成。

下面介紹14種公式，以下所有公式如果沒有特殊說明，均需要按【CTRL SHIFT ENTER】三鍵結束。

核心思路分析：

要解決分數從高到低排序問題，首先要想到用LARGE函數。還有一個問題就是當遇到分數相同時，需要把分數和分數所在的行號綁定在一起，公式中$C$2:$C$11*10^3 ROW($C$2:$C$11)的作用就是把分數與分數所在行號綁定在一起。因為優先要按分數高低排序，當分數相同時才按行號大小排序。要達到綁定在一起，又不互相干擾的目的，根據成績表記錄行數，記錄只有10條，分數範圍1-100，分數所在行號2-11，可以把分數放大至少100倍，這裡為了區分明顯，中間隔一個0，放大了1000倍，再加上分數所在的單元格行號，用LARGE函數從大到小計算最值即可。實際應用可以根據數據的行數來確定需要放大多少倍。以下所有公式都是類似的，不過是用不同的查找引用函數來實現的。

公式1

E2=INDEX(A$2:A$11,MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),))

向右填充，再向下填充。

公式2

I2=INDEX($A$2:$C$11,MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),),COLUMN(A1))

向右填充，再向下填充。

公式3

A14=LOOKUP(,0/(($C$2:$C$11*10^3 ROW($C$2:$C$11))=LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1))),A$2:A$11)

這公式不需要三鍵結束。

向右填充，再向下填充。

公式4

E14=LOOKUP(,IF({1,0},0/(($C$2:$C$11*10^3 ROW($C$2:$C$11))=LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1))),A$2:A$11))

向右填充，再向下填充。

公式5

I14=LOOKUP(,CHOOSE({1,2},0/(($C$2:$C$11*10^3 ROW($C$2:$C$11))=LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1))),A$2:A$11))

向右填充，再向下填充。

公式6

M14=VLOOKUP(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),IF({1,0},$C$2:$C$11*10^3 ROW($C$2:$C$11),A$2:A$11),2,)

向右填充，再向下填充。

公式7

Q14=VLOOKUP(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),CHOOSE({1,2},$C$2:$C$11*10^3 ROW($C$2:$C$11),A$2:A$11),2,)

向右填充，再向下填充。

公式8

A26=HLOOKUP(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),TRANSPOSE(IF({1,0},$C$2:$C$11*10^3 ROW($C$2:$C$11),A$2:A$11)),2,)

向右填充，再向下填充。

公式9

E26=HLOOKUP(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),TRANSPOSE(CHOOSE({1,2},$C$2:$C$11*10^3 ROW($C$2:$C$11),A$2:A$11)),2,)

向右填充，再向下填充。

公式10

I26=INDIRECT(CHAR(64 COLUMN(A1))&MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),) 1)

向右填充，再向下填充。

公式11

M26=INDIRECT("r"&MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),) 1&"c"&COLUMN(A1),)

向右填充，再向下填充。

公式12

Q26=INDIRECT(ADDRESS(MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),) 1,COLUMN(A1)))

向右填充，再向下填充。

公式13

A38=OFFSET(A$1,MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),),)

向右填充，再向下填充。

公式14

E38=OFFSET($A$1,MATCH(LARGE($C$2:$C$11*10^3 ROW($C$2:$C$11),ROW(A1)),$C$2:$C$11*10^3 ROW($C$2:$C$11),),COLUMN(A1)-1)

向右填充，再向下填充。