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

2017.05.03

關(guān)注

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

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

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

怎么辦？

那么，如何在區(qū)域范圍內(nèi)達到類似“vlookup”的功能呢？

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

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

1、找到ID的位置

2、作相應偏移

Step 1: 如何根據(jù)內(nèi)容找到單元格地址？

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

關(guān)鍵詞：Address，數(shù)組組合鍵(Ctrl + Shift + Enter)

公式：

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

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

1、現(xiàn)在區(qū)域范圍內(nèi)找到與我們要查找的內(nèi)容相等的單元格，并且取得該單元格的行號和列號

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

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

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

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

3、輸入其它參數(shù)（引用類型，結(jié)果樣式….）

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

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

其它還存在運算結(jié)果為數(shù)組等等不同的情況也需要用到的，請大家關(guān)注后續(xù)內(nèi)容！

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

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

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

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

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

就其本質(zhì)而且ADDRESS函數(shù)就是一個取得地址的函數(shù)，至于到底取到什么值的地址，這個值如何查找，可以結(jié)合其它函數(shù)變化出無盡的組合。

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

關(guān)鍵詞：OFFSET，INDIRECT

公式：

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

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

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

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

rows：上（下）偏移的行數(shù)。

cols：左（右）偏移的列數(shù)。

height：表示所要返回的引用區(qū)域的行數(shù)。（可省略）

width：表示所要返回的引用區(qū)域的列數(shù)。（可省略）

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