AT Microsoft Excel existuje veľa rôznych funkcií, ktoré výrazne uľahčujú prácu používateľa, a v tomto článku si povieme niečo o jednej z nich. Volá sa VLOOKUP a ak v angličtine, tak VLOOKUP.
Funkcia VLOOKUP prenáša hodnoty z jednej tabuľky do konkrétnych buniek v druhej. Vysvetlíme to podrobnejšie - v prvej tabuľke vyberiete hodnotu, ktorú chcete nájsť, v ľavom stĺpci druhej. Ak existujú zhody, VLOOKUP prenesie hodnotu zo zadaného stĺpca tohto riadku do prvej tabuľky. Aj keď je definícia trochu mätúca, použitie funkcie nie je také ťažké. Pozrime sa na niekoľko príkladov.
Pretože sa funkcia najčastejšie používa na vyplnenie stĺpca cenou, ktorá je uvedená v samostatných cenách, vezmeme si nasledujúci príklad. Napríklad existuje tabuľka s ovocím (oranžová), kde je pre každú z nich uvedené, koľko kilogramov si chceme objednať. Podľa toho musí každé ovocie napísať cenu z cenníka (zeleného) uvedeného dodávateľom a potom vypočítať, koľko bude nákup stáť. Je ťažké zobraziť každú položku a preniesť údaje, najmä ak existujú tisíce riadkov s tovarom, takže môžeme použiť VLOOKUP.
Kliknite na hornú bunku v prvej tabuľke v stĺpci Cena a potom na tlačidlo fx na paneli vzorcov otvorte Sprievodcu funkciami.
Ďalšia vec, ktorú urobíme, je zápis argumentov do poskytnutých polí.
Do poľa vložte kurzívu „Lookup_value“ a vyberte hodnotu, ktorú budeme hľadať v prvej tabuľke. Mám toto jablko.
V riadku „Tabuľka“ vyberte ten, z ktorého sa budú brať údaje - nevyberajte hlavičku. Upozorňujeme, že stĺpec úplne vľavo musí obsahovať hodnoty, ktoré hľadáme. To znamená, že potrebujem jablko a ďalšie ovocie, čo znamená, že ich zoznam by mal byť v ľavom stĺpci vybranej oblasti.
Takže po tom, čo napíšeme vzorec a roztiahneme ho po celom stĺpci, sa vybraný rozsah neposunie nadol, musíte odkazy urobiť absolútnymi: vyberte údaje v poli a stlačte „F4“. Teraz sa adresa buniek stala absolútnou, bol k nim pridaný znak „$“ a rozsah sa nebude posúvať.
Ak je číslo stĺpca, vložte číslo zodpovedajúce v druhej tabuľke do stĺpca, z ktorého chcete preniesť údaje. Môj cenník sa skladá z ovocia a cien, potrebujem druhý, tak som dal číslo „2“.
AT „Interval_view“ napíšte „FALSE“ - ak potrebujete vyhľadať presnú zhodu, alebo „Pravda“ - ak môžu byť hodnoty približné. Pre náš príklad vyberte prvý. Ak v tomto poli neurčíte nič, predvolene sa vyberie druhý. Potom kliknite na tlačidlo „OK“.
Tu dávajte pozor na nasledujúce, ak pracujete s číslami a označujete „True“, potom musí byť druhá tabuľka (to je náš cenník) zoradená vzostupne. Napríklad pri hľadaní 5,25 bude 5,27 a budú sa brať údaje z tohto riadku, aj keď pod ním môže byť stále číslo 5,2599 - vzorec však nebude hľadať ďalej, pretože si myslí, že nižšie uvedeného čísla je iba viac.
Ako funguje VLOOKUP? Vezme požadovanú hodnotu (jablko) a vyhľadá ju v ľavom stĺpci zadaného rozsahu (zoznam plodov). Ak existuje zhoda, hodnota sa prevezme z rovnakého riadku, iba stĺpca uvedeného v argumentoch (2), a prenesie sa do bunky, ktorú potrebujeme (C2). Vzorec vyzerá takto:
VLOOKUP (A2; $ G $ 2: $ H $ 12; 2; FALSE)
Teraz ho môžete roztiahnuť nad požadovaný počet riadkov potiahnutím za pravý dolný roh.
Všetky ceny boli presunuté z cenníka do nákupnej tabuľky v súlade s názvami plodov.
Pokiaľ v prvej tabuľke máte názvy produktov, ktoré nie sú v cenníku, mám zeleninu, tak oproti týmto bodom vzorec VLOOKUP dá chybu # N / A.
Keď do hárka pridáte stĺpce, automaticky sa zmenia údaje pre argument funkcie „Tabuľka“. V príklade je cena posunutá o 2 stĺpce doprava. Vyberte ľubovoľnú bunku so vzorcom a uvidíte, že namiesto $ G $ 2: $ H $ 12, teraz $ I $ 2: $ J $ 14.
Teraz poďme na to, ako pracovať s funkciou VLOOKUP v programe Excel a s rozbaľovacími zoznamami. Najprv musíte vytvoriť rozbaľovací zoznam. Vyberte bunku, hore otvorte „Údaje“ a kliknite na tlačidlo "Overovanie dát".
V okne „Typ údajov“, ktoré sa otvorí, bude „Zoznam“, nižšie označujeme oblasť zdroja - to sú názvy plodov, to znamená stĺpec, ktorý je v prvej a druhej tabuľke. Kliknite na tlačidlo OK.
Vyberiem F2 a vložím funkciu VLOOKUP. Prvým argumentom je vytvorený zoznam (F1).
Druhým je tabuľka dodávateľov so stanovenými cenami. Nezabudnite, že tu sa musí ľavý stĺpec zhodovať s údajmi rozbaľovacieho zoznamu.
Ukázalo sa to ako hľadanie: vyberieme ovocie a VLOOKUP nájde jeho cenu v cenníku.
Ak ste mali rovnaké ceny a potom dodávatelia dali nový zoznam, musíte nejako zistiť, čo sa zmenilo. Manuálne to trvá dlho, ale pomocou tejto funkcie sa dá všetko zvládnuť veľmi rýchlo. Budete musieť pridať jeden stĺpec a preniesť doň nové hodnoty a potom už len porovnávať údaje.
Kliknite na ľubovoľnú bunku v stĺpci D a vložte jednu novú.
Pomenoval som to Nová cena - budú nové ceny a tie staré sú uvedené v stĺpci vľavo. Mám novú tabuľku na inom hárku, aby vám bolo jasné, ako používať VLOOKUP, ak sa údaje nachádzajú na rôznych hárkoch. V pridanom stĺpci vyberte prvú prázdnu bunku.
Vložíme funkciu a zadáme argumenty. Najprv to, čo budeme hľadať, napríklad Apple (A2). Ak chcete zvoliť rozsah z nového cenníka, umiestnite kurzor do poľa „Tabuľka“ a prejdite na požadovaný hárok, ja mám „List1“.
Vyberte požadované stĺpce a riadky pomocou myši bez hlavičiek.
Potom urobíme absolútne odkazy na bunky: „List1! $ A $ 2: $ B $ 12“. Zvýraznite riadok a stlačte kláves „F4“, aby ste na adresy buniek pridali znak dolára. Zadajte stĺpec (2) a napíšte „FALSE“.
Nakoniec kliknite na tlačidlo „OK“.
Teraz sú vedľa seba umiestnené dva stĺpce s novou a starou cenou a môžete vykonať vizuálne porovnanie alebo použitie určitých vzorcov alebo podmienené formátovanie.
Dúfam, že sa mi to podarilo inštrukcia krok za krokom o použití a aplikácii funkcie VLOOKUP v Exceli a teraz je vám všetko jasné.
Ohodnoťte článok: (15
odhady, priemer: 5,00
z 5)
Správca webu. Vysokoškolské vzdelanie v odbore „Informačná bezpečnosť“ .. Autor väčšiny článkov a lekcií počítačovej gramotnosti
- A1. Toto je približný odkaz na bunku. Môže obsahovať ľubovoľnú hodnotu v závislosti od výsledku, ktorý chce používateľ získať.
- Databáza. Názov oblasti informácií, ktorá sa má vyhľadať. Koncept nie je taký široký ako ten predchádzajúci. Môže sa použiť iba na prvé riadky alebo stĺpce.
- 2. Toto je poradové číslo stĺpca, z ktorého bude program čerpať informácie.
- Klamstvo. Označuje presnú zhodu. Niekedy sú zadané ďalšie ďalšie parametre tohto slova. A program vyhľadá všetky zhody a určí najbližšie hodnoty.
- Keď potrebujete nájsť informácie vo veľkej tabuľke alebo nájsť všetky duplicitné údaje a zhody.
- Vo vyučovacom prostredí. Učiteľ môže kedykoľvek nájsť svoju dochádzku, pokrok a ďalšie informácie podľa mien svojich študentov. Je to obzvlášť užitočné, keď je zoznam študentov veľký a učiteľ si nemôže spomenúť na každého z nich.
- V maloobchode. Ak použijete túto funkciu na vyhľadanie ceny, zloženia alebo článku výrobku, potom môže človek rýchlo odpovedať na otázky zákazníkov.
- Použite už vytvorený dokument alebo otvorte nový prázdny list;
- Kliknite na tlačidlo „Vzorce“, ako je znázornené na obrázku nižšie;
- Do vyhľadávacieho panela zadajte „VLOOKUP“ alebo „VLOOKUP“ v závislosti od jazyka programu;
- Prispôsobte si kategóriu „Kompletný zoznam“;
- Kliknite na „Nájsť“.
- <ЧТО> - prvý prvok. Namiesto toho musíte zaregistrovať presne hodnotu, ktorú chcete nájsť v tabuľke. Môžete tiež zadať adresu bunky v tabuľke;
- <НОМЕР_СТОЛБЦА> - tu je potrebné vytlačiť číslo stĺpca, v rámci ktorého sa budú hľadať údaje.
- <ГДЕ> - tu používateľ definuje počet buniek a nastaví ich rozmer vo formulári dvojrozmerné pole údaje. Prvý stĺpec je prvok WHAT;
- <ОТСОРТИРОВАНО> - tento prvok funkcie VLOOKUP je zodpovedný za zoradenie prvého stĺpca vo vzostupnom poradí (prvý stĺpec pre „WHERE“). Výsledkom úspešného triedenia je hodnota true (jedna). Ak sa pri zadávaní parametrov vyskytnú nepresnosti alebo chyby, zobrazí sa nesprávna hodnota triedenia (nula). Stojí za zmienku, že počas priradenia VLOOKUP<ОТСОРТИРОВАНО> možno vynechať, v takom prípade je jeho predvolená hodnota prijatá ako pravda.
- Keď je potrebné rozdeliť hodnoty objektu tabuľkového procesora podľa jeho rozsahov;
- Pre tie tabuľky, v ktorých parameter WHERE môže obsahovať niekoľko identických hodnôt. V takom prípade vráti vzorec iba ten, ktorý je v poslednom riadku vzhľadom na pole;
- Keď potrebujete hľadať hodnoty, ktoré sú väčšie ako hodnoty obsiahnuté v prvom stĺpci. Takto nájdete posledný riadok tabuľky prakticky okamžite.
Excel VLOOKUP je funkcia v príslušnom programe, ktorá je krásna a jednoduchá. Má veľa rôznych aplikácií, používa sa v úplne odlišných oblastiach: od školení po maloobchod. Základným konceptom funkcie je hľadanie zhôd v jednej alebo viacerých tabuľkách. Môžete tak ľahko nájsť informácie, ktoré vás zaujímajú, a to tak, že budete tráviť minimum času.
všeobecné informácie
Excel - čo to je? V anglickej verzii sa nazýva aj VLOOKUP. Toto je jedna z najbežnejších funkcií poľa a referencie. Špecialisti, ktorí tvoria stupnicu BRP ADVICE, nastavili úroveň obtiažnosti na 3 alebo 7.
Uvažovaná funkcia vám umožňuje rýchlo nájsť vo veľkej tabuľke tie hodnoty z riadkov alebo stĺpcov, ktoré používateľ potrebuje. Tento program sám nájde prvý parameter. Stĺpec je zadaný používateľom. Existuje ešte jedna funkcia HLP, kde je čiara označená osobou. Stĺpec nájde sama.
Ak sa používateľ už zaoberal odkazmi a poľami, potom bude ľahké pochopiť akcie VLOOKUPU. V rôznych tabuľkových dokumentoch môžete vytvoriť poznámku pod čiarou ku konkrétnej bunke. Berie sa to ako príklad alebo sa naopak označuje ako výnimka. V úlohe VLOOKUP je bunka zvyčajne zadaná ako A1, D9, K8 atď.
Niekedy je odkaz odoslaný v inej podobe (R1C1). Jedným slovom sú označené stĺpec a riadok, na križovatke ktorých sa nachádzajú informácie potrebné pre používateľa. Program dostane presnú indikáciu, kde má tieto údaje hľadať.
Parametre funkcie príkladom
Vo funkcii Excel VLOOKUP je najskôr uvedené číslo riadku, za ktorým nasleduje označenie stĺpca. Mali by ste dostať niečo ako:
VLOOKUP (A1; Database; 2; FALSE)
Parametre funkcie znamenajú nasledujúce:
Bohužiaľ táto funkcia nemôže fungovať profesionálnejšie. Niektoré údaje, ktoré musí používateľ pamätať (číslo riadku alebo stĺpca). V opačnom prípade nebude môcť nájsť potrebné informácie.
Argumenty VLOOKUP
Ak chcete hovoriť o tom, ako funkcia VLOOKUP funguje v programe Excel, musíte sa oboznámiť s jej argumentmi. Prvou je požadovaná hodnota. Nastavuje parametre vyhľadávania, ktoré program bude hľadať v prvom stĺpci tabuľky. Nemôže pracovať s druhým a nasledujúcim, funkcia tieto informácie jednoducho nenájde. Vo vzorci je tento argument uvedený v úvodzovkách. Výnimkou sú názvy funkcií.
Ďalším argumentom je tabuľka. Môže byť špecifikovaný v súradnicovom systéme. A priamo v tejto tabuľke, v jej prvom stĺpci, sa funkcia pokúsi nájsť požadovaný prvok. Je to indikované na začiatku (pozri vyššie).
Tretím argumentom je číslo stĺpca. Tu sú uvedené informácie, ktoré používateľ hľadá. Napríklad môže vidieť pozíciu zodpovedajúcu priezvisku z prvého stĺpca, jeho plat, známky a podobne. Všetko závisí od oblasti činnosti používateľa.
A posledným argumentom je intervalové skenovanie. Označuje „1“ a „0“ alebo „False“ a „True“. V prvom prípade budú údaje znamenať, že dané hľadanie je približné. Potom program začne hľadať všetky zápasy. Ak je použitá druhá možnosť, potom funkcia bude venovať pozornosť iba presným hodnotám.
Bežné chyby
Excel VLOOKUP nikdy nezlyhá, ak je skontrolovaná nesprávna úloha. To znamená, že za porušenie môže iba používateľ. Existujú tri bežné chyby. Po prvé, človek je často zmätený v súvislosti s argumentmi „nepravdivé“ a „pravdivé“. Prvá je zameraná na nájdenie presnej zhody. Ak zadáte hodnotu „true“, funkcia vyberie približné hodnoty.
Po druhé, vzorec programu Excel VLOOKUP nemožno označiť, takže vyhľadávanie sa začína od druhého alebo nasledujúceho stĺpca. Údaje nájdete iba pri prvom. Ak teda používateľ zadá iný vzorec, ktorý sa líši od pravidiel, potom ho program jednoducho nebude schopný rozpoznať.
A do tretice, číslo stĺpca, odkiaľ sú informácie potrebné, je často nesprávne uvedené. V takom prípade musíte skontrolovať údaje znova.
Kedy sa používa funkcia VLOOKUP?
Stojí za to o tom hovoriť podrobne. Nie je žiadnym tajomstvom, že funkcia Excel VLOOKUP sa používa v úplne odlišných oblastiach. Návod na jeho použitie sa môže zdať komplikovaný, ale iba na prvý pohľad. V opačnom prípade by sa to tak nerozšírilo.
Pamätajte na to hlavné: funkcia vyhľadáva informácie vertikálne, teda podľa stĺpcov. Používa sa v rôznych situáciách:
Stručne povedané, v každom prostredí, keď potrebujete vyhľadať údaje z tabuľky, môžete použiť VLOOKUP.
Ako môžem použiť funkciu VLOOKUP v tabuľke?
Nie je ťažké tomu porozumieť. Aby vzorec Excel VLOOKUP skutočne fungoval, musíte si najskôr vytvoriť tabuľku. Aj pre plné využitie funkcia vyžaduje najmenej dva stĺpce, maximálny počet z nich nie je obmedzený.
Potom do prázdnej bunky musíte zadať tento vzorec, kde používateľ nastaví parametre hľadania zhôd a informácií. Prázdny výklenok môže byť umiestnený kdekoľvek: hore, dole, vpravo. Aby bolo možné nájsť údaje, bude potrebné bunky rozbaliť. Pretože sú umiestnené v ich vlastných stĺpcoch, sú potrebné minimálne dva z nich. Ak existuje viac parametrov vyhľadávania, zvyšuje sa aj počet buniek. Potom sa skontroluje funkcia a to, ako správne je vzorec napísaný. Kliknite na „zobraziť hodnoty“. Možno zistiť nezrovnalosti vo vzorci.
VLOOKUP v anglickom jazyku Excel a v ruskom analógovom formáte sa používa rovnako. Existuje však niekoľko tipov od profesionálov. Aby funkcia fungovala lepšie, najmä po zmene údajov, odporúča sa medzi polia zadať znak dolára. Napríklad nie A1, ale A $ 1 $. Keď sú vložené primárne hodnoty, medzi názvy riadkov a stĺpcov nie je potrebné vkladať žiadne znaky alebo medzery.
Odporúča sa tiež starostlivo skontrolovať tabuľku, aby v nej neboli zbytočné interpunkčné znamienka alebo medzery. Ich prítomnosť neumožňuje programu normálne vyhľadávať zhody, najmä ak je zhoda iba približná (pomocou parametra „True“).
Záver
Excel VLOOKUP (vertikálne zobrazenie) je pre skúseného používateľa jednoduchý. Ale pre neskúseného človeka nebude ťažké zoznámiť sa s pravidlami jeho používania. Po ich zvládnutí bude používateľ schopný rýchlo nájsť informácie bez ohľadu na to, aká rozsiahla je tabuľka. Ak potrebujete použiť horizontálne sledovanie, použite funkciu HLP.
Práca so súhrnnou tabuľkou znamená načítať do nej hodnoty z iných tabuliek. Ak je veľa tabuliek, manuálny prenos zaberie obrovské množstvo času a ak sa dáta neustále aktualizujú, potom to už bude sizyfovská práca. Našťastie existuje funkcia VLOOKUP, ktorá ponúka možnosť automatického načítania údajov. Pozrime sa na konkrétne príklady toho, ako táto funkcia funguje.
Názov funkcie VLOOKUP znamená „funkcia vertikálneho pohľadu“. V angličtine znie jeho názov - VLOOKUP. Táto funkcia vyhľadá údaje v ľavom stĺpci skúmaného rozsahu a potom vráti výslednú hodnotu do zadanej bunky. Jednoducho povedané, VLOOKUP vám umožňuje usporiadať hodnoty z bunky v jednej tabuľke do inej tabuľky. Poďme zistiť, ako používať funkciu VLOOKUP v programe Excel.
Príklad použitia VLOOKUP
Poďme sa spolu s konkrétnym príkladom pozrieť na to, ako funguje funkcia VLOOKUP.
Máme dva stoly. Prvým z nich je tabuľka obstarávania, ktorá obsahuje názvy potravinárskych výrobkov. Nasledujúci stĺpec za menom obsahuje hodnotu množstva tovaru, ktorý chcete kúpiť. Ďalej nasleduje cena. A v poslednom stĺpci - celkové náklady na nákup konkrétneho názvu produktu, ktoré sa vypočítajú pomocou vzorca na vynásobenie množstva cenou už v bunke. Musíme ale len sprísniť cenu pomocou funkcie VLOOKUP z nasledujúcej tabuľky, čo je cenník.
Ako vidíte, cena zemiakov sa z cenníka posunula vyššie na tabuľku. Aby sme s inými názvami produktov neuskutočnili taký zložitý postup, jednoducho stojíme v pravom dolnom rohu vyplnenej bunky tak, aby sa objavil krížik. Tento krížik nakreslíme na samú spodnú časť tabuľky.
Takto sme pomocou funkcie VLOOKUP vytiahli všetky potrebné údaje z jednej tabuľky do druhej.
Ako vidíte, funkcia VLOOKUP nie je taká zložitá, ako sa na prvý pohľad zdá. Nie je veľmi ťažké pochopiť jeho použitie, ale zvládnutie tohto nástroja vám ušetrí veľa času pri práci s tabuľkami.
Funkcia VLOOKUP v Príklady Excelu ktoré sú popísané nižšie v článku.
Používatelia pri práci s programom často musia rýchle vyhľadávanie informácie v jednej tabuľke a ich prenos do iného objektu listu.
Pochopenie toho, ako funguje VLOOKUP, výrazne zjednoduší vaše a pomôže vám rýchlejšie dokončiť úlohy.
Obsah:VLOOKUP (Vertical Lookup) je ďalší názov funkcie, ktorý sa nachádza v anglickej verzii tabuľkového procesora.
Samotná skratka VLOOKUP znamená „vertikálne zobrazenie“.
Analýza údajov a ich vyhľadávanie v tabuľke sa vykonáva pomocou postupného počítania prvkov z riadku na riadok v každom stĺpci.
Excel má tiež opačnú funkciu s názvom HLOOKUP alebo HLP - horizontálne prezeranie.
Jediný rozdiel v tom, ako tieto možnosti fungujú, je ten, že HLP prehľadáva tabuľku iteráciou cez stĺpce, nie riadky.
Používatelia častejšie uprednostňujú funkcie VLOOKUP, pretože väčšina tabuliek má viac riadkov ako stĺpcov.
Ako vyzerá syntax VLOOKUP?
Syntax funkcie v programe Excel je sada parametrov, pomocou ktorých ju môžete volať a nastavovať. Písanie je podobné metóde písania matematických funkcií.
Výsledkom hľadania vzorca je jeho umiestnenie v zozname. Kliknutím na prvok sa jeho vzorec zobrazí v dolnej časti obrazovky.
Názov funkcie je uvedený v zátvorkách a jej parametre sú v zátvorkách. Vo vnútri vzorca je každý jednotlivý parameter napísaný v uhlovom tvare<> zátvorky.
Všeobecné zobrazenie popisu pre VLOOKUP vyzerá takto:
Obr. 3 - zoznam parametrov
Pozrime sa podrobnejšie na každú z hodnôt, ktoré sú popísané v zátvorkách:
Ako funguje VLOOKUP. Užitočný príklad
Aby sme lepšie pochopili, ako funguje VLOOKUP, prejdime k konkrétnym príkladom. Zoberme si jednoduchú dvojstĺpcovú tabuľku. Označte kód a názov produktu.
Po vyplnení tabuľky kliknite na prázdnu bunku a na výsledok VLOOKUP. Kliknite na kartu Vzorce a vyberte možnosť ZOBRAZIŤ.
Potom zadajte všetky požadované parametre do okna zobrazeného na obrázku 3. Potvrďte akciu. Bunka zobrazuje výsledok príkazu.
Obrázok 4 - Príklad vyhľadávania v jednoduchej tabuľke
Na obrázku vyššie farebné bunky označujú hodnotu produktu. Ak ste nezadali hodnotu pre triedenie, funkcia to automaticky zaobchádza ako s jednou.
Z tohto dôvodu bude postup vyhľadávania zastavený až po dosiahnutí riadku s hodnotou, ktorej počet už presahuje hľadaný objekt.
Uvažujme o ďalšom príklade použitia funkcie, s ktorým sa často stretávame pri skutočnej práci s cenníkmi a zoznamami názvov produktov.
V prípade, že užívateľ zadá, že posledný prvok v zátvorke je nula, voľba: skontroluje prvý stĺpec v danom rozsahu poľa.
Vyhľadávanie sa automaticky zastaví, akonáhle dôjde k zhode medzi parametrom „ČO“ a názvom produktu.
Ak tabuľka neobsahuje identifikátor, ktorý ste zadali pre názov produktu, vyhľadávanie VLOOKUP vráti hodnotu „N / A“, čo znamená, že pre dané číslo neexistuje žiadna položka.
Obrázok 5 - druhý príklad VLOOKUP
Kedy použiť VLOOKUP?
Vyššie uvedené sú dva spôsoby použitia VLOOKUP.
Prvá variácia VLOOKUP vhodné pre nasledujúce prípady:
Prvý pravopisný variant VLOOKUP nemôže nájsť položku, ak sa nenašla hodnota menšia alebo rovná hľadanej . Výsledok bude v bunke vrátený iba „N / A“.
Druhá možnosť pre VLOOKUP (pre triedenie zadaním „0“) sa používa pre veľké tabuľky, v ktorých sú pre niekoľko buniek rovnaké názvy.
VLOOKUP uľahčí prácu s údajmi, pretože vráti prvý nájdený riadok.
V reálnom živote sa táto možnosť používa, keď potrebujete vyhľadávať v danom rozsahu - nemusí sa zhodovať s celou veľkosťou tabuľky.
V objektoch hárkov, ktoré obsahujú rôzne druhy hodnôt, vám VLOOKUP pomôže nájsť textové reťazce.
Obrázok 6 - príklad nájdenia textovej hodnoty
VLOOKUP je užitočný, keď potrebujete odstrániť veľa ďalších medzier. Funkcia rýchlo nájde všetky mená s medzerami a môžete ich rýchlo vymazať. Príklad:
Obrázok 7 - VLOOKUP pri odstraňovaní medzier
VYHĽADÁVANIE VLOOKUP
Pri práci s veľkými množinami údajov môže byť program Excel príliš pomalý. Na starších zariadeniach tabuľka niekedy dokonca visí kvôli príliš pomalému vyhľadávaniu pomocou VLOOKUP.
Ak máte na jednom hárku dokumentu niekoľko tisíc vzorcov, je lepšie sa zoradiť podľa prvého stĺpca.
To zlepšuje celkový výkon vyhľadávania až o 400% - 500%.
Tento rozdiel v rýchlosti vykonávania rôznych typov VLOOKUPU je v tom, že pre akýkoľvek počítačový program je oveľa jednoduchšie pracovať s už zoradenými údajmi vykonaním binárneho vyhľadávania.
Ako používať funkciu VLOOKUP v programe Excel
Podrobná ukážka použitia funkcie VLOOKUP na prepojenie dvoch tabuliek, t.j. nahradenie údajov z jednej tabuľky do druhej
V anglickej verzii sa táto funkcia nazýva VLOOKUP - znamená vertikálne zobrazenie. K dispozícii je tiež funkcia HLOOKUP, ktorá sa zameriava na horizontálne sledovanie. Funkcia VLOOKUP sa v zásade používa na načítanie údajov z jednej tabuľky do druhej a dá sa tiež použiť na porovnanie stĺpcov v dvoch rôznych tabuľkách.
Aby sme lepšie porozumeli všetkým vlastnostiam práce s funkciou, pouvažujme nad jednoduchými príkladmi.
Máme dva stoly. Prvá tabuľka obsahuje údaje o objednávkach na jeden deň. Zaznamenáva sa v ňom názov produktu a množstvo produktov predávaných po žreboch. Druhá tabuľka obsahuje informácie o cenách výrobkov. Úlohou je vypočítať celkový výnos z každého produktu za deň.
Na vyriešenie problému je potrebné zvýšiť ceny tovaru z tabuľky „Cenník“ do stĺpca „Cena za kg“ tabuľky „Objednávky“. Tých. náhradné ceny, ktoré zodpovedajú každému produktu od dolnej tabuľky po hornú.
Excel má niekoľko možností, ako vyriešiť tento príklad, z ktorých jedna využíva funkciu VLOOKUP. Prejdeme do prvej bunky, kde je potrebné údaje nahradiť, a začneme písať funkciu, začíname ako obvykle znakom rovnosti.
Mala by sa zobraziť výzva, podľa ktorej k nej nastavíme argumenty. Ak dešifrujeme prácu VLOOKUPU z technického jazyka na normálny, potom funkcia VLOOKUP vyhľadá vybraný produkt z tabuľky „Objednávky“ v ľavom stĺpci tabuľky „Cenník“ a ak tento produkt nájde, zobrazí hodnotu jeho ceny, ktorá sa nachádza na rovnakom mieste. riadok ako nájdený produkt. Stručne povedané, vyzerá to ako snímka obrazovky nižšie.
Funkcia VLOOKUP má 4 argumenty. Prvým argumentom, ktorý nahradíme do vzorca, je požadovaná hodnota, ktorej cena musí byť uvedená v druhej tabuľke „Cenník“. Po napísaní názvu funkcie a pridaní úvodnej zátvorky kliknite na požadovanú bunku a pridajte ju ako argument. V mojom prípade ide o bunku „E4“ s hodnotou „Jablká“. Ďalej dáme oddeľovač - bodkočiarku.
Druhým argumentom je tabuľka, v ktorej hľadáme hodnotu a z ktorej vytiahneme cenu. Tu stačí zvoliť tabuľku „Cenník“ a je lepšie, aby ste sa nelepili na nepotrebné bunky (hlavička, prázdne bunky a len zbytočné informácie). Na koniec nezabudnite dať bodkočiarku.
Pretože v našom príklade je tabuľka, v ktorej sa vyhľadáva, na rovnakom hárku ako tá prvá, pri pretiahnutí vzorca do podkladových buniek sa zadaná tabuľka automaticky posunie nadol. Aby ste tomu zabránili, musíte ľavým tlačidlom myši vybrať rozsah tabuľky vo vzorci a stlačiť kláves F4 na klávesnici. K adresám buniek bude pridaný znak dolára a odkaz na tabuľku sa zmení z relatívneho na absolútny. Ďalej budeme analyzovať prípady, keď nebude potrebné vytvárať absolútne odkazy na bunky.
Tretím argumentom je číslo stĺpca v tabuľke „Cenník“, z ktorého chceme získať potrebnú hodnotu. V našom príklade je všetko jednoduché. V prvom stĺpci hľadáme hodnotu a z druhého stĺpca vytiahneme cenu nahor. Dali sme číslo 2 a za bodkočiarkou.
Posledný parameter sa volá „Interval scan“ - je to boolovský argument a môže nadobúdať iba 2 hodnoty: 0 alebo 1 (zap / vyp). Touto hodnotou definujeme presne číslo 0 alebo približne číslo 1, hľadáme meno v cenníku. V prípade názvov textov je lepšie použiť presné vyhľadávanie (číslo 0), pretože približné funguje v prípade buniek s číslami viac či menej presne. Zadajte intervalové zobrazenie ako 0 a zatvorte zátvorku. Potom stlačte kláves Enter, aby vzorec fungoval. Vo výsledku dostaneme bunku s cenou vytiahnutou z cenníkovej tabuľky.
V stĺpci „Celkové náklady“ predpisujeme jednoduchý vzorec na vynásobenie hmotnosti dávky cenou za kg.
Vyberte obe bunky a podržaním ľavého tlačidla myši v pravom dolnom rohu výberu potiahnite vzorce dole do všetkých zostávajúcich buniek.
Vo výsledku dostaneme cenu a konečné náklady na každú dávku. Pri ďalšom použití získaných údajov nezabudnite, že sú výsledkom vzorca, a nie hotovými hodnotami. Aby ste s nimi mohli v budúcnosti pracovať, musíte bunky s výslednými údajmi skopírovať a vložiť ich späť na svoje miesto pomocou špeciálneho prilepenia ako normálne hodnoty.
Zvažovali sme s vami jeden z prípadov, keď je potrebné vytiahnuť údaje z tabuľky, ktorá je na rovnakom hárku. Môžete samozrejme použiť tabuľky, ktoré sú na rôznych listoch, a to aj vtedy, keď sú otvorené v rôznych dokumentoch. V takom prípade bude druhý argument funkcie vyzerať trochu inak. V prípade iného hárku alebo dokumentu nie je absolútne nevyhnutné nastavovať absolútne referencie a je lepšie pridať tretí a štvrtý argument do vstupného poľa funkcie bez toho, aby ste sa vrátili späť na hárok, kde ste ich začali písať.
Ďalšou užitočnou aplikáciou VLOOKUP je použitie na porovnanie dvoch tabuliek s cieľom nájsť zhody alebo rozdiely. Napríklad ak odstránite niekoľko riadkov v jednej z tabuliek, v údajoch nebude úplná zhoda a v niektorých bunkách sa zobrazí nápis # N / A. Tento nápis označuje, že hodnoty z tabuľky s objednávkami sa nenašli v cenníkovej tabuľke, čo vedie k chybe.
Teraz zvážime prípad, keď je potrebné použiť intervalové zobrazenie, nie presné, s číslom 0, ale približné, s číslom 1. Táto metóda sa používa zriedka, existujú úlohy, v ktorých ju môžete potrebovať.
Vráťme sa k našej tabuľke s objednávkami a pokúsme sa určiť veľkosť dávky podľa jej hmotnosti. Budeme mať tabuľku s objednávkami a tabuľku s parametrami dávky.
Ďalej máme kritérium „od a do“, ktoré hovorí o veľkosti dávky. V príklade začneme od jeho hmotnosti. Napríklad, ak program Excel zobrazuje hmotnosť dávky 15 kg, bude pochopené, že ide o malú dávku.
Poďme na to, ako to funguje. Začneme písať funkciu VLOOKUP, ale na koniec vzorca dáme nie 0, ale 1. V argumente, kde je potrebné zadať tabuľku s bunkami, nebudeme určovať stĺpec s parametrom „Od a do“, pretože Excel takému záznamu nebude rozumieť. Namiesto toho zavedieme pole kritéria, ktoré určí začiatok a koniec rozsahu hmotnosti. Na konci stlačte kláves Enter a presuňte ho do všetkých buniek.
Niektoré prvky práce je potrebné vysvetliť, aby bolo zrejmé, čo sa tu stalo. Vidíme, že pre určitú váhu vytiahla funkcia z druhého stola určitú dávku. Aby sme logike porozumeli presnejšie, pozrime sa napríklad na váhu 15 kg. V tabuľke s veľkosťami šarží takéto kritérium neexistuje. Ale keď nastavíme intervalové vyhľadávanie na 1, funkcia vytiahne hodnotu, ktorá je najbližšie menšia. Pri hmotnosti dávky 15 kg VLOOKUP vytiahne z tabuľky hodnotu s kritériom 10. Ak je hmotnosť dávky 47 kg, funkcia vytiahne hodnotu s kritériom 30, ktoré bude najbližšie a najnižšie.
Pri práci s funkciou v tomto režime je potrebné vziať do úvahy veľmi dôležitý bod. Kritériá triedenia musia nevyhnutne postupovať vzostupne - od najmenších po najväčšie. Ak sú kritériá zoradené v opačnom poradí, funkcia nebude fungovať.
