Excel функция если пример: Функция ЕСЛИ в Excel – примеры использования

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

Excel предлагает много функций для выполнения анализов с условиями. Понятие условный анализ — значит выполнения различных операций в зависимости от определенных условий.

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

Логический анализ с условием формулы в аргументе функции ЕСЛИ

На рисунке изображена таблица с городами и ценами на топливо за период 6-ти месяцев. Допустим необходимо определить: цена в каждом месяце была ниже или выше от средней цены для всех городов в данном месяце. Цена выше средней должна быть обозначена текстовым значением «Высокая», а ниже – словом «Низкая». Результаты анализа с условием находятся в отдельной таблице ниже.

Функция ЕСЛИ – является базовой для выполнения условных анализов в Excel. Содержит 3 аргумента:

1. Условие.
2. Выражение формулы выполняемое в случаи если условие в первом аргументе возвращает значение ИСТИНА.
3. Второе выражение выполняется если условие вернуло – ЛОЖЬ.



Первый аргумент с условием в данном примере содержит формулу: C3>СРЗНАЧ(C$3:C$12). Условие должно иметь такую конструкцию, чтобы возвращалось логическое значение ИСТИНА или ЛОЖЬ. Поэтому в конструкции обычно применяется оператор сравнения (например, больше >, меньше <, равно =, неравно <>, больше или равно >=, и т.д.). Или же может быть использована логическая функция (например, =ЕПУСТО(), =ИЛИ(), =И(), и др.). Могут быть использованы все формулы или функции в результате вычисления которых возвращается значение ИСТИНА или ЛОЖЬ. В данном примере первый аргумент функции ЕСЛИ содержит условие в конструкции которого имеется оператор сравнения больше (>). Им сравниваются значения ячейки C3 со средним значением диапазона ячеек C$3:C$12.

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

Если выражение в условии первого аргумента возвращает значение ИСТИНА, тогда в текущую ячейку записывается слово, которое содержится во втором аргументе функции ЕСЛИ. Во втором аргументе находится текст «Высокая». Так как значение в ячейке C3 больше от среднего значения в диапазоне ячеек: C$3:C$12, в ячейку C14 введено соответственное условию слово «Высокая».

В ячейке С17 находится результат сравнения ячейки C6 и средней цены за август:

В результате цена меньше, значит условие возвращает – ЛОЖЬ и формула возвращает текст из третьего аргумента – «Низкая». И так далее.

Вложенные функции ЕСЛИ в Excel

Функция IF (ЕСЛИ) может быть вложенной в случае, когда нужно проверить несколько условий одновременно. Значение FALSE (ЛОЖЬ) может измениться на другое, если функция продолжит проверку. Для примера посмотрите на формулу ниже:

=IF(A1=1,"Bad",IF(A1=2,"Good",IF(A1=3,"Exellent","No Valid Score")))
=ЕСЛИ(A1=1;"Bad";ЕСЛИ(A1=2;"Good";ЕСЛИ(A1=3;"Exellent";"No Valid Score")))

1. Если значение в ячейке A1 равно 1, функция возвращает Bad.
2. Если значение в ячейке A1 равно 2, функция возвращает Good.
3. Если значение в ячейке A1 равно 3, функция возвращает Excellent.
4. Если ячейка A1 принимает другое значение, функция возвращает No Valid Score.

Вот еще один пример:

=IF(A1<=10,350,IF(A1<=20,700,IF(A1<=30,1400,2000)))
=ЕСЛИ(A1<=10;350;ЕСЛИ(A1<=20;700;ЕСЛИ(A1<=30;1400;2000)))

1. Если значение в ячейке A1 меньше или равно 10, функция возвращает 350.
2. Если значение в ячейке A1 больше, чем 10, но меньше или равно 20, функция возвращает 700.
3. Если значение в ячейке A1 больше, чем 20, но меньше или равно 30, функция возвращает 1400.
4. Если значение в ячейке A1 больше, чем 30, функция возвращает 2000.

Примечание: Возможно, в своей формуле вы захотите использовать “<” вместо “<=“, чтобы немного изменить границы.

Оцените качество статьи. Нам важно ваше мнение:

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

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

Перед нами две таблицы (читайте дополнительно: Работа с границами таблицы в экселе), в «Таблицу №1» должна попадать информация по одному из контрагентов из «Таблицы №2», а выбор должен попадать из выпадающего списка (читайте дополнительно: («Как в экселе сделать зависимые выпадающие списки»), который находиться в ячейки «В3».

Сначала пропишем функцию для выбора выручки, в зависимости от контрагента. В ячейки «В4» пишем формулу:

=ЕСЛИ($B$3=»Альтаир»;E4;ЕСЛИ($B$3=»Альфа Баран»;F4;ЕСЛИ($B$3=»Медведица»;G4;0)))

Читайте дополнительно по теме:

Как в экселе исправить ошибку #ЗНАЧ!

Как в экселе убрать ошибку ДЕЛ/0

Структура формулы будет следующей:

1. Первая функция ЕСЛИ идет следующим путем: если в ячейки «В3» находится значение «Альтаир», то в ячейку «В4» попадает значение из ячейки «E4», если нет, то уходит на вторую функцию «ЕСЛИ»;
2. Вторая функция проверяет на значение «Альфа Баран», если оно имеется в ячейки «В3», то в ячейку «В4» попадает значение из ячейки «F4», если нет, то уходит на третью функцию «ЕСЛИ»;
3. Третья функция ЕСЛИ проверяет на значение «Медведица», если там есть это название, то в ячейку «В4» попадает значение из ячейки «G4», если не одно из значений в ячейки «В4» не совпадет, то ставиться ноль.

Ячейку «В3» закрепляем значками долларов, чтобы можно скопировать формулу на оставшиеся показатели: «Себестоимость» и «Валовая прибыль». Копируем эту формулу.

Теперь в ячейки «В3» мы можем выбирать контрагента и в таблице №1 будут появляться показатели этого контрагента.

Смотрите дополнительно:

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

Видео

Функция СЧЕТЕСЛИ в Excel и примеры ее использования

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

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

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
• Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.



Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;»»&»стулья»). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

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

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

1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

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

1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ) — Служба поддержки Office

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

Например:

СЧЁТЕСЛИ(диапазон;критерий)

Примеры

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

Распространенные неполадки

Рекомендации

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Для подсчета непустых ячеек используйте функцию СЧЁТЗ

Для подсчета ячеек на основании нескольких критериев используйте функцию СЧЁТЕСЛИМН

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

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

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

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Для удаления начальных и конечных пробелов в ячейках используйте функцию СЖПРОБЕЛЫ

Для удаления непечатаемых символов в ячейках используйте функцию ПЕЧСИМВ

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

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

Обнаружение ошибок в формулах

Статистические функции

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

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

Примечание: 
Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Простые примеры if | И / или критерии | Вложенный If | Подробнее о If

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

Простые примеры if

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

1а.Например, взгляните на функцию ЕСЛИ в ячейке B2 ниже.

Объяснение: если цена больше 500, функция ЕСЛИ возвращает High, иначе возвращает Low.

1б. Следующая функция ЕСЛИ дает точно такой же результат.

Примечание. Вы можете использовать следующие операторы сравнения: = (равно),> (больше), = (больше или равно), (не равно).

2. Всегда заключайте текст в двойные кавычки.

3а. Приведенная ниже формула вычисляет прогресс между двумя точками времени.

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

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

И / или Критерий

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

1. Например, посмотрите на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: функция И возвращает ИСТИНА, если первая оценка больше или равна 60, а вторая оценка больше или равна 90, иначе она возвращает ЛОЖЬ.Если TRUE, функция IF возвращает Pass, если FALSE, функция IF возвращает Fail.

2. Например, посмотрите на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: функция ИЛИ возвращает ИСТИНА, если хотя бы одна оценка больше или равна 60, иначе она возвращает ЛОЖЬ. Если TRUE, функция IF возвращает Pass, если FALSE, функция IF возвращает Fail.

3. Например, посмотрите на функцию ЕСЛИ в ячейке D2 ниже.

Объяснение: указанная выше функция AND имеет два аргумента, разделенных запятой (таблица, зеленый или синий). Функция И возвращает значение ИСТИНА, если Продукт равен «Таблица», а Цвет равен «Зеленому» или «Синему». Если ИСТИНА, функция ЕСЛИ снижает цену на 50%, если ЛОЖЬ, функция ЕСЛИ снижает цену на 10%.

Вложенный, если

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

1. Например, посмотрите на вложенную формулу ЕСЛИ в ячейке C2 ниже.

Объяснение: если оценка равна 1, вложенная формула IF возвращает Bad, если оценка равна 2, вложенная формула IF возвращает Good, если оценка равна 3, вложенная формула IF возвращает отлично, в противном случае возвращает Not Valid. Если у вас Excel 2016 или новее, просто используйте функцию IFS.

2. Например, посмотрите на вложенную формулу ЕСЛИ в ячейке C2 ниже.

Объяснение: если оценка меньше 60, вложенная формула IF возвращает F, если оценка больше или равна 60 и меньше 70, формула возвращает D, если оценка больше или равна 70 и меньше чем 80, формула возвращает C, если оценка больше или равна 80 и меньше 90, формула возвращает B, иначе она возвращает A.

Подробнее об If

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

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

Объяснение: функция И возвращает ИСТИНА, если человек старше 12 и моложе 20 лет, иначе она возвращает ЛОЖЬ. Если ИСТИНА, функция ЕСЛИ возвращает Да, если ЛОЖЬ, функция ЕСЛИ возвращает №

.

2. Вы можете комбинировать ЕСЛИ с СРЕДНИМ, СУММ и другими функциями Excel. Небо это предел!

Объяснение: функция И возвращает ИСТИНА, если входное значение больше 100 и среднее значение в именованном диапазоне Data1 больше 100, иначе возвращается ЛОЖЬ.Если ИСТИНА, функция ЕСЛИ возвращает сумму Данных2, если ЛОЖЬ, функция ЕСЛИ возвращает 0.

Запутались? Вы всегда можете использовать инструмент «Вычислить формулу» для пошагового выполнения формулы ЕСЛИ. Этот инструмент Excel поможет вам понять формулу.

3. Например, выберите ячейку G3 выше.

4. На вкладке Формулы в группе Аудит формул щелкните Вычислить формулу.

5. Нажмите кнопку «Оценить» несколько раз.

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

.

Excel Функция IF и пример IF

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

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

Введение

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

1. ЕСЛИ ячейка «Количество» пуста, оставьте ячейку «Общая сумма» пустой.
2. ЕСЛИ ячейка количества пуста или если ячейка цены пуста, оставьте
   общая ячейка пуста. (Вложенная формула ЕСЛИ)
3. ЕСЛИ установлен флажок «Местный», добавьте налог с продаж.

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

1. Преобразование числовых оценок учащихся в буквенные — используйте ВПР
2. Выберите процентную ставку на основе даты транзакции — используйте HLOOKUP
3. Показывать конкретное значение вместо результата ошибки — используйте ЕСЛИОШИБКА
   (Excel 2007 и более поздние версии)

Как работает функция ЕСЛИ

Функция ЕСЛИ состоит из 3 частей (аргументов):

1. ПРОВЕРЬТЕ что-нибудь, например значение в
   клетка.
2. Укажите, что должно произойти, если результат теста ИСТИНА.
3. Укажите, что должно произойти, если результат теста ЛОЖНЫЙ.

Пример 1: Проверка на пустую ячейку

В этом примере Итого в ячейке E7 должна отображать сумму, только если
Количество было введено в ячейку D7.

Это 3 части (аргументы) функции ЕСЛИ для этого примера:

1. ТЕСТ ячейка D7, чтобы убедиться, что она пуста ( D7 = «» ).
2. Если D7 пуст (ИСТИНА), ячейка с общей формулой ничего не покажет
   ( «» ).
3. Если D7 не пуст (FALSE), ячейка с формулой Total будет умножаться
   Цена x количество ( C7 * D7 )

Заполненная формула: = ЕСЛИ (D7 = «», «», C7 * D7)

На снимке экрана ниже ячейка D7 пуста (ИСТИНА), поэтому формула ЕСЛИ
в ячейке E7 тоже выглядит пустой.

Если ячейка D7 не пуста (ЛОЖЬ), в ячейке Итого отображается значение
Цена x количество.

Пример 2: Вложенные функции ЕСЛИ

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

1. ЕСЛИ ячейка «Цена» пуста («»), итоговая сумма будет пустой («»)
2. Тогда, ЕСЛИ ячейка «Количество» пуста («»), сумма будет пустой.
   («»)
3. В противном случае умножьте Цена на Кол-во, чтобы вычислить Итого

Примечания к формулам вложенных IF

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

Формула в именованной таблице Excel

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

Например, чтобы вычислить простую сумму (Цена x Количество), эта формула
будет использовано: = [Цена] * [Кол-во]

Проверить наличие пустых ячеек

В примере 1 в формуле ЕСЛИ проверялась только ячейка «Количество». Вот
это формула из Примера 1, написанная со структурированными ссылками:

= IF ([Qty] = «», «», [Цена] * [Qty])

Для проверки ячеек Цена и Количество, будет добавлен еще один IF
к существующей формуле:

= IF ([Price] = «», «», IF ([Qty] = «», «», [Price] * [Qty]) )

1. Если ячейка цены пуста (ИСТИНА), ячейка с формулой итога
   ничего не покажет ( «» )
2. Если ячейка Qty пуста (TRUE), ячейка с формулой Total будет
   ничего не показать ( «» )
3. Если ни одна из ячеек не пуста, вычисляется сумма ( [Цена] * [Кол-во] )

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

Порядок вложенных функций IF

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

Сначала тяжелые испытания

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

= ЕСЛИ (B2> = 20000, «Хорошо», ЕСЛИ (B2> = 10000, «Среднее», «Плохо»))

Если сумма в ячейке B2 равна 25000, результатом будет « Хорошо »,
потому что он больше 20000.

Сначала простой тест

Однако, если тесты поменять местами, формула будет проверять> = 10000
первый .:

= ЕСЛИ (B2> = 10000, «Среднее», ЕСЛИ (B2> = 20000, «Хорошо», «Плохо»))

Теперь, если сумма в ячейке B2 равна 25000, результат формулы будет
быть «Среднее» , потому что результаты этого первого теста
ИСТИННЫ.

Пример 3: Функция IF с флажком

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

• Для местных заказов установите флажок, чтобы установить флажок, и
  связанная ячейка показывает ИСТИНА
• Если флажок снят, в связанной ячейке отображается ЛОЖЬ

Формула ЕСЛИ в ячейке «Налог» проверяет связанную ячейку на предмет наличия
«ЛОЖНЫЙ». В таком случае налог равен нулю. В противном случае промежуточный итог умножается.
по ставке налога.

= ЕСЛИ (G11 = ЛОЖЬ, 0, E10 * G12)

Загрузить образец файла

Чтобы следовать примерам в этом руководстве, загрузите образец рабочей книги IF

.

Оператор ЕСЛИ в Excel с несколькими условиями И / ИЛИ, вложенными формулами ЕСЛИ и т. Д.

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

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

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

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

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

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

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

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

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

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

IF / И формула:

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

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

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

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

.

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

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

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

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

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

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

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

В столбце E будет оценка «Пройдено», если первая оценка равна или больше 20 ИЛИ вторая оценка равна или больше 30.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для начала вы можете добавить дополнительный столбец (E) со следующей формулой, которая суммирует числа в столбцах C и D:

= C2 + D2

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

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

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

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

Приведенная выше формула добавляет еще одно условие — общая оценка от 70 баллов квалифицируется как «Отлично».

Дополнительные сведения о Excel IF с несколькими условиями см. В разделе Как использовать вложенный IF в Excel.

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

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

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

= СУММ (ЕСЛИ (B1: B5 <= 1,1,2))

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

Примечание. Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно.

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

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

Пример 1. Использование IF с функциями SUM, AVERAGE, MIN и MAX

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

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

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

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

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

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

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

= ЕСЛИ (СРЕДНИЙ (C2: F2)> = 30, «хорошо», ЕСЛИ (СРЕДНИЙ (C2: F2)> = 25, «удовлетворительно», «плохо»))

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

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

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

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

МИН: = ЕСЛИ (F2 = МИН ($ F $ 2: $ F $ 10), «Худший результат», «»)

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

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

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

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

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

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

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

Вы уже знаете способ выделить пустые и непустые ячейки с помощью функции ISBLANK. Microsoft Excel предоставляет аналогичные функции для идентификации текстовых и числовых значений - ISTEXT и ISNUMBER соответственно.

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

= ЕСЛИ (ISTEXT (B1), «Текст», IF (ISNUMBER (B1), «Number», IF (ISBLANK (B1), «Пусто», «»)))

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

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

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

Вот еще один способ совместного использования функций CONCATINATE и IF:

= СЦЕПИТЬ («Вы выполнили», ЕСЛИ (C1> 5, «фантастика!», «Хорошо»))

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

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

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

Разница в том, что IFERROR и ISERROR обрабатывают все возможные ошибки Excel, включая #VALUE !, # N / A, #NAME?, #REF !, #NUM !, # DIV / 0 !, и #NULL !. IFNA и ISNA специализируются исключительно на ошибках # N / A.

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

= ЕСЛИОШИБКА (B2 / C2, «К сожалению, произошла ошибка»)

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

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

= ЕСЛИ (C2 = 0, «Извините, произошла ошибка», B2 / C2)

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

Вас также может заинтересовать

.

Функция ЕСЛИ в Excel

На главную »Встроенные функции Excel» Логические функции Excel »Функция ЕСЛИ в Excel

Связанные функции:

ЕСЛИОШИБКА
IFNA

Описание функции

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

Синтаксис функции:

IF (logic_test, value_if_true, value_if_false)

где аргументы следующие:

Обратите внимание, что функция If будет работать, если логический_тест возвращает числовое значение. В этом случае любое ненулевое значение трактуется как ИСТИНА, а ноль - как ЛОЖЬ.

Примеры функции Excel If

Пример функции If 1

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

• Текстовая строка «отрицательная», если значение в столбце A меньше 0

или

• Текстовая строка «положительная». в противном случае (например, если значение в столбце A больше или равно 0).

Пример функции If 2

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

Пример функции If 3

В следующем примере показано вложение функции Excel If (т.е.е. используя функцию if в другой функции if). В каждом случае:

• Если значение в столбце B равно , равному 0, выполняется следующий вызов «Если» для проверки значения в столбце C. В этом вызове функции If:

  • Если значение в столбце C равно 0, функция возвращает текстовую строку «div by zero»;
  • Если значение в столбце C равно , а не , равное нулю, функция возвращает значение в столбце A, деленное на значение в столбце C.
• Если значение в столбце B - , а не , равное нулю, функция возвращает значение в столбце A, деленное на значение в столбце B.

Дополнительные сведения и примеры функции Excel If см. на веб-сайте Microsoft Office.

Вложение функции If в Excel

Функция If часто «вкладывается» в Excel.Т.е. аргумент value_if_true или value_if_false принимает форму дальнейшего вызова функции If (см. Пример 3 выше).

Excel 2003 допускает до 7 уровней вложенных функций If, но более поздние версии Excel допускают до 64 уровней вложенности. Например, следующая формула (которая имеет 8 уровней вложенности) приведет к ошибке в Excel 2003, но будет правильно работать в Excel 2007 или более поздней версии:

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

Ошибка функции ЕСЛИ

Если вы получаете сообщение об ошибке от функции ЕСЛИ Excel, скорее всего, это #VALUE! ошибка:

Общая ошибка

Вернуться к странице Логические функции Excel
Вернуться к списку всех встроенных функций Excel .