W dzisiejszym poście zajmiemy się kolejnym zadaniem z matury z informatyki 2018. Omówimy rozwiązanie zadania nr 5.4 dotyczące cyklu pracy zbiornika (rys. nr 1 i rys nr 2). Najtrudniejszym elementem zadania jest opisanie pracy zbiornika retencyjnego i czasu, kiedy wylewamy nadmiar wody (ponad 1 000 000 m3 wody) oraz codziennie o 8 rano wylewanie 2% wody z pomiaru o północy.
Zakładamy, że mamy już wczytane dane dotyczące zbiornika retencyjnego z pliku tekstowego (rys. nr 3).
Do danych źródłowych dopisujemy dwie kolumny, pierwsza ze stanem zbiornika i druga z ilością wody jaką wylewamy. Zaznaczamy pierwszy wiersz danych, pod nagłówkami i za pomocą skrótu klawiszowego Ctrl+Shift+Plus dodajemy nowy wiersz, żeby dołożyć stan początkowy zbiornika, jaki był podany w treści zadania.
W komórce D2 (kolumna Wylewamy) musimy napisać formułę, która obliczy ile wody musimy wylać w zależności od poziomu napełnienia zbiornika. Wylewać wodę będziemy dopiero kiedy jej ilość przekroczy 1 000 000 m3 pojemności. Użyjemy do tego celu funkcji JEŻELI. Zapis funkcji będzie wyglądał następująco:
=JEŻELI([@[Stan zbiornika]]>1000000;1000000-[@[Stan zbiornika]] ‑ZAOKR.GÓRA(0,02*[@[Stan zbiornika]];0);-ZAOKR.GÓRA(0,02*[@[Stan zbiornika]]))
Sprawdzamy w teście logicznym czy stan zbiornika jest większy od 1 000 000.
Jeśli przekroczył to od razu wylewamy tą nadwyżkę powyżej miliona, czyli musimy odjąć 1000000 od zawartości zbiornika ([@[Stan zbiornika]]-1000000). Chcemy, aby do dalszych obliczeń ta wartość była ujemna, więc musimy ją zapisać inaczej, mianowicie odjąć aktualny stan zbiornika od miliona (1000000-[@[Stan zbiornika]]). Oprócz tej wartości musimy jeszcze wylać 2% pojemności wody w zbiorniku w chwili pomiaru zaokrąglone w górę. Użyjemy funkcji ZAOKR.GÓRA, w której lepiej nie pisać 2% bo to też jest operacja matematyczna w Excelu, lepiej zapisać jako 0,02 i pomnożyć to przez aktualny stan zbiornika. Jako drugi argument wpiszemy 0, bo chcemy zaokrąglić do liczb całkowitych.
Jeśli natomiast stan zbiornika nie przekroczy miliona m3 to wylewamy tylko 2% aktualnego stanu zbiornika, czyli ‑ZAOKR.GÓRA(0,02*[@[Stan zbiornika]]).
Zatwierdzamy formułę i otrzymujemy wyniki, formuła automatycznie skopiuje się do wszystkich wierszy, bo działamy w tabeli Excela (rys. nr 4).
Teraz zajmiemy się formułą dla aktualnego stanu zbiornika. Użyjemy tutaj funkcji SUMA, dodamy do siebie wartości: objętość wody jaka wpłynęła danego dnia, stan zbiornika z dnia wcześniejszego i objętość jaką wylaliśmy. Zapis funkcji będzie wyglądał następująco:
=SUMA([@[Woda m3];C2;D2)
Zatwierdzamy formułę. Excel chciałby zastąpić wszystkie komórki, ale my chcemy aby w pierwszym wierszu została stała wartość (500000), którą wpisaliśmy ręcznie, dlatego klikamy dwa razy na prawy dolny róg komórki z wynikiem obliczeń (zaznaczone strzałką na rysunku poniżej) i otrzymujemy wyniki przedstawione na rysunku nr 5.
W pierwszej części zadania mamy podać dzień w którym pierwszy raz stan zbiornika przekroczył 1 000 000 m3. Moglibyśmy sobie to łatwo sprawdzić w tabeli za pomocą polecenia Filtry liczb (rys. nr 6).
Otworzy nam się okno Autofiltr niestandardowy, gdzie na rysunku nr 7 w miejscu oznaczonym strzałką wpisujemy wartość 1 000 000 (rys. nr 7).
Zatwierdzamy przyciskiem OK i otrzymujemy pierwszy dzień, w którym stan zbiornika przekroczył 1 000 000 m3, to jest 2015-04-18. Musimy pamiętać, że jest to dzień kiedy stan przekroczył ten poziom a wodę wylewamy dopiero następnego dnia, więc prawidłowa odpowiedz to 2015-04-19 (rys. nr 8).
W taki sposób możemy sprawdzić to ręcznie, a teraz pokażemy jak to zrobić za pomocą formuł. Możemy wykorzystać funkcję PODAJ.POZYCJĘ, która będzie szukała wyniku naszej operacji logicznej. Zapis funkcji będzie wyglądał następująco:
=PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)
Szukamy wartości logicznej prawda, czyli robimy test logiczny, czy stan zbiornika jest większy od 1 000 000. Jako trzeci argument wybieramy dopasowanie dokładne, czyli wpisujemy 0. Funkcja PODAJ.POZYCJĘ zwróci nam pierwszy wiersz, gdzie stan zbiornika przekroczył 1 000 000 m3 wody.
Musimy pamiętać, że jest to dzień kiedy stan przekroczył ten poziom a wodę wylewamy dopiero następnego dnia, więc prawidłowa odpowiedź to 2015-04-19, czyli do naszej formuły musimy dodać jeden dzień (rys. nr 9). Zapis funkcji powinien wyglądać następująco:
=PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)+1)
Otrzymaliśmy numer wiersza jako wynik, teraz aby otrzymać datę z tego wiersza, musimy użyć funkcji INDEKS. Zapis formuły będzie wyglądał następująco:
=INDEKS(tWoda[Data]; PODAJ.POZYCJĘ(prawda;tWoda[Stan zbiornika]>1000000;0)+1)
Funkcja INDEKS patrzy na kolumnę z datą, a funkcja PODAJ.POZYCJĘ zwraca numer wiersza, z którego chcemy wyciągnąć datę z kolumny Data. Zatwierdzamy Ctrl+Enter i otrzymujemy w wyniku liczbę (pamiętamy, że w Excelu daty to liczby). Kolejnym krokiem jest zmiana formatowania na Datę (rys. nr 10).
Drugie zadanie polega na wyznaczeniu wszystkich dni, kiedy stan zbiornika był powyżej 800 000 m3. Wystarczy użyć funkcji LICZ.JEŻELI, dla której pierwszym argumentem jest kolumna stan zbiornika, a Kryterium to "> 800000". Zapis funkcji będzie wyglądał następująco:
=LICZ.JEŻELI(tWoda[Stan zbiornika]; "> 800000")
Zatwierdzamy Ctrl+Enter. Otrzymaliśmy wynik – 188 dni, w których poziom wody w zbiorniku przekraczał 800 000 m3 (rys. nr 11).
Trzecie zadanie polega na wyznaczeniu maksymalnego stanu zbiornika, jeśli zmodyfikujemy sposób wylewania wody. Przyjmiemy, że nie będziemy wylewać wody po przekroczeniu stanu 1 000 000 m3, a tylko 2% dziennie oraz że zbiornik ma niegraniczoną pojemność. Możemy skopiować całą tabelkę obok, żeby nie stracić wcześniejszych obliczeń. Teraz musimy zmodyfikować formułę w kolumnie Wylewamy. Zapis powinien wyglądać następująco:
=-ZAOKR.GÓRA(0,02*[@[Stan zbiornika]])
Zatwierdzamy Ctrl+Enter. Formuła sama wypełni cała tabelę (rys. nr 12).
Wystarczy teraz użyć funkcji MAX. Szukamy wartości maksymalnej w kolumnie Stan zbiornika w drugiej tabeli. Zapis będzie wyglądał następująco:
=MAX(tWoda2[Stan zbiornika])
Zatwierdzamy formułę i otrzymujemy wynik przedstawiony na rysunku nr 13, czyli maksymalny poziom wody w zbiorniku wyniesie 1 399 242 m3.
Podsumowując większość z tych zadań można rozwiązać stosując odpowiednie filtry, ale my pokazaliśmy jak można je rozwiązać za pomocą odpowiednich funkcji.
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
