作者：宋志明

　　【IT168 實(shí)用技巧】在Excel中，我們經(jīng)常會(huì)需要從某些工作表中查詢有關(guān)的數(shù)據(jù)復(fù)制到另一個(gè)工作表中。比如我們需要把學(xué)生幾次考試成績(jī)從不同的工作表中匯總到一個(gè)新的工作表中，而這幾個(gè)工作表中的參考人數(shù)及排列順序是不完全相同的，并不能直接復(fù)制粘貼。此時(shí)，如果使用Excel的VLOOKUP、INDEX或者OFFSET函數(shù)就可以使這個(gè)問(wèn)題變得非常簡(jiǎn)單。我們以Excel 2007為例。

圖1

　　假定各成績(jī)工作表如圖1所示。B列為姓名，需要匯總的項(xiàng)目“總分”及“名次”位于H列和I列（即從B列開(kāi)始的第7列和第8列）。而匯總表則如圖2所示，A列為姓名列，C、D兩列分別為要匯總過(guò)來(lái)的第一次考試成績(jī)的總分和名次。其它各次成績(jī)依次向后排列。

圖2

　　一、VLOOKUP函數(shù)

　　我們可以在“綜合”工作表的C3單元格輸入公式“=VLOOKUP($B3,第1次!$B$1:$I$92,7,FALSE)”，回車后就可以將第一位同學(xué)第一次考試的總分匯總過(guò)來(lái)了。

　　把C3單元格公式復(fù)制到D3單元格，并將公式中第三個(gè)參數(shù)“7”改成“8”，回車后，就可以得到該同學(xué)第一次考試名次。

　　選中C3:D3這兩個(gè)單元格，向下拖動(dòng)填充句柄到最后就可以得到全部同學(xué)的總分及名次了。是不是很簡(jiǎn)單呀？如圖3所示。

VLOOKUP函數(shù)的用法是這樣的：VLOOKUP(參數(shù)1,參數(shù)2,參數(shù)3,參數(shù)4)。“參數(shù)1”是“要查找誰(shuí)？”本例中B3單元格，那就是要查找B3單元格中顯示的人名。“參數(shù)2”是“在哪里查找？”本例中“第1次!$B$1:$I$92”就是告訴Excel在“第1次”工作表的B1:I92單元格區(qū)域進(jìn)行查找。“參數(shù)3”是“找第幾列的數(shù)據(jù)？”本例中的“7”就是指從“第1次”工作表的B列開(kāi)始起，第7列的數(shù)據(jù)，即H列。本例中“參數(shù)4”即“FALSE”是指查詢方式為只查詢精確匹配值。

　　該公式先在“第1次”工作表的B!:I92單元格區(qū)域的第一列（即B1:B92單元格區(qū)域）查找B3單元格數(shù)據(jù)，找到后，返回該數(shù)據(jù)所在行從B列起第7列（H列）的數(shù)據(jù)。所以，將參數(shù)3改成“8”以后，則可以返回I列的數(shù)據(jù)。

　　由此可以看出，使用VLOOKUP函數(shù)時(shí)，參數(shù)1的數(shù)據(jù)必須在參數(shù)2區(qū)域的第一列中。否則是不可以查找的。

　　二、INDEX函數(shù)

　　某些情況下，VLOOKUP函數(shù)可能會(huì)無(wú)用武之地，如圖4所示。“綜合”工作表中，姓名列放到了A列，而B(niǎo)列要求返回該同學(xué)所在的班級(jí)。但我們看前面的工作表就知道了，“班級(jí)”列是位于“姓名”列前面的。所以，此時(shí)我們不可能使用VLOOKUP函數(shù)來(lái)查找該同學(xué)的班級(jí)。而INDEX函數(shù)就正可以一試身手。

圖4

　　在B3單元格輸入公式“=INDEX(第1次!$A$1:$I$92,MATCH(A3,第1次!$B$1:$B$92,0),1)”，回車并向下復(fù)制公式就可以了，如圖5所示。

圖5

　　這里用到了兩個(gè)函數(shù)，INDEX和MATCH。先說(shuō)說(shuō)這個(gè)MATCH(A3,第1次!$B$1:$B$92,0)。它的意思是在“第1次”工作表的B1:B92單元格區(qū)域中查找A3單元格數(shù)據(jù)，然后返回該數(shù)據(jù)在B1:B92單元格區(qū)域中的行數(shù)。以本例而言，該公式返回的結(jié)果是“2”。這一點(diǎn)，只要看一下圖1所示工作表就清楚了。至于該公式中最后一個(gè)參數(shù)“0”是要求精確匹配，而且使用該參數(shù)時(shí)，B1:B92單元格區(qū)域不需要排序。

　　再來(lái)看這個(gè)INDEX函數(shù)。它的用法是INDEX(參數(shù)1,參數(shù)2,參數(shù)3)。參數(shù)1是要查找的區(qū)域。參數(shù)2是行數(shù)，參數(shù)3是列數(shù)。比如公式INDEX(A2:C6,2,3)的意思就是要返回在A2:C6這個(gè)區(qū)域中第二行第三列的數(shù)據(jù)，即C3單元格的數(shù)據(jù)。所以，本例中公式的意思就是返回“第1次”工作表A1:I92單元格區(qū)域中第二行第一列的數(shù)據(jù)，呵呵，那不正是該同學(xué)所在的班級(jí)嘛！

　　與VLOOKUP函數(shù)相比較，INDEX函數(shù)的“限制”要少一些。

三、OFFSET函數(shù)

　　其實(shí)，我們還可以使用OFFSET函數(shù)完成這個(gè)任務(wù)的。比如我們要查A3單元格同學(xué)在第二次考試中的總分。那么只需要點(diǎn)擊E3單元格，并輸入公式“=OFFSET(第2次!$A$1,MATCH($A3,第2次!$B$1:$B$92,0)-1,7,1,1)”，回車后并向下復(fù)制公式，就可以得到所需要的數(shù)據(jù)了。如圖6所示。

圖6

　　OFFSET函數(shù)是以指定的引用為參照系，通過(guò)給定偏移量得到新的引用，比如公式“=OFFSET(C3,2,3,1,1)”，它的意思就是返回以C3單元格為基準(zhǔn)，向下偏移2個(gè)單元格，向右偏移3個(gè)單元格處的單元格數(shù)據(jù)，即F5單元格的數(shù)據(jù)，數(shù)一下就清楚了。

　　所以本例公式中先由“MATCH($A3,第2次!$B$1:$B$92,0)”得到“第2次”工作表B1:B92單元格中與A3單元格相匹配的數(shù)據(jù)所在行數(shù)（本例結(jié)果為2），再減去1正好是它相對(duì)于“第2次”工作表A1單元格的向下偏移的行數(shù)，而后面的參數(shù)“7”，則是該同學(xué)的總分成績(jī)所在單元格相對(duì)于A1單元格的向右偏移量。因此，本例中在E3單元格的公式其實(shí)就是“=OFFSET(第2次!$A$1,1,7,1,1)”，其意思就是返回“第2次”工作表中相對(duì)于A1單元格下移一行右移7行處單元格數(shù)據(jù)。

　　好了，這三個(gè)查找引用函數(shù)的用法應(yīng)該算說(shuō)清楚了吧？只要我們認(rèn)真研究它們的用法，相信會(huì)有很多妙用的。

摘自:http://tech.sina.com.cn/s/2007-08-06/13241658813.shtml

轉(zhuǎn)載于:https://blog.51cto.com/wangheyu1/2410222

總結(jié)

以上是生活随笔為你收集整理的(摘)Excel 2007查询操作中的函数应用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。