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

Функция ЕСЛИ СОДЕРЖИТ — EXCEL СПб

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ», чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова, или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

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

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов.

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

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

Выражение должно быть универсальным, для того, чтобы обрабатывать новые добавляемые данные.

Для того, чтобы это сделать, необходимо:

1. Начинаем с ввода функции ЕСЛИ (вводим «=», набираем наименование ЕСЛИ, выбираем его из выпадающего списка, нажимаем fx в строке формул).
2. В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ(), выделяем его и нажимаем 2 раза fx.

3. Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ*, добавляя в начале и в конце символ *.

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

4. Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
5. Далее нажимаем ОК, выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

6. В Значение_если_истина вводим «Реализация», а в Значение_если_ложь – можно ввести прочерк « — »
7. Далее протягиваем формулу до конца таблицы и подключаем сводную.

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

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

• Назад

• Вперёд

Основные принципы работы с датами и временем в Excel

3889
27. 10.2012
Скачать пример

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами — и понимает их все:

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т. д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть 

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27. 10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!  😉




• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

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

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

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

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).  

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

• Как вычислять возраст (стаж) в полных годах-месяцах-днях




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




• Автоматическое добавление текущей даты в ячейку при вводе данных.




• Как вычислить дату второго воскресенья февраля 2007 года и т.п.

Функция ЕСЛИ в Excel с несколькими условиями

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

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

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

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

• Функция И — возвращает ИСТИНА, если все условия соблюдены; ЛОЖЬ в противном случае.
• Функция ИЛИ — возвращает ИСТИНА, если выполняется любое одно условие ; ЛОЖЬ в противном случае.

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

Оператор ЕСЛИ в Excel с несколькими условиями (логика И)

Общая формула ЕСЛИ в Excel с двумя или более условиями:

В переводе на человеческий язык формула гласит: Если условие 1 истинно И условие 2 истинно, вернуть значение_если_истина ; иначе вернуть значение_если_ложь .

Предположим, у вас есть таблица, в которой перечислены результаты двух тестов в столбцах B и C. Чтобы сдать выпускной экзамен, учащийся должен иметь оба балла выше 50.

Для логического теста вы используете следующий оператор AND: AND (B2>50, C2>50)

Если оба условия верны, формула вернет «Пройдено»; если какое-либо условие ложно — «Fail».

=ЕСЛИ(И(B2>50, B2>50), "Пройдено", "Не пройдено")

Легко, не правда ли? Скриншот ниже доказывает, что наша формула Excel IF/AND работает правильно:

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

Например, чтобы вывести «Хорошо», если и B2, и C2 больше 50, и «Плохо» в противном случае, формула выглядит следующим образом: «Хорошо!», «Плохо»)

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

На практике кажущийся правильным оператор IF может привести к ошибке из-за этой специфики. Например, приведенная ниже формула вернет #DIV/0! (ошибка «делить на ноль»), если ячейка A2 равна 0:

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

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

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

Подробнее информацию см. в формуле ЕСЛИ И в Excel.

Excel Функция ЕСЛИ с несколькими условиями (логика ИЛИ)

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

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

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

Итак, если в предыдущей формуле использовать ИЛИ вместо И:

=ЕСЛИ(ИЛИ(B2>50, B2>50), "зачетно", "незачетно")

Тогда любой, кто наберет более 50 баллов на любом из экзаменов, получит «зачет» в столбце D. При таком условиях наши ученики имеют больше шансов сдать выпускной экзамен (особенно не повезло Иветте, которая провалилась всего на 1 балл 🙂

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

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

=ЕСЛИ(ИЛИ(B2={"доставлено", "оплачено"}) , "Закрыто", "")

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

ЕСЛИ с несколькими операторами И и ИЛИ

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

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

• Условие 1: экзамен1>50 и экзамен2>50
• Условие 2: экзамен1>40 и экзамен2>60

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

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

ИЛИ(И(B2>50, C2>50), И(В2>40, С2>60)

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

=ЕСЛИ(ИЛИ(И(B2>50, C2>50), AND(B2>40, C2>60), "Pass" , "Fail")

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

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

• В Excel 2007 и выше у вас не более 255 аргументов, а общая длина формулы ЕСЛИ не превышает 8 192 символов.
• В Excel 2003 и более ранних версиях не более 30 аргументов, а общая длина формулы ЕСЛИ не превышает 1024 символов.

Вложенный оператор IF для проверки нескольких логических тестов

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

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

• Хорошо: 60 или более (> =60)
• Удовлетворительно: от 40 до 60 (>40 и <60)
• Плохо: 40 или меньше (<=40)

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

В нашем случае функции расположены от большего к меньшему:

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

Естественно, при необходимости вы можете вложить больше функций (до 64 в современных версиях).

Дополнительные сведения см. в разделе Использование нескольких вложенных операторов IF в Excel.

Формула массива ЕСЛИ Excel с несколькими условиями

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

Чтобы оценить условия с помощью логики И, используйте звездочку:

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

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

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

Чтобы правильно заполнить формулу массива, нажмите одновременно клавиши Ctrl + Shift + Enter. В Excel 365 и Excel 2021 это также работает как обычная формула благодаря поддержке динамических массивов.

Например, чтобы получить «Пройдено», если и B2, и C2 больше 50, используйте следующую формулу:

=ЕСЛИ((B2>50) * (C2>50), "Пройдено", "Не пройдено")

В моем Excel 365 обычная формула работает нормально (как вы можете видеть на скриншотах выше). В Эксель 2019и ниже, не забудьте сделать его формулой массива, используя сочетание клавиш Ctrl + Shift + Enter.

Для оценки нескольких условий с помощью логики ИЛИ используется следующая формула:

=ЕСЛИ((B2>50) + (C2>50), «Удачно», «Не пройдено»)

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

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

Пример 1. Если ошибка #Н/Д в ВПР

Когда функция ВПР или другая функция поиска не могут что-либо найти, она возвращает ошибку #Н/Д. Чтобы ваши таблицы выглядели лучше, вы можете вернуть нулевой, пустой или конкретный текст, если #N/A. Для этого используйте эту общую формулу:

IF(ISNA(VLOOKUP(…)), value_if_na , VLOOKUP(…))

Например:

Если #N/A возвращает 0:

Если искомое значение в E1 не найдено, формула возвращает ноль.

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ)), 0, ВПР(E1, A2:B10, 2, ЛОЖЬ))

Если #Н/Д, вернуть пустое значение:

Если искомое значение не найдено, формула ничего не возвращает (пустая строка).

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "", ВПР(E1, A2:B10, 2, ЛОЖЬ))

Если #Н/Д вернуть определенный текст:

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

=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "Не найдено", ВПР(E1, A2:B10, 2, ЛОЖЬ))

Дополнительные примеры формул см. в разделе ВПР с оператором ЕСЛИ в Excel.

Пример 2. ЕСЛИ с функциями СУММ, СРЗНАЧ, МИН и МАКС

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

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

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

Если сумма больше 130, то результат «хорошо»; если больше 110 – «удовлетворительно», если 110 и ниже – «плохо».

Аналогичным образом вы можете встроить функцию СРЗНАЧ в логическую проверку ЕСЛИ и возвращать разные метки на основе среднего балла:

=ЕСЛИ(СРЕДНИЙ(B2:C2)>65, "Хорошо", ЕСЛИ(СРЕДНИЙ(B2:C2)>55, "Удовлетворительно", "Плохо"))

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

=IF(D2=MAX($D$2:$D$10), "Лучший результат", "")

=IF(D2=MAX($D$2:$D$10), "Лучший результат", "")

Чтобы обе метки были в одном столбце, вложите вышеуказанные функции одну в другую:

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

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

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

• СЧЁТЕСЛИ — подсчет ячеек, соответствующих условию
• COUNTIFS — количество ячеек с несколькими критериями
• СУММЕСЛИ — условно суммировать ячейки
• СУММЕСЛИМН — сумма ячеек с несколькими критериями

Пример 3. ЕСЛИ с ISNUMBER, ISTEXT и ISBLANK

Для идентификации текста, чисел и пустых ячеек в Microsoft Excel предусмотрены специальные функции, такие как ISTEXT, ISNUMBER и ISBLANK. Поместив их в логические тесты трех вложенных операторов IF, вы можете идентифицировать все различные типы данных за один раз:

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

Пример 4. ЕСЛИ и СЦЕПИТЬ

Чтобы вывести результат ЕСЛИ и некоторый текст в одну ячейку, используйте функции СЦЕПИТЬ или СЦЕПИТЬ (в Excel 2016 — 365) и ЕСЛИ вместе. Например:

=СЦЕПИТЬ("Вы выступили ", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50,"ну", "плохо")))

=CONCAT("Вы выполнено", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50, "хорошо", "плохо")))

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

ЕСЛИ ОШИБКА / формула ISNA в Excel

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

Разница в том, что ЕСЛИОШИБКА и ЕОШИБКА обрабатывают все возможные ошибки Excel, включая #ЗНАЧ!, #Н/Д, #ИМЯ?, #ССЫЛКА!, #ЧИСЛО!, #ДЕЛ/0! и #NULL!. В то время как IFNA и ISNA специализируются исключительно на ошибках #N/A.

Например, чтобы заменить ошибку «делить на ноль» (#DIV/0!) собственным текстом, можно использовать следующую формулу:

=ЕСЛИ(ЕОШИБКА(A2/B2), «Н/Д ", А2/В2)

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

Практическая рабочая тетрадь для загрузки

Excel Множественные критерии ЕСЛИ — примеры (файл .xlsx)

Как использовать операторы IF-THEN в Excel

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

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

Как использовать операторы IF-THEN в Excel

Операторы IF-THEN в Excel работают, создавая парадигму для предоставления логических выводов на основе значений, которые вы вводите в формулу. Формула довольно проста и понятна, она состоит из двух основных частей: ЕСЛИ и ТО.

Базовая формула ЕСЛИ-ТО в ячейке выглядит следующим образом:

=ЕСЛИ(логический тест, значение, если оно истинно, значение, если оно ложно)

В следующих параграфах более подробно объясняется логический тест и полученные значения.

ЕСЛИ: логическое условие

Проще говоря, часть формулы ЕСЛИ устанавливает логический тест, который будет использоваться для перехода к утверждениям «значение, если истинно» или «значение, если ложно». Часть IF должна иметь тип Boolean, который является особым типом, который может принимать значения TRUE или FALSE. Часть логического теста также принимает числа, где все, кроме 0, считается ИСТИННЫМ.

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

• > (больше)
• < (меньше)
• = (равно)
• >= (больше или равно)
• <= (меньше или равно)
• <> (не равно)

Правильно составленный оператор IF предоставит одно из двух возможных логических значений, которые будут определять, какая ветвь оператора THEN будет использоваться. В противном случае формула выдаст ошибку «#ЗНАЧ!» ошибка.

Давайте посмотрим, как создать логический тест на простом примере: « =(B2>C2) ».

Для простоты предположим, что ячейки B2 и C2 содержат числовое значение. В этом случае эта логическая проверка возвращает ИСТИНА, если B2 имеет большее число, чем C2, и FALSE, если C2 больше или равно B2.

Можно сравнивать значения другого типа (например, текстовые строки и числа), но они имеют различные правила, которые могут не совпадать с тем, что считается полезным («бумага»>45 возвращает ИСТИНА).

ТОГДА: Значения, если ИСТИНА или ЛОЖЬ

Теперь к конкретной части формулы. Часть оператора THEN здесь умалчивается, а это означает, что вы никогда не пишете ее явно, но считаете, что она существует после части логического теста.

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

Значения в Excel могут относиться только к определенному типу данных: тексту, числам, датам и, наконец, логическим данным. Строковые (текстовые) значения, которые вы используете, должны быть заключены в кавычки.

Что важно при формировании рабочего оператора IF-THEN, так это то, что «значение, если оно истинно» будет использоваться только в том случае, если заданное вами условие истинно, и так далее.

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

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

Как создать полностью работающее выражение ЕСЛИ-ТО

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

• Выберите ячейку и введите формулу в строке формул или сама клетка. Строка формул располагается над рабочим листом.
• Выберите ячейку, затем щелкните значок формулы ( f _x ) рядом с панелью ввода текста вверху и выберите «ЕСЛИ» из списка формул. Нажмите «ОК», чтобы перейти к построителю формул.

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

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

Как использовать несколько операторов IF-THEN

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

Звучит как слишком много математики, не так ли? Это довольно просто, и мы собираемся показать вам, как вы можете использовать более одного оператора IF-THEN в ячейке Excel.

Как вложить функцию

Вставка другой формулы в качестве аргумента в Excel называется вложением. Одна формула ЕСЛИ-ТО может содержать до 7 различных вложенных функций (согласно построителю формул; поддержка Microsoft предлагает до 64 вложенных функций).

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

Один из самых простых примеров вложения IF-THEN — это оценивание заданий. Давайте рассмотрим пример, когда результатам теста присваиваются оценки от A до F с общими ориентирами (90, 80, 70 и 60).

Вот как на практике это будет работать для ячейки: IF(C2>=60, «D», «F»))))) ”

Давайте немного разберемся. Если числовое значение в ячейке C2 (от 0 до 100) больше или равно 90 присвоенная оценка будет A. В противном случае формула переходит ко второй части утверждения, которая является второй функцией ЕСЛИ, и так далее. Если оценка в C2 меньше 60, Excel пропустит все функции ЕСЛИ-ТО и определит результат как «F».

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

Функция ЕСЛИ-ТО сделает то, что вы ей скажете

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