顯示滿足條件的所有數(shù)據(jù)--vlookup,match

1、顯示滿足條件的所有數(shù)據(jù)VLookup函數(shù)、IF函數(shù)、Row函數(shù)、Small函數(shù)、Index函數(shù)、Match函數(shù)、IFERROFB數(shù)、表結(jié)構(gòu)的組合使用2009年03月20日，1:26下午令介1口翁口,（4人投票，平均：5.00outof5）一個(gè)簡單的示例：查找Excel工作表中的重復(fù)數(shù)據(jù)記得一位網(wǎng)友曾問：要求找出Excel工作表中的重復(fù)數(shù)據(jù)并顯示在工作表相應(yīng)的單元格中。我給出了一個(gè)數(shù)組公式供參考，但不是太符合要求，因?yàn)檫@個(gè)數(shù)組公式雖然找出了重復(fù)數(shù)據(jù)，但是如果將數(shù)組公式向下復(fù)制時(shí)超出了出現(xiàn)重復(fù)數(shù)據(jù)的數(shù)量，會(huì)在相應(yīng)單元格中顯示錯(cuò)誤。不久，這位朋友獲得了更好的一個(gè)公式。這個(gè)公式非常好，完美地解決了這類

2、問題，因此，我將其轉(zhuǎn)貼于此，供有興趣的朋友參考。先看看下圖：kBcDEFr1姓名培訓(xùn)記聿姓名張三2張三ISO培訓(xùn)記錄方法一方法二p李四IS011ISOISO4張三IS022IS02IS025李四ISOS3IS04ISOI6張三IS044IS061E0&7李四ISOS58張三IS0669李四ISO77在列A和列B中存在一系列數(shù)據(jù)（表中只是示例，可能還有更多的數(shù)據(jù)），要求找出某人（即列A中的姓名）所對應(yīng)的所有培訓(xùn)記錄（即列B中的數(shù)據(jù)）。也就是說，在單元格E1中輸入某人的姓名后，下面會(huì)自動(dòng)顯示這個(gè)人所有的培訓(xùn)記錄。我們知道，Excel的LOOKUP列函數(shù)能夠很方便地實(shí)現(xiàn)查找，但是對于查找后返

3、回一系列的結(jié)果，這類函數(shù)無能為力，因此只能聯(lián)合其它函數(shù)來實(shí)現(xiàn)。這里，在方法一中使用了INDEX函數(shù)、SMAL喃數(shù)、IF函數(shù)和RO®數(shù)，在方法二中還使用了Excel2007中新增的IFERROFE數(shù)。方法一：選擇單元格E3;輸入公式：=INDEX(B:B,SMALL(IF($A$2:$A$25=$E$1,ROW($A$2:$A$25),65536),ROW(1:1)&然后同時(shí)按下Ctrl+Shift+Enter鍵，即輸入數(shù)組公式。 選擇單元格E3后下拉至所有單元格方法 選擇單元格F3; 輸入公式：=IFERROR(INDEX($A$2:$B$9,SMALL(IF($A$2:$A

4、$9=$E$1,ROW($A$2:$A$9)-ROW($A$2)+1,ROW($A$9)+1),ROW(1:1),2),"")然后同時(shí)按下Ctrl+Shift+Enter鍵，即輸入數(shù)組公式。 選擇單元格F3后下拉至所有單元格。示例文檔下載:一個(gè)復(fù)雜的示例：查找不同工作表中的數(shù)據(jù)并顯示滿足條件的所有數(shù)據(jù)對于VLOOKUP數(shù)來說，其主要缺點(diǎn)是僅能返回與查找條件相匹配的單條數(shù)據(jù)，不能夠返回與某條件相匹配的所有數(shù)據(jù)，但其優(yōu)勢是能夠相當(dāng)容易地獲取與所給條件相匹配的第一條數(shù)據(jù)，如下圖1所示。圖1:使用VLOOKUP數(shù)返回包含某顧客姓名相應(yīng)的行中的數(shù)據(jù)是很容易的方式在Excel2007中

5、使用結(jié)構(gòu)化引用，VLOOKUP數(shù)不僅容易使用，而且可讀性也很強(qiáng)。在示例中，將表Table1中第一列的值與單元格A9中的值相匹配，并從表中第3列返回值。但是，不能返回表中第二個(gè)與“Dan”相匹配的值。事實(shí)上，很多時(shí)候我們都會(huì)碰到這樣的情況，我們想要返回與條件相匹配的所有值，但是VLOOKUP數(shù)只能返回滿足條件的第一個(gè)值。下面，讓我們看看如何從表中獲取滿足條件的所有數(shù)據(jù)。首先，準(zhǔn)備一些要操作的數(shù)據(jù)。我們以O(shè)fice自帶的Northwind示例數(shù)據(jù)庫的一部分?jǐn)?shù)據(jù)來演示，將其中的部分?jǐn)?shù)據(jù)導(dǎo)入Excel表中，如下圖2、圖3、圖4所示。r-MicrosoftE，；cHS-nxJ開始撞入頁腳公5寸數(shù)翅/國唄

6、引加案項(xiàng)Si+潁-EX_tb】Cust8ET3£CompanyA三B11A._c_DE1T1±,2CoapanyABedelsAimsOwner3CompanyBGratac<*sSolAntonio*ner4CompanyCAxenThomasPure*_5_CompanyDLeeChristinaPurchi：6C<?tpany£0"Dom(!11MartinOwner?jCoapanyFF&rez-OlastaFransis-c«Pixcha；6CoapanyGXiehling-?ansOwnergCoopanyHAn

7、dersenElinbEhPurcha：10ConpanyIMortens&nSvenPurcha:11CoftpanyJWackerRolandPurcha：12)CoopanyKKx,schnePeterPLLrcha：-1G00'_:電®_IK1M4，hCustojnezfOrder?平順二499gg.5544WO:2899977，?；鼐蕓""j圖2：導(dǎo)入到工作表Customers中的“Customer”表并命名為“tblCustomers”嘮，：Msster-Cail-Miao-softExcel恚_rixJ/w?«A兵面粉為公式

8、數(shù)據(jù)*嵬覬/比則設(shè)iT-內(nèi)tblOrders。叁I30*kECDE-i，：-!2302006-1-15cmAJiritHeilung-LarsenC<&npany/3_312006-1-20ClosedJanKotasCcrapany1>4322006-1-22ClosedMariyaSergienkoCompany】國332006-1-"30ClosedMieHaelNftLpperCcnpany6S42Q06-2-6C16£*dAhftt1?3520P6-2-10ClosedJanK«lasCompanyt_a，36372C06r*2-28

9、Closed200&-3-6ClosedMariyaSergienkoLauraGiussanlCcnpany(Conpany10_382006-3-10ClosedAnneHellung-LrsenCompany11392006-3-22ClosedJanKotasCompany1,124。20Dt-3-24ClosedMariyaSergienkoCcnpany,.一q-M4rumznmjishCustomersCh加工與Details二高拜"皿|fiaaartt1半嗨工274822骷415纏二3452321吼力tblOrders圖3:導(dǎo)入到工作表Orders中的“Ord

10、ers”表并命名為MA$t?r-Oetaj|-MicrosoftExc包毒開始侵人頁面制V公式2mM聞視IB就期於27役計(jì)業(yè)_f/A：Bc>3TDQOrdt江lOProduct22730NorthwindTradersBeer32830UorthwindTradersDriedPlmns42931KonhindTradersDriedFtars53031MorthvindIradtrsDriedApples&"3131TriritrsDri.tdPIujs73232MrthvindIxad&rsChai83333HorthwlndTradersCoffet534

11、35KorthifindTradersChocolateBiscuits肥103534HorthwindTradersChocolateBiscuits雁113635NorthwindTradersChoeolate；2736HorthwindiTadersClatChowder一ja.JMSq«r叫i*t«K(5向$，”<UitimersOrdersDetails平母直：204575W35計(jì)數(shù);535求舐857g96后回叫10（微二;圖4：導(dǎo)入到工作表Details中的“OrderDetails”表并命名為“tblDetails當(dāng)然,上述數(shù)據(jù)都不需要自已手工輸入，只

12、需從Northwind數(shù)據(jù)庫中導(dǎo)入即可現(xiàn)在，希望選擇訂單號（OrderNumber后，能夠顯示該訂單的詳細(xì)信息，如下圖5所示。rn.cDrdcrlsua圖5:當(dāng)選擇某訂單號后，會(huì)顯示該訂單的詳細(xì)信息下面是圖5顯示的界面的主要設(shè)計(jì)過程。步驟1在工作表相應(yīng)的單元格中輸入下列字符：單元格B1:OrderNumber單元格C3:OrderInformation單元格C4：Customer單元格F4：OrderDate單元格F5:Status單元格F6:Salesperson單元格F7：ShipDate單元格C10OrderDetails單元格C11:Product單元格D11：Quantity單元格E

13、11:UnitPrice單元格F11：Discount單元格G11:TotalPrice單元格H11：StatusID步驟2創(chuàng)建包含所有訂單IDs的一個(gè)命名區(qū)域，然后使用該名稱在數(shù)據(jù)有效性中創(chuàng)建訂單號的下拉列表。為此，單擊“公式一定義名稱”，在“新建名稱”對話框中輸入：名稱：OrderIds引用位置：=tblOrdersID注:數(shù)據(jù)有效性不能夠引用不同工作表中的單元格區(qū)域，除非為該區(qū)域定義名稱。步驟3選擇單元格D1,將其命名為rngOrderId。步驟4選擇單元格D1,單擊“數(shù)據(jù)一數(shù)據(jù)有效性”，在“允許”中選擇“序列”，在“來源”框中輸入“=OrderIds”。步驟5選擇單元格C5,輸入下列函

14、數(shù)：=VLOOKUP(rngOrderId,tblOrders,MATCH(C4,tblOrders#標(biāo)題,0),FALSE)步驟6與單元格C5中的函數(shù)相似，設(shè)置剩余單元格的查找函數(shù)。C6:=VLOOKUP($C$5,tblCustomers,MATCH(Address",tblCustomers#標(biāo)題,0),FALSE)C7：=VLOOKUP($C$5blCustomers,MATCH(City”，tblCustomers#標(biāo)題,0),FALSE)&"，”&VLOOKUP($C$5,tblCustomers,MATCH("State"，

15、tblCustomers#標(biāo)題,0),FALSE)&”"&VLOOKUP($C$5,tblCustomers,MATCH(“Zip"，tblCustomers#標(biāo)題,0),FALSE)H4：=VLOOKUP(rngOrderId,tblOrders,MATCH(F4,tblOrders#標(biāo)題,0),FALSE)H5:=VLOOKUP(rngOrderId,tblOrders,MATCH(F5,tblOrders#標(biāo)題,0),FALSE)H6:=VLOOKUP(rngOrderId,tblOrders,MATCH(F6,tblOrders#標(biāo)題,0),FALS

16、E)H7：=VLOOKUP(rngOrderId,tblOrders,MATCH(F7,tblOrders#標(biāo)題,0),FALSE)至此，完成了我們的界面的上半部分，如圖6所示。Ordcrtlirr：er1morn：tianCeipihi'ifTWLbThSlratl匕往ktntc£0tuuUa.Lzp£r$orCHr在mJIeiLUHLaDer.;就G*F圖6:已完成的查找界面接下來，讓我們看看后面將要使用的公式中的一些函數(shù)。 實(shí)際上，我們將不會(huì)使用VLOOKU畫數(shù)。因?yàn)樾枰祷囟鄠€(gè)條目，因此需要一種方式來返回一組值，而VLOOKUP數(shù)不具備這樣的功能。這里，我們

17、使用INDEX函數(shù)返回指定行和列交叉部分的值。 我們需要指定想要在單元格中顯示的數(shù)組中的條目，這里可使用SMALL函數(shù)和ROV®數(shù)來實(shí)現(xiàn)。 我們希望如果在源數(shù)據(jù)中添加額外的列時(shí)確保數(shù)據(jù)能夠自動(dòng)調(diào)整，這里使用MATCIffi數(shù)。 最后，我們希望有錯(cuò)誤處理功能，這里使用IFERRO就數(shù)來確保如果在計(jì)算中有錯(cuò)誤將只是顯示空單元格。（IFERRO就數(shù)是Excel2007中新增的函數(shù)）好了，先讓我們來看看已經(jīng)完成的函數(shù)，如下圖7中的單元格C12所示（注意，這是一個(gè)數(shù)組公式，其左右兩側(cè)的花括號不需要自已輸入。在輸完公式后按下Ctrl+Shift+Enter組合鍵即可）。MmioKiirniKtb

18、inEiaiii,svar(it(tbiDeiAiiNLardernu10OrderIWHL0：割13.5：圖7:數(shù)組公式用于返回與特定值相匹配的第N項(xiàng)這個(gè)公式看起來很復(fù)雜，為便于閱讀，將其書寫如下:=IFERROR(INDEX(tblDetails,SMALL(IF(tblDetailsOrderID=rngOrderId,標(biāo)題）ROW(tblDetailsOrderID)-ROW(tblDetails#),ROW(1:1),MATCH(C$11,tblDetails#標(biāo)題,0),）首先，看看SMAL喃數(shù)，該函數(shù)接受一組值并從中返回第N小的值SMALL(IF(tblDetailsOrderI

19、D=rngOrderId,ROW(tblDetailsOrderID)-ROW(tblDetails#標(biāo)題),ROW(1:1),本例中，數(shù)組的值由IF函數(shù)確定。特別地，如果Details表中某行的OrderID與在下拉列表中選擇的OrdeED相等，那么添加該行的行號到數(shù)組值中。通過使用ROW!數(shù)，Excel能夠自動(dòng)調(diào)整公式來向下填充單元格。并且，通過使用結(jié)構(gòu)化引用，使得工作表數(shù)據(jù)能夠自動(dòng)適應(yīng)源數(shù)據(jù)的變化。然后，再看看INDEX函數(shù)，該函數(shù)接受一個(gè)二維數(shù)據(jù)并返回?cái)?shù)組中指定行列位置的值。INDEX(tblDetails,SMALL(IF(tblDetailsOrderID=rngOrderId,R

20、OW(tblDetailsOrderID)-ROW(tblDetails#標(biāo)題),ROW(1:1),MATCH(C$11,tblDetails#標(biāo)題,0),我們已經(jīng)知道，本例中的SMAL喃數(shù)返回與輸入的OrdeED相匹配的OrderDetails列表中ROW(Nth的值，這里是第一個(gè)值即行號。接著需要獲取列號，即使用MATC函數(shù)，本例中該函數(shù)接受C11的值并查找在Details表中有相同名稱的列。最后使用的是IFERRORS數(shù)，如果包含的計(jì)算式發(fā)生錯(cuò)誤，使用空字符串(”)替換錯(cuò)誤值?，F(xiàn)在，選擇單元格C12,拖動(dòng)其右下角的填充柄至單元格H12選擇單元格區(qū)域C12:H12,拖動(dòng)填充句柄向下10行或

21、更多?，F(xiàn)在，將顯示滿足該訂單號的所有信息，并且當(dāng)我們改變OrdeED時(shí)，將會(huì)自動(dòng)更新相關(guān)信息。圖8:完成的界面表，使用數(shù)組公式向下填充以獲取所有相關(guān)信息示例文檔下載：這個(gè)INDEX函數(shù)是什么意思=INDEX('1'!D:D,SMALL(IF('1'!$I$1:$I$33="需請購",ROW('1'!$I$1:$I$33),65536),ROW(B1)這里面有個(gè)if函數(shù)，誰能給我解釋一下那個(gè)65536是什么意思嗎？還有，按照EXCE郵助里面的內(nèi)容INDEX(array,row_num,column_num)返回?cái)?shù)組中指定單元格或單元格數(shù)組的數(shù)值。INDEX(reference,row_num,column_num,area_num)返回引用中指定單元格區(qū)域的引用“。以上那個(gè)INDEX函數(shù)屬于哪種？而且為什么里面就只有一個(gè)逗號？補(bǔ)充一下，我對以上IF函數(shù)的理解是如果1'!$I$1:$I$33=&qu