Wyszukiwanie w witrynie

3 najlepsze funkcje logiczne, których zawsze używam w Excelu

Funkcje logiczne Excela sprawdzają, czy dane stwierdzenie lub dane są prawdziwe czy fałszywe, zanim program pozwoli na wykonanie działania w oparciu o wynik. Są one przydatne do analizowania danych, automatyzowania niektórych zadań lub obliczeń, a ostatecznie do podejmowania decyzji.

Gdy są używane samodzielnie, zwracają TRUE lub FALSE, w zależności od tego, czy spełnione są określone przez Ciebie kryteria.

Przedstawię trzy funkcje logiczne (lub grupy funkcji), które uważam za szczególnie przydatne, oraz jak można je również wykorzystać w praktycznych sytuacjach.

JEŻELI z AND, OR i NOT

Zanim wyjaśnię, jak te funkcje mogą być używane razem, wyjaśnię, co każda z nich robi osobno.

AND, OR i NOT same w sobie pomagają określić warunki. W poniższym przykładzie chcę ustalić trzy rzeczy dla każdego kandydata:

  • Czy mają ukończone 18 lat i posiadają certyfikat złoty (kolumna F)?
  • Czy mają pełne prawo jazdy lub możliwość pracy za granicą (kolumna G)?
  • Czy oni nie mają ponad 50 lat (kolumna H)?

W komórce F2 muszę wpisać

=AND(B2>18,D2="Gold")

ponieważ chcę, aby Excel powiedział mi, czy wartość w B2 jest większa niż 18 i wartość w D2 jest równa „Gold”.

W komórce G2 wpisałem

=OR(C2="Full",E2="YES")

ponieważ chcę, aby Excel sprawdził, czy wartość w C2 to „Full” lub wartość w E2 to „Yes”.

Wreszcie, w komórce H2, będę musiał wpisać

=NOT(B2>50)

ponieważ muszę sprawdzić, czy wartość w komórce B2 nie jest większa niż 50.

Tworząc argumenty logiczne, cały tekst musi znajdować się w podwójnych cudzysłowach.

Następnie użyję funkcji AutoFill w Excelu, aby skopiować formuły do pozostałych wierszy.

Zauważ, że każdy wynik jest albo PRAWDA, albo FAŁSZ, w zależności od tego, czy warunki zostały spełnione. Chociaż jest to wygodne, posiadanie konkretnego słowa lub frazy wskaźnikowej może być jeszcze bardziej przydatne. Tutaj z pomocą przychodzi funkcja IF, ponieważ zwraca określoną wartość w zależności od ustalonych warunków.

Tym razem chcę osiągnąć następujące rezultaty dla każdego kandydata:

  • Jeśli mają ponad 18 lat i są certyfikowani na złoto, mogą być oznaczeni jako członek seniorskiego poziomu.
  • Jeśli mają pełne prawo jazdy lub możliwość pracy za granicą, mogą być określeni jako kandydat, który może podróżować.
  • Jeśli nie mają ponad 50 lat, chcę, aby Excel dodał etykietę informującą mnie, że kwalifikują się do nauki zawodu.

Więc w komórce F2 wpiszę

=IF(AND(B2>18,D2="Gold"),"Senior","Junior")

ponieważ chcę, aby Excel sprawdził, czy wartość w B2 jest większa niż 18 i wartość w D2 to „Gold”, a następnie zwrócił „Senior”, jeśli obie te warunki są prawdziwe, lub „Junior”, jeśli nie.

W komórce G2 muszę wpisać

=IF(OR(C2="Full",E2="YES"),"Can travel","Cannot travel")

ponieważ jeśli wartość w C2 to „Full” lub wartość w E2 to „Yes”, muszę wiedzieć, że „mogą podróżować”. Jeśli żaden z warunków nie jest spełniony, „nie mogą podróżować”.

Na koniec, w komórce F2 wpiszę

=IF(NOT(B2>50),"Eligible for scholarship","No scholarship")

ponieważ jeśli wartość w komórce B2 nie jest większa niż 50, wnioskodawca jest „Uprawniony do stypendium”, natomiast jeśli jest większa niż 50, to „Brak stypendium”.

Ponownie przeciągnę uchwyty AutoFill w dół dla wszystkich trzech kolumn, aby wypełnić pozostałe wiersze mojej tabeli.

Spróbuj użyć funkcji IF, AND i OR z polami wyboru w Excelu, aby śledzić postęp zadań. Możesz także skorzystać z funkcji XOR, która w najprostszej formie zwraca TRUE, jeśli tylko jedna wartość jest TRUE, lub FALSE, jeśli obie wartości są TRUE.

JEŻELIBŁĄD

Używam funkcji IFERROR, aby moje arkusze kalkulacyjne były uporządkowane. W końcu nikt nie chce arkusza pełnego błędów #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? i #NULL!, a funkcja IFERROR pomaga temu zapobiec.

W tym przykładzie śledzę współczynnik goli na mecz dziesięciu zawodników. Aby to zrobić, wpisałem

=SUM(C2/B2)

do komórki D2, a następnie rozszerzono to obliczenie w dół całej kolumny D.

Jednak ponieważ gracz C nie rozegrał żadnych gier, dzielenie zwraca #DZIEL/0!.

Aby to uporządkować, osadziłbym wszystkie obliczenia w kolumnie D w funkcji IFERROR.

=IFERROR(x,y)

gdzie x to wykonywane obliczenie, a y to wartość zwracana w przypadku błędu.

Jeśli pozostawisz y puste po przecinku, Excel zwróci 0 za błędne obliczenie.

Więc w komórce D2 wpiszę

=IFERROR(SUM(C2/B2),"-")

i rozciągnij to w dół kolumny. Zwróć uwagę, jak

SUM(C2/B2)

Obliczenie nadal istnieje, ale jest osadzone w funkcji IFERROR. W takim przypadku, za każdym razem gdy w obliczeniu pojawi się błąd, zostanie zwrócony myślnik, co wygląda znacznie czyściej niż komunikat o błędzie.

Pamiętaj, że używanie funkcji IFERROR do ukrywania błędów w arkuszu może utrudniać identyfikację błędów obliczeniowych. Dlatego zwykle stosuję ją tylko w sytuacjach, gdy w przeciwnym razie pojawiłoby się #DZIEL/0!

IS z IF

Istnieje kilka funkcji IS, a każda z nich robi zupełnie inne rzeczy. Często ich używam (w funkcji IF), aby sprawdzić, czy w moich danych nie ma żadnych błędów lub niezgodności.

Zanim przyjrzymy się temu, jak łączyć je z funkcją IF, spójrzmy na nie osobno.

Wszystkie funkcje IS mają tę samą składnię:

=IS[TYPE](a)

gdzie [TYPE] to typ funkcji IS, który chcesz użyć, a a to odwołanie do komórki lub wartość do oceny.

Oto rodzaje funkcji systemów informacyjnych, które możesz wybrać, a poniżej możesz zobaczyć kilka przykładów:

  • ISBLANK: Sprawdza, czy a jest puste.
  • ISOMITTED: Sprawdza, czy wartość w funkcji LAMBDA jest pominięta.
  • ISERROR: Sprawdza, czy a jest wartością błędu (taką jak #N/A, #VALUE! itp.).
  • ISERR: Sprawdza, czy a jest dowolną wartością błędu z wyjątkiem #N/A.
  • ISNA: Sprawdza, czy a zawiera błąd #N/A.
  • ISFORMULA: Sprawdza, czy a jest formułą.
  • ISLOGICAL: Sprawdza, czy a zawiera wartość logiczną (PRAWDA lub FAŁSZ) na podstawie funkcji logicznej.
  • ISTEXT: Sprawdza, czy a jest tekstem, w tym tekstem wygenerowanym przez funkcję logiczną.
  • ISNONTEXT: Sprawdza, czy a nie jest tekstem (np. formułą lub liczbą), w tym tekstem wygenerowanym przez funkcję logiczną.
  • ISNUMBER: Sprawdza, czy a jest liczbą, w tym liczbą uzyskaną w wyniku formuły.
  • ISEVEN lub ISODD: Sprawdza, czy a jest liczbą parzystą czy nieparzystą, w zależności od tego, którego użyjesz. W tych przypadkach pusta komórka jest traktowana jako parzysta, a komórka nienumeryczna zwraca błąd.
  • ISREF: Sprawdza, czy a jest odwołaniem do komórki. Na przykład, jeśli a to (A1), zwróci TRUE, ale jeśli a to ("apple"), zwróci FALSE.

Mimo że zaczynają się od IS, funkcje ISPMT i ISOWEEKNUM nie należą do grupy IS.

Sprawdźmy niektóre z nich w arkuszu Excel. W kolumnach B do J użyłem określonych funkcji IS, aby przetestować wartości w kolumnie A.

Na przykład w komórce B2 wpisałem

=ISBLANK(A2)

a w komórce G4 wpisałem

=ISNONTEXT(A4)

Jeśli Twoja formuła IS odwołuje się do komórki w sformatowanej tabeli programu Excel, wartością w nawiasach byłaby nazwa kolumny. W powyższym przykładzie wpisując

=ISTEXT([@Value to test])

w komórce F1 automatycznie zastosowałoby ją następnie do pozostałych komórek w kolumnie F w tabeli.

Aby używać funkcji IS z IF, musisz umieścić te pierwsze wewnątrz tych drugich.

=IF(IS[TYPE](a),b,c)

gdzie [TYPE] to typ funkcji IS, którą chcesz użyć z powyższej listy, a to odwołanie do komórki lub wartość do oceny, b to wartość lub formuła, jeśli wynik jest PRAWDA, a c to wartość lub formuła, jeśli wynik jest FAŁSZ.

W poniższym przykładzie chciałem obliczyć prognozowane tygodniowe zyski moich pracowników na podstawie ich dziennych zysków, ale wyświetlić komunikat, jeśli dzienny zysk jest pusty. Aby to zrobić, wpisałem tę formułę w komórce C2:

=IF(ISBLANK(B2),"Data required",B2*7)

ponieważ chciałem, aby Excel sprawdził, czy komórka B2 jest pusta, i albo zwrócił „Wymagane dane” (jeśli B2 jest pusta), albo pomnożył wartość w B2 przez siedem (jeśli nie jest pusta). Następnie użyłem funkcji Autouzupełnianie, aby skopiować względną formułę do pozostałych komórek w kolumnie C.

Możesz zrobić to samo z dowolną z funkcji IS wymienionych powyżej.

Oprócz korzystania z funkcji logicznych Excela, używam różnych innych kombinacji funkcji do analizowania i wykorzystywania danych w tabelach Excela, takich jak INDEX z MATCH oraz COUNTIF z SUM. Jeśli nie znasz ich jeszcze, zdecydowanie warto je wypróbować!

Powiązane artykuły