Najczęściej przy pomocy funkcji WYSZUKAJ.PIONOWO korzystamy z dokładnego dopasowania, czyli w ostatnim argumencie [przeszukiwany.zakres] wpisujemy 0, czyli FAŁSZ. By taką samą operację wykonać w Power Query wystarczy scalić ze sobą 2 tabele. Zupełnie inaczej sprawa się ma, gdy chcemy w PQ otrzymać efekt działania funkcji WYSZUKAJ.PIONOWO z argumentem PRAWDA czyli 1 – potrzeba nieco więcej zabiegów.

Jak wygląda WYSZUKAJ.PIONOWO Z 1?

Opcja 1: Kolumna warunkowa w Power Query

W arkuszach posiadam dwie tabele z danymi: pierwszą z produktami oraz uzyskanym na nich zyskiem, a drugą z kategorią dla wysokości uzyskanego zysku.

Jeśli reguła jest liczbowa, możemy odwzorować działanie WYSZUKAJ.PIONOWO z 1 kolumną warunkową: Query Editor > Dodaj kolumnę > Ogólne > Kolumna warunkowa.

W oknie Dodawanie kolumny warunkowej wprowadzam kolejne warunki, czyli kategorię do której przypiszę daną wartość zysku. Poniżej okno uzupełnione o wszystkie kategorie i warunki.

Po wybraniu przycisku OK, w danych pojawia się nowa kolumna, uzupełniona wcześniejszymi kategoriami.

Zalety kolumny warunkowej

• szybkość wprowadzania przy małej ilości warunków,
• prostota – gotowy mechanizm dodawania kolumny.

Wady kolumny warunkowej

• brak możliwości szybkiej edycji wpisanych warunków lub dodania nowych przez użytkownika tabeli
• długi czas wprowadzania kolejnych warunków w oknie Dodawanie kolumny warunkowej – im ich jest więcej, tym dłużej to trwa
• opcja nie zadziała dla wyszukiwań tekstowych

Opcja 2: Dołącz zapytania w Power Query

Sposób polega na wprowadzeniu do PQ obu tabel z Excela i wykonaniu kilku operacji w odpowiedniej sekwencji. Po wprowadzeniu tabel do Power Query w pierwszym ruchu połączymy je ze sobą Narzędzia główne > Połącz > Dołącz zapytania (kolumna Zysk musi nazywać się identycznie w obu tabelach).

Następnie sortujemy dane rosnąco według kolumny Zysk, co powoduje wymieszanie kategorii oraz pozostałych danych.

Na kolumnie Kategoria wywołuję prawym przyciskiem myszy menu podręczne i wybieram Wypełnij > W górę.

Dzięki temu wszystkie wartości do 4000 będą miały przypisane kategorie. Co z wartościami które przekraczały ostatnią z kategorii (czyli 4000)?

Jeżeli założę filtr na kolumnie Kategoria wówczas widać null, czyli pola bez przypisanej kategorii.

Jeżeli szybko zerknę na dane okaże się, że są o rekordy o zysku większym niż 4000. Na nie również jest sposób. Ponownie na kolumnie Kategoria wybieram menu podręczne i opcję Zamień wartości. Zamieniam wartości null (bez przypisanej kategorii, czyli większe od 4000) na kategorię bardzo duży.

Dzięki temu już wszystkie wiersze mają przypisane kategorie.

Na koniec pamiętajmy o odfiltrowaniu sztucznie dodanych wierszy (np. w kolumnie Nazwa produktu).

Zalety dołączania

• prostota wprowadzania zmian (wystarczy aktualizacja tabelki w Excelu)
• działa dla wyszukiwań liczbowych i tekstowych

Wady dołączania

• granica przedziału, której należy pilnować ręcznie (ewentualnie można ustawić ostatni przedział daleko za danymi)
• bardziej złożony proces wykonania

Pobierz plik Excel

Pobierz plik WYSZUKAJ.PIONOWO w Power Query z 1 i podstaw własne dane.