Ako niste baš početnik, sigurno ste već primijetili da je 99% svega u Excel-u dizajnirano za rad s vertikalnim tabelama, gdje parametri ili atributi (polja) prolaze kroz stupce, a informacije o objektima ili događajima se nalaze u redovima. Zaokretne tabele, međuzbirovi, kopiranje formula dvostrukim klikom – sve je skrojeno posebno za ovaj format podataka.
Međutim, nema pravila bez izuzetaka i prilično redovno me pitaju šta da radim ako u radu naiđe na tabelu horizontalne semantičke orijentacije, odnosno tabelu u kojoj redovi i kolone imaju istu težinu u značenju:
A ako Excel i dalje zna kako da sortira horizontalno (sa naredbom Podaci – Sortiraj – Opcije – Sortiraj kolone), onda je situacija s filtriranjem gora – jednostavno nema ugrađenih alata za filtriranje kolona, a ne redova u Excelu. Dakle, ako se suočite s takvim zadatkom, morat ćete smisliti rješenja različitog stepena složenosti.
Metoda 1. Nova funkcija FILTER
Ako koristite novu verziju Excel 2021 ili pretplatu na Excel 365, možete iskoristiti novouvedenu funkciju FILTER (FILTER), koji može filtrirati izvorne podatke ne samo po redovima, već i po stupcima. Da bi radila, ova funkcija zahtijeva pomoćni horizontalni jednodimenzionalni niz-red, gdje svaka vrijednost (TRUE ili FALSE) određuje hoćemo li prikazati ili, obrnuto, sakriti sljedeću kolonu u tabeli.
Dodajmo sljedeći red iznad naše tabele i upišemo status svake kolone u nju:
- Recimo da uvijek želimo prikazati prvu i posljednju kolonu (zaglavlja i zbrojeve), pa za njih u prvoj i posljednjoj ćelijama niza postavljamo vrijednost = TRUE.
- Za preostale stupce, sadržaj odgovarajućih ćelija bit će formula koja provjerava stanje koje nam je potrebno pomoću funkcija И (I) or OR (OR). Na primjer, da je ukupan broj u rasponu od 300 do 500.
Nakon toga, ostaje samo koristiti funkciju FILTER da odaberete kolone iznad kojih naš pomoćni niz ima TRUE vrijednost:
Slično, možete filtrirati kolone prema datoj listi. U ovom slučaju, funkcija će pomoći COUNTIF (COUNTIF), koji provjerava broj pojavljivanja imena sljedeće kolone iz zaglavlja tablice na listi dozvoljenih:
Metoda 2. Pivot tabela umjesto uobičajene
Trenutno Excel ima ugrađeno horizontalno filtriranje po kolonama samo u zaokretnim tabelama, tako da ako uspijemo da konvertujemo našu originalnu tabelu u pivot tabelu, možemo koristiti ovu ugrađenu funkcionalnost. Da bismo to učinili, naša izvorna tabela mora zadovoljiti sljedeće uslove:
- imati “ispravan” red zaglavlja u jednom redu bez praznih i spojenih ćelija – inače neće raditi za pravljenje pivot tablice;
- ne sadrže duplikate u oznakama redova i kolona – oni će se „srušiti“ u sažetku u listu samo jedinstvenih vrijednosti;
- sadrže samo brojeve u rasponu vrijednosti (na sjecištu redaka i stupaca), jer će pivot tablica definitivno primijeniti neku vrstu funkcije agregiranja na njih (zbir, prosjek, itd.) i to neće funkcionirati s tekstom
Ako su svi ovi uvjeti ispunjeni, tada će se, da bi se napravila pivot tablica koja izgleda kao naša originalna tabela, (originalna) trebati proširiti iz unakrsne tabele u ravnu (normaliziranu). A najlakši način da to učinite je pomoću dodatka Power Query, moćnog alata za transformaciju podataka ugrađenog u Excel od 2016.
Ovo su:
- Pretvorimo tabelu u “pametnu” dinamičku komandu Početna – Formatirajte kao tabelu (Početna — Format kao tabela).
- Učitavanje u Power Query pomoću naredbe Podaci – iz tabele / opsega (podaci - iz tabele / opsega).
- Filtriramo red sa ukupnim iznosima (sažetak će imati svoje zbrojeve).
- Desnom tipkom miša kliknite naslov prve kolone i odaberite Poništite druge kolone (Poništi druge kolone). Sve kolone koje nisu odabrane se pretvaraju u dvije – ime zaposlenog i vrijednost njegovog indikatora.
- Filtriranje kolone sa ukupnim iznosima koji su ušli u kolonu atribut.
- Naredbom gradimo stožernu tabelu prema rezultujućoj ravnoj (normalizovanoj) tabeli Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori i učitaj — Zatvori i učitaj do…).
Sada možete koristiti mogućnost filtriranja kolona dostupnih u zaokretnim tabelama – uobičajene kvačice ispred imena i stavki Signature Filters (Filteri oznaka) or Filtrira po vrijednosti (Filteri vrijednosti):
I naravno, kada mijenjate podatke, morat ćete ažurirati naš upit i sažetak pomoću prečice na tipkovnici Ctrl+alt+F5 ili tim Podaci – Osvježi sve (Podaci — Osvježi sve).
Metod 3. Makro u VBA
Sve prethodne metode, kao što možete lako vidjeti, nisu baš filtriranje – ne skrivamo kolone u originalnoj listi, već formiramo novu tabelu sa datim skupom kolona iz originalne. Ako je potrebno filtrirati (sakriti) stupce u izvornim podacima, onda je potreban fundamentalno drugačiji pristup, naime, makro.
Pretpostavimo da želimo da filtriramo kolone u hodu u kojima ime menadžera u zaglavlju tabele zadovoljava masku navedenu u žutoj ćeliji A4, na primer, počinje slovom "A" (to jest, dobijamo "Anna" i "Arthur" " kao rezultat).
Kao iu prvoj metodi, prvo implementiramo pomoćni red raspona, gdje će u svakoj ćeliji naš kriterij biti provjeren formulom i logičke vrijednosti TRUE ili FALSE će biti prikazane za vidljive i skrivene stupce, respektivno:
Zatim dodajmo jednostavan makro. Kliknite desnim tasterom miša na karticu lista i izaberite komandu izvor (Izvorni kod). Kopirajte i zalijepite sljedeći VBA kod u prozor koji se otvori:
Privatni pod Radni list_Change(ByVal Target As Range) Ako Target.Address = "$A$4" Zatim Za svaku ćeliju u opsegu("D2:O2") Ako je ćelija = True Onda cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Sljedeća ćelija End If End Sub Njegova logika je sljedeća:
- Općenito, ovo je obrađivač događaja Radni list_Promjena, tj. ovaj makro će se automatski pokrenuti pri svakoj promjeni bilo koje ćelije na trenutnom listu.
- Referenca na promijenjenu ćeliju uvijek će biti u varijabli meta.
- Prvo provjeravamo da li je korisnik promijenio tačno ćeliju sa kriterijem (A4) – to radi operater if.
- Tada počinje ciklus Za svaki… za ponavljanje preko sivih ćelija (D2:O2) sa vrijednostima indikatora TRUE / FALSE za svaku kolonu.
- Ako je vrijednost sljedeće sive ćelije TRUE (true), kolona nije skrivena, inače je sakrivamo (svojstvo sakriven).
- Funkcije dinamičkog niza iz Office 365: FILTER, SORT i UNIC
- Zaokretna tabela sa višerednim zaglavljem koristeći Power Query
- Šta su makroi, kako ih kreirati i koristiti