excel如何查找一个数值并返回这个单元格左面单元格的数值

Question

如图 左面是人名对应的订单数，右面我想查找订单量前5名的人，这个如何做到呢？订单量前5可以用large 函数做到，但是左侧对应名字怎么办呢，Vlookup的话 有重复订单销量，只显示第一个名字，用INDEX和INDIRECT函数也会遇到同样的问题

Answer 1

这个问题不错，引来的不少高手。

我也班门弄斧来一道公式：

D2输入

=INDEX(A$2:A$7,MATCH(LARGE(B$2:B$7*100-ROW($1:$6),ROW(1:1)),B$2:B$7*100-ROW($1:$6),0))

或

=IF(ROW(1:1)<6,INDEX(A$2:A$7,MATCH(LARGE(B$2:B$7*100-ROW($1:$6),ROW(1:1)),B$2:B$7*100-ROW($1:$6),0)),"")

数组公式，输入后先不要回车，按Ctrl+Shift+Enter结束计算，再向下填充。

E2输入

=VLOOKUP(D2,A:B,2,)

回车并向下填充。

追答

你也可以先求E列，再求D列，列式就简单些：
E2=IF(ROW(1:1)>5,"",LARGE(B:B,ROW(1:1)))
回车并向下填充。
D2=INDEX(A:A,SMALL(IF(B$2:B$100=E2,ROW($2:$100)),COUNTIF(E$2:E2,E2)))&""
数组公式，操作同上。
其实，第一道公式，加绝对引用，向下填充也可以向右填充，省略E列公式：
=INDEX(A$2:A$7,MATCH(LARGE($B$2:$B$7*100-ROW($1:$6),ROW(1:1)),$B$2:$B$7*100-ROW($1:$6),0))

追问

这个如果是跨sheet 如何实现呢
就是说数据源在sheet1 我要在sheet2 实现前5的排名

追答

也一样的，公式引用Sheet1里面的数据就行：

在Sheet2的A2输入=IF(ROW(1:1)>5,"",INDEX(Sheet1!A$2:A$7,MATCH(LARGE(Sheet1!$B$2:$B$7*100-ROW($1:$6),ROW(1:1)),Sheet1!$B$2:$B$7*100-ROW($1:$6),0)))

也是数组公式，操作同上，再向下和向右填充（可以往下拉）。

公式给你扩大到100行吧：

=IF(ROW(1:1)>5,"",INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$B$2:$B$100*100-ROW($2:$100),ROW(1:1)),Sheet1!$B$2:$B$100*100-ROW($2:$100),0)+1))

Answer 2

将两个或多个位于同一行或者同一列的单元格合并成一个单元格，这种方法就叫做合并单元格。合并单元格的操作多用于Word中的表格或Excel及表格控件中。也许你已了解，但还是要说明一下。工具栏里的合并居中，就可以合并单元格了。同样还可以在对齐选卡中的取消选择合并单元格选项。自定义中，选择命令选卡，在类别中选择格式，然后在命令中找到跨越合并这个选项，然后把它拖拉到工具栏中。下面说几个VBA中与合并单元格相关的内容。

Answer 3

确实会出现你说的问题，需要巧妙设置一下，亲测公式如下：

订单排名  公式=IFERROR(LARGE(B:B,ROW(1:1)),"")

提取姓名  公式=IF($E2="","",INDEX(A:A,SMALL(($B$2:$B$9<>$E2)/1%+ROW($2:$9),COUNTIF($E$2:$E2,$E2))))

提取姓名公式为数组公式，需按CTRL+SHIFT+ENTER结束公式，使公式前后出现{}，同时拖动两个公式下拉即可。

公式中的单元格或数据区域可能与你的表格有差异，你可照猫画虎地自行修改。

如图：

Answer 4

加点左图在AB两列、右图在DE两列，D2写公式：
=INDEX(A:A,MATCH(E2,B:B,0),1)
下拉填充公式即可。追问

这个88的 只会返回王五 不能返回王五和马六

Answer 5

加入行号重建构建订单序列（原理就相当于把王五的88重新构建为88.03，马六的88重新构建为88.04），然后再INDEX，结果就出来了。

C2=INDEX(A:A,MATCH(LARGE(B$2:B$6+(1-ROW(B$2:B$6))%,ROW(A1)),B$2:B$6+(1-ROW(B$2:B$6))%,)+1)