Horizontalno filtriranje stolpcev v Excelu

Če niste povsem novi uporabnik, potem ste zagotovo že opazili, da je 99% vsega v Excelu zasnovano za delo z navpičnimi tabelami, kjer parametri ali atributi (polja) potekajo skozi stolpce in se nahajajo informacije o predmetih ali dogodkih v vrsticah. Vrtilne tabele, delni seštevki, kopiranje formul z dvojnim klikom – vse je prilagojeno posebej za ta format podatkov.

Vendar ni pravil brez izjem in dokaj pogosto me sprašujejo, kaj storiti, če je pri delu naletela na tabelo s horizontalno pomensko orientacijo ali tabelo, kjer imajo vrstice in stolpci enako pomensko težo:

In če Excel še vedno zna sortirati vodoravno (z ukazom Podatki – Razvrsti – Možnosti – Razvrsti stolpce), potem je situacija s filtriranjem slabša - v Excelu preprosto ni vgrajenih orodij za filtriranje stolpcev, ne vrstic. Torej, če se soočite s takšno nalogo, boste morali najti rešitve različnih stopenj zapletenosti.

Metoda 1. Nova funkcija FILTER

Če uporabljate novo različico Excela 2021 ali ste naročeni na Excel 365, lahko izkoristite novo uvedeno funkcijo FILTER (FILTER), ki lahko filtrira izvorne podatke ne samo po vrsticah, ampak tudi po stolpcih. Ta funkcija za delovanje potrebuje pomožno vodoravno enodimenzionalno matrično vrstico, kjer vsaka vrednost (TRUE ali FALSE) določa, ali prikažemo ali, nasprotno, skrijemo naslednji stolpec v tabeli.

Dodajmo naslednjo vrstico nad tabelo in vanjo zapišimo stanje vsakega stolpca:

• Recimo, da želimo vedno prikazati prvi in ​​zadnji stolpec (glave in vsote), zato zanju v prvi in ​​zadnji celici matrike nastavimo vrednost = TRUE.
• Za preostale stolpce bo vsebina ustreznih celic formula, ki s funkcijami preverja pogoj, ki ga potrebujemo И (IN) or OR (OR). Na primer, da je skupni znesek v razponu od 300 do 500.

Po tem ostane le še uporaba funkcije FILTER da izberete stolpce, nad katerimi ima naš pomožni niz vrednost TRUE:

Podobno lahko filtrirate stolpce glede na dani seznam. V tem primeru bo funkcija pomagala COUNTIF (COUNTIF), ki preveri število pojavitev imena naslednjega stolpca iz glave tabele na dovoljenem seznamu:

Metoda 2. Vrtilna tabela namesto običajne

Trenutno ima Excel vgrajeno vodoravno filtriranje po stolpcih samo v vrtilnih tabelah, tako da če nam uspe našo prvotno tabelo pretvoriti v vrtilno tabelo, lahko uporabimo to vgrajeno funkcionalnost. Za to mora naša izvorna tabela izpolnjevati naslednje pogoje:

• imejte »pravilno« enovrstično vrstico glave brez praznih in spojenih celic – sicer vrtilne tabele ne bo delovalo;
• ne vsebujejo dvojnikov v oznakah vrstic in stolpcev - v povzetku se bodo "zrušili" v seznam samo edinstvenih vrednosti;
• vsebujejo samo številke v območju vrednosti (na presečišču vrstic in stolpcev), ker bo vrtilna tabela zanje zagotovo uporabila neko funkcijo združevanja (vsota, povprečje itd.) in to ne bo delovalo z besedilom

Če so vsi ti pogoji izpolnjeni, potem, da bi zgradili vrtilno tabelo, ki je videti kot naša originalna tabela, jo (prvotno) je treba razširiti iz navzkrižne tabele v ravno (normalizirano). Najlažji način za to je z dodatkom Power Query, zmogljivim orodjem za pretvorbo podatkov, ki je vgrajeno v Excel od leta 2016. 

To so:

1. Pretvorimo tabelo v »pameten« dinamični ukaz Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo).
2. Nalaganje v Power Query z ukazom Podatki – iz tabele / obsega (Podatki – iz tabele / obsega).
3. Filtriramo vrstico s seštevki (povzetek bo imel svoje seštevke).
4. Z desno miškino tipko kliknite naslov prvega stolpca in izberite Razveljavi druge stolpce (Odstrani druge stolpce). Vsi neizbrani stolpci se pretvorijo v dva – ime zaposlenega in vrednost njegovega kazalnika.
5. Filtriranje stolpca s skupnimi vrednostmi, ki so šle v stolpec Lastnost.
6. Glede na dobljeno ravno (normalizirano) tabelo z ukazom zgradimo vrtilno tabelo Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri in naloži — Zapri in naloži v…).

Zdaj lahko uporabite možnost filtriranja stolpcev, ki so na voljo v vrtilnih tabelah – običajne kljukice pred imeni in elementi Podpisni filtri (Filtri oznak) or Filtri po vrednosti (Vrednostni filtri):

In seveda, ko spreminjate podatke, boste morali posodobiti našo poizvedbo in povzetek z bližnjico na tipkovnici Ctrl+druga+F5 ali ekipa Podatki – Osveži vse (Podatki — Osveži vse).

Metoda 3. Makro v VBA

Vse prejšnje metode, kot zlahka vidite, niso ravno filtriranje – ne skrijemo stolpcev na prvotnem seznamu, ampak oblikujemo novo tabelo z danim nizom stolpcev iz prvotne. Če je potrebno filtrirati (skrijeti) stolpce v izvornih podatkih, potem je potreben bistveno drugačen pristop, in sicer makro.

Recimo, da želimo sproti filtrirati stolpce, kjer ime upravitelja v glavi tabele ustreza maski, določeni v rumeni celici A4, na primer, začne se s črko »A« (to je, dobite »Anna« in »Arthur« " kot rezultat). 

Tako kot pri prvi metodi najprej implementiramo pomožno vrstico obsega, kjer bo v vsaki celici naš kriterij preverjen s formulo in logične vrednosti TRUE ali FALSE bodo prikazane za vidne in skrite stolpce:

Nato dodajmo preprost makro. Z desno miškino tipko kliknite zavihek lista in izberite ukaz vir (izvorna koda). Kopirajte in prilepite naslednjo kodo VBA v okno, ki se odpre:

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  

Njegova logika je naslednja:

• Na splošno je to naprava za obravnavo dogodkov Delovni list_Spremeni, kar pomeni, da se bo ta makro samodejno zagnal ob vsaki spremembi katere koli celice na trenutnem listu.
• Sklic na spremenjeno celico bo vedno v spremenljivki ciljna.
• Najprej preverimo, ali je uporabnik spremenil točno celico s kriterijem (A4) – to naredi operater if.
• Nato se cikel začne Za vsakogar… za ponavljanje sivih celic (D2:O2) z vrednostmi indikatorja TRUE/FALSE za vsak stolpec.
• Če je vrednost naslednje sive celice TRUE (true), potem stolpec ni skrit, sicer ga skrijemo (lastnost skrita).

•  Funkcije dinamičnega polja iz Office 365: FILTER, SORT in UNIC
• Vrtilna tabela z večvrstično glavo z uporabo Power Query
• Kaj so makri, kako jih ustvariti in uporabljati