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 wielu zadań z matematyki, fizyki, chemii, w tworzeniu różnych zestawień z geografii lub biologii. Poznając jego tajniki, na pewno znajdziesz dla niego wiele zastosowań i będziesz autorem wielu tabel wypełnionych 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.
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 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.
Zadanie spróbuj wykonać samodzielnie. Jeżeli masz problemy, skorzystaj ze wskazówki. Możesz obejrzeć wybrany film pokazujący, w jaki sposób zastosujesz funkcję JEŻELI w programie ExcelExcel lub programie CalcCalc.
Aby arkusz informował cię, kto zakwalifikował się do drugiego etapu konkursu, postępuj według poniższej instrukcji.
Zbuduj tabelę, wypełnij danymi lub pobierz ją z podręcznika.
Zaprojektuj formułę obliczającą całkowitą liczbę punktów uzyskaną przez poszczególnych zawodników. Zgodnie z zamieszczoną ilustracją w komórce J4 zastosuj formułę: =SUMA(F4:I4), a następnie skopiuj ją do bloku komórek J5:J16. Zwróć uwagę, że w formule zostało zastosowane adresowanie względne.
Zaprojektuj formułę, która zakwalifikuje odpowiednie osoby do drugiego etapu konkursu. Proponujemy ci, aby dla osób, które zostały zakwalifikowane do kolejnego etapu konkursu, w kolumnie K pojawiło się słowo TAK, zaś dla pozostałych uczestników słowo NIE. Do zaprojektowania tej formuły zastosuj funkcję JEŻELI. Wpisz jako pierwszy argument warunek sprawdzający, czy wartość komórki zawierającej łączną liczbę punktów jest większa lub równa połowie maksymalnej liczby punktów do zdobycia, drugim argumentem jest słowo TAK, zaś trzecim słowo NIE. Projektując formułę, należy pamiętać o zastosowaniu adresowania bezwzględnego w stosunku do adresu komórki przechowującej maksymalną liczbę punktów. Formuła dla pierwszego zawodnika (w komórce K4) ma następującą postać: =JEŻELI(J4>=0,5*$J$18;”TAK”;”NIE”). Zwróć uwagę, że poszczególne argumenty oddzielone są od siebie znakiem „;”.Funkcja JEŻELI występuje w kategorii funkcji: Logiczne.
Skopiuj komórkę K4 zawierającą zaprojektowaną formułę do bloku komórek K5:K16. Operacja ta pozwoli ci uzyskać informację, kto zakwalifikował się do drugiego etapu konkursu.
Dla bloku komórek K4:K16, korzystając z formatowania warunkowego, zaprojektuj tabelę w ten sposób, aby informacje o zakwalifikowaniu się do drugiego etapu konkursu zostały wyróżnione
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ę 9 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 Statystyczne. 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.
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 ze wskazówki. Możesz obejrzeć wybrany film pokazujący, w jaki sposób zastosujesz funkcję LICZ.JEŻELI w programie ExcelExcel lub programie CalcCalc.
Aby obliczyć liczbę osób zakwalifikowanych do drugiego etapu konkursu, postępuj według podanej instrukcji.
Zaznacz komórkę, w której zamierzasz policzyć uczniów zakwalifikowanych do drugiego etapu.
Wywołaj funkcję LICZ.JEŻELI.
Zaznacz zakres, czyli blok komórek, których zawartość będzie przeszukana zgodnie z określonym dalej kryterium.
Określ kryterium, tzn. podaj zawartość komórki, która będzie zliczana w formule.
Potwierdź wprowadzone dane.
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 J, 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 „K” 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.
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 ze wskazówki. Możesz obejrzeć wybrany film pokazujący, w jaki sposób zastosujesz funkcję SUMA.JEŻELI w programie ExcelExcel lub programie CalcCalc.
Aby obliczyć ile punktów łącznie zdobyły osoby zakwalifikowane do drugiego etapu konkursu, postępuj według podanej instrukcji.
Zaznacz komórkę, w której chcesz umieścić to obliczenie.
Wywołaj funkcję SUMA.JEŻELI.
Zaznacz zakres, czyli blok komórek, których zawartość będzie przeszukana zgodnie z określonym dalej kryterium – są to komórki kolumny „K”, zawierające informacje o zakwalifikowaniu się osób do II etapu konkursu.
Określ kryterium, tzn. podaj zawartość komórki, która będzie zliczana w formule; tym kryterium jest słowo „TAK”.
Określ blok komórek, z którego wartości będą mogły być sumowane; jest to blok komórek kolumny „J”.
Potwierdź wprowadzone dane.
Funkcję SUMA.JEŻELI możesz wykorzystać w wielu sytuacjach. Zapoznaj się z zamieszczonymi w podręczniku zadaniami. W nich znajdziesz kilka zastosowań tej funkcji. Pomyśl, może sam ułożysz problem, do rozwiązania którego będzie potrzebna funkcja SUMA.JEŻELI.
Oblicz, ile w Polsce występuje rodzajów drzew zakwalifikowanych do poszczególnych rodzin. Wyniki zamieść w przygotowanej tabeli, w bloku komórekH4:H13.
W celu wykonania zadania pobierz plik z tabelą.
Zadanie wykonaj w następującej kolejności:
Zaprojektuj formułę w komórce H4, wykorzystując poznaną funkcję oraz stosując adresowanie względne i bezwzględne.
Skopiuj komórkę H4 do bloku komórek H5:H13.
Oblicz, ile w przybliżeniu w Polsce występuje gatunków drzew zakwalifikowanych do poszczególnych rodzin. Wyniki zamieść w przygotowanej tabeli, w bloku komórekH4:H13. W celu wykonania zadania pobierz plik z tabelą.
Zadanie wykonaj w następującej kolejności:
Zaprojektuj formułę w komórce H4, wykorzystując poznaną funkcję oraz stosując adresowanie względne i bezwzględne.
Skopiuj komórkę H4 do bloku komórek H5:H13.
Pobierz tabelę zawierającą informacje o parkach narodowych. Wypełnij puste pola H4:H7 formułami, które obliczą wielkości zgodne z zamieszczonymi w tabeli informacjami. Do rozwiązania zadania wykorzystaj poznane funkcje.
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 100 kmIndeks górny 22, zaś duży – większy lub równy 100 kmIndeks górny 22
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 2500 m.
Oblicz, ile jest jezior w każdym typie oraz oblicz łączną powierzchnię jezior określonych typów.
W tabeli zostały zamieszczone oceny z poszczególnych przedmiotów 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łę.
Przyjrzyj się przykładowemu rozwiązaniu.
Istnieje możliwość zastosowania w formułach funkcji, które wykonują obliczenia w zależności od spełnienia określonych warunków. Do tych funkcji należą:
funkcja JEŻELI wprowadza do komórki określone wartości w zależności czy warunek jest spełniony lub nie, funkcja LICZ.JEŻELI zlicza komórki, które spełniają określony warunek,
funkcja SUMA.JEŻELI dodaje wartości tych komórek, które odpowiadają wartościom zakresu komórek spełniających określony warunek.