Inhoud
Formulering van het probleem
Stel we hebben zo'n tafel waar we elke dag mee moeten “dansen”:
To whom the table seems small – mentally multiply it twenty times by area, adding a couple more blocks and two dozen large cities.
De taak is om tijdelijk rijen en kolommen van het scherm te verwijderen die momenteel niet nodig zijn voor het werk, dwz
- details verbergen per maand, zodat er alleen kwartalen overblijven
- verberg totalen in maanden en kwartalen, zodat alleen het totaal een half jaar overblijft
- verberg steden die op dit moment niet nodig zijn (ik werk in Moskou - waarom zou ik St. Petersburg zien?), enz.
In het echte leven zijn er een zee van voorbeelden van dergelijke tabellen.
Methode 1: Rijen en kolommen verbergen
De methode is eerlijk gezegd primitief en niet erg handig, maar er kunnen twee woorden over worden gezegd. Alle eerder geselecteerde rijen of kolommen op een blad kunnen worden verborgen door met de rechtermuisknop op de kolom- of rijkop te klikken en de opdracht in het contextmenu te selecteren Verbergen (Verbergen):
Voor omgekeerde weergave selecteert u aangrenzende rijen/kolommen en, door met de rechtermuisknop te klikken, selecteert u respectievelijk in het menu, tonen (Maak zichtbaar).
Het probleem is dat je elke kolom en rij afzonderlijk moet behandelen, wat onhandig is.
Methode 2. Groepering
Als u meerdere rijen of kolommen selecteert en vervolgens selecteert in het menu Gegevens – Groep en Structuur – Groep (Gegevens — Groep en overzicht — Groep), dan worden ze tussen vierkante haken geplaatst (gegroepeerd). Bovendien kunnen groepen in elkaar worden genest (er zijn maximaal 8 nestingniveaus toegestaan):
Een handiger en snellere manier is om een sneltoets te gebruiken om vooraf geselecteerde rijen of kolommen te groeperen. Alt+Shift+Pijl naar rechts, en voor het opheffen van de groep Alt+Shift+Pijl naar links, Respectievelijk.
Deze methode om onnodige gegevens te verbergen is veel handiger – u kunt ofwel klikken op de knop met de “+"Of"-", of op de knoppen met een numeriek groeperingsniveau in de linkerbovenhoek van het blad - dan worden alle groepen van het gewenste niveau in één keer samengevouwen of uitgevouwen.
Dus, als uw tabel samenvattingsrijen of kolommen bevat met de functie om aangrenzende cellen op te tellen, dat wil zeggen, een kans (niet 100% waar) dat Excel hij zal alle benodigde groepen maken met één beweging in de tafel – door het menu Gegevens – Groeperen en Structuur – Structuur creëren (Gegevens — Groep en overzicht — Overzicht maken). Helaas werkt zo'n functie erg onvoorspelbaar en doet het soms complete onzin op complexe tabellen. Maar je kan het proberen.
In Excel 2007 en nieuwer staan al deze geneugten op het tabblad Data (Datum) in groep Structuur (overzicht):
Methode 3. Gemarkeerde rijen/kolommen verbergen met een macro
Deze methode is misschien wel de meest veelzijdige. Laten we een lege rij en een lege kolom aan het begin van ons blad toevoegen en met een willekeurig pictogram die rijen en kolommen markeren die we willen verbergen:
Laten we nu de Visual Basic Editor openen (ALT + F11), plaats een nieuwe lege module in ons boek (menu Invoegen – Module) en kopieer daar de tekst van twee eenvoudige macro's:
Sub Hide() Dim cel As Range Application.ScreenUpdating = False 'Schermupdates uitschakelen om te versnellen voor elke cel in ActiveSheet.UsedRange.Rows(1).Cells 'Itereer alle cellen in de eerste rij If cell.Value = "x " Dan cel .EntireColumn.Hidden = True 'indien in cel x - verberg kolom Volgende Voor elke cel In ActiveSheet.UsedRange.Columns(1).Cells 'ga door alle cellen van de eerste kolom If cell.Value = "x" Dan cell.EntireRow.Hidden = True 'indien in cel x - verberg de rij Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'annuleer alle verborgen rijen en kolommen Rows.Hidden = False End Sub
Zoals je zou kunnen raden, de macro Verbergen verbergt en de macro Zichtbaar - Geeft rijen en kolommen met de achterkant weer. Indien gewenst kunnen aan macro's sneltoetsen worden toegewezen (Alt + F8 en knop parameters), of maak knoppen rechtstreeks op het blad om ze vanaf het tabblad te starten Ontwikkelaar – Invoegen – Knop (Ontwikkelaar — Invoegen — Knop).
Methode 4. Rijen/kolommen met een bepaalde kleur verbergen
Laten we zeggen dat we in het bovenstaande voorbeeld juist de totalen willen verbergen, dwz paarse en zwarte rijen en gele en groene kolommen. Dan moet onze vorige macro enigszins worden aangepast door, in plaats van te controleren op de aanwezigheid van "x", een controle toe te voegen voor het matchen van de vulkleur met willekeurig geselecteerde voorbeeldcellen:
Sub HideByColor() Dim cel As Range Application.ScreenUpdating = False For Each cell In ActiveSheet.UsedRange.Rows(2).Cells If cell.Interior.Color = Range("F2").Interior.Color Dan cel.EntireColumn.Hidden = True If cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = True Volgende voor elke cel in ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Bereik ("D6").Interior.Color Then cell.EntireRow.Hidden = True If cell.Interior.Color = Range("B11").Interior.Color Then cell.EntireRow.Hidden = True Volgende Application.ScreenUpdating = True End Sub We mogen echter één voorbehoud niet vergeten: deze macro werkt alleen als de cellen van de brontabel handmatig met kleur zijn gevuld en geen voorwaardelijke opmaak gebruiken (dit is een beperking van de eigenschap Interior.Color). Dus als u bijvoorbeeld automatisch alle deals in uw tabel markeert waar het getal kleiner is dan 10 met behulp van voorwaardelijke opmaak:
… en je wilt ze in één beweging verbergen, dan moet de vorige macro “af” zijn. Als u Excel 2010-2013 hebt, kunt u naar buiten gaan in plaats van de eigenschap Interieur eigendom DisplayFormaat.Interieur, die de kleur van de cel uitvoert, ongeacht hoe deze is ingesteld. De macro om de blauwe lijnen te verbergen kan er dan als volgt uitzien:
Sub HideByConditionalFormattingColor() Dim cel As Range Application.ScreenUpdating = False For Each cell In ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Dan cel .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Cel G2 wordt genomen als een monster voor kleurvergelijking. Helaas het pand Weergaveformaat verscheen pas in Excel vanaf de 2010-versie, dus als je Excel 2007 of ouder hebt, zul je andere manieren moeten bedenken.
- Wat is een macro, waar moet je macrocode invoegen, hoe gebruik je ze?
- Automatisch groeperen in lijsten met meerdere niveaus