1
Funkcja – Jeżeli
Funkcja
ta
należy do grupy funkcji Logiczne. Jest to funkcja warunkowa powodująca wykonanie
określonej czynności jeśli zostanie spełniony warunek logiczny, w przypadku gdy warunek nie jest spełniony
wykonane zostaje inne polecenie. Funkcja jeżeli jest funkcją trój parametrową, w której:
-
pierwszy parametr jest warunkiem logicznym
-
drugi parametr odpowiada za to, co ma się wydarzyć w przypadku gdy warunek logiczny jest spełniony (ma
wartość logiczną – prawda)
-
trzeci parametr określa, co ma się stać w przypadku gdy warunek logiczny nie jest spełniony (ma wartość
logiczną – fałsz)
Jako przykład zastosowania tej funkcji stworzymy prostą tabelkę (rys. 1.)
A B
C
D
Lp
imie
wiek
Czy osoba ma mniej niż 25 lat
1 Anna
22
TAK
2 Stanisław 30
NIE
3 Agnieszka
25
NIE
Rys. 1. Przykład wykorzystania funkcji jeżeli: kolumny A,B,C wypełniane są ręcznie, zaś kolumna D2 (w
wierszach poniżej napisu „Czy osoba ma mniej niż 25 lat”) ma zostać wypełniona wartościami będącymi
wynikami działania funkcji jeżeli.
Aby w kolumnie D została wyświetlona informacja czy wpisana w danym wierszu osoba ma mniej niż 25 lat
należy w komórce D2 wpisać następującą funkcję:
=jeżeli(C2<25;”TAK”;”NIE”)
Działanie funkcji jest następujące:
1. Zostaje sprawdzony warunek logiczny czy zawartość komórki C2 jest mniejsza niż25, czy nie
2. Jeśli liczba wpisana w komórce C2 jest mniejsza niż 25 wówczas w komórce D2 wyświetlony zostaje tekst
TAK
3. Jeśli liczba wpisana w komórce C2 jest większa lub równa 25 wówczas w komórce D2 wyświetlony
zostanie napis NIE
Widzimy, że jeśli w danej komórce oznaczającej wiek zostanie wpisana liczba 25 wówczas wypisany zostaje
napis NIE ponieważ warunek logiczny sprawdzał czy liczba jest mniejsza niż 25. A co jeśli chcielibyśmy aby:
1. gdy wiek mniejszy niż 25 – wypisywany jest tekst TAK
2. gdy wiek równy jest 25 – wypisywany jest tekst RÓWNY
3. gdy wiek większy niż 25 – wypisywany jest tekst NIE
Widać, że nie możemy zastosować tutaj funkcji jeżeli w prostej postaci (takiej jakiej używaliśmy do tej
pory). W celu zrealizowania powyższego zadania należało będzie użyć złożonej funkcji warunkowej jeżeli.
Funkcja
złożona tym różni się od funkcji prostej, że wśród jej argumentów (parametrów) występuje
kolejna (zagnieżdżona) funkcja. Dostępne są tu następujące możliwości:
=jeżeli(warunek logiczny; jeżeli(warunek logiczny; wartość jeżeli prawda; wartość jeżeli fałsz); wartość jeżeli
fałsz)
=jeżeli(warunek logiczny; wartość jeżeli prawda; jeżeli(warunek logiczny; wartość jeżeli prawda; wartość
jeżeli fałsz))
Tekst pogrubiony ilustruje funkcję zagnieżdżoną. Możliwość tworzenia funkcji złożonych ogranicza się
jednoczesnego użycia 8 funkcji.
Wykorzystując umieszczone powyżej informacje możemy przystąpić do wykonania zadania. Wiemy już, że
będziemy musieli użyć złożonej funkcji jeżeli. Postać wpisywanego polecenia może wyglądać następująco:
=jeżeli(C2<25;”TAK”; jeżeli(C2=25;”RÓWNY”;”NIE”))
lub też:
2
W przedstawionych przykładach funkcja zagnieżdżona zaznaczona jest poprzez jej pogrubienie. Wynikiem
działania tej funkcji (po uprzednim wstawieniu jej w odpowiednie miejsce!!!) jest:
A B C
D
Lp
Imie
Wiek
Czy osoba ma mniej lat niż 25
1 Anna
27
NIE
2 Stanisław 21
TAK
3 Agnieszka
25
RÓWNY
Rys. 2. Wykorzystanie funkcji jeżeli (zagnieżdżonej) wypełnienie tak jak na rysunku pierwszym
Opisując słownie działanie powyższych funkcji możemy przedstawić następująco:
1. Sprawdzamy czy wartość komórki C2 jest mniejsza niż 25
-
jeśli tak wówczas w komórce D2 (bo w niej powinna być wpisana podana funkcja) wyświetlony zostaje
napis TAK
-
jeśli nie to wówczas (i tu wchodzimy do funkcji zagnieżdżonej) sprawdzamy czy zawartość komórki C2 jest
równa 25 i jeśli tak to wyświetlony zostaje napis RÓWNY, a jeśli nie to wówczas wyświetlany jest napis
NIE
2. Sprawdzamy czy zawartość komórki C2 jest mniejsza od 26
-
jeśli tak (teraz tu wchodzimy do funkcji zagnieżdżonej) to sprawdzamy czy C2 jest równe 25 i jeśli tak to
wówczas wyświetlony zostaje napis RÓWNY jeśli nie to wyświetlony jest napis TAK
-
jeśli nie (jeśli wartość w komórce C2 jest większa lub równa 26) wówczas wyświetlany jest napis NIE
Korzystanie z danych umieszczonych w innych arkuszach
Jeśli w dowolnej komórce skoroszytu (np.) 2 chcielibyśmy wyświetlić wartość dowolnej komórki ze
skoroszytu (np.) nr 1musielibyśmy umieścić w niej następujące polecenie
=arkusz1!adreskolumnyadreswiersza
przykład: (w komórce D2 skoroszytu 2 wyświetlamy zawartość komórki F4 skoroszytu 1)
założenie: (umieszczamy kursor na komórce D2 w skoroszycie 2 i piszemy)
=arkusz1!F4
Ogólnie możemy powiedzieć, ze odniesienie się do dowolnej komórki innego skoroszytu niż teraz aktualny
następuje poprzez wpisanie słowa arkusz następnie podanie numeru arkusza, wpisaniu znaku ! i następnie
podaniu adresu konkretnej komórki z tego arkusza.
Zadaniem naszym będzie teraz stworzenie następującej tabeli w skoroszycie drugim
A B
Lp Podatność na choroby
1 Podatność duża
2 Podatność mała
3 Podatność średnia
Rys.3. tabela umieszczona w skoroszycie drugim
W przedstawionej tabeli widzimy w kolumnie B następujące teksty:
-
podatność duża
-
podatność mała
-
podatność średnia
Przedstawione teksty powstały w wyniku sprawdzenia zawartości poszczególnych komórek w kolumnie D (od
D2 do D4) skoroszytu pierwszego. I tak jeśli w danej komórce (kolumny D skoroszytu 1) znajdował się napis
TAK wówczas w odpowiedniej komórce (w której zapisana została odpowiednia funkcja) skoroszytu 2
wyświetlony został tekst podatność mała:
3
Wartość komórki w kolumnie D
skoroszytu pierwszego
Wartość komórki w kolumnie B
skoroszytu drugiego
TAK Podatność mała
NIE Podatność duża
RÓWNY Podatność średnia
Rys. Wyświetlające się wartości w skoroszycie 2 w zależności od zawartości odpowiedniej komórki w
skoroszycie 1
Widzimy, że zawartość komórek od B2 do B4 (rys.3.) skoroszytu drugiego uzależniona jest od zawartości
odpowiednich komórek z kolumny D skoroszytu 1. Dlatego musimy skorzystać tu z opisanej uprzednio funkcji
jeżeli. Opis tekstowy jak wyglądać ma ta funkcja wygląda następująco:
1. umieszczamy kursor w komórce B2 skoroszytu 2
2. sprawdzamy czy zawartość komórki D2 skoroszytu 1 jest równa (np.) „TAK” jeśli jest to prawda to musi
zostać wyświetlony tekst „podatność mała”
3. jeśli tekst w komórce D2 skoroszytu 1 jest inny niż „TAK” to wówczas:
-
sprawdzamy czy napisem tym jest tekst „NIE” i jeśli tak to wypisany ma ma zostać tekst „podatność duża”
-
jeśli tekst umieszczony w komórce D2 skoroszytu 1 jest inny niż „TAK” oraz inny niż „NIE” wówczas
wypisany ma zostać na ekranie napis „podatność średnia”
A oto opisywana funkcja w formie właściwej:
=jeżeli(arkusz1D2=”TAK”;”podatność mała”; jeżeli(arkusz1!D2=”NIE”;”podatność duża”;”podatność
średnia”))
Oczywiste jest, że podana funkcja ma zostać wpisana w komórce B2 arkusza 2.
Instrukcja Licz.Jeżeli
Instrukcja ta służy do zliczania komórek, które spełniają określone przez nas kryterium logiczne.
Funkcja ta jest funkcją o dwu parametrach, z których pierwszy określa zakres komórek, które mają zostać
sprawdzone, a drugi to warunek logiczny według którego sprawdzamy określone w „zakresie” komórki (wśród
sprawdzanych komórek zliczane są te, które spełniają wpisany warunek logiczny).
=licz.jeżeli( zakres komórek; warunek logiczny)
Warunek logiczny wpisywany jest pomiędzy cudzysłowiami.
Zadaniem naszym będzie stworzenie tabeli (rys.4.)
A B
C
D
E
Lp Nazwa towaru
Cena netto
VAT
Cena brutto
1 Serek homogenizowany
10
0%
10
2 Mleko
20
7%
21,4
3 Jajka
30
14%
34,2
4 Cukierki
40
22%
48,8
5 Serek topiony
50
7%
53,5
Rys.4. Tabela którą należy stworzyć: kolumny A,B,C,D mają być wpisane ręcznie; kolumna E ma zostać
wypełniona wartościami powstałymi w wyniku działania określonej funkcji.
Po stworzeniu tabeli musimy obliczyć i wyświetlić na ekranie ile jest towarów o stopie procentowej VAT
-
0%
-
7%
-
14%
-
22%
4
Ilość towarów o stopie procentowej VAT 0%
1
Ilość towarów o stopie procentowej VAT 7%
2
Ilość towarów o stopie procentowej VAT 14% 1
Ilość towarów o stopie procentowej VAT 22% 1
Rys.5. Tabela przedstawiająca ilość towarów o określonej wartości procentowej VAT: kolumna pierwsza
wpisywana jest ręcznie kolumna 2 (wartości liczbowe) wypełniana jest wartościami powstałymi w wyniku
działania funkcji Licz.Jeżeli
Aby stworzyć powyższą tabelę (Rys.5.) musimy użyć funkcji licz.jeżeli. Jako pierwszy parametr tej funkcji
podajemy zakres komórek, które będą sprawdzane (a zarazem zliczane) czyli D2:D6, teraz określamy warunek
logiczny:
-
„=0%” – jeżeli zliczamy ilość towarów o 0 stopie procentowej VAT
-
„=7%” – jeżeli zliczamy ilość towarów o 7% stopie procentowej VAT
-
„=14%” – jeżeli zliczamy ilość towarów o 14% stopie procentowej VAT
-
„=22%” – jeżeli zliczamy ilość towarów o 22% stopie procentowej VAT
Tak więc przykładowa funkcja pozwalająca na zliczenie 9z rys.4.) ilość towarów o stopie procentowej VAT 7%
będzie wyglądała następująco:
=licz.jeżeli(D2:D6;”=0%”)
Funkcja licz.jeżeli pozwala nam również na zliczanie komórek, w których zamiast liczb znajduje się tekst.
Wówczas w warunku logicznym wpisujemy wyrażenie zawierające określony wyraz. Jeśli np. w tabeli, w której
znajdują się dane osobowe będziemy chcieli obliczyć ilość wierszy dla imienia Bartek:
A B
C D
Lp Imię Nazwisko
Wiek
1 Bartek
Waligóra
20
2 Stanisław Starszy
23
3 Tomasz
Niezły 24
4 Joanna
Śmiała 21
5 Bartek
Stały 22
Rys.6.
Będziemy musieli wpisać jako zakres B2:B6, a jako warunek logiczny „=Bartek”
Funkcja będzie wyglądała następująco:
=licz.jeżeli(B2:B6;”=Bartek”)
Instrukcja Suma.jeżeli
Instrukcja
służąca do sumowania zawartości komórek z podanego zakresu. Funkcja jest funkcją trzy
parametrową:
Suma.jeżeli( zakres sprawdzanych komórek; warunek logiczny; zakres sumowanych komórek)
Zasada
działania funkcji jest następująca: według podanego warunku logicznego sprawdzane są
komórki określone w zakresie sprawdzanych komórek a następnie sumowane są wartości odpowiednich
komórek z zakresu sumowanych komórek (odpowiadających komórkom w sprawdzanym zakresie tzn.
znajdujących się w tym samym wierszu).
Dla wykonania przykładu zastosowania tej funkcji wykorzystajmy tabelę z rysunku 6. Będziemy chcieli
obliczyć łączny wiek osób o wybranym przez nas imieniu. Niech tym imieniem będzie Bartek.
Dla wykonania zadania tworzymy funkcję:
=suma.jeżeli(B2:B6;”=Bartek”;D2:D6)
Wynikiem wykonania tej funkcji będzie liczba 42 (biorąc pod uwagę dane z tabeli z rys.6.)
Widzimy, że pierwszy parametr B2:B6 określa zakres komórek porównywanych ze wzorcem określonym w
parametrze drugim „=Bartek” (warunek logiczny. Następnie sumowane są tylko te wartości komórek, których
numer wiersza jest zgodny z numerem wiersza komórek sprawdzanych, spełniających podane kryterium. W
warunku logicznym możemy używać dowolnych operatorów logicznych czyli (<,>,<=,>=,=).