Obsah
Ak nie ste úplne začiatočník, určite ste si už všimli, že 99% všetkého v Exceli je navrhnutých pre prácu s vertikálnymi tabuľkami, kde parametre alebo atribúty (polia) prechádzajú cez stĺpce a nachádzajú sa informácie o objektoch alebo udalostiach v riadkoch . Kontingenčné tabuľky, medzisúčty, kopírovanie vzorcov dvojitým kliknutím – všetko je prispôsobené špeciálne pre tento formát údajov.
Neexistujú však žiadne pravidlá bez výnimiek a s pomerne pravidelnou frekvenciou dostávam otázku, čo robiť, ak v práci narazila na tabuľku s horizontálnou sémantickou orientáciou, alebo tabuľku, kde majú riadky a stĺpce rovnakú významovú váhu:
A ak Excel stále vie, ako triediť horizontálne (pomocou príkazu Údaje – Zoradiť – Možnosti – Zoradiť stĺpce), potom je situácia s filtrovaním horšia – v Exceli jednoducho nie sú zabudované nástroje na filtrovanie stĺpcov, nie riadkov. Ak teda stojíte pred takouto úlohou, budete musieť prísť s riešeniami rôzneho stupňa zložitosti.
Metóda 1. Nová funkcia FILTER
Ak používate novú verziu Excelu 2021 alebo predplatné Excel 365, môžete využiť novo predstavenú funkciu FILTER (FILTER), ktorý dokáže filtrovať zdrojové údaje nielen podľa riadkov, ale aj podľa stĺpcov. Táto funkcia na svoju činnosť vyžaduje pomocné horizontálne jednorozmerné pole-riadok, kde každá hodnota (TRUE alebo FALSE) určuje, či zobrazíme alebo naopak skryjeme ďalší stĺpec v tabuľke.
Pridajme nasledujúci riadok nad našu tabuľku a napíšme do nej stav každého stĺpca:
- Povedzme, že chceme vždy zobraziť prvý a posledný stĺpec (hlavičky a súčty), preto im v prvej a poslednej bunke poľa nastavíme hodnotu = TRUE.
- Pre zvyšné stĺpce bude obsahom zodpovedajúcich buniek vzorec, ktorý pomocou funkcií kontroluje podmienku, ktorú potrebujeme И (A) or OR (OR). Napríklad, že súčet je v rozsahu od 300 do 500.
Potom už zostáva len používať funkciu FILTER na výber stĺpcov, nad ktorými má naše pomocné pole hodnotu TRUE:
Podobne môžete filtrovať stĺpce podľa daného zoznamu. V tomto prípade funkcia pomôže COUNTIF (COUNTIF), ktorá kontroluje počet výskytov názvu nasledujúceho stĺpca z hlavičky tabuľky v povolenom zozname:
Metóda 2. Kontingenčná tabuľka namiesto bežnej
V súčasnosti má Excel zabudované horizontálne filtrovanie podľa stĺpcov iba v kontingenčných tabuľkách, takže ak sa nám podarí previesť našu pôvodnú tabuľku na kontingenčnú, môžeme využiť túto vstavanú funkcionalitu. Aby to bolo možné, naša zdrojová tabuľka musí spĺňať nasledujúce podmienky:
- mať „správny“ riadok hlavičky s jedným riadkom bez prázdnych a zlúčených buniek – inak nebude fungovať vytvorenie kontingenčnej tabuľky;
- neobsahujú duplikáty v označeniach riadkov a stĺpcov – v súhrne sa „zbalia“ do zoznamu iba jedinečných hodnôt;
- obsahujú iba čísla v rozsahu hodnôt (v priesečníku riadkov a stĺpcov), pretože kontingenčná tabuľka na ne určite použije nejakú agregovanú funkciu (súčet, priemer atď.) a nebude to fungovať s textom
Ak sú splnené všetky tieto podmienky, potom na zostavenie kontingenčnej tabuľky, ktorá vyzerá ako naša pôvodná tabuľka, bude potrebné ju (pôvodnú) rozšíriť z krížovej tabuľky na rovnú (normalizovanú). A najjednoduchší spôsob, ako to urobiť, je pomocou doplnku Power Query, výkonného nástroja na transformáciu údajov zabudovaného do Excelu od roku 2016.
Jedná sa o:
- Preveďme tabuľku na „inteligentný“ dynamický príkaz Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku).
- Načítavanie do Power Query pomocou príkazu Údaje – z tabuľky/rozsahu (údaje – z tabuľky/rozsahu).
- Filtrujeme riadok so súčtami (súhrn bude mať vlastné súčty).
- Kliknite pravým tlačidlom myši na hlavičku prvého stĺpca a vyberte Rozbaľte ďalšie stĺpce (Zrušte pivotovanie ďalších stĺpcov). Všetky nevybrané stĺpce sa skonvertujú na dva – meno zamestnanca a hodnotu jeho ukazovateľa.
- Filtrovanie stĺpca so súčtami, ktoré sa dostali do stĺpca atribút.
- Kontingenčnú tabuľku zostavíme podľa výslednej plochej (normalizovanej) tabuľky príkazom Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť a načítať — Zavrieť a načítať do...).
Teraz môžete použiť možnosť filtrovania stĺpcov dostupných v kontingenčných tabuľkách – obvyklé značky začiarknutia pred názvami a položkami Podpisové filtre (Filtre štítkov) or Filtruje podľa hodnoty (Filtre hodnôt):
A samozrejme, pri zmene údajov budete musieť aktualizovať náš dotaz a súhrn pomocou klávesovej skratky ctrl+ostatné+F5 alebo tím Údaje – Obnoviť všetko (Údaje – Obnoviť všetko).
Metóda 3. Makro vo VBA
Všetky predchádzajúce metódy, ako môžete ľahko vidieť, nie sú presne filtrujúce – stĺpce v pôvodnom zozname neskryjeme, ale vytvoríme novú tabuľku s danou sadou stĺpcov z pôvodného. Ak je potrebné filtrovať (skryť) stĺpce v zdrojových údajoch, potom je potrebný zásadne odlišný prístup, konkrétne makro.
Predpokladajme, že chceme priebežne filtrovať stĺpce, v ktorých meno manažéra v hlavičke tabuľky vyhovuje maske zadanej v žltej bunke A4, napríklad začína písmenom „A“ (to znamená, že dostaneme „Anna“ a „Arthur“ " ako výsledok).
Rovnako ako v prvej metóde najprv implementujeme pomocný riadok rozsahu, kde v každej bunke bude naše kritérium skontrolované pomocou vzorca a logické hodnoty TRUE alebo FALSE sa zobrazia pre viditeľné a skryté stĺpce:
Potom pridáme jednoduché makro. Kliknite pravým tlačidlom myši na kartu listu a vyberte príkaz zdroj (Zdrojový kód). Skopírujte a prilepte nasledujúci kód VBA do okna, ktoré sa otvorí:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Ďalšia bunka End If End Sub Jeho logika je nasledovná:
- Vo všeobecnosti ide o obsluhu udalosti Worksheet_Change, tj toto makro sa automaticky spustí pri každej zmene ktorejkoľvek bunky na aktuálnom hárku.
- Odkaz na zmenenú bunku bude vždy v premennej Cieľ.
- Najprv skontrolujeme, či používateľ zmenil presne bunku s kritériom (A4) – vykoná to operátor if.
- Potom začne cyklus Pre každý… iterujte cez sivé bunky (D2:O2) s hodnotami indikátora TRUE / FALSE pre každý stĺpec.
- Ak je hodnota nasledujúcej sivej bunky TRUE (true), stĺpec nie je skrytý, inak ho skryjeme (vlastnosť skrytý).
- Funkcie dynamického poľa z Office 365: FILTER, SORT a UNIC
- Kontingenčná tabuľka s viacriadkovou hlavičkou pomocou Power Query
- Čo sú makrá, ako ich vytvárať a používať