Horisontale kolomfiltrering in Excel

As jy nie heeltemal 'n beginner gebruiker is nie, moet jy alreeds opgemerk het dat 99% van alles in Excel ontwerp is om met vertikale tabelle te werk, waar parameters of eienskappe (velde) deur die kolomme gaan, en inligting oor voorwerpe of gebeurtenisse geleë is. in die lyne. Spiltabelle, subtotale, kopieer formules met 'n dubbelklik – alles is spesifiek vir hierdie dataformaat aangepas.

Daar is egter geen reëls sonder uitsonderings nie en met 'n redelik gereelde frekwensie word ek gevra wat om te doen as 'n tabel met 'n horisontale semantiese oriëntasie, of 'n tabel waar rye en kolomme dieselfde gewig in betekenis het, in die werk teëgekom het:

En as Excel nog steeds weet hoe om horisontaal te sorteer (met die opdrag Data – Sorteer – Opsies – Sorteer kolomme), dan is die situasie met filtering erger – daar is eenvoudig geen ingeboude gereedskap om kolomme te filter nie, nie rye in Excel nie. Dus, as jy voor so 'n taak te staan ​​kom, sal jy met oplossings van verskillende grade van kompleksiteit vorendag moet kom.

Metode 1. Nuwe FILTER-funksie

As jy op die nuwe weergawe van Excel 2021 of 'n Excel 365-intekening is, kan jy voordeel trek uit die nuut bekendgestelde kenmerk FILTER (FILTER), wat die brondata nie net volgens rye kan filtreer nie, maar ook volgens kolomme. Om te werk, vereis hierdie funksie 'n bykomende horisontale eendimensionele skikking-ry, waar elke waarde (WAAR of ONWAAR) bepaal of ons die volgende kolom in die tabel wys of, omgekeerd, versteek.

Kom ons voeg die volgende reël bo ons tabel by en skryf die status van elke kolom daarin:

• Kom ons sê ons wil altyd die eerste en laaste kolomme (opskrifte en totale) vertoon, dus vir hulle in die eerste en laaste selle van die skikking stel ons die waarde = TRUE.
• Vir die oorblywende kolomme sal die inhoud van die ooreenstemmende selle 'n formule wees wat die toestand kontroleer wat ons benodig met behulp van funksies И (EN) or OR (OR). Byvoorbeeld, dat die totaal in die reeks van 300 tot 500 is.

Daarna bly dit net om die funksie te gebruik FILTER om kolomme te kies waarbo ons hulpskikking 'n WARE waarde het:

Net so kan u kolomme volgens 'n gegewe lys filter. In hierdie geval sal die funksie help COUNTIF (COUNTIF), wat die aantal voorkomste van die volgende kolomnaam vanaf die tabelopskrif in die toegelate lys nagaan:

Metode 2. Spiltabel in plaas van die gewone een

Tans het Excel ingeboude horisontale filtering volgens kolomme slegs in spilpunttabelle, so as ons dit regkry om ons oorspronklike tabel in 'n spiltabel om te skakel, kan ons hierdie ingeboude funksionaliteit gebruik. Om dit te doen, moet ons brontabel aan die volgende voorwaardes voldoen:

• 'n "korrekte" eenlyn-koplyn hê sonder leë en saamgevoegde selle – anders sal dit nie werk om 'n spiltabel te bou nie;
• moenie duplikate in die etikette van rye en kolomme bevat nie – dit sal in die opsomming “inmekaarvou” in 'n lys van slegs unieke waardes;
• bevat slegs getalle in die reeks waardes (by die kruising van rye en kolomme), want die spiltabel sal beslis 'n soort samevoegingsfunksie daarop toepas (som, gemiddeld, ens.) en dit sal nie met die teks werk nie.

As aan al hierdie voorwaardes voldoen word, sal dit (die oorspronklike een) van die kruistabel na 'n plat een uitgebrei moet word om 'n spiltabel te bou wat soos ons oorspronklike tabel lyk (genormaliseer). En die maklikste manier om dit te doen is met die Power Query-byvoeging, 'n kragtige datatransformasie-instrument wat sedert 2016 in Excel ingebou is. 

Dit is:

1. Kom ons verander die tabel in 'n "slim" dinamiese opdrag Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel).
2. Laai tans in Power Query met die opdrag Data – Van Tabel / Reeks (Data – Van Tabel / Reeks).
3. Ons filtreer die lyn met die totale (die opsomming sal sy eie totale hê).
4. Regskliek op die eerste kolomopskrif en kies Ontvou ander kolomme (Ontspil ander kolomme). Alle nie-geselekteerde kolomme word in twee omgeskakel – die naam van die werknemer en die waarde van sy aanwyser.
5. Filtreer die kolom met die totale wat in die kolom ingegaan het kenmerk.
6. Ons bou 'n spiltabel volgens die resulterende plat (genormaliseerde) tabel met die opdrag Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na...).

Nou kan jy die vermoë gebruik om kolomme beskikbaar in spilpunttabelle te filter – die gewone regmerkies voor die name en items Handtekening filters (Etiketfilters) or Filtreer volgens waarde (Waardefilters):

En natuurlik, wanneer jy die data verander, sal jy ons navraag en die opsomming moet opdateer met 'n sleutelbordkortpad Ctrl+alt+F5 of span Data – Herlaai alles (Data – Herlaai alles).

Metode 3. Makro in VBA

Al die vorige metodes, soos u maklik kan sien, is nie presies filter nie – ons versteek nie die kolomme in die oorspronklike lys nie, maar vorm 'n nuwe tabel met 'n gegewe stel kolomme van die oorspronklike een. As dit vereis word om die kolomme in die brondata te filtreer (versteek), dan is 'n fundamenteel ander benadering nodig, naamlik 'n makro.

Gestel ons wil kolomme dadelik filter waar die naam van die bestuurder in die tabelopskrif voldoen aan die masker gespesifiseer in die geel sel A4, byvoorbeeld, begin met die letter "A" (dit wil sê, kry "Anna" en "Arthur " as gevolg daarvan). 

Soos in die eerste metode, implementeer ons eers 'n hulpreeks-ry, waar in elke sel ons maatstaf deur 'n formule gekontroleer sal word en die logiese waardes TRUE of FALSE sal onderskeidelik vir sigbare en versteekte kolomme vertoon word:

Kom ons voeg dan 'n eenvoudige makro by. Regskliek op die bladoortjie en kies opdrag Bron (Bronkode). Kopieer en plak die volgende VBA-kode in die venster wat oopmaak:

Private Sub Worksheet_Change(ByVal Target As Range) As Target.Address = "$A$4" Dan Vir Elke sel In Range("D2:O2") As sel = Waar Dan cell.EntireColumn.Hidden = False Anders cell.EntireColumn.Hidden = Ware Einde As Volgende sel Einde As Einde Sub  

Die logika daarvan is soos volg:

• Oor die algemeen is dit 'n gebeurtenishanteerder Werkkaart_Verander, dws hierdie makro sal outomaties loop op enige verandering aan enige sel op die huidige blad.
• Die verwysing na die veranderde sel sal altyd in die veranderlike wees Doel.
• Eerstens kyk ons ​​of die gebruiker presies die sel met die maatstaf (A4) verander het – dit word deur die operateur gedoen if.
• Dan begin die siklus Vir elke… om oor grys selle (D2:O2) te herhaal met WAAR / ONWAAR aanwyserwaardes vir elke kolom.
• As die waarde van die volgende grys sel WAAR (waar) is, word die kolom nie versteek nie, anders versteek ons ​​dit (eienskap verborge).

•  Dinamiese skikking funksies vanaf Office 365: FILTER, SORT, en UNIC
• Spiltabel met multilyn-kopskrif met behulp van Power Query
• Wat is makro's, hoe om dit te skep en te gebruik