Minuty czytania: 4

Automatyczne filtrowanie bez VBA

Jednymi z częstszych operacji jakie wykonujesz w Excelu są: filtrowanie i wyszukiwanie tekstu i liczb. Jeżeli masz jakąś bazę danych (klienci, produkty, pracownicy), to możesz zautomatyzować proces filtrowania informacji w tej bazy.

Takie automatyczne filtrowanie danych w Excelu jest możliwe bez użycia makr (VBA). Wystarczy, że we właściwy sposób użyjesz kilku funkcji. Jacek, na filmie wyjaśnia jak to zrobić.

Poniżej znajduje się wersja tekstowa filmu. Więcej poradników w wersji wideo, znajdziesz na naszym kanale na You Tube.


Automatyczne filtrowanie danych w formularzu

Cześć. Jacek z Excelness.

W dzisiejszym filmie pokażę Ci formularz, dzięki któremu będziesz mógł automatycznie filtrować dane. Na czym to polega?

Zobacz masz tutaj bazę pewnych klientów. Chciałbyś wyszukiwać ich po nazwisku, a dokładnie po fragmencie nazwiska.

Automatyczne filtrowanie w Excelu - baza danych

Tutaj mają Ci się wyświetlać tylko te rekordy, które spełniają dane kryterium.

Automatyczne filtrowanie w Excelu - wyszukiwarka

Przykładowo chciałby znaleźć osoby, które w swoim nazwisku mają „ecka”. Wpisuję i wyświetliły się te nazwiska. Teraz chciałbyś wyszukać pełne nazwisko. I też oczywiście ten formularz je znajdzie.

Kolumny pomocnicze

Przejdźmy zatem do rozwiązania takiego zadania. Mam tutaj przygotowany czysty arkusz, w którym wykonam kilka czynności. Pierwszą rzeczą jaką muszę zrobić to stworzenie kolumny pomocniczej, która będzie mi wyszukiwać ten fragment tekstu w danym nazwisku.

Wykorzystam do tego funkcję SZUKAJ.TEKST. Szukany tekst to będzie to, co jest wpisane w komórce wyszukiwania nazwiska. Ma wyszukiwać mi w kolumnie z nazwiskami. Będę kopiował tę formułę w dół zatem muszę zablokować komórkę Q2. Kopiuje to niżej. Zobacz, że już to działa.

=SZUKAJ.TEKST(Q$2;C5)

Fragment tekstu „bob” został znaleziony we właściwych komórkach, a w pozostałych wskazał błąd. Dzięki temu jesteśmy w stanie wskazać, w których wierszach bazy znajduje się wiersz, który ma być zwrócony jako wynik wyszukiwania.

Stworzę kolejną kolumnę pomocniczą. Teraz na podstawie tej informacji, będę chciał zwrócić numer wiersza w którym znajduje się szukany tekst. Wykorzystam do tego funkcje ILE.WIERSZY i CZY.LICZBA. Czyli będę sprawdzał czy ten element jest liczbą. Jeżeli jest – ma mi zliczyć, w którym wierszu znajduje się ta komórka. Aby to w pełni działało, muszę wykorzystać jeszcze funkcję JEŻELI. Zatem zaczynam.

Sprawdzam czy ten element jest liczbą i jeżeli jest, to ma mi zwrócić numer tego wiersza w tej tabelce. Natomiast jeżeli nie – ma mi zwrócić po prostu pusty ciąg znaków. Będę chciał tę formułę kopiować w dół. W związku z powyższym będę musiał zastosować mieszany tryb adresowania.

Funkcją, która potrafi zwrócić numer wiersza w danym obszarze, to funkcja ILE.WIERSZY. Wykorzystam do tego na przykład kolumnę region. Jeżeli tak zrobiłem to zablokuję pierwszą część adresu. Ta pierwsza część adresu będzie stała, a druga część adresu będzie się zmieniać.

=JEŻELI(CZY.LICZBA(I5);ILE.WIERSZY(I$5:I5);"")

Automatyczne filtrowanie jedną formułą (1 – INDEKS)

Tak przygotowane dane mogę wykorzystać do mojego docelowego formularza, który będzie mi pokazywał wiersze które spełniają kryteria. Mogę to zrobić jedną formułą, która nie będzie może najłatwiejsza ale będzie bardzo uniwersalna. Będę chciał, aby formuła pobierała kolejne wiersze z bazy, jeśli w danym wierszu funkcja ILE.WIERSZY podała jakąś wartość liczbową.

Żeby to osiągnąć muszę zrozumieć kilka rzeczy. Pierwszą z nich jest zasada działania funkcji INDEKS. Funkcja INDEKS potrafi zwracać dane z jakiegoś obszaru na podstawie numeru wiersza numeru i kolumny.

Zobacz, tablicą będzie cała tabela z danymi. Będę ją kopiować i w prawo i w dół w związku z powyższym muszę zablokować jej wiersze i kolumny. Teraz, będę chciał zwrócić trzeci wiersz. Ale jak wskazać że to jest 3? W naszym przypadku, czasami to będzie także inny numer np. 7, 8.

Automatyczne filtrowanie jedną formułą (2 – MIN.K, WIERSZ, NR. KOLUMNY)

Mogę wykorzystać tutaj funkcję MIN.K, która zwraca mi kolejny najmniejszy element. W pierwszym wierszu będę chciał, aby funkcja zwracała pierwszy najmniejszy element. Będę kopiował w prawo i w dół, więc tworzę adresowane bezwzględne. Muszę zrobić tak, aby Excel zwrócił mi pierwszy najmniejszy element ale tak jak powiedziałem, chciałbym to zrobić jedną formułą. Co zrobić, żeby po kopiowania pojawiały się tutaj kolejne liczby: 1, 2, 3, 4, 5, 6?

INDEKS($A$5:$H$68;MIN.K($J$5:$J$68;WIERSZ(A1));NR.KOLUMNY(A1))

Mogę zastosować taki trik, który wykorzystuje funkcję WIERSZ. Funkcja ta, zwraca numer wiersza komórki, w której ją umieścisz. I teraz jeśli wskażę komórkę A1, to po skopiowanie w dół funkcja wskaże mi kolejno – komórkę A2 – czyli zwróci mi liczbę 2. Dzięki temu, uzyskam taki efekt, że kopiując formułę w dół uzyskam kolejne liczby …,3, 4, 5, 6 itd.

Podobnie muszę zrobić dla kolumn, czyli po kopiowaniu w prawo Excel ma zwracać pierwszą, drugą, trzecią, …kolejną kolumnę. Do tego wykorzystam funkcję NR.KOLUMNY. I tak samo, jak poprzednio odwołuje się do komórki A1. Po kopiowaniu w prawo będzie mi przenosić się na B1, C1, D1…, a funkcja będzie zwracać 1, 2, 3, 4, 5.

W ten sposób uzyskałem funkcję, która zwraca mi właściwy element z tabeli danych.

INDEKS($A$5:$H$68;MIN.K($J$5:$J$68;WIERSZ(A1));NR.KOLUMNY(A1))

Automatyczne filtrowanie jedną formułą (3 – JEŻELI.BŁĄD)

Jeśli skopiuję formułę w dół i w prawo, może się zdarzyć, że dla kolejnych wierszy formuła nie znajdzie już wyszukiwanej wartości. W takim przypadku formuła zwróci błąd.

Mogę sobie z tym poradzić za pomocą funkcji JEŻELI.BŁĄD. Zrobimy tak, że gdy funkcja natrafi na błąd, zwróci pusty ciąg znaków. Finalnie cała formuła wygląda tak:

=JEŻELI.BŁĄD(INDEKS($A$5:$H$68;MIN.K($J$5:$J$68;WIERSZ(A1));NR.KOLUMNY(A1));"")

Podsumowanie

W ten sposób uzyskałem uniwersalną formułę, dzięki której będę mógł moje zadanie zrobić za jednym zamachem. Skopiuje teraz formułę do kolejnych komórek i widzimy, że funkcja w niższych wierszach miejscach zwraca puste znaki.

Na koniec, aby nasz formularz ładnie wyglądał zmienię kolor czcionki dla pomocniczych elementów na biały. Zadanie mamy skończone.

Jeżeli podobał Ci się ten film udostępnił go innym i skomentuj. A może masz jakieś propozycje na kolejne filmy i artykuły? Napisz w komentarzu.


Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.


0 Comments

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *