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键完成后向右向下填充就可。如下图所示：

解答：

以下解答皆以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.

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