I_R_W08_M19 Arkusz kalkulacyjny na maturze
W tej sekcji jako przykład omówimy zadanie z egzaminu maturalnego z informatyki, maj, część w r. – Statek. Oto treść zadania:
Statek towarowy „Kormoran” pływał po Morzu Śródziemnym pomiędzy dziesięcioma portami, przewożąc pięć różnych rodzajów towarów (T1, T2, , T5). Statek wypływał z portu zawsze tego samego dnia, w którym do tego portu przypłynął.
W pliku statek.txt podano informacje o kursach wykonanych przez statek w okresie od 2016‑01‑01 do 2018‑12‑18. W każdym wierszu, oddzielone znakiem tabulacji, podane są: data – data przebywania statku w porcie (w formacie rrrr‑mm‑dd), port – nazwa portu, towar – rodzaj towaru, Z/W – informacja, czy to był załadunek (Z) czy wyładunek (W), ile ton – informacja, jaka masa (w tonach) towaru była wyładowana/załadowana, cena za tone w talarach – cena towaru w talarach za tonę.
Wykorzystaj dostępne narzędzia informatyczne i podaj odpowiedzi do zadań 6.1.–6.5. Odpowiedzi zapisz w pliku wyniki6.txt, poprzedzając każdą z nich numerem odpowiedniego zadania.
Jak widzimy, zanim będziemy mogli przystąpić do rozwiązywania konkretnych podpunktów zadania, musimy dane zaimportować do arkusza. Dane dostarczone są w formie tekstowej, a w treści zadania podane jest, że kolejne wiersze oddzielone są znakiem tabulacji.
Pobierz dane do zadania:
Tak prezentują się zaimportowane przez nas dane.
Po zaimportowaniu danych do arkusza kalkulacyjnego, możemy przejść do rozwiązywania konkretnych podpunktów zadania.
Zadanie 6.1
Podaj, który towar był ładowany na statek najwięcej razy i jaka była łączna masa tych załadunków.
Zastanówmy się nad możliwymi podejściami do rozwiązania zadania i wybierzmy ten najlepszy.
Formuły
W pierwszej kolejności sprawdźmy, jakie kroki musielibyśmy wykonać, aby znaleźć szukane w zadaniu wartości. Przygotujmy prostą tabelę, w której przeprowadzimy odpowiednie operacje. Dodajmy nagłówki tabeli, np. do komórek I1 oraz J1. Nagłówkami niech będą towar oraz liczba załadowań. W komórkach z zakresu I2:I6 wpiszemy nazwy towarów, a więc kolejno T1, T2, T3, T4, T5. W kolumnie J obliczymy, ile razy towar został załadowany na statek. W tym przypadku najlepiej sprawdzi się formuła z wykorzystaniem funkcji LICZ.WARUNKI. Formuła wpisana do komórki J2 prezentuje się następująco:
Formuła ta zwróci liczbę wystąpień w komórkach C2:C203 wartości znajdującej się w komórce I2, pod warunkiem, że równocześnie w komórkach z zakresu D2:D203 występuje wartość Z. Skopiujmy formułę do pozostałych komórek z zakresu J1:J6. Tak prezentuje się nasza tabela:
Jak widać, na statek najwięcej razy ładowany był towar o nazwie T4. Znajdźmy więc dla niego liczbę ton załadowanych na statek. W tym wypadku skorzystamy z funkcji SUMA.WARUNKÓW. Nie musimy wykonywać obliczeń dla każdego towaru, ponieważ wiemy, że tylko towar o nazwie T4 je spełnia. W komórce K5 wpiszmy więc odpowiednią formułę:
Formuła zsumuje wartości w komórkach z zakresu E2:E203, jeżeli spełnione są 2 warunki, w komórkach z zakresu C2:C203 znajduje się wartość z komórki I5 oraz w komórkach z zakresu D2:D203 znajduje się tekst „Z”, a więc te same warunki co w poprzedniej formule, jednak nie obliczamy teraz liczby wystąpień tylko sumujemy liczbę ton załadowanych na statek. Tak prezentują się uzupełnione przez nas komórki:
Jak widzimy, najwięcej razy załadowany został towar o nazwie T4, a łączna liczba załadowanych ton to 905, więc w pliku z odpowiedziami do zadania wpiszemy T4 905.
Tabela przestawna
Mimo poprawnych wyników, przeprowadzone operacje wymagają znajomości funkcji i tworzenia formuł, gdzie łatwo o pomyłkę. Sprawdźmy więc inny sposób dojścia do poprawnego rozwiązania, mianowicie narzędzie Tabela przestawna.
Microsoft Excel
Zaznaczmy dowolną komórkę zawierającą dane, a następnie po przejściu do zakładki Wstawianie wybierzmy narzędzie Tabela przestawna. Tabelę wstawmy do nowego arkusza. W menu Pola tabeli przestawnej musimy umieścić pola w odpowiednich obszarach. Wiemy, że mamy znaleźć nazwę towaru, który był najwięcej razy załadowany na statek oraz znaleźć liczbę ton. Do obszaru Wiersze dodamy więc pole towar. Informacje, których szukamy, zawierają pola Z/W oraz ile ton, przeniesiemy je więc do obszaru Wartości. Dodatkowo upewnijmy się, że pole Z/W podsumowane zostało za pomocą typu obliczeń Liczba, a pole ile ton za pomocą Suma.
Tak prezentuje się tabela przestawna na tym etapie:
To jednak nie koniec, tabela przestawna bierze pod uwagę zarówno załadunek, jak i wyładunek, musimy więc dodatkowo nałożyć na tabelę przestawną filtr. Do obszaru Filtry dodajmy więc pole Z/W, a następnie za pomocą filtra wyświetlamy tylko wartości dla załadunku. Tak powinna wyglądać nasza tabela przestawna:
Jak widzimy, otrzymaliśmy ten sam wynik co w przypadku obliczania ich przy pomocy formuł, a więc największą liczbę razy został załadowany towar o nazwie T4 i łączna liczba ton wynosi 905, jednak korzystanie z tabeli przestawnej jest bardziej intuicyjne, przez co zmniejszamy prawdopodobieństwo popełnienia błędu.
LibreOffice Calc
Zaznaczmy dowolną komórkę zawierającą dane, a następnie wybierzmy narzędzie Tabela przestawna po wybraniu Wstaw z menu głównego. W oknie Układ tabeli przestawnej musimy umieścić pola w odpowiednich obszarach. Wiemy, że mamy znaleźć nazwę towaru, który był najwięcej razy załadowany na statek oraz znaleźć liczbę ton. Do obszaru Pola wierszy dodamy więc pole towar. Informacje, których szukamy zawierają pola Z/W oraz ile ton, przeniesiemy je więc do obszaru Pola danych. Dodatkowo upewnijmy się, że pole Z/W podsumowane zostało za pomocą funkcji Liczba, a pole ile ton za pomocą Suma.
Tak prezentuje się tabela przestawna na tym etapie:
To jednak nie koniec, tabela przestawna bierze pod uwagę zarówno załadunek, jak i wyładunek, musimy więc dodatkowo nałożyć na tabelę przestawną filtr. Do obszaru Filtry dodajmy więc pole Z/W, a następnie za pomocą filtra wyświetlamy tylko wartości dla załadunku. Tak powinna wyglądać nasza tabela przestawna:
Jak widzimy, otrzymaliśmy ten sam wynik co w przypadku obliczania ich przy pomocy formuł, a więc największą liczbę razy został załadowany towar o nazwie T4 i łączna liczba ton wynosi 905, jednak korzystanie z tabeli przestawnej jest bardziej intuicyjne przez co zmniejszamy prawdopodobieństwo popełnienia błędu.
Zadanie 6.2
Podaj, ile było kursów, w których statek spędził więcej niż 20 pełnych dni na morzu, bez zawijania do portów.
Na przykład:
Jeśli statek wypłynął z jednego portu w dniu 2016‑01‑10 i wpłynął do następnego portu w dniu 2016‑01‑16, to spędził na morzu 5 pełnych dni (11.01, 12.01, 13.01, 14.01, 15.01)
W przypadku tego zadania korzystanie z tabeli przestawnej nie pozwoli nam na osiągnięcie poprawnego wyniku, musimy więc wykorzystać inne operację, aby osiągnąć poprawny wynik. Sprawdźmy, czy wykorzystując formuły w łatwy sposób rozwiążemy zadanie.
Zastanówmy się, w jaki sposób możemy dotrzeć do poprawnej odpowiedzi. W pierwszej kolejności musimy sprawdzić, czy statek wypłynął z portu, a więc sprawdzamy, czy data w dwóch kolejno występujących po sobie komórkach jest taka sama. Jeśli tak, jak na przykład w przypadku komórek A2 i A3, to nie wykonujemy żadnych obliczeń, ponieważ statek najpewniej nie wypłynął z portu, nastąpiło wyłącznie załadowanie lub wyładowanie towaru. Jeśli jednak daty różnią się, to musimy obliczyć, ile dni statek spędził na morzu. Możemy tego dokonać poprzez obliczenie różnicy między datą zawinięcia do portu (data końcowa), a datą wypłynięcia (data początkowa). Zwróćmy uwagę na przykład podany w treści zadania. Statek wypłynął z portu w dniu 2016‑01‑10 i wpłynął do następnego portu w dniu 2016‑01‑16, a na morzu spędził 5 dni. Odejmując te dwie daty od siebie, otrzymamy wartość 6, a więc dodatkowo, w tworzonej przez nas formule, po obliczeniu różnicy między datami musimy odjąć wartość 1, aby otrzymać poprawny wynik.
To jednak nie koniec, po znalezieniu liczby dni spędzonych na morzu podczas każdego kursu musimy sprawdzić, które z tych kursów trwały więcej niż 20 pełnych dni.
Przed przejściem do dalszej części sekcji spróbuj utworzyć poprawne formuły na podstawie powyższego opisu.
Przejdźmy do rozwiązania zadania w arkuszu kalkulacyjnym. W pierwszej kolejności, do komórki G1 wpiszmy odpowiednią nazwę kolumny, np. „ile dni na morzu”. Do komórki G2 wpiszmy formułę, która sprawdzi, ile dni na morzu spędził statek. Tak może wyglądać poprawnie stworzona formuła:
Skopiujmy formułę do pozostałych komórek z zakresu G2:G203
Zwróćmy uwagę, że komórka G2 zawiera błąd #ARG. Wynika on z tego, iż w tej komórce wykonujemy sprawdzenie, czy wartość w komórce A2 jest większa od tej w komórce A1, jednak typy danych znajdujących się w tych komórkach są różne, w komórce A1 znajduje się tekst, a w komórce A2 – data. Nie wpłynie to jednak na rozwiązanie zadania.
Skoro tę część zadania mamy wykonaną, to sprawdźmy, ile było kursów, które trwały więcej niż 20 dni. W komórce np. I2 wpiszmy formułę:
Formuła ta policzy, ile komórek w zakresie od G2 do G203 ma wartość większą niż 20. W komórce G2 została zwrócona wartość 22, a więc statek odbył dokładnie 22 kursy, które trwały więcej niż 20 dni. Wartość 22 wpiszemy więc do pliku z odpowiedziami.
Zadanie 6.3
W dniach 2016‑02‑01 i 2018‑08‑01 statek nie zawijał do portu. Dla każdego z tych dni podaj:
rodzaj i liczbę ton towaru, którego było najwięcej na statku,
rodzaj i liczbę ton towaru, którego było najmniej na statku (przyjmujemy, że towar był na statku, jeśli liczba ton tego towaru była większa od 0).
Zadanie 6.5
Kapitan przy załadunku płacił za towar, a przy wyładunku otrzymywał za niego zapłatę.
a) Przyjmij, że kapitan przed pierwszym rejsem miał w kasie 500 000 talarów, a następnie:
oblicz, ile talarów miał 18 grudnia 2018 roku po zakończeniu wszystkich transakcji
znajdź dzień, w którym po wypłynięciu z portu stan kasy kapitana był największy – podaj ten dzień oraz stan kasy kapitana tego dnia.
b) Podaj, ile minimum talarów powinien mieć kapitan przed pierwszym rejsem, aby mógł wykonać wszystkie transakcje, tzn. zapłacić za każdy załadunek, każdego dnia.
Spróbuj rozwiązać zadania 6.3 i 6.5. Porównaj swoje rozwiązanie z tym przedstawionym na filmie (samouczku). Sprawdź, czy otrzymałeś takie same wyniki.
Spróbuj rozwiązać zadania 6.3 i 6.5. Porównaj swoje rozwiązanie z tym przedstawionym na filmie (samouczku). Sprawdź, czy otrzymałeś takie same wyniki.
Microsoft Excel
Film dostępny pod adresem /preview/resource/R1US3M2LDB2AO
Film nawiązujący do treści materiału dotyczącej programu Microdoft Excel.
LibreOffice Calc
Film dostępny pod adresem /preview/resource/RGGGR3O5EOD4K
Film nawiązujący do treści materiału dotyczącej programu LibreOffice Calc.