|
查詢引用，不是Lookup或vlookup的專職工作嗎？是的，確實(shí)如此，除了Lookup、Vlookup外，還有Hlookup以及Xlookup等；但除此之外，還有一組黃金搭檔，那就是Index+Match；要掌握這對(duì)黃金搭檔，首先要了解Index函數(shù)和Match函數(shù)本身的作用。 一、Index。 功能：在給定的單元格區(qū)域中，返回特定行列交叉處單元格的值或引用。 從功能中就可以看出（返回特定行列交叉處單元格的值或引用），此函數(shù)具有兩種引用形式： （一）數(shù)組形式：=Index(單元格區(qū)域或數(shù)組常量,行,[列])。 參數(shù)解讀： 1、單元格區(qū)域或數(shù)組常量：必需。 如果數(shù)組僅包含一行或一列，則對(duì)應(yīng)的參數(shù)“行”或“列”是可選的。 2、行：必須。 選擇數(shù)組中的某行，函數(shù)從該行返回值。 3、列：可選。 選擇數(shù)組中的某列，函數(shù)從該列返回值。 備注： 參數(shù)“行”和“列”必須指向數(shù)組中的單元格，否則Index將返回#REF！錯(cuò)誤。 案例： 目的：返回?cái)?shù)據(jù)表中“司馬懿”的“月薪”。
方法： 在目標(biāo)單元格中輸入公式：=INDEX(C3:H12,5,6)或=INDEX(H3:H12,5)。 解讀： 1、公式：=INDEX(C3:H12,5,6)中，數(shù)組不是單行或單列，所以需要同時(shí)指定參數(shù)“行”和參數(shù)“列”。 2、公式：=INDEX(H3:H12,5)中，只有一列，所以只需指定“行”即可。 （二）引用形式：=Index(單元格區(qū)域,行,[列],[區(qū)域順序])。 參數(shù)解讀： 1、單元格區(qū)域：必需。 （1）此處的單元格區(qū)域可以是1個(gè)，也可以是多個(gè)。 （2）如果引用的區(qū)域是非鄰的，必需用括號(hào)()括住。 （3）如果引用的每個(gè)區(qū)域僅包含1行或1列，則對(duì)應(yīng)的參數(shù)“行”、“列”是可選的。 2、行：必需。 引用中某行的行號(hào)，函數(shù)從該行返回引用。 3、列：可選。 引用中某列的列標(biāo)，函數(shù)從該列返回一個(gè)引用。 4、區(qū)域順序：可選。 （1）指定“單元格區(qū)域”中被引用的區(qū)域，從該范圍中按參數(shù)“行”、“列”的值返回指定的引用。 （2）選定或輸入的第一個(gè)區(qū)域編號(hào)為1，第二個(gè)為2，以此類推。 （3）缺省該參數(shù)的情況下，Index默認(rèn)該值為1。 案例： 目的：返回?cái)?shù)據(jù)表中“財(cái)務(wù)部”第2行第6列的值。
方法： 在目標(biāo)單元格中輸入公式：=INDEX((C3:H5,C6:H12),2,6,2)。 解讀： 公式中的最后一個(gè)參數(shù)2指定的是第2個(gè)數(shù)據(jù)區(qū)域，即C6:H12；從C6:H12中提取第2行第6列較差處的值。 二、Match。 功能：返回符合特定值順序的項(xiàng)在數(shù)值中的相對(duì)位置。 語法結(jié)構(gòu)：=Match(定位值,數(shù)據(jù)范圍,[匹配模式])；“匹配模式”分為-1、0、1三種，分別為：“小于”、“精準(zhǔn)匹配”、“大于”。 1或省略：Match查找小于或等于“定位置”的最大值?！皵?shù)據(jù)范圍”中的值必須以升序排序。 0：Match查找完全等于“定位值”的第一個(gè)值。 -1：Match查找大于或等于“定位置”的最小值?！皵?shù)據(jù)范圍”中的值必須以降序排序。 備注： 1、Match函數(shù)返回的是“定位值”在“數(shù)據(jù)范圍”中的相對(duì)位置，而非其值本身。 2、匹配文本時(shí)，Match函數(shù)不區(qū)分大小寫字母。 3、如果查詢不到“定位值”，Match函數(shù)將返回：#N/A!。 4、如果“匹配模式”為0且“定位值”為文本字符串，則可以在“定位值”中使用通配符？（問號(hào)，匹配任意單個(gè)字符）和*（星號(hào)：匹配任意一串字符）；如果要查找實(shí)際的？或*，則在字符前輸入~（波形符）。 案例： 目的：返回最低“月薪”在月薪列的相對(duì)位置。
方法： 在目標(biāo)單元格中輸入公式：=MATCH(SMALL(H3:H12,1),H3:H12,0)。 解讀： 利用Small函數(shù)獲取最低“月薪”，然后利用Match函數(shù)定位其位置。 三、Index+Match應(yīng)用案例。 1、常規(guī)查詢。 目的：根據(jù)“員工姓名”查詢對(duì)應(yīng)的“月薪”。
方法： 在目標(biāo)單元格中輸入公式：=INDEX(H3:H12,MATCH(L3,C3:C12,0))。 解讀： 典型的數(shù)組形式應(yīng)用案例，首先用Match函數(shù)定位出當(dāng)前值（L3）在C3:C12中的相對(duì)位置，然后返回給Index當(dāng)做“行”參數(shù)使用，最后返回H3:H12中指定行的值。 2、逆向查詢。 目的：根據(jù)“員工姓名”查詢對(duì)應(yīng)的“員工編號(hào)”。
方法： 在目標(biāo)單元格中輸入公式：=INDEX(B3:B12,MATCH(L3,C3:C12,0))。 解讀： 用Match函數(shù)定位當(dāng)前值（L3）在相對(duì)范圍（C3:C12）中的相對(duì)位置，并返回給Index函數(shù)的“行”參數(shù)；用Index提取B3:B12范圍中指定行的值。 3、多條件查詢。 目的：查詢符合指定“性別”、“婚姻”、和“部門”的“人員姓名”。
方法： 在目標(biāo)單元格中輸入公式：=INDEX(C3:C12,MATCH(L3&M3&N3,E3:E12&F3:F12&I3:I12,0))。 解讀： 1、“定位值”和“數(shù)據(jù)范圍”之間用連接符“&”對(duì)應(yīng)連接即可；由于有多個(gè)數(shù)據(jù)范圍，所以在填充時(shí)用Ctrl+Shift+Enter填充。 2、如果有多個(gè)符合條件的值，返回第1條記錄。 結(jié)束語： 查詢引用中，無外呼常規(guī)查詢（正向查詢）、逆向查詢以及多條件查詢外；用Index+Match函數(shù)都可以輕松應(yīng)對(duì)，相對(duì)于Lookup和Vlookup來說，更容易理解和上手應(yīng)用。 如果親有更多的關(guān)于Index和Match函數(shù)的應(yīng)用技巧，歡迎在留言區(qū)留言討論哦！ |
|
|
