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.

rys. nr 1 — Treść zadania 5.4
rys. nr 2 — Treść zadania 5.4 ciąg dalszy

Zakładamy, że mamy już wczytane dane dotyczące zbiornika retencyjnego z pliku tekstowego (rys. nr 3).

rys. nr 3 — Wczytane dane do zadania

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

rys. nr 4 — Ilość wody jaką wylewamy

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.

rys. nr 5 — Codzienny stan zbiornika

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

rys. nr 6 — Filtry liczb — Mniejsze niż

Otworzy nam się okno Autofiltr niestandardowy, gdzie na rysunku nr 7 w miejscu oznaczonym strzałką wpisujemy wartość 1 000 000 (rys. nr 7).

rys. nr 7 — Autofiltr niestandardowy

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

rys. nr 8 — Pierwszy dzień w którym stan zbiornika przekroczył 1000000 m³

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)

rys. nr 9 — Wynik funkcji PODAJ.POZYCJĘ

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

rys. nr 10 — Zmiana formatowania

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

rys. nr 11 — Wynik funkcji LICZ.JEŻELI

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

rys. nr 12 — Wynik funkcji ZAOKR.GÓRA

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.

rys. nr 13 — Wynik funkcji MAX

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

Książka Mistrz Excela reklama