В статье объясняется, как использовать в одной формуле несколько функций ЕСЛИ, и приводится несколько примеров формул ЕСЛИ с несколькими условиями для наиболее распространенных задач.
Если кто-то спросит вас, какую функцию Excel вы используете чаще всего, каков будет ваш ответ? В большинстве случаев это функция Excel ЕСЛИ. Обычная формула ЕСЛИ, проверяющая одно условие, очень проста и ее легко написать. Но что, если ваши данные требуют более сложных логических проверок с множеством условий? В этом случае вы можете включить несколько функций ЕСЛИ в одну формулу, и это будет называться ЕСЛИ с несколькими условиями или Вложенной функцией ЕСЛИ.
Самым большим преимуществом такого способа является то, что это позволяет вам проверять несколько условий и возвращать разные значения в зависимости от результатов этих проверок. И все это можно делатьв одной формуле.
Microsoft Excel имеет ограничения на уровни вложенности функций ЕСЛИ. В Excel 2003 и более ранних версиях допускалось до 7 уровней. В Excel 2007 и более поздних версиях в одну формулу можно вложить до 64 функций ЕСЛИ.
Однако тот факт, что вы можете вкладывать множество ЕСЛИ в одну формулу, не означает, что вам обязательно следует это делать. Помните, что каждый дополнительный уровень усложняет понимание формулы и поиск возможных ошибок. Если ваша формула имеет слишком много условий и вложенных ЕСЛИ, вы можете оптимизировать ее, используя один из способов, о которых мы поговорим ниже.
Далее в этом уроке вы найдете несколько примеров формул ЕСЛИ с несколькими условиями, а также подробное объяснение их синтаксиса и логики.
ЕСЛИ с несколькими условиями полного совпадения
Вот типичный пример формулы ЕСЛИ с несколькими условиями. Предположим, у вас есть список студентов в столбце A и их сумма баллов по результатам тестирования в столбце B. Вы хотите классифицировать их результаты по следующим условиям:
- Отлично: более 249 баллов.
- Хорошо: от 249 до 200 включительно.
- Удовлетворительно: от 199 до 150 включительно.
- Плохо: до 150 баллов.
А теперь давайте напишем вложенную функцию ЕСЛИ на основе приведенной выше классификации. Хорошей практикой считается начинать с самого максимального условия и затем идти в порядке убывания результатов. Пример формулы ЕСЛИ с четырьмя условиями выглядит следующим образом:
=ЕСЛИ(B2>249; "Отлично"; ЕСЛИ(B2>199; "Хорошо"; ЕСЛИ(B2>149; "Удовлетворительно"; "Плохо")))
И работает именно так, как надо:
Формула ЕСЛИ оценивает логические условия в том порядке, в котором они появляются в формуле. И как только одно из условий принимает значение ИСТИНА, последующие условия больше не проверяются. Другими словами, формула останавливается после первого результата ИСТИНА.
Давайте подробно и пошагово разберем, как работают вложенные функции ЕСЛИ с несколькими условиями в Excel.
На первый взгляд, эта формула может показаться сложной и немного запутанной. Но давайте посмотрим на нее немного под другим углом зрения:
Формула указывает Excel выполнить оценку первого условия и, если оно выполнено, то вернуть значение, указанное в аргументе значение_если_истина.
Если условие первой функции ЕСЛИ не выполняется, проверьте следующее условие, и так далее.
ЕСЛИ( проверяем, если B2>249; если ИСТИНА - возвращаем "Отлично", иначе
ЕСЛИ ( проверяем, если B2>199; если ИСТИНА - возвращаем "Хорошо", иначе
ЕСЛИ ( проверяем, если B2>149; если ИСТИНА - возвращаем "Удовлетворительно", если ложно - возвращаем"Плохо")))
Одна из основных проблем с формулой ЕСЛИ с несколькими условиями — сопоставление пар скобок. То есть, в формуле должно быть одинаковое количество открывающих и закрывающих скобок. Каждая вложенная в формулу функция ЕСЛИ, обрабатывающая определенное условие, должна иметь скобки справа и слева. Если скобки не совпадают, ваша формула не будет работать. К счастью, Microsoft Excel предоставляет несколько возможностей, которые помогут вам сбалансировать круглые скобки при редактировании формулы:
- Если у вас более одного набора круглых скобок, каждая их пара закрашивается своим цветом, чтобы открывающая скобка соответствовала закрывающей.
- Когда вы закрываете скобку, Excel на короткое время выделяет соответствующую ей пару. Тот же эффект выделения жирного шрифта или «мерцания» создается при перемещении по формуле с помощью клавиш со стрелками.
Вложенное ЕСЛИ с арифметическими вычислениями
Вот еще одна типовая задача: цена за единицу товара меняется в зависимости от приобретаемого количества, и ваша цель — написать формулу, которая вычисляет стоимость для любого количества товаров, введенного в определенную ячейку.
Другими словами, ваша формула должна проверять несколько условий и выполнять различные вычисления в зависимости от того, в какой диапазон попадает указанное количество товара.
Эту задачу можно решить, используя функцию ЕСЛИ с множеством условий. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными ЕСЛИ (т. е. на соответствующую цену за единицу).
Предполагая, что пользователь вводит количество в ячейку B7, формула будет следующей:
=B7*ЕСЛИ(B7>=31;110; ЕСЛИ(B7>=20;140; ЕСЛИ(B7>=10;170; ЕСЛИ(B7>0;200; "Проверьте количество"))))
И результат будет выглядеть примерно так, как на скриншоте ниже:
Как вы понимаете, этот пример демонстрирует лишь общий подход подсчета по нескольким условиям, и вы можете легко настроить эту вложенную функцию ЕСЛИ в зависимости от вашей конкретной задачи.
Например, вместо «жесткого кодирования» цен в формуле вы можете ссылаться на ячейки, содержащие эти значения (ячейки от B2 до B5). Это позволит вашим пользователям редактировать исходные данные без необходимости обновлять формулу:
=B7*ЕСЛИ(B7>=31;B5;ЕСЛИ(B7>=20;B4;ЕСЛИ(B7>=10;B3;ЕСЛИ(B7>0;B2;"Проверьте количество"))))
ЕСЛИ с несколькими условиями частичного совпадения
Если вам нужна вложенная формула ЕСЛИ с подстановочными знаками (что означает поиск частичногосовпадения со значением в ячейке), предлагаем примеры ЕСЛИ с несколькими условиями в Excel.
Формула 1
Используйте функцию СЧЕТЕСЛИ, чтобы подсчитать, сколько раз определенный текст появляется в ячейке. Если результат больше нуля, верните соответствующее значение. В противном случае проверяемследующее условие.
ЕСЛИ(СЧЁТЕСЛИ( ячейка ; "*текст1*"); значение1 ; ЕСЛИ(СЧЕТЕСЛИ( ячейка ; "*текст2*"); значение2 ; ЕСЛИ(СЧЁТЕСЛИ( ячейка ; "*текст3*"); значение3 ; "")))
Формула 2
Используйте функцию ПОИСК, чтобы найти положение определенного текста в ячейке. Далее при помощи функции ЕСЛИ создаем условия: когда позиция является числом (то есть, поиск был успешным), верните соответствующее значение. В противном случае проверьте следующее условие.
ЕСЛИ(ЕЧИСЛО(ПОИСК(" текст1" ; ячейка )); значение1 ; ЕСЛИ(ЕЧИСЛО(ПОИСК(" текст2" ; ячейка )); значение2 ; ЕСЛИ(ЕЧИСЛО(ПОИСК(" текст3" ; ячейка )); значение3 ; "" )))
Например, чтобы проверить, содержит ли ячейка A2 «яблоко», «банан» или «апельсин», и затем вернуть соответствующее название фрукта в ячейку B2, вы можете использовать одну из этих формул:
=ЕСЛИ(СЧЁТЕСЛИ(A2; "*яблоко*"); "Яблоко"; ЕСЛИ(СЧЁТЕСЛИ(A2; "*банан*"); "Банан"; ЕСЛИ(СЧЁТЕСЛИ(A2; "*апельсин*"); "Апельсин"; "")))
или
=ЕСЛИ(ЕЧИСЛО(ПОИСК("яблоко"; A2)); "Яблоко"; ЕСЛИ(ЕЧИСЛО(ПОИСК("банан"; A2)); "Банан"; ЕСЛИ(ЕЧИСЛО(ПОИСК("апельсин"; A2)); "Апельсин"; "")))
В соответствии с вашими конкретными потребностями, вы можете при необходимости удлинить цепочку условий в формуле ЕСЛИ для обработки большего количества вариантов.
ЕСЛИ условие – одно из нескольких совпадений (логика ИЛИ)
Чтобы определить ячейки, содержащие хотя бы одно из нескольких искомых значений, используйте рекомендации ниже.
Формула ЕСЛИ+ПОИСК+ЕЧИСЛО
Наиболее очевидным подходом было бы проверять каждый вариант отдельно и заставить функцию ИЛИ возвращать ИСТИНА в логическом условии формулы ЕСЛИ, если найдено хотя бы одно совпадение:
ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК(" строка1 "; ячейка )));ЕЧИСЛО(ПОИСК(" строка2 "; ячейка ))); возвращаемое_значение; "")
Вернемся к нашему списку товаров и предположим, что вы хотите найти те из них, в названии которых встречаются слова «апельсин» или «мандарин». Такие товары обозначим как «цитрусовые».
Вы можете сделать это, используя формулу:
=ЕСЛИ(ИЛИ(ЕЧИСЛО(ПОИСК("апельсин",A2)); ЕЧИСЛО(ПОИСК("мандарин";A2))),"Цитрусовые";"")
Формула работает очень хорошо для нескольких элементов, но это определенно не лучший вариант, если вы хотите проверить множество вариантов совпадения. Например, ваш список цитрусовых будет состоять не из 2, а из 10 наименований.
В этом случае хорошей альтернативой формуле ЕСЛИ с множеством условий будет использование функции СУММПРОИЗВ, как показано в следующем примере.
Формула СУММПРОИЗВ+ЕЧИСЛО+ПОИСК
Если вы имеете дело с несколькими текстовыми строками, поиск в тексте ячейки каждой строки по отдельности сделает вашу формулу ЕСЛИ с несколькими условиями слишком длинной и трудной для чтения. Более элегантным решением было бы встроить ранее рассмотренную нами комбинацию ЕЧИСЛО+ПОИСК в функцию СУММПРОИЗВ и посмотреть, будет ли результат больше нуля:
СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК( текст ; ячейка )))>0
Например, чтобы узнать, содержит ли A2 какие-либо слова, введенные в ячейки Е1:Е3, используйте следующую формулу:
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$1:$E$3;A2)))>0
Или же вы можете создать именованный диапазон, содержащий строки для поиска, или же можете простоуказать слова непосредственно в формуле:
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК({"апельсин";"банан"},A2)))>0
Чтобы сделать результат более удобным и понятным для пользователя, вы можете вложить приведенную выше формулу в функцию ЕСЛИ и возвращать какой-то собственный текст вместо значений ИСТИНА/ЛОЖЬ:
=ЕСЛИ(СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$1:$E$3,A2)))>0;"Да";"")
В этом случае результат будет примерно такой:
Список слов, которые мы будем искать в столбце А, мы поместили в ячейки Е1:Е3. При необходимости, этот диапазон можно легко расширить и добавить в него новые значения.
ЕСЛИ условие – несколько совпадений (логика И)
В ситуациях, когда вы хотите найти ячейки, содержащие все указанные вами варианты, используйте уже знакомую комбинацию ЕЧИСЛО+ПОИСК вместе с ЕСЛИ И:
ЕСЛИ(И(ЕЧИСЛО(ПОИСК(" строка1 "; ячейка ))); ЕЧИСЛО(ПОИСК(" строка2 "; ячейка ))); возвратить_значение ;"")
Например, вы можете найти наименования товаров, содержащие оба слова – «брюки» и «черные», по этой формуле:
=ЕСЛИ(И(ЕЧИСЛО(ПОИСК("брюки";A2));ЕЧИСЛО(ПОИСК("черные";A2))),"Да ";"")
Или вы можете записать искомые слова в отдельные ячейки и ссылаться на эти ячейки в формуле:
=ЕСЛИ(И(ЕЧИСЛО(ПОИСК($D$2;A2));ЕЧИСЛО(ПОИСК($D$3,A2)));"Да ";"")
Результат вы видите на скриншоте ниже.
В качестве альтернативного решения вы можете подсчитать, сколько раз в наименовании товара встречается каждое слово и проверить, больше ли нуля полученные результаты. Если оба слова встречаются хотя бы раз, то возвращаем «Да»:
=ЕСЛИ(И(СЧËТЕСЛИ(A2;"*брюки*")>0; СЧËТЕСЛИ(A2;"*черные*")>0); "Да";"")
Итоговый результат будет точно таким же, как показано на скриншоте выше.
ЕСЛИ с условиями ИЛИ/И
Теперь рассмотрим примеры, когда у нас есть несколько условий, которые должны быть проверены при помощи формулы ЕСЛИ на нескольких ячейках, а не на одной, как в примерах выше.
Если вам нужно оценить несколько наборов различных условий, вы можете объединить эти условия с помощью функции ИЛИ или И, вложить эти функции в формулу ЕСЛИ, а затем вложить формулы ЕСЛИ друг в друга. Выбор И или ИЛИ зависит от того, должны ли выполняться сразу все условия (И), или же достаточно выполнения хотя бы одного из них (ИЛИ).
Несколько условий ЕСЛИ с операторами ИЛИ
Используя функцию ИЛИ, вы можете проверить два или более различных условий в логическом тесте каждой функции ЕСЛИ. Вы получите результат ИСТИНА, если какой-либо (хотя бы один) аргумент ИЛИ оценивается как ИСТИНА. Чтобы увидеть, как это на самом деле работает, рассмотрим следующий пример.
Предположим, у вас есть два столбца продаж: скажем, продажи за октябрь в столбце B и продажи за ноябрь в столбце C. Вы хотите проверить числа в обоих столбцах и определить возможную скидку на основе наибольшего количества продаж.
Другими словами, вы строите формулу со следующей логикой: если продажи в октябре или ноябре превышают 150 единиц, покупатель получает скидку 15%, если продажи в октябре или ноябре превышают или равны 101единице, продавец получает комиссию 11%, и так далее.
Чтобы это сделать, запишите несколько условий ИЛИ, например ИЛИ(B3>150, C3>150), и создайте из нихлогические условия функции ЕСЛИ. В результате вы получите примерно такую формулу:
=ЕСЛИ(ИЛИ(B3>150;C3>150);15%; ЕСЛИ(ИЛИ(B3>=101;C3>=101);11%; ЕСЛИ(ИЛИ(B3>=51;C3>=51);9%; ЕСЛИ(ИЛИ(B3>=1;C3>=1);5%;""))))
И назначьте комиссию на основе более высокой суммы продаж:
Несколько условий ЕСЛИ с операторами И
Если ваши логические тесты включают несколько условий и все эти условия должны выполняться и иметь значение ИСТИНА, объедините и запишите их с помощью функции И.
Например, чтобы назначить скидку на основе наименьшего количества продаж, возьмите приведенную выше формулу и замените ИЛИ на функцию И. Другими словами, вы указываете Excel возвращать 15%, только если продажи и за октябрь и за ноябрь превышают 150 единиц, 11%, если продажи за каждый из этих месяцевпревышают или равны 101 ед., и так далее.
=ЕСЛИ(И(B3>150;C3>150);15%; ЕСЛИ(И(B3>=101;C3>=101);11%;ЕСЛИ(И(B3>=51;C3>=51);9%; ЕСЛИ(И(B3>=1;C3>=1);5%;0))))
В результате наша вложенная формула ЕСЛИ с множеством условий вычисляет комиссию на основе наименьшего числа в столбцах B и C. Если какой-либо столбец пуст (то есть продаж не было), то скидкавообще отсутствует, поскольку ни одно из условий И не выполняется:
Если вы хотите вернуть пустые ячейки вместо нулевых процентов, замените ноль на пустую строку в последнем аргументе:
=ЕСЛИ(И(B3>150;C3>150);15%; ЕСЛИ(И(B3>=101;C3>=101);11%; ЕСЛИ(И(B3>=51;C3>=51);9%; ЕСЛИ(И(B3>=1;C3>=1);5%;””))))
ЕСЛИ с несколькими условиями – варианты и альтернативы
Как видите, чтобы правильно и до конца построить логику формулы ЕСЛИ с несколькими условиями,требуется довольно много времени и внимания. И хотя Microsoft Excel позволяет вкладывать до 64 функций ЕСЛИ в одну формулу, думаю, это не то, что вам действительно хотелось бы делать в своих таблицах.
Итак, если вы (или кто-то другой) смотрите на свою формулу ЕСЛИ с несколькими условиями, пытаясь выяснить, что же она на самом деле делает, то возможно, пришло время пересмотреть свою стратегию и выбрать другой инструмент для решения проблемы вычисления по условиям .
Если вы хотите сравнить каждую ячейку в целевом столбце с каким-то списком и вернуть определенноезначение для каждого найденного совпадения, используйте один из следующих подходов.
Вложенные ЕСЛИ
Логика вложенной формулы ЕСЛИ проста: вы используете отдельную функцию ЕСЛИ для проверки каждого условия и возвращаете разные значения в зависимости от результатов этих проверок.
ЕСЛИ( ячейка =" искомый_текст1 " ; " возвращаем_текст1 " ; ЕСЛИ( ячейка = " искомый_текст2 ";" возвращаем_текст2 " ; ЕСЛИ( ячейка =" искомый_текст3 "; " возвращаем_текст3 "; "")) )
Посмотрим на список товаров в столбце A. Чтобы создать для каждого из них краткий артикул, вы хотите, чтобы их сокращенные обозначения были записаны в столбце B. Чтобы это сделать, используйте следующую формулу:
=ЕСЛИ(A2="банан";"Бн";ЕСЛИ(A2="апельсин";"Ап";ЕСЛИ(A2="яблоко";"Я";"")))
Далее рассмотрим на примерах альтернативы громоздким формулам ЕСЛИ с несколькими условиями.
Формула ПРОСМОТР
Если вы ищете более компактную и более понятную формулу, используйте функцию ПРОСМОТР, в которой искомые и возвращаемые значения предоставляются в виде констант вертикального массива:
ПРОСМОТР( ячейка , {" искомый_текст1 ";" искомый_текст2 ";" искомый_текст3 ";…}, {" результат1 ";" результат2 ";" результат3 ";…})
То есть, у нас есть массив значений, с которыми мы будем сравнивать значение ячейки, и соответствующие им значения, которые будут возвращены в случае совпадения.
Для получения точных результатов обязательно указывайте искомые значения в алфавитном порядке от А до Я.
Рассмотрим пример и вот такую формулу:
=ПРОСМОТР(A2;{"апельсин":"банан":"яблоко"};{"Ап":"Бн":"Я"})
Формула ПЕРЕКЛЮЧ
В ситуациях, когда вы имеете дело с фиксированным набором предопределенных значений, а не диапазонами значений, функция ПЕРЕКЛЮЧ может быть компактной альтернативой сложным вложенным формулам ЕСЛИ с множеством условий:
ПЕРЕКЛЮЧ(ячейка; значение1; результат1; значение2; результат2; ...; [значение по умолчанию])
Функция ПЕРЕКЛЮЧ сравнивает ячейкусо списком значений и возвращает результат, соответствующий первому найденному совпадению.
В случае, если вы хотите определить условные обозначения для товаров, как в предыдущем примере, вы можете использовать эту компактную версию формулы ЕСЛИ с несколькими условиями:
=ПЕРЕКЛЮЧ(A2;"апельсин";"Ап";"банан";"Б";"яблоко";"Я";””)
Или вы можете создать таблицу сокращений, как показано на скриншоте ниже, и использовать ссылки на ячейки вместо жестко закодированных в формуле значений:
=ПЕРЕКЛЮЧ(A2;$D$3;$E$3;$D$2;$E$2;$D$4;$E$4;””)
Обратите внимание, что мы фиксируем все ссылки, кроме первой, знаком $, чтобы предотвратить их изменение при копировании формулы в другие ячейки ниже.
Формула ВПР
При работе с переменным набором условий может быть удобнее ввести список условий в отдельные ячейки и затем получить результат с помощью формулы ВПР, например:
=ВПР(A2; $D$2:$E$4; 2;ЛОЖЬ )
Такая формула может быть весьма полезна, если у вас действительно много условий (например, более 10). Обычная формула ЕСЛИ с множеством условий была бы весьма громоздкой и практически нечитаемой.
На простом примере такая альтернатива формуле ЕСЛИ с несколькими условиями может выглядеть так:
Для получения дополнительной информации об этой формуле см. ВПР для начинающих.
ВПР с приблизительным поиском
Когда вы имеете дело с «шкалами», то есть непрерывными интервалами числовых значений, которые вместе охватывают весь возможный диапазон, в большинстве случаев целесообразно использовать функцию ВПРвместо вложенных ЕСЛИ с большим количеством условий. Пример таких шкал – шкала налогов, шкала скидок или дилерских вознаграждений. Описывать их при помощи формулы ЕСЛИ с несколькими условиями – весьма хлопотное дельце.
Если у нас очень много условий, то использование ВПР вместо ЕСЛИ становится практически единственным альтернативным вариантом. На скриншоте ниже вы видите один из возможных вариантов установления цены в зависимости от количества товара.
Имеется шкала возможных значений количества, где каждому интервалу соответствует определенная цена. Вложенная формула ЕСЛИ с описанием всех возможных условий была бы слишком громоздкой, да и ошибки в ней были бы весьма вероятны. Даже не буду пытаться предложить вам такую огромную формулу ЕСЛИ с 14 условиями, так как нормально пользоваться ею все равно практически невозможно. Вот отличная альтернатива:
=ВПР(E1;A2:B15;2;ИСТИНА)
Использованный в этой формуле приблизительный поиск ВПР означает, что Excel последовательно, начиная с первого, ищет в столбце поиска значение, которое больше искомого. Как только таковое будет найдено, поиск прекращается и возвращается результат, соответствующий последнему обнаруженному значению, которое меньше искомого, и на котором и был остановлен поиск.
В нашем примере, мы последовательно ищем в столбце В последнее число, которое будет меньше искомого значения 115. Как видно, это количество товара 110 единиц. Соответственно и цена для 115 единиц будет 140. Мы берем ее из второго столбца в той же строке, где находится 110.
Важное замечание! Не забывайте, что при использовании приблизительного поиска значения в столбце поиска обязательно должны быть отсортированы в порядке возрастания.
Формула ВЫБОР
Еще одним способом проверки нескольких условий в рамках одной формулы в Excel является использование функции ВЫБОР, которая предназначена для возврата значения из списка на основе позиции этого значения.
Вот вкратце ее синтаксис:
ВЫБОР ( номер_индекса ; значение1 ;значение2;…)
где
- Номер_индекса — номер выбираемого значения. Он должен быть числом от 1 до 254. Индекс можно ввести формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254;
- Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, возвращается значение2 и так далее;
- Если номер_индекса меньше 1 или больше, чем номер последнего значения в формуле, то функция ВЫБОР возвращает ошибку #ЗНАЧ!
- Если номер_индекса представляет собой дробь, то округляется целого в меньшую сторону;
- значение1 ;значение2; ...могут быть числами, текстовыми строками и ссылками на диапазоны ячеек. Если в качестве значений введены конкретные значения, то функция возвращает одно из этих значений в зависимости от индекса, а если в качестве значений использованы ссылки на ячейки, то функция возвращает соответственно ссылки.
Применительно к нашему условному набору данных, формула принимает следующую форму:
=ВЫБОР((B7>=1) + (B7>=11) + (B7>=21) + (B7>30); 200;170; 140;110)
В первом аргументе номер_индекса вы оцениваете все возможные условия и затем складываете результаты. Учитывая, что ИСТИНА равнозначно 1, а ЛОЖЬ равно 0, таким образом вы вычисляете номер позициивозвращаемого значения.
Например, значение в B7 составляет 25 единиц. Для этого значения первые 3 условия возвратят ИСТИНА, а последнее (B2 > 30) - ЛОЖЬ. Таким образом, номер_индекса равен 3, что означает, что возвращается 3-е значение из перечисленных результатов, которое составляет 140.
Совет. Если ни один из логических тестов не является ИСТИНА, номер_индекса будет равен 0, и формула возвратит ошибку #ЗНАЧ!. Простое решение этой проблемы - это обертывание формулы в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВЫБОР((B7>=1) + (B7>=11) + (B7>=21) + (B7>30); 200;170; 140;110);””)
Функция ЕСЛИМН
В Excel 2019 и более поздних версиях Microsoft представила специальную функцию для оценки нескольких условий — функцию ЕСЛИМН.
Формула ЕСЛИМН может обрабатывать до 127 условий, и первый логический тест, который дает значение ИСТИНА, «выигрывает»:
ЕСЛИМН(логический_тест1; значение_если_истина1; [логический_тест2; значение_если_истина2]…)
Давайте вернемся к примеру, который мы рассматривали выше.
В соответствии с приведенным выше синтаксисом формулу с несколькими условиями можно записатьследующим образом:
=ЕСЛИМН(B7>30;110;B7>20;140;B7>10;170;B7>0;200)
Обратите внимание, что функция ЕСЛИМН возвращает ошибку #Н/Д, если ни одно из перечисленных в нейусловий не выполнено. Чтобы избежать этого, вы можете добавить еще одно условие в конец вашей формулы.Оно будет возвращать 0, пустую строку ("") или же любое другое значение, которое вы хотите, если ни одно из предыдущих условий не возвратило значение ИСТИНА:
=ЕСЛИМН(B7>30;110;B7>20;140;B7>10;170;B7>0;200;ИСТИНА;"")
В результате наша формула вернет пустую строку (пустую ячейку) вместо ошибки #Н/Д, если ячейка B7 пуста или содержит текст или отрицательное число.
Примечание. Как и вложенный ЕСЛИ с несколькими условиями, функция ЕСЛИМН возвращает значение, соответствующее первому найденному условию, которое оценивается как ИСТИНА. Поэтому порядок следования логических условий в формуле ЕСЛИМН имеет значение.
Для получения дополнительной информации см. статью функция ЕСЛИМН вместо ЕСЛИ с несколькими условиями.
Условия для разных диапазонов чисел.
Продвинутые пользователи Excel, знакомые с формулами массивов , могут использовать эту формулу , которая по сути делает то же самое, что и вложенная функция ЕСЛИ с множеством условий, описанная выше. Хотя формулу массива гораздо сложнее понять, не говоря уже о написании, у нее есть одно неоспоримое преимущество — вы указываете диапазон ячеек, содержащих ваши условия, а не ссылаетесь на каждое условие индивидуально. Это делает формулу более гибкой, и если ваши пользователи изменят какое-либо из существующих условий или добавят новое, вам нужно будет обновить только одну ссылку на диапазон в формуле.
Вот типичная ситуация для многих поставщиков - цена за единицу варьируется в зависимости от приобретенного количества, и ваша цель - написать формулу, которая вычисляет общую сумму для любогоколичества, введенного в ячейке В8.
Эту задачу можно легко выполнить, используя следующую вложенную формулу ЕСЛИ с пятью условиями:
=B8*ЕСЛИ(B8>=41;B6; ЕСЛИ(B8>=31; B5; ЕСЛИ(B8>=21; B4; ЕСЛИ( B8>=11; B3; ЕСЛИ(B8>=1; B2; "")))))
Однако этот подход имеет значительные ограничения. Поскольку формула ссылается на каждую цену в ячейках от B2 до B6 по отдельности, вам придется обновить ее, как только ваши пользователи изменят любой из существующих диапазонов или добавят новый диапазон количества.
Чтобы сделать формулу более гибкой, используйте массивы, а не отдельные ячейки. В этом случае, независимо от того, сколько значений будет изменено, добавлено или удалено, вам нужно будет обновить только одну ссылку на диапазон в формуле.
Для примера, показанного на скриншоте выше, подойдет эта формула:
=СУММ(B8*(B2:B6) * (--(B8>=ЗНАЧЕН(ЛЕВСИМВ(A2:A6;НАЙТИ(" ";A2:A6))))) *(--(B8<=ЗНАЧЕН(ПРАВСИМВ(A2:A6;ДЛСТР(A2:A6) - НАЙТИ(" до ";A2:A6)-ДЛСТР(" до" ))))))
Чтобы формула работала правильно, обязательно проверьте эти две вещи:
- Значения в A2:A6 должны представлять собой непрерывный диапазон, чтобы ни одно значение не было пропущено.
- Все количества в A2:A6 должны быть выглядеть как "X до Y", потому что этот шаблон жестко записан в формуле. Если ваши количества введены по-другому, кажем, "1 - 10", то замените "до" на " -" в формулевыше.
Если вы хотите отобразить сообщение "Вне диапазона", когда значение количества в B8 находится за пределами описанных диапазонов, добавьте еще одно условие ЕСЛИ:
=ЕСЛИ(И(B8>=ЗНАЧЕН(ЛЕВСИМВ(A2;НАЙТИ(" ";A2))); B8<=ЗНАЧЕН(ПРАВСИМВ(A6;ДЛСТР(A6)-НАЙТИ(" до ";A6)-ДЛСТР(" до" )))); СУММ(B8*(B2:B6)*(--(B8>= ЗНАЧЕН(ЛЕВСИМВ(A2:A6; НАЙТИ(" ";A2:A6)))))*(--(B8<= ЗНАЧЕН(ПРАВСИМВ(A2:A6;ДЛСТР(A2:A6)-НАЙТИ(" до ";A2:A6)-ДЛСТР(" до" )))))); "Вне диапазона")
Этим мы дополнительно проверяем, находится ли количество в ячейке В8 между первым числом в А2 и последним числом в А6. Другими словами, проверяем это условие: И(B8>=1, B8<=50)
Несколько условий ЕСЛИ в Excel – советы и рекомендации
Записывайте текст и числа по-разному
Записывая логические условия формулы ЕСЛИ, помните, что к тексту и числам следует относиться по-разному — всегда заключайте текстовые значения в двойные кавычки, но никогда не заключайте в кавычки числа:
Верно: =ЕСЛИ(B2>259, "Отлично",…)
Неверно: =ЕСЛИ(B2>"259", "Отлично",…)
Логическая проверка второй формулы вернет ЛОЖЬ, даже если значение в B2 больше 259. Почему? Потому что 259 — это число, а «259» — текстовая строка из цифр, а это две разные вещи.
Добавляйте пробелы или разрывы строк, чтобы облегчить чтение вложенных ЕСЛИ.
При построении формулы с несколькими вложенными уровнями ЕСЛИ вы можете сделать логику формулы более понятной, разделив различные функции ЕСЛИ пробелами или разрывами строк. Excel не обращает внимания на лишние пробелы в формуле, поэтому вы можете не беспокоиться о ее искажении.
Чтобы переместить определенную часть формулы на следующую строку, просто в режиме редактирования установите курсор на то место, где вы хотите вставить разрыв строки, и нажмите ALT + ENTER.Затем расширьте вниз панель формул настолько, насколько это необходимо, и вы увидите, что вашу функцию ЕСЛИ с множеством условий стало намного проще читать.
Вот как вы можете использовать формулу ЕСЛИ с несколькими условиями в Excel.
Как вы видите, Microsoft Excel предоставляет несколько хороших альтернатив как можно описать множество условий в формуле ЕСЛИ, и, надеюсь, это руководство дало вам некоторые подсказки о том, как использовать это в своих таблицах.
Вот еще несколько статей о работе с условиями, которые могут быть вам полезны:
