W dzisiejszym poście nauczymy się jak znaleźć n‑tą wartość spełniającą warunki (kryteria). Zadanie to wykonamy na podstawie przykładowych danych z rysunku nr 1.

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

W wyzwaniu nr 17 pokazywaliśmy jak rozwiązać taki problem klasycznym Excelem i wtedy w ogóle nie wykorzystywaliśmy funkcji związanych z wyszukiwaniem oprócz funkcji INDEKS. Funkcje jakie zostały tam wykorzystane zostały przedstawione na rys. nr 2.

Rys. nr 2 – wyszukiwanie wartości spełniającej 2 warunki w klasycznym Excelu
Rys. nr 2 – wyszukiwanie wartości spełniającej 2 warunki w klasycznym Excelu

Próbowałem rozwiązać taki problem za pomocą rozwiązania zaproponowanego na stronie www.chandoo.org, które opierało się na wykorzystaniu funkcji X.WYSZUKAJ, ale to rozwiązanie jest zbyt skomplikowane dla przeciętnego użytkownika Excela. Rozwiązanie zostało przedstawione na rys. nr 3.

Rys. nr 3 – rozwiązanie zadania za pomocą sposobu ze strony www.chandoo.org
Rys. nr 3 – rozwiązanie zadania za pomocą sposobu ze strony www.chandoo.org

Nasze zadanie wykonamy w Excelu tablicowym, wykorzystamy tutaj funkcje FILTRUJ i INDEKS. Użyjemy najpierw funkcji FILTRUJ, która w chwili nagrywania filmu (wrzesień 2019) była dostępna tylko w programie niejawnych testów Offica (Insider). Pierwszym argumentem funkcji FILTRUJ jest tablica, czyli cała tabela z danymi od komórki A3. Tabela zawiera dużo danych więc aby ją odpowiednio i szybko zaznaczyć, musimy ustawić aktywną komórkę w komórce A3 i za pomocą skrótu klawiszowego Ctrl+Shift+strzałka w bok zaznaczyć cały wiersz, a następnie za pomocą skrótu Ctrl+Shift+strzałka w dół zaznaczyć obszar do ostatniego wiersza. W celu powrotu do komórki, gdzie wpisujemy formułę musimy użyć skrótu klawiszowego Ctrl+Backspace. Drugi argument funkcji to uwzględnienie, czyli testy logiczne sprawdzające (inaczej filtry). W naszym teście chcemy sprawdzić czy w kolumnie Województwo znajduje się to wybrane przez nas województwo (np. lódzkie). Podsumowując zaznaczamy cała kolumnę Województwo (bez nagłówka) i przyrównujemy ją do wartości, którą chcemy sprawdzić, czyli B3:B300=F5. Zapis całej formuły powinien wyglądać następująco:

=FILTRUJ(A3:D300;B3:B300=F5)

Kiedy podejrzymy sobie wynik formuły w trybie edycji komórki za pomocą klawisza F9, otrzymamy sporą tablicę wartości logicznych PRAWDA i FAŁSZ, której fragment został pokazany na rys. nr 4.

Rys. nr 4 – podgląd wyników funkcji FILTRUJ
Rys. nr 4 – podgląd wyników funkcji FILTRUJ

Z podglądu wyników formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. T wynikach wartości logiczne PRAWDA otrzymamy tylko dla sytuacji, kiedy w kolumnie Województwo pojawi się województwo łódzkie, natomiast wartości FAŁSZ będą wszędzie tam gdzie będzie każde inne województwo. Funkcja FILTRUJ zwróci nam tablicę danych dla każdego wystąpienia województwa łódzkiego przedstawioną na rys. nr 5.

Rys. nr 5 – tablica zwrócona przez funkcje FILTRUJ
Rys. nr 5 – tablica zwrócona przez funkcje FILTRUJ

W związku z tym, że nie mamy nałożonego odpowiedniego formatowania wyniki w pierwszej kolumnie nie przypominają dat. Wystarczy zaznaczyć kolumnę z datami i na karcie Narzędzia główne w kategorii Liczba wybrać z listy rozwijanej typ danych Data krótka jak na rys. nr 6.

Rys. nr 6 – szybkie formatowanie danych – typ Data krótka
Rys. nr 6 – szybkie formatowanie danych – typ Data krótka

Otrzymaliśmy dane spełniające jeden warunek (wybrane województwo), a nam zależy na otrzymaniu danych spełniających 2 warunki. Teraz w formule funkcji wystarczy dołożyć drugi filtr (drugie kryterium) dotyczący sprzedawcy. Ze względu na to, że operacje porównania są wykonywane w Excelu na końcu musimy zapisać je w nawiasach. Aby otrzymać drugie kryterium (oba kryteria mają być spełnione jednocześnie) musimy te dwa kryteria przemnożyć przez siebie. W drugim kryterium musimy porównać dane z kolumny Sprzedawca do określonego (wybranego przez nas) sprzedawcy, czyli zapis testu logicznego powinien wyglądać następująco: C3:C300=G5. Zapis całej formuły funkcji FILTRUJ powinien wyglądać następująco:

=FILTRUJ(A3:D300;(B3:B300=F5)*(C3:C300=G5))

Dzięki takiemu zapisowi otrzymaliśmy filtrowanie danych po 2 kryteriach przedstawione na rys. nr 7.

Rys. nr 7 – dane spełniające dwa kryteria
Rys. nr 7 – dane spełniające dwa kryteria

Otrzymaliśmy dane spełniające dwa kryteria, czyli każde wystąpienie województwa łódzkiego i sprzedawcy Kinga. Nasz nałożony filtr jest dynamiczny, mianowicie możemy zmienić sobie nazwę zarówno województwa jak i sprzedawcy rozwijając listy rozwijane w odpowiednich komórkach (rys. nr 8).

Rys. nr 8 – dynamiczny charakter nałożonego filtru
Rys. nr 8 – dynamiczny charakter nałożonego filtru

Podsumowując funkcja FILTRUJ zwraca nam tablicę danych. Naszym celem jest znalezienie n‑tej wartości spełniającej podane warunki. Załóżmy że chcemy wyciągnąć z danych 5‑ty wiersz (n=5). Możemy to zrobić za pomocą INDEKS, wystarczy, że do funkcji INDEKS włożymy tablicę którą otrzymaliśmy z funkcji FILTRUJ. Pierwszym argumentem funkcji INDEKS jest tablica, czyli tablica otrzymana za pomocą funkcji FILTRUJ. Drugi argument funkcji to nr_wiersza, czyli wartość parametru n (w naszym przykładzie wartość 5 zapisana w komórce F2). Zapis formuły powinien wyglądać następująco:

=INDEKS(FILTRUJ(A3:D300;(B3:B300=F5)*(C3:C300=G5));F2)

Po zatwierdzeniu formuły otrzymamy jeden wiersz (5‑ty wiersz) przedstawiony na rys. nr 9.

Rys. nr 9 – 5-ty wiersz zwrócony przez funkcję INDEKS
Rys. nr 9 – 5‑ty wiersz zwrócony przez funkcję INDEKS

Ze względu na to, że nas interesowała w tym przypadku tylko kwota a nie cały wiersz, możemy ograniczyć zakres tablicy w formule funkcji FILTRUJ. Wystarczy zmienić zakres danych A3:D300 na D3:D300. Zapis całej formuły powinien wyglądać następująco:

=INDEKS(FILTRUJ(D3:D300;(B3:B300=F5)*(C3:C300=G5));F2)

Po zatwierdzeniu formuły otrzymamy jeden konkretny wynik przedstawiony na rys. nr 10.

Rys. nr 10 – wynik funkcji INDEKS po zmianie zakresu w funkcji FILTRUJ
Rys. nr 10 – wynik funkcji INDEKS po zmianie zakresu w funkcji FILTRUJ

Jak łatwo zauważyć, wynik ma dziwną postać. Wystarczy zmienić formatowanie na walutowe, aby otrzymać prawidłowo pokazaną daną za pomocą skrótu klawiszowego Ctrl+Shift+4 (rys. nr 11).

Rys. nr 11 – zmiana formatowania na walutowe za pomocą skrótu klawiszowego
Rys. nr 11 – zmiana formatowania na walutowe za pomocą skrótu klawiszowego

Podsumowując w tym przykładzie nie korzystaliśmy z funkcji X.WYSZUKAJ jak w klasycznym Excelu, aby odnaleźć n‑tą wartość, dzięki temu nasza formuła jest prosta (o wiele mniej skomplikowana niż te przedstawione na rys. nr 2 i 3). Czasami praca w Excelu polega na obliczeniu jakichś wartości w najłatwiejszy sposób, nie zawsze trzeba na siłę używać skomplikowanych funkcji, które na pierwszy rzut oka wydają się oczywiste do rozwiązania danego zadania.


Książka Mistrz Excela + promo na 35 urodziny

Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.

Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".

Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.

Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY

Książka Mistrz Excela reklama