Funkcje logiczne w programie Excel. Wszystko o stosowaniu funkcji logicznych w Excelu

Excel to niesamowicie funkcjonalny program, który pozwala nie tylko rejestrować dane w formie tabelarycznej, ale także automatyzować ich przetwarzanie. Funkcje logiczne są głównym elementem pozwalającym na wykonywanie dowolnych operacji tego rodzaju. Są używane w formułach i innych funkcjach w celu uproszczenia wszystkich operacji.

Mają na celu sprawdzenie, czy wartości spełniają określone kryteria. Jeżeli takie dopasowanie występuje, w komórce, w której jest wpisane, wpisuje się wartość „PRAWDA”, w przypadku rozbieżności – „FAŁSZ”. Dzisiaj rozważymy bardziej szczegółowo takie kwestie, jak struktura funkcji logicznych, zakres ich wykorzystania.

Lista funkcji logicznych w Excelu

Istnieje ogromna liczba funkcji logicznych, ale najczęściej używane są następujące:

1. TRUE
2. KŁAMLIWY
3. IF
4. JEŻELI BŁĄD
5. OR
6. И
7. NIE
8. EOSZIBKA
9. JEST PUSTY

Wszystkie mogą posłużyć do tworzenia złożonych struktur i określania kryteriów dowolnego zlecenia. Prawie wszystkie te funkcje wiążą się z przekazaniem im określonych parametrów. Jedynymi wyjątkami są TRUE i FALSE, które zwracają się same. Liczby, tekst, odwołania do komórek, zakresy itd. są często używane jako parametry. Przyjrzyjmy się wszystkim powyższym operatorom.

Operatory PRAWDA i FAŁSZ

Cechą wspólną obu tych funkcji jest to, że zwracają tylko jedną wartość. Zakres ich stosowania to wykorzystanie jako składnik innych funkcji. Jak wynika z nazwy operatorów, funkcje TRUE и KŁAMLIWY zwracaj wartości TRUE и KŁAMLIWY odpowiednio.

NIE operator

Ta funkcja jest używana z jednym argumentem i zapisuje w komórce wartość przeciwną. Jeśli zdasz tego operatora TRUE, to wróci KŁAMLIWY i odpowiednio przeciwne twierdzenie jest prawdziwe. Dlatego wynik przetwarzania danych przez tego operatora całkowicie zależy od tego, jakie parametry mu przekazać.

Składnia tego operatora jest następująca: =NIE(prawda lub fałsz).

Operatory AND i OR

Te dwa operatory są konieczne, aby przekazać wzajemne relacje warunków wyrażenia. Funkcjonować И służy do wskazania, że ​​dwa kryteria muszą odpowiadać temu samemu numerowi lub tekstowi w tym samym czasie. Ta funkcja zwraca wartość TRUE tylko pod warunkiem, że wszystkie kryteria dają tę wartość w tym samym czasie. Jeśli co najmniej jedno kryterium zawiedzie, cała sekwencja zwróci wartość KŁAMLIWY.

Sposób budowy operatora AND jest bardzo prosty: =I(argument1; argument2; …). Maksymalna liczba argumentów, których może użyć ta funkcja to 255. Składnia operatora OR podobnie, ale mechanika pracy jest nieco inna. Jeśli jedna z listy funkcji daje wynik TRUE, to liczba ta zostanie zwrócona jako cały ciąg logiczny.

Oświadczenia IF i ISERROR

Te dwie funkcje mają bardzo ważny cel – bezpośrednio wyznaczają kryterium zgodności, z którym należy sprawdzić dane wyrażenie. Aby lepiej zrozumieć, jak działa operator JEŻELI BŁĄD, musisz najpierw opisać funkcję IF. Jego ogólna struktura jest nieco bardziej skomplikowana niż poprzednie: =JEŻELI(wyrażenie_logiczne;wartość_jeśli_prawda;wartość_jeśli_fałsz).

Zadaniem tego operatora jest tworzenie najbardziej skomplikowanych konstrukcji. Sprawdza, czy kryteria są spełnione. Jeśli tak, operator wróci TRUE, Jeśli nie - KŁAMLIWY. Ale operator jest często używany w połączeniu z innymi. Na przykład, jeśli jest używany jako argument funkcji NIE, to odpowiednio suma zostanie automatycznie zastąpiona przez odwrotną. Oznacza to, że jeśli istnieje dopasowanie do kryterium, to wartość zostanie zwrócona KŁAMLIWY. To jest główna zaleta funkcji logicznych: można je łączyć w najdziwniejsze formy.

Co więcej, schemat staje się bardziej skomplikowany. Jeśli według tego kryterium otrzymamy wynik „PRAWDA”, to można określić tekst, liczbę, która będzie wyświetlana lub funkcję, która zostanie obliczona. Podobnie można ustawić wynik, który będzie wyświetlany, jeśli wynik zostanie zwrócony po przetworzeniu danych. KŁAMLIWY.

Struktura operatora JEŻELI BŁĄD całkiem podobny, ale wciąż nieco inny. Zawiera dwa wymagane argumenty:

1. Oznaczający. To samo wyrażenie jest testowane. Jeśli okaże się, że to prawda, zwracana jest ta wartość.
2. Wartość w przypadku błędu. Jest to tekst, liczba lub funkcja, która zostanie wyświetlona lub wykonana, jeśli wynik sprawdzenia pierwszego argumentu będzie FAŁSZ.

Składnia: =JEŻELIBŁĄD(wartość;wartość_jeśli_błąd).

Operatorzy ISERROW i ISMPLAND

Pierwsza z powyższych funkcji zawiera tylko jedną wartość i ma następującą składnię: =BŁĄD(wartość). Zadaniem tego operatora jest sprawdzenie, jak dobrze wypełnione są komórki (jedna lub cały zakres). Jeśli okaże się, że wypełnienie było nieprawidłowe, zwraca prawdziwy wynik. Jeśli wszystko jest dobre – fałszywe. Może być stosowany bezpośrednio jako kryterium innej funkcji.

Program Excel może sprawdzać łącza pod kątem następujących typów błędów:

• #NAZWA?;
• #Nie dotyczy;
• #DEL/0!;
• #NUMER!;
• #WIĘC;
• #PUSTY!;
• #POŁĄCZYĆ!.

Funkcjonować JEST PUSTY Ogólnie rzecz biorąc, jest to niewiarygodnie proste. Zawiera tylko jeden parametr, którym jest komórka/zakres do sprawdzenia. Jeśli istnieje komórka, która nie zawiera tekstu, liczb ani znaków niedrukowalnych, zwracany jest wynik TRUE. W związku z tym, jeśli we wszystkich komórkach zakresu znajdują się dane, użytkownik otrzymuje wynik KŁAMLIWY.

Tabela notatek „Funkcje logiczne w Excelu”

Podsumowując wszystko opisane powyżej, dajmy małą tabelkę zawierającą informacje o wszystkich powszechnie używanych funkcjach logicznych.

Funkcje logiczne i przykłady rozwiązywania problemów

Funkcje logiczne umożliwiają rozwiązywanie różnorodnych zadań, także złożonych. Podajmy kilka przykładów ich działania w praktyce.

Zadanie 1. Załóżmy, że po pewnym czasie sprzedaży pozostała nam część towaru. Należy go ponownie wycenić według następujących zasad: jeśli nie udało się go sprzedać w ciągu 8 miesięcy, jego cenę podziel 2 razy. Najpierw utwórzmy zakres opisujący początkowe dane. To wygląda tak.

Aby opisane zadanie zostało pomyślnie rozwiązane, musisz skorzystać z poniższej funkcji.

Możesz to zobaczyć na pasku formuły na zrzucie ekranu. Teraz zróbmy kilka wyjaśnień. Logiczne wyrażenie pokazane na zrzucie ekranu (czyli C2>=8) oznacza, że ​​produkt musi znajdować się w magazynie do 8 miesięcy włącznie. Używając operatorów arytmetycznych >=, definiujemy regułę większą lub równą. Po wpisaniu tego warunku funkcja zwróci jedną z dwóch wartości: „PRAWDA” lub „FAŁSZ”. Jeśli formuła spełnia kryterium, to wartość po przeszacowaniu jest zapisywana do komórki (no, lub przekazywana jako argument do innej funkcji, wszystko zależy od parametrów ustawionych przez użytkownika), podzielona przez dwa (w tym celu podzieliliśmy cena w momencie odbioru w magazynie przez dwójkę) . Jeśli po tym okaże się, że produkt był na magazynie krócej niż 8 miesięcy, zwracana jest ta sama wartość, która jest zawarta w komórce.

Teraz utrudnijmy zadanie. Stosujemy warunek: skala rabatów musi być progresywna. Mówiąc najprościej, jeśli towar leży dłużej niż 5 miesięcy, ale krócej niż 8, cenę należy podzielić przez półtora raza. Jeśli więcej niż 8, dwa. Aby ta formuła była zgodna z wartością, musi wyglądać następująco. Spójrz na zrzut ekranu na pasku formuły, aby go zobaczyć.

Ważne! Jako argumenty dopuszczalne jest użycie nie tylko wartości liczbowych, ale również tekstowych. Dlatego dopuszczalne jest ustalanie kryteriów w najróżniejszym porządku. Na przykład, aby zrobić zniżkę na towary otrzymane w styczniu i nie robić tego, jeśli przybyły w kwietniu.

Zadanie 2. Zastosujmy to kryterium do produktu, który jest w magazynie. Załóżmy, że jeśli po dokonaniu powyższej przeceny jego wartość spadła poniżej 300 rubli lub jeśli nie była sprzedawana przez ponad 10 miesięcy, po prostu zostaje wycofana ze sprzedaży. Wzór jest następujący.

Przeanalizujmy to. Użyliśmy funkcji jako kryterium OR. Konieczne jest zapewnienie takiego widelca. Jeżeli komórka D2 zawiera liczbę 10, to w odpowiednim wierszu kolumny E zostanie automatycznie wyświetlona wartość „odpisana”. To samo dotyczy drugiego warunku. Jeśli żaden z nich nie zostanie spełniony, po prostu zwracana jest pusta komórka.

Zadanie 3. Załóżmy, że mamy próbkę uczniów próbujących dostać się do liceum. Aby to zrobić, muszą zdać egzaminy z kilku przedmiotów, pokazanych na poniższym zrzucie ekranu. Aby zostać uznanym za kwalifikującego się do przyjęcia do tej instytucji edukacyjnej, muszą zdobyć łącznie 12 punktów. Jednocześnie ważnym warunkiem jest, aby wynik z matematyki wynosił nie mniej niż 4 punkty. Zadaniem jest zautomatyzowanie przetwarzania tych danych, a także sporządzenie raportu, na który studenci weszli, a którzy nie. Aby to zrobić, zrobimy taki stół.

Naszym zadaniem jest więc zmuszenie programu do obliczenia, ile będzie w sumie punktów, spojrzenia na wynik zaliczenia i dokonania porównania. Po tych operacjach funkcja musi umieścić wynik w komórce, w której się mieści. Możliwe są dwie opcje: „zaakceptowano” lub „nie”. Aby zrealizować to zadanie, wprowadź podobną formułę (wystarczy wpisać swoje wartości): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

Z funkcją logiczną И możemy sprawdzić, czy spełnione są jednocześnie dwa warunki. W tym przypadku użyliśmy funkcji SUMA aby obliczyć całkowity wynik. Jako pierwszy warunek (w pierwszym argumencie funkcji AND) podaliśmy formułę B3>=4. Kolumna ta zawiera wynik z matematyki, który nie powinien być niższy niż 4 punkty.

Widzimy szerokie zastosowanie funkcji IF podczas pracy z arkuszami kalkulacyjnymi. Dlatego jest to najpopularniejsza funkcja logiczna, którą musisz najpierw poznać.

Zdecydowanie zaleca się ćwiczenie na tablicy testowej przed użyciem tych umiejętności w rzeczywistej pracy. Pomoże to zaoszczędzić dużo czasu.

Zadanie 4. Stoimy przed zadaniem określenia całkowitego kosztu towaru po przecenie. Warunek – koszt produktu musi być wyższy lub średni. Jeżeli warunek ten nie jest spełniony, towar należy spisać. W tym przykładzie zobaczymy, jak działa kilka funkcji arytmetycznych i statystycznych.

Użyjmy tabeli, którą już narysowaliśmy. Aby rozwiązać ten problem, konieczne jest ustalenie reguły jako warunku, że komórka D2 musi być mniejsza niż średnia arytmetyczna całego asortymentu towarów. Jeśli reguła zostanie potwierdzona, to w komórce, w której zapisana jest ta formuła, ustawiona jest wartość „odpisana”. Jeśli kryterium nie jest spełnione, ustawiana jest pusta wartość. Aby zwrócić średnią arytmetyczną, istnieje funkcja ŚREDNI.

Zadanie 5. Załóżmy, że musimy obliczyć średnią sprzedaż różnych produktów w różnych sklepach tej samej marki. Zróbmy taki stół.

Naszym zadaniem jest wyznaczenie średniej dla wszystkich wartości, która pasuje do pewnych cech. Aby to zrobić, używamy specjalnej funkcji, której nie było na powyższej liście. Pozwala połączyć dwie funkcje ŚREDNI и JEŚLI. I zadzwoniła BEZ SERCA. Zawiera trzy argumenty:

1. Zasięg do sprawdzenia.
2. Warunek do sprawdzenia.
3. Uśrednianie zakresu.

W rezultacie otrzymujemy następującą formułę (na zrzucie ekranu).

Widzimy, że zakres zastosowań funkcji logicznych jest po prostu ogromny. A ich lista jest w rzeczywistości znacznie większa niż opisana powyżej. Wymieniliśmy tylko najpopularniejsze z nich, ale opisaliśmy też przykład innej funkcji, która jest połączeniem funkcji statystycznej i logicznej. Istnieją również inne podobne hybrydy, które zasługują na osobną uwagę.