Ačkoli kontingenční tabulky jsou fantastické nástroje pro sumarizaci dat, někdy potřebujete jednoduché a odlehčené řešení založené na vzorcích. Dobrou zprávou je, že můžete vytvářet vlastní souhrny pomocí funkcí jako COUNTIF a SUMIF. Podívejte se, jak v tomto 3minutovém videu.
Jak vytvořit jednoduchou souhrnnou tabulku
V tomto videu vám chci ukázat, jak vytvořit rychlou souhrnnou tabulku pomocí funkcí COUNTIF a SUMIF.
Zde máme ukázkovou sadu dat, která ukazuje prodej triček.
Můžete vidět, že máme sloupce pro datum, položku, barvu a částku.
Pojďme tedy rozdělit tato data podle barev.
Než začneme, chci zmínit, že kontingenční tabulky by byly vynikajícím způsobem, jak shrnout tato data, ale určitě můžete použít vzorce pro základní souhrny, a to zde ukážu.
Nejprve v datech pojmenuji sloupce barvy a množství. To není nutné, ale pojmenování těchto rozsahů znamená, že později nebudu muset používat absolutní adresy a vzorce budou opravdu krátké a snadno čitelné.
Nyní v naší souhrnné tabulce potřebujeme seznam jedinečných barev. Chcete-li vytvořit tento seznam, zkopíruji celý seznam a použiji příkaz remove duplicates v aplikaci Excel.
Pokud máte v seznamu jen několik položek, není třeba odstraňovat duplikáty. Ale je to skvělý způsob, jak vytvořit čistý seznam jedinečných hodnot, když pracujete s neznámými daty.
Nyní přidám první vzorec. COUNTIF potřebuje rozsah a kritéria. Protože počítáme barvy, rozsah je sloupec barev.
Dále potřebujeme kritéria, a to je místo, kde přichází náš seznam barev. Barvy již máme v seznamu v naší tabulce, takže mohu jen ukázat na tento sloupec a vyzvednout odkaz. Když stisknu klávesu Enter a zkopíruji vzorec dolů, dostaneme počet pro každou barvu.
Nyní rozšíříme tento souhrn o částky.
V tomto případě budeme muset použít funkci SUMIF. Stejně jako dříve, musím poskytnout barvu jako rozsah, pak vyzvednout název barvy jako odkaz na buňku v naší tabulce.
Pak musíme zadat rozsah k součtu, což je sloupec Částky.
Když zkopíruji vzorec do tabulky, máme rozpis množství podle barvy.
Nyní, abych to dokončil, mohu zkopírovat formátování ze zdrojové tabulky do souhrnné tabulky pomocí Paste Special.
Takže tady to máte, máme naši souhrnnou tabulku.
Pokud změním některá data, okamžitě se zobrazí aktualizace souhrnné tabulky.
Kurs
Základní vzorecSouvisející zkratky
Rozšířit výběr do poslední buňky dolů Ctrl + Shift + ↓ ⌃ + ⇧ + ↓ Přesunout do první buňky v listu Ctrl + Home Fn + ⌃ + ← Zobrazit dialogové okno Vložit jinak Ctrl + Alt + V ⌘ + ⌃ + V Kopírovat vybrané buňky Ctrl + C ⌘ + C
