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

«Как прописать функцию «если» с несколькими условиями в программе Excel?» — Яндекс Кью

Популярное

Сообщества

КомпьютерыЭксель+3

Анонимный вопрос

14 марта 2019  ·

876,5 K

Ответить4Уточнить

Сергей Харук

Технологии

597

увлекаюсь компьютерами и природой. люблю точность и анализировать данные, может поэтому…  · 6 сент 2020  · office-study.ru

НЕ совсем корректный вопрос. Где именно нужно проверить несколько условий? надо ли это сделать последовательно или при соблюдении условия дополнительно проверить второе условие, а пи его несоблюдение третье? Все зависти о ситуации.

на самом деле ответ кроется в самом синтаксисе функции ЕСЛИ. Ее параметры

1. Условие

2. Действие, если условие выполняется

3. действие, если условие не выполняется.

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

1. Для каждого дополнительного условия надо прописывать отдельно функцию ЕСЛИ, не забывая про скобку после ее названия.

2. Следить, чтобы новое условие не дублировало старое . Другими словами, если в ячейке С2 находится число 25, а в формуле вы напишите так

=ЕСЛИ(С2<100;10;ЕСЛИ(С2<50;5;))

результат будет 10

секрет в том, что 25 УЖЕ МЕНЬШЕ 100, поэтому остальные условия не будут проверяться.

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

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

приведу пример расчета скидки по нескольким условиям.

собственно, задание

установить процент скидки следующим образом. Если цена товара меньше 500 то процент равен 2,5%, если меньше 1000 то 5%, если меньше 2500 то 7,5%, если меньше 5000 то 10%, остальные — 12,5%

при условии, что цены лежат в столбце D и начинаются с третьей строки начальная формула будет выглядеть так

=ЕСЛИ(D3<500;2,5%;ЕСЛИ(D3<1000;5%;ЕСЛИ(D3<2500;7,5%;ЕСЛИ(D3<5000;10%;12,5%))))

Желаю успеха!

https://office-study.xyz

Успеха Вам.
С уважением, Сергей

Перейти на office-study.ru

31,7 K

Комментировать ответ…Комментировать…

tDots. ru

5,6 K

Мы смотрим на бизнес через цифры и знаем, как получить максимум пользы.   · 26 мар 2019  · tdots.ru

Вопрос не совсем точно сформулирован, лучше было бы привести пример конкретной задачи. Допустим, задача такая: при выполнении ряда условий вывести букву «А», а в ином случае — «Б».
Сочетание нескольких условий внутри функции ЕСЛИ задается с помощью логической функции И. Например, нам надо вывести А, если в A1 — текст «Привет», в B1 — число больше 5, а в C1 — число… Читать далее

223,9 K

Максим Пангин

23 ноября 2019

а если к примеру, в ячейке какое либо выражение и мне нужно найти только одно слово в этих ячейках, как… Читать дальше

Комментировать ответ…Комментировать…

Первый

иван бубликов

31 мая 2022

помогите пожалуйста, формула не считает то что нужно, как правильно составить?
как правильно составить? нужно чтобы в ячейке J12 считалось вознграждание в зависимости от цифры в ячейке i12 , но в соответствии с процентом в столбце P17-21, если число в i12<1000000, то 0,5% и т. д Читать далее

Комментировать ответ…Комментировать…

Первый

Алексей Ф.

8

13 мар 2020

Так же для нескольких условий можно испольщовать функцию =ЕСЛИМН
Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.
=ЕСЛИМН(условие1; значение1… Читать далее

42,3 K

pin-ooo

31 марта 2020

нет такой функции. Только в excel 365

Комментировать ответ…Комментировать…

Вы знаете ответ на этот вопрос?

Поделитесь своим опытом и знаниями

Войти и ответить на вопрос

СУММЕСЛИ с несколькими критериями в Excel с примерами формулы на сайте

Главная » Формулы в Экселе

Автор Дмитрий Якушев На чтение 2 мин. Просмотров 10.5k.

Содержание

1. Сумма, если несколько критериев
2. Сумма, если один критерий в нескольких столбцах
3. Сумма, если равно одному из нескольких критериев

Сумма, если несколько критериев

=СУММЕСЛИМН(ранг_суммы; ранг_крит1 ; крит1 ; ранг_крит2 ; крит2)

Если вам необходимо суммировать числа, основанные на нескольких критериях, вы можете использовать функцию СУММЕСЛИМН.

В показанном примере, формула в G5 является:

=СУММЕСЛИМН(D5:D12; B5:B12; «синий»; C5:C12; «>15»)

Первым диапазоном (D5: D12) являются ячейки суммы, называется «диапазон суммы».

Критерии поставляются в парах (диапазон / критериев).

Парой Первый критерий является (B5: B12 / «синий»). Это означает, что ячейки в B5: B12 должны быть равны «синий».

Пара Второй критерий представляет собой (C5: C12 / «> 15»). Ячейки в C5: C12 должны также быть> 15.

Эти ячейки в пределах суммы будут суммироваться только тогда, когда все критерии подходят.

Сумма, если один критерий в нескольких столбцах

=СУММПРОИЗВ((ранг_критериев= «красный» ) * ( суммарный_диапазон ))

Подсчитывая несколько столбцов условно, используя один или несколько критериев, вы можете использовать формулу, основанную на функции СУММПРОИЗВ. В показанном примере, формула в Н4:

=СУММПРОИЗВ(( B5: B10 = «красный» ) * ( С5: Е10 ))

Это первое выражение в СУММПРОИЗВ является критерием, проверяя, если клетки в B5: B10 содержат «красный». Результатом является массив значений ИСТИНА, ЛОЖЬ:

{ ИСТИНА ; ЛОЖЬ; ЛОЖЬ; ИСТИНА ; ЛОЖЬ; ЛОЖЬ}

Это значение умножается на значения в диапазоне С5: Е10:

{1;6;2; 8;6;8; 5;2;6; 2;6;7; 7;4;5; 5;3;1}

Результатом внутри СУММПРОИЗВ является:

= СУММПРОИЗВ ( { 1 ; 6 ; 2 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 2 ; 6 ; 7 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 } )

который рассчитывает 24, сумма всех значений в С5: Е10, где B5: B10 = «красный».

Сумма, если равно одному из нескольких критериев

=СУММПРОИЗВ (СУММЕСЛИ ( диапазон_условия ; условие ; диапазон_суммирования ))

В показанном примере, формула в Н4:

=СУММПРОИЗВ(СУММЕСЛИ(B5:B11; E5:E7; C5:C11))

Функция СУММЕСЛИ принимает три аргумента: диапазон, критерии и суммарный_диапазон.

Для диапазона, мы используем B5: B11. Эти ячейки содержат значения, которые мы тестируем по нескольким критериям.

Для критериев, мы используем именованный диапазон «вещи» (E5: E7). Этот диапазон содержит 3 значения, которые мы используем в качестве критерия. Этот диапазон может быть расширен за счет включения дополнительных критериев, по мере необходимости.

Для суммарного_диапазона, мы используем C5: C11, который содержит числовые значения.

Потому как мы даем СУММЕСЛИ более чем один критерий, он будет рассчитывать несколько результатов — один результат для каждого значения в «вещи». Результаты рассчитываются в массиве следующим образом:.

=СУММПРОИЗВ( { 60 ; 30 ; 12 } )

СУММПРОИЗВ затем суммирует все элементы в массиве и рассчитывает конечный результат, 102.

Использование операторов Excel If с несколькими диапазонами условий (и/или)

Последнее обновление: 9 февраля 2023 г.
| Дата публикации: 9 ноября 2022 г.

Цель использования сложных и обширных инструкций Excel Если операторы с несколькими диапазонами условий важны, когда речь идет об анализе данных, который требует проверки определенных условий в определенном диапазоне в один момент времени.

Логический тест также может потребоваться для проверки различных условий.

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

Вот различные операторы If, которые вы можете использовать в зависимости от условий, для которых они вам нужны:

Оператор If Excel

Вложенный оператор If

Предположим, у нас есть следующие условия:

• Если оценка от 0 до 50, оценка F
• Если сумма баллов находится в диапазоне от 51 до 60, оценка D
• Если оценка находится в диапазоне от 61 до 75, оценка C
• Если оценка находится в диапазоне от 76 до 90, оценка B
• Если оценка находится в диапазоне от 91 до 100, оценка A

Синтаксис формулы, основанный на приведенном выше условии, будет таким:

=ЕСЛИ(B2<50, «F»,IF(B2<61, «D», IF(B2<76»,C»,IF( B2<91, "B", "A"))))

Excel If с логическим тестом

При использовании операторов Excel If для выполнения логического теста можно использовать два разных условия — И и ИЛИ .

Функция И

Используя эту функцию, результат, который Excel выдаст при выполнении логического теста, будет True в каждом условии, в противном случае он будет False.

Функция ИЛИ

Результатом будет True, если логический тест выполняется при совпадении любой из ситуаций, в противном случае результат будет False.

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

Со временем вы научитесь использовать эти две функции с оператором If.

Как применять функции ЕСЛИ и И

1. Во-первых, нам нужно взять данные о баллах каждого учащегося по естественным наукам и английскому языку.
2. Оценка по естественным наукам хранится в данных B, тогда как оценка по английскому языку хранится в данных C.
3. В качестве примера предположим, что учащийся сможет пройти, если его или ее балл больше или равен 50 по естественным наукам и больше 75 по английскому языку.
4. Чтобы получить результат для всех учащихся всего за несколько секунд, оператор If будет объединен с функцией AND.
5. Чтобы запустить формулу, можно ввести следующее: =ЕСЛИ(Excel отобразит логическую подсказку под ячейкой F2. Он покажет параметры этой функции: логическая_проверка, значение_если_истина, значение_если_ложь.
6. Первый параметр указывает условие, которое необходимо выполнить. Этот логический тест также допускает комбинацию функций ЕСЛИ и И.
7. Второй параметр покажет значение, если условие истинно.
8. Наконец, третий параметр покажет, что значение условия ложно.
9. Применяя формулы ЕСЛИ и И, вы получите =ЕСЛИ(И(B2>=75,C2>=75), «Пройдено», «Не пройдено»)
10. Добавьте столбец «годен/не годен». Добавьте формулу в ячейку и скопируйте и вставьте ее в остальные ячейки ниже. Затем вы увидите результат для каждого ученика.

Как использовать ЕСЛИ с функцией ИЛИ в Excel

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

Если принять приведенный выше пример, то формула будет =ЕСЛИ(ИЛИ(B2>=50,C2>=75), «Удачно», «Не пройдено»)

Как использовать ЕСЛИ с функциями И и ИЛИ

Это применимо при тестировании нескольких условий на основе разных данных в один момент времени.

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

Ниже приведены примеры:

• 1-я ситуация: если столбец B>=70 и столбец C>=85
• 2-я ситуация: если столбец B>=80 и столбец C>=85

При соблюдении всех вышеперечисленных условий учащийся считается зачисленным.

В противном случае студент терпит неудачу.

Используемая формула:

=ЕСЛИ(ИЛИ(И(B2>=70,C2>=85), И(B2>=80,C2>=85))»,«Выполнено», «Не выполнено ”)

Ниже приведены примеры использования оператора ЕСЛИ с другими функциями:

Оператор ЕСЛИ с функциями суммы, среднего, минимума и максимума Удовлетворительно или Плохо.

Если у вас есть предопределенная формула, такая как показанная ниже, и изменение данных не разрешено:

=Если((B2+C2)>=150, «Хорошо», Если((B2+C2)=>100, «Удовлетворительно», «Плохо»))

Использование функции СУММ

=Если(СУММ (B2:C2)>=150, «Хорошо», Если(СУММ(B2+C2)=>100, «Удовлетворительно», «Плохо»))

Использование функции СРЗНАЧ

=Если(СРЗНАЧ(B2:C2) )>=150, «Хорошо», If(AVERAGE(B2+C2)=>100, «Удовлетворительно», «Плохо»))

Использование функции MAX / MIN

пул студентов, вы можете использовать функцию MAX.

Таким же образом вы можете использовать функцию MIN, чтобы узнать, кто получил наименьшее количество очков.

Тогда формула для получения наивысшего балла будет следующей:

=If(B2=Max($B$2:$B$10), «Лучший результат», «)

Формула для получения наименьшего балла будет также:

=If(B2=Min($B$2:$B$10), «Худший результат»», «)

Объединение этих двух формул: 

=If(B2=Max($B $2:$B$10), «Лучший результат», If(B2=Min($B$2:$B$10), «Худший результат», «»))

Теперь вы можете начать использовать четыре различных типа операторов Excel If.

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

Использование И() или ИЛИ() в Excel для разрешения нескольких условий при использовании ЕСЛИ()

Функция ЕСЛИ() в Excel, если вы не знакомы с ней, позволяет вам выполнено, но сделать что-то еще, если это условие не выполнено. Например, при оценке тестов вы можете захотеть отобразить «Пройдено», если оценка составляет 80 % или выше, и отобразить «Не пройдено», если оценка ниже 80 %.

IF — чрезвычайно полезная функция, но она ограничена оценкой одного условия. Что делать, если вам нужно оценить несколько условий? Вот тут-то и появляются функции И() и ИЛИ(). И и ИЛИ могут вычислять до 255 условий (Excel называет эти условия аргументами ). И () даст результат «Истина», только если все условий (аргументов) выполнены, в противном случае результат «Ложь». Но ИЛИ() даст результат «Истина», если любые условий выполнены.

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

В качестве примера создадим формулу, которая будет рассчитывать комиссию в размере 10% только в том случае, если счет оплачен и маржа составляет не менее 20%.

Сначала давайте рассмотрим два простых примера, которые мы сможем комбинировать, чтобы построить нужную нам формулу.

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

= ЕСЛИ (E1 = «Да», B2 * 0,1,0 )

Помните, что каждый «аргумент», как их называет Excel, отделяется запятой. Итак, на простом английском языке формула гласит ЕСЛИ ячейка Е1 = Да, то умножаем содержимое ячейки В2 на 0,1, если нет, то комиссия 0.

Так как у нас есть более одного условия, которое необходимо выполнить, мы хотим заменить критерии из приведенной выше формулы, E1=”YES” с помощью простой формулы с использованием функции AND.

Если бы мы ввели формулу =И(D2>=0,2,E2=”Да”) в ячейку F2, результат был бы ЛОЖЬ . Счет оплачен (E2=”Да”), но маржа меньше 20% ( D2>=0,2). Если бы мы использовали ИЛИ вместо И с теми же аргументами, результатом было бы ИСТИНА , поскольку ИЛИ требует выполнения только одного из аргументов. С помощью AND или OR вы можете включить до 255 аргументов. Просто перечислите все аргументы (ваши условия) между скобками и разделите их запятыми.

Чтобы получить формулу комиссии для этого примера, все, что осталось сделать, это взять первую формулу =IF(E1=”Yes”,B2*0.1,0) заменить одно условие E1=”Yes” с =И(D2>=0,2,E2=”Да”) . Если сложить их вместе, получится следующее:

=ЕСЛИ(И(D1>=0,2,E1=”Да”),B2*0,1,0) .

Проще говоря, это: Если ячейка D1 больше или равна 0,2 и ячейка E2 = Да, тогда умножьте ячейку B2 на 0,1. Если нет, то результат равен 0,

Помните, что вам не нужно писать формулы с нуля. Если вы перейдете на вкладку Формулы и нажмете кнопку Вставить функцию , вы можете выбрать функцию, и она даст вам форму для заполнения, чтобы помочь вам ввести все в правильном порядке.

Эта запись была опубликована 29 августа 2017 г.