excel公式技巧之連接數組運算

excel公式技巧之連接數組運算

連接運算符是：&，可以將兩個或多個項目連接成一個項目，這些項目可以是數字、文本（使用引號括起來）、公式結果，等等。

如下圖1所示，在單元格區域A2:C16中是源數據，在單元格區域E2:G10中是想要的交叉表報告，顯示每種產品的L和R的數量。

圖1

可以看出，每個查找的結果都是基于兩個查找值。例如，單元格F4中得到的數量30是在源數據中查找同時滿足單元格E4中的產品代碼2A35-2A36和單元格F3中的L的結果。實現這種雙值查找的一種方法是在公式中連接兩個查找值和源數據表中的被查找的兩個列。在單元格F4中的數組公式為：

=INDEX($C$3:$C$16,MATCH($E4&F$3,$A$3:$A$16&$B$3:$B$16,0))

其中，MATCH函數用來獲得要查找的值在源數據中的相對位置，其第一個參數lookup_value的值是$E4&F$3（使用混合引用使得公式能夠向下向右擴展），將兩個查找值連接為單個值；第二個參數lookup_array的值是$A$3:$A$16&$B$3:$B$16，將源數據中被查找的值所在的列連接起來。

下圖2展示了一種改進方法，即在連接時在要連接的項目之間添加一個分隔符，這使得公式更為健壯。因為如果要查找的值都是數字的話，在連接后可能出現意想不到的結果。

圖2

使用DGET函數進行多條件查找

如果數據集帶有字段名（即每列頂部的名稱），那么DGET函數能夠執行基于多條件的查找，如下圖3所示。注意，條件單元格在相同的行表示AND條件，在不同的行表示OR條件。

圖3

使用DGET函數的缺點是，公式不能向下復制。

使用輔助列進行多條件查找

如下圖4所示，添加了一個輔助列將要查找的值所在的列合并成一列，這樣就可以實現使用VLOOKUP函數進行查找了。在單元格A3中的公式為：=B3&” “&C3，下拉至數據末尾構建輔助列。在單元格G4中的公式為：

=VLOOKUP($F4&” “&G$3,$A$3:$D$16,4,0)

向下向右拖拉即可。

圖4

使用數據透視表查找

對于上述示例，也可以使用數據透視表實現所需報表，如下圖5所示。

圖5

對查找列進行排序并使用近似匹配查找

當進行雙值查找時，如果可以對源數據中的列進行排序，那么查找時使用近似匹配比精確匹配更快。（因為精確匹配從頭到尾遍歷列，而近似匹配進行折半查找）如下圖6所示，先對“L/R?”列進行升序排序，然后對“產品代碼”列進行升序排序，在單元格F4中輸入數組公式：

=INDEX($C$3:$C$16,MATCH($E4&F$3,$A$3:$A$16&$B$3:$B$16))

向下向右拖動至全部數據單元格。

圖6

可以看到，公式中的MATCH函數省略了參數match_type，默認為執行近似匹配。

如果可以對查找列進行排序，那么可以使用LOOKUP函數處理數組操作，而無需按Ctrl+Shift+回車鍵。

使用LOOKUP函數

如果對查找列進行了排序，那么就可以使用LOOKUP函數。LOOKUP函數執行近似匹配查找，且能夠處理數組操作。對于上面的示例，在單元格F4中使用LOOKUP函數的公式為：

=LOOKUP($E4&F$3,$A$3:$A$16&$B$3:$B$16,$C$3:$C$16)

結果如下圖7所示。

圖7

公式改進

INDEX函數能夠獲取整行或整列。決竅是將其row_num參數指定為0或者忽略，這將獲取整列。這樣，上文示例中的公式可以改進，無需按Ctrl+Shift+回車鍵，如下圖8所示。

圖8

在單元格F4中的公式為：

=INDEX($C$3:$C$16,MATCH($E4&F$3,INDEX($A$3:$A$16&$B$3:$B$16,),0))

向下向右拖拉即可。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。