А.Г. Пимонов Информатика. Рабочая программа дисциплины, методические указания и контрольные задания

	10
	X = A
	H = (B-A)/N
	i=1,N+1,1
	i, X
да	X <= -1	нет
Не опред.		F(x)
	H = H + X
Рис. 3. Блок-схема алгоритма табулирования

да X≤0,0001 нет

						ln( 1 + x )					sin( πx )
F = sin(1)						F = sin				e
							x

Рис. 4. Блок–схема алгоритма вычисления значений функции

Текст же VBA–подпрограммы табулирования будет выглядеть следующим образом:

Sub Tabulir(ByVal A As Single, ByVal B As Single, ByVal N As Integer)

	11
Dim i As Integer	'|
Dim H As Single	'|Описание перемененных
Dim X As Single	'|
H = (B - A) / N	'Расчет шага табулирования
X = A

Worksheets("••••1").Select 'Выбор листа для построения таблицы

'Начало цикла, переменную I используем как параметр цикла и счетчик итераций

For i	= 1	To N + 1
Cells(5		+ i, 2).Value = i 'Выводим номер итерации
Cells(5		+ i, 3).Value = X
		'Выводим текущее значение переменной Х
If X <=		-1 Then 'Используем ветвление
	Cells(5 + i, 4).Formula="Не опр." 'Вып. если Х ≤ -1
		Else	'Иначе
	Cells(5 + i, 4).Value = f(X)		'Вып. если Х > -1
End	If 'Конец ветвления
X =	X +	H 'Переходим к следующему Х
Next 'Конец цикла

End Sub 'Конец подпрограммы табулирования

2.5. Задание для выполнения контрольной работы №1

По номеру зачётной книжки выберите из прил. 3 вариант задания для выполнения контрольной работы, воспользовавшись следующей формулой: №варианта=1+(№зач. mod 50) (A mod B – остаток от деления A на B, например, (13 mod 50)=13), где №зач. – число, составленное из двух последних цифр номера зачётной книжки. Например, пусть номер зачётной книжки 237633, тогда №зач.=33, №варианта=1+(33 mod 50)=1+33=34. Разработайте алгоритм и напишите подпрограмму (паскаль или VBA) табулирования выбранной функции. Отчёт по контрольной работе должен состоять из следующих разделов:

1)постановка задачи (см. п.2.1);

2)анализ области определения выбранной функции (см. п.2.2);

3)блок-схема алгоритма табулирования (см. п.2.3, рис. 2; п.2.4,

рис.3,4);

12

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

п.2.3 или п.2.4).

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

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

3. МЕТОДИЧЕСКИЕ УКАЗАНИЯ К КОНТРОЛЬНОЙ РАБОТЕ №2 «ТАБУЛИРОВАНИЕ И ПОИСК ЭКСТРЕМУМОВ

ФУНКЦИИ ОДНОЙ ПЕРЕМЕННОЙ СРЕДСТВАМИ EXCEL»

С момента появления в 1985г. первой версии электронной таблицы Excel эта программа, постоянно совершенствуясь, остаётся на самых передовых позициях в ряду аналогичных программных продуктов. «Простые задачи должны решаться просто». Этому постулату как нельзя лучше отвечают вычислительные возможности Excel, которые без оговорки можно назвать безграничными. Практически любую задачу вычислительного характера можно решить средствами Excel, используя уникальную по простоте технологию, условно называемую «щёлк – щёлк – щёлк». В сочетании с языком программирования Visual Basic for Application (VBA) Excel приобретает универсальный характер. Для ускорения и облегчения вычислительной работы Excel предоставляет в распоряжение пользователя мощный аппарат функций рабочего листа, позволяющих осуществить математические, финансовые, статистические и т.д. расчёты.

3.1.Функции рабочего листа

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

13

разнообразных вычислений. Все они в соответствии с характером вычислений делятся на 12 групп:

1)математические функции;

2)текстовые функции;

3)логические функции;

4)информационные функции;

5)функции ссылки и автоподстановки;

6)функции даты и времени;

7)финансовые функции;

8)инженерные функции;

9)статистические функции;

10)функции проверки свойств и значений;

11)функции DDE и внешние функции;

12)функции для работы со списками.

Обращение к каждой функции производится указанием её имени и следующего за ним в круглых скобках списка аргументов (параметров). Наличие круглых скобок обязательно, именно они служат признаком того, что используемое имя является именем функции. Аргументы списка разделяются точкой с запятой. Их количество не должно превышать 30, а длина формулы, содержащей сколько угодно обращений к функциям, не может превышать 1024 символов. В качестве аргументов могут использоваться константы, адреса ячеек, интервалы, выражения. Например:

=СУММ(2,71; A1; B2:D10; 2*SIN($A$3*ПИ()))

Наличие знака равенства (=) перед формулой обязательно, именно на основании его присутствия перед выражением Excel интерпретирует (точнее пытается интерпретировать) выражение как формулу, а не как текст. Большинство имён функций образовано буквами русского алфавита и имеет определённую смысловую нагрузку, иногда понятную даже неискушённому пользователю (=СУММ() – возвращает сумму своих аргументов). Все имена при вводе формулы лучше набирать строчными буквами, тогда правильно введённые имена Excel отображает прописными буквами.

14

Нет возможности, да и необходимости приводить полное описание более трёх сотен встроенных функций. Excel обладает отличной справочной системой, которой в состоянии воспользоваться даже малоопытный пользователь. Описание встроенных математических функций Excel приведено в прил. 4. Логические функции, которые понадобятся при выполнении контрольной работы, и примеры их использования даны в прил. 5.

3.2. Решение задачи табулирования и поиска экстремумов функции одной переменной

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

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

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

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

x

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

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

15

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

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

3.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 примет вид, приведённый на рис. 5. Вводить дан-

Рис. 5. Фрагмент рабочего листа ные и редактировать их на рабочем листе можно различными способами.

16

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

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

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

Рис. 6. Фрагмент рабочего листа

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

=$B$2+(A6-1)*$F$2 и т.д.

A4:A14.

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

=$B$2+(A4-1)*$F$2 мы и введём в ячейку B4, затем протащим её за маркер заполнения до ячейки B14 включительно. В резуль-

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

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

17

вавшись логическими функциями рабочего листа, в ячейку C4 введём формулу, в которой учтены все возможные ситуации:

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

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

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

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

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

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

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

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

ван через пункт меню Сервис/Поиск решения.

После открытия диалога Поиск решения (рис.

Рис. 8. Окно диалога Поиск решения

Рис. 7. Фрагмент рабочего листа

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

1) в поле Установить целевую ячейку

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

18

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

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

сле чего рабочий лист примет вид, представленный на рис. 9.

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

(например	найти
максимум и минимум
Рис. 10. Диапазон ячеек для сохраняемой модели одной функции,	или

Рис. 11. График функции

19

наибольшие значения нескольких функций), то удобнее сохранить эти моде-

ли, используя кнопку Параметры / Сохранить модель окна Поиск решения.

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

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

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

Для построения необходимого нам графика воспользуемся услугами

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

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

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

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

– в столбцах);

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

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

листе).

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