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

20.10.2007 21:00 | Články | Július Pastierik
Vyhľadávanie údajov v definovaných oblastiach tabuliek patrí medzi často požadované funkcie tabuľkových procesorov. V ďalšom pokračovaní seriálu o OpenOffice.org Calc si preto ukážeme tieto možnosti.

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

    • Skvele 26.10.2007 | 03:57
      Streco   Návštevník
      Len tak dalej!!!
      Chcel by som sa spytat ci by sa nenasiel niekto, co by spravil taky to navod aj na Databazy.
      Myslim ze by nasli dostatocne vyuzitie aj v praktickom zivote
      • Re: Skvele 26.10.2007 | 08:21
        Avatar Július Pastierik   Používateľ
        Po Calcu môžem urobiť podobný návod aj o Base. Už som o ňom trochu písal do časopisu LinuxEXPRES, môžete si to prečítať v seriáli na www.pc.sk (OpenOffice.org Base v krátkom predstavení - odkazy na ostatné časti nájdete na konci článku). Inak siedma časť tohto seriálu bude "Tabuľka ako zdroj dát pre databázový modul Base".
        • Re: Skvele 31.10.2007 | 12:05
          brzda   Návštevník
          Po Calcu môžem urobiť podobný návod aj o Base.

          Bud taky dobry!!!
      • Re: Skvele 31.10.2007 | 12:03
        ooo   Návštevník
        Aj ja sa pripajam k Strecovi.

        Skvele!!!
        Len tak dalej!!!
        Pokracuj, pokracuj, pokracuj...