比VLOOKUP函數(shù)好用10倍的函數(shù)Index match函數(shù)怎么用？

酒心1000 2018-03-22

展開全文

首先，該問題有哇眾取寵之嫌，在日常工作中，因為VLookup函數(shù)更加簡單、易用、直接，實際上VLookup函數(shù)的應用廣泛程度是Index Match函數(shù)組合的10倍。

說Index Match函數(shù)比VLOOKUP函數(shù)好用，是因為Index Match組合比單純使用VLookup函數(shù)更加靈活，功能更加強大，而且在多列數(shù)據(jù)匹配查找時能通過合適的方法來改善計算效率。

關于這兩個函數(shù)怎么使用，前面的答者給出了非常詳細的描述，在此不再贅述。以下我從兩個方面來進一步說明Index Match函數(shù)組合比VLookup函數(shù)好用的地方。

一、結合“表格”（超級表），跨表引用巨輕松——那些你曾經(jīng)看不懂的公式，原來是這么好用！

大海 Excel到PowerBI
很多時候，我們在寫公式時，都需要進行跨表的數(shù)據(jù)引用，比如要通過VLookup函數(shù)引用其他表的數(shù)據(jù)參與計算，或做匹配等，經(jīng)常要拿著鼠標到處找需要引用的那一列或那個區(qū)域，尤其當數(shù)據(jù)表很大、列數(shù)很多的時候，找起來更是費勁，但自從你將普通的表轉(zhuǎn)成了'表格'（超級表：見文章《用了Excel這么久，還有辣么多人不知道“表格”！》），你就輕松了——因為你可以直接在寫公式的時候得到相應的提示！
以下將通過一個簡單的例子來見證'奇跡的時刻'。
Step01-在成績表里插入新的列'學生姓名'
只要在成績表的右邊，標題行上輸入'學生姓名'，然后回車，Excel將自動生成一個新的列，如下圖所示：
Step02-輸入公式，根據(jù)提示快速選擇表
接下來我們開始輸入公式'=index(stu……'
See？student表隨著公式的輸入出來了！
Excel就是這么牛B，直接給你提示！
此時，如果還有多個表的話，我們可以通過鍵盤的上下箭頭進行表的選擇，當選到我們需要的表時，按Tab鍵即選中該表進入公式。
Step03-在公式中快速選擇要引用的列
引用表后，我們還要指定要引用的列，這時，我們在表名后面輸入'['——真正見證奇跡的時候！student表中所有的列名都出來了！
此時，同樣地，如果列很多的話，我們可以直接輸列名，或者可以通過鍵盤的上下箭頭進行列的選擇，當選到我們需要的列時，按Tab鍵即選中該列進入公式，然后輸入']'完成列的引用。
Step04-在公式中僅引用某列的當前行
為完成'學生姓名'的提取，我們繼續(xù)，到match的時候，我們的lookup_value可是要用當前行的值，怎么辦？——當然沒問題，在'['后再輸入'@'，提示還在！是的，在Excel的'表格'中，對'[列名]'表示對整列的引用，'[@列名]'表示對該列當前行的引用，如下圖所示：
Step05-公式的自動填充
公式全部輸入完畢后，回車，該公式將自動填充到該列的所有單元格中，不需要再動鼠標了——就是這么方便！如下圖所示：
至此，通過在公式輸入時得到的提示，快速地實現(xiàn)了跨表的引用，當你開始習慣了這種輸入的方法后，你將會發(fā)現(xiàn)原來通過鼠標到處找數(shù)據(jù)的過程是多么的痛苦，尤其是表很多、列很多的時候！

二、用Index Match函數(shù)提升多列大量數(shù)據(jù)匹配查詢效率

VLookup是Excel中進行數(shù)據(jù)匹配查詢用得最廣泛的函數(shù)，但是，隨著企業(yè)數(shù)據(jù)量的不斷增加，分析需求越來越復雜，越來越多的朋友明顯感覺到VLookup函數(shù)在進行批量性的數(shù)據(jù)匹配過程中出現(xiàn)的卡頓問題也越來越嚴重。
那么，在數(shù)據(jù)量較大，需要批量進行數(shù)據(jù)匹配查找的情況下，是否有辦法進行適當?shù)母纳?，以提高?shù)據(jù)的匹配查找效率呢？
以下用一個例子，分別對比了四種常用的數(shù)據(jù)匹配查找的方法，并在借鑒PowerQuery的合并查詢思路的基礎上，提出一個簡單的公式改進思路，供大家參考。
一、測試數(shù)據(jù)
本次測試涉及數(shù)據(jù)概況及要求如下：
訂單表21581行（含標題）
訂單明細表17257行（含標題）
要求將訂單表中的“訂單ID”、“客戶”、“雇員”、“訂購日期”、“到貨日期”、“發(fā)貨日期”等6列數(shù)據(jù)匹配到訂單明細表中。
如下圖所示：
二、4種數(shù)據(jù)匹配查找方法
1、VLookup函數(shù)，按常用全列匹配公式寫法如下圖所示：
2、Index Match函數(shù)，按常用全列匹配公式寫法如下圖所示：
3、Lookup函數(shù)，按常用全列匹配公式寫法如下圖所示：
4、Power Query合并查詢，按常規(guī)表間合并操作如下圖所示：
三、4種方法數(shù)據(jù)匹配查找方法用時對比
經(jīng)過分別對以上4中方法單獨執(zhí)行多列同時填充（Power Query數(shù)據(jù)合并法單獨執(zhí)行數(shù)據(jù)刷新）并計算時間，結果如下表所示：
從運行用時來看：
VLookup函數(shù)和Index Match函數(shù)的效率基本一樣；
Lookup函數(shù)在大批量數(shù)據(jù)的查找中效率最低，甚至不能忍受；
Power Query的效率非常高。
四、對公式法的改進
我們在前面用VLookup、Index Match寫公式的思路則是對每一個需要取的值，都是一次單獨的匹配和單獨的取值。也就是說，每次為了查找到一個數(shù)據(jù)，都需要從訂單表的2萬多條數(shù)據(jù)里搜索一遍，這種效率自然會很低。
那么，如果我們在公式中可以做到只匹配一次，后面所需要取的數(shù)據(jù)都跟著這次匹配的結果而直接得到，那么，效率是否會大有改善呢？
再回頭看Index Match結合的公式，其中，Match函數(shù)用于確定所需要查找內(nèi)容的位置，而Index用于提取該位置相應的值！
那么，如果我們只用Match一次把位置先找出來，后面所有的列都直接用這個位置去提取相應的值，會怎樣？
于是，首先用Match函數(shù)構建一個輔助列，用于獲取匹配位置，如下圖所示：
然后，通過Index函數(shù)，直接根據(jù)輔助列的位置從訂單表里讀取相應的數(shù)據(jù)，如下圖所示：
經(jīng)執(zhí)行公式的批量填充，結果：
用時約17秒，約為直接使用VLookup函數(shù)或Index Match函數(shù)組合公式（約85秒）的五分之一！
五、結論
在批量性匹配查找多列數(shù)據(jù)的情況下，通過對Index和Match函數(shù)的分解使用，先單獨獲取所需要匹配數(shù)據(jù)的位置信息，然后再根據(jù)位置信息提取所需多列的數(shù)據(jù)，效率明顯提升，所需匹配提取的列數(shù)越多，效率提升越明顯。
當然，使用公式的方法，即使在一定程度上進行改進，和Power Query相比仍然有很大的差距。因此，在數(shù)據(jù)量較大，數(shù)據(jù)處理較為復雜的情況下，建議使用Power Query來進行。