Funkcja JEŻELI w programie Excel. Przykłady (z wieloma warunkami)

Jedną z głównych zalet arkuszy kalkulacyjnych Excel jest możliwość zaprogramowania funkcjonalności konkretnego dokumentu. Jak większość ludzi wie ze szkolnych lekcji informatyki, jednym z głównych elementów, które pozwalają zastosować to w praktyce, są operatory logiczne. Jednym z nich jest operator IF, który umożliwia wykonanie określonych akcji po spełnieniu określonych warunków. 

Na przykład, jeśli wartość pasuje do określonej, w komórce wyświetlana jest jedna etykieta. Jeśli nie, to jest inaczej. Przyjrzyjmy się temu skutecznemu narzędziu bardziej szczegółowo w praktyce.

Funkcja JEŻELI w Excelu (informacje ogólne)

Każdy program, nawet mały, koniecznie zawiera sekwencję działań, którą nazywamy algorytmem. Może to wyglądać tak:

1. Sprawdź całą kolumnę A pod kątem liczb parzystych.
2. Jeśli zostanie znaleziona liczba parzysta, dodaj takie a takie wartości.
3. Jeśli liczba parzysta nie zostanie znaleziona, wyświetl napis „nie znaleziono”.
4. Sprawdź, czy wynikowa liczba jest parzysta. 
5. Jeśli tak, dodaj go do wszystkich liczb parzystych wybranych w akapicie 1.

I nawet jeśli jest to tylko hipotetyczna sytuacja, która prawdopodobnie nie będzie potrzebna w prawdziwym życiu, wykonanie dowolnego zadania z konieczności implikuje obecność podobnego algorytmu. Przed użyciem funkcji JEŚLI, musisz mieć w głowie jasny pomysł na to, jaki wynik chcesz osiągnąć. 

Składnia funkcji JEŻELI z jednym warunkiem

Każda funkcja w programie Excel jest wykonywana za pomocą formuły. Wzorzec, według którego dane muszą być przekazane do funkcji, nazywa się składnią. W przypadku operatora IF, formuła będzie miała ten format.

=JEŻELI (wyrażenie_logiczne, wartość_jeśli_prawda, wartość_jeśli_fałsz)

Przyjrzyjmy się składni bardziej szczegółowo:

1. Wyrażenie logiczne. Jest to sam warunek, którego zgodność lub niezgodność sprawdza program Excel. Można sprawdzić zarówno informacje liczbowe, jak i tekstowe.
2. Wartość_jeśli_prawda. Wynik, który zostanie wyświetlony w komórce, jeśli sprawdzane dane spełniają określone kryteria.
3. wartość_jeśli_fałsz. Wynik wyświetlany w komórce, jeśli sprawdzane dane nie spełniają warunku.

Oto przykład jasności.

Tutaj funkcja porównuje komórkę A1 z liczbą 20. To jest pierwszy akapit składni. Jeśli zawartość jest większa niż ta wartość, w komórce, w której zapisano formułę, wyświetlana jest wartość „większa niż 20”. Jeżeli sytuacja nie odpowiada temu warunkowi – „mniej lub równa 20”.

Jeśli chcesz wyświetlić wartość tekstową w komórce, musisz ująć ją w cudzysłów.

Oto inna sytuacja. Aby móc przystąpić do sesji egzaminacyjnej, studenci muszą zdać sesję testową. Uczniom udało się zdobyć zaliczenia ze wszystkich przedmiotów, a teraz pozostała ostatnia, która okazała się decydująca. Naszym zadaniem jest ustalenie, którzy z uczniów zostaną dopuszczeni do egzaminów, a którzy nie.

Ponieważ chcemy sprawdzić tekst, a nie liczbę, pierwszym argumentem jest B2=”cons.”.

Składnia funkcji IF z wieloma warunkami

Często jedno kryterium nie wystarcza do sprawdzenia wartości. Jeśli musisz rozważyć więcej niż jedną opcję, możesz zagnieździć funkcje IF jeden w drugi. Będzie kilka zagnieżdżonych funkcji.

Aby było jaśniej, oto składnia.

= JEŻELI(wyrażenie_logiczne; wartość_jeśli_prawda; JEŻELI(wyrażenie_logiczne;wartość_jeśli_prawda;wartość_jeśli_fałsz))

W takim przypadku funkcja sprawdzi jednocześnie dwa kryteria. Jeśli pierwszy warunek jest spełniony, zwracana jest wartość uzyskana w wyniku operacji w pierwszym argumencie. Jeśli nie, sprawdzane jest drugie kryterium pod kątem zgodności.

Oto przykład.

Za pomocą takiej formuły (pokazanej na zrzucie ekranu poniżej) możesz przeanalizować wydajność każdego ucznia.

Jak widać, dodano tutaj jeszcze jeden warunek, ale zasada się nie zmieniła. Możesz więc sprawdzić kilka kryteriów jednocześnie.

Jak rozszerzyć funkcjonalność IF za pomocą operatorów AND i OR?

Od czasu do czasu pojawia się sytuacja, aby natychmiast sprawdzić zgodność z kilkoma kryteriami, a nie używać logicznych operatorów zagnieżdżonych, jak w poprzednim przykładzie. Aby to zrobić, użyj funkcji И lub funkcja OR w zależności od tego, czy musisz spełnić kilka kryteriów na raz, czy przynajmniej jedno z nich. Przyjrzyjmy się bliżej tym kryteriom.

Funkcja IF z warunkiem AND

Czasami trzeba sprawdzić wyrażenie pod kątem wielu warunków naraz. W tym celu używana jest funkcja AND, zapisana w pierwszym argumencie funkcji IF. Działa to tak: jeśli a jest równe jeden i a jest równe 2, wartość będzie równa c.

Funkcja JEŻELI z warunkiem „LUB”

Funkcja OR działa w podobny sposób, ale w tym przypadku tylko jeden z warunków jest spełniony. O ile to możliwe, w ten sposób można sprawdzić do 30 warunków. 

Oto kilka sposobów korzystania z funkcji И и OR jako argument funkcji IF.

Porównanie danych w dwóch tabelach

Od czasu do czasu możliwe jest porównanie dwóch podobnych tabel. Na przykład osoba pracuje jako księgowa i musi porównać dwa raporty. Istnieją inne podobne zadania, takie jak porównywanie kosztów towarów z różnych partii, następnie oceny uczniów za różne okresy i tak dalej.

Aby porównać dwie tabele, użyj funkcji COUNTIF. Przyjrzyjmy się temu bardziej szczegółowo.

Załóżmy, że mamy dwie tabele zawierające specyfikacje dwóch robotów kuchennych. I musimy je porównać i podkreślić różnice kolorem. Można to zrobić za pomocą formatowania warunkowego i funkcji COUNTIF. 

Nasz stół wygląda tak.

Dobieramy asortyment odpowiadający charakterystyce technicznej pierwszego robota kuchennego.

Następnie kliknij następujące menu: Formatowanie warunkowe – utwórz regułę – użyj formuły, aby określić sformatowane komórki.

W postaci wzoru na formatowanie zapisujemy funkcję =LICZ.JEŻELI (zakres do porównania; pierwsza komórka pierwszej tabeli)=0. Tabela z cechami drugiego robota kuchennego służy jako zakres porównawczy.

Musisz upewnić się, że adresy są bezwzględne (ze znakiem dolara przed nazwami wierszy i kolumn). Dodaj =0 po formule, aby program Excel szukał dokładnych wartości.

Następnie musisz ustawić formatowanie komórek. Aby to zrobić, obok próbki musisz kliknąć przycisk „Format”. W naszym przypadku stosujemy wypełnienie, ponieważ jest to najwygodniejsze do tego celu. Ale możesz wybrać dowolne formatowanie.

Przypisaliśmy nazwę kolumny jako zakres. Jest to znacznie wygodniejsze niż ręczne wprowadzanie zakresu.

Funkcja SUMA.JEŻELI w Excelu

Przejdźmy teraz do funkcji IF, co pomoże zastąpić dwa punkty algorytmu jednocześnie. Pierwszy to SUMMESLEY, który dodaje dwie liczby spełniające określony warunek. Na przykład stajemy przed zadaniem ustalenia, ile pieniędzy należy miesięcznie płacić wszystkim sprzedawcom. Do tego jest to konieczne.

1. Dodaj wiersz z całkowitymi dochodami wszystkich sprzedawców i po wprowadzeniu formuły kliknij komórkę, która będzie zawierać wynik. 
2. Znajdujemy przycisk fx, który znajduje się obok wiersza formuł. Następnie pojawi się okno, w którym możesz znaleźć niezbędną funkcję poprzez wyszukiwanie. Po wybraniu operatora należy kliknąć przycisk „OK”. Ale ręczne wprowadzanie danych jest zawsze możliwe.
   

   

3. Następnie pojawi się okno do wprowadzania argumentów funkcji. Wszystkie wartości można określić w odpowiednich polach, a zakres wprowadzić przyciskiem obok nich.
   

   

4. Pierwszy argument to zakres. Tutaj wpisujesz komórki, które chcesz sprawdzić pod kątem zgodności z kryteriami. Jeśli mówimy o nas, to są to stanowiska pracowników. Wprowadź zakres D4:D18. Lub po prostu wybierz interesujące komórki.
5. W polu „Kryteria” wpisz pozycję. W przypadku nas – „sprzedawca”. Jako zakres sumowania wskazujemy te komórki, w których znajdują się pensje pracowników (odbywa się to zarówno ręcznie, jak i wybieramy je myszką). Kliknij „OK”, a otrzymamy gotowe obliczone zarobki wszystkich pracowników, którzy są sprzedawcami.

Zgadzam się, że jest to bardzo wygodne. Czyż nie?

Funkcja SUMIFS w programie Excel

Ta funkcja pozwala określić sumę wartości spełniających wiele warunków. Na przykład otrzymaliśmy zadanie ustalenia łącznego wynagrodzenia wszystkich menedżerów pracujących w południowym oddziale firmy.

Dodaj wiersz, w którym będzie wynik końcowy, i wstaw formułę w żądanej komórce. Aby to zrobić, kliknij ikonę funkcji. Pojawi się okno, w którym musisz znaleźć funkcję SUMMESLIMN. Następnie wybierz go z listy i otworzy się znajome okno z argumentami. Ale liczba tych argumentów jest teraz inna. Ta formuła umożliwia użycie nieskończonej liczby kryteriów, ale minimalna liczba argumentów to pięć. 

W oknie dialogowym wprowadzania argumentów można określić tylko pięć. Jeśli potrzebujesz więcej kryteriów, należy je wprowadzić ręcznie, zgodnie z tą samą logiką, co pierwsze dwa.

Przyjrzyjmy się bardziej szczegółowo głównym argumentom:

1. Zakres sumowania. Komórki do zsumowania.
2. Zakres warunków 1 – zakres, który zostanie sprawdzony pod kątem zgodności z określonym kryterium. 
3. Warunek 1 to sam warunek.
4. Zakres kryteriów 2 to drugi zakres, który będzie sprawdzany pod kątem kryterium.
5. Warunek 2 to drugi warunek.

Dalsza logika jest podobna. W efekcie ustaliliśmy płace wszystkich kierowników Oddziału Południowego.

LICZ.JEŻELI w Excelu

Jeśli chcesz określić, ile komórek spełnia określone kryterium, użyj funkcji LICZ.JEŻELI. Powiedzmy, że musimy zrozumieć, ilu sprzedawców pracuje w tej organizacji:

1. Najpierw dodaj wiersz zawierający liczbę sprzedawców. Następnie musisz kliknąć komórkę, w której zostanie wyświetlony wynik.
2. Następnie musisz kliknąć przycisk „Wstaw funkcję”, który można znaleźć w zakładce „Formuły”. Pojawi się okno z listą kategorii. Musimy wybrać pozycję „Pełna lista alfabetyczna”. Na liście interesuje nas formuła LICZ.JEŻELI. Po wybraniu go musimy kliknąć przycisk „OK”.
   

   

3. Po tym mamy liczbę handlowców zatrudnionych w tej organizacji. Uzyskiwany był przez policzenie liczby komórek, w których jest napisane słowo „sprzedawca”. Wszystko jest proste. 

Funkcja COUNTSLIM w Excelu

Podobny do formuły SUMMESLIMN, ta formuła zlicza komórki spełniające wiele warunków. Składnia jest podobna, ale nieco inna niż formuła SUMMESLIMN:

1. Zakres warunków 1. Jest to zakres, który będzie testowany względem pierwszego kryterium.
2. Warunek 1. Bezpośrednio pierwsze kryterium.
3. Zakres warunków 2. Jest to zakres, który będzie testowany względem drugiego kryterium. 
4. Warunek 2.
5. Warunki zasięgu 3.

I tak dalej.

Więc funkcja IF w Excelu – nie jedyny, jest jeszcze kilka jego odmian, które automatycznie wykonują najczęstsze czynności, co znacznie upraszcza życie człowieka. 

W dużej mierze ze względu na funkcję IF Arkusze kalkulacyjne Excel są uważane za programowalne. To więcej niż zwykły kalkulator. Jeśli o tym pomyślisz, to funkcja IF jest podstawą wszelkiego rodzaju programowania.

Jeśli więc nauczysz się pracować z dużymi ilościami danych w Excelu, znacznie łatwiej będzie nauczyć się programowania. Dzięki operatorom logicznym te obszary mają naprawdę wiele wspólnego, choć z Excela częściej korzystają księgowi. Ale mechanizm pracy z danymi jest w dużej mierze taki sam. 

Funkcja we właściwych rękach IF a jego odmiany pozwalają przekształcić arkusz Excela w pełnoprawny program, który może działać na złożonych algorytmach. Zrozumienie, jak działa funkcja IF to pierwszy krok w kierunku nauki makr – kolejny krok w bardziej elastycznej pracy z arkuszami kalkulacyjnymi. Ale to już jest bardziej profesjonalny poziom.