Sve tajne Excel VLOOKUP funkcije za pronalaženje podataka u tabeli i izdvajanje u drugu

2024 Autor: Malcolm Clapton | [email protected]. Zadnja izmjena: 2023-12-17 03:49

Nakon čitanja članka, ne samo da ćete naučiti kako pronaći podatke u Excel proračunskoj tablici i izdvojiti ih u drugu, već ćete naučiti i tehnike koje se mogu koristiti u kombinaciji s funkcijom VLOOKUP.

Kada radite u Excel-u, vrlo često postoji potreba da se u jednoj tabeli pronađu podaci i izdvoje u drugu. Ako još uvijek ne znate kako to učiniti, onda nakon čitanja članka nećete samo naučiti kako to učiniti, već ćete saznati i pod kojim uvjetima možete iscijediti maksimalne performanse iz sistema. Razmatra se većina vrlo efikasnih tehnika koje treba koristiti zajedno sa funkcijom VLOOKUP.

Čak i ako već godinama koristite funkciju VLOOKUP, onda će vam ovaj članak s velikom vjerovatnoćom biti od koristi i neće vas ostaviti ravnodušnim. Na primjer, kao IT specijalista, a potom i šef IT-a, koristim VLOOKUP već 15 godina, ali sam se uspio izboriti sa svim nijansama tek sada, kada sam počeo profesionalno podučavati ljude Excel-u.

VLOOKUP je skraćenica za vvertikalno NSinspekcija. Isto tako, VLOOKUP - Vertikalni LOOKUP. Sam naziv funkcije nam nagovještava da pretražuje u redovima tabele (vertikalno - iteracija po redovima i fiksiranje kolone), a ne u kolonama (horizontalno - iteracija po kolonama i fiksiranje reda). Treba napomenuti da VLOOKUP ima sestru - ružno pače koje nikada neće postati labud - to je funkcija HLOOKUP. HLOOKUP, za razliku od VLOOKUP-a, obavlja horizontalna pretraživanja, ali koncept Excela (i zapravo koncept organizacije podataka) podrazumijeva da vaše tabele imaju manje kolona i mnogo više redova. Zbog toga moramo mnogo puta češće pretraživati po redovima nego po kolonama. Ako prečesto koristite funkciju HLO u Excelu, onda je vjerovatno da nešto niste razumjeli u ovom životu.

Sintaksa

Funkcija VLOOKUP ima četiri parametra:

= VLOOKUP (;; [;]), ovdje:

- željenu vrijednost (rijetko) ili referencu na ćeliju koja sadrži željenu vrijednost (u velikoj većini slučajeva);

- referenca na opseg ćelija (dvodimenzionalni niz), u čijoj PRVOJ (!) koloni će se tražiti vrijednost parametra;

- broj kolone u rasponu iz kojeg će biti vraćena vrijednost;

- ovo je vrlo važan parametar koji odgovara na pitanje da li je prva kolona raspona sortirana uzlaznim redoslijedom. Ako je niz sortiran, navodimo vrijednost TRUE ili 1, u suprotnom - FALSE ili 0. Ako je ovaj parametar izostavljen, on je zadana vrijednost 1.

Kladim se da će se mnogi od onih koji VLOOKUP funkciju poznaju kao nestabilnu, nakon čitanja opisa četvrtog parametra, možda osjećati nelagodno, jer su navikli da je vide u malo drugačijem obliku: obično govore o potpunom podudaranju kada pretraživanje (FALSE ili 0) ili skeniranje raspona (TRUE ili 1).

Sada treba da se napregnete i pročitate sledeći pasus nekoliko puta dok ne osetite smisao onoga što je rečeno do kraja. Svaka reč je tu važna. Primjeri će vam pomoći da to shvatite.

Kako tačno funkcioniše formula VLOOKUP?

• Tip formule I. Ako je posljednji parametar izostavljen ili naveden jednak 1, tada VLOOKUP pretpostavlja da je prva kolona sortirana uzlaznim redoslijedom, tako da se pretraga zaustavlja na redu koji neposredno ispred reda koji sadrži vrijednost veću od željene … Ako takav niz nije pronađen, vraća se zadnji red raspona.

  

• Formula II. Ako je zadnji parametar naveden kao 0, tada VLOOKUP skenira prvi stupac niza uzastopno i odmah zaustavlja pretragu kada se pronađe prvo tačno podudaranje s parametrom, u suprotnom # N / A (# N / A) kod greške se vraća.

  

Tokovi rada formula

VPR tip I

VPR tip II

Posljedice za formule oblika I

1. Formule se mogu koristiti za distribuciju vrijednosti u rasponima.
2. Ako prva kolona sadrži duple vrijednosti i pravilno je sortirana, tada će biti vraćen zadnji redak s dupliranim vrijednostima.
3. Ako tražite vrijednost koja je očito veća od one koju može sadržavati prva kolona, onda možete lako pronaći posljednji red tabele, što može biti prilično vrijedno.
4. Ovaj prikaz će vratiti grešku # N / A samo ako ne pronađe vrijednost manju ili jednaku željenoj.
5. Prilično je teško razumjeti da formula vraća pogrešne vrijednosti ako vaš niz nije sortiran.

Posljedice za formule tipa II

Ako se željena vrijednost pojavljuje više puta u prvoj koloni niza, tada će formula odabrati prvi red za naknadno preuzimanje podataka.

VLOOKUP performanse

Došli ste do vrhunca članka. Čini se, pa, koja je razlika ako navedem nulu ili jedan kao posljednji parametar? U osnovi, svi pokazuju, naravno, nulu, jer je to prilično praktično: ne morate brinuti o sortiranju prve kolone niza, odmah možete vidjeti da li je vrijednost pronađena ili ne. Ali ako imate nekoliko hiljada VLOOKUP formula na svom listu, primijetit ćete da je VLOOKUP II spor. Istovremeno, obično svi počinju da razmišljaju:

• Treba mi moćniji računar;
• Treba mi brža formula, na primjer, mnogi znaju za INDEX + MATCH, koja je navodno brža za mizernih 5-10%.

I malo ljudi misli da će se brzina VLOOKUP-a povećati 57 puta čim počnete koristiti VLOOKUP tipa I i osigurate da se prva kolona sortira na bilo koji način. Pišem riječima - PEDESET SEDAM PUTA! Ne 57%, već 5.700%. Ovu činjenicu sam prilično pouzdano provjerio.

Tajna ovako brzog rada leži u činjenici da se na sortirani niz može primijeniti izuzetno efikasan algoritam pretraživanja, koji se naziva binarno pretraživanje (metoda prepolovljenja, metoda dihotomije). Dakle, VLOOKUP tipa I ga primjenjuje, a VLOOKUP tipa II pretražuje bez ikakve optimizacije. Isto vrijedi i za funkciju MATCH, koja uključuje sličan parametar, i funkciju LOOKUP, koja radi samo na sortiranim nizovima i uključena je u Excel radi kompatibilnosti s Lotusom 1-2-3.

Nedostaci formule

Nedostaci VLOOKUP-a su očigledni: prvo, on pretražuje samo u prvoj koloni navedenog niza, a drugo, samo desno od ove kolone. I kao što razumijete, može se dogoditi da kolona koja sadrži potrebne informacije bude lijevo od kolone u kojoj ćemo tražiti. Već pomenuta kombinacija formula INDEX + MATCH je lišena ovog nedostatka, što je čini najfleksibilnijim rešenjem za izdvajanje podataka iz tabela u poređenju sa VLOOKUP (VLOOKUP).

Neki aspekti primjene formule u stvarnom životu

Pretraga dometa

Klasična ilustracija pretraživanja raspona je zadatak određivanja popusta prema veličini narudžbe.

Potražite tekstualne nizove

Naravno, VLOOKUP traži ne samo brojeve, već i tekst. Treba imati na umu da formula ne pravi razliku između velikih i malih slova. Ako koristite zamjenske znakove, možete organizirati nejasnu pretragu. Postoje dva zamjenska znaka: "?" - zamjenjuje bilo koji znak u tekstualnom nizu, "*" - zamjenjuje bilo koji broj znakova.

Borbeni prostori

Često se postavlja pitanje kako riješiti problem viška prostora prilikom pretraživanja. Ako se tabela za pretraživanje i dalje može obrisati od njih, tada prvi parametar formule VLOOKUP nije uvijek na vama. Stoga, ako postoji rizik od začepljenja ćelija s dodatnim razmacima, tada možete koristiti funkciju TRIM da ga očistite.

Različiti format podataka

Ako se prvi parametar funkcije VLOOKUP odnosi na ćeliju koja sadrži broj, ali je pohranjena u ćeliji kao tekst, a prva kolona niza sadrži brojeve u ispravnom formatu, pretraga neće uspjeti. Moguća je i suprotna situacija. Problem se lako može riješiti prevođenjem parametra 1 u traženi format:

= VLOOKUP (−− D7; Proizvodi! $ A $ 2: $ C $ 5; 3; 0) - ako D7 sadrži tekst, a tabela sadrži brojeve;

= VLOOKUP (D7 & ""); Proizvodi $ A $ 2: $ C $ 5; 3; 0) - i obrnuto.

Usput, tekst možete prevesti u broj na nekoliko načina odjednom, odaberite:

• Dvostruka negacija -D7.
• Množenje sa jednim D7 * 1.
• Zbrajanje nula D7 + 0.
• Povećanje na prvi stepen D7 ^ 1.

Pretvaranje broja u tekst vrši se spajanjem sa praznim nizom, što prisiljava Excel da konvertuje tip podataka.

Kako potisnuti # N / A

Ovo je vrlo zgodno učiniti sa funkcijom IFERROR.

Na primjer: = IFERROR (VLOOKUP (D7; Proizvodi! $ A $ 2: $ C $ 5; 3; 0); "").

Ako VLOOKUP vrati kod greške # N / A, tada će ga IFERROR presresti i zamijeniti parametar 2 (u ovom slučaju prazan niz), a ako ne dođe do greške, onda će se ova funkcija pretvarati da uopće ne postoji, ali postoji samo VLOOKUP koji je vratio normalan rezultat.

Niz

Često zaborave da referencu niza učine apsolutnim, a pri rastezanju niz "pluta". Ne zaboravite da koristite $ A $ 2: $ C $ 5 umjesto A2: C5.

Dobra je ideja postaviti referentni niz na poseban list radne knjige. Ne pada pod noge i biće sigurnije.

Još bolja ideja bi bila da se ovaj niz deklarira kao imenovani raspon.

Mnogi korisnici, kada specificiraju niz, koriste konstrukciju kao što je A: C, navodeći cijele stupce. Ovaj pristup ima pravo na postojanje, jer ste spašeni od potrebe da pratite činjenicu da vaš niz uključuje sve potrebne nizove. Ako dodate redove na list s originalnim nizom, tada raspon naveden kao A: C ne treba prilagođavati. Naravno, ova sintaktička konstrukcija prisiljava Excel da obavi malo više posla od preciznog specificiranja raspona, ali ovaj dodatni troškovi se mogu zanemariti. Govorimo o stotinkama sekunde.

Pa, na ivici genija - urediti niz u formi.

Korištenje funkcije COLUMN za određivanje stupca za izdvajanje

Ako tabela u koju dohvaćate podatke pomoću VLOOKUP-a ima istu strukturu kao i tabela za traženje, ali jednostavno sadrži manje redaka, tada možete koristiti funkciju COLUMN () u VLOOKUP-u da automatski izračunate brojeve stupaca koje treba dohvatiti. U ovom slučaju, sve VLOOKUP formule će biti iste (prilagođene za prvi parametar, koji se automatski mijenja)! Imajte na umu da prvi parametar ima apsolutnu koordinate stupca.

Kreiranje kompozitnog ključa sa & "|" &

Ako je potrebno tražiti po nekoliko kolona istovremeno, potrebno je napraviti složeni ključ za pretragu. Da povratna vrijednost nije tekstualna (kao što je slučaj sa poljem "Šifra"), već numerička, tada bi za to bila pogodnija formula SUMIFS i kompozitni ključ stupca uopće ne bi bio potreban.

Ovo je moj prvi članak za Lifehackera. Ako vam se svidjelo, pozivam vas da posjetite, a također rado pročitajte u komentarima o vašim tajnama korištenja VLOOKUP funkcije i slično. Hvala.:)