Linux E X P R E S

Mnohí z používateľov nechcú používať tabuľkový procesor Calc iba ako „kalkulačku“, ale chcú ho využiť aj na iné, omnoho komplexnejšie úlohy – napr. tlač dodacích listov, faktúr a pod. Prečo nie, napokon ako údaje môžeme mať napr. na jednom liste náš tovar s jeho kódovým označením, jednotkovou cenou, sadzbou DPH a pod., a na druhom liste potom chceme po zadaní príslušného kódu, aby sa údaje automaticky vyhľadali a uložili.

Pozn. redakce. Četli jste články Lukáše Faltýnka o základní práci s Calcem a o zápisu funkcí v Calcu?

Znovu si pripomeňme základné spôsoby adresácie bunky. Adresa sa môže skladať z troch častí – názvu listu, písmena, ktorý označuje stĺpec a čísla riadku. V tejto súvislosti musíme podotknúť, že v prípade kopírovania adresy Calc adresu automaticky upravuje – t.j. posúva číslo riadku, písmeno stĺpca a aj názov listu podľa poradia, v akom ich máme usporiadané.

Toto nám však nemusí vždy vyhovovať. Napríklad, ak chceme v tabuľke používať nejakú konštantu, je výhodné, ak si ju zadefinujeme do špeciálnej bunky (napr. A1) a potom sa vo vzorcoch odvolávame iba na túto bunku. Pravdaže, pokiaľ by sme do vzorcov zadali adresu v tvare „A1“, potom by sa pri kopírovaní do oných častí menila, čo by bolo neželateľné, lebo konštanta musí byť vo všetkých vzorcoch tá istá.

Za týmto účelom môžeme používať v adresách buniek znak dolára – $. Tento určuje, že časť, ktorá sa nachádza za ním, sa nebude pri kopírovaní meniť. Takto môžeme dostať tvary adries, ktoré si uvedieme vo všetkých možných príkladoch:

• List1.A1 – relatívna adresa, pri kopírovaní na iné miesto sa mení ako stĺpec, tak aj riadok a pri skopírovaní na iný list aj číslo listu.
• List1.A$1 – adresa s nemenným číslom riadku. Pri kopírovaní na iné miesto sa bude meniť len stĺpec a názov listu.
• List1.$A1 – adresa s nemenným písmenom stĺpca. Pri kopírovaní na iné miesto sa bude meniť len riadok a názov listu.
• List1.$A$1 – adresa s nemenným číslom stĺpca a písmenom riadku. Pri kopírovaní sa bude meniť iba názov listu.
• $List1.A1 – adresa s nemenným názvom listu. Pri kopírovaní na iné miesto sa bude meniť len stĺpec alebo riadok.
• $List1.A$1 – adresa s nemenným názvom listu a číslom riadku. Pri kopírovaní na iné miesto sa bude meniť len stĺpec.
• $List1.$A1 – adresa s nemenným názvom listu a písmenom stĺpca. Pri kopírovaní na iné miesto sa bude meniť len riadok.
• $List1.$A$1 – absolútne nemenná adresa. Pri kopírovaní sa nemení jej žiadna časť.

Východzie údaje pre výpočet

Ako jednoduchý príklad sme zvolili tabuľku s číslami, ktoré zároveň určujú svoju polohu. Na zosnímaných obrazovkách vidíte, ako sa príslušné zmeny prejavujú. Pravdaže, úplne všetky prípady sa tam nenachádzajú, pretože by to bolo zbytočné, pre základný prehľad postačujú aj tieto.

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Rôzne prípady adresácie buniek

Definícia údajov na liste Konstanty

Uviedli sme si základné možnosti priameho adresovania buniek. V príklade s nejakou konštantou by sme mohli potom využiť napr. tvar „$A$1“ alebo „$List1.$A$1“ podľa toho, či by sme s konštantou pracovali na jednom alebo viacerých listoch. Osobne by som v tomto prípade zrejme použil špeciálny list, ktorý by som nazval „Konstanty“ a tam by som si ich definoval, takže adresa by vyzerala: „$Konstanty.$A$1“.

Použitie konštanty vo výpočte

To, čo sme si uviedli doteraz je mnohým používateľom známe. Pozrime sa teraz na iné – zaujímavejšie možnosti adresovania buniek. Pokiaľ si spomeniete, pri hromadnej korešpodencii, kde sme využívali tabuľku ako zdroj dát sme uviedli, že prvý riadok nepredstavuje vlastné údaje, ale ich názvy. Tieto názvy môžeme použiť aj priamo vo vzorcoch. Napríklad, do bunky A1 vložíme text „Počet“, do bunky „B1“ text „JC“ a do bunky „C1“ text „Celkom“. Následne zadáme do buniek C2, C3… (podľa toho, koľko potrebujeme) rovnaký vzorec:

Použitie nadpisov namiesto adries buniek

=Počet*JC

O hromadnej korešpodencii s použitím tabuliek sme písali, nezabudnite sa tam pozrieť na to, ako sa definovali názvy stĺpcov, pretože ich môžeme použiť aj pri zadávaní vzorcov.

Teraz nám stačí zadať príslušné číselné údaje do buniek A2, B2, A3, B3… a v bunkách stĺpca C sa nám objavia príslušné výpočty. Pokiaľ si všimnete, Calc automaticky uloží zadané názvy do apostrofov. Pokiaľ nadpis zmeníme, zmení sa automaticky aj vo vzorcoch, takže sa o to nemusíme starať.

Uvedený postup však nestačí, ak si chceme podobným spôsobom nazvať riadky a musíme povedať, že nie je úplne najlepší, lebo pre takéto účely máme k dispozícii tzv. oblasti. Oblasť je množina buniek, ktorú si nazveme určitým názvom. Naraz si môžeme definovať viacero oblastí, pričom sa môžu vzájomne aj prekrývať.

Definícia oblastí je jednoduchá – označíme skupinu buniek, ktorú chceme označiť nejakým názvom (v prípade že tak chceme urobiť pre celý riadok alebo stĺpec, tak klikneme na číslo riadku alebo písmeno označujúce príslušný stĺpec) a cez postupnosť volieb menu „Dáta – Definovať oblasť…“ otvoríme dialógové okno „Definovať rozsah databázy“. Označené dáta už máme pripravené a nám nezostáva nič iné, ako vložiť nami požadovaný názov a definíciu potvrdiť tlačidlo „OK“.

Definícia oblasti

Ak si dobre všimnete, pri definícii oblasti sú dáta pripravené s adresáciou v absolútnom tvare (napr. „$List1.$B$1:$List1.$B$65536“. Pri použití príslušného názvu sa však automaticky mení číslo riadku alebo stĺpca v závislosti od toho, aký rozsah údajov sme definovali, t.j. ak označíme nejakým názvom stĺpec, bude sa meniť číslo riadku, ak riadok, tak písmeno stĺpca. Názov listu sa nemení. Týmto spôsobom môžeme následne takto zadefinované údaje používať aj v iných listoch bez toho, aby sme sa museli odvolávať na názov listu.

Po zadaní vzorca s využitím názvu definovanej oblasti Calc nepridáva k príslušným názvom žiadne apostrofy. To je rozdiel oproti tomu, keď používame nadpisy a je to veľmi dôležité pre rozoznávanie jednotlivých prípadov.

Použitie oblastí v adresácii buniek

Pravdaže, OpenOffice.org Calc nám umožňuje označiť spoločným názvom aj celú maticu údajov, tento názov však nemôžeme potom použiť priamo vo vzorcoch. Táto možnosť je určená napr. pre vyhľadávanie v množine údajov, o čom si ešte budeme písať v súvislosti s príkladom, ktorý sme uviedli v úvode dnešného článku – vyhľadávanie údajov v databáze tovaru.

Doteraz uvedené príklady adresácie buniek boli veľmi jednoduché a vlastne vždy predpokladali, že vopred vieme adresu bunky, z ktorej chceme získať požadovaný údaj napr. pre výpočet. Iná situácia však nastáva vtedy, ak adresu bunky nepoznáme priamo, ale najprv aj ju musíme nejako vypočítať či získať.

Ako príklad si zoberme už viackrát spomínaný zoznam tovaru. Povedzme, že ho budeme mať číslovaný veľmi jednoducho – od jednotky nahor. Pri zadávaní tovaru do nejakého zoznamu potom zapíšeme jeho číslo a chceme, aby sa nám do ostatných koloniek vypísali údaje, ako je názov, cena a pod. V takomto prípade môžeme pre získanie adresy použiť funkciu ADDRESS:

=ADDRESS(riadok; stĺpec; relatívnosť; list)

Pozor, pri údaji o stĺpci sa nezadáva jeho písmeno, ale poradové číslo (t.j. stĺpcu „A“ zodpovedá číselný údaj 1). Parameter „relatívnosť“ určuje, akú adresu získame a môže nadobúdať tieto hodnoty (pokiaľ zadáme iné hodnoty, ako sú uvedené, funkcia sa správa, ako by sme zadali hodnotu 1):

• 1 – absolútna adresa ($A$1)
• 2 – absolútne číslo riadku (A$1)
• 3 – absolútne písmeno stĺpca ($A1)
• 4 – relatívna adresa (A1)

Názov listu (zadáva sa v úvodzovkách) je vždy relatívny a nemusí byť vôbec zadaný v prípade, že pracujeme iba s aktuálnym listom. Pravdaže, výsledok tejto funkcie je „iba“ názov adresy, čo nám ešte nepostačuje, pretože my chceme získať obsah príslušných buniek. Na to nám slúži funkcia INDIRECT, ktorá vracia obsah bunky podľa zadanej adresy:

=INDIRECT(adresa)

Jednoduchá definícia zoznamu tovaru

V praktickom použití je preto výhodné tieto dve funkcie použiť naraz. Predpokladajme, že zoznam tovaru (v poradí údajov názov, jednotková cena, DPH) máme na liste s názvom „Tovar“. Ak poradové číslo tovaru zadáme do napr. bunky A2, potom môžeme ostatné údaje o tovare získať nasledovne:

=INDIRECT(ADDRESS($A2;1;4;“Tovar“))
=INDIRECT(ADDRESS($A2;2;4;“Tovar“))
=INDIRECT(ADDRESS($A2;3;4;“Tovar“))

Nepriame adresovanie buniek

Samozrejme, aj poradové číslo stĺpca, spôsob adresácie či názov listu môžeme mať v iných bunkách a nemusíme ich zadávať priamo. Môžu byť dokonca výsledkom určitého výpočtu, čo dokážeme využiť pri tvorbe rôznych fyzikálnych, matematických či iných podobných tabuliek.

Tento príklad môže mnohým postačovať pri vyhľadávaní údajov v zozname tovaru v takom prípade, že si tovar číslujú iba jednoduchým poradovým číslom. Toto je však mnohokrát (či dokonca väčšinou) nepostačujúce, pretože pri číslovaní tovaru môžeme používať omnoho komplexnejšie údaje, ako je napríklad ľubovoľné vlastné číslovanie, čiarový kód, pri knihách zase ISBN a pod.

Definícia zoznamu tovaru s vlastnými kódmi

V takýchto prípadoch už jednoznačne nevystačíme s priamou adresáciou, ale údaje musíme vyhľadávať. Na toto nám slúži funkcia VLOOKUP, ktorá vyhľadáva údaje v prvom stĺpci definovanej oblasti:

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

Definícia viacerých stĺpcov do spoločnej oblasti

Pravdaže, v takomto prípade musíme predpokladať, že ako prvý údaj je vyhľadávací kód a ostatné údaje o tovare sú príslušne posunuté. V tomto prípade tiež už nevystačíme iba s tým, že sa budeme odvolávať na list „Tovar“, ale je potrebné zadefinovať rozsah tohto číselníka najlepšie tak, že si označíme jeho všetky stĺpce a pomocou definície oblasti ich nazveme povedzme „Zásoby“. Údaje o názve, cene a DPH získame týmto spôsobom nasledovne:

=VLOOKUP($A2;Zásoby;2)
=VLOOKUP($A2;Zásoby;3)
=VLOOKUP($A2;Zásoby;4)

Vyhľadávanie podľa prvého stĺpca definovanej oblasti

Pravdaže, ak si nezadefinujeme oblasť zásoby, môžeme použiť aj priame adresy (je to však menej pohodlné a neprehľadnejšie), napríklad:

=VLOOKUP($A2;Tovar.A1:Tovar.D3000;2)

Teraz sa môžete opýtať, čo máte robiť, ak vyhľadávacie údaje nemáte radené v prvom stĺpci, ale v prvom riadku. Nič sa nedeje, pretože OpenOffice.org Calc nám aj na toto ponúka riešenie – funkciu HLOOKUP. Z jej názvu je zrejmé, že je to vlastne taká istá funkcia, ako VLOOKUP (Vertical LOOKUP), ibaže vyhľadáva v riadku (Horizontal LOOKUP):

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

Pravdaže, v prípade, že budeme používať funkciu HLOOKUP, musíme zadefinovať rozsah oblasti inak (označením príslušných riadkov, ako pri vyhľadávaní podľa stĺpcov (kde sme označovali stĺpce).

Definícia riadkovej oblasti

Funkcie LOOKUP, VLOOKUP a HLOOKUP sa dajú použiť aj na veľmi zaujímavé výpočty. Nasledujúci vzorec vypočítava konkrétne výšku autorskej odmeny za článok, pričom jeho výška narastá podľa určitého rozsahu znakov (t.j. nie za každý znak, ale povedzme za každých 300 znakov) a celková výška odmeny je zároveň obmedzená maximálnym počtom znakov (článok môže byť dlhší, ale už za to nie je zaplatené). A aby to nebolo jednoduché, táto odmena je závislá od typu článku, pričom pre každý typ sú všetky potrebné údaje úplne iné.

Pri používaní funkcií VLOOKUP a HLOOKUP je vhodné mať vyhľadávané údaje utriedené podľa poradia, inak nie je zaručený správny výsledok vyhľadania. Pokiaľ ich však utriedené nemáme, môžeme použiť inak nepovinný štvrtý parameter „triedenie“, o ktorom sme sa doteraz ešte nezmienili. Na tomto mieste musíme upozorniť, že hoci nápoveda k tejto funkcii hovorí o zadaní logickej hodnoty TRUE alebo FALSE, nie je to pravda, pretože vtedy tieto funkcie vracajú chybové hlásenie „#NAME?“.

Chybový výpis pri zadaní nesprávneho parametra

V skutočnosti je potrebné zadávať číselný údaj 0 v prípade, že údaje zotriedené nemáme, alebo 1 (štandardná hodnota, ktorú nemusíme zadávať) v prípade, že údaje zotriedené sú:

=HLOOKUP($A2;Zásoby;2;0)
=HLOOKUP($A2;Zásoby;3;0)
=VLOOKUP($A2;Zásoby;4;0)

Vyhľadávanie v neutriedenej oblasti

A aby sme nezabudli, pokiaľ nechceme údaje spájať do jednej oblasti (alebo to nie je ani vhodné), môžeme použiť funkciu pre vyhľadávanie v jednom stĺpci alebo riadku – LOOKUP:

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

Funkcia vyhľadá hľadaný výraz v prehľadávanom stĺpci alebo riadku a vráti zodpovedajúcu hodnotu, ktorá sa vyskytuje na príslušnej úrovni vo výslednom stĺpci alebo riadku. Týmto spôsobom nemusíme mať údaje nijako zviazané do spoločnej oblasti. Vyhľadávanie v stĺpci môžeme urobiť napríklad takto:

=LOOKUP($A2;Tovar.A1:Tovar.A3000;Tovar.B1:Tovar.B3000)

A vyhľadávanie v riadku napríklad takto:

Použitie funkcie LOOKUP pri vyhľadávaní v riadku

=LOOKUP($A2;Tovar.A1:Tovar.IV1;Tovar.A2:Tovar.IV2)

Na rozdiel od funkcií VLOOKUP a HLOOKUP v prípade funkcie LOOKUP musia byť prehľadávané údaje určite utriedené. Všetky tri funkcie umožňujú vyhľadávanie pomocou regulárnych výrazov, čo je už však iná kapitola.

Nakoniec sa ešte musíme zmieniť o prípade, keď potrebujeme získať údaj, ktorý sa nachádza v úplne inej tabuľke. Na takéto účely je určená funkcia DDE. Jej použitie nie je možné pri medziplatformových odkazoch, t.j. nie je možné odkazovať z počítača s operačným systémom Linux na počítač s operačným systémom Windows a pod.:

=DDE(server; súbor; oblasť; režim)

Parameter „server“ obsahuje názov serverovej aplikácie a pre OpenOffice.org je to „soffice“. Pozor na veľkosť písmen, názov musí byť zadaný presne, inak nedosiahneme požadovaný výsledok. Toto však nemusí platiť pre všetky platformy, napr. v operačnom systéme Windows to tak nie je, preto pri prípadnom posielaní a preberaní súborov je potrebné skontrolovať tieto údaje.

DDE odkaz na tabuľku tovaru

Parameter „súbor“ obsahuje celé meno súboru vrátane cesty. Pravdaže, pokiaľ máme napr. na jednom počítači dva rôzne operačné systémy (obvykle Linux a Windows) a Linux má prístup k súborom v druhom operačnom systéme (čo je v prípade Linuxu úplne bežné), potom môžeme zadať aj cestu k súboru napr. na Windowsovskom diskovom oddieli.

DDE odkaz na tabuľku vo Windowsovskom oddieli

Pri funkcii DDE nemôžeme použiť vyhľadávanie (LOOKUP) v odkazovanej tabuľke a preto prípadné nepriame adresné odkazy získame najskôr pomocou funkcie ADDRESS. V prípade použitia ako databázy tovaru to preto nie je práve najvhodnejšie riešenie.

Parameter „oblasť“ je vlastná definícia buniek v zdrojovom súbore, ktoré spájame s našim súborom. Parameter „režim“ je voliteľný (štandardní hodnota je nula) a riadi spôsob prenosu:

• 0 – zachováva sa formát čísla, ako je zadaný v zdrojovom súbore.
• 1 – údaje budú interpretované v štandardnom formáte nášho súboru (slovenčine alebo češtine).
• 2 – údaje nebudú prevedené ako čísla, ale ako text.

Ak budeme predpokladať, že tovar máme definovaný v osobitnom súbore „tovar.ods“, potom môžeme získať potrebné informácie napríklad takto (pravdaže, spôsob adresácie bunky by musel byť v tomto prípade iný, uvedené sú iba jednoduché príklady):

=DDE(“soffice“; “/home/julko/Dokumenty/tovar.ods“; “A1“)
=DDE(“soffice“; “/mnt/win_c/Moje dokumenty/linux/tovar.ods“; “B1“)

V prípade, že sa zmenia údaje v odkazovanom (zdrojovom) súbore, neprejavia sa tieto ihneď aj v cieľovom, ale musíme cieľový súbor zavrieť a znovu otvoriť, alebo vykonať postupnosť krokov menu „Upraviť – Odkazy…“. Žiaľ, pri prenose textových údajov dochádza k nesprávnej interpretácii znakov s diakritikou.

V článku sme nespomenuli viaceré funkcie, ktoré môžeme použiť pri výpočte adresy – AREAS, ktorá vracia počet súvislých oblastí buniek, INDEX, ktorá vracia hodnoty z definovaných oblastí údajov, COLUMN, ktorá vracia číslo stĺpca odkazovanej bunky a pod. Tieto funkcie ponecháme na samoštúdium láskavým čitateľom.