Sadržaj
Ako niste početnik, sigurno ste već primijetili da je 99% svega u Excelu dizajnirano za rad s okomitim tablicama, gdje parametri ili atributi (polja) prolaze kroz stupce, a nalaze se informacije o objektima ili događajima u redovima . Pivot tablice, međuzbrojevi, kopiranje formula dvostrukim klikom – sve je skrojeno posebno za ovaj format podataka.
Međutim, nema pravila bez iznimaka i prilično redovito me pitaju što učiniti ako se u radu naiđe na tablicu s vodoravnom semantičkom orijentacijom ili tablicu u kojoj redovi i stupci imaju istu težinu u značenju:
A ako Excel još uvijek zna sortirati vodoravno (naredbom Podaci – Sortiraj – Opcije – Sortiraj stupce), onda je situacija s filtriranjem gora - jednostavno nema ugrađenih alata za filtriranje stupaca, a ne redaka u Excelu. Dakle, ako se suočite s takvim zadatkom, morat ćete smisliti zaobilazna rješenja različitih stupnjeva složenosti.
Metoda 1. Nova funkcija FILTER
Ako ste na novoj verziji programa Excel 2021 ili ste pretplaćeni na Excel 365, možete iskoristiti prednosti novouvedene značajke FILTER (FILTAR), koji može filtrirati izvorne podatke ne samo po redovima, već i po stupcima. Za rad, ova funkcija zahtijeva pomoćni vodoravni jednodimenzionalni niz-red, gdje svaka vrijednost (TRUE ili FALSE) određuje hoćemo li prikazati ili, obrnuto, sakriti sljedeći stupac u tablici.
Dodajmo sljedeći redak iznad naše tablice i napišimo status svakog stupca u njemu:
- Recimo da uvijek želimo prikazati prve i zadnje stupce (zaglavlja i zbrojeve), pa za njih u prvoj i zadnjoj ćeliji niza postavimo vrijednost = TRUE.
- Za preostale stupce, sadržaj odgovarajućih ćelija bit će formula koja provjerava uvjet koji nam je potreban pomoću funkcija И (I) or OR (ILI). Na primjer, da je zbroj u rasponu od 300 do 500.
Nakon toga ostaje samo koristiti funkciju FILTER za odabir stupaca iznad kojih naš pomoćni niz ima TRUE vrijednost:
Slično, možete filtrirati stupce prema danom popisu. U ovom slučaju, funkcija će vam pomoći COUNTIF (COUNTIF), koji provjerava broj pojavljivanja naziva sljedećeg stupca iz zaglavlja tablice na dopuštenom popisu:
Metoda 2. Pivot tablica umjesto uobičajene
Trenutno Excel ima ugrađeno horizontalno filtriranje po stupcima samo u zaokretnim tablicama, tako da ako uspijemo našu izvornu tablicu pretvoriti u zaokretnu tablicu, možemo koristiti ovu ugrađenu funkcionalnost. Da bismo to učinili, naša izvorna tablica mora zadovoljiti sljedeće uvjete:
- imati "ispravan" redak zaglavlja u jednom retku bez praznih i spojenih ćelija - inače neće uspjeti izgraditi stožernu tablicu;
- nemojte sadržavati duplikate u oznakama redaka i stupaca - oni će se "srušiti" u sažetku u popis samo jedinstvenih vrijednosti;
- sadrže samo brojeve u rasponu vrijednosti (na sjecištu redaka i stupaca), jer će zaokretna tablica na njih sigurno primijeniti neku vrstu agregacijske funkcije (zbroj, prosjek itd.) i to neće raditi s tekstom
Ako su svi ovi uvjeti zadovoljeni, onda da bismo izgradili stožernu tablicu koja izgleda kao naša originalna tablica, ona (originalna) mora biti proširena iz unakrsne tablice u ravnu (normaliziranu). A najlakši način da to učinite je s dodatkom Power Query, snažnim alatom za transformaciju podataka ugrađenim u Excel od 2016.
To su:
- Pretvorimo tablicu u “pametnu” dinamičku naredbu Početna – Format kao tablica (Početna — Formatiraj kao tablicu).
- Učitavanje u Power Query s naredbom Podaci – iz tablice / raspona (Podaci – iz tablice / raspona).
- Filtriramo redak s ukupnim zbrojevima (sažetak će imati svoje ukupne zbrojeve).
- Desnom tipkom miša kliknite naslov prvog stupca i odaberite Poništavanje skupljanja ostalih stupaca (Poništi zakretanje ostalih stupaca). Svi stupci koji nisu odabrani pretvaraju se u dva – ime zaposlenika i vrijednost njegovog pokazatelja.
- Filtriranje stupca s ukupnim iznosima koji su ušli u stupac Atribut.
- Naredbom gradimo stožernu tablicu prema dobivenoj ravnoj (normaliziranoj) tablici Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori i učitaj — Zatvori i učitaj u…).
Sada možete koristiti mogućnost filtriranja stupaca dostupnih u zaokretnim tablicama – uobičajene kvačice ispred naziva i stavki Filtri potpisa (Filtri naljepnica) or Filtri po vrijednosti (Filtri vrijednosti):
I naravno, kada mijenjate podatke, morat ćete ažurirati naš upit i sažetak pomoću tipkovničke prečice Ctrl+drugo+F5 ili tim Podaci – Osvježi sve (Podaci — Osvježi sve).
Metoda 3. Makro u VBA
Sve prethodne metode, kao što lako možete vidjeti, nisu baš filtrirajuće – ne skrivamo stupce na izvornom popisu, već formiramo novu tablicu sa zadanim skupom stupaca iz izvorne. Ako je potrebno filtrirati (sakriti) stupce u izvornim podacima, tada je potreban bitno drugačiji pristup, naime makronaredba.
Pretpostavimo da želimo filtrirati stupce u hodu gdje ime upravitelja u zaglavlju tablice zadovoljava masku navedenu u žutoj ćeliji A4, na primjer, počinje slovom "A" (tj. dobiti "Anna" i "Arthur" " kao rezultat).
Kao i u prvoj metodi, prvo implementiramo pomoćni raspon-redak, gdje će se u svakoj ćeliji naš kriterij provjeriti formulom, a logičke vrijednosti TRUE ili FALSE bit će prikazane za vidljive odnosno skrivene stupce:
Onda dodajmo jednostavnu makronaredbu. Desnom tipkom miša kliknite karticu lista i odaberite naredbu izvor (Izvorni kod). Kopirajte i zalijepite sljedeći VBA kod u prozor koji se otvori:
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 sljedeća:
- Općenito, ovo je rukovatelj događajima Radni list_Promjena, tj. ova će se makronaredba automatski pokrenuti pri svakoj promjeni bilo koje ćelije na trenutnom listu.
- Referenca na promijenjenu ćeliju uvijek će biti u varijabli Meta.
- Prvo provjeravamo da je korisnik promijenio točno ćeliju s kriterijem (A4) – to radi operator if.
- Tada počinje ciklus Za svakoga… za ponavljanje preko sivih ćelija (D2:O2) s vrijednostima indikatora TRUE/FALSE za svaki stupac.
- Ako je vrijednost sljedeće sive ćelije TRUE (true), tada stupac nije skriven, inače ga skrivamo (svojstvo skriven).
- Funkcije dinamičkog niza iz sustava Office 365: FILTER, SORT i UNIC
- Zaokretna tablica s višelinijskim zaglavljem pomoću Power Queryja
- Što su makronaredbe, kako ih izraditi i koristiti