大家好，我是愛聊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小胖子，廖晨！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!