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:
Zostaje sprawdzony warunek logiczny czy zawartość komórki C2 jest mniejsza niż25, czy nie
Jeśli liczba wpisana w komórce C2 jest mniejsza niż 25 wówczas w komórce D2 wyświetlony zostaje tekst TAK
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:
gdy wiek mniejszy niż 25 - wypisywany jest tekst TAK
gdy wiek równy jest 25 - wypisywany jest tekst RÓWNY
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ż:
=jeżeli(C2<26; jeżeli(C2=25;”RÓWNY”;”TAK”);”NIE”)
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:
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
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:
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:
umieszczamy kursor w komórce B2 skoroszytu 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”
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%
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 (<,>,<=,>=,=).
1