W tym poście
omówimy temat kolorowania składni języka Power Query, języka M. Edytor
zawansowany nie do końca taki jest, ponieważ pokazuje jedynie tekst, nic nie
koloruje, nic nie podpowiada (rys. nr 1).
rys. nr 2 — dodatek do Notepada ++ kolorujący kod M
Od wpisu
Matta Massona minęło już trochę czasu i pojawiły się ciekawsze informacje, Lars
Schreiber opisał dokładnie jak zdefiniować własny język w Notepadzie ++ (rys.nr
3). Podał odpowiednie linki do listy funkcji oraz dodatkowy plik, który
podpowiada strukturę funkcji.
rys. nr 3 — strona www Larsa Schreiber, który opisał jak zdefiniować własny język w Notepadzie ++
Przechodzimy
teraz do Notepada, gdzie mamy pokazany ten sam kod co w Edytorze zaawansowanym
Power Query, ale już pokolorowany (rys. nr 4).
rys. nr 4 — kolorowy kod M w notepadzie ++
Notepad sam
rozpoznaje, że ma wpisane funkcje i koloruje je w odpowiedni sposób. Co istotne
Power Query jest case sensitive, czyli zwraca uwagę na wielkość liter. Dzięki
temu, że Notepad również ma taką funkcję (case sensitive) od razu możemy
zobaczyć, że mamy błąd w zapisie (błąd zaznaczony strzałką na rys. nr 5).
rys. nr 5 — Notepad ++ jest case sensitive — pokazuje błędy
Ponadto
Notepad podpowiada poprawne nazwy funkcji rozpoczynające się od wpisanych
znaków (rys. nr 6). Następnie strzałkami w górę i w dół możemy wybrać
odpowiednia funkcję, bez wpisywania jej całej nazwy.
rys. nr 6 — Notepadd pokazuje możliwe nazwy funkcji rozpoczynające się od podanych znaków
Nie dość że
Notepad podpowiada nazwy funkcji, to wpisaniu nawiasów możemy tak zaprogramować
program, że będzie podpowiadał nam strukturę danej funkcji (rys. nr 7).
rys. nr 7 — Struktura wpisanej funkcji
Notepad
podpowiada nam nazwy argumentów oraz po wpisaniu części danych podpowiada, w którym
argumencie w danym momencie jesteśmy (prawie tak jak w Excelu, ponieważ
argumenty funkcji rozdzielamy przecinkiem).
Podsumowując
dodatki do Notepada ++ sprawiają, że jest kod w nim wpisany jest bardziej
czytelny i łatwiejszy do zrozumienia niż w Edytorze zaawansowanym Power Query.
Jest tylko jeden minus, mianowicie wszystko jest w języku angielskim (funkcje,
ich argumenty i opisy).
Przy takim
kolorowym zapisie jest łatwiej zanalizować kod napisany przez inną osobę. W
kolejnym odcinku nauczymy się napisać taki kod samodzielnie. Dzięki kolorowaniu
kodu widzimy składnie kodu i łatwo możemy zrozumieć, jakie kroki wykonał twórca
danego kodu. Możemy również w łatwy sposób znaleźć błędy w takim kodzie,
ponieważ np. błędnie wpisana funkcja będzie miała inny kolor niż ta wpisana
poprawnie.
Po
stworzeniu kodu wystarczy go skopiować za pomocą skrótu klawiszowego Ctrl+C, a
następnie wkleić go do Edytora zaawansowanego za pomocą skrótu Ctrl+V. Po
wklejeniu kodu stracimy jego kolory, ale będziemy mieli pewność, że zapis jest
prawidłowy i formuła będzie działać. Wpisany kod zatwierdzamy przyciskiem
Gotowe (rys. nr 8).
rys. nr 8 — Kolorowy kod skopiowany do Edytora zaawansowanego staje się czarno-biały
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 policzyć wszystkie komórki zawierające
konkretny tekst. Naszym zadaniem będzie policzenie ile ciasteczek było w
paczkach, oraz wskazanie wszystkich możliwych par.
W Power
Query odcinku 43 obliczaliśmy ile produktów w paczkach sprzedał konkretny
sprzedawca. Pojawiał się tam taki problem, że mieliśmy np. czterech sprzedawców
a tylko trzech z nich sprzedało produkt ciasteczka. W wyniku naszych działań
pojawiali się tylko sprzedawcy, którzy dany produkt sprzedali (rys. nr 1).
rys. nr 1 — Dane ze sprzedawcami i produktami
Naszym
zadaniem będzie otrzymanie wyniku, w którym Power Query będzie pokazywał każdą
możliwą parę sprzedawca – produkt, również „puste” pary jak na rys. nr 2.
rys. nr 2 — zestawienie danych ze sprzedawcami, którzy nie sprzedali danego produktu
W odcinku
Power Query nr 44 omawialiśmy iloczyn kartezjański, który moglibyśmy wykonać
ręcznie, chociaż jest to bardzo czaso- i pracochłonne przy większej ilości
danych. Zależy nam, aby Power Query sam wyznaczył pary sprzedawca- produkt oraz
dodatkowo podliczył ile poszczególnych produktów sprzedał każdy sprzedawca.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 3.
rys. nr 3 — Przykładowe dane
W pierwszym
kroku musimy zaczytać nasze przykładowe dane do Power Query. W tym celu
wybieramy polecenie Z tabeli/zakresu z karty Dane (rys. nr 4).
rys. nr 4 — polecenie zaczytania danych z tabeli/zakresu
Otworzy nam
się Edytor zapytań z wczytaną tabelą tPaczki. Do dalszej pracy musimy
zduplikować nasze zapytanie. Na tych zapytaniach wykonamy różne operacje,
których wyniki odpowiednio ze sobą połączymy. Będą nam potrzebne takie dane jak
unikatowa lista Produktów, Sprzedawców oraz podsumowane wartości sprzedaży.
Musimy wyznaczyć ilość konkretnych produktów sprzedanych przez każdego
sprzedawcę. Będziemy potrzebować 3 zapytań z naszymi danymi bazowymi. W panelu
bocznym z listą zapytań klikamy na nazwę zapytania tPaczki prawym przyciskiem
myszy i z podręcznego menu wybieramy polecenie Duplikuj (zaznaczone zieloną
strzałką na rys. nr 5). Czynność tę powtarzamy, aby otrzymać trzy jednakowe
zapytania.
rys. nr 5 — polecenie Duplikuj zapytanie
Zaczniemy od
grupowania. W tym celu klikamy na zapytanie tPaczki(3) i w panelu bocznym
Ustawień zapytania po prawej stronie ekranu zmieniamy jego nazwę na tGrupowanie
(rys. nr 6).
rys. nr 6 — Zmiana nazwy zapytania w Ustawieniach zapytania
Nasze dane
pogrupujemy po sprzedawcach i produktach, w tym celu musimy najpierw rozdzielić
paczki na poszczególne produkty. Zaznaczamy kolumnę Paczki i rozwijamy
polecenie Podziel kolumny (punkt 2 na rys. nr 7) z karty Narzędzia główne
(punkt 1), a następnie wybieramy polecenie Według ogranicznika (punkt 3).
Należy pamiętać, że nasze dane chcemy rozdzielić na wiersze.
rys. nr 7 — polecenie Podziel kolumny Według ogranicznika
Otworzy nam
się okno Dzielenia kolumny według ogranicznika, gdzie w polu Wybierz lub
wprowadź ogranicznik wybieramy typ –Niestandardowe–, a następnie w polu
poniżej wpisujemy nasz ogranicznik, czyli przecinek i spacja. Rozwijamy opcje
zaawansowane, w których w polu Podziel na, zaznaczamy checkbox Wiersze. Tak
ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 8).
rys. nr 8 — Okno Dzielenia kolumny według ogranicznika
Otrzymamy
podzielone dane przedstawione na rys. nr 9.
rys. nr 9 — Dane podzielone według ogranicznika
Podzielone
dane chcemy pogrupować według sprzedawcy, aby mieć jasne wyniki ile dany
sprzedawca sprzedał produktów. Zaznaczamy kolumny Sprzedawca oraz Paczka,
następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys.
nr 10).
rys. nr 10 — Polecenie Grupowanie według
Otworzy nam
się okno Grupowania według, gdzie możemy pozostać przy domyślnych ustawieniach,
ponieważ naszym celem jest zliczenie produktów. Domyślne parametry zatwierdzamy
przyciskiem OK (rys. nr 11).
rys. nr 11 — Okno Grupowania według
Otrzymamy
pogrupowane dane przedstawione na rys. nr 12.
rys. nr 12 — Pogrupowane dane
Teraz
przechodzimy do zapytania tPaczki (2), któremu zmieniamy nazwę na tProdukty w
Ustawieniach zapytania. Naszym zadaniem w tym zapytaniu jest podzielenie paczek
na poszczególne produkty. Inne kolumny są zbędne. Zaznaczamy kolumnę Paczki, a
następnie klikamy prawym przyciskiem myszy na jej obszarze i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — polecenie Usuń inne kolumny
Otrzymamy
kolumnę Paczki przedstawioną na rys. nr 14.
rys. nr 14 — dane po usunięciu niepotrzebnych kolumn
Następnie
powyższą kolumnę musimy podzielić według ogranicznika. Rozwijamy polecenie
Podziel kolumny z karty Narzędzia główne, a następnie wybieramy polecenie
Według ogranicznika (tak jak na rys. nr 7).
Otworzy nam
się okno Dzielenia kolumny według ogranicznika, w którym ustawiamy takie same
parametry jak dla zapytania tGrupowanie (jak na rys. nr 8). Parametry te
zatwierdzamy przyciskiem OK.
Otrzymamy
listę wszystkich produktów z poszczególnych paczek przedstawioną na rys. nr 15.
rys. nr 15 — lista produktów z poszczególnych paczek
Naszym
zadaniem jest stworzenie unikatowej listy produktów, więc musimy usunąć
powtarzające się dane. Zaznaczamy kolumnę, rozwijamy polecenie Usuń wiersze
(punkt 2 na rys. nr 16) z karty Narzędzia główne (punkt 1), a następnie
wybieramy polecenie Usuń duplikaty (punkt 3).
rys. nr 16 — polecenie Usuń duplikaty
Ponadto
posortujemy sobie naszą listę alfabetycznie od A do Z za pomocą polecenia na
karcie Narzędzia główne (zaznaczone strzałką na rys. nr 17).
rys. nr 17 — Sortowanie danych od A do Z
Otrzymamy posortowaną
unikatową listę produktów przedstawioną na rys. nr 18.
rys. nr 18 — posortowana unikatowa lista produktów
Ponadto
zmienimy nazwę naszej kolumny na Produkty (rys. nr 19).
rys. nr 19 — Zmiana nazwy kolumny
Teraz
przechodzimy do naszego zapytania głównego tPaczki. Zmieniamy nazwę zapytania w
Ustawieniach zapytania na tPary (rys. nr 20).
rys. nr 20 — Zmiana nazwy zapytania w Ustawieniach zapytania
W pierwszym
etapie chcemy uzyskać unikatową listę sprzedawców. Zaznaczamy kolumnę
Sprzedawca, klikamy prawym przyciskiem myszy na obszarze kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 21).
rys. nr 21 — polecenie Usuń inne kolumny
Otrzymamy
kolumnę Sprzedawca, z której chcemy usunąć powtarzających się sprzedawców.
Rozwijamy polecenie Usuń wiersze z karty Narzędzia główne, a następnie
wybieramy polecenie Usuń duplikaty (tak jak na rys. nr 16).
Uzyskaną
listę sortujemy od A do Z za pomocą polecenia z karty Narzędzia główne (jak na
rys. nr 17).
Otrzymamy
posortowaną unikatową listę sprzedawców przedstawioną na rys nr 22.
rys. nr 22 — posortowana unikatowa lista sprzedawców
Na tym
etapie do listy sprzedawców z zapytania tPary chcemy dołożyć listę produktór z
zapytania tProdukty. Kopiujemy nazwę zapytania tProdukty za pomocą skrótu
klawiszowego Ctrl+C. Przechodzimy do zapytania tPary (ważne które zapytanie
jest aktywne) i wybieramy polecenie Kolumna niestandardowa z karty Dodaj
kolumnę (rys. nr 23).
rys. nr 23 — polecenie Kolumna niestandardowa z karty Dodaj kolumnę
Otworzy nam
się okno Kolumny niestandardowej, gdzie w polu Nazwa nowej kolumny wpisujemy
Produkty oraz w polu Formuła kolumny niestandardowej wklejamy skopiowaną nazwę
drugiego zapytania za pomocą skrótu klawiszowego Ctrl+V. Zapis powinien
wyglądać następująco: =tProdukty (w polu formuły robimy odwołanie do innego zapytania
– zaznaczone strzałką na rys. nr 24). Tak ustawione parametry zatwierdzamy
przyciskiem OK.
rys. nr 24 — parametry kolumny niestandardowej
Otrzymamy
pary sprzedawca – produkty. W kolumnie Produkty są tabele z listą unikatowych
produktów (rys. nr 25).
rys. nr 25 — Pary sprzedawca — produkty
Aby rozwinąć
te listy produktów musimy kliknąć na ikonkę ze strzałkami przy nazwie kolumny
Produkty. Otworzy nam się okienko, gdzie zaznaczamy checkbox przy poleceniu
Rozwiń, a następnie odznaczamy checkbox przy poleceniu Użyj oryginalnej nazwy
kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 26).
rys. nr 26 — polecenie Rozwiń
Otrzymaliśmy
listę wszystkich możliwych par sprzedawca – produkt przedstawioną na rys. nr
27.
rys. nr 27 — lista wszystkich możliwych par sprzedawca — produkt
Zmieniła się
nazwa kolumny z produktami, aby ją zmienić rozwijamy pasek formuły i tam
wprowadzamy zmianę (zaznaczone strzałką na rys. nr 28).
rys. nr 28 — zmiana nazwy kolumny w pasku formuły
W zapytaniu
tPary otrzymaliśmy wszystkie możliwe kombinacje par między sprzedawcą a
produktem, natomiast w zapytaniu tGrupowanie mamy listę tych par, które
faktycznie istnieją. Z zapytania tGrupowanie będziemy chcieli skorzystać z
kolumny Liczność. W tym celu scalimy te zapytania.
Główne
zapytanie (tPary) musi być aktywne. Rozwijamy polecenie Scal zapytania (punkt 2
na rys. nr 29) z karty Narzędzia główne (punkt 1), a następnie wybieramy
polecenie Scal zapytania (punkt 3).
rys. nr 29 — droga dostępu do polecenia Scal zapytania
Otworzy nam
się okno Scalania, gdzie w drugim polu wybieramy zapytanie tGrupowanie.
Zaznaczamy kolumny Sprzedawca i Produkty z pierwszego zapytania przytrzymując
klawisz Ctrl a następnie te same kolumny z drugiego zapytania. Wybieramy rodzaj
sprzężenia jako Lewe zewnętrzne. Należy zwrócić uwagę, aby zaznaczanie kolumn
odbyło się w tej samej kolejności, czyli jeśli w pierwszym zapytaniu najpierw
zaznaczymy kolumnę Sprzedawca to w drugim musimy zrobić tak samo. Nazwy kolumn
nie muszą się pokrywać. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 30).
rys. nr 30 — Okno Scalania z ustawionymi parametrami
Otrzymaliśmy
listę wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z
zapytania tGrupowanie (rys. nr 31).
rys. nr 31 — lista wszystkich możliwych par sprzedawca – produkt oraz ilość tych produktów z zapytania tGrupowanie
Sprawdzimy
teraz co się dzieje w sytuacji sprzedawcy, który nie sprzedał żadnego produktu
z listy. Sprzedawca Robert nie sprzedał żadnych ciasteczek, więc jego tabela
jest pusta (rys. nr 32).
rys. nr 32 — pusta tabela dla sprzedawcy, który nie sprzedał danego produktu
Na tym
etapie musimy rozwinąć kolumnę tGrupowanie, czyli klikamy na ikonkę ze
strzałkami przy nazwie kolumny i zaznaczamy polecenie Rozwiń oraz kolumnę,
którą chcemy rozwinąć. Tak ustawione parametry zatwierdzamy przyciskiem OK
(rys. nr 33).
rys. nr 33 — Polecenie Rozwiń
Otrzymamy
listę sprzedawców, produktów oraz ilość sprzedanych produktów (rys. nr 34).
rys. nr 34 — rozwinięte dane
Chcemy
zmienić nazwę kolumny otrzymanej po rozwinięciu na Ilość, w tym celu zmieniamy
nazwę w pasku formuły. Pierwsze wystąpienie nazwy Liczność jest nazwą kolumny,
którą rozwijaliśmy, natomiast drugie wystąpienie – nazwą kolumny docelowej
(rys. nr 35).
rys. nr 35 — Zmiana nazwy kolumny w pasku formuły
Po zmianie
nazwy kolumny otrzymamy dane przedstawione na rys. nr 36.
rys. nr 36 — Dane po zmianie nazwy kolumny
Wartości
null w danych po załadowaniu danych do Excela przekształcą się w puste komórki.
Jeśli chcemy aby przekształciły się w wartość zero, to musimy przekształcić
naszą kolumnę. W tym celu rozwijamy ikonę polecenia Zamieniania (punkt 2 na
rys. nr 37) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Zamienianie
wartości (punkt 3).
rys. nr 37 — polecenie Zmienianie wartości
Otworzy nam
się okno Zamieniania wartości, gdzie w polu wartość do znalezienia wpisujemy
null, natomiast w polu Zamień na wpisujemy zero (0). Tak ustawione parametry
zatwierdzamy przyciskiem OK (rys. nr 38).
rys. nr 38 — okno Zamieniania wartości
Otrzymamy
zmienione dane przedstawione na rys. nr 39.
rys. nr 39 — dane ze zmienionymi wartościami null
Aby dane
były bardziej czytelne musimy je posortować za pomocą polecenia na karcie
Narzędzia główne. Najpierw sortujemy kolumnę Produkt a następnie kolumnę
Sprzedawca. Otrzymamy dane przedstawione na rys. nr 40.
rys. nr 40 — posortowane dane
Tak
przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie
Zamknij i załaduj do z karty Narzędzia główne (rys. nr 41).
rys. nr 41 — polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych. Tutaj pojawi się pewien problem, mianowicie stworzyliśmy
trzy zapytania, więc nie możemy ich wstawić w konkretną komórkę. Dlatego
wybieramy sposób wyświetlania danych w skoroszycie jako Utwórz tylko połączenie
(rys. nr 42).
rys. nr 42 — Okno Importowania danych
Wstawiliśmy
dane jako połączenie ponieważ interesuje nas tylko jedno ze stworzonych
zapytań. Klikamy prawym przyciskiem myszy na zapytanie tPary i z podręcznego
menu wybieramy polecenie Załaduj do (rys. nr 43).
rys. nr 43 — polecenie Załaduj do (ponieważ wstawiliśmy dane jako połączenie)
Otworzy nam
się okno Importowania danych, gdzie wybieramy rodzaj wyświetlania danych w
skoroszycie jako Tabela, a następnie wskazujemy konkretną komórkę w istniejącym
arkuszu, gdzie ma zostać wstawiona. Tak ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 44).
rys. nr 44 — ustawienia sposobu wstawienia danych z zapytania
Otrzymamy
wstawione dane do Excela przedstawione na rys. nr 45. Otrzymaliśmy listę par
sprzedawca – produkt, zawierającą każde możliwe połączenie.
rys. nr 45 — lista par sprzedawca – produkt, zawierającą każde możliwe połączenie
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 zrobimy wstęp do kolejnego odcinka, w którym w Power Query
będziemy uwzględniać sytuacje, w jakich dany sprzedawca nie sprzedawał
konkretnego produktu (ciasteczek). Chodzi nam o sprzedawcę Roberta, który nie
pokazywał się w rozwiązaniu z odcinka o Power Query nr 43 (rys. nr 1).
rys. nr 1 — rozwiązanie odcinka Power Query 43
W tym wideo
zaczynamy od iloczynu kartezjańskiego, czyli sparowania każdego elementu z
każdym (rys. nr 2).
rys. nr 2 — sparowanie danych — iloczyn kartezjański
Mamy dwie
listy rozwijane, jedna zawiera nazwy sprzedawców, a druga produkty. Naszym
zadaniem jest sparowanie, przypisanie do każdego ze sprzedawców z pierwszej
listy, wszystkich produktów z drugiej listy (rys. nr 3).
rys. nr 3 — zasada iloczyn kazrtezjańskiego
Ręcznie
możemy to zrobić kopiując sprzedawcę Jan za pomocą skrótu klawiszowego Ctrl+C
do komórki E1, następnie kopiujemy wszystkie produkty z kolumny Produkt do
komórki F1. Następnie kopiujemy nazwę sprzedawcy na odpowiednią ilość wierszy w
dół, aby każdy produkt miał przypisanego sprzedawcę (rys. nr 4).
rys. nr 4 — pary dla jednego sprzedawcy
Analogicznie postępujemy dla wszystkich sprzedawców, otrzymamy wtedy listę wszystkich sprzedawców i produktów (rys. nr 5).
rys. nr 5 — pary dla wszystkich sprzedawców
Taki sposób
tworzenia tych list jest czasochłonne. Szczególnie w sytuacji gdybyśmy mieli
większą ilość danych. Naszym zadaniem jest zautomatyzowanie tego procesu – żeby
zrobił to za nas Power Query.
Pierwszym
krokiem jest zaczytanie obu list (Sprzedawca i Produkt) do Power Query. W tym
celu ustawiamy aktywną komórkę na liście Produkt i wybieramy polecenie Z
tabeli/zakresu z karty Dane (rys. nr 6).
rys. nr 6 — Z tabeli/zakresu
Otworzy nam
się Edytor zapytań Power Query z wczytaną tabelą Produkt. Power Query
pozostawił domyślny typ danych, a nam zależy, żeby traktował nasze dane jako
tekst, więc rozwijamy ikonkę ABC123 przy tytule kolumny i wybieramy typ danych
Tekst (rys. nr 7).
rys. nr 7 — zmiana typu danych
Tak
przygotowane dane chcemy zaczytać do Excela jako połączenie. Wybieramy
polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 8).
rys. nr 8 — zamknij i załaduj do
W Excelu
otworzy nam się okno importowania danych, gdzie wybieramy sposób wyświetlania
danych jako Utwórz tylko połączenie, następnie ustawienie to zatwierdzamy
przyciskiem OK (rys. nr 9).
rys. nr 9 — Importowanie danych
Następnie
ustawiamy aktywną komórkę na liście Sprzedawca i ponownie wybieramy polecenie Z
tabeli/zakresu z karty Dane (jak na rys. nr 6).
Otworzy nam
się Edytor zapytań Power Query z wczytaną tabelą Sprzedawca (rys. nr 10). Co
istotne musimy zwrócić uwagę na nazwy naszych zapytań, czyli tProdukty oraz
tSprzedawcy.
rys. nr 10 — nazwy zapytań z danymi
Głównym
zapytaniem jest tSprzedawcy, ponieważ zapytanie tProdukty zaczytaliśmy tylko
jako połączenie.
Naszym celem
jest stworzenie iloczynu kartezjańskiego, czyli otrzymanie tabeli, gdzie
każdemu sprzedawcy będzie przyporządkowany każdy produkt.
Musimy
skopiować sobie nazwę zapytania tProdukty, klikamy na nazwę zapytania i za
pomocą klawisza F2, przychodzimy w tryb edycji. Następnie za pomocą skrótu
klawiszowego Ctrl+C kopiujemy nazwę zapytania (rys. nr 11).
rys. nr 11 — Tryb edycji nazwy zapytania
Wracamy do
zapytania tSprzedawcy i wybieramy polecenie Kolumna niestandardowa z karty
Dodaj kolumnę (rys. nr 12).
rys. nr 12 — kolumna niestandardowa
Otworzy nam
się okno Kolumny niestandardowej, gdzie zmieniamy nazwę nowej kolumny na
Produkty (punkt 1 na rys. nr 13). W polu Formuła kolumny niestandardowej
wklejamy skopiowaną nazwę zapytania tProdukty (punkt 2). Tak przygotowane
parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.
rys. nr 13 — okno kolumny niestandardowej
Otrzymamy
dane przedstawione na rys. nr 14.
rys. nr 14 — dane z nową kolumną
W każdej komórce
ze skrótem Table w kolumnie Produkty ukryta jest tabelka z danymi (rys. nr 15).
rys. nr 15 — dane ukryte pod nazwą table
Klikamy
ikonkę ze strzałkami po prawej stronie nazwy kolumny Produkty, a następnie
wybieramy polecenie Rozwiń oraz odznaczamy checkbox przy poleceniu Użyj
oryginalnej nazwy kolumny jako prefiksu. Tak ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 16).
rys. nr 16 — polecenie rozwiń
Otrzymamy
dane przedstawione na rys. nr 17.
rys. nr 17 — rozwinięte dane
W danych
tych każdy sprzedawca został powtórzony tyle razy, ile jest produktów. Tak
przygotowane dane możemy załadować do Excela. W tym celu wybieramy polecenie Zamknij
i załaduj do z karty Narzędzia główne (jak na rys. nr 8).
Otworzy nam
się okno Importowania danych, gdzie ustawiamy Sposób wyświetlania danych jako
Tabela, a następnie wybieramy miejsce wstawienia tych danych – Istniejący
arkusz i wskazujemy konkretną komórkę – w naszym przykładzie komórka H1. Tak
przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 18).
rys. nr 18 — importowanie danych
Otrzymamy
dane zaczytane do Excela przedstawione na rys. nr 19
rys. nr 19 — dane wstawione do Excela
Poprawność
działania formuły możemy sprawdzić, kiedy dołożymy innego sprzedawcę np. Michał
w tabeli Sprzedawca oraz produkt Mleko w tabeli Produkt, następnie klikniemy
prawym przyciskiem myszy na dowolną komórkę w danych otrzymanych z Power Query
i wybierzemy polecenie Odśwież (rys. nr 20).
rys. nr 20 — polecenie Odśwież dane
Otrzymamy
zaktualizowane dane przedstawione na rys. nr 21.
rys. nr 21 — zaktualizowane dane
Zmieniła nam
się ilość sprzedawców (5 osób) oraz ilość produktów (6 szt.), więc w wyniku
powinniśmy otrzymać 30 par (rys. nr 22).
rys. nr 22 — ilość wyników z Power Query
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 poznamy sposób jak policzyć
komórki zawierające konkretny tekst na przykładzie ilości paczek zawierających
ciasteczka. Przykładowe dane do tego zadania zostały przedstawione na rysunku
nr 1.
rys. nr 1 — Przykładowe dane
Rozwiązanie tego zadania w Excelu zostało omówione w pytaniu od widzów, w filmie Excel — Jak policzyć komórki zawierające konkretny tekst — Widzowie #89 (https://www.youtube.com/watch?v=LghJGhKo9Gw). Pytanie od widzów polegało na znalezieniu formuły, aby policzyć komórki, w których dany produkt wystąpił więcej niż jeden raz.
W Power Query rozwiązanie tego zadania jest proste. W pierwszym kroku musimy zaczytać nasze dane bazowe do Power Query , w tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli/zakresu
Otworzy nam się edytor
zapytań z wczytaną tabelą tPaczki2 (rys. nr 3).
rys. nr 3 — Edytor zapytań
Kolumna
Data jest nam niepotrzebna, więc możemy ją sobie usunąć. Zaznaczamy tą kolumnę
i klikamy prawym przyciskiem myszy na jej nazwę, następnie z podręcznego menu
wybieramy polecenie Usuń (rys. nr 4).
rys. nr 4 — Polecenie Usuń
Otrzymamy dane przedstawione
na rysunku nr 5, zawierające kolumnę z nazwą sprzedawcy oraz zawartością paczki
dla danego sprzedawcy.
rys. nr 5 — Dane po usunięciu kolumny
W Power Query zadanie wyznaczenia
ilości występowania produktu ciasteczka jest bardzo łatwe. Zaznaczamy kolumnę
Produkt, rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 6) z karty
Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt 3
na rys. nr 6).
rys. nr 6 — Według ogranicznika
Otworzy nam się okno Dzielenia kolumny według
ogranicznika, gdzie w polu Wybierz lub wprowadź ogranicznik z listy rozwijanej
musimy wybrać niestandardowe, a następnie w polu niżej wpisać nasz ogranicznik,
czyli przecinek i spację. Rozwijamy również opcje zaawansowane, aby podzielić
dane na Wiersze, ponieważ jest to idealne rozwiązanie dla naszego przykładu.
Tak przygotowane parametry podziału kolumny zatwierdzamy przyciskiem OK (rys.
nr 7).
rys. nr 7 — Dzielenie według ogranicznika
Otrzymamy dane przedstawione na rysunku nr 8. W tych
danych każda paczka zostaje podzielona na pojedyncze produkty.
rys. nr 8 — Podzielone dane
Power Query zmienił automatycznie typ danych, więc
usuwamy krok Zmieniono typ z Zastosowanych kroków.
Na tym etapie zaznaczamy obie kolumny, a następnie
wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 9).
rys. nr 9 — Grupowanie według
Otworzy nam się okno Grupowania według, gdzie Power Query
automatycznie wybrał zaawansowane grupowanie z obiema kolumnami. W polu nazwa
nowej kolumny wpisujemy Ilość, wybieramy operację Zlicz wiersze. Tak
przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 10).
rys. nr 10 — Parametry grupowania według
Otrzymamy dane przedstawione na rysunku nr 11.
rys. nr 11 — Pogrupowane dane
Otrzymaliśmy w kolumnie Ilość wynik grupowania, czyli ilość
wystąpienia w danych konkretnych produktów. Tak przygotowane dane wystarczy
załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia
główne (rys. nr 12).
rys. nr 12 — Zamknij i załaduj do
Otworzy nam się okno Importowania danych, gdzie musimy
określić sposób wyświetlania danych – tabela oraz miejsce ich wstawienia –
Istniejący arkusz i wskazać konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem OK (rys. nr 13).
rys. nr 13 — Importowanie danych
Otrzymamy dane w Excelu przedstawione na rysunku nr 14.
rys. nr 14 — dane zaimportowane do Excela
Kiedy posortujemy dane w kolumnie Produkt, możemy zauważyć,
że produkt ciasteczka występuje na liście tylko 3 razy (rys. nr 15).
rys. nr 15 — posortowane dane
Wynika to z tego, że jeden sprzedawca (Robert) nie sprzedał
żadnych ciasteczek. Grupowanie Power Query działa podobnie jak grupowanie w
tabelach przestawnych, czyli jeśli jakiegoś produktu jest zero, to nie pokaże
się on na liście.
W Power Query jest również możliwość wypisania wszystkich
sprzedawców wraz ze wszystkimi produktami, mimo że ich ilość wynosi 0, ale jest
to skomplikowane zadanie dla bardziej zaawansowanych użytkowników.
W dzisiejszym poście nauczymy się dodawać kolumny z wartością
null. Temat ten omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — przykładowe dane
W Power Query pojawia się problem kiedy zaczytujemy dane z
pustymi komórkami. Przykładowo w Excelu takiego problemu nie ma, kiedy dodamy
wszystkie wartości z danego wiersza otrzymamy prawidłowy wynik (dla wiersza 10
w wyniku otrzymamy wartość 0). Możemy to sprawdzić korzystając ze skrótu
klawiszowego Alt+= (suma po wierszach). Otrzymamy dane przedstawione na rysunku
nr 2. Kiedy mamy w danych pustą komórkę, Excel traktuje ją jako zero.
rys. nr 2 — pusta komórka traktowana jako zero
Dane z
rysunku nr 1 zaczytujemy do Power Query, czyli wybieramy polecenie Z
tabeli/zakresu z karty Dane (rys. nr 3).
rys. nr 3 — Z tabeli/zakresu
W Power Query pojawia się problem z pustymi komórkami, ponieważ
dodatek ten zwraca uwagę na typy danych.
Otworzy nam się edytor zapytań z wczytaną tabelą tSprzedaż.
Standardowo musimy zmienić format danych. Klikamy na ikonkę kalendarza przy
nazwie pierwszej kolumny i z podręcznego menu wybieramy format Data (rys. nr
4).
rys. nr 4 — zmiana typu danych
Pojawi nam się komunikat o zmianie typu kolumny, którym
musimy zatwierdzić przyciskiem Zamień bieżącą (rys. nr 5).
rys. nr 5 — Zmień typ kolumny
Naszym zadaniem w Power Query jest dodanie do siebie trzech
kolumn: Jabłek, Gruszek i Śliwek. Możemy to zrobić na różne sposoby. W
pierwszym przykładzie dodamy kolumnę niestandardową. Wybieramy polecenie
kolumna niestandardowa z karty Dodaj kolumnę (rys. nr 6).
rys. nr 6 — kolumna niestandardowa
Otworzy nam się okno Kolumny niestandardowej, w którym musimy
określić jej parametry. W polu nazwa nowej kolumny wpiszemy Suma, a następnie w
polu Formuła kolumny niestandardowej wpiszemy następującą formułę:
=[Jabłka]+[Gruszki]+[Śliwki]
Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys.
nr 7).
rys. nr 7 — okno kolumny niestandardowej
Otrzymamy dane przedstawione na rysunku nr 8, gdzie od razu
można zauważyć błędy. W każdym wierszu, w którym chodź raz pojawiła się wartość
null, otrzymaliśmy tę właśnie wartość w wyniku (rys. nr 8).
rys. nr 8 — dane z kolumną niestandardową
Taki wynik jest nie do zaakceptowania, ale możemy sobie z tym
poradzić. Usuwamy ostatni krok z Zastosowanych kroków, czyli stworzoną kolumnę
niestandardową.
Zaznaczamy tylko dwie kolumny (Gruszki i Jabłka), a następnie
rozwijamy polecenie Standardowy (punkt 2 na rys. nr 9) z karty Dodaj kolumnę i
wybieramy polecenie Dodaj (punkt 3 na rys. nr 9).
rys. nr 9 — polecenie Dodaj
Po raz kolejny z takiego dodawania w każdym wierszu, w którym
wartość null, otrzymujemy taką właśnie wartość (rys. nr 10). Zapis formuły
wygląda następująco:
=Table.AddColumn(#"Zmieniono typ",
"Dodawanie" each [Jabłka] + [Gruszki], Int64.Type)
rys. nr 10 — działanie polecenia Dodaj dla 2 kolumn
Tym razem zaznaczamy wszystkie trzy kolumny z produktami i
korzystamy z tego samego polecenia co poprzednio, czyli z polecenia Dodaj.
Otrzymujemy dane przedstawione na rysunku nr 11.
rys. nr 11 — działanie polecenia Dodaj dla 3 kolumn
Możemy łatwo zauważyć, że otrzymane wyniki są prawidłowe.
Wartość null otrzymaliśmy tylko w wierszu, w którym wszystkie wartości wynosiły
null.
Dzieje się tak dlatego, że zamiast zwykłego dodawania Power
Query skorzystał z jednej ze swoich funkcji – List.Sum (rys. nr 12).
rys. nr 12 funkcja List.Sum
Funkcja List.Sum sumuje listę elementów. Funkcja ta w inny
sposób zapisuje argumenty. Poprzednio nazwy kolumn były zapisane tylko w
nawiasach kwadratowych oraz dodane za pomocą znaku plus. W tej funkcji dane są
zapisane dodatkowo w nawiasach klamrowych, które oznaczają, że jest to lista
elementów, ponadto elementy są oddzielone od siebie przecinkami (rys. nr 13).
Dzięki temu Power Query radzi sobie z wartościami null.
rys. nr 13 — dodatkowe nawiasy klamrowe w zapisie funkcji List.Sum
Co ważne, przy dwóch dodawanych kolumnach, formuła zadziałała
jak zwykłe dodawanie (zapis ze znakami +). Nawet przy tak łatwych obliczeniach
w Power Query mogą pojawić się różnice, a tym samym błędne wyniki.
Możemy zmodyfikować formułę dla dodawania dwóch kolumn,
wstawić funkcję List.Sum, plusy zastąpić przecinkami oraz dołożyć nawiasy
klamrowe. Zapis formuły wyglądać będzie następująco:
=Table.AddColumn(#"Zmieniono typ",
"Dodawanie" each List.Sum({[Jabłka],[Gruszki]}), Int64.Type)
Otrzymamy prawidłowe wyniki dla dodawania dwóch kolumn
przedstawione na rysunku nr 14.
rys. nr 14 — Dodane dane
Podsumowując musimy pamiętać jak radzi sobie Power Query z
danymi, w których występują wartości null, aby nie popełnić błędów w
obliczeniach.
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.
