Formuły warunkowe
JEŻELI
Funkcja logiczna Jeżeli ma szerokie zastosowanie w arkuszu kalkulacyjnym.
Przykładowe zdanie: Jeżeli będzie ładna pogoda idę na spacer, w przeciwnym razie zostaję w domu. składa się z trzech elementów: warunku, wykonywanej czynności gdy warunek jest spełniony oraz wykonywanej czynności gdy warunek nie jest spełniony.
Zgodnie z tym funkcja JEŻELI ma taką konstrukcję:
=JEŻELI(warunek;wartość jeżeli warunek jest spełniony;wartość jeżeli warunek nie jest spełniony)
Inny zapis:
=JEŻELI(test logiczny;wartość jeżeli prawda;wartość jeżeli fałsz)
- test logiczny: ta część funkcji sprawdza czy spełniony jest założony warunek np. czy w komórce A1 jest wartość większa niż 0 (A1>0). W teście logicznym można się posługiwać takimi operatorami jak: =, >, <, >=, <=, <> (różne).
- wartość jeżeli prawda: jeżeli warunek jest spełniony funkcja wykonuje określoną operację matematyczną, oddaje wartość tekstową lub liczbę.
- wartość jeżeli fałsz: jeżeli warunek nie jest spełniony funkcja, jak w poprzednim przypadku, wykonuje określoną operację matematyczną, oddaje wartość tekstową lub liczbę.
Zagnieżdżenie funkcji JEŻELI
Często zdarza się, że musimy sprawdzić więcej niż jeden warunek. Wyobraźmy sobie taką sytuację. Nauczyciel poprawia sprawdzian. Na podstawie liczby uzyskanych punktów program ma wystawić ocenę.
Sytuację na początku przedstawia rysunek
Formuła przeliczjąca punkty na procenty.
Otrzymane ułamki przedstawiam w postaci procentowej stosując odpowiednie formatowanie po zaznaczeniu komórek jak na rysunku.
Teraz przystępuję do zamiany wyników procentowych na oceny. Ważne jest, aby warunki były wpisywane zgodnie z kolejnością od najmniejszej do największej albo od największej do najmniejszej. Granie przedziałów widoczne są w tabeli z prawej strony.
Inne podejście - funkcja WYSZUKAJ.PIONOWO
Funkcja JEŻELI może być zagnieżdżona co najwyżej siedmiokrotnie. Stąd też w wielu przypadkach wygodniej jest posłużyć się funkcją WYSZUKAJ.PIONOWO lub WYSZUKAJ.POZIOMO. Różnią się one tylko układem tabeli.
Zanim zastosuję funkcję WYSZUKAJ.PIONOWO muszę przygotować odpowiedbią tabelę. Najlepiej, żeby dane w lewej kolumnie były uporządkowane rosnąco. Funkcja wyszukuje wartość w I kolumnie i zamienia go na wpis z innej kolumny (podanej w treści formuły). Pomimo tego, iż w tabeli nie ma np. 69% (nie osiągnęliśmy jeszcze 70%), funkcja wpisze to, co wpisałaby dla granicy przedziału poprzedzającego (%). Funkcja ta ma postać
=WYSZUKAJ.PIONOWO(szukana_wartość;tablica;nr_kolumny;kolumna). W prostych zastosowaniach ostatnią pozycję możemy pominąć.
W naszym przykładzie zastosuję ją do wpisania ocen słownie. Mogłem nie używać funkcji JEŻELI i także do wpisania ocen cyfrą użyć funkcji WYSZUKAJ.PIONOWO.
Na koniec otrzymujemy
LICZ.JEŻELI
Funkcji LICZ.JEŻELI używamy w sytuacjach, gdy chcemy zliczyć liczbę komórek zawierających interesującą nas wartość. Jeśli notuję sobie wszystkie oceny, mogę posłużyć się tą funkcją do zliczania ile mam poszczególnych ocen.
Składnia funkcji: LICZ.JEŻELI(zakres_komórek;kryteria)
Wybraźmy sobie, że przeprowadzamy ankietę dotyczącą czytanych czasopism. Na użytek naszego przykładu wybieramy tylko 4 tytuły (Polityka, Wprost, Bravo, Pani Domu). Zanotowane wpisy przedstawia rysunek.
Chcemy policzyć ile osób wskazało poszczególne tytuły. W tym celu zastosujemy funkcję LICZ.JEŻELI. Dane podsumowujące zbierzemy w odrębnej tabelce. W komórce F2 wpisujemy formułę zawierającą funkcję LICZ.JEŻELI. Zaczynamy od wskazania zakresu komórek, w których są dane. W przykładzie wybieram 3 pełne kolumny. Taki wybór pozwala dopisywać dane w kolumnach bez konieczności zmieniania zakresu w formule.
Następnie wskazuję kryterium zliczania komórek. W naszym przykładze jest to komórka E2. Czyli liczymy ile komórek w kolumnach A, B i C ma taką samą zawartość jak komórka E2 (Wprost). Poniżej widoczne jest pełne określenie funkcji.
Na koniec pozostaje skopiować formułę do pozostałych komórek.
SUMA.JEŻELI
Funkcja SUMA.JEŻELI dodaje komórki spełniające określone kryteria.Składnia funkcji: SUMA.JEŻELI(zakres; kryteria; zakres_sumowania).
- Argument zakres określa zakres komórek, do którego będzie zastosowane kryterium wyboru.
- Argument kryteria określa kryterium, którego spełnienie lub niespełnienie włącza lub wyłącza komórkę z operacji sumowania.
- Argument zakres_sumowania określa zakres komórek, z którego wartości zostaną zsumowane.
Dwa pierwsze argumenty są takie jak w funkcji LICZ.JEŻELI.
My wykorzystamy funkcję SUMA.JEŻELI do budżetu domowego. Chcemy otrzymać zestawienie dotyczące struktury naszych wydatków. Na początku ustalamy kategorie (rodzaje) zakupów. Na użytek naszego przykładu będą: odzież, kosmetyki, jedzenie, rozrywka. Każdy wydatek zapisujemy w arkuszu - np. w kolumnie A rodzaj zakupu, a w kolumnie B wydane kwoty.
Operację wklejania tej funkcji przedstawiają kolejne zrzuty ekranu.
Pozostaje nam tylko skopiowanie wklejonej funkcji do pozostałych komórek.
Dalszy ciąg tego przykładu jest w dziale Prezent. danych.