Innhold
Formulering av problemet
Anta at vi har et slikt bord som vi må "danse" med hver dag:
For hvem bordet virker lite – multipliser det mentalt tjue ganger etter område, legg til et par blokker til og to dusin store byer.
Oppgaven er å midlertidig fjerne fra skjermen rader og kolonner som for øyeblikket er unødvendige for arbeid, dvs.
- skjul detaljer etter måned, og la bare kvartaler stå igjen
- skjul totaler etter måneder og kvartaler, og la bare summen stå i et halvt år
- skjule byer som er unødvendige for øyeblikket (jeg jobber i Moskva – hvorfor skal jeg se St. Petersburg?), osv.
I det virkelige liv er det et hav av eksempler på slike tabeller.
Metode 1: Skjul rader og kolonner
Metoden, ærlig talt, er primitiv og ikke veldig praktisk, men to ord kan sies om den. Alle tidligere valgte rader eller kolonner på et ark kan skjules ved å høyreklikke på kolonne- eller radoverskriften og velge kommandoen fra kontekstmenyen Gjemme seg (Gjemme seg):
For omvendt visning, velg tilstøtende rader/kolonner og, ved å høyreklikke, velg fra menyen, henholdsvis, utstilling (Vis).
Problemet er at du må håndtere hver kolonne og rad individuelt, noe som er upraktisk.
Metode 2. Gruppering
Hvis du velger flere rader eller kolonner og deretter velger fra menyen Data – Gruppe og struktur – Gruppe (Data – gruppe og disposisjon – gruppe), så vil de være omsluttet av en firkantet parentes (gruppert). Dessuten kan grupper nestes inn i hverandre (opptil 8 nestenivåer er tillatt):
En mer praktisk og raskere måte er å bruke en hurtigtast for å gruppere forhåndsvalgte rader eller kolonner. Alt+Shift+høyrepil, og for oppheving av gruppering Alt+Shift+Pil venstreHhv.
Denne metoden for å skjule unødvendige data er mye mer praktisk - du kan enten klikke på knappen med "+"Eller"-“, eller på knappene med et numerisk grupperingsnivå i øvre venstre hjørne av arket – da vil alle gruppene på ønsket nivå bli slått sammen eller utvidet samtidig.
Også hvis tabellen inneholder sammendragsrader eller kolonner med funksjonen å summere naboceller, det vil si en sjanse (ikke 100 % sant) for at Excel han vil opprette alle nødvendige grupper i bordet med én bevegelse – gjennom menyen Data – Gruppe og struktur – Lag struktur (Data – gruppe og disposisjon – lag disposisjon). Dessverre fungerer en slik funksjon veldig uforutsigbart og gjør noen ganger fullstendig tull på komplekse tabeller. Men du kan prøve.
I Excel 2007 og nyere er alle disse gledene på fanen Data (Dato) i gruppe Structure (Oversikt):
Metode 3. Skjul markerte rader/kolonner med en makro
Denne metoden er kanskje den mest allsidige. La oss legge til en tom rad og en tom kolonne i begynnelsen av arket vårt og markere med et hvilket som helst ikon de radene og kolonnene vi ønsker å skjule:
La oss nå åpne Visual Basic Editor (ALT + F11), sett inn en ny tom modul i boken vår (meny Sett inn – modul) og kopier teksten til to enkle makroer der:
Sub Hide() Dim celle As Range Application.ScreenUpdating = False 'Deaktiver skjermoppdatering for å øke hastigheten for hver celle i ActiveSheet.UsedRange.Rows(1).Cells 'Iterer over alle cellene i den første raden If cell.Value = "x " Deretter celle .EntireColumn.Hidden = True 'hvis i celle x - skjul kolonne Neste For hver celle I ActiveSheet.UsedRange.Columns(1). Celler 'går gjennom alle cellene i den første kolonnen Hvis cell.Value = "x" Deretter cell.EntireRow.Hidden = True 'hvis i celle x - skjul raden Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = False 'avbryt alle skjulte rader og kolonner Rows.Hidden = False End Sub Show()
Som du kanskje gjetter, makroen Gjemme seg skjuler og makroen Show – Viser tilbake merkede rader og kolonner. Om ønskelig kan makroer tildeles hurtigtaster (Alt + F8 og knapp parametere), eller lag knapper direkte på arket for å starte dem fra fanen Utvikler – Sett inn – Knapp (Utvikler – Sett inn – knapp).
Metode 4. Skjuling av rader/kolonner med en gitt farge
La oss si at i eksemplet ovenfor ønsker vi tvert imot å skjule totalsummene, dvs. lilla og svarte rader og gule og grønne kolonner. Da må den forrige makroen vår endres litt ved å legge til, i stedet for å sjekke for tilstedeværelsen av "x", en sjekk for å matche fyllfargen med tilfeldig valgte prøveceller:
Sub HideByColor() Dim celle som område Application.ScreenUpdating = False for hver celle i 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 Neste For hver celle I ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Range ("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 Vi må imidlertid ikke glemme en advarsel: denne makroen fungerer bare hvis cellene i kildetabellen ble fylt med farger manuelt, og ikke bruker betinget formatering (dette er en begrensning av Interior.Color-egenskapen). Så, for eksempel, hvis du automatisk uthevet alle avtaler i tabellen din der tallet er mindre enn 10 ved bruk av betinget formatering:
... og du vil skjule dem i én bevegelse, så må den forrige makroen være "ferdig". Hvis du har Excel 2010-2013, kan du komme deg ut ved å bruke stedet for eiendommen Interiør eiendom DisplayFormat.Interiør, som gir ut fargen på cellen, uavhengig av hvordan den ble satt. Makroen for å skjule de blå linjene kan da se slik ut:
Sub HideByConditionalFormattingColor() Dim celle As Range Application.ScreenUpdating = False for hver celle i ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Deretter celle .EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub Celle G2 er tatt som en prøve for fargesammenligning. Dessverre eiendommen DisplayFormat dukket opp i Excel bare fra og med 2010-versjonen, så hvis du har Excel 2007 eller eldre, må du finne andre måter.
- Hva er en makro, hvor du skal sette inn makrokode, hvordan du bruker dem
- Automatisk gruppering i flernivålister