VLOOKUP v programe Excel jednoducho vysvetlil AS Computertraining

6. apríla 2020 v programe Excel

VLOOKUP a veľké množstvo dát jednoducho patria k sebe. Ak veľa pracujete s programom Excel, budete vedieť, koľko obsahu môže obsahovať tabuľka. Prechádzanie každou jednotlivou položkou ručne a zadávanie ďalších informácií zaberie veľa času. Na uľahčenie vašej práce v programe Excel existuje takzvaný VLOOKUP. Tento vzorec sa nevzťahuje iba na pracovný hárok, ale napríklad tiež spája údaje z dvoch rôznych tabuliek a správne ich priraďuje. Táto príručka vám ukáže, ako presne funguje VLOOKUP programu Excel.

Mnoho používateľov si kladie otázku: Čo je to vlastne VLOOKUP? Funkcia VLOOKUP sa používa vždy, keď chcete porovnávať alebo sumarizovať viac tabuliek programu Excel.

The S. znamenať Kolmý, pretože Excel používa túto funkciu na hľadanie zadanej cieľovej hodnoty v prvom zvislom stĺpci oboch tabuliek.
Ako náprotivok existuje aj HLOOKUP, ktorý vyhľadáva zľava doprava.

Zjednodušene to znamená: VLOOKUP vám pomôže určiť príslušné údaje v stĺpci na základe určitých vyhľadávacích kritérií. Je to však iba prvý krok. Predtým, ako sa však pozrieme na ďalšie podrobnosti, poďme sa rýchlo pozrieť na rôzne spôsoby použitia programu VLOOKUP.

V zásade môžete použiť funkciu VLOOKUP v programe Excel v troch oblastiach:

Ak chcete skontrolovať, či je určitá hodnota v jednej tabuľke obsiahnutá aj v druhej.
Ak si chcete navzájom porovnať dve tabuľky.
Ak chcete rozšíriť obsah jednej tabuľky o údaje z druhej tabuľky.

Ako už bolo spomenuté, VLOOKUP je vzorec. To ukazuje program Excel, ktoré kritériá chcete použiť na hľadanie niečoho - vrátane toho, čo, kde a číslo stĺpca.

Ako každá funkcia v programe Excel, začína sa na = Podpísať a Názov príkazu, tak = VLOOKUP. Excel teraz vie, že musí niečo priradiť k vybranej bunke. Celý vzorec, ktorý zadáte ako VLOOKUP v programe Excel, má nasledujúcu štruktúru:

= VLOOKUP (kritérium vyhľadávania, matica, index, pravda/nepravda)

Vzorec je teda definovaný štyrmi samostatnými argumentmi:

Kritérium vyhľadávania Toto je hodnota, ktorá sa zobrazí v oboch zoznamoch na porovnanie. Pomocou kritéria vyhľadávania teda zadáte hodnotu v zozname 1, ktorý je tiež v zozname 2.
Matica Nachádza sa v zozname, ktorý sa má porovnávať (Zoznam 2), a je to oblasť, v ktorej sa nachádzajú informácie pre Zoznam 1.
Index stĺpca To signalizuje programu Excel, ktorý stĺpec sa má preniesť z matice do zoznamu 1 pomocou nástroja VLOOKUP.
Odkaz na oblasť Tieto informácie sú voliteľné. Ak ho chcete použiť, vyplní sa True alebo False.

Keď hovoríme o „true“, znamená to približnú zhodu vyhľadávacieho kritéria zo zoznamu 1 s kritériom matice zo zoznamu 2. Ak zadáte true, vzorec pre vyhľadávacie kritérium nepoužíva jeho presnú hodnotu. Namiesto toho je to aj najbližšia aproximácia. V prípade zoznamov výrobkov to môžu byť napríklad cenové stupnice alebo stupnice množstva.

Ak je naopak hodnota „false“, hodnota sa prenesie, iba ak je kritérium vyhľadávania presne vyjadrené. To by bol prípad čísel výrobkov alebo personálu.

Pre tých, ktorí s VLOOKUP nikdy nepracovali, znie vzorec spočiatku dosť komplikovane. Je za tým však veľmi jednoduchý princíp. Ak chcete vytvoriť SVYHLEDANIE v programe Excel, nezabudnite na nasledujúce body:

Zadajte vyhľadávaciu hodnotu - kritérium vyhľadávania, ktoré chcete vyhľadať.
Zúžte oblasť vyhľadávania.
Nezabudnite na číslo stĺpca návratovej hodnoty.
Ak chcete presný výsledok, na konci pridajte False. Na druhej strane, ak je približná zhoda postačujúca, použite True.

Aby bolo všetko jasnejšie, zostavte vzorec znova:

= VLOOKUP (kritérium vyhľadávania/hľadania; oblasť vyhľadávania/matica; číslo stĺpca/index; true/false)

Komplexné vzorce sú najlepšie ilustrované na príklade. V takom prípade sa pri úvode držíme zoznamu produktov, ku ktorému musia byť priradené ceny. K dispozícii sú dva stoly:

Červená tabuľka obsahuje čísla a názvy produktov.
Zelená tabuľka však zobrazuje ceny výrobkov.

Cieľom programu VLOOKUP je spojiť informácie z oboch tabuliek programu Excel do jednej.

Pri takom malom množstve dát by za normálnych okolností nebol problém použiť metódu kopírovania/vkladania. Čím viac údajov však tabuľka obsahuje, tým je proces zložitejší. Navyše často nastáva problém, že nie všetky informácie sú vždy úplné alebo správne. Excel VLOOKUP je v takýchto prípadoch oveľa elegantnejším riešením.

Vyberte bunku a zadajte kritérium vyhľadávania.
V tomto príklade klikneme na bunku v oblasti C2 a zadáme vzorec name = sreference a prvý argument. V tomto prípade by to bolo A2, pretože chceme vyhľadať číslo produktu 1234 v obidvoch tabuľkách programu Excel.

Definujte maticu uvedením, kde má program Excel hľadať súvisiace informácie.
Zadáme = sverweis (A2; $ F & 2: $ G $ 10, pretože chceme do okruhu vyhľadávania zahrnúť všetky čísla produktov a ceny. Pozor: Nezabudnite, že pred číslo poľa musíte dať znak dolára, inak vzorec nebude fungovať.

Zadajte index stĺpca, kde program Excel nájde informácie, ktoré potrebuje pre SVYHLEDANIE. V tomto prípade by to bolo v riadku 2, pretože tam je zodpovedajúca cena.
Takže zadajte = referencia (A2; $ F $ 2: $ G $ 10; 2).
Chcete použiť presné vyhľadávacie kritérium alebo stačí približná hodnota? V takom prípade by ste mali vyhľadať presné číslo produktu. Môžete zadať hodnotu false alebo hodnotu 0 pre hodnotu false.
Teraz kliknite na Enter.

Cena sa teraz zobrazuje v bunke. Je zrejmé, že jednotlivé ceny sa vzťahujú na príslušné čísla produktov. Napríklad ak výrobca zmení názov produktu, nie je to žiadny problém. Cena zostáva rovnaká vďaka Excel VLOOKUP.

V tomto príklade používame tabuľku so zoznamom zákazníkov so zodpovedajúcim predajom (červená tabuľka). V závislosti od dosiahnutého obratu dostane zákazník zľavu podľa stupnice zľavy (zelená tabuľka). Tabuľka 1 už bola rozšírená o stĺpec Zľava.

Vyberte bunku a zadajte kritérium vyhľadávania. Urobíte to tak, že do bunky C2 vložíte VYHĽADÁVANIE programu Excel. Kritériom vyhľadávania je obrat, ktorý je obsiahnutý ako hodnota v obidvoch zoznamoch a na ktorý je v zozname 2 prepojený príslušný zľavový kód.

Definujte maticu uvedením, kde má program Excel hľadať súvisiace informácie. V tomto prípade informačný zdroj alebo matica siaha od poľa H2 do 110. Pozor: Nezabudnite na znak dolára pred číslom poľa.
Pre SVYHLEDANIE zadajte index stĺpca Excel. Opäť vzniká otázka, v ktorom stĺpci Excel nájde zľavu pre zákazníka - stĺpec 2.
Zákazník B má obrat 310 000 €. V zozname zliav však vidíte, že zákazník dostane zľavu 2% iba vtedy, ak je obrat medzi 200 000 a 300 000 €. Teraz VLOOKUP musí tiež „čítať medzi riadkami“. Postačuje približné prepísanie. Referenčná oblasť nie je vyplnená - alebo je ako alternatíva zadané TRUE.
Kliknutím na Enter a vyplnením funkcie na všetkých údajových záznamoch sú zľavy viditeľné pre všetkých zákazníkov.