Применение формулы если в excel примеры: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Использование анализа «что если» в Excel на примере

Анализ «Что Если» в Excel позволяет попробовать различные значения (сценарии) для формул.
Следующий пример поможет Вам освоить Анализ «что если»  быстро и легко.

Предположим, у вас есть книжный магазин и есть 100 книг на продажу. Вы продаете определенный % книг по самой высокой цене в $ 50 и определенный % книг по более низкой цене $ 20.

Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.

Создание различных сценариев

Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг — это различный сценарий.
Вы можете использовать «Диспетчер сценариев» для создания этих сценариев.

Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ «что если» позволит Вам сравнить результаты различных сценариев.

Итак, приступим.

1. На вкладке Данные выберите Анализ «что если» и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.

 2. Добавьте сценарий, нажав на кнопку Добавить.

3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.

4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.

5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).

И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:

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

Отчет по сценариям

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

1. Кликните по кнопке «Отчет» в Диспетчере сценариев.

2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.

Результат:

Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ «что если» в Excel.

Подписывайтесь на нас в социальных сетях, оставляйте комментарии к статье. Надеюсь пример использования анализа «что если» в Excel Вам понравился.

ТОП-20 DAX формул для Power Pivot и Power BI

Перейти к содержанию

Вы здесь:

Чтобы превратить отчеты Power BI и Excel в настоящий инструмент аналитики, вам потребуются DAX-формулы. Используя эти формулы, можно настраивать вычисления в отчетах один раз, а при изменении исходных данных в отчете показатели будут пересчитаны. В Excel для этого потребуется нажать только одну кнопку — «Обновить все».

В этой статье мы собрали самые полезные и часто встречающиеся DAX-формулы. Какие именно формулы выбрали? Все, что доступно пользователю, перечислять смысла нет. А вот несколько формул, которые позволят решить 80% задач аналитики и пригодятся для оформления отчетов, рассмотреть стоит. Например, как показать остатки на конец периода, а не сумму всех остатков, как обычно получается в сводной таблице. Или как посчитать нарастающие итоги.

Кстати, в предыдущей статье «Введение в вычисления DAX» есть примеры других полезных и более простых формул – SUM, MIN, MAX, DIVIDE, BLANK и др. Если вы ещё не читали эту статью, советуем начать с неё.

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

Часто применяемые DAX-формулы:

1. CALCULATE
2. FILTER
3. ALL
4. SUMX
5. CALENDARAUTO
6. FORMAT
7. DATEADD
8. TOTALYTD
9. LASTNONBLANK
10. CLOSINGBALANCEYEAR

Формулы для развернутой аналитики:

11. VALUES
12. KEEPFILTERS
13. REMOVEFILTERS
14. ISFILTERED
15. SELECTEDVALUE
16. HASONEVALUE
17. RELATED
18. USERELATIONSHIP
19. ADDCOLUMNS
20. SUMMARIZE

CALCULATE рассчитывает выражение с учетом измененного контекста фильтра.
Это самая главная формула в языке DAX. Ее название так и переводится – «вычислить», поэтому без такой формулы отчеты точно сделать не получится. Записывается формула просто:

CALCULATE ( <выражение> [, <фильтр1>, <фильтр2> … ] )

В вычислениях CALCULATE часто используют вместе с функциями фильтрации, например, с FILTER. Или с модификаторами – USERELATIONSHIP, REMOVEFILTERS и др. Что это за функции и как они работают – смотрите дальше.

Пример: посчитать выручку по заказам в указанном регионе.

FILTER выбирает данные в таблице или в выражении по заданному критерию. Результат ее вычисления — таблица. Синтаксис функции:

FILTER ( <таблица>, <фильтр> )

В мерах FILTER обычно используют вместе с другими формулами, например, вместе с CALCULATE, CONTROWS и др.

Примеры:

1) Найти количество заказов по региону «Юг».

С помощью формулы FILTER выбираются заказы по региону Юг, а CONTROWS считает, сколько таких заказов было.

2) Найти сумму убытков по заказам, в которых выручка меньше расходов.

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

ALL ( <таблица> или <столбец> [, <столбец1> ,<столбец2>, … ] )

Так же как FILTER, функция ALL используется с другими формулами, например с CALCULATE. Если вам нужно убрать фильтры не со всей таблицы полностью, а только с некоторых столбцов, можно воспользоваться похожей формулой – ALLEXCEPT. С помощью ALL можно делать различные вычисления – найти долю от общего объема продаж, соотношения или проценты и т.д.

Пример: найти долю выручки от продаж товара.

С помощью DIVIDE выручка по товару делится на общий итог от продаж. Для расчета общего итога как раз и используется формула ALL – она снимает все фильтры, примененные к справочнику товаров. Точно такой же результат получится, если вместо ALL использовать функцию REMOVEFILTERS, о которой написано дальше.

Поведение функции ALL можно проверить с помощью среза по товарам. Если в срезе поставить галочки рядом с некоторыми товарами, то видно, что сумма процентов в итоговой строке таблицы не равна 100%. Доля товара считается без учета того, выбраны или нет товары в срезе. Если нужно посчитать долю только с учетом выбранных товаров – так, чтобы итоговая сумма процентов в таблице всегда была равна 100%, вместо ALL используйте ALLSELECTED.

Для чего нужна формула SUMX? Она позволяет суммировать значения выражения по строкам. Синтаксис формулы:

SUMX ( <таблица>, <выражение> )

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

Самый простой пример вычислений с SUMX – расчет суммы продаж по формуле = количество х цена:

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

Таблица, без которой точно не обойдется ни один отчет в Power BI и Power Pivot – это таблица дат, Календарь. С помощью такой таблицы можно создавать аналитику по периодам и делать вычисления, связанные с датами.

Календарь можно создать несколькими способами, самый простой из которых в Power BI –  сделать вычисляемую таблицу с помощью формулы CALENDARAUTO. Эта формула автоматически найдет первую и последнюю дату и создаст справочник.

Создается календарь (таблица дат) в Power BI просто:

Также таблицу дат в Power BI можно создать с помощью CALENDAR, но в этой формуле потребуется указать границы – наибольшую и наименьшую дату.

А вот в Power Pivot в Excel таких формул для календаря нет. Так же, как нет возможности создавать вычисляемые таблицы. Поэтому календарь в Power Pivot можно сделать с помощью специальной кнопки «Таблица дат» (для этого даже предусмотрена специальная кнопка) или с помощью загрузки из Power Query.

Для создания «правильной» таблицы дат в Power BI вам пригодится формула FORMAT. Функция FORMAT преобразует значение в текст в указанном формате. Это аналог формулы ТЕКСТ в «обычном» Excel.
Синтаксис формулы:

FORMAT ( <значение>, <формат строки> )

Пример: определить дни недели для дат.

С помощью формулы FORMAT и шаблона "DDD" в созданном вычисляемом столбце записаны даты в виде сокращенных названий дней недели – пн, вт, ср и т.д. Кроме шаблона форматирования "DDD" в формулу можно добавить другие форматы, например, "DDDD" для отображения дней недели полностью (понедельник, вторник и т. д.)

Примеры форматов строк для дат, записываются в кавычках:

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

Полезные ссылки:

• Пользовательские числовые форматы
• Пользовательские форматы дат

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

DATEADD ( <даты>, <количество интервалов>, <вид интервала> )

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

Пример: рассчитать значение показателя за прошлый год.

продажи пр. год = CALCULATE ( [продажи], DATEADD ( 'Календарь'[Date], -1, YEAR ) )

Формула TOTALYTD вычисляет значение выражения с начала года в текущем контексте. То есть с помощью TOTALYTD можно посчитать нарастающий итог по показателю за год. Чтобы рассчитать нарастающие итоги по кварталам или месяцам, вам потребуются похожие формулы: TOTALQTD для квартала и TOTALMTD для месяца.
Записывается формула просто:

TOTALYTD ( <выражение>, <даты> [, <фильтр> ] [, <конец года>] )
TOTALQTD ( <выражение>, <даты> [, <фильтр> ] )
TOTALMTD ( <выражение>, <даты> [, <фильтр> ] )

Пример. Посчитать выручку нарастающим итогом за квартал и год.

LASTNONBLANK определяет последнее непустое значение в столбце. Чтобы найти первое непустое значение в столбце – FIRSTNONBLANK. Синтаксис формул:

FIRSTNONBLANK ( <столбец>, <выражение> )
LASTNONBLANK ( <столбец>, <выражение> )

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

Пример. Найти остатки ТМЦ на конец периода.

CLOSINGBALANCEYEAR вычисляет значения выражения для последней даты года. Аналогично расчет для последней даты квартала – CLOSINGBALANCEQUARTER, для месяца — CLOSINGBALANCEMONTH. Так можно рассчитать остатки товаров или денег на счете на конец периода.

CLOSINGBALANCEYEAR ( <выражение>, <даты> [, <фильтр> ][, <конец года> ] )
CLOSINGBALANCEQUARTER ( <выражение>, <даты> [, <фильтр> ] )
CLOSINGBALANCEMONTH ( <выражение>, <даты> [, <фильтр> ] )

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

Пример: найти сумму значений на последнюю дату месяца.

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

Функция VALUES возвращает значения столбца или таблицы:

• если указать столбец, то VALUES вернет список его уникальных значений;
• если как аргумент указать таблицу, то формула вернет список всех строк таблицы с сохранением повторений.

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

VALUES ( <таблица или столбец> )

Пример: посчитать, сколько менеджеров работало в каждом городе.

Для сравнения в таблицу добавлен столбец с похожими вычислениями с помощью формулы ALL:

Формула ALL игнорирует фильтры, поэтому в строках одинаковые цифры. Формула VALUES сохраняет фильтры и выдает правильный результат.

KEEPFILTERS – это еще одна полезная формула, которую используют вместе с CALCULATE и CALCULATETABLE. При вычислениях формула CALCULATE перезаписывает существующие фильтры в столбцах отчета. А если перезапись фильтров не требуется, и к действующим нужно просто добавить новые фильтры, это можно сделать с помощью KEEPFILTERS. Формула записывается так:

KEEPFILTERS ( <выражение> )

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

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

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

REMOVEFILTERS очищает фильтры в указанной таблице или столбце.
Синтаксис формулы:

REMOVEFILTERS ( [ <таблица> или <столбец>[, <столбец>[, …] ] ] )

С помощью этой функции нельзя получить «промежуточную» таблицу, а можно только убрать фильтры. Формула REMOVEFILTERS применяется в вычислениях с CALCULATE. Например, с ее помощью можно посчитать долю (%) продаж товара от общей суммы продаж, как в примере с ALL.

Пример: найти долю от продаж товара по отношению к общей сумме выручки.

Что лучше использовать для вычислений в этом примере – ALL или REMOVEFILTERS? Если в приложении доступна формула REMOVEFILTERS, и в формуле вам просто нужно очистить фильтры, то рекомендуется использовать REMOVEFILTERS.

Формула ISFILTERED определяет, применен ли прямой фильтр к столбцу. Если такой фильтр есть, то функция возвращает TRUE. С помощью этой формулы можно, например, управлять отображением итогов.
Синтаксис формулы:

ISFILTERED ( <столбец> )

Пример: посчитать среднюю цену по товарам, а по группам цену не показывать.

Функция SELECTEDVALUE возвращает отфильтрованное значение. Если столбец отфильтрован по одному отдельному значению, то его можно получить с помощью SELECTEDVALUE, в противном случае функция возвращает «альтернативный результат».
С помощью этой формулы можно создавать комментарии или делать вычисления, которые зависят от выделенного (отфильтрованного) значения.
Записывается формула просто:

SELECTEDVALUE ( <столбец> [, <альтернативный результат> ] )

Пример: заголовок для одного выбранного года.

Более интересные комментарии и заголовки можно создавать с помощью сочетаний SELECTEDVALUE и HASONEVALUE.

HASONEVALUE проверяет, содержит ли контекст столбца только одно значение (или контекст должен быть отфильтрован до одного значения). Если это верно, то функция возвращает значение TRUE.
Эту функцию удобно применять, если вам нужно сделать расчет только по единичным значениям. А также для написания комментариев и пояснений.
Синтаксис формулы:

HASONEVALUE ( <столбец> )

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

Функция RELATED ищет и возвращает связанное значение из другой таблицы. Эта формула немного похожа на ВПР в «обычном» Excel. RELATED относится к функциям связи – функции этой категории используют связи между таблицами и управляют ими. Чтобы создать вычисления с помощью RELATED, между таблицами должна существовать активная связь «один ко многим».
Записывается функция просто:

RELATED ( <столбец> )

Как использовать RELATED? В сочетании с другими формулами, например с SUMX.

Пример: посчитать план выручки без НДС. Данные о ставках НДС есть в отдельной таблице.

В случае, если между таблицами нет связи, вы можете воспользоваться функцией LOOKUPVALUE Эта функция так же, как RELATED, ищет и возвращает связанное значение из другой таблицы. Отличие в том, что для LOOKUPVALUE связь между таблицами создавать не требуется, а поиск можно выполнять по нескольким столбцам. Несмотря на то, что связь между таблицами не нужна, LOOKUPVALUE, как и RELATED, все-таки требует, чтобы искомые строки и их сочетания в просматриваемых столбцах были уникальными. Если это условие не выполняется, LOOKUPVALUE выдаст сообщение об ошибке.

USERELATIONSHIP активирует связь в вычислении в соответствии существующей связью между двумя столбцами. Сама по себе отдельно эта функция не используется, ее можно применять в формулах CALCULATE, TOTALYTD и др. Функция USERELATIONSHIP, как и RELATED, относится к функциям связи. Но в отличие от RELATED, для USERELATIONSHIP вовсе не требуется, чтобы существующие связи между таблицами были активными.
Записывается формула просто:

USERELATIONSHIP ( <столбец1>, <столбец2> )

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

ADDCOLUMNS добавляет вычисляемые столбцы к таблице. На выходе рассчитывается таблица с исходными и добавленными столбцами.
Синтаксис формулы:

ADDCOLUMNS ( <таблица>, <столбец>, <выражение>[, <столбец2>, < выражение2>]…)

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

Здесь функция ADDCOLUMNS создает новый столбец в таблице с названием «показатель», а SUMX суммирует его значения.

SUMMARIZE – одна из очень часто используемых DAX-формул. Она возвращает таблицу для запрошенных значений, объединенных в наборы групп. Обычно в расчетах эта формула используется для создания комбинации значений вместо таблицы со всеми данными.
Синтаксис формулы:

SUMMARIZE ( <таблица>, <столбец группировки> [, <столбец группировки> ] … [, <имя>, <выражение> ] … )

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

Теги: ExcelPower BI

Автор: Станислав Салостей

Вверх

Функция ЕСЛИ в Excel — обзор, примеры Как использовать?

Функция ЕСЛИ в Excel оценивает, выполняется ли заданное условие, и возвращает значение в зависимости от того, является ли результат «истинным» или «ложным». Это условная функция Excel, которая возвращает результат на основе выполнения или невыполнения заданных критериев.

Например, формулу ЕСЛИ в Excel можно применить следующим образом:

 «=ЕСЛИ(условие A,»значение B»,»значение C»)»

 Функция ЕСЛИ Excel возвращает «значение B», если условие A выполняется и возвращает «значение C», если условие A не выполняется.

Часто используется для логических интерпретаций, помогающих в принятии решений.

Содержание

• Что такое функция ЕСЛИ в Excel?
  • Синтаксис функции ЕСЛИ Excel
  • Как использовать функцию ЕСЛИ в Excel?
    • Пример № 1
    • Пример № 2
    • Пример № 3
    • Пример № 4
    • Пример № 5
  • Рекомендации по использованию нескольких операторов IF0020
  • Рекомендуемые статьи

Синтаксис функции IF Excel

Синтаксис функции IF показан на следующем изображении:

Функция Excel принимает следующие аргументы:

• Это относится к состоянию, которое необходимо оценить. Условие может быть значением или логическим выражением.
• Value_if_true: Это значение, возвращаемое в результате, когда условие истинно.
• Value_if_false: Это значение, возвращаемое в результате, когда условие «ложно».

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

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

Оператор	.
<=	Меньше или равно
<>	Не равно

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

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

Вы можете скачать этот шаблон Excel функции ЕСЛИ здесь – Шаблон Excel функции ЕСЛИ

Пример #1

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

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

Формула ЕСЛИ выглядит следующим образом:

«=ЕСЛИ(B2=«Да», «Жизнь возможна», «Жизнь невозможна»)

На следующем рисунке показана формула ЕСЛИ, примененная к ячейке C2.

На следующем рисунке показано, как формула ЕСЛИ применяется к диапазону ячеек C2:C5.

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

Результаты в таблице ниже показывают, что жизнь возможна на планете Земля.

Блок-схема общей функции IF Excel

Блок-схема функции IF для Марса (пример № 1)

Блок-схема функции IF для Юпитера и Венеры такая же, как и для функции IF Марс (Пример №1).

Блок-схема функции IF для Земли

Таким образом, функция IF excel позволяет проводить логические сравнения между значениями. modus operandi функции ЕСЛИ формулируется следующим образом: если что-то верно, то сделайте что-нибудь; в противном случае сделайте что-нибудь еще.

Пример #2

В следующей таблице показан список лет. Мы хотим узнать, является ли данный год високосным или нет.

В високосном году 366 дней; дополнительный день 29 февраля. Критерии високосного года формулируются следующим образом:

1. Год будет точно делиться на 4 и не будет делиться на 100 или
2. Год будет точно делиться на 400.

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

Мы используем функцию ОСТАТ, чтобы найти остаток после деления делимого на делитель.

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

Применим функцию ОСТАТ к следующим условиям:

Если ОСТАТ(год,4)=0 и ОСТАТ(год,100)<>(не равно) 0, то год високосный.

или

Если MOD(year,400)=0, то год високосный; в противном случае год не является високосным.

Формула ЕСЛИ выглядит следующим образом:

«=ЕСЛИ(ИЛИ(И((MOD(год,4)=0),(MOD(год,100)<>0)),(MOD(год,400 )=0)), «Високосный год», «Не високосный год»)»

Аргумент «год» относится к эталонному значению.

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

На следующем рисунке показано, как формула ЕСЛИ применяется к диапазону ячеек B2:B18.

Следующая таблица показывает 1960, 2028 и 2148 годы как високосные, а остальные годы как невисокосные.

Результат формулы Excel отображается для диапазона ячеек B2:B18 на следующем изображении.

Пример #3

Следующая таблица показывает список водителей и направления, по которым они добирались до места назначения. Ему предшествует изображение перекрестка дорог, объясняющее повороты водителей и их пункты назначения. Поворот направо ведет к городу B, а поворот налево ведет к городу C. Определите пункт назначения водителя в город B и город C.

Изображение пересечения дорог

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

• Если водитель поворачивает направо, он/она достигает города B.
• Если водитель поворачивает налево, он/она достигает города C.

Мы используем следующую формулу ЕСЛИ для найти пункт назначения:

 «=ЕСЛИ(B2=»Левый», «Город C», «Город B»)»

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

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

На изображении ниже показана формула ЕСЛИ, примененная к диапазону.

Результат формулы ЕСЛИ и адресаты отображаются на следующем изображении.

Результат показывает, что шесть водителей достигли города C, а оставшиеся четыре — города B.

Пример #4

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

Давайте перечислим имена элементов в столбце A и количество элементов в столбце B. Список данных, которые необходимо проверить для всего списка элементов, показан в ячейке E2 на изображении ниже.

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

Функция ВПР ищет значения, относящиеся к количеству элементов, а функция ЕСЛИ проверит, больше ли число элементов больше нуля или нет.

Применим следующую формулу ЕСЛИ в ячейке F2:

«=ЕСЛИ(ВПР(E2,A2:B11,2,0)=0, «Элемент недоступен»,«Элемент доступен»)»

Если значение поиска элемента равно 0, то элемент недоступен; в противном случае товар доступен.

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

Выберите «летучая мышь» в ячейке элемента E2, чтобы узнать, доступен ли предмет в инвентаре (как показано на следующем рисунке).

Пример №5

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

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

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

Синтаксис функции вложенности ЕСЛИ определяется следующим образом:

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

Следующая таблица представляет диапазон оценок и оценок соответственно.

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

«=ЕСЛИ((B2>=95),«A»,ЕСЛИ(И(B2>=85,B2<=94),«B»,ЕСЛИ(И(B2>=75,B2<=84) ,"C",IF(AND(B2>=61,B2<=74),"D","F"))))"

Функция ЕСЛИ проверяет логическое условие, как показано в формуле ниже:

«=ЕСЛИ(логическая_проверка, [значение_если_истина],[значение_если_ложь])»

Мы разделим вышеупомянутую вложенную формулу и проверим операторы ЕСЛИ, как показано ниже:

Первая логическая проверка: B2>=95

Если формула возвращает,

• Значение_если_истина, выполните: «A» (Уровень А) иначе (запятая) введите значение_если_ложь
• Значение_если_ложь, затем формула найдет другое условие ЕСЛИ и введите условие ЕСЛИ логическое выражение 1) и B2<=94(логическое выражение 2)

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

  Если формула возвращает значение,

  • Значение_если_истина, выполнить: «B» (Уровень B) иначе (запятая) ввести значение_если_ложь
  • Значение_если_ложь, затем формула найдет другое условие ЕСЛИ и введет условие ЕСЛИ

  Третий логический тест: B2>=75(логическое выражение 1 ) и B2<=84(логическое выражение 2)

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

  Если формула возвращает значение,

  • ,  выполнить: «C» (уровень C), иначе (запятая) введите значение_если_ложь
  • значение_если_ложь, затем формула находит другое условие ЕСЛИ и вводит условие ЕСЛИ

  Четвертый логический тест : B2>=61(логическое выражение 1) и B2<=74(логическое выражение 2)

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

  Если формула возвращает значение

  • Значение_если_истина, выполните: «D» (уровень D) иначе (запятая) введите значение_если_ложь
  • Значение_если_ложь, выполнить: «F» (Уровень F)
  • Наконец, закрыть скобки.

   На изображении ниже показан результат применения формулы ЕСЛИ к диапазону.

  На следующем изображении показана вложенная формула ЕСЛИ, примененная к диапазону.

  Оценки учащихся перечислены в следующей таблице.

  Рекомендации для нескольких операторов IF

  Рекомендации для нескольких операторов IF перечислены ниже:

  • Используйте вложенную функцию IF в ограниченной степени, так как несколько операторов IF требуют тщательного обдумывания, чтобы быть точными.
  • Множественные операторы ЕСЛИМножественные операторы ЕСЛИВ Excel множественные условия ЕСЛИ — это операторы ЕСЛИ, содержащиеся в другом операторе ЕСЛИ. Они используются для проверки нескольких условий одновременно и возвращают разные значения. Дополнительные операторы ЕСЛИ могут быть включены в аргументы «значение, если истина» и «значение, если ложь» стандартной формулы ЕСЛИ. Для этого требуется несколько круглых скобок (), с которыми часто трудно справиться. Excel предоставляет способ проверить цвет каждой открывающей и закрывающей скобки, чтобы избежать этой ситуации. Цвет последней закрывающей скобки всегда будет черным, обозначая конец оператора формулы.
  • Всякий раз, когда мы передаем строковое значение для аргументов «value_if_true» и «value_if_false» или проверяем ссылку на строковое значение, заключайте строковое значение в двойные кавычки. Передача строкового значения без кавычек приведет к ошибке «#ИМЯ?» ошибка.

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

  1. Что такое функция ЕСЛИ в Excel?

  Функция Excel ЕСЛИ — это логическая функция, которая проверяет заданные критерии и возвращает одно значение для «истинного» и другое значение для «ложного» результата.

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

  Аргументы следующие:
  1. Логическая_проверка — относится к значению или условию, которое тестируется.
  2. Value_if_true — это значение, возвращаемое, когда условие logical_test равно «истине».
  3. Value_if_false — это значение, возвращаемое, когда условие logical_test равно «ложь».

  «logical_test» является обязательным аргументом, тогда как «value_if_true» и «value_if_false» являются необязательными аргументами.

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

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

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

  «=ЕСЛИ (условие 1_«истина», сделать что-то, ЕСЛИ (условие 2_«истина», сделать что-то, ЕСЛИ (условие 3_ «истина», сделать что-то что-нибудь, еще что-нибудь сделай)))»

  3. Как использовать функцию ЕСЛИОШИБКА в Excel?

  Функция ЕСЛИОШИБКАФункция ЕСЛИОШИБКАФункция ЕСЛИОШИБКА в Excel проверяет формулу (или ячейку) на наличие ошибок и возвращает указанное значение вместо ошибки.Подробнее в Excel используется для поиска и устранения ошибок в формулах и вычислениях.

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

  Синтаксис следующий:
  «=ЕСЛИОШИБКА(значение, значение_если_ошибка)»
  Где,
  • Значение – Относится к тестируемому значению.
  • Value_if_error — относится к значению, которое будет возвращено, если формула вернет ошибку.
  Оба аргумента являются обязательными.

  Видео о функции ЕСЛИ в Excel

   

  Рекомендуемые статьи

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

  • Что такое логическая проверка в Excel?Что такое логическая проверка в Excel?Логическая проверка в Excel приводит к аналитическому результату, истинному или ложному. Наиболее часто используемым логическим тестом является оператор равенства «=».Подробнее
  • «Не равно» в ExcelАргумент «Не равно» в Excel вставляется с выражением <>. Две скобки, расположенные вдали друг от друга, представляют собой команду Excel для аргумента «Не равно», а затем пользователь выполняет проверку Excel, если два значения не равны друг другу.Подробнее
  • Проверка данных ExcelПроверка данных ExcelПроверка данных в Excel помогает контролировать тип ввода, вводимого пользователем на листе.Подробнее

  Excel IF Formula: от простого к сложному
  

  В том числе: Функция ЕСЛИ Excel с несколькими критериями, вложенные ЕСЛИ, И, ИЛИ Логические функции Excel

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

  Выполняет простую логическую проверку (является утверждение ИСТИНА или ЛОЖЬ?) и, в зависимости от результата сравнения, возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.

  Синтаксис функции ЕСЛИ() следующий:

  = ЕСЛИ ( логическое_тест , [значение_если_ИСТИНА] , [значение _если_ЛОЖЬ] )

  Первые два атрибута обязательны, т.е. формула не работает и выдает сообщение об ошибке.

  • logical _test — заполнитель для условия, которое мы хотим проверить
  • value_if_TRUE — атрибут, в котором нам нужно указать значение (текст или другую функцию), которое возвращает формула ЕСЛИ, когда результат logical_test равен TRUE

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

  • value_if_FALSE — атрибут значения, текста или другой формулы, которую должна вернуть функция ЕСЛИ(), когда результат логической проверки равен ЛОЖЬ.

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

  ЕСЛИ что-то верно , THEN вернуть первое значение , ELSE – вернуть второе значение.

  Простейшую функцию IF() можно написать только с первыми двумя параметрами.

  Например, эта формула абсолютно верна:

   =ЕСЛИ(C2=1,"Здравствуйте!") 

  Эта формула проверяет заданное нами условие (C2=1) и если оно истинно, то формула возвращает текст сообщение («Привет!»).

  В противном случае возвращается значение по умолчанию FALSE.

  Интересный факт!!! Вы можете использовать другой более простой тест в Excel для определенного условия. Просто поместите формулу вида «=C2>C3» в ячейку и нажмите клавишу ВВОД. Если это утверждение истинно, формула возвращает значение по умолчанию — ИСТИНА. В противном случае (т. е. когда значение в ячейке C2 НЕ больше, чем значение в ячейке C3), формула вернет другое значение по умолчанию — FALSE. В этих случаях мы используем функцию ЕСЛИ(); когда мы хотим инициировать какие-то дальнейшие действия или вычисления в зависимости от результатов логического теста.

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

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

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

   =IF(C3="Hello!",1, 0) 

  Если значением ячейки C3 является текст «Hello!» то эта формула возвращает одно (1) значение.

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

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

  Например, заменим единицу (как value_if_true) на другую формулу «C3+C4» и заменим нулевое значение (0) на функцию TODAY().

  Наша исходная формула выглядит следующим образом:

   =ЕСЛИ(C3="Привет!",C2+C3,СЕГОДНЯ()) 

  Это означает — ЕСЛИ значение в ячейке C2 равно единице, ТО выполнить сложение C2+C3.

  ИНАЧЕ (т. е. когда C2 не равно «Привет!»), ТОГДА выполнить функцию СЕГОДНЯ() и вернуть ее результат.

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

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

  Кроме того, вы можете использовать так называемую вложенную функцию IF() и заменить [value_if_false] другой функцией IF().

  Чаще всего это делается, когда вам нужно создать сравнение с несколькими вариантами ответов, например водопад.

  Последний атрибут value_if_FALSE — это значение, которое мы хотим, чтобы формула возвращала, если результат ВСЕХ логических проверок в формуле равен FALSE.

  Мы знаем, что нам нужно использовать вложенный IF() (т. е. один IF() внутри другого оператора IF()), когда у нас есть ключевое слово «но» внутри предложения с логическим тестом.

  Взгляните на скриншот ниже.

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

  Мы знаем, что нам нужно использовать вложенный ЕСЛИ(), когда в логическом тесте есть ключевое слово «но».

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

  Функция ЕСЛИ() часто используется с другими логическими функциями, такими как функции И() или ИЛИ(), которые мы обсудим далее.

  Давайте рассмотрим несколько практических примеров…

  ТЕСТ-КЕЙС №1

  Добавление слова «Хорошо», если доход больше 15000

  9000 Это типичное использование функции IF .

  Давайте отметим все приложения, если их доход превышает 15 000.

  Шаг 1. Перейдите к первой ячейке, куда нужно вставить формулу (в данном примере ячейка C5).

  Начните формулу со знака равенства (=), добавьте ключевое слово ЕСЛИ и открывающие круглые скобки, чтобы формула выглядела так:

   =ЕСЛИ( 

  Шаг 2. Первый аргумент функции ЕСЛИ(): логический тест

  В этом примере мы хотим пометить значение в ячейке B5 (Доход), если оно больше 15000.

  Итак, продолжим формулу =ЕСЛИ(B5>15000,

  Не забудьте поставить запятая после того, как этот шаг будет готов

  Эта запятая используется Excel в качестве разделителя для разделения аргументов друг от друга

  Совет для профессионалов: Мы можем либо ввести числовое значение в логическом тесте, либо использовать ссылку на ячейку, которая содержит такое значение (ячейка C2 в нашем примере). Второй подход предпочтительнее, потому что он дает нам больше гибкости в будущем. После того, как мы скопируем/вставим формулу во весь диапазон, все, что нам нужно сделать в этом случае — если вы хотите изменить это значение — это перейти в ячейку реферала и изменить его там без необходимости переписывать формулу для всех связанных ячеек. .

  ПРИМЕЧАНИЕ:  Имейте в виду, что если мы хотим использовать этот подход, нам нужно исправить ссылку на ячейку, чтобы она выглядела как $C$2. Этот фиксированный адрес ячейки необходим для копирования формулы во весь диапазон. Без этого формула вернет неверный результат. Мы можем просто нажать F4, чтобы зафиксировать ссылку на ячейку в формуле (убедитесь, что курсор находится рядом с соответствующим адресом ячейки)

  Формула будет выглядеть так:

   =ЕСЛИ(B5>$C$2, 

  Шаг 3. Вторым аргументом функции ЕСЛИ(), как мы помним, является значение_если_ИСТИНА

  Введите текст «Хорошо» в качестве второго атрибута в формулу:

   =ЕСЛИ(B5>$C$2, «Хорошо», 

  Не забудьте поставить запятую, когда этот шаг будет готов.

  Также , будьте осторожны — если вы вводите текстовое значение, вам нужно поместить его в двойные кавычки (« ваш текст »)

  Совет для профессионалов: Мы можем либо ввести числовое значение в логическом тесте, либо использовать ссылка ячейки на ячейку, которая содержит такое значение (в нашем примере ячейка C3). Если в будущем мы решим заменить слово «Хорошо» на что-то другое (например, «Неплохо»), то все, что нам нужно нужно перейти в ячейку C3 и изменить этот текст в ячейке без необходимости повторного ввода всей формулы. 0004

  Наша формула принимает следующий вид:

   =ЕСЛИ(B5>$C$2,$C$3, 

  ПРИМЕЧАНИЕ.  Не забудьте исправить ссылку на ячейку, чтобы она выглядела как адрес $C$3. Нажмите F4, чтобы ввести абсолютный Без этого формула вернет неверный результат

  Шаг 4. Третий аргумент функции ЕСЛИ() — значение_если_ЛОЖЬ . Этот атрибут можно пропустить (в таких случаях Excel возвращает значение по умолчанию — ЛОЖЬ). ). Однако, если мы хотим сделать это более эстетично, пусть формула возвращает пустое значение. Пустое значение в Excel равно ничему между двумя кавычками, например «». Итак, окончательный вариант формулы становится:

   =ЕСЛИ(B5>$C$2,$C$3,"") 

  ПРИМЕЧАНИЕ.  Не забудьте закрыть скобки и нажать ENTER.

  Шаг 5. Перетащите формулу вниз на весь диапазон.

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

  ТЕСТ-КЕЙС №2

  Добавление слова «Хорошо», если доход больше 15000 и меньше 200000004

  Шаг 1. Перейдите к первой ячейке, в которую нужно вставить формулу (в данном примере ячейка D5).

  Начните формулу со знака равенства (=), добавьте ключевое слово ЕСЛИ и открывающие скобки, чтобы формула выглядела так:

   =ЕСЛИ( 

  Шаг 2. Первый аргумент функции ЕСЛИ(): логический тест

  В этом примере мы хотим пометить значение в ячейке B5 (Доход), если оно больше 15000 и меньше 20 000.

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

  Функция AND() может принимать до 255 условий в качестве аргументов.

  Продолжим формулу и добавим второе ключевое слово AND с открывающей скобкой.

  Сделав это, мы можем ввести оба условия для логического теста внутри блока AND() и закрыть его вторую скобку.

  Формула выглядит так:

   =ЕСЛИ(И(B5>$D$2,B5<$D$3), 

  ПРИМЕЧАНИЕ.  Не забудьте исправить ссылку на ячейку, чтобы она выглядела как $D$2 и $ Адрес D$3

  Нажмите F4, чтобы сделать абсолютную ссылку.

  Без этого формула вернет неправильный результат после того, как мы перетащим ее на весь диапазон.

  ПРИМЕЧАНИЕ. Не забудьте поставить запятую, когда этот шаг будет готов.

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

  Шаг 2. Второй аргумент функции ЕСЛИ(), как мы помним, это значение_если_ИСТИНА.

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

   =ЕСЛИ(И(B5>$D$2,B5<$D$3),$C$3, 

  ПРИМЕЧАНИЕ.  Не забудьте поставить запятую после атрибута, когда этот шаг будет готов. Будьте внимательны. Если вы вводите текстовое значение, вам нужно поместить его в двойные кавычки (« ваш текст »)

  Шаг 3. Третий аргумент функции ЕСЛИ() — это значение_если_ЛОЖЬ.

  Мы можем пропустить этот атрибут, и Excel возвращает значение по умолчанию — ЛОЖЬ

  Однако более эстетичный способ сделать это — ничего не возвращать (или пустое значение)

  Ничто в Excel не обозначается двойными кавычками без пробела:

   =ЕСЛИ(И(B5>$D$2,B5<$D$3),$C$3,"") 

  ПРИМЕЧАНИЕ: Don не забудьте закрыть скобку и нажать ENTER.

  Шаг 4. Перетащите формулу вниз на весь диапазон.

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

  ТЕСТ-КЕЙС №3

  Добавление слова «Хорошо», если Выручка больше 15000 и меньше 20000. Добавление слова «Исключительно», если Выручка больше или равна 20 000. В противном случае, сохраняя значения

  На этот раз мы пометим все приложения как «хорошие», если их доход превышает 15 000 и меньше 20 000.

  Кроме того, мы пометим их как «исключительные», если их доход больше или равен 20 000

  Шаг 1. Перейдите к первой ячейке, в которую нам нужно вставить формулу (ячейка E5 в этом примере). .

  Начните формулу со знака равенства (=), добавьте ключевое слово ЕСЛИ и открывающие скобки, чтобы формула выглядела так:

   =ЕСЛИ( 

  Шаг 2. Первый аргумент функции ЕСЛИ() является логической проверкой.

  Первая часть формулы в этом примере такая же, как мы писали в предыдущем примере:

   =ЕСЛИ(И(B5>$E$2,B5<$E$3),$C$3, 

  ПРИМЕЧАНИЕ :  Не забудьте поставить запятую после атрибута, когда этот шаг будет готов.

  Шаг 3. Обычно третьим аргументом функции ЕСЛИ() является значение_если_ЛОЖЬ.

  чтобы добавить второе условие, когда значение в ячейке B5 больше или равно 20 000.

  Мы можем сделать это, если воспользуемся вложенной функцией IF().

  Теперь добавим еще один блок IF() в качестве третьего параметра:

   =IF(AND(B5>$E$2,B5<$E$3),$C$3,IF( 

  Шаг 4. Добавьте второй логический тест и второе значение_если_ИСТИНА

  В нашем примере формула должна возвращать ключевое слово «Исключение», если значение в ячейке B5 больше или равно 20 000.

  Давайте напечатаем это:

   =ЕСЛИ(И (B5>$E$2,B5<$E$3),$C$3,ЕСЛИ(B5>=$E$3,"Исключительно", 

  ПРИМЕЧАНИЕ:  Если вы вводите значение в виде текста, вам нужно поместить его в двойные кавычки (« ваш текст »)

  Шаг 5. Нам нужно написать значение, которое формула будет вернуть, если результат обоих логических тестов FALSE.

  В нашем примере формула должна возвращать необработанное значение дохода.

  Поместим этот атрибут в качестве третьего аргумента во второй блок ЕСЛИ():

   =ЕСЛИ(И(B5>$E$2,B5<$E$3),$C$3,IF(B5>=$E$3 ,"Исключительно",B5)) 

  ПРИМЕЧАНИЕ:  Не забудьте закрыть скобки.

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

  Шаг 6. Перетащите формулу вниз на весь диапазон.

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

  ТЕСТ-КЕЙС №4

  Добавление слова «Флаг», если Выручка больше или равна 20 000 ИЛИ меньше или равна 15 000

  Давайте пометим все приложения, если их доход <= 15 000 или >= 20 000

  Шаг 1. Перейдите к первой ячейке, в которую нам нужно вставить формулу (ячейка D5 в этом примере).

  Начните формулу со знака равенства (=), добавьте ключевое слово ЕСЛИ и открывающие круглые скобки, чтобы формула выглядела так:

   =ЕСЛИ( 

  Шаг 2. Первый аргумент функции ЕСЛИ(): логический тест

  В этом примере мы хотим пометить значение в ячейке B5 (Доход), если оно меньше или равно 15000 ИЛИ больше или равно 20000.

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

  Продолжим формулу и добавим второе ключевое слово ИЛИ с открытой скобкой.

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

  Формула выглядит так:

   =ЕСЛИ(ИЛИ(B5>=$F$2,B5=<$F$3), 

  ПРИМЕЧАНИЕ.  Нажмите кнопку F4 на клавиатуре, чтобы задать абсолютную ссылку.

  Шаг 3. Второй аргумент функции ЕСЛИ(), как мы помним, это значение_если_ИСТИНА .

  Добавим этот атрибут в формулу:

   =ЕСЛИ(И(B5>$D$2,B5<$D$3),"Флаг", 

  ПРИМЕЧАНИЕ:  Если вы вводите текстовое значение, вам нужно поместите его в двойные кавычки (« ваш текст »)

  Шаг 4. Третий аргумент функции ЕСЛИ() — это значение_если_ЛОЖЬ.

  Добавим двойные кавычки без пробела, чтобы вернуть пустое значение:

   =ЕСЛИ(И(B5>$D$2, B5<$D$3),"Флаг","") 

  ПРИМЕЧАНИЕ: Не забудьте закрыть скобку и нажать ENTER.

  Шаг 5. Перетащите формулу вниз на весь диапазон.

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

  TEST CASE №5

  Использование других формул внутри логических функций

  Рассчитаем разницу между фактическим доходом и бюджетным доходом и отметим те случаи, когда разница превышает +/- 10%.

  Шаг 1. Самый простой способ начать строить такую ​​сложную формулу – вычислить базовое значение (в нашем случае это пороговое значение +/-10%).

  Шаг 2. Перейдите к первой ячейке, куда нужно вставить формулу (в данном примере ячейка D23).

  Начните формулу со знака равенства (=), добавьте формулу для расчета разницы (фактическое деление на бюджет минус один:

   =B23/C23 - 1 

  Шаг 3. Добавим блок IF() и соответствующий логический тест.

  Это ситуация, когда мы можем использовать логическую функцию ИЛИ(), поскольку нам нужно пометить значение, если оно больше +10% или меньше -10%:

   =ЕСЛИ(ИЛИ(B23/C23 - 1 >10%,B23/C23 - 1<-10%), 

  Шаг 4. Вторым аргументом функции ЕСЛИ(), как мы помним, является значение_если_ИСТИНА

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

   =ЕСЛИ(ИЛИ(B23/C23 - 1>10%,B23/C23 - 1<-10%),B23/C23 - 1, 

  Шаг 5. Третий аргумент функции ЕСЛИ(): a value_if_FALSE

  Давайте добавим двойную кавычку без пробела, чтобы вернуть пустое значение:

   =ЕСЛИ(ИЛИ(B23/C23 - 1>10%,B23/C23 - 1<-10%),B23/C23 - 1,"") 

  ПРИМЕЧАНИЕ: Не забудьте закрыть скобку и нажать ENTER.

  Шаг 6. Перетащите формулу вниз на весь диапазон.

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

  TEST CASE №6

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

  Отметим положительное отклонение стрелкой вверх, отрицательное отклонение стрелкой вниз.

  Шаг 1. Продолжим предыдущий пример.

  Допустим, мы хотим пометить все положительные отклонения символом в виде стрелки вверх, а все отрицательные отклонения — символом в виде стрелки вниз.

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

  Перейдем к ячейкам выше тестового диапазона (в данном примере ячейки A20 и A21).

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

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

  Для этого нам нужно перейти на вкладку ВСТАВКА на ленте Excel и пройти по пути: ВСТАВКА > Символ > Arial > Геометрические фигуры

  Нажмите  Вставьте  после того, как вы выбрали фигуру.