Kõik Exceli VLOOKUP funktsiooni saladused tabelist andmete leidmiseks ja teiseks ekstraheerimiseks

2024 Autor: Malcolm Clapton | [email protected]. Viimati modifitseeritud: 2023-12-17 03:53

Pärast artikli lugemist saate teada mitte ainult Exceli arvutustabelist andmete leidmise ja teiseks ekstraktimise kohta, vaid ka tehnikaid, mida saab kasutada koos funktsiooniga VLOOKUP.

Excelis töötades tekib väga sageli vajadus ühest tabelist andmed leida ja teise tabelisse eraldada. Kui te ikka veel ei tea, kuidas seda teha, siis pärast artikli lugemist saate mitte ainult teada, kuidas seda teha, vaid saate ka teada, millistel tingimustel saate süsteemist maksimaalse jõudluse välja pigistada. Vaadeldakse enamikku väga tõhusatest tehnikatest, mida tuleks kasutada koos funktsiooniga VLOOKUP.

Isegi kui olete funktsiooni VLOOKUP kasutanud juba aastaid, on see artikkel suure tõenäosusega teile kasulik ega jäta teid ükskõikseks. Näiteks IT-spetsialistina ja seejärel IT-valdkonna juhina olen VLOOKUP-i kasutanud 15 aastat, kuid kõigi nüanssidega sain hakkama alles nüüd, kui hakkasin inimestele professionaalselt Excelit õpetama.

VLOOKUP on lühend sõnadest vvertikaalne NS ülevaatus. Samuti VLOOKUP – vertikaalne OTSIIN. Funktsiooni nimi vihjab meile, et see otsib tabeli ridadelt (vertikaalselt - kordades üle ridade ja fikseerides veeru), mitte veergudes (horisontaalselt - veergude kordamine ja rea fikseerimine). Tuleb märkida, et VLOOKUP-il on õde - inetu pardipoeg, kellest ei saa kunagi luiki - see on funktsioon HLOOKUP. HLOOKUP, erinevalt VLOOKUP-ist, teostab horisontaalseid otsinguid, kuid Exceli kontseptsioon (ja ka andmete korraldamise kontseptsioon) eeldab, et teie tabelites on vähem veerge ja palju rohkem ridu. Seetõttu peame ridade järgi otsima mitu korda sagedamini kui veergude järgi. Kui kasutate Excelis liiga sageli funktsiooni HLO, siis on tõenäoline, et te ei saanud siin elus millestki aru.

Süntaks

Funktsioonil VLOOKUP on neli parameetrit:

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

- soovitud väärtus (harva) või viide soovitud väärtust sisaldavale lahtrile (valdav enamus juhtudest);

- viide lahtrite vahemikule (kahemõõtmeline massiiv), mille ESIMESES (!) veerus parameetri väärtust otsitakse;

- veeru number vahemikus, kust väärtus tagastatakse;

- see on väga oluline parameeter, mis vastab küsimusele, kas vahemiku esimene veerg on järjestatud kasvavas järjekorras. Kui massiiv on sorteeritud, siis määrame väärtuse TRUE või 1, vastasel juhul - FALSE või 0. Kui see parameeter on välja jäetud, on see vaikimisi 1.

Vean kihla, et paljud neist, kes tunnevad funktsiooni VLOOKUP ebaühtlasena, võivad pärast neljanda parameetri kirjelduse lugemist tunda end ebamugavalt, kuna nad on harjunud nägema seda veidi erineval kujul: tavaliselt räägivad nad täpsest vastest, kui otsimine (FALSE või 0) või vahemiku skannimine (TRUE või 1).

Nüüd peate pingutama ja lugema järgmist lõiku mitu korda, kuni tunnete öeldu tähendust lõpuni. Iga sõna on seal oluline. Näited aitavad teil seda mõista.

Kuidas VLOOKUP valem täpselt töötab?

• Valemi tüüp I. Kui viimane parameeter jäetakse välja või määratakse võrdseks 1-ga, eeldab VLOOKUP, et esimene veerg on järjestatud kasvavas järjekorras, nii et otsing peatub rea juures, mis vahetult enne rida, mis sisaldab soovitud väärtusest suuremat väärtust … Kui sellist stringi ei leita, tagastatakse vahemiku viimane rida.

  

• Vormel II. Kui viimaseks parameetriks on määratud 0, kontrollib VLOOKUP järjestikku massiivi esimest veergu ja peatab otsingu kohe, kui leitakse parameetriga esimene täpne vaste, vastasel juhul kuvatakse veakood # N / A (# N / A) tagastatakse.

  

Valemite töövood

VPR tüüp I

VPR II tüüp

Järeldused I vormi valemitele

1. Väärtuste jaotamiseks vahemike vahel saab kasutada valemeid.
2. Kui esimene veerg sisaldab dubleerivaid väärtusi ja on õigesti sorteeritud, tagastatakse viimane korduvate väärtustega rida.
3. Kui otsite väärtust, mis on ilmselgelt suurem, kui esimene veerg võib sisaldada, leiate hõlpsalt tabeli viimase rea, mis võib olla üsna väärtuslik.
4. See vaade tagastab veateate # N / A ainult siis, kui see ei leia soovitud väärtusest väiksemat või sellega võrdset väärtust.
5. On üsna raske mõista, et valem tagastab valed väärtused, kui teie massiiv pole sorteeritud.

Järeldused II tüüpi valemitele

Kui soovitud väärtus esineb massiivi esimeses veerus mitu korda, valib valem järgnevaks andmete toomiseks esimese rea.

VLOOKUPi esitus

Olete jõudnud artikli haripunkti. Näib, mis vahet on, kui ma määran viimase parameetrina nulli või ühe? Põhimõtteliselt märgivad kõik loomulikult nulli, kuna see on üsna praktiline: te ei pea muretsema massiivi esimese veeru sortimise pärast, näete kohe, kas väärtus leiti või mitte. Kuid kui teie lehel on mitu tuhat VLOOKUP valemit, märkate, et VLOOKUP II on aeglane. Samal ajal hakkavad tavaliselt kõik mõtlema:

• Vajan võimsamat arvutit;
• Mul on vaja kiiremat valemit, näiteks teavad paljud INDEX + MATCH, mis on väidetavalt 5-10% kiirem.

Ja vähesed inimesed arvavad, et niipea, kui hakkate kasutama I tüüpi VLOOKUP-i ja tagate, et esimene veerg on mis tahes viisil sorteeritud, suureneb VLOOKUP-i kiirus 57 korda. Ma kirjutan sõnadega - VIISkümmend SEITSI KORDA! Mitte 57%, vaid 5700%. Kontrollisin seda fakti üsna usaldusväärselt.

Sellise kiire töö saladus peitub selles, et sorteeritud massiivile saab rakendada üliefektiivset otsingualgoritmi, mida nimetatakse binaarotsinguks (poolitamise meetod, dihhotoomia meetod). Seega rakendab seda I tüüpi VLOOKUP ja II tüüpi VLOOKUP otsib ilma optimeerimiseta. Sama kehtib ka funktsiooni MATCH, mis sisaldab sarnast parameetrit, ja funktsiooni LOOKUP kohta, mis töötab ainult sorteeritud massiividel ja on Lotus 1-2-3 ühilduvuse tagamiseks Excelis kaasas.

Valemi miinused

VLOOKUP-i puudused on ilmsed: esiteks otsib see ainult määratud massiivi esimesest veerust ja teiseks ainult sellest veerust paremal. Ja nagu te mõistate, võib juhtuda, et vajalikku teavet sisaldav veerg asub veerust, mida me otsime, vasakul. Juba mainitud valemite kombinatsioonil INDEX + MATCH see puudus puudub, mistõttu on see VLOOKUP-iga (VLOOKUP) võrreldes kõige paindlikum lahendus tabelitest andmete eraldamiseks.

Mõned valemi rakendamise aspektid päriselus

Vahemiku otsing

Vahemikuotsingu klassikaline näide on allahindluse määramine tellimuse suuruse järgi.

Otsige tekstistringe

Muidugi ei otsi VLOOKUP mitte ainult numbreid, vaid ka teksti. Tuleb meeles pidada, et valem ei tee vahet tähemärkide vahel. Kui kasutate metamärke, saate korraldada ähmase otsingu. Seal on kaks metamärki: "?" - asendab mis tahes ühe märgi tekstistringis, "*" - asendab mis tahes arvu märke.

Võitlusruumid

Tihti tõstatub küsimus, kuidas lahendada otsimisel lisaruumide probleem. Kui otsingutabelit saab neist siiski puhastada, ei ole VLOOKUP valemi esimene parameeter alati teie otsustada. Seega, kui on oht, et rakud võivad ummistuda lisatühikutega, saate selle kustutamiseks kasutada funktsiooni TRIM.

Erinevad andmevormingud

Kui funktsiooni VLOOKUP esimene parameeter viitab lahtrile, mis sisaldab arvu, kuid mis on lahtrisse tekstina salvestatud, ja massiivi esimene veerg sisaldab õiges vormingus numbreid, siis otsing nurjub. Võimalik on ka vastupidine olukord. Probleemi saab hõlpsasti lahendada, tõlkides parameetri 1 nõutavasse vormingusse:

= VLOOKUP (−− D7; Tooted! $ A $ 2: $ C $ 5; 3; 0) - kui D7 sisaldab teksti ja tabel numbreid;

= VLOOKUP (D7 & ""); Tooted! $ A $ 2: $ C $ 5; 3; 0) - ja vastupidi.

Muide, saate teksti numbriks tõlkida mitmel viisil korraga, valige:

• Topelteitus -D7.
• Korrutamine ühega D7 * 1.
• Null lisamine D7 + 0.
• Tõstmine esimesele astmele D7 ^ 1.

Arvu teisendamine tekstiks toimub tühja stringiga ühendamise teel, mis sunnib Exceli andmetüübi teisendama.

Kuidas maha suruda # N / A

Seda on väga mugav teha funktsiooniga IFERROR.

Näiteks: = IFERROR (VLOOKUP (D7; Tooted! $ A $ 2: $ C $ 5; 3; 0); "").

Kui VLOOKUP tagastab veakoodi # N / A, siis IFERROR katkestab selle ja asendab parameetri 2 (antud juhul tühja stringi) ja kui viga ei ilmne, siis see funktsioon teeskleb, et seda pole üldse olemas, kuid on ainult VLOOKUP, mis andis normaalse tulemuse.

Massiiv

Sageli unustatakse massiivi viide absoluutseks muuta ja venitades massiiv "ujub". Ärge unustage kasutada A2: C5 asemel $ A $ 2: $ C $ 5.

Viitemassiivi on hea paigutada töövihiku eraldi lehele. See ei jää jalge alla ja see on turvalisem.

Veelgi parem idee oleks deklareerida see massiiv nimega vahemikuna.

Paljud kasutajad kasutavad massiivi määramisel sellist konstruktsiooni nagu A: C, mis määrab terved veerud. Sellel lähenemisviisil on õigus eksisteerida, kuna te ei pea jälgima, et teie massiiv sisaldab kõiki vajalikke stringe. Kui lisate lehele ridu algse massiiviga, ei pea A:C määratud vahemikku kohandama. Muidugi sunnib see süntaktiline konstruktsioon Excelil natuke rohkem tööd tegema kui vahemiku täpne määramine, kuid selle üldkulu võib tähelepanuta jätta. Me räägime sajandiksekunditest.

Noh, geniaalsuse äärel - massiivi vormis korrastamine.

Funktsiooni COLUMN kasutamine ekstraktitava veeru määramiseks

Kui tabel, millesse VLOOKUP-i abil andmeid toote, on sama struktuuriga kui otsingutabel, kuid see sisaldab lihtsalt vähem ridu, saate VLOOKUP-i funktsiooni COLUMN () abil automaatselt arvutada allalaaditavate veergude arvud. Sel juhul on kõik VLOOKUP valemid samad (kohandatud esimese parameetri järgi, mis muutub automaatselt)! Pange tähele, et esimesel parameetril on veeru absoluutne koordinaat.

Liitvõtme loomine & "|" & abil

Kui tekib vajadus otsida korraga mitme veeru järgi, siis tuleb otsingu jaoks teha liitvõti. Kui tagastatav väärtus poleks tekstiline (nagu väljal "Kood"), vaid numbriline, siis sobiks selleks mugavam SUMIFS-valem ja liitveeru võtit poleks üldse vaja.

See on minu esimene artikkel Lifehackerile. Kui teile meeldis, kutsun teid külla ja lugege kommentaarides hea meelega ka teie VLOOKUP-funktsiooni jms kasutamise saladusi. Aitäh.:)