Логические функции в excel с примерами их использования

Функция ИСТИНА

Он не принимает аргументов и просто возвращает логическое значение «ИСТИНА».

Синтаксис: =ИСТИНА()

Функция ЛОЖЬ

Аналогична функции ИСТИНА, за исключением того, что она возвращает значение, противоположное ЛОЖЬ.

Синтаксис: = ЛОЖЬ()

Функция ЕСЛИОШИБКА

Он предназначен для проверки возвращаемого значения выражения ошибки. При обнаружении ошибки возвращает значение второго аргумента, в противном случае — первого.

Функция принимает 2 аргумента, все они обязательны.

Синтаксис: =ЕСЛИ.ОШИБКА(значение;значение_если_ошибка)

Пример использования функции:

В приведенном выше примере видно, что выражение в первом аргументе возвращает ошибку деления на ноль, но поскольку оно вложено в нашу функцию, ошибка перехватывается и заменяется вторым аргументом, а именно строкой «Вы не можете делим на ноль», которую мы ввели сами. Вместо этой строки могли быть и другие функции, все зависит от поставленной перед ней задачи.

 

 

Формула ЕСЛИ в Excel – примеры нескольких условий

Очень часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае вы также можете использовать функцию ЕСЛИ, но теперь вы должны вкладывать их друг в друга, указывая поочередно все условия. Рассмотрим следующий пример.

Несколько менеджеров по продажам должны накапливать бонусы в зависимости от выполнения плана продаж. Система мотивации выглядит следующим образом. Если план выполнен менее чем на 90%, то премия не начисляется, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20%, а при перевыполнении плана , то 30%. Как видите, есть 4 варианта. Чтобы указать их в формуле, требуется следующая логическая структура. Если выполняется первое условие, то происходит первый вариант, иначе, если выполняется второе условие, то происходит второй вариант, иначе, если.. и так далее. Количество условий может быть довольно большим. В конце формулы указывается последний вариант, для которых не выполняется ни одно из перечисленных выше условий (например, третье поле в обычной формуле ЕСЛИ). В результате формула имеет следующий вид.

 

 

Комбинация функций ЕСЛИ работает таким образом, что при выполнении любого заданного условия следующие уже не проверяются. Поэтому важно перечислить их в правильном порядке. Если бы мы начали проверку с B2

При вводе формулы легко запутаться, поэтому рекомендуется смотреть на всплывающие подсказки.

 

 

В конце нужно закрыть все скобки, иначе Excel выдаст ошибку

 

 

Основные операции

Создание формулы в Excel не представляет особых трудностей, если пользователь полностью освоил некоторые азы работы с табличным редактором. Лучше всего начать изучение этого офисного приложения с тех вычислительных примеров, которые наиболее просты и распространены. Это позволит понять суть и важные правила работы с табличным редактором.

Освоив простейшие азы, можно приступать к созданию логических формул.

Ведение простейших подсчётов

Для начала вам нужно запустить приложение Office. Далее важно решить, что должно рассчитываться автоматически. В большинстве случаев работа начинается, когда уже имеется числовая информация, на основании которой необходимо выполнить несколько операций с калькулятором. Теперь эту вычислительную машину нужно отложить в сторону и полностью полагаться на электронные таблицы.

Любое числовое выражение записывается со знаком «=». Без его установки Excel будет воспринимать введенный запрос как ввод текста, отображать его, но не вычислять.

Часто необходимо не только произвести расчеты с использованием данных таблицы, но и провести анализ, сопровождаемый различными условиями. Опять же, делать все это вручную самостоятельно неразумно, когда есть уникальные возможности Excel, способные удовлетворить любые требования пользователя.

В категории «Логика» наиболее распространена функция ЕСЛИ. Эта функция рассматривает предложенные варианты, а затем определяет, выполняется ли это условие. Результат функции ЕСЛИ зависит от того, что пользователь хочет увидеть в конце. А чтобы результат отображался корректно, нужно знать, как задать условие в Excel.

Функция ЕСЛИ имеет три аргумента:

• Логическое (базовое) выражение (требование, которое пользователь хочет проверить в таблице);
• значение для отображения, если условие полностью выполнено;
• значение, если указанное требование не выполняется.

Так что можно попробовать составить простейшую расчетную таблицу на примере, где основной логической функцией является ЕСЛИ.

 

 

В частности, в ячейку А1 введите числовой показатель «5», а в ячейку А2 показатель «8». Если пользователю необходимо сравнить два показателя и выдать автоматическую сводку выполненных действий, составляется выражение, которое, разумеется, начинается со знака «равно»: =ЕСЛИ(А1>А2; «Больше», «Меньше»). После ввода этого ввода в любую свободную ячейку, нажатия Enter, в ней сразу отобразится ответ. Если пользователь заменит значения в A1 или A2, ответ также будет автоматически перестроен.

В тех случаях, когда важно выявить несоответствие между двумя показателями, введенными в соседние столбцы, необходимо сначала понять, как записать условие неравноправия в Excel, а затем создать запрос с помощью логических функций. Знак «неравно» в Excel обозначается двумя операторами: «». Соответственно, писать нужно так: = ЕСЛИ (A1A2; «Значения разные»; «Значения равны»).

Логический набор

Количество логических функций зависит от версии программы. В приложении 2007 года их было 7, позже добавилось еще несколько. Список доступных логических операций можно увидеть следующим образом:

• перейти на вкладку «Формулы» на главной панели;
• нажмите на иконку fx с надписью «Вставить формулу>»;
• в появившемся окне выберите категорию «Логические>»;
• внизу откроется список доступных операторов.

   

  

   

У большинства есть аргументы, определяющие условия использования. Формат записи следующий: «=оператор(аргумент1;аргумент2…)». Логическая запись включает в себя знаки сравнения.

ИСКИЛИ

Он появился в версии программы 2013. Реализует операцию XOR. Написание похоже на «И»: =ИСКЛЮЧИТЬ(логический_вопрос1;логический_вопрос2;…) и может иметь до 255 аргументов.

Если всего 2 варианта, то общий результат будет ИСТИНА, если есть аргумент с таким же ответом. В этом произведении «ИСКИЛ» совпадает с «ИЛИ». Если оба решения ИСТИНА или ЛОЖЬ, результат будет ЛОЖЬ. Для пояснения приведена следующая таблица:

Исходные данные	Результат	Оценки
=ИСКЛЮЧИТЬ(3>0, 4	ОПРЕДЕЛЕННЫЙ	Результат ИСТИНА, потому что одно из значений ИСТИНА.
=ИСКЛЮЧИТЬ(3	ПОДДЕЛКА	ЛОЖЬ, потому что есть 2 ЛОЖНЫХ ответа .
=ИСКЛЮЧИТЬ(3>0, 4>1)	ПОДДЕЛКА	НЕВЕРНО, потому что есть 2 ответа ИСТИНА

ЕСЛИМН (УСЛОВИЯ) и ПЕРЕКЛЮЧ

«ЕСЛИ» и «ПЕРЕКЛЮЧЕНИЕ» появились в Excel 2016 и 2019 соответственно. Предназначен для облегчения проработки формул, так как они уменьшают количество вложений.

Способы вставки таблицы из Microsoft Excel в Word

 

«IFS» ранее называлось «TERMS». Его введение связано с попыткой облегчить работу за счет вложения нескольких «ЕСЛИ». Не нужно несколько раз набирать «YES» и открывать многочисленные скобки. Синтаксис: «=IFS(условие1, значение1, условие2, значение2, условие3, значение3…)». Вы можете создать до 127 условий.

«SWITCH» имеет следующую структуру: «=SWITCH(значение для переключения; значение для совпадения1…[2-126]; возвращаемое значение, если совпадение1…[2-126]; возвращаемое значение, если совпадения нет)».

Первый аргумент указывает на расположение проверяемого выражения, остальные задают для ячейки первое совпавшее значение.

Оформление и примеры использования

Алгоритм написания логических формул в Excel следующий:

1. Вам нужно выбрать пустую ячейку, в которой будет записана формула и отобразится результат действия.

    

   

    

   Вы также можете ввести в строке формул, после выбора ячейки.

    

   

    

2. Перед формулами ставится знак «=». Положить его.
3. Введите имя оператора.
4. После этого вводятся аргументы, если они есть. Запись начинается со знака «открытая скобка “(“».
5. Аргументы вводятся последовательно через знак «;». Также, если после ввода имени функции нажать клавиши Ctrl+A, то откроется меню аргументов и их можно ввести здесь.

    

   

    

6. В конце поставить символ «закрыть скобку»). Проверить правильность написания можно в строке формул.

    

   

    

7. Когда вы закончите, нажмите кнопку ENTER. Результат появится в ячейке.

Работа с ПЕРЕКЛЮЧ

Сравнивает значение, указанное в ячейке или формуле, со списком данных и записывает первое значение, соответствующее ячейке. Если совпадений нет и значение по умолчанию не установлено, оператор вернет ошибку «#Н/Д». Функция аналогична IIFS, но в отличие от нее условие задается точно, без сравнительных знаков.

Работа оператора проиллюстрирована на рисунке.

 

 

Здесь вместо цифр 1, 2, 7 нужно прописать прописью соответствующие им дни недели. Если есть другие цифры, будет возвращено значение по умолчанию «Не совпадает)».

Использование ЕСЛИОШИБКА

Оператор используется для поиска ошибки в таблице. После обнаружения функция не записывает в ячейку какие-либо ошибки, а возвращает заданный ответ, который может быть текстом, пустой строкой: =ЕСЛИ.ОШИБКА(Что_проверить, Что_показать_вместо_ошибки).

Например, вам нужно разделить значения в столбце A на значения в столбце B. Если по ошибке в строках стоят 0, получится деление на 0.

 

 

Использование оператора «=IF.ERROR(A2/B2;»»)» скрывает ошибки.

 

 

Здесь сравнивается выражение A2/B2. При обнаружении ошибки ячейка заполняется пустой строкой, обозначаемой пробелом в кавычках ““.

ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая не могла самостоятельно обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае ошибки необходимо было использовать оператор IF: «IF(ISERROR(A2/B2);»»;A2/B2)».

И/ИЛИ

Простые операторы редко используются без привязки к другим функциям.

На рисунке показано, как работает функция И.

 

 

Пример использования: «=Y(A1>B1; A225)». Здесь есть два условия:

1. Значение в ячейке A1 должно быть больше числа в B1.
2. Число в A2 не должно быть равно 25.

Оба ИСТИННЫ при выполнении.

 

 

Если одна из задач нарушена, она FALSE. В этом случае число в A1 меньше, чем в B1.

 

 

Алгоритм работы оператора ИЛИ показан ниже.

 

 

Пусть даны 3 выражения: A1>B1; А2>В2; А3>В3. К ним требуется применить действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:

 

 

Здесь окончательный результат ИСТИНА, так как верно одно из трех выражений: A3>B3. На изображении ниже функция вернула ЛОЖНЫЙ ответ, потому что на все вопросы были получены одинаковые ответы.

 

 

Основные операторы

Есть и менее распространенные логические функции.

Каждый из вышеперечисленных операторов, кроме первых двух, имеет аргументы. Аргументами могут быть конкретные числа или текст, либо ссылки, указывающие на адрес ячеек с данными.

Функции ЕОШИБКА и ЕПУСТО

Функция ЕОШИБКА проверяет, содержит ли конкретная ячейка или диапазон ячеек недопустимые значения. Значения ошибок означают следующее:

• #Н/Д;
• #СТОИМОСТЬ;
• #КОЛИЧЕСТВО!;
• #ДЕЛ/0!;
• #СВЯЗЬ!;
• #ИМЯ?;
• #ПУСТОЙ!

В зависимости от того, является аргумент ошибкой или нет, оператор сообщает значение TRUE или FALSE. Синтаксис этой функции: = ЕОШИБКА(значение) . Аргумент — это просто ссылка на ячейку или массив ячеек.

 

 

Оператор IBLANK проверяет, является ли ячейка пустой или содержит значения. Если ячейка пуста, функция сообщает TRUE, если ячейка содержит данные, FALSE. Синтаксис этого оператора: =NULL(значение) . Как и в предыдущем случае, аргументом является ссылка на ячейку или массив.

 

 

Пример применения функций

Теперь давайте посмотрим на применение некоторых из вышеперечисленных функций на конкретном примере.

У нас есть список сотрудников компании с их зарплатами. Но, кроме того, все сотрудники имеют право на премию. Обычная премия 700 руб. Но пенсионеры и женщины имеют право на более высокую премию в размере 1000 рублей. Исключение составляют сотрудники, которые по разным причинам отработали менее 18 дней в данном месяце. В любом случае им положена только обычная премия в 700 рублей.

 

 

Попробуем составить формулу. Итак, имеем два условия, при выполнении которых устанавливается премия в размере 1000 рублей: это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом к пенсионерам будем относить всех, кто родился до 1957 года. В нашем случае для первой строки таблицы формула примет следующий вид: =ЕСЛИ(ИЛИ(C4

 

 

Чтобы скопировать эту функцию в ячейки столбца таблицы, где указано значение надбавки, наводим курсор в правый нижний угол ячейки, в которой уже есть формула. Появится маркер заполнения. Просто перетащите его в конец таблицы.

 

 

Таким образом, мы получаем таблицу с информацией о размере премии для каждого сотрудника компании в отдельности.

 

 

Как видите, логические функции — очень удобный инструмент для выполнения вычислений в Microsoft Excel. Используя сложные функции, вы можете одновременно задавать несколько условий и получать результат в зависимости от того, выполняются эти условия или нет. Использование таких формул позволяет автоматизировать ряд действий, экономя время пользователя.

Практический пример использования логических функций

В следующем примере мы попробуем частично использовать описанные выше функции для решения задачи, близкой к реальной ситуации с расчетом премии, зависящей от определенных условий.

В качестве исходных данных: таблица с информацией о сотрудниках с указанием их пола и возраста.

 

 

Нам нужно рассчитать премию. Основные условия, от которых зависит размер премии:

• размер обычной премии, которую получат все без исключения сотрудники, составляет 3 000 рублей.;
• работницы имеют право на большую премию — 7 000 руб.;
• молодые работники (до 1984 г.р.) имеют право на большую премию — 7000 руб.;

Выполним необходимые вычисления, используя логические функции.

1. Встаем в первую ячейку столбца, в котором хотим рассчитать размер надбавок и нажимаем кнопку «Вставить функцию» (слева от условий формул).

    

   

    

2. В открывшемся Мастере функций выберите категорию «Логические», затем в предложенном списке операторов нажмите на строку «ЕСЛИ» и нажмите ОК.

    

   

    

3. Теперь нам нужно установить аргументы функции. Поскольку у нас есть не одно, а два условия для получения повышенной премии, и необходимо, чтобы хотя бы одно из них было выполнено, для установления логического выражения воспользуемся функцией ИЛИ. Находясь в поле ввода значения аргумента «Логическое_выражение», кликните в основной рабочей области рабочей книги на маленькую стрелку вниз, расположенную в верхней левой части окна программы, где обычно отображается направление логики ячейки. В открывшемся списке функций выберите оператор ИЛИ, если он представлен в списке (либо можно нажать на пункт «Другие функции» и выбрать его в новом окне Мастера функций, как мы делали изначально для выбора оператора ЕСЛИ оператор).

    

   

    

4. Перейдем к окну аргументов функции ИЛИ. Здесь выставляем свои условия для получения бонуса 7000 рублей.:
   • год рождения после 1984;
   • женский пол;

      

     

      

5. Теперь давайте посмотрим на строку формул. Щелкаем по нему над названием исходного оператора ЕСЛИ, чтобы перейти к аргументам этой функции.

    

   

    

6. Заполните аргументы функции и нажмите OK:
   • в значение «Истина» пишем число 7000;
   • в значении «False» указываем число 3000;

      

     

      

7. Результат работы логических операторов отобразится в первой ячейке столбца, который мы выбрали. Как мы видим, окончательный вид формулы выглядит так:
   =ЕСЛИ(ИЛИ(C2>1984,D2=»женщина»),7000,3000).
   Кстати, вместо использования Мастера функций вы можете вручную набрать и ввести эту формулу в нужную ячейку.

    

   

    

8. Для расчета премии для всех сотрудников воспользуемся маркером заполнения. Переместите курсор в правый нижний угол ячейки с формулой. После того, как курсор примет вид черного креста (это маркер заливки), зажмите левую кнопку мыши и перетащите выделение в последнюю ячейку в столбце.

    

   

    

9. Все готово. Благодаря логическим операторам мы получили полные данные для бонусной колонки.

    

   

    

Логическая функция ИСКЛИЛИ в Excel

В Excel 2013 Microsoft представила функцию EXCLUSIVE, которая представляет собой логическую функцию XOR. Для тех, кто не знаком с концепцией XOR, сначала может быть немного сложно понять значение булевой функции, но мы надеемся, что приведенное ниже объяснение, проиллюстрированное примерами формул, поможет прояснить ситуацию.

Синтаксис логической функции EXCLUSIVE идентичен синтаксису OR:

=EXCLU(логическое значение1, [логическое значение2];…)

В самой простой версии формулы EXCLUSIVE, которая содержит только 2 логических оператора, логическая функция EXCLUSIVE Excel вернет значение TRUE, если какой-либо из аргументов оценивается как TRUE. Если оба аргумента имеют значение TRUE или оба имеют значение FALSE, EXCLUSIVE возвращает значение FALSE. Рассмотрим примеры формул:

Формула	Результат	Описание
=ИСКЛЮЧИТЬ(1>0, 2	ОПРЕДЕЛЕННЫЙ	Логическая функция возвращает значение ИСТИНА, поскольку первый аргумент имеет значение ИСТИНА, а второй аргумент — ЛОЖЬ.
=ИСКЛЮЧИТЬ(1	ПОДДЕЛКА	Логическая функция возвращает FALSE, поскольку оба аргумента имеют значение FALSE.
=ИСКЛЮЧИТЬ(1>0, 2>1)	ПОДДЕЛКА	Логическая функция возвращает FALSE, поскольку оба аргумента имеют значение TRUE.

Когда добавляются дополнительные логические операторы, функция ИСКЛЮЧЕНО в Excel работает следующим образом:

• ИСТИНА, если нечетное количество аргументов оценивается как ИСТИНА;
• FALSE, если общее количество утверждений TRUE четно или если все утверждения FALSE.

Следующее изображение наглядно иллюстрирует это:

 

 

Заключение

Логические операторы используются практически во всех сложных формулах, в которых значение ячейки зависит от выполнения одного или нескольких условий, и позволяют строить гибкие структуры, объединяя простые функции и вычисления в одной ячейке. Это позволяет значительно сократить время обработки данных и повысить эффективность работы, так как исключает большое количество промежуточных шагов при расчетах и ​​расчетах.