|
哈喽，小伙伴们，你们好呀~~ 在群里看到一个数据匹配的问题觉得挺有趣，如图所示，根据人物名称匹配出对应的书名。
条件值（如人名）的位置不固定在某列，而是可能存在A、B、C等列中的任何一列中，也就是A、B、C列的数据是并列的，都是人名； 那如何在并列的多列数据中查到条件值并返回对应的需要值呢？ 很显然，这个问题，直接用VLOOKUP肯定是不行的——因为它要求条件值必须位于查找区域的首列。 今天就来给大家分享几个函数公式。 1、常规数据匹配思路： 首先要明确的是目标结果（书名）所在位置是A2:A5这个单元格区域，只要能够确定是这个区域的第几行，就能得到所需结果。 而要确定目标在第几行，就需要根据提供的人物与B2:J5这个区域中的单元格去比较，这也是解决问题的最核心所在。 这一步有两个公式可以实现： 公式1 =SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4))
公式2 =MAX(IF($B$2:$J$5=A8,ROW($A$1:$A$4))) 数组公式，需要三键（Ctrl、shift、enter）输入。
两个公式中都用到了$B$2:$J$5=A8，这个等式会得到一组逻辑值，人物所对应的具体单元格会返回TRUE。
两个公式的另外一个共同点就是ROW($A$1:$A$4)，这部分相当于常量数组{1;2;3;4}。 公式1是利用SUMPRODUCT计算出一组逻辑值和数组的乘积之和，最终得到符合条件的姓名所在的行号。 关于SUMPRODUCT函数的详细示例参考教程：Excel的求和函数之王！ 公式2则是利用IF函数返回数据，其中TRUE所对应的是一个数字。
再用MAX得到这一组数据中的最大值，从而得到姓名所在的行号。 要理解这个思路需要对数组有一定的认识才行，关键在于公式中IF函数的第一参数是一组逻辑值而不是一个逻辑值，所以IF函数返回的结果也是一组数据，必须借助MAX得到需要的结果。 理解了以上两个公式，其实要解决问题就非常容易了，使用INDEX、OFFSET和INDIRECT函数都可以得到正确结果。 以下采用公式1的进行介绍，有兴趣的同学可以自己替换公式2去研究一下。 INDEX函数解法： 公式为： =INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)))
INDEX根据SUMPRODUCT得到的行号，在目标区域中得到对应的书名。 OFFSET函数解法： 公式为： =OFFSET($A$1,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)),)
OFFSET以A1单元格为基点，按照SUMPRODUCT得到的行数向下偏移，就得到了对应的书名。 INDIRECT函数解法： 公式为： =INDIRECT("A"&SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$2:$A$5)))
注意这个公式中的ROW($A$2:$A$5)和前几个公式稍有区别，因为INDIRECT需要的是表格中的行号，而不是目标区域中的行号，所以这里用的$A$2:$A$5而不是$A$1:$A$4。 以上分享的这些公式都是常规数据匹配思路。 2、两个非常规的思路 分别用到了合并函数CONCAT函数和TEXTJOIN函数，并且都是数组公式，需要三键（Ctrl、shift、enter）输入。 如果你的Excel或WPS也有这两个函数的话，可以研究一下了。 CONCAT函数解法： 公式为：=CONCAT(IF($B$2:$J$5=A8,$A$2:$A$5,""))
TEXTJOIN函数解法： 公式为：=TEXTJOIN("",,IF($B$2:$J$5=A8,$A$2:$A$5,""))
这两个公式的本质都是一样的，利用IF函数返回一组文本，只有一个是书名，其他都是空值。
将这一组文本合并后就得到所需要的结果。 以上就是今天要分享的全部内容，不知道你学会了多少，下面再来给大家留一个自测题吧。
如果是这个样式的查找，又应该如何来写公式呢？知道答案的同学，可以在评论区留言告诉我们哟~~ |
|
|
