Л.С. Таганов Решение численных задач средствами MS Excel
.pdf30
Кроме того, при желании, можно создать макрос для всего процесса оформления рабочего листа.
Для форматирования рабочего листа выполнить следующие действия:
-отформатировать заголовок (название работы). Для этого, выделив текст заголовка, установить: тип шрифта <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.РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ
Вобщем случае решение нелинейного уравнения проводится численно в два этапа (здесь речь идёт лишь о вещественных корнях уравнения). На первом этапе производится поиск интервалов, в которых содержится только по одному корню. Второй этап решения связан с уточнением корня в выбранном интервале (определением значения корня с заданной точностью). Известно, что корень уравнения – это
33
значение аргумента функции, при котором функция равна нулю. В графическом представлении – это может быть точка пересечения или касания графика функции с осью X.
При решении уравнения не надейтесь никогда найти точное значение корня и добиться обращения функции в нуль при использовании компьютера, где сами числа представлены ограниченным числом знаков. Здесь критерием может служить приемлемая абсолютная или относительная погрешность корня. Если, например, относительная погрешность равна 0,000001 (ε = 0,000001), то искомый результат буде иметь 6 верных цифр после запятой (n=lg(1/ε)).
В настоящей лабораторной работе решение уравнений сводится к выполнению второго этапа, то есть к поиску корня в заданных границах отрезка функции.
Для решения нелинейных уравнений в программе MS Excel имеются следующие средства:
-подбор параметра;
-поиск решения;
-циклические ссылки.
1.1.Подбор параметра
При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность (относительная погрешность) устанавливаются следующей последовательностью команд:
-щёлкнуть мышкой по кнопке меню <Сервис>;
-в раскрывшемся меню щёлкнуть мышкой по строке
<Параметры>;
-в появившемся диалоговом окне <Параметры> щёлкнуть мышкой по вкладке <Вычисления>;
-во вкладке уменьшить относительную погрешность до 0,000001 (окно <Относительная погрешность:>);
-в окне <Предельное количество итераций>, при желании,
можно увеличить количество итераций. Однако это едва ли улучшит искомый результат;
-щёлкнуть по кнопке <ОК>.
При подборе параметра Excel изменяет значение аргумента функции в одной конкретной ячейке до тех пор, пока вычисления
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>; е) щёлкнуть мышкой по кнопке меню <Сервис>;
|
|
|
37 |
|
|
|
|
ж) в |
раскрывшемся |
меню |
щёлкнуть мышкой |
по строке |
|||
<Поиск решения>. Если этой строки в меню нет, |
то в этом |
||||||
же |
меню |
надо |
встать |
на |
строку <Надстройки…>, |
||
щёлкнуть |
мышкой, установить |
флажок |
в |
окошечке |
|||
<Поиск решения> |
диалогового |
окна <Надстройки> и |
|||||
щёлкнуть |
мышкой |
по кнопке <ОК>. После этого повторить |
|||||
запуск <Поиска решения>; |
|
|
|
|
|||
з) в появившемся диалоговом окне <Поиск решения> выполнить следующие установки:
-в окне <Установить целевую ячейку:> установить абсолютный адрес ячейки с целевой функцией ($I$6);
-установить переключатель варианта в положение <значению:> и ввести значение 0;
-в окне <Изменяя ячейки:> установить абсолютный адрес
ячейки со значением X ($I$5);
-щёлкнуть мышкой по кнопке <Выполнить>.
-в диалоговом окне <Результаты поиска решения>
щёлкнуть по кнопке <ОК>. Решение найдено.
1.4.Варианты заданий
Вариант задания соответствует порядковому номеру в списке группы. Заданное уравнение привести к нормальному виду, то есть все части уравнения справа перенести в левую часть и приравнять нулю. В дальнейшем левая часть уравнения и будет являться целевой функцией. Упростить целевую функцию насколько это возможно и получить выражение первой производной от выражения целевой функции. Уравнение решить каждым из рассмотренных средств и сравнить полученные результаты. Допустимое их различие может быть M10 –6.
Таблица 1.1
№ |
Уравнение |
a |
b |
№ |
Уравнение |
a |
b |
п/п |
|
|
|
п/п |
|
|
|
1 |
Ln(x) = 1/x |
1 |
2 |
17 |
Ln(x) = Sin2(x) |
0 |
π/2 |
2 |
Ln(x) = Sin(x) |
1 |
3 |
18 |
Ln(x) = e-x |
0 |
2 |
3 |
Sin(x) = 1/x |
0 |
π/2 |
19 |
Lg(x) = e-x |
0 |
1 |
4 |
Sin(x) = x/2 |
π/2 |
π |
20 |
Cos(x) = x3 |
0 |
π/2 |
5 |
Cos(x) = x |
0 |
π/2 |
21 |
Cos(x) = x2 |
0 |
π/2 |
6 |
Cos(x) = Ln(x) |
0 |
π/2 |
22 |
Lg(x) = 10-x |
0 |
10 |
38
Продолжение таблицы 1.1
|
№ |
|
Уравнение |
|
|
|
|
a |
|
|
|
|
b |
|
|
№ |
|
|
Уравнение |
|
|
|
|
a |
|
|
|
|
b |
|
|
п/п |
|
|
|
|
|
|
|
|
|
|
|
п/п |
|
|
|
|
|
|
|
|
|
|
|
||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||
7 |
Cos(x) = Tg(x) |
|
0 |
|
|
|
π/2 |
|
23 |
|
Tg(x) = 1/x |
|
1,6 |
|
4,5 |
|||||||||||||||
8 |
Cos(x) = 1/x |
|
4 |
|
|
6 |
|
|
24 |
|
Ln(1+x)/x=2/π |
|
0 |
|
|
2 |
|
|||||||||||||
9 |
Cos(x) = Ln(1+x) |
|
0 |
|
|
|
π/2 |
|
25 |
|
2+Ln(x) = 1/x |
|
0 |
|
|
1 |
|
|||||||||||||
10 |
Sin(x) = x/3 |
|
|
π/2 |
|
|
π |
|
26 |
|
2+Ln(x) = 1/x2 |
|
0 |
|
|
1 |
|
|||||||||||||
11 |
|
e-x = x |
|
0 |
|
|
1 |
|
|
27 |
|
Tg(x) = 1/x2 |
|
0 |
|
|
|
π/2 |
||||||||||||
12 |
Ln(x) = 1/x2 |
|
1 |
|
|
2 |
|
|
28 |
|
Tg(x) = 1/x |
|
0 |
|
|
|
π/2 |
|||||||||||||
13 |
|
e-x = Sin(x) |
|
0 |
|
|
|
π/2 |
|
29 |
|
|
x5 + 1 = 3x |
|
0 |
|
|
1 |
|
|||||||||||
14 |
ex = 1/Sin(x) |
|
0 |
|
|
|
π/2 |
|
30 |
|
|
x + 2 = x3 |
|
1 |
|
|
2 |
|
||||||||||||
15 |
|
e-x = x2 |
|
0 |
|
|
1 |
|
|
31 |
|
x – 0,5 = x8 |
|
0 |
|
|
0,5 |
|||||||||||||
16 |
|
x +x3 = 5 |
|
1 |
|
|
2 |
|
|
32 |
|
x – 1 = x0,15 |
|
1 |
|
|
3 |
|
||||||||||||
2. ПОИСК ЭКСТРЕМУМОВ ФУНКЦИИ ОДНОЙ ПЕРЕМЕННОЙ
Решение данной задачи сводится к поиску максимального и (или) минимального значения в заданных границах отрезка целевой функции, а также соответствующего значения её аргумента.
Для практической реализации этой задачи можно использовать средство <Поиск решения>, исходя из его возможностей (п 1.3).
2.1. Поиск экстремумов с помощью <Поиска решения>
Последовательность и содержание действий такие же, как и в пункте 1.3. Отличие состоит в выборе варианта решения и в установке ограничений для изменяемой ячейки. Для поиска максимума переключатель варианта в диалоговом окне <Поиск решения> установить <максимальному значению>, а для минимума - <минимальному значению>. Далее задать ограничения для изменяемой ячейки. Порядок установки ограничений следующий:
- щёлкнуть мышкой |
по |
кнопке |
<Добавить> в диалоговом |
|||
окне |
<Поиск решения>; |
<Добавление |
ограничения> |
|||
- в |
появившемся |
окне |
||||
установить абсолютный |
адрес |
изменяемой |
ячейки в окне |
|||
<Ссылка на ячейку:>. Это можно сделать |
щелчком мышки |
|||||
по ячейке; |
|
|
|
|
|
|
39
-в среднем окне выбрать показатель ограничения (<=; >=; =);
-в окне <Ограничение:> ввести значения границ заданного отрезка функции. Эту операцию выполнить для каждой границы отрезка;
-после установки ограничения щёлкнуть мышкой по кнопке
<ОК>
-в окне <Поиск решения> щёлкнуть мышкой по кнопке <Выполнить>. Решение найдено. В ячейке с адресом целевой функции будет искомый максимум или минимум, а в изменяемой ячейке будет значение аргумента функции,
соответствующее искомому экстремуму.
Для изменения (корректировки) ограничения надо выделить строку с ограничением и щёлкнуть мышкой по кнопке <Изменить>, а затем выполнить корректировку.
Назначение остальных кнопок в диалоговом окне <Поиск
решения> уяснить самостоятельно. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
|
2.2. |
Варианты задания |
|
|
|
|
|
|
||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таблица 2.1 |
||||
№ |
|
|
Функция f(x) |
a |
|
b |
|
|
№ |
|
|
Функция f(x) |
|
a |
|
b |
|
|
||||||||||||||||
п/п |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
п/п |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
Cos(x - π/4)/ |
|
|
|
|
|
|
|
6 |
|
8 |
|
|
18 |
|
|
Sin(x - π/4)/ |
|
|
|
|
|
|
|
|
|
1 |
|
3 |
|
|
|
|
|
x |
|
|
|
|
|
x |
|
|
|
|
||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||
2 |
|
|
Sin(x - π/4)/ |
|
|
|
|
|
|
|
7 |
|
9 |
|
|
19 |
|
|
Sin(x - 3π/4)/ |
|
|
|
|
|
|
|
3 |
|
5 |
|
|
|||
|
|
x |
|
|
|
|
|
x |
|
|
|
|
||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||
3 |
|
|
Sin(x - 3π/4)/ |
|
|
|
|
|
|
9 |
|
11 |
|
|
20 |
|
|
Cos(x - 3π/4)/ |
|
|
|
|
|
|
1 |
|
3 |
|
|
|||||
|
|
x |
|
|
|
|
|
x |
|
|
|
|
||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||
4 |
|
|
Cos(x - 3π/4)/ |
|
|
|
|
|
7 |
|
9 |
|
|
21 |
|
|
(1 – Cos(x))/ |
|
|
|
|
|
|
1 |
|
3 |
|
|
||||||
|
|
x |
|
|
|
|
|
|
x |
|
|
|
|
|||||||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
5 |
|
|
Sin(x) + 5Sin(3x) |
2 |
|
3 |
|
|
22 |
|
|
Sin(x) + 5Sin(3x) |
|
0 |
|
1 |
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
6 |
|
|
3Sin(x) - Sin(3x) |
0 |
|
2 |
|
|
23 |
|
|
3Sin(x) - Sin(3x) |
|
7 |
|
9 |
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
7 |
|
|
Cos(x) – Cos(3x) |
4 |
|
6 |
|
|
24 |
|
|
Cos(x) – Cos(3x) |
|
0 |
|
2 |
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
||||||||||||||||
8 |
|
|
x2Ln(x)10-x |
2 |
|
3 |
|
|
25 |
|
|
xLn(1 + x)e-x |
|
1 |
|
3 |
|
|
||||||||||||||||
9 |
|
|
Ln(1 + x)Sin(x)/x2 |
7 |
|
9 |
|
|
26 |
|
|
Ln(1 + x)Sin(x)/x |
|
1 |
|
3 |
|
|
||||||||||||||||
10 |
|
|
Ln(x)Cos(x)x2 |
2 |
|
3 |
|
|
27 |
|
|
Ln(x)xe-x |
|
2 |
|
4 |
|
|
||||||||||||||||