Возможность проведения логических проверок в ячейках является мощным инструментом. Вы найдете бесконечное количество применений для ЕСЛИ() в ваших повседневных документах. Проблема заключается в том, что часто необходимые проверки выходят за рамки возможностей простой функции ЕСЛИ() . Это происходит, когда необходимо сделать проверку на два и более условия до принятия результата.
Для таких случаев в Excel предусмотрено несколько вариантов: использование ЕСЛИ() внутри другого ЕСЛИ() , функции И() и ИЛИ() . Далее мы познакомимся с этими способами.
Использование ЕСЛИ() внутри другой функции ЕСЛИ()
Давайте рассмотрим вариант на основе изученной ранее функции =ЕСЛИ(А1>1000;"много"; "мало") . Что если вам необходимо вывести другую строку, когда число в А1 является, например, большим, чем 10.000? Другими словами, если выражение А1>1000 верно, вы захотите запустить другую проверку и посмотреть, верно ли, что А1>10000. Такой вариант вы можете создать, применив вторую функцию ЕСЛИ() внутри первой в качестве аргумента значение _если_истина: =ЕСЛИ(А1>1000;ЕСЛИ(А1>10000;"очень много"; "много");"мало") .
Если А1>1000 является истинным, запускается другая функция ЕСЛИ() , возвращающая значение «очень много», когда А1>10000. Если же при этом А1 меньше или равно 10000, возвращается значение «много». Если же при самой первой проверке число А1 будет меньше 1000, выведется значение «мало».
Обратите внимание, что с таким же успехом вы можете запустить вторую проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции еслио). Вот небольшой пример, возвращающий значение «очень мало», когда число в А1 меньше 100: =ЕСЛИ(А1>1000;"много";ЕСЛИ(А1<100;"очень мало"; "мало")) .
Расчет бонуса с продаж
Хорошим примером использования одной проверки внутри другой проверки является расчет бонуса с продаж персоналу. который работает в Клуб — отель Гелиопарк Талассо, Звенигород . В данном случае, если значение равно X, вы хотите получить один результат, если У — другой, если Z
— третий. Например, в случае вычисления бонуса за успешные продажи возможны три варианта:
- Продавец не достиг планового значения, бонус равен 0.
- Продавец превысил плановое значение менее чем на 10%, бонус равен 1 000 рублей.
- Продавец превысил плановое значение более чем на 10%, бонус равен 10 000 рублей.
Вот формула для расчета такого примера: =ЕСЛИ(Е3>0;ЕСЛИ(Е3>0.1;10000;1000);0) . Если значение в Е3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он 10%, и в зависимости от этого выдается 1 000 или 10 000. Рис. 4.17 показывает пример работы формулы.
Функция И()
Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или оба условия), премия не выплачивается. В булевой логике это называется логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.
В Excel выражения логического И обрабатываются с помощью функции И() : И(логическое_значение1;логическое_значение2;…). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо.
Еще раз отметим работу функции:
- Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.
- Если один или более аргументов возвращают ЛОЖЬ (или 0), И() возвращает ЛОЖЬ.
Чаще всего И() применяется внутри функции ЕСЛИ() . В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение если истина. Если одно или более из выражений в И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.
Вот небольшой пример: =ЕСЛИ(И(С2>0;В2>0);1000;"нет бонуса") . Если значение в В2 будет больше нуля и значение в С2 будет больше нуля, формула вернет 1000, в противном случае выведется строка «нет бонуса».
Разделение значений по категориям
Полезным применением функции и () является разделение по категориям в зависимости от значения. Например, у вас имеется таблица с результатами какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34,35-49, 50-64,65 и более. Предполагая, что возраст респондента находится в ячейке В9, следующие аргументы функции и () проводят логическую проверку на принадлежность возраста диапазону: =И(В9>=18;В9
Если ответ человека находится в ячейке С9, следующая формула выведет результат голосования человека, если срабатывает проверка на соответствие возрастной группе 18-34: =ЕСЛИ(И(В9>=18;В9
- 35-49: =ЕСЛИ(И(В9>=35;В9
- 50-64: =ЕСЛИ(И(В9>=50;В9
- 65+: =ЕСЛИ(В9>=65;С9;"")
Функция ИЛИ()
Существуют ситуации, когда вам необходимо решение, только когда одно из условий принимает положительное значение (ИСТИНА). Например, вы можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В булевой логике это называется логическим ИЛИ .
Такие условия проверяются в Excel с помощью функции ИЛИ() : ИЛИ(логическое_значение1; логическое_значение2;...). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо. Результат работы ИЛИ() зависит от следующих условий:
- Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.
- Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.
Так же как и И() , чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ() . В таком случае, когда один из аргументов внутри ИЛИ() вернет ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина. Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь . Вот небольшой пример: = ЕСЛИ(ИЛИ(С2>0;В2>0);1000;"нет бонуса") .
В случае когда в одной из ячеек (С2 или В2) будет положительное число, функция вернет 1000. Только когда оба значения будут отрицательны (или равны нулю), функция вернет строку "нет бонуса".
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Для этого нужно выполнить следующие действия:
- Кликните на иконку вставки формул (Fx).
- В появившемся меню выберите пункт «ЕСЛИ».
- В результате этого вы увидите окно, в котором нужно указать аргументы функции:
- логическое выражение;
- значение, если истина;
- значение, если ложь.
- После ввода значений в эти поля, правее их будет отображаться предварительный результат.
- Для вставки нажмите на кнопку «OK».
Примеры
Рассмотрим несколько различных логических выражений, чтобы вы поняли, как пользоваться этой функцией.
- Сделайте активной ячейку «А1».
- Повторите описанные выше действия по вызову окна вставки «Аргументов».
- Введите в поле условие следующий код.
Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится.
- В поле для истины вводим следующее.
Именно этот текст будет выводиться в случае выполнения этого условия.
- В поле «если ложь» вводим следующее.
Данный текст мы увидим в случае ложного выполнения условия.
- Для вставки нашей формулы нажимаем на кнопку «OK».
- В результате этого мы увидим следующее (поскольку ячейка пустая, то и сообщение соответствующее).
- Введите любой текст в ячейку C.
- После нажатия на кнопку Enter вы увидите следующий результат.
Как видите, редактор Эксель выдал сообщение, которое мы указывали в случае ложного результата условия.
Операторы сравнения
Ниже вы видите список операторов сравнения:
Арифметические операторы
А это таблица арифметических операторов:
Более подробно об арифметических операторах и их приоритете в Экселе можно почитать в онлайн справке сайта Microsoft.
Несколько условий
Если хотите, то можете использовать большое количество логических выражений. Всё работает точно так же. Рассмотрим пример более сложного условия.
Составлять формулу будем через панель инструментов. Это намного удобнее, чем всё писать в одну строку, поскольку очень легко запутаться. Особенно в самом начале, когда вы только учитесь.
Для этого необходимо выполнить следующие действия.
- Перейдите на вкладку «Формулы». Кликните на кнопку «Вставить функцию».
- В результате этого всё содержимое ячейки автоматически подставится в «конструктор».
- Удалите всё, что находится в поле «Значение_если_ложь».
- Теперь в этой строке будет написано новое логическое выражение. То есть у нас будет новое вложенное условие. Для начала введите условие «Если значение ячейки С1 больше 1000». Для этого вводим следующий код. Обязательно в конце поставьте символ точки с запятой.
Очень внимательно следите за текущей раскладкой клавиатуры. Многие ошибаются и вводят русскую букву С вместо английской C. Визуально вы разницу не увидите, но для редактора это очень важно. В таком случае ничего работать не будет.
- Теперь добавим сообщение, которое будет выводиться, если число в ячейке C1 больше 1000.
Все текстовые значения обязательно вводим в кавычках.
- Теперь точно таким же образом вводим значение при невыполнении данного условия.
- В конце нажмите на кнопку «OK».
- В результате этого, мы видим сообщение о том, что введенное число больше 1000.
- Удалите содержимое ячейки C. Результатом станет следующее.
Мы проверили все три возможных результата. Всё прекрасно работает.
Копирование функции в таблицах
Иногда бывает так, что введенное логическое выражение необходимо продублировать на несколько строк. В некоторых случаях дублировать приходится очень много. Такая автоматизация намного удобнее, чем ручная проверка.
Рассмотрим пример копирования на таблице премий для сотрудников на праздники. Для этого нужно сделать следующие шаги.
- Создайте соответствующую таблицу.
- Кликните на пустую ячейку в первой строке и выберите «Fx» в поле ввода.
- В появившемся окне выберите функцию «ЕСЛИ» и нажмите на кнопку «OK».
- В первом поле введите следующее условие.
Таким образом мы проверяем, является ли данный сотрудник мужчиной.
- В случае истины вводим какое-нибудь число. Например, 3000. В случае ложного выполнения условия вводим 0. Это значит, что девушкам премию давать не нужно. Для вставки нажмите на кнопку «OK».
- Далее наведите курсор на правый нижний угол ячейки. После того как курсор изменит свой внешний вид на «черный плюс», не отпуская пальца, потяните его до самой последней строчки.
- Результатом будет следующее.
- Теперь полностью скопируйте всю таблицу (при помощи сочетания клавиш Ctrl +C и Ctrl +V ). Измените заголовок на 8 марта.
- Перейдите на первую ячейку. Кликните на поле ввода формулы.
- Измените букву «М» на «Ж».
- Теперь так же продублируйте функцию до самого низа.
- Результатом будет следующее.
Здесь мы видим, что получилась полная противоположность. Это означает, что всё работает правильно.
Использование дополнительных операторов
Кроме арифметических выражений и сравнений, также можно использовать операторы «И» и «ИЛИ». Рассмотрим их более внимательно, поскольку благодаря им возможности функции «ЕСЛИ» значительно расширяются.
Первым делом создайте таблицу, в которой будет несколько полей, по которым можно будет сравнивать строки. В нашем случае при помощи поля «Статус сотрудника» мы будем проверять, кому нужно выплатить деньги, а кому – нет.
Оператор И
Будем использовать старую функцию, в которой мы проверяли, является ли сотрудник мужчиной. Теперь нужно сделать следующие изменения.
- Добавьте в условие скобку и рядом букву «И».
- Старое условие будет первым, а второе – после точки с запятой.
- Продублируйте эту формулу до самого низа.
- В результате этого мы видим, что те сотрудники, которые не относятся к основному штату, будут лишены премии на праздники. Даже несмотря на то, что они мужчины.
Оператор Или
Того же самого эффекта можно добиться и при помощи оператора «ИЛИ».
Для этого в формулу внесите следующие изменения:
- Поменяйте букву «Ж» на «М».
- Статус сотрудника измените на «Совместителя».
- В поле истины поставьте 0, а для ложного события – 3000. Мы меняем их местами.
- Продублируем формулу до самой последней строки.
- Результат оказался точно таким же. Дело в том, что операторы «И» или «ИЛИ» являются полной противоположностью друг друга. Поэтому очень важно правильно указывать значения в поля для истины и лжи. Не ошибитесь.
- Для проверки можете изменить статус одного сотрудника на «Основной штат».
- Сразу после этого вы увидите, что напротив его фамилии появится число 3000.
Функция СУММЕСЛИ
Наверняка многие из вас делали расчеты в редакторе Excel. Но бывают случаи, когда в таблице нужно посчитать не все строки, а только лишь некоторые, соответствующие определенному условию.
Например, возьмем таблицу, в которой указаны различные товары, их стоимость, количество и статус. Посчитать общую сумму несложно. Но как узнать количество проданных продуктов?
" была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях - вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции - И() и ИЛИ().
Функция И()
Функция И()
Условие 1
И
Условие 2
. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.
Функция ИЛИ()
Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом - Условие 1 ИЛИ Условие 2 . Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.
Примеры использования функции И и ИЛИ
Все вышесказанное сложно к пониманию и относится к разделу Мат. логики и Дискретной математики. Попробую это все изложить на понятном языке. Разберем несколько примеров. Скажу сразу, все примеры будут с использованием функции ЕСЛИ.
Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В - 52, 55, 88, 4, 25. Столбец С - 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.
Переходим в ячейку D1 и с помощью мастера функций вводим следующее
Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);"Все значения равны";"Значения не равны") “
Результат работы:
Пример 2.
Теперь пример на функцию ИЛИ. Те же столбцы, те же значения, условие следующее: с помощью функций ЕСЛИ и ИЛИ необходимо определить строки, в которых есть совпадающие значения А=В или В=С или А=С.
Мастер функций выглядит так:
Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);"Есть равные значения";"Нет равных значений")”
Результат:
Вот и все. Функции не сложны. Применяйте их там, где встречаются условия и произносится И и ИЛИ. Так же, всеми выше перечисленными функциями можно сравнивать текстовые значения.
И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.
Пример 3.
В таблицу занесены процент выполнения заданий, необходимо проценты перевести в пятибалльную систему следующим образом:
1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%
Таблица следующего вида.
Проверка истинности условий и логических сравнений между выражениями, которые являются общими для многих задач. Для создания условных формул можно использовать функции " и ", "," и " Если ".
Например, функция Если использует указанные ниже аргументы.
Формула, использующая функцию если
Лог_выражение: условие, которое нужно проверить.
Значение_если_истина: возвращаемое значение, если условие истинно.
Значение_если_ложь: возвращаемое значение, если условие имеет значение false.
Дополнительные сведения о том, как создавать формулы, можно найти в разделе Создание и удаление формул .
В этой статье
Создание условной формулы, которая приводит к логическому значению (истина или ложь)
Для выполнения этой задачи используйте функции и операторы and, or и Not
Пример
Копирование примера
Важно:
Формулы в группе Зависимости формул нажмите кнопку Показывать формулы .
|
|
Дополнительные сведения о том, как использовать эти функции, можно найти в статьях, функциях и функциях, а не функциях .
Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от истина или ложь
Для выполнения этой задачи используйте функции и операторы Если , и и или , как показано в следующем примере.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
Нажмите клавиши CTRL+C.
В Excel создайте пустую книгу или лист.
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы .
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
|
|
Дополнительные сведения об использовании этих функций можно найти в статьях функция и функция .
Основу любого логического выражения составляют две величины: истина (TRUE) и ложь (FALSE). С помощью логических выражений строятся многочисленные деревья решений. Самое простое – вопрос с ответом «ДА» или «НЕТ». В случае «ДА» выполняется одно действие, в случае «НЕТ» – другое. Для реализации условий существует программный оператор IF (ЕСЛИ).
В программе EXCEL есть функция IF , которая позволяет сделать самые невероятные логические конструкции, вкладывая операторы IF друг в друга. В старых версиях программы вложенность была ограничена 7 уровнями, ЕСЛИ в EXCEL 2010 не имеет ограничений, а в версии 2016 появилась новая функция ЕСЛИМН, учитывающая множественность вложений.
Познакомимся с многоликой функцией IF.
Функция IF
ЕСЛИ – стандартная функция программы EXCEL, которая выполняет проверку конкретного условия . Условие представляет собой вопрос, имеющий два ответа: истина и ложь.
Условие записывается в виде логического выражения, состоящего из правой и левой части, связанного логическими операторами сравнения: больше (>), меньше (<), равно (=), неравно (<>), а также >= и <=. Результат сравнения показывает, выполняется или нет это условие. В случае выполнения условия отрабатывает алгоритм ИСТИНА, в противном случае – ЛОЖЬ.
Синтаксис функции выглядит следующим образом:
ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь).
Функции ЕСЛИ в EXCEL, примеры
Простым примером реализации функции IF может служить необходимость присвоения качественного признака ряду значений
. Например, необходимо выделить в списочном кадровом составе предприятия всех работников моложе 35 лет. Для этого достаточно в новый столбец напротив каждой фамилии подставить формулу вида: =ЕСЛИ(ячейка_с_возрастом<35; 1;0). Таким образом, всем молодым людям будет присвоен качественный признак =1, который даст возможность работать только с нужной частью списка.
Интересен вариант работы с формулой IF, когда используется не цифровые данные, а текст.
Текст в формуле нужно выделять кавычками для корректной отработки функции .
Всем инженерам 2 категории премия составляет 30%, остальным работникам – 50%. Для реализации этого условия вставляем формулу: =ЕСЛИ(ячейка_профессия=“инженер 2 категории”; “премия 30%”; “премия 50%”). Все инженеры 2 категории промаркируются данными по премии в 30%, остальным работникам будет введено значение «премия 50%».
Когда условия более сложные, то используют комбинации вложенных функций.
Применение вложенных IF
На предприятии при ведении табеля учёта рабочего времени возникает необходимость поставить месячную норму времени для расчёта зарплаты
. У разных специалистов эта величина различается. С помощью функции IF можно легко решить эту задачу.
Пусть у дневного персонала месячная норма равна 162 часа, у сменного при 8-часовом рабочем дне она составит 148 часов, а при рабочем дне 7,2 часа норма будет равна 136 часов.
Тогда нужно проверить дневную норму работника (8 или 7,2 часа) и категорию работающего (смена или день). Реализовать такую проверку можно разными способами.
- Вариант 1
Когда в качестве первой проверки берём дневную норму и вторую проверку делаем по истине, то формула будет следующая:
ЕСЛИ(ячейка_дневнаянорма=8; ЕСЛИ(ячейка_категория=“смена”;148;162);136)
- Вариант 2
Первую проверку оставляем прежней, вторую проверяем по лжи, то выражение будет выглядеть так:
ЕСЛИ(ячейка_дневнаянорма=7.2; 136;ЕСЛИ(ячейка_категория=“смена”;148;162))
- Вариант 3
Когда первую проверку делаем по категории, то имеем: =ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;136);162)
Результат во всех случаях будет одинаковый.
Допустим, что есть ещё смена по 12 часов с нормой в 144 часа. Тогда для реализации такого алгоритма потребуется ещё одно вложение функции IF. Это уже третий уровень.
Следующее выражение даёт решение и в этом случае:
Освоив принцип проверок, можно сделать сложные древовидные структуры . Хотя на практике более 4 уровней вложенности практически не применяется, так как более сложные комбинации можно реализовать проще через другие функции программы, работающие с выбором данных из массивов по критериям.
Одновременное применение двух условий
Многоликость функции IF
выражается в том, что её можно применять одновременно с другими функциями. В EXCEL есть ряд полезных встроенных конструкций, использующих метод IF. Это такие формулы, как СУММЕСЛИ, СЧЕТЕСЛИ, СРЗНАЧЕСЛИ. С их помощью легко выполнять расчёты по конкретному условию. Но и саму конструкцию IF можно применить одновременно с другими функциями. Рассмотрим её одновременное использование с оператором И и ИЛИ.
Функция IF с формулой И
Если требуется чтобы было выполнено одновременно несколько условий, то поможет конструкция И , имеющая синтаксис:
И(лог_выраж1; лог_выраж2;лог_выраж3;…)
Она проверяет истинность каждой из логических структур и возвращает ИСТИНУ только при истинности всех проверяемых выражений.
Где это можно использовать? Например, необходимо выделить из общего списка работников только слесарей 6 разряда, имеющих стаж работы больше 10 лет, то поможет конструкция вида:
ЕСЛИ(И(ячейка_профессия=”слесарь”;ячейка_разряд=6;ячейка_стаж>=10); 1;0)
Эта формула выделит в общем списке нужную категорию работников по установленному качественному признаку =1.
Функция IF с формулой ИЛИ
Вариант использования функции ИЛИ с IF
может быть следующий. Руководство предприятия в течение года поквартально поощряло работников. Необходимо выбрать всех, кто получал поквартальные премии больше 5000 р.
Конструкция ИЛИ имеет синтаксис аналогичный формуле И. Но ИСТИНА возвращается при условии истинности хотя бы одного логического выражения.
В нашем примере необходима формула:
ЕСЛИ(ИЛИ(ячейка_премия1квартал>=5000; ячейка_премия2квартал>=5000; ячейка_премия3квартал>=5000; ячейка_премия4квартал>=5000);1;0)
Последующая фильтрация данных по признаку 1 даст сделать нужный список трудящихся.
Таким образом, очевидна полезность функции IF. В новых версиях программы появляются улучшенные формулы, объединяющие наиболее востребованные старые приёмы и действия, что даёт неограниченные возможности для изучения и применения на практике программы эксель.
