Wprowadzenie
Prezentacja multimedialna
Przeczytaj
FormułyformułaFormuły znajdujące się w arkuszu kalkulacyjnym zgrupowano w kilku kategoriach. Dzięki temu, w zależności od potrzeb, łatwo znajdziemy szukaną formułę.
Sprawdźmy, jak wykorzystane mogą zostać formuły tekstowe oraz logiczne w arkuszu kalkulacyjnym na podstawie zadania typu maturalnego.
Treść zadania
Pewna firma, która oferuje usługi telekomunikacyjne, wyeksportowała do pliku polaczenia.txt pewną część połączeń między telefonami stacjonarnymi. Tak prezentuje się kilka pierwszych wierszy pliku:
typ_rozmowy;miasto_wych;wojewodztwo_wych;miasto_przych;wojewodztwo_przych;data;godzina;czas_rozmowy
WOJ2week;Krobia;Wielkopolskie;Wozniki;Slaskie;02.01.2010;04:38:49;01:51:04
WOJ1norm;Ostrow Lubelski;Lubelskie;Limanowa;Malopolskie;04.01.2010;13:41:06;00:51:29
WOJ1norm;Wolomin;Mazowieckie;Starachowice;Swietokrzyskie;05.01.2010;20:40:44;00:41:06
WOJ2norm;Nowa Ruda;Dolnoslaskie;Slubice;Lubuskie;08.01.2010;13:58:26;01:29:10Przycisk do pobrania pliku TXT z treścią zadania. Pobierz załącznik
W każdym wierszu pliku znajdują się następujące dane: typ rozmowy, nazwa miasta, z którego połączenie jest wykonywane, nazwa województwa, z którego wykonywane jest połączenie, nazwa miasta, do którego wykonywane jest połączenie, nazwa województwa, do którego wykonywane jest połączenie, data wykonywania połączenia (w formacie DD:MM:RRRR), godzina rozpoczęcia rozmowy (w formacie GG:MM:SS) oraz czas rozmowy (również w formacie GG:MM:SS). Pierwszy wiersz jest wierszem nagłówkowym, a dane w wierszach rozdzielono znakami średnika.
Dane znajdujące się w pierwszej kolumnie – typ_rozmowy – zawierają ważne informacje o połączeniu. Pierwsze trzy litery oznaczają, jakie połączenie zostało wykonane:
WOJ– połączenie między miastami znajdującymi się w różnych województwach;MIA– połączenie między miastami znajdującymi się w tym samym województwie;DOM– połączenie między domami znajdującymi się w tej samej miejscowości.
Cyfra na czwartym miejscu oznacza czas rozmowy telefonicznej:
0– połączenie trwające poniżej minut;1– połączenie trwające od minut do godziny włącznie;2– połączenie trwające powyżej godziny.
Ostatnie cztery litery oznaczają rodzaj połączenia:
norm– połączenia wykonane w dni od poniedziałku do piątku, lecz w piątek przed godziną ;week– połączenia wykonane w piątek od godziny , sobotę lub niedzielę.
Wykorzystaj dostępne narzędzia informatyczne i podaj odpowiedzi do poniższych zadań. Odpowiedzi zapisz w pliku wyniki_polaczenia.txt. Każdą odpowiedź poprzedź numerem oznaczającym zadanie.
Do oceny oddajesz:
plik
wyniki_polaczenia.txtzawierający odpowiedzi do zadań Połączenia.1-Połączenia.3,plik z komputerową realizacją zadania.
Zadanie Połączenia.1
Podaj liczbę połączeń:
a) wykonanych między województwami, między miastami w tym samym województwie oraz między domami w tej samej miejscowości;
b) trwających mniej niż pół godziny, od pół godziny do jednej godziny włącznie oraz więcej niż godzinę;
c) w zależności od rodzaju połączenia, a więc ile wykonanych zostało połączeń norm oraz week.
Microsoft Excel
Formuły dla danych tekstowych
W celu wykonania powyższego zadania, wykorzystamy formuły tekstowe. W tym przypadku pomocne będą formuły LEWY, FRAGMENT.TEKSTU, a także PRAWY.
Najpierw jednak należy zaimportować dane do arkusza. Aby dowiedzieć się więcej o importowaniu danych, przejdź do e‑materiałów Import danych z plików tekstowych w arkuszu kalkulacyjnymP10xnCm7GImport danych z plików tekstowych w arkuszu kalkulacyjnym lub Import i synchronizacja danych w arkuszu kalkulacyjnymPJihwl306Import i synchronizacja danych w arkuszu kalkulacyjnym.
Tak powinien wyglądać fragment arkusza kalkulacyjnego po zaimportowaniu danych:
Rozwiązanie zadania Połączenia.1 a)
Wprowadźmy kolumnę, którą nazwiemy Połączenie między. Następnie do komórki I2 wprowadzamy formułę =LEWY(A2;3). Wyświetli ona pierwsze trzy znaki tekstu znajdującego się w komórce A2. Formułę kopiujemy do pozostałych komórek w kolumnie.
Na podstawie tych danych tworzymy tabelę przestawną. Wstawiamy ją np. do istniejącego arkusza, w komórkę O1. Zarówno do obszaru Wiersze jak i Wartości wprowadzimy pole Połączenie między, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.1 a).
Jak widzimy, zostało wykonanych 5 połączeń między domami w tej samej miejscowości, 12 połączeń między miastami w tym samym województwie oraz 83 połączenia między miastami w różnych województwach. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Rozwiązanie zadania Połączenia.1 b)
Aby rozwiązać kolejny podpunkt zadania, dodajemy nową kolumnę o nazwie Kod trwania rozmowy. Tym razem do komórki J2 wprowadzimy formułę =FRAGMENT.TEKSTU(A2;4;1), a następnie skopiujemy ją do pozostałych komórek w kolumnie. Wynikiem formuły jest jeden znak, stojący na czwartym miejscu w kolumnie A.
Tworzymy nową tabelę przestawną i wstawiamy ją do komórki O7. Tym razem do obszaru Wiersze oraz Wartości wprowadzimy pole Kod trwania rozmowy, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.2 b).
Jak widzimy, zostało wykonanych 30 połączeń krótszych niż 30 minut, 23 połączenia w granicach od 30 minut do 1 godziny włącznie oraz 47 połączeń trwających dłużej niż godzinę. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Rozwiązanie zadania Połączenia.1 c)
Stwórzmy trzecią, ostatnią kolumnę do zadania Połączenie.1 o nazwie Rodzaj połączenia. Do komórki K2 wprowadzimy formułę =PRAWY(A2;4), a następnie skopiujemy ją do pozostałych komórek w kolumnie. Wynikiem formuły są cztery znaki znajdujące się z prawej strony tekstu z kolumny A.
Tworzymy kolejną tabelę przestawną i wstawiamy ją do komórki O13, a następnie do obszarów Wiersze oraz Wartości wprowadzimy pole Rodzaj połączenia, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.1 c).
Jak widzimy, zostały wykonane 74 połączenia normalne oraz 26 połączeń weekendowych. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Alternatywne rozwiązanie zadania Połączenia.1
Powyższe zadanie można rozwiązać także w inny sposób, tym razem zamiast formuł tekstowych, wykorzystując formuły logiczne.
Wróćmy do stanu arkusza kalkulacyjnego zaraz po zaimportowaniu danych, a więc sprzed dodania tabel przestawnych oraz dodatkowych kolumn.
Rozwiązanie zadania Połączenia.1 a)
W pierwszej kolejności dodajemy nową kolumnę o nazwie Połączenie między, a następnie w komórce I2 wprowadzamy formułę =JEŻELI(C2<>E2;"Województwami";JEŻELI(B2<>D2;"Miastami";"Domami")), a następnie kopiujemy ją do pozostałych komórek w kolumnie.
Do znalezienia liczby połączeń nie musimy wykorzystywać tabeli przestawnej, możemy to zrobić także za pomocą formuł. Do kolumny wprowadźmy tylko unikatowe wartości z kolumny I. Możemy to zrobić, zaznaczając całą kolumnę I, a następnie wybierając polecenie Zaawansowane w obszarze Sortowanie i filtrowanie po przejściu do zakładki Dane na wstążce.
W otwartym oknie filtra zaawansowanego wybieramy opcję kopiowania tylko unikatowych wartości do komórki O1.
Następnie w komórce P2 wprowadzamy formułę, która obliczy liczbę wystąpień kolejnych tekstów w komórkach I2:I101. Formuła wprowadzona do komórki P2 wyglądać może następująco:
=LICZ.JEŻELI($I$2:$I$101;O2)
Formułę kopiujemy do komórek P3 oraz P4, dzięki czemu otrzymujemy następujące wyniki, będące odpowiedzią na zadanie Połączenia.1 a)
Rozwiązanie zadania Połączenia.1 b)
Podobnie postąpimy podczas rozwiązywania tego podpunktu. Dodamy nową kolumnę o nazwie Kod trwania rozmowy, a następnie wprowadzimy formułę, która sprawdzi czas rozmowy, a w dalszej kolejności wpisze odpowiedni kod. Formuła wpisana do komórki J2 wyglądać może następująco:
=JEŻELI(H2<CZAS(0;30;0);0;JEŻELI(H2<=CZAS(1;0;0);1;2))Formułę kopiujemy do pozostałych komórek w kolumnie. Formuła ta sprawdzi, czy czas rozmowy w kolumnie H jest mniejszy od minut. Jeśli tak, to wpisze do komórki wartość 0. W przeciwnym wypadku, a więc jeśli czas trwania rozmowy jest większy lub równy 30 minut, kolejny warunek sprawdzi, czy czas trwania rozmowy jest mniejszy lub równy godzinie. Jeśli tak, to wyświetlona zostanie wartość 1, a w przeciwnym wypadku, a więc gdy rozmowa trwała godzinę lub dłużej, zostanie wprowadzona wartość 2.
Ponownie tworzymy tabelę, tym razem stworzoną z niezduplikowanych wartości kolumny J, a następnie do komórki P7 wprowadzamy formułę:
=LICZ.JEŻELI($J$2:$J$101;O7)
Kopiujemy ją do komórek P8 oraz P9, dzięki czemu otrzymujemy odpowiedzi do zadania Połączenia.1 b).
Rozwiązanie zadania Połączenia.1 c)
Dodajmy ostatnią dodatkową kolumnę o nazwie Rodzaj połączenia, a następnie w komórce K2 wprowadzimy formułę:
=JEŻELI(LUB(DZIEŃ.TYG(F2)=1;DZIEŃ.TYG(F2)=7;ORAZ(DZIEŃ.TYG(F2)=6;G2>=CZAS(17;0;0)));"weekendowe";"normalne")Skopiujmy ją do pozostałych komórek w kolumnie. Formuła sprawdza, czy dzień tygodnia to niedziela (DZIEŃ.TYG(F2)=1), sobota (DZIEŃ.TYG(F2)=7) lub piątek (DZIEŃ.TYG(F2)=6), jednocześnie sprawdzając, czy w piątek godzina jest większa lub równa 17:00 (G2>=CZAS(17;0;0)). Jeśli podane warunki zostaną spełnione, formuła zwróci tekst „weekendowe”, w przeciwnym wypadku zwróci tekst „normalne”.
Tworzymy listę rodzajów połączeń tak samo jak w poprzednich przypadkach, a więc wstawiamy do komórki O11 tylko unikatowe wartości z kolumny K, a następnie do komórki P12 wpisujemy formułę:
=LICZ.JEŻELI($K$2:$K$101;O12)
Następnie kopiujemy ją do komórki P13, dzięki czemu otrzymujemy odpowiedzi do zadania Połączenia.1 c).
LibreOffice Calc
Formuły dla danych tekstowych
W celu wykonania powyższego zadania wykorzystamy formuły tekstowe. W tym przypadku pomocne będą formuły LEWY, FRAGMENT.TEKSTU, a także PRAWY.
Najpierw jednak należy zaimportować dane do arkusza. Aby dowiedzieć się więcej o importowaniu danych, przejdź do e‑materiałów Import danych z plików tekstowych w arkuszu kalkulacyjnymP10xnCm7GImport danych z plików tekstowych w arkuszu kalkulacyjnym lub Import i synchronizacja danych w arkuszu kalkulacyjnymPJihwl306Import i synchronizacja danych w arkuszu kalkulacyjnym.
Tak powinien wyglądać fragment arkusza kalkulacyjnego po zaimportowaniu danych:
Warto upewnić się, czy w kolumnach G i H znajdują się wartości w formacie godzinowym.
Rozwiązanie zadania Połączenia.1 a)
Wprowadźmy kolumnę, którą nazwiemy Połączenie między. Następnie do komórki I2 wprowadzamy formułę =LEWY(A2;3). Wyświetli ona pierwsze trzy znaki tekstu znajdującego się w komórce A2. Formułę kopiujemy do pozostałych komórek w kolumnie.
Na podstawie tych danych tworzymy tabelę przestawną. Wstawiamy ją np. do istniejącego arkusza, w komórkę O1. Zarówno do obszaru Pole wierszy, jak i Pola danych wprowadzimy pole Połączenie między, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.1 a).
Jak widzimy, zostało wykonanych 5 połączeń między domami w tej samej miejscowości, 12 połączeń między miastami w tym samym województwie oraz 83 połączenia między miastami w różnych województwach. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Rozwiązanie zadania Połączenia.1 b)
Aby rozwiązać kolejny podpunkt zadania, dodajemy nową kolumnę o nazwie Kod trwania rozmowy. Tym razem do komórki J2 wprowadzimy formułę =FRAGMENT.TEKSTU(A2;4;1), a następnie skopiujemy ją do pozostałych komórek w kolumnie. Wynikiem formuły jest jeden znak, stojący na czwartym miejscu w kolumnie A.
Tworzymy nową tabelę przestawną i wstawiamy ją do komórki O7. Tym razem do obszaru Pola wierszy oraz Pola danych wprowadzimy pole Kod trwania rozmowy, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.2 b).
Jak widzimy, zostało wykonanych 30 połączeń krótszych niż 30 minut, 23 połączenia w granicach od minut do godziny włącznie oraz 47 połączeń trwających dłużej niż godzinę. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Rozwiązanie zadania Połączenia.1 c)
Stwórzmy trzecią, ostatnią kolumnę do zadania Połączenie.1 o nazwie Rodzaj połączenia. Do komórki K2 wprowadzimy formułę =PRAWY(A2;4), a następnie skopiujemy ją do pozostałych komórek w kolumnie. Wynikiem formuły są cztery znaki znajdujące się z prawej strony tekstu z kolumny A.
Tworzymy kolejną tabelę przestawną i wstawiamy ją do komórki O13, a następnie do obszarów Wiersze oraz Wartości wprowadzimy pole Rodzaj połączenia, dzięki czemu w tabeli przestawnej otrzymamy informacje szukane w zadaniu Połączenia.1 c).
Jak widzimy, zostały wykonane 74 połączenia normalne oraz 26 połączeń weekendowych. Właśnie te dane zamieścimy w pliku z odpowiedziami wyniki_polaczenia.txt.
Alternatywne rozwiązanie zadania Połączenia.1
Powyższe zadanie można rozwiązać także w inny sposób, tym razem zamiast formuł tekstowych, wykorzystując formuły logiczne.
Wróćmy do stanu arkusza kalkulacyjnego zaraz po zaimportowaniu danych, a więc sprzed dodania tabel przestawnych oraz dodatkowych kolumn.
Rozwiązanie zadania Połączenia.1 a)
W pierwszej kolejności dodajemy nową kolumnę o nazwie Połączenie między, a następnie w komórce I2 wprowadzamy formułę =JEŻELI(C2<>E2;"Województwami";JEŻELI(B2<>D2;"Miastami";"Domami")) i kopiujemy ją do pozostałych komórek w kolumnie.
Do znalezienia liczby połączeń nie musimy wykorzystywać tabeli przestawnej, możemy to zrobić także za pomocą formuł. Do kolumny wprowadźmy tylko unikatowe wartości z kolumny I, a więc kolejno „Połączenie między”, „Województwami”, „Domami” oraz „Miastami”.
Następnie w komórce P2 wprowadzamy formułę, która obliczy liczbę wystąpień kolejnych tekstów w komórkach I2:I101. Formuła wprowadzona do komórki P2 wyglądać może następująco:
=LICZ.JEŻELI($I$2:$I$101;O2)
Formułę kopiujemy do komórek P3 oraz P4, dzięki czemu otrzymujemy następujące wyniki, będące odpowiedzią na zadanie Połączenia.1 a)
Rozwiązanie zadania Połączenia.1 b)
Podobnie postąpimy podczas rozwiązywania tego podpunktu. Wprowadzimy nową kolumnę o nazwie Kod trwania rozmowy, a następnie wprowadzimy formułę, która sprawdzi czas rozmowy, a w dalszej kolejności wpisze odpowiedni kod. Formuła wpisana do komórki J2 wyglądać może następująco:
=JEŻELI(H2<CZAS(0;30;0);0;JEŻELI(H2<=CZAS(1;0;0);1;2))Formułę kopiujemy do pozostałych komórek w kolumnie. Formuła ta sprawdzi, czy czas rozmowy w kolumnie H jest mniejszy od 30 minut. Jeśli tak, to wpisze do komórki wartość 0. W przeciwnym wypadku, a więc jeśli czas trwania rozmowy jest większy lub równy 30 minut, kolejny warunek sprawdzi, czy czas trwania rozmowy jest mniejszy lub równy godzinie. Jeśli tak, to wyświetlona zostanie wartość 1, a w przeciwnym wypadku, a więc gdy rozmowa trwała godzinę lub dłużej, zostanie wprowadzona wartość 2.
Ponownie tworzymy tabelę, tym razem stworzoną z niezduplikowanych wartości kolumny J, a następnie do komórki P7 wprowadzamy formułę:
=LICZ.JEŻELI($J$2:$J$101;O7)
Kopiujemy ją do komórek P8 oraz P9, dzięki czemu otrzymujemy odpowiedzi do zadania Połączenia.1 b).
Rozwiązanie zadania Połączenia.1 c)
Dodajmy ostatnią dodatkową kolumnę o nazwie Rodzaj połączenia, a następnie w komórce K2 wprowadzimy formułę:
=JEŻELI(LUB(DZIEŃ.TYG(F2)=1;DZIEŃ.TYG(F2)=7;I(DZIEŃ.TYG(F2)=6;G2>=CZAS(17;0;0)));"weekendowe";"normalne")Skopiujmy ją do pozostałych komórek w kolumnie. Formuła sprawdza, czy dzień tygodnia to niedziela (DZIEŃ.TYG(F2)=1), sobota (DZIEŃ.TYG(F2)=7) lub piątek (DZIEŃ.TYG(F2)=6), jednocześnie sprawdzając, czy w piątek godzina jest większa lub równa (G2>=CZAS(17;0;0)). Jeśli podane warunki zostaną spełnione, formuła zwróci tekst „weekendowe”, w przeciwnym wypadku zwróci tekst „normalne”.
Tworzymy listę rodzajów połączeń tak samo jak w poprzednich przypadkach, a więc wstawiamy do komórki O11 tylko unikatowe wartości z kolumny K, a następnie do komórki P12 wpisujemy formułę:
=LICZ.JEŻELI($K$2:$K$101;O12)
Następnie kopiujemy ją do komórki P13, dzięki czemu otrzymujemy odpowiedzi do zadania Połączenia.1 c).
Jak widzimy, zadanie to można rozwiązać na różne sposoby. Ważne jest, aby wybrać ten, w którym czujemy się pewnie i wiemy, że jest mniejsza szansa na popełnienie błędu. W tym przypadku większą szansę na poprawne rozwiązanie zadania mamy wtedy, gdy wykorzystamy pierwszy sposób, ponieważ w przypadku sposobu drugiego możemy łatwo popełnić błąd podczas tworzenia formuł, przez co obliczenie liczby połączeń może nie być poprawne.
Warto więc pamiętać, że może istnieć kilka sposobów rozwiązania zadań maturalnych, więc gdy stosowany przez nas sposób wydaje się być zbyt skomplikowany, dobrze jest się zastanowić, czy można wykonać te same operacje łatwiej.
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