Spis treści

Projektowanie formuł wykorzystujących sprawdzanie warunków

Arkusz kalkulacyjny wspiera wiele działań ludzi wykonujących zadania zawodowe w różnych dziedzinach: bankowości, projektowaniu inżynierskim, badaniach naukowych, statystyce itp. Może być również twoim sprzymierzeńcem w rozwiązywaniu zadań z matematyki, fizyki, chemii, w tworzeniu różnych zestawień z geografii lub biologii. Gdy poznasz jego tajniki, na pewno wielokrotnie go wykorzystasz, tworząc przy tym tabele wypełnione danymi i nietypowymi formułami.

Demonstrowane przykłady wprowadzą cię w tajniki projektowania formuł z użyciem nowych, dla ciebie, funkcji dostępnych w arkuszu kalkulacyjnym.

Funkcja JEŻELI

Stosując funkcję JEŻELI, należy podać trzy argumenty. Pierwszym jest test logiczny, a więc sprawdzenie, czy określony warunek jest spełniony, drugim argumentem jest informacja określająca wartość komórki w przypadku, gdy warunek jest spełniony, zaś trzecim argumentem jest wartość komórki w przypadku, gdy warunek nie jest spełniony.

Ćwiczenie 1

Wyobraź sobie, że organizujesz konkurs dla swoich kolegów. Konkurs jest dwuetapowy.  W drugim etapie mogą wziąć udział tylko ci uczestnicy, którzy uzyskają w pierwszym etapie co najmniej połowę maksymalnej liczby punktów.

Zaprojektuj tabelę prezentującą wyniki uczestników konkursu. Zastosuj funkcję JEŻELI do uzyskania informacji, kto zakwalifikował się do następnego etapu konkursu. Do graficznego oznaczenia zakwalifikowanych uczestników wykorzystaj polecenie formatowania warunkowego. W ten sposób łatwo zlokalizujesz w tabeli koleżanki i kolegów, którzy zakwalifikowali się do drugiego etapu konkursu.

Konstruując własną tabelę, możesz wzorować się na poniższej ilustracji. Możesz również pobrać plik z tabelą wypełnioną danymi.

Ćwiczenie - arkusz "konkurs"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 10.21 KB w języku polskim

Ćwiczenie - arkusz "konkurs"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 22.24 KB w języku polskim

Konkurs dla uczniów szkół podstawowych

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Zadanie spróbuj wykonać samodzielnie. Jeżeli masz problemy, skorzystaj z podpowiedzi.

Pokaż podpowiedź

Pokaż odpowiedź

Funkcja LICZ.JEŻELI

W projektowanej w poprzednim przykładzie tabeli możesz wyliczyć liczbę koleżanek i kolegów zakwalifikowanych do drugiego etapu konkursu. Prezentowana tabela zawiera niewiele nazwisk i może ci się wydawać, że to zadanie jest niepotrzebne. Przecież widać, że zakwalifikowało się

osób. Musisz sobie jednak zdawać sprawę, że taka tabela może zawierać o wiele więcej informacji. Jeśli w konkursie weźmie udział kilkaset osób, nie będzie już tak łatwo policzyć, bez popełnienia błędów, ile z nich zakwalifikowało się do drugiego etapu. Pomoże ci w tym odpowiednio zaprojektowana formuła, w której wykorzystasz funkcję LICZ.JEŻELI. Poszukaj jej wśród dostępnych funkcji. Dla ułatwienia poszukiwań sprawdź funkcje w kategorii Statystyka. Funkcja LICZ.JEŻELI, na podstawie dwóch argumentów, określa, ile razy w podanym zakresie występuje komórka spełniająca zadane kryterium. W prezentowanym przykładzie zakresem jest blok komórek zawierający informację o zakwalifikowaniu się do drugiego etapu konkursu, kryterium jest słowo TAK.

Efektem wykonania zadania jest uzyskanie informacji o liczbie zakwalifikowanych osób do drugiego etapu konkursu.

Konkurs dla uczniów szkół podstawowych

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Ćwiczenie 2

Wykorzystaj funkcję LICZ.JEŻELI do określenia liczby zakwalifikowanych osób do drugiego etapu konkursu. Do wykonania zadania wykorzystaj tabelę z poprzedniego ćwiczenia. Jeżeli masz problemy, skorzystaj z podpowiedzi.

Ćwiczenie - arkusz "konkurs"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 11.06 KB w języku polskim

Ćwiczenie - arkusz "konkurs"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 25.95 KB w języku polskim

Pokaż podpowiedź

Pokaż odpowiedź

Funkcję LICZ.JEŻELI możesz wykorzystać w bardzo wielu sytuacjach. Możesz na przykład określić liczbę swoich ocen celujących lub bardzo dobrych, wykorzystać do zliczania określonych gatunków zwierząt i roślin, a także do zliczania głosów podczas wyborów do samorządu szkolnego.

Funkcja SUMA.JEŻELI

Analizując dane z przeprowadzonego konkursu, możesz również obliczyć łączną liczbę punktów, jaką uzyskały tylko osoby zakwalifikowane do drugiego etapu konkursu. Taką operację w arkuszu kalkulacyjnym można przeprowadzić, projektując formułę z użyciem funkcji SUMA.JEŻELI.

Funkcja ta sumuje wartości z interesującej nas kolumny tabeli, które odpowiadają komórkom innej kolumny, w której jest sprawdzany określony warunek. W prezentowanym przykładzie dodawane będą wartości z kolumny

, przechowującej łączne wyniki uczestników konkursu. Przed dodaniem kolejnej wartości z tej kolumny będzie sprawdzany warunek, czy odpowiadająca komórka w kolumnie „” zawiera wartość „TAK” informującą o zakwalifikowaniu danej osoby do drugiego etapu konkursu.

Efektem wykonania zadania jest uzyskanie informacji o łącznej liczbie uzyskanych punktów przez osoby zakwalifikowane do drugiego etapu konkursu.

Konkurs dla uczniów szkół podstawowych

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Ćwiczenie 3

Wykorzystaj funkcję SUMA.JEŻELI do obliczenia łącznej liczby punktów osób, które zakwalifikowały się do drugiego etapu konkursu. Do wykonania zadania wykorzystaj tabelę z poprzedniego ćwiczenia. Jeżeli masz problemy, skorzystaj z podpowiedzi.

Pokaż podpowiedź

Pokaż odpowiedź

Funkcję SUMA.JEŻELI możesz wykorzystać w wielu sytuacjach. Pomyśl, może sam ułożysz problem, do rozwiązania którego będzie potrzebna funkcja SUMA.JEŻELI.

Ćwiczenie 4

Na podstawie zgromadzonych w pliku danych oblicz ile w Polsce występuje rodzajów drzew zakwalifikowanych do poszczególnych rodzin. Wyniki zamieść w przygotowanej tabeli, w bloku komórek

:. W celu wykonania zadania pobierz plik z tabelą.

Ćwiczenie - arkusz "drzewa"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 9.61 KB w języku polskim

Ćwiczenie - arkusz "drzewa"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 3.99 KB w języku polskim

Rodzaje drzew występujących w Polsce

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 5

Na podstawie zgromadzonych w pliku danych, oblicz ile w Polsce występuje gatunków drzew zakwalifikowanych do poszczególnych rodzin. Wyniki zamieść w przygotowanej tabeli, w bloku komórek

:. W celu wykonania zadania pobierz plik z tabelą.

Ćwiczenie - arkusz "drzewa"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 9.77 KB w języku polskim

Ćwiczenie - arkusz "drzewa"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 4.00 KB w języku polskim

Rodzaje drzew występujących w Polsce

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 6

Pobierz tabelę zawierającą informacje o parkach narodowych. Wypełnij puste pola

: formułami, które obliczą wielkości zgodne z zamieszczonymi w tabeli informacjami. Do rozwiązania zadania wykorzystaj poznane funkcje.

Ćwiczenie - arkusz "parki narodowe"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 13.70 KB w języku polskim

Ćwiczenie - arkusz "parki narodowe"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 4.85 KB w języku polskim

Parki narodowe w Polsce

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 7

W tabeli prezentującej parki narodowe w Polsce utwórz dodatkową kolumnę, w której określisz słownie wielkość parku: mały – poniżej

Indeks górny 22, zaś duży – większy lub równy Indeks górny 22

Ćwiczenie - arkusz "parki narodowe"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 9.79 KB w języku polskim

Ćwiczenie - arkusz "parki narodowe"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 4.48 KB w języku polskim

Parki narodowe - powierzchnia i rok utworzenia

Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 8

Pobierz plik z tabelą wypełnioną danymi o najwyższych tatrzańskich szczytach. Uzupełnij komórkę E3 formułą, która obliczy liczbę szczytów o wysokości powyżej

.

Ćwiczenie - arkusz "szczyty"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 9.36 KB w języku polskim

Ćwiczenie - arkusz "szczyty"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 3.89 KB w języku polskim

Najwyższe szczyty Tatr

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 9

Oblicz, ile jest jezior w każdym typie oraz oblicz łączną powierzchnię jezior określonych typów.

Ćwiczenie - arkusz "jeziora"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 9.54 KB w języku polskim

Ćwiczenie - arkusz "jeziora"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 4.13 KB w języku polskim

Polskie jeziora

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Ćwiczenie 10

W tabeli zostały zamieszczone oceny z poszczególnych przedmiotów dla grupy uczniów. Uzupełnij tabelę, obliczając średnie oceny i zliczając poszczególne oceny. Rozwiązując zadanie, zastosuj adresowanie względne do obliczenia średnich i mieszane do obliczenia liczby poszczególnych ocen. Formuła zliczająca oceny powinna zostać zaprojektowana tylko w jednej komórce. Pozostałe komórki należy wypełnić, kopiując wcześniej zaprojektowaną formułę.

Ćwiczenie - arkusz "średnia ocen"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 11.07 KB w języku polskim

Ćwiczenie - arkusz "średnia ocen"

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Plik o rozmiarze 5.51 KB w języku polskim

Zliczanie ocen

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.

Pokaż podpowiedź

Pokaż odpowiedź

Zapamiętaj!

Istnieje możliwość zastosowania w formułach funkcji, które wykonują obliczenia w zależności od spełnienia określonych warunków, należą do nich:

- funkcja JEŻELI, która wprowadza do komórki określone wartości, w zależności od tego, czy określony warunek został spełniony.

- funkcja LICZ.JEŻELI zliczająca komórki, które spełniają określony warunek,

- funkcja SUMA.JEŻELI dodająca wartości tych komórek, które odpowiadają wartościom zakresu komórek spełniających określony warunek.

Wykorzystaj poniższy dzienniczek do zapisania swoich notatek lub przemyśleń.

Uzupełnij

Źródło: GroMar Sp. z o.o., licencja: CC BY 3.0.