Pokud nejste úplně začínající uživatel, tak jste si jistě již všimli, že 99 % všeho v Excelu je navrženo pro práci s vertikálními tabulkami, kde parametry nebo atributy (pole) procházejí sloupci a jsou umístěny informace o objektech nebo událostech v řádcích. Kontingenční tabulky, mezisoučty, kopírování vzorců dvojitým kliknutím – vše je přizpůsobeno právě tomuto datovému formátu.
Neexistují však žádná pravidla bez výjimek a s celkem pravidelnou frekvencí dostávám otázku, co dělat, když v práci narazila na tabulku s horizontální sémantickou orientací, nebo tabulku, kde řádky a sloupce mají stejnou významovou váhu:
A pokud Excel stále ví, jak třídit vodorovně (pomocí příkazu Data – Seřadit – Možnosti – Seřadit sloupce), pak je situace s filtrováním horší – v Excelu prostě nejsou vestavěné nástroje na filtrování sloupců, ne řádků. Pokud tedy stojíte před takovým úkolem, budete muset přijít s náhradními řešeními různého stupně složitosti.
Metoda 1. Nová funkce FILTER
Pokud používáte novou verzi Excelu 2021 nebo předplatné Excel 365, můžete využít nově zavedenou funkci FILTER (FILTR), který umí filtrovat zdrojová data nejen podle řádků, ale také podle sloupců. Ke své funkci tato funkce vyžaduje pomocné horizontální jednorozměrné pole-řádek, kde každá hodnota (TRUE nebo FALSE) určuje, zda zobrazíme nebo naopak skryjeme další sloupec v tabulce.
Nad naši tabulku přidáme následující řádek a zapíšeme do něj stav každého sloupce:
- Řekněme, že chceme vždy zobrazit první a poslední sloupec (záhlaví a součty), takže pro ně v první a poslední buňce pole nastavíme hodnotu = TRUE.
- Pro zbývající sloupce bude obsahem odpovídajících buněk vzorec, který pomocí funkcí kontroluje podmínku, kterou potřebujeme И (A) or OR (OR). Například, že celkový počet je v rozmezí od 300 do 500.
Poté zbývá pouze funkci používat FILTER pro výběr sloupců, nad kterými má naše pomocné pole hodnotu TRUE:
Podobně můžete filtrovat sloupce podle daného seznamu. V tomto případě funkce pomůže COUNTIF (COUNTIF), který zkontroluje počet výskytů názvu dalšího sloupce ze záhlaví tabulky v povoleném seznamu:
Metoda 2. Kontingenční tabulka místo obvyklé
Aktuálně má Excel zabudované horizontální filtrování podle sloupců pouze v kontingenčních tabulkách, takže pokud se nám podaří převést naši původní tabulku do kontingenční tabulky, můžeme tuto vestavěnou funkcionalitu využít. K tomu musí naše zdrojová tabulka splňovat následující podmínky:
- mít „správný“ jednořádkový řádek záhlaví bez prázdných a sloučených buněk – jinak nebude fungovat sestavení kontingenční tabulky;
- neobsahují duplikáty v popiscích řádků a sloupců – v souhrnu se „sbalí“ do seznamu pouze jedinečných hodnot;
- obsahovat pouze čísla v rozsahu hodnot (v průsečíku řádků a sloupců), protože kontingenční tabulka na ně určitě použije nějakou agregační funkci (součet, průměr atd.) a to nebude fungovat s textem
Pokud jsou všechny tyto podmínky splněny, pak aby bylo možné sestavit kontingenční tabulku, která vypadá jako naše původní tabulka, bude potřeba ji (tu původní) rozšířit z kontingenční tabulky na plochou (normalizovanou). Nejjednodušší způsob, jak toho dosáhnout, je pomocí doplňku Power Query, což je výkonný nástroj pro transformaci dat zabudovaný do Excelu od roku 2016.
Tyto jsou:
- Převedeme tabulku na „chytrý“ dynamický příkaz Domů – Formát jako tabulka (Domů — Formátovat jako tabulku).
- Načtení do Power Query pomocí příkazu Data – z tabulky/rozsahu (data – z tabulky/rozsahu).
- Řádek se součty vyfiltrujeme (souhrn bude mít vlastní součty).
- Klikněte pravým tlačítkem na záhlaví prvního sloupce a vyberte Rozbalte další sloupce (Odklopit další sloupce). Všechny nevybrané sloupce se převedou na dva – jméno zaměstnance a hodnotu jeho ukazatele.
- Filtrování sloupce pomocí součtů, které se do sloupce dostaly Atribut.
- Příkazem sestavíme kontingenční tabulku podle výsledné ploché (normalizované) tabulky Domů — Zavřít a načíst — Zavřít a načíst v… (Domů — Zavřít a načíst — Zavřít a načíst do…).
Nyní můžete využít možnost filtrovat sloupce dostupné v kontingenčních tabulkách – obvyklé zaškrtnutí před názvy a položkami Podpisové filtry (Filtry štítků) or Filtruje podle hodnoty (Filtry hodnot):
A samozřejmě při změně údajů budete muset aktualizovat náš dotaz a souhrn pomocí klávesové zkratky Ctrl+Další+F5 nebo tým Data – Obnovit vše (Data – Obnovit vše).
Metoda 3. Makro ve VBA
Všechny předchozí metody, jak snadno uvidíte, nejsou přesně filtrovací – sloupce v původním seznamu neskryjeme, ale z původního vytvoříme novou tabulku s danou sadou sloupců. Pokud je vyžadováno filtrování (skrytí) sloupců ve zdrojových datech, je zapotřebí zásadně odlišný přístup, konkrétně makro.
Předpokládejme, že chceme za běhu filtrovat sloupce, kde jméno manažera v záhlaví tabulky odpovídá masce uvedené ve žluté buňce A4, například začíná písmenem „A“ (tj. získat „Anna“ a „Arthur " jako výsledek).
Stejně jako v první metodě nejprve implementujeme pomocný řádek rozsahu, kde v každé buňce bude naše kritérium zkontrolováno vzorcem a pro viditelné a skryté sloupce se zobrazí logické hodnoty TRUE nebo FALSE:
Pak přidáme jednoduché makro. Klepněte pravým tlačítkem myši na kartu listu a vyberte příkaz Zdroj (Zdrojový kód). Zkopírujte a vložte následující kód VBA do okna, které se otevře:
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 Další buňka End If End Sub Jeho logika je následující:
- Obecně se jedná o obsluhu události Worksheet_Change, tj. toto makro se automaticky spustí při jakékoli změně libovolné buňky na aktuálním listu.
- Odkaz na změněnou buňku bude vždy v proměnné Cíl.
- Nejprve zkontrolujeme, že uživatel změnil přesně buňku s kritériem (A4) – to provede operátor if.
- Poté začne cyklus Pro každého… iterovat přes šedé buňky (D2:O2) s hodnotami indikátoru TRUE / FALSE pro každý sloupec.
- Pokud je hodnota další šedé buňky TRUE (true), pak sloupec není skrytý, jinak jej skryjeme (vlastnost Skrytý).
- Funkce dynamického pole z Office 365: FILTER, SORT a UNIC
- Kontingenční tabulka s víceřádkovým záhlavím pomocí Power Query
- Co jsou makra, jak je vytvářet a používat