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ł.
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.
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.
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.
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.
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.
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.
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).
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).
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.
Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich trzech tabel (rys. nr 11).
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).
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.
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).
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.
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.
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.
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).
Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu połączyliśmy wszystkie dane (rys. nr 19).
Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).
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.
Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 22.
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).
Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).
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).
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.
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).
Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.
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
