Vyhľadávacie funkcie INDEX a MATCH

Temer všetky vyhľadávacie funcie prehľadávajú určitú oblasť a hľadajú v nej nejakú hodnotu, ktorá zodpovedá zadanému vyhľadávaciemu kritériu. Na príklade vekového zloženia obyvateľstva Prahy v rokoch 1996 až 1999 si ukážeme používanie funkcií INDEX a MATCH s odkazom na oblasti v inom liste zošitu Excelu.


Najprv si zapíšeme dáta do prvého listu zošita. (V tomto príklade pomenovanom ako Obyv). Nachádza sa v ponúkanom súbore matchindx.zip. Ako je vidieť na Obr. 1 v prvom stĺpci zapíšeme poradové čísla, ktoré sú potrebné na to, aby po voľbe veku v prvom zozname (list box2 na Obr.3) nám nižšie prezentovaný program transformoval vek na poradové číslo, ktoré potom použijeme vo vzorci vyhľadávacej funkcie. V stĺpci G zapíšeme čísla od 0 do 100 (pre voľbu veku), ktoré sa vložia do list boxu (Obr. 3) zadefinovaním vlastnosti:

match0 (10K)

Stĺpec 1 a stĺpec 7 skryjeme, potom list bude vyzerať ako na Obr. 2. V liste s indexom 2 [v makre Sheet(2)], v tomto príklade pomenovanom ako Voľba sú vložené dva zoznamy (ListBox1 a ListBox2) pre voľbu roku a pre voľbu veku. ListBox1 a ListBox2 má zadané v okne vlastnosti (properties) :

match4 (3K) match3 (3K)

   V liste Voľba zapíšeme do buniek D2:D5 potrebné roky a do bunky D1 nadpis, ktorý bude v ListBox1. Do ListBox1 sa potom načítaju roky z oblasti D2:D5 a zvolený rok sa premietne do prepojenej bunky A1.


match1 (6K)

match2 (5K) <

Do bunky B1 listu Voľba zapíšeme vzorec:
=INDEX(Obl1;MATCH(C1;Obyv!A3:A23;1);MATCH(A1;Obyv!A3:F3;1))

Používame tu funkciu INDEX(pole;riadok;stĺpec). Index vráti obsah bunky ležiacej v priesečníku daného riadka a stĺpca. (V bunke B1 je vložený aj obrázok s nápisom "Počet obyvateľov Prahy").

Ďalšia funkcia MATCH(hľadaná hodnota;pole;typ_zhody). Pole je súvislý rozsah buniek, v ktorom sa hľadajú hodnoty. Typ zhody môže byť: Teda v horeuvedenon vzorci prvý MATCH určí riadok, druhý určí stĺpec a potom INDEX nám vráti hľadanú hodnotu. Obl1 je názov zadefinovaný v prvom liste (vložiť-názov-definovať, odkaz je Obyyv!A3:F23). Makro CommandButton1_Click() zabezpečí priradenie vekového intervalu poradovému číslu.

Private Sub CommandButton1_Click()
Dim cis As Integer
Sheets(2).Activate
Range("A3").Select
Range("A3").Activate
If ActiveCell = 0 Then cis = 1
If ActiveCell > 0 And ActiveCell <= 4 Then cis = 2
If ActiveCell > 4 And ActiveCell <= 9 Then cis = 3
If ActiveCell > 9 And ActiveCell <= 14 Then cis = 4
If ActiveCell > 14 And ActiveCell <= 19 Then cis = 5
If ActiveCell > 19 And ActiveCell <= 24 Then cis = 6
If ActiveCell > 24 And ActiveCell <= 29 Then cis = 7
If ActiveCell > 29 And ActiveCell <= 34 Then cis = 8
If ActiveCell > 34 And ActiveCell <= 39 Then cis = 9
If ActiveCell > 39 And ActiveCell <= 44 Then cis = 10
If ActiveCell > 44 And ActiveCell <= 49 Then cis = 11
If ActiveCell > 49 And ActiveCell <= 54 Then cis = 12
If ActiveCell > 54 And ActiveCell <= 59 Then cis = 13
If ActiveCell > 59 And ActiveCell <= 64 Then cis = 14
If ActiveCell > 64 And ActiveCell <= 69 Then cis = 15
If ActiveCell > 69 And ActiveCell <= 74 Then cis = 16
If ActiveCell > 74 And ActiveCell <= 79 Then cis = 17
If ActiveCell > 79 And ActiveCell <= 84 Then cis = 18
If ActiveCell >= 85 Then cis = 19
Cells(1, 3) = cis
End Sub

Typickým príkladom pre použitie funkcie VLOOKUP je určenie napr. mesačnej dane z príjmu. Je to jednoduché - zostavte si podľa Obr.4 daňovú tabuľku (uvedený príklad bol vypočítaný podľa VBA programu, ktorý som zostavila, a ktorý uverejním za pár dní.

dane1 (9K)

S Vašou aplikáciou bude môcť pracovať aj užívateľ, ktorý nie je príliš zbehlý v používaní tabuľkového procesoru EXCEL, ak vzorce vložíte do nasledujúceho listu (v tomto prípade s názvom VypDane) a list (hárok) Dane skryjete. Vtedy list VypDane bude vyzerať ako na Obr. 5

dane2 (5K)

Do bunky B3 vložíme funkciu pre zistenie dane z príjmu za mesiac
=IF(ISERROR(VLOOKUP(A3;Dan;false));"Chyba!";VLOOKUP(A3;Dan;FALSE))

Vo vzorci odkazujeme na bunku A3, do ktorej užívateľ má vložiť hrubý mesačný príjem. Túto bunku však zabezpečíme cez Údaje-Overenie. V oknách, ktoré sa zobrazia zadáme definíciu platných údajov a správ (Obr. 6, 7, 8):

dane3 (8K)

dane4 (5K)

dane5 (7K)

    Ak teda užívateľ klikne do bunky A3, zobrazí sa text "Sem vložte výšku Vašej hrubej mzdy za mesiac". Ak užívateľ zadá menej ako 12000 Sk, alebo viac ako 30 000 SK, zobrazí sa chybové hlásenie podľa Obr.8. (Samozrejme, daňovú tabuľku si môžete upraviť, a zadať väčšie rozpätie.) Vo vzorci v bunke B3 je použitá aj informačná funkcia ISERROR. Vo vzorci je aj názov oblasti daňovej tabuľky (dan) v skrytom liste - tento názov zadáme cez Vložiť-Názov-Definovať. Tabuľka je v oblasti A3:B19.
Príklady boli vypracované v Excel 2000, pod OS INDOWS XP.

Stiahnite si súbor matchindx.zip    Veľkosť: 25 kB

Zdroje: Statistická ročenka 2000. Český statistický úřad , 2000.


Stránka je v súlade s aktuálnymi normami.


Valid HTML 4.01 Transitional

©  Klára Mrázová