Excel

23 stvari, ki jih morate vedeti o VLOOKUP -u

23 Things You Should Know About Vlookup

Če želite izvleči podatke iz tabele, je funkcija Excel VLOOKUP odlična rešitev. Sposobnost dinamičnega iskanja in pridobivanja informacij iz tabele je za mnoge uporabnike zelo pomembna, zato boste VLOOKUP našli povsod.



Kljub temu, da je VLOOKUP razmeroma enostaven za uporabo, je lahko veliko narobe. Eden od razlogov je, da ima VLOOKUP veliko oblikovno napako - privzeto predpostavlja, da ste v redu s približnim ujemanjem. Kar verjetno niste.

To lahko povzroči take rezultate videti popolnoma normalno , čeprav so popolnoma napačno . Verjemite mi, tega NE želite poskusiti razložiti svoji šefici, potem ko je že poslala vašo preglednico vodstvu :)





Spodaj preberite, kako se spopasti s tem izzivom, in odkrijte druge nasvete za obvladovanje Excel VLOOKUP funkcija .

1. Kako deluje VLOOKUP

VLOOKUP je funkcija za iskanje in pridobivanje podatkov v tabeli. 'V' v VLOOKUP pomeni navpično, kar pomeni, da morajo biti podatki v tabeli razporejeni navpično, s podatki v vrsticah. (Za vodoravno strukturirane podatke glejte HLOOKUP ).



Če imate dobro strukturirano tabelo z navpično razporejenimi podatki in stolpec na levi strani, ki ga lahko uporabite za ujemanje vrstice, lahko verjetno uporabite VLOOKUP.

VLOOKUP zahteva, da je tabela strukturirana tako, da se iskalne vrednosti prikažejo v skrajnem levem stolpcu. Podatki, ki jih želite pridobiti (vrednosti rezultatov), ​​so lahko prikazani v katerem koli stolpcu na desni. Ko uporabljate VLOOKUP, si predstavljajte, da je vsak stolpec v tabeli oštevilčen, začenši z leve. Če želite iz določenega stolpca dobiti vrednost, preprosto vnesite ustrezno številko kot 'indeks stolpca'. V spodnjem primeru želimo poiskati e -poštni naslov, zato za indeks stolpca uporabljamo številko 4:

Pregled delovanja VLOOKUP -a

V zgornji tabeli so ID -ji zaposlenih v stolpcu 1 na levi, e -poštni naslovi pa v stolpcu 4 na desni.

Če želite uporabljati VLOOKUP, predložite 4 podatke ali 'argumente':

  1. Vrednost, ki jo iščete ( lookup_value )
  2. Obseg celic, ki sestavljajo tabelo ( matrika_maza )
  3. Številka stolpca, iz katerega želite pridobiti rezultat ( stolpec_indeks )
  4. Način ujemanja ( range_lookup , TRUE = približno, FALSE = natančno)

Video: Kako uporabljati VLOOKUP (3 minute)

Če še vedno ne razumete osnovne ideje o VLOOKUP -u, ima Jon Acampora v Excelovem kampusu super razlaga temelji na meniju kave Starbucks.

2. VLOOKUP izgleda samo pravilno

Morda je največja omejitev VLOOKUP -a ta, da lahko gleda le desno v smeri pridobivanja podatkov.

To pomeni, da lahko VLOOKUP dobi podatke samo iz stolpcev desno od prvega stolpca v tabeli. Ko se iskalne vrednosti prikažejo v prvem (skrajnem levem) stolpcu, ta omejitev ne pomeni veliko, saj so vsi drugi stolpci že na desni. Če pa se iskalni stolpec nekje pojavi v tabeli, boste lahko iskali samo vrednosti iz stolpcev desno od tega stolpca. VLOOKUP -u boste morali dostaviti tudi manjšo tabelo, ki se začne s stolpcem za iskanje.

VLOOKUP lahko gleda le desno od stolpca vrednosti iskanja

To omejitev lahko premagate z uporabo INDEX in MATCH namesto VLOOKUP.

3. VLOOKUP najde prvo ujemanje

V načinu natančnega ujemanja, če iskalni stolpec vsebuje podvojene vrednosti, se bo VLOOKUP ujemal samo s prvo vrednostjo. V spodnjem primeru uporabljamo VLOOKUP za iskanje imena in VLOOKUP je nastavljen za natančno ujemanje. Čeprav sta na seznamu dve 'Janet', se VLOOKUP ujema le s prvim:

VLOOKUP vedno najde prvo tekmo

Opomba: obnašanje se lahko spremeni, če uporabljate VLOOKUP v načinu približnega ujemanja. Ta članek podrobno razlaga temo.

4. VLOOKUP ne razlikuje velikih in malih črk

Pri iskanju vrednosti VLOOKUP ne obdeluje besedila velikih in malih črk drugače. Za VLOOKUP je koda izdelka, kot je 'PQRF', enaka 'pqrf'. V spodnjem primeru iščemo velike črke 'JANET', vendar VLOOKUP ne razlikuje velikih črk, zato se preprosto ujema z 'Janet', saj je to prvo ujemanje, ki ga najde:

VLOOKUP NI občutljiv na velike in male črke

Ponujamo tudi plačano usposabljanje za VLOOKUP in INDEX/MATCH

5. VLOOKUP ima dva načina ujemanja

VLOOKUP ima dva načina delovanja: natančno in približno ujemanje. V večini primerov boste verjetno želeli uporabiti VLOOKUP v načinu natančnega ujemanja. To je smiselno, če želite poiskati podatke na podlagi neke vrste edinstvenega ključa, na primer podatke o izdelku na podlagi kode izdelka ali podatke o filmu na podlagi naslova filma:

Primer natančnega ujemanja VLOOKUP - ujemanje filmov

Formula v letu H6 za iskalno leto, ki temelji na natančnem ujemanju naslova filma, je:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Vendar boste želeli uporabiti približen način, če se ne ujemate z edinstvenim ID -jem, ampak iščete 'najboljše ujemanje' ali 'najboljšo kategorijo'. Na primer, morda iščete poštnino glede na težo, davčno stopnjo glede na dohodek ali provizijo na podlagi mesečne prodajne številke. V teh primerih natančne vrednosti iskanja v tabeli verjetno ne boste našli. Namesto tega želite, da vam VLOOKUP zagotovi najboljše ujemanje z določeno vrednostjo iskanja.

Primer približnega ujemanja VLOOKUP - provizije

Formula v D5 se približno ujema za pridobitev pravilne provizije:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Pozor: VLOOKUP privzeto uporablja približno ujemanje

Natančno in približno ujemanje v VLOOKUP -u nadzira 4. argument, imenovan 'iskanje obsega'. To ime ni intuitivno, zato si morate zapomniti, kako deluje.

Za natančno ujemanje uporabite FALSE ali 0. Za približno ujemanje nastavite range_lookup na TRUE ali 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Na žalost je četrti argument, range_lookup, neobvezen in je privzeto na TRUE, kar pomeni, da bo VLOOKUP privzeto opravil približno ujemanje. Pri približnem ujemanju VLOOKUP predvideva, da je tabela razvrščena in izvede binarno iskanje. Če med binarnim iskanjem VLOOKUP najde natančno ujemanje, vrne vrednost iz te vrstice. Če pa VLOOKUP naleti na vrednost, večjo od iskalne vrednosti, bo vrnil vrednost iz prejšnje vrstice.

To je nevarna privzeta vrednost, ker mnogi ljudje nehote zapustijo VLOOKUP v privzetem načinu, kar lahko povzroči napačen rezultat ko tabela ni razvrščena.

Če se želite izogniti tej težavi, uporabite četrto argumentacijo FALSE ali nič, če želite natančno ujemanje.

7. VLOOKUP lahko prisilite v natančno ujemanje

Če želite prisiliti VLOOKUP, da poišče natančno ujemanje, nastavite argument 4 (range_lookup) na FALSE ali nič. Ti dve formuli sta enakovredni:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

V načinu natančnega ujemanja, ko VLOOKUP ne najde vrednosti, vrne #N/A. To je jasen znak, da vrednosti ni v tabeli.

8. VLOOKUP -u lahko poveš, da naredi približno ujemanje

Če želite uporabiti VLOOKUP v načinu približnega ujemanja, bodisi izpustite 4. argument (range_lookup) ali ga vnesite kot TRUE ali 1. Te 3 formule so enakovredne:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Priporočamo, da vedno nastavite argument range_lookup izrecno, čeprav VLOOKUP tega ne zahteva. Na ta način imate vedno vizualni opomnik na način ujemanja, ki ga pričakujete.

Video: Kako uporabiti VLOOKUP za približna ujemanja

9. Za približna ujemanja je treba podatke razvrstiti

Če uporabljate približni način ujemanja, bodo vaši podatki je treba razvrstiti v naraščajočem vrstnem redu glede na iskalno vrednost. V nasprotnem primeru lahko dobite napačni rezultati . Upoštevajte tudi, da lahko včasih pridejo do besedilnih podatkov poglej razvrščeno, čeprav ni.

Felienne Hermans tukaj ima odličen primer te težave , iz kul analize, ki jo je naredila o dejanskem Enron preglednice!

10. VLOOKUP lahko združi podatke v različne tabele

Pogost primer uporabe VLOOKUP je združevanje podatkov iz dveh ali več tabel. Na primer, morda imate v eni tabeli podatke o naročilu, v drugi pa podatke o strankah in želite v tabelo naročil vnesti nekaj podatkov o strankah za analizo:

VLOOKUP združi podatke tako, da se pridruži tabelam -prej

Ker ID stranke obstaja v obeh tabelah, lahko s to vrednostjo povlečete želene podatke z VLOOKUP. Samo konfigurirajte VLOOKUP za uporabo vrednosti id v tabeli ena in podatkov v tabeli 2 z zahtevanim indeksom stolpcev. V spodnjem primeru uporabljamo dve formuli VLOOKUP. Eden vleče ime stranke, drugi pa stanje stranke.

VLOOKUP združi podatke tako, da se pridruži tabelam -naprej

Povezava: Primer združitve z VLOOKUP .

Video: Kako uporabiti VLOOKUP za združevanje tabel .

11. VLOOKUP lahko razvrsti ali razvrsti podatke

Če boste kdaj morali uporabiti podatkovne zapise poljubnih kategorij, lahko to preprosto storite z VLOOKUP -om z uporabo tabele, ki deluje kot „ključ“ za dodelitev kategorij.

Klasičen primer so ocene, pri katerih morate oceno dodeliti na podlagi ocene:

VLOOKUP se uporablja za kategorizacijo - dodeljevanje ocen

V tem primeru je VLOOKUP konfiguriran za približno ujemanje, zato je pomembno, da je tabela razvrščena v naraščajočem vrstnem redu.

VLOOKUP pa lahko uporabite tudi za dodelitev poljubnih kategorij. V spodnjem primeru uporabljamo VLOOKUP za izračun skupine za vsak oddelek z uporabo majhne tabele (imenovane 'ključ'), ki definira združevanje.

VLOOKUP se uporablja za kategorizacijo - dodelitev poljubnih skupin

12. Absolutne reference naredijo VLOOKUP bolj prenosljiv

V primerih, ko nameravate pridobiti informacije iz več stolpcev v tabeli ali če morate kopirati in prilepiti VLOOKUP, lahko prihranite čas in poslabšanje z uporabo absolutnih referenc za iskalno vrednost in matriko tabel. To vam omogoča, da kopirate formulo in nato spremenite samo številko indeksa stolpca, da uporabite isto iskanje, da dobite vrednost iz drugega stolpca.

Ker sta na primer iskalna vrednost in matrika tabel absolutna, lahko formulo prekopiramo po stolpcih, nato se vrnemo in po potrebi spremenimo indeks stolpca.

Absolutne reference naredijo formule VLOOKUP bolj prenosljive

13. Imenovani obsegi olajšajo branje VLOOKUP (in bolj prenosljiv)

Absolutni obsegi so precej grdega videza, zato lahko vaše formule VLOOKUP postanejo veliko čistejše in lažje berljive, tako da absolutne reference zamenjate z imenovanimi obsegi, ki so samodejno absolutni.

Na primer, v zgornjem primeru podatkov o zaposlenih lahko vnosno celico poimenujete 'id' in nato poimenujete podatke v tabeli 'data', svojo formulo lahko napišete na naslednji način:

Imenovani obsegi olajšajo branje formul VLOOKUP

Ta formula ni le lažje berljiva, ampak je tudi bolj prenosna, saj so imenovani obsegi samodejno absolutni.

14. Vstavljanje stolpca lahko prekine obstoječe formule VLOOKUP

Če imate na delovnem listu obstoječe formule VLOOKUP, se lahko formule zlomijo, če v tabelo vstavite stolpec. To je zato, ker se trdo kodirane vrednosti indeksa stolpcev ne spremenijo samodejno, ko vstavite ali izbrišete stolpce.

V tem primeru so bila iskanja za uvrstitev in prodajo prekinjena, ko je bil med letom in uvrstitvijo vstavljen nov stolpec. Leto še naprej deluje, ker je na levi strani vstavljenega stolpca:

Vstavljanje stolpca v tabelo lahko prekine VLOOKUP

Da bi se izognili tej težavi, lahko izračunate indeks stolpca, kot je opisano v naslednjih dveh nasvetih.

15. Za izračun indeksa stolpca lahko uporabite ROW ali COLUMN

Če ste tip, ki ga po kopiranju formule moti kakršno koli urejanje, lahko za ustvarjanje dinamičnih indeksov stolpcev uporabite ROW ali COLUMN. Če podatke pridobivate iz zaporednih stolpcev, vam ta trik omogoča, da nastavite eno formulo VLOOKUP in jo nato prekopirate brez sprememb.

S spodnjimi podatki o zaposlenih lahko na primer s funkcijo COLUMN ustvarimo indeks dinamičnega stolpca. Za prvo formulo v celici C3 bo stolpec sam vrnil 3 (ker je stolpec C tretji na delovnem listu), zato moramo eno odšteti in kopirati formulo:

Primer uporabe stolpca COLUMN za izračun indeksa stolpca za VLOOKUP

Vse formule so enake, naknadna obdelava ni potrebna.

Formula, ki jo uporabljamo, je naslednja:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Uporabite VLOOKUP + MATCH za popolnoma dinamičen indeks stolpca

Če naredite zgornji nasvet še korak dlje, lahko z MATCH poiščete položaj stolpca v tabeli in vrnete popolnoma dinamičen indeks stolpca.

To se včasih imenuje dvosmerno iskanje, saj iščete tako vrstico kot stolpec.

Primer bi bil iskanje prodaje za prodajalca v določenem mesecu ali cena za določen izdelek pri določenem dobavitelju.

Recimo, da imate mesečno prodajo, razčlenjeno po prodajalcu:

VLOOKUP dvosmerno iskanje - kako poiskati mesec?

VLOOKUP lahko zlahka najde prodajalca, vendar ne more samodejno upravljati imena meseca. Trik je v tem, da uporabite funkcijo MATCH namesto statičnega indeksa stolpca.

VLOOKUP dvosmerno iskanje z uporabo MATCH, da dobite indeks stolpca

Upoštevajte, da podajamo obseg ujemanja, ki vključuje vse stolpce v tabeli za 'sinhronizacijo' številk stolpcev, ki jih uporablja VLOOKUP.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Opomba: pogosto boste videli dvosmerne iskanja z INDEX in MATCH, pristopom, ki ponuja večjo prilagodljivost in boljše delovanje pri nabiranju velikih podatkov. Oglejte si, kako v tem hitrem videu: Kako narediti dvosmerno iskanje z INDEX in MATCH .

17. VLOOKUP dovoljuje nadomestne znake za delno ujemanje

Kadar koli uporabljate VLOOKUP v načinu natančnega ujemanja, imate možnost, da v iskalni vrednosti uporabite nadomestne znake. Morda se zdi nerazumljivo, vendar vam nadomestni znaki omogočajo natančno ujemanje na podlagi delnega ujemanja :)

Excel ponuja dva nadomestna znaka: zvezdica (*) se ujema z enim ali več znaki, vprašaj (?) Pa z enim znakom.

Zvezdico lahko na primer vnesete neposredno v celico in jo z VLOOKUP označite kot iskalno vrednost. Na spodnjem zaslonu smo vnesli 'Mon*' v H3, ki je imenovano območje, imenovano 'val'. Zaradi tega se VLOOKUP ujema z imenom 'Monet'.

VLOOKUP z nadomestnimi znaki - neposredno z zvezdico

Formula v tem primeru je preprosta:

 
= VLOOKUP (val,data,1,0)

Če želite, lahko formulo VLOOKUP prilagodite uporabi vgrajenega nadomestnega znaka, kot je spodnji primer, kjer vrednost v H3 preprosto združimo z zvezdico.

VLOOKUP z nadomestnimi znaki - zvezdica je združena v iskalno vrednost

V tem primeru zvezdo povežemo z iskalno vrednostjo znotraj funkcije VLOOKUP:

 
= VLOOKUP (val&'*',data,1,0)

Opomba: Bodite previdni pri nadomestnih znakih in VLOOKUP -u. Omogočajo vam enostaven način ustvarjanja 'lenega ujemanja', hkrati pa olajšajo iskanje napačnega ujemanja.

18. Lahko ujamete #N/A napake in prikažete prijazno sporočilo

V načinu natančnega ujemanja bo VLOOKUP prikazal napako #N/A, če ni najdenega ujemanja. Na nek način je to koristno, ker vam dokončno pove, da v iskalni tabeli ni ujemanja. Napake #N/A niso zelo zabavne, zato lahko to napako ujamete in prikažete nekaj drugega.

Ko začnete uporabljati VLOOKUP, boste zagotovo naleteli na napako #N/A, ki se pojavi, ko VLOOKUP ne more najti ujemanja.

kako najti stdev v

To je koristna napaka, ker vam VLOOKUP jasno pove, da ne more najti iskalne vrednosti. V tem primeru 'Latte' ne obstaja kot pijača v tabeli, zato VLOOKUP vrne napako #N/A

VLOOKUP ima napako #N/A

Formula v tem primeru je popolnoma standardno natančno ujemanje:

 
= VLOOKUP (E6,data,2,0)

Vendar pa #N/A napake niso zelo zabavne za ogled, zato boste morda želeli ujeti to napako in prikazati prijaznejše sporočilo.

Napake z VLOOKUP -om najlažje prestrežete tako, da VLOOKUP zavijete v funkcijo IFERROR. IFERROR vam omogoča, da 'ulovite' vsako napako in vrnete rezultat po vaši izbiri.

Če želite ujeti to napako in namesto napake prikazati sporočilo 'ni najdeno', lahko preprosto vnesete originalno formulo v IFERROR in nastavite želeni rezultat:

VLOOKUP #N/A napaka, ujeta z IFERROR

Če je iskalna vrednost najdena, ne pride do napake in funkcija VLOOKUP vrne normalen rezultat. Tukaj je formula:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Številke kot besedilo lahko povzročijo napako pri ujemanju

Včasih lahko tabela, s katero delate v VLOOKUP, vsebuje številke, vnesene kot besedilo. Če preprosto pridobivate številke kot besedilo iz stolpca v tabeli, ni pomembno. Če pa prvi stolpec tabele vsebuje številke, vnesene kot besedilo, boste dobili napako #N/A, če iskalna vrednost ni tudi besedilo.

V naslednjem primeru so ID -ji za tabelo planetov številke vneseno kot besedilo , zaradi česar VLOOKUP vrne napako, saj je iskalna vrednost številko 3:

Številke, vnesene kot besedilni primer napake VLOOKUP

Če želite rešiti to težavo, se prepričajte, da sta iskalna vrednost in prvi stolpec tabele enaka podatkovna vrsta (obe številki ali oboje besedilo).

Eden od načinov za to je pretvorba vrednosti v iskalnem stolpcu v številke. Enostaven način za to je, da z uporabo posebne paste dodate nič.

Če nimate preprostega nadzora nad izvorno tabelo, lahko prilagodite tudi formulo VLOOKUP za pretvorbo iskalne vrednosti v besedilo tako, da združite »« na vrednost tako:

 
= VLOOKUP (id&'',planets,2,0)

Številke, vnesene kot besedilna rešitev napake VLOOKUP

Če niste prepričani, kdaj boste imeli številke in kdaj boste imeli besedilo, se lahko odločite za obe možnosti, tako da VLOOKUP zavijete v IFERROR in napišete formulo za obravnavo obeh primerov:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. Za zamenjavo ugnezdenih stavkov IF lahko uporabite VLOOKUP

Ena bolj zanimivih uporab VLOOKUP je zamenjava ugnezdenih stavkov IF. Če ste že kdaj zgradili vrsto ugnezdenih IF -jev, veste, da delujejo dobro, vendar zahtevajo malo prereza oklepajev. Prav tako morate biti previdni pri vrstnem redu, v katerem delate, da ne bi prišlo do logične napake.

Na primer, običajna uporaba gnezdenih IF je dodeljevanje ocen na podlagi neke vrste ocene. V spodnjem primeru si lahko ogledate formulo, ki je bila zgrajena z ugnezdenimi IF -ji za to, z uporabo ključa ocene na desni kot vodila.

Dodeljevanje ocen z dolgo ugnezdeno formulo IF

Popolnoma ugnezdena formula IF izgleda tako:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

To deluje v redu, vendar upoštevajte, da sta logika in dejanski rezultati zapisani neposredno v formulo. Če se točkovanje iz kakršnega koli razloga spremeni, boste morali eno formulo previdno posodobiti in jo kopirati po celotni tabeli.

Nasprotno pa lahko VLOOKUP dodeli iste ocene s preprosto formulo. Vse, kar morate storiti, je, da se prepričate, da je tabela ključev ocen nastavljena za VLOOKUP (tj. Da je najbolj razvrščena po ocenah in vsebuje oklepaje za obdelavo vseh točk).

Po določitvi imenovanega obsega 'ključ' za tabelo ključev razredov je formula VLOOKUP zelo preprosta in ustvari enake ocene kot prvotna ugnezdena formula IF:

Dodeljevanje ocen s preprosto formulo VLOOKUP

S tabelo ključev razreda z imenom 'ključ' imamo zelo preprosto formulo VLOOKUP:

 
= VLOOKUP (C5,key,2,TRUE)

Prijeten bonus tega pristopa je, da sta logika in rezultati vgrajeni neposredno v tabelo ključev ocen. Če se kaj spremeni, lahko preprosto posodobite tabelo neposredno in formule VLOOKUP se bodo samodejno posodobile - urejanje ni potrebno.

Video: Kako zamenjati ugnezdene IF -je z VLOOKUP

21. VLOOKUP lahko obravnava samo eno merilo

VLOOKUP lahko po zasnovi najde le vrednosti, ki temeljijo na enem samem kriteriju, ki je na voljo kot iskalna vrednost, ki jo je treba najti v prvem stolpcu tabele (stolpec za iskanje).

To pomeni, da ne morete zlahka narediti stvari, kot je iskanje zaposlenega s priimkom 'Smith' v 'Računovodstvo', ali iskanje zaposlenega na podlagi imena in priimka v ločenih stolpcih.

Vendar obstajajo načini za premagovanje te omejitve. Ena od rešitev je ustvariti pomožni stolpec, ki združuje vrednosti iz različnih stolpcev, da ustvarite iskalne vrednosti, ki se obnašajo kot več pogojev. Na primer, tukaj želimo najti oddelek in skupino za zaposlenega, vendar se ime in priimek pojavita v ločenih stolpcih. Kako lahko poiščemo oboje hkrati?

VLOOKUP težava z več kriteriji - kako poiskati ime in priimek?

Najprej dodajte pomožni stolpec, ki preprosto poveže ime in priimek skupaj:

2. korak VLOOKUP več kriterijev - dodajte stolpec pomočnik, ki se pridruži več merilom

Nato konfigurirajte VLOOKUP za uporabo tabele, ki vključuje ta nov stolpec, in se pridružite imenu in priimku za iskalno vrednost:

3. korak VLOOKUP več meril - pridružite se merilom, da oblikujete iskalno vrednost

Končna formula VLOOKUP poišče imena in priimke skupaj s pomožnim stolpcem kot ključem:

 
= VLOOKUP (C3&D3,data,4,0)

22. Dva VLOOKUPA sta hitrejša od enega VLOOKUPA

Morda se zdi popolnoma noro, toda ko imate velik nabor podatkov in morate natančno ujemati, lahko VLOOKUP veliko pospešite tako, da formuli dodate še en VLOOKUP!

Ozadje: predstavljajte si, da imate veliko podatkov o naročilu, recimo več kot 10.000 zapisov, in uporabljate VLOOKUP za iskanje celotnega naročila na podlagi ID -ja naročila. Torej uporabljate nekaj takega:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

Napaka na koncu prisili VLOOKUP, da se natančno ujema. Želite natančno ujemanje, ker obstaja možnost, da številka naročila ne bo najdena. V tem primeru bo nastavitev natančnega ujemanja povzročila, da VLOOKUP vrne napako #N/A.

Težava je v tem, da so natančna ujemanja res počasna, saj mora Excel nadaljevati linearno skozi vse vrednosti, dokler ne najde ujemanja ali ne.

Nasprotno pa so približna ujemanja bliskovito hitra, saj Excel zmore narediti tisto, kar se imenuje a binarno iskanje .

Težava pri binarnih iskanjih (t.j. VLOOKUP v načinu približnega ujemanja) je, da lahko VLOOKUP vrne napačen rezultat, ko vrednosti ne najdemo. Še huje, rezultat je lahko videti povsem normalen, zato ga je zelo težko opaziti.

Rešitev je v tem, da dvakrat uporabite VLOOKUP, obakrat v načinu približnega ujemanja. Prvi primerek preprosto preveri, ali vrednost res obstaja. Če je tako, se zažene želeni podatek (spet v načinu približnega ujemanja) z drugim VLOOKUP -om. V nasprotnem primeru lahko vrnete poljubno vrednost, ki označuje, da rezultat ni bil najden.

Končna formula izgleda takole:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Tega pristopa sem se naučil od Charlesa Williamsa iz FastExcela, ki ima tukaj fantastičen, podroben članek: Zakaj sta 2 VLOOKUPA boljša od 1 VLOOKUPA .

Opomba: Za uporabo tega trika je treba vaše podatke razvrstiti. To je preprosto način zaščite pred manjkajočo vrednostjo iskanja, hkrati pa ohranite hitro iskanje.

23. INDEX in MATCH skupaj zmoreta vse, kar zmore VLOOKUP, in še več

Če sledite Excelu na spletu, boste verjetno naleteli na VLOOKUP vs INDEX/MATCH razprava. Prepir se lahko presenetljivo razgreje :)

Bistvo je naslednje: INDEX + MATCH lahko naredi vse, kar zmore VLOOKUP (in HLOOKUP), z veliko večjo prilagodljivostjo, za ceno nekoliko bolj zapletene. Zato bodo tisti, ki se zavzemajo za INDEX + MATCH, trdili (zelo razumno), da bi se lahko začeli učiti tudi INDEX in MATCH, saj vam na koncu daje boljši nabor orodij.

Argument proti INDEX + MATCH je, da zahteva dve funkciji namesto ene, zato je za uporabnike (zlasti nove uporabnike) bolj zapleteno učenje in obvladovanje.

Moja dva centa sta, da se boste, če pogosto uporabljate Excel, želeli naučiti uporabljati INDEX in MATCH. To je zelo močna kombinacija.

Mislim pa tudi, da bi se morali naučiti VLOOKUP -a, na katerega boste naleteli povsod, pogosto na delovnih listih, ki jih podedujete od drugih. V enostavnih situacijah bo VLOOKUP delo opravil brez težav.

Če želite izvedeti več o INDEX in MATCH, glej ta članek .

Avtor Dave Bruns


^