Tabuľky vytvorené v Exceli nie sú vždy charakteristické tým, že názvy kategórií údajov by mali byť definované len v hlavičkách stĺpcov. Niekedy pri analýze údajov tabuľky máme možnosť použiť hlavičky stĺpcov aj názvy riadkov, ktoré sú v prvom stĺpci.

Príklad vzorca s funkciami VLOOKUP a MATCH

Príklad tabuľky bonusovej správy je uvedený nižšie na obrázku:

Účelom tejto tabuľky je nájsť zodpovedajúce hodnoty poistného v rozsahu B5: K11 na základe určitej výšky tržieb a obchodov s minimálnymi alebo maximálnymi limitmi pre platbu poistného. Problém nastáva v automatickom určení výšky bonusu, s ktorým môže zamestnanec počítať pri prekročení určitej hranice príjmov. Keďže nie je jasne definovaná jedna výška platby poistného pre každú pravdepodobnú výšku príjmu. Existujú len stropy a stropy na výšku prémií pre každý obchod.

Potrebujeme napríklad, aby program automaticky určil, aký je možný minimálny bonus pre predajcu z 3. obchodu, ktorého tržby presiahli úroveň 370-tisíc.

Pre to:

1. Do bunky B14 zadajte sumu výnosu: 370 000.
2. Do bunky B15 zadajte číslo obchodu: 3.
3. Do bunky B16 zadajte nasledujúci vzorec:

V dôsledku toho bola určená spodná hranica prémie pre obchod č. 3 s tržbami viac> 370 000, ale menej<400 000.



Batyanov Denis Ako hosťujúci prispievateľ v tomto príspevku vysvetľuje, ako nájsť údaje v jednej tabuľke Excel a extrahovať ich do inej, a tiež odhalí všetky tajomstvá funkcie vertikálneho prezerania.

Pomocou funkcie COLUMN zadajte stĺpec, ktorý sa má extrahovať

Ak tabuľka, do ktorej získavate údaje pomocou funkcie VLOOKUP, má rovnakú štruktúru ako vyhľadávacia tabuľka, ale obsahuje len menej riadkov, môžete použiť funkciu COLUMN () vo funkcii VLOOKUP na automatický výpočet čísiel stĺpcov, ktoré sa majú načítať. V tomto prípade budú všetky vzorce VLOOKUP rovnaké (upravené pre prvý parameter, ktorý sa automaticky zmení)! Všimnite si, že prvý parameter má absolútnu súradnicu stĺpca.

Vytvorenie zloženého kľúča pomocou & "|" &

Ak je potrebné vyhľadávať podľa niekoľkých stĺpcov súčasne, je potrebné vytvoriť zložený kľúč na vyhľadávanie. Ak by vrátená hodnota nebola textová (ako je to v prípade poľa „Kód“), ale číselná, potom by sa na to hodil pohodlnejší vzorec SUMIFS a kľúč zloženého stĺpca by nebol vôbec potrebný.

Toto je môj prvý článok pre Lifehacker. Ak sa vám to páčilo, pozývam vás na návštevu mojej stránky a tiež si rád v komentároch prečítam vaše tajomstvá používania funkcie VLOOKUP a podobne. Vďaka. :)

Dobrý deň, priatelia. Ako často musíte hľadať zhodu pre danú hodnotu v excelovej tabuľke? Napríklad potrebujete nájsť adresu osoby v adresári alebo v cenníku - cena produktu. Ak sa vyskytnú takéto úlohy - tento príspevok je pre vás!

Tieto procedúry robím každý deň a bez nižšie popísaných funkcií by som to mal naozaj ťažké. Berte na vedomie a aplikujte ich vo svojej práci!

Excelové vyhľadávanie v tabuľke, funkcie VLOOKUP a HLOOKUP

Úlohu týchto funkcií v živote bežného užívateľa možno len ťažko preceňovať. Teraz môžete ľahko nájsť vhodný záznam v tabuľke údajov a vrátiť zodpovedajúcu hodnotu.

Syntax funkcie VLOOKUP je: = VLOOKUP ( Lookup_value; table_for_search; číslo_stĺpca na zobrazenie; [match_type]). Zvážte argumenty:

• Požadovaná hodnota- hodnota, ktorú budeme hľadať. Toto je povinný argument;
• Prehľadávať tabuľku- pole buniek, v ktorých sa bude hľadať. Stĺpec s požadovanými hodnotami musí byť prvý v tomto poli. Toto je tiež povinný argument;
• Zobrazené číslo stĺpca- poradové číslo stĺpca (začínajúc prvým v poli), z ktorého funkcia vypíše údaje, ak sa požadované hodnoty zhodujú. Požadovaný argument;
• Zodpovedajúci typ- zvoľte "1" (alebo "TRUE") pre neprísnu zhodu, "0" ("FALSE") pre úplnú zhodu. Argument je voliteľný, ak ho vynecháte, vykoná sa vyhľadávanie voľný zápas.

Nájdenie presnej zhody pomocou funkcie VLOOKUP

Pozrime sa na príklad toho, ako funguje funkcia VLOOKUP, keď je typ zhody „FALSE“ a hľadá sa presná zhoda. V poli B5: E10 je uvedený dlhodobý majetok určitej spoločnosti, jeho účtovná hodnota, inventárne číslo a umiestnenie. Bunka B2 obsahuje názov, pre ktorý je potrebné nájsť inventárne číslo v tabuľke a umiestniť ho do bunky C2.

Funkcia VLOOKUP v Exceli

Napíšeme vzorec: = VLOOKUP (B2; B5: E10; 3; FALSE).

Tu prvý argument naznačuje, že v tabuľke treba hľadať hodnotu z bunky B2, t.j. slovo "Fax". Druhý argument hovorí, že tabuľka na vyhľadávanie je v rozsahu B5: E10 a treba hľadať v prvom stĺpci slovo "Fax", t.j. v poli B5: B10. Tretí argument hovorí programu, že výsledok výpočtu je obsiahnutý v treťom stĺpci poľa, t.j. D5: D10. Štvrtý argument je FALSE, t.j. vyžaduje sa úplná zhoda.

Funkcia teda prijme riadok „Fax“ z bunky B2 a bude ho hľadať v poli B5: B10 zhora nadol. Po nájdení zhody (riadok 8) funkcia vráti zodpovedajúcu hodnotu zo stĺpca D, t.j. obsah D8. Presne toto sme potrebovali, problém je vyriešený.

Ak sa požadovaná hodnota nenájde, funkcia sa vráti.

Nájdite nejasné zhody pomocou funkcie VLOOKUP

Vďaka tejto možnosti v práci VLOOKUP sa môžeme vyhnúť zložitým vzorcom, aby sme našli požadovaný výsledok.

Pole B5: C12 zobrazuje úrokové sadzby úverov v závislosti od výšky úveru. V bunke B2 uvádzame výšku pôžičky a chceme získať sadzbu v C2 pre takúto transakciu. Úloha je ťažká, pretože súčet môže byť akýkoľvek a je nepravdepodobné, že by sa zhodoval s tými, ktoré sú uvedené v poli; vyhľadávanie podľa presnej zhody nie je vhodné:

Potom si zapíšeme vzorec pre nenáročné vyhľadávanie: = VLOOKUP (B2, B5: C12, 2, TRUE)... Teraz program vyhľadá najbližšie menšie zo všetkých údajov uvedených v stĺpci B. To znamená, že pre sumu 8 000 sa vyberie hodnota 5 000 a zobrazí sa zodpovedajúce percento.

Plynulé vyhľadávanie VLOOKUP v Exceli

Aby funkcia fungovala správne, musíte prvý stĺpec tabuľky zoradiť vzostupne. V opačnom prípade môže poskytnúť chybný výsledok.

Funkcia HLOOKUP má rovnakú syntax ako funkcia VLOOKUP, ale nehľadá výsledok v stĺpcoch, ale v riadkoch. To znamená, že prehľadá tabuľky nie zhora nadol, ale zľava doprava a zobrazí zadané číslo riadku, nie stĺpec.

Vyhľadanie údajov pomocou funkcie BROWSE

Funkcia LOOKUP funguje podobne ako funkcia VLOOKUP, má však inú syntax. Používam ho vtedy, keď dátová tabuľka obsahuje niekoľko desiatok stĺpcov a pre použitie VLOOKUP je potrebné dodatočne vypočítať číslo zobrazeného stĺpca. V takýchto prípadoch funkcia LOOKUP uľahčuje úlohu. Takže syntax je: = VYHĽADAŤ ( Lookup_value; Array_for_search; Array_to_ display) :

• Požadovaná hodnota- údaje alebo prepojenie na údaje, ktoré sa majú vyhľadať;
• Vyhľadávacie pole- jeden riadok alebo stĺpec, v ktorom hľadáme podobnú hodnotu. Toto pole musíme triediť vo vzostupnom poradí;
• Pole na zobrazenie- rozsah obsahujúci údaje na zobrazenie výsledkov. Prirodzene, musí mať rovnakú veľkosť ako vyhľadávacie pole.

S týmto zápisom dávate nerelatívny odkaz na pole výsledkov. A ty ukazuješ priamo na to, t.j. nie je potrebné vopred počítať číslo výstupného stĺpca. Funkciu LOOKUP v prvom príklade použijeme pre funkciu VLOOKUP (dlhodobý majetok, inventárne čísla): = VYHĽADAŤ (B2, B5: B10, D5: D10)... Problém bol úspešne vyriešený!

Funkcia LOOKUP v programe Microsoft Excel

Hľadajte podľa relatívnych súradníc. Funkcie SEARCH a INDEX

Ďalším spôsobom vyhľadávania údajov je spojenie funkcií SEARCH a INDEX.

Prvý z nich sa používa na vyhľadanie hodnoty v poli a získanie jej poradového čísla: SEARCH ( Lookup_value; Sledované_pole; [Zodpovedajúci typ] ). Argumenty funkcie:

• Požadovaná hodnota- požadovaný argument
• Pole na pozeranie- jeden riadok alebo stĺpec, v ktorom hľadáme zhodu. Požadovaný argument
• Zodpovedajúci typ- zadajte "0" na vyhľadanie presnej zhody, "1" - najbližšia nižšia, "-1" - najbližšia vyššia. Keďže funkcia hľadá od začiatku zoznamu až po koniec, pri hľadaní najbližšieho menšieho zoraďte stĺpec vyhľadávania zostupne. A keď budete hľadať viac, zoraďte ich vo vzostupnom poradí.

Pozícia požadovanej hodnoty bola nájdená, teraz ju môžete zobraziť na obrazovke pomocou funkcie INDEX( Array; Poradové číslo; [Column_number]) :

• Pole- argument označuje, z ktorého poľa buniek vybrať hodnotu
• Poradové číslo- zadajte poradové číslo riadku (začínajúceho od prvej bunky poľa), ktorý chcete zobraziť. Tu môžete zapísať hodnotu ručne, alebo použiť výsledok výpočtu inej funkcie. Napríklad HĽADAŤ.
• Číslo stĺpca- voliteľný argument, zadaný, ak pole pozostáva z niekoľkých stĺpcov. Ak sa vynechá, vzorec použije prvý stĺpec v tabuľke.

Teraz skombinujme tieto funkcie, aby sme dosiahli výsledok:

Funkcie HĽADAŤ a INDEX v Exceli

Toto sú spôsoby, ako nájsť a zobraziť údaje v Exceli. Ďalej ich môžete použiť vo výpočtoch, použiť ich v prezentácii, vykonávať s nimi operácie, špecifikovať ďalšie funkcie ako argumenty atď.

Cítite, ako vaše vedomosti a zručnosti rastú a silnejú? Potom neprestávajte, čítajte ďalej! V ďalšom príspevku zvážime: bude to ťažké a zaujímavé!

Microsoft Excel je skvelý nástroj na prácu s údajmi. Možnosti sú obrovské, potenciál je v skutočnosti málo študovaný, pretože pravidlá pre písanie príkazov spôsobujú ťažkosti aj profesionálom. Ale ak tvrdo pracujete, ukáže sa, že tu dokážete neuveriteľné veci. Funkcia Excel VLOOKUP je jedným z nástrojov na spracovanie dát a teraz si o nej povieme.

Čo je VLOOKUP v Exceli - rozsah

Pri spracovaní niekoľkých tabuliek, ktoré sa často nachádzajú v rôznych knihách, je potrebné prenášať údaje z jednej do druhej, pričom je potrebné zabezpečiť, aby hodnoty nestratili svoj význam a proces bol automatizovaný. Mechanizmus v exceli by mal fungovať jednoducho a rýchlo.

Napríklad máme spoločnosť. Pracuje tam Ivan Ivanovič. Na jednom hárku je uložená hodnota jeho mzdy, na inom suma peňazí, ktorú mu účtovné oddelenie zadrží z platu za pokutu. Je potrebné spojiť všetky hodnoty do jedného dokumentu. Ďalším príkladom sú dve tabuľky: ceny a sklad. V jednej sú uvedené náklady na vreckovky, v druhej ich množstvo. Je potrebné dať dohromady množstvo a cenu na jednom mieste.

V prípadoch, keď sú v podniku iba dvaja alebo traja zamestnanci alebo existuje až tucet tovaru, môžete robiť všetko ručne. S náležitou starostlivosťou bude človek pracovať bez chýb. Ak sú však hodnoty, ktoré sa majú spracovať, napríklad tisíc, je potrebná automatizácia práce. Na tento účel má Excel funkciu VLOOKUP (VLOOKUP).

Príklady pre názornosť: v tabuľkách 1, 2 - počiatočné údaje, tabuľka 3 - čo treba získať.

Tabuľka počiatočných údajov 1

Tabuľka kombinovaných údajov 3

CELÉ MENO.	Z.P.	Dobre
Ivanov	20 000 ₽	38 000 ₽
Petrov	19 000 ₽	12 000 ₽
Sidorov	21 000 ₽	200 ₽

Funkcia VLOOKUP v Exceli - ako používať

Aby sa tabuľka 1 dostala do finálnej podoby, zadáme do nej nadpis stĺpca, napríklad „Penalizácia“. V skutočnosti je to voliteľné, môžete napísať ľubovoľný text alebo ho nechať prázdne. Funkcia bude fungovať aj kliknutím myšou do poľa, kde by sa mala objaviť hodnota nájdená v inej tabuľke.

Teraz musíme zavolať funkciu. Dá sa to urobiť rôznymi spôsobmi:

Je potrebné vyplniť hodnoty pre funkciu VLOOKUP

Výsledok je evidentný – v tabuľke 3 (pozri vyššie).

VLOOKUP - návod na prácu s dvoma podmienkami

V situáciách, keď je potrebné zadať kritériá vyhľadávania nie podľa jednej podmienky, ale podľa niekoľkých - dvoch stĺpcov naraz, možno na prácu použiť aj funkciu VLOOKUP. Na tento účel musia byť dokončené počiatočné údaje.

Príklad, do tabuľky 4 je potrebné vložiť cenu z tabuľky 5.

Tabuľka vlastností telefónu 4

Príklad je vybraný na telefónoch, ale je zrejmé, že údaje môžu byť úplne akékoľvek. Ako môžete vidieť z tabuliek, značky telefónov sa nelíšia, líšia sa však RAM a Fotoaparát. Na vytvorenie súhrnných údajov musíme vybrať telefóny podľa značky a pamäte RAM. Aby funkcia VLOOKUP fungovala na niekoľkých podmienkach, musíte skombinovať stĺpce s podmienkami.

Pridajte stĺpec úplne vľavo. Nazvime to napríklad „Únia“. V prvej bunke hodnôt máme B 2, napíšeme konštrukciu "= B 2 & C 2". Rozmnožujeme sa myšou. Ukázalo sa to ako v tabuľke 6.

Tabuľka charakteristík telefónu 6

únie	názov	RAM	cena
ZTE 0,5	ZTE	0,5	1 990 ₽
ZTE 1	ZTE	1	3 099 ₽
DNS1	DNS	1	3 100 ₽
DNS 0,5	DNS	0,5	2 240 ₽
Alcatel 1	Alcatel	1	4 500 ₽
Alcatel 256	Alcatel	256	450 ₽

Tabuľku 5 spracovávame rovnakým spôsobom. Potom pomocou funkcie VLOOKUP vyhľadáme jednu podmienku. Podmienkou sú údaje z kombinovaných stĺpcov. Nezabudnite, že sa zmení číslo stĺpca, z ktorého pochádzajú údaje vo funkcii VLOOKUP. Po aplikácii funkcie získate výber podľa dvoch podmienok. Namiesto susedných stĺpcov môžete kombinovať stĺpce so značkou telefónu a fotoaparátom.

Pozrite si video tutoriál o tom, ako používať funkciu VLOOKUP v Exceli pre figuríny:

Funkcia VLOOKUP v Exceli sa ľahko používa a možnosti spracovania údajov sú obrovské.

Dobrý deň, milý čitateľ!

V tomto článku chcem popísať pravdepodobne jednu z najužitočnejších funkcií v Exceli – funkciu VLOOKUP. O funkcii sa dá povedať veľa, ale hneď prvá vec, ktorá stojí za zmienku, je, že funkcia je jednou z najzložitejších a najmenej zrozumiteľných.

V tomto článku sa pokúsim jednoduchým a prístupným jazykom opísať, ako funguje funkcia VLOOKUP, ako aj ukázať jej vlastnosti, popis a syntax s príkladmi.

Takže, čo presne je táto funkcia, čo robí a ako je napísaná. Najjednoduchšie vysvetlenie, o práci funkcie VLOOKUP, hľadá v zozname hodnôt, podľa zadaného kritéria, jedinečný identifikátor, určité informácie, ktoré sú s ním spojené.

Ak dešifrujete názov funkcie VLOOKUP, potom s prvým písmenom bude jasné, ako táto funkcia funguje, B znamená "Vertikálne", to znamená, že hľadá hodnoty v, ale pre horizontálne zoznamy budeme mať. Funkcia VLOOKUP nám bola prvýkrát dostupná s Excelom 2000.
Funkcia VLOOKUP v Exceli má nasledujúcu syntax:

= VLOOKUP (_hľadaná hodnota _; _ tabuľka_; _ číslo stĺpca_; _ [intervalové zobrazenie] _), kde:

• vyhľadávaná_hodnota- toto je presne tá hodnota, ktorú musíme vyhľadať, a môže to byť ľubovoľná hodnota: číslo, dátum, text, odkaz na bunku, ktorá obsahuje požadovanú hodnotu alebo hodnotu získanú iným vzorcom;
• tabuľky- ide o dva alebo viac stĺpcov s rôznymi údajmi, mimochodom, funkcia neberie do úvahy veľkosť písmen pri vyhľadávaní;
• číslo_stĺpca- toto je číslo stĺpca v zadanom rozsahu, z ktorého sa získa hodnota, ktorá je v nájdenom riadku;
• interval_view- tento parameter určuje, čo presne budeme hľadať, pre presnú zhodu bude argument rovnaký FALSE alebo približná zhoda, argument sa stáva PRAVDA... Tento parameter je voliteľný, no napriek tomu dôležitý. V príkladoch nižšie ukážem, ako vytvoriť presné a približné vzorce zhody.

Teraz sa na príklade pozrime na to, ako vyhľadať niektoré údaje na inom hárku, pretože v praxi sa funkcia VLOOKUP používa len veľmi zriedkavo na vyhľadávanie v aktuálnom hárku:

VLOOKUP ("GM"; $ A $ 5: $ B $ 10; 2)

Vzorec hľadá text „GM“ v stĺpci A na aktuálnom hárku.
Poradte! Pri použití argumentu "tabuľka" je žiaduce použiť možnosť ako (toto je adresa bunky so znakom $). V tomto prípade sa rozsah vyhľadávania zafixuje a pri kopírovaní vzorca sa nezmení.

Pozrime sa na príklad hľadania hodnôt, ako funguje funkcia VLOOKUP v inom zošite:

VLOOKUP ("GM"; [Cesta k súboru] Základňa! A2: B10; 2)

Ako vidíte, nič zložité, hádka sa len skomplikovala. "stôl", teraz je k nemu pripojený odkaz na súbor s jeho názvom.

Poradte! Je lepšie zadať vzorec s dvoma súbormi, v jednom zadáte funkciu VLOOKUP a keď sa dostanete k argumentu "tabuľka", prejdite na druhý súbor a vyberte rozsah, ktorý potrebujete vyhľadať pomocou myši.

Môžete tiež zvážiť príklad, keď nepoznáme presnú hodnotu, vo vzorci VLOOKUP použijeme zástupné znaky. Niekedy potrebujeme nájsť nie niečo konkrétne, ale niečo, čo má spoločné vlastnosti, ako možnosť máme veľa pohoviek rôznych modelov, ako je iný nábytok, ale stačí si ich vybrať.

Keď je funkcia VLOOKUP nahradená, môže používať nasledujúce znaky:

• "?" (otáznik) - umožňuje nahradiť ľubovoľný jeden znak;
• "*" (hviezdička)- nahradí ľubovoľný počet a postupnosť znakov.

VLOOKUP („A *“; $ A $ 2: $ B $ 10; 1; FALSE)

Poradte! Aby funkcia VLOOKUP fungovala správne, musíte ako štvrtý argument použiť parameter „FALSE“.
No, ak sme sa už dotkli témy presnej alebo približnej zhody v syntaxi funkcie VLOOKUP, tak sa na ňu pozrime bližšie:

• ak argument "Intervalové zobrazenie" rovná sa "KLAMAŤ", v takom prípade vzorec hľadá presnú zhodu s argumentom vyhľadávacej hodnoty. Ak vzorec spĺňa dve alebo viac hodnôt, ktoré zodpovedajú argumentu „požadovaná hodnota“, vyberie sa prvá zo zoznamu; v prípade, že sa nenájde žiadna zhoda, vzorec sa vráti;
• ak argument "Intervalové zobrazenie" má význam "PRAVDA" vzorec vyhľadá približnú zhodu, presnejšie, funkcia najprv vyhľadá presnú zhodu a až potom, keď ju nenájde, vyberie približnú.

Poradte!V prípade, že sa argument "interval view" rovná "TRUE" alebo nie je zadaný, je potrebná hodnota v prvom stĺpci, od najmenšej po najväčšiu. V opačnom prípade existuje možnosť chybného výsledku funkcie VLOOKUP.

Pozrime sa, ako funguje funkcia VLOOKUP, keď sa vykonáva presné vyhľadávanie. Skúsme napríklad zistiť, ktoré auto sa pohybuje rýchlosťou 200 km/h. Myslím, že tento vzorec pre vás nebude ťažký:

= VLOOKUP (200, $ A $ 2: $ B $ 15, 2, FALSE)

Napriek tomu, že máme niekoľko 200 hodnôt, dostali sme len jednu, keďže ak sa funkcia VLOOKUP presne zhoduje, systém použije len prvú hodnotu, ktorá bola nájdená v zadanom rozsahu.
Teraz si otestujme, ako funguje funkcia VLOOKUP pre približnú zhodu hodnôt. Hľadajme auto, ktoré jazdí rýchlosťou 260 km/h. Prvá vec, ktorú urobíte, keď "Intervalové zobrazenie" rovná sa "PRAVDA"- radíte svoj rozsah hodnôt podľa prvého stĺpca vo vzostupnom poradí. Je to potrebné a dôležité, pretože funkcia VLOOKUP nájde ďalšiu najväčšiu hodnotu z danej podmienky a potom sa vyhľadávanie zastaví. Ak sa nebudete riadiť pokynmi na triedenie, výsledkom bude správa o alebo iné podivné výsledky.

Na vyhľadávanie používame nasledujúce VLOOKUP:

= VLOOKUP (260; $ A $ 2: $ B $ 15; 2; TRUE)

Náš vzorec teda vrátil nájdenú hodnotu 240, aj keď máme aj hodnotu 270 a táto hodnota sa zdá byť bližšia, ale zvláštnosťou fungovania funkcie VLOOKUP je, že pri použití presnej zhody hľadá najväčšiu hodnotu v zoznam, ale nepresahujúci jeho.
Myslím si, že príklady, na ktoré som sa pozrel v článku, vám pomôžu pochopiť všetko o tom, ako funkcia VLOOKUP funguje, ako sa používa a na čo by sa mala používať.

Existuje však mnoho pokročilejších príkladov, ktorým sa budeme venovať v iných článkoch.

A to je z mojej strany všetko! Naozaj dúfam, že všetko vyššie uvedené je vám jasné. Bol by som veľmi vďačný za zanechané komentáre, pretože je to indikátor čítanosti a inšpiruje k písaniu nových článkov! Zdieľajte, čo čítate, so svojimi priateľmi a lajkujte to!