Примеры использования формулы с функцией ЕСЛИ в 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 и средней цены за август:

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

exceltable.com

Использование анализа "что если" в 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 Вам понравился.

www.excelguide.ru

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

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

За перевыполнение плана от 0 до 5 штук (включительно), работнику выплачивается премия 2%от 6 до 10 шт. — 4%от 11 до 15 шт. — 6%от 16 до 20 шт. — 8%и так далееот 95 до 100 шт. — 40%

У нас есть данные по выполнению планов работников и нам необходимо рассчитать премию.

Если бы диапазонов расчета премии было бы несколько, то мы могли бы использовать функцию ЕСЛИ, то есть указываем, если перевыполнение больше 0 и меньше или равно 5, то поставить премию 2%, если нет, то снова открываем ЕСЛИ и проверяем если перевыполнение больше 5 и меньше или равно 10, то поставить премию 4%, если нет, то снова открываем ЕСЛИ и так далее.

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

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

 

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

Пишем:

=ВПР(h4;$A$2:$C$22;3;1), где

h4 — это искомое значение, в нашем случае это выполнение плана определенным сотрудником, для Иванова — это 13 шт.

$A$2:$C$22 — это таблица с условиями по премии. Важно, чтобы первым столбцом данной таблицы были значения ОТ

3 — это третий столбец таблицы с условиями, в котором находятся % премии, который выплачивается сотруднику

1 — это интервальный просмотр, обычно всегда используется цифра 0, для поиска точного значения. Мы же указываем 1, так как нам нужно найти не точное, а ближайшее значение. Допустим, если перевыполнение плана составляет 8 шт., то при интервальном просмотре равным 1, функция будет искать ближайшее число меньше или равное 8, в нашем случае это будет 6, которому соответствует 4% премии

Далее протягиваем формулу на всех сотрудников и меняем формат премии на проценты

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

Скачать пример файла - https://yadi.sk/i/Qnum6_IXdezhV

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

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

sirexcel.ru