Excel的搭檔函數INDEX,MATCH,你用對了嗎?

大家好，我是愛聊Excel的小胖子廖晨，今天要聊的是在一次製作自動增減內容的工資條，你會做嗎？這有實例！一文中使用過的函數，查找定位函數中的最佳搭檔match和index,還有個不成文的說法與這兩個函數有關：查詢用的好，5大函數離不了，分別為index,match,lookup,hlookup,vlookup。今天我們只說index,match函數的用法。

函數之術

術在道德經中的解釋具體的操作方法，為下乘

函數中的「術」其實就是了解函數功能，具體的使用方法？

MATCH功能：查找值在引用區域中的相對引用位置；

語法結構：MTACH(值,引用範圍,[查找模式])

值：查找的值，支持通配符「*?~」（必填）

引用範圍：可謂引用的範圍或數組，引用範圍只能包含行或列，否則返回#N/A;(必填)

查找模式：設定為3個值：1，0，-1；具體信息如下

0.表示精準查找，對應算術中的=，與查找「值」相等就返回當前單元格的相對位置，若沒有找到返回#N/A

1.不填默認值，相當於算數中的<=，返回與值最接近且小於等於「值」的單元格的相對位置；

-1.相當算術中的>=,返回與值接近且大於等於「值」的單元格的相對位置,若沒有找到返回#N/A

提示：當為1時，若數據非升序，結果不可預測（慎用）。

MATCH函數示意圖（圖1）

我最常用的就是精準查找，因為模糊查找有條件約束，所以不太常用，下面我們就詳細了解一下精準查找的用法：

面試題：有一張業績表包含部門，姓名，銷售業績，行數（輔助列）引用範圍為A1:D14,用match函數查詢姓名在B2:B14的位置(是不是簡單令人髮指，不過筆試題越簡單，坑就比較多，考察的內容比你想像的要多的多)。

解：公式比較簡單，若查找「張冶」所在的位置，只需在結果單元格F1錄入=match(「張冶」,B2:B14,0),回車；

如果這是一道面試的題的話，這麼寫答案只怕就是個及格分，因為我們製作某項功能的時候，需要考慮易維護和管理，在這個問題上，其實就是增加一個輔助單元格F2為姓名錄入入口，然後將G2的公式變為=match(E1,B2:B14,0);

如果你做了上面的內容認為就能拿滿分的話，你就太天真了，最多80分，除非你把另外的兩個參數也做成可變的，比如查找模式引用的輔助單元格做成可選列表且加條件約束只能為-1，0，1；難點如何動態生成查找用的引用範圍，有興趣的你可以玩玩！不過在工作中，看留給你的時間是否充裕，沒有時間做最簡就好！

創建查詢模式編碼列表示意圖（圖2）

INDEX功能：通過設定引用範圍的行列號，讀取引用範圍或數組中相應位置的值

語法結構：INDEX(引用範圍,行號,[列號],[區域編號])

引用範圍：即支持單區域引用，也支持多區域引用，當遇到多區域引用的，區域編號則會起到作用

行號：準確的說行號不太準確，因為當引用範圍只在行或列內時，它表示引用範圍的相對位置，自動識別為行號或列號；

列號：當引用範圍同時包含行或列的單元格，才需填寫列號；

區域編號：當引用範圍非連續多個區域時，可以使用區域編號來指定讀取某區域的單元格，從1開始，如果設定小於1的值，則返回#VALUE;

接下來我們將通過例子來熟悉一下INDEX函數的用法：

例：讀取B2:B14引用範圍中的第5行的值是什麼？結果單元格錄入公式=index(b2:b14,5)；

如果將引用範圍換成A1:G1,讀取第5列的內容公式依然=index(A1:G1,5),

面試題：在工作表中B2:G13中，第1行每一個單元格的數字全為1，第2行每個單元格的數字全為2，依次累加，在h5單元格的公式=index(B2:G13,2,0)，則H6的公式為=sum(index(B2:G13,2,0)),兩個最終的分別是什麼？

解：第1個結果為#VALUE,第2個結果為12，因為B到G為6個數，恰巧第2行數字全是2，即6*2=12;

知識點：當引用範圍即包含行和列的單元格時，行號或列號為0時，則表示所在位置的引用範圍行或列的內容，不過在單元格單獨輸入公式則返回#VALUE，而用sum包裹則等效=sum(B3:G3)；

面試題公式示意圖（圖3）

函數之法

法在道德經中解釋為一套體系的原理和規則，中乘

通過上面的介紹，我們不難了解到，MATCH函數可以查詢某些值的在引用範圍中的相對位置,而INDEX函數則可以通過輸入位置編號就能讀取相應位置的值，哪麼問題來了，如何才能查找上面「業績表」中的同一部門所有的員工呢？

思路：其實查詢某個部門所有的銷售人員，只需查出這一部門在引用範圍的所有的相對位置即可，再用INDEX依次讀取銷售人員的姓名就好，哪麼問題來了，該如何讀取一個部門的所有位置信息？而MATCH精準查找只能返回第一次的位置，哪我們依次從第一次出現的位置+1開始查，依次循環是不是就能找到所有的位置了呢？只用前面的兩個函數是無法實現，需藉助INDIRCET函數來拼接生成查找下一個員工姓名的引用範圍，開始位置為上一員工相對位置+1，結束位置不變。

操作:

1.F1:H1分別輸入「查詢部門「、」員工姓名「、」位置「，在F2錄入查詢的部門的名稱」部門1「，在H2輸入=MATCH(F2,A:A,0),回車；

2.第1個元格的相對位置+1即H2+1,所下一個位置的引用範圍字符串=」A」&H2+1&」:A100」,轉化成引用範圍=INDIRECT("A"&H2+1&":A100」),H3的公式=MATCH($F$2,INDIRECT("A"&H2+1&":A100」),0)，再加上第1個員工的相對位置即公式為=MATCH($F$2,INDIRECT("A"&H2+1&":A100"),0)+H2；最後容錯處理，最終為=IFERROR(MATCH($F$2,INDIRECT("A"&H2+1&":A30"),0)+H2,"")

3.快速批量擴填H3的公式方法有拖拽法和快捷鍵法：

拖拽法：將滑鼠移至H3單元格的右下角，變為＋，按下滑鼠左鍵不放，一直拖拽到最終位置（最常用，缺點：填充公式的引用範圍太多時，操作不太順暢）快捷鍵法：先在名稱框輸入公式填充的引用，回車，然後輸入H3的公式，直接ctrl+回車(缺點：步驟稍微多一點，需提前知道填充結束位置；)4.然後在G2輸入=index(B:B,h2),容錯後公式為=IFERROR(INDEX(B:B,H2),"")滑鼠移至G2的右下角變為＋，雙擊滑鼠左鍵，就能完成公式填充，並於H3引用範圍相對齊（有時間會專門寫一篇總結技巧的優缺點和使用場景）最後選中H列，CTRL+0隱藏輔助列

案例示意圖（圖4）

問：處理這類問題，數據大時，查詢匯總填充的公式多了佔用空間，少了會頻繁二次增添公式，填充公式量該怎麼掌握呢？

答：2個類型數據，查詢的填充公式要做到整體數據量的75%，3個類型，查詢公式的量為50%，4個類型，公式的填充量為40%，並非絕對,純屬個人經驗總結。

其實函數「法「的階段就是通過已知的條件中，發現解決問題的規律，然後再去選擇合適的函數或函數組合；選擇合適函數必須先過函數「術」的階段，大概花一個月的時間來熟悉函數的分類，常用的函數60-70個足以和常用的功能技巧，而找到解決問題的規律，就需要長時間的積累，如果開始沒有思路可以找一些做好的項目，開始可以抄，抄的時候，需要注意3點，

1.了解函數的用法和組合的含義，自己不懂，可以搜索相關內容；2.儘量找到所有為什麼這麼用的答案3.最後比較簡單，分別解決了什麼樣的問題，還有其他的什麼情況可以這麼做，雖然簡單卻很關鍵，不可略去；抄過4-5個後，可以嘗試自己做，不要貪快，因為學好的捷徑就是用正確方法上花費時間，至於時間的多少就看你的悟性和學習能力。

聊完函數之「法"後就是函數第三階段「道」，但「道「卻不是1，2個函數就能說明白的，不過可以先解釋一下什麼是「道」？

「道「在道德經中解釋為萬物變遷循環中的亙古規則，上乘；而函數的「道「，就是自己設計，編寫自己需要的功能,所使用的模塊編程的設計思想，要點有12個字：約定優於配置，配置高於邏輯；字不多，學成難，因為需要你大量的知識積累，不光需要你程式語言的知識，也需要算法，設計思維和計算機知識的加持，才能成道。

好了，今天的文章就到這了，文章的最後給大家準備了一個思考題，問：部門員工表中，能用INDEX和MATCH函數實現查詢銷售4的王城銀所在的位置？（註：不用輔助列，不用其他函數，一個公式搞定），欲知公式如何寫，下文分解，最後一句良言：看遍千文，不如實踐一篇，希望你通過實現有所收穫，歡迎你來審查文中紕漏，留言給我，我會立馬改正。不過不要一看有些難度就放棄，你只不過是學前的我，我只是學後你而已！喜歡我就關注吧，我是一個愛聊Excel小胖子，廖晨！