Excel应用实践09：特殊的排序——根据人名提取相应的数据

学习Excel技术，关注微信公众号：

excelperfect

这是知乎网友的一个问题：

Excle中A列是乱序，B列是人名排序，什么函数公式可以让A列根据B列排序，且一一对应？也就是说，如下图1所示，A列是一种排序（A列中每个单元格中有个人名，这个人名对应B列中的人名），B列一种排列顺序，C列的排列顺序要对应B列中的人名（使A列和B列形成一一对应关系），除了复制粘贴可以用什么函数公式得到呢？

图1

可以通过数组公式实现：

=INDEX($A$2:$A$5,LARGE(ISNUMBER(FIND(B2,$A$2:$A$5))*(ROW($A$2:$A$5)-1),1),1)

在单元格C2中输入上述公式，按Ctrl+Shift+Enter组合键完成输入。然后，拖动单元格C2右下解至C5即可完成公式复制填充。

如下图2所示：

图2

其中，公式中的

FIND(B2,$A$2:$A$5)

在单元格区域A2:A5的每个单元格中查找单元格B2中的值在该单元格中出现的位置，没有找到返回#VALUE!，找到则返回位置值，本例中的结果为：

{#VALUE!; #VALUE!; #VALUE!;3}

ISNUMBER函数将上面得到的数组转换为数组：

{FALSE; FALSE; FALSE;TRUE}

同样，公式中的

ROW($A$2:$A$5)-1

得到数组

{1;2;3;4}

上面的两个数组相乘：

{FALSE; FALSE; FALSE;TRUE}*{1;2;3;4}

得到数组：

{0;0;0;4}

上述数组传递给函数LARGE，获取第1个最大值为：

4

这样，公式变为：

INDEX($A$2:$A$5,4,1)

即获取单元格区域A2:A5中第4行第1列的值，即为单元格A5中的值。

如果嫌数组公式麻烦，可以使用VBA编写一个简单的用户自定义函数来解决。代码如下：

Function GetDataByName(rng AsRange, Data As Range)
    GetDataByName = Data.Find(rng).Value
End Function

然后，在工作表中像使用Excel内置公式一样，在单元格C2中输入公式：

=GetDataByName(B2,$A$2:$A$5)

向下拖至单元格C5即可。如下图3所示。

图3