excel / Excel_№1_Лабораторный практикум

Рис. 19. Вид графика после форматирования

Рис. 20. Окно форматирования линий сетки

30

Корни

Рис. 22. График функции sin(x)

Шаг 8. Найти (уточнить корень). Для уточнения корня можно использовать команду меню Сервис → Подбор параметра4.

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

Относительная погрешность вычислений задается с помощью команды меню Сервис → Параметры во вкладке Вычисления

(рис. 23). Задайте значение относительной погрешности 0,0000001.

Далее задайте начальное приближение первого корня. Для этого на свободной части листа задайте значение близкое к корню (например, для разбираемого примера, это будет -3,5). Скопируйте формулу, содержащую вашу функцию, и вставьте ее в соседнюю ячейку

(рис. 24).

4 В Excel 2010 используют Данные → Работа с данными → Анализ

→ «Что-Если» → Подбор параметра

32

Замените на 0,0000001

Рис. 23. Окно параметров для установки погрешности вычислений

Рис. 24. Задание начального приближения корня

33

Теперь можно вызывать команду нахождения более точного вычисления корня Сервис → Подбор параметров. Открывшееся окно Подбор параметров необходимо заполнить следующим образом

(рис. 25):

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

-в текстовом окне Значение указать значение 0, т.к. функция в точке корня должна иметь значение 0;

-в текстовом окне Изменяя значение ячейки: указать ссылку на адрес ячейки, где находится первое приближение корня .

После нажатия кнопки ОК откроется окно Результат подбора параметров, а в указанных ячейках произойдут изменения (рис. 26). Окно закройте кнопкой ОК.

Формат представления значения корня сделайте с 6-ю знаками после запятой.

Рис. 25. Заполнение окна Подбор параметров

Рис. 26. Результат работы Подбор параметра

34

Шаг 9. Проанализировать полученный результат. Если вы заранее не установили формат представления данных, то полученное значение функции в корне может быть представлено в экспоненциальном формате, т.е. с плавающей запятой, например, -7,29Е-09. Его интерпретация:

Значение функции в точке корня получилось достаточно близким к нулю, но не 0 (по модулю ≈ 0,00000001), значит значение корня -3,141593 найдено с заданной относительной погрешностью

0,0000001.

Рис. 27 показывает, как может выглядеть в итоге лист задания «Анализ функции».

Рис. 27. Окончательный вид листа задания «Анализ функции»

35

3.2.ПОСТРОЕНИЕ ГРАФИКА КУСОЧНО-ЗАДАННОЙ ФУНКЦИИ

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

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

Шаг 1. Аналогичен первому шагу первого задания. Шаг изменения аргумента подберите сами.

Шаг 2. Задать формулу функции. Так как заданная функция вычисляется по разным формулам, в зависимости от того, в каком диапазоне находится аргумент х (рис. 28), то при выполнении данного задания необходимо использовать функцию ЕСЛИ.

Рис. 28. Вид функции в зависимости от аргумента

Функция ЕСЛИ(х1; х2; х3) имеет три параметра:

- х1 – логическое выражение;

- х2 – значение если истина;

- х3 – значение если ложь.

Функция ЕСЛИ вычисляет сначала логическое выражение, а затем, если логическое выражение истинно, то возвращает значение параметра х2; если логическое выражение ложно, то возвращает значение параметра х3.

36

Так как в данном примере функция вычисляется по трем разным формулам, то функцию ЕСЛИ необходимо использовать два раза.

Порядок вызова функции ЕСЛИ при использовании Мастера функций:

-В списке функций выбирается функция ЕСЛИ, в окне Аргументы функции заполняются два первых текстовых окошка (рис. 29).

-После того как курсор устанавливается в третьем текстовом окошке, снова вызывается функция ЕСЛИ (рис. 30. Эта функция как раз использовалась последний раз), и заполняются текстовые окошки уже для нее (рис. 31).

-После окончания набора нажимается кнопка ОК.

Рис. 29. Задание параметров при первом вызове функции ЕСЛИ

Рис. 30. Последняя используемая функция (ЕСЛИ)

37

3.3. АППРОКСИМАЦИЯ ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ

Пусть в результате эксперимента были получены некоторые данные, представленные в виде таблицы. Задача инженера состоит в том, чтобы подобрать такую функцию f (x) , значения которой в за-

данных точках x1 , x2 , K, xn возможно мало отличались бы от опытных данных.

Геометрическое представление задачи построения такой аналитической зависимости представлено на рис. 33.

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

ная функция – эмпирической зависимостью.

Рис. 33. Геометрическое представление задачи аппроксимации

В MS Excel аппроксимация осуществляется следующим обра-

зом:

-по экспериментальным данным строится точечная диаграмма;

-подбирается соответствующая линия тренда, аппроксимирующая приведенную на графике зависимость.

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

39