前幾天小必老師無意中做到了一個題目，是關于一個查詢的問題，其中這個查詢呢，是帶有合并單元格的。下面的問題僅提供于訓練函數的解決思維，實際工作中可提供自身的情況選擇适合自己的一些就方法。

題目：按右邊的要求進行交叉匹配查找。如下圖所示：

要求：

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.

注：以上的所有的過程揭示的是數組内部是如何進行運算的，屬于高階内容。小白朋友可做了解，嘗涳度用戶可以深入地拆分與學習，了解公式與函數的用法。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!