Файл: Е.Е. Дадонова Анализ функций проектирование простых таблиц средствами Excel.pdf

10

Примеры:

а) =ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ.

б) Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то: =ИЛИ(A1:A3) равняется ИСТИНА.

3)НЕ(логическое_значение) – меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.

Пример:

=НЕ(1+1=2) равняется ЛОЖЬ.

4) ИСТИНА( ) – возвращает логическое значение ИСТИНА.

Можно непосредственно ввести значение ИСТИНА в ячейки и формулы без использования этой функции. Функция ИСТИНА предназначена для совместимости с другими системами электронных таблиц.

5) ЛОЖЬ( ) – возвращает логическое значение ЛОЖЬ.

Можно непосредственно ввести слово ЛОЖЬ в ячейку рабочего листа или использовать его в формуле, и Microsoft Excel будет интерпретировать это слово как логическое значение ЛОЖЬ.

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

ложь) – возвращает значение_если_истина, если заданное условие при вычислении дает значение ИСТИНА, и значение_если_ложь в противном случае.

Лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Примеры:

а) Предположим, что необходимо записать формулу для вычисления функции F(x)=1-3/х. Значение аргумента содержится в ячейке В2. Функция не определена при х=0. Таким образом, формула примет вид

11

=ЕСЛИ(В2=0;1-3/В2;”Неопред.”)

б) Однако, существует огромное количество функций, для которых область определения не ограничивается одним лишь значением. Например, об-

ласть определения функции F( x ) = 1 исключает точки х=3 ( 3 − x ) ( 4 + x )

и х=-4. Предположив, что значение аргумента содержится в ячейке В2, в ячейку С2 можно записать формулу для определения функции в следующем виде:

=ЕСЛИ(ИЛИ(B2=3;B2=-4);”Неопред.”;1/((3-B2)*(4+B2)))

Таким образом учитывается область определения данной функции, а ввод в ячейку В2 критических точек (3 или –4) сопровождается выводом на экран (ячейка С2) текста сообщения о том, что функция ”Неопред.”.

в) Однако, формулу для вычисления той же функции можно записать и иным образом:

=ЕСЛИ(И(B2<>3;B2<>-4);1/((3-B2)*(4+B2));”Неопред.”)

г) А можно и так:

=ЕСЛИ(B2=3;”Неопред.”;ЕСЛИ(B2=-4;”Неопред.”;1/((3-B2)*(4+B2))))

д) Учитывая то, что вероятность попадания непосредственно в точки x=3 или x=-4, если они находятся внутри или на конце интервала табулирования, достаточно мала, ту же формулу можно записать так:

=ЕСЛИ(ABS(B2-3)>0,001;ЕСЛИ(ABS(B2+4)>0,001;1/((3-B2)*(4+B2)); ”Неопред.”); ”Неопред.”),

и этим не исчерпываются все возможные варианты.

2. ТАБУЛИРОВАНИЕ И ПОИСК ЭКСТРЕМУМОВ ФУНКЦИИ ОДНОЙ ПЕРЕМЕННОЙ

Табулирование функции – типичная задача, на примере решения которой обучаются азам программирования. Поиск наибольшего и наименьшего значений (экстремумов) функции в некотором интервале ее области опреде-

12

ления – одна из простейших задач теории оптимального управления. Но как раз такого рода и подобные этим задачи вычислительного характера очень просто и наглядно решаются средствами Excel.

2.1. Постановка задачи

Пусть задана некоторая произвольная элементарная функция в виде аналитического выражения y=F(x). Необходимо построить таблицу значений этой функции (протабулировать функцию) для значений аргумента, изменяющихся на отрезке [a,b] с шагом h=(b-a)/10, т.е. разбив отрезок на 10 частей, найти ее наибольшее и наименьшее значения на этом отрезке и построить график. Решим эту задачу на примере функции

y= sin ln(1 + x) esin( πx ).

x

2.2. Анализ области определения функции

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

и неопределённости (типа 00 или ∞∞), которые надо обязательно попытаться

случае раскрытия неопределённости значение функции в этой точке можно заменить найденным предельным значением, если этот предел существует и конечен.

13

Для заданной функции область определения очевидна – x>–1. Однако,

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

2.3. Ввод текстовых и числовых констант

Для оформления конструируемой таблицы и хранения исходных данных (границ отрезка [a,b]) выполним следующие действия:

1)в ячейку A1 введём текст «Табулирование функции»;

2)в A2 – текст «a=»;

3)в B2 – число –1,5 (левая граница отрезка);

4)в C2 – текст «b=»;

5)в D2 – число 1,5 (правая граница отрезка);

6)в E2 – текст «h=».

Выполнив команду меню Вставка/Объект и выбрав Microsoft Equation 3.0 (или 2.0), с помощью редактора (Мастера) формул создадим объект –

Для оформления таблицы значений функции введём заголовки колонок:

1)в ячейку A3 введём текст «Номер»;

2)в ячейку B3 – текст «X»;

3)в ячейку C3 – текст «Y».

В результате выполненных действий рабочий лист Excel примет вид, приведённый на рис. 1. Подробная информация о различных способах ввода

15

учесть её область определения. В нашей ситуации это довольно просто (см. п. 2.2). Должно быть x>–1, а, кроме того, y(0)=sin 1. Воспользовавшись логическими функциями рабочего листа, в ячейку C4 введём формулу, в которой учтены все возможные ситуации:

=ЕСЛИ(B4>-1;ЕСЛИ(ABS(B4)<0,001;SIN(1); SIN(LN(1+B4)/B4)*EXP(SIN(ПИ()*B4)));"Неопред.")

Протащив C4 за маркер заполнения до ячейки C14 включительно, получим в качестве содержимого ячеек интервала C4:C14 необходимые нам формулы, а рабочий лист Excel примет вид, приведённый на рис. 2.

2.5. Поиск наибольшего и наименьшего значений функции

Найти наибольшее и наименьшее значения функции среди вычисленных достаточно просто. Для этого можно в ячейку D4 ввести формулу:

=ЕСЛИ(C4=МАКС($C$4:$C$14);"<=макс.";

ЕСЛИ(C4=МИН($C$4:$C$14);"<=мин.";""))

После чего скопировать ее перетаскиванием в интервал D5:D14. Тогда рабочий лист Excel примет вид, приведенный на рис. 3.

Однако, для решения подобных и гораздо более сложных оптимизационных задач в инструментарии Excel имеется мощное средство – Ре-

шатель (Solver), доступ к которому реализован через пункт меню Сер-

вис/Поиск решения. По-

сле открытия диалога

Поиск решения (рис. 4)

16

необходимо выполнить следующие действия:

1)в поле Установить целевую ячейку ввести адрес ячейки, содержащей формулу для вычисления значений оптимизируемой функции, в нашем примере целевая ячейка – это D15, а формула в ней имеет

вид:

=ЕСЛИ(ABS(F15)<0,001;SIN(1);SIN(LN(1+F15)/F15)*EXP(SIN(ПИ()*F15)));

2)для максимизации значения целевой ячейки, установить переключатель максимальному значению в положение , для минимизации используется переключатель минимальному значению;

3)в поле Изменяя ячейки ввести адреса изменяемых ячеек, т.е. аргументов целевой функции (F15), разделяя их знаком «;» (или щелкая мышью при нажатой клавиши Сtrl на соответствующих ячейках), для автоматического поиска всех влияющих на решение ячеек используется кнопка Предположить;

4)в поле Ограничения с помощью кнопки Добавить ввести все ограничения, которым должен отвечать результат поиска: в нашем примере изменяемое значение не должно быть меньше, чем начало интервала ($B$2), а также не должно быть больше конца заданного интервала ($D$2), т.е. $F$15>=$B$2 и $F$15<=$D$2;

5)для запуска процесса поиска решения нажать кнопку Выполнить. Для сохранения получен-