Vyhľadávanie hodnôt pomocou funkcií VLOOKUP, INDEX a MATCH

Poznámka:  Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

Predpokladajme, že máte zoznam office umiestnenie čísel a potrebujete vedieť, ktorí zamestnanci sú v jednotlivých službách office. Tabuľka je veľký, takže si myslíte, že je to náročné úlohy. Je to vlastne celkom jednoduché vyhľadávacie funkcie.

Funkcia VLOOKUP a HLOOKUP funkcií INDEX a MATCH,sú najužitočnejšie funkcie v programe Excel.

Poznámka: Sprievodca vyhľadávaním funkcia už nie je k dispozícii v Exceli.

Tu je príklad toho, ako sa používa funkcia VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

V tomto príklade B2 je prvý argument– prvok údajov, funkcia musí pracovať. VLOOKUP, tento prvý argument je hodnota, ktorú chcete nájsť. Tento argument môže byť odkaz na bunku alebo pevné hodnoty, napríklad "Kováč" alebo 21 000. Druhým argumentom je rozsah buniek, C2-:E7, do ktorého sa má vyhľadať požadovanú hodnotu. Tretí argument je stĺpec v danom rozsahu buniek, ktorý obsahuje hodnoty, ktoré hľadáte.

Štvrtý argument je voliteľné. Zadajte hodnotu TRUE alebo FALSE. Ak zadáte hodnotu TRUE, alebo ponechajte argument prázdny, funkcia vráti približnú zhodu hodnoty zadané v prvom argumente. Ak zadáte FALSE, funkcia sa zhodujú s hodnotou poskytujú podľa prvého argumentu. Inými slovami, takže štvrtý argument prázdny –, alebo zadaním hodnoty TRUE – vám dáva väčšiu flexibilitu.

Tento príklad zobrazuje, ako funguje funkcia. Ak chcete zadať hodnotu v bunke B2 (prvý argument), funkcia VLOOKUP vyhľadá bunky v rozsahu C2:E7 (2. argumentu) a vráti najbližšiu približnú zhodu z tretieho stĺpca v rozsahu, stĺpec E (3. argument).

Typické použitie funkcie VLOOKUP

Štvrtý argument je prázdny, funkcia preto vráti približnú zhodu. Ak nie, budete musieť zadať jednu z hodnôt v stĺpcoch C a D a zobrazí výsledok vôbec.

Keď ste pripravení pri použití funkcie VLOOKUP, HLOOKUP (funkcia) je rovnako jednoduché použitie. Zadáte rovnaké argumenty, ale vyhľadáva do riadkov namiesto stĺpcov. "

Skúste to

Ak si chcete vyskúšať funkcie lookup pred pokusom ich s vlastnými údajmi, tu je niekoľko vzorových údajov. Niektorí používatelia programu Excel ako s použitím funkcie VLOOKUP a HLOOKUP; iní radšej spoločné použitie INDEX a MATCH. Vyskúšajte jednotlivé postupy a uvidíte, ktoré z nich sa vám páči najlepšie.

ZAČIARKAVACIE políčko Tlačiť v práci

Tieto údaje skopírujte do prázdny hárok.

TIP:    Pred prilepením údajov do Excelu, nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť Text (kartadomov, skupina Zarovnanie ).

Hustota

Viskozita

Teplota

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Vzorec

Popis

Výsledok

=VLOOKUP(1;A2:C10;2)

Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca B v rovnakom riadku.

2,17

=VLOOKUP(1;A2:C10;3;TRUE)

Hľadá približnú zhodu k hodnote 1 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 1 v stĺpci A, ktorá je 0,946, a potom vráti hodnotu zo stĺpca C v rovnakom riadku.

100

=VLOOKUP(0,7;A2:C10;3;FALSE)

Hľadá úplnú zhodu k hodnote 0,7 v stĺpci A. Pretože stĺpec A úplne zhodnú hodnotu neobsahuje, vráti sa chyba.

#NEDOSTUPNÝ

=VLOOKUP(0,1;A2:C10;2;TRUE)

Hľadá približnú zhodu k hodnote 0,1 v stĺpci A. Pretože hodnota 0,1 je menšia ako najmenšia hodnota v stĺpci A, vráti sa chyba.

#NEDOSTUPNÝ

=VLOOKUP(2;A2:C10;2;TRUE)

Hľadá približnú zhodu k hodnote 2 v stĺpci A, nájde najväčšiu hodnotu menšiu alebo rovnú 2 v stĺpci A, ktorá je 1,29, a potom vráti hodnotu zo stĺpca B v rovnakom riadku.

1,71

Príklad HLOOKUP

Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.

TIP:    Pred prilepením údajov do Excelu, nastavte šírku stĺpcov A až C na 250 pixlov a kliknite na položku Zalomiť Text (kartadomov, skupina Zarovnanie ).

Nápravy

Ložiská

Skrutky

4

4

9

5

7

10

6

8

11

Vzorec

Popis

Výsledok

=HLOOKUP("Nápravy"; A1:C4; 2; TRUE)

Vyhľadá výraz „Nápravy" v prvom riadku a vráti hodnotu z druhého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci A).

4

=HLOOKUP("Ložiská"; A1:C4; 3; FALSE)

Vyhľadá výraz „Ložiská" v prvom riadku a vráti hodnotu z tretieho riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci B).

7

=HLOOKUP("B"; A1:C4; 3; TRUE)

Vyhľadá hodnotu B v riadku 1 a vráti hodnotu z riadka 3 toho istého stĺpca. Vzhľadom na to, že presná zhoda sa nenašla, použije sa najväčšia hodnota v prvom riadku, ktorá je súčasne menšia ako hodnota B: Nápravy v stĺpci A

5

=HLOOKUP("Svorníky"; A1:C4; 4)

Vyhľadá výraz „Svorníky" v prvom riadku a vráti hodnotu zo štvrtého riadka, ktorý sa nachádza v tom istom stĺpci (stĺpci C).

11

=HLOOKUP(3; {1,2,3;"a","b","c";"d","e","f"}; 2; TRUE)

Vyhľadá číslo 3 v trojriadkovej konštante poľa a vráti hodnotu z druhého riadka toho istého (v tomto prípade tretieho) stĺpca. V konštante poľa sa nachádzajú tri riadky s hodnotami, jednotlivé riadky sú oddelené bodkočiarkou (;). Keďže hodnota c sa nachádza v druhom riadku a v tom istom stĺpci ako číslo 3, vráti sa hodnota c.

c

INDEX a MATCH príklady

V tomto príklade sa používa funkcií INDEX a MATCH spolu na vrátenie najskorší číslo faktúry a jeho zodpovedajúci dátum pre každú päť miest. Pretože vráti dátum ako číslo, používame funkciu TEXT formátovať ako dátum. Funkcia INDEX skutočne používa výsledok funkcie MATCH ako svoj argument. Kombináciu funkcií INDEX a MATCH sa používajú v jednotlivých vzorec – dvakrát najprv vrátiť číslo faktúry a potom sa vráti dátum.

Skopírujte všetky bunky v tejto tabuľke a vložte ich do bunky A1 prázdneho hárka v Exceli.

TIP:    Pred prilepením údajov do Excelu, nastavte šírku stĺpcov A až D na 250 pixlov a kliknite na položku Zalomiť Text (kartadomov, skupina Zarovnanie ).

Faktúra

Mesto

Dátum vystavenia faktúry

Najstaršia faktúra podľa mesta s dátumom

3115

Brezno

07.04.12

="Brezno = "&INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Brezno";$B$2:$B$33;0);3);"d/m/rr")

3137

Brezno

09.04.12

="Bernolákovo = "&INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Bernolákovo";$B$2:$B$33;0);3);"d/m/rr")

3154

Brezno

11.04.12

="Dudince = "&INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Dudince";$B$2:$B$33;0);3);"d/m/rr")

3191

Brezno

21.04.12

="Nové Zámky = "&INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Nové Zámky";$B$2:$B$33;0);3);"d/m/rr")

3293

Brezno

25.04.12

="Trnava = "&INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);1)& ", Dátum vystavenia faktúry: " & TEXT(INDEX($A$2:$C$33;MATCH("Trnava";$B$2:$B$33;0);3);"d/m/rr")

3331

Brezno

27.04.12

3350

Brezno

28.04.12

3390

Brezno

01.05.12

3441

Brezno

02.05.12

3517

Brezno

08.05.12

3124

Bernolákovo

09.04.12

3155

Bernolákovo

11.04.12

3177

Bernolákovo

19.04.12

3357

Bernolákovo

28.04.12

3492

Bernolákovo

06.05.12

3316

Dudince

25.04.12

3346

Dudince

28.04.12

3372

Dudince

01.05.12

3414

Dudince

01.05.12

3451

Dudince

02.05.12

3467

Dudince

02.05.12

3474

Dudince

04.05.12

3490

Dudince

05.05.12

3503

Dudince

08.05.12

3151

Nové Zámky

09.04.12

3438

Nové Zámky

02.05.12

. 3471

Nové Zámky

04.05.12

3160

Trnava

18.04.12

3328

Trnava

26.04.12

3368

Trnava

29.04.12

3420

Trnava

01.05.12

3501

Trnava

06.05.12

Ďalšie informácie o vyhľadávacích funkciách

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×