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

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 или ∞∞), которые надо обязательно попытаться

f ( x )

′

раскрыть, используя правило Лопиталя

lim

=

lim

f ( x )

, или своё

ϕ( x )

′

x→x0

x→x0

ϕ ( x )

sin( x )

1

знакомство c замечательными пределами

lim

=1, lim ( 1 + x )x = e . В

x

x→0

x→0

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

13

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

ln(1 +0)			0		–	неопределенность.				Попытаемся
y( 0 ) = sin	esin( π0 ) = sin
0			0
раскрыть ее. Найдем			ln(1 + x)			sin( πx )			(1	1 x
		lim sin		x	e		= sin ln lim			+ x)	×
		x→0						x→0
× lim esin( πx) = (sin(ln e))×esin 0			= (sin 1)×1 = sin1.				Таким образом, в качестве
x→0

значений заданной функции при значениях аргумента, близких к нулю, можно использовать найденный предел, равный 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), с помощью редактора (Мастера) формул создадим объект –

	ln(1 + x)
формулу, задающую вид табулируемой функции	y = sin		esin( πx ) .
		x

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

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

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

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

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

14

и редактирования данных на рабочем листе Excel содержится в методических разработках [1,2].

2.4. Конструирование и ввод формул

Рис. 1

Для вычисления величины шага табулирования h=(b-a)/10 в ячейку F2 введём формулу =(D2-B2)/10. Использование и ввод формул, управление вычислениями, применение ссылок и способы адресации подробно изложены в методических разработках [3,4].

В качестве номеров точек в столбце A можно использовать константы 1,2,3,…, а можно и воспользоваться формулой: номер_текущей_строки-3 (столько строк уже занято), которая в Excel будет выглядеть так: =СТРОКА()-3. Её мы и введём в ячейку A4. Затем, воспользовавшись автозаполнением, протащим ячейку A4 за маркер заполнения (знак + в правом нижнем углу) до ячейки A14 включительно. В результате одна и та же формула, необходимая нам, будет содержаться во всех ячейках интервала

A4:A14.

Для вычисления текущего значения аргумента можно воспользоваться формулой (далеко не единственной) xi=a+(i-1)h, где i – номер текущей точки. Эту формулу =$B$2+(A4-1)*$F$2 мы и введём в ячейку B4, затем протащим её за маркер заполнения до ячейки B14 включительно.

В результате содержимым ячейки B5 станет формула

=$B$2+(A5-1)*$F2,

B6 – =$B$2+(A6-1)*$F$2 и

т.д.

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

Рис. 2

Рис. 4

Рис. 3

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)для запуска процесса поиска решения нажать кнопку Выполнить. Для сохранения получен-

	ного решения необходимо ис-
	пользовать переключатель Со-
	хранить найденное решение в
	открывшемся окне диалога Ре-
	зультаты поиска решения. По-
	сле чего рабочий лист примет	Рис. 5
	вид, представленный на рис.5.

17

Если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например, найти максимум и минимум

		одной функции, или
		максимальные
		значения	нескольких
		функций), то удобнее
		сохранить эти модели,
	Рис. 6	используя	кнопку
		Параметры	/

Сохранить модель окна Поиск решения.

Диапазон для сохраняемой модели (рис. 6) содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения диалога Параметры. Выбор модели для решения конкретной оптимизационной задачи осуществляется с помощью кнопки Параметры/ За-

грузить модель диалога Поиск решения.

2.6. Построение графика

Подробное описание возможностей графического представления данных в среде Excel вы найдёте в методической разработке [5]. Для построения необходимого нам графика воспользуемся услугами Мастера диаграмм

(кнопка на панели инструментов Стандартная). Следуя указаниям

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

1)выбрать тип диаграммы (точечная) и вид (№3 – со значениями, соединенными

сглаживающими линиями без

Рис. 7

18

маркеров);

2)определить источник данных диаграммы ($B$3:$C$14) – диапазон ячеек, содержащих числовую информацию и подписи, и расположение рядов данных (у нас – в столбцах);

3)задать параметры диаграммы (заголовки, оси, линии сетки, легенда, подписи данных);

4)указать место размещения диаграммы (на имеющемся листе).

В результате выполнения этих действий и после удаления заливки области построения диаграммы на рабочем листе Excel к расчётам добавится график табулируемой функции (рис. 7).

2.7. Форматирование и защита рабочего листа

Основные действия по форматированию можно произвести с помощью кнопок панели инструментов Форматирование. Придадим нашей таблице более привлекательный вид, отформатировав объекты (формулу и диаграмму) и содержимое некоторых ячеек:

1)сделав активной ячейку A1, увеличим размер символов содержащегося в ней текста, выбрав его в списке Размер шрифта (список раскрывается щелчком левой кнопки мыши на стрелке) и изменим начертание символов, щёлкнув по кнопке Курсив ;

2)уберём рамку по периметру объекта-формулы, щёлкнув правой кнопкой мыши на нём, выбрав затем во всплывающем контекстнозависимом меню пункт Формат объекта/вкладка Вид (таким же способом уберём рамку вокруг диаграммы);

3)с помощью кнопки По правому краю сдвигаем содержимое ячеек A2, C2 и E2 вправо (несмежные ячейки выделяются щелчком левой кнопки мыши при нажатой клавише Ctrl), с помощью соответст-

вующих кнопок числа в ячейках B2, D2 и F2 выровняем влево (), а содержимое интервала A3:C14 – по центру ();

19

4)командой меню Формат/Ячейки представим числа в ячейках B2, D2, F2, B4:C14 в формате с двумя (0,00) значащими цифрами в дробной части (формат и разрядность можно изменить и кнопками

);

5)используя кнопку Границы , обрамим таблицу, содержащую значения аргумента, функции и моделей оптимизации (это можно сделать и с помощью команды меню Формат/Ячейки/ вкладка Граница или с помощью контекстного меню);

6)скроем линии сетки, воспользовавшись командой меню Сервис/ Па-

раметры/ вкладка Вид и убрав флажок в окне Сетка .

Врезультате этих манипуляций, даже без злоупотребления кнопками

Цвет заливки и Цвет шрифта , вид рабочего листа изменился в лучшую сторону (рис. 8). Осталось поработать с диаграммой.

Рис 8

Для редактирования диаграмма выделяется одинарным щелчком, после чего вокруг неё появляются маркеры границы в виде черных квадратиков. Для окончания редактирования достаточно щёлкнуть левой кнопкой

20

мыши в любом месте рабочего листа вне диаграммы или нажать Esc. Изменять (форматировать) можно отдельные части диаграммы, которые называются элементами. В Excel определены следующие классы элементов диаграммы:

1)область диаграммы;

2)область построения диаграммы;

3)основание (только для объёмных диаграмм);

4)стены (только для объёмных диаграмм);

5)легенда;

6)оси Х и Y;

7)текст;

8)стрелки;

9)название осей Х и Y;

10)линии сетки осей Х и Y;

11)первый ряд данных;

12)второй и последующие ряды данных;

13)линии проекции;

14)линии мини-макс уровней;

15)плюс-маркеры;

16)минус-маркеры;

17)линии рядов.

Каждый из этих классов можно выделить на диаграмме щелчком левой кнопки мыши и затем отредактировать. Редактирование можно производить с помощью контекстно-зависимого меню (индивидуального для каждого элемента), которое вызывается щелчком правой кнопки мыши на выделенном элементе (двойной щелчок левой кнопки мыши вызывает сразу же окно диалога форматирования соответствующего элемента). Результаты редактирования графика, на наш (может быть и не самый изысканный) вкус, представлены на рис. 8.