V tomto svete založenom na údajoch človek potrebuje rôzne nástroje na správu údajov. Dáta v reálnom čase sú obrovské a načítanie podrobností o konkrétnom údaji by bolo určite únavnou úlohou, ale s funkciou VLOOKUP v Excel , je možné túto úlohu dosiahnuť jediným príkazovým riadkom. V tomto článku sa dozviete o jednom z dôležitých Funkcie programu Excel tj funkcia VLOOKUP.
Predtým, ako sa posunieme ďalej, poďme sa rýchlo pozrieť na všetky témy, o ktorých sa tu diskutuje:
- Čo je VLOOKUP v programe Excel?
- Ako to funguje?
- Presná zhoda
- Približná zhoda
- Prvý zápas
- Citlivosť na veľké a malé písmená
- Chyby
- Obojsmerné vyhľadávanie
- Používanie zástupných znakov
- Viacero vyhľadávacích tabuliek
Čo je VLOOKUP v programe Excel?
V programe Excel je VLOOKUP a zabudovaná funkcia ktorý sa používa na vyhľadávanie a načítanie konkrétnych údajov z hárka programu Excel. V je skratka pre Vertical (Vertikálne). Ak chcete v programe Excel používať funkciu VLOOKUP, musia byť údaje usporiadané vertikálne. Táto funkcia je veľmi užitočná, ak máte obrovské množstvo údajov a bolo by prakticky nemožné manuálne vyhľadať niektoré konkrétne údaje.
Ako to funguje?
Funkcia VLOOKUP získava hodnotu, tj vyhľadávaciu hodnotu a začne ju vyhľadávať v stĺpci úplne vľavo. Keď sa nájde prvý výskyt vyhľadávanej hodnoty, začne sa pohybovať priamo v danom riadku a vráti hodnotu zo stĺpca, ktorý určíte. Túto funkciu je možné použiť na vrátenie presných aj približných zhôd (predvolená zhoda je približná zhoda).
Syntax:
Syntax tejto funkcie je nasledovná:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
kde,
- vyhľadávacia_hodnota je hodnota, na ktorú si treba dávať pozor v prvom stĺpci danej tabuľky
- table_index je tabuľka, z ktorej sa majú údaje načítať
- col_index_num je stĺpec, z ktorého sa má načítať hodnota
- range_lookup je logická hodnota, ktorá určuje, či má byť hľadaná hodnota dokonalá alebo približná zhoda ( PRAVDA nájde najbližšiu zhodu NEPRAVDA kontroluje presnú zhodu)
Presná zhoda:
Ak chcete, aby funkcia VLOOKUP hľadala presnú zhodu vyhľadávanej hodnoty, budete musieť nastaviť range_lookup hodnota na FALSE. Zoznámte sa s nasledujúcim príkladom, ktorý je tabuľkou obsahujúcou podrobnosti o zamestnancoch:
Ak chcete hľadať označenie niektorého z týchto zamestnancov, môžete postupovať takto:
- Vyberte bunku, kde chcete zobraziť výstup, a potom zadajte znak „=“
- Použite funkciu VLOOKUP a zadajte vyhľadávacia_hodnota (Tu to bude ID zamestnanca)
- Potom zadajte ďalšie parametre, tj table_array , col_index_num a nastaviť range_lookup hodnota na FALSE
- Preto bude funkcia a jej parametre: = VLOOKUP (104, A1: D8, 3, FALSE)
Funkcia VLOOKUP začne hľadať ID zamestnanca 104 a potom sa posunie doprava v riadku, kde sa nachádza hodnota. Pokračuje do col_index_num a vráti hodnotu prítomnú na tejto pozícii.
čo je vizualizácia údajov v tablo
Približná zhoda:
Táto vlastnosť funkcie VLOOKUP vám umožňuje načítať hodnoty, aj keď nemáte presnú zhodu s hodnotou loopup_value. Ako už bolo spomenuté vyššie, aby VLOOKUP hľadal približnú zhodu, budete musieť nastaviť range_lookup hodnota na TRUE. Prezrite si nasledujúci príklad, kde sú známky mapované spolu s ich známkami a triedou, do ktorej patria.
- Rovnakým spôsobom postupujte rovnako ako pri presnej zhode
- Namiesto hodnoty range_lookup použite namiesto FALSE hodnotu TRUE
- Preto bude funkcia spolu s jej parametrami: = VLOOKUP (55, A12: C15, 3, TRUE)
V tabuľke, ktorá je zoradená vzostupne, program VLOOKUP začne hľadať približnú zhodu a zastaví sa na nasledujúcej najväčšej hodnote, ktorá je menšia ako zadaná vyhľadávacia hodnota. Potom sa presunie doprava v danom riadku a vráti hodnotu zo zadaného stĺpca. Vo vyššie uvedenom príklade je vyhľadávacia hodnota 55 a ďalšia najväčšia vyhľadávacia hodnota v prvom stĺpci je 40. Preto je výstupom druhá trieda.
Prvý zápas:
V prípade, že máte tabuľku, ktorá sa skladá z viacerých vyhľadávacích hodnôt, VLOOKUP sa zastaví pri jej prvej zhode a načíta hodnotu z tohto riadku v zadanom stĺpci.
Pozrite sa na obrázok nižšie:
ID 105 sa opakuje a keď je vyhľadávacia hodnota zadaná ako 105, funkcia VLOOKUP vrátila hodnotu z riadku, ktorý má prvý výskyt vyhľadávacej hodnoty.
Citlivosť prípadu:
Vo funkcii VLOOKUP sa nerozlišujú malé a veľké písmená. V prípade, že máte vyhľadávaciu hodnotu, ktorá je písaná veľkými písmenami, a hodnota uvedená v tabuľke je malá, funkcia VLOOKUP stále načíta hodnotu z riadku, v ktorom je uvedená hodnota. Pozrite sa na obrázok nižšie:
Ako vidíte, hodnota, ktorú som určil ako parameter, je „RAFA“, zatiaľ čo hodnota uvedená v tabuľke je „Rafa“, ale VLOOKUP stále vrátil zadanú hodnotu. Ak máte presnú zhodu aj s prípadom, funkcia VLOOKUP vráti prvú zhodu vyhľadávanej hodnoty bez ohľadu na použitý prípad. Pozrite sa na obrázok nižšie:
Chyby:
Je prirodzené, že pri využívaní funkcií narazíme na chyby. Podobne sa môžete stretnúť s chybami aj pri použití funkcie VLOOKUP a niektoré bežné chyby sú:
- #NÁZOV
- # N / A
- #REF
- #HODNOTA
Chyba #NAME:
Táto chyba vás má v podstate informovať, že ste urobili chybu v syntaxi. Aby ste sa vyhli syntaktickým chybám, je lepšie pre každú funkciu použiť Sprievodcu funkciami, ktorý poskytuje Excel. Sprievodca funkciami vám pomôže s informáciami o všetkých parametroch a typoch hodnôt, ktoré musíte zadať. Pozrite sa na obrázok nižšie:
Ako vidíte, Sprievodca funkciami vás informuje o zadaní ľubovoľného typu hodnoty namiesto parametra lookup_value a poskytuje tiež ich stručný popis. Podobne, keď vyberiete ďalšie parametre, zobrazia sa aj informácie o nich.
# N / A chyba:
Táto chyba sa vráti v prípade, že sa pre danú vyhľadávaciu hodnotu nenájde zhoda. Napríklad, ak zadám „AFA“ namiesto „RAFA“, zobrazí sa mi chyba # N / A.
ako hlboko kopírovať v
Aby ste mohli definovať chybové hlásenie pre vyššie uvedené dve chyby, môžete využiť funkciu IFNA. Napríklad:
#REF Chyba:
Táto chyba sa vyskytla, keď ste uviedli odkaz na stĺpec, ktorý nie je k dispozícii v tabuľke.
Chyba #VALUE:
Táto chyba sa vyskytla, keď ste do parametrov vložili nesprávne hodnoty alebo ste vynechali niektoré povinné parametre.
Obojsmerné vyhľadávanie:
Obojsmerné vyhľadávanie označuje načítanie hodnoty z dvojrozmernej tabuľky z ktorejkoľvek bunky odkazovanej tabuľky. Aby ste mohli vykonať obojsmerné vyhľadávanie pomocou VLOOKUP, budete musieť spolu s ním použiť funkciu MATCH.
Syntax MATCH je nasledovná:
MATCH (lookup_value, lookup_array, match_type)
- vyhľadávacia_hodnota je hodnota, ktorá sa má vyhľadať
- lookup_array je rozsah buniek, ktoré pozostávajú z vyhľadávacích hodnôt
- typ_zhody môže byť číslo, tj. buď 0, 1, alebo -1 predstavujúce presnú zhodu, menšie ako a väčšie ako v uvedenom poradí
Namiesto použitia pevne zakódovaných hodnôt s funkciou VLOOKUP môžete vykonať dynamické obchádzanie odkazov na bunky. Uvažujme o nasledujúcom príklade:
Ako môžete vidieť na obrázku vyššie, funkcia VLOOKUP vezme odkaz na bunku ako F6 pre vyhľadávaciu hodnotu a hodnota indexu stĺpca je určená funkciou MATCH. Keď urobíte zmeny v ktorejkoľvek z týchto hodnôt, výstup sa tiež príslušne zmení. Pozrite sa na obrázok nižšie, kde som zmenil hodnotu prítomnú vo F6 z Chrisa na Lea a podľa toho sa aktualizoval aj výstup:
V prípade, že zmením hodnotu G5 alebo F6 aj G5, bude tento vzorec fungovať zodpovedajúcim spôsobom a zobrazí príslušné výsledky.
Môžete tiež vytvoriť rozbaľovacie zoznamy, vďaka ktorým bude úloha so zmenami hodnôt veľmi užitočná. Vo vyššie uvedenom príklade by to malo byť vykonané pre F6 a G5. Rozbaľovacie zoznamy môžete vytvoriť takto:
- Na karte s páskou vyberte položku Údaje
- V skupine Dátové nástroje vyberte položku Overenie údajov
- Otvorte tablu Nastavenia a v časti Povoliť vyberte možnosť Zoznam
- Zadajte pole zoznamu zdrojov
Po vytvorení rozbaľovacieho zoznamu to vyzerá takto:
Používanie zástupných znakov:
V prípade, že nepoznáte presnú vyhľadávaciu hodnotu, ale iba jej časť, môžete použiť zástupné znaky. V programe Excel predstavuje symbol „*“ zástupný znak. Tento symbol informuje program Excel, že je potrebné vyhľadať postupnosť, ktorá nasleduje pred, za alebo medzi a pred nimi alebo za nimi môže byť ľubovoľný počet znakov. Napríklad v tabuľke, ktorú som vytvoril, ak zadáte „erg“ spolu s divokými kartami na oboch stranách, funkcia VLOOKUP vráti výstup pre „Sergio“, ako je uvedené nižšie:
Viacero vyhľadávacích tabuliek:
V prípade, že máte viac vyhľadávacích tabuliek, môžete spolu s ňou použiť funkciu IF, aby ste mohli vyhľadať niektorú z tabuliek na základe danej podmienky. Napríklad ak existuje tabuľka s údajmi o dvoch supermarketoch a potrebujete zistiť zisk každého z nich na základe predaja, môžete postupovať takto:
Vytvorte hlavnú tabuľku takto:
rámec riadený údajmi v príklade webového ovládača selénu
Potom vytvorte dve tabuľky, z ktorých sa musí získať zisk.
Po dokončení vytvorte pomenovaný rozsah pre každú z novovytvorených tabuliek. Pri vytváraní pomenovaného rozsahu postupujte podľa krokov uvedených nižšie:
- Vyberte tabuľku celú tabuľku, ktorej chcete priradiť názov
- Na karte na páse kariet vyberte položku Vzorce a potom v skupine Definované mená vyberte položku Definovať názov
- Zobrazí sa nasledujúce dialógové okno
- Uveďte akékoľvek meno podľa vášho výberu
- Kliknite na tlačidlo OK
Keď to urobíte pre obe tabuľky, môžete tieto pomenované rozsahy vo funkcii IF použiť takto:
Ako vidíte, VLOOKUP vrátil príslušné hodnoty na vyplnenie stĺpca Zisk podľa toho, do ktorého supermarketu patria. Namiesto toho, aby som do každej bunky stĺpca Zisk písal vzorec, mám iba skopíroval vzorec v záujme úspory času a energie.
Týmto sa dostávame na koniec tohto článku o VÝHĽADU v programe Excel. Dúfam, že máte jasno vo všetkom, čo bolo s vami zdieľané. Určite cvičte čo najviac a obráťte sa na svoje skúsenosti.
Máte na nás otázku? Uveďte to v sekcii komentárov tohto blogu „VLOOKUP in Excel“ a my sa vám ozveme čo najskôr.
Ak sa chcete dozvedieť viac o akýchkoľvek trendových technológiách a ich rôznych aplikáciách, môžete sa zaregistrovať naživo s nepretržitou podporou a doživotným prístupom.