V dnešním videu si ukážeme, jak v excelové tabulce pracovat s řádkem souhrnu. V jednom z minulých videí jsme si ukázali deset důvodů, proč v Excelu pracovat s excelovými tabulkami. Dnes si ukážeme další výhodu excelových tabulek, kterou je řádek souhrnů. Jedná se o výpočtový řádek, který může na jedno kliknutí přidat do excelové tabulky a provádět v něm výpočty za pomoci rozbalovacího seznamu. A to vše bez použití jediné excelové funkce. V minulých videích jsme si rovněž ukázali, jak pracovat se seskupováním řádků a sloupců a se souhrny v excelových tabulkách.
Excelový soubor ke stažení
Řádek SUBTOTAL v excelové tabulce
V příkladu máme tabulku dat, kterou nejprve změníme na excelovou tabulku. Klikneme do tabulky a pomocí klávesové kombinace CTRL+T změníme obyčejnou tabulku na excelovou tabulku. Ještě potvrdíme, že tabulka má záhlaví.
Do každé excelové tabulky můžeme přidat řádek celkového součtu. Jediné, co musíme udělat je, že klikneme do tabulky a na kartě Návrh tabulky zaškrtneme pole Řádek souhrnů. Tím se na spodek excelové tabulky přidá součtový řádek. Automaticky se sečetl poslední sloupec marže. Když na součtové číslo klikneme, tak zjistíme, že Excel automaticky uplatnil funkci SUBTOTAL a nejen to, ve funkci SUBTOTAL použil jako funkci 109, tedy funkci SUMA.
Výhodou tohoto celkového řádku v excelové tabulce je to, že nejen, že pomocí něho můžeme velmi rychle měnit výpočty bez toho, abychom se opakovali s excelovými funkcemi, ale protože je ve výpočtu použitá funkce SUBTOTAL, tak kdykoliv vyfiltrujeme excelovou tabulku, tak nám funkce ukáže výpočet jen pro viditelné buňky. Na rozdíl od funkce SUMA. Kdybychom vedle tabulky provedli kontrolní výpočet pomocí funkce SUMA, tak po vyfiltrování tabulky bude funkce SUMA ukazovat stále původní výpočet, jelikož funkce SUMA počítá i se skrytými řádky, kdežto funkce SUBTOTAL specificky pracuje pouze s viditelnými řádky.
Když klikneme do funkce SUBTOTAL k číslu 109 a smažeme ho, tak se ve funkci SUBTOTAL rozbalí celá nabídka funkcí, které máme k dispozici. Ve výpočtu tak můžeme použít součet, počet, průměr, minimální a maximální hodnoty až po statistické výpočty směrodatných odchylek a rozptylů. Všimněte si, že máme ovšem na výběr vždy dvě funkce. V seznamu máme od každého výpočtu dvě variace. Jedny výpočty jsou uvedené číslem od 1 do 11. Druhá sada výpočtů má před čísly stovky, tedy funkce od 101 do 111. Rozdíl mezi těmito funkcemi je v tom, že výpočty, které začínají stovkou ignorují kromě vyfiltrovaných řádků i řádky skryté. Ukážeme si rozdíl. Ve výpočtu vybereme součet, tedy funkci 9. Funkci potvrdíme. Zkusíme některé řádky skrýt. Označíme vybrané řádky, pravé tlačítko myši a Skrýt. Vidíme, že se výpočet celkového součtu nezměnil, jelikož máme ve funkci SUBTOTAL funkci 9, tedy funkci, která do výpočtu započítává i skryté řádky. Klikneme do funkce SUBTOTAL a změníme funkci z 9 na 109, potvrdíme a vidíme, že se výpočet okamžitě přepočítal, jelikož funkce SUMA pod číslem 109 ignoruje skryté řádky.
Když klikneme na číslo celkového součtu, tak si všimněte, že se vedle čísla objeví rozbalovací seznam. Řádek celkového součtu nám totiž umožňuje změnit výpočet, nejsme tedy odkázání jen na součet, ale můžeme si vybrat ze všech funkcí, které najdeme ve funkci SUBTOTAL. Místo součtu vybereme třeba počet a vrátí se nám počet čísel v tabulce. Kdybychom vybrali průměr, tak celkový řádek vrátí průměr hodnot ve sloupci.
Výpočet pomocí funkce SUBTOTAL můžeme přidat do každého sloupce, a to nejen do sloupce, který obsahuje čísla. Můžeme tedy kliknout do sloupce tržba a manuálně vybrat výpočet, který si přejeme pro sloupec provést. V každém sloupci můžeme nastavit libovolný výpočet. U sloupce tržby tedy klidně můžeme nastavit součet, u sloupce s náklady průměr a u sloupce marže třeba maximální hodnotu. Funkci SUBTOTAL můžeme použít i u sloupce s produkty, která obsahuje textové hodnoty. Jedinou funkci, kterou zde ale můžeme využít je funkce POČET. Funkce POČET spočítá počet textových hodnot ve sloupci. Výpočet si můžeme vybrat i v buňce, kde teď máme napsáno celkem. Když na buňku klikneme, tak i zde máme funkci SUBTOTAL, jelikož se nachází pod datumovým sloupcem. Můžeme si zde tedy rovněž vybrat výpočet, třeba maximální nebo minimální datum ve sloupci. Máme rovněž možnost přepsat slovo celkem na něco jiného. Klikneme do buňky, smažeme funkci a místo slova celkem napíšeme třeba Výpočty. Potvrdíme a slovo celkem se změnilo na Výpočty.
Stejně tak, pokud bychom pod nějakým sloupcem výpočet nechtěli, tak buď máme možnost vybrat v buňce výpočtu žádný, čímž výpočet zmizí.
Pokud chceme řádek celkového součtu z excelové tabulky odstranit, tak klikneme do tabulky a na kartě Návrh tabulky odškrtneme pole Řádek souhrnů, a tím celkový řádek zmizí.
Znovu klikneme do excelové tabulky a znovu zobrazíme celkový řádek. Jak jsme si řekli už na začátku, tak funkce SUBTOTAL vloží součet pouze do posledního sloupce v tabulce. Jak ale rychle zařídit, aby se stejná funkce objevila rovnou u všech sloupců? Představme si, že máme v tabulce desítky sloupců, nechceme následně jít buňku po buňce a u každého sloupce měnit výpočet. Pokud by vás napadlo výpočet z poslední buňky označit, zkopírovat a vložit do ostatních buněk, tak budete nemile překvapeni, jelikož se výpočet bude stále odkazovat na původní sloupec. Stejně jako kopírování nefunguje na řádku celkového součtu ani klávesová zkratka CTRL+R, která kopíruje obsah první buňky doprava. Pokud bychom tedy nechali pouze výpočet sumy v první buňce, označili ostatní buňky a použili CTRL+R, tak se všude nakopíruje sice suma, ale s odkazem na první sloupec.
Řešením je funkci v celkovém řádku jednoduše přetáhnout. Klikneme na výpočet, najedeme myší na pravý spodní roh, až se z kurzoru myši stane kříž, chytneme funkci a přetáhneme ji do ostatních buněk. Teď je výpočet správný a funkce se odkazuje na správné sloupce.
Další možností, jak do řádku celkového součtu rychle vložit součty, je použít automatickou sumu. Nicméně tento přístup má jednu zvláštnost. Smažeme výpočty na řádku celkového součtu. Označíme buňky, kam chceme součty vložit a použijeme klávesovou kombinaci ALT =. Tato kombinace vloží do označených buněk automatickou sumu. Výhodou je, že se nám i při použití automatické sumy vložila do buňky funkce SUBTOTAL a nikoliv prostá funkce SUMA. Zvláštností ovšem je to, že se automatická suma odkazuje na rozpětí buněk, nikoliv na název sloupců. Jelikož ale pracujeme s excelovou tabulkou, tak se nemusíte bát, že by nově přidané buňky nebyly zahrnuté do výpočtu. Do excelové tabulky vložíme několik nových řádků, kam zkopírujeme poslední hodnoty z tabulky a jak vidíme, tak se výpočty okamžitě rozšíří, a to i přesto, že původní rozpětí buněk v automatické sumě tyto buňky neobsahovalo. Klikneme na jeden výpočet a vidíme, že se rozpětí automaticky rozšířilo.
I po použití automatické sumy nám zůstane možnost měnit výpočty v buňce, a další zajímavostí je, že jakmile změníme výpočet z automatické sumy například na průměr, tak se rozpětí buněk změní na odkaz na sloupec.
Trik na to, jak u každého sloupce rychle změnit výpočet ze sumy například na průměr je pomocí funkce najít a nahradit. Smažeme obsahu buněk a ukážeme si to celé od začátku. Označíme buňky, a pomocí klávesy ALT = vložíme do buněk automatickou sumu. Necháme buňky označené a rovnou vyvoláme funkci najít a nahradit pomocí CTRL+H. Hledáme funkci 109 a abychom ošetřili, že se například nezmění odkaz na buňku, tak za 109 napíšeme ještě středník. A funkci 109 pro sumu chceme nahradit funkcí 101 pro průměr a se středníkem. Potvrdíme a naráz se změnila ve všech buňkách suma na průměr.
Tak toto bylo něco málo k řádku souhrnu v excelových tabulkách. Pokud se vám video líbilo budeme rádi, pokud se stanete členem Akademie Excelu na našem webu. Členství je zdarma a můžete ho kdykoliv zrušit. A já se zatím budu těšit u dalšího videa.
Jedna odpověď
😊