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

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

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

Prilikom rada u Excelu vrlo često postoji potreba pronaći podatke u jednoj tablici i izdvojiti ih u drugu. Ako još uvijek ne znate kako to učiniti, nakon čitanja članka ne samo da ćete naučiti kako to učiniti, već ćete i saznati pod kojim uvjetima možete iscijediti maksimalnu učinkovitost iz sustava. Razmatra se većina vrlo učinkovitih tehnika koje bi se trebale koristiti zajedno s funkcijom VLOOKUP.

Čak i ako već godinama koristite funkciju VLOOKUP, onda će vam ovaj članak s velikom vjerojatnošću biti koristan i neće vas ostaviti ravnodušnim. Na primjer, kao informatičar, a potom i voditelj IT-a, koristim VLOOKUP 15 godina, ali sam se uspio nositi sa svim nijansama tek sada, kada sam počeo profesionalno podučavati ljude Excelu.

VLOOKUP je skraćenica za vokomito NSinspekcija. Isto tako, VLOOKUP - Vertical LOOKUP. Sam naziv funkcije nam daje naslutiti da pretražuje u redovima tablice (vertikalno - ponavljanje redaka i fiksiranje stupca), a ne u stupcima (horizontalno - ponavljanje po stupcima i fiksiranje reda). Valja 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 tablice imaju manje stupaca i mnogo više redaka. Zato više puta češće trebamo pretraživati po recima nego po stupcima. Ako prečesto koristite funkciju HLO u Excelu, onda je vjerojatno 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 raspon ćelija (dvodimenzionalni niz), u čijem će se PRVOM (!) stupcu tražiti vrijednost parametra;

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

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

Kladim se da bi se mnogi od onih koji VLOOKUP funkciju poznaju kao nepostojanu, nakon čitanja opisa četvrtog parametra mogli osjećati neugodno, jer su je navikli vidjeti u malo drugačijem obliku: obično govore o točnom podudaranju kada pretraživanje (FALSE ili 0) ili skeniranje raspona (TRUE ili 1).

Sada se trebate napregnuti i nekoliko puta pročitati sljedeći odlomak dok ne osjetite smisao izrečenog do kraja. Tu je svaka riječ važna. Primjeri će vam pomoći da to shvatite.

Kako točno funkcionira formula VLOOKUP?

• Formula tipa I. Ako je posljednji parametar izostavljen ili naveden jednak 1, tada VLOOKUP pretpostavlja da je prvi stupac sortiran uzlaznim redoslijedom, tako da se pretraživanje zaustavlja na retku koji neposredno ispred retka 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 točno podudaranje s parametrom, u suprotnom kod pogreške # N / A (# N / A) se vraća.

  

Tijek 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 prvi stupac sadrži duplicirane vrijednosti i ispravno je sortiran, tada će se vratiti zadnji redak s dupliciranim vrijednostima.
3. Ako tražite vrijednost koja je očito veća od one koju može sadržavati prvi stupac, tada možete lako pronaći zadnji redak tablice, što može biti vrlo vrijedno.
4. Ovaj prikaz će vratiti pogreš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 prvom stupcu niza, formula će odabrati prvi redak za naknadno dohvaćanje 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 označavaju, naravno, nulu, budući da je to prilično praktično: ne morate se brinuti oko sortiranja prvog stupca niza, odmah možete vidjeti je li vrijednost pronađena ili ne. Ali ako imate nekoliko tisuća VLOOKUP formula na svom listu, primijetit ćete da je VLOOKUP II spor. U isto vrijeme, obično svi počinju razmišljati:

• Trebam snažnije računalo;
• 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 je prvi stupac na bilo koji način sortiran. Pišem riječima - PEDESET SEDAM PUTA! Ne 57%, nego 5700%. Ovu sam činjenicu prilično pouzdano provjerio.

Tajna tako brzog rada leži u činjenici da se na sortirani niz može primijeniti iznimno učinkovit 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čiti: prvo, pretražuje samo u prvom stupcu navedenog niza, a drugo, samo desno od ovog stupca. I kao što razumijete, može se dogoditi da stupac koji sadrži potrebne informacije bude lijevo od stupca u kojem ćemo tražiti. Već spomenuta kombinacija formula INDEX + MATCH je lišena ovog nedostatka, što je čini najfleksibilnijim rješenjem za izdvajanje podataka iz tablica u odnosu na VLOOKUP (VLOOKUP).

Neki aspekti primjene formule u stvarnom životu

Pretraživanje raspona

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

Potražite tekstualne nizove

Naravno, VLOOKUP ne traži samo brojeve, već i tekst. Treba imati na umu da formula ne razlikuje velika i mala slova znakova. Ako koristite zamjenske znakove, možete organizirati nejasno pretraživanje. 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 ih se iz tablice pretraživanja još uvijek može izbrisati, tada prvi parametar formule VLOOKUP nije uvijek na vama. Stoga, ako postoji rizik od začepljenja stanica s dodatnim razmacima, tada možete upotrijebiti funkciju TRIM za brisanje.

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 prvi stupac niza sadrži brojeve u ispravnom formatu, pretraživanje 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 tablica 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 s jednim D7 * 1.
• Zbrajanje nula D7 + 0.
• Povećanje na prvi stepen D7 ^ 1.

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

Kako potisnuti # N / A

To je vrlo zgodno učiniti s funkcijom IFERROR.

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

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

Niz

Često zaborave učiniti referencu niza apsolutnom, a pri rastezanju niz "pluta". Ne zaboravite koristiti $ A $ 2: $ C $ 5 umjesto A2: C5.

Dobro je postaviti referentni niz na poseban list radne knjige. Ne pada pod noge, a bit će sigurnije.

Još bolja ideja bila bi deklarirati ovaj niz kao imenovani raspon.

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

Pa, na rubu genija - urediti niz u obliku.

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

Ako tablica u koju dohvaćate podatke pomoću VLOOKUP-a ima istu strukturu kao i tablica pretraživanja, ali jednostavno sadrži manje redaka, tada možete koristiti funkciju COLUMN () u VLOOKUP-u za automatski izračunavanje brojeva stupaca koji se trebaju dohvatiti. U tom slučaju, sve formule VLOOKUP-a bit će iste (prilagođene za prvi parametar, koji se automatski mijenja)! Imajte na umu da prvi parametar ima apsolutnu koordinate stupca.

Izrada složenog ključa sa & "|" &

Ako je potrebno tražiti po nekoliko stupaca istovremeno, tada je potrebno napraviti složeni ključ za pretragu. Da povratna vrijednost nije tekstualna (kao što je slučaj s poljem "Šifra"), već numerička, tada bi za to bila prikladnija 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.:)