Pokročilé adresování buněk v Calcu

calc.png V tomto díle se dozvíte, jak zjišťovat různé informace o buňkách a oblastech a jak lze snadno a pružně vytvářet a ovládat odkazy na buňky.  

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 CELLNejpraktič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ů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ý.

Vzorec

Výsledek

=CELL(1;1;1)

$A$1

=CELL(1;1;2)

A$1

=CELL(1;1;3)

$A1

=CELL(1;1;4)

A1

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 INDEXSnadné 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 OFFSETMož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 INDIRECTVyuž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

(Jako ve škole) Průměr: 1,13 | Hodnotilo: 15
 

Komentáře

user avatar mucha
Odpovědět
Pokročilé adresování buněk v Calcu
3. 06. 2009, 11:03:54
Dobrý den,
existuje nějaká funkce, která každý řádek/sloupec buňky zkontroluje na nějakou hodnotu a pokud tam bude správná hodnota, provede nějaký výpočet? teoreticky něco jako hledání po sloupcích, řádkách, nějakou hodnotu a pokud jí najdu tak s ní provedu nějaký výpočet..?
user avatar hanus
Odpovědět
Pokročilé adresování buněk v Calcu
3. 06. 2009, 20:24:35
To je příliš obecná otázka. Máte na mysli něco jako toto?
=IF(COUNTIF(A1:B3;"aa")>0;1;0)
pokud se v oblasti A1:B3 nachází buňka obsahující přesně "aa", pak bude výsledkem 1, jinak 0.
COUNTIF podporuje regulární výrazy (musí být zapnuto v Calcu, vizte jiný díl), tak se to dá vylepšit jako:
=IF(COUNTIF(A1:B3;".*aa.*")>0;1;0)
tj. v buňkách hledá kdekoli řetězec aa
user avatar mucha
Odpovědět
Re:Pokročilé adresování buněk v Calcu
7. 06. 2009, 13:30:35
no ano, to umí ve více buňkách hledat, jenže s tím se poté nedá nic dělat. Jenže já potřebuji z toho zjistit na jaké pozici (řádek, sloupec) se nachází, abych poté mohl dále s toutou nalezenou buňkou pracovat a to se mi stále nedaří..
user avatar hanus
Odpovědět
Re:Re:Pokročilé adresování buněk v Calcu
10. 06. 2009, 23:12:18
Tak ukažte, co konkrétně chcete, pořád to není jasné.
Např. v E10 mám nějakou hodnotu, ke které chci přičíst 5, pokud se taková hodnota nachází v oblasti A1:B3, jinak ji nechat stejnou. To udělám tak, že např. do E11 vložím:
=IF(COUNTIF(A1:B3;E10)>0;E10+5;E10)
user avatar tomik
Odpovědět
Pokročilé adresování buněk v Calcu
1. 11. 2011, 19:39:16
Dobrý den,
a lze nějak vyřešit toto:
Mám ve sloupci A na každém řádku název listu (např. A2=List1). V dalších sloupcích chci zobrazovat hodnoty buněk z různých listů. Například ve sloupci B chci zobrazit obsah buňky C5 z různých listů podle sloupce A. Ručně tedy napíšu do B2=List1.C5. Pokud však takto zapsaný vzorec rozkopíruji do dalších řádků, tak musím ručně přepsat názvy listů. Ty však mám ve sloupci A, ale jak to zapsat do vzorce?

Děkuji za odpověď.
user avatar Sedláček Daniel
Odpovědět
Re:Pokročilé adresování buněk v Calcu
1. 11. 2011, 21:25:44
Dobrý den,
zkuste =INDIRECT(A2&".C5") vzorec složí dohromady List1.C5 a vrátí obsah buňky z dané adresy.

Toto je dva roky starý článek, směrujte prosím své dotazy do fóra. Děkuji.
user avatar Pavel
Odpovědět
Pokročilé adresování buněk v Calcu
4. 11. 2011, 21:54:55
Dobrý den, potřebuji udělat v Caclu databázi, jde o něco jako vysvědčení.. V jendom sloupci jsou předměty, ve druhém známky, které se zadávají seznamem (kontrola vstupních dat). Pod tuto tabulku potřebuji spočítat průměr, ale s tím, aby se napsala např. pomlčka, pokud nebude alespon jedna ze známek vyplněna, dále potřebuji rozhodnout, zda je žák klasifikován, či nikoliv (pokud jsou vyplněny všechny známky, tak ano, jinak ne) a v poslední řadě potřebuji rozhodnout, zda prospěl nebo neprospěl (vázáno na 5 v daném sloupci). Nemělo by jít o nic těžkého, ale nedokážu si s tím poradit. Děkuji za všechny návrhy řešení.
user avatar Sedláček Daniel
Odpovědět
Re:Pokročilé adresování buněk v Calcu
5. 11. 2011, 07:54:27
Položte dotaz ve fóru:
http://forum.openoffice.cz/viewforum.php?id=4

Jak jsem psal výše, toto je dva roky starý článek

Děkuji
user avatar neutr
Odpovědět
Re:Pokročilé adresování buněk v Calcu
5. 11. 2011, 21:16:27
Jestli rozumím dobře, tak jde o 1 formulář s několika okny do kterých se podle provolby zadají žákovy generálie a výsledky. Následně by tento formulář měl vyhodnotit několik podmínek a vrátit nějaký výsledek. Opravdu by to asi chtělo vidět jak vypadá zápis (databáze) ze které se má načítat. Ale snad poradím i tak. Nejlepší je na tohle OFFSET. Je - li například zdroj v řádcích (je to jedno umí i sloupce a jinak provedené zdroje dat) Každá kolonka = jiná část databáze, ale společné číslo posun řádku a sloupce (tedy podle struktury to může být například konstantní sloupec, a měnitelné číslo řádku, nebo opačně, a nebo oba údaje proměnlivé).
user avatar Tomáš Galla
Odpovědět
Připnutí buněk
8. 10. 2015, 12:53:10
Dobrý den,

poradí mi, prosím, někdo, jak připnu buňky ke sloupcům i řádkům?

Mám jednoduchou tabulku pro rezervační systém, kde jednotlivé sloupce představují
dny a řádky čas. Potřebuji docílt toho, aby když roluji s tabulkou, tak aby mi tyto
zůstávali viditelné.

Děkuji za opodvěď.

Tomáš Galla
user avatar strepon
Odpovědět
Re:Připnutí buněk
8. 10. 2015, 18:27:10
Okno → Ukotvit, viz https://help.libreoffice.org/Calc/Freezing_Rows_or_Columns_as_Headers/cs

Přidat názor

 

Nejsou podporovány žádné značky, komentáře jsou jen čistě textové. Více o diskuzích najdete v nápovědě. Diskuzi můžete sledovat pomocí RSS kanálu.

 
Tomáš Hanusek

Tomáš Hanusek

 
 
 
woo jaw demo hz