• Home
  • Server 2003/2008
  • Windows 7
  • Office
  • Linux
  • Sieci komputerowe
  • Wujek dobra rada
  • Mapa strony
  • Napisz
  • czcionka Zmniejsz czcionkę Zmniejsz czcionkę Powiększ czcionkę Powiększ czcionkę
  • Wydrukuj
  • Email
  • Komentarze (1)
Excel w zadaniach. Funkcja JEŻELI
pikolo pikolo

Excel w zadaniach. Funkcja JEŻELI

17 październik 2014
Dział: Office
Czytany 46113 razy
Oceń ten artykuł
  • 1
  • 2
  • 3
  • 4
  • 5
(8 głosów)

Funkcja JEŻELI zwraca daną wartość, jeśli podany argument (parametr) zostanie oszacowany jako PRAWDA, albo inną wartość, jeśli argument (parametr) zostanie oszacowany jako FAŁSZ. Dodatkowo funkcję JEŻELI należy stosować do przeprowadzania testów logicznych na wartościach i formułach. Taką definicję funkcji JEŻELI odnajdziemy w pomocy pakietu MS Office. Tłumacząc to trochę prościej zadaniem funkcji JEŻELI jest wykonanie odpowiedniego działania w zależności od tego czy dany warunek zostanie spełniony czy też nie.

Arkusz na którym będziemy pracować można pobrać: tutaj

 

Ogólna składnia funkcji przedstawia się następująco:

 

image1 

czyli: =JEŻELI(A1=B1; "Prawda, wszystko jest ok"; "Fałsz, nie jest równe")

 

Wykonując nasz przykład w przypadku spełnienia warunku (komórki A1 i B1 są sobie równe a więc PRAWDA) otrzymamy:

 image2

 

natomiast w przypadku zaistnienia nierówności (różnica pomiędzy wartościami zawartymi w komórkach A1 i B1 - FAŁSZ), otrzymamy o to taki wynik:

 image3

 

Tak więc jak widać w zależności od uzyskanego wyniku (PRAWDA bądź FAŁSZ) sprawdzanego warunku (a raczej testu logicznego) jest podejmowane odpowiednie działanie.

 

Rozbijając funkcję JEŻELI na składowe, otrzymamy:

      • test_logiczny czyli dowolna wartość lub wyrażenie, którą da się oszacować jako PRAWDA lub jako FAŁSZ. W naszym przykładzie testem logicznym jest wyrażenie A1=B1, jeśli wartości umieszczone w obu komórkach są zgodne co do wartości cały test zostanie oszacowany jako PRAWDA. W przeciwnym przypadku wyrażenie zostanie oszacowane jako FAŁSZ.
      • wartość_jeżeli_prawda to wartość bądź działanie, które jest zwracane bądź wykonywane tylko wtedy, gdy argument test_logiczny przyjmuje wartość PRAWDA lub po prostu, gdy warunek jest spełniony wykonaj wartość_jeżeli_prawda W naszym przykładzie (jak widać powyżej) zostaje wyświetlony ciąg znaków: Prawda, wszystko jest ok gdy test logiczny zostaje oszacowany jako PRAWDA (warunek spełniony, gdyż w obu komórkach jest wpisana liczba 100 czyli zdefiniowany warunek A1=B1 jest PRAWDĄ).
      • wartość_jeżeli_fałsz to wartość bądź działanie, które jest zwracane bądź wykonywane tylko wtedy, gdy argument test_logiczny przyjmuje wartość FAŁSZ lub po prostu, gdy warunek jest niespełniony wykonaj wartość_jeżeli_fałsz. Wracając do naszego przykładu zostaje wyświetlony tekst: Fałsz, nie jest równe w przypadku w którym test logiczny zostaje oszacowany jako FAŁSZ (warunek niespełniony, gdyż wartości wpisane do komórek A1 i B1 nie są zgodne co do wartości).

 

Tyle co mówi nam teoria na temat funkcji JEŻELI, przejdźmy do przykładów tak aby zobaczyć funkcję w działaniu.

 

Wróćmy do naszego wcześniejszego zadania omawianego w wpisie: Excel w zadaniach. Sortowanie, losowanie, zliczanie oraz nazywanie a mianowicie arkusza ucznia. Przyjrzyjmy się kolumną w których jest wyliczana średnia. Można np. tak ułożyć formułę aby w kolumnie Średnia wszystkich ocen była wyświetlana wartość 0,00 a dopiero w momencie wpisania jakiejkolwiek oceny była wyliczana średnia. Zadanie to możemy zrealizować (jest mnóstwo innych sposobów) np. za pomocą formuły: =JEŻELI(SUMA(F3:R3)>0;ŚREDNIA(E3:S3);"0,00"), co tłumaczymy: gdy suma z zakresu ocen będzie większa niż 0 to wykonaj funkcję ŚREDNIA w przeciwnym przypadku wstaw wartość 0,00.

 image4

 

Chociaż mnie osobiście bardziej by przekonała o to taka formuła: =JEŻELI(CZY.LICZBA(E3)+CZY.LICZBA(S3)=2;ŚREDNIA(E3:S3);"0,00") Ten przykład zapewnia nam dodatkowo sprawdzenie czy wpisaliśmy ocenę pierwszą (religia) oraz ocenę ostatnią (j.niemiecki) i dopiero gdy te dwa warunki są spełnione następuje wyliczenie średniej. W warunku funkcji JEŻELI zostało zapisane: gdy wartość testu logicznego będzie równać się 2 oblicz średnią z komórek, jeżeli zaś warunek ten jest nie spełniony wypisz 0,00. Można by zapytać skąd u licha wzięło się 2? Wartość 2 została użyta ponieważ warunek CZY.LICZBA(E3)+CZY.LICZBA(S3) po spełnieniu zwraca wartość 2. Funkcja CZY.LICZBA(E3) sprawdza czy w komórce E3 znajduje się liczba, gdy fakt ten zachodzi zostaje zwrócona wartość PRAWDA. Analogicznie działa druga funkcja sprawdzająca wystąpienie liczby w komórce S3. Ponieważ pomiędzy tymi funkcjami został użyty znak + to ni jako otrzymujemy dwie PRAWDY. Poniżej widzimy efekt zastosowania formuły. Jak widać poniżej średnia jest wyliczana tylko w przypadku wpisania obu ocen tj. oceny z religii i j. niemieckiego.

 image5

 

Podobnie moglibyśmy wykonać sprawdzenie faktu wpisania ocen z wszystkich dostępnych przedmiotów, przyrównując wartość testu logicznego do 15 (gdyż tyle jest przedmiotów a każde poprawne wykonanie funkcji CZY.LICZBA daje 1):

=JEŻELI(CZY.LICZBA(E3)+CZY.LICZBA(F3)+CZY.LICZBA(G3)+CZY.LICZBA(H3)+CZY.LICZBA(I3)+

+CZY.LICZBA(J3)+CZY.LICZBA(K3)+CZY.LICZBA(L3)++CZY.LICZBA(M3)+CZY.LICZBA(N3)+

+CZY.LICZBA(O3)+CZY.LICZBA(P3)+CZY.LICZBA(Q3)+CZY.LICZBA(R3)+CZY.LICZBA(S3)=

=15;ŚREDNIA(E3:S3);"0,00")

 

Jak widać zapisanie funkcji będzie już dość skomplikowane a że działa to widzimy na rysunku poniżej.

 image6

 

Oczywiście, ktoś uważny mógłby zapytać - A co z wpisem nkl? (oznaczającym ucznia niesklasyfikowanego, uczeń 4, na rysunku powyżej). Czemu to średnia w przypadku wpisania nkl, nie miała by być liczona? Oczywiście działanie naszej powyższej formuły jest jak najbardziej prawidłowe, ponieważ weryfikujemy wystąpienie liczby a zapis nkl nie jest liczbą i dla tego test logiczny nie zostaje sprawdzony a tym samym w komórce zostaje wypisana wartość 0,00. Aby poprawić wpiszmy:

=JEŻELI(CZY.PUSTA(E6)+CZY.PUSTA(F6)+CZY.PUSTA(G6)+CZY.PUSTA(H6)+CZY.PUSTA(I6)+

+CZY.PUSTA(J6)+CZY.PUSTA(K6)+CZY.PUSTA(L6)+CZY.PUSTA(M6)+CZY.PUSTA(N6)+

+CZY.PUSTA(O6)+CZY.PUSTA(P6)+CZY.PUSTA(Q6)+CZY.PUSTA(R6)+CZY.PUSTA(S6)=

=0;ŚREDNIA(E3:S3);"0,00")

 image7

 

Wartość testu logicznego została przyrównana do 0 gdyż funkcja CZY.PUSTA sprawdza czy w komórce jest wpisana jakakolwiek wartość (liczba, tekst, data) gdy komórka jest uzupełniona zwracana jest wartość FAŁSZ (czyli 0) a w przypadku braku wpisu wartość PRAWDA (czyli 1). Nam zależy aby wszystkie komórki były uzupełnione a więc nie może się zdarzyć aby któraś z funkcji CZY.PUSTA przyjęła wartość PRAWDA tym samym dając 1 (brak wpisu oceny). Każda z funkcji CZY.PUSTA musi zwrócić wartość FAŁSZ, zwracając 0 a tym samym suma wszystkich wykonanych funkcji CZY.PUSTA musi dać 0 (tak się stanie gdy wpiszemy wszystkie oceny). Gdy warunek zostaje spełniony obliczana jest wartość średniej wprowadzonych ocen.

 

Jak widać powyżej zaprezentowane funkcje, co zostało już nadmienione wyglądają nader skomplikowanie i łatwo jest przy konstruowaniu ich popełnić błąd. Czy można je zapisać prościej? Oczywiście, że tak. Trzeba znać tylko pojęcie formuły tablicowej. Tak więc w obu przypadkach nasze funkcje mogłyby by przyjąć postać:

- dla funkcji CZY.LICZBA - {=JEŻELI(ORAZ(CZY.LICZBA(E3:S3));ŚREDNIA(E3:S3);"0,00")}

 image8

 

-dla funkcji CZY.PUSTA - {=JEŻELI(LUB(CZY.PUSTA(E3:S3));"0,00";ŚREDNIA(E3:S3))}

 image9

 

Wybacz czytelniku ale nie będę w tym wpisie rozwijał zagadnienia formuł tablicowych jak i co pewne zauważyłeś dwóch kolejnych funkcji logicznych ORAZ i LUB (użytych w powyższych formułach), gdyż tematy te będę chciał opisać w osobnych wpisach. Na tym etapie chciałem tylko pokazać, iż funkcję JEŻELI można łączyć z innymi funkcjami oraz używać jej właśnie w formułach tablicowych. Oczywiście zachęcam do wykonania opisanych przeze mnie przykładów i sprawdzenia, czy to wszystko działa. Przy formule tablicowej nadmienię tylko jedną rzecz, że aby ona zadziałał należy użyć klawiszy Ctrl+Shift+Enter tzn. nawiasów klamrowych {} nie wprowadzamy, wpisujemy formułę a po wciśnięciu nadmienionych klawiszy nawiasy te automatycznie zostaną dopisane. Nawiasy klamrowe przez Excel są używane do zaznaczenia, iż mamy do czynienia właśnie z formułą tablicową.

 

Funkcję JEŻELI bardzo często używa się do porównania tych samych danych lecz umieszczonych w różnych kolumnach. Za pomocą tej funkcji bardzo szybko jesteśmy w stanie zweryfikować czy dane są ze sobą zgodne.

 

Posiadamy arkusz uzupełniony przykładowymi danymi, należy porównać czy dane z kolumny B oraz kolumny C w tych samych wierszach są ze sobą zgodne. W przypadku gdy w komórkach występuje zgodność, powinien pojawić się komunikat „ok”, natomiast gdy komórki różnią się od siebie, powinien pojawić się komunikat „błąd”. Porównanie zrealizujemy za pomocą o to takiej prostej formuły: =JEŻELI(B6=C6;"ok";"błąd")

 image10

 

Jak widać poniżej przy tak skonstruowanej formule nie jest uwzględniana wielkość liter. Dla Excela komórka z wpisaną wartością AQZ będzie tożsama z Aqz, AQz, azq, aZq itd. Aby wprowadzić rozróżnienie wielkości liter należy skorzystać z dodatkowej funkcji PORÓWNAJ. Funkcja ta porównuje ze sobą dwa ciągi tekstu zwracając wartość PRAWDA w przypadku zgodności ciągów a wartość FAŁSZ w przypadku jej braku. W przypadku funkcji PORÓWNAJ rozróżniane są wielkie i małe litery, ale ignorowane są różnice w formatowaniu.

 image11

 

Aby porównać ze sobą trzy różne komórki można oto skorzystać z takiej formuły: =JEŻELI(PORÓWNAJ(B16;C16)+PORÓWNAJ(C16;D16)=2;"ok";"błąd") Formuła ta sprawdza czy dana komórka (w tym przypadku komórka C16) jest zgodna z dwiema pozostałymi (z komórką B16 oraz D16). W przypadku zgodności zwracana jest wartość PRAWDA czyli 1, zgodność z obiema komórkami zwraca wartość 2 (PRAWDA+PRAWDA).

 image12

 

Porównanie tego typu można również zrealizować za pomocą zagnieżdżenia funkcji JEŻELI ale jak to wykonać wytłumaczę za chwilę.

 

Kolejnym zadaniem w którym możemy wykorzystać funkcję JEŻELI jest wyszukiwanie wartości spełniających zadane kryterium.

 

Przypuśćmy, że pracujemy w sklepie AGD i mamy przygotowany arkusz zawierający listę modeli telewizorów dostępnych w sklepie (taki jak poniżej) i chcemy znać liczbę modeli telewizorów firmy Sony.

 image13

 

Jednym z sposobów poradzenia sobie z zadaniem (rozwiązanie wcale nie jedyne i trza przyznać, że niezbyt wyszukane) jest dołożenie sobie kolejnej kolumny w której pojawi się słowo tak jeżeli model telewizora będzie wyprodukowany właśnie przez Sony. W kolumnie tej trzeba oczywiście wpisać odpowiednią formułę, która przyjmie postać: =JEŻELI(B2="Sony";"tak";"nie").

 image14

 

Jak widać powyżej przy każdym modelu Sony pojawiło się słowo tak.

 

Skomplikujmy sobie troszkę zadanie i dodajmy kolejne kryterium. Powiedzmy, że teraz interesują nas telewizory marki Samsung lecz tylko te, których rozmiar przekracza 32 cale. Zadanie to można wykonać lecz trzeba użyć dwa razy funkcję JEŻELI, cały trik polega na umiejętnym zagnieżdżeniu funkcji JEŻELI. Tak więc dla lepszego zrozumienia działania formuły w której funkcja JEŻELI została zagnieżdżona przyjmijmy: w przypadku gdy telewizor nie został wyprodukowany przez firmę Samsung niech pojawi się komunikat – „inna firma”; w przypadku w którym telewizor jest mniejszy niż 32 cale - komunikat – „poniżej 32 cali” a gdy nasze kryterium zostanie spełnione (firma Samsung i powyżej 32 cali) – komunikat – „wszystko ok”

 

Czyli nasze zadanie moglibyśmy rozpisać w ten poniższy sposób (wykorzystamy schemat blokowy):

 image15

 

Z pomocą schematu dokładnie możemy określić składnię formuły (jak ona działa), wystarczy, że romby symbolizujące warunek zamienimy na funkcję JEŻELI, przypisując do pytań w rombach odpowiedni adres komórki, wartości na TAK użyjemy do przypisania parametru wartość jeżeli prawda a wartości na NIE do przypisania parametru wartość jeżeli fałsz. Tak więc nasza formuła przyjmie następującą postać: =JEŻELI(B2="Samsung";JEŻELI(D2>32;"wszystko ok";"poniżej 32 cale");"inna firma")

 

Jak widać poniżej po przekopiowaniu formuły do pozostałych komórek uzyskaliśmy zamierzony efekt.

 image16

 

Oczywiście funkcję JEŻELI możemy zagnieżdżać wiele razy (a tak naprawdę maksimum to 7) tym samym sprawdzając wiele warunków.

 

Takim przykładowym zadaniem może być obliczenie wskaźnika BMI. BMI to skrót z języka angielskiego „body mass index”, co tłumaczymy jako wskaźnik masy ciała. Wskaźnik BMI określa relację pomiędzy masą ciała a wzrostem. Współczynnik ten wyliczamy poprzez podzielenie masy ciała podanej w kilogramach przez kwadrat wysokości podanej w metrach. Obliczając BMI możemy określić ilość tkanki tłuszczowej w organizmie.

 

Nasz kalkulator w zależności od wartości wyliczonego indeksu wyświetli odpowiedni komunikat według następującego przelicznika:

      • do 18,5 - niedobór masy,
      • 18,6 - 24,9 - masa prawidłowa,
      • 25,0 - 29,9 - nadwaga,
      • 30 - 34,9 - 1. st. otyłości,
      • 35 - 39,9 - 2. st. otyłości.

 

Zadanie to jest trochę trudniejsze niż te z wyborem telewizora z kilku powodów. Po pierwsze rozwiązaniem zadania będzie formuła zawierająca cztery funkcje JEŻELI. Po drugie w zadaniu tym nie będziemy porównywać wartości z wzorcem lecz będziemy musieli zdefiniować przedziały. I w końcu po drugie i pół (gdyż kolejna trudność jest powiązana z powodem drugim) – utworzone zakresy (przedziały) trzeba ułożyć w odpowiedniej kolejności.

 

Tak więc przykładowa formuła rozwiązująca nasz problem mogłaby wyglądać następująco:

=JEŻELI(E18<=18,5;"niedobór masy";JEŻELI(E18<=24,9;"masa prawidłowa";JEŻELI(E18<=29,9;"nadwaga";JEŻELI(E18<=34,9;"1 st otyłości";"2 st otyłości"))))

 

Oczywiście by rozwiązać to zadanie można by również użyć takiej o to formuły:

=JEŻELI(E18>=35;"2 st. otyłości";JEŻELI(E18>=30;"1 st. otyłości";JEŻELI(E18>=25;"nadwaga";JEŻELI(E18>=18,6;"masa prawidłowa";"niedobór masy"))))

 

Wspomniałem, że należy zwrócić uwagę na odpowiednie położenie zakresów (właściwa kolejność). Spójrzmy co by się wydarzyło gdybyśmy naszą formułę zapisali następująco:

=JEŻELI(E18>=18,6;"masa prawidłowa";JEŻELI(E18>=25;"nadwaga";JEŻELI(E18>=30;"1 st. otyłości";JEŻELI(E18>=35;"2 st. otyłości";"niedobór masy"))))

 

Tak zapisana formuła po wpisaniu wartości, która przekroczy wartość 18,6 zawsze będzie wyświetlać komunikat: masa prawidłowa, stanie się tak, ponieważ funkcja JEŻELI po wykonaniu pierwszego testu uzna, że jest on prawdziwy (co zresztą będzie prawdą) a tym samym zostanie pominięte sprawdzenie kolejnych warunków.

 

Z funkcją JEŻELI będziemy mieli do czynienia bardzo często. Zaryzykuję stwierdzenie, że jest to najczęściej wykorzystywana funkcja Excela a połączenie funkcji JEŻELI z innymi dostępnymi funkcjami zwiększa jej standardowe możliwości. Tak więc czytelniku po omówieniu tematu oraz ukazaniu podstawowych możliwości jak na ten wpis sprawę uważam za zamkniętą, co nie zmienia faktu, że jeszcze nie jeden raz w innych artykułach do funkcji JEŻELI będę się odwoływał.

Ostatnio zmieniany czwartek, 15 grudzień 2016 08:23
Etykiety
  • JEŻELI
  • CZYPUSTA
  • CZYLICZBA
  • warunek
  • formuła tablicowa
  • arkusz kalkulacyjny
  • formuła
  • zakres
  • funkcja
  • MSOffice
  • ŚREDNIA
  • SUMA
  • EXCEL

Artykuły powiązane

  • Excel w zadaniach. Funkcje tekstowe i wyszukujące.
  • Łączenie liczb i tekstu.
  • Jak usunąć puste komórki?
  • Excel w zadaniach. Tworzenie i modyfikacja wykresów.
  • Bezstresowa prezentacja.
Więcej w tej kategorii: « Excel w zadaniach. Funkcja ORAZ i LUB Excel w zadaniach. Sortowanie, losowanie, zliczanie oraz nazywanie. »

Dodaj komentarz



Odśwież

Wyślij
Skasuj

Komentarze  

# Andrzej 2018-10-30 17:04
Poszukuję formuły, która komórkę liczbową o wartości "0" wyświetli (pokaże )jako pustą.
Cytować
Odśwież komentarze
JComments
Powrót na górę

Wujek dobra rada

Szybkie pytania i szybkie odpowiedzi czyli garść porad do wykorzystania w codziennej pracy z komputerem.

  • Jak utworzyć RAMdysk w systemie Windows? Jak utworzyć RAMdysk w systemie Windows?

    RAMdysk jest wydzieloną częścią pamięci, która w systemie operacyjnym jest widziana jak kolejny dysk/partycja. Praca z tak wydzielona przestrzenią pamięci odbywa się jak z normalnym dyskiem. Dostępne są wszystkie operacje związane z plikami.  

    Napisano poniedziałek, 04 grudzień 2017 21:44
  • Bezpieczny pendrive Bezpieczny pendrive

    Jak zabezpieczyć nasze dane w sytuacji utraty pendiva/karty pamięci.

    Napisano czwartek, 29 czerwiec 2017 12:00
  • Wyszukiwanie plików w systemie Windows Wyszukiwanie plików w systemie Windows

    Krótki opis jak wyszukać pliki przy wykorzystaniu Eksploratora plików.

    Napisano sobota, 17 czerwiec 2017 20:31
  • Diagnostyka pamięci RAM Diagnostyka pamięci RAM

    Jak zdiagnozować uszkodzenie modułu pamięci RAM

    Napisano wtorek, 16 maj 2017 12:39
  • Konwersja maszyny fizycznej na wirtualną (odsłona druga). Konwersja maszyny fizycznej na wirtualną (odsłona druga).

    W poprzednim wpisie (Konwersja maszyny fizycznej na wirtualną) opisałem konwersję maszyny fizycznej do wirtualnej, efektem Naszych działań było przeniesienie systemu działającego na fizycznym hoście do środowiska opartego o oprogramowanie Vmware. Zaś w tym wpisie wykonamy podobne działanie lecz efektem będzie uzyskanie maszyny działającej w VirtualBox.

    Napisano czwartek, 04 maj 2017 11:53
Czytaj więcej...

Najczęściej komentowane

  • Jak wyznaczyć broadcast, adres sieci i liczbę hostów? (+20)
  • Dostęp zdalny oraz prawa użytkownika w urządzeniach CISCO (+14)
  • Instalacja Windows XP/Vista/7 z pendriv'a. (+12)
  • Co w sieci siedzi. Protokół DNS. (+9)
  • Windows 10 - Hyper-V Czyli jak skonfigurować i uruchomić wirtualny system. (+9)

Najnowsze komentarze

  • Kamil 09.07.2019 21:25
    Samba dziala wysmienicie. Dzięki kolego za ten artykuł :)
     
  • Damian 09.06.2019 19:45
    A co się dzieje jak padnie mi dysk systemowy i będę musiał postawić nowy system? Bez problemu odzyskam ...
     
  • Piotr 29.05.2019 21:01
    Dzięki mordo
     
  • Bravo 22.05.2019 13:11
    Gratuluję strony i znajomości tematu. Tak 3mać
     
  • tej 02.05.2019 10:35
    swietny artykul! Ciekawe czy autor ma czas na kontynuacje, np. odpowiadajac na komentarz BG o failover ...

Ostatnio komentowane

  • Windows i Linux w jednej stali sieci. (8)
  • Macierze RAID w systemie Linux (5)
  • Listy kontroli dostępu ACL (4)
  • Usługa katalogowa Active Directory - Zarządzanie (4)
  • Windows Server 2012 - Serwer RADIUS (4)

Popularne tagi

AAA Active Directory arkusz kalkulacyjny CISCO cmd DHCP DNS domena EXCEL filtrowanie formuła funkcja GPO grupy ICMP jednostka organizacyjna JEŻELI LibreOffice Linux MSOffice panel sterowania PowerShell przełącznik PuTTY RADIUS rejestr Router Serwer SSH SUMA switch TCP trunk Ubuntu UDP usługi VLAN warstwa 2 warstwa 3 warstwa sieciowa warstwa łącza danych Windows wirtualizacja zakres ŚREDNIA

UWAGA! Ten serwis używa cookies

Brak zmiany ustawienia przeglądarki oznacza zgodę na to.

Zrozumiałem

Created by: clivio.pl

Copyright © Created by: 2019 All rights reserved. Custom Design by Youjoomla.com
Home