Kontingenční stoly jsou dobré pro každého – rychle kalkulují, jsou flexibilně konfigurovatelné a lze do nich v případě potřeby elegantně navinout design. Ale je tu také několik much, zejména nemožnost vytvořit souhrn, kde by oblast hodnot neměla obsahovat čísla, ale text.
Zkusme toto omezení obejít a vymyslet „pár berliček“ v podobné situaci.
Předpokládejme, že naše společnost přepravuje své výrobky v kontejnerech do několika měst v naší zemi a Kazachstánu. Kontejnery jsou odesílány maximálně jednou za měsíc. Každý kontejner má alfanumerické číslo. Jako počáteční údaje je standardní tabulka se seznamem dodávek, ze které je potřeba udělat nějaký souhrn, abyste jasně viděli počty odeslaných kontejnerů do jednotlivých měst a každý měsíc:
Pro usnadnění udělejme tabulku s počátečními údaji předem „chytrou“ pomocí příkazu Domů – Formát jako tabulka (Domů — Formátovat jako tabulku) a dát jí jméno Dodávky Karta Stavitel (Design). V budoucnu to zjednoduší život, protože. přímo ve vzorcích bude možné použít název tabulky a jejích sloupců.
Metoda 1. Nejjednodušší – použijte Power Query
Power Query je super výkonný nástroj pro načítání a transformaci dat v Excelu. Tento doplněk je ve výchozím nastavení zabudován do Excelu od roku 2016. Pokud máte Excel 2010 nebo 2013, můžete si jej stáhnout a nainstalovat samostatně (zcela zdarma).
Celý proces jsem pro názornost analyzoval krok za krokem v následujícím videu:
Pokud není možné použít Power Query, můžete jít jinými způsoby – prostřednictvím kontingenční tabulky nebo vzorců.
Metoda 2. Pomocné shrnutí
Do naší původní tabulky přidáme ještě jeden sloupec, kde pomocí jednoduchého vzorce vypočítáme počet každého řádku v tabulce:
Je zřejmé, že -1 je potřeba, protože v naší tabulce máme jednořádkové záhlaví. Pokud vaše tabulka není na začátku listu, můžete použít trochu složitější, ale univerzální vzorec, který vypočítá rozdíl v číslech aktuálního řádku a záhlaví tabulky:
Nyní standardním způsobem sestavíme na základě našich dat kontingenční tabulku požadovaného typu, ale v poli hodnoty pole vypustíme Číslo řádku místo toho, co chceme kontejner:
Vzhledem k tomu, že nemáme ve stejném městě několik kontejnerů ve stejném měsíci, naše shrnutí ve skutečnosti neuvede množství, ale čísla řádků kontejnerů, které potřebujeme.
Kromě toho můžete na kartě vypnout velké a mezisoučty Konstruktor – Obecné součty и Mezisoučet (Design – celkové součty, mezisoučty) a na stejném místě tlačítkem přepnout souhrn do pohodlnějšího rozložení tabulky Nahlásit maketu (Rozvržení přehledu).
Tím jsme již v polovině výsledku: máme tabulku, kde je na křižovatce města a měsíce číslo řádku ve zdrojové tabulce, kde leží kód kontejneru, který potřebujeme.
Nyní zkopírujeme souhrn (na stejný list nebo jiný) a vložíme jej jako hodnoty a poté do oblasti hodnot zadejte náš vzorec, který extrahuje kód kontejneru podle čísla řádku nalezeného v souhrnu:
funkce IF (LI), v tomto případě zkontroluje, zda další buňka v souhrnu není prázdná. Pokud je prázdný, vypíše prázdný textový řetězec „“, tj. buňku ponechte prázdnou. Pokud není prázdný, vyjměte ze sloupce Kontejner zdrojová tabulka Dodávky obsah buňky podle čísla řádku pomocí funkce INDEX (INDEX).
Snad jediným nepříliš zřejmým bodem je zde dvojsloví Kontejner ve vzorci. Taková zvláštní forma psaní:
Potřeby[[Kontejner]:[Kontejner]]
… je potřeba pouze k odkazování na sloupec Kontejner byl absolutní (jako odkaz se znaky $ u běžných „neinteligentních“ tabulek) a při kopírování našeho vzorce doprava nesklouzl do sousedních sloupců.
V budoucnu při změně dat ve zdrojové tabulce Dodávky, musíme pamatovat na aktualizaci našeho pomocného souhrnu čísly řádků tak, že na něj klepneme pravým tlačítkem a vybereme příkaz Aktualizovat a uložit (Obnovit).
Metoda 3. Vzorce
Tato metoda nevyžaduje vytvoření mezilehlé kontingenční tabulky a ruční aktualizaci, ale využívá „těžkou zbraň“ Excelu – funkci SUMMESLIMN (SUMIFS). Místo hledání čísel řádků v souhrnu je můžete vypočítat pomocí tohoto vzorce:
S určitou vnější objemností se ve skutečnosti jedná o standardní případ použití funkce selektivního součtu SUMMESLIMNA, které sečte čísla řádků pro dané město a měsíc. Opět, protože nemáme několik kontejnerů ve stejném městě ve stejném měsíci, naše funkce ve skutečnosti nevydá částku, ale samotné číslo řádku. A pak funkce již známá z předchozí metody INDEX Můžete také extrahovat kódy kontejnerů:
Samozřejmě v tomto případě již není třeba myslet na aktualizaci souhrnu, ale na velkých tabulkách funkce SUMMESLI může být znatelně pomalé. Pak budete muset vypnout automatickou aktualizaci vzorců, nebo použít první metodu – kontingenční tabulku.
Pokud se vzhled souhrnu pro vaši sestavu příliš nehodí, můžete z něj extrahovat čísla řádků do výsledné tabulky nikoli přímo, jako jsme to udělali my, ale pomocí funkce GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Jak na to, najdete zde.
- Jak vytvořit sestavu pomocí kontingenční tabulky
- Jak nastavit výpočty v kontingenčních tabulkách
- Selektivní počítání pomocí SUMIFS, COUNTIFS atd.