excel匹配数据的函数（教你excel表格匹配数据怎么操作）

2022-04-26 191 zzx

站点名称：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})