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

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

Он не принимает аргументов и возвращает только логическое значение TRUE».

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

Функция ЛОЖЬ

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

Синтаксис: = FALSE()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ИСКИЛИ

Появился в выпуске программы 2013 г. Реализует операцию «Исключительный O». Написание такое же, как и «И»: = EXCURED (логический_запрос1; логический_запрос2;…) и может иметь до 255 аргументов.

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

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

«ISLIMN» и «SWITCH» появились в Excel 2016 и 2019 соответственно. Предназначены для облегчения написания формул, поскольку они уменьшают сумму инвестиций.

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

«ISLIMN» раньше назывался «УСЛОВИЯ». Его введение связано с попыткой облегчить работу при вложении большего количества «IF». Нет необходимости писать «ЕСЛИ» несколько раз и открывать несколько скобок. Синтаксис: «= ЕСЛИ (условие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. Результат появится в ячейке.

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

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

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

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

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

Оператор используется для поиска ошибки в таблице. Обнаружив его, функция не записывает ни одной ошибки в ячейку, но возвращает указанный ответ, который может быть текстом, пустой строкой: = IF ERROR (Qual_check; What_ to_put_ вместо_ errors).

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

Использование оператора «= ЕСЛИОШИБКА (A2 / B2;» «)» скрывает ошибки.

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

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

И/ИЛИ

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

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

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

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

Когда оба будут выполнены, результат будет ИСТИННЫМ.

Если какая-либо из задач нарушена, результат — ЛОЖЬ. В этом случае число в A1 меньше, чем в B1.

Ниже приведен алгоритм работы оператора ИЛИ.

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

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

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

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

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

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

Функция ISERROR проверяет, содержит ли определенная ячейка или диапазон ячеек неправильные значения. Под неправильными значениями понимаются следующие:

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

В зависимости от того, является ли аргумент недопустимым, оператор возвращает ИСТИНА или ЛОЖЬ. Синтаксис этой функции: = ЕСТЬ ОШИБКА (значение). Аргумент — это ссылка только на ячейку или массив ячеек.

Оператор ISBLANK проверяет ячейку, чтобы убедиться, что она пуста или содержит значения. Если ячейка пуста, функция возвращает ИСТИНА, если ячейка содержит данные, ЛОЖЬ. Синтаксис этого оператора: = ISBLANK (значение). Как и в предыдущем случае, аргумент — это ссылка на ячейку или массив.

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Мы находимся в первой ячейке столбца, в котором хотим рассчитать вознаграждение, и нажимаем кнопку «Вставить функцию» (слева от временной шкалы формулы).
2. В открывшемся мастере выберите категорию «Логические», затем в списке предложенных операторов щелкните строку «ЕСЛИ» и нажмите ОК.
3. Теперь нам нужно установить аргументы для функции. Поскольку у нас есть не одно, а два условия для получения повышенной премии, и необходимо, чтобы хотя бы одно из них было выполнено, для задания логического выражения воспользуемся функцией ИЛИ. Находясь в поле для ввода значения аргумента «Log_expression», щелкните в основной рабочей области книги на маленькую стрелку вниз, расположенную в верхней левой части окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выберите оператор ИЛИ, если он присутствует в списке (или вы можете нажать на пункт «Другие функции» и выбрать его в новом окне мастера, как мы делали изначально, чтобы выбрать оператор ЕСЛИ).
4. Перейдем в окно аргументов функции ИЛИ. Здесь мы устанавливаем свои условия для получения приза 7000 руб.:
   • родился после 1984 г;
   • женский пол;
5. Теперь обратим внимание на строку формул. Щелкните в нем имя исходного оператора IF, чтобы перейти к аргументам этой функции.
6. Заполните аргументы функции и нажмите ОК:
   • в значении «Истина» пишем число 7000;
   • в значении «Ложь» указываем число 3000;
7. Результат работы логических операторов отобразится в первой ячейке выбранной нами колонки. Как видим, окончательный вид формулы выглядит так:
   
   = SE (ИЛИ (C2> 1984; D2 = «женский»); 7000; 3000).
   
   Кстати, вместо использования функции мастера можно было вручную составить и записать эту формулу в нужную ячейку.
8. Для расчета бонуса для всех сотрудников мы воспользуемся маркером заполнения. Наведите указатель мыши на правый нижний угол ячейки с формулой. После того, как курсор изменится на черный крест (это индикатор заполнения), удерживая левую кнопку мыши, перетащите выделение в последнюю ячейку столбца.
9. Все готово. Благодаря логическим операторам мы получили скомпилированные данные для столбца премиум-класса.

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

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

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

= ИСКЛЮЧЕНИЕ (логическое1; [логическое2];…)

В простейшей версии формулы ISKLI, содержащей только 2 логических оператора, логическая функция Excel ISKLI вернет ИСТИНА, если один из аргументов имеет значение ИСТИНА. Если оба аргумента — ИСТИНА или оба — ЛОЖЬ, EXCEL возвращает ЛОЖЬ. Давайте посмотрим на примеры формул:

При добавлении нескольких логических операторов функция EXCEL в Excel работает следующим образом:

• ИСТИНА, если нечетное количество аргументов возвращает ИСТИНА;
• ЛОЖЬ, если общее количество операторов ИСТИНА четное, или если все операторы ЛОЖЬ.

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

Заключение

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