7 способов проверить условия в Excel
Проверка условий и выполнение расчётов по разной логике в зависимости от выполнения или невыполнения этих условий - один из базовых навыков, который одним из первых осваивают все, кто работает в Microsoft Excel. Обычно для этой цели используют классическую функцию ЕСЛИ (IF). Если же необходимо проверить несколько критериев, то приходится вкладывать несколько функций ЕСЛИ друг в друга или использовать связки ЕСЛИ+И (ИЛИ), что уже не так просто и приводит к появлению страшноватых "матрёшек-монстров".
Однако, кроме общеизвестной функции ЕСЛИ, на самом деле, есть ещё несколько более изящных способов выполнить подобную проверку.
Во-первых, в последних версиях Microsoft Excel появились новые удобные функции для этой цели: ЕСЛИМН (IFS) и ПЕРЕКЛЮЧ (SWITCH) - с и помощью реализовать сложные многоуровневые проверки стало теперь гораздо проще. Во-вторых, даже в старых версиях Excel есть несколько подходов к проверке условий вообще без функции ЕСЛИ, зато с использованием формул массива. которые однозначно стоит изучить.
ЕСЛИ (IF)
Для начала, кратко вспомним "классику" - функцию ЕСЛИ (IF), доступную абсолютно в любой версии Microsoft Excel:
Первый аргумент здесь - это проверяемое условие, второй - что выводим если условие выполняется, третий - что выводим, если условие не выполняется.
Если нужно проверить несколько условий, то придётся вкладывать друг в друга несколько функций ЕСЛИ и формула получается уже пострашнее:
Для наглядности, кстати, весьма полезно бывает прямо в строке формул разнести вложенные функции по разным строчкам (с помощью сочетания клавиш Alt+Enter) и сделать для них отступы соответственно уровню вложенности (с помощью пробелов) - так всё будет гораздо понятнее:
ЕСЛИМН (IFS)
Эту функцию добавили в Excel, начиная с 2019-й версии. С её помощью матрёшку из вложенных друг в друга функций ЕСЛИ можно заменить одной компактной формулой:
Здесь в качестве аргументов мы задаём пары - условие и значение, которое нужно вывести, если условие выполняется. Максимально разрешается задать до 127 таких пар "условие-результат", что более, чем достаточно для повседневных задач.
Имейте в виду, что первое же выполнившееся условие останавливает расчёт и дальнейшие условия уже не проверяются.
ПЕРЕКЛЮЧ (SWITCH)
Ещё одна новая функция, добавленная с 2019 года - это функция ПЕРЕКЛЮЧ (SWITCH). Она тоже позволяет заменить кучу вложенных друг в друга ЕСЛИ, но делает это слегка иначе:
Важно отметить, что первым аргументом этой функции может быть не проверяемая ячейка, а логическое значение ИСТИНА или ЛОЖЬ. Тогда все указанные далее условия будут проверяться на выполнение или невыполнение, соответственно. Например, весьма частым случаем проверки условий бывает проверка попадания исходного значения в один из нескольких определённых интервалов. Как раз для такого отлично подойдет функция ПЕРКЛЮЧ со значением ИСТИНА в первом аргументе:
ВПР с интервальным просмотром
Предыдущую задачу с проверкой попадания в интервалы можно решить и более компактным способом - с использованием функции ВПР (VLOOKUP) в режиме интервального просмотра, когда она ищет не точное, а ближайшее наименьшее совпадение:
Здесь:
- F2 - ячейка с ценой для которой ищем класс;
- J:K - таблица с ценовыми категориями;
- 2 - номер столбца в этой таблице, откуда нужно вернуть класс;
- 1 (или ИСТИНА) - включение режима приблизительного поиска, когда в первом столбце таблицы J:K ищется не точное, а ближайшее наименьшее совпадение.
ВЫБОР
Помимо новых функций ЕСЛИМН (IFS) и ПЕРКЛЮЧ (SWITCH) для решения задач множественной проверки условий можно использовать и классику - например, функцию ВЫБОР (CHOOSE):
Начиная со второго её аргумента мы указываем все возможные результаты, а первым аргументом - номер результата, который нам нужен.
Дополнительно снаружи можно завернуть её в функцию ЕСЛИОШИБКА (IFERROR) для перехвата ошибок в том случае, когда ни одно из значений не может быть получено (например, оценка не указана или меньше нуля).
Что интересно, в качестве второго и далее аргументов функции ВЫБОР можно задавать не только текст или числа, но и целые диапазоны. Например, можно легко вычислить сумму продаж за выбранный квартал:
Прямое умножение на условие(я)
Есть и способы проверки условий, которые вообще не предполагают использования каких-либо логических функций типа ЕСЛИ (IF).
Например, можно использовать в формуле прямое умножение на условие. Там где условие выполняется - возвращается ИСТИНА, где не выполняется - ЛОЖЬ. Однако вычислительный движок Excel интерпретирует ИСТИНУ как 1, а ЛОЖЬ как 0, так что последующее умножение на условие обнуляет, таким образом, все ячейки, где критерий не выполняется.
Так, например, можно легко дать скидку в 15% на нужный товар:
А если захочется добавить ещё условие (например, давать скидку только на свитера дороже 3000), то можно просто дописать к формуле ещё один множитель-условие:
Формула массива
Если нужно проверить сразу несколько значений на соответствие заданным критериям, то можно использовать и функции И (AND) и ИЛИ (OR), причём в варианте формулы массива.
Предположим, что нам нужно дать ту же скидку в 15% на товары с характеристиками из жёлтых ячеек:
Здесь мы проверяем сразу три ячейки A2:C2 на предмет равенства искомым критериям в жёлтых ячейках H2:J2. Если каждая проверяемая пара ячеек даёт ИСТИНУ, то и вся функция И тоже вернёт логическое значение ИСТИНА, которое мы затем умножаем на нашу скидку.
Если у вас последние версии Excel (2021 или новее), то после ввода этой формулы достаточно просто нажать на Enter. В старых же версиях необходимо ввести эту формулу уже как формулу массива - используя сочетание клавиш Ctrl+Shift+Enter.
Ссылки по теме
- Выборочные вычисления по одному или нескольким критериям
- Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE)
- Формулы массива в Excel
Читаемость выросла в разы!
Спасибо, Николай!
Оттолкнувшись от раздела "Прямое умножение на условие(я)" решил попробовать по аналогии "сложение", т.е. функцию ИЛИ() заменил на "+".
В результате моя формула:
=ЕСЛИ(ИЛИ(И($G$9=1;
ДЛСТР($A$12)=10);
И($G$9=2;
ДЛСТР($A$12)=12);
ЕПУСТО($A$12));
ИСТИНА();
ЛОЖЬ())
приобрела вид:
=И((($G$9=1)*(ДЛСТР($A$12)=10)+($G$9=2)*(ДЛСТР($A$12)=12)+ЕПУСТО($A$12)))
Еще один момент: у Вас в видео для ПЕРКЛЮЧ со значением ИСТИНА в первом аргументе для пустой клетки выдает "Эконом", а должен быть "-".
Сразу хочу спросить уважаемых форумчан, начальник выдал на гора идею, пока не пойму в какую сторону копать! Есть таблица в которой названия контрагентов, оказанная услуга, место, объём услуги и т.д., предпоследний столбец - дата выставления счёта и последний - дата оплаты. Начальник хочет чтобы строка начинала выделяться красным, если счёт не оплачен в течении трёх рабочих дней, а при последующим заполнении строки оплаты цвет менялся на жёлтый, чтобы впоследствии можно было выделить контрагентов которые хронически задерживают оплату.
Честно говоря моих знаний не хватает для решения этой задачи в полном объёме! Самый затык в том, что нужно считать не просто дни, а только рабочие!
Function KeepValueIf(condition As Boolean, trueValue As Variant, falseValue As Variant) As Variant
' Вызов: =KeepValueIf(A1=B1; A2; B2)
' Меняет ячейку B2 на значение ячейки А2 (А2 => В2) при выполнении условия и не меняет ее иначе
' conditio- (A1=B1) - условие, любое в синтаксисе Excel. Например, текущая дата равна целевой дате. Или "больше", "меньше", "не равно" и т.п.
' trueValue- A2 - новое значение, которое будет установлено в ячейке B2
' falseValue- B2 - результат в ячейке B2
' Если A1=B1, то {В2=А2} - изменение В2 на А2
' иначе {В2=В2} - нет изменения В2
If condition Then
KeepValueIf = trueValue
Else
KeepValueIf = falseValue
End If
End Function
Все работает, пока файл не закрыт. При открытии файла, в ячеке В2 ошибка #ИМЯ, если условие не выполняется.
Предположу, что при первом сохранении и закрытии файла Эксель запомнил только формулы в ячейке В2, но не запомнил значение этой ячейки. При повторном открытии файла запускается пересчет формул. Если условие не выполняется, идет обращение за значением этой ячейки, а его нет. Это интерпретируется Экселем, как ошибка #ИМЯ (недопустимое имя). Если моя гипотеза верна, то невозможно написать такую пользовательскую функцию, не прибегая к организации процесса самостоятельного сохранения значений ячейки В2.
Мне этот механизм нужен для автоматической фиксации любых итогов в конце дня, например, стоимость инвестиционного портфеля.
Таблица.
A1= СЕГОДНЯ() текущая дата
A2= Текущая стоимость портфеля
B1, C1, D1 ... = заранее установленные даты на какой-то период. Например, 10.11.2025, 11.11.2025, 12.11.2025, ...
B2, C2, D2 ... = зафиксированная стоимость портфеля на дату в B1, C1, D1
Т.е. итоги каждого дня последовательно автоматически переносятся в ячеки B2, C2, D2 ..., не затирая ранее установленные значения смежных ячеек слева.
Какой подход можно применить ? Может быть, Вы уже решали такую задачу другими методами.
Заранее благодарен.