OpenOffice.org Calc pod drobnohľadom (4) - Adresovanie buniek II.

V doterajších ukážkach práce s tabuľkovým procesorom Calc sme predpokladali, že dopredu vieme, kde máme uložené údaje pre výpočet. Tento spôsob adresácie však nevyhovuje napríklad vtedy, keď potrebujeme získať nejaký údaj na základe iného výpočtu alebo ho potrebujeme vyhľadať podľa určitých kritérií.

Adresovanie výpočtom

Ako príklad získavania údajov podľa výpočtu si môžeme uviesť zapísanie slovného znenia známky (výborný, chválitebný, dobrý, dostatočný a nedostatočný). V tomto prípade si môžeme zapísať tieto slovné znenia napr. na list, ktorý pomenujeme „znamky“.

Slovné znenie známok na liste „znamky“

Premenovanie jednotlivých listov je jednoduché – dole, na záložke s jeho názvom stlačíme pravé tlačidlo myši, čím sa nám otvorí kontextové menu, kde nájdeme funkciu „Premenovať“. Ostatný postup hádam nemusíme už popisovať (a ani ostatné funkcie, ktoré tu nájdeme – vložiť list, odstrániť list, presunúť list…).

Kontextové možnosti pre prácu s listami

Získanie adresy výpočtom

Na prvom liste budeme mať uvedené známky a podľa nich budeme chcieť určiť adresu. Na to nám poslúži funkcia ADDRESS:

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

Pri používaní tejto funkcie si musíme zvyknúť na to, že pri stĺpci sa nezadáva jeho písmenové označenie, ale poradové číslo, t.j. napr. stĺpcu „C“ zodpovedá číslo 3. Parameter relatívnosť určuje spôsob adresovania a môže nadobúdať tieto štyri hodnoty:

1 – absolútna adresa (napr. $C$7)
2 – absolútne číslo riadku (napr. B$4)
3 – absolútne písmeno stĺpca (napr. $F3)
4 – relatívna adresa (napr. H5)

Pokiaľ zadáme inú hodnotu, OpenOffice.org Calc tam automaticky dosadí hodnotu 1, t..j absolútne adresovanie. V parametri „list“ zadávame v úvodzovkách názov listu a pokiaľ pracujeme s aktuálnym, nemusíme tento parameter zadať.

Teraz si vytvorme tabuľku so známkami. V prvom stĺpci si uvedieme napr. predmety, v druhom ich známky a do tretieho budeme zadávať ich slovné znenie. Adresu, kde máme uložené slovné znenia známok získame pomocou vzorca:

=ADDRESS(B1;1;4;"znamky")

„Vypočítaná“ adresa

Získanie obsahu bunky

Týmto spôsobom však získame „iba“ adresu (napr. „znamky.A3“). My však chceme do príslušnej bunky zapísať aj samotné slovné znenie. Na tento účel musíme použiť funkciu INDIRECT, ktorá vracia obsah bunky, ktorej adresu zadáme ako jej parameter. Pretože je zbytočné, aby sme adresu z funkcie ADDRESS zapisovali do osobitnej bunky, môžeme ju priamo použiť ako potrebný parameter funkcie INDIRECT:

=INDIRECT(ADDRESS(B1;1;4;"znamky"))

Získaná hodnota z vypočítanej adresy

Túto funkciu zapíšeme do bunky C1 a následne skopírujeme do ostatných buniek v stĺpci „C“.

Ako zaujímavý spôsob využitia tohto spôsobu adresovania si môžeme uviesť tabuľku pre výpočet autorských honorárov. Predpokladajme, že sme platení podľa autorských hárkov (1800 znakov), pričom odmena nie je lineárna, ale špecificky určená, povedzme takto:

do 1 AH – 150,-
1 AH – 200,-
2 AH – 300,-
3 AH – 450,-
od 4 AH – 500,-

Špecifické hodnoty honorárov

Vytvoríme si tabuľku, kde uvedieme v liste „honorare“ uvedené sumy (čím si jednoducho zabezpečíme, že sa nám automaticky prepočítajú honoráre pri ich prípadnej zmene). Na prvom liste budeme zadávať do stĺpca „A“ názov príspevku, do stĺpca „B“ počet znakov a v stĺpci „C“ budeme chcieť vidieť príslušnú odmenu.

Ako prvé, čo musíme urobiť, je prepočítať počet znakov príspevku na autorské hárky. Na to nám poslúži funkcia TRUNC, ktorá vracia celú časť čísla:

TRUNC(B1/1800)

Prepočet počtu znakov na autorské hárky

Samozrejme, pri počte znakov menej ako 1800 dostaneme výsledok 0. To nám však nevyhovuje, pretože pre adresovanie odmien potrebujeme v tomto prípade číslo 1, ktoré označuje prvý riadok. Preto funkciu upravíme takto:

TRUNC(B1/1800)+1

Prepočet počtu autorských hárkov na číslo riadku

Podmienky vo vzorcoch

Teraz sa dostávame k opačnému prípadu – vrchnej hranici odmien, pretože od štyroch autorských hárkov sa odmena už nelíši. Preto ako maximálnu hodnotu potrebujeme číslo 5, ktoré zodpovedá tomuto rozsahu. Počet autorských hárkov musíme preto najprv otestovať a ak je výsledok väčší, ako sú 4 autorské hárky, nastavíme mu konštantnú veľkosť 5 autorských hárkov, inak ponecháme predchádzajúci výpočet. Na tento účel použijeme funkciu IF:

IF(TRUNC(B1/1800)>4;5;TRUNC(B1/1800)+1)

Obmedzenie hornej hranice počtu autorských hárkov

Funkcia IF, ktorú sme práve použili má takýto obecný tvar:

IF(podmienka; výsledok, ak je podmienka splnená; výsledok, ak podmienka splnená nie je)

Výsledok funkcie IF je v našom prípade vlastne číslo riadku, v ktorom sa nachádza príslušný autorský honorár. Preto ju použijeme vo funkcii ADDRESS v parametri pre zadanie čísla riadku. Ostatné parametre použijeme rovnako, ako sme ich použili v príklade so známkami a, ako inak, zároveň funkciu ADDRESS priamo použijeme vo funkcii INDIRECT. Do bunky C1 zapíšeme v takomto prípade nasledujúcu funkciu, ktorú potom skopírujeme do ostatných potrebných buniek v stĺpci „C“:

=INDIRECT(ADDRESS(IF(TRUNC(B1/1800)>4;5;TRUNC(B1/1800)+1);1;4;"honorare"))

Výška autorskej odmeny podľa počtu autorských hárkov

Tento príklad je však príliš konkrétny – výška honorárov je viazaná na presné autorské hárky a obmedzili sme maximálny počet členení odmien na 5. Pokiaľ sa z ľubovoľného dôvodu zmení tento počet, resp. honoráre sa nebudú vyplácať na celé autorské hárky, ale povedzme po polovičke, musíme meniť všetky vzorce. Preto je jednoduchšie urobiť si obecnejšiu tabuľku, v ktorej si uvedieme počet znakov, po koľkých sa počítajú odmeny a celkový počet členení budeme spočítavať automaticky.

Z tohto dôvodu zmeníme údaje v liste honoráre tak, že do bunky B1 si zapíšeme počet znakov, po koľkých sa počítajú odmeny. Pre orientáciu si do ostatných buniek zapíšeme údaje o počte znakov. Do bunky B2 preto vložíme vzorec:

=B1+B$1

Pomocné údaje o počte znakov

Tento vzorec následne skopírujeme do všetkých potrebných riadkov. Nakoniec si v bunke C1 spočítame počet členení autorských odmien pomocou funkcie COUNT, ktorá spočítava počet riadkov, v ktorých máme zadané iné číslo, ako 0 (táto funkcia nespočítava riadky, kde máme text):

=COUNT(A1:A65536)

Počet členení autorských odmien

Teraz môžeme upraviť vzorec pre získanie výšky odmeny nasledovne:

=INDIRECT(ADDRESS(IF(TRUNC(B5/honorare.$B$1)+1>=honorare.$C$1;honorare.$C$1;TRUNC(B5/honorare.$B$1)+1);1;4;"honorare"))

Výška autorskej odmeny podľa špecifikovaného počtu znakov

Ako vidíme, napísali sme pomerne komplikovanú kombináciu funkcií, pomocou ktorých získavame adresu bunky a následne údaj, ktorý je v nej zapísaný. Svojim spôsobom sa tento zápis podobá už malému programovaniu a preto sme uviedli jednotlivé kroky, ktoré viedli k dosiahnutiu želaného výsledku. Pre skutočné využívanie možností OpenOffice.org Calc je to však nevyhnutné, pretože pri riešení mnohých tabuliek v praxi potrebujeme ešte komplikovanejšie vzorce a preto sa musíme tieto postupy naučiť kombinovať a používať.

Nabudúce: Vyhľadávanie v definovaných oblastiach údajov.

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