Vse skrivnosti funkcije Excel VLOOKUP za iskanje podatkov v tabeli in njihovo ekstrahiranje v drugo

2024 Avtor: Malcolm Clapton | [email protected]. Nazadnje spremenjeno: 2023-12-17 04:10

Po branju članka se ne boste naučili le, kako najti podatke v Excelovi preglednici in jih ekstrahirati v drugo, ampak tudi tehnik, ki jih je mogoče uporabiti v povezavi s funkcijo VLOOKUP.

Pri delu v Excelu je zelo pogosto treba najti podatke v eni tabeli in jih ekstrahirati v drugo. Če še vedno ne veste, kako to storiti, potem po branju članka ne boste le izvedeli, kako to storiti, ampak tudi ugotovili, pod kakšnimi pogoji lahko iz sistema iztisnete največjo zmogljivost. Upošteva se večina zelo učinkovitih tehnik, ki jih je treba uporabiti v povezavi s funkcijo VLOOKUP.

Tudi če že leta uporabljate funkcijo VLOOKUP, vam bo ta članek z veliko verjetnostjo koristen in vas ne bo pustil ravnodušnega. Kot na primer IT specialist in nato vodja IT uporabljam VLOOKUP že 15 let, vendar sem se z vsemi niansami uspel spopasti šele zdaj, ko sem ljudi začel profesionalno učiti Excela.

VLOOKUP je okrajšava za vnavpično NSinšpekcijski pregled. Podobno, VLOOKUP - Vertical LOOKUP. Že samo ime funkcije nam namiguje, da išče po vrsticah tabele (navpično - iteracija po vrsticah in popravljanje stolpca), ne pa po stolpcih (vodoravno - iteracija po stolpcih in popravljanje vrstice). Treba je opozoriti, da ima VLOOKUP sestro - grdo račko, ki nikoli ne bo postala labod - to je funkcija HLOOKUP. HLOOKUP v nasprotju z VLOOKUP izvaja horizontalna iskanja, vendar koncept Excela (in pravzaprav koncept organizacije podatkov) pomeni, da imajo vaše tabele manj stolpcev in veliko več vrstic. Zato moramo večkrat pogosteje iskati po vrsticah kot po stolpcih. Če v Excelu prepogosto uporabljate funkcijo HLO, je verjetno, da v tem življenju nečesa niste razumeli.

Sintaksa

Funkcija VLOOKUP ima štiri parametre:

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

- želeno vrednost (redko) ali sklicevanje na celico, ki vsebuje želeno vrednost (velika večina primerov);

- sklicevanje na obseg celic (dvodimenzionalni niz), v PRVI (!) stolpcu katerih se bo iskala vrednost parametra;

- številka stolpca v obsegu, iz katerega bo vrnjena vrednost;

- to je zelo pomemben parameter, ki odgovarja na vprašanje, ali je prvi stolpec obsega razvrščen v naraščajočem vrstnem redu. Če je matrika razvrščena, podamo vrednost TRUE ali 1, sicer - FALSE ali 0. Če je ta parameter izpuščen, je privzeto 1.

Stavim, da se marsikdo od tistih, ki vedo, da je funkcija VLOOKUP nespremenjen, po branju opisa četrtega parametra morda počutijo neprijetno, saj so navajeni videti jo v nekoliko drugačni obliki: običajno govorijo o natančnem ujemanju, ko iskanje (FALSE ali 0) ali skeniranje obsega (TRUE ali 1).

Zdaj se morate napeti in večkrat prebrati naslednji odstavek, dokler ne začutite smisla povedanega do konca. Tam je pomembna vsaka beseda. Primeri vam bodo pomagali ugotoviti.

Kako natančno deluje formula VLOOKUP?

• Vrsta formule I. Če je zadnji parameter izpuščen ali podan enak 1, potem VLOOKUP predpostavlja, da je prvi stolpec razvrščen v naraščajočem vrstnem redu, zato se iskanje ustavi pri vrstici, ki neposredno pred vrstico, ki vsebuje vrednost, večjo od želene … Če takšnega niza ni mogoče najti, se vrne zadnja vrstica obsega.

  

• Formula II. Če je zadnji parameter podan kot 0, potem VLOOKUP zaporedoma pregleda prvi stolpec matrike in takoj ustavi iskanje, ko se najde prvo natančno ujemanje s parametrom, sicer pa koda napake # N / A (# N / A). se vrne.

  

Delovni potek formul

VPR tipa I

VPR tip II

Posledice za formule v obliki I

1. Formule se lahko uporabljajo za distribucijo vrednosti po obsegih.
2. Če prvi stolpec vsebuje podvojene vrednosti in je pravilno razvrščen, bo vrnjena zadnja vrstica s podvojenimi vrednostmi.
3. Če iščete vrednost, ki je očitno večja, kot jo lahko vsebuje prvi stolpec, potem zlahka najdete zadnjo vrstico tabele, ki je lahko zelo dragocena.
4. Ta pogled bo vrnil napako # N / A le, če ne najde vrednosti, ki je manjša ali enaka želeni.
5. Precej težko je razumeti, da formula vrne napačne vrednosti, če vaša matrika ni razvrščena.

Posledice za formule tipa II

Če se želena vrednost večkrat pojavi v prvem stolpcu matrike, bo formula izbrala prvo vrstico za nadaljnje pridobivanje podatkov.

Učinkovitost VLOOKUP

Dosegli ste vrhunec članka. Zdi se, no, kakšna je razlika, če navedem nič ali ena kot zadnji parameter? V bistvu vsi navedejo, seveda, nič, saj je to precej praktično: ni vam treba skrbeti za razvrščanje prvega stolpca matrike, takoj lahko vidite, ali je bila vrednost najdena ali ne. Če pa imate na svojem listu več tisoč formul VLOOKUP, boste opazili, da je VLOOKUP II počasen. Hkrati običajno vsi začnejo razmišljati:

• Potrebujem zmogljivejši računalnik;
• Potrebujem hitrejšo formulo, na primer marsikdo ve za INDEX + MATCH, ki je menda hitrejši za pičlih 5-10%.

In malo ljudi misli, da se bo hitrost VLOOKUP-a povečala za 57-krat, takoj ko začnete uporabljati VLOOKUP tipa I in zagotovite, da je prvi stolpec razvrščen na kakršen koli način. Pišem z besedami - SEDEDESET PETDESET! Ne 57%, ampak 5700%. To dejstvo sem precej zanesljivo preveril.

Skrivnost tako hitrega dela je v tem, da lahko na razvrščeno matriko uporabimo izjemno učinkovit iskalni algoritem, ki mu pravimo binarno iskanje (metoda razpolovitve, metoda dihotomije). Torej VLOOKUP tipa I ga uporabi, VLOOKUP tipa II pa išče brez kakršne koli optimizacije. Enako velja za funkcijo MATCH, ki vključuje podoben parameter, in funkcijo LOOKUP, ki deluje samo na razvrščenih nizih in je vključena v Excel zaradi združljivosti z Lotusom 1-2-3.

Slabosti formule

Pomanjkljivosti VLOOKUP-a so očitne: prvič, išče samo v prvem stolpcu določenega niza in drugič, samo desno od tega stolpca. In kot razumete, se lahko zgodi, da bo stolpec, ki vsebuje potrebne informacije, levo od stolpca, v katerem bomo iskali. Že omenjena kombinacija formul INDEX + MATCH je brez te pomanjkljivosti, zaradi česar je v primerjavi z VLOOKUP (VLOOKUP) najbolj prilagodljiva rešitev za ekstrakcijo podatkov iz tabel.

Nekateri vidiki uporabe formule v resničnem življenju

Iskanje po območju

Klasična ilustracija iskanja po obsegu je naloga določanja popusta po velikosti naročila.

Poiščite besedilne nize

Seveda VLOOKUP ne išče samo številk, ampak tudi besedila. Upoštevati je treba, da formula ne razlikuje med velikimi črkami znakov. Če uporabljate nadomestne znake, lahko organizirate mehko iskanje. Obstajata dva nadomestna znaka: "?" - nadomesti kateri koli znak v besedilnem nizu, "*" - nadomesti poljubno število znakov.

Bojni prostori

Pogosto se postavlja vprašanje, kako rešiti problem dodatnih prostorov pri iskanju. Če jih je še vedno mogoče počistiti iz iskalne tabele, potem prvi parameter formule VLOOKUP ni vedno odvisen od vas. Če torej obstaja nevarnost zamašitve celic z dodatnimi presledki, lahko uporabite funkcijo TRIM, da jo počistite.

Različne oblike podatkov

Če se prvi parameter funkcije VLOOKUP nanaša na celico, ki vsebuje številko, ki pa je v celici shranjena kot besedilo, prvi stolpec matrike pa vsebuje številke v pravilni obliki, potem iskanje ne bo uspelo. Možna je tudi nasprotna situacija. Težavo je mogoče enostavno rešiti s prevajanjem parametra 1 v zahtevano obliko:

= VLOOKUP (−− D7; Izdelki! $ A $ 2: $ C $ 5; 3; 0) - če D7 vsebuje besedilo in tabela vsebuje številke;

= VLOOKUP (D7 & ""); Izdelki! $ A $ 2: $ C $ 5; 3; 0) - in obratno.

Mimogrede, lahko besedilo prevedete v številko na več načinov hkrati, izberite:

• Dvojna negacija -D7.
• Množenje z enim D7 * 1.
• Brez seštevanja D7 + 0.
• Dviganje na prvo potenco D7 ^ 1.

Pretvorba števila v besedilo se izvede s povezovanjem s praznim nizom, kar prisili Excel, da pretvori vrsto podatkov.

Kako zatreti # N / A

To je zelo priročno narediti s funkcijo IFERROR.

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

Če VLOOKUP vrne kodo napake # N / A, jo bo IFERROR prestregel in nadomestil parameter 2 (v tem primeru prazen niz), in če ne pride do napake, se bo ta funkcija pretvarjala, da sploh ne obstaja, vendar obstaja samo VLOOKUP, ki je vrnil normalen rezultat.

Niz

Pogosto pozabijo narediti referenco matrike absolutno in pri raztezanju matrika "plava". Ne pozabite uporabiti $ A $ 2: $ C $ 5 namesto A2: C5.

Dobro je, da referenčno matriko postavite na ločen list delovnega zvezka. Ne gre pod noge in bo varneje.

Še boljša ideja bi bila, da to matriko razglasite kot poimenovani obseg.

Mnogi uporabniki pri določanju matrike uporabljajo konstrukcijo, kot je A: C, ki določa celotne stolpce. Ta pristop ima pravico do obstoja, saj vam ne bo treba slediti dejstvu, da vaša matrika vključuje vse zahtevane nize. Če dodate vrstice na list z izvirnim nizom, obsega, določenega kot A: C, ni treba prilagajati. Seveda ta sintaktična konstrukcija prisili Excel, da opravi malo več dela, kot da natančno določi obseg, vendar je te obremenitve mogoče zanemariti. Govorimo o stotinkah sekunde.

No, na meji genija - urediti matriko v obliki.

Uporaba funkcije COLUMN za določitev stolpca za ekstrakcijo

Če ima tabela, v katero pridobivate podatke z uporabo VLOOKUP, enako strukturo kot tabela za iskanje, vendar preprosto vsebuje manj vrstic, lahko uporabite funkcijo COLUMN () v VLOOKUP za samodejno izračunavanje števila stolpcev, ki jih je treba pridobiti. V tem primeru bodo vse formule VLOOKUP enake (prilagojene za prvi parameter, ki se samodejno spremeni)! Upoštevajte, da ima prvi parameter absolutno koordinato stolpca.

Ustvarjanje sestavljenega ključa s & "|" &

Če je potrebno iskati po več stolpcih hkrati, je treba za iskanje izdelati sestavljeni ključ. Če vrnjena vrednost ne bi bila besedilna (kot je v primeru polja "Koda"), ampak številčna, bi bila za to primerna bolj priročna formula SUMIFS in sestavljeni ključ stolpca sploh ne bi bil potreben.

To je moj prvi članek za Lifehackerja. Če vam je bilo všeč, vas vabim na obisk, v komentarjih pa z veseljem preberete tudi vaše skrivnosti uporabe funkcije VLOOKUP in podobno. Hvala.:)