|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Использование электронной таблицы для численного моделирования.
Цель работы: • научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.
Электронная таблица выполняет не только функцию автоматизации вычислений. Она является очень эффективным средством проведения численного моделирования ситуации или объекта, для математического описания которых (т.е. построения математической модели) используется ряд параметров. Часть этих параметров известна, а часть рассчитывается по формулам. Меняя во всевозможных сочетаниях значения исходных параметров, вы будете наблюдать за изменением расчетных параметров и анализировать получаемые результаты. Ехсеl производит такие расчеты быстро и без ошибок, предоставляя в считанные минуты множество вариантов решения поставленной задачи, на основании которых вы выберите наиболее приемлемое. Поиск решения и моделирование - одни из самых мощных инструментов Ехсе1.
Чему будет равна численность населения России в начале 21 века?
f (t) = a ∙ ebt (1)
где коэффициенты а, b для каждого государства свои; t - годы, е - основание натурального логарифма. Эта формула лишь приближенно отражает реальность. Однако слишком большая точность и не нужна. Будет хорошо, если численность населения будет спрогнозирована с точностью до нескольких миллионов. Как же определить а и b? Идея состоит в том, что хотя а и b не известны, значение функции f(t) можно получить из статистического справочника. Зная эти данные, можно приближенно подобрать а и b так, чтобы теоретические значения f(t), вычисленные по формуле (1), не сильно отличались от данных справочника (т.е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений - это модуль разности двух чисел: фактического и соответствующего теоретического значений f(t). Максимальное отклонение называют погрешностью. Необходимо найти такие коэффициенты а и b, чтобы погрешность была наименьшей. Итак, математическая модель процесса изменения численности населения такова. Предполагается, что: 1. зависимость численности населения от времени выражается формулой 2. a = const и b = const следует считать справедливым лишь для не очень большого промежутка времени (несколько десятков лет); 3. значения а и b можно найти с достаточной точностью, минимизировав погрешность Исходные данные: сведения из статистического справочника за период с 1960 по 1995 г. (60 < = t < = 95). Результаты: 1) значения а и b; 2) численность населения России в 2000 г. (при t=100). Кроме того, установлена связь между исходными данными и результатами: сначала надо найти a и b, минимизируя погрешность, а затем при этих а и b вычислить значения f(100). Итак, математическая модель составлена. Использование электронной таблицы освобождает нас от составления программы. Нужно только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель. После этого можно начать процесс численного моделирования исследуемой ситуации, т.е. подбор коэффициентов а и b в формуле (1), а затем определение численности населения.
Рис. 5.1. Заполнение таблицы. 1. Сделайте заголовок и заполните шапку таблицы. 2. Столбцы А и В отведите под коэффициенты а и b, соответственно. 3. В столбец С занесите значения t с 1960 г. 4. Выберите из справочной таблицы нужные значения (в млн. чел.)
5. Получим таблицу:
6. В столбец D занесите взятые из таблицы значения численности населения России с 1960 года (в млн.чел.)
Задание 2. Подберите значения коэффициентов а и b.
Следующий шаг в решении задачи - это вычисление теоретической численности по формуле (1), в которой не известны значения коэффициентов а и b. Подбор а и b можно произвести в два этапа.
Сначала определим их значения приближенно, для чего построим график роста статистической численности и аппроксимируем его. Затем уточним полученные коэффициенты а и b с использованием функции Ехсеl Поиск решения.
2.1.Постройте график типа Х – Y по данным таблицы (рис. 5.2).
Рис. 5.2. (Х – годы; Y – статистическая численность). 2.2.Перемасштабируйте оси Х и Y. · Для более наглядного представления данных необходимо перемасштабировать оси. · Выделите ось X. · Вызовите контекстно-зависимое меню и выполните команду: формат оси, шкала · Установите минимальное значение X и пересечение с осью Y. · Аналогично перемасштабируйте ось Y. 2.3.Аппроксимируйте полученную кривую. · Необходимо статистические данные по численности населения описать математической функцией (аппроксимировать). Кривая, описываемая математической функцией, называется линией тренда. Для построения линии тренда: · Выделите линию графика. · Выполните команду меню Диаграмма → Добавить линию тренда или аналогичную команду контекстно-зависимого меню. Откроется окно Линия тренда (рис. 5.3).
Рис. 5.3. Выберите экспоненциальный тип (см. формулу (1)).
Выберите в окне Линия тренда вкладку Параметры (рис. 5.4).
Рис. 5.4. Установите флажок R показывать уравнение на диаграмме, R поместить на диаграмму величину достоверности аппроксимации (R2) и нажмите кнопку ОК. Чем больше значение R2 (чем ближе к единице), тем точнее аппроксимация, т.е. выбранная функция лучше описывает конкретный процесс.
В результате на графике появится линия тренда, уравнение с подобранными коэффициентами а и b и коэффициент R2 (рис. 5.5).
Рис. 5.5.
2.4. Занесите полученные значения коэффициентов а = 80.455 и b = 0.0066 в ячейки А3 и В3 и присвойте им имена – А3 имя а; В3 имя b. (рис. 5.6):
Рис. 5.6.
Коэффициенты а и b не изменяются с течением времени, это константы, следовательно, при вычислении теоретической численности они должны быть адресованы абсолютно. Задание 3. Вычислите теоретическую численность по формуле (1). Задание 4. Вычислите отклонение. Отклонение – это модуль разности теоретических и фактических значений функции f(t). 4.1. В ячейку F3 занесите формулу =ABS(E3- D3) Задание 5. Вычислите погрешность. Погрешность - это максимальное отклонение. Задание 6. Подберите значения коэффициентов а и b более точно. При полученных в результате аппроксимации коэффициентах а и b погрешность уже неплохая (по условию она должна быть в пределах нескольких миллионов). Но коэффициенты а и b можно подобрать более тонко, используя функцию Excel Поиск решения. В отличие от Подбора параметра, Поиск решения может для достижения нужного результата изменять или подбирать подходящие значения во многих ячейках. 6.1. Выполните команду: Сервис - Поиск решения
Рис. 5.7. 6.2. Сделайте необходимые настройки в окне диалога Поиск решения В поле Установить целевую ячейку укажите адрес ячейки $F$13 (в ней погрешность).
Рис. 5.8.
Рис. 5.9. Поиск свелся к текущему решению. Все ограничения выполнены.
Произойдет изменение значений ячеек в соответствии с найденным решением. Обратите внимание, что коэффициенты а и b изменились, а погрешность уменьшилась. Задание 7. Определите численность населения России в 2000 г. Задание 8. Постройте на одной диаграмме совмещенные графики роста численности населения на основе статистических и теоретических данных. 8.2. Добавьте в уже построенную диаграмму теоретические данные.
Рис. 5.10. Задание 9. Оформите таблицу на свой вкус (обрамление, заполнение, шрифты). Задание 10. Воспользуйтесь предварительным просмотром печати. 10.1. Разместите диаграмму на одном листе с таблицей. Задание 11. Сохраните файл в личном каталоге под именем work 5. xls Задание 12. Проанализировав данные таблицы и графика, сделайте вывод об адекватности предложенной математической модели реальному процессу (т.е. вывод о правильности описания роста населения формулой (1)). Лабораторная работа №6 Популярное:
|
Последнее изменение этой страницы: 2016-05-29; Просмотров: 1392; Нарушение авторского права страницы
В этом поле задаются адреса ячеек, значения которые, будут варьироваться в процессе поиска решения. В нашем случае - это адреса ячеек со значениями a и b.
Начнется поиск решения. Так как у нас довольно точные коэффициенты а и b, то поиск займет немного времени. Когда Excel найдет решение, то откроется диалог, представленный на рисунке.
8.3. Оформите диаграмму в соответствии с рисунком, где показан примерный вид графиков.