Jak sloučit data z více tabulek

LO_Calc.png Představte si, že máte na různých místech v listě nebo dokonce v sešitu nějaké tabulky, jejichž obsah si přejete sloučit – a přitom vstupní hodnoty ještě například sečíst. K tomuto účelu lze použít funkci Konsolidovat, o níž je tento článek.  

Jak to funguje

Při konsolidaci se sloučí dvě a více tabulek (které musí mít stejnou strukturu) do jedné tak, že se zachovají všechny vstupní hodnoty (ty budou skryty) a ke každým odpovídajícím řádkům se připojí ještě jeden s výsledkem provedené operace (např. určení maxima, průměru atd.). Při konsolidaci lze určit řadu parametrů, a ty si níže popíšeme.

Jak na to

Potřebujete alespoň dvě tabulky, které budou mít stejnou strukturu, to znamená stejný počet řádků a sloupců a pokud budete brát do úvahy popisky sloupců a řádků, pak i ty musí být stejné. Jako příklad lze uvést prospěch stejných žáků v různých ročnících, jak jsme použili i v našem příkladu.

Na listě 1.A tabulky je seznam osmi žáků a jejich známky v matematice a biologii. Na listě 2.A je prospěch stejných žáků ve stejných předmětech.

Předtím, než se pustíte do konsolidace samé, je možné si oblasti buněk, které budete konsolidovat, uložit jako oblast. To lze provést několika způsoby, například si označte na druhém listu vybranou oblast myší a v Poli názvu zadejte platný název oblasti. Ten může tvořit písmena, číslice a podtržítka. Nazveme tuto oblast třeba „List2“. Odpovídající oblast na prvním listu necháme nepojmenovanou.

Pojmenování vybrané skupiny buněk Pojmenování vybrané skupiny buněk

Připomínáme, že tabulky samozřejmě mohou být umístěny kdekoliv, třeba i na stejném listu. Na různé listy jsme je přidali proto, aby byla úloha zajímavější.

Vraťte se na první list a z nabídky Data vyberte Konsolidovat. Spustí se okno jako na obrázku.

Spuštěné okno Konsolidovat – zatím bez parametrů Spuštěné okno Konsolidovat – zatím bez parametrů

Nejprve přidáme oblasti, které si přejeme konsolidovat. Ty se zapisují do pole Zdrojová oblast dat. Klepněte do tohoto pole a označte oblast A3:C9 na prvním listu – můžete si pomoci stisknutím tlačítka Zmenšit vpravo od tohoto pole. Jakmile budete mít souřadnice oblasti v tomto poli, stiskněte tlačítko Přidat. Do části Oblast konsolidace se vloží vámi definovaná oblast.

Oblast konsolidace lze vybrat ručně... Oblast konsolidace lze vybrat ručně...

Takto můžete postupovat dál a definovat další oblasti, anebo můžete použít již definovanou oblast – jelikož jste vytvořili oblast a pojmenovali ji List2, můžete ji použít. Stačí pojmenovanou oblast vybrat pomocí rozbalovacího tlačítka.

Výběr předem definované oblasti Výběr předem definované oblasti

Dále je třeba vybrat oblast, do níž se tabulky zkonsolidují. Vybírá se přitom první buňka oblasti. Klepněte myší do pole Kopírovat výsledky do: a klepněte myší do nějaké prázdné buňky. Při použití v praxi buďte opatrní, abyste si obsah buněk nepřepsali – zvolte dostatečně prázdnou plochu.

Zvažte, jakou Funkci chcete při konsolidaci použít. Výchozí je Průměr, ale můžete použít i jiné. Některé, jako jsou například Součet, Průměr, Počet asi použijete často, nicméně lze použít i dvě, resp. čtyři statistické funkce. První je SměrOdch, což znamená směrodatná odchylka. Ve výpočtech je reprezentována funkcí STDEV pro směrodatnou odchylku výběru, resp. STDEVP pro směrodatnou odchylku základního souboru. Jak si můžete na obrázku všimnout, můžete si při konsolidaci vybrat jednu z nich. Podobně je to při použití Rozptylu, ve funkcích VAR a VARP podle toho, na jakém základě se počítají vstupní data.

Při konsolidaci tabulek lze využít 11 funkcí Při konsolidaci tabulek lze využít 11 funkcí

Tímto způsobem si tak ušetříte spoustu práce při následném zpracování – samozřejmě by nebyl problém použít libovolnou jinou operaci a do příslušných buněk (jak níže uvidíte) patřičné funkce dopsat, ale bylo by to zbytečně pracné.

Vyberme si třeba Průměr. Po stisknutí OK se vloží zkonsolidovaná tabulka – viz obrázek.

Zkonsolidovaná tabulka Zkonsolidovaná tabulka

Jak si můžete všimnout, vlevo od levé lišty se zobrazila tlačítka v podobě +. Ty indikují skryté oblasti, které lze zobrazit klepnutím na toto +. Hromadně je lze zobrazit klepnutím na číslici „2“ v záhlaví. Pokud tak učiníte, zjistíte, co se vlastně při konsolidaci stalo.

Zobrazené buňky odhalí vložená data z tabulek Zobrazené buňky odhalí vložená data z tabulek

Vidíte, že se zkopíroval obsah prvního řádku první tabulky, pod něj se zkopíroval obsah prvního řádku druhé tabulky (a tak dále…) a pod tyto řádky se vložil výsledný řádek s obsahem odpovídajícím použité funkci. Můžete tak hned vidět:

  • Jak se daný žák v průběhu času zlepšoval či zhoršoval,
  • Jaký je jeho aktuální průměr známek z daného předmětu (či jiný údaj).

Odpovídající řádky jsou označeny listem, z něhož tabulka pochází. Název se vkládá za nadpis řádku.

A další možnosti

To ovšem není zdaleka vše. Odstraňte řádky zkonsolidované tabulky anebo několikrát stiskněte [Ctrl + Z], dokud nebude tabulka odstraněna. Znovu spusťte okno konsolidace. Vidíte, že vstupní podmínky zůstaly zachovány – což je velmi pozitivní. Rozklepněte sekci Možnosti (v OpenOffice je patrně stále starší název Konsolidovat) a podívejte se na možnosti, které máte.

Při konsolidaci se nemusí brát v úvahu popisky řádků, resp. sloupců – může se to hodit, ale většinou to asi bude spíš vytvářet zmatek. Hodit se to bude tehdy, pokud popisky tabulek nejsou stejné (pak tomu ovšem musíte přizpůsobit oblasti výběru). Velice zajímavou možností ale je možnost synchronizace se zdrojovými daty, lépe řečeno udržení si vazby na ně. K tomu slouží pole Odkaz na zdrojová data.

I to zůstává, podobně jako předchozí, ve výchozím stavu aktivní. Co to znamená? Nechte pole zaškrtnuté a stiskněte OK. Pozměňte některé hodnoty v nějaké zdrojové tabulce. Vidíte, že se automaticky konsolidovaná tabulka přepočítává.

Jaké další možnosti máte při konsolidaci Jaké další možnosti máte při konsolidaci

Teď zkopírujte vytvořenou tabulku a umístěte ji někam jinam. I tehdy vazba zůstává zachována. Dokonce můžete na původní či jiné místo vložit další konsolidovanou tabulku a další a další… stále vazba funguje, a to u všech. Jen si musíte dávat pozor, abyste se nepokoušeli vložit konsolidovanou tabulku nad zdrojová data – program vám to jednoduše nedovolí.

Autor používá LibreOffice 4.3.

(Jako ve škole) Průměr: 1.36 | Hodnotilo: 11
 

Komentáře

user avatar PavelBisik
Odpovědět
Jak sloučit data z více tabulek
16. 03. 2016, 14:14:18
Zdravím,

Omlouvám se, ale ruka byla rychlejší jak mozek, chybně jsem odklikl hodnocení, chtěl jsem dát, že je super, že návody vycházejí a vyložil si to tak, že 5 je nejvíce a až poté jsem dočetl, myšlenku. Známkujte jako ve škole.
Tím jsme změnil nechtěně průměr hodnocení.

Pěkný den přeji

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.

 
Petr Valach

Petr Valach

Redaktor & editor OpenOffice.cz a LinuxEXPRES.cz. Správce sociálních médií těchto redakcí. Člen nadace The Document Foundation. Ve svém volnu se věnuje popularizaci vědy a fotografování.

 
 
 
woo jaw demo hz