Vandret kolonnefiltrering i Excel

Hvis du ikke er helt nybegynder, så har du sikkert allerede bemærket, at 99% af alt i Excel er designet til at arbejde med vertikale tabeller, hvor parametre eller attributter (felter) går gennem kolonnerne, og information om objekter eller begivenheder er placeret. i linjerne. Pivottabeller, subtotaler, kopiering af formler med et dobbeltklik – alt er skræddersyet specifikt til dette dataformat.

Der er dog ingen regler uden undtagelser, og med en nogenlunde regelmæssig frekvens bliver jeg spurgt, hvad jeg skal gøre, hvis en tabel med vandret semantisk orientering, eller en tabel, hvor rækker og kolonner har samme betydningsvægt, stødte på i arbejdet:

Og hvis Excel stadig ved, hvordan man sorterer vandret (med kommandoen Data – Sorter – Indstillinger – Sorter kolonner), så er situationen med filtrering værre – der er simpelthen ingen indbyggede værktøjer til at filtrere kolonner, ikke rækker i Excel. Så hvis du står over for en sådan opgave, bliver du nødt til at finde løsninger af forskellig grad af kompleksitet.

Metode 1. Ny FILTER-funktion

Hvis du bruger den nye version af Excel 2021 eller et Excel 365-abonnement, kan du drage fordel af den nyligt introducerede funktion FILTER (FILTER), som kan filtrere kildedataene ikke kun efter rækker, men også efter kolonner. For at fungere kræver denne funktion en ekstra vandret endimensionel array-række, hvor hver værdi (TRUE eller FALSE) bestemmer, om vi viser eller omvendt skjuler den næste kolonne i tabellen.

Lad os tilføje følgende linje over vores tabel og skrive status for hver kolonne i den:

• Lad os sige, at vi altid ønsker at vise den første og sidste kolonne (overskrifter og totaler), så for dem i den første og sidste celle i arrayet sætter vi værdien = TRUE.
• For de resterende kolonner vil indholdet af de tilsvarende celler være en formel, der kontrollerer den betingelse, vi har brug for ved hjælp af funktioner И (OG) or OR (OR). For eksempel at totalen er i intervallet fra 300 til 500.

Derefter er det kun tilbage at bruge funktionen FILTER for at vælge kolonner, over hvilke vores hjælpearray har en TRUE-værdi:

På samme måde kan du filtrere kolonner efter en given liste. I dette tilfælde vil funktionen hjælpe TÆL.HVIS (COUNTIF), som kontrollerer antallet af forekomster af det næste kolonnenavn fra tabeloverskriften på den tilladte liste:

Metode 2. Pivottabel i stedet for den sædvanlige

I øjeblikket har Excel indbygget vandret filtrering efter kolonner kun i pivottabeller, så hvis vi formår at konvertere vores oprindelige tabel til en pivottabel, kan vi bruge denne indbyggede funktionalitet. For at gøre dette skal vores kildetabel opfylde følgende betingelser:

• have en "korrekt" en-linjes overskriftslinje uden tomme og flettede celler - ellers vil det ikke fungere at bygge en pivottabel;
• ikke indeholder dubletter i etiketterne for rækker og kolonner - de vil "kollapse" i oversigten til en liste med kun unikke værdier;
• indeholder kun tal i rækken af ​​værdier (ved skæringspunktet mellem rækker og kolonner), fordi pivottabellen helt sikkert vil anvende en form for aggregeringsfunktion på dem (sum, gennemsnit osv.), og dette vil ikke fungere med teksten

Hvis alle disse betingelser er opfyldt, så for at kunne bygge en pivottabel, der ligner vores originale tabel, skal den (den originale) udvides fra krydstabel til en flad (normaliseret). Og den nemmeste måde at gøre dette på er med tilføjelsesprogrammet Power Query, et kraftfuldt datatransformationsværktøj indbygget i Excel siden 2016. 

Disse er:

1. Lad os konvertere tabellen til en "smart" dynamisk kommando Hjem – Formater som en tabel (Hjem — Formater som tabel).
2. Indlæser i Power Query med kommandoen Data – fra tabel / område (Data – fra tabel / område).
3. Vi filtrerer linjen med totalerne (resuméet vil have sine egne totaler).
4. Højreklik på den første kolonneoverskrift og vælg Frigør andre kolonner (Ophæv pivot andre kolonner). Alle ikke-valgte kolonner konverteres til to - navnet på medarbejderen og værdien af ​​hans indikator.
5. Filtrering af kolonnen med totalerne, der gik ind i kolonnen Attribut.
6. Vi bygger en pivottabel i henhold til den resulterende flade (normaliserede) tabel med kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk og indlæs — Luk og indlæs til...).

Nu kan du bruge muligheden for at filtrere kolonner, der er tilgængelige i pivottabeller - de sædvanlige flueben foran navnene og elementerne Signaturfiltre (Etiketfiltre) or Filtrerer efter værdi (Værdifiltre):

Og selvfølgelig, når du ændrer dataene, skal du opdatere vores forespørgsel og oversigten med en tastaturgenvej Ctrl+andre+F5 eller hold Data – Opdater alle (Data – Opdater alle).

Metode 3. Makro i VBA

Alle de tidligere metoder, som du nemt kan se, filtrerer ikke ligefrem - vi skjuler ikke kolonnerne i den oprindelige liste, men danner en ny tabel med et givet sæt kolonner fra den oprindelige. Hvis det er påkrævet at filtrere (skjule) kolonnerne i kildedataene, er det nødvendigt med en fundamentalt anden tilgang, nemlig en makro.

Antag, at vi vil filtrere kolonner på farten, hvor navnet på lederen i tabeloverskriften opfylder masken angivet i den gule celle A4, for eksempel starter med bogstavet "A" (det vil sige, få "Anna" og "Arthur" " som resultat). 

Som i den første metode implementerer vi først en hjælpeområderække, hvor vores kriterium i hver celle vil blive kontrolleret af en formel, og de logiske værdier TRUE eller FALSE vil blive vist for henholdsvis synlige og skjulte kolonner:

Lad os derefter tilføje en simpel makro. Højreklik på arkfanen og vælg kommando Kilde (Kildekode). Kopier og indsæt følgende VBA-kode i vinduet, der åbnes:

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 Næste celle End If End Sub  

Dens logik er som følger:

• Generelt er dette en hændelseshandler Arbejdsark_Ændring, dvs. denne makro vil automatisk køre ved enhver ændring af en celle på det aktuelle ark.
• Referencen til den ændrede celle vil altid være i variablen mål.
• Først kontrollerer vi, at brugeren har ændret nøjagtigt cellen med kriteriet (A4) - dette gøres af operatøren if.
• Så starter cyklussen For hver… at iterere over grå celler (D2:O2) med TRUE / FALSE indikatorværdier for hver kolonne.
• Hvis værdien af ​​den næste grå celle er TRUE (true), så er kolonnen ikke skjult, ellers skjuler vi den (egenskab Skjult).

•  Dynamiske array-funktioner fra Office 365: FILTER, SORT og UNIC
• Pivottabel med multiline header ved hjælp af Power Query
• Hvad er makroer, hvordan man opretter og bruger dem