W tym artykule opowiem Ci, jak filtrować zakres w sposób dynamiczny, po wyborze jednego kryterium filtru z listy rozwijanej. Ponieważ ma się to stać dynamicznie, bez makr, to użyjemy do tego funkcji. Konkretnie funkcji tablicowych dostępnych w Excelu 365. Mam tutaj na myśli przede wszystkim funkcję filtrującą FILTRUJ, ale wspomożemy się również funkcjami UNIKATOWE i SORTUJ. One z kolei posłużą nam do stworzenia dynamicznego źródła listy rozwijanej. 

Wybór z listy rozwijanej

Wszystkie artykuły serii Dynamiczne filtrowanie funkcją FILTRUJ:

1. Dynamiczne filtrowanie funkcją FILTRUJ: jedno kryterium filtru
2. Dynamiczne filtrowanie funkcją FILTRUJ: wiele kryteriów filtru
3. Wyświetlanie tylko wybranych kolumn filtrowania – WYBIERZ.KOLUMNY

Dane wejściowe – opis 

W arkuszu Dane mamy do czynienia z następującym zakresem, który będziemy chcieli filtrować: 

Formatka

Zakres ten to obiekt tabela o nazwie tbDane. Utworzone formuły będą się więc odwoływały do takiej nazwy tej tabeli. 

Chcemy ten zakres filtrować po handlowcu, wybierając go z listy rozwijanej. Całe filtrowanie ma się odbywać w arkuszu WYNIK.  

Aby to zrobić, potrzebujemy wykonać kilka czynności: 

1. Stworzenie źródła do listy rozwijanej (arkusz Dane) 
2. Stworzenie listy rozwijanej (arkusz WYNIK) 
3. Napisanie formuły filtrującej zakres, w oparciu o listę rozwijaną (arkusz WYNIK) 

Do dzieła! 

1. Tworzenie dynamicznego źródła listy rozwijanej 

Źródło listy rozwijanej z handlowcami utworzymy, wyodrębniając unikatowe wartości z kolumn Handlowiec tabeli. 

W tym celu, w arkuszu Dane w komórce L3, napiszemy następującą formułę: 

=UNIKATOWE(tbDane[Handlowiec]) 

(ang: UNIQUE)

Otrzymamy taki wynik: 

Wynik funkcji UNIKATOWE

Jest on całkowicie poprawny, na utworzonej liście faktycznie są unikalne wpisy z kolumny Handlowiec. Ten wynik jednak nie do końca mi się podoba – zauważ, że handlowcy na tej liście nie są posortowani, przez co ciężko będzie pracować z taką listą. Aby temu zaradzić, wrzucimy więc formułę, którą dotychczas napisaliśmy, w funkcję SORTUJ. Interesuje nas domyślne sortowanie rosnące, zatem nowa formuła wygląda tak: 

=SORTUJ(UNIKATOWE(tbDane[Handlowiec])) 

(ang: SORT)

A jej wynik– rozlana formuła tablicowa – tak: 

Wynik funkcji SORTUJ

Jest ślicznie, zatem mamy źródło listy rozwijanej. Stwórzmy ją więc. 

2. Tworzenie listy rozwijanej opartej o funkcje tablicowe 

Listę rozwijaną z handlowcami stworzymy w arkuszu WYNIK w komórce B3: 

Miejsce na listę rozwijaną

Aby to zrobić, ustaw się w komórce B3, a następnie z menu Dane wybierz Poprawność danych. W okienku, które się pojawi, wybierz dozwolone: Lista, a jako źródło wpisz odwołanie do komórki z formułą UNIKATOWE, o tak: 

=Dane!$L$3# 

Znak # na końcu oznacza, że źródłem listy ma być cały wynik formuły tablicowej w tej komórce. 

Wynik jest następujący: 

Lista rozwijana oparta o funkcję tablicową

Możesz od razu wybrać z tej listy przykładowego handlowca po to, aby formuła, którą za chwilę napiszemy, miała dane. 

3. Filtrowanie zakresu funkcją FILTRUJ 

Mamy listę, to możemy filtrować tabelę. Użyjemy do tego funkcji FILTRUJ (ang: FILTER), którą wpiszemy w komórce A6. Jest ona następująca: 

=FILTRUJ(tbDane;tbDane[Handlowiec]=B3) 

A oto wynik (dla handlowca Bogusława Jankowska): 

Wynik funkcji FILTRUJ bez nagłówków i formatowania

Zauważ, że powyższy zakres nie ma nagłówków i nie jest sformatowany (np. daty są zwykłymi liczbami). 

Odnośnie formatowania – należy je po prostu nadać w wyfiltrowanym zakresie. Najlepiej zrobić to z pewnym zapasem, aby po zmianie kryteriów, cały wynik na pewno był sformatowany. 

W przypadku nagłówków – należy je również uzupełnić. Możemy to zrobić albo ręcznie, albo użyć prostych formuł odwołujących się do nagłówków tabeli źródłowej. Decyzja należy do Ciebie. Ja użyję prostej formuły (komórka A6), którą później przeciągnę w prawo: 

=Dane!A3 

Po sformatowaniu i dodaniu nagłówków mój filtrowany zakres wygląda następująco: 

Wynik po sformatowaniu i dodaniu nagłówków

Wszystko wygląda SUPER i nawet można byłoby to uznać za koniec pracy, gdyby nie jeden drobny szczegół… 

4. Obsługa błędu #OBL! (braku wyboru z listy rozwijanej) 

Zauważ bowiem, co się stanie, gdy z listy rozwijanej nie wybierzemy żadnego handlowca:

Błąd OBL!

Formuła zwróciła błąd #OBL!, ponieważ nie daliśmy jej kryteriów filtru. 

Warto się zabezpieczyć na taką sytuację, korzystając np. z klasycznej funkcji JEŻELI, która sprawdzi, czy żółta komórka jest uzupełniona. Jeśli nie będzie – zwróci całą tabelę, a jeśli będzie – zwróci odpowiednio przefiltrowaną tabelę. 

Formuła jest taka: 

=JEŻELI(B3="";tbDane;FILTRUJ(tbDane;tbDane[Handlowiec]=B3)) 

A wynik w przypadku braku wyboru – taki: 

Wynik w przypadku braku wyboru na liście rozwijanej

Teraz to już ma większy sens 😉. 

Oczywiście powyższą sytuację można jeszcze dalej rozwijać. Zwróć uwagę, że w filtrowanym zakresie znajdują się faktury. Jeśli interesowałyby nas tylko faktury nieopłacone (kolumna Opłacone) i to takie, których termin płatności minął (kolumna Termin płatności), to warto byłoby widzieć w tym filtrze tylko takie. To jednak wymaga ustawienia trzech kryteriów filtru, co pokażę w kolejnym artykule 😊. 

Plik do pobrania:

https://malinowyexcel.pl/wp-content/uploads/2024/03/MalinowyExcel-20240326-FILTRUJ-jedno-kryt-DW.xlsx

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: