Zergatik INDEX eta MATCH dira Excel-en VLOOKUP baino hobeak

Lehenago hasiberriei VLOOKUP-en oinarrizko funtzioak nola erabili (ingelesez VLOOKUP, laburdurak "bilaketa bertikala funtzioa") esan nahi du. Eta esperientziadun erabiltzaileei hainbat formula konplikatuago erakutsi zitzaizkien.

Eta artikulu honetan bilaketa bertikala lantzeko beste metodo bati buruzko informazioa ematen saiatuko gara.

Galdetuko zara: "Zergatik da beharrezkoa?". Eta hori beharrezkoa da bilaketa-metodo posible guztiak erakusteko. Gainera, VLOOKUP murrizketa ugarik askotan nahi den emaitza lortzea eragozten dute. Ildo horretatik, INDEX( ) MATCH( ) askoz funtzionalagoa eta anitzagoa da, eta murrizketa gutxiago dituzte.

Oinarrizkoak AURKIBIDEA MATCH

Gida honen helburua ezaugarri hau zein ona den erakustea denez, guk Ikus ditzagun bere funtzionamenduaren printzipioei buruzko oinarrizko informazioa. Eta adibideak erakutsiko ditugu, eta aztertuko dugu zergatik den VLOOKUP () baino hobea.

AURKIBIDEA Funtzio sintaxia eta erabilera

Funtzio honek zutabearen edo lerroaren zenbakiaren arabera zehaztutako bilaketa-eremuen artean nahi duzun balioa bilatzen laguntzen du. Sintaxia:

=INDEX(matrizea, errenkada-zenbakia, zutabe-zenbakia):

• array - bilaketa egingo den eremua;
• lerro-zenbakia – zehaztutako matrizean bilatu beharreko lerroaren zenbakia. Errenkada-zenbakia ezezaguna bada, zutabe-zenbakia zehaztu behar da;
• zutabe-zenbakia - zehaztutako matrizean aurkitu beharreko zutabearen zenbakia. Balioa ezezaguna bada, lerro-zenbaki bat behar da.

Formula sinple baten adibidea:

=AURKIBIDEA(A1:S10,2,3)

Funtzioak A1 eta C10 arteko tartean bilatuko du. Zenbakiek zein errenkada (2) eta zutabe (3) erakutsi nahi den balioa erakusten dute. Emaitza C2 gelaxka izango da.

Nahiko sinplea, ezta? Baina benetako dokumentuekin lan egiten duzunean, nekez izango duzu zutabe-zenbakiei edo gelaxken inguruko informazioa. Horretarako dago MATCH() funtzioa.

MATCH Funtzioaren sintaxia eta erabilera

MATCH() funtzioak nahi duzun balioa bilatzen du eta bere gutxi gorabeherako zenbakia erakusten du zehaztutako bilaketa-eremuan.

searchpos() sintaxiak itxura hau du:

=MATCH(bilaketari dagokion balioa, bilaketarako matrizea, bat-etortze mota)

• bilaketa-balioa - aurkitu beharreko zenbakia edo testua;
• searched array - bilaketa egingo den eremua;
• bat-etortze-mota - balio zehatza edo hurbilen dauden balioak bilatu behar diren zehazten du:
  • 1 (edo ez dago zehaztutako baliorik) - zehaztutako balioaren berdina edo txikiagoa den balio handiena itzultzen du;
  • 0: bat-etortze zehatza erakusten du bilatutako balioarekin. INDEX() MATCH() konbinazioan ia beti bat-etortze zehatza beharko duzu, beraz, 0 idazten dugu;
  • -1 - Formulan zehaztutako balioa baino handiagoa edo berdina den balio txikiena erakusten du. Sailkatzea beheranzko ordenan egiten da.

Adibidez, B1:B3 barrutian New York, Paris, Londres erregistratuta daude. Beheko formulak 3 zenbakia erakutsiko du Londres zerrendan hirugarrena baita:

=EXPOSI (Londres,B1:B3,0)

INDEX MATCH funtzioarekin nola lan egin 

Ziurrenik, funtzio horien baterako lana eraikitzen den printzipioa ulertzen hasia zara jada. Laburbilduz, bada INDEX() zehaztutako errenkada eta zutabeen artean nahi den balioa bilatzen du. Eta MATCH() balio hauen zenbakiak erakusten ditu:

=INDEX(balioa itzultzen den zutabea, MATCH(balioa bilatzeko, zutabea bilatzeko, 0))

Oraindik zaila duzu nola funtzionatzen duen ulertzea? Agian adibideren batek hobeto azalduko du. Demagun munduko hiriburuen eta haien biztanleriaren zerrenda bat duzula:

Hiriburu jakin bateko biztanleriaren tamaina jakiteko, adibidez, Japoniako hiriburua, honako formula hau erabiltzen dugu:

=AURKIBIDEA(C2:C10, MATCH(Japonia, A2:A10,0))

Azalpen:

• MATCH() funtzioak A2:A10 matrizean "Japan" balioa bilatzen du eta 3 zenbakia itzultzen du, Japonia delako zerrendako hirugarren balioa. 
• Zifra hau doalerro-zenbakia” INDEX() formulan eta funtzioari errenkada honetako balio bat inprimatzeko esaten dio.

Beraz, goiko formula formula estandarra bihurtzen da AURKIBIDEA(C2:C10,3). Formulak C2 gelaxketatik C10era bilatzen du eta barruti honetako hirugarren gelaxkako datuak itzultzen ditu, hau da, C4, atzerako kontaketa bigarren errenkadatik hasten delako.

Ez al duzu formulan hiriaren izena ezarri nahi? Ondoren, idatzi edozein gelaxkatan, esan F1, eta erabili erreferentzia gisa MATCH() formulan. Eta bilaketa-formula dinamiko batekin amaitzen duzu:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Garrantzitsua! Lerro kopurua barne array INDEX() errenkada kopuruaren berdina izan behar du array kontsideratua MATCH()n, bestela emaitza okerra lortuko duzu.

Itxaron pixka bat, zergatik ez erabili VLOOKUP() formula?

=BIKETA(F1, A2:C10, 3, faltsua)

 Zertarako balio du INDEX MATCH-en konplexutasun horiek guztiak asmatu nahian denbora galtzeak?

Kasu honetan, berdin du zein funtzio erabili. Hau adibide bat besterik ez da INDEX() eta MATCH() funtzioek elkarrekin nola funtzionatzen duten ulertzeko. Beste adibide batzuek funtzio hauek zertarako gai diren erakutsiko dute VLOOKUP botererik gabeko egoeretan. 

INDEX MATCH edo VLOOKUP

Zein bilaketa-formula erabili erabakitzeko orduan, asko ados daude INDEX() eta MATCH() VLOOKUP baino askoz handiagoak direla. Hala ere, jende askok VLOOKUP() erabiltzen du oraindik. Lehenik eta behin, VLOOKUP() sinpleagoa da, eta bigarrenik, erabiltzaileek ez dituzte guztiz ulertzen INDEX() eta MATCH()-ekin lan egitearen abantaila guztiak. Ezagutza hori gabe, inor ez da onartuko bere denbora sistema konplexu bat aztertzen pasatzea.

Hona hemen INDEX() eta MATCH() VLOOKUP()-ren abantaila nagusiak:

• Bilatu eskuinetik ezkerrera. VLOOKUP() ezin da eskuinetik ezkerrera bilatu, beraz, bilatzen ari zaren balioek taulako ezkerreko zutabeetan egon behar dute beti. Baina INDEX() eta MATCH()-ek arazorik gabe kudea dezakete. Artikulu honek praktikan nolakoa den esango dizu: nola aurkitu nahi duzun balioa ezkerreko aldean.

1. Zutabeak gehitzea edo kentzea segurua. VLOOKUP() formulak emaitza okerrak erakusten ditu zutabeak kentzean edo gehitzean, VLOOKUP() zutabe-zenbaki zehatza behar duelako arrakasta izateko. Jakina, zutabeak gehitzen edo kentzen direnean, haien zenbakiak ere aldatzen dira. 

Eta INDEX() eta MATCH() formuletan, zutabe sorta bat zehazten da, ez zutabe indibidualak. Ondorioz, zutabeak segurtasunez gehitu eta kendu ditzakezu formula bakoitzean eguneratu beharrik gabe.

1. Ez dago mugarik bilaketa-bolumenetan. VLOOKUP() erabiltzean, bilaketa-irizpideen guztizko kopuruak ez du 255 karaktere baino gehiago izan behar edo #BALOIA lortuko duzu! Beraz, zure datuek karaktere kopuru handia badute, INDEX() eta MATCH() dira aukerarik onena.
2. Prozesatzeko abiadura handia. Zure mahaiak nahiko txikiak badira, orduan nekez nabarituko duzu desberdintasunik. Baina, taulak ehunka edo milaka errenkada baditu, eta, horren arabera, ehunka eta milaka formula badaude, INDEX () eta MATCH () VLOOKUP () baino askoz azkarrago egingo dute aurre. Kontua da Excel-ek formulan zehaztutako zutabeak soilik prozesatuko dituela, taula osoa prozesatu beharrean. 

VLOOKUP()-ren errendimenduaren eragina bereziki nabarmena izango da zure lan-orriak VLOOKUP() eta SUM() bezalako formula ugari baditu. VLOOKUP() funtzioen egiaztapen bereiziak behar dira array bateko balio bakoitza analizatzeko. Beraz, Excel-ek informazio kopuru handia prozesatu behar du, eta horrek lana nabarmen moteltzen du.

Formula Adibideak 

Funtzio horien erabilgarritasuna igarri dugu jada, eta, beraz, alderik interesgarrienera pasa gaitezke: ezagutza praktikan aplikatzea.

Eskuinetik ezkerrera bilatzeko formula

Esan bezala, VLOOKUP-ek ezin du bilaketa mota hau egin. Beraz, nahi diren balioak ezkerreko zutabean ez badaude, VLOOKUP()-k ez du emaitzarik emango. INDEX() eta MATCH() funtzioak polifazetikoagoak dira, eta balioen kokapenak ez du funtzio handirik betetzen.

Adibidez, gure taularen ezkerreko aldean maila-zutabe bat gehituko dugu eta Gure Herriko hiriburuak biztanleriari dagokionez zer maila duen asmatzen saiatuko gara.

G1 gelaxkan, aurkitu beharreko balioa idazten dugu eta, ondoren, formula hau erabiliko dugu C1:C10 barrutian bilatzeko eta A2:A10-tik dagokion balioa itzultzeko:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Galdera. Formula hau gelaxka anitzetan erabiltzeko asmoa baduzu, ziurtatu barrutiak helbide absolutua erabiliz konpontzen dituzula (adibidez, $A$2: $A$10 eta $C$2: 4C$10).

AURKIBIDEA ERAKUGI GEHIAGO  zutabeetan eta errenkadetan bilatzeko

Goiko adibideetan, funtzio hauek VLOOKUP()-ren ordezko gisa erabili ditugu aurredefinitutako errenkada sorta bateko balioak itzultzeko. Baina zer gertatzen da matrizea edo bi aldeetako bilaketa bat egin behar baduzu?

Konplikatua dirudi, baina kalkulu horien formula INDEX() MATCH() formula estandarraren antzekoa da, desberdintasun bakarrarekin: MATCH() formula bi aldiz erabili behar da. Lehenengo aldiz errenkada-zenbakia lortzeko, eta bigarrena zutabe-zenbakia lortzeko:

=INDEX(matrizea, MATCH (bilaketa-balioa, bilaketa-zutabea, 0), MATCH (bilaketa-balio horizontala, bilaketa-errenda, 0))

Ikus dezagun beheko taula eta saiatu formula bat egiten AURKIBIDEA() ADIERAZPENA() ADIERAZPENA() hautatutako urte bateko herrialde zehatz bateko demografia bistaratzeko.

Xede-herrialdea G1 gelaxkan dago (bilaketa bertikala) eta xede-urtea G2 gelaxkan (bilaketa horizontala). Formulak honela izango du:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Formula honek nola funtzionatzen duen

Beste edozein formula konplexuekin bezala, errazago ulertzen dira ekuazio indibidualetan zatituz. Ondoren, funtzio bakoitzak zer egiten duen ulertu dezakezu:

• PARTIDA(G1,A2:A11,0) – A1:A2 barrutian balio bat (G11) bilatzen du eta balio horren zenbakia erakusten du, gure kasuan 2 da;
• BILATU(G2,B1:D1,0) – balio bat (G2) bilatzen du B1:D1 tartean. Kasu honetan, emaitza 3 izan zen.

Aurkitutako errenkada eta zutabe zenbakiak INDEX() formulako dagokion baliora bidaltzen dira:

=AURKIBIDEA(B2:D11,2,3)

Ondorioz, B2:D3 barrutian 2 errenkadaren eta 11 zutabeen elkargunean dagoen gelaxka batean dagoen balio bat dugu. Eta formulak nahi den balioa erakusten du, D3 gelaxkan dagoena.

Bilatu hainbat baldintzaren arabera INDEX eta MATCH erabiliz

VLOOKUP()-rako gure gida irakurri baduzu, ziurrenik hainbat bilaketa-formula probatu dituzu. Baina bilaketa-metodo honek muga nabarmen bat du: zutabe laguntzaile bat gehitu beharra.

Baina berri ona hori da INDEX() eta MATCH()-ekin hainbat baldintza bilatu ditzakezu zure lan-orria editatu edo aldatu beharrik gabe.

Hona hemen INDEX() MATCH() baldintza anitzeko bilaketa-formula orokorra:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Oharra: formula hori teklatuko lasterbidearekin batera erabili behar da CTRL+SHIFT+SARTU.

Demagun bila ari zaren balioa bi baldintzatan oinarrituta aurkitu behar duzula: Buyer и Produktuen.

Honek formula hau eskatzen du:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Formula honetan, C2:C10 bilaketa egingo den barrutia da, F1 - Baldintza hau, A2:A10 - egoera alderatzeko tartea da, F2 - 2. baldintza, V2:V10 – 2. egoera alderatzeko tartea.

Ez ahaztu formularekin lanaren amaieran konbinazioa sakatzeaz CTRL + MAIUS + SARTU - Excelek automatikoki itxiko du formula giltza kizkurrekin, adibidean erakusten den moduan:

Zure lanerako matrize-formularik erabili nahi ez baduzu, gehitu beste INDEX() formulari eta sakatu ENTER, adibideko itxura izango du:

Formula hauek nola funtzionatzen duten

Formula honek INDEX() MATCH() formula estandarraren modu berean funtzionatzen du. Baldintza anitz bilatzeko, baldintza indibidual zuzenak eta okerrak adierazten dituzten baldintza faltsu eta egiazko anitz sortu besterik ez duzu. Eta gero baldintza hauek matrizeari dagozkion elementu guztiei aplikatzen zaizkie. Formulak False eta True argumentuak 0 eta 1 bihurtzen ditu, hurrenez hurren, eta matrize bat ateratzen du, non 1 katean aurkitu diren balio bat datozenak diren. MATCH() 1arekin bat datorren lehen balioa aurkituko du eta INDEX() formulara pasatuko du. Eta, aldi berean, nahi den zutabetik zehaztutako lerroan lehendik nahi den balioa itzuliko du.

Arrayrik gabeko formula bat INDEX()-ek bere kabuz kudeatzeko duen gaitasunaren araberakoa da. Formulako bigarren INDEX() falsy (0) bat dator, beraz, balio horiek dituen array osoa MATCH() formulara pasatzen du. 

Formula honen atzean dagoen logikaren azalpen luze samarra da hau. Informazio gehiagorako irakurri artikulua "INDEX MATCH baldintza anitzekin'.

BATEZBESTEKOA, MAX. eta MIN. INDEX eta MATCH-n

Excel-ek bere funtzio bereziak ditu batez bestekoak, maximoak eta minimoak aurkitzeko. Baina zer gertatzen da balio horiekin lotutako gelaxkako datuak lortu nahi badituzu? Kasu honetan AVERAGE, MAX eta MIN erabili behar dira INDEX eta MATCH-ekin batera.

INDEX MATCH eta MAX

D zutabean balio handiena aurkitzeko eta C zutabean bistaratzeko, erabili formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH eta MIN

D zutabean baliorik txikiena aurkitzeko eta C zutabean bistaratzeko, erabili formula hau:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

BILATZAILEA eta SUGEA

D zutabean batez besteko balioa aurkitzeko eta balio hau C-n bistaratzeko:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Zure datuak idazteko moduaren arabera, MATCH()-ren hirugarren argumentua 1, 0 edo -1 da:

• zutabeak goranzko ordenan ordenatzen badira, ezarri 1 (orduan formulak balio maximoa kalkulatuko du, hau da, batez besteko balioa baino txikiagoa edo berdina);
• ordena beherakorra bada, orduan -1 (formulak batez bestekoa baino handiagoa edo berdina den balio minimoa aterako du);
• Bilaketa-matrizeak batez bestekoaren berdina den balio bat badu, ezarri 0-n. 

 Gure adibidean, populazioa beheranzko ordenan ordenatzen da, beraz -1 jartzen dugu. Eta emaitza Tokio da, biztanleriaren balioa (13,189) batez besteko baliotik (000) hurbilen baita.

VLOOKUP() kalkuluak ere egin ditzake, baina matrize formula gisa soilik: VLOOKUP AVERAGE, MIN eta MAX-ekin.

INDEX MATCH eta ESND/IFERROR

Seguruenik dagoeneko ohartu zara formulak ezin badu nahi den balioa aurkitzen, errore bat botatzen duela # N / A. Errore-mezu estandarra informazio gehiagorekin ordez dezakezu. Adibidez, ezarri argumentua formulan XNUMXan:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Formula honekin, taulan ez dauden datuak sartzen badituzu, inprimakiak zehaztutako mezua emango dizu.

Akats guztiak harrapatu nahi badituzu, orduan izan ezik XNUMXan erabili ahal izango dira ERROREA:

=ERROREA(AURKIBIDEA(C2:C10,MATCH(F1,A2:A10,0)), "Zerbait gaizki egon da!")

Baina gogoratu erroreak modu honetan ezkutatzea ez dela ideia ona, errore estandarrek formulan urraketak adierazten dituztelako.

INDEX MATCH() funtzioa erabiltzeko gure gida lagungarria izatea espero dugu.