Excel - kurs online - oferta dla każdego

Power Query 54 — Matura z informatyki 2018 — Cykl pracy zbiornika zad 5.4

utworzone przez Adam Kopeć | paź 29, 2019 | Excel, Excel PowerQuery | 0 komentarzy

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). 

Otworzy nam się Edytor zapytań Power Query z wczytanymi danymi z tabeli przedstawiony na rys. nr 2.

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.

Otrzymamy dane ze zmienionym typem danych w kolumnie Data przedstawione na rys. nr 4.

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).

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).

Ikonka przy nazwie zapytania oznaczona strzałką na rys. nr 7 wskazuje nam, że nasze dane mają formę 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.

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:

=List.Buffer(Table.Column (#"Zmieniono typ", "Woda m3"))

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).

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.

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.

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.

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).

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).

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:

=List.Generate (()=> [Date=#date(2007,12,31), #Woda m3=0, Stan=500000, Wylać=10000, Stan_2=Stan, Wylać_2=Wylać, I=0]

Musimy zmienić nazwę kroku Niestandardowe 1, w którym obliczaliśmy ilość wierszy, na RowCount (Ustawienia zapytania) – rys. nr 15.

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")),

            Stan_2 = [Stan_2] — [Wylać] + # "Woda m3",

Wylać_2 = Number.RoundUp (0,02* ([Stan_2] – [Wylać_2] + # "Woda m3")),

i = [i] +1

]

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.

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

W kolejnym kroku musimy przekształcić nasze dane z listy na tabelę, skorzystamy tutaj z polecenia Do tabeli z karty Przekształć (rys. nr 18).

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).

Otrzymamy listę rekordów przekonwertowaną na tabelę co widać na rys. nr 20.

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).

Otrzymamy rozwinięte dane w postaci tabeli przedstawione na rys. nr 22.

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).

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).

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).

Otrzymamy dane zaczytane do Excela przedstawione na rys. nr 26.

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.

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).

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 m³ 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).

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).

Otrzymamy przefiltrowane dane tylko z wierszami, w których stan wody przekraczał 1000000 m³ wody przedstawione na rys. nr 31 .

Interesuje nas pierwszy dzień, w którym ilość wody w zbiorniku przekroczyła 1000000 m³. 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).

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).

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:

=#"Przefiltrowano wiersze"{0}[Date] + #duration (1,0,0,0)

Po zatwierdzeniu powyższej formuły otrzymamy prawidłową odpowiedź przedstawioną na rys. nr 34.

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).

Otrzymamy odpowiedź na pierwszą część zadania wstawioną do Excela przedstawioną na rys. nr 36.

Druga część zadania polega na podaniu liczby dni, kiedy poziom wody w zbiorniku przekraczał 800 000 m³. 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 m³ 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).

Otrzymamy wszystkie wiersze, dla których stan wody przekraczał 800000 m³ przedstawione na rys. nr 38.

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 Zlicz wartości (punkt 3).

Otrzymamy liczbę wierszy, gdzie stan wody przekraczał 800 000m³ przedstawioną na rys. nr 40.

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.

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.

Trzecia część zadania polega na podaniu największego stanu wody w zbiorniku, gdyby zrezygnować z wylewania jej nadmiaru po przekroczeniu 1000000m³. 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).

Otrzymamy maksymalny stan zbiornika przedstawiony na rys. nr 44.

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.

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.

Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela

Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych. 

W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.

Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY

Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY

Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY

Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY

VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY

Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY