Альтернатива если в excel
Логические функции предназначены для проверки одного либо нескольких условий, и выполняют действия, предписанные для каждого из двух возможных результатов. Таковыми результатами могут являться только логические ИСТИНА или ЛОЖЬ.
В Excel содержится несколько логических функций, таких как ЕСЛИ, ЕСЛИОШИБКА, СУММЕСЛИ, И, ИЛИ и другие. Две последние на практике, как правило, самостоятельно не используют, поскольку результатом их вычислений может являться один из только двух возможных вариантов (ИСТИНА, ЛОЖЬ). При совместном использовании с функцией ЕСЛИ, они способны значительно расширить ее функционал.
Примеры использования формул с функциями ЕСЛИ, И, ИЛИ в Excel
Пример 1. При расчете стоимости количества потребленных кВт электроэнергии для абонентов учитывают следующие условия:
- Если в квартире проживают менее 3 человек или за месяц было потреблено менее 100 кВт электроэнергии, ставка за 1 кВт составляет 4,35 рубля.
- В остальных случаях ставка за 1кВт составляет 5,25 рубля.
Рассчитать сумму к оплате за месяц для нескольких абонентов.
Вид исходной таблицы данных:
Выполним расчет по формуле:
Растянем формулу для остальных ячеек с помощью функции автозаполнения. Результат расчета для каждого абонента:
Используя в формуле функцию И в первом аргументе в функции ЕСЛИ, мы проверяем соответствие значений сразу по двум условиям.
Формула с функциями ЕСЛИ и СРЗНАЧ для отбора значений при условии
Пример 2. Абитуриенты, поступающие в университет на специальность «инженер-механик», обязаны сдать 3 экзамена по предметам математика, физика и русский язык. Максимальный балл за каждый экзамен – 100. Средний проходной балл за 3 экзамена составляет 75, при этом минимальная оценка по физике должна составить не менее 70 баллов, а по математике – 80. Определить абитуриентов, которые успешно сдали экзамены.
Вид исходной таблицы:
Для определения зачисленных студентов используем формулу:
- И(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логические выражения согласно условию задачи;
- "Зачисл." – результат, если функция И вернула значение ИСТИНА (все выражения, представленные в виде ее аргументов, в результате вычислений вернули значение ИСТИНА);
- "Не зач." – результат, если И вернула ЛОЖЬ.
Используя функцию автозаполнения (сделав двойной щелчок по маркеру курсора в нижнем правом углу), получим остальные результаты:
Формула с логическими функциями И ЕСЛИ ИЛИ в Excel
Пример 3. Субсидии в размере 30% начисляются семьям со средним уровнем дохода ниже 8000 рублей, которые являются многодетными или отсутствует основной кормилец. Если число детей свыше 5, размер субсидии – 50%. Определить, кому полагаются субсидии, а кому – нет.
Вид исходной таблицы:
Для проверки критериев согласно условию задачи запишем формулу:
Выполним расчет для первой семьи и растянем формулу на остальные ячейки, используя функцию автозаполнения. Полученные результаты:
Особенности использования логических функций ЕСЛИ, И, ИЛИ в Excel
Функция ЕСЛИ имеет следующую синтаксическую запись:
=ЕСЛИ( лог_выражение ;[значение_если_истина];[значение_если_ложь])
Как видно, по умолчанию можно выполнить проверку только одного условия, например, больше ли e3 числа 20? С использованием функции ЕСЛИ такую проверку можно выполнить так:
В результате будет возвращена текстовая строка «больше». Если нам потребуется узнать, принадлежит ли какое-либо значение указанному интервалу, потребуется сравнить это значение с верхней и нижней границей интервалов соответственно. Например, находится ли результат вычисления e3 в интервале от 20 до 25? При использовании одной лишь функции ЕСЛИ придется ввести следующую запись:
Имеем вложенную функцию ЕСЛИ в качестве одного из возможных результатов выполнения основной функции ЕСЛИ, в связи с чем синтаксис выглядит несколько громоздким. Если потребуется также узнать, например, равен ли корень квадратный e3 числовому значению из диапазона дробных чисел от 4 до 5, итоговая формула будет выглядеть громоздкой и неудобочитаемой.
Гораздо проще использовать в качестве условия сложное выражение, которое может быть записано с использованием функций И и ИЛИ. Например, приведенная выше функция может быть переписана следующим образом:
Иногда требуется узнать, оказалось ли хотя бы одно предположение верным. В этом случае удобно использовать функцию ИЛИ, которая выполняет проверку одного или нескольких логических выражений и возвращает логическое ИСТИНА, если результат вычислений хотя бы одного из них является логическим ИСТИНА. Например, требуется узнать, является ли e3 целым числом или числом, которое меньше 100? Для проверки такого условия можно использовать следующую формулу:
Запись «<>» означает неравенство, то есть, больше либо меньше некоторого значения. В данном случае оба выражения возвращают значение ИСТИНА, и результатом выполнения функции ЕСЛИ будет текстовая строка «верно». Однако, если бы выполнялась проверка ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)0 возвращает ИСТИНА.
На практике часто используют связки ЕСЛИ+И, ЕСЛИ+ИЛИ или сразу все три функции. Рассмотрим примеры подобного использования этих функций.
Одиночные условия функции ЕСЛИ, которые проверяют в первом аргументе выполняется ли условие, можно объединять с последующим условием во втором и/или в третьем аргументе (в зависимости от поставленной задачи). Данное решение в Excel называется циклование функций или функция ЕСЛИ с несколькими условиями. Второй и третий аргументы: Значение_если_ИСТНИА и Значение_если_ЛОЖЬ в функции также могут содержать простые условия, как и первый аргумент. Таким образом можно проверять несколько условий, при чем проверка очередного условия зависит от результата проверки предыдущего.
Формула с несколькими функциями ЕСЛИ в Excel
Ниже на рисунке изображен рабочий лист с двумя выпадающими списками, в которых пользователь может указать тип автомобиля и его характеристики.
В ячейках A2 и C2 находятся выпадающие списки. Свойства характеристик автомобиля записаны в двух диапазонах ячеек ниже под этими таблицами D2:D3 и D6:D7. Создайте выпадающие списки выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных», в окне «Проверка вводимых значений» укажите «Тип данных: Список». Заполните их параметры так как показано ниже на рисунке:
В данном примере используется формула в ячейке C7, которая в зависимости от типа и характеристик автомобиля возвращает его категорию кузова: Седан, Купе, Пикап или Внедорожник:
В данной формуле результат вычисления первого логического выражения по первому условию влияет на результат, возвращаемый вторым логическим выражением по второму условию. Рисунок:
В данном примере если проверяемая ячейка содержит значение «Легковой», тогда во втором условии проверяется имеет ли другая ячейка значение 2-хдверный или 4-хдверный. А если же проверяемая ячейка в первом условии имеет значение «Грузовик», тогда проверяется второе условие. Теперь оно проверяет содержит ли другая ячейка значение «с задним сидением» или «без заднего сидения».
Базовым инструментом для выполнения условных анализов данных в Excel является функция ЕСЛИ. Если необходимо выполнить анализ данных по нескольким условиям эту функцию можно зациклить, для этого в ее аргументах снова использовать еще одну функцию ЕСЛИ. В выше приведенном примере первая функция ЕСЛИ проверяет содержимое ячейки A2. Функция вместо того чтобы вернуть результат из второго аргумента вызывает вторую функцию ЕСЛИ, которая уже проверяет значение ячейки C2 по второму условию. Аналогично в третьем аргументе первой функции не содержится обычное значение, а третья функция ЕСЛИ, которая также проверяет значения ячейки C2, но уже по другому своему условию.
На рисунке примера мы видим, что пользователь выбрал из выпадающего списка тип автомобиля «Грузовик». Логическое выражение в первой функции ЕСЛИ вернуло значение ЛОЖЬ, так как содержимое ячейки A2 не равно значению «Легковой», поэтому возвращается второй аргумент первой функции. А в том аргументе формула проверяла: содержит ли ячейка C2 значение «с задним сидением» и вернула значение первого аргумента «Внедорожник». Если бы пользователь во втором выпадающем списке указал на опцию «без заднего сидения», тогда было бы возвращено содержимое третьего аргумента третьей функции ЕСЛИ – «Пикап».
Проверка по нескольким условиям без использования функции ЕСЛИ
В старых версиях Excel до 2007-й версии можно было создавать не больше 7-ми уровней циклирования функций ЕСЛИ. Начиная с 2007-й версии Excel, количество циклов увеличено до 64-х уровней. Как не сложно догадаться формулы с зацикленными функциями ЕСЛИ по нескольким условиям уже на 5-ом уровне весьма нечитабельны и сложно применимы, не говоря уже о 7-ми или 64-х уровнях. Если требуется формула больше чем для 3-х – 4-х уровней циклирования, стоит поискать альтернативные методы.
Альтернативная формула с одной функцией ЕСЛИ и ВПР
Когда формула содержит слишком много зацикленных логических функций ЕСЛИ, ее сложно использовать и прочитать. Ниже на рисунке приведен немного другой пример выборки типа кузова автомобиля. Вместо ввода постоянных значений в аргументах функций ЕСЛИ, можно указать ссылки на ячейки, которые содержат соответствующие значения. Например, возле ячейки с тестом «4-хдерный» находится текст «Седан». На эти значения можно ссылаться прямо из формулы.
Новая альтернативная формула будет выглядеть следующим образом:
Мы получили аналогичный результат. Условия первой функции ЕСЛИ остается неизменным. Однако во втором ее аргументе уже содержится функция ВПР, которая умеет искать значения по условию в диапазоне ячеек D2:E3. А в третьем аргументе функции ЕСЛИ формула ВПР ищет значения по условию в диапазоне D6:E7.
Проверка ввода данных по условию динамических выпадающих списков
Выпадающие списки для ввода данных пользователем показаны на рисунке примера этой статьи. Это стандартный инструмент проверки вода данных Excel. Пользователь не вводит значение в ячейку, а лишь выбирает его из выпадающего списка.
Создадим зависимый выпадающий список с динамическим изменением значений. Вам необходимо изменить правила проверки ввода данных выпадающим списком в ячейке C2 использовать интересную технику с функцией =ДВССЫЛ(). Благодаря ей содержимое выпадающего списка будет изменятся в зависимости от значения в ячейке A2.
Выше на рисунке изображено окно «Проверки вводимых значений» с формулой для списка в ячейке C2.
Еще определим на листе 2 имена диапазонов для ячеек. Диапазон с именем «Легковой» охватывает ячейки D2:D3, а диапазон имени «Грузовик» относится к ячейкам D6:D7. Можно просто выделить диапазон и в поле «Имя» ввести название. Или создать имена по инструкции: «ФОРМУЛЫ»-«Диспетчер имен»-«Создать» как изображено ниже на рисунке:
Эти имена диапазонов идентично совпадают со значениями позиций первого выпадающего списка для ячейки A2. А как вы помните поле ввода «Источник:» содержит функцию ДВССЫЛ со ссылкой на ячейку A2 в ее аргументе.
Аргумент функции ДВССЫЛ получает текст с помощью ссылки на ячейку A2. Например, в данном случае формула имеет отладочный вид: =ДВССЫЛ(«Грузовик»), так как на данный момент ячейка A2 содержит этот текст. Это же и есть имя диапазона. В результате вычислений функция ДВССЫЛ возвращает ссылку с адресом диапазона ячеек D6:D7. А значения этих ячеек используются для второго выпадающего списка в ячейке C2. Когда ячейка A2 будет содержать слово «Легковой», тогда функция ДВССЫЛ вернет уже ссылку на другой диапазон ячеек D2:D3, который бы использовался во втором выпадающем списке. Таким образом мы получаем динамический выпадающий список в ячейке C2, который меняет свои значения при условии выбора по первому выпадающему списку.
Теперь связанный выпадающий список не содержит лишних опций, как ему и полагается.
Единственная проблема осталась этом примере это то, что в момент изменения значения пользователем в ячейке A2, автоматически не меняется значение в C2. Перечень значений – обновляются, а значения в ячейке будут обновлены только после использования списком. Поэтому пользователь должен обязательно использовать 2 списка для обновления значений в обеих ячейках. Иначе будут ошибочные результаты при вычислении формулы.
Макрос для связанного динамического выпадающего списка
Но можно для нашего листа просто прописать в пару строчек кода элементарный макрос для очистки ячейки B2 при каждом использовании выпадающего списка в A2. Для этого откройте окно редактора VBA-кода макросов: «РАЗРАБОТЧИК»-«VisualBasic» или нажмите ALT+F11. После чего вставите это код в лист где находится таблица:
А в ячейке C7 где была старая формула вводим новую формулу с функцией ВПР:
В результате нам не нужна функция ЕСЛИ для проверки значений по нескольким условиям.
Функция ЕСЛИ позволяет решать большинство задач в 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 – вероятности промахов (событий, противоположным указанным, то есть попаданию в цель). Используем следующую формулу:
Результаты расчета для всех стрелков:
Таким образом, расчет производится только в том случае, если все три ячейки 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. Воспользуемся созданной функцией для расчетов:
«Растянем» формулу на остальные ячейки и получим следующие результаты:
Как видно, формулы с большим числом проверок лучше реализовывать в виде пользовательских функций.
По данному вопросу также приходит очень много вопросов. Вот пример задачи, где на первый взгляд нужно использовать функцию ЕСЛИ в Excel, причем не один раз.
За перевыполнение плана от 0 до 5 штук (включительно), работнику выплачивается премия 2%
от 6 до 10 шт. — 4%
от 11 до 15 шт. — 6%
от 16 до 20 шт. — 8%
и так далее
от 95 до 100 шт. — 40%У нас есть данные по выполнению планов работников и нам необходимо рассчитать премию.
Если бы диапазонов расчета премии было бы несколько, то мы могли бы использовать функцию ЕСЛИ, то есть указываем, если перевыполнение больше 0 и меньше или равно 5, то поставить премию 2%, если нет, то снова открываем ЕСЛИ и проверяем если перевыполнение больше 5 и меньше или равно 10, то поставить премию 4%, если нет, то снова открываем ЕСЛИ и так далее.
В нашем примере 20 условий и сделать 20 вложенных ЕСЛИ можно, но не рационально. Предлагаю вам решение данной задачи с использованием функции ВПР с интервальным просмотром равным 1 (приблизительный поиск).
Итак, давайте начнем рассматривать решение данной задачи. Сначала создадим таблицу с условиями по выплате премии. Причем значения ОТ и ДО должны быть в разных столбцах и указываться в качестве цифр.
Далее прописываем функцию ВПР, которая будет искать нужный диапазон и возвращать соответствующую ему премию.
=ВПР( H3 ; $A$2:$C$22 ; 3 ; 1 ), где
H3 — это искомое значение, в нашем случае это выполнение плана определенным сотрудником, для Иванова — это 13 шт.
$A$2:$C$22 — это таблица с условиями по премии. Важно, чтобы первым столбцом данной таблицы были значения ОТ
3 — это третий столбец таблицы с условиями, в котором находятся % премии, который выплачивается сотруднику
1 — это интервальный просмотр, обычно всегда используется цифра 0, для поиска точного значения. Мы же указываем 1, так как нам нужно найти не точное, а ближайшее значение. Допустим, если перевыполнение плана составляет 8 шт., то при интервальном просмотре равным 1, функция будет искать ближайшее число меньше или равное 8, в нашем случае это будет 6, которому соответствует 4% премии
Далее протягиваем формулу на всех сотрудников и меняем формат премии на проценты
Это очень простой и эффективный способ в подобных задачах, которые встречаются очень часто. Многие сразу задумываются о вложенных ЕСЛИ, но как вы видите есть более простой, хоть и не очень очевидный способ с использованием функции ВПР.
Надеюсь, это статья поможет вам решить задачу с большим количеством вложенных ЕСЛИ в Excel.
При работе с формулой ВПР в Excel может сложиться ситуация, когда получить данные нужно не из столбца справа от ключевого столбца, а слева. ВПР так делать не умеет, в таких случаях к нам приходит на помощь сцепка из двух функций СМЕЩ и ПОИСКПОЗ, которые мы сегодня и изучим.
Рассмотрим такой учебный пример. У нас есть база данных с фамилиями, именами, отчествами сотрудников компании, а также годами их рождения. Нам необходимо по части сотрудников сделать сводку, где указать их фамилии и года рождения. Структурно лист с базой данных выглядит вот так:
Как вы видите, мы могли бы по фамилии вытащить год рождения при помощи функции ВПР, но год рождения стоит слева от столбца Фамилия, конечно можно поменять их местами, но в реальной жизни такое решение не всегда возможно, потому будет решать такую задачу. (Кстати, если вы еще не знакомы с функцией ВПР или неуверенно ею пользуетесь, ознакомьтесь со статьей на нашем сайте: "Функция ВПР в Excel"). Для ее решение нам будут нужны функции СМЕЩ и ПОИСКПОЗ. Разберем их синтаксис:
Ссылка - ячейка, от которой рассчитывается смещение по строкам и столбцам.
Смещение по строкам - указываем число, на какое количество строк надо сдвинуться. Положительное число - количество строк вниз, отрицательное число - количество строк вверх.
Смещение по столбцам - указываем число, на какое количество столбцов надо сдвинуться. Положительное число - количество столбцов вправо, отрицательное число - количество столбцов влево.
К примеру для таблицы в скриншоте сверху функция СМЕЩ(C3;-1;1) вернет значение Петрович. Почему именно так? В качестве стартовой ячейки мы указали ячейку С3 (в ней содержится имя Иван). Относительно этой ячейки мы сдвигаемся на одну строку вверх (второй параметр -1) и на один столбец вправо (третий параметр 1).
Искомое значение - это то значение, что мы ищем в таблице
Просматриваемый массив - таблица, где мы ищем искомое значение
Тип сопоставления - как будет искать Excel это значение. Используйте значение 0 для поиска точного результата.
К примеру для таблицы в скринщоте выше функция ПОИСКПОЗ("Петров";B2:B6;0) вернет значение 3. Мы ищем фамилию Петров в перечне фамилий. Она там третья по списку, поэтому формула вернула число 3.
Теперь давайте объединим наши знания и решим задачу по альтернативному ВПР в Экселе.
Итак, для нашего примера формула будет следующая:
Сначала мы используем формулу СМЕЩ, так как мы будет считать смещение относительно конкретной ячейки. Первый параметр - ссылка на заголовок столбца "Фамилия" на листе с базой данных, далее нам нужно узнать на сколько строк нам надо сдвинуться, для этого мы используем функцию ПОИСКПОЗ. В ней в качестве первого параметра указываем фамилию, которую ищем, вторым параметром наш столбец с фамилиями в листе с базой данных. Из полученного значения мы вычитаем 1, что бы убрать эффект заголовка столбца. Последний параметр - смещение на один столбец влево, т.е. -1.
Таким образом, используя нехитрую комбинацию из двух функций мы получили универсальный аналог ВПР, который может возвращать значения, как со столбцов справа, так и слева.
Читайте также: