Horisontell kolumnfiltrering i Excel

Om du inte är helt nybörjare, måste du redan ha märkt att 99% av allt i Excel är designat för att fungera med vertikala tabeller, där parametrar eller attribut (fält) går igenom kolumnerna och information om objekt eller händelser finns. i raderna. Pivottabeller, delsummor, kopiering av formler med ett dubbelklick – allt är skräddarsytt specifikt för detta dataformat.

Det finns dock inga regler utan undantag och med en ganska regelbunden frekvens får jag frågan vad jag ska göra om en tabell med horisontell semantisk orientering, eller en tabell där rader och kolumner har samma betydelse i betydelse, stöter på i arbetet:

Och om Excel fortfarande vet hur man sorterar horisontellt (med kommandot Data – Sortera – Alternativ – Sortera kolumner), då är situationen med filtrering värre – det finns helt enkelt inga inbyggda verktyg för att filtrera kolumner, inte rader i Excel. Så om du står inför en sådan uppgift måste du komma på lösningar av varierande grad av komplexitet.

Metod 1. Ny FILTER-funktion

Om du använder den nya versionen av Excel 2021 eller en Excel 365-prenumeration kan du dra nytta av den nyligen introducerade funktionen FILTER (FILTRERA), som kan filtrera källdata inte bara efter rader utan också efter kolumner. För att fungera kräver denna funktion en extra horisontell endimensionell array-rad, där varje värde (TRUE eller FALSE) avgör om vi visar eller omvänt döljer nästa kolumn i tabellen.

Låt oss lägga till följande rad ovanför vår tabell och skriva statusen för varje kolumn i den:

• Låt oss säga att vi alltid vill visa de första och sista kolumnerna (rubriker och totaler), så för dem i den första och sista cellen i arrayen sätter vi värdet = TRUE.
• För de återstående kolumnerna kommer innehållet i motsvarande celler att vara en formel som kontrollerar villkoret vi behöver med hjälp av funktioner И (OCH) or OR (ELLER). Till exempel att summan ligger i intervallet från 300 till 500.

Därefter återstår bara att använda funktionen FILTER för att välja kolumner över vilka vår extra array har ett TRUE-värde:

På samma sätt kan du filtrera kolumner efter en given lista. I det här fallet kommer funktionen att hjälpa COUNTIF (COUNTIF), som kontrollerar antalet förekomster av nästa kolumnnamn från tabellrubriken i den tillåtna listan:

Metod 2. Pivottabell istället för den vanliga

För närvarande har Excel inbyggd horisontell filtrering efter kolumner endast i pivottabeller, så om vi lyckas konvertera vår ursprungliga tabell till en pivottabell kan vi använda denna inbyggda funktionalitet. För att göra detta måste vår källtabell uppfylla följande villkor:

• ha en "korrekt" enradsrubrik utan tomma och sammanslagna celler – annars fungerar det inte att bygga en pivottabell;
• Innehåll inte dubbletter i etiketterna för rader och kolumner – de kommer att "komprimera" i sammanfattningen till en lista med endast unika värden;
• innehåller endast siffror i värdeintervallet (vid skärningspunkten mellan rader och kolumner), eftersom pivottabellen definitivt kommer att tillämpa någon form av aggregeringsfunktion på dem (summa, medelvärde, etc.) och detta kommer inte att fungera med texten

Om alla dessa villkor är uppfyllda, för att bygga en pivottabell som ser ut som vår ursprungliga tabell, måste den (den ursprungliga) expanderas från korstabellen till en platt (normaliserad). Och det enklaste sättet att göra detta är med Power Query-tillägget, ett kraftfullt verktyg för datatransformation inbyggt i Excel sedan 2016. 

Dessa är:

1. Låt oss konvertera tabellen till ett "smart" dynamiskt kommando Hem – Formatera som en tabell (Hem — Formatera som tabell).
2. Laddar in i Power Query med kommandot Data – från tabell/intervall (Data – från tabell/intervall).
3. Vi filtrerar raden med summorna (sammanfattningen kommer att ha sina egna totaler).
4. Högerklicka på den första kolumnrubriken och välj Ta bort andra kolumner (Ta bort andra kolumner). Alla ej valda kolumner omvandlas till två – den anställdes namn och värdet på hans indikator.
5. Filtrera kolumnen med summan som gick in i kolumnen Attribut.
6. Vi bygger en pivottabell enligt den resulterande platta (normaliserade) tabellen med kommandot Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng & Ladda — Stäng & Ladda till...).

Nu kan du använda möjligheten att filtrera kolumner tillgängliga i pivottabeller – de vanliga bockarna framför namnen och objekten Signaturfilter (Etikettfilter) or Filtrerar efter värde (Värdefilter):

Och naturligtvis, när du ändrar data måste du uppdatera vår fråga och sammanfattningen med ett kortkommando ctrl+andra+F5 eller team Data – Uppdatera alla (Data – Uppdatera alla).

Metod 3. Makro i VBA

Alla tidigare metoder, som du lätt kan se, filtrerar inte exakt - vi gömmer inte kolumnerna i den ursprungliga listan, utan bildar en ny tabell med en given uppsättning kolumner från den ursprungliga. Om det krävs att filtrera (dölja) kolumnerna i källdata, så behövs ett fundamentalt annorlunda tillvägagångssätt, nämligen ett makro.

Anta att vi vill filtrera kolumner i farten där namnet på chefen i tabellhuvudet uppfyller masken som anges i den gula cellen A4, till exempel börjar med bokstaven "A" (det vill säga hämta "Anna" och "Arthur" " som ett resultat). 

Som i den första metoden implementerar vi först en extra intervallrad, där i varje cell vårt kriterium kommer att kontrolleras av en formel och de logiska värdena TRUE eller FALSE kommer att visas för synliga respektive dolda kolumner:

Låt oss sedan lägga till ett enkelt makro. Högerklicka på arkfliken och välj kommando Källa (Källkod). Kopiera och klistra in följande VBA-kod i fönstret som öppnas:

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 cell End If End Sub  

Dess logik är följande:

• I allmänhet är detta en händelsehanterare Arbetsblad_Ändra, dvs det här makrot körs automatiskt vid varje ändring av valfri cell på det aktuella arket.
• Referensen till den ändrade cellen kommer alltid att finnas i variabeln Målet.
• Först kontrollerar vi att användaren har ändrat exakt cellen med kriteriet (A4) – detta görs av operatören if.
• Sedan startar cykeln För varje… att iterera över grå celler (D2:O2) med TRUE / FALSE indikatorvärden för varje kolumn.
• Om värdet på nästa grå cell är TRUE (true) är kolumnen inte dold, annars döljer vi den (egenskap dold).

•  Dynamic array-funktioner från Office 365: FILTER, SORT och UNIC
• Pivottabell med flerradshuvud med hjälp av Power Query
• Vad är makron, hur man skapar och använder dem