Аппроксимируйте полученную кривую

Сделайте заголовок и заполните шапку таблицы.

1.2. Столбцы А и В отведите под коэффициенты а Ь соответ­ственно.

В столбец С занесите значения t с 1960 г. (см. рис. 8.1).

1.4. В столбец D занесите взятые из справочника значения чис­ленности населения России с 1960 г. (см. рис.8.1).

ЗАДАНИЕ 2. Подберите значения коэффициентов а и Ь.

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

2.1. Постройте точечную диаграмму по данным таблицы. (Х - годы; Y- статистическая численность).

Перемасштабируйте оси Х и Y.

Для более наглядного представления данных необходимо пе­ремасштабировать оси.

• Выделите ось X.

• Вызовите контекстно-зависимое меню и выполните коман­ду: формат оси, шкала

• Установите минимальное значение X, основную единицу измерений и пересечение с осью Y (см. рис. 8.4а), чтобы график получился как на рис. 8.4в.

РИС. 8.4а

РИС.8.4в

• Аналогично перемасштабируйте ось Y (рис. 8.4б)

РИС. 8.4б.

Аппроксимируйте полученную кривую.

Необходимо статистические данные по численности населе­ния представить на графике плавной кривой (аппроксимировать). Эта кривая называется линией тренда. Для построения линии тренда:

• Выделите линию графика правой кнопкой.

• Выполните команду контекстно-зависимого меню: ДОБАВИТЬ ЛИНИЮ ТРЕНДА.

Откроется окно (рис. 8.2)

РИС.8.2

• Выберите экспоненциальный тип (см. формулу (1)).

• Выберите в диалоге Линия тренда вкладку параметры (рис. 8.3).

РИС. 8.3.

• Установите флажок [x] показывать уравнение на диаграмме и нажмите кнопку ОК.

В результате на графике появится линия тренда и уравнение с подобранными коэффициентами а и Ь (рис. 8.4).

РИС. 8.4.

2,4. Занесите полученные значения коэффициентов а и Ь в ячейки A3 и ВЗ и присвойте им имена:

A3 имя а

ВЗ имя b

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

ЗАДАНИЕ 3. Вычислите теоретическую численность по форму­ле (1).

3.1. В ячейку ЕЗ занесите формулу =а *ЕХР(b * СЗ)

3.2. Скопируйте формулу в ячейки Е4:Е11

ЗАДАНИЕ 4. Вычислите отклонение.

Отклонение - это модуль разности теоретических и факти­ческих значений функции f(t).

4.1. В ячейку F3 занесите формулу =:ABS(EЗ- D3)

4.2. Скопируйте формулу в ячейки F4:F11

ЗАДАНИЕ 5. Вычислите погрешность.

Погрешность - это максимальное отклонение.

• В ячейкур 13 введите функцию определения максимального из чисел этого столбца.

ЗАДАНИЕ 6. Подберите значения коэффициентов а и b более точно.

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

6.1. Выполните команду: сервис, Поиск решения

Если этого пункта в меню нет, то его следует загрузить, вы­полнив команду меню сервис, дополнения. В открывшемся диа­логе следует поставить флажок [х] около дополнения Поиск ре­шения.

Сделайте необходимые настройки в окне диалога Поиск решения (рис. 8.5).

В поле установить целевую ячейку укажите адрес ячейки $F$13 (в ней погрешность).

• Установите переключатель [·] минимальному зна чению

• В поле изменяя ячейки укажите $А$3:$В$3

РИС.8.5

В этом поле задаются адреса ячеек, значения которых будут варьироваться в процессе поиска решения. В нашем случае - это адреса ячеек со значениями а и Ь.

• Нажмите на кнопку <Выполнить>.

Начнется поиск решения. Так как у нас довольно точные коэффициенты а и Ь, то поиск займет немного времени. Когда Excel найдет решение, то откроется диалог, представленный на рис. 8.6.

РИС.8.6.

• Нажните на кнопку <ОК>.

Произойдет изменение значений ячеек в соответствии с най­денным решением. Обратите внимание, что коэффициенты о и Ь изменились, а погрешность уменьшилась.

ЗАДАНИЕ 7. Определите численность населения России в 2000 г.