「Excel」大殺器vlookup的再次「進化」

06-18

「 Excel的大殺器vlookup雖然功能強大, 但是只能支持單列的搜索。即使原始數據是由50x50的數據表組成，顯然也是很費時間（廢手）的，比如……

你的老闆給你這樣格式的excel:

然後讓你填充這樣的匯總:

怎麼辦？

那麼，如何在區域範圍內達到類似「vlookup」的功能呢？

我們給出一種思路，因為ID（Apple, Orange…）和其屬性的相對位置都是一樣的，那麼只要找到ID所在單元格，然後作相應的位置偏移，即可得到對應結果的值。

那麼我們只需要做2件事就可以了：

1、找到ID的位置

2、作相應偏移

Step 1: 如何根據內容找到單元格地址？

（這是一句非常標準的，可以在各大搜索引擎得到答案的搜索用問句。下面給出一種但不是唯一的解決方案）

關鍵詞：Address，數組組合鍵(Ctrl + Shift + Enter)

公式：

{=ADDRESS(MIN(IF(查詢範圍=目標單元格,ROW(查詢範圍))),MIN(IF(查詢範圍=目標單元格,COLUMN(查詢範圍))))}

我們先來解讀一下這個公式的邏輯，

1、現在區域範圍內找到與我們要查找的內容相等的單元格，並且取得該單元格的行號和列號

IF(查詢範圍=目標單元格, ROW(查詢範圍))

IF(查詢範圍=目標單元格, COLUMN(查詢範圍))

2、當區域內可能存在多處重複值時，取最先出現的結果（最小目標行號/列號）。這個操作只是為了防止多結果情況下隨機取到了行列不對應的情況，所以選MAX值也可以。

MIN(IF(查詢範圍=目標單元格,ROW(查詢範圍)))

3、輸入其它參數（引用類型，結果樣式….）

不知道大家有沒有注意到上面給出的公式被一個大的花括弧括住了呢？

這個{}其實並不是輸入的，而是在輸入完公式後把常用的回車（Enter鍵）改成了數組組合鍵(Ctrl + Shift + Enter)。那麼到底什麼是數組組合鍵以及它強大的適用範圍我們下次再講！現在大家先留一個印象，涉及到多個數值項的運算（比如此例中涉及到區域內多個單元格的等值運算）大多數情況下需要用到數組組合鍵。

其它還存在運算結果為數組等等不同的情況也需要用到的，請大家關注後續內容！

下面我們來看一下這個公式和對應參數。

ADDRESS(rownum, columnnum, absnum, a1, [sheettext]) rownum：表示要在單元格引用中使用的行號。

columnnum：表示要在單元格引用中使用的列號。

absnum：表示要返回的引用類型。（1或省略 - 絕對引用，2/3 - 混合引用， 4 - 相對引用）

a1：表示返回的單元格地址的引用樣式。（0 - B2, 1 - R[2]C[2]，可省略） sheettext：表示指定要用外部引用的工作表的名稱。（可省略）

就其本質而且ADDRESS函數就是一個取得地址的函數，至於到底取到什麼值的地址，這個值如何查找，可以結合其它函數變化出無盡的組合。

Step 2: 如何進行位置偏移並且得到值？

關鍵詞：OFFSET，INDIRECT

公式：

=OFFSET(INDIRECT(單元格地址), 行偏移值, 列偏移值)

Offset函數以指定單元格為參照系，通過偏移量來得到新的單元格引用。

同樣的我們來看一下這個函數的參數： OFFSET(reference, rows, cols, [height], [width])

reference：表示指定單元格或者單元格區域的引用。

rows：上（下）偏移的行數。

cols：左（右）偏移的列數。

height：表示所要返回的引用區域的行數。（可省略）

width：表示所要返回的引用區域的列數。（可省略）

可見OFFSET函數就能滿足行列偏移的需求。然而我們通過ADDRESS函數取到的實際上是一個文本。而OFFSET函數第一個參數需要的是一個引用，所以此處引出一個新的函數INDIRECT。這個函數的基本功能非常簡潔，Office官方的描述就是根據文本值返回其引用。(Returns the reference specified by a text string.)

就這個案例而言，品名對應ID，在水平方向偏移了1，垂直方向不變，因此offset(address,0,1)即可。

最終，我們可以根據兩步的運算，得到我們想要的結果。

小提示！

當公式複雜或者引用較多的時候，如下圖

在公式編輯時選中對應引用，按下F9可以直觀的看到對應的值或計算結果：

不選中任何引用/參數對於整個公式F9的話，則可以直接看到該公式的結果：