Wróć do informacji o e-podręczniku Wydrukuj Pobierz materiał do PDF Pobierz materiał do EPUB Pobierz materiał do MOBI Zaloguj się, aby dodać do ulubionych Zaloguj się, aby skopiować i edytować materiał Ten materiał nie może być udostępniony

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.

RnoXD3biimvHK1
Źródło: CP, licencja: CC BY 3.0.
iN140KeQaO_d5e144

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 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.

R1Fh2LeHq6OlT1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
RIYQpAHq18xTU1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.

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 ExceliN140KeQaO_d498e113Excel lub programie CalciN140KeQaO_d498e119Calc.

Wskazówka

Aby arkusz informował cię, kto zakwalifikował się do drugiego etapu konkursu, postępuj według poniższej instrukcji.

  1. Zbuduj tabelę, wypełnij danymi lub pobierz ją z podręcznika.

  2. 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.

  3. 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.

  4. 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.

  5. 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

iN140KeQaO_d498e113
iN140KeQaO_d498e119
iN140KeQaO_d5e246

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.

REz02L2BlwEKF1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, 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 ze wskazówki. Możesz obejrzeć wybrany film pokazujący, w jaki sposób zastosujesz funkcję LICZ.JEŻELI w programie ExceliN140KeQaO_d498e156Excel lub programie CalciN140KeQaO_d498e163Calc.

Wskazówka

Aby obliczyć liczbę osób zakwalifikowanych do drugiego etapu konkursu, postępuj według podanej instrukcji.

  1. Zaznacz komórkę, w której zamierzasz policzyć uczniów zakwalifikowanych do drugiego etapu.

  2. Wywołaj funkcję LICZ.JEŻELI.

  3. Zaznacz zakres, czyli blok komórek, których zawartość będzie przeszukana zgodnie z określonym dalej kryterium.

  4. Określ kryterium, tzn. podaj zawartość komórki, która będzie zliczana w formule.

  5. 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.

iN140KeQaO_d498e156
iN140KeQaO_d498e163
iN140KeQaO_d5e347

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.

REit4rsydzy9m1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, 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 ze wskazówki. Możesz obejrzeć wybrany film pokazujący, w jaki sposób zastosujesz funkcję SUMA.JEŻELI w programie ExceliN140KeQaO_d498e205Excel lub programie CalciN140KeQaO_d498e211Calc.

Wskazówka

Aby obliczyć ile punktów łącznie zdobyły osoby zakwalifikowane do drugiego etapu konkursu, postępuj według podanej instrukcji.

  1. Zaznacz komórkę, w której chcesz umieścić to obliczenie.

  2. Wywołaj funkcję SUMA.JEŻELI.

  3. 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.

  4. Określ kryterium, tzn. podaj zawartość komórki, która będzie zliczana w formule; tym kryterium jest słowo „TAK”.

  5. Określ blok komórek, z którego wartości będą mogły być sumowane; jest to blok komórek kolumny „J”.

  6. 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.

Ćwiczenie 4

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ą.

R10owSJZ3j6W21
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
R1eFWLz2dMZA31
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.

Zadanie wykonaj w następującej kolejności:

  1. Zaprojektuj formułę w komórce H4, wykorzystując poznaną funkcję oraz stosując adresowanie względne i bezwzględne.

  2. Skopiuj komórkę H4 do bloku komórek H5:H13.

Ćwiczenie 5

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ą.

Rrimd0ehdvldz1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
R1VO7unZChqId1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.

Zadanie wykonaj w następującej kolejności:

  1. Zaprojektuj formułę w komórce H4, wykorzystując poznaną funkcję oraz stosując adresowanie względne i bezwzględne.

  2. Skopiuj komórkę H4 do bloku komórek H5:H13.

Ćwiczenie 6

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.

ROoVbyUdYULXL1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
RbenEN0TGI9vQ1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
Ć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 100 kmIndeks górny 2, zaś duży – większy lub równy 100 kmIndeks górny 2

RU31bFfz8nq2V1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
R1DEox7Pcvg7f1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
Ć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 2500 m.

RGorXgeuNoWZg1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
RXQSja2C1YCtT1
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
Ćwiczenie 9

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

RdzU5k3IOUlVT1
Plik do pobrania
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
RZOEi7ITxPLN51
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
Ćwiczenie 10

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łę.

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

Przyjrzyj się przykładowemu rozwiązaniu.

R1Gfufi64I0881
Źródło: Anna Koludo, Katarzyna Koludo-Durkiewicz, licencja: CC BY 3.0.
iN140KeQaO_d498e205
iN140KeQaO_d498e211
iN140KeQaO_d5e577
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. 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.