Скачать примеры использования логических функций И ИЛИ в Excel
Функция И – это логическая функция. Она позволяет определять условия, которые проверяет, на результат ИСТИНА, если все условия выполняются (ЛОЖЬ – если хоть одно условие не выполняется). Функция ИЛИ – также логическая функция, которая в отличие от «И» определяет только одно условие из имеющихся на результат ИСТИНА (ЛОЖЬ – если все проверяемые условия не выполняется).
Логические функции в Excel практическая работа
Синтаксис обеих функций выглядит следующим образом:
Логическое значение 1 является обязательным аргументом, а количество последующих зависит от поставленной задачи. Теперь рассмотрим как работают эти функции. У нас есть форма о результатах тестов, которые проводились среди сотрудников и указан проходной балл. Нам нужно узнать кто из сотрудников справился со всеми тестами, а кто нет. Для этого укажем в формуле, что будем проверять И первый тест, И второй, И третий, И четвёртый, каждый должен быть не менее 7 (ячейка D8):
В результате в ячейке G3 мы получили результат ИСТИНА, поскольку все условия выполнены. Обязательно используем абсолютные ссылки и копируем формулу до конца столбца. Теперь у нас есть результат для всех сотрудников:
Поскольку в ячейках E4, F4 и F6 у нас значение 6, условие не выполнилось и возвратился результат ЛОЖЬ и мы узнали, кто из сотрудников провалил хотя бы один тест, а кто справился со всеми и может продолжать работать. Но слова «ИСТИНА» или «ЛОЖЬ» не совсем корректно отображают смысл результатов. Поэтому добавим к функции ещё два выражения – «Отлично» будет использоваться, если ИСТИНА, и «Есть пересдачи» будет использоваться, если ЛОЖЬ. В этом нам поможет функция ЕСЛИ, в которую мы встроим функцию И:
Скопируем функцию до конца столбца и получим готовую таблицу:
Напротив ячеек, значение которых меньше 7, мы получили результат «Есть пересдачи».
Теперь рассмотрим работу функции ИЛИ. У нас всё те же тесты и сотрудники, но сейчас нам нужно проверить, есть ли у нас отличники хотя бы по одному тесту. То есть, есть ли среди значений ячеек C19, D19, E19, F19 величина 10:
Мы получили значение ИСТИНА, поскольку значение ячейки С19 равно 10. Теперь копируем нашу формулу до конца и получаем результат по остальным сотрудникам:
Для более корректной информативности заменим результат «ИСТИНА» на фразу «Есть оценки 10», а результат «ЛОЖЬ» - на фразу «Нет оценок 10». Для этого также используем функцию ЕСЛИ, в которую вложим формулу ИЛИ:
В ячейке С26 у нас расположено значение 10, поэтому формула возвратила значение если ИСТИНА – «Есть оценки 10». Скопируем формулу до конца столбца и получим готовую таблицу:
Использование функций И ИЛИ вместо вложенных функций ЕСЛИ
Иногда благодаря функциям И/ИЛИ можно избежать вложенных функций ЕСЛИ. Вложенные функции – хороший инструмент, но они становятся громоздкими при добавлении новых условий. Например, наше задание «Определить, кто из сотрудников выполнил все тесты, а кому нужно их доработать» можно выполнить вложенными функциями ЕСЛИ:
В ячейке G33 мы построили вложенную функцию и скопировали её до конца столбца. Результаты у нас получились те же, что и с использованием ЕСЛИ+И, однако, сама формула очень длинная, в ней легко будет запутаться, если нам нужно будет добавлять новые условия. К тому же работа с ней отнимет больше времени, хотя выполняют они задачу корректно и одинаково. Поэтому лучше отдать предпочтение функции ЕСЛИ с использованием функции И:
Второй пример, где мы использовали ЕСЛИ+ИЛИ для того чтобы найти, кто из сотрудников получил наивысшие баллы, мы также можем рассмотреть с использованием вложенных функций ЕСЛИ:
Результаты у нас получились те самые. Однако, если сравнить формулы этих двух примеров, функция ЕСЛИ+ИЛИ значительно короче и аккуратнее, особенно при добавлении новых условий:
Как работают формулы ЕСЛИ+И+ИЛИ, ЕСЛИ+ИЛИ+И
Иногда функции И, ИЛИ, ЕСЛИ используются одновременно. Прежде, чем углубиться в некоторые такие примеры, укрепим использование уже знакомых нам формул. У нас есть информация о бюджете различных отделов. Мы хотим проверить, превышает ли сумма бюджета определённого отдела значение 100 000 И превышают ли абсолютные расходы с начала года значение 50 000. Если эти два условия будут выполнены, укажем слово «Выполнено», а если нет – знак «-». Указываем абсолютные ссылки для ячеек с условиями, иначе при копировании формулы, значения тоже будут опускаться:
Мы получили результат «-», поскольку только одно условие выполнилось – 70 000 больше 50 000, но 80 000 не больше 100 000. Скопируем нашу формулу до конца столбца и определим, какие отделы соответствуют условиям:
В этом примере мы задали определенные условия, которые должны были выполниться одновременно. Теперь рассмотрим пример, где расширим функционал формулы через добавление функции ИЛИ. Оставим два условия, известные нам из предыдущего примера – бюджет превышает 100 000, абсолютные расходы превышают 50 000 и добавим новые условия через функцию ИЛИ: был ли перерасход бюджета в прошлом году ИЛИ был ли перерасход бюджета больше, чем на 20%. Теперь у нас 4 условия, но только в случае, если три из них верны – мы получим значение «Выполнено»:
Мы использовали комбинацию функций ЕСЛИ + И + ИЛИ. Скопируем формулу до конца столбца и увидим отделы, данные которых соответствуют заданным нами условиям:
Производственный отдел и НИОКР выполнили наши условия, поскольку бюджет превышает 100 000 И абсолютные расходы превышают 50 000, кроме этого, отдел НИОКР имеет превышение бюджета больше, чем на 20%, а производственный отдел имеет превышение, но меньше чем на 20% - этого достаточно для выполнения наших условий.
Теперь выполним поиск отделов, которые будут соответствовать одному из заданных условий. Принцип работы тот же, что и в примере с выполнением условий «Бюджет больше 100 000 И абсолютные расходы больше 50 000», но на этот раз изменим требования – нам нужны отделы, которые выполняют ХОТЯ БЫ одно требование: бюджет больше 150 000 или расходы абсолютные больше 160 000. В этом нам помогут функции ЕСЛИ + ИЛИ:
Копируем формулу и видим, что статус «Выполнено» получили те отделы, которые удовлетворили одно из требований:
Наконец, рассмотрим последнюю комбинацию функций ЕСЛИ + ИЛИ + И. В этом случае нам надо добавить последнюю функцию И в качестве третьего условия функции ИЛИ. Для функции И мы укажем два требования – наличие превышения бюджета, превышение больше 10%:
Эта комбинация ищет соответствие ХОТЯ БЫ одному требованию из трёх:
- Бюджет больше 150 000.
- Абсолютные расходы больше 160 000.
- Наличие превышения прошлогоднего бюджета, показатель превышения больше 10%.
Копируем формулу до конца таблицы и проверим, какие отделы выполнили наши требования:
Скачать логические функции И ИЛИ в Excel с примерами
Так работают логические функции в Excel с использованием дополнительных формул для решения много задачных задач.