Visos „Excel VLOOKUP“funkcijos paslaptys, leidžiančios rasti duomenis lentelėje ir išgauti juos į kitą

2024 Autorius: Malcolm Clapton | [email protected]. Paskutinį kartą keistas: 2023-12-17 04:04

Perskaitę straipsnį sužinosite ne tik, kaip rasti duomenis Excel skaičiuoklėje ir išskleisti juos į kitą, bet ir technikų, kurias galima naudoti kartu su funkcija VLOOKUP.

Dirbant Excel, labai dažnai reikia rasti duomenis vienoje lentelėje ir ištraukti į kitą. Jei vis dar nežinote, kaip tai padaryti, perskaitę straipsnį ne tik sužinosite, kaip tai padaryti, bet ir sužinosite, kokiomis sąlygomis galite išspausti maksimalų sistemos našumą. Apsvarstyta dauguma labai veiksmingų metodų, kurie turėtų būti naudojami kartu su VLOOKUP funkcija.

Net jei daugelį metų naudojate funkciją VLOOKUP, labai tikėtina, kad šis straipsnis jums bus naudingas ir nepaliks abejingų. Pavyzdžiui, būdamas IT specialistu, o vėliau – IT vadovu, VLOOKUP naudoju 15 metų, bet su visais niuansais susitvarkiau tik dabar, kai pradėjau profesionaliai mokyti žmones Excel.

VLOOKUP yra santrumpa vvertikaliai NSinspekcija. Taip pat VLOOKUP – vertikali LOOKUP. Jau pats funkcijos pavadinimas mums sufleruoja, kad ji ieško lentelės eilutėse (vertikaliai – kartodama eilutes ir fiksuodama stulpelį), o ne stulpeliuose (horizontaliai – kartodama per stulpelius ir fiksuodama eilutę). Reikėtų pažymėti, kad VLOOKUP turi seserį – bjaurųjį ančiuką, kuris niekada netaps gulbe – tai funkcija HLOOKUP. HLOOKUP, priešingai nei VLOOKUP, atlieka horizontalias paieškas, tačiau „Excel“koncepcija (ir iš tikrųjų duomenų organizavimo koncepcija) reiškia, kad jūsų lentelėse yra mažiau stulpelių ir daug daugiau eilučių. Štai kodėl mums reikia ieškoti pagal eilutes daug kartų dažniau nei pagal stulpelius. Jei „Excel“per dažnai naudojate HLO funkciją, greičiausiai šiame gyvenime kažko nesupratote.

Sintaksė

Funkcija VLOOKUP turi keturis parametrus:

= VLOOKUP (;; [;]), čia:

- norima reikšmė (retai) arba nuoroda į langelį, kuriame yra norima reikšmė (dauguma atvejų);

- nuoroda į langelių diapazoną (dvimatis masyvas), kurio PIRMAME (!) stulpelyje bus ieškoma parametro reikšmės;

- stulpelio numeris diapazone, iš kurio bus grąžinama reikšmė;

- tai labai svarbus parametras, atsakantis į klausimą, ar pirmasis diapazono stulpelis rūšiuojamas didėjančia tvarka. Jei masyvas surūšiuotas, nurodome reikšmę TRUE arba 1, kitu atveju - FALSE arba 0. Jei šis parametras praleistas, jis pagal nutylėjimą yra 1.

Galiu lažintis, kad daugelis tų, kurie VLOOKUP funkciją žino kaip dribsnius, perskaitę ketvirtojo parametro aprašymą gali pasijusti nejaukiai, nes yra įpratę jį matyti kiek kitokiu pavidalu: dažniausiai kalbama apie tikslią atitiktį, kai. paieška (FALSE arba 0) arba diapazono nuskaitymas (TRUE arba 1).

Dabar reikia įsitempti ir keletą kartų perskaityti kitą pastraipą, kol pajusite to, kas buvo pasakyta, prasmę iki galo. Ten svarbus kiekvienas žodis. Pavyzdžiai padės tai išsiaiškinti.

Kaip tiksliai veikia VLOOKUP formulė?

• I formulės tipas. Jei paskutinis parametras praleistas arba nurodytas lygus 1, tada VLOOKUP daroma prielaida, kad pirmasis stulpelis surūšiuotas didėjančia tvarka, todėl paieška sustoja ties eilute, kuri iš karto prieš eilutę, kurioje yra didesnė už norimą reikšmę … Jei tokios eilutės nerasta, grąžinama paskutinė diapazono eilutė.

  

• Formulė II. Jei paskutinis parametras nurodytas kaip 0, tada VLOOKUP nuosekliai nuskaito pirmąjį masyvo stulpelį ir iškart sustabdo paiešką, kai randama pirmoji tiksli parametro atitiktis, kitaip klaidos kodas # N / A (# N / A) yra grąžinamas.

  

Formulių darbo eigos

VPR I tipas

VPR II tipas

I formos formulių išvados

1. Formulės gali būti naudojamos vertėms paskirstyti diapazonuose.
2. Jei pirmame stulpelyje yra pasikartojančios reikšmės ir jis yra teisingai surūšiuotas, bus grąžinta paskutinė eilutė su pasikartojančiomis reikšmėmis.
3. Jei ieškote reikšmės, kuri akivaizdžiai didesnė nei gali būti pirmame stulpelyje, tuomet nesunkiai rasite paskutinę lentelės eilutę, kuri gali būti gana vertinga.
4. Šis rodinys pateiks klaidą # N / A tik tuo atveju, jei neras reikšmės, mažesnės už norimą arba lygią jai.
5. Gana sunku suprasti, kad formulė grąžina neteisingas reikšmes, jei jūsų masyvas nėra surūšiuotas.

II tipo formulių išvados

Jei norima reikšmė atsiranda kelis kartus pirmajame masyvo stulpelyje, formulė pasirinks pirmąją eilutę tolesniam duomenų gavimui.

VLOOKUP pasirodymas

Jūs pasiekėte straipsnio kulminaciją. Atrodytų, koks skirtumas, jei kaip paskutinį parametrą nurodysiu nulį arba vienetą? Iš esmės visi, žinoma, nurodo nulį, nes tai gana praktiška: nereikia jaudintis dėl pirmojo masyvo stulpelio rūšiavimo, iškart galite pamatyti, ar vertė buvo rasta, ar ne. Bet jei jūsų lape yra keli tūkstančiai VLOOKUP formulių, pastebėsite, kad VLOOKUP II yra lėtas. Tuo pačiu metu paprastai visi pradeda galvoti:

• Man reikia galingesnio kompiuterio;
• Man reikia greitesnės formulės, pavyzdžiui, daugelis žino apie INDEX + MATCH, kuri, kaip manoma, yra greitesnė 5-10%.

Ir tik nedaugelis galvoja, kad kai tik pradėsite naudoti I tipo VLOOKUP ir bet kokiomis priemonėmis užtikrinsite, kad pirmasis stulpelis būtų surūšiuotas, VLOOKUP greitis padidės 57 kartus. Rašau žodžiais – PENKIASdešimt SEPTYNI KARTAI! Ne 57%, o 5700%. Šį faktą patikrinau gana patikimai.

Tokio greito darbo paslaptis slypi tame, kad surūšiuotame masyve galima pritaikyti itin efektyvų paieškos algoritmą, kuris vadinamas dvejetaine paieška (perdalinimo metodas, dichotomijos metodas). Taigi I tipo VLOOKUP jį taiko, o II tipo VLOOKUP ieško visiškai be optimizavimo. Tas pats pasakytina apie funkciją MATCH, kuri apima panašų parametrą, ir funkciją LOOKUP, kuri veikia tik surūšiuotuose masyvuose ir yra įtraukta į Excel, kad būtų suderinama su Lotus 1-2-3.

Formulės trūkumai

VLOOKUP trūkumai akivaizdūs: pirma, ji ieško tik pirmajame nurodyto masyvo stulpelyje, antra, tik šio stulpelio dešinėje. Ir kaip jūs suprantate, gali atsitikti taip, kad stulpelis, kuriame yra reikalinga informacija, bus stulpelio, kuriame mes ieškosime, kairėje. Jau minėtas formulių derinys INDEX + MATCH neturi šio trūkumo, todėl jis yra lankstiausias sprendimas duomenims iš lentelių išgauti, palyginti su VLOOKUP (VLOOKUP).

Kai kurie formulės taikymo realiame gyvenime aspektai

Diapazono paieška

Klasikinė diapazono paieškos iliustracija yra užduotis nustatyti nuolaidą pagal užsakymo dydį.

Ieškokite teksto eilučių

Žinoma, VLOOKUP ieško ne tik skaičių, bet ir teksto. Reikėtų nepamiršti, kad formulė neskiria simbolių didžiosios ir mažosios raidės. Jei naudojate pakaitos simbolius, galite organizuoti neaiškią paiešką. Yra du pakaitos simboliai: "?" - pakeičia bet kurį vieną simbolį teksto eilutėje, "*" - pakeičia bet kokį skaičių bet kokių simbolių.

Kovos erdvės

Dažnai kyla klausimas, kaip ieškant išspręsti papildomų erdvių problemą. Jei vis tiek galima juos išvalyti iš paieškos lentelės, pirmasis VLOOKUP formulės parametras ne visada priklauso nuo jūsų. Todėl, jei kyla pavojus, kad ląstelės užsikimš papildomomis erdvėmis, galite naudoti funkciją TRIM, kad ją išvalytumėte.

Skirtingas duomenų formatas

Jei pirmasis funkcijos VLOOKUP parametras nurodo langelį, kuriame yra skaičius, bet kuris yra saugomas langelyje kaip tekstas, o pirmame masyvo stulpelyje yra teisingo formato skaičiai, tada paieška nepavyks. Galima ir priešinga situacija. Problemą galima lengvai išspręsti išvertus 1 parametrą į reikiamą formatą:

= VLOOKUP (−− D7; Produktai! $ A $ 2: $ C $ 5; 3; 0) - jei D7 yra tekstas, o lentelėje yra skaičiai;

= VLOOKUP (D7 ir ""); Produktai! $ A $ 2: $ C $ 5; 3; 0) – ir atvirkščiai.

Beje, tekstą į skaičių galite išversti keliais būdais vienu metu, pasirinkite:

• Dvigubas neigimas -D7.
• Daugyba iš vieno D7 * 1.
• Nulis pridėjimo D7 + 0.
• Pakėlimas į pirmą laipsnį D7 ^ 1.

Skaičius konvertuojamas į tekstą sujungiant tuščią eilutę, o tai verčia Excel konvertuoti duomenų tipą.

Kaip nuslopinti # N / A

Tai labai patogu padaryti naudojant IFERROR funkciją.

Pavyzdžiui: = IFERROR (VLOOKUP (D7; Produktai! $ A $ 2: $ C $ 5; 3; 0); "").

Jei VLOOKUP grąžins klaidos kodą # N / A, tada IFERROR jį perims ir pakeis 2 parametrą (šiuo atveju tuščią eilutę), o jei klaida neįvyks, ši funkcija apsimes, kad jos visai nėra, bet yra tik VLOOKUP, kuris grąžino normalų rezultatą.

Masyvas

Dažnai pamiršta suabsoliutinti masyvo nuorodą, o tempiant masyvas „plaukia“. Nepamirškite naudoti $ A $ 2: $ C $ 5 vietoj A2: C5.

Nuorodų masyvą patartina įdėti į atskirą darbaknygės lapą. Jis nepakliūva po kojomis ir bus saugiau.

Dar geresnė idėja būtų paskelbti šį masyvą kaip pavadintą diapazoną.

Daugelis vartotojų, nurodydami masyvą, naudoja tokią konstrukciją kaip A: C, nurodydami ištisus stulpelius. Šis metodas turi teisę egzistuoti, nes jums nereikės sekti, kad jūsų masyvas apima visas reikalingas eilutes. Jei į lapą įtrauksite eilutes su pradiniu masyvu, diapazono, nurodyto kaip A: C, koreguoti nereikia. Žinoma, ši sintaksinė konstrukcija verčia „Excel“atlikti šiek tiek daugiau darbo nei tiksliai nurodyti diapazoną, tačiau šios papildomos išlaidos gali būti nepaisomos. Mes kalbame apie šimtąsias sekundės dalis.

Na, ant genialumo slenksčio – sutvarkyti masyvą formoje.

Funkcijos COLUMN naudojimas nurodyti stulpelį, kurį reikia išgauti

Jei lentelės, į kurią nuskaitote duomenis naudodami VLOOKUP, struktūra yra tokia pati kaip ir peržvalgos lentelės, bet joje yra mažiau eilučių, galite naudoti VLOOKUP funkciją COLUMN (), kad automatiškai apskaičiuotumėte nuskaitytinų stulpelių skaičių. Tokiu atveju visos VLOOKUP formulės bus vienodos (pritaikytos pirmam parametrui, kuris keičiasi automatiškai)! Atkreipkite dėmesį, kad pirmasis parametras turi absoliučią stulpelio koordinatę.

Sudėtinio rakto kūrimas naudojant & „|“&

Jei reikia ieškoti pagal kelis stulpelius vienu metu, tuomet reikia sukurti sudėtinį paieškos raktą. Jei grąžinama reikšmė būtų ne tekstinė (kaip yra lauke „Kodas“), o skaitinė, tai tam tiktų patogesnė SUMIFS formulė ir sudėtinio stulpelio rakto visai nereikėtų.

Tai mano pirmasis straipsnis skirtas Lifehacker. Jei patiko, kviečiu užsukti, o taip pat mielai komentaruose paskaityti apie Jūsų paslaptis naudojant VLOOKUP funkciją ir panašiai. Dėkoju.:)