Как или чем заменить функцию ЕСЛИ в формулах Excel

Функция ЕСЛИ позволяет решать большинство задач в Excel, вычисления в которых выполняются не последовательно, шаг за шагом, а с некоторыми ветвлениями. Например, был определен некоторый коэффициент, от значения которого полностью зависит ход дальнейших расчетов. Кроме того, часто требуется выполнить какую-либо операцию над диапазоном данных (суммированием, вычисление среднего значения и т. д.) с использованием какого-либо критерия. Например, найти сумму чисел из диапазона, значения которых не менее 10.

Существует как минимум три способа заменить использование функции ЕСЛИ:

Заменить данную функцию другой встроенной функцией Excel, при этом необязательно логической. Например, одну и ту же задачу можно решить тремя разными способами (с использованием формулы ЕСЛИ, с помощью СУММЕСЛИ или без логических функций вовсе), что будет показано в одном из примеров.
Использовать простейшие логические конструкции в связке с арифметическими действиями.
Создание пользовательских функций с помощью VBA.

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

Примеры замены функции ЕСЛИ в Excel с помощью формул

Пример 1. В таблице Excel хранятся данные о доходах компании за каждый месяц (обозначен номером) прошедшего года. Реализовать алгоритм расчета суммы доходов за любых несколько месяцев года без использования функции ЕСЛИ в Excel.

Вид исходной таблицы данных:

Для расчетов суммы доходов за любой из возможных периодов в ячейке C3 запишем следующую формулу:

Описание аргументов функции СМЕЩ:

A6 – ячейка, относительно которой ведется отсчет;
ПОИСКПОЗ(A3;A7:A18;0) – функция, возвращающая ячейку, с которой будет начат отсчет номера месяца, с которого ведется расчет суммы доходов за определенный период;
1 – смещение по столбцам (нас интересует сумма доходов, а не номеров месяцев);
ПОИСКПОЗ(B3;A7:A18;0)-ПОИСКПОЗ(A3;A7:A18;0)+1 – выражение, определяющее разность между указанными начальной и конечной позициями в таблице по вертикали, возвращающее число ячеек рассматриваемого диапазона;
1 – ширина рассматриваемого диапазона данных (1 ячейка).

Функция СУММ принимает в качестве аргумента диапазон ячеек, возвращаемый функцией СМЕЩ, и вычисляет сумму содержащихся в них значений.

Для примера приведем результат расчетов с 3 по 7 месяц:

Для сравнения, рассмотрим вариант расчета с использованием функции ЕСЛИ. Формула, которая приведена ниже, должна быть выполнена в качестве формулы массива (для ввода CTRL+SHIFT+Enter), а для определения суммы доходов для различных периодов ее придется видоизменять:

Диапазон ячеек, для которых будет выполняться функция СУММ, определяется двумя условиями, созданными с использованием функций ЕСЛИ. В данном случае расчет производится для месяцев с 3 по 7 включительно. Результат:

Как видно, полученные результаты совпадают. Несмотря на кажущуюся сложность формулы с использованием функции СМЕЩ, она является более простой, наглядной и не требует внесения изменений для каждого нового расчета.

Формулы решений при нескольких условиях без функции ЕСЛИ

Пример 2. В таблице Excel содержатся значения вероятностей попадания в цель для стрелков из трех различных видов оружия. Рассчитать вероятность попадания в цель хотя бы из одного оружия для каждого стрелка. В некоторых ячейках содержатся ошибочные данные (значения взяты не из диапазона допустимых значений для вероятности). Для таких случаев рассчитать вероятность как 0. При условии, что в формуле нельзя использовать логическую функцию ЕСЛИ.

Вид исходной таблицы:

Для расчета вероятности используем формулу P(A)=1-q1q2q3, где q1,q2 и q3 – вероятности промахов (событий, противоположным указанным, то есть попаданию в цель). Используем следующую формулу:

Часть формулы «И(B3>=0;B3<=1;C3>=0;C3<=1;D3>=0;D3<=1))» необходима для выполнения операции сравнения содержащегося в каждой ячейке значения с диапазоном допустимых (от 0 до 1). Функция ИСТИНА возвращает логическое ИСТИНА, если все выражения вернули результат ИСТИНА, и ЛОЖЬ, если хотя бы одно из проверяемых выражений вернуло ЛОЖЬ. Поскольку за данным выражением следует знак «*» (умножение), Excel автоматически преобразует полученное логическое значение к числовому (1 или 0).

Результаты расчета для всех стрелков:

Таким образом, расчет производится только в том случае, если все три ячейки A, B и C содержат корректные данные. Иначе будет возвращен результат 0.

Пример кода макроса как альтернативная замена функции ЕСЛИ

Пример 3. В МФО выдают кредиты на срок от 1 до 30 дней на небольшие суммы под простые проценты (сумма задолженности на момент выплаты состоит из тела кредита и процентов, рассчитанных как произведение тела кредита, ежедневной процентной ставки и количества дней использования финансового продукта). Однако значение процентной ставки зависит от периода, на который берется кредит, следующим образом:

От 1 до 5 дней – 1,7%;
От 6 до 10 дней – 1,9%;
От 11 до 15 дней – 2,2%;
От16 до 20 дней – 2,5;
Свыше 21 дня – 2,9%.

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

Вид исходной таблицы данных:

Вместо проверки множества условий с использованием функции ЕСЛИ напишем простую пользовательскую функцию с помощью макроса (ALT+F11). Исходный код пользовательской функции MFODebt:

Public Function MFODebt(amount As Double, period As Double) As Double Dim interest As Double Select Case period Case 1 To 5 interest = 0.017 Case 6 To 10 interest = 0.019 Case 11 To 15 interest = 0.022 Case 16 To 20 interest = 0.025 Case 21 To 30 interest = 0.029 End Select MFODebt = amount + amount * interest * period End Function

Для определения размера процентной ставки используется простая и наглядная конструкция Select Case. Воспользуемся созданной функцией для расчетов:

=MFODebt(B3;C3)

«Растянем» формулу на остальные ячейки и получим следующие результаты:

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