Kaikki Excelin VLOOKUP-toiminnon salaisuudet tietojen etsimiseen taulukosta ja sen purkamiseen toiseen

2024 Kirjoittaja: Malcolm Clapton | [email protected]. Viimeksi muokattu: 2023-12-17 03:55

Artikkelin lukemisen jälkeen opit paitsi löytämään tietoja Excel-laskentataulukosta ja purkamaan ne toiseen, mutta myös tekniikoita, joita voidaan käyttää VLOOKUP-toiminnon kanssa.

Excelissä työskennellessä on hyvin usein tarve löytää tiedot yhdestä taulukosta ja purkaa se toiseen. Jos et vieläkään tiedä kuinka tehdä tämä, artikkelin lukemisen jälkeen et vain opi kuinka tehdä tämä, vaan myös selvittää, millä ehdoilla voit puristaa maksimaalisen suorituskyvyn järjestelmästä. Useimmat erittäin tehokkaista tekniikoista, joita tulisi käyttää VLOOKUP-toiminnon kanssa, tarkastellaan.

Vaikka olet käyttänyt VLOOKUP-toimintoa vuosia, tämä artikkeli on suurella todennäköisyydellä hyödyllinen sinulle eikä jätä sinua välinpitämättömäksi. Esimerkiksi IT-asiantuntijana ja sitten IT-päällikkönä olen käyttänyt VLOOKUPia 15 vuotta, mutta onnistuin käsittelemään kaikki vivahteet vasta nyt, kun aloin opettaa ihmisille Exceliä ammattimaisesti.

VHAKU on lyhenne sanasta vpystysuora NStarkastus. Samoin VLOOKUP - Pystyhaku. Jo funktion nimi vihjaa meille, että se etsii taulukon riveiltä (pystysuoraan - iteroi rivien yli ja kiinnittää sarakkeen), eikä sarakkeista (vaakasuuntaisesti - iteroi sarakkeiden yli ja korjaa riviä). On huomattava, että VLOOKUPilla on sisko - ruma ankanpoikanen, josta ei koskaan tule joutsenta - tämä on HLOOKUP-toiminto. HLOOKUP, toisin kuin VLOOKUP, suorittaa vaakasuuntaisia hakuja, mutta Excelin käsite (ja itse asiassa tietojen järjestämisen käsite) tarkoittaa, että taulukoissasi on vähemmän sarakkeita ja paljon enemmän rivejä. Tästä syystä meidän on etsittävä rivien perusteella monta kertaa useammin kuin sarakkeiden perusteella. Jos käytät HLO-funktiota liian usein Excelissä, et todennäköisesti ymmärtänyt jotain tässä elämässä.

Syntaksi

VLOOKUP-funktiolla on neljä parametria:

= VHAKU (;; [;]), tässä:

- haluttu arvo (harvoin) tai viittaus halutun arvon sisältävään soluun (suurin osa tapauksista);

- viittaus solualueeseen (kaksiulotteinen matriisi), jonka ENSIMMÄISEN (!) sarakkeen parametrin arvoa haetaan;

- sarakkeen numero alueella, josta arvo palautetaan;

- Tämä on erittäin tärkeä parametri, joka vastaa kysymykseen, onko alueen ensimmäinen sarake lajiteltu nousevaan järjestykseen. Jos taulukko on lajiteltu, määritämme arvon TOSI tai 1, muussa tapauksessa - EPÄTOSI tai 0. Jos tämä parametri jätetään pois, se on oletuksena 1.

Lyön vetoa, että monet niistä, jotka tietävät VLOOKUP-funktion hilseilevänä, saattavat tuntea olonsa epämukavaksi luettuaan neljännen parametrin kuvauksen, koska he ovat tottuneet näkemään sen hieman eri muodossa: yleensä he puhuvat tarkasta vastaavuudesta, kun haku (FALSE tai 0) tai etäisyysskannaus (TRUE tai 1).

Nyt sinun on rasittava ja luettava seuraava kappale useita kertoja, kunnes tunnet sanotun merkityksen loppuun asti. Siellä jokainen sana on tärkeä. Esimerkit auttavat sinua ymmärtämään sen.

Kuinka VLOOKUP-kaava tarkalleen toimii?

• Kaavan tyyppi I. Jos viimeinen parametri jätetään pois tai sen arvoksi määritetään 1, VLOOKUP olettaa, että ensimmäinen sarake on lajiteltu nousevaan järjestykseen, joten haku pysähtyy riville, välittömästi edeltää riviä, joka sisältää haluttua suuremman arvon … Jos tällaista merkkijonoa ei löydy, palautetaan alueen viimeinen rivi.

  

• Formula II. Jos viimeiseksi parametriksi määritetään 0, VLOOKUP skannaa taulukon ensimmäisen sarakkeen peräkkäin ja lopettaa haun heti, kun ensimmäinen tarkka vastaavuus parametrin kanssa löytyy, muuten # N / A (# N / A) -virhekoodi palautetaan.

  

Kaavat työnkulkuja

VPR tyyppi I

VPR tyyppi II

Seuraukset muotoa I oleville kaavoille

1. Kaavoja voidaan käyttää arvojen jakamiseen alueiden kesken.
2. Jos ensimmäinen sarake sisältää päällekkäisiä arvoja ja se on lajiteltu oikein, viimeinen rivistä, jolla on päällekkäiset arvot, palautetaan.
3. Jos etsit arvoa, joka on selvästi suurempi kuin ensimmäinen sarake voi sisältää, voit helposti löytää taulukon viimeisen rivin, joka voi olla varsin arvokas.
4. Tämä näkymä palauttaa # N / A -virheen vain, jos se ei löydä arvoa, joka on pienempi tai yhtä suuri kuin haluttu arvo.
5. On melko vaikea ymmärtää, että kaava palauttaa väärät arvot, jos taulukkoasi ei ole lajiteltu.

Seuraukset tyypin II kaavoille

Jos haluttu arvo esiintyy useita kertoja taulukon ensimmäisessä sarakkeessa, kaava valitsee ensimmäisen rivin myöhempää tietojen hakua varten.

VLOOKUP-suoritus

Olet saavuttanut artikkelin huippupisteen. Vaikuttaa siltä, että mitä eroa on, jos määritän viimeisenä parametrina nollan tai yhden? Periaatteessa kaikki ilmoittavat tietysti nollan, koska tämä on varsin käytännöllistä: sinun ei tarvitse huolehtia taulukon ensimmäisen sarakkeen lajittelusta, näet heti, löytyikö arvo vai ei. Mutta jos taulukossasi on useita tuhansia VLOOKUP-kaavoja, huomaat, että VLOOKUP II on hidas. Samaan aikaan yleensä kaikki alkavat ajatella:

• Tarvitsen tehokkaamman tietokoneen;
• Tarvitsen nopeamman kaavan, esimerkiksi monet ihmiset tietävät INDEX + MATCHista, joka on oletettavasti nopeampi 5-10%.

Ja harvat ajattelevat, että heti kun aloitat tyypin I VLOOKUP:in käytön ja varmistat, että ensimmäinen sarake on lajiteltu millään tavalla, VLOOKUPin nopeus kasvaa 57-kertaiseksi. Kirjoitan sanoilla - VIISIKYMMENEN SEITSEMÄN KERTAA! Ei 57%, vaan 5700%. Tarkistin tämän tosiasian melko luotettavasti.

Nopean työn salaisuus on siinä, että lajiteltuun taulukkoon voidaan soveltaa erittäin tehokasta hakualgoritmia, jota kutsutaan binäärihauksi (puolitusmenetelmä, dikotomiamenetelmä). Joten tyypin I VLOOKUP käyttää sitä ja tyypin II VLOOKUP etsii ilman optimointia. Sama koskee MATCH-funktiota, joka sisältää samanlaisen parametrin, ja HAKU-funktiota, joka toimii vain lajitetuissa taulukoissa ja joka sisältyy Exceliin yhteensopivuuden vuoksi Lotus 1-2-3:n kanssa.

Kaavan haitat

VLOOKUPin haitat ovat ilmeiset: ensinnäkin se etsii vain määritetyn taulukon ensimmäisestä sarakkeesta ja toiseksi vain tämän sarakkeen oikealta puolelta. Ja kuten ymmärrät, voi hyvinkin käydä niin, että tarvittavat tiedot sisältävä sarake on sen sarakkeen vasemmalla puolella, jota etsimme. Jo mainitusta kaavojen yhdistelmästä INDEX + MATCH puuttuu tämä epäkohta, mikä tekee siitä joustavimman ratkaisun tietojen poimimiseen taulukoista VLOOKUPiin (VLOOKUP) verrattuna.

Jotkut kaavan soveltamisen näkökohdat tosielämässä

Aluehaku

Klassinen esimerkki valikoimahausta on tehtävä alennuksen määrittämisestä tilauksen koon mukaan.

Hae tekstijonoja

Tietenkin VLOOKUP ei etsi vain numeroita, vaan myös tekstiä. On pidettävä mielessä, että kaava ei tee eroa merkkien tapausten välillä. Jos käytät yleismerkkejä, voit järjestää sumean haun. Jokerimerkkejä on kaksi: "?" - korvaa minkä tahansa merkin tekstijonossa, "*" - korvaa minkä tahansa määrän merkkejä.

Taistelutilat

Usein herää kysymys, kuinka ratkaista ylimääräisten tilojen ongelma haettaessa. Jos hakutaulukko voidaan silti tyhjentää niistä, VLOOKUP-kaavan ensimmäinen parametri ei aina ole sinun. Siksi, jos on olemassa vaara, että solut tukkeutuvat ylimääräisillä välilyönneillä, voit tyhjentää sen käyttämällä TRIM-toimintoa.

Erilainen datamuoto

Jos VLOOKUP-funktion ensimmäinen parametri viittaa soluun, joka sisältää numeron, mutta joka on tallennettu soluun tekstinä, ja taulukon ensimmäinen sarake sisältää numeroita oikeassa muodossa, haku epäonnistuu. Myös päinvastainen tilanne on mahdollinen. Ongelma voidaan ratkaista helposti kääntämällä parametri 1 vaadittuun muotoon:

= VHAKU (−− D7; Tuotteet! $ A $ 2: $ C $ 5; 3; 0) - jos D7 sisältää tekstiä ja taulukko sisältää numeroita;

= VHAKU (D7 & ""); Tuotteet! $ A $ 2: $ C $ 5; 3; 0) - ja päinvastoin.

Muuten, voit kääntää tekstin numeroksi useilla tavoilla kerralla, valitse:

• Kaksoisnegatio -D7.
• Kertominen yhdellä D7 * 1.
• Nolla lisäystä D7 + 0.
• Nosto ensimmäiseen potenssiin D7 ^ 1.

Numeron muuntaminen tekstiksi tapahtuu ketjuttamalla tyhjään merkkijonoon, mikä pakottaa Excelin muuttamaan tietotyypin.

Kuinka tukahduttaa # N / A

Tämä on erittäin kätevää tehdä IFERROR-toiminnolla.

Esimerkki: = IFERROR (VLOOKUP (D7; Tuotteet! $ A $ 2: $ C $ 5; 3; 0); "").

Jos VLOOKUP palauttaa virhekoodin # N / A, IFERROR sieppaa sen ja korvaa parametrin 2 (tässä tapauksessa tyhjän merkkijonon), ja jos virhettä ei tapahdu, tämä toiminto teeskentelee, ettei sitä ole ollenkaan, mutta on vain VLOOKUP, joka palautti normaalin tuloksen.

Array

Usein he unohtavat tehdä taulukon viittauksen absoluuttiseksi, ja venytettäessä taulukko "kelluu". Muista käyttää $ A $ 2: $ C $ 5 sijaan A2: C5.

Viitetaulukko on hyvä sijoittaa työkirjan erilliselle arkille. Se ei jää jalkojen alle, ja se on turvallisempaa.

Vielä parempi idea olisi ilmoittaa tämä taulukko nimetyksi alueeksi.

Monet käyttäjät käyttävät taulukkoa määrittäessään A: C:n kaltaista rakennetta, joka määrittää kokonaisia sarakkeita. Tällä lähestymistavalla on oikeus olemassaoloon, koska et joudu pitämään kirjaa siitä, että taulukkosi sisältää kaikki vaaditut merkkijonot. Jos lisäät arkkiin rivejä alkuperäisen taulukon kanssa, A: C:ksi määritettyä aluetta ei tarvitse säätää. Tietenkin tämä syntaktinen rakenne pakottaa Excelin tekemään hieman enemmän työtä kuin alueen tarkan määrittämisen, mutta tämä yleiskustannukset voidaan jättää huomiotta. Puhumme sekunnin sadasosista.

No, nerouden partaalla - järjestää joukko muodossa.

Poimittavan sarakkeen määrittäminen COLUMN-funktiolla

Jos taulukolla, johon haet tietoja VLOOKUP-toiminnolla, on sama rakenne kuin hakutaulukolla, mutta se sisältää vain vähemmän rivejä, voit käyttää VHAKU-sovelluksen COLUMN ()-toimintoa laskeaksesi automaattisesti haettavien sarakkeiden lukumäärät. Tässä tapauksessa kaikki VLOOKUP-kaavat ovat samat (säädetty ensimmäiselle parametrille, joka muuttuu automaattisesti)! Huomaa, että ensimmäisellä parametrilla on absoluuttinen sarakkeen koordinaatti.

Yhdistelmäavaimen luominen käyttämällä & "|" &

Jos tulee tarpeelliseksi hakea useasta sarakkeesta samanaikaisesti, on haulle tehtävä yhdistelmäavain. Jos palautusarvo ei olisi tekstimuotoinen (kuten "Koodi"-kentän tapauksessa), vaan numeerinen, niin kätevämpi SUMIFS-kaava sopisi tähän eikä yhdistelmäsarakeavainta tarvittaisi ollenkaan.

Tämä on ensimmäinen artikkelini Lifehackerille. Jos pidit siitä, kutsun sinut käymään ja myös mielelläni lukemaan kommenteissa VLOOKUP-toiminnon ja vastaavien käytön salaisuuksistasi. Kiitos.:)