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

Создание условных формул — Служба поддержки Майкрософт

Проверка того, являются ли условия истинными или ложными, и логические сравнения между выражениями являются общими для многих задач. Для создания условных формул можно использовать функции AND, OR, NOT и IF .

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

Формула, использующая функцию IF

logical_test: условие, которое необходимо проверить.

value_if_true: возвращаемое значение, если условие имеет значение True.

value_if_false: возвращаемое значение, если условие имеет значение False.

Дополнительные сведения о создании формул см. в разделе «Создание или удаление формулы».

Что вы хотите сделать?

• 
  Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)

• 
  Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE

Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)

Для выполнения этой задачи используйте функции и операторы AND, OR и NOT , как показано в следующем примере.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

1. Выделите пример, приведенный в этой статье.

   Выделение примера в справке

2. Нажмите клавиши CTRL+C.

3. В Excel создайте пустую книгу или лист.

4. org/ListItem»>

   Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функции AND», «OR» и «НЕ».

К началу страницы

Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE

Для выполнения этой задачи используйте функции и операторы IF, AND и OR , как показано в следующем примере.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

1. Выделите пример, приведенный в этой статье.

   Важно: Не выделяйте заголовки строк или столбцов.

   Выделение примера в справке

2. Нажмите клавиши CTRL+C.

3. В Excel создайте пустую книгу или лист.

4. Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функция ЕСЛИ», «И» и » ИЛИ».

К началу страницы

Создание простой формулы в Excel

Excel

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

Формулы

Формулы

Создание простой формулы в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще…Меньше

Вы можете создать простую формулу для с суммы, вычитания, умножения и деления значений на вашем компьютере. Простые формулы всегда начинаются со знака равной(=),за которым следуют константы, которые являются числами и операторами вычислений, такими как «плюс»(+),«минус» (— ),«звездочка»*или «косая черта»(/)в начале.

В качестве примера рассмотрим простую формулу.

1. Выделите на листе ячейку, в которую необходимо ввести формулу.

2. Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

   В нашем примере введите =1+1.

   Примечания: 

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

   • org/ListItem»>

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

3. Нажмите клавишу ВВОД (Windows) или Return (Mac).

Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.

Использование автосуммирования

Для быстрого суммирования чисел в столбце или строке можно использовать кнопку «Автосумма». Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная, а затем нажмите клавишу ВВОД (Windows) или Return (Mac).

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT++= (Mac), и Excel автоматически вставит функцию СУММ.

Приведем пример. Чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку «Автоумма». Формула появится в ячейке B7, а Excel выделит ячейки, которые вы суммируете.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

Примечания: 

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

• Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

• Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.

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

Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Создание условных формул — Служба поддержки Майкрософт

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

Например, функция IF использует следующие аргументы.

Формула, использующая функцию ЕСЛИ

logical_test: условие, которое вы хотите проверить.

value_if_true: возвращаемое значение, если условие истинно.

value_if_false: возвращаемое значение, если условие имеет значение False.

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

Что ты хочешь сделать?

• Создайте условную формулу, результатом которой является логическое значение (ИСТИНА или ЛОЖЬ).

• Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ

Создать условную формулу, результатом которой является логическое значение (ИСТИНА или ЛОЖЬ)

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

Пример

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

Как скопировать пример?

1. Выберите пример из этой статьи.

   Выбор примера из справки

2. org/ListItem»>

   Нажмите CTRL+C.

3. В Excel создайте пустую книгу или лист.

4. На листе выберите ячейку A1 и нажмите CTRL+V.

Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (большое ударение) или на вкладке Формулы в группе Аудит формул щелкните значок Кнопка Показать формулы .

После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.

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

Верх страницы

Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ

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

Пример

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

Как скопировать пример?

1. Выберите пример из этой статьи.

   Важно:  Не выделяйте заголовки строк или столбцов.

   Выбор примера из справки

2. Нажмите CTRL+C.

3. В Excel создайте пустую книгу или лист.

4. На листе выберите ячейку A1 и нажмите CTRL+V.

Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (большое ударение) или на вкладке Формулы в группе Аудит формул нажмите кнопку Показать формулы .

После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.

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

Верх страницы

вложенная формула, несколько операторов и многое другое

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

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

Оператор ЕСЛИ И в Excel

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

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

В переводе на простой английский формула выглядит следующим образом: ЕСЛИ верно условие 1 И верно условие 2, сделайте одно, в противном случае сделайте что-нибудь другое.

В качестве примера составим формулу, которая проверяет, «доставлено» ли В2 и не является ли С2 пустым, и в зависимости от результатов выполняет одно из следующих действий:

• Если оба условия ИСТИННЫ, пометить ордер как «Закрытый».
• Если какое-либо из условий равно FALSE или оба условия FALSE, то вернуть пустую строку («»).

=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "")

На приведенном ниже снимке экрана показана функция ЕСЛИ И в Excel:

Если вы хотите вернуть какое-то значение в случае, если логический тест дает FALSE, укажите это значение в аргументе value_if_false . Например:

=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "Открыто")

Измененная формула выводит «Закрыто», если столбец B «доставлен» и C содержит любую дату (непустую). Во всех остальных случаях возвращает «Открыто»:

.

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

Теперь, когда вы знаете синтаксис оператора ЕСЛИ И, позвольте мне показать вам, какие задачи он может решить.

Excel ЕСЛИ: больше И меньше

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

Допустим, у вас есть несколько данных о продажах в столбце B, и вас просят пометить суммы больше 50 долларов, но меньше 100 долларов. Чтобы это сделать, вставьте эту формулу в C2, а затем скопируйте ее вниз по столбцу:

=ЕСЛИ(И(B2>50, B2<100), "x", "")

Если вам нужно включить граничные значения (50 и 100), используйте оператор меньше или равно (<=) и больше или равно (>=) оператор:

=ЕСЛИ(И(B2>=50, B2<=100), "x", "")

Чтобы обработать некоторые другие граничные значения без изменения формулы, введите минимальное и максимальное значения в две отдельные ячейки и обратитесь к этим ячейкам в формуле. Чтобы формула корректно работала во всех строках, обязательно используйте абсолютные ссылки для граничных ячеек (в нашем случае $F$1 и $F$2):

=ЕСЛИ(И(B2>=$F$1, B2<=$F$2), "x", "")

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

Например, давайте отметим даты между 10 сентября 2018 года и 30 сентября 2018 года включительно. Небольшое препятствие заключается в том, что даты не могут быть предоставлены логическим тестам напрямую. Чтобы Excel понимал даты, они должны быть заключены в функцию ДАТАЗНАЧ, например:

.

=ЕСЛИ(И(B2>=ДАТАЗНАЧ("9/10/2018"), B2<=DATEVALUE("30.09.2018")), "x", "")

Или просто введите даты От и До в две ячейки ($F$1 и $F$2 в этом примере) и «вытяните» их из этих ячеек с помощью уже знакомой формулы ЕСЛИ И:

=ЕСЛИ(И(B2>=$F$1, B2<=$F$2), "x", "")

Для получения дополнительной информации см. оператор Excel IF между двумя числами или датами.

ЕСЛИ это И то, то посчитайте что-нибудь

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

Чтобы продемонстрировать подход, мы будем рассчитывать бонус в размере 5% для «Закрытых» продаж на сумму, превышающую или равную 100 долларам США.

Предполагая, что сумма находится в столбце B, а статус заказа в столбце C, формула выглядит следующим образом:

=ЕСЛИ(И(B2>=100, C2="закрыто"), B2*10%, 0)

Приведенная выше формула присваивает остальным ордерам ноль ( value_if_false = 0). Если вы готовы давать небольшой стимулирующий бонус, скажем, 3%, к заказам, которые не соответствуют условиям, включите в аргумент value_if_false соответствующее уравнение:

=ЕСЛИ(И(B2>=100, C2="закрыто"), B2*10%, B2*3%)

Множественные операторы IF AND в Excel

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

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

В качестве примера нескольких условий И рассмотрим следующие:

• Сумма (B2) должна быть больше или равна 100 долл. США
• Статус заказа (C2) "Закрыт"
• Дата поставки (D2) находится в текущем месяце

Теперь нам нужен оператор IF AND, чтобы определить заказы, для которых все 3 условия ИСТИННЫ. И вот он:

=ЕСЛИ(И(B2>=100, C2="Закрыто", МЕСЯЦ(D2)=МЕСЯЦ(СЕГОДНЯ())), "x", "")

Учитывая, что «текущим месяцем» на момент написания был октябрь, формула дает следующие результаты:

Вложенные операторы IF AND

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

ЕСЛИ(И(…), выход1 , ЕСЛИ(И(…), выход2 , ЕСЛИ(И(…), выход3 , выход4 )))

Чтобы получить общее представление, посмотрите на следующий пример.

Предположим, вы хотите оценить свои услуги на основе стоимости доставки и предполагаемого времени доставки (ETD):

• Отлично : стоимость доставки менее 20 долларов США и ETD менее 3 дней
• Плохой : стоимость доставки более 30 долларов США и ETD более 5 дней
• Среднее : что-то среднее между

Для этого нужно написать два отдельных оператора IF AND:

ЕСЛИ(И(B2<20, C2<3), «Отлично», …)

ЕСЛИ(И(B2>30, C2>5), "Плохо", …)

…и вложить один в другой:

=ЕСЛИ(И(B2>30, C2>5), "Плохо", ЕСЛИ(И(B2<20, C2<3), "Отлично", "Средне"))

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

Дополнительные примеры формул можно найти во вложенных операторах IF AND Excel.

Функция ЕСЛИ И с учетом регистра в Excel

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

Если вы работаете с чувствительными к регистру данными и хотите оценить условия И с учетом регистра текста, выполните каждый отдельный логический тест внутри функции EXACT и вложите эти функции в оператор AND:

ЕСЛИ(И(ТОЧНО( ячейка ," условие1 "), ТОЧНО( ячейка ," условие2 ")), значение_если_истина, значение_если_ложь)

В этом примере мы будем отмечать заказы конкретного клиента (например, компании с именем Cyberspace ) на сумму, превышающую определенное число, скажем, 100 долларов.

Как вы можете видеть на снимке экрана ниже, названия некоторых компаний в столбце B выглядят одинаково, если не считать регистр символов, и, тем не менее, это разные компании, поэтому мы должны проверить названия ровно . Суммы в столбце C являются числами, и мы запускаем для них обычный тест «больше чем»:

.

=ЕСЛИ(И(ТОЧНО(B2, "Киберпространство"), C2>100), "x", "")

Чтобы сделать формулу более гибкой, вы можете ввести имя и сумму целевого клиента в две отдельные ячейки и ссылаться на эти ячейки. Только не забудьте заблокировать ссылки на ячейки знаком $ (в нашем случае $G$1 и $G$2), чтобы они не изменялись при копировании формулы в другие строки:

=ЕСЛИ(И(ТОЧНО(B2, $G$1), C2>$G$2), "x", "")

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

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

Предположим, вы хотите пометить заказы двух клиентов на сумму, превышающую определенное число, скажем, 100 долларов.

На языке Excel наши условия выражаются следующим образом:

ИЛИ(И( Клиент1 , Сумма >100), И( Клиент2 , Сумма >100)

Предположим, что имена клиентов находятся в столбце B, суммы — в столбце C, 2 целевых имени — в G1 и G2, а целевая сумма — в G3. Используйте эту формулу, чтобы пометить соответствующие заказы знаком «x»:

=ЕСЛИ(ИЛИ(И(B2=$G$1, C2>$G$3), И(B2=$G$2, C2>$G$3)), "x", "")

Те же результаты могут быть достигнуты с более компактным синтаксисом:

=ЕСЛИ(И(ИЛИ(B2=$G$1,B2=$G$2), C2>$G$3), "x", "")

Не уверены, что полностью понимаете логику формулы? Дополнительную информацию можно найти в Excel ЕСЛИ с несколькими условиями И/ИЛИ.