Obsah
Formulácia problému
Predpokladajme, že máme taký stôl, s ktorým musíme „tancovať“ každý deň:
Komu sa tabuľka zdá malá – mentálne ju znásobte dvadsaťkrát podľa oblasti, pridajte pár blokov a dva tucty veľkých miest.
Úlohou je dočasne odstrániť z obrazovky riadky a stĺpce, ktoré sú momentálne pre prácu nepotrebné, tj.
- skryť podrobnosti podľa mesiacov a ponechať iba štvrtiny
- skryť súčty podľa mesiacov a štvrťrokov a ponechať len súčet za pol roka
- skryť mestá, ktoré sú momentálne nepotrebné (pracujem v Moskve – prečo by som mal vidieť Petrohrad?) atď.
V reálnom živote existuje množstvo príkladov takýchto tabuliek.
Metóda 1: Skrytie riadkov a stĺpcov
Metóda, úprimne povedané, je primitívna a nie príliš pohodlná, ale dajú sa o nej povedať dve slová. Všetky predtým vybraté riadky alebo stĺpce na hárku možno skryť kliknutím pravým tlačidlom myši na hlavičku stĺpca alebo riadka a výberom príkazu z kontextovej ponuky skryť (skryť):
Pre spätné zobrazenie vyberte susediace riadky / stĺpce a kliknutím pravým tlačidlom myši vyberte z ponuky, resp. zobraziť (Odkryť).
Problém je, že s každým stĺpcom a riadkom sa musíte zaoberať individuálne, čo je nepohodlné.
Metóda 2. Zoskupovanie
Ak vyberiete viacero riadkov alebo stĺpcov a potom vyberiete z ponuky Údaje – Skupina a Štruktúra – Skupina (Údaje — Skupina a prehľad — Skupina), potom budú uzavreté v hranatej zátvorke (zoskupené). Okrem toho je možné skupiny vnoriť jedna do druhej (povolených je až 8 úrovní vnorenia):
Pohodlnejším a rýchlejším spôsobom je použiť klávesovú skratku na zoskupenie vopred vybraných riadkov alebo stĺpcov. Alt+Shift+šípka dopravaa na zrušenie skupiny Alt+Shift+šípka doľava, Resp.
Tento spôsob skrytia nepotrebných údajov je oveľa pohodlnejší – môžete kliknúť na tlačidlo s „+"Alebo"-“, alebo na tlačidlá s číselnou úrovňou zoskupenia v ľavom hornom rohu hárku – vtedy sa všetky skupiny požadovanej úrovne naraz zbalia alebo rozbalia.
Takže, ak vaša tabuľka obsahuje súhrnné riadky alebo stĺpce s funkciou sčítania susedných buniek, teda šanca (nie je to 100% pravda), že Excel vytvorí všetky potrebné skupiny v tabuľke jedným pohybom – cez menu Údaje – Skupina a štruktúra – Vytvorenie štruktúry (Údaje — Skupina a obrys — Vytvoriť obrys). Bohužiaľ, takáto funkcia funguje veľmi nepredvídateľne a na zložitých tabuľkách niekedy robí úplné nezmysly. Ale skúsiť to môžeš.
V Exceli 2007 a novšom sú všetky tieto radosti na karte dátum (Dátum) v skupine štruktúra (obrys):
Metóda 3. Skrytie označených riadkov/stĺpcov pomocou makra
Táto metóda je možno najuniverzálnejšia. Pridajme na začiatok hárka prázdny riadok a prázdny stĺpec a označme ľubovoľnou ikonou tie riadky a stĺpce, ktoré chceme skryť:
Teraz otvorme editor jazyka Visual Basic (ALT + F11), vložte nový prázdny modul do našej knihy (menu Vložiť – Modul) a skopírujte tam text dvoch jednoduchých makier:
Sub Hide() Dim cell As Range Application.ScreenUpdating = False 'Zakázať aktualizáciu obrazovky na zrýchlenie Pre každú bunku In ActiveSheet.UsedRange.Rows(1).Cells 'Iterovať cez všetky bunky v prvom riadku If cell.Value = "x " Then cell .EntireColumn.Hidden = True 'ak je v bunke x - skryť stĺpec Next For Každej bunke In ActiveSheet.UsedRange.Columns(1).Cells 'prechádzať cez všetky bunky prvého stĺpca If cell.Value = "x" Then cell.EntireRow.Hidden = True 'ak je v bunke x - skryť riadok Next Application.ScreenUpdating = True End Sub Show() Columns.Hidden = False 'zrušiť všetky skryté riadky a stĺpce Rows.Hidden = False End Sub
Ako asi tušíte, makro skryť skrýva a makro Šou – Zobrazuje spätne označené riadky a stĺpce. V prípade potreby je možné makrám priradiť klávesové skratky (Alt + F8 a tlačidlo parametre), alebo vytvorte tlačidlá priamo na hárku, aby ste ich spúšťali z karty Vývojár – Vložiť – Tlačidlo (Vývojár — Vložiť — Tlačidlo).
Metóda 4. Skrytie riadkov/stĺpcov s danou farbou
Povedzme, že vo vyššie uvedenom príklade naopak chceme skryť súčty, teda fialové a čierne riadky a žlté a zelené stĺpce. Potom bude potrebné naše predchádzajúce makro mierne upraviť pridaním namiesto kontroly prítomnosti „x“ kontroly zhody farby výplne s náhodne vybranými vzorovými bunkami:
Sub HideByColor() Stmaviť bunku ako rozsah Application.ScreenUpdating = False Pre každú bunku 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 Next For Each cell 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 Nesmieme však zabúdať na jedno upozornenie: toto makro funguje iba v prípade, že bunky zdrojovej tabuľky boli vyplnené farbou manuálne, a nie pomocou podmieneného formátovania (ide o obmedzenie vlastnosti Interior.Color). Ak ste teda napríklad pomocou podmieneného formátovania automaticky zvýraznili všetky ponuky v tabuľke, ktorých číslo je menšie ako 10:
... a chcete ich skryť jedným pohybom, potom bude potrebné „dokončiť“ predchádzajúce makro. Ak máte Excel 2010-2013, môžete sa dostať von pomocou namiesto vlastnosti Interiér majetok DisplayFormat.Interiér, ktorá zobrazuje farbu bunky bez ohľadu na to, ako bola nastavená. Makro na skrytie modrých čiar môže potom vyzerať takto:
Sub HideByConditionalFormattingColor() Stmaviť bunku ako rozsah Application.ScreenUpdating = False pre každú bunku v ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Potom bunka .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Bunka G2 sa berie ako vzorka na porovnanie farieb. Bohužiaľ nehnuteľnosť DisplayFormat sa v Exceli objavil až od verzie 2010, takže ak máte Excel 2007 alebo starší, budete musieť prísť na iné spôsoby.
- Čo je makro, kam vložiť kód makra, ako ho použiť
- Automatické zoskupovanie do viacúrovňových zoznamov