I_R_W08_M19 Arkusz kalkulacyjny na maturze
Jako przykład zadania maturalnego, do rozwiązania którego można wykorzystać arkusz kalkulacyjny, wykorzystamy zadanie 4. próbnego egzaminu maturalnego z informatyki z grudnia roku, w nowej formule, poziom rozszerzony – EKOdom. Omówione zostanie rozwiązanie wybranych podpunktów tego zadania.
Treść zadania brzmi:
W EKOdomu działa instalacja zbierająca z dachu wodę deszczową, która jest retencjonowana w zbiorniku i wykorzystywana do celów gospodarczych. W pliku ekodom.txt zapisano ilość zebranej wody deszczowej w kolejnych dniach roku (w litrach). Fragment pliku:
> data retencja
> 01.01.2022 0
> 02.01.2022 0
> 03.01.2022 0
>Schemat wykorzystania wody deszczowej:
Rodzina codziennie (poza przypadkami wymienionymi niżej) zużywa 190 l wody ze zbiornika.
W każdą środę ze względu na dodatkowe prace zużycie wzrasta do 260 l.
Dodatkowo w okresie od kwietnia do września, jeśli w kolejnych dniach nie wystąpią naturalne opady, to piątego dnia bez opadów jest podlewany ogródek. Na podlewanie zużywa się 300 l wody. Jeżeli susza się przedłuża, to kolejne podlewanie jest dziesiątego dnia, piętnastego dnia itd.
Jeśli zabraknie wody w zbiorniku retencyjnym to rodzina korzysta z wody z sieci wodociągowej.
Zakładamy na potrzeby zadania, że zbiornik na wodę retencyjną nigdy się nie przepełni.
Z myślą o uproszczeniu symulacji przyjmujemy, że w każdym dniu najpierw następuje retencja opadów w zbiorniku retencyjnym, a potem zużycie wody. Z wykorzystaniem danych zawartych w plikach i dostępnych narzędzi informatycznych, wykonaj zadania. Odpowiedzi zapisz w kolejnych wierszach pliku tekstowego wyniki4.txt. Odpowiedź do każdego zadania poprzedź numerem tego zadania. Do oceny oddajesz:
plik wyniki4.txt, zawierający odpowiedzi do zadań.
plik z wykresem do zadania 4.2.
pliki z komputerową realizacją Twoich rozwiązań.
Zanim przejdziemy do rozwiązywania poszczególnych podpunktów tego zadania, należy zaimportować dane do arkusza kalkulacyjnego. Dane dostarczone są w formie tekstowej i są dostępne w załączniku do pobrania.
Microsoft Excel
W celu zaimportowania danych wybieramy ze wstążki kartę Dane, a z obszaru Pobieranie i przekształcanie danych wybieramy opcję Z pliku tekstowego/CSV.
Następnie wybieramy pobrany wcześniej plik ekodom.txt i importujemy dane do nowego arkusza. Otrzymujemy w ten sposób gotową tabelę z danymi.
W nowszych wersjach programu Microsoft Excel, po zaimportowaniu danych z pliku tekstowego, tabela tworzona jest automatycznie. Jeśli pracujesz na starszej wersji programu, uzgodnij arkusz, aby wyglądał tak jak na ilustracjach powyżej, dzięki czemu, podczas wprowadzania formuł nie wystąpią błędy.
Aby to zrobić, zaznacz dowolną komórkę wchodzącą w skład zaimportowanych danych z pliku ekodom.txt, przejdź do grupy poleceń Wstawianie na wstążce, a następnie wybierz polecenie Tabela.
Pamiętaj również, aby w zakładce Projekt tabeli zmienić nazwy tabeli. Tabelę w arkuszu ekodom nazwij ekodom.
Po zaimportowaniu wszystkich danych możemy przejść do rozwiązania zadania 4.1, którego treść brzmi:
a) Podaj najdłuższy okres bez opadów (kiedy zbiornik nie był napełniany), datę jego początku i końca.
b) Podaj, ile razy w okresie od kwietnia do września był podlewany ogródek.
Aby rozwiązać podpunkt a) zadania, wystarczy sprawdzić, od ilu dni nie padało w danym dniu. W tym celu tworzymy nową kolumnę, w której obliczona zostanie ta liczba dni.
Do komórki C2 wprowadzamy następującą formułę:
=JEŻELI(B2=0;C1+1;0)Formuła ta sprawdzi, czy w danym dniu wystąpiły opady, a więc czy wartość w kolumnie B jest równa 0. Jeśli tak, to zostanie zwiększona liczba dni, przez które nie padało, a w przeciwnym wypadku, więc jeśli w danym dniu wystąpiły opady, liczba dni zostanie wyzerowana. Formułę skopiujmy do pozostałych komórek w kolumnie.
Jak widać jednak w kilku pierwszych komórkach, wystąpił błąd formuły. Dzieje się tak, ponieważ w komórce C2 próbujemy dodać wartość 1 do tekstu z komórki C1. Aby naprawić ten błąd, do komórki C2 wprowadzimy wartość 1, ponieważ jest to pierwszy dzień w roku, w którym nie padało. Tak prezentuje się więc tabela z poprawnie obliczonymi dniami bez opadów:
Skoro udało nam się obliczyć wszystkie okresy, w których nie padało, możemy teraz znaleźć najdłuższy taki okres. W tym celu, na przykład w komórce K2 użyjemy funkcji MAX, która zwróci największą liczbę dni bez opadów. Formuła wygląda następująco:
=MAX(C2:C366)
Jednak w zadaniu nie proszeni jesteśmy o znalezienie wyłącznie liczby dni, lecz również o datę początku oraz końca okresu, w którym najdłużej nie padało. Najpierw znajdźmy dzień, w którym zakończył się najdłuższy okres bez opadów. W tym celu wykorzystamy dwie funkcje INDEKS oraz PODAJ.POZYCJĘ. Do komórki, na przykład K4 wprowadzamy więc następującą formułę:
=INDEKS(A2:A366;PODAJ.POZYCJĘ(K2;C2:C366;0))Funkcja PODAJ.POZYCJĘ sprawdza, w którym wierszu zakresu od C2 do C366 znajduje się liczba 21, następnie funkcja INDEKS podaje wartość, która stoi na tym samym miejscu w zakresie od A2 do A366. Pamiętajmy o zmianie zwracanej wartości na datę.
Aby znaleźć datę początku okresu, w którym najdłużej nie padało, wystarczy od daty końcowej tego okresu odjąć liczbę dni bez opadów, a dodatkowo do tej wartości dodamy wartość 1, gdyż w innym wypadku znajdziemy ostatni dzień, w którym padało. Formuła wprowadzona, na przykład do komórki J4 wygląda więc następująco:
=K4-K2+1
Sprawdźmy, czy poprawnie znaleźliśmy ten okres.
Jak widać, poprawnie znaleźliśmy okres, w którym najdłużej nie padało, a co za tym idzie, znaleźliśmy odpowiedź na podpunkt a) zadania 4.1.
Przejdźmy więc do rozwiąznia podpunktu b) zadania 4.2:
b) Podaj, ile razy w okresie od kwietnia do września był podlewany ogródek.
Zastosujmy filtr na tabeli z danymi. Wyświetlmy dane dla miesięcy z okresu, w którym mamy znaleźć, ile razy został podlany ogródek. A więc będą to miesiące: kwiecień, maj, czerwiec, lipiec, sierpień oraz wrzesień.
Następnie stwórzmy nową kolumnę, w której sprawdzimy, czy ogródek został podlany. Pamiętajmy jednak, że w tym okresie obowiązuje następujący system podlewania:
Dodatkowo w okresie od kwietnia do września, jeśli w kolejnych dniach nie wystąpią naturalne opady, to piątego dnia bez opadów jest podlewany ogródek. Na podlewanie zużywa się 300 l wody. Jeżeli susza się przedłuża, to kolejne podlewanie jest dziesiątego dnia, piętnastego dnia itd.
Tak więc podsumowując, ogródek podlewany będzie co pięć dni, w przypadku braku opadów. Formuła wprowadzona do komórki D92, którą użyjemy do sprawdzenia, czy w danym dniu należy podlać ogródek wyglądać więc będzie następująco:
=JEŻELI(ORAZ(C92>0;MOD(C92;5)=0);"TAK";"NIE")Formułę tę kopiujemy w dół, aż do komórki D274. Formuła sprawdza, czy wartość w kolumnie C jest większa od zera, a więc czy w danym dniu nie wystąpiły opady, oraz czy liczba w kolumnie C jest podzielna przez 5. Jeśli oba te warunki zostaną spełnione, oznacza to, iż w danym dniu należy podlać ogródek i zwracany jest tekst TAK, w przeciwnym wypadku ogródek nie musi być podlewany i zwracany jest tekst NIE.
Ostatnim krokiem, który pozwoli nam znaleźć odpowiedź na podpunkt b) zadania 4.1 jest sprawdzenie, ile razy w kolumnie D występuje tekst TAK. Możemy tego dokonać przy pomocy prostej funkcji LICZ.JEŻELI:
=LICZ.JEŻELI(D92:D274;"TAK")
W ten sposób znaleźliśmy odpowiedzi do zadania 4.1:
a) 21 dni, początek 12.08.2022, koniec 01.09.2022
b) 18
LibreOffice Calc
W celu zaimportowania danych wybieramy grupę poleceń Arkusz, a następnie wybieramy opcję Dane zewnętrzne.
Następnie wybieramy pobrany wcześniej plik ekodom.txt i importujemy dane do nowego arkusza. Podczas importu, pamiętajmy o ustawieniu odpowiadającego nam formatu daty, w tym przypadku jest to DMR. Otrzymujemy w ten sposób gotową tabele z danymi.
Po zaimportowaniu wszystkich danych możemy przejść do rozwiązania zadania 4.1, którego treść brzmi:
a) Podaj najdłuższy okres bez opadów (kiedy zbiornik nie był napełniany), datę jego początku i końca.
b) Podaj, ile razy w okresie od 1 kwietnia do 30 września był podlewany ogródek.
Aby rozwiązać podpunkt a) zadania, wystarczy sprawdzić od ilu dni nie padało w danym dniu. W tym celu tworzymy nową kolumnę, w której obliczona zostanie ta liczba dni.
Do komórki C2 wprowadzamy następującą formułę:
=JEŻELI(B2=0;C1+1;0)Formuła ta sprawdzi, czy w danym dniu wystąpiły opady, a więc czy wartość w kolumnie B jest równa 0. Jeśli tak, to zostanie zwiększona liczba dni, przez które nie padało, a w przeciwnym wypadku, więc jeśli w danym dniu wystąpiły opady, liczba dni zostanie wyzerowana. Formułę skopiujmy do pozostałych komórek w kolumnie.
Jak widać, jednak w kilku pierwszych komórkach wystąpił błąd formuły. Dzieje się tak, ponieważ w komórce C2 próbujemy dodać wartość 1 do tekstu z komórki C1. Aby naprawić ten błąd, do komórki C2 wprowadzimy wartość 1, ponieważ jest to pierwszy dzień w roku, w którym nie padało. Tak prezentuje się więc tabela z poprawnie obliczonymi dniami bez opadów:
Skoro udało nam się obliczyć wszystkie okresy, w których nie padało, możemy teraz znaleźć najdłuższy taki okres. W tym celu, na przykład w komórce K2 użyjemy funkcji MAKS, która zwróci największą liczbę dni bez opadów. Formuła wygląda następująco:
=MAKS(C2:C366)
Jednak w zadaniu nie proszeni jesteśmy o znalezienie wyłącznie liczby dni, lecz również o datę początku oraz końca okresu, w którym najdłużej nie padało. Najpierw znajdźmy dzień, w którym zakończył się najdłuższy okres bez opadów. W tym celu wykorzystamy dwie funkcje INDEKS oraz PODAJ.POZYCJĘ. Do komórki, na przykład K4 wprowadzamy więc następującą formułę:
=INDEKS(A2:A366;PODAJ.POZYCJĘ(K2;C2:C366;0))Funkcja PODAJ.POZYCJĘ sprawdza, w którym wierszu zakresu od C2 do C366 znajduje się liczba 21, następnie funkcja INDEKS podaje wartość, która stoi na tym samym miejscu w zakresie od A2 do A366. Pamiętajmy o zmianie zwracanej wartości na datę.
Aby znaleźć datę początku okresu, w którym najdłużej nie padało, wystarczy od daty końcowej tego okresu odjąć liczbę dni bez opadów, a dodatkowo do tej wartości dodamy wartość 1, gdyż w innym wypadku znajdziemy ostatni dzień, w którym padało. Formuła wprowadzona, na przykład do komórki J4 wygląda więc następująco:
=K4-K2+1
Sprawdźmy, czy poprawnie znaleźliśmy ten okres.
Jak widać, poprawnie znaleźliśmy okres, w którym najdłużej nie padało, a co za tym idzie, znaleźliśmy odpowiedź na podpunkt a) zadania 4.1.
Przejdźmy więc do rozwiąznia podpunktu b) zadania 4.2:
b) Podaj, ile razy w okresie od kwietnia do września był podlewany ogródek.
Zastosujmy autofiltr na tabeli z danymi. Wyświetlmy dane dla miesięcy z okresu, w którym mamy znaleźć ile razy został podlany ogródek. A więc będą to miesiące: kwiecień, maj, czerwiec, lipiec, sierpień oraz wrzesień.
Następnie stwórzmy nową kolumnę, w której sprawdzimy, czy ogródek został podlany. Pamiętajmy jednak, że w tym okresie obowiązuje następujący system podlewania:
Dodatkowo w okresie od kwietnia do września, jeśli w kolejnych dniach nie wystąpią naturalne opady, to piątego dnia bez opadów jest podlewany ogródek. Na podlewanie zużywa się 300 l wody. Jeżeli susza się przedłuża, to kolejne podlewanie jest dziesiątego dnia, piętnastego dnia itd.
Tak więc podsumowując, ogródek podlewany będzie co pięć dni, w przypadku braku opadów. Formuła wprowadzona do komórki D92, którą użyjemy do sprawdzenia, czy w danym dniu należy podlać ogródek wyglądać więc będzie następująco:
=JEŻELI(I(C92>0;MOD(C92;5)=0);"TAK";"NIE")Formułę tę kopiujemy w dół, aż do komórki D274. Formuła sprawdza, czy wartość w kolumnie C jest większa od zera, a więc czy w danym dniu nie wystąpiły opady, oraz czy liczba w kolumnie C jest podzielna przez 5. Jeśli oba te warunki zostaną spełnione, oznacza to, iż w danym dniu należy podlać ogródek i zwracany jest tekst TAK, w przeciwnym wypadku ogródek nie musi być podlewany i zwracany jest tekst NIE.
Ostatnim krokiem, który pozwoli nam znaleźć odpowiedź na podpunkt b) zadania 4.1 jest sprawdzenie ile razy w kolumnie D występuje tekst TAK. Możemy tego dokonać przy pomocy prostej funkcji LICZ.JEŻELI:
=LICZ.JEŻELI(D92:D274;"TAK")
W ten sposób znaleźliśmy odpowiedzi do zadania 4.1:
a) 21 dni, początek 12.08.2022, koniec 01.09.2022
b) 18
Słownik
polecenie wpisywane w komórkę arkusza kalkulacyjnego, służące m.in. do obliczeń matematycznych. Polecenie to rozpoczynamy znakiem „=”, a w jego skład mogą wchodzić stałe, nazwy funkcji, czy adresy komórek. Formuły przeliczane są dynamicznie