Horizontale kolomfiltering in Excel

Als je niet helemaal een beginnende gebruiker bent, dan moet je al gemerkt hebben dat 99% van alles in Excel is ontworpen om te werken met verticale tabellen, waar parameters of attributen (velden) door de kolommen gaan en informatie over objecten of gebeurtenissen zich bevindt in de lijnen. Draaitabellen, subtotalen, formules kopiëren met een dubbelklik - alles is specifiek op dit dataformaat afgestemd.

Er zijn echter geen regels zonder uitzonderingen en met een vrij regelmatige frequentie wordt mij gevraagd wat te doen als een tabel met een horizontale semantische oriëntatie, of een tabel waar rijen en kolommen hetzelfde gewicht in betekenis hebben, in het werk tegenkomt:

En als Excel nog weet horizontaal te sorteren (met het commando Gegevens – Sorteren – Opties – Kolommen sorteren), dan is de situatie met filteren erger - er zijn gewoon geen ingebouwde tools voor het filteren van kolommen, geen rijen in Excel. Dus als u met zo'n taak wordt geconfronteerd, zult u oplossingen moeten bedenken van verschillende mate van complexiteit.

Methode 1. Nieuwe FILTER-functie

Als je de nieuwe versie van Excel 2021 of een Excel 365-abonnement hebt, kun je profiteren van de nieuw geïntroduceerde functie FILTER (FILTER), waarmee de brongegevens niet alleen op rijen, maar ook op kolommen kunnen worden gefilterd. Om te werken, heeft deze functie een horizontale eendimensionale array-rij nodig, waarbij elke waarde (TRUE of FALSE) bepaalt of we de volgende kolom in de tabel tonen of juist verbergen.

Laten we de volgende regel boven onze tabel toevoegen en de status van elke kolom erin schrijven:

• Laten we zeggen dat we altijd de eerste en laatste kolommen (koppen en totalen) willen weergeven, dus voor hen in de eerste en laatste cellen van de array stellen we de waarde = TRUE in.
• Voor de overige kolommen zal de inhoud van de corresponderende cellen een formule zijn die de voorwaarde controleert die we nodig hebben met behulp van functies И (EN) or OR (OR). Bijvoorbeeld dat het totaal tussen 300 en 500 ligt.

Daarna blijft het alleen om de functie te gebruiken FILTER om kolommen te selecteren waarboven onze hulparray een TRUE-waarde heeft:

Op dezelfde manier kunt u kolommen filteren op een bepaalde lijst. In dit geval zal de functie helpen COUNTIF (AANTAL.ALS), die het aantal keren dat de volgende kolomnaam voorkomt uit de tabelkop in de toegestane lijst controleert:

Methode 2. Draaitabel in plaats van de gebruikelijke

Momenteel heeft Excel alleen ingebouwde horizontale filtering op kolommen in draaitabellen, dus als het ons lukt om onze oorspronkelijke tabel om te zetten in een draaitabel, kunnen we deze ingebouwde functionaliteit gebruiken. Hiervoor moet onze brontabel aan de volgende voorwaarden voldoen:

• een "juiste" kopregel van één regel hebben zonder lege en samengevoegde cellen - anders zal het niet werken om een ​​draaitabel te bouwen;
• geen duplicaten bevatten in de labels van rijen en kolommen - ze zullen in de samenvatting "samenvouwen" tot een lijst met alleen unieke waarden;
• bevatten alleen getallen in het waardenbereik (op de kruising van rijen en kolommen), omdat de draaitabel zeker een soort aggregatiefunctie op hen zal toepassen (som, gemiddelde, enz.) en dit zal niet werken met de tekst

Als aan al deze voorwaarden is voldaan, moet deze (de originele) worden uitgebreid van de kruistabel naar een platte (genormaliseerd) om een ​​draaitabel te bouwen die eruitziet als onze oorspronkelijke tabel. En de gemakkelijkste manier om dit te doen is met de Power Query-invoegtoepassing, een krachtige tool voor gegevenstransformatie die sinds 2016 in Excel is ingebouwd. 

Deze zijn:

1. Laten we de tabel omzetten in een "slimme" dynamische opdracht Home – Opmaak als tabel (Home - Opmaken als tabel).
2. Laden in Power Query met de opdracht Gegevens – Van Tabel / Bereik (Gegevens – Van Tabel / Bereik).
3. We filteren de regel met de totalen (de samenvatting heeft zijn eigen totalen).
4. Klik met de rechtermuisknop op de eerste kolomkop en selecteer Andere kolommen samenvouwen (Maak de draai van andere kolommen ongedaan). Alle niet-geselecteerde kolommen worden omgezet in twee: de naam van de werknemer en de waarde van zijn indicator.
5. De kolom filteren met de totalen die in de kolom zijn gegaan Kenmerk.
6. We bouwen een draaitabel volgens de resulterende platte (genormaliseerde) tabel met het commando Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten & laden — Sluiten & laden naar…).

Nu kunt u de mogelijkheid gebruiken om kolommen te filteren die beschikbaar zijn in draaitabellen - de gebruikelijke vinkjes voor de namen en items Handtekeningfilters (Labelfilters) or Filtert op waarde (Waardefilters):

En natuurlijk, wanneer u de gegevens wijzigt, moet u onze zoekopdracht en de samenvatting bijwerken met een sneltoets Ctrl+anders+F5 of team Gegevens – Alles vernieuwen (Gegevens — Alles vernieuwen).

Methode 3. Macro in VBA

Zoals u gemakkelijk kunt zien, filteren alle voorgaande methoden niet precies - we verbergen de kolommen in de originele lijst niet, maar vormen een nieuwe tabel met een bepaalde set kolommen van de originele. Als het nodig is om de kolommen in de brongegevens te filteren (verbergen), dan is een fundamenteel andere aanpak nodig, namelijk een macro.

Stel dat we kolommen direct willen filteren waarbij de naam van de manager in de tabelkop voldoet aan het masker dat is gespecificeerd in de gele cel A4, bijvoorbeeld begint met de letter "A" (dat wil zeggen, haal "Anna" en "Arthur " als resultaat). 

Net als bij de eerste methode implementeren we eerst een hulpbereikrij, waarbij in elke cel ons criterium wordt gecontroleerd door een formule en de logische waarden TRUE of FALSE worden weergegeven voor respectievelijk zichtbare en verborgen kolommen:

Laten we dan een eenvoudige macro toevoegen. Klik met de rechtermuisknop op de bladtab en selecteer opdracht bron (Broncode). Kopieer en plak de volgende VBA-code in het geopende venster:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range ("D2:O2") If cell = True Dan cell.EntireColumn.Hidden = False Anders cel.EntireColumn.Hidden = Waar Einde Als Volgende cel Einde Als Einde Sub  

De logica ervan is als volgt:

• Over het algemeen is dit een gebeurtenisafhandelaar Werkblad_Wijzigen, dwz deze macro wordt automatisch uitgevoerd bij elke wijziging in een cel op het huidige blad.
• De verwijzing naar de gewijzigde cel zal altijd in de variabele staan doelwit.
• Eerst controleren we of de gebruiker precies de cel met het criterium (A4) heeft gewijzigd – dit wordt gedaan door de operator if.
• Dan begint de cyclus Voor elke… om grijze cellen (D2:O2) te herhalen met TRUE / FALSE-indicatorwaarden voor elke kolom.
• Als de waarde van de volgende grijze cel TRUE (true) is, is de kolom niet verborgen, anders verbergen we deze (eigenschap verborgen).

•  Dynamische arrayfuncties van Office 365: FILTER, SORT en UNIC
• Draaitabel met koptekst met meerdere regels met Power Query
• Wat zijn macro's, hoe maak je ze aan en gebruik je ze?