Решение. 1 В ячейки А2:В12 введём исходные данные, в ячейку А14 – 12 (рисунок 3.13).

1 В ячейки А2: В12 введём исходные данные, в ячейку А14 – 12 (рисунок 3.13).

2 Ячейки B16 и В17 отведём под искомые коэффициенты а и b.

3 В ячейку С3 введём формулу для вычисления квадрата отклонения заданной зависимости от экспериментальных данных в первой точке.

Рисунок 3.13 – Ввод исходных данных и вычисление квадратов отклонений
в таблице Excel

4 Скопируем полученную формулу в ячейки С4: С12.

5 В ячейке С13 вычислим сумму по диапазону С3: С12 – сумму квадратов отклонений (рисунок 3.14).

6 Выделим ячейку С13 (с суммой квадратов отклонений) и выполним команду Сервис / Поиск решения.

7 В окне Поиск решения (рисунок 3.14):

- выбрать переключатель • минимальному значению;

- Изменяя ячейки: В16: В17;

- щёлкнуть по кнопке ОК.

Рисунок 3.14 – Заполнение окна Поиск решения для вычисления
коэффициентов а и b

8 В результате в ячейках В16, В17 получим значения искомых коэффициентов. Результаты представим с тремя десятичными знаками.

9 Для построения графика теоретической кривой в ячейку D3 введём формулу: =$B$16*EXP($B$17*A3) и скопируем её на диапазонD4: D12 и в ячейку D14.

10 Выделим диапазоны А2: В12, D2: D12 и с помощью Мастера диаграмм построим точечный график (рисунок 3.15). Экспериментальные данные представим в виде отдельных маркеров, а график теоретической кривой в виде линии.

Рисунок 3.15 – Отображение на графике исходных данных и теоретической кривой

10 Для добавления на график точки, соответствующей предсказанному значению для 12-й недели: - выберем команду Исходные данные (из меню Диаграмма или из контекстного меню для области диаграммы); - в окне Исходные данные (рисунок 3.16) щёлкнуть по кнопке Добавить и заполнить поля Имя:, Значения Х:, Значения Y: соответствующими ссылками и подписями.

На рисунке 3.17 представлен результат решения задачи.

Рисунок 3.17 – Результат решения задачи аппроксимации с помощью метода наименьших квадратов и надстройки Excel Поиск решения