VLOOKUP函數配合數組公式進階應用

05-28

本帖最後由天遠於 2013-4-28 19:32 編輯

首先來認識一下數組公式：

數組公式可以對一組或多組值執行多重計算，並返回一個或多個結果。按 Ctrl+Shift+Enter 可以鍵入數組公式，此時通過編輯框可以看到數組公式的兩端被自動加上了大括弧{}。

(以上引用自WPS的幫助文檔)

示例1：

如上表，可以利用vlookup函數方便地查詢某人的年收入。

對B14單元格，在C14單元格中輸入:

=VLOOKUP(B14,B3:O11,14,FALSE)

複製代碼下面，再提供兩種方法：

1.本例中我們只用到了姓名列和年收入列，那麼，查找區域是不是可以縮小一下，只要B列和O列呢，當然是可以的。

我們知道，","(逗號是合併單元格區域的運算符)，於是我們就想,對B15單元格，在C15單元格中輸入:

=VLOOKUP(B15,(B3:B11,O3:O11),2,FALSE)

複製代碼一試，發現出錯了，顯然，這樣直接合併的方法不對，這樣，就想到了用數組公式，我們想讓其將兩個區域都返回一次，可以用IF來完成：

=IF({TRUE,FALSE},B3:B11,O3:O11)

複製代碼這樣，對B15單元格，在C15單元格中輸入完整公式 : (注意這是數組公式，輸入後按Ctrl+Shift+Enter )

=VLOOKUP(B15,IF({TRUE,FALSE},B3:B11,O3:O11),2,FALSE)

複製代碼2.要是原表中沒有年收入怎麼辦呢，沒關係，不是有每個月的數據嗎，相加就好了，那麼，怎麼一步完成：

對B16單元格，在C16單元格中輸入公式 : (注意這是數組公式，輸入後按 Ctrl+Shift+Enter )

=SUM(VLOOKUP(B16,B3:N11,ROW(2:13),FALSE))

複製代碼ROW(2:13)返回一個垂直數組（注意這裡不能用COLUMN(C:N)，因為它返回的是水平數組，這裡我們想要的是垂直數組）

由此，我們可以利用VLOOKUP函數分別查找出某人每個月的收入，最後再這些數據用SUM求和，就是年收入。

示例2：

根據姓名和地址查找業績，我們首先想到的是構造一個輔助列:

在C和D列中插入一列，再用 &再B、C兩列數據連接，再用VLOOKUP查找。

那麼，有沒有更直接的辦法呢，有：

我們可以把查找值看成兩列連接值，把查找區域看成B、C列的連接值的整體與D列合併。因示例1,我們借用IF數組公式可以合併得到查找區域:

=IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11))

複製代碼這樣我們再用VLOOKUP查詢，在D15中輸入公式: (注意這是數組公式，輸入後按 Ctrl+Shift+Enter )

=VLOOKUP(B15&C15,IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11)),2,FALSE)

複製代碼這裡,B15&C15是查找值,IF({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11)) 是查找區域，列序數是2,匹配模式是FASLE(精確匹配)

對於列序數是2 而不是3，解釋:

B3:B11&C3:C11 整體是第一列,D3:D11 是第二列

總結，VLOOKUP函數是一個非常強大的查找函數，但它也有一些不足，比如只能返回一個查找結果，查找值也只能指定一個，但是，我們可以利用數組公式將這"一個"擴展成"一個數組"，那就相當於是多個了，這樣，VLOOKUP函數的應用範圍將大大擴展!

測試文檔:

VLOOKUP函數配合數組公式進階應用.et(12.5 KB, 下載次數: 40)

補充1：

=I F ({TRUE,FALSE},(B3:B11&C3:C11),(D3:D11))中，第一參數{TRUE,FALSE}用大括弧，表示是一個數組，在內容，相當於各執行一次

=I F (TRUE,(B3:B11&C3:C11),(D3:D11))

=I F (FALSE,(B3:B11&C3:C11),(D3:D11))

分別返回一個區域，兩個區域就組成了一個區域數組

補充2:

請看5樓,水月團長給出的示例3

——————修訂記錄——————

2013年04月28日上傳附件

VLOOKUP函數綜合應用.et(21 KB, 下載次數: 14)

1.示例1增加INDEX+MATCH函數使用示例

2.收錄@松風水月增加的示例3

3.根據@無魂的帖子增加示例4

4.http://bbs.wps.cn/thread-22368733-1-1.html 這個帖子的內容和示例2,3,4相同