|
如下图所示，左边成绩表需要用函数公式按分数从高到低重新排列。如果分数相同，所在行号较大的排在前面。右表是完成效果，填充颜色区域由一个公式完成。 下面介绍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) 向右填充，再向下填充。
|
|
|
