W dzisiejszym poście nauczymy się jak połączyć listy osób
zapisanych na różne kursy za pomocą Power Query – dodatku do Excela. W
przykładowych danych do tego zagadnienia przedstawionych na rysunku nr 1 mamy
podane trzy listy adresów mailowych związanych z różnymi kursami. Naszym
zadaniem jest połączenie tych list i stworzenie skumulowanej tabeli z adresami
mailowymi z zaznaczonymi kursami, na jaki właściciel danego maila się zapisał.
rys. nr 1 — Przykładowe dane
Nasze zadanie ma również na celu to, żeby konkretna osoba
która zapisała się na wszystkie trzy kursy w naszej tabeli występowała jeden
raz (np. Andrzej@gazeta.pl zapisał
się na trzy kursy).
Pierwszym krokiem jest zaczytanie naszych danych bazowych, w
tym celu zaznaczamy dowolną komórkę w pierwszej tabeli i wybieramy polecenie Z
tabeli (punkt nr 2 na rysunku nr 2) z karty Dane. Ułatwieniem tutaj jest, że
tabele wejściowe zostały nazwane Tabela1, Tabela2 i Tabela3.
rys. nr 2 — Z tabeli
Otworzy nam się Edytor zapytań z wczytaną tabelą Tabela1
(rys. nr 3). Z Zastosowanych kroków (Ustawienia zapytania) usuwamy krok
Zmieniono typ, ponieważ jest on niepotrzebny.
rys. nr 3 — Edytor zapytań
Pozostałe tabele możemy wczytać w łatwy sposób, wystarczy że
zduplikujemy sobie Zapytanie. Klikamy prawym przyciskiem myszy na nazwę
Zapytanie1 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 4).
Czynność tę powtarzamy dwa razy ponieważ potrzebujemy trzech tabel.
rys. nr 4 — Duplikuj zapytanie
Zaznaczamy sobie zapytanie Tabela1 (2) i w pasku formuły
zmieniamy nazwę Tabela1 na Tabela2 (oznaczone zieloną strzałką na rysunku nr
5). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.
rys. nr 5 — Zmiany w pasku formuły
Otrzymamy zapytanie Tabela1 (2) z wczytaną drugą tabelą z
danymi (osoby zapisane na kurs Dashboardy) przedstawione na rysunku nr 6. Po
raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.
rys. nr 6 — Połączone zapytania
Analogicznie wygląda sytuacja dla trzeciego zapytania.
Klikamy na nazwę zapytania Tabela1 (3) i w pasku formuły zmieniamy
nazwę Tabela1 na Tabela3. Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.
Otrzymamy trzecie zapytanie z wczytanymi danymi z tabeli numer 3
przedstawionymi na rysunku nr 7. Po raz kolejny usuwamy niepotrzebny krok
Zmieniono typ z Zastosowanych kroków.
rys. nr 7 — Dane z trzech tabel
Kolejnym krokiem jest zmiana nazwy zapytań nr 2 i 3 we
Właściwościach w panelu bocznym z Ustawieniami zapytania (oznaczone zieloną
strzałką na rysunku nr 8).
rys. nr 8 — Zmiana nazwy zapytania
Kiedy już mamy zaczytane wszystkie trzy tabele, możemy je
połączyć. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 9) z karty
Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3 na rysunku
nr 9).
rys. nr 9 — Dołącz zapytania
Pojawi nam się okno Dołączanie, gdzie zaznaczamy checkbox
przy opcji Co najmniej trzy tabele (punkt nr 1 na rysunku nr 10). Następnie
dodajemy Tabelę2 i Tabelę3 z pola Dostępne tabele do pola Tabele do dołączenia
za pomocą przycisku Dodaj (punkt nr 2 na rysunku nr 10). Dołączone tabele
zatwierdzamy przyciskiem OK.
rys. nr 10 — Okno Dołączania zapytań
Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich
trzech tabel (rys. nr 11).
rys. nr 11 — Skumulowane dane
Mimo że dane zostały dołączone to nadal osoby które zapisały
się na większą ilość kursów są wyświetlane osobno, a nam chodzi o to by takie
osoby wyświetlane były w tabeli tylko raz. W tym celu musimy anulować
przestawienie innych kolumn poza kolumną Mail. Klikamy prawym przyciskiem myszy
na tytuł kolumny Mail i z podręcznego menu wybieramy polecenie Anuluj
przestawienie innych kolumn (rys. nr 12).
rys. nr 12 — Anuluj przestawienie innych kolumn
Otrzymamy dane przedstawione na rysunku nr 13, gdzie stworzą
nam się nowe kolumny. Kolumna Atrybuty będzie zawierała nazwy kolumn, natomiast
kolumna Wartość będzie zawierała wartości jakie były przypisane do odpowiednich
adresów mailowych w tych kolumnach.
rys. nr 13 — Dane z nowymi kolumnami
Najważniejszy efekt działania tego polecenia to pozbycie się
z danych wartości null. W kolejnym etapie musimy przekształcić jedną z tych
kolumn (Atrybuty bądź Wartość), zrobimy operację odwrotną. Z karty Przekształć
wybieramy polecenie Kolumna przestawna (punkt nr 2 na rysunku nr 14).
rys. nr 14 — Kolumna przestawna
Otworzy nam się okno Tabeli przestawnej, gdzie stworzymy nowe
kolumny przy użyciu kolumny Atrybyt, czyli powstaną kolumny z nazwami VBA,
Dashboardy i Excel (rys. nr 15). Odpowiednio w każdej z tych kolumn będzie
wpisana wartość z kolumny Wartość (punkt nr 1 na rysunku nr 15). Ponadto musimy
wybrać w Opcjach zaawansowanych wybieramy w jaki sposób będą agregowane nasze
dane – Nie agreguj (punkt nr 2 na rysunku nr 15). Wybrane parametry
zatwierdzamy przyciskiem OK.
rys. nr 15 — Okno kolumny przestawnej
Otrzymamy dane przedstawione na rysunku nr 16, gdzie mamy już
skumulowane dane. Przykładowo właściciel maila Andrzej@gazeta.pl występuje tylko raz na
liście mimo że zapisał się aż na trzy kursy.
rys. nr 16 — Skumulowane dane
Tak przygotowane dane możemy załadować do Excela za pomocą
polecenia Zamknij i załaduj (punkt nr 2 na rysunku nr 17) z karty Narzędzia
główne.
rys. nr 17 Zamknij i załaduj
Ponieważ jednocześnie pobieraliśmy dane z trzech tabel, to
utworzyło nam się w Excelu tylko połączenie. Dane te będą widoczne w panelu
bocznym Zapytania. Gdyby nie były widoczne należy wybrać polecenie Pokaż
zapytania z karty Dane (rys. nr 18).
rys. nr 19 — Pokaż zapytania
Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu
połączyliśmy wszystkie dane (rys. nr 19).
rys. nr 19 — Zapytanie Tabela1
Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i
z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).
rys. nr 20 — Załaduj do
Otworzy nam się okno Ładowania do, gdzie wybieramy sposób
przedstawienia załadowanych danych jako Tabela, a następnie lokalizację tych
danych jako Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 21).
Powyższe parametry zatwierdzamy przyciskiem Załaduj.
rys. nr 21 — Okno Ładowanie do
Otrzymamy dane załadowane do Excela przedstawione na rysunku
nr 22.
rys. nr 22 — Dane w Excelu
Przy tworzeniu danych został zrobiony błąd, którego nie
zauważyliśmy. W Excelu w oknie Zapytania dotyczące skoroszytu jest on
wyszczególniony (rys. nr 23).
rys. nr 23 — Błąd w zapytaniu
Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z
wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).
rys. nr 24 — Edytor zapytań z błędem
W panelu bocznym Ustawienia zapytania klikamy na Źródło w
Zastosowanych krokach. Mamy podany numer wiersza w którym był błąd, więc możemy
go łatwo znaleźć. Kiedy klikniemy na ten wiersz wyświetli nam się pod tabelą
informacja o tym błędzie (rys. nr 25).
rys. nr 25 — Informacja o błędzie
Błąd ten dotyczy maila Sebastian@yahoo.com, który wystąpił w danych
źródłowych dwa razy (dla tego samego kursu) co widać na rysunku nr 26.
rys. nr 26 — Miejsce błędu
Musimy poprawić te dane W Excelu i usunąć powtarzający się
adres mailowy, następnie klikamy prawym przyciskiem myszy na dowolną komórkę w
danych z zapytania i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).
rys. nr 27 — Odśwież
Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.
rys. nr 28 — Prawidłowe dane
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.
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.
W dzisiejszym poście nauczymy się jak zamienić wszystkie x w
tabeli na nagłówki kolumn. Jest to zadanie zaczerpnięte od Oza du Soleila https://www.youtube.com/watch?v=abQrUE10Xqo , on
rozwiązał to za pomocą kilku zapytań. Nasze zadanie ma na celu podstawienie w
miejsce x w tabeli odpowiednich nagłówków. Przykładowe dane i cel do którego
dążymy został przedstawiony na rysunku nr 1.
rys. nr 1 — Dane i cel do którego dążymy
Na przykład chcemy aby zamiast znaku x dla Scoobiego zostało
wstawione Jedzenie (czyli nagłówek kolumny, w której został wstawiony ten znak
x). Pokażemy jak to zrobić w Power Query za pomocą tylko jednego zapytania.
Załóżmy, że mamy przykładową tabelkę z danymi przedstawioną na rysunku nr 2.
rys. nr 2 — Przykładowe dane
Z karty Dane wybieramy polecenie Z tabeli (punkt nr 2 na
rysunku nr 3).
rys. nr 3 — Z tabeli
Otworzy nam się edytor zapytań tCechy z wczytaną tabela z
Excela (rys. nr 4). W pierwszym etapie chcemy usunąć krok Zmieniono tym z Zastosowanych
kroków, bo nie jest nam on potrzebny.
rys. nr 4 — Edytor zapytań
W naszych wczytanych danych jest jedna postać, która w każdej
kolumnie ma Null, czyli nie ma w żadnej kolumnie znaku x. to był największy
problem, żeby rozwiązać to zadanie za pomocą jednego zapytania. Klikamy na
tytuł komuny Imię prawym przyciskiem myszy i z podręcznego menu wybieramy
polecenie Anuluj przestawienie innych kolumn (rys. nr 5).
rys. nr 5 — Anuluj przestawienie innych kolumn
W efekcie otrzymamy tabelkę z tylko tymi osobami gdzie był
znak x (rys. nr 6). Nie została uwzględniona tutaj postać Duch, u której nie
było w ogóle tego znaku.
rys. nr 6 — tabela z danymi
Wynika z tego, że nie możemy użyć tego polecenia.
Potrzebujemy sposobu na uwzględnienie osoby, która nie zaznaczyła żadnego znaku
x (czyli np. nie wybrała żadnego kursu z naszej oferty). Usuwamy z
Zastosowanych kroków ostatni etap, ponieważ zwracał niekompletne dane. Można to
zrobić za pomocą Kolumny indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna
indeksu (punkt nr 2 na rysunku na 7), a następnie od 0 lub 1 (nie ma to znaczenia,
ponieważ jest to kolumna pomocnicza, dzięki której osoba Duch nie zniknie z
danych, ponieważ będzie miała przypisaną jakąś wartość).
rys. nr 7 — Kolumna indeksu
Otrzymamy dane przedstawione na rysunku nr 8.
rys. nr 8 — Dane z nową kolumną
Na tym etapie po raz kolejny wybieramy polecenie z podręcznego
menu Anuluj przestawienie innych kolumn (identycznie jak na rysunku nr 5
wyżej). Otrzymamy dane przedstawione na rysunku nr 9.
rys. nr 9 — Dane
Kolumna z Wartościami jest nam nie potrzebna, natomiast
potrzebujemy drugiej kolumny z Atrybutami. Klikamy prawym przyciskiem myszy na
nagłówek kolumny Atrybut i z podręcznego menu wybieramy polecenie Duplikuj
kolumnę (rys. nr 10).
rys. nr 10 — Duplikuj kolumnę
W kolejnym etapie usuwamy kolumnę z wartościami. Klikamy na
jej nagłówek prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń
(rys. nr 11).
rys. nr 11 — Usuń
Otrzymamy dane przedstawione na rysunku nr 12.
rys. nr 12
Mamy dwie kolumny z takimi samymi wartościami, część z tych
danych ma posłużyć jako nagłówki kolumn a część jako wartości. Aby tak się
stało musimy znaleźć na karcie Przekształć ikonkę Kolumny przestawnej (punkt nr
2 na rysunku nr 13). Ważne jest aby aktywna (zaznaczona) była kolumna Atrybut.
rys. nr 13 — Kolumna przestawna
Pojawi się okno Kolumny przestawnej (rys. nr 14). Polecenie
to utworzy nowe kolumny przy użyciu danych z kolumny Atrybut. Jeśli jakaś nazwa
się powtórzy to kolumna pozostanie pojedyncza, nie zostanie zduplikowana np.
dla danej Jedzenie. W polu Kolumna wartości (punkt nr 1 na rysunku nr 14) musimy
wybrać daną, jaka ma być przechowywana w tej nowej kolumnie. Tę wartość chcemy
wziąć z kolumny Atrybut – kopia. Nasza kolumna z imionami natomiast zostanie na
miejscu. Ważne jest tutaj użycie opcji zaawansowanych – w polu Agreguj funkcje
wartości (punkt nr 2) wybieramy polecenie Nie agreguj, ponieważ nie chcemy
tutaj wykonywać żadnych obliczeń. Tak ustawione parametry kolumny przestawnej zatwierdzamy klikając
przycisk OK.
rys. nr 14 — Okno kolumny przestawnej
Otrzymamy dane przedstawione na rysunku nr 15.
rys. nr 15 — Dane z kolumną przestawną
W kolejnym etapie musimy usunąć kolumnę indeks, która była
kolumną pomocniczą. Klikamy prawym przyciskiem myszy na nagłówek kolumny Indeks
i z podręcznego menu wybieramy polecenie Usuń. Otrzymamy wynik końcowy, o który
nam chodziło (rys. nr 16).
Rys nr 16 — Ostateczny wynik
Następnie wybieramy polecenie Zamknij i załaduj do z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Zamknij i załaduj do
Otworzy nam się okno Ładowanie do, gdzie wybieramy rodzaj
wyświetlania danych jako Tabela i miejsce ich wstawienia jako istniejący arkusz
i konkretną komórkę. Powyższe parametry zatwierdzamy klikając przycisk Załaduj
(rys. nr 18).
rys. nr 18 — Okno Ładowania do
Otrzymamy dane w Excelu przedstawione na rysunku nr 19.
rys. nr 19 — Dane wczytane do Excela
Jedyną wadą takiego rozwiązania jest to, że Power Query sortuje wszystkie dane, łącznie z nazwami kolumn. Możemy sobie sprawdzić działanie zapytania dodając dodatkowe dane do tabeli z dany mi bazowymi (dodatkowe wiersze i kolumnę). Następnie klikamy prawym przyciskiem myszy na komórkę z tabeli z Power Query i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 20).
rys. nr 20 — Odśwież dane
Otrzymamy prawidłowo przeliczone dane przedstawione na
rysunku nr 21.
rys. nr 21 — Przeliczone dane
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.
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.
W dzisiejszym poście zajmiemy się grupowaniem danych z wielu
arkuszy. W przykładowych danych mamy imiona i nazwiska osób (klientów), z
którymi się spotkaliśmy bądź im coś sprzedaliśmy. Dane te są podzielone na
różne arkusze, np. pod względem miesięcy. Dane te chcemy połączyć, podliczyć,
np. wyznaczyć ile razy odbyliśmy spotkań z konkretnym klientem lub ile
sprzedaliśmy takich samych produktów. Część danych (jeden z arkuszy), na którym
będziemy pracować, został przedstawiony na rysunku nr 1.
rys. nr 1 — przykładowy arkusz z danymi
Naszym
zadaniem jest uzyskanie podsumowania przedstawionego na rysunku nr 2
rys. nr 2 — Nasz cel — pogrupowane dane
Do tych obliczeń chcemy wykorzystać Power Query. Zaczynamy od
pustego arkusza, gdzie wybieramy polecenie Nowe zapytanie (punkt nr 2 na
rysunku nr 3) z karty Dane, a następnie Z pliku (punkt nr 3) i Ze skoroszytu
(punkt nr 4).
rys. nr 3 — Nowe zapytanie ze skoroszytu
Otworzy nam się okno Importowanie pliku, gdzie musimy wybrać
lokalizację naszego pliku z danymi (rys. nr 4). Wybieramy plik i zatwierdzamy
nasz wybór przyciskiem Otwórz.
rys. nr 4 — Importowanie danych
Łączymy się z tym plikiem, z którego chcemy pobierać
informacje. Otworzy nam się okno Nawigator. W opcjach wyświetlania wybieramy
np. Styczeń – zaznaczony na rysunku zieloną strzałką (nie ma to większego
znaczenia, bo nasze nowe zapytanie będziemy edytować) nasz wybór zatwierdzamy
przyciskiem Edytuj (zaznaczone zielonym prostokątem na rysunku nr 5).
rys. nr 5 — Nawigator
Czekamy aż nasze dane ze stycznia zostaną wczytane do Power
Query. Otworzy nam się Edytor zapytań z wczytaną tabelą z danymi ze stycznia.
Dane te zostały przedstawione na rysunku nr 6. Nie interesują nas na tym etapie
te dane, a jedynie Zastosowane kroki (oznaczone na rysunku nr 6 zieloną
strzałką). Usuwamy kroki Zmieniono typ i Nawigacja, a zostawiamy krok Źródło.
rys. nr 6 — Edytor zapytań
Otrzymamy
dane przedstawione na rysunku nr 7.
rys. nr 7 — dane
Dzięki temu nie będziemy musieli sami wpisywać skomplikowanej
formuły, tylko skorzystamy w wbudowanych funkcjonalności Power Query, czyli
będziemy wprowadzać zmiany w istniejącej formule na pasku formuły. W naszych
danych mamy podział na Kind, czyli rodzaje danych w pliku. Sheet to arkusze,
Table to tabela, natomiast DefinedName to zdefiniowane zakresy danych.
Rozwijamy ikonkę ze strzałką w tytule kolumny Kind i w podręcznym menu
zaznaczamy tylko Sheet, bo interesują nas tylko arkusze (rys. nr 8). Podane
parametry filtru zatwierdzamy klikając przycisk OK.
rys. nr 8 — Filtry
W naszych danych mamy jeszcze niepotrzebny ukryty arkusz,
którego nie chcemy widzieć w naszych danych, więc klikamy na ikonkę strzałki w
tytule kolumny Hidden i odznaczamy checkbox True (rys. nr 9). Nasz filtr
zatwierdzamy klikając przycisk OK.
rys. nr 9 — Filtr
Z kolumny Item interesują nas dane tylko z konkretnych
arkuszy : styczeń, luty i marzec. Chcemy odfiltrować dane (arkusze) domyślne,
czyli takie które mają w sobie jakąś nazwę, w naszym przykładzie niepotrzebne
arkusze mają w nazwie litery PQ. Klikamy na ikonkę ze strzałką w tytule kolumny
Item, a następnie wybieramy opcje Filtr tekstu i Nie zawiera (rys. nr 10).
rys. nr 10 — Nie zawiera
Otworzy nam się okno Filtrowanie wierszy, gdzie wpisujemy w
pole Nie zawiera zestaw liter PQ (miejsce zaznaczone strzałka na rysunku nr
11). Musimy tutaj pamiętać, że Power Query jest Case sensitive, czyli zwraca
uwagę na wielkość liter. Nasz filtr zatwierdzamy klikając przycisk OK.
rys. nr 11 — Okno filtrowanie wierszy
Otrzymamy interesujące nas dane (arkusze) przedstawione na
rysunku nr 12.
rys. nr 12 — Interesujące nas arkusze
Dane te z poszczególnych arkuszy znajdują się w kolumnie Data
pod nazwą Table. Na tym etapie interesuje nas tylko kolumna Data, zawierająca
tabelki z danymi. Klikamy prawym przyciskiem myszy na tytuł tej kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — Usuń inne kolumny
Otrzymamy jedną kolumnę Data, następnie klikamy na ikonkę ze
strzałkami w jej nazwie i z podręcznego menu wybieramy polecenie Rozwiń. Nasze
parametry zatwierdzamy przyciskiem OK (rys. nr 14).
rys. nr 14 — Rozwiń
Otrzymamy dane bez nagłówków, więc musimy wybrać polecenie
Użyj pierwszego wiersza jako nagłówków z karty Narzędzia główne (rys. nr 15).
rys. nr 15 — Użyj pierwszego wiersza jako nagłówków
W danych mamy 96 wierszy. Nasze dane będą się prezentować
następująco (rys. nr 16):
rys. nr 16
W kolejnym etapie wybieramy polecenie Grupowanie według z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Grupowanie według
Otworzy nam się okno Grupowanie według. Power Query
automatycznie wstawi parametry grupowania, czyli Nazwisko i Imię (według czego
ma grupować), bo mamy w danych dwie kolumny. W polu Nazwa nowej Kolumny
automatycznie zostało wstawione Liczność (nie musimy zmieniać bo chodzi nam o
policzenie ilości takich samych danych). W polu operacja wybieramy polecenie
Zlicz wiersze. Pole Kolumna pozostaje w naszym przykładzie pusta (jest to pole
do innych operacji). Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 18).
rys. nr 18 — Okno grupowanie według
Po
przefiltrowaniu danych zostało 75 wierszy. Z karty Narzędzia główne wybieramy
polecenie Sortuj od Z do A (rys. 19).
rys. nr 19 — Sortuj od Z do A
Nasze dane posortowane od największej do najmniejszej pokazaliśmy
na rysunku nr 20.
rys. nr 20 — Posortowane dane
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do (punkt nr 2 na rysunku nr 21).
rys. nr 21 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie wybieramy
sposób przedstawienia danych jako Tabela oraz miejsce załadowania danych –
Istniejący arkusz komórka $A$1 (rys. nr 22). Ustawione parametry zatwierdzamy
klikając przycisk Załaduj.
rys. nr 22 — Okno Ładowanie do
Otrzymamy dane w Excelu przedstawione na rysunku nr 23.
rys. nr 23 — Dane załadowane do Excela
Jeśli dodamy kolejny miesiąc do danych bazowych, to filtry w
Power Query zadziałają prawidłowo i wstawią dodatkowe dane do tabeli w Excelu.
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.
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.
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.
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.
