excel匹配数据的函数（excel表格匹配操作方法）

2024年11月22日 16:16:57 • 投稿 • 阅读 72

前几天小必老师无意中做到了一个题目，是关于一个查询的问题，其中这个查询呢，是带有合并单元格的。下面的问题仅提供于训练函数的解决思维，实际工作中可提供自身的情况选择适合自己的一些就方法。

题目：按右边的要求进行交叉匹配查找。如下图所示：

excel匹配数据的函数（excel表格匹配操作方法）

要求：

1、不能破坏数据源结构，即不能取消合并单元格，不能对B列的单元格进行排序；

2、使用纯公式完成上面的查询匹配，不允许使用辅助列；

分析：

这个问题首先是不能破坏合并单元格与增加辅助列，那么需要使用数组公式如何取消并批量填充单元格就成解决这个问题的一大难点，合并单元格的查询一般情况下可以使用OFFSET或者LOOKUP等函数，但是如何在数组公式中取消合并单元格且填充就成了纯公式解决这个公式的一大难题。

公式：

在G3单元格中输入公式:

=SUMPRODUCT((LOOKUP(ROW($2:$13),ROW($2:$13)/($A$2:$A$13<>””),$A$2:$A$13)=$F3)*($B$2:$B$13=G$2)*$C$2:$C$13)

按Enter键完成后向右向下填充即可。如下图所示：

excel匹配数据的函数（excel表格匹配操作方法）

解释：

以下解释皆以G3单元格中的公式为例：

1、LOOKUP函数使用了其向量形式，即在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。具体语法为：

LOOKUP(lookup_value, lookup_vector, [result_vector])，

如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。

2、ROW($2:$13)是生成一个常量数组即：{2;3;4;5;6;7;8;9;10;11;12;13}；

($A$2:$A$13<>””)则生成一个与常量数组相同的尺寸的由逻辑值组成的数组

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}；

ROW($2:$13)/($A$2:$A$13<>””)是根据逻辑值与数值的转化关系TRUE=1,FALSE=0，两者相除可以得成到一组由数字与逻辑值构成的数组：

{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;8;#DIV/0!;#DIV/0!;11;#DIV/0!;#DIV/0!}；

然后使用LOOKUP查找，即完成的运算为：

=LOOKUP({2;3;4;5;6;7;8;9;10;11;12;13},{2;#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;8;#DIV/0!;#DIV/0!;11;#DIV/0!;#DIV/0!},{“成1″;0;0;0;”成2″;0;”成3″;0;0;”成4”;0;0})

上面这一步骤最终的结果为：

{“成1″;”成1″;”成1″;”成1″;”成2″;”成2″;”成3″;”成3″;”成3″;”成4″;”成4″;”成4”}

即在数组公式里面将合并单元格进行了完成的填充。

3、接上面的步骤，接下来就是返回同时符合两个条件的结果。在公式与函数中，AND函数是用来表示两个条件的同时成立的函数，比如A,B两个条件同时成立可以写成：A And B,而条件是否成立的结果只有两个，成立则返回TRUE,不成立则返回FALSE，再根据逻辑值与数值互换的关系，TRUE=1,FALSE=0，那么根据这个原理，可以将条件同时成立等价于条件相乘不等于0；不成立则为条件相乘等于0，即条件1*条件2*……*条件n，如果成立那么则返回结果为1,即TRUE，否则返回结果为1，即为FALSE。

本题中：

=SUMPRODUCT(({“成1″;”成1″;”成1″;”成1″;”成2″;”成2″;”成3″;”成3″;”成3″;”成4″;”成4″;”成4″}=”成1”)*({“A-1″;”A-3″;”A-2″;”A-4″;”A-1″;”A-2″;”A-2″;”A-1″;”A-3″;”A-3″;”A-1″;”A-2″}=”A-1”)*{336;85;52;203;234;252;224;374;234;72;135;60})

根据条件是否成立可以转化为：

=SUMPRODUCT({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{336;85;52;203;234;252;224;374;234;72;135;60})

对前两个乘数转化可得到：

=SUMPRODUCT({1;1;1;1;0;0;0;0;0;0;0;0}*{1;0;0;0;1;0;0;1;0;0;1;0}*{336;85;52;203;234;252;224;374;234;72;135;60})

然后再次转化：

=SUMPRODUCT({1;0;0;0;0;0;0;0;0;0;0;0}*{336;85;52;203;234;252;224;374;234;72;135;60})

最后使用SUMPRODUCT函数支持数组内部将乘积相加的功能返回相加的结果：

=SUMPRODUCT({336;0;0;0;0;0;0;0;0;0;0;0})

即将0与不为0的结果相加后得到结果为336.

注：以上的所有的过程揭示的是数组内部是如何进行运算的，属于高阶内容。小白朋友可做了解，尝涳度用户可以深入地拆分与学习，了解公式与函数的用法。

原创文章，作者：admin，如若转载，请注明出处：https://www.qq65hfghe5.com/tg/121888.html