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 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.
W dzisiejszym poście zajmiemy się tematem scalania tego
samego zapytania w Power Query. Zagadnienie to omówimy na podstawie danych z
poprzedniego postu przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Poprzedni post dotyczył wyciągania pierwszych i ostatnich
wierszy po unikatowych wartościach z kolumn Data i Firma. Stworzyliśmy dwa
zapytania i scaliliśmy je ze sobą. Tym razem chcemy mieć jedno zapytanie.
Zagadnienie to jest wstępem do języka zapytań M, czyli kulis zapytań Power
Query. Pozwoli nam to w miarę naszego rozwoju nie korzystać tylko z gotowych
rozwiązań tego dodatku do Excela.
Pierwszym krokiem jest wczytanie naszych danych z tabeli do
Power Query. Musimy w tym celu użyć polecenia Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Pobieranie danych zewnętrznych
Tak jak w poprzednim poście dane zostały wczytane w innym
formacie więc musimy dla kolumny Data i Czas zmienić typ kolumny na bieżącą –
klikamy prawym przyciskiem myszy na tytuł kolumn Data i analogicznie Czas,
następnie wybieramy polecenie Data lub dla drugiej kolumny Godzina i na
komunikacie, który się wyświetli klikamy przycisk Zamień bieżącą (zaznaczono
zieloną strzałką na rysunku nr 3). Celem tych działań jest to, żeby Power Query
odpowiednio interpretował te dane.
rys. nr 3 — Zmiana typu kolumny
Na karcie Widok musimy mieć włączony Pasek formuły (rys. nr
4). Będziemy robić na nim drobne zmiany w kodzie.
rys. nr 4 — Pasek formuły
Wczytane dane przedstawione zostały na rysunku nr 5.
rys. nr 5 — Wczytane dane
Kolejnym krokiem jest posortowanie danych. Klikamy prawym
przyciskiem myszki na trójkącik przy tytule kolumny Data i wybieramy polecenie
Sortuj rosnąco (rys. nr 6).
rys. nr 6 — Sortuj rosnąco
Te same działania musimy powtórzyć dla kolumny Czas. Klikamy
prawym przyciskiem myszki na trójkącik przy tytule kolumny Czas i wybieramy
polecenie Sortuj rosnąco. Z boku ekranu w Ustawieniach zapytania mamy wpisane
kroki, które wykonaliśmy na naszych danych (rys. nr 7).
rys. nr 7 — Ustawienia zapytania
Możemy zmienić nazwę tego kroku, aby w każdym momencie
działań widzieć co konkretnie zrobiliśmy. Naciskamy klawisz F2 i zmieniamy
nazwę na Posortowano Asc (nie napisałem rosnąco, bo ze względu na język
programowania staram się nie używać polskich znaków) – rys. nr 8.
rys. nr 8 — Zmiana nazwy kroku
Teraz tak jak w poprzednim poście musimy usunąć duplikaty po
kolumnach Data i Firma. Zaznaczamy kolumny Data i Firma, a następnie wybieramy
z karty Narzędzia główne polecenie Usuń wiersze (punkt nr 2 na rysunku nr 9), a
potem Usuń duplikaty (punkt nr 3 na rysunku nr 9).
rys. nr 8 — Usuń duplikaty
Otrzymamy wyciągnięte pierwsze wiersze po unikatowych
wartościach. Zostały one przedstawione na rysunku nr 10.
rys. nr 10 — Pierwsze wiersze po unikatowych wartościach
Analogicznie jak dla poprzedniego etapu zmieniamy nazwę
zastosowanego kroku w Ustawieniach zapytania za pomocą klawisza F2 z Usunięto
duplikaty na Pierwsze.
Chcemy aby teraz kolejny krok jaki wykonamy nie był zależny
od kroku Pierwsze tylko od wcześniejszego czyli Posortowano Asc. Klikamy na
krok Posortowano Asc i kopiujemy formułę z paska formuły za pomocą skrótu
klawiszowego Ctrl+C. Następnie przechodzimy do ostatniego naszego kroku, czyli
Pierwsze i na pasku formuły kliknąć przycisk funkcyjny fx – dodać krok
niestandardowy i wkleić (Ctrl+V lub kliknąć prawym przyciskiem myszy i z
podręcznego menu wybrać polecenie Wklej) w pasek formuły skopiowaną wcześniej
formułę z kroku Posortowano Asc.
Musimy zmienić część wklejonej formuły, bo chcemy aby kolumna
Czas była posortowana malejąco. Najprościej wystarczyło by zmienić rodzaj
sortowania klikając na znaczek sortowania przy nazwie kolumny Czas (rys. nr 12).
rys. nr 12 — Sortuj malejąco po kolumnie Czas
Dla bardziej zaawansowanych użytkowników jest inny sposób.
Wystarczy zmienić w formule dla kolumny czas sortowanie z Ascending na Descending,
co zostało pokazane na rysunku nr 13.
rys. nr 13 — Zmiana na pasku formuły
Oba sposoby zadziałają w ten sam sposób. Zmieniamy nazwę
kroku Niestandardowe 1 za pomocą klawisza F2 na Posortowano Desc. Musimy
pamiętać, że Power Query ma problem z tym, że dane nie zostały wczytane od
nowa, pamięta wcześniejszy bufor więc jak w poprzednim poście musimy dodać
kolumnę indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu
(punkt nr 2 na rysunku nr 14) a następnie Od 0 (punkt nr 3 na rysunku nr 14).
Nie ma znaczenia jaki typ kolumny indeksu wybierzemy bo jest to kolumna
pomocnicza i w późniejszym etapie ją usuniemy.
rys. nr 14 — Kolumna indeksu
Power Query dzięki dodaniu dodatkowej kolumny od nowa zaczyta
dane i zapamięta kolejność z odwrotnym sortowaniem. Zaznaczamy kolumny Data i
Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze a następnie
Usuń duplikaty (analogicznie jak na rysunku nr 9 dla pierwszych wierszy).
Otrzymamy ostatnie wiersze dla unikatowych wartości przedstawione na rysunku nr 15.
rys. nr 15 — Ostatnie wiersze dla unikatowych wartości
Zaznaczamy kolumnę Czas i Indeks i usuwamy za pomocą polecenia Usuń kolumny z podręcznego menu (rys. nr 16).
rys. nr 16 — Usuń kolumny
W
poprzednim poście te same wyniki otrzymaliśmy w dwóch osobnych zapytaniach. W
tym poście otrzymaliśmy takie same wyniki w jednym zapytaniu tylko w innych
krokach. Pierwsze wiersze są w kroku Pierwsze, a ostatnie wiersze są w kroku
Usunięto kolumny. Zmienimy jeszcze nazwę ostatniego kroku za pomocą klawisza F2
na Ostatnie (rys. nr 17). Teraz wiemy, które kroki chcemy połączyć.
rys. nr 17 — Kroki które trzeba połączyć
Z karty Narzędzia główne wybieramy polecenie Połącz (punkt nr
2 na rysunku nr 18), a następnie Scal zapytania (punkt nr 3 na rysunku nr 18).
rys. nr 18 — Scal zapytania
Otworzy nam się okno Scalanie. Scalać będziemy to samo
zapytanie, czyli wybierzemy tNotowania i w drugim okienku tNotowania (bieżący).
Zaznaczamy w obu zapytaniach kolumny Data i Firma. One są na razie identyczne.
W kolejnym kroku zrobimy pewną zmianę. Zatwierdzamy nasz wybór przyciskiem OK
(rys. nr 19).
rys. nr 19 — Okno scalanie
W tym kroku bardzo ważne jest, że widzimy pasek formuły, bo
mamy tam podane, na którym kroku wykonujemy dane zadanie (rys. nr 20).
rys. nr 20 — Pasek formuły
W naszej formule powinniśmy zmienić pierwsze wystąpienie
słowa Ostanie na słowo Pierwsze (nazwy kroków zapytania). Aby otrzymać
prawidłowe dane formuła na pasku formuły powinna wyglądać jak na rysunku nr 21.
rys. nr 21 — Zmiana nazwy kroku w formule
W formule powyżej mamy informację, że chcemy połączyć dane z
dwóch kroków w tym samym zapytaniu – krok Pierwsze i krok Ostatnie.
Z naszej tabeli musimy się pozbyć kolumny Czas za pomocą
polecenie Usuń kolumnę z podręcznego menu. Otrzymamy dane przedstawione na
rysunku nr 22.
rys. nr 22 — Usuń kolumnę
Następnie rozwijamy kolumnę Ostatnie (strzałki w prawym roku
nazwy kolumny), odznaczamy Datę i Firmę, a zostawiamy tylko Cenę. Nie chcemy
używać oryginalnej nazwy kolumny jako prefiksu i na koniec zatwierdzamy nasze
ustawienia przyciskiem OK (rys. nr 23).
rys. nr 23
W otrzymanych danych zmienimy nazwę dwóch ostatnich kolumn z
Cenami na Pierwsze i Ostatnie. Otrzymamy końcowe dane dla pierwszych i
ostatnich wierszy unikatowych wartości przedstawione na rysunku nr 24.
rys. nr 24 — Pierwsze i ostatnie wiersze dla unikatowych wartości
Są to dane uzyskane w jednym zapytaniu, włącznie ze
scalaniem. Był to wstęp do zagłębiania się w język M, aby poznać lepiej formuły
i możliwości jakie nam dają. Ostatnim krokiem jest załadowanie danych do Excela
za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu otworzy nam się okno Ładowanie do, gdzie wybieramy
sposób wyświetlania danych jako Tabela i miejsce załadowania danych jako
Istniejący arkusz (konkretną komórkę). Nasz wybór zatwierdzamy przyciskiem
Załaduj (rys. nr 26).
rys. nr 26 — Okno Ładowanie do
Dane wklejone do Excela prezentują się jak na rysunku nr 27.
Dane wklejone do Excela
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.
