Практическая работа №11
Тема: Использование электронной таблицы для численного
моделирования
Цель работы: Научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели, применяя надстройку Поиск решения.
Время работы: 2 часа.
Задание: Определить чему будет равна численность населения России в начале третьего тысячелетия.
Для того чтобы решить эту задачу, надо знать, как со временем будет меняться численность населения России, т.е. необходимо иметь функцию, выражающую зависимость численности населения от времени. Такая функция зависит от многих факторов: экологии, состояния медицинского обслуживания, морали, права и даже от политической обстановки. Обозначим эту функцию f(t). Обобщив демографические данные, можно указать общий вид функции:
,
(1)
где коэффициенты a, b для каждого государства свои;
e – основание натурального логарифма.
Значение функции f(t) можно получить из статистического справочника. Зная эти данные, можно приближенно подобрать a и b так, чтобы теоретические значения f(t),вычисленные по формуле (1), не сильно отличались от данных справочника (т.е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений – это модуль разности двух чисел: фактического и соответствующего теоретического значений f(t). Максимальное отклонение называют погрешностью.
Итак, нам необходимо найти такие a и b, чтобы погрешность была наименьшей, а затем при этих a и b вычислить численность населения России в 2000 г., т.е. f(t) при t=100.
Порядок работы
-
Создайте таблицу Таблица эксперимента (см. рис. 25).
-
В столбец С занесите значения t с 1960г. по 1995г.
3. В столбец D занесите взятые из справочника значения численности населения России за соответствующие годы.
Рис. 25
Выполним подбор значений коэффициентов a и b. Подбор произведем в два этапа. Сначала определим значения a и b приближенно, для чего построим график роста статистической численности и аппроксимируем его. Затем уточним полученные коэффициенты a и b с использованием функции Excel Поиск решения.
4. Постройте график типа X-Y по данным таблицы. X – годы; Y – статистическая численность. Выберите точечную диаграмму со значениями, соединенными сглаживающими линиями без маркеров. В качестве исходных данных задайте адреса ячеек $C$2:$D$11.
5. Перемасштабируйте оси X и Y. Выделите ось X. Вызовите контекстное меню и выберите команду Формат оси – Шкала. Установите минимальное значение X в 60, максимальное значение – в 100, цена основных делений - 5, цена промежуточных делений – 1, ось Y (значений) пересекает в значении: 60 (см. рис. 26). Аналогично перемасштабируйте ось Y.
Рис. 26
6. Аппроксимируйте полученную кривую. Аппроксимировать, т.е. статистические данные по численности населения представить на графике плавной кривой. Эта кривая называется линией тренда. Выделите линию графика, щелкнув по ней правой клавишей мыши. Выберите команду контекстного меню Добавить линию тренда. Выберите экспоненциальный тип (см. формулу (1)). В диалоговом окне Линия тренда выберите вкладку Параметры. Установите флажок Показывать уравнение на диаграмме и нажмите кнопку OK.
В результате на графике появится линия тренда и уравнение с подобранными коэффициентами a и b (см. рис. 27).
7. Занесите полученные значения коэффициентов a и b в ячейки A3 и B3. Коэффициенты a и b не изменяются с течением времени, это константы, следовательно, при вычислении теоретической численности они должны быть адресованы абсолютно. Присвойте ячейкам A3 и B3 имена a и b соответственно, используя пункт меню Вставка – Имя – Присвоить.
Рис. 27
8. В ячейку E3 занесите формулу =a*EXP(b*C3), чтобы вычислить теоретическую численность по формуле (1). Скопируйте формулу в ячейки E4:E11.
9. Вычислите отклонение. В ячейку F3 занесите формулу =ABS(E3-D3), т.к. отклонение – это модуль разности теоретических и фактических значений функции f(t). Скопируйте формулу в ячейки F4:F11.
10. Вычислите погрешность. В ячейку F13 введите функцию определения максимального из чисел этого столбца, т.к. погрешность – это максимальное отклонение.
11. Подберите значения коэффициентов a и b более точно.
При полученных в результате аппроксимации коэффициентах a и b погрешность уже неплохая (по условию она должна быть в пределах нескольких миллионов). Но коэффициенты a и b можно подобрать более тонко, используя инструмент Поиск решения.
Выполните команду Сервис – Поиск решения. Если этого пункта в меню нет, то его следует загрузить, выполнив команду Сервис – Надстройки. В открывшемся диалоговом окне Надстройки следует поставить флажок около надстройки Поиск решения.
В диалоговом окне Поиск решения сделайте необходимые настройки (см. рис. 28):
– в поле Установить целевую ячейку укажите адрес ячейки $F$13;
– установите переключатель минимальному значению;
– в поле Изменяя ячейки укажите $A$3:$B$3.
– нажмите кнопку Выполнить.
Рис. 28
Когда Excel найдет решение, то откроется диалоговое окно Результаты поиска решения (см. рис. 29). Нажмите кнопку OK. Произойдет изменение значений ячеек в соответствии с найденным решением. Обратите внимание, что коэффициенты a и b изменились, а погрешность уменьшилась.
Рис. 29
12. Определите численность населения России в 2000 году. Подставьте в ячейку C12 число 100, что соответствует 2000 г. В ячейку E12 скопируйте формулу из ячейки E11. В ячейке E12 появится искомое число.