Horisontal kolonnefiltrering i Excel

Hvis du ikke er helt nybegynner, må du allerede ha lagt merke til at 99% av alt i Excel er designet for å fungere med vertikale tabeller, hvor parametere eller attributter (felt) går gjennom kolonnene, og informasjon om objekter eller hendelser er plassert i linjene. Pivottabeller, delsummer, kopiering av formler med et dobbeltklikk – alt er skreddersydd spesielt for dette dataformatet.

Det er imidlertid ingen regler uten unntak, og med en ganske regelmessig frekvens blir jeg spurt om hva jeg skal gjøre hvis en tabell med horisontal semantisk orientering, eller en tabell der rader og kolonner har samme betydning i betydning, kom over i arbeidet:

Og hvis Excel fortsatt vet hvordan man sorterer horisontalt (med kommandoen Data – Sorter – Alternativer – Sorter kolonner), så er situasjonen med filtrering verre - det er rett og slett ingen innebygde verktøy for å filtrere kolonner, ikke rader i Excel. Så hvis du står overfor en slik oppgave, må du finne løsninger med ulik grad av kompleksitet.

Metode 1. Ny FILTER-funksjon

Hvis du bruker den nye versjonen av Excel 2021 eller et Excel 365-abonnement, kan du dra nytte av den nylig introduserte funksjonen FILTER (FILTER), som kan filtrere kildedataene ikke bare etter rader, men også etter kolonner. For å fungere krever denne funksjonen en ekstra horisontal endimensjonal array-rad, der hver verdi (TRUE eller FALSE) bestemmer om vi viser eller omvendt skjuler neste kolonne i tabellen.

La oss legge til følgende linje over tabellen og skrive statusen til hver kolonne i den:

• La oss si at vi alltid ønsker å vise den første og siste kolonnen (overskrifter og totaler), så for dem i den første og siste cellen i matrisen setter vi verdien = TRUE.
• For de resterende kolonnene vil innholdet i de tilsvarende cellene være en formel som kontrollerer tilstanden vi trenger ved hjelp av funksjoner И (OG) or OR (ELLER). For eksempel at totalen er i området fra 300 til 500.

Etter det gjenstår det bare å bruke funksjonen FILTER for å velge kolonner over hvilke hjelpearrayen vår har en TRUE-verdi:

På samme måte kan du filtrere kolonner etter en gitt liste. I dette tilfellet vil funksjonen hjelpe ANTALL.HVIS (COUNTIF), som sjekker antall forekomster av neste kolonnenavn fra tabelloverskriften i den tillatte listen:

Metode 2. Pivottabell i stedet for den vanlige

Foreløpig har Excel innebygd horisontal filtrering etter kolonner kun i pivottabeller, så hvis vi klarer å konvertere vår opprinnelige tabell til en pivottabell kan vi bruke denne innebygde funksjonaliteten. For å gjøre dette må kildetabellen vår tilfredsstille følgende betingelser:

• ha en "riktig" én-linjes overskriftslinje uten tomme og sammenslåtte celler – ellers vil det ikke fungere å bygge en pivottabell;
• ikke inneholder duplikater i etikettene til rader og kolonner - de vil "kollapse" i sammendraget til en liste med bare unike verdier;
• inneholder bare tall i verdiområdet (ved skjæringspunktet mellom rader og kolonner), fordi pivottabellen definitivt vil bruke en slags aggregeringsfunksjon på dem (sum, gjennomsnitt, etc.), og dette vil ikke fungere med teksten

Hvis alle disse betingelsene er oppfylt, må den (den opprinnelige) utvides fra krysstabellen til en flat (normalisert) for å kunne bygge en pivottabell som ser ut som vår originale tabell. Og den enkleste måten å gjøre dette på er med Power Query-tillegget, et kraftig datatransformasjonsverktøy innebygd i Excel siden 2016. 

Disse er:

1. La oss konvertere tabellen til en "smart" dynamisk kommando Hjem – Formater som en tabell (Hjem — Formater som tabell).
2. Laster inn i Power Query med kommandoen Data – fra tabell / område (Data – fra tabell / område).
3. Vi filtrerer linjen med totalsummene (sammendraget vil ha sine egne totaler).
4. Høyreklikk på den første kolonneoverskriften og velg Unngå å skjule andre kolonner (Opphev andre kolonner). Alle ikke-valgte kolonner konverteres til to - navnet på den ansatte og verdien av indikatoren hans.
5. Filtrering av kolonnen med totalsummene som gikk inn i kolonnen Egenskap.
6. Vi bygger en pivottabell i henhold til den resulterende flate (normaliserte) tabellen med kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk og last — Lukk og last til...).

Nå kan du bruke muligheten til å filtrere kolonner som er tilgjengelige i pivottabeller – de vanlige hakene foran navnene og elementene Signaturfiltre (Etikettfiltre) or Filtrerer etter verdi (Verdifiltre):

Og selvfølgelig, når du endrer dataene, må du oppdatere søket vårt og sammendraget med en hurtigtast Ctrl+andre+F5 eller team Data – Oppdater alle (Data – Oppdater alle).

Metode 3. Makro i VBA

Alle de tidligere metodene, som du lett kan se, filtrerer ikke akkurat – vi skjuler ikke kolonnene i den opprinnelige listen, men danner en ny tabell med et gitt sett med kolonner fra den opprinnelige. Hvis det er nødvendig å filtrere (skjule) kolonnene i kildedataene, er det nødvendig med en fundamentalt annen tilnærming, nemlig en makro.

Anta at vi ønsker å filtrere kolonner i farten der navnet på lederen i tabelloverskriften tilfredsstiller masken spesifisert i den gule cellen A4, for eksempel starter med bokstaven "A" (det vil si få "Anna" og "Arthur" " som et resultat). 

Som i den første metoden implementerer vi først en hjelpeområderad, der i hver celle kriteriet vårt vil bli sjekket av en formel og de logiske verdiene TRUE eller FALSE vises for henholdsvis synlige og skjulte kolonner:

La oss så legge til en enkel makro. Høyreklikk på arkfanen og velg kommando kilde (Kildekode). Kopier og lim inn følgende VBA-kode i vinduet som åpnes:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Every Cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next celle End If End Sub  

Dens logikk er som følger:

• Generelt er dette en hendelsesbehandler Arbeidsark_Endre, dvs. at denne makroen kjøres automatisk ved enhver endring i en hvilken som helst celle på gjeldende ark.
• Referansen til den endrede cellen vil alltid være i variabelen Target.
• Først sjekker vi at brukeren har endret nøyaktig cellen med kriteriet (A4) – dette gjøres av operatøren if.
• Så starter syklusen For hver… å iterere over grå celler (D2:O2) med TRUE / FALSE indikatorverdier for hver kolonne.
• Hvis verdien til neste grå celle er TRUE (true), er ikke kolonnen skjult, ellers skjuler vi den (egenskap skjult).

•  Dynamiske array-funksjoner fra Office 365: FILTER, SORT og UNIC
• Pivottabell med flerlinjeoverskrift ved hjelp av Power Query
• Hva er makroer, hvordan lage og bruke dem