W
dzisiejszym poście omówimy jak za pomocą Power Query możemy wyciągnąć listę
wszystkich folderów, podfolderów wskazanego folderu. Rozwiązanie to jest
proste, ale ma jedną wadę, o której pomówimy na końcu. W celu wykonania zadania
rozwijamy polecenie Pobierzdane (punkt 2 na rys. nr 1) z karty Dane
(punkt 1), następnie rozwijamy polecenie Zpliku (punkt 3) i
wybieramy polecenie Zfolderu (punkt 4).
Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
Na potrzeby
naszego zadania przygotowałem katalog z większą ilością podfolderów o nazwie
WebinarPQ. Po użyciu polecenia z rys. nr 1 otworzy nam się okno Folder, gdzie musimy
wkleić ścieżkę dostępu do tego folderu lub przy użyciu przycisku Przeglądaj,
wyszukać folder ręcznie. Wklejoną ścieżkę do wybranego folderu zatwierdzamy
przyciskiem OK (rys. nr 2).
Rys. nr 2 – okno Folder
Wtedy Power
Query pobierze informacje o wszystkich plikach znajdujących się w folderze
głównym i jego pod folderach. Otworzy się okno z wybranej ścieżki dostępu,
gdzie wystarczy kliknąć przycisk Przekształć dane, aby przejść do ich
edycji (rys. nr 3). Dzięki drobnym przekształceniom danych otrzymamy listę
wszystkich elementów wybranego folderu.
Rys. nr 3 – okno z elementami wybranego folderu
Otworzy nam
się Edytor zapytań Power Query z wczytanymi danymi przedstawiony na rys. nr 4.
Rys. nr 4 – fragment danych z edytora zapytań Power Query
Dla nas
istotna jest tylko ostatnia kolumna o nazwie Folder Path, inne są zbędne
ponieważ zawierają ind=formacje na temat poszczególnych plików. Klikamy na
nazwę kolumny Folder Path prawym przyciskiem myszy i z podręcznego menu
wybieramy polecenie Usuń inne kolumny (rys. nr 5).
Rys. nr 5 – polecenie Usuń inne kolumny
Otrzymamy
listę wszystkich folderów i pod folderów zawartych w katalogu WebinarPQ
przedstawioną na rys. nr 6.
Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ
Rys. nr 7 – polecenie Usuń duplikaty Jak łatwo zauważyć poszczególne ścieżki się powtarzają, więc musimy je usunąć. Zaznaczamy kolumnę i rozwijamy polecenie Usuń wiersze a następnie wybieramy polecenie Usuń duplikaty (rys. nr 7).
Rys. nr 7 – polecenie Usuń duplikaty
Otrzymamy
listę ścieżek dostępu do wszystkich pod folderów wybranego katalogu
przedstawioną na rys. nr 8.
Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu
Jeśli
potrzebujemy jakieś dodatkowe dane to możemy dalej przekształcać te dane.
Naszym zadaniem było przygotowanie listy wszystkich pod folderów. Nasza lista
zawiera również główny folder, czyli WebinarPQ, możemy go usunąć rozwijając
podręcznego menu (przycisk z trójkącikiem przy nazwie kolumny), a następnie
odznaczając checkbox przy ścieżce dostępu do głównego katalogu. Zmiany w
filtrowaniu zatwierdzamy przyciskiem OK (rys. nr 9).
Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)
Otrzymamy
dane z listą ścieżek dostępu do pod folderów, natomiast bez ścieżki dostępu do
głównego katalogu (rys. nr 10).
Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ
Rys. nr 11 – polecenie Zamknij i załaduj doTak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 11).
Rys. nr 11 – polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako
tabela i wskazujemy miejsce ich wstawienia, czyli istniejący arkusz i konkretna
komórka. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem
OK (rys. nr 12).
Rys. nr 12 – okno Importowania danych
Otrzymamy
dane zaimportowane do Excela i przedstawione na rys. nr 13.
Rys. nr 13 – Dane zaimportowane z Power Query
Jak wspominaliśmy
na początku, rozwiązanie to jest proste, lecz ma jedną wadę. Mianowicie jeśli
podejrzymy pliki w folderze WebinarPQ, widzimy że mamy dodatkowy folder o
nazwie Nowy folder, który nie pojawił się na naszej liście (rys. nr 14). Dzieje
się tak dlatego, że folder ten jest pusty.
Rys. nr 14 – pod foldery katalogu WebinarPQ
Pusty folder
nie pojawił się na liście otrzymanej przy użyciu funkcji, która wyciąga
informacje o wszystkich plikach znajdujących się we wskazanym folderze,
ponieważ nie zawierał żadnych plików. Podsumowując przedstawiony sposób działa
poprawnie w 90%, ale musimy pamiętać o pustych folderach.
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 będziemy kontynuować rozwiązywanie zadań z Matury z
informatyki 2018. Dziś rozwiążemy zadanie nr 5.4 w Power Query. Rozwiązanie
tego zadania pomogł mi znaleźć Bill Szysz. Był on w stanie rozwiązać całe
zadanie w jednym zapytaniu, lecz okazało się to bardzo skomplikowane. W jego
rozwiązaniu część zadania wykonano w Power Query a wyciągnięcie samych wyników
nastąpiło w Excelu. Nam zależy aby całe zadanie rozwiązać w Power Query.
Rozbijemy zadanie na kilka zapytań, aby łatwiej znaleźć rozwiązanie.
Zadanie nr
5.4 opiera się o cykl pracy zbiornika retencyjnego. W pierwszym kroku zaczytamy
dane z tabeli w Excelu. Wybieramy polecenie Z tabeli/ zakresu z karty Dane
(rys. nr 1).
rys. nr 1 — polecenie Z tabeli/zakresu
Otworzy nam
się Edytor zapytań Power Query z wczytanymi danymi z tabeli przedstawiony na
rys. nr 2.
rys. nr 2 — Edytor zapytań Power Query z wczytanymi danymi
Dane te
musimy odpowiednio przekształcić. W pierwszym kroku musimy zmienić typ danych,
możemy to zrobić usuwając słowo time z formuły w pasku formuły (zaznaczone na
niebiesko na rys. nr 3). Power Query automatycznie wybrał typ danych data i
czas, a nam czas nie jest potrzebny, interesuje nas tylko data.
rys. nr 3 — Zmiana w formule — usunięcie czasu z daty
Otrzymamy
dane ze zmienionym typem danych w kolumnie Data przedstawione na rys. nr 4.
rys. nr 4 — zmieniony typ danych w kolumnie Data
Nas interesuje tylko kolumna Woda m3. Klikamy prawym przyciskiem myszy na tytuł kolumny Woda m3 i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).
rys. nr 5 — polecenie Usuń inne kolumny
Jeśli
skorzystamy z powyższego polecenia Power Query użyje funkcji
Table.SelectColumn, która zwraca nam tabelę. Nasze zapytanie nazywa się tDane6,
zmienimy jego nazwę w Ustawieniach zapytania na tWoda (rys. nr 6).
rys. nr 6 — zmiana nazwy zapytania w Ustawieniach zapytania
Ikonka przy
nazwie zapytania oznaczona strzałką na rys. nr 7 wskazuje nam, że nasze dane
mają formę tabeli.
rys. nr 7 — dane w formie tabeli
My nie
potrzebujemy tabeli, więc musimy zmienić użytą formułę, czyli zamiast funkcji Table.SelectColumns
powinniśmy użyć funkcji Table.Column. Zapis formuły powinien wyglądać
następująco:
=Table.Column
(#"Zmieniono typ", "Woda m3")
Otrzymamy
dane w postaci listy przedstawione na rys. nr 8.
rys. nr 8 — dane w postaci listy
Aby mieć
pewność, że ta lista się zmieni, tzn. nie zmieni się kolejność elementów musimy
dołożyć dodatkową funkcję o nazwie List.Buffer. Zapis formuły powinien
wyglądać następująco:
Funkcja List.Buffer
sprawia, że nasza lista nie ulegnie zmianie, tzn. że w tej liście mamy ilość
wody wpływającej do zbiornika każdego kolejnego dnia i kolejność wpływów wody
nie może ulec zmianie. Aby mieć jasne informacje, że dany krok odpowiada za
przekształcenie danych z tabeli w listę, zmieniamy sobie nazwę kroku Usunięto
inne kolumny na LWoda w Ustawieniach zapytania za pomocą klawisza F2 (rys. nr 9).
rys. nr 9 — zmiana nazwy kroku
Zmieniamy
nazwę kroku, ponieważ w późniejszym etapie będziemy się do niego odwoływać.
Kolejnym
krokiem jest policzenie ilości wierszy w danych. Ze względu na to iż
zmieniliśmy typ danych (nasze dane już nie są tabelą), nie możemy korzystać z
operacji przekształcania danych na karcie Przekształć. Musimy dodać kolejny
krok za pomocą ikonki fx przy pasku formuły, ponadto ten krok musimy napisać
samodzielnie. Skorzystamy tutaj z funkcji Table.RowCount, pamiętając o
użyciu odpowiedniej wielkości liter, ponieważ Power Query jest case sensitive.
W funkcji tej musimy się odwołać do kroku, w którym mamy jeszcze cała tabelę,
czyli u nas do kroku Zmieniono typ. Klikamy na nazwę kroku, przechodzimy do
edycji nazwy kroku za pomocą klawisza F2 a następnie kopiujemy jego nazwę za
pomocą skrótu klawiszowego Ctrl+C. Należy pamiętać, aby przed wklejeniem nazwy
kroku wpisać znak hashtag (#) ponieważ w nazwie kroku występuje spacja. Zapis
formuły powinien wyglądać następująco:
=Table.RowCount
(#"Zmieniono typ")
Otrzymamy
ilość wierszy w naszych danych przedstawioną na rys. nr 10.
rys. nr 10 — ilość wierszy w danych
Ilość wierszy będzie nam potrzebna do następnego, najtrudniejszego kroku, gdzie za pomocą funkcji List.Generate wyznaczymy listę rekordów. Funkcja ta ma 4 argumenty, więcej szczegółów na temat funkcji przedstawia rys. nr 11.
rys. nr 11 — informacje o funkcji List.Generate
Pierwszym argumentem
są wartości początkowe, czyli wartość z pierwszego wiersza, drugi argument to
warunek, przy którym zatrzymujemy generowanie listy, a trzeci to sposób w jaki
przekształcimy dane aby otrzymać kolejne wartości. Działanie funkcji wyjaśnimy
na prostym przykładzie przedstawionym na rys. nr 12.
rys. nr 12 — przykładowe argumenty funkcji List.Generate
Zaczynami
liczenie od wartości 10, czyli początkową wartością jest liczba 10. Pierwszy
warunek jest taki, że wykonujemy obliczenia dopóki ta wartość jest większa od
0, drugi warunek to odjęcie wartości 1 na każdym kroku. Taka formuła funkcji
generuje listę wartości od 10 do 1 (rys. nr 13).
rys. nr 13 — wynik działania przykładowej funkcji z rysunku powyżej
W naszym
zadaniu będziemy korzystać z dużo bardziej skomplikowanej formuły, ponieważ
musimy wygenerować dużo więcej danych, które muszą spełnić określone warunki
(rys. nr 14).
rys. nr 14 — zapis funkcji do zadania 5.4
Pierwsze
dwie linijki formuły z rysunku nr 14 to pierwszy argument funkcji, czyli
wartości początkowe. Zapis w nawiasach kwadratowych oznacza, że mamy do
czynienia z rekordem. Odwołujemy się do poszczególnych kolumn, najpierw do
kolumny Data (początkowa data to 31.12.2007), której odpowiada wartość z
kolumny Woda m3, czyli wartość 0 dla tej daty. Stan= 500000 to stan wody w
zbiorniku na początku obliczeń. W danych do zadania mamy informację, że należy
wylać ze zbiornika 2% stanu wody z dnia mierzenia, stąd wartość Wylać=10000. Są
to podstawowe kolumny, których potrzebujemy. Druga linijka (wartości _2) to
informacje do rozwiązania trzeciego podpunktu zadania 5.4 z matury z
informatyki. Wartość i jest wartością pomocniczą, zliczającą w którym aktualnie
jesteśmy wierszu. Zapis formuły powinien wyglądać następująco:
Musimy zmienić nazwę kroku Niestandardowe 1, w którym obliczaliśmy ilość wierszy, na RowCount (Ustawienia zapytania) – rys. nr 15.
rys. nr 15 — zmiana nazwy kroku
Jest to
warunek, że tworzymy listę dopóki nie wygenerujemy wszystkich wartości z kroku
RowCount, czyli wszystkich dni. Ma zostać spełniony warunek, że i jest mniejsze
od ilości wierszy +1. Jest to nasz drugi argument funkcji. Warunek powinien
wyglądać następująco:
each [i]
< RowCount+1
Trzeci
argument funkcji obejmuje wszystkie obliczenia jakie musimy wykonać w zadaniu.
W kolejnym kroku wartość z kolumny Date jest równa wartości z kolumny Date z
poprzedniego wiersza powiększona o 1. Korzystamy tutaj z funkcji Date.AddDays,
czyli dodajemy dni do wiersza powyżej. Wartość w kolumnie Woda m3 jest równa
wartości z kroku LWoda dla określonego wiersza i. Stan wody jest równy stanowi
wody z wcześniejszego kroku pomniejszony o ilość wody, którą należało wylać
oraz powiększony o ilość dowy jaka włynęła w danym dniu (Woda m3).
Warunek
ilości wylewanej wody polega na tym, że jeśli stan zbiornika przekracza 1000000 m³ wody to trzeba wylać tą nadwyżkę (ilość która przekracza 1000000 m³), a
następnego dnia rano wylać dodatkowo 2% stanu w momencie pomiaru. W przeciwnej
sytuacji, czyli kiedy zbiornik nie jest przepełniony to należy wylać tylko 2%
stanu zbiornika z momentu pomiaru. Wartości te mają być zaokrąglone w górę,
czyli użyjemy tutaj funkcji Number.RoundUp.
Podobne
obliczenia wykonamy dla Stanu_2, gdzie nie wylewamy wody jeśli przekroczymy
1000000 m³ wody w zbiorniku, ale zawsze wylewamy te 2% stanu z czasu pomiaru.
Cały drugi warunek powinien wyglądać następująco:
each [
Date=Date.AddDays ([Date], 1),
# "Woda m3" = LWoda { [i]
},
Stan = [Stan] – [Wylać] + #
"Woda m3",
Wylać = if Stan > 1000000 then (Stan – 1000000) + Number.RoundUp (0,02* ([Stan] – [Wylać] + # "Woda m3")) else Number.RoundUp (0,02* ([Stan] – [Wylać] + # "Woda m3")),
Całą formułę
przedstawioną na rys. nr 14 zaznaczamy i kopiujemy za pomocą skrótu
klawiszowego Ctrl+C, a następnie w Power Query dodajemy nowy krok za pomocą
ikonki fx i wklejamy skopiowaną formułę do paska formuły za pomocą skrótu
klawiszowego Ctrl+V. Zatwierdzamy przyciskiem Enter i otrzymamy dane
przedstawione na rys. nr 16.
rys. nr 16 lista rekordów
Otrzymaliśmy
listę rekordów, zawierającą informacje o dacie, stanie wody danego dnia, ilości
wody jaką musimy wylać itd., przedstawioną na rys. nr 17
rys. nr 17 — każdy rekord zawiera szczegółowe dane
W kolejnym
kroku musimy przekształcić nasze dane z listy na tabelę, skorzystamy tutaj z
polecenia Do tabeli z karty Przekształć (rys. nr 18).
rys. nr 18 — konwertowanie listy do tabeli
Otworzy nam
się okno Do tabeli, gdzie musimy wybrać ogranicznik, jakim mają być oddzielone
dane (wybieramy Brak) oraz w polu Sposób obsługi dodatkowych kolumn wybieramy
opcję Pokaż jako błędy. Tak ustawione parametry konwertowania danych do tabeli
zatwierdzamy przyciskiem OK (rys. nr 19).
rys. nr 19 — okno Do tabeli — parametry konwertowania
Otrzymamy
listę rekordów przekonwertowaną na tabelę co widać na rys. nr 20.
rys. nr 20 — tabela z rekordami
Nasze
rekordy dalej wyglądają po rozwinięciu tak samo jak dla listy ale tym razem
zapisane są w formie tabeli. Dzięki zmianie typu danych na tabelę możemy
rozwinąć te dane. Klikamy na ikonkę ze strzałkami obok nazwy kolumny Column1,
zaznaczamy wszystkie kolumny (oprócz kolumny i, która była tylko kolumną
pomocniczą) i odznaczamy opcję Użyj oryginalnej nazwy kolumny jako prefiksu,
następnie zatwierdzamy rozwijanie danych przyciskiem OK (rys. nr 21).
rys. nr 21 — rozwijanie rekordów tabeli
Otrzymamy
rozwinięte dane w postaci tabeli przedstawione na rys. nr 22.
rys. nr 22 — rozwinięte dane w tabeli
Kolumny Stan
i Wylać są nam potrzebne do rozwiązania podpunktu a i b z zadania a kolumny
Stan_2 i Wylać_2 do rozwiązania podpunktu c. Ostatnim etapem pracy nad danymi w
Power Query jest dopasowanie typu danych. Kolumna Date powinna być typu Data, a
pozostałe kolumny typu Liczba całkowita (rys. nr 23).
rys. nr 23 — zmiana typu danych w poszczególnych kolumnach
Dla każdej
części zadania zrobimy sobie osobne zapytanie, bo uda nam się wykorzystać
proste polecenia ze wstążki. Tak przygotowane dane możemy załadować do Excela,
korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne
(rys. nr 24).
rys. nr 24 — polecenie Zamknij i załaduj do
W Excelu
otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania
danych jako Tabela oraz wskazujemy miejsce wstawienia danych – konkretna
komórka w istniejącym arkuszu. Tak ustawione parametry wstawienia danych
zatwierdzamy przyciskiem OK (rys. nr 25).
rys. nr 25 — okno Importowania danych
Otrzymamy
dane zaczytane do Excela przedstawione na rys. nr 26.
rys. nr 26 — dane zaczytane do Excela
Mamy do
rozwiązania trzy części zadania przedstawione na rys. nr 27. Pierwszy podpunkt zadania
polega na wyznaczeniu dnia, w którym pierwszy raz wypuszczono nadmiar wody po
przepełnieniu.
rys. nr 27 — treść zadań do wykonania
Aby uzyskać
rozwiązanie w Power Query klikamy dwukrotnie na zapytanie tWoda w zakładce
Zapytania i połączenia, aby przejść do Edytora zapytań Power Query. Otworzy nam
się Edytor zapytań z naszymi danymi, gdzie klikamy prawym przyciskiem myszy na
nazwę zapytania tWoda i z podręcznego menu wybieramy polecenie Odwołanie
(rys. nr 28).
rys. nr 28 — polecenie Odwołanie
Powstanie
zapytanie tWoda (2), któremu zmieniamy nazwę na OdpA w Ustawieniach zapytania.
Nasze zadanie polega na znalezieniu daty, kiedy w zbiorniku było powyżej
1000000 m3 wody, czyli wystarczy nałożyć filtr po kolumnie Stan.
Rozwijamy menu klikając na ikonkę z trójkątem przy nazwie kolumny i rozwijamy
polecenie Filtry liczb, następnie wybieramy polecenie Większe niż
(rys. nr 29).
rys. nr 29 — ścieżka dostępu do polecenia Większe niż
Otworzy nam
się okienko Filtrowania wierszy, gdzie w polu jest większe niż wpisujemy
wartość od której chcemy otrzymać większą wartość (wpisujemy 1000000). Tak
ustawiony próg zatwierdzamy przyciskiem OK (rys. nr 30).
rys. nr 30 — okno Filtrowania wierszy
Otrzymamy przefiltrowane dane tylko z wierszami, w których stan wody przekraczał 1000000 m3 wody przedstawione na rys. nr 31 .
rys. nr 31 — przefiltrowane dane
Interesuje
nas pierwszy dzień, w którym ilość wody w zbiorniku przekroczyła 1000000 m3.
Z naszych danych wynika, że tym dniem jest 18.04.2019 ale musimy pamiętać że
pomiar danego dnia odbywa się tuż po północy, więc faktycznie jest to już
19.04.2019. Możemy to rozwiązać klikając prawym przyciskiem myszy na pierwszą
komórkę z datą i z podręcznego menu wybrać polecenie Wyszczególnij (rys.
nr 32).
rys. nr 32 — polecenie Wyszczególnij w podręcznym menu
Otrzymamy
wyszczególnioną datę 18.04.2019. W pasku formuły mamy zapis, że został
wyciągnięty wiersz o indeksie 0, ponieważ Power Query zaczyna numerację wierszy
od 0, a nie jak Excel od 1 oraz informację z jakiej kolumny została wyciągnięta
informacja (rys. nr 33).
rys. nr 33 — wyszczególnione dane
Problem
polega na tym, że dalej wyświetla się nam 18.04.2019 a wiemy że poprawną
odpowiedzią jest 19.04.2019. Aby otrzymać prawidłową odpowiedź w formule możemy
dodać jeden dzień. W celu dodania jednego dnia musimy skorzystać z funkcji #duration
a następnie w nawiasie wpisać ilość
dni jakie chcemy dodać oraz ilość godzin, minut i sekund (1,0,0,0). Zapis całej
formuły powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy prawidłową odpowiedź przedstawioną na
rys. nr 34.
rys. nr 34 — działanie funkcji #duration
Uzyskaliśmy
odpowiedź na pierwszą część zadania, którą teraz możemy załadować do Excela
korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne
(jak na rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy
sposób wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić
dane – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry
importowania danych zatwierdzamy przyciskiem OK (rys. nr 35).
rys. nr 35 — okno Importowania danych do Excela
Otrzymamy
odpowiedź na pierwszą część zadania wstawioną do Excela przedstawioną na rys.
nr 36.
rys. nr 36 — rozwiązanie pierwszej części zadania
Druga część
zadania polega na podaniu liczby dni, kiedy poziom wody w zbiorniku przekraczał
800 000 m3. Po raz kolejny klikamy dwukrotnie na zapytanie
tWoda w panelu bocznym Zapytania i połączenia i przechodzimy do Edytora zapytań
Power Query. Następnie robimy odwołanie do tego zapytania klikając na jego
nazwę prawym przyciskiem myszy i wybierając z podręcznego menu polecenie Odwołanie
(jak na rys. nr 28). W ustawieniach zapytania zmieniamy jego nazwę na OdpB. Aby
uzyskać wyniki stanu wody powyżej 800 000 m3 wystarczy
przefiltrować dane. Jak w rozwiązaniu pierwszej części zadania klikamy na
ikonkę z trójkątem obok nazwy kolumny Stan, następnie rozwijamy polecenie Filtry
liczb i wybieramy polecenie Większe niż (jak na rys. nr 29). Otworzy
nam się okno Filtrowania wierszy, gdzie w polu jest większe niż wpisujemy naszą
wartość – 800000. Ustawiony próg zatwierdzamy przyciskiem OK (rys. nr 37).
rys. nr 37 — okno Filtrowania wierszy — parametry filtrowania
Otrzymamy
wszystkie wiersze, dla których stan wody przekraczał 800000 m3
przedstawione na rys. nr 38.
rys. nr 38 — przefiltrowane dane
Aby otrzymać
ilość tych wierszy zaznaczamy kolumnę Data i rozwijamy polecenie Statystyka
(punkt 2 na rys. nr 39) z karty Przekształć (punkt 1), a następnie
wybieramy polecenie Zliczwartości (punkt 3).
rys. nr 39 — ścieżka dostępu do polecenia Zlicz wartości
Otrzymamy
liczbę wierszy, gdzie stan wody przekraczał 800 000m3
przedstawioną na rys. nr 40.
rys. nr 40 — odpowiedź do drugiej części zadania
Możemy
zauważyć że zapytania z odpowiedziami do punktu A i B różnią się ikonkami. OdpA
jest datą, a OdpB jest liczbą co widać na rys. nr 41.
rys. nr 41 — typy danych w odpowiedziach do poszczególnych części zadania 5.4
Uzyskaliśmy
odpowiedź na drugą część zadania, którą teraz możemy załadować do Excela
korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne
(jak na rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy
sposób wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić
dane – konkretna komórka w istniejącym arkuszu. Tak ustawione parametry
importowania danych zatwierdzamy przyciskiem OK (jak na rys. nr 35).
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 42.
rys. nr 42 — odpowiedź na drugą część zadania w Excelu
Trzecia
część zadania polega na podaniu największego stanu wody w zbiorniku, gdyby
zrezygnować z wylewania jej nadmiaru po przekroczeniu 1000000m3. W
zadaniu tym będziemy korzystać z kolumny Stan_2. Przechodzimy do Edytora
zapytań Power Query klikając dwukrotnie na zapytanie tWoda w Excelu. Po raz
kolejny robimy odwołanie do tego zapytania klikając na jego nazwę prawym
przyciskiem myszy i wybierając z podręcznego menu polecenie Odwołanie
(jak na rys. nr 28). W ustawieniach zapytania zmieniamy jego nazwę na OdpC. Aby
uzyskać rozwiązanie zadania wystarczy znaleźć wartość maksymalną w kolumnie
Stan_2. Zaznaczamy kolumnę Stan_2, następnie rozwijamy polecenie Statystyka
(punkt 2 na rys. nr 43) z karty Przekształć (punkt 1) i wybieramy
polecenie Maksimum (punkt 3).
rys. nr 43 — ścieżka dostępu do polecenia Maksimum
Otrzymamy
maksymalny stan zbiornika przedstawiony na rys. nr 44.
rys. nr 44 — odpowiedź na trzecią część zadania — maksymalny stan zbiornika
Uzyskaliśmy
odpowiedź na trzecią część zadania, którą teraz możemy załadować do Excela korzystając
z polecenia Zamknij i załaduj do z karty Narzędzia główne (jak na
rys. nr 24). Otworzy nam się okno Importowania danych, gdzie wybieramy sposób
wyświetlania danych jako Tabela oraz wskazujemy gdzie chcemy wstawić dane –
konkretna komórka w istniejącym arkuszu. Tak ustawione parametry importowania
danych zatwierdzamy przyciskiem OK (jak na rys. nr 35).
Otrzymamy
dane wstawione do Excela przedstawione na rys. nr 45.
rys. nr 45 — odpowiedzi na trzy części zadania zaczytane do Excela
Na tym
kończymy rozwiązywanie zadań z matury z informatyki z 2018 roku.
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 tym poście
zajmiemy się automatycznym wybieraniem kolumn z tabeli z listy.
W Power
Query odcinku 48 pokazywaliśmy jak ręcznie z dużej ilości danych wybrać
konkretne kolumny ( https://www.youtube.com/watch?v=sy0ZAca-0qw ). Pod tym postem otrzymaliśmy
pytanie od widza, jak to zrobić bazując na liście, czyli zakładamy że mamy
listę kolumn, które chcemy wyciągnąć z większej tabeli z danymi.
Zadanie to
wykonamy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Okazuje się to bardzo proste. Wystarczy że mamy listę kolumn, których potrzebujemy. Ustawiamy aktywną komórkę w obszarze tej listy i zaczytujemy dane do Power Query za pomocą polecenia Z tabeli/ zakresu z karty Dane (rys. nr 2).
rys. nr 2 — Z tabeli/zakresu
Otworzy nam
się Edytor zapytań Power Query z wczytaną tabelą tListaKolumn, która zawiera
wartości null w pustych komórkach. Usunięciem tych wartości zajmiemy się w
późniejszym etapie (rys. nr 3).
rys. nr 3 — Edytor zapytań Power Query z wczytaną tabelą tListaKolumn
Co istotne
nasze zapytanie zostało wczytane jako tabela, co widać w Liście zapytań
(zaznaczone czerwoną strzałką na rys. nr 4).
rys. nr 4 — dane wczytane do Power Query jako Tabela
Do dalszych działań potrzebujemy listy a nie tabeli. W tym celu wybieramy ikonkę polecenia Konwertuj na listę z karty Przekształć (rys. nr 5).
rys. nr 5 — polecenie Konwertuj na listę
Otrzymamy
przekonwertowane dane na listę przedstawione na rys. nr 6.
rys. nr 6 — Przekonwertowane dane na listę
Na tym etapie pracy wykonaliśmy wszystko, co było potrzebne z naszymi danymi. Tak przygotowane dane musimy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 7).
rys. nr 7 — polecenie Zamknij i załaduj do
Otworzy się
nam okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Połączenie, czyli zaznaczamy checkbox przy opcji Utwórz tylko połączenie. Tak
ustawione opcje importowania danych zatwierdzamy przyciskiem OK (rys. nr 8).
rys. nr 8 — okno Importowania danych
W kolejnym etapie pobieramy właściwe połączenie, które w naszym przykładzie jest plikiem CSV. Wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 9).
rys. nr 9 — polecenie Z pliku tekstowego /CSV
Otworzy się
nam okno Importowania danych, gdzie musimy znaleźć interesujący nas plik,
zaznaczyć go i zatwierdzić nasz wybór przyciskiem OK (rys. nr 10).
rys. nr 10 — Okno Importowania danych
W pliku CSV
mamy dużą ilość danych, z których chcemy wyciągnąć tylko kolumny z naszej
listy. Otworzy nam się okno z tymi danymi, gdzie klikamy przycisk Edytuj, aby
przejść do edycji tego zapytania (rys. nr 11).
rys. nr 11 — polecenie Edytuj zapytanie
Otworzy nam
się edytor zapytań Power Query z wczytanymi wszystkimi danymi przedstawiony na
rys. nr 12.
rys. nr 12 — Edytor zapytań z wczytanymi danymi z pliku CSV
Przykładowo możemy usunąć niepotrzebne kolumny, czyli zaznaczyć interesujące nas kolumny, następnie kliknąć prawym przyciskiem myszy na dowolną z nich i z podręcznego menu wybrać polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — polecenie Usuń inne kolumny
Nie ma znaczenia, które kolumny usuniemy, o ile Power Query użyje funkcji Table.SelectColumns, która ma w zapisie formuły listę kolumn, które chcemy zachować (rys. nr 14).
rys. nr 14 — argumenty funkcji Table.SelectColumns
W Power Query możemy się odwołać do innego zapytania, czyli w zapisie funkcji zastąpić listę kolumn, które zostały wpisane automatycznie, zapytaniem z listą, które przygotowaliśmy sobie na początku pracy (zapytanie tListaKolumn). Zapis funkcji powinien wyglądać następująco: (rys. nr 15)
rys. nr 15 — Zapis funkcji Table.SelectColumns z odwołaniem do zapytania z listą
Po
zatwierdzeniu formuły otrzymamy błąd, wynikający z wartości null na liście
(rys. nr 16). Power Query nie może przekonwertować wartości null.
rys. nr 16 — Błąd funkcji wynikający z wartości null na liście
Aby usunąć błąd przechodzimy do zapytania tListaKolumn, następnie co istotne w kroku poprzedzającym krok Lista wybór (krok Zmieniono typ), nakładamy na dane filtr. Rozwijamy ikonkę trójkąta w nagłówku kolumny z następnie wybieramy polecenie Usuń puste (rys. nr 17).
rys. nr 17 — Filtrowanie listy za pomocą polecenia Usuń puste
Pojawi nam się
komunikat Wstawianie kroku, w którym musimy zatwierdzić nasze działanie
przyciskiem Wstaw (rys. nr 18).
rys. nr 18 — Komunikat o wstawieniu nowego kroku
Dzięki temu
krokowi wartości null zostały usunięte z listy. Otrzymamy listę przedstawioną
na rys. nr 19.
rys. nr 19 — Odfiltrowana lista kolumn
Nasza lista
potrzebnych kolumn nie zawiera już wartości null i dzięki temu jak przejdziemy
do naszego zapytania FoodMart Klienci, błędu nie będzie. Wpisana wcześniej
formuła z odwołaniem do zapytania tListaKolumn zadziała prawidłowo i otrzymamy
dane zawierające kolumny z listy (rys. nr 20).
rys. nr 20 — prawidłowe działanie funkcji Table.SelectColumns
Tak przygotowane dane możemy załadować do Excela za pomocą polecenie Zamknij i załaduj do z karty Narzędzia główne (analogicznie jak na rys. nr 7).
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Tabela, następnie wskazujemy miejsce wstawienia danych – Istniejący arkusz oraz
wskazujemy konkretną komórkę. Tak ustawione parametry wstawienia danych do
Excela zatwierdzamy przyciskiem OK (rys. nr 21).
rys. nr 21 — Okno importowania danych
Otrzymamy
dane wczytane do Excela zawierające tylko kolumny z listy, przedstawione na
rys. nr 22.
rys. nr 22 — Dane zaimportowane do Excela
Tabela wczytana z Power Query działa dynamicznie. Przykładowo możemy zmienić nasze dane w liście kolumn (zmieniamy kolumnę Nazwisko na Adres i dokładamy nową kolumnę Liczba dzieci), następnie odświeżyć dane z Power Query klikając prawym przyciskiem myszy na dowolną komórkę z obszaru tabeli i wybierając polecenie Odśwież (rys. nr 23).
rys. nr 23 — polecenie Odśwież
Po
odświeżeniu danych otrzymamy zmienione dane, w których kolumna Nazwisko została
zastąpiona kolumną Adres i została dołożona nowa kolumna Liczba dzieci (rys. nr 24).
rys. nr 24 — automatycznie zaktualizowane dane
Podsumowując
stworzyliśmy tabelę z kolumnami z listy, która działa dynamicznie i zawiera
kolumny, które aktualnie są na liście.
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 usunąć lub wybrać konkretne kolumny z
danych. W przykładowych danych mamy zapożyczoną z internetu tabelę z dużą
ilością kolumn z informacjami (rys. nr 1).
rys. nr 1 — Przykładowe dane
Nie zawsze potrzebujemy aż tyle danych ze wszystkich tych kolumn. Kiedy pobieramy takie dane do Power Query, (w naszym przykładzie z pliku CSV) to może być ciężko wybrać tylko te, które nas interesują lub usunąć konkretne z nich. W celu pobrania danych wybieramy polecenie Z pliku tekstowego/CSV z karty Dane (rys. nr 2).
rys. nr 2 — polecenie Z pliku tekstowego/CSV
Otworzy nam
się okno Importowania danych, w którym musimy znaleźć lokalizację naszego
pliku, a następnie dwa razy na niego kliknąć lub nasz wybór zatwierdzić
przyciskiem Otwórz (rys. nr 3).
rys. nr 3 — okno Importowania danych
Otworzy nam
się okno z danymi, gdzie możemy wybrać konkretne dane, ale jest to
problematyczne przy tak dużej ich ilości. Dlatego klikamy przycisk Edytuj (rys.
nr 4).
rys. nr 4 — okno z danymi, z których możemy wybrać konkretne dane
Power Query
posiada funkcjonalność, która ułatwia takie operacje. Otworzy nam się Edytor
zapytań Power Query z wczytaną tabelą z danymi.
Nie znając tej funkcjonalności możemy zaznaczyć kolumny, które chcemy zostawić przytrzymując klawisz Ctrl, a następnie kliknąć prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Usuń inne kolumny (rys. nr 5).
rys. nr 5 — polecenie Usuń inne kolumny
W wyniku tej
operacji otrzymamy tylko zaznaczone wcześniej kolumny (rys. nr 6).
rys. nr 6 — kolumny pozostałe po usunięciu pozostałych
To polecenie
sprawdza się wtedy kiedy tych kolumn jest mało i łatwo możemy znaleźć te, które
nas interesują. Zatem usuwamy ostatni krok (Usunięto inne kolumny) z
Zastosowanych kroków.
Kiedy mamy kilkadziesiąt kolumn dużo łatwiejszym sposobem jest wykorzystanie polecenia Wybieranie kolumn z karty Narzędzia główne (rys. nr 7).
rys. nr 7 — polecenie Wybieranie kolumn z karty Narzędzia główne
Otworzy nam
się okno Wybierania kolumn, gdzie mamy listę wszystkich kolumn w tabeli i
możemy wybrać nazwy kolumn, których potrzebujemy za pomocą zaznaczenia/
odznaczenia odpowiednich checkboxów (rys. nr 8). Po wybraniu odpowiednich
checkboxów nasz wybór zatwierdzamy przyciskiem OK.
rys. nr 8 — Okno wybieranie kolumn
Wybraliśmy
takie same kolumny jak w pierwszym przykładzie, więc otrzymamy tabelę przedstawioną
na rys. nr 6. Tak przygotowane dane możemy załadować do Excela za pomocą
polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 9).
rys. nr 9 — polecenie Zamknij i załaduj
Dane zostały
załadowane tylko jako połączenie, więc klikamy prawym przyciskiem myszy na
nazwę zapytania w panelu bocznym Zapytania i połączenia i z podręcznego menu
wybieramy polecenie Załaduj do (rys. nr 10).
rys. nr 10 — polecenie Załaduj do z podręcznego menu
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
tabela oraz wskazujemy miejsce wstawienia danych, w naszym przykładzie
Istniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry
zatwierdzamy przyciskiem OK (rys. nr 11).
rys. nr 11 — okno Importowania danych
Otrzymamy
wybrane przez nas kolumny załadowane do Excela przedstawione na rys. nr 12.
rys. nr 12 — wybrane dane wczytane do Excela
W pierwszym
arkuszu mamy konkretne cztery kolumny, które były nam potrzebne, a w kolejnym
arkuszu możemy załadować inne kolumny z tego samego pliku CSV. Przechodzimy do
Arkusza 2 i analogicznie jak w przykładzie powyżej korzystając z polecenia Z
pliku tekstowego/CSV z karty Dane możemy pobrać dane jeszcze raz, z tym że tym
razem wybrać inne kolumny, które nas interesują (rys. nr 2).
Załóżmy, że tym razem będzie nas interesował adres klienta, więc po zaczytaniu danych do Power Query rozwijamy polecenie Wybieranie kolumn (punkt 2 na rys. nr 13) z karty Narzędzia główne (punkt 1), a następnie wybieramy polecenie Przejdź do kolumny (punkt 3).
rys. nr 13 — polecenie Przejdź do kolumny
Otworzy nam
się okno Przejdź do kolumny, gdzie możemy wybrać nazwę konkretnej kolumny i
przeskoczyć do niej. Polecenie to przydaje się w sytuacji, kiedy mamy bardzo
dużą ilość kolumn i ciężko znaleźć tę, która nas aktualnie interesuje (rys. nr 14).
rys. nr 14 — okno Przejdź do kolumny
Power Query
automatycznie przeskoczy do wybranej kolumny i zaznaczy ją (rys. nr 15).
rys. nr 15 — wybrana kolumna zaznaczona w danych
Po raz kolejny wybieramy polecenie Wybieranie kolumn z karty Narzędzia główne (jak na rys. nr 7). Otworzy nam się okno Wybierania kolumn, gdzie zgodnie z założeniem zaznaczamy checkboxy przy kolumnach związanych z adresem. Zaznaczone kolumny zatwierdzamy przyciskiem OK (rys. nr 16).
rys. nr 16 — Okno Wybierania kolumn
Otrzymamy
dane z wybranymi kolumnami przedstawione na rys. nr 17.
rys. nr 17 — dane z wybranymi wcześniej kolumnami
Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 18).
rys. nr 18 — polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
tabela oraz wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz i
konkretna komórka. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 19).
rys. nr 19 — okno Importowania danych
Otrzymamy
dane w Excelu zawierające wybrane przez nas kolumny związane z adresami
klientów (rys. nr 20).
rys. nr 20 — dane wczytane do Excela
Podsumowując wyciąganie konkretnych kolumn z tabel, gdzie mamy kilkadziesiąt kolumn jest dużo łatwiejsze dzięki funkcjonalności Wybierania kolumn.
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.
