OpenOffice.org Calc pod drobnohľadom (5) - Vyhľadávanie v oblastiach

Doteraz ukázané spôsoby adresovania predpokladali, že vieme, na kde máme uložený konkrétny údaj (bez ohľadu na to, či sme zadávali adresu takejto bunky priamo, alebo sme ju vypočítavali). Bežne však používame tabuľky aj ako zdroje údajov, kde nemôžeme vedieť, v ktorej bunke sa údaj nachádza iba preto, lebo ho musíme vyhľadať podľa určitých kritérií. Ako príklad si môžeme zobrať databázu osôb, kde ako vyhľadávací údaj použijeme rodné číslo.

Osobné údaje na liste „osoby“

Vytvorme si list s názvom „osoby“, kde zadáme do prvého stĺpca rodné číslo, do druhého meno a priezvisko, do tretieho ulicu, do štvrtého PSČ a do piateho mesto, kde býva. Celú oblasť (t.j. všetky stĺpce) spolu zadefinujeme ako oblasť „osobne_udaje“.

Definícia stĺpcovej oblasti „osobne_udaje“

Vyhľadávacie funkcie

Pre vyhľadávanie sa používajú funkcie LOOKUP, VLOOKUP a HLOOKUP. Funkcie VLOOKUP („Vertikal Lookup“) a HLOOKUP („Horizontal Lookup“) sa používajú na vyhľadávanie v jednej oblasti, pričom sa líšia smerom hľadania. Funkciu LOOKUP využijeme vtedy, ak nehľadáme v jednej oblasti, resp. údaje nechceme alebo nemôžeme spájať do jednej oblasti. Všetky funkcie umožňujú aj vyhľadávanie podľa regulárnych výrazov. Ich použitie by však už značne presiahlo rámec tohto článku.

Hľadanie v stĺpci

V našom prípade sa nám bude hodiť funkcia VLOOKUP, ktorá má nasledovný obecný tvar:

VLOOKUP(hľadaná hodnota; hľadaná oblasť; číslo stĺpca návratovej hodnoty; triedenie)

Predpokladajme, že na prvom liste chceme zadať do prvého stĺpca rodné číslo a následne chceme, aby sa nám v druhom stĺpci vypísalo meno, v treťom PSČ, v štvrtom mesto a v piatom ulica. Zároveň predpokladajme, že osobné údaje máme zotriedené podľa rodného čísla (vtedy nemusíme zadávať parameter triedenie). Za týchto predpokladov musíme postupne zadať do buniek B1, C1, D1 a E1 tieto vzorce:

=VLOOKUP($A1;osobne_udaje;2)
=VLOOKUP($A1;osobne_udaje;4)
=VLOOKUP($A1;osobne_udaje;5)
=VLOOKUP($A1;osobne_udaje;3)

Hľadanie v stĺpcovej oblasti pomocou príkazu VLOOKUP

Pokiaľ by sme údaje nemali zotriedené (čo nie je práve najlepšie riešenie, ale niekedy sa tomu nedá vyhnúť), museli by sme zadať aj štvrtý parameter triedenie, ktorý môže nadobúdať dve hodnoty – 0, ak údaje nemáme zotriedené alebo 1, ak údaje zotriedené sú (vtedy však tento parameter zadávať nemusíme):

=VLOOKUP($A1;osobne_udaje;2;0)
=VLOOKUP($A1;osobne_udaje;4;0)
=VLOOKUP($A1;osobne_udaje;5;0)
=VLOOKUP($A1;osobne_udaje;3;0)

Neutriedené osobné údaje

Na tomto mieste musíme upozorniť, že poradové číslo stĺpca návratovej hodnoty nie je myslené ako poradové číslo stĺpca v liste, ale poradové číslo stĺpca vo vyhľadávanej oblasti. Preto, ak by sme definovali oblasť „osobne_udaje“ v ktorýchkoľvek iných stĺpcoch, parametre funkcie VLOOKUP by sa nezmenili. To isté platí aj pre funkciu HLOOKUP, ktorá však nevyhľadáva údaje v prvom stĺpci definovanej oblasti, ale v jej prvom riadku.

Hľadanie v neutriedenej stĺpcovej oblasti

Hľadanie v riadku

Aby sme si ukázali spomínané možnosti, zmeníme osobné údaje na liste „osoby“ tak, že povedzme do tretieho riadku budeme zapisovať rodné číslo, do štvrtého meno, do piateho ulicu, šiesteho PSČ a siedmeho mesto. Tieto riadky následne označíme ako oblasť „osobne_udaje“.

Osobné údaje na liste „osoby“ v riadkoch

Na prvom liste budeme požadovať rovnaké výsledky, ako v predchádzajúcom príklade, takže nám stačí zmeniť iba názov funkcie, ostatné parametre vôbec nemusíme meniť:

=HLOOKUP($A1;osobne_udaje;2)
=HLOOKUP($A1;osobne_udaje;4)
=HLOOKUP($A1;osobne_udaje;5)
=HLOOKUP($A1;osobne_udaje;3)

Definícia riadkovej oblasti „osobne_udaje“

Z týchto príkladov vidíme, aké jednoduché je používanie oblastí a vyhľadávanie v nich. Zaiste, príklad s rodným číslom sa môže zdať na prvý pohľad jednoduchý, ukazuje však vyhľadávanie podľa nečíselných údajov, pričom sme si ukázali ako horizontálne, tak aj vertikálne usporiadanie údajov. Jedinou podmienkou je to, že vyhľadávací parameter sa musí nachádzať v prvom stĺpci alebo v prvom riadku takejto oblasti.

Hľadanie v riadkovej oblasti pomocou príkazu HLOOKUP

Oblasť, v ktorej vyhľadávame údaje nemusíme pomenovať. Vtedy však musíme zadať jej konkrétny rozsah priamo v príslušných funkciách, čo je pomerne neprehľadné:

=VLOOKUP($A2;osoby.A1:osoby.E65536;4)

Priame adresovanie oblasti

Hľadanie v nespojitých oblastiach

Niekedy nemôžeme mať údaje uložené do jednej oblasti – napríklad vtedy, ak ich máme na viacerých listoch. V takomto prípade musíme pre vyhľadávanie použiť funkciu LOOKUP, ktorá má nasledovný obecný tvar:

LOOKUP(hľadaná hodnota; prehľadávaný stĺpec/riadok; výsledný stĺpec/riadok)

List s rodnými číslami

Ak si dobre všimnete, chýba tu parameter „triedenie“. Pri používaní tejto funkcie OpenOffice.org Calc predpokladá, že údaje máme utriedené. Toto nám môže spôsobovať určité problémy, najmä v prípade, že ich máme uložené na viacerých listoch. Zároveň musíme upozorniť na to, že rozsah prehľadávaných údajov a rozsah výsledných údajov (t.j. počet riadkov alebo stĺpcov) musí byť rovnaký, inak funkcia vráti chybové hlásenie.

List s menom a adresou

Ako príklad pre použitie tejto funkcie si znovu uvedieme osobné údaje, teraz ich však budeme mať na dvoch listoch. Na prvom, ktorý si nazveme „rodne_cislo“ budeme mať uložené iba rodné čísla (povedzme v prvom stĺpci) a na druhom, ktorý si nazveme „meno“ budeme mať od prvého stĺpca uložené ostatné údaje – meno a adresu. Potom požadované výsledky dostaneme pomocou nasledujúcich funkcií (schválne sme nedefinovali oblasť, aby sme zvýraznili to, že údaje sú na rôznych listoch a že rozsah hľadaných údajov a výsledkov musí byť zhodný):

=LOOKUP($A1;rodne_cislo.$A$1:rodne_cislo.$A$65536;meno.A$1:meno.A$65536)
=LOOKUP($A1;rodne_cislo.$A$1:rodne_cislo.$A$65536;meno.C$1:meno.C$65536)
=LOOKUP($A1;rodne_cislo.$A$1:rodne_cislo.$A$65536;meno.D$1:meno.D$65536)
=LOOKUP($A1;rodne_cislo.$A$1:rodne_cislo.$A$65536;meno.B$1:meno.B$65536)

Hľadanie v nespojitej oblasti pomocou príkazu LOOKUP

Nabudúce: Začíname so stručným pohľadom na ďalšie funkcie OpenOffice.org Calc.

Stránky OpenOffice.org: www.openoffice.sk