Файл: Л.С. Таганов Решение численных задач средствами MS Excel.pdf

30

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

Для форматирования рабочего листа выполнить следующие действия:

-отформатировать заголовок (название работы). Для этого, выделив текст заголовка, установить: тип шрифта <Times New Roman>, размер символов <16> , начертание текста <Полужирный> и, при необходимости, расположить его по середине строки относительно содержимого на листе;

-убрать рамку по периметру объекта-формулы и переместить его так, чтобы он сочетался с заголовком;

-отформатировать исходные данные. Для этого: выделить заголовок и установить начертание текста <Полужирный>, выделить столбец ячеек B4:B7 и сдвинуть содержимое ячеек к правому краю, выделить столбец ячеек C4:C7 и сдвинуть содержимое ячеек к левому краю;

-расположить название таблицы по середине строки относительно левой и правой границ таблицы;

-выделив строку с названиями столбцов таблицы A9:D9, расположить содержимое ячеек по центру и установить начертание текста

<Полужирный>;

-выделив диапазон ячеек A9:D30, оформить рамку таблицы;

-выделив диапазон ячеек B10:C30, командой меню <Формат/ Ячейки> представить числа в ячейках в формате с двумя значащими цифрами в дробной части, а затем числа расположить по центру ячеек в том числе и для столбца A10:A30;

-скопировать объект-формулу в область диаграммы (в строку названия диаграммы);

-отформатировать диаграмму, если это не было сделано при её создании;

-скрыть линии сетки рабочего листа, воспользовавшись командой меню <Сервис/ Параметры/ вкладка Вид> и убрав флажок в окне <Сетка>;

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

31

3. ОФОРМЛЕНИЕ ОТЧЁТА

Отчёт оформить в виде документа MS Word.

3.1. Создание документа

Для создания документа (текстового файла) выполнить следующие действия:

1)не закрывая файл рабочей книги и не выходя из программы MS Excel, выполнить следующие команды:

-щёлкнуть мышкой по кнопке меню <Пуск>;

-в меню <Пуск> щёлкнуть мышкой по строке <Программы>;

-в меню <Программы> щёлкнуть мышкой по ярлыку Microsoft Word ;

2)после загрузки программы MS Word выполнить следующие команды:

- щёлкнуть мышкой по стрелке окна <Масштаб> и установить

масштаб 75% или 50%;

-щёлкнуть мышкой по стрелке окна <Шрифт> и установить шрифт <Times New Roman>;

-щёлкнуть мышкой по стрелке окна <Размер> и установить размер символов <16>;

-щёлкнуть мышкой по стрелке окна <Стиль> и установить стиль

<Основной текст>;

3) для записи файла текстового документа с заданным именем в свой каталог выполнить следующие команды:

-щёлкнуть мышкой по кнопке меню <Файл>;

-в раскрывшемся меню щёлкнуть мышкой по строке

<Сохранить как…>;

-в диалоговом окне <Сохранение документа> щёлкнуть мышкой по стрелке окна <Мои документы>;

-в раскрывшемся меню щёлкнуть мышкой по строке с адресом своего каталога;

-в окне <Имя файла> вместо стандартного имени файла ввести своё название документа, например, <Отчёт по лабораторной работе>;

-щёлкнуть мышкой по кнопке <Сохранить>.

32

3.2.Структура документа

1.Титульный лист (образец на странице 54).

2.Постановка задачи (что требуется выполнить по данной работе?).

3.Анализ области определения функции (результаты анализа и выводы).

4.Результаты выполнения работы (исходные данные, копии с рабочего листа Excel таблицы и графика функции). Таблицу и график расположить на отдельных листах. Текст таблицы отформатировать в соответствии с установками MS Word. Таблицу и график отформатировать по всей ширине рабочего поля листа.

После завершения работы и проверки правописания документ защитить.

ЛАБОРАТОРНАЯ РАБОТА №4

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

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

Цель и содержание работы: овладеть практическими навыками решения нелинейных уравнений и поиска экстремумов функции одной переменной средствами программы MS Excel. Время на выполнение работы 4 часа.

1.РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ

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

34

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

Реализация решения этим средством сводится к следующим действиям:

-в выбранную ячейку рабочего листа ввести текст <x=>, например, в ячейку B5 ;

-в соседнюю ячейку справа ввести значение начальной границы заданного отрезка функции, например, в ячейку C5;

-в соседнюю ячейку (строкой ниже) ввести текст <f(x)=>, например, в ячейку B6;

-в соседнюю ячейку (справа от предыдущей) ввести формулу, в качестве которой использовать левую часть приравненного к

нулю уравнения, например, в ячейку C6 ввести =C5*TAN(C5)-1. Эта формула соответствует уравнению вида:

tgx = 1/x или xtg(x) – 1 = 0;

-щёлкнуть мышкой по кнопке меню <Сервис>;

-в раскрывшемся меню щёлкнуть мышкой по строке

<Подбор параметра>;

-в появившемся диалоговом окне <Подбор параметра> удалить адрес текущей ячейки в окне <Установить в ячейке:> и щёлкнуть мышкой по ячейке с формулой, в окно <Значение:> ввести 0 (ноль), щелкнуть мышкой в окне <Изменяя значение ячейки:>, а затем щёлкнуть мышкой по ячейке со значением X;

-щёлкнуть мышкой по кнопке <ОК>. Результат получен.

1.2.Циклические ссылки

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

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

- щёлкнуть мышкой по кнопке меню <Сервис>;

35

-в раскрывшемся меню щёлкнуть мышкой по строке

<Параметры>;

-в диалоговом окне <Параметры> щёлкнуть мышкой по вкладке

<Вычисления>;

-включить флажок в окне <Итерации>;

-установить вариант вычислений <автоматически>;

-щелкнуть мышкой по кнопке <ОК>.

Реализация решения этим средством сводится к следующим действиям:

-получить выражение первой производной от выражения заданной функции;

-в произвольную ячейку, например E5, ввести текст <Xнач=>;

-в соседнюю ячейку снизу, например E6, ввести текст <X=>;

-в следующую ниже ячейку, например E7, ввести текст <F(X)=>;

- в нашем примере в ячейку F5 ввести начальное значение X, в качестве которого можно использовать среднее значение границ заданного отрезка функции или значение левой границы отрезка, если оно не равно нулю;

- в ячейку F6 ввести рекуррентную формулу, задающую

F ( x )

вычисления по методу Ньютона x − F ' ( x ) . При этом

использовать логическую функцию ЕСЛИ(). Применение логической функции позволит исключить вычисление формулы при возможных неопределённостях, так как в ячейке F3 исходное значение X будет равно нулю. Для функции xtgx – 1 выражение первой производной будет иметь вид tgx+x/Cos2x. Таким образом, формула, которую надо ввести в

ячейку F6, будет иметь вид:

=ЕСЛИ(F6=0;F5;F6-(F6*TAN(F6) – 1)/(TAN(F6) + F6/COS(F6)^2);

- в ячейку F7 ввести формулу, содержащую выражение заданной функции. Искомый результат будет в ячейке F6.

Чтобы сменить начальное приближение Xнач необходимо выполнить следующие действия:

-в ячейке со значением Xнач (F5) удалить прежнее значение и ввести новое;

-дважды щёлкнуть мышкой по ячейке с формулой (F6). Это приведёт к обнулению прежнего результата. Нажать клавишу

<Enter>

36

1.3. Поиск решения

Задачи, которые можно решать с помощью <Поиска решения>, в общей постановке формулируются так:

Найти:

x1, x2, …, xn такие , что F(x1, x2, …, xn) → (Max; Min; = Value) при ограничениях: G(x1, x2, …, xn) → (≤ Value; ≥ Value; =Value),

где Value – это значение.

Искомые переменные x1, x2, …, xn – ячейки рабочего листа – называются регулируемыми ячейками.

Целевая функция F(x1, x2, …, xn) должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определённые пользователем, и должна зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одного из вариантов:

-найти максимум целевой функции;

-найти минимум целевой функции;

-добиться того чтобы целевая функция имела фиксированное

значение: F(x1, x2, …, xn) = a.

Функции G(x1, x2, …, xn) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На регулируемые ячейки можно наложить и другие дополнительные ограничения.

Выше для решения нелинейных уравнений были рассмотрены: средство <Подбор параметра> (п. 1.1) и метод Ньютона с использованием циклических ссылок (п. 1.2). Рассмотрим, как можно воспользоваться <Поиском решения> (Решателем) для той же цели.

Реализация <Поиска решения> сводится к следующим действиям: а) в произвольную ячейку, например H5, ввести текст <X=>;

б) в ячейку справа от H5, например I5, ввести значение начальной границы заданного отрезка функции;

в) в соседнюю ячейку снизу, например H6, ввести текст <f(x)=>; г) в соседнюю ячейку (справа от H6), например I6, ввести формулу, в качестве которой использовать левую часть приведённого к нормальному виду уравнения, например, в ячейку I3 ввести =I5*TAN(I5)-1. Эта формула соответствует

уравнению вида: tgx = 1/x или xtg(x) – 1 = 0;

д) щёлкнуть мышкой по ячейке с целевой функцией <I6>; е) щёлкнуть мышкой по кнопке меню <Сервис>;

з) в появившемся диалоговом окне <Поиск решения> выполнить следующие установки:

-в окне <Установить целевую ячейку:> установить абсолютный адрес ячейки с целевой функцией ($I$6);

-установить переключатель варианта в положение <значению:> и ввести значение 0;

-в окне <Изменяя ячейки:> установить абсолютный адрес

ячейки со значением X ($I$5);

-щёлкнуть мышкой по кнопке <Выполнить>.

-в диалоговом окне <Результаты поиска решения>

щёлкнуть по кнопке <ОК>. Решение найдено.

1.4.Варианты заданий

Вариант задания соответствует порядковому номеру в списке группы. Заданное уравнение привести к нормальному виду, то есть все части уравнения справа перенести в левую часть и приравнять нулю. В дальнейшем левая часть уравнения и будет являться целевой функцией. Упростить целевую функцию насколько это возможно и получить выражение первой производной от выражения целевой функции. Уравнение решить каждым из рассмотренных средств и сравнить полученные результаты. Допустимое их различие может быть M10 –6.

Таблица 1.1