Wyobraź sobie sytuację: dostajesz od przełożonego proste pytanie – „Jaka jest cena tego produktu?”. Otwierasz arkusz, patrzysz na tabelę i… szybko okazuje się, że odpowiedź wcale nie jest taka oczywista.
Ten sam produkt występuje w kilku wierszach. Różne regiony, różne kanały sprzedaży, różne ceny. Zaczynasz przewijać dane, filtrujesz kolumny, próbujesz znaleźć właściwy rekord „na oko”. Niby się da, ale zajmuje to czas i łatwo o pomyłkę.
I właśnie w tym momencie pojawia się realny problem, który w Excelu występuje bardzo często, czyli jak znaleźć jedną konkretną wartość, gdy zależy ona od kilku warunków jednocześnie?
W Excelu często pracujemy na tabelach, w których jedna wartość nie wystarcza do znalezienia poprawnego wyniku. Dobrym przykładem jest cennik produktów. Ten sam produkt może mieć inną cenę w zależności od regionu i kanału sprzedaży. W takiej sytuacji zwykłe wyszukiwanie po jednej kolumnie przestaje być wystarczające.
W tym artykule pokażę trzy praktyczne sposoby rozwiązania tego problemu. Każdy z nich prowadzi do tego samego celu: odnalezienia właściwej ceny katalogowej na podstawie trzech warunków jednocześnie:
Zanim przejdziemy do konkretnego przykładu, warto zrozumieć ogólny problem.
W praktyce bardzo rzadko zdarza się sytuacja, w której wystarczy wyszukiwanie po jednej kolumnie. Zazwyczaj poprawny wynik zależy od kombinacji kilku warunków jednocześnie. Może to być produkt i region, klient i data, albo jak w tym przypadku kilka różnych cech opisujących jedną pozycję.
Excel standardowo nie oferuje jednej prostej funkcji do wyszukiwania po wielu kryteriach, dlatego trzeba wykorzystać bardziej zaawansowane podejścia. Dobra wiadomość jest taka, że istnieje kilka sprawdzonych metod, które pozwalają rozwiązać ten problem w sposób elastyczny i skalowalny.
Przykładowe dane wyglądają następująco:
Z kolei wartości, po których chcemy wyszukiwać, czyli konkretna kombinacja produktu, regionu i kanału.
W zależności od wybranego podejścia możemy użyć jednej z trzech formuł.
Załóżmy, że chcemy znaleźć cenę dla zestawu:
Produkt: Telefon Max
Region: Wschód
Kanał: Retail
W tabeli może występować wiele wierszy z produktem „Telefon Max”, więc samo wyszukanie po nazwie produktu nic nie daje. Musimy znaleźć rekord, który jednocześnie spełnia wszystkie trzy warunki.
To właśnie nazywamy wyszukiwaniem wielokryterialnym.
=X.WYSZUKAJ(1;(A2:A21=G2)*(B2:B21=H2)*(C2:C21=I2);D2:D21;””)
To rozwiązanie jest bardzo eleganckie i nowoczesne. W środku formuły tworzona jest tablica wartości logicznych:
Każde z tych porównań zwraca serię wartości PRAWDA/FAŁSZ. Następnie Excel zamienia je w praktyce na 1 i 0, a mnożenie powoduje, że:
Funkcja X.WYSZUKAJ szuka więc wartości 1 i zwraca odpowiadającą jej cenę z kolumny D.
To jedna z najlepszych metod w nowoczesnym Excelu, ponieważ:
To rozwiązanie wymaga Excela z obsługą funkcji X.WYSZUKAJ, która została wprowadzona w 2019 roku i jest dostępna w Excelu dla Microsoft 365, Excel 2021 (oraz nowszych wersjach, np. Excel 2024). W starszych wersjach, takich jak Excel 2016 czy Excel 2019, funkcja ta nie jest dostępna i formuła nie zadziała.
=X.WYSZUKAJ(G5&”|”&H5&”|”&I5;A2:A21&”|”&B2:B21&”|”&C2:C21;D2:D21;” „)
Tutaj podejście jest inne. Zamiast porównywać trzy warunki osobno, łączymy je w jeden tekstowy identyfikator.
Podobny identyfikator tworzony jest dla każdego wiersza w tabeli:
Dzięki temu X.WYSZUKAJ nie musi analizować trzech osobnych warunków. Szuka jednego gotowego klucza.
Znak “|” pełni rolę separatora. Jest bardzo ważny, ponieważ zmniejsza ryzyko przypadkowego zlepienia tekstów w taki sposób, który mógłby dać błędne dopasowanie.
Przykład:
=JEŻELI.BŁĄD(INDEKS(D2:D21;PODAJ.POZYCJĘ(1;(A2:A21=G8)*(B2:B21=H8)*(C2:C21=I8);0));””)
To klasyczne rozwiązanie, bardzo cenione przez bardziej zaawansowanych użytkowników Excela.
Cały mechanizm działa w dwóch krokach:
Mimo że nowoczesny Excel oferuje X.WYSZUKAJ, zestaw INDEKS + PODAJ.POZYCJĘ nadal pozostaje bardzo praktyczny, ponieważ:
W niektórych starszych wersjach Excela taka formuła mogła wymagać zatwierdzenia jako formuła tablicowa. W nowszych wersjach zwykle działa już standardowo.
W praktyce wybór zależy od wersji Excela i stylu pracy.
Wybierz X.WYSZUKAJ z logiką warunków, jeśli:
Wybierz X.WYSZUKAJ z łączeniem kluczy, jeśli:
Wybierz INDEKS + PODAJ.POZYCJĘ, jeśli:
Jeżeli w jednej komórce występuje dodatkowa spacja, Excel potraktuje taką wartość jako inną. Na pierwszy rzut oka dane mogą wyglądać identycznie, ale formuła nie zwróci wyniku.
Wszystkie zakresy w formule muszą mieć tę samą długość. Jeśli jedna kolumna obejmuje 20 wierszy, a druga 19, formuła może zwrócić błąd albo nieprawidłowy wynik.
Jeżeli dopasowanie nie istnieje, warto zwrócić pusty wynik lub czytelny komunikat zamiast błędu. Właśnie dlatego JEŻELI.BŁĄD albo ostatni argument w X.WYSZUKAJ są tak przydatne.
Wyszukiwanie po wielu kryteriach to jedna z najważniejszych umiejętności w pracy z Excelem. W praktyce bardzo rzadko szukamy informacji wyłącznie po jednej wartości. Znacznie częściej poprawny wynik zależy od kombinacji kilku pól, tak jak w przykładzie z produktem, regionem i kanałem sprzedaży.
Najważniejszy wniosek jest prosty: ten sam problem można rozwiązać na kilka sposobów, a wybór najlepszego zależy od narzędzi, danych i poziomu zaawansowania użytkownika.
Jeśli korzystasz z nowoczesnego Excela, najwygodniejszy będzie zwykle X.WYSZUKAJ. Jeśli zależy Ci na kompatybilności i większej kontroli, bardzo mocnym wyborem pozostaje zestaw INDEKS + PODAJ.POZYCJĘ.
Warto znać wszystkie trzy podejścia, bo dzięki temu łatwiej dobrać rozwiązanie do konkretnego arkusza, a nie odwrotnie.omyłek.czy ponosić koszty w postaci straty czasu i pieniędzy na „analogowych” rozwiązaniach starego typu.
