Использование электронной таблицы для численного моделирования

Лабораторная модель №4. Задача определения численности населения РФ в начале третьего тысячелетия

Цели работы:

1)уметь использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели;

2) иметь представление о статистических методах в прогностических моделях;

2) применять графический метод представления решения модели.

Основные понятия

Электронная таблица является эффективным средством проведения численного моделирования ситуации или объекта, для математического описания которых используется ряд параметров. Часть этих параметров известна, а часть рассчитывается по формулам. Во многих случаях известные параметры модели задаются на основании наблюдений. Статистические данные можно представить на графике плавной кривой (аппроксимировать). Эта кривая называется линией тренда. Изменяя исходные данные, наблюдая за изменением расчетных параметров и анализируя получаемые результаты, можно прогнозировать их изменение во времени.

Содержание работы. Рассмотрим задачу определения численности населения России в начале третьего тысячелетия.

Очевидно, что задачу невозможно решить, если не знать, как со временем будет меняться численность населения России, т.е. необходимо иметь функцию, выражающую зависимость численности населения от времени. Обозначим эту функцию f(t), которая неизвестна, так как народонаселение зависит от многих факторов: экологии, состояния медицинского обслуживания, морали, права, социального, экономического и политического уровня жизни. Но, обобщив демографические данные, можно указать общий вид функции f(t):

f(t)=a·e^b·t_, (1)

где коэффициенты a, b для каждого государства свои; е – основание натурального логарифма.

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

Определение коэффициентов а и b. Хотя а и b неизвестны, значение функции f(t) можно получить из статистического справочника (по переписи населения). Зная эти данные, можно приближенно подобрать а и b так, чтобы теоретические значения f(t), вычисленные по формуле (1) не сильно отличались от данных справочника (т.е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений – это модуль разности двух чисел: фактического и соответствующего теоретического значений. Максимальное отклонение называют погрешностью. Необходимо найти такие а и b, чтобы погрешность была наименьшей.

Итак, математическая модель процесса изменения численности населения может быть следующей. Предполагается, что:

1) зависимость численности населения от времени выражается формулой (1);

2) a=const и b=const следует считать справедливым лишь для не очень большого промежутка времени (например, 40 лет);

3) значения а и b можно найти с достаточной точностью, минимизировав погрешность.

Исходные данные: сведения из статистического справочника за период с 1960 по 2003 г. (60< =t< =103).

Результат:

1) значения а и b;

2) численность населения России в 2010 г. (при t=100).

Кроме того, установлена связь между исходными данными и результатами: сначала необходимо определить а и b, минимизируя погрешность, а затем при этих а и b вычислить значение функции (110).

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

Задание 1. Введите исходные данные (табл.3.14).

1) сделайте заголовок и заполните шапку таблицы;

2) столбцы А и В отведите под коэффициенты а и b соответственно;

3) в столбец С занесите значения t с 1960 г;

4) в столбец D занесите взятые из статистического справочника значения численности населения России с 1960 г.

Таблица 3.14

Задание 2. Подберите значения коэффициентов а и b.

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

1) постройте диаграмму (Х–годы; Y–статистическая численность);

2) перемасштабируйте оси X и Y.

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

– выделите ось X;

– вызовите контекстно-зависимое меню и выполните команду: Формат оси, вкладка Шкала;

– установите минимальное значение X, основную единицу измерений и пересечений с осью Y. Аналогично перемасштабируйте ось Y.

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

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

Для построения линии тренда:

– выделите линию графика;

–выполните команду контекстно-зависимого меню Добавить линию тренда.

В диалоговом окне «Линия Тренда»:

– выберите экспоненциальный тип (см. формулу (1), рис.3.3);

– выберите в диалоге Линия Тренда вкладку Параметры;

– установите флажок [X] Показывать уравнение на диаграмме и нажмите кнопку Ок .

– в поле Установить целевую ячейку укажите адрес ячейки, например $F$14 (в ней значение погрешности);

–установите переключатель на минимизацию погрешности (минимальному значению);

–в поле Изменяя ячейки укажите оптимизируемые значения – $А$3: $В$3.

Задание 7.

1) выполните работу для полиномиальной функции определения численности населения РФ;

2) проведите анализ решений для экспоненциальной и полиминоминальной функций определения численности населения;

3) обоснуйте выбор решения.

САМОСТОЯТЕЛЬНАЯ РАБОТА. Определите численность населения республики Бурятия к 2010 г.