ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ EXCEL ДЛЯ ЧИСЛЕННОГО МОДЕЛИРОВАНИЯ

Электронная таблица выполняет не только функцию автоматизации расчетов. Она является очень эффективным средством численного моделирования ситуации или объекта, для математического описания которых, т.е. построения математической модели, используется ряд параметров. Часть этих параметров известна, а часть рассчитывается по формулам. Меняя в различных сочетаниях значения исходных параметров, можно наблюдать за изменением расчетных параметров и анализировать получаемые результаты. Электронная таблица производит такие расчеты быстро и без ошибок, предоставляя в считанные минуты множество вариантов решения поставленной задачи, на основании которых вы выберете наиболее приемлемый.
Попробуем решить следующую задачу: Какова будет численность населения города “А” в начале третьего тысячелетия?
Сразу ясно, что задачу не решить, если не знать, как со временем будет меняться численность населения города “А”, т. е. необходимо иметь функцию, выражающую зависимость численности населения от времени. Обозначим эту функцию f(t). Но такая функция неизвестна, так как народонаселение зависит от многих факторов: экологии, состояния медицинского обслуживания, морали, права и даже от политической обстановки. Но, обобщив демографические данные, можно указать общий вид функции f(t): (1), где коэффициенты а, b - свои для каждого города и государства, е - основание натурального логарифма. Эта формула лишь приближенно отражает реальность. Однако слишком большая точность и не нужна. Хорошо, если численность населения будет спрогнозирована с точностью до десятков тысяч.
Как же определить а и b? Идея состоит в следующем: хотя а и b неизвестны, значение функции f(t) можно получить из статистического справочника. Зная t и f(t), можно приближенно подобрать а и b так, чтобы теоретические значения, вычисленные по формуле (1), не сильно отличались от данных справочника (т. е. максимальное отклонение теоретических результатов от фактических данных не должно быть слишком большим). Каждое из отклонений - это модуль разности двух чисел: фактического и соответствующего теоретического значений f(t). Максимальное отклонение называют погрешностью. Необходимо найти такие а и b, чтобы погрешность была наименьшей. Итак, математическая модель процесса изменения численности населения такова.
Предполагается, что:
1) Зависимость численности населения от времени выражается формулой .
2) а=const и b=const следует считать справедливым лишь для не очень большого промежутка времени (например, 40 лет).
3) Значения а и b можно найти с достаточной точностью, минимизировав погрешность.
Исходные данные: Будем считать, что у нас имеются сведения из статистического справочника о численности населения (в тыс. человек) за период с 1960 по 1996 г. (60 <= 1 <= 96). Результаты:
1) значения а и b,
2) численность населения города “А” в 2000 г. (t = 100). Кроме того, установлена связь между исходными данными и результатами: сначала надо найти а и b, минимизируя погрешность, а затем при этом а и b вычислить значение f(100).
Итак, математическая модель составлена. Использование электронной таблицы освобождает от составления программы. Нужно только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель. После этого можно начать процесс численного моделирования исследуемой ситуации, т. е. подбор коэффициентов а и b в формуле (1), а затем определение численности населения. Заполним таблицу . Сделаем заголовок. Заполним шапку таблицы. Столбцы А и В отведем под коэффициенты а и b соответственно. Коэффициенты а и b не изменяются с течением времени, значит, во всех ячейках столбцов А и В должны быть записаны одни и те же числа. В столбец С занесем значения t с 1960 г. В столбец D занесем взятые из справочника значения численности населения города “А” (с 1960 г.) в тыс.чел. В столбец Е занесем формулу =А?ЕХР(ВС). В столбец F занесем формулу =ABS(E-D). Это модуль разности теоретического и фактического значений функции f.
Для подсчета максимальной погрешности по столбцу G в любую свободную ячейку этого столбца введем функцию max. Всякий раз после пересчета чисел в столбце F в этой клетке будет записано максимальное из чисел этого столбца. Следующим шагом будет подбор коэффициентов а и b. Поместим в столбцы А и В какие-нибудь числа. При этом заполнятся столбцы Е и F, причем погрешность, появившаяся в ячейках столбца F, скорее всего очень велика. Изменим коэффициенты а и b так, чтобы погрешность уменьшилась. Для этого лучше всего составить алгоритм поиска а и b (примерный). В составлении этого алгоритма нам поможет анализ формулы (1). Сделав погрешность малой (на Ваш взгляд), подставьте в свободную ячейку столбца С число 100. В соответствующей ячейке столбца Е появится искомое число.
Теперь построим на одной диаграмме графики роста численности населения: на основе статистических и теоретических данных (с подобранными вами коэффициентами а и b.