5 Tabulky a seznamy dat Příklad 4 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
TÉMA: Sumarizace dat v tabulkách, přehledy
Při práci s tabulkami dat často potřebujeme seskupit záznamy dle různých kritérií. Pro jednotlivé skupiny pak lze
vkládat další údaje, ať už to jsou podrobnější informace nebo výpočty. Sekretářka společnosti „Naše zahrada“
potřebuje vytvořit přehledy jednotlivých druhů zboží a dále pak s pomocí souhrnů a slučování dat zpracuje
statistické údaje pro jednotlivé tabulky.
Zadání:
Otevřete soubor Souhrny.xlsx.
1. Na listu Nábytek vytvořte pro tabulku dat souhrn, který zobrazí počet druhů výrobků, které
se nachází nebo nenachází na skladě (zjistěte počet hodnot ano/ne ve sloupci Skladem). Pro
zjištění počtu druhů výrobků použijte sloupec ID výrobku.
2. Na listu Prodejci vytvořte pro tabulku dat souhrn objemu prodejů v jednotlivých měsících
(sečtěte položky sloupce Prodej ve skupinách vytvořených pro jednotlivé měsíce – sloupec
Měsíc).
3. Souhrn na listu Prodejci doplňte o maximum prodejů v jednotlivých městech (zjistěte
maximální položku ze sloupce Prodej ve skupinách vytvořených pro jednotlivá města –
sloupec Město). Zobrazte pouze souhrnné údaje dle náhledu.
4. Na list Celkem proveďte sloučení dat z listu Leden a Únor dle jednotlivých týdnů (dle kategorií
ve sloupci týden). Jako sumarizační funkci použijte součet dat, vytvořte propojení
na zdrojová data. Sloučená data vložte od buňky A5.
5. Na listu Zboží vytvořte ručně přehled výrobků dle jednotlivých kategorií.
6. Sešit uložte a uzavřete.
Řešení
5 Tabulky a seznamy dat Příklad 4 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
Řešení:
Karta Soubor/Otevřít (nebo tlačítko Otevřít na panelu nástrojů Rychlý přístup, popř. klávesová
zkratka Ctrl+O). V dialogovém okně Otevřít nalézt soubor dle zadání, tlačítko Otevřít. Pozn. Sešit
lze otevřít také přímo v systému Windows (dvakrát kliknout na ikonu souboru nebo kontextová
nabídka ikony/Otevřít).
1. Zobrazit list dle zadání. Aby bylo možné vytvořit souhrn (rozdělit záznamy tabulky do skupin
dle hodnot ve sloupci Skladem a provést pro tyto skupiny výpočet), je nutné mít záznamy
v tabulce seřazené právě dle sloupce Skladem (stejné údaje v tomto sloupci musí být u sebe,
jinak by skupiny nešly automaticky vytvořit) – označit libovolnou buňku tabulky, karta
Data/skupina Seřadit a filtrovat/příkaz Seřadit od A do z. Vytvoření souhrnu - označit
libovolnou buňku v tabulce dat, karta Data/skupina Osnova/příkaz Souhrn/v dialogovém
okně Souhrny nastavit parametry pro vytvoření souhrnu dle zadání: v seznamu U každé
změny ve sloupci: vybrat položku Skladem (skupiny záznamů se budou vytvářet dle položek
ve sloupci Skladem), v seznamu Použít funkci: zvolit položku Počet (budou se počítat
výrobky), v seznamu Přidat souhrn do sloupce: zatrhnout položku ID výrobku (počet výrobků
se bude počítat dle údajů tohoto sloupce), tlačítko OK. V tabulce bude automaticky vytvořen
souhrn dle zadaných požadavků: záznamy budou rozděleny do dvou skupin dle údajů ve
sloupci Skladem, na konci každé skupiny bude ve sloupci ID výrobku uveden počet výrobků,
na konci tabulky bude uveden celkový počet výrobků. Podrobné údaje v souhrnu lze
zobrazovat/skrývat pomocí znaků +/- (popř. kliknutím na číslo úrovně) v šedé oblasti, která
vznikla nalevo od tabulky dat. Pozn. Excel nabízí několik souhrnných funkcí, které lze
v souhrnech pro výpočty použít. Pokud hodláme použít složitější výpočty, musíme vytvořit
souhrn ručně. Vytvořený souhrn lze zrušit následujícím způsobem: karta Data/skupina
Osnova/příkaz Souhrn/v dialogovém okně Souhrny tlačítko Odebrat vše. Pro jeden list lze
vytvořit pouze jeden souhrn dat.
2. Zobrazit list dle zadání. Záznamy v tabulce jsou již předem seřazeny, lze tedy přistoupit
k vytvoření souhrnu – označit libovolnou buňku v tabulce dat, karta Data/skupina
Osnova/příkaz Souhrn/v dialogovém okně Souhrny nastavit parametry pro vytvoření
souhrnu dle zadání: v seznamu U každé změny ve sloupci: vybrat položku Měsíc (skupiny
5 Tabulky a seznamy dat Příklad 4 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
záznamů se budou vytvářet dle položek ve sloupci Měsíc), v seznamu Použít funkci: zvolit
položku Součet (budou se sčítat hodnoty), v seznamu Přidat souhrn do sloupce: zatrhnout
položku Prodej (sčítat se budou hodnoty sloupce Prodej), tlačítko OK. V tabulce bude
automaticky vytvořen souhrn dle zadaných požadavků.
3. Do již vytvořeného souhrnu na listu Prodejci přidáme další úroveň – označit libovolnou buňku
v souhrnu, karta Data/skupina Osnova/příkaz Souhrn/v dialogovém okně Souhrny nastavit
parametry pro další úroveň souhrnu: v seznamu U každé změny ve sloupci: vybrat položku
Město (skupiny v druhé úrovni souhrnu se budou provádět dle měst), v seznamu Použít
funkci: zvolit položku Maximum, v seznamu Přidat do sloupce: zatrhnout položku Prodej
(bude se zjišťovat největší částka ze sloupce Prodej), zrušit zatržení položky Nahradit aktuální
souhrny! (jinak by nebyla přidána další úroveň již existujícímu souhrnu, ale celý souhrn by byl
nahrazen jiným!), tlačítko OK. Do již existujícího souhrnu bude přidána další, podrobnější
úroveň. Zobrazení pouze souhrnných údajů – podrobnosti lze skrýt kliknutím na + nebo
na číslo 3 v šedé oblasti nalevo od souhrnu.
4. Zobrazit list dle zadání, označit buňku A5, karta Data/skupina Datové nástroje/příkaz
Sloučit…/v dialogovém okně Sloučit vybrat funkci pro výpočet – Součet, do pole Odkaz vložit
odkaz na buňky tabulky listu Leden (Leden!A5:O37), tlačítko Přidat, obdobným způsobem
vložit odkaz na buňky tabulky listu Únor (Únor!A5:O37), tlačítko Přidat, zatrhnout obě
položky ve skupině Použít popisky, zatrhnout Vytvořit propojení na zdrojová data, tlačítko
OK. Na listu bude vytvořen souhrn, který je výsledkem sloučení údajů ze dvou tabulek.
Propojení na zdrojová data zajišťuje, že se jakékoliv změny v tabulkách na listech Leden
a Únor projeví také ve výsledném souhrnu. Pozn. Vytváření souhrnů pomocí slučování více
tabulek lze pouze v případě, že mají tabulky stejnou strukturu (nebo záhlaví sloupců/řádků
nesou stejný název).
5 Tabulky a seznamy dat Příklad 4 Excel 2010
OPF v Karviné, Slezská univerzita v Opavě Kateřina Slaninová
5. Zobrazit list dle zadání, označit řádky první skupiny (řádky 4:18), karta Data/skupina
Osnova/příkaz Seskupit. Vybrané řádky budou seskupeny, zobrazení/skrytí podrobností
skupiny lze provést pomocí znaku + v šedé oblasti nalevo od skupiny. Obdobným způsobem
vytvořit ručně skupiny také pro ostatní kategorie. Pozn. Ruční vytváření přehledů se používá
v případech, kdy potřebujeme provádět pro jednotlivé skupiny složitější výpočty (kdy nám
nabízené souhrnné funkce nestačí), popř. když nelze vytvořit skupiny automaticky.
6. Uložit sešit - karta Soubor/Uložit (nebo tlačítko Uložit na panelu nástrojů Rychlý přístup nebo
klávesy Ctrl+S). Zavřít sešit - karta Soubor/Zavřít (nebo tlačítko Zavřít – x v pravém horním
rohu okna).
Zpět na zadání