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

Функция ЕСЛИ() позволяет предусмотреть разные способы заполнения одной и той же ячейки. То, каким из них следует воспользоваться в данный момент, 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,5 x} надо отметить символом "*" строку с минимальным значением.

Введем в ячейки А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, который принимает фирма, должен лежать в диапазоне от a 1 до a 2 штук изделий. Цена одного изделия составляет 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,5 x} надо отметить словами "минимум" и "максимум" строки с самым маленьким и самым большим значениями функции. Остальные строки не помечать ничем.

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

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

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

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

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

Пример

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

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

Налог=

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

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

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

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

В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует еще две возможности: d n 2 и d > n 2, в этот аргумент вставляют дополнительную функцию ЕСЛИ(), которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в подразд. 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,5 x} – 2 x + 4 = 3.

Решение систем уравнений

Для решения систем линейных и нелинейных уравнений используют разные средства Excel.

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

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

АХ = В; Х = А^{- 1} В.

Здесь А – матрица коэффициентов при неизвестных; В – столбец свободных членов системы; Х – неизвестные решения; А^{– 1} – обратная матрица коэффициентов системы.

В библиотеке Мастера функций Excel в категории Математические есть функции МУМНОЖ() и МОБР(), которые выполняют соответственно умножение и обращение матриц, необходимые для решения данной задачи. Так как результатом работы этих функций являются массивы чисел, их следует вводить как функции массива (см. подразд. 1.6, 1.9).

Пример

Рассмотрим систему четырех линейных уравнений с четырьмя неизвестными. Введем на Рабочий лист информацию, необходимую для ее решения, в соответствии с планом, представленным в табл. 6.6.1. Для удобства работы перед вводом коэффициентов системы и расчетных формул можно провести форматирование данных (см. подразд. 1.13):

· объединить ячейки, в которых размещены заголовки;

· разместить эти заголовки по центру объединенных ячеек;

· изменить направление текста в заголовке А4:А7 на вертикальное;

· разрешить перенос по словам в заголовках А4:А7, G2:G3, H2:H3, I2:I3;

· разделить тонкими линиями столбцы полученной таблицы;

· обвести жирной рамкой всю таблицу в целом и блоки заголовков (A2:B7 и A2:I3).

Таблица 6.6.1

Перед вводом формулы массива следует выделить ячейки, в которых надо разместить результаты. При решении системы это блок Н4:Н7, при проверке правильности найденного решения – I4:I7. Затем формула набирается обычным способом с помощью Мастера функций, но ввод заканчивается нажатием клавиши <Enter> или кнопки <ОК> при дополнительно утопленных клавишах <Ctrl+Shift>. При правильном вводе отображение формулы массива в Информационном поле автоматически заключается в фигурные скобки.

Решение задач оптимизации

Команда Сервис ® Поиск решения… предоставляет пользователю следующие возможности:

· поиск безусловных экстремумов функции одного или нескольких аргументов;

· поиск экстремумов функции одного или нескольких аргументов при наличии ограничений на найденное решение;

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

· выбор метода решения поставленной задачи;

· ввод ограничений на точность и время выполнения задачи.

Эти возможности реализуются с помощью параметров, собранных в основном окне Поиск решения и дополнительном – Параметры поиска решения. Дополнительное окно вызывается кнопкой <Параметры> из основного. Кнопка <Справка> вызывает окно с разъяснением смысла каждого параметра и возможностей, которые предоставляются при его заказе.

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

f ₁(x ₁, x ₂, …, x_n) = 0; f ₂(x ₁, x ₂, …, x_n) = 0; …. f_n (x ₁, x ₂, …, x_n) = 0

составляют вспомогательную целевую функцию

S = f ₁² + f ₂² + … f_n ².

S – неотрицательная функция. Ее минимальное значение равно нулю и достигается только тогда, когда все слагаемые одновременно равны нулю. А это и есть решение исходной задачи.

Рассмотрим в качестве примера систему двух нелинейных уравнений

x ² + y ² = 3; 2 x + 3 y = 1.

Введем исходные данные по плану, представленному в табл. 6.7.1. Для удобства дальнейшей работы можно провести форматирование введенной информации.

Таблица 6.7.1

Вызовем команду Сервис ® Поиск решения… В окне Поиск решения установим следующие параметры:

· "Установить целевую ячейку:" А9;

· "Равной:" минимальному значению;

· "Изменяя ячейки:" А4:В4;

· нажмем кнопку <Параметры> и в дополнительном окне Параметры поиска решения проверим, что флажок "Линейная модель" не установлен. Закроем дополнительное окно кнопкой <ОК>;

· запустим команду кнопкой <Выполнить> основного окна.

Когда команда закончит работу, на экране автоматически появляется окно Результаты поиска решения. Пояснения к параметрам, представленным в нем, вызываются кнопкой <Справка>. Закажем, к примеру, параметры "Сохранить найденное решение" и "Тип отчета: результаты". В этом случае начальные значения переменных в ячейках А4:В4 заменятся на найденные и в таблицу будет вставлен новый лист "Отчет по результатам 1". Просмотрите отчет. Проверьте, какое значение приняла вспомогательная целевая функция в А9 при найденных решениях. Если она существенно отличается от нуля, то решение найдено неверно.

Успешность поиска решения во многом зависит от выбора начального приближения переменных. В случае двух уравнений с двумя переменными можно не делать аналитического исследования функций системы, а составить таблицу вспомогательной целевой функции S (см. подразд. 3.2) и выбрать в качестве начального приближения те комбинации аргументов, при которых S принимает наименьшие значения.

Задание

Составьте таблицу значений целевой функции

S = (x ² + y ² – 3)² + (2 x + 3 y – 1)2

в диапазоне аргументов – 3 < x < 3, – 3 < y < 3. Выберите 4–5 точек с наименьшими значениями функции, проведите поиск минимума, используя каждую из них в качестве начального приближения. В результате должно быть получено только два разных решения: х ₁ = –1,268; у ₁ = 1,179 и х ₂ = 1, 576; у ₂ = –0,717. Графически уравнения системы представляются окружностью и прямой линией. Система такого типа не может иметь больше двух точек пересечения.

Задачи технического и экономического планирования часто решаются методами линейного программирования. Особенность этих задач – большое количество исходных данных. Чтобы упорядочить их, полезно перед вводом данных в ЭВМ составить математическую модель задачи на бумаге. При этом можно руководствоваться следующим планом:

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

2. Определяемся с целью оптимизации, т. е. решаем, минимальное или максимальное значение критерия будет соответствовать наилучшему плану.

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

4. Составляем формулы ограничений, которые показывают, какие планы имеет смысл рассматривать при выборе оптимального.

Только после составления математической модели имеет смысл вводить информацию в ЭВМ.

Пример

Процесс изготовления двух видов изделий состоит в последовательной обработке каждого из них на трех станках. Возможности использования каждого из них ограничены 10-ю часами в сутки. Удельное время обработки и удельная прибыль для каждого изделия приведены в табл.6.7.2. Найти оптимальный план выпуска каждого изделия.

Таблица 6.7.2

Составляем модель по предложенному выше плану:

1. Критерий оптимизации – общая прибыль (ОП), планируемые параметры – число запланированных к выпуску изделий каждого вида (k_A, k_В).

2. Цель оптимизации – максимальная прибыль.

3. Целевая функция – ОП = k_A П _А + k_B П _В, где П _А и П _В – удельные прибыли от продажи каждого вида изделий.

4. Ограничения – в этой задаче наложены ограничения только по времени использования в трудовом процессе каждого станка:

· Станок 1: t_{A 1} k_A + t_{B 1} k_B <= 600;

· Станок 2: t_{A 2} k_A + t_{B 2} k_B <= 600;

· Станок 3: t_{A 3} k_A + t_{B 3} k_B <= 600.

Здесь индексы показывают, какому станку и изделию соответствует удельная норма времени обработки. Например, t_{B 2} – норма времени обработки одного изделия В на станке 2.

На рис. 6.7.1 изображен пример расположения модели на рабочем листе Excel.

Рис. 6.7.1

В ячейки F3, F4 вводится ориентировочный план. Можно оставить их пустыми. В ячейки G3, G4 вводятся формулы прибыли по каждому виду изделий, в ячейке G5 эти прибыли суммируются. В ячейки B5:D5 вводятся левые части формул ограничений.

После того, как все элементы модели занесены на рабочий лист, можно вызывать команду Сервис ® Поиск решения… В ее диалоговом окне для нашего примера следует установить такие значения параметров:

· "Установить целевую ячейку" – G5 (ячейка, в которой находится окончательное значение целевой функции).

· "Равной" – максимальному значению.

· "Изменяя ячейки" – F3:F4 (ячейки, в которых находятся планируемые параметры. После окончания работы команды в них будут записаны оптимальные значения).

· "Ограничения" – для заполнения этого окна надо нажать кнопку <Добавить>. В появившемся окне Добавление ограничения три поля. В левом указывают адрес ячейки, в которой сосчитана та часть ограничения, которая меняет значение для разных планов. В правом – должна стоять константа, с которой сравнивается значение левого поля при разных планах. Если она уже введена на рабочий лист, здесь можно указать ее адрес. Для нашего примера в левое поле вводим В5, в правое – В6, в центральном поле устанавливаем соотношение <=. Нажимаем кнопку <Добавить>. Повторяем аналогичные действия для ввода ограничений по второму и третьему станкам. После установки последнего ограничения вместо кнопки <Добавить> нажимают <ОК>. Если какие-то ограничения оказались лишними или введены неверно, это можно исправить кнопками<Удалить> и <Изменить>.

· Нажимаем кнопку <Параметры>. Появляется диалоговое окно Параметры поиска решения. Здесь оговариваются метод и точность решения. Для задач линейного программирования достаточно установить только параметры "Линейная модель" и "Неотрицательные значения", закрыть окно кнопкой <ОК> и нажать кнопку <Выполнить>.

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

ЧТО ПРОИСХОДИТ ВО ВЗРОСЛОЙ ЖИЗНИ? Если вы все еще «неправильно» связаны с матерью, вы избегаете отделения и независимого взрослого существования...

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

Что будет с Землей, если ось ее сместится на 6666 км? Что будет с Землей? - задался я вопросом...

Система охраняемых территорий в США Изучение особо охраняемых природных территорий(ООПТ) США представляет особый интерес по многим причинам...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: