Kontingenčné stoly sú dobré pre každého – rýchlo počítajú, sú flexibilne konfigurovateľné a v prípade potreby sa do nich dá elegantne navinúť dizajn. Existuje však aj niekoľko problémov, najmä nemožnosť vytvoriť súhrn, kde by oblasť hodnôt nemala obsahovať čísla, ale text.
Skúsme toto obmedzenie obísť a vymyslime si v podobnej situácii „pár barlí“.
Predpokladajme, že naša spoločnosť prepravuje svoje výrobky v kontajneroch do niekoľkých miest v našej krajine a Kazachstane. Kontajnery sa odosielajú maximálne raz za mesiac. Každý kontajner má alfanumerické číslo. Ako počiatočné údaje je štandardná tabuľka so zoznamom dodávok, z ktorej je potrebné urobiť nejaký súhrn, aby ste jasne videli počty kontajnerov odoslaných do každého mesta a každý mesiac:
Pre pohodlie urobme tabuľku s počiatočnými údajmi „inteligentnou“ vopred pomocou príkazu Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku) a daj jej meno dodávky pútko staviteľ (Dizajn). V budúcnosti to zjednoduší život, pretože. bude možné použiť názov tabuľky a jej stĺpcov priamo vo vzorcoch.
Metóda 1. Najjednoduchší – použite Power Query
Power Query je super výkonný nástroj na načítanie a transformáciu údajov v Exceli. Tento doplnok je štandardne zabudovaný do Excelu od roku 2016. Ak máte Excel 2010 alebo 2013, môžete si ho stiahnuť a nainštalovať samostatne (úplne zadarmo).
Celý proces som pre prehľadnosť analyzoval krok za krokom v nasledujúcom videu:
Ak nie je možné použiť Power Query, môžete ísť inými spôsobmi – prostredníctvom kontingenčnej tabuľky alebo vzorcov.
Metóda 2. Pomocné zhrnutie
Pridajme ešte jeden stĺpec do našej pôvodnej tabuľky, kde pomocou jednoduchého vzorca vypočítame počet každého riadku v tabuľke:
Je zrejmé, že -1 je potrebný, pretože v tabuľke máme jednoriadkovú hlavičku. Ak vaša tabuľka nie je na začiatku hárka, môžete použiť o niečo zložitejší, ale univerzálny vzorec, ktorý vypočítava rozdiel v číslach aktuálneho riadku a hlavičky tabuľky:
Teraz štandardným spôsobom zostavíme kontingenčnú tabuľku požadovaného typu na základe našich údajov, ale v poli hodnoty pole vypustíme Číslo riadku namiesto toho, čo chceme kontajner:
Keďže v tom istom meste nemáme niekoľko kontajnerov v tom istom mesiaci, v našom súhrne v skutočnosti nebude uvedené množstvo, ale čísla riadkov kontajnerov, ktoré potrebujeme.
Okrem toho môžete na karte vypnúť veľké a medzisúčty Konštruktor – Všeobecné súčty и Medzisúčty (Návrh – celkové súčty, medzisúčty) a na tom istom mieste tlačidlom prepnite súhrn na pohodlnejšie rozloženie tabuľky Nahlásiť maketu (Rozloženie prehľadu).
Tým pádom sme už na polceste k výsledku: máme tabuľku, kde je na priesečníku mesta a mesiaca v zdrojovej tabuľke číslo riadku, kde leží kód kontajnera, ktorý potrebujeme.
Teraz skopírujeme súhrn (na ten istý alebo iný hárok) a prilepíme ho ako hodnoty a potom do oblasti hodnôt zadáme náš vzorec, ktorý extrahuje kód kontajnera podľa čísla riadku nájdeného v súhrne:
Funkcie IF (AK), v tomto prípade skontroluje, či ďalšia bunka v súhrne nie je prázdna. Ak je prázdny, vypíšte prázdny textový reťazec „“, tj nechajte bunku prázdnu. Ak nie je prázdny, vyberte ho zo stĺpca Kontajner zdrojová tabuľka dodávky obsah bunky podľa čísla riadku pomocou funkcie INDEX (INDEX).
Snáď jediným nie príliš zrejmým bodom je dvojité slovo Kontajner vo vzorci. Taká zvláštna forma písania:
spotrebný materiál[[Kontajner]:[Kontajner]]
... je potrebné iba na odkazovanie na stĺpec Kontajner bol absolútny (ako odkaz so znakmi $ pre bežné „neinteligentné“ tabuľky) a pri kopírovaní nášho vzorca doprava neskĺzol do susedných stĺpcov.
V budúcnosti pri zmene údajov v zdrojovej tabuľke dodávky, musíme pamätať na to, že musíme aktualizovať naše pomocné zhrnutie o čísla riadkov tak, že naň kliknete pravým tlačidlom myši a vyberiete príkaz Aktualizovať a uložiť (Obnoviť).
Metóda 3. Vzorce
Táto metóda nevyžaduje vytvorenie medziľahlej kontingenčnej tabuľky a manuálnu aktualizáciu, ale využíva „ťažkú zbraň“ Excelu – funkciu SUMMESLIMN (SUMIFS). Namiesto vyhľadávania čísel riadkov v súhrne ich môžete vypočítať pomocou tohto vzorca:
S určitou vonkajšou objemnosťou je to v skutočnosti štandardný prípad použitia funkcie selektívneho súčtu SUMMESLIMNA, ktorý sčítava čísla riadkov pre dané mesto a mesiac. Opätovne, keďže v tom istom mesiaci nemáme niekoľko kontajnerov v tom istom meste, naša funkcia v skutočnosti nevydá množstvo, ale samotné číslo linky. A potom funkcia už známa z predchádzajúcej metódy INDEX Môžete tiež extrahovať kódy kontajnerov:
Samozrejme, v tomto prípade už netreba myslieť na aktualizáciu súhrnu, ale na veľkých tabuľkách na funkciu SUMMESLI môže byť výrazne pomalé. Potom budete musieť vypnúť automatickú aktualizáciu vzorcov, alebo použiť prvý spôsob – kontingenčnú tabuľku.
Ak vzhľad súhrnu nie je príliš vhodný pre váš prehľad, môžete z neho extrahovať čísla riadkov do výslednej tabuľky nie priamo, ako sme to urobili my, ale pomocou funkcie GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Ako na to, nájdete tu.
- Ako vytvoriť prehľad pomocou kontingenčnej tabuľky
- Ako nastaviť výpočty v kontingenčných tabuľkách
- Selektívne počítanie pomocou SUMIFS, COUNTIFS atď.