Лабораторная работа №4 (3 часть) Использование электронной таблицы для численного моделирования
Цель работы:
научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.
Используемое программное обеспечение: табличный процессор Excel.
Постановка задачи
Определить, чему будет равна численность населения России в начале третьего тысячелетия.
Обозначим функцию, выражающую зависимость численности населения от времени через f(t). Обобщив демографические данные, можно указать общий вид функции f(t):
f(t)=aе bt, (1)
где коэффициенты a,b для каждого государства свои, e - основание натурального логарифма.
Эта формула лишь приближенно отражает реальность. Достаточно, если численность населения будет спрогнозирована с точностью до нескольких миллионов.
Хотя a и b не известны, значение функции f(t) можно получить из статистического справочника. Зная эти данные, можно приближенно подобрать a и b так, чтобы теоретические значения f(t), вычисленные по формуле (1), не сильно отличались от данных справочника (т.е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений - это модуль разности двух чисел: фактического и соответствующего теоретического значения f(t). Максимальное отклонение называют погрешностью. Необходимо найти такие a и b, чтобы погрешность была наименьшей.
Математическая модель процесса изменения численности населения такова. Предполагается, что:
зависимость численности населения от времени выражается формулой f(t)=aе bt
a=const и b=const следует считать справедливым лишь для не очень большого промежутка времени (например, 40 лет);
значения a и b можно найти с достаточной точностью, минимизировав погрешность.
Исходные данные: сведения из статистического справочника за период с 1960 по 1995 г. (60<=t<=95).
Результаты:
значения a и b;
численность населения России в 2000 году (при t=100) .
Кроме того, установлена связь между исходными данными и результатами: сначала надо найти a и b, минимизируя погрешность, а затем при этих a и b вычислить значения f(100).
Итак, математическая модель составлена. Использование электронной таблицы освобождает нас от составления программы. Нужно только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель. После этого можно начать процесс численного моделирования исследуемой ситуации, т.е. подбор коэффициентов a и b в формулу (1), а затем определение численности населения.
Решение задачи
ЗАДАНИЕ 1. Заполните таблицу :
Рис. 3.1
1.1 Сделайте заголовок и заполните шапку таблицы.
1.2 Столбцы а и в отведите под коэффициенты a и b соответственно.
1.3 В столбец с занесите значения t с 1960 г. (см. Рис. 3.1).
В столбец D занесите взятые из справочника значения численности населения России с 1960 г. (см. рис 3.1)
ЗАДАНИЕ 2. Подберите значения коэффициентов а и b.
Следующий шаг в решении задачи – это вычисление теоретической численности по формуле (1), в которой не известны значения коэффициентов а и b. Подбор а и b можно произвести в два этапа. Сначала определим их значения приближенно, для чего построим график роста статистической численности и аппроксимируем его. Затем уточним полученные коэффициенты а и в с использованием функции Excel Поиск решения.