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:
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) :
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.
<
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ť:
1 .... MATCH nájde najvyššiu hodnotu, ktorá je
nižšia alebo sa rovná hľadanej hodnote. Hodnoty v
prehľadávanom poli musia byť zoradené vzostupne.
0 ....nájde prvú hodnotu, ktorá sa presne
zhoduje s hľadanou hodnotou. Hodnoty v prehľadávanom poli
nemusia byť zoradené.
-1 ....nájde najnižšiu hodnotu, ktorá je vyššia alebo
sa rovná hľadanej hodnote. Hodnoty v prehľadávanom poli
musia byť zoradené zostupne.
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í.
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
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):
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.
<
