W dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany element jest na liście. W przykładowych danych przedstawionych na rys. nr 1 mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się dany sprzedawca, to z tej listy chcemy pobierać dane.
Zakładamy, że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z tabel pobrać do Power Query. Możemy to zrobić wybierając polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
Otworzy nam się edytor zapytań Power Query z wczytaną tabelą pobraną z Excela przedstawioną na rys. nr 3.
W analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu z karty Dane (jak na rys. nr 2). Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).
Możemy przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=. Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 5).
Otworzy nam się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4), natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.
W drugiej regule w polu Nazwa kolumny wybieramy kolumnę Sprzedawca, pole Operator zostaje bez zmian, w polu Wartość wpisujemy Dominik, następnie zmieniamy typ Wartości wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null (oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej zatwierdzamy przyciskiem OK.
Otrzymamy dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji JEŻELI.
W zapisie formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains (rys. nr 9).
Funkcja List.Contains potrzebuje jako pierwszego argumentu listy wszystkich sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową. Zapis formuły powinien wyglądać następująco:
=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains ({"Aleksandra", "Robert"}, [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))
Zmieniliśmy tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły otrzymamy zmienione dane przedstawione na rys. nr 10.
Ten sposób jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał następująco:
=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie] else null))
Po zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:
=Table.AddColumn(#"Zmieniono typ", "Główny region", each if List.Contains (tRegiony_k [Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains (tRegiony_k [Mazowieckie] then [Mazowieckie] else null))
Power Query przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys. nr 12.
W danych mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list. Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym razie na rys. nr 7).
Tak przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 13).
Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia – konkretny arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 14).
Otrzymamy dane zaimportowane do Excela i przedstawione na rys. nr 15.
Jak widać na rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej Główny region.
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
