OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

Ke zjišťování různých praktických informací o buňce slouží funkce CELL. Její syntaxe je =CELL(jaká_informace; o_jaké_buňce). Těmi nejužitečnějšími jsou číslo řádku a sloupce dané buňky, název listu a souboru včetně úplné cesty, číslo listu a absolutní adresa. Vše je vidět na obrázku.

Nejpraktičtější informace funkce CELL

Zjišťují se informace o buňce B7 – ta má absolutní adresu $B$7, nachází se na 7. řádku a ve 2. sloupci v souboru 1.ods a v listu CELL, který je 1. listem v daném sešitu. Zde je potřeba upozornit na jeden nedostatek v případě informace filename - po změně názvu listu bohužel nedojde k automatické změně výsledku daného vzorce; ten se aktualizuje např. až po přidání či odebrání jiného listu nebo uložením a znovuotevřením sešitu. Funkce CELL umí vrátit i další informace o buňce, které však nejsou prakticky využitelné.

Někdy se může hodit funkce INFO, která vrací údaje o prostředí, např. =INFO("system") vrací „LINUX“, „SOLARIS“, nebo „WNT“ pro Microsoft Windows. Užitečnější bude zřejmě =INFO("release"), tj. informace o aktuální verzi OpenOffice.org (např. „300m15(Build:9379)“), popř. =INFO("recalc"), čímž zjistíte režim přepočtu vzorců (automaticky či ručně).

Pomocí CELL můžete snadno vyřešit následující problém: ve sloupci A jsou údaje, kdy na lichých řádcích je ulice a číslo popisné, pod nimi na sudých řádcích je pak odpovídající město a vy potřebujete tyto údaje spojit k sobě do jedné buňky ve sloupci B.

Využití funkce CELL pro určení lichých a sudých řádků

V buňce B1 je vzorec vzorec

=IF(ISODD(CELL("row"));A1&" "&A2;"")

který byl dále rozkopírován do ostatních buněk sloupce B. Jeho interpretace je následující: jestliže (IF) se buňka se vzorcem nachází na lichém (ISODD) řádku (CELL), pak jako výsledek vrať spojení buněk A1 a A2, jinak vrať prázdný řetězec (""). Tudíž vzorce, které se nacházejí v buňkách na sudých řádcích, vracejí jako výsledek prázdný řetězec (vizuálně nic).

Pro některé výše uvedené parametry CELL existují přímo odpovídající funkce, např. ROW, COLUMN a SHEET, tzn. =CELL("row") je stejné jako =ROW(). CELL pak využijete zejména v případě, kdy v něm chcete dynamicky měnit parametr jaká_informace.

Jistým způsobem opačnou funkcí k CELL je ADDRESS, která převádí adresu buňky uvedenou jako počet řádků a sloupců na její textovou adresu, např. =ADDRESS(2;2) vrátí „$B$2“. Takže zápis =CELL("address") je ekvivalentní zápisu =ADDRESS(CELL("row");CELL("col")). ADDRESS lze navíc třetím parametrem sdělit, jestli má vrátit odkaz absolutní, relativní, nebo smíšený.

Velmi šikovná funkce je INDEX, která umí z nastavené oblasti vrátit obsah buňky z určitého řádku a sloupce, např. z oblasti C2:E5 obsah buňky z jejího 2.řádku a 2.sloupce, což je buňka D3. Vše je jasné z následujícího obrázku.

Snadné otočení matice pomocí funkce INDEX

Jednoduše, pomocí indexů v záhlaví řádků a sloupců, tak můžete libovolně otočit či transponovat libovolnou oblast/matici. Pokud si vhodně zafixujete oblast, řádky a sloupce pro kopírování, pak je to vše otázka jedné minuty.

Lehce podobnou funkcí je OFFSET, která ovšem dokáže vracet odkazy na celé oblasti. Vše bude opět nejlépe zřetelné z krátkého příkladu.

Možnosti funkce OFFSET

V kratším tvaru =OFFSET(odkaz;řádky;sloupce) vrací funkce obsah buňky, která je relativně posunuta vůči odkazu o daný počet řádků a sloupců, např. =OFFSET(A1;2;2) vrátí obsah buňky C3.

Delší forma OFFSET(odkaz;řádky;sloupce;výška;šířka) vrací celou oblast buněk s počtem řádků „výška“ a sloupců „šířka“. Tento tvar je použit i ve výše uvedeném příkladu. Buňky označené stejnou barvou patří k sobě, tj. v buňce E11 je vzorec =SUM(OFFSET($C$2;0;0;$B11;E$8)), jehož výsledkem je suma buněk C2:E4. Síla OFFSET spočívá v tom, že lze jejími parametry odkazovat na jiné buňky, a tak dynamicky měnit pozici a rozsah vracené oblasti, s níž lze dále pracovat (SUM, COUNTIF aj.).

Zajímavou a občas nepostradatelnou funkcí je INDIRECT pro nepřímý odkaz na buňku či oblast pomocí textu. Vzorec =INDIRECT("E10") v buňce A1 vrátí hodnotu z buňky E10. Pokud přidáte nebo odstraníte sloupce před buňkou E10, pak se v A1 objeví obsah z té buňky, která se nově dostala na pozici E10. V případě, že byste měli v buňce A1 přímý odkaz pomocí vzorce =E10, tak by došlo s přidáním či odebráním sloupců k jeho automatické úpravě. Stejného efektu jako při INDIRECT můžete dosáhnout i pomocí OFFSET, zde by to bylo =OFFSET(A1;9;4), což je ovšem méně vypovídající. Případně můžete použít zřejmější =INDIRECT(ADDRESS(10;5)).

V INDIRECT můžete použít také odkaz na buňku, ovšem tato odkazovaná buňka musí obsahovat adresu nějaké buňky či oblasti: např. v A1 je =INDIRECT(B1) a v B1 je hodnota „E10“, popř. vzorec =ADDRESS(5;10).

Kromě této vlastnosti je další výhodou INDIRECT možnost tvořit odkazy jako skládaný text. Nejlépe vše opět objasní praktický příklad.

Využití funkce INDIRECT

V oblasti A10:D26 jsou původní data výdajů a příjmů za měsíc. Nová data jsou vždy vkládána jako nový řádek za řádek č.9. V oblasti A2:G4 jsou pak přeskládaná data pro graf pomocí funkce INDIRECT, kterou neovlivní vkládání nových řádků do oblasti s původními daty. Žlutě označené buňky jsou pouze pomocné údaje, které slouží pro jednoduché přeuspořádání dat pro graf.

V buňce B3 je vzorec =INDIRECT($H3&B$5), který po vyzvednutí hodnot z buněk H3 a B5 vytvoří nepřímý odkaz =INDIRECT(D15), což je příjem za prosinec 2008. Pomocí vhodného zafixování řádků, resp. sloupců a zkopírováním vzorce do dalších buněk lehce vytvoříte ostatní nepřímé odkazy. Graf je pak navázán na oblast A2:G4. Změnou hodnoty v buňce B5 lze snadno ovlivňovat, kterých posledních 6 měsíců se v grafu objeví.

Calc disponuje ještě funkcemi pro zjištění velikosti odkazované oblasti: COLUMNS vrací počet sloupců, ROWS počet řádků a SHEETS počet listů, přičemž =SHEETS() vrátí počet listů v sešitu, =COLUMNS(A1:C3)*ROWS(A1:C3) pak počet buněk v oblasti A1:C3, tj. 9.

Stáhněte si uvedené příklady jako dokumenty pro Calc: Pokročilé adresování buněk v Calcu