Spis treści
Jeśli nie jesteś całkiem początkującym użytkownikiem, to zapewne już zauważyłeś, że 99% wszystkiego w Excelu jest przystosowane do pracy z tabelami pionowymi, gdzie przez kolumny przechodzą parametry lub atrybuty (pola), a informacje o obiektach lub zdarzeniach znajdują się w liniach. Tabele przestawne, sumy częściowe, kopiowanie formuł za pomocą dwukrotnego kliknięcia – wszystko jest dostosowane specjalnie do tego formatu danych.
Nie ma jednak żadnych reguł bez wyjątków i dość regularnie jestem pytany, co zrobić, jeśli w pracy natknę się na tabelę o poziomej orientacji semantycznej lub tabelę, w której wiersze i kolumny mają taką samą wagę:
A jeśli Excel nadal wie, jak sortować w poziomie (za pomocą polecenia Dane – Sortuj – Opcje – Sortuj kolumny), to sytuacja z filtrowaniem jest gorsza – po prostu nie ma wbudowanych narzędzi do filtrowania kolumn, a nie wierszy w Excelu. Tak więc, jeśli staniesz przed takim zadaniem, będziesz musiał wymyślić obejścia o różnym stopniu złożoności.
Metoda 1. Nowa funkcja FILTR
Jeśli korzystasz z nowej wersji programu Excel 2021 lub subskrypcji programu Excel 365, możesz skorzystać z nowo wprowadzonej funkcji FILTER (FILTR), który może filtrować dane źródłowe nie tylko według wierszy, ale także kolumn. Do działania funkcja ta wymaga pomocniczego poziomego jednowymiarowego wiersza tablicy, w którym każda wartość (PRAWDA lub FAŁSZ) określa, czy pokazujemy, czy odwrotnie, ukrywamy następną kolumnę w tabeli.
Dodajmy następujący wiersz nad naszą tabelą i wpiszmy w nim status każdej kolumny:
- Powiedzmy, że zawsze chcemy wyświetlać pierwszą i ostatnią kolumnę (nagłówki i sumy), więc dla nich w pierwszej i ostatniej komórce tablicy ustawiamy wartość = TRUE.
- Dla pozostałych kolumn zawartość odpowiednich komórek będzie formułą, która sprawdza warunek, którego potrzebujemy, za pomocą funkcji И (ORAZ) or OR (OR). Na przykład, że suma mieści się w zakresie od 300 do 500.
Po tym pozostaje tylko korzystać z funkcji FILTER aby wybrać kolumny, powyżej których nasza tablica pomocnicza ma wartość TRUE:
Podobnie możesz filtrować kolumny według podanej listy. W takim przypadku funkcja pomoże COUNTIF (LICZ.JEŻELI), który sprawdza liczbę wystąpień nazwy następnej kolumny z nagłówka tabeli na liście dozwolonych:
Metoda 2. Stół przestawny zamiast zwykłego
Obecnie Excel ma wbudowane filtrowanie poziome według kolumn tylko w tabelach przestawnych, więc jeśli uda nam się przekonwertować naszą oryginalną tabelę na tabelę przestawną, możemy skorzystać z tej wbudowanej funkcjonalności. Aby to zrobić, nasza tabela źródłowa musi spełniać następujące warunki:
- mieć „poprawny” jednowierszowy wiersz nagłówka bez pustych i scalonych komórek – w przeciwnym razie nie będzie działać budowanie tabeli przestawnej;
- nie zawierają duplikatów w etykietach wierszy i kolumn – „zwiną się” w podsumowaniu na listę tylko unikalnych wartości;
- zawierają tylko liczby z zakresu wartości (na przecięciu wierszy i kolumn), ponieważ tabela przestawna na pewno zastosuje do nich jakąś funkcję agregującą (suma, średnia itp.) i to nie zadziała z tekstem
Jeśli wszystkie te warunki są spełnione, to aby zbudować tabelę przestawną, która wygląda jak nasza oryginalna tabela, należy ją (oryginalną) rozwinąć z tabeli przestawnej do tabeli płaskiej (znormalizowanej). Najłatwiej to zrobić, korzystając z dodatku Power Query — zaawansowanego narzędzia do przekształcania danych wbudowanego w program Excel od 2016 r.
Są to:
- Przekształćmy tabelę w „inteligentne” dynamiczne polecenie Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę).
- Ładowanie do dodatku Power Query za pomocą polecenia Dane – Z tabeli / Zakres (Dane – Z tabeli / Zakres).
- Filtrujemy wiersz z sumami (podsumowanie będzie miało własne sumy).
- Kliknij prawym przyciskiem myszy nagłówek pierwszej kolumny i wybierz Rozwiń inne kolumny (Unpivot inne kolumny). Wszystkie niewybrane kolumny są przeliczane na dwie – nazwisko pracownika i wartość jego wskaźnika.
- Filtrowanie kolumny z sumami, które trafiły do kolumny Atrybut.
- Budujemy tabelę przestawną zgodnie z otrzymaną tabelą płaską (znormalizowaną) za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…).
Teraz możesz skorzystać z możliwości filtrowania kolumn dostępnych w tabelach przestawnych – zwykłe znaczniki wyboru przed nazwami i elementami Filtry podpisu (Filtry etykiet) or Filtry według wartości (Filtry wartości):
I oczywiście przy zmianie danych będziesz musiał zaktualizować nasze zapytanie i podsumowanie skrótem klawiszowym Ctrl+inny+F5 lub zespół Dane – Odśwież wszystko (Dane — Odśwież wszystko).
Metoda 3. Makro w VBA
Wszystkie poprzednie metody, jak łatwo zauważyć, nie są dokładnym filtrowaniem – nie ukrywamy kolumn z oryginalnej listy, ale tworzymy nową tabelę z danym zestawem kolumn z oryginalnej. Jeśli wymagane jest filtrowanie (ukrywanie) kolumn w danych źródłowych, potrzebne jest zasadniczo inne podejście, a mianowicie makro.
Załóżmy, że chcemy filtrować w locie kolumny tam, gdzie nazwa menedżera w nagłówku tabeli odpowiada masce określonej w żółtej komórce A4, na przykład zaczyna się na literę „A” (czyli uzyskaj „Anna” i „Artur " w rezultacie).
Podobnie jak w pierwszej metodzie, najpierw implementujemy pomocniczy wiersz zakresu, gdzie w każdej komórce nasze kryterium będzie sprawdzane formułą, a wartości logiczne TRUE lub FALSE zostaną wyświetlone odpowiednio dla widocznych i ukrytych kolumn:
Następnie dodajmy proste makro. Kliknij prawym przyciskiem myszy kartę arkusza i wybierz polecenie Źródło (Kod źródłowy). Skopiuj i wklej następujący kod VBA do okna, które się otworzy:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Następna komórka End If End Sub Jego logika jest następująca:
- Ogólnie jest to procedura obsługi zdarzeń Arkusz_Zmiana, tzn. to makro zostanie automatycznie uruchomione po każdej zmianie dowolnej komórki w bieżącym arkuszu.
- Odwołanie do zmienionej komórki zawsze będzie znajdować się w zmiennej cel.
- Najpierw sprawdzamy, czy użytkownik zmienił dokładnie komórkę z kryterium (A4) – robi to operator if.
- Wtedy zaczyna się cykl Dla każdego… iterować po szarych komórkach (D2:O2) z wartościami wskaźnika TRUE / FALSE dla każdej kolumny.
- Jeżeli wartość następnej szarej komórki to TRUE (prawda), to kolumna nie jest ukryta, w przeciwnym razie ją ukrywamy (właściwość Ukryty).
- Dynamiczne funkcje tablicowe z Office 365: FILTER, SORT i UNIC
- Tabela przestawna z wielowierszowym nagłówkiem za pomocą dodatku Power Query
- Czym są makra, jak je tworzyć i używać