Примеры использования формулы с функцией ЕСЛИ в Excel. Примеры если excel
Примеры использования формулы с функцией ЕСЛИ в Excel
Excel предлагает много функций для выполнения анализов с условиями. Понятие условный анализ — значит выполнения различных операций в зависимости от определенных условий.
Условием выступает переменное значение и логическое: ИСТИНА или ЛОЖЬ. В зависимости от этих значений формула может выполнять два разных вычисления. Если условие возвращает значение ИСТИНА, вычисляется первое выражение формулы, а второе пропускается. Если условие вернуло значение ЛОЖЬ, тогда наоборот первое выражение пропускается, а второе выполняется формулой.
Логический анализ с условием формулы в аргументе функции ЕСЛИ
На рисунке изображена таблица с городами и ценами на топливо за период 6-ти месяцев. Допустим необходимо определить: цена в каждом месяце была ниже или выше от средней цены для всех городов в данном месяце. Цена выше средней должна быть обозначена текстовым значением «Высокая», а ниже – словом «Низкая». Результаты анализа с условием находятся в отдельной таблице ниже.
Функция ЕСЛИ – является базовой для выполнения условных анализов в Excel. Содержит 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 позволяет попробовать различные значения (сценарии) для формул.Следующий пример поможет Вам освоить Анализ "что если" быстро и легко.
Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа "что если" в Excel.
Создание различных сценариев
Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг - это различный сценарий.Вы можете использовать "Диспетчер сценариев" для создания этих сценариев.Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ "что если" позволит Вам сравнить результаты различных сценариев.
Итак, приступим.
1. На вкладке Данные выберите Анализ "что если" и выберите Диспетчер сценариев из списка. Откроется диалоговое окно Диспетчер сценариев.
2. Добавьте сценарий, нажав на кнопку Добавить.
4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз. 5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:
Примечание: чтобы увидеть результат сценария, выберите сценарий и нажмите на кнопку Вывести. Excel изменит значение ячейки C4 в соответствии со сценарием, что бы Вы смогли увидеть результат на листе.Отчет по сценариям
Для того, чтобы легко сравнить результаты этих сценариев, выполните следующие действия:1. Кликните по кнопке "Отчет" в Диспетчере сценариев.
2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.
Результат: Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ "что если" в 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
