Nie znasz dobrze obsługi Excela? A może nie znasz jej wcale? Doskonale wiem, że w dzisiejszych czasach i realiach, znajomość tego programu jest nie tylko niezbędna, ale przede wszystkim korzystna dla Ciebie samego. Moje szkolenie z Excela online pokaże Ci, że arkusz kalkulacyjny nie jest wcale Twoim wrogiem, a sprzymierzeńcem w porządkowaniu i analizie danych - nie tylko liczbowych. Stworzyłem kurs Excel online na każdym poziomie, zarówno dla osób początkujących, średniozaawansowanych, jak i zaawansowanych. Znajdziesz u mnie także szkolenie z Excela online dedykowane specjalnie przedsiębiorcom. Każdy kurs zawiera starannie wyselekcjonowany zakres wiedzy i zagadnień, które odpowiadają na ściśle sprecyzowane potrzeby jego użytkowników.
Excel - kurs online. Dlaczego warto?
Zastanawiasz się, czy Excel i kurs online to dobre połączenie? Czy taka forma nauki pracy z arkuszem kalkulacyjnym może być efektywna? Pozwól, że rozwieję Twoje wątpliwości.
Od lat z powodzeniem zajmuję się dzieleniem swoją wiedzą zdalnie. Ta forma nauki, szczególnie w ostatnim czasie, mocno zyskała na popularności, a jej techniki znacząco się rozwinęły. Abyś mógł biegle posługiwać się programem Excel, każdy swój kurs zaprojektowałem z ogromną starannością i w oparciu o zasady metodyki nauczania na odległość.
Jednak szkolenie z Excela online, to przede wszystkim ogromna wygoda dla Ciebie, ponieważ nie musisz wychodzić z domu, możesz uczyć się wraz ze mną z dowolnego miejsca i w wybranym przez siebie czasie. Dodatkowo każdy mój kurs Excela online zawiera materiały dydaktyczne dostępne dla Ciebie już po zakończeniu szkolenia. Krótkie nagrania filmowe, prezentujące różnego rodzaju działania w arkuszu kalkulacyjnym, które w dowolnej chwili możesz zatrzymać, odtworzyć ponownie, by jeszcze raz przeanalizować i utrwalić wiedzę, to doskonały sposób, by opanować Excel. Kurs online to nie tylko wygoda, to także oszczędność Twojego i mojego czasu - zamiast dojeżdżać w umówione miejsce, możemy poświęcić więcej chwil na ćwiczenia i praktykę.
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 dzisiejszym poście zajmiemy się grupowaniem danych z wielu
arkuszy. W przykładowych danych mamy imiona i nazwiska osób (klientów), z
którymi się spotkaliśmy bądź im coś sprzedaliśmy. Dane te są podzielone na
różne arkusze, np. pod względem miesięcy. Dane te chcemy połączyć, podliczyć,
np. wyznaczyć ile razy odbyliśmy spotkań z konkretnym klientem lub ile
sprzedaliśmy takich samych produktów. Część danych (jeden z arkuszy), na którym
będziemy pracować, został przedstawiony na rysunku nr 1.
rys. nr 1 — przykładowy arkusz z danymi
Naszym
zadaniem jest uzyskanie podsumowania przedstawionego na rysunku nr 2
rys. nr 2 — Nasz cel — pogrupowane dane
Do tych obliczeń chcemy wykorzystać Power Query. Zaczynamy od
pustego arkusza, gdzie wybieramy polecenie Nowe zapytanie (punkt nr 2 na
rysunku nr 3) z karty Dane, a następnie Z pliku (punkt nr 3) i Ze skoroszytu
(punkt nr 4).
rys. nr 3 — Nowe zapytanie ze skoroszytu
Otworzy nam się okno Importowanie pliku, gdzie musimy wybrać
lokalizację naszego pliku z danymi (rys. nr 4). Wybieramy plik i zatwierdzamy
nasz wybór przyciskiem Otwórz.
rys. nr 4 — Importowanie danych
Łączymy się z tym plikiem, z którego chcemy pobierać
informacje. Otworzy nam się okno Nawigator. W opcjach wyświetlania wybieramy
np. Styczeń – zaznaczony na rysunku zieloną strzałką (nie ma to większego
znaczenia, bo nasze nowe zapytanie będziemy edytować) nasz wybór zatwierdzamy
przyciskiem Edytuj (zaznaczone zielonym prostokątem na rysunku nr 5).
rys. nr 5 — Nawigator
Czekamy aż nasze dane ze stycznia zostaną wczytane do Power
Query. Otworzy nam się Edytor zapytań z wczytaną tabelą z danymi ze stycznia.
Dane te zostały przedstawione na rysunku nr 6. Nie interesują nas na tym etapie
te dane, a jedynie Zastosowane kroki (oznaczone na rysunku nr 6 zieloną
strzałką). Usuwamy kroki Zmieniono typ i Nawigacja, a zostawiamy krok Źródło.
rys. nr 6 — Edytor zapytań
Otrzymamy
dane przedstawione na rysunku nr 7.
rys. nr 7 — dane
Dzięki temu nie będziemy musieli sami wpisywać skomplikowanej
formuły, tylko skorzystamy w wbudowanych funkcjonalności Power Query, czyli
będziemy wprowadzać zmiany w istniejącej formule na pasku formuły. W naszych
danych mamy podział na Kind, czyli rodzaje danych w pliku. Sheet to arkusze,
Table to tabela, natomiast DefinedName to zdefiniowane zakresy danych.
Rozwijamy ikonkę ze strzałką w tytule kolumny Kind i w podręcznym menu
zaznaczamy tylko Sheet, bo interesują nas tylko arkusze (rys. nr 8). Podane
parametry filtru zatwierdzamy klikając przycisk OK.
rys. nr 8 — Filtry
W naszych danych mamy jeszcze niepotrzebny ukryty arkusz,
którego nie chcemy widzieć w naszych danych, więc klikamy na ikonkę strzałki w
tytule kolumny Hidden i odznaczamy checkbox True (rys. nr 9). Nasz filtr
zatwierdzamy klikając przycisk OK.
rys. nr 9 — Filtr
Z kolumny Item interesują nas dane tylko z konkretnych
arkuszy : styczeń, luty i marzec. Chcemy odfiltrować dane (arkusze) domyślne,
czyli takie które mają w sobie jakąś nazwę, w naszym przykładzie niepotrzebne
arkusze mają w nazwie litery PQ. Klikamy na ikonkę ze strzałką w tytule kolumny
Item, a następnie wybieramy opcje Filtr tekstu i Nie zawiera (rys. nr 10).
rys. nr 10 — Nie zawiera
Otworzy nam się okno Filtrowanie wierszy, gdzie wpisujemy w
pole Nie zawiera zestaw liter PQ (miejsce zaznaczone strzałka na rysunku nr
11). Musimy tutaj pamiętać, że Power Query jest Case sensitive, czyli zwraca
uwagę na wielkość liter. Nasz filtr zatwierdzamy klikając przycisk OK.
rys. nr 11 — Okno filtrowanie wierszy
Otrzymamy interesujące nas dane (arkusze) przedstawione na
rysunku nr 12.
rys. nr 12 — Interesujące nas arkusze
Dane te z poszczególnych arkuszy znajdują się w kolumnie Data
pod nazwą Table. Na tym etapie interesuje nas tylko kolumna Data, zawierająca
tabelki z danymi. Klikamy prawym przyciskiem myszy na tytuł tej kolumny i z
podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 13).
rys. nr 13 — Usuń inne kolumny
Otrzymamy jedną kolumnę Data, następnie klikamy na ikonkę ze
strzałkami w jej nazwie i z podręcznego menu wybieramy polecenie Rozwiń. Nasze
parametry zatwierdzamy przyciskiem OK (rys. nr 14).
rys. nr 14 — Rozwiń
Otrzymamy dane bez nagłówków, więc musimy wybrać polecenie
Użyj pierwszego wiersza jako nagłówków z karty Narzędzia główne (rys. nr 15).
rys. nr 15 — Użyj pierwszego wiersza jako nagłówków
W danych mamy 96 wierszy. Nasze dane będą się prezentować
następująco (rys. nr 16):
rys. nr 16
W kolejnym etapie wybieramy polecenie Grupowanie według z karty
Narzędzia główne (rys. nr 17).
rys. nr 17 — Grupowanie według
Otworzy nam się okno Grupowanie według. Power Query
automatycznie wstawi parametry grupowania, czyli Nazwisko i Imię (według czego
ma grupować), bo mamy w danych dwie kolumny. W polu Nazwa nowej Kolumny
automatycznie zostało wstawione Liczność (nie musimy zmieniać bo chodzi nam o
policzenie ilości takich samych danych). W polu operacja wybieramy polecenie
Zlicz wiersze. Pole Kolumna pozostaje w naszym przykładzie pusta (jest to pole
do innych operacji). Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 18).
rys. nr 18 — Okno grupowanie według
Po
przefiltrowaniu danych zostało 75 wierszy. Z karty Narzędzia główne wybieramy
polecenie Sortuj od Z do A (rys. 19).
rys. nr 19 — Sortuj od Z do A
Nasze dane posortowane od największej do najmniejszej pokazaliśmy
na rysunku nr 20.
rys. nr 20 — Posortowane dane
Następnie z karty Narzędzia główne wybieramy polecenie
Zamknij i załaduj do (punkt nr 2 na rysunku nr 21).
rys. nr 21 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie wybieramy
sposób przedstawienia danych jako Tabela oraz miejsce załadowania danych –
Istniejący arkusz komórka $A$1 (rys. nr 22). Ustawione parametry zatwierdzamy
klikając przycisk Załaduj.
rys. nr 22 — Okno Ładowanie do
Otrzymamy dane w Excelu przedstawione na rysunku nr 23.
rys. nr 23 — Dane załadowane do Excela
Jeśli dodamy kolejny miesiąc do danych bazowych, to filtry w
Power Query zadziałają prawidłowo i wstawią dodatkowe dane do tabeli w Excelu.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak filtrować dane po
liście wartości. Zagadnienie to omówimy no podstawie przykładowych danych z
rysunku nr 1.
rys. nr 1 — Przykładowe dane
W danych mamy podaną tabelę z listą sprzedawców i wartości
sprzedaży im przypisaną. W naszym zadaniu chcemy wyciągnąć z tej tabeli listę
tylko tych sprzedawców, którzy należą do grupy A (druga tabelka). Możemy to
zadanie zrobić w Power Query. Przede wszystkim musimy te tabelki załadować do
Power Query. Wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane.
rys. nr 2 — Z tabeli
Wczytujemy sobie najpierw mniejsza tabelkę, czyli ze
sprzedawcami z grupy A. Otworzy nam się okno Edytor zapytań z wczytaną tabelą
tGrupa. Chcemy ją mieć tylko w pamięci, więc od razu, za pomocą polecenia
Zamknij i załaduj do (punkt nr 2 na rysunku nr 3) z karty Narzędzia główne,
przenosimy do Excela.
rys. nr 3 — Zamknij i załaduj do
Otworzy nam się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry naszej nowej tabeli – ustawiamy opcję Utwórz tylko połączenie i
zatwierdzamy przyciskiem Załaduj (rys. nr 4).
rys. nr 4 — Okno Ładowanie do
Teraz ustawiamy aktywną komórkę w obszarze pierwszej tabelki
ze sprzedawcami i jak wyżej korzystając z polecenie Z tabeli z karty Dane,
wczytujemy tabelę do Power Query. Otworzy nam się Edytor zapytań z wczytaną
tabelą tSprzedaż. Aby móc wyciągnąć tylko tych sprzedawców którzy znajdują się
w grupie sprzedawców A musimy scalić nasze dane. Wybieramy polecenie Połącz
(punkt nr 2 na rysunku nr 5) z karty Narzędzia główne, a następnie Scal
zapytania (punkt nr 3 na rysunku nr 5).
rys. nr 5 — Scal zapytania
Otworzy nam się okno Scalanie. W punkcie nr 1 oznaczonym na
rysunku nr 7 wybieramy kolumnę, po której będziemy scalać dane – Sprzedawca. W
punkcie oznaczonym numerem 2 na rysunku nr 7 wybieramy z listy rozwijanej
zapytanie tGrupa. Następnie w punkcie oznaczonym numerem 3 na rysunku nr 7 wybieramy
rodzaj sprzężenia. Musimy pamiętać, że w scalaniu tabelka u góry jest
traktowana jako lewa (oznaczona na rysunku nr 6 strzałką czerwoną, a tabelka na
dole jako prawa (strzałka niebieska).
rys. nr 6 — Okno scalanie
Wybieramy sprzężenie Prawe zewnętrzne, czyli wszystkie z drugiej
tabeli i pasujące z pierwszej. Nasze ustawione parametry zatwierdzamy
przyciskiem OK (rys. nr 7).
rys. nr 7
Otrzymujemy dane przedstawione na rysunku nr 8.
rys. nr 8 — Scalone dane
Kolumnę tGrupa możemy sobie rozwinąć klikając na strzałki
oznaczone na rysunku nr 9 zieloną strzałką. Zatwierdzamy Przyciskiem OK.
rys. nr 9 — Rozwiń
W wyniku rozwinięcia kolumny tGrupa otrzymamy dane
przedstawione na rysunku nr 10.
rys. nr 10 — rozwinięte dane
Są to ci sami sprzedawcy co w pierwszej kolumnie, więc możemy
sobie usunąć ten krok z bocznego panelu Zastosowane kroki i usunąć całkowicie
tą kolumnę za pomocą polecenia Usuń z podręcznego menu (rys. nr 11).
rys. nr 11 — Usuń kolumnę
Otrzymaliśmy wynik jaki nas interesuje, więc musimy go
załadować do Excela. Z karty Narzędzia główne wybieramy polecenie Zamknij i
załaduj do (punkt nr 2 na rysunku nr 12).
rys. nr 12 — Zamknij i załaduj do
Otworzy się w Excelu okno Ładowanie do, gdzie ustawiamy
parametry ładowanych danych i zatwierdzamy przyciskiem Załaduj. Dane wstawione
jako tabela w istniejącym arkuszu, w konkretnej komórce (rys. nr 13).
rys. nr 13 — Okno Ładowanie do
Nasze dane w Excelu pokazane zostały na rysunku nr 14.
rys. nr 14 — Dane zaczytane do Excela
Co jest ciekawego w naszych działaniach w scalaniu – jeśli
wyznaczyliśmy wszystkich sprzedawców, którzy są na liście grupy A, to możemy w
bardzo łatwy sposób wyznaczyć tych, których na tej liście nie ma. Wystarczy
wrócić do Power Query i zmienić typ scalania. Aby nie stracić aktualnych
wyników, skopiujemy je za pomocą polecenia Duplikuj z podręcznego menu (rys. nr 15).
rys. nr 15 — Duplikuj
W ustawieniach zapytania w Zastosowanych krokach musimy
wrócić do kroku Scalanie zapytania i kliknąć w koło zębate przy nazwie tego
zapytania. Otworzy nam się okno Scalanie, gdzie możemy zmienić (w miejscu
oznaczonym zieloną strzałką na rysunku nr 16) rodzaj sprzężenia. Chcemy
wyrzucić te elementy które są w prawej kolumnie (tGrupa), a zachować tylko te, których tam nie ma, więc
musimy wybrać Lewe anty (wiersze tylko w pierwszej).
rys. nr 16 — Lewe anty
Po usunięciu dodatkowej kolumny (tak jak w poprzednim
scalaniu) otrzymujemy dane przedstawione na rysunku nr 17.
rys. nr 17
Następnie wybieramy polecenie Zamknij i załaduj z karty
Narzędzia główne (jak na rysunku nr 12). Otworzy nam się okno Ładowanie do,
gdzie ustawiamy wstawiane dane jako tabela oraz miejsce wstawienia danych –
konkretna komórka w istniejącym arkuszu (analogicznie jak na rysunku nr 13).
Zatwierdzamy te parametry przyciskiem Załaduj i otrzymujemy dane przedstawione
na rysunku nr 18.
rys. nr 18
Innym sposobem rozwiązania tego zagadnienia jest użycie
filtrów. Po raz kolejny wczytujemy dane z tabeli do Power Query. Otworzy nam
się Edytor zapytań z wczytaną tabelą tSprzedaż(3). Rozwijamy podręczne menu za
pomocą ikonki z boku nazwy kolumny Sprzedawca i wybieramy polecenie Filtry
tekstu, a następnie Zawiera (rys. nr 19).
rys. nr 19 — Filtry tekstu
Otworzy nam się okno Filtrowanie wierszy, w którym wpisujemy
jakiś ciąg znaków (bez znaczenia bo później go zmodyfikujemy) i zatwierdzamy OK
(rys. nr 20).
rys. nr 20 — Filtrowanie wierszy
Otrzymamy pustą tabele przedstawioną na rysunku nr 21. Jest
pusta ponieważ żadne ze sprzedawców nie zawiera ciągu liter asd, jakie
wpisaliśmy.
rys. nr 21 — pusta tabela
Została tu użyta funkcja Text.Contains, która ma zwrócić każdy
wiersz, który zawiera ciąg znaków. My chcemy aby funkcja zwróciła nam tabelę
więc użyjemy następującej formuły:
List.ContainsAny(tGrupa[Grupa A],[{Sprzedawca}])
Możemy tę funkcje przetłumaczyć na przypadek, jeśli lista
zawiera którąkolwiek z wartości. Tą formułę musimy wkleić w pasek formuły
zamiast funkcji Text.Contains. Zapis formuły oraz jej wyniki zostały
przedstawione na rysunku nr 22.
rys. nr 22
Otrzymany wynik jest identyczny z tym otrzymanym przez
scalanie. Moim zdaniem dużo bardziej skomplikowany i przeznaczony dla osób
bardziej zaawansowanych w Power Query. Ważną informacją do zapamiętania jest
to, że Power Query nie obsługuje symboli wieloznacznych, takich jak np. * czy
?. Formuły wtedy nie zadziałają. Najlepiej korzystać z tego rozwiązania, które
rozumiemy i potrafimy go używać, ponieważ jest większe prawdopodobieństwo, że
nie popełnimy błędu. Jeśli użyjemy rozwiązania, które sprawia nam trudności i
jest nie do końca zrozumiałe, może się zdarzyć że otrzymamy błędne wyniki.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
