INDEX和MACTH函數(shù)

INDEX和MATCH函數(shù)嵌套應(yīng)用第一，MATCH函數(shù)用法介紹MATCH函數(shù)也是一個(gè)查找函數(shù)。MATCH 函數(shù)會(huì)返回匹配值的位置而不是匹配值本身。在使用時(shí)，MATCH函數(shù)在眾多的數(shù)字中只查找第一次出現(xiàn)的，后來出現(xiàn)的它返回的也是第一次出現(xiàn)的位置。MATCH函數(shù)語法：MATCH(查找值，查找區(qū)域，查找模式)MATCH(lookup_value,lookup_array,match_type)可以通過下圖來認(rèn)識(shí)MATCH函數(shù)的用法：=MATCH(41,B2:B5,0)，得到結(jié)果為4，返回?cái)?shù)據(jù)區(qū)域 B2:B5 中 41 的位置。=MATCH(39,B2:B5,1)，得到結(jié)果為2，由于此處無正確匹配，所以返回?cái)?shù)據(jù)區(qū)域 B2:B5 中(38) 的位置。注：匹配的查找值，MATCH 函數(shù)會(huì)查找小于或等于(39)的最大值。=MATCH(40,B2:B5,-1)，得到結(jié)果為#N/A，由于數(shù)據(jù)區(qū)域 B2:B5 不是按降序排列，所以返回錯(cuò)誤值。如果 match_type 為 1，函數(shù) MATCH 查找小于或等于 lookup_value 的最大數(shù)值。Lookup_array 必須按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。 如果 match_type 為 0，函數(shù) MATCH 查找等于 lookup_value 的第一個(gè)數(shù)值。Lookup_array 可以按任何順序排列。 如果 match_type 為 -1，函數(shù) MATCH 查找大于或等于 lookup_value 的最小數(shù)值。Lookup_array 必須按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。 第二，INDEX函數(shù)用法介紹INDEX函數(shù)的功能就是返回指定單元格區(qū)域或數(shù)組常量。如果同時(shí)使用參數(shù)行號(hào)和列號(hào)，函數(shù)INDEX返回 行號(hào)和列號(hào)交叉處的單元格中的值。INDEX函數(shù)語法：INDEX(單元格區(qū)域，行號(hào)，列號(hào))可以通過下圖來認(rèn)識(shí)INDEX函數(shù)的用法：=INDEX(A1:C6,2,3)，意思就是返回A1:C6中行號(hào)是2 列號(hào)是3 ,即第二行與第三列的交叉處，也就是C2單元格的值，為84。第二部分：INDEX和MATCH函數(shù)應(yīng)用案例介紹下圖工作表所示的是一個(gè)產(chǎn)品的型號(hào)和規(guī)格的價(jià)格明細(xì)表。通過這個(gè)表的數(shù)據(jù)，進(jìn)行一些對(duì)應(yīng)的查詢操作。第一，單擊B5單元格下拉按鈕，選擇型號(hào)，然后在B6單元格完成型號(hào)所在行號(hào)的查詢。如下圖所示：隨意選擇一個(gè)型號(hào)，比如A0110，然后在B6單元格輸入公式：=MATCH($B$5,$D$4:$D$12,0)，得到結(jié)果1。公式解釋：用MATCH函數(shù)查找B5單元格這個(gè)型號(hào)在D4:D12區(qū)域中對(duì)應(yīng)的位置。其中的0參數(shù)可以省略不寫。MATCH函數(shù)中0代表精確查找，1是模糊查找。第二，單擊B9單元格下拉按鈕，選擇規(guī)格，然后在B10單元格完成規(guī)格所在列號(hào)的查詢。如下圖所示：隨意選擇一個(gè)規(guī)格，比如101，然后在B10單元格輸入公式：=MATCH(B9,E3:G3,0)，得到結(jié)果1。第三，查詢B6和B10單元格所對(duì)應(yīng)的價(jià)格。價(jià)格的查詢，可以使用index函數(shù)完成，輸入公式：=INDEX(E4:G12,B6,B10)可以得到結(jié)果為78。嵌套上面的match函數(shù)，可以將公式改為：=INDEX(E4:G12,MATCH(B5,D4:D12,0),MATCH(B9,E3:G3,0)。大家可以變化C3中的型號(hào)來看看結(jié)果是否正確。通過下面工作表的源數(shù)據(jù)，利用index函數(shù)實(shí)現(xiàn)行列匯總查詢。INDEX函數(shù)的幫助文件知道，如果將 row_num 或 column_num 設(shè)置為 0，函數(shù) INDEX 則分別返回整個(gè)列或行的數(shù)組數(shù)值。通過用法說明，來實(shí)現(xiàn)上圖的行列匯總。首先，單擊C3單元格下拉按鈕，選擇數(shù)據(jù)，比如選擇A0111，然后在C4單元格進(jìn)行C3單元格對(duì)應(yīng)的行號(hào)查找，公式為：=MATCH(C3,E2:E10,)，得到結(jié)果為2，說明A0111在E2:E10區(qū)域的第二行。然后對(duì)行號(hào)所對(duì)應(yīng)的價(jià)格進(jìn)行匯總求和。在C5單元格輸入公式：=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,),)如果選中公式中的INDEX(F2:H10,MATCH(C3,E2:E10,),)部分，按下F9鍵，執(zhí)行結(jié)果是80,97,84，可以看到就是對(duì)第二行的數(shù)據(jù)進(jìn)行求和。同樣的方法，可以進(jìn)行列匯總，在C9單元格輸入公式：=SUM(INDEX(F2:H10,MATCH(C7,F1:H1,)。說明：查找行號(hào)和查找列號(hào)，只是過渡一下，幫助新手朋友加深對(duì)match函數(shù)的理解和使用，對(duì)熟悉的朋友，可以直接在行匯總一步完成公式的輸入。根據(jù)下面的工作表，進(jìn)行區(qū)域匯總求和。首先，分別在開始行號(hào)、結(jié)束行號(hào)、結(jié)束列號(hào)、結(jié)束列號(hào)選定需要求和的區(qū)域，比如A0110、A0111、201、301。此題可以套用下圖的格式進(jìn)行求和。因此，在C20單元格輸入求和公式：=SUM(