Skrytí/zobrazení nepotřebných řádků a sloupců

Formulace problému

Předpokládejme, že máme takový stůl, se kterým musíme „tančit“ každý den:

 

Komu se tabulka zdá malá – mentálně ji vynásobte dvacetkrát plochou, přidejte pár dalších bloků a dvě desítky velkých měst. 

Úkolem je dočasně odstranit z obrazovky řádky a sloupce, které jsou aktuálně pro práci nepotřebné, tzn. 

  • skrýt podrobnosti podle měsíce a ponechat pouze čtvrtletí
  • skrýt součty podle měsíců a čtvrtletí a ponechat pouze součet za půl roku
  • skrýt města, která jsou momentálně nepotřebná (pracuji v Moskvě – proč bych měl vidět Petrohrad?) atd.

V reálném životě existuje moře příkladů takových tabulek.

Metoda 1: Skrytí řádků a sloupců

Metoda, upřímně řečeno, je primitivní a nepříliš pohodlná, ale dají se o ní říci dvě slova. Jakékoli dříve vybrané řádky nebo sloupce na listu lze skrýt kliknutím pravým tlačítkem na záhlaví sloupce nebo řádku a výběrem příkazu z místní nabídky Skrýt (Skrýt):

 

Pro obrácené zobrazení vyberte sousední řádky / sloupce a kliknutím pravým tlačítkem vyberte z nabídky, resp. zobrazit (Odkrýt).

Problém je v tom, že se musíte vypořádat s každým sloupcem a řádkem samostatně, což je nepohodlné.

Metoda 2. Seskupování

Pokud vyberete více řádků nebo sloupců a poté vyberete z nabídky Data – Skupina a Struktura – Skupina (Data — Skupina a osnova — Skupina), pak budou uzavřeny v hranaté závorce (seskupeny). Skupiny lze navíc vnořovat jedna do druhé (je povoleno až 8 úrovní vnoření):

Pohodlnějším a rychlejším způsobem je seskupení předem vybraných řádků nebo sloupců pomocí klávesové zkratky. Alt+Shift+šipka dopravaa pro oddělování Alt+Shift+šipka doleva, V uvedeném pořadí.

Tento způsob skrytí nepotřebných dat je mnohem pohodlnější – můžete buď kliknout na tlačítko s „+", nebo "-“, nebo na tlačítkách s číselnou úrovní seskupení v levém horním rohu listu – pak se všechny skupiny požadované úrovně najednou sbalí nebo rozbalí.

Takže, pokud vaše tabulka obsahuje souhrnné řádky nebo sloupce s funkcí sčítání sousedních buněk, tedy šance (ne 100% pravda), že Excel vytvoří všechny potřebné skupiny v tabulce jedním pohybem – přes menu Data – Skupina a struktura – Vytvořit strukturu (Data — Skupina a obrys — Vytvořit obrys). Bohužel taková funkce funguje velmi nepředvídatelně a na složitých tabulkách někdy dělá úplné nesmysly. Ale zkusit to můžeš.

V Excelu 2007 a novějších jsou všechny tyto radosti na kartě Data (Datum) ve skupině   Struktura (Obrys):

Metoda 3. Skrytí označených řádků/sloupců pomocí makra

Tato metoda je možná nejuniverzálnější. Na začátek našeho listu přidáme prázdný řádek a prázdný sloupec a označíme libovolnou ikonou ty řádky a sloupce, které chceme skrýt:

Nyní otevřeme editor jazyka Visual Basic (ALT + F11), vložte nový prázdný modul do naší knihy (menu Vložit – Modul) a zkopírujte tam text dvou jednoduchých maker:

Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'Zakázat aktualizaci obrazovky pro urychlení Pro každou buňku In ActiveSheet.UsedRange.Rows(1).Cells 'Iterovat přes všechny buňky v prvním řádku If cell.Value = "x " Then cell .EntireColumn.Hidden = True 'if v buňce x - skrýt sloupec Next For Each cell In ActiveSheet.UsedRange.Columns(1).Cells 'procházet všemi buňkami prvního sloupce If cell.Value = "x" Then cell.EntireRow.Hidden = True 'pokud je v buňce x - skrýt řádek Next Application.ScreenUpdating = True End Sub Show() Columns.Hidden = False 'zrušit všechny skryté řádky a sloupce Rows.Hidden = False End Sub

Jak asi tušíte, makro Skrýt skrývá a makro show – Zobrazí zpět označené řádky a sloupce. V případě potřeby lze makrům přiřadit klávesové zkratky (Alt + F8 a tlačítko parametry), nebo vytvořte tlačítka přímo na listu a spouštějte je z karty Vývojář – Vložit – Tlačítko (Vývojář — Vložit — Tlačítko).

Metoda 4. Skrytí řádků/sloupců s danou barvou

Řekněme, že ve výše uvedeném příkladu naopak chceme skrýt součty, tedy fialové a černé řádky a žluté a zelené sloupce. Pak bude muset být naše předchozí makro mírně upraveno přidáním namísto kontroly přítomnosti „x“ kontroly shody barvy výplně s náhodně vybranými ukázkovými buňkami:

Sub HideByColor() Ztlumit buňku jako rozsah Application.ScreenUpdating = False Pro každou buňku v ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color Then cell.EntireColumn.Hidden = True If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True Další pro každou buňku In ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Rozsah ("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub

Nesmíme však zapomenout na jedno upozornění: toto makro funguje pouze v případě, že byly buňky zdrojové tabulky vyplněny barvou ručně, a nikoli pomocí podmíněného formátování (jde o omezení vlastnosti Interior.Color). Pokud jste tedy například pomocí podmíněného formátování automaticky zvýraznili všechny nabídky v tabulce, jejichž počet je menší než 10:

Skrytí/zobrazení nepotřebných řádků a sloupců

… a chcete je skrýt jedním pohybem, pak bude muset být předchozí makro „dokončeno“. Pokud máte Excel 2010-2013, můžete se dostat ven pomocí místo vlastnosti Interiér majetek DisplayFormat.Interiér, který vypíše barvu buňky bez ohledu na to, jak byla nastavena. Makro pro skrytí modrých čar by pak mohlo vypadat takto:

Sub HideByConditionalFormattingColor() Ztlumit buňku jako rozsah Application.ScreenUpdating = False pro každou buňku v ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Potom buňka .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub

Buňka G2 se bere jako vzorek pro srovnání barev. Bohužel nemovitost DisplayFormat se v Excelu objevil až od verze 2010, takže pokud máte Excel 2007 nebo starší, budete muset přijít na jiné způsoby.

  • Co je makro, kam vložit kód makra, jak je používat
  • Automatické seskupování do víceúrovňových seznamů

 

Napsat komentář