Логические функции в Excel: И, ИЛИ, НЕ
Как собрать условие из нескольких: И требует все сразу, ИЛИ — хотя бы одно, НЕ переворачивает ответ. Семь формул на живых данных.
Коротко
Логические функции отвечают не числом, а словом: ИСТИНА или ЛОЖЬ. Сами по себе они нужны редко — их ставят внутрь ЕСЛИ, чтобы собрать условие из нескольких.
И требует, чтобы выполнились все условия: =И(D5>100000;C5="Москва") даёт ИСТИНА только москвичам с окладом больше ста тысяч. ИЛИ достаточно одного: =ИЛИ(C5="Москва";C5="Питер"). НЕ переворачивает ответ: =НЕ(C5="Москва") — все, кроме москвичей.
Обе принимают до 255 условий, но читаемость кончается на трёх. И главное: диапазон «от 50 до 150 тысяч» пишется только через И — =И(D5>50000;D5<150000). Привычная по математике цепочка 50000<D5<150000 в Excel не ошибка, и это хуже ошибки: формула посчитается и вернёт бессмыслицу.
То же самое формулой в Excel
=И(D5>100000;C5="Москва") — оба условия сразу; ответ ИСТИНА или ЛОЖЬ=ИЛИ(C5="Москва";C5="Питер") — достаточно одного=НЕ(C5="Москва") — переворачивает ответ=ЕСЛИ(И(D5>100000;C5="Москва");"премия";"нет") — так это обычно и используют — внутри ЕСЛИ=ЕСЛИ(ИЛИ(D5<60000;D5>200000);"проверить";"норма") — ИЛИ ловит выбросы с обеих сторон=И(D5>50000;D5<150000) — диапазон «от и до» — только так=СУММПРОИЗВ((C5:C10="Москва")*(D5:D10>100000)) — сколько строк подходит под оба условия сразу, без вспомогательного столбцаФайл с семью примерами
Шесть сотрудников: город, оклад, дата приёма. Справа И, ИЛИ и НЕ по отдельности и внутри ЕСЛИ, плюс подсчёт строк по двум условиям через СУММПРОИЗВ.
Файл
logicheskie-funkcii.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Решите, что должно выполниться: всё сразу — это И, хотя бы одно — ИЛИ.
- Наберите
=И(или=ИЛИ(и перечислите условия через точку с запятой: каждое — это сравнение видаD5>100000илиC5="Москва". - Проверьте ответ: он должен быть ИСТИНА или ЛОЖЬ. Если
#ЗНАЧ!— в сравнении текст там, где ждали число. - Оберните это в ЕСЛИ, чтобы вместо ИСТИНА и ЛОЖЬ появились понятные слова:
=ЕСЛИ(И(…);"премия";"нет"). - Протяните вниз. Ссылки на столбцы сдвинутся вместе со строками, а ссылку на ячейку с порогом закрепите знаком доллара.
Где обычно ломается
- Диапазон цепочкой.
=50000<D5<150000Excel посчитает, но неправильно: он сравнит результат первого сравнения со вторым числом. Ошибки не будет, будет неверный ответ. Только=И(D5>50000;D5<150000). - Текст в кавычках, числа без.
=И(D5>"100000")сравнивает число с текстом и в Excel всегда даёт ЛОЖЬ: текст «больше» любого числа. - Регистр не важен, пробелы важны. «москва» и «Москва» — одно и то же, а «Москва » с пробелом — уже другое. Оборачивайте в
СЖПРОБЕЛЫ. - И с диапазоном вместо ячейки.
=И(D5:D10>100000)в Excel 2016 без особого ввода не работает как ожидается. Чтобы посчитать строки по нескольким условиям, берите СУММПРОИЗВ или СЧЁТЕСЛИМН. - Пустая ячейка — это ноль.
=И(D5>0)на пустой ячейке даст ЛОЖЬ, а=И(D5=0)— ИСТИНА. Если пустое нужно отличать от нуля, проверяйте=ЕПУСТО(D5).