Medtem ko izbirajo, kateri recept za navpično poizvedbo naj uporabijo, se večina strokovnjakov za Excel strinja, da je INDEX MATCH boljša enačba kot VLOOKUP. Kakor koli že, veliko posameznikov se dejansko zateče k uporabi VLOOKUP-a v luči dejstva, da je to manj težaven recept. Eden pomembnih vzrokov za to težavo je, da veliko ljudi dejansko ne dojame popolnoma prednosti prehoda iz VLOOKUP-a in brez takšnega razumevanja nočejo prispevati priložnosti za pridobitev znanja z bolj osupljivo enačbo. Motivacija za to objavo je, da izčrpno predstavimo vse prednosti uporabe INDEX MATCH in vas prepričamo, da bi morali uporabiti INDEX MATCH samo za vaše poizvedbe navzgor kot celoto.

Kaj je INDEX MATCH v Excelu

Naslednja formula je mešanica dveh zmožnosti v Excelu: INDEX in MATCH.

java char v niz

=INDEX() vrne vrednost celice v tabeli glede na segment in številko stolpca.

=MATCH() vrne mesto celice v zaporedju ali segmentu.

Konsolidirani lahko obe enačbi pogledata navzgor in vrneta vrednost celice v tabeli glede na navpične standarde in standarde ravni.

Kako uporabljati formulo INDEX v Excelu

Sledi tabela, ki prikazuje imena, ravni in uteži posameznikov. Uporabiti moramo enačbo INDEX, da preučimo Thanishovo raven, tukaj je ilustracija, kako to uresničiti.

Po teh sredstvih,

• Vnesite =INDEX( in izberite regijo tabele, nato pa na tej točki dodajte vejico
• Vnesite številko stolpca za Thanish, ki je 4, in dodajte vejico
• Vnesite številko razdelka za Height, ki je 2, in zaprite razdelek
• Rezultat je 5.8.

Kako uporabljati formulo MATCH v Excelu

Če ostanemo pri podobnem modelu kot zgoraj, bi morali uporabiti MATCH, da razvrstimo, v katerem stolpcu je Thanish.

Po teh sredstvih,

• Vnesite =MATCH( in povezavo do celice, ki vsebuje Thanish ... ime, ki ga moramo obrniti navzgor.
• Izberite vsako celico v razdelku Ime (vključno z glavo Ime).
• Vnesite nič 0 za natančno ujemanje.
• Rezultat je, da je Thanish v stolpcu 4.

Kako združiti INDEX in MATCH

Trenutno lahko vzamemo dve enačbi MATCH in ju uporabimo, da nadomestimo 4 in 2 v prvem receptu INDEX. Rezultat je enačba INDEKSNEGA UJEMANJA.

Po teh sredstvih,

• Izrežite enačbo MATCH za Thanish in z njo nadomestite 4.
• Izrežite enačbo MATCH za višino in z njo nadomestite 2.
• Rezultat je, da je Thanisheva višina 5,8.
• Čestitke, trenutno imate zmogljivo enačbo INDEX MATCH!

Kaj je VLOOKUP v Excelu?

VLOOKUP pomeni 'navpično iskanje'. Zmožnost omogoča, da Excel išče določeno vrednost v segmentu (domnevna »gruča tabel«), da vrne vrednost iz nadomestnega razdelka v podobnem stolpcu. Ta članek vam bo pokazal, kako uporabiti zmožnost VLOOKUP.

Zmogljivost VLOOKUP obstaja v 4 delih,

• Vrednost, ki jo morate obrniti navzgor;
• Doseg, v katerem morate najti vrednost in vrniti spoštovanje;
• Količina razdelka v vašem značilnem obsegu, ki vsebuje vrnitev spoštovanja;
• 0 ali FALSE za natančno koordinato z vrednostjo, ki jo iščete; 1 ali TRUE za ocenjeno ujemanje.

Sintaksa: VLOOKUP([vrednost], [obseg], [številka stolpca], [false ali true])

V našem modelu imamo povzetek imen, višine in teže. V tej tabeli moramo hitro najti višino Saija. Najprej izberite celico, v katero morate razdeliti višino:

V skladu s tem začnemo v naši izbrani celici sestavljati: =VLOOKUP( :

Nato na tej točki izberemo vrednost, ki jo moramo preučiti, za to situacijo je to »Sai« v celici F3,

Nato vnesemo ',' in izberemo doseg (ali gručo tabele), v kateri moramo najti cenjenost poizvedbe in vrniti cenjenost. Za to situacijo je obseg (B2:D8):

Nato na tej točki vnesemo ',' in količino razdelka znotraj označenega obsega, v katerem moramo iskati vrnitev spoštovanja. Za to situacijo je to segment 2, ki mu sledi ',' in '0' ali Zavajajoče za natančno koordinato s poizvedbeno oceno 'Sai':

Ko pritisnemo enter, dobimo primerjalno ceno iz stolpca, ki ima vrednost 'Sai' znotraj izbrane tabele:

V tem modelu imamo le majhen povzetek imen, vendar si predstavljajte, da imate precejšen potek informacij in želite uporabiti določeno vrednost nekje drugje v vašem knjigovodskem listu. Z uporabo funkcije VLOOKUP (in HLOOKUP) mora odjemalec samo spremeniti določeno vrednost na enem delovnem listu in posledično bo spremenjena na vseh preostalih pomembnih mestih. Kakor koli že, predstavljajte si scenarij, v katerem nismo prepričani glede ocene poizvedbe. To je točka, na kateri je lahko ocenjeno ujemanje dragoceno.

Ocenjeno ujemanje v VLOOKUP

Na točki, ko vnesemo besedo 'Sia' v našo poizvedbeno celico G3, se zmožnost VLOOKUP, kot smo načrtovali, zmoti, ker ne more izslediti te vrednosti v tabeli. Vrne standardno Excelovo zmoto #N/A:

Kakor koli že, ko spremenimo del '0' (ali 'False') naše zmožnosti VLOOKUP v '1' ali 'Valid':

=VLOOKUP(F3;B2:D8;2;1)

Trenutno smo zahtevali, da zmogljivost poišče predvideno koordinacijo s „Sio“. V primeru, da ne najde določenega ujemanja, bo poiskal ujemanje, ki je najbližje vrednosti »Sia«, ne toliko kot »Sia«, ki je »Sai«:

INDEX MATCH Vs. VLOOKUP

Dinamično sklicevanje na stolpec vodi do manj napak

Kritično nasprotje med INDEX MATCH in VLOOKUP je, da VLOOKUP zahteva statično referenco odseka, medtem ko INDEX MATCH uporablja močno referenco segmenta. Z VLOOKUP bo velika večina vključila določeno, statično številko, ki bo pokazala, iz katerega razdelka se morajo vrniti. Na točki, ko uporabite INDEX MATCH, vam recept dovoljuje, da fizično izberete razdelek, iz katerega želite potegniti.

Razlaga, da to povzroči manj napak, je na podlagi tega, da ko sledite jezikovni strukturi INDEX MATCH, kliknete naravnost na polje, ki vsebuje vrednost, ki jo morate vrniti.

Z jezikovno strukturo VLOOKUP določite celotno razstavo tabele IN POTEM določite sklic na razdelek, da pokažete, iz katerega segmenta morate črpati informacije.

To je majhen kontrast, vendar ta dodatni korak nedvomno povzroči več napak. Ta napaka je še posebej prevladujoča, če imate ogromno gručo tabel in morate navzven prešteti število razdelkov, čez katere se morate premakniti. V trenutku, ko uporabite INDEX MATCH, takšno štetje ni potrebno.

Naslednji dve prednosti uporabe INDEX MATCH prav tako izhajata iz načina, na katerega VLOOKUP zahteva statični referenčni vnos segmenta za kvalitete, ki jih morate vrniti. Če to ni prevelika težava, upoštevajte, da bi se lahko izognili težavi s statično referenco z uporabo enačbe znotraj ločila VLOOKUP, s čimer bi ustvarili mešani recept, kot je VLOOKUP MATCH ali VLOOKUP HLOOKUP. Ne glede na to je veliko preprostejše samo naučiti se INDEX MATCH in pridobiti vse dodatne prednosti v primerjavi z dinamičnim sklicevanjem na odsek.

Vstavite odpornost stolpca

Najboljša prednost uporabe INDEX MATCH pred VLOOKUP je način, na katerega lahko z INDEX MATCH vdelate segmente v svojo tabelo, ne da bi popravili rezultate poizvedbe. Vsakič, ko delate z ogromnim naborom podatkov, obstaja spodobna priložnost, da se boste morali vrniti, da spremenite naše razdelke in morda vdelate drug segment. Z VLOOKUP-om bodo vsi vdelani ali izbrisani odseki, ki izkoreninijo vaše vrnjene vrednosti, spremenili posledice vaših enačb.

Vzemite model VLOOKUP spodaj. Tukaj smo uredili enačbo, da potegnemo spoštovanje države iz naše informacijske tabele. Ker gre za recept VLOOKUP, smo se sklicevali na četrti razdelek.

Če vstavimo stolpec na sredino matrike tabele, je nov rezultat zdaj Bangalore; ne vlečemo več pravilne vrednosti za državo in moramo spremeniti sklic stolpca.

INDEX MATCH kot nedovzetnost za vdelane odseke, tako da lahko vdelate in izbrišete segmente, ne da bi se obremenjevali z osveževanjem vsake povezane enačbe poizvedbe.

Preprostejše vlečenje in kopiranje

Pri delu z ogromnimi nabori podatkov je neobičajno sestaviti le en recept za poizvedbo; verjetno boste morali svojo enačbo povleči in podvojiti v številne celice za predvajanje različnih poizvedb. Na primer, predpostavimo, da moram za določen ID vrniti napredovanje vrednosti iz svoje tabele natančno glede na vzorec, v katerem so prikazane v tabeli. Na točki, ko poskušam povleči in podvojiti standardno enačbo VLOOKUP (z zaklepanjem sklicevanja tako na vrednosti poizvedbe kot na gruči tabele), poizvedba ne deluje, saj preprosto potegne podobno spodbudo za vsak odlomek. Ta pomanjkljivost je ponovno posledica funkcije VLOOKUP, ki zahteva vnos sklicevanja na določen odsek za vrnitev vrednosti.

Pri INDEX MATCH, ker lahko nastavite odmik povratnega odseka (v bistvu tako, da ne zaklenete sklicevanja), se bo povratni segment premaknil, ko boste podvojili svojo enačbo, kar vam bo dalo različna polja, ko se prikažejo.

Brez omejitve polja

Še ena ključna ovira funkcije VLOOKUP je, da od vas pričakuje, da določite kvadratno gručo tabele, mimo katere se referenca segmenta ne more premakniti. Ključna okoliščina, ko se to spremeni v težavo, je točka, ko v svoj nabor podatkov dodate drugo polje mimo edinstvene gruče tabele. Razmislite o spodnjem modelu:

Ob predpostavki, da temu naboru podatkov dodamo še en segment, ne moremo uporabiti mojega edinstvenega recepta VLOOKUP za črpanje vrednosti iz tega novega razdelka. V primeru, da mojo sklicevanje na razdelek spremenimo na 6, recept vrne napako na podlagi tega, da je moja tabela široka samo 5 segmentov. Da bo recept deloval, moramo osvežiti eksponat tabele, ki smo ga določili vsakič, ko dodamo nov segment.

Z INDEX MATCH vam ni treba določiti gruče tabele in se zato ne ukvarjajte s to težavo.

Iskanje od desne proti levi

Ena od bistvenih prednosti uporabe INDEX MATCH je možnost, da poizvedbene ključe postavite na desno polovico tabele z informacijami. Novi poizvedbeni ključi so večinoma narejeni z izvajanjem ocen na poljih znotraj vašega edinstvenega nabora podatkov. Ti ključi obravnavajo vaš segment poizvedbe znotraj jezikovne strukture INDEX MATCH. Medtem ko uporabljate INDEX MATCH, lahko dodate te nove poizvedbene ključe na desno polovico tabele in predvajate možnost posredovanja na poizvedbo, da pridobite lastnosti, ki jih morate vrniti. Ločilo INDEX MATCH ne vpliva na to, ali je segment vaše poizvedbe na levi ali desni polovici vašega razdelka za vrnitev.

Z VLOOKUP, na podlagi tega, da lahko predvajate poizvedbo od leve proti desni, mora biti vsak nov ključ poizvedbe, ki ga dodate, na levi polovici vaše edinstvene gruče tabele. Zato morate vsakič, ko dodate nov ključ, premakniti celoten nabor podatkov na eno stran za en segment. Poleg dejstva, da je to dražilno, lahko ovira obstoječe recepte in izračune, ki ste jih naredili v svojem izračunskem listu.

Deluje kot HLOOKUP

To verjetno ni najboljša ureditev za večino strank, vendar se je vseeno vredno osredotočiti nanjo. VLOOKUP vas omejuje samo na izvajanje navpičnih poizvedb v gruči tabel. Ne morem na primer poizvedovati po vrednostih na najvišji točki mojega nabora podatkov in predvajati pavšalne poizvedbe. Da bi dosegel ta cilj, bi moral uporabiti nekaj povsem drugega.

Z INDEX MATCH lahko v bistvu enakomerno organizirate razstavo svoje poizvedbe in vrnjeno gručo, da predvajate HLOOKUP. Kot splošno pravilo ne predlagam izvajanja ravnih poizvedb, vendar bi morali uporabiti recept INDEX MATCH, če bi to kadar koli potrebovali.

Nižja potreba po obdelavi

Prednost ravnanja z uporabo INDEX MATCH je bila podcenjena v tekočih prilagoditvah Excela na podlagi tega, da so se zmogljivosti ravnanja izdelka tako izjemno razširile. Kakor koli že, preprosto vsem dajte jedrnat zgodovinski primer, ki ga bom razširil na to prednost. V delu Excelovih bolj izkušenih izvedb je imel izdelek nedvoumne omejitve pri rokovanju, ki bi jih lahko opazili, če bi ustvarili ogromen informativni indeks. V določenih okoliščinah se je od mene pričakovalo, da bom poizvedoval po vrednostih za zelo veliko vrstic, da bi lahko dodal še en razdelek v ogromno tabelo. Ko sem dodal te recepte, bi izdelek zmrznil in potreboval bi nekaj minut, da se vrnejo vrednosti. Končno sem moral svoje recepte VLOOKUP nadomestiti z INDEX MATCH, da bi pospešil ocene.

Utemeljitev tega razlikovanja je zelo enostavna. VLOOKUP od Excela zahteva resno moč upravljanja glede na dejstvo, da mora oceniti celotno gručo tabele, ki ste jo izbrali. Z INDEX MATCH mora Excel razmišljati samo o segmentu poizvedbe in vrnjenem razdelku. Z manj neposrednimi celicami, ki jih je treba upoštevati, lahko Excel ta recept obravnava veliko hitreje.

Še enkrat, če to ni preveč težavno, ne pozabite, da ta prednost najverjetneje trenutno ni opazna glede na nadgradnje, ki so bile izvedene v Excelovi moči upravljanja. V vsakem primeru, če v kakršnih koli okoliščinah resnično želite zagnati veliko število receptov poizvedbe znotraj lista za izračun, poskusite uporabiti INDEX MATCH.

Primerjalna tabela VLOOKUP in INDEX MATCH

Zaključek

Edina ovira pri uporabi INDEX MATCH je, da je očitno bolj navdušeno za učenje in težje priklicati. Succeed nima stavčne strukture vključene v izdelek in ločila brez drugih niso instinktivna.

Spreminjanje iz VLOOKUP v INDEX MATCH je bil eden ključnih napredkov, ki sem jih uporabil pri delu na naravi Excelovih modelov, ki sem jih sestavil. Trenutno uporabljam INDEX MATCH izključno za svoje poizvedbe navzgor na splošno. To delam, ker je vrhunski recept, poleg tega pa mi pomaga pri priklicu bolj zapletenih ločil. Kadar koli ste preklopili in se srečali s prednostmi uporabe INDEX MATCH, vam zagotavljam, da se ne boste vrnili na VLOOKUP.

Pogosta vprašanja o tem, zakaj je INDEX MATCH boljši od VLOOKUP v Excelu

Ali lahko INDEX MATCH obravnava več iskanj kriterijev?

INDEX MATCH je zmožen obravnavati več iskanj kriterijev z uporabo več funkcij MATCH skupaj. Ta funkcija vam omogoča izvajanje zapletenih iskanj in pridobivanje natančnih podatkov na podlagi več pogojev.

Kakšne so omejitve INDEX MATCH?

Čeprav je INDEX MATCH zmogljiva funkcija, je edina težava te funkcije ta, da je morda bolj zapletena za uporabo kot VLOOKUP, predvsem za začetnike. Pri pisanju formule bodite bolj pozorni.

Ali lahko zamenjamo vse LOOKUPS z INDEX MATCH?

INDEX MATCH je vsestranska alternativa VLOOKUP-u, ni treba zamenjati vseh VLOOKUP-ov. Za preprosta navpična iskanja je VLOOKUP še vedno učinkovit in lažji za uporabo.