Общие сведения о функции ЕСЛИ()

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

ЕСЛИ(Логическое_выражение;
Значение_если_истина;Значение_если_ложь)

Здесь:

· Логическое_выражение – это условие, которое при одних значениях введенных данных выполняется, при других – нет;

· Значение_если_истина – алгоритм, по которому определяется значение функции, когда условие оказывается правильным;

· Значение_если_ложь – алгоритм, по которому определяется значение функции, когда условие оказывается неправильным.

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

Рассмотрим действие этой функции на конкретных примерах.

6.2. Выбор из двух вариантов по одному условию

Пример

Поставщик ввел оптовую скидку на цену для больших партий товара. Надо составить шаблон для расчета стоимости любой партии товара.

Составим таблицу из констант, необходимых для расчета. В ячейки А1:А4 введем названия констант: "ОбъемПартии", "ОптБарьер", "РознЦена", "ОптЦена". Присвоим ячейкам В1:В4 такие же имена (удобно пользоваться командой Вставка ® Имя ® Создать…). В ячейку С1 введем текст "СтоимПартииТовара".

Сделаем активной ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ(). В окне аргументов в текстовые поля введем следующие значения:

· В поле "Логическое_выражение:" вводится условие, по которому Excel будет выбирать нужный вариант действий. Его можно составить так:

ОбъемПартии<=ОптБарьер

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

ОбъемПартии* РознЦена

· В поле "Значение_если_ложь:" указывается, как рассчитывать функцию, если условие не выполняется. Для нашего примера следует ввести

ОбъемПартии*ОптЦена

Расчетный шаблон готов. Чтобы проверить его, введите удобные для устных расчетов числа в ячейки В1:В4 и проверьте, правильно ли функция ЕСЛИ() выбрала формулу для заполнения ячейки С2. Введите в В1 другой объем партии, при котором требуется использовать другую цену при расчете стоимости покупки. Если в обоих случаях получены верные результаты, можно красиво отформатировать ячейки А1:С4 (см. подразд. 1.13–1.16) и пользоваться этим шаблоном, меняя только значения констант в В1:В4.

Пример

В таблице значений функции y = 2cos(x + 2)e^0,5x надо отметить символом "*" строку с минимальным значением.

Введем в ячейки А1 и В1 подписи "X" и "Y", в блок А2:А11 – значения аргументов, в блок В2:В11 – формулу расчета функции. Столбец С зарезервируем для заказанной в условии метки. В ячейку D1 введем текст "минимум", в ячейке D2 с помощью функции МИН() найдем это значение в блоке В2:В11.

Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ(). Условие, по которому Excel выбирает нужный вариант действий, составим так: В2=$D$2. В строку второго аргумента вводим символ "*" (без кавычек), в третий – пробел и нажмем после этого <ОК>. С помощью протяжки скопируем полученную формулу на блок С2:С11.

Задание

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

Рассмотрим функцию ЕСЛИ() в той строке, в которой появилась "*". Значение функции y в этой строке минимально. Левая и правая части условия оказались одинаковыми, т. е. первый аргумент – правильный. Поэтому для заполнения своей ячейки функция ЕСЛИ выбрала то, что указано во втором аргументе. Для значений функции у в других строках условие, введенное в функцию ЕСЛИ(), оказывается неверным, поэтому она заполняет свои ячейки по варианту третьего аргумента. В нашем случае это пробел, который невидим на экране, поэтому ячейки кажутся пустыми.

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

Задание

В ячейку А1 введите формулу:

=ЕСЛИ(С3=37;"СЕНО";"СОЛОМА")

Определите влияющую ячейку (команда Сервис ® Зависимости ® Влияющие ячейки) и введите в нее такое число, при котором СОЛОМА превратится в СЕНО.

6.3. Проверка двух условий при выборе одного
из двух вариантов

Пример

Объем заказа k, который принимает фирма, должен лежать в диапазоне от a1 до a2 штук изделий. Цена одного изделия составляет d рублей. Составьте шаблон для расчета стоимости заказа. Требуется, чтобы на экране появлялась стоимость заказа, если его объем лежит в нужном диапазоне, и предупреждение, если объем не попал в допустимые границы.

Введем в ячейку А1 общее название шаблона: Расчет стоимости заказа, в А3:А6 – названия переменных, а в В3:В6 – их значения. Названия переменных, использованные при формулировке примера, неудобны для Excel-расчета: а1, а2 похожи на стандартные адреса ячеек, поэтому можно воспользоваться длинными текстовыми именами, например, в А3 – ОбъемЗаказа, в А4 – НижнГран, в А5 – ВерхГран, в А6 – Цена. В А2 введем текст СтоимостьЗаказа, в В2 – функцию ЕСЛИ() со следующими аргументами:

Логическое_выражение: И(B3>=B4;B3<=B5)

Значение_если_истина: B3*B6

Значение_если_ложь: ТАКОЙ ЗАКАЗ НЕ ПРИНИМАЕМ

Если ячейкам В3:В6 предварительно присвоить имена, указанные в А3:А6 (см. подразд. 1.7), то смысл аргументов станет более понятным.

Задание

1. Посмотрите, как действует шаблон при разных значениях, введенных в В3.

2. Отформатируйте расчетный шаблон (см. подразд. 1.13–1.16):

· измените ширину столбца А так, чтобы все длинные тексты были видны на экране;

· выровняйте по левому краю значения переменных в столбце В;

· выделите заливкой или цветом шрифта ячейки А2:В2, А3:В3;

· отцентрируйте заголовок по ячейкам А1:В2.

3. Измените условие в первом аргументе:

ИЛИ(В3<=B4;B3>=B5)

В какой последовательности надо теперь перечислять способы заполнения ячейки В2?

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

· должны выполняться все одновременно – И;

· должно выполняться хотя бы одно из них – ИЛИ.

Примечание

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

6.4. Выбор одного из многих вариантов

Пример

В таблице значений функции y = 2cos(x + 2)e^0,5x надо отметить словами "минимум" и "максимум" строки с самым маленьким и самым большим значениями функции. Остальные строки не помечать ничем.

Введем в ячейки А1 и В1 подписи "X" и "Y", в блок А2:А11 – значения аргументов, в блок В2:В11 – формулу расчета функции. Столбец С зарезервируем для заказанных в условии меток. В ячейки D1 и Е1 введем тексты "минимум" и "максимум" соответственно, в ячейках D2 и Е2 с помощью функций МИН() и МАКС() вычислим эти значения по блоку В2:В11.

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

=ЕСЛИ(В2=$D$2;"минимум";ЕСЛИ(В2=$Е$2;"максимум";""))

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

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

Пример

Составить шаблон для расчета подоходного налога по прогрессивной схеме.

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

Налог=

Здесь d – доход; n1, n2 – границы налоговых ставок; р1, р2, р3 – процентные ставки налогов (очередной процент действует только на ту часть дохода, которая попала в его диапазон).

Введем в ячейку А1 общий заголовок шаблона: "Расчет налога". В А2:А7 введем соответственно названия "Доход", "Граница1", "Граница2", "Проц1", "Проц2", "Проц3". В В2:В7 введем значения этих величин, В С2 – текст: "Налог", в D2 – функцию ЕСЛИ(), первый и второй аргументы которой такие:

Логическое_выражение: B2<=B3

Значение_если_истина: B5*B2

В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует еще две возможности: d n2 и d > n2, в этот аргумент вставляют дополнительную функцию ЕСЛИ(), которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в подразд. 1.6, 1.9. Окончательно третий аргумент основной функции ("Значение_если_ложь") выглядит так:

ЕСЛИ(B2<=B4;B5*B3+B6*(B2-B3);B5*B3+B6*(B4-B3)+B7*(B2-B4))

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

Решение уравнения

Помимо способа, изложенного в подразд. 2.1, для решения этой задачи можно воспользоваться командой Сервис ® Подбор параметра… Перед обращением к этой команде следует ввести в Рабочий лист алгоритм расчета функции (он может быть представлен одной или несколькими формулами) и ввести в ячейку ее аргумента ориентировочное значение, с которого следует начать поиск корня.

Команда Сервис ® Подбор параметра… вызывает на экран окно Подбор параметра, в котором следует указать:

· адрес ячейки, в которой находится конечное значение функции;

· то число, к которому ее надо приравнять;

· ячейку аргумента.

В процессе выполнения команды начальное значение аргумента заменится на такое, при котором функция будет равна нужному значению (не обязательно нулю). Точность подбора аргумента и максимально допустимое количество итераций при решении задачи задаются в диалоговом окне команды Сервис ® Параметры… на вкладке Вычисления.

Задание

Решите с точностью до 0,001 уравнение e^–0,5x – 2x + 4 = 3.