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

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

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

Здесь:

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

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

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

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

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

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

Пример

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

Составим таблицу из констант, необходимых для расчета. В ячейки А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?

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

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

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