题目

帮忙解释一下这个强悍的EXCEL函数公式啥意思?
=INDEX(Sheet1!$A:$A,MATCH(LARGE(Sheet1!$B$2:$B$500+1/ROW(Sheet1!$B$2:$B$500)*0.000001,ROW(A1)),Sheet1!$B$2:$B$500+1/ROW(Sheet1!$B$2:$B$500)*0.000001,0)+1)
讲的越细越好,

提问时间：2021-09-10

答案

公式的意思是,按B列数字大小,依次取A列的数据.
类似于large函数或者rank函数,不同的地方是,遇到相同值时,位置在前的排在前面.
这是一个数组公式,同时涉及的函数比较多,所以看着晕.
弄简单一点,取5行数据看看：
姓名x05分数x05名次x05姓名
张三x0520x051x05何七
李四x0530x052x05赵六
王五x0520x053x05李四
赵六x0540x054x05张三
何七x0550x055x05王五
（根据B列的成绩,得到前几名的姓名.张三和王五的分数一样,但是张三顺序靠前,所以名次排在前面.）
关键点在于：
为了取第N大值,同时避免重复值,所以这里要将b2:b6+1/row(2:6)*0.00001,意图是将行号硬拉扯进来参与排序,暗度陈仓.
把公式简化为：
=INDEX(Sheet1!$A:$A,MATCH(LARGE(Sheet1!$B$2:$B$6+1/ROW(Sheet1!$B$2:$B$6)*0.000001,ROW(A1)),Sheet1!$B$2:$B$6+1/ROW(Sheet1!$B$2:$B$6)*0.000001,0)+1)
分解如下：
第一步：
Sheet1!$B$2:$B$6+1/ROW(Sheet1!$B$2:$B$6)*0.000001：用1分别除以数据所在的将行号,再乘以一个微不足道的0.000001,再与分数相加.那么相同的分数,行号小的（在前面的）比行号大的（在后面的）就大那么一丁点.
第二步：
LARGE(...,row(a1))取得经过处理的第n大数据
第三步：
用MATCH函数在同样的数据中匹配他的位置,得到相对行号.
由于MATCH函数取得的是相对行号,比如50.0000001666667在{20.0000005;30.0000003333333;20.00000025;40.0000002;50.0000001666667}的位置是1,而数据是从B2起的,那么行号就要加1.
第四步：
用INDEX函数在A列取得相应行的数据.
……
公式以ctrl+shift+enter结束
补充：
换个思路也可以,道理类似,只是反过来
=INDEX(Sheet1!A:A,LARGE(Sheet1!$B$2:$B$500*100000,ROW(A1))-LARGE(Sheet1!$B$2:$B$500*100000-ROW(Sheet1!$B$2:$B$500),ROW(A1)))