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

Содержание

Создание пользовательских функций в Excel

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


Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

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

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:

  1. Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

  2. org/ListItem»>

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


    
Function DISCOUNT(quantity, price)
   If quantity >=100 Then
     DISCOUNT = quantity * price * 0.1
   Else
     DISCOUNT = 0
   End If
 
 DISCOUNT = Application.Round(Discount, 2)
End Function

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.


Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:


=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

Рассмотрим, как Excel обрабатывает эту функцию. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки (quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:


If quantity >= 100 Then
 DISCOUNT = quantity * price * 0. 1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:


Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:


Discount = 0

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


Discount = Application. Round(Discount, 2)

В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.


Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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


Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.


Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

Апостроф указывает приложению Excel на то, что следует игнорировать всю строку справа от него, поэтому вы можете добавлять комментарии в отдельных строках или в правой части строк, содержащих код VBA. Советуем начинать длинный блок кода с комментария, в котором объясняется его назначение, а затем использовать встроенные комментарии для документирования отдельных операторов.

Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.

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


Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

  1. Создав нужные функции, выберите Файл > Сохранить как.

    В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.

  2. В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.

  3. Сохранив книгу, выберите Файл > Параметры Excel.

    В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.

  4. В диалоговом окне Параметры Excel выберите категорию Надстройки.

  5. В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.

  6. org/ListItem»>

    В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

  1. Создав нужные функции, выберите Файл > Сохранить как.

  2. В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.

  3. org/ListItem»>

    Сохранив книгу, выберите Сервис > Надстройки Excel.

  4. В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.


Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

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


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



Функция СУММЕСЛИ

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)


Это видео — часть учебного курса Сложение чисел в Excel.

Советы: 

  • При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».

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

Важно: Функция СУММЕСЛИ возвращает неверные результаты, если она используется для сопоставления строк длиннее 255 символов или строки #VALUE !.

Синтаксис


СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Аргументы функции СУММЕСЛИ описаны ниже.

  • org/ListItem»>


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


  • Условие   .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включить подстановочные знаки — вопросительный знак (?) для сопоставления любого отдельного символа, звездочку (*) для сопоставления любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~).

    Например, критерии могут быть выражены как 32, «>32», B5, «3?», «apple*», «*~?», или TODAY().

    Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки («). Если условием является число, использовать кавычки не требуется.


  • Диапазон_суммирования   .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).



    Sum_range
    должны иметь тот же размер и форму, что и диапазон. В противном случае производительность может снизиться, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range но имеет те же размеры, что и диапазон. Например:






    диапазон



    Диапазон_суммирования.


    Фактические суммарные ячейки

    A1:A5

    B1:B5

    B1:B5

    A1:A5

    B1:K5

    B1:B5

Примеры




Пример 1


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









Стоимость имущества


Комиссионные


Данные

1 000 000 ₽

70 000 ₽

2 500 000 ₽

2 000 000 ₽

140 000 ₽

3 000 000 ₽

210 000 ₽

4 000 000 ₽

280 000 ₽


Формула


Описание


Результат

=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)

Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽.

630 000 ₽

=СУММЕСЛИ(A2:A5; «>160000»)

Сумма по имуществу стоимостью больше 1 600 000 ₽.

9 000 000 ₽

=СУММЕСЛИ(A2:A5;300000;B2:B5)

Сумма комиссионных за имущество стоимостью 3 000 000 ₽.

210 000 ₽

=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)

Сумма комиссионных за имущество, стоимость которого превышает значение в ячейке C2.

490 000 ₽


Пример 2


Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.














Категория


Продукты


Объем продаж

Овощи

Помидоры

23 000 ₽

Овощи

Сельдерей

55 000 ₽

Фрукты

Апельсины

8 000 ₽

Масло

4 000 ₽

Овощи

Морковь

42 000 ₽

Фрукты

Яблоки

12 000 ₽


Формула


Описание


Результат

=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)

Объем продаж всех продуктов категории «Фрукты».

20 000 ₽

=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)

Объем продаж всех продуктов категории «Овощи».

120 000 ₽

=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)

Объем продаж всех продуктов, названия которых заканчиваются на «ы» («Помидоры» и «Апельсины»).

43 000 ₽

=СУММЕСЛИ(A2:A7;»»;C2:C7)

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

4 000 ₽


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


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


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



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



Функция СУММЕСЛИМН суммирует все аргументы, которые соответствуют нескольким условиям


Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат


Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию


Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям


Функция ЕСЛИМН (Office 365, Excel 2016 и более поздние версии)


Общие сведения о формулах в Excel


Рекомендации, позволяющие избежать появления неработающих формул


Поиск ошибок в формулах


Математические и тригонометрические функции


Функции Excel (по алфавиту)


Функции Excel (по категориям)



Как использовать функцию ЕСЛИ в Excel [пошаговое руководство]

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

Функция ЕСЛИ в Excel выполняет логический тест и возвращает одно значение, если условие ИСТИНА, и другое, если условие ЛОЖЬ.

Как написать формулу «если-то» в Excel? Что ж, синтаксис операторов IF одинаков во всех версиях Excel. Это означает, что вы можете использовать любой из примеров, показанных в этой статье, в Excel для Microsoft 365 или Excel 2021, 2019, 2016, 2013, 2010, 2007 и 2003.

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

  1. Выберите ячейку, в которую вы хотите вставить формулу ЕСЛИ. С помощью мыши или клавиатуры перейдите к ячейке, в которую вы хотите вставить формулу.
  2. Введите =IF(
  3. Вставьте условие, которое вы хотите проверить, после запятой (,) . Первый аргумент функции ЕСЛИ — это логическая_проверка. Это условие, которое вы хотите проверить. Например, C6 > 70.
  4. Вставьте значение для отображения, когда условие ИСТИНА, после запятой (,) . Второй аргумент функции ЕСЛИ — значение_если_истина. Здесь вы можете вставить вложенную формулу или простую сообщение, такое как «ДА».
  5. Вставьте значение, которое будет отображаться, когда условие ЛОЖЬ . Последний аргумент функции ЕСЛИ — значение_если_ложь. Как и в предыдущем шаге, вы можете вставить вложенную формулу или отобразить сообщение, например «НЕТ». Это также может быть установлено как пустая строка («»), которая будет отображать ячейку, которая выглядит пустой.
  6. Введите ), чтобы закрыть функцию, и нажмите ENTER

Как использовать функцию ЕСЛИ в ExcelЗагрузить

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

https://www. youtube.com/watch?v=dz-Hwa7EWa8Видео не может быть загружено, так как отключен JavaScript: как использовать функцию ЕСЛИ в Excel (https://www.youtube.com/watch?v =dz-Hwa7EWa8)

Синтаксис, показывающий, как создать функцию ЕСЛИ в Excel, поясняется ниже:
=ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

ЕСЛИ — это логическая функция, которая подразумевает установку 3 аргумента:
logical_test — Логическое условие, которое вы хотите проверить. Это вернет значение TRUE или FALSE.
value_if_true — [необязательно] Значение или формула, которая будет использоваться, когда logical_test имеет значение TRUE.
value_if_false — [необязательно] Значение или формула, которая будет использоваться, когда logical_test имеет значение FALSE.

Помните, что оба параметра value_if_true и value_if_false являются необязательными, хотя бы один из них должен быть указан. В противном случае ваша формула ЕСЛИ просто вернет 0 (ноль).

Где находится функция ЕСЛИ в Excel? Поскольку это логическая функция, вы можете найти функцию ЕСЛИ в Вкладка «Формулы» , раздел «Библиотека функций» , в разделе «Логический» .

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

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

9081 9081

Логические операторы Определение Пример
=, равное A1 = B1
<> Не равняется A1 <> B1
> Большая, чем A175

>. или равен A1> = B1
< ниже A1
<= ниже или равны A11 <= B1
. 0002 Функция ЕСЛИ не поддерживает подстановочные знаки.

Ваша первая формула ЕСЛИ

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

Теперь давайте перейдем к примерам.

Мы будем оценивать экзаменационные оценки. Если учащийся получил оценку выше или равную 70, то мы вернем сообщение «Пройдено». Если оценка ниже 70, то будет отображаться «Fail».

В этом примере я вставил следующую формулу в ячейку F9:
=ЕСЛИ(E9>=70, "Пройдено", "Не пройдено")

3 аргумента для этой формулы ЕСЛИ:
logical_test : E9>=70
value_if_true : Pass возвращается, если E9>=70.
value_if_false : Ошибка возвращается, если E9<70.

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

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

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

В нашем следующем примере мы будем использовать функцию ЕСЛИ для расчета комиссии за платеж на основе стоимости заказа. Если стоимость заказа выше или равна 1000 долларов США, то следует рассчитать комиссию за платеж в размере 1,00%. Однако, если общая стоимость заказа ниже 1000 долларов США, следует использовать 1,50%.

Формула в ячейке F31:
=ЕСЛИ(E31>=1000, E31*1%, E31*1,5%)

Теперь давайте рассмотрим формулу ЕСЛИ, которая зависит от ввода пользователя. Если мы выберем бесплатную доставку для заказа, то стоимость доставки будет равна нулю. В противном случае она будет рассчитана как 3% от стоимости заказа.

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

Вложение дополнительных функций ЕСЛИ позволяет выполнять несколько сравнений и создавать более сложные формулы. Однако в Excel можно вложить не более 64 функций ЕСЛИ. Если вы когда-нибудь достигнете этого предела (я никогда не достигал), я могу гарантировать, что существует лучшее и более элегантное решение с использованием таких функций, как ВПР, СУММЕСЛИ или СЧЁТЕСЛИМН.

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

=ЕСЛИ(Е71<60, "F", ЕСЛИ(Е71<70, "Г", ЕСЛИ(Е71<80, "С", ЕСЛИ(Е71<90, "В", "А"))) )

Порядок условий важен. Когда условия перекрываются, Excel извлекает аргумент [значение_если_истина] из первого оператора ЕСЛИ, возвращающего ИСТИНА. Вот почему условия из приведенной выше формулы должны быть вставлены в том же порядке, чтобы формула работала правильно.

Примечание. Если вы используете Office 365, вы также можете ознакомиться с новой функцией IFS. Эта функция запускает несколько тестов и возвращает значение, соответствующее первому результату TRUE. Это очень полезная альтернатива вложенным формулам ЕСЛИ, которая делает ваши формулы более понятными для других. Вы можете прочитать больше о IFS на веб-сайте Microsoft.

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

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

Давайте посмотрим на формулу ЕСЛИ, которая вычисляет стоимость доставки в размере 2,00%, когда общая стоимость заказа превышает 1000 долларов США или если в заказе более 5 товаров.

Оператор ЕСЛИ ИЛИ, который я использовал в ячейке h206:
=ЕСЛИ(ИЛИ(G106>1000, F106>5), G106*2%, 0)

Функция ИЛИ оценивает, если G106>1000 или если F106>5 и формула возвращает ИСТИНА , когда выполняется одно или оба условия.

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

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

Работая с нашим предыдущим примером, давайте применим плату за доставку только в том случае, если общая стоимость заказа превышает 1000 долларов США и заказ содержит более 5 товаров.

Оператор ЕСЛИ И, который я использовал в ячейке h206, выглядит следующим образом:
=ЕСЛИ(И(G128>1000, F128>5), G128*2%, 0)

Функция И оценивает, если G106>1000 и если F106>5 и возвращает TRUE , когда выполняются оба условия.

Как использовать функцию ЕСЛИ с ВПР в Excel

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

Формула, которую я использовал в ячейке F152:
=ЕСЛИ(G152="Нет", ВПР(E152, $J$146:$K$152, 2, ИСТИНА)*E152, 0)

В формуле используются следующие аргументы:
лог_тест : G152= "Нет"
value_if_true : VLOOKUP(E152, $J$146:$K$152, 2, TRUE)*E152 используется для получения соответствующего процента стоимости доставки, когда G152="Нет"
value_if_false

: 0 возвращается, если G152 отличается от «Нет». В нашем случае альтернативой является выбор «Да» из раскрывающегося списка.

Примечание. Следует помнить, что я использовал формулу ВПР с аргументом приблизительного совпадения. Это означает, что ваши данные должны быть отсортированы в порядке возрастания по поисковому значению (в нашем случае по сумме заказа).

Если вам нужна дополнительная помощь, прочтите также эту статью, в которой пошагово объясняется, как использовать функцию ВПР в Excel.

Что делать дальше?

IF — это универсальная функция, которую можно использовать в самых разных сценариях. Я использую его каждый день, и я не могу представить себе мир, в котором в Excel не было бы этой функциональности.

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

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

Учебное пособие по условному форматированию Excel с примерами

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

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

Многие пользователи, особенно новички, находят его сложным и непонятным. Если вы чувствуете себя запуганным и неудобным с этой функцией, пожалуйста, не делайте этого! На самом деле, условное форматирование в Excel очень простое и удобное в использовании, и вы убедитесь в этом всего за 5 минут, когда закончите читать этот учебник 🙂

  • Условное форматирование в Excel
  • Как найти условное форматирование в Excel?
  • Как использовать условное форматирование
  • Как создать новое правило
  • Условное форматирование на основе другой ячейки
  • Применение нескольких правил к одним и тем же ячейкам
  • Остановить, если True в условном форматировании
  • Изменить правила условного форматирования
  • Копировать условное форматирование
  • Удалить правила форматирования

Что такое условное форматирование в Excel?

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

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

Где условное форматирование в Excel?

Во всех версиях Excel 2010–Excel 365 условное форматирование находится в одном и том же месте: Вкладка Главная > Группа Стили > Условное форматирование .

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

В наших примерах мы будем использовать Excel 365, наиболее популярную версию в наши дни. Тем не менее, параметры практически одинаковы во всех Excel, поэтому у вас не возникнет проблем со следованием независимо от того, какая версия установлена ​​на вашем компьютере.

Как использовать условное форматирование в Excel

Чтобы по-настоящему использовать возможности условного форматирования, вам нужно научиться использовать различные типы правил. Хорошая новость заключается в том, что какое бы правило вы ни применяли, оно определяет две ключевые вещи:

  • На какие ячейки распространяется правило.
  • Какое условие должно быть выполнено.

Итак, вот как вы используете условное форматирование Excel:

  1. В электронной таблице выберите ячейки, которые вы хотите отформатировать.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование .
  3. Из набора встроенных правил выберите то, которое соответствует вашей цели.

    В качестве примера мы собираемся выделить значения меньше 0, поэтому мы нажимаем Правила выделения ячеек > Меньше чем…

  4. В появившемся диалоговом окне введите значение в поле слева и выберите нужный формат из раскрывающегося списка справа (по умолчанию Светло-красная заливка с темно-красным текстом ).

    Когда все будет готово, Excel покажет вам предварительно отформатированные данные. Если вас устраивает предварительный просмотр, нажмите OK .

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

  • Больше или равно
  • Между двумя значениями
  • Текст, содержащий определенные слова или символы
  • Дата, попадающая в определенный диапазон
  • Повторяющиеся значения
  • N номеров сверху/снизу

Как использовать предустановленное правило с пользовательским форматированием

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

  1. В диалоговом окне предустановленного правила в раскрывающемся списке справа выберите Пользовательский формат…
  2. В диалоговом окне Format Cells переключитесь между шрифтом , Граница и Заполните вкладки , чтобы выбрать нужный стиль шрифта, стиль границы и цвет фона соответственно. Как только вы это сделаете, вы сразу же увидите предварительный просмотр выбранного формата. Когда закончите, нажмите OK .
  3. Щелкните OK еще раз, чтобы закрыть предыдущее диалоговое окно и применить пользовательское форматирование по вашему выбору.

Советы:

  • Если вы хотите больше цветов , чем предусмотрено стандартной палитрой, нажмите кнопку Больше цветов… на вкладке Заливка или Шрифт .
  • Если вы хотите применить градиентный цвет фона , нажмите кнопку Fill Effects на вкладке Fill и выберите нужные параметры.

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

Если ни одно из предустановленных правил вам не подходит, вы можете создать новое с нуля. Чтобы сделать это, выполните следующие действия:

  1. Выберите ячейки для форматирования и нажмите Условное форматирование > Новое правило .
  2. В открывшемся диалоговом окне Новое правило форматирования выберите тип правила.

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

  3. Нажмите кнопку Format… , а затем выберите нужный цвет Fill и/или Font .
  4. Дважды щелкните OK , чтобы закрыть оба диалоговых окна, и условное форматирование выполнено!

Условное форматирование Excel на основе другой ячейки

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

В качестве примера выделим в столбце B цены, превышающие пороговую цену в ячейке D2. Для этого выполните следующие шаги:

  1. Нажмите Условное форматирование > Правила выделения ячеек > Больше чем…
  2. В появившемся диалоговом окне поместите курсор в текстовое поле слева (или щелкните значок Свернуть диалоговое окно ) и выберите ячейку D2.
  3. Когда закончите, нажмите OK .

В результате все цены выше значения в D2 будут выделены выбранным цветом:

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

  • Формулы условного форматирования Excel на основе другой ячейки
  • Как изменить цвет строки на основе значения ячейки
  • Видео: формулы условного форматирования на основе другой ячейки

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

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

Например, вы можете создать 3 правила, чтобы выделять цены выше 105 долларов США красным цветом, выше 100 долларов США оранжевым цветом и выше 99 долларов США желтым цветом. Для корректной работы правил необходимо расположите их в правильном порядке . Если правило «больше 99» помещено первым, то будет применено только желтое форматирование, потому что два других правила не смогут сработать — очевидно, что любое число, превышающее 100 или 105, также больше, чем 99 🙂

Чтобы изменить правила, вам нужно сделать следующее:

  1. Выберите любую ячейку в вашем наборе данных, на которую распространяются правила.
  2. Откройте Диспетчер правил , нажав Условное форматирование > Управление правилами…
  3. Щелкните правило, которое необходимо применить первым, а затем используйте стрелку вверх , чтобы переместить его наверх. Сделайте то же самое для второго по приоритету правила.
  4. Установите флажок Stop If True рядом со всеми правилами, кроме последнего, поскольку вы не хотите, чтобы последующие правила применялись при выполнении предыдущего условия.

Что такое Stop if True в условном форматировании Excel?

Параметр Stop If True в условном форматировании запрещает Excel обрабатывать другие правила при выполнении условия в текущем правиле. Другими словами, если для одной и той же ячейки установлено два или более правил и Остановить, если для первого правила включено значение True , последующие правила игнорируются после активации первого правила.

В приведенном выше примере мы уже использовали эту опцию, чтобы игнорировать последующие правила, когда применяется правило первого приоритета. Это использование вполне очевидно. А вот еще пара примеров, где использование функции Stop If True не столь очевидно, но крайне полезно:

  • Как показать только некоторые элементы набора иконок
  • Исключить пустые ячейки из условного форматирования

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

Чтобы внести некоторые изменения в существующее правило, выполните следующие действия:

  1. Выберите любую ячейку, к которой применяется правило, и нажмите Условное форматирование > Управление правилами…
  2. В диалоговом окне Rules Manager щелкните правило, которое требуется изменить, а затем нажмите кнопку Edit Rule… .
  3. В В диалоговом окне «Редактировать правило форматирования» внесите необходимые изменения и нажмите OK , чтобы сохранить изменения.

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

Наконечник. Если вы не видите правило, которое хотите изменить, выберите Этот рабочий лист в раскрывающемся списке Показать правила форматирования для в верхней части диспетчера правил 9диалоговое окно 0317. Это отобразит список всех правил на вашем листе.

Как скопировать условное форматирование Excel

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

  1. Щелкните любую ячейку с форматированием, которое вы хотите скопировать.
  2. Нажмите Главная > Редактор форматов . Это изменит указатель мыши на кисть.

    Совет. Чтобы скопировать форматирование в несколько несмежных ячеек или диапазонов, дважды щелкните Format Painter .

  3. Чтобы вставить скопированное форматирование, щелкните первую ячейку и перетащите кисть вниз к последней ячейке в диапазоне, который вы хотите отформатировать.
  4. Когда закончите, нажмите Esc , чтобы прекратить использование кисти.
  5. Выберите любую ячейку в новом наборе данных, откройте Диспетчер правил и проверьте скопированные правила.

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

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

Я оставил самую простую часть напоследок 🙂 Чтобы удалить правило, вы можете:

  • Откройте Диспетчер правил условного форматирования , выберите правило и нажмите кнопку Удалить правило .
  • Выберите диапазон ячеек, щелкните Условное форматирование > Очистить правила и выберите вариант, который соответствует вашим потребностям.

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

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

Условное форматирование Excel — примеры (файл .xlsx)

Подробнее об условном форматировании Excel

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