Несколько если в одной формуле excel: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Содержание

Примеры формулы ЕСЛИ с несколькими условиями в Excel

Одиночные условия функции ЕСЛИ, которые проверяют в первом аргументе выполняется ли условие, можно объединять с последующим условием во втором и/или в третьем аргументе (в зависимости от поставленной задачи). Данное решение в 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 где была старая формула вводим новую формулу с функцией ВПР:

Скачать примеры проверки значений по нескольким условиям

В результате нам не нужна функция ЕСЛИ для проверки значений по нескольким условиям.

Все должно быть просто и красиво ведь в этом и заключается магия!

Функции Google таблиц IF (ЕСЛИ) и IFS (ЕСЛИМН)

Функции и Формулы

11.11.2022
Автор статьи:
Александр Солунин

Июл

Функция IF (ЕСЛИ) в Google таблицах — на практике одна из самых востребованных. В зависимости от условий она возвращает указанный результат (значение или другую формулу со своими параметрами).

Новички без особых знаний (математика за 3 класс) с помощью данной функции могут построить простые отчеты, которые уже будут удовлетворять потребности в визуальном восприятии результата работы.

Функция IF (ЕСЛИ) в Google таблицах

Логическая функция имеет следующий синтаксис:

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])

Логическое_выражение – это искомое проверяемое условие. Например: B2<100. Если значение в ячейке B2 действительно меньше 100, то в памяти гугл таблиц генерируется ответ «ИСТИНА» и функция возвращает то, что указано следующим параметром. Если это не так, в памяти генерируется ответ «ЛОЖЬ» и возвращается значение из последнего указанного нами параметра.

Значение_если_истина – значение или любая другая формула, которая отображается при наступлении указанного в первом параметре события.
[Значение_если_ложь] – это альтернативное значение или любая другая формула, которая отображается при невыполнении нашего условия (первый проверяемый параметр). Данный параметр не обязателен к заполнению. В этом случае при наступлении альтернативного события функция вернет значение «ЛОЖЬ». Для удобства все необязательные к заполнению параметры в формулах google таблиц помечаются в квадратными скобками «[…]».

Примеры функции IF (ЕСЛИ) в Google таблицах

Функция IF (ЕСЛИ) практически всегда используется в связке с другими функциями. Чаще всего она является составным элементом в сложной аналитической структуре. Но ее можно использовать и в простейших вариантах.

Простой пример функции IF (ЕСЛИ)

Рассмотрим очень простой пример проверки продаж отдельных продуктов. По условию, нам нужно пометить, какие продукты хорошо продаются, а какие нет. Критерий определения — больше 30 кг в день. Если в день мы продаем больше 30 кг — Хорошо, если меньше — Плохо.

В таком случае синтаксис формулы будет выглядеть так:

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])

 =IF(B2>30;"Хорошо";"Плохо")

Все текстовые выражения помещаются в кавычки (слова, числа в виде текста и т. д.).

Пошагово: IF (ЕСЛИ) мы продали моркови больше 30 кг за 1 день, то выводится значение_если_истина — «Хорошо» (да, мы продали 35 кг > 30 кг), если меньше — «Плохо» ([значение_если_ложь] — альтернативное значение, если условие не соблюдается).

Более сложный пример функции IF (ЕСЛИ)

Допустим, у нас на складе есть определенное количество овощей и нам нужно рассчитать сколько товаров останется после недели торговли. Для этого нам нужно умножить значения дневных продаж на 7 (дни недели), а зачем вычесть полученный результат из имеющихся складских остатков.

 =C2-B2*7

Мы столкнулись с ситуацией, когда математический результат продаж наших товаров оказался отрицательным, а все понимают, что остатков на складе не может быть меньше нуля. Чтобы прогноз был корректным, нужно заменить отрицательные значения нулями. С такой задачей прекрасно справится функция IF (ЕСЛИ). Она проверит результат вычислений и если он окажется меньше нуля — выдаст ответ «0», а если больше — отобразит результат вычислений.

 =IF(логическое_выражение; значение_если_истина; [значение_если_ложь])

 =IF(C2-B2*7<0;0;C2-B2*7)

Таким образом мы получили корректный результат и в прогнозе нет больше отрицательных значений.

Функция IF (ЕСЛИ) в Google таблицах — несколько условий

Чаще всего на практике требуется применять формулу где условий более чем 2 — 3, 4, 5… В таком случае можно использовать функцию, но уже вкладывая одну в одну, как матрешку, указывая все условия по очереди.

Рассмотрим пример многомерной функции IF (ЕСЛИ) на живом примере начисления премии по KPI продаж. Система начислений следующая:

Если план выполнен менее чем на 90% — премия не начисляется.
90% — 95% — премия 10%.
95% — 100% — премия 20%.
Если план перевыполнен — премия 30%.

Фактически у нас 4 конкретных условия, которые могут быть указаны по следующей логической структуре:

если выполняется первое условие, то выводится первый вариант, в противном случае переход ко второму условию;
если выполняется второе условие, то выводится второй вариант, в противном случае переход к третьему условию;
если выполняется третье условие, то выводится третий вариант, в противном случае переход к четвертому условию;
если выполняется четвертое условие, то выводится четвертый вариант, а в качестве завершения логической структуры указывается [Значение_если_ложь].

Максимальное количество таких условий = 256. В конце формулы указывается [Значение_если_ложь], для которого не выполняется ни одно из перечисленных ранее условий. В итоге формула имеет следующий синтаксический вид:

 =IF(логическое_выражение; значение_если_истина; IF(логическое_выражение; значение_если_истина; IF(логическое_выражение; значение_если_истина; [Значение_если_ложь])))

 =IF(B2<0,9;0;IF(B2<0,95;0,1;IF(B2<1;0,2;0,3)))

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

При создании формульной логической цепочки очень легко запутаться. Новичкам рекомендуется либо абстрактно принимать переменные (условия) за «Х», «Y», «Z», …, либо смотреть на всплывающие подсказки. Плюсом, система сама подсвечивает разные переменные и условия, а так же, доставляет закрывающие скобки формулы функций.

Функция IFS (ЕСЛИМН) в Google таблицах

В более поздних версиях Excel и Google таблицах (с 2016 года) появилась функция IFS (ЕСЛИМН). Она фиксирует сразу множество условий, что значительно оптимизирует серверную нагрузку (формулы считаются быстрее и наши таблицы не тормозят так, как это было бы, если бы сервер просчитывал каждое вложенное условие IF (ЕСЛИ)) и разгружает наше абстрактное мышление, упраздняя повторяющиеся простые условия IF (ЕСЛИ). По сути, это та же функция ЕСЛИ, только в разы упрощенная, к тому же, максимальное число условий увеличилось до 512.

В качестве примера возьмем имеющиеся значения из примеров выше и рассчитаем начисление премий уже более простой, но не менее функциональной функцией:

Первый вариант

 =IFS(B2<0,9;0;B2<0,95;0,1;B2<1;0,2;B2>=1;0,3)

Второй вариант

 =IFS(B2<0,9;0;B2<0,95;0,1;B2<1;0,2;TRUE;0,3)

Формулы функции IFS (ЕСЛИМН) выполняют одно и то же действие, отличаясь завершающим последним обязательным (в отличии от обычной функции IF (ЕСЛИ)) аргументом. В первом варианте мы конкретно указали какое значение у аргумента должно выводиться, если выполняется 4 условие функции. Можно избежать конкретного действия, прописав значение «TRUE» (ИСТИНА), тогда будет выводиться последнее альтернативное значение 4 условия.

P.S.: первый вариант мне нравится больше.

Копировать Google таблицу с примерами

Вы познакомились с функциями IF (ЕСЛИ) и IFS (ЕСЛИМН), увидели где их можно применить на практике, и, я уверен, сможете применить их в своей работе. Поделитесь, пожалуйста в комментариях своими идеями или случаями применения рассматриваемых функций.

Вложенный ЕСЛИ в Excel — формула с несколькими условиями

В этом руководстве объясняется, как использовать несколько ЕСЛИ в Excel, и приводится несколько примеров вложенных ЕСЛИ для наиболее распространенных задач.

Если кто-нибудь спросит вас, какую функцию Excel вы используете чаще всего, каков будет ваш ответ? В большинстве случаев это функция Excel ЕСЛИ. Обычная формула If, проверяющая одно условие, очень проста и удобна в написании. Но что, если ваши данные требуют более сложных логических тестов с несколькими условиями? В этом случае вы можете включить несколько функций ЕСЛИ в одну формулу, и эти несколько операторов Если вызываются Excel Вложенный IF . Самым большим преимуществом вложенного оператора If является то, что он позволяет вам проверять более одного условия и возвращать разные значения в зависимости от результатов этих проверок, и все это в одной формуле.

Microsoft Excel имеет ограничения до уровней вложенных ЕСЛИ . В Excel 2003 и более ранних версиях допускается до 7 уровней. В Excel 2007 и более поздних версиях в одну формулу можно вложить до 64 функций ЕСЛИ.

Далее в этом руководстве вы найдете несколько вложенных примеров Excel вместе с подробным объяснением их синтаксиса и логики.

Пример 1. Классическая вложенная формула ЕСЛИ

Вот типичный пример Excel If с несколькими условиями. Предположим, у вас есть список студентов в столбце A и их экзаменационные баллы в столбце B, и вы хотите классифицировать баллы со следующими условиями:

Отлично: более 249
Хорошее: от 249 до 200 включительно
Удовлетворительно: от 199 до 150 включительно
Бедные: до 150 лет

А теперь давайте напишем вложенную функцию ЕСЛИ на основе вышеуказанных критериев. Считается хорошей практикой начинать с самого важного условия и максимально упрощать свои функции. Наша вложенная формула ЕСЛИ в Excel выглядит следующим образом:

=ЕСЛИ(В2>249, "Отлично", ЕСЛИ(В2>=200, "Хорошо", ЕСЛИ(В2>150, "Удовлетворительно", "Плохо")))

И работает как надо:

Понимание вложенной логики ЕСЛИ Excel

Я слышал, что некоторые люди говорят, что множественное число Excel сводит их с ума 🙂 Попробуйте посмотреть на это под другим углом:

Что формула на самом деле говорит Excel, так это оценить logical_test первой функции ЕСЛИ и, если условие выполнено, вернуть значение, предоставленное в значение_если_истина аргумент. Если условие 1-й функции If не выполняется, то проверяется 2-я инструкция If и так далее.

IF( проверить, если B2>=249, если верно — вернуть «Отлично», или иначе
IF( проверить, если B2>=200, если верно — вернуть «Хорошо», или else
IF( проверить, если B2>150, если правда — вернуть «Удовлетворительно», если ложь —
вернуть «Плохо»)))

Пример 2. Множественное ЕСЛИ с арифметическими вычислениями

Вот еще одна типичная задача: цена за единицу варьируется в зависимости от указанного количества, и ваша цель — написать формулу, вычисляющую общую цену для любого количества товаров, введенных в определенную ячейку. Другими словами, ваша формула должна проверять несколько условий и выполнять различные вычисления в зависимости от того, в какой диапазон сумм попадает указанное количество:

Количество	Цена за единицу
от 1 до 10	20 долларов
от 11 до 19	18 долларов
от 20 до 49	$16
от 50 до 100	$13
Более 101	12 долларов

Эту задачу также можно выполнить, используя несколько функций ЕСЛИ. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными ЕСЛИ (т. е. соответствующую цену за единицу).

Предполагая, что пользователь вводит количество в ячейку B8, формула выглядит следующим образом:

=В8*ЕСЛИ(В8>=101, 12, ЕСЛИ(В8>=50, 13, ЕСЛИ(В8>=20, 16, ЕСЛИ( В8>=11, 18, ЕСЛИ(В8>=1, 20, "")))))

И результат будет выглядеть примерно так:

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

Например, вместо «жесткого кодирования» цен в формуле вы можете ссылаться на ячейки, содержащие эти значения (ячейки с B2 по B6). Это позволит вашим пользователям редактировать исходные данные без необходимости обновлять формулу:

=В8*ЕСЛИ(В8>=101,В6, ЕСЛИ(В8>=50, В5, ЕСЛИ(В8>=20, В4, ЕСЛИ( В8>=11, В3, ЕСЛИ(В8>=1, В2, "")))))

Или вы можете включить дополнительные функции ЕСЛИ, которые фиксируют верхнюю, нижнюю или обе границы диапазона суммы. Когда количество выходит за пределы диапазона, формула будет отображать сообщение «вне диапазона». Например:

=ЕСЛИ(ИЛИ(B8>200,B8<1), "Количество вне диапазона", B8*IF(B8>=101,12, IF(B8>=50, 13, IF(B8>=20) , 16, ЕСЛИ( В8>=11, 18, ЕСЛИ(В8>=1, 20, ""))))))

Описанные выше вложенные формулы ЕСЛИ работают во всех версиях Excel. В Excel 365 и Excel 2021 вы также можете использовать функцию IFS для той же цели.

Опытные пользователи Excel, знакомые с формулами массива, могут использовать эту формулу, которая в основном делает то же самое, что и описанная выше вложенная функция ЕСЛИ. Хотя формулу массива гораздо сложнее понять, не говоря уже о написании, у нее есть одно неоспоримое преимущество — вы указываете диапазон ячеек, содержащих ваши условия, а не ссылаетесь на каждое условие по отдельности. Это делает формулу более гибкой, и если ваши пользователи изменят какое-либо из существующих условий или добавят новое, вам нужно будет обновить только одну ссылку на диапазон в формуле.

Вложенный ЕСЛИ в Excel — советы и рекомендации

Как вы только что убедились, в использовании нескольких ЕСЛИ в Excel нет ничего сложного. Следующие советы помогут вам улучшить вложенные формулы ЕСЛИ и предотвратить распространенные ошибки.

Вложенные ограничения IF

В Excel 2007 — Excel 365 можно вложить до 64 функций ЕСЛИ. В более ранних версиях Excel 2003 и более ранних можно использовать до 7 вложенных функций ЕСЛИ. Однако тот факт, что вы можете вложить множество ЕСЛИ в одну формулу, не означает, что вы должны это делать. Имейте в виду, что каждый дополнительный уровень усложняет понимание формулы и устранение неполадок. Если в вашей формуле слишком много вложенных уровней, вы можете оптимизировать ее, используя один из этих вариантов.

Порядок вложенных функций ЕСЛИ имеет значение

Вложенная функция ЕСЛИ в Excel оценивает логические проверки в порядке их появления в формуле, и как только одно из условий оценивается как ИСТИНА, последующие условия не проверяются. Другими словами, формула останавливается после первого результата TRUE.

Посмотрим, как это работает на практике. При значении B2, равном 274, приведенная ниже вложенная формула ЕСЛИ оценивает первую логическую проверку (B2>249) и возвращает «Отлично», поскольку эта логическая проверка ИСТИНА:

=ЕСЛИ(В2>249, "Отлично", ЕСЛИ(В2>=200, "Хорошо", ЕСЛИ(В2>150, "Удовлетворительно", "Плохо")))

Теперь поменяем порядок функций ЕСЛИ на обратный:

=ЕСЛИ(В2>150, "Удовлетворительно", ЕСЛИ(В2>200, "Хорошо", ЕСЛИ(В2>249, "Отлично", "Плохо")))

Формула проверяет первое условие, и поскольку 274 больше 150, результат этой логической проверки также ИСТИНА. Следовательно, формула возвращает «удовлетворительно» без проверки других условий.

Видите ли, изменение порядка функций ЕСЛИ меняет результат:

Оцените логику формулы

Для пошагового просмотра логической последовательности вашей вложенной формулы ЕСЛИ используйте функцию Оценить формулу, расположенную на вкладке Формула в группе Аудит формул . Подчеркнутое выражение — это та часть, которая в данный момент оценивается, и нажатие кнопки Оценить покажет вам все этапы процесса оценки.

Например, оценка первого логического теста вложенной формулы ЕСЛИ, показанная на снимке экрана ниже, будет выглядеть следующим образом: B2>249; 274>249; ИСТИННЫЙ; Отличный.

Баланс скобок вложенных функций ЕСЛИ

Одной из основных проблем с вложенными ЕСЛИ в Excel является сопоставление пар скобок. Если скобки не совпадают, ваша формула не будет работать. К счастью, Microsoft Excel предоставляет несколько функций, которые могут помочь вам сбалансировать круглые скобки при редактировании формулы:

Если у вас более одного набора скобок, пары скобок затеняются разными цветами, чтобы открывающая скобка совпадала с закрывающей.
Когда вы закрываете скобки, Excel ненадолго выделяет совпадающую пару. Тот же эффект выделения полужирным шрифтом или «мерцания» создается при перемещении по формуле с помощью клавиш со стрелками.

Дополнительные сведения см. в разделе Сопоставление пар скобок в формулах Excel.

Обрабатывайте текст и числа по-разному

При построении логических тестов ваших вложенных формул ЕСЛИ помните, что текст и числа должны обрабатываться по-разному — всегда заключайте текстовые значения в двойные кавычки, но никогда не заключайте кавычки вокруг чисел:

Правильно: =ЕСЛИ(B2>249, «Отлично»,…)

Неправильно: =ЕСЛИ(B2>»249″, «Отлично»,…)

Логическая проверка второй формулы вернет ЛОЖЬ, даже если значение в ячейке B2 больше 249.. Почему? Потому что 249 — это число, а «249» — числовая строка, а это две разные вещи.

Добавьте пробелы или разрывы строк, чтобы упростить чтение вложенных ЕСЛИ

При построении формулы с несколькими вложенными уровнями ЕСЛИ можно сделать логику формулы более понятной, разделив различные функции ЕСЛИ пробелами или разрывами строк. Excel не заботится о дополнительных пробелах в формуле, поэтому вы можете не беспокоиться о его искажении.

Чтобы переместить определенную часть формулы на следующую строку, просто щелкните место, где вы хотите вставить разрыв строки, и нажмите Alt + Enter. Затем разверните панель формул настолько, насколько это необходимо, и вы увидите, что ваша вложенная формула ЕСЛИ стала намного проще для понимания.

Альтернативы вложенному ЕСЛИ в Excel

Чтобы обойти ограничение в семь вложенных функций ЕСЛИ в Excel 2003 и более ранних версиях и сделать ваши формулы более компактными и быстрыми, рассмотрите возможность использования следующих альтернатив вложенным функциям ЕСЛИ Excel.

Чтобы проверить несколько условий и вернуть разные значения на основе результатов этих тестов, вы можете использовать функцию ВЫБОР вместо вложенных ЕСЛИ.
Создайте справочную таблицу и используйте ВПР с приблизительным совпадением, как показано в этом примере: ВПР вместо вложенного ЕСЛИ в Excel.
Используйте ЕСЛИ с логическими функциями ИЛИ/И, как показано в этих примерах.
Используйте формулу массива, как показано в этом примере.
Объедините несколько операторов IF с помощью функции CONCATENATE или оператора объединения (&). Пример формулы можно найти здесь.
Для опытных пользователей Excel лучшей альтернативой использованию нескольких вложенных функций ЕСЛИ может быть создание пользовательской функции рабочего листа с помощью VBA.

Вот как вы используете формулу If в Excel с несколькими условиями. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.

Практическая рабочая тетрадь для скачивания

Вложенные операторы If Excel (файл .xlsx)

Вас также может заинтересовать

Как использовать вложенные функции ЕСЛИ (WS)

В этом учебном пособии по Excel объясняется, как вложить функцию ЕСЛИ в Excel с синтаксисом и примерами.

Описание

Функция ЕСЛИ — это встроенная функция Excel, относящаяся к категории Логическая функция . Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ЕСЛИ можно ввести как часть формулы в ячейку рабочего листа.

В одну формулу Excel можно вложить несколько функций ЕСЛИ. Вы можете вложить до 7 функций IF для создания сложного оператора IF THEN ELSE.

СОВЕТ: Если у вас Excel 2016, попробуйте новую функцию ЕСЛИ вместо вложения нескольких функций ЕСЛИ.

Синтаксис

Синтаксис вложения функции ЕСЛИ:

 ЕСЛИ( условие1, значение_если_истина1, ЕСЛИ( условие2, значение_если_истина2, значение_если_ложь2 ))

Это будет эквивалентно следующему оператору IF THEN ELSE:

 ЕСЛИ условие1 ТО
   значение_если_истина1
ИНАЧЕ ЕСЛИ условие2 ТО
   значение_если_истина2
ЕЩЕ
   значение_если_ложь2
END IF

Параметры или аргументы

условие: Значение, которое вы хотите проверить.
значение_если_истина: Значение, которое возвращается, если условие оценивается как ИСТИНА.
значение_если_ложь: Значение, которое возвращается, если условие оценивается как ЛОЖЬ.

Пример (в качестве функции рабочего листа)

Давайте рассмотрим пример, чтобы увидеть, как можно использовать вложенную функцию ЕСЛИ, и изучить, как использовать вложенную функцию ЕСЛИ в качестве функции рабочего листа в Microsoft Excel:

На основе приведенной выше электронной таблицы Excel , будут возвращены следующие примеры вложенных ЕСЛИ:

 =IF(A1="10x12",120,IF(A1="8x8",64,IF(A1="6x6",36)))
  Результат:  120
=ЕСЛИ(A2="10x12",120,ЕСЛИ(A2="8x8",64,ЕСЛИ(A2="6x6",36)))
  Результат:  64
=ЕСЛИ(A3="10x12",120,ЕСЛИ(A3="8x8",64,ЕСЛИ(A3="6x6",36)))
  Результат:  36

Часто задаваемые вопросы

Вопрос: В Microsoft Excel мне нужно написать формулу, которая работает следующим образом:

Если (ячейка A1) меньше 20, то умножить на 1,
Если да больше или равно 20, но меньше 50, то умножить на 2
Если больше или равно 50 и меньше 100, то умножить на 3
А если больше или равно 100, то умножить на 4

Ответ: Для этого можно написать вложенный оператор IF. Например:

 =ЕСЛИ(A1<20, A1*1, ЕСЛИ(A1<50, A1*2, ЕСЛИ(A1<100, A1*3, A1*4)))

Вопрос:В Excel мне нужен формула в ячейке C5, которая делает следующее:

ЕСЛИ A1+B1 <= 4, вернуть 20 долларов
ЕСЛИ A1+B1 > 4, но <= 9, вернуть 35 долларов
ЕСЛИ A1+B1 > 9, но <= 14, вернуть 50 долларов
ЕСЛИ A1+B1 > 15, вернуть $75

Ответ:В ячейке C5 вы можете написать вложенный оператор ЕСЛИ, который использует функцию И следующим образом:

 =ЕСЛИ((А1+В1)<=4,20,ЕСЛИ( И((А1+В1)>4,(А1+В1)<=9),35,ЕСЛИ(И((A1+B1)>9,(A1+B1)<=14),50,75)))

Вопрос: В Microsoft Excel мне нужна формула для следующего:

ЕСЛИ ячейка A1= PRADIP, тогда значение будет 100
ЕСЛИ ячейка A1= PRAVIN, тогда значение будет 200
ЕСЛИ ячейка A1= PARTHA, тогда значение будет 300
ЕСЛИ ячейка A1= PAVAN, тогда значение будет 400

Ответ: Вы можете напишите оператор ЕСЛИ следующим образом:

 =ЕСЛИ(А1="ПРАДИП",100,ЕСЛИ(А1="ПРАВИН",200,ЕСЛИ(А1="ПАРТА",300,ЕСЛИ(А1="ПАВАН",400, ""))))

Вопрос: В Microsoft Excel я хочу рассчитать следующее, используя формулу «если»:

, если A1<100 000, то A1*. 1%, но не менее 25
и если A1>1 000 000, то A1*.01%, но не более 5000

Ответ: Вы можете написать вложенный оператор ЕСЛИ, который использует функцию МАКС и функцию МИН, следующим образом: 5000,A1*0.01%),""))

Вопрос: У меня Excel 2000. Если ячейка A2 больше или равна 0, то прибавить к C1. Если ячейка B2 больше или равна 0, вычтите из C1. Если и A2, и B2 пусты, то равно C1. Можете ли вы помочь мне с функцией ЕСЛИ на этом?

Ответ: Вы можете написать вложенный оператор ЕСЛИ, который использует функцию И и функцию ЕПУСТО следующим образом: (ПУСТО(B2)=ЛОЖЬ,B2>=0),C1-B2, ЕСЛИ(И(ПУСТО(A2)=ИСТИНА, ЕСПУСТО(B2)=ИСТИНА),C1,"")))

Вопрос: Как я бы написал это уравнение в Excel? Если D12<=0, то D12*L12, Если D12 > 0, но <=600, то D12*F12, Если D12 >600, то ((600*F12)+((D12-600)*E12))

Ответ : Вы можете написать вложенный оператор IF следующим образом:

 =ЕСЛИ(D12<=0,D12*L12,ЕСЛИ(D12>600,((600*F12)+((D12-600)*E12)),D12*F12))

Вопрос:У меня есть прочитал вашу статью о вложенных ЕСЛИ в Excel, но я все еще не могу понять, что не так с моей формулой, не могли бы вы помочь? Вот что у меня есть:

 =ЕСЛИ(63<=A2<80,1,ЕСЛИ(80<=A2<95,2,ЕСЛИ(A2=>95,3,0)))

Ответ: Самый простой способ написать вложенный оператор IF на основе описанной выше логики:

 =IF(A2>=95,3,IF(A2>=80,2,IF(A2>=63,1,0)))

Эта формула будет выполнять следующие действия:

Если A2 >= 95, формула вернет 3 (первая функция ЕСЛИ)
Если A2 < 95 и A2 >= 80, формула вернет 2 (вторая функция ЕСЛИ)
Если A2 < 80 и A2 >= 63, формула вернет 1 (третья функция ЕСЛИ)
Если A2 < 63, формула вернет 0

Вопрос: Я новичок в мире Excel, и я пытаюсь чтобы выяснить, как настроить правильную формулу для ячейки If/then.

Я пытаюсь:

Если значение B2 от 1 до 5, умножьте E2 на 0,77
Если значение B2 от 6 до 10, умножьте E2 на 0,735
Если значение B2 от 11 до 19, умножьте E2 на 0,7
Если значение B2 составляет от 20 до 29, затем умножьте E2 на 0,675
Если значение B2 равно 30–39, умножьте E2 на 0,65

функциональные учебники здесь, но я не могу понять это правильно.

Ответ: Чтобы написать формулу ЕСЛИ, вам нужно объединить несколько функций ЕСЛИ вместе с функцией И.

Следующая формула должна работать для того, что вы пытаетесь сделать:

 =ЕСЛИ(И(B2>=1, B2<=5), E2*0,77, ЕСЛИ(И(B2>=6, B2<=10) ), E2*0,735, ЕСЛИ(И(B2>=11, B2<=19), E2*0,7, ЕСЛИ(И(B2>=20, B2<=29), E2*0,675, ЕСЛИ(И(B2> =30, B2<=39), E2*0,65,"")))))

В качестве последнего компонента формулы вам нужно решить, что делать, если ни одно из условий не выполняется. В этом примере мы вернули «», когда значение в ячейке B2 не соответствует ни одному из приведенных выше условий ЕСЛИ.

Вопрос: У меня проблема с функцией вложенного ИЛИ:

Моя неработающая формула:

 =ЕСЛИ(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9 /L7,IF(C9=4,0,K9/N7))))

В ячейке C9 я могу ввести 1, 2, 3, 4 или 0. Проблема в том, как написать "или " условие, когда в столбце C существует "4 или 0". Если в столбце C существуют условия "4 или 0", я хочу, чтобы столбец K был разделен на столбец N, а ответ был помещен в столбец M и связанную с ним строку

Ответ:Вы должен иметь возможность использовать функцию ИЛИ в вашей функции ЕСЛИ для проверки C9=4 ИЛИ C9=0 следующим образом:

 =IF(C9=1,K9/J7,IF(C9=2,K9/J7,IF(C9=3,K9/L7,IF(OR(C9=4, C9=0),K9/N7))))

Эта формула вернет K9/N7, если ячейка C9 равна 4 или 0.

Вопрос: В Excel я пытаюсь создать формулу, которая будет отображать следующее :

Если столбец B = Росс и столбец C = 8, то в ячейке AB этой строки я хочу, чтобы он отображал 2013 год. Если столбец B = Block и столбец C = 9, то в ячейке AB этой строки я хочу, чтобы он отображал 2012 год.

Ответ: Вы можете создать формулу Excel, используя вложенные функции ЕСЛИ с функцией И.

 =ЕСЛИ(И(B1="Росс",C1=8),2013,ЕСЛИ(И(B1="Блок",C1=9),2012,""))

Эта формула вернет 2013 как числовое значение, если B1 — «Росс», а C1 — 8, или 2012 в качестве числового значения, если B1 — «Блок», а C1 — 9. В противном случае возвращается пустое значение, обозначенное знаком «».

Вопрос: В Excel у меня действительно проблема с поиском правильной формулы для выражения следующего:

Если B1=0, C1 равно A1/2
Если B1=1, C1 равно A1/2 умножить на 20%
Если D1=1, C1 равно A1/2-5

Я пытался найти подобные выражения на вашем сайте. Пожалуйста, помогите мне исправить это.

Ответ: В ячейке C1 можно использовать следующую формулу Excel с 3 вложенными функциями ЕСЛИ:

 =ЕСЛИ(B1=0,A1/2, ЕСЛИ(B1=1,(A1/2)*0,2, ЕСЛИ( D1=1,(A1/2)-5,"")))

Обратите внимание, что если ни одно из условий не выполнено, формула Excel вернет в качестве результата "".

Вопрос: В Excel, что я сделал не так с этой формулой?

 =ЕСЛИ(ИЛИ(ПУСТО(C9),ПУСТО(B9))),"",IF(ISBLANK(C9),D9-TODAY(), "Reactivated"))

Я хочу сделать событие, что если B9 и C9 пусты, значение будет пустым. Если пуста только ячейка C9, то выводом будут оставшиеся дни между двумя датами, а если две ячейки не пусты, выводом должна быть строка «Повторно активирована».

Проблема с этим кодом заключается в том, что IF(ISBLANK(C9),D9-TODAY() не работает.

Ответ:Во-первых, вы можете заменить функцию ИЛИ на функцию И, чтобы ваш Excel Формула ЕСЛИ выглядит так:

 =ЕСЛИ(И(ПУСТО(C9),ПУСТО(B9)),"",ЕСЛИ(ПУСТО(C9),D9-СЕГОДНЯ(),"Повторно активировано"))

Затем убедитесь, что вы не иметь какое-либо ненормальное форматирование в ячейке, содержащей результаты. Чтобы быть в безопасности, щелкните правой кнопкой мыши ячейку, содержащую формулу, и выберите Формат ячеек во всплывающем меню. Когда появится окно Format Cells , выберите вкладку Number . Выберите General в качестве формата и нажмите кнопку OK.

Вопрос: Я хочу получить ответ от числа 'n', которое должно удовлетворять определенным критериям диапазона. Новые калькуляторы гербовых сборов для недвижимости в Великобритании устанавливают диапазоны процентных гербовых сборов следующим образом:

0-125000 =0%
125001-250000 =2%
250001-975000 =5%
975001-1500000 =10%
>1500000 =12%
и

5 функция но я, кажется, требую слишком много аргументов. Вы можете помочь?

Ответ:Эту формулу можно создать с помощью вложенных функций ЕСЛИ. Предположим, что ваш номер «n» находится в ячейке B1. Формулу можно создать следующим образом:

 =ЕСЛИ(B1>1500000,B1*0,12, ЕСЛИ(B1>=975001,B1*0,1, ЕСЛИ(B1>=250001,B1*0,05, ЕСЛИ(B1>=125001, В1*0,02,0))))

Поскольку ваши условия IF охватывают все числа в диапазоне от 0 до >1500000, проще всего работать в обратном направлении, начиная с условия >1500000. Excel оценит каждое условие и остановится, когда условие будет ИСТИННЫМ. Вот почему мы можем упростить формулы во вложенных функциях ЕСЛИ вместо проверки диапазонов с помощью двух сравнений, таких как И (B1>=125001, B1<=250000).

Вопрос: Давайте расширим последний вопрос и предположим, что нам нужно рассчитать проценты на основе уровней (а не только значения в целом):

0-125000 =0 %
125001-250000 =2 %
250001-975000 =5 %
975001-1500000 =10 % Б1. Первые 125 000 притягивают 0%, следующие от 125 000 до 250 000 притягивают 2% и так далее.

Ответ: Это усложняет нашу формулу, поскольку мы должны вычислять каждый диапазон числа, используя разные проценты.

Это решение можно создать по следующей формуле:

 =ЕСЛИ(B1<=125000,0, ЕСЛИ(B1<=250000,(B1-125000)*0,02, ЕСЛИ(B1<=975000,(125000*0,02)+((B1-250000)*0,05), ЕСЛИ(B1<=1500000,(125000*0,02)+(725000*0,05)+((B1-975000)*0,1), (125000* 0,02)+(725000*0,05)+(525000*0,1)+((B1-1500000)*0,12)))))

Если значение меньше 125 000, формула вернет 0.

Если значение находится между 125 001 и 250 000, он будет рассчитывать 0% для первых 125 000 и 2% для остальных.