W dzisiejszym poście nauczymy się jak filtrować dane po liście wartości. Zagadnienie to omówimy no podstawie przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

W danych mamy podaną tabelę z listą sprzedawców i wartości sprzedaży im przypisaną. W naszym zadaniu chcemy wyciągnąć z tej tabeli listę tylko tych sprzedawców, którzy należą do grupy A (druga tabelka). Możemy to zadanie zrobić w Power Query. Przede wszystkim musimy te tabelki załadować do Power Query. Wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.

rys. nr 2 — Z tabeli

Wczytujemy sobie najpierw mniejsza tabelkę, czyli ze sprzedawcami z grupy A. Otworzy nam się okno Edytor zapytań z wczytaną tabelą tGrupa. Chcemy ją mieć tylko w pamięci, więc od razu, za pomocą polecenia Zamknij i załaduj do (punkt nr 2 na rysunku nr 3) z karty Narzędzia główne, przenosimy do Excela.

rys. nr 3 — Zamknij i załaduj do

Otworzy nam się w Excelu okno Ładowanie do, gdzie ustawiamy parametry naszej nowej tabeli – ustawiamy opcję Utwórz tylko połączenie i zatwierdzamy przyciskiem Załaduj (rys. nr 4).

rys. nr 4 — Okno Ładowanie do

Teraz ustawiamy aktywną komórkę w obszarze pierwszej tabelki ze sprzedawcami i jak wyżej korzystając z polecenie Z tabeli z karty Dane, wczytujemy tabelę do Power Query. Otworzy nam się Edytor zapytań z wczytaną tabelą tSprzedaż. Aby móc wyciągnąć tylko tych sprzedawców którzy znajdują się w grupie sprzedawców A musimy scalić nasze dane. Wybieramy polecenie Połącz (punkt nr 2 na rysunku nr 5) z karty Narzędzia główne, a następnie Scal zapytania (punkt nr 3 na rysunku nr 5).

rys. nr 5 — Scal zapytania

Otworzy nam się okno Scalanie. W punkcie nr 1 oznaczonym na rysunku nr 7 wybieramy kolumnę, po której będziemy scalać dane – Sprzedawca. W punkcie oznaczonym numerem 2 na rysunku nr 7 wybieramy z listy rozwijanej zapytanie tGrupa. Następnie w punkcie oznaczonym numerem 3 na rysunku nr 7 wybieramy rodzaj sprzężenia. Musimy pamiętać, że w scalaniu tabelka u góry jest traktowana jako lewa (oznaczona na rysunku nr 6 strzałką czerwoną, a tabelka na dole jako prawa (strzałka niebieska).

rys. nr 6 — Okno scalanie

Wybieramy sprzężenie Prawe zewnętrzne, czyli wszystkie z drugiej tabeli i pasujące z pierwszej. Nasze ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 7).

rys. nr 7

Otrzymujemy dane przedstawione na rysunku nr 8.

rys. nr 8 — Scalone dane

Kolumnę tGrupa możemy sobie rozwinąć klikając na strzałki oznaczone na rysunku nr 9 zieloną strzałką. Zatwierdzamy Przyciskiem OK.

rys. nr 9 — Rozwiń

W wyniku rozwinięcia kolumny tGrupa otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 — rozwinięte dane

Są to ci sami sprzedawcy co w pierwszej kolumnie, więc możemy sobie usunąć ten krok z bocznego panelu Zastosowane kroki i usunąć całkowicie tą kolumnę za pomocą polecenia Usuń z podręcznego menu (rys. nr 11).

rys. nr 11 — Usuń kolumnę

Otrzymaliśmy wynik jaki nas interesuje, więc musimy go załadować do Excela. Z karty Narzędzia główne wybieramy polecenie Zamknij i załaduj do (punkt nr 2 na rysunku nr 12).

rys. nr 12 — Zamknij i załaduj do

Otworzy się w Excelu okno Ładowanie do, gdzie ustawiamy parametry ładowanych danych i zatwierdzamy przyciskiem Załaduj. Dane wstawione jako tabela w istniejącym arkuszu, w konkretnej komórce (rys. nr 13).

rys. nr 13 — Okno Ładowanie do

Nasze dane w Excelu pokazane zostały na rysunku nr 14.

rys. nr 14 — Dane zaczytane do Excela

Co jest ciekawego w naszych działaniach w scalaniu – jeśli wyznaczyliśmy wszystkich sprzedawców, którzy są na liście grupy A, to możemy w bardzo łatwy sposób wyznaczyć tych, których na tej liście nie ma. Wystarczy wrócić do Power Query i zmienić typ scalania. Aby nie stracić aktualnych wyników, skopiujemy je za pomocą polecenia Duplikuj z podręcznego menu (rys. nr 15).

rys. nr 15 — Duplikuj

W ustawieniach zapytania w Zastosowanych krokach musimy wrócić do kroku Scalanie zapytania i kliknąć w koło zębate przy nazwie tego zapytania. Otworzy nam się okno Scalanie, gdzie możemy zmienić (w miejscu oznaczonym zieloną strzałką na rysunku nr 16) rodzaj sprzężenia. Chcemy wyrzucić te elementy które są w prawej kolumnie (tGrupa),  a zachować tylko te, których tam nie ma, więc musimy wybrać Lewe anty (wiersze tylko w pierwszej).

rys. nr 16 — Lewe anty

Po usunięciu dodatkowej kolumny (tak jak w poprzednim scalaniu) otrzymujemy dane przedstawione na rysunku nr 17.

rys. nr 17

Następnie wybieramy polecenie Zamknij i załaduj z karty Narzędzia główne (jak na rysunku nr 12). Otworzy nam się okno Ładowanie do, gdzie ustawiamy wstawiane dane jako tabela oraz miejsce wstawienia danych – konkretna komórka w istniejącym arkuszu (analogicznie jak na rysunku nr 13). Zatwierdzamy te parametry przyciskiem Załaduj i otrzymujemy dane przedstawione na rysunku nr 18.

rys. nr 18

Innym sposobem rozwiązania tego zagadnienia jest użycie filtrów. Po raz kolejny wczytujemy dane z tabeli do Power Query. Otworzy nam się Edytor zapytań z wczytaną tabelą tSprzedaż(3). Rozwijamy podręczne menu za pomocą ikonki z boku nazwy kolumny Sprzedawca i wybieramy polecenie Filtry tekstu, a następnie Zawiera (rys. nr 19).

rys. nr 19 — Filtry tekstu

Otworzy nam się okno Filtrowanie wierszy, w którym wpisujemy jakiś ciąg znaków (bez znaczenia bo później go zmodyfikujemy) i zatwierdzamy OK (rys. nr 20).

rys. nr 20 — Filtrowanie wierszy

Otrzymamy pustą tabele przedstawioną na rysunku nr 21. Jest pusta ponieważ żadne ze sprzedawców nie zawiera ciągu liter asd, jakie wpisaliśmy.

rys. nr 21 — pusta tabela

Została tu użyta funkcja Text.Contains, która ma zwrócić każdy wiersz, który zawiera ciąg znaków. My chcemy aby funkcja zwróciła nam tabelę więc użyjemy następującej formuły:

List.ContainsAny(tGrupa[Grupa A],[{Sprzedawca}])

Możemy tę funkcje przetłumaczyć na przypadek, jeśli lista zawiera którąkolwiek z wartości. Tą formułę musimy wkleić w pasek formuły zamiast funkcji Text.Contains. Zapis formuły oraz jej wyniki zostały przedstawione na rysunku nr 22.

rys. nr 22

Otrzymany wynik jest identyczny z tym otrzymanym przez scalanie. Moim zdaniem dużo bardziej skomplikowany i przeznaczony dla osób bardziej zaawansowanych w Power Query. Ważną informacją do zapamiętania jest to, że Power Query nie obsługuje symboli wieloznacznych, takich jak np. * czy ?. Formuły wtedy nie zadziałają. Najlepiej korzystać z tego rozwiązania, które rozumiemy i potrafimy go używać, ponieważ jest większe prawdopodobieństwo, że nie popełnimy błędu. Jeśli użyjemy rozwiązania, które sprawia nam trudności i jest nie do końca zrozumiałe, może się zdarzyć że otrzymamy błędne wyniki.


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