Использование ЭТ Excel для математического моделирования
ЦЕЛЬ: научиться использовать ЭТ для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.
ЭТ выполняет не только функцию автоматизации вычислений. Она является очень эффективным средством проведения численного моделирования ситуации или объекта, для математического описания которых (т. е. построения математической модели) используется ряд параметров. Часть этих параметров известна, а часть рассчитывается по формулам. Меняя во всевозможных сочетаниях значения исходных параметров, можно наблюдать за изменением расчетных параметров и анализировать получаемые результаты. Excel производит такие расчеты быстро и без ошибок, предоставляя в считанные минуты множество вариантов решения поставленной задачи, на основании которых можно выбрать наиболее приемлемое. Поиск решения и моделирование – одни из самых мощных инструментов Excel.
Задача 1. Чему будет равна численность населения России в начале третьего тысячелетия?
Ясно, что задачу не решить, если не знать, как со временем будет меняться численность населения России, т. е. необходимо иметь функцию, выражающую зависимость численности населения от времени. Обозначим эту функцию f(t). Но такая функция неизвестна, т. к. народонаселение зависит от многих факторов: экологии, состояния медицинского обслуживания, морали, права и даже от политической обстановки. Но, обобщив демографические данные, можно указать общий вид функции f(t):
f(t)=aekt, (1)
где a, k –коэффициенты для каждого государства свои.
Эта формула лишь приближенно отражает реальность. Однако слишком большая точность и не нужна. Будет хорошо, если численность населения будет спрогнозирована с точностью до нескольких миллионов.
Как определить a и k? Идея состоит в том, что хотя a и k – неизвестны, значение функции f(t) можно получить из статистического справочника. Зная эти данные, можно приближенно подобрать a и k таким образом, чтобы теоретические значения f(t), вычисленные по формуле (1), не сильно отличались от данных справочника (т. е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений – это модуль разности двух чисел: фактического и соответствующего теоретического значений f(t). Максимальное отклонение называют погрешностью. Необходимо найти такие a и k, чтобы погрешность была наименьшей.
Итак, математическая модель процесса изменения численности населения такова. Предполагается, что
1) зависимость численности населения от времени выражается формулой: f(t)=aekt.
2) A=const и k=const следует считать справедливым лишь для не очень большого промежутка времени (например, 40 лет).
3) Значения a и k можно найти с достаточной точностью, минимизировав погрешность.
Исходные данные: сведения из статистического справочника за период с 1960 по 1995г. (60<=t<=95).
Результаты: 1) Значения a и k.
2) численность населения России в 2000г. (при t=100).
Кроме того, будет установлена связь между исходными данными и результатами: сначала будут найдены a и k, из условия минимума погрешности, а затем при этих a и k вычислено значение f(100). Итак, математическая модель составлена. Использование ЭТ освобождает от составления программы. Надо только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель. После этого надо начать процесс численного моделирования исследуемой ситуации, т. е. подбор коэффициентов a и k в формуле (1), а затем определение численности населения.
Численность населения России в начале третьего тысячелетия
Ход работы.
1. Заполнить таблицу:
2. Подобрать значения коэффициентов a, k:
· Построить график по данным таблицы, шкалу по оси значений (y) задать от 100 до 160
· Аппроксимируйте полученную кривую:
à Выделить линию графика;
à Выполнить команду <Вставка-Линия тренда> или аналогичную команду контекстно-зависимого меню;
à Установить флажок <Показывать уравнение на диаграмме> (щелчок по линии тренда, формат линии, параметры, выбрать тип графика степенной или экспоненциальный) и ОК.
· Занести полученные значения коэффициентов a=120,86 и k=0,1091 в ячейки А5 и В5
3. Вычислить теоретическую численность по формуле: f(t)=a*ekt, где e - основание натурального логарифма:
· В ячейку E3 занести формулу =$A$5*EXP($B$5*C5) и скопируйте ее в ячейки E6:E13.
4. Вычислить погрешность. Погрешность - это модуль разности теоретических и фактических значений функции f(t).
· В ячейку F3 занесите формулу =ABS(E5-D5) и скопируйте ее в ячейки F6:F13.
5. Вычислить максимальную погрешность.
· В ячейку F15 введите функцию определения максимального из чисел этого столбца.
6. Подберите значения коэффициентов a, k более точно.
· Выполнить команду <Сервис-Поиск решения> (если этого пункта нет, загрузить его через команду <Сервис-Дополнение>).
· Сделать необходимые настройки в окне <Поиск решения>.
à В поле <Установить целевую ячейку> укажите абсолютный адрес ячейки F15 (в ней максимальная погрешность).
à Установить переключатель на минимальное значение
à В поле <Изменяя ячейки> укажите абсолютный адрес диапазона A5:B5
(В этом поле задаются адреса ячеек, значения которых будут варьироваться в процессе поиска решения, т. е. адреса ячеек со значениями a, k)
à Нажмите кн. <Выполнить>.
à После появления окна <Результаты поиска решения> нажмите ОК.
7. Определить численность населения России в 2000г.
· Поставьте в ячейке С14 число 40, что соответствует 2000г.
· В Е14 скопируйте формулу из Е13. В ячейке Е14 появится искомое число.
8. Постройте на одной диаграмме совмещенные графики роста численности населения на основе статистических и теоретических данных:
· Выделите на построенном графике линию тренда и удалите ее. (<Очистить> из контекстно-зависимого меню)
· Добавьте в уже построенную диаграмму теоретические данные:
à Выделить блок Е4:Е14.
à Переместите (с помощью мыши) данные на диаграмму.
à В появившемся окне сделайте настройку (если это необходимо)
à Оформите диаграмму: название, подписи осей, легенду.
9. Оформите таблицу на свой вкус.
10. Проанализировав данные таблицы и графики, сделайте вывод об адекватности предложенной математической модели реальному процессу, т. е. вывод о правильности описания роста населения формулой: f(t)=a*exp(k*t).
Население России Результат. xls
