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

Обновлено: 25.06.2024

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

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

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

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

. Если ваш логический тест содержит функцию И, Microsoft Excel возвращает значение ИСТИНА, если все условия соблюдены; в противном случае возвращается ЛОЖЬ. . Если вы используете функцию ИЛИ в логическом тесте, Excel возвращает ИСТИНА, если выполняется какое-либо из условий; ЛОЖЬ в противном случае.

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

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

Допустим, у вас есть таблица с результатами двух экзаменационных баллов. Первая оценка, хранящаяся в столбце C, должна быть равна или больше 20. Вторая оценка, указанная в столбце D, должна быть равна или превышать 30. Только при выполнении обоих вышеперечисленных условий учащийся проходит итоговый экзамен. экзамен.

Самый простой способ составить правильную формулу – сначала записать условие, а затем включить его в аргумент logical_test вашей функции ЕСЛИ:

Условие: И(B2>=20, C2>=30)

=ЕСЛИ((И(C2>=20, D2>=30)), "Пройдено", "Не пройдено")

Легко, не так ли? Формула указывает Excel вернуть «Пройдено», если значение в столбце C >= 20 И значение в столбце D >= 30. В противном случае формула возвращает «Fail». На приведенном ниже снимке экрана показано, что наша функция ЕСЛИ/И в Excel верна:

Примечание. Microsoft Excel проверяет все условия в функции И, даже если одно из уже проверенных условий оценивается как ЛОЖЬ. Такое поведение немного необычно, поскольку в большинстве языков программирования последующие условия не проверяются, если какой-либо из предыдущих тестов вернул FALSE.

Чтобы этого избежать, следует использовать вложенную функцию ЕСЛИ:

=ЕСЛИ(A2<>0, ЕСЛИ((1/A2)>0,5, "Хорошо", "Плохо"), "Плохо")

Пример 2. Использование ЕСЛИ с функцией ИЛИ в Excel

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

Итак, если мы изменим приведенную выше формулу следующим образом:

=ЕСЛИ((ИЛИ(C2>=20, D2>=30)), "Пройдено", "Не пройдено")

Столбец E будет иметь отметку "Удачно", если первая оценка равна или больше 20 ИЛИ вторая оценка равна или больше 30.

Как видно на скриншоте ниже, у наших студентов больше шансов сдать выпускной экзамен с такими условиями (особенно не повезло Скотту, который провалился всего на 1 балл : )

Дополнительные примеры формул можно найти в функции Excel ЕСЛИ ИЛИ.

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

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

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

Условие 1: столбец C>=20 и столбец D>=25
Условие 2: столбец C>=15 и столбец D>=20

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

Формула может показаться сложной, но через мгновение вы увидите, что это не так! Вам просто нужно выразить два условия как операторы И и заключить их в функцию ИЛИ, поскольку вам не требуется выполнение обоих условий, достаточно любого из них:

ИЛИ(И(C2>=20, D2>=25), И(C2>=15, D2>=20)

Наконец, используйте приведенную выше функцию ИЛИ в качестве логического теста в функции ЕСЛИ и укажите аргументы value_if_true и value_if_false. В результате вы получите следующую формулу ЕСЛИ с несколькими условиями И/ИЛИ:

=ЕСЛИ(ИЛИ(И(C2>=20, D2>=25), И(C2>=15, D2>=20)), "Пройдено", "Не пройдено")

< /p>

Снимок экрана ниже показывает, что формула верна:

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

В Excel 2016, 2013, 2010 и 2007 ваша формула включает не более 255 аргументов, а общая длина формулы не превышает 8 192 символов.
В Excel 2003 и более ранних версиях можно использовать до 30 аргументов, а общая длина формулы не должна превышать 1024 символа.

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

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

Вот типичный пример: предположим, вы хотите не просто квалифицировать результаты учащихся как "Удачно/Неудовлетворительно", а определить общую оценку как "Хорошо", "Удовлетворительно". " и "Плохо". Например:

Хорошо: 60 и более (>=60)
Удовлетворительно: от 40 до 60 (>40 и =C2+D2

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

=ЕСЛИ(E2>=60, "Хорошо", ЕСЛИ(E2>40, "Удовлетворительно", "Плохо"))

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

=ЕСЛИ(E2>=70, "Отлично", ЕСЛИ(E2>=60, "Хорошо", ЕСЛИ(E2>40, "Удовлетворительно", "Плохо")))

Вышеприведенная формула добавляет еще одно условие - суммарная оценка 70 баллов и выше квалифицируется как "Отлично".

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

Использование ЕСЛИ Excel в формулах массива

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

Например, следующая формула массива СУММ/ЕСЛИ демонстрирует, как можно суммировать ячейки в указанном диапазоне на основе определенного условия, а не суммировать фактические значения:

Формула присваивает определенное количество «баллов» каждому значению в столбце B. Если значение равно или меньше 1, оно приравнивается к 1 баллу; и 2 балла присваиваются каждому значению больше 1. Затем функция СУММ суммирует полученные 1 и 2, как показано на снимке экрана ниже.

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

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

Пример 1. Использование ЕСЛИ с функциями СУММ, СРЗНАЧ, МИН и МАКС

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

Но что, если ваша таблица имеет предопределенную структуру, не допускающую никаких изменений? В этом случае вместо добавления вспомогательного столбца вы можете добавить значения непосредственно в формулу «Если», например:

=ЕСЛИ((C2+D2)>=60, "Хорошо", ЕСЛИ((C2+D2)=>40, "Удовлетворительно", "Плохо"))

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

=ЕСЛИ(СУММ(C2:F2)>=120, "Хорошо", ЕСЛИ(СУММ(C2:F2)>=90, "Удовлетворительно", "Плохо"))

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

ЕСЛИ и СРЕДНЕЕ:

Формулы перенастраиваются на «Хорошо», если средний балл в столбцах C:F равен или превышает 30, «Удовлетворительно», если средний балл находится в диапазоне от 29 до 25 включительно, и «Плохо», если он меньше 25.< /p>

ЕСЛИ и МАКС/МИН:

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

МАКС: =ЕСЛИ(F2=МАКС($F$2:$F$10), "Лучший результат", "")

MIN: =IF(F2=MIN($F$2:$F$10), "Худший результат", "")

Если вы предпочитаете, чтобы результаты Min и Max находились в одном столбце, вы можете вложить одну из вышеперечисленных функций в другую, например:

=IF(F2=MAX($F$2:$F$10) ,"Лучший результат", IF(F2=MIN($F$2:$F$10), "Худший результат", ""))

Похожим образом вы можете использовать функцию ЕСЛИ со своими пользовательскими функциями рабочего листа. Например, вы можете использовать его с функциями GetCellColor/GetCellFontColor, чтобы возвращать разные результаты в зависимости от цвета ячейки.

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

Например, чтобы подсчитать количество вхождений текстового или числового значения на основе одного или нескольких условий, можно использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН соответственно. Чтобы узнать сумму значений на основе указанных условий, используйте функции СУММЕСЛИ или СУММЕСЛИМН. Чтобы рассчитать среднее значение по определенным критериям, используйте СРЗНАЧЕСЛИ или СРЗНАЧСЛИМН.

Подробные пошаговые примеры формул см. в следующих руководствах:

Пример 2. ЕСЛИ с функциями ISNUMBER и ISTEXT

Вы уже знаете, как определять пустые и непустые ячейки с помощью функции ЕПУСТО. Microsoft Excel предоставляет аналогичные функции для идентификации текстовых и числовых значений — ISTEXT и ISNUMBER соответственно.

Вот пример вложенной функции ЕСЛИ Excel, которая возвращает "Текст", если ячейка B1 содержит какое-либо текстовое значение, "Число", если ячейка B1 содержит числовое значение, и "Пусто", если ячейка B1 пуста.

=ЕСЛИ(ИСТЕКСТ(B1), "Текст", ЕСЛИ(ЕЧИСЛО(B1), "Число", ЕСЛИ(ЕСПУСТО(B1), "Пусто", "")))

Примечание. Обратите внимание, что приведенная выше формула отображает «Число» для числовых значений и дат. Это связано с тем, что Microsoft Excel хранит даты в виде чисел, начиная с 1 января 1900 г., что соответствует 1.

Пример 3. Использование результата, возвращаемого ЕСЛИ, в другой функции Excel

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

Еще один способ совместного использования функций СЦЕПИТЬ и ЕСЛИ:

=CONCATENATE("Вы выполнили ", ЕСЛИ(C1>5,"фантастика!","хорошо"))

Полагаю, вам вряд ли нужно объяснять, что делает формула, особенно глядя на скриншот ниже:

Функция ЕСЛИ в сравнении с ЕСЛИОШИБКА и IFNA

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

Вот простейший пример формулы ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(B2/C2, "Извините, произошла ошибка")

Как видно на снимке экрана выше, в столбце D отображается частное от деления значения в столбце B на значение в столбце C. Вы также можете увидеть два сообщения об ошибке в ячейках D2 и D5, потому что все знают, что вы не можете разделить число на ноль.

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

=IF(C2=0, "Произошла ошибка", B2/C2)

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel Mobile Excel Starter 2010 Еще. Меньше

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

СУММПРОИЗВ принимает в качестве аргументов массивы и арифметические операторы. Вы можете использовать массивы, которые оцениваются как True или False (1 или 0), в качестве критериев, используя их как коэффициенты (умножая их на другие массивы).

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

". Например, *(Таблица1[Агент]="Джонс"). Это приводит к тому, что ячейки оцениваются как 1 или 0, поэтому при умножении на другие значения в формуле результатом будет либо то же значение, либо ноль, что фактически включает или исключает соответствующие ячейки в любых вычислениях.

Если у вас есть другие критерии, повторите шаг 6 по мере необходимости. После последнего диапазона введите ).

Ваша заполненная формула может выглядеть так, как в нашем примере выше: =СУММПРОИЗВ(((Таблица1[Продажи])-(Таблица1[Расходы]))*(Таблица1[Агент]=B8)), где ячейка B8 содержит имя агента.

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

Например, если вы ищете формулу, которая войдет в ячейку B2, и если число находится в диапазоне от 100 до 999, результатом будет 100. В противном случае, если оно выходит за пределы этого диапазона, результат будет быть нулем.

Инструкция IF между двумя числами

=ЕСЛИ(И(C6>=C8,C6

(См. скриншоты ниже).

Пример использования формулы:

Шаг 1. Поместите число, которое вы хотите проверить, в ячейку C6 (150).

Шаг 2. Поместите критерии в ячейки C8 и C9 (100 и 999).

Шаг 3. Поместите результаты, если они истинны или ложны, в ячейки C11 и C12 (100 и 0).

Шаг 4. Введите формулу =ЕСЛИ(И(C6>=C8,C6). Загрузите бесплатный шаблон.

Инструкция IF между двумя числами (с текстом)

Вам не нужно ограничивать результирующий вывод модели только числами. Вы также можете использовать текст, как показано в примере ниже. На этот раз вместо того, чтобы выводить в результате 100 или 0, Excel может отображать ДА или НЕТ, чтобы показать, является ли аргумент истинным или ложным.

Дополнительные руководства по Excel

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

В приведенном ниже примере у вас есть начало диапазона в столбце A, конец диапазона в столбце B и значение для оценки в столбце C.

Вам нужно проверить, находится ли число, введенное в столбце C, между числами в столбцах A и B, используя творчески сформулированную формулу МЕЖДУ в Excel.

Функция, которую можно использовать для определения того, находится ли значение в ячейке D7 между значениями в ячейках A7 и B7, является

=ЕСЛИ(И(C7>=МИН(A7,B7),C7

Давайте разобьем эту формулу на части, чтобы лучше понять ее:

C7 >= MIN(A7, B7) – это выражение проверяет, больше ли значение в ячейке C7 меньшего из двух чисел в ячейках A7 и B7 (или равно ему).
C7 = MIN(A7, B7), C7 ВАРИАНТ 2: Использование функции МЕДИАНА

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

=ЕСЛИ(C7=МЕДИАНА(A7:C7), «Да», «Нет»)

В нашем первом примере выше диапазон составляет 20–60, при проверке значения 50 оно находится между этим диапазоном.

Формула медианы вернет значение в середине этих трех значений, расположенных в порядке возрастания: 20, 50, 60. Среднее значение равно 50.

Поскольку оно соответствует значению, которое мы оцениваем, мы получаем ответ: Да, это значение (50) находится между диапазоном.

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

Между формулой в Excel для дат

Независимо от того, как вы форматируете ячейку для отображения даты, Excel всегда сохраняет ее как число. Число, сохраненное для каждой даты, фактически представляет собой количество дней, прошедших с 0 января 1990 года.

1 января 1990 г. хранится как 1 (соответствует 1 дню с 0 января 1990 г.), а 23 июня 2020 г. сохраняется как 44 005 (соответствует 44 005 дней с 0 января 1990 г.).

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

Ниже приведен пример использования функции медианы для проверки дат.

=ЕСЛИ(C10=МЕДИАНА(A10:C10), «Да», «Нет»)

В нашем первом примере выше диапазон составляет 1 мая – 1 июля, при проверке даты 1 июня он находится между этими диапазонами.

Формула медианы вернет значение в середине этих трех дат, расположенных в порядке возрастания: 1 мая, 1 июня, 1 июля. Медиана – 1 июня.

Поскольку оно соответствует значению, которое мы оцениваем, мы получаем ответ: Да, это значение (1 июня) находится между диапазоном.

Между формулами в Excel для текста

Для текста мы проверяем, находится ли значение в алфавитном порядке посередине. Мы будем использовать формулу and:

=ЕСЛИ(И(C12>=A12, C12

Интересно, что вы можете сравнивать тексты, используя символы >= и = Cat и Cow. ПОЛЕЗНЫЙ РЕСУРС:

Для подсчета вызовов через определенные промежутки времени можно использовать функцию СЧЁТЕСЛИМН. В показанном примере формула в H5 выглядит так:

где "таблица" – это таблица Excel, содержащая время звонков, как показано на рисунке.

Примечание. Эта формула зависит от значений в столбце "Время" (C) и значений в столбцах "Начало" и "Конец" (F, G), являющихся действительным временем Excel.

Данные находятся в таблице Excel под названием "таблица". Создавая правильную таблицу Excel, мы облегчаем чтение и запись формул. Кроме того, любые новые данные, добавляемые в таблицу, будут автоматически учитываться формулами в столбцах H и I.

Сводная таблица справа создается путем ввода времени Excel в столбцах "Начало" и "Конец". После того, как вы введете пару раз, вы можете использовать дескриптор заполнения, чтобы ввести остальные. Чтобы подсчитать ячейки, которые встречаются в каждом интервале, как показано, формула в H5:

Функция СЧЁТЕСЛИМН была настроена с двумя критериями, и, как и другие функции RACON, СЧЁТЕСЛИМН принимает критерии, введенные в пары диапазон/критерий следующим образом:

Буквальный перевод: подсчет значений в столбце "Время" в "таблице", которые больше или равны времени начала в F5 И меньше времени окончания в G5"

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

Общее время

Для расчета общего времени всех вызовов за каждый интервал можно использовать функцию СУММЕСЛИМН. Логические критерии точно такие же, разница только в первом аргументе, называемом sum_range. Это диапазон, содержащий значения для суммирования, которые представляют собой столбец «Длительность» в показанной таблице. Формула в I5, скопированная вниз, выглядит так:

Результаты, возвращаемые функцией СУММЕСЛИМН в столбце I, отформатированы как часы и минуты:

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

Квадратные скобки запрещают Excel сбрасывать часы на 1 день (24 часа).

С динамическими массивами

Если у вас есть Excel 365, вы можете ввести по одной формуле для подсчета и суммирования времени во всех интервалах одновременно:

Читайте также: