Jak działa X.WYSZUKAJ – najnowsza funkcja Excela!

Z lekcji dowiesz się:

• O różnicach między WYSZUKAJ.PIONOWO, a X.WYSZUKAJ?

• W jakich przypadkach lepiej sprawdzi się X.WYSZUKAJ?

• Jak, dzięki X.WYSZUKAJ - znaleźć ostatnią wartość np. produkt?

Microsoft rozwija swój produkt zgodnie z potrzebami użytkowników. Taką potrzebą było usprawnienie funkcji WYSZUKAJ.PIONOWO, dlatego została wprowadzona nowa funkcja o nazwie X.WYSZUKAJ.

Działanie funkcji X.WYSZUKAJ omówimy na podstawie przykładowych danych z rysunku nr 1 zawartych w arkuszu Standard.

Rys. 1 – Przykładowe dane dla funkcji X.WYSZUKAJ z dopasowaniem dokładnym

Działanie zaczniemy od standardowego wyszukiwania na zasadzie dopasowania dokładnego. W arkuszu Standard w kolumnie Cena mamy przygotowaną formułę funkcji WYSZUKAJ.PIONOWO. Analogicznie mamy przygotowane formuły w innych arkuszach Excela, które korzystają z „klasycznych” funkcji Excela.

W kolumnie CenaX stworzymy formułę funkcji X.WYSZUKAJ, żeby otrzymać ten sam wynik, który został uzyskany przy użyciu standardowej funkcji WYSZUKAJ.PIONOWO. Pierwszym zadaniem będzie odnalezienie ceny produktu – podane są nazwy produktów i na ich podstawie należy znaleźć ich ceny (rys. 2).

Rys. 2 – tabela przedstawiająca listę produktów oraz ich cenę znalezioną przy użyciu standardowej funkcji WYSZUKAJ.PIONOWO

Pierwszym argumentem funkcji X.WYSZUKAJ jest szukana_wartość, czyli należy wskazać funkcji czego szukamy - mianowicie nazwy konkretnego produktu z kolumny Produkt (np. komórka A2).

Drugi argument funkcji to szukana_tablica, czyli zakres, w którym będziemy szukać nazwy danego produktu. Ten argument można nazwać wektorem, ponieważ obejmuje on pojedynczą kolumnę lub pojedynczy wiersz w tabeli. W podanym przykładzie jest to zakres kolumny G o nazwie Produkt, bez nagłówka (rys. 3). Zaznaczony zakres G2:G12 należy zablokować bezwzględnie za pomocą klawisza F4 po to, żeby nie przesuwał się on, przy przeciąganiu formuły na wiersze poniżej.

Rys. 3 – Zakres pojedynczej kolumny jako drugi argument funkcji (szukana_tablica)

Trzeci argument funkcji to zwracana_wartość, czyli tablica, z której Excel ma wyciągnąć wartość ceny dla danego produktu. W większości sytuacji jest to pojedyncza kolumna lub pojedynczy wiersz. W tym przykładzie poszukiwana jest cena dla produktu Mysz, czyli zakresem trzeciego argumentu będzie kolumna I o nazwie Cena (zakres I2:I12). Zakres ten blokujemy bezwzględnie za pomocą klawisza F4 (rys. 4).

Rys. 4 – Zakres pojedynczej kolumny jako trzeci argument funkcji (zwracana_wartość)

Są to już wszystkie argumenty, jakich potrzeba do zdefiniowania funkcji X.WYSZUKAJ przy dopasowaniu dokładnym, ponieważ jest to domyślny sposób wyszukiwania dla tej funkcji.

Zapis funkcji powinien wyglądać następująco:

=X.WYSZUKAJ(A2;$G$2:$G$12;$I$2:$I$12)

Tak przygotowaną formułę zatwierdzamy i kopiujemy w dół (na wiersze poniżej wpisanej formuły). Otrzymamy wyniki przedstawione w kolumnie CenaX na rys. 5.

Rys. 5 – Wyniki dla funkcji X.WYSZUKAJ

Podsumowując obie funkcje działają prawidłowo, otrzymaliśmy takie same wyniki, ale w działaniu funkcji WYSZUKAJ.PIONOWO oraz X.WYSZUKAJ jest jedna podstawowa różnica. Mianowicie dane często ulegają zmianie, np. może zostać dodana dodatkowa kolumna w środku, co spowoduje poszerzenie zakresu funkcji WYSZUKAJ.PIONOWO i w następstwie nieprawidłowe wyniki. Jest to skutek tego, że w funkcji WYSZUKAJ.PIONOWO w argumentach podawany jest indeks kolumny z tabeli z której pobierane są wartości. Po dołożeniu dodatkowej kolumny w tej tabeli, dane zostają pobrane ze złej kolumny (kolumna o indeksie nr 3 stanie się kolumną o indeksie nr 4, co widać na rys. 6).

Rys. 6 – Pobieranie danych ze złej kolumny, wynikające z dołożenia nowej kolumny

Sposobem na poprawne działanie funkcji WYSZUKAJ.PIONOWO jest zabezpieczenie się przed zmianami w danych, za pomocą dodatkowych funkcji np. PODAJ.POZYCJĘ. Przy wykorzystaniu funkcji X.WYSZUKAJ jest to zabezpieczone, ponieważ zaznaczone są konkretne zakresy, które przy np. dołożeniu dodatkowej kolumny ulegają przesunięciu.

Przy zmianie danych wejściowych może dojść do zmiany kolejności kolumn. Funkcja WYSZUKAJ.PIONOWO znowu nie zadziała więc prawidłowo i zwróci błąd (rys. 7).

Rys. 7 – Błąd funkcji WYSZUKAJ.PIONOWO wynikający ze zmiany kolejności kolumn w danych wejściowych

Natomiast funkcja X.WYSZUKAJ będzie w powyższym przypadku działała prawidłowo, ponieważ przy zaznaczonych konkretnych zakresach - kolejność kolumn nie ma znaczenia.

Drugi przypadek użycia funkcji X.WYSZUKAJ, tym razem na zasadzie dopasowania przybliżonego, omówimy na podstawie przykładowych danych z rys. 8 zawartych w arkuszu Przybliżone.

Rys. 8 – Przykładowe dane dla funkcji X.WYSZUKAJ z dopasowaniem przybliżonym

Dopasowanie przybliżone często związane jest z progami, premiami i temu podobnymi zagadnieniami. W danych określone zostały progi wartości sprzedaży, od których należą się premie oraz wysokość premii w każdym z progów.

Pierwszym argumentem funkcji X.WYSZUKAJ jest szukana_wartość, czyli wartość sprzedaży dla konkretnego pracownika z kolumny B o nazwie Sprzedaż.

Drugi argument to szukana_tablica, czyli wartość z tablicy z przedziałami sprzedaży – kolumna G. Zaznaczony zakres G2:G5 blokujemy bezwzględnie za pomocą klawisza F4.

Trzeci argument to zwracana_tablica, czyli kolumna, z której chcemy otrzymać wartość premii dla danego przedziału. W tym przykładzie będzie to zakres z kolumny I (I2:I5), również zablokowany bezwzględnie za pomocą klawisza F4.

Dodatkowym, czwartym argumentem jest [tryb_dopasowywania]. Przy dopasowaniu dokładnym, które jest domyślne, ten dodatkowy argument można pominąć. Jednak w omawianym przykładzie trzeba go określić, ponieważ wykorzystywane jest dopasowanie przybliżone. Istnieją 4 rodzaje dopasowania przedstawione na rys. 9.

Rys. 9 – Rodzaje trybu dopasowywania

Wybieramy Dopasowanie dokładne lub następny mniejszy element, czyli w formule wpisujemy wartość -1.

Zapis formuły powinien wyglądać następująco:

=X.WYSZUKAJ(B2;$G$2:$G$5;$I$2:$I$5;-1)

Tak przygotowaną formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wysokość premii dla każdego pracownika przedstawioną na rys. 10.

Rys. 10 – Wysokość premii dla każdego pracownika otrzymana przy wykorzystaniu funkcji X.WYSZUKAJ na zasadzie dopasowania przybliżonego

Różnica w działaniu funkcji WYSZUKAJ.PIONOWO oraz X.WYSZUKAJ polega na tym, że nowa funkcja nie potrzebuje posortowanych danych. W przypadku, gdy zostanie zmieniona ich kolejność (dane nie będą posortowane) otrzymamy błędne wyniki dla funkcji WYSZUKAJ.PIONOWO (rys. 11).

Rys. 11 – Błędne wyniki funkcji WYSZUKAJ.PIONOWO wynikające z nieposortowanych danych

Podsumowując - aby funkcja WYSZUKAJ.PIONOWO działała prawidłowo, dane z zakresu szukanej_wartości (drugi argument funkcji), muszą być posortowane rosnąco. Natomiast funkcja X.WYSZUKAJ nie potrzebuje wcześniejszego ułożenia tych informacji.

W kolejnym przykładzie omówimy działanie funkcji X.WYSZUKAJ z dopasowaniem w poziomie. Zagadnienie to omówimy na podstawie przykładowych danych zawartych w arkuszu Poziomo (rys. 12).

Rys. 12 – Przykładowe dane dla funkcji X.WYSZUKAJ z dopasowaniem w poziomie

W kolumnie C o nazwie Cena korzystaliśmy z funkcji WYSZUKAJ.POZIOMO, natomiast dla funkcji X.WYSZUKAJ kierunek wyszukiwania nie ma znaczenia.

Pierwszym argumentem funkcji X.WYSZUKAJ jest szukana_wartość, czyli w tym przykładzie ID (komórka A6).

Drugi argument to szukana_tablica, czyli jeden konkretny wiersz z numerami ID (B2:L2). Zaznaczony zakres blokujemy bezwzględnie za pomocą klawisza F4 (rys. 13).

Rys. 13 – Zakres pojedynczego wiersza jako drugi argument funkcji (szukana_tablica)

Kolejny argument funkcji to zwracana_tablica, czyli zakres pojedynczego wiersza z ceną (B3:L3). Zakres ten również blokujemy bezwzględnie za pomocą klawisza F4.

Tym razem korzystamy z funkcji na zasadzie dopasowania dokładnego, więc pomijamy czwarty argument funkcji (tryb_dopasowania).

Zapis funkcji powinien wyglądać następująco:

=X.WYSZUKAJ(A6;$B$2:$L$2;$B$3:$L$3)

Tak przygotowaną formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy wartości Ceny dla poszczególnych numerów ID przedstawione na rys. 14.

Rys. 14 – Wartość ceny dla każdego numeru ID otrzymana przy wykorzystaniu funkcji X.WYSZUKAJ na zasadzie dopasowania dokładnego

Kolejny przykład wykorzystania funkcji X.WYSZUKAJ dotyczy wyszukiwania ostatniego elementu. Zagadnienie to omówimy na podstawie przykładowych danych zawartych w arkuszu Ostatni (rys. 15).

Rys. 15 – Przykładowe dane dla funkcji X.WYSZUKAJ w celu wyszukiwania ostatniego elementu

Przy wykorzystaniu standardowych funkcji dostępnych do tej pory w Excelu trzeba było korzystać ze skomplikowanej formuły (rys. 16).

Rys. 16 – Skomplikowana formuła do wyznaczenia ostatniego elementu

Nowa funkcja X.WYSZUKAJ jest w stanie znaleźć ostatnią wartość w dużo prostszy sposób. W tym zadaniu celem jest znalezienie sprzedawcy, który sprzedał produkt jako ostatni.

Pierwszym argumentem funkcji jest szukana_wartość, czyli produkt, który został sprzedany jako ostatni (komórka G2).

Drugi argument funkcji to szukana_tablica, czyli kolumna z produktami, a mianowicie zakres od komórki B2 do ostatniej komórki w tej kolumnie. Przy zaznaczaniu danych do ostatniej komórki w danej kolumnie przechodzimy za pomocą skrótu klawiszowego Ctrl+Shift+strzałka w dół. Zaznaczony zakres blokujemy bezwzględnie za pomocą klawisza F4.

Trzeci argument funkcji to zwracana_tablica, czyli kolumna z której otrzymamy zwróconą wartość – kolumna Sprzedawca. Zaznaczamy zakres przy użyciu skrótu klawiszowego jak wyżej, a następnie blokujemy bezwzględnie.

Przy tak ustawionych zakresach i pominięciu dodatkowych argumentów funkcji otrzymalibyśmy pierwszego sprzedawcę. Czwarty argument funkcji, czyli [tryb_dopasowywania], ustawiamy jako dopasowanie dokładne (wartość 0).

Piątym argumentem funkcji jest tryb_wyszukiwania, w którym można ustawić kolejność wyszukiwania, tzn. przeszukiwać od końca do początku. Tym sposobem otrzymamy ostatnią wartość. W formule należy wtedy wpisać wartość -1. Otrzymujemy 4 sposoby wyszukiwania przedstawione na rys. 17.

Rys. 17 – Rodzaje trybów_wyszukiwania (piaty argument funkcji X.WYSZUKAJ)

Zapis formuły powinien wyglądać następująco:

=X.WYSZUKAJ(G2;$B$2:$B$97;$A$2:$A$97;0;-1)

Tak zapisaną formułę zatwierdzamy i kopiujemy na wiersze poniżej. Otrzymamy Sprzedawców, którzy sprzedali dany produkt jako ostatni (rys. 18).

Rys. 18 – Nazwa sprzedawcy, który jako ostatni sprzedał dany produkt przy wykorzystaniu funkcji X.WYSZUKAJ przy ustawionym trybie wyszukiwania od końca

Podsumowując: funkcja X.WYSZUKAJ zwraca taki sam wynik, jak dużo bardziej skomplikowana w zapisie formuła, wykorzystująca funkcję WYSZUKAJ, użyta w kolumnie I.

   Polecamy także:

• Jak obliczyć efektywną roczną stopę procentową?

• Jak obliczyć wartość: średnią, medianę i dominantę?