Имитационное моделирование в среде ms excel

 

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

181

 

___________________________________________________________________________________________

Министерство образования Российской Федерации

Петрозаводский государственный университет

 

 

 

 

 

А. В. Коросов

 

 

 

Имитационное моделирование

в среде MS Excel

(на примерах  из экологии)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Петрозаводск

2002

 

 

УДК 28.08:22.172

К686

Рецензенты:

член.-корр. РАН, профессор, доктор биологических наук Э. В. Ивантер;

доцент, кандидат физико-математических наук В. Б. Ефлов;

доцент, кандидат физико-математических наук С. Р. Богданов

 

Печатается по решению редакционно-издательского совета

Петрозаводского государственного университета

 

           Коросов А. В.

К686   Имитационное моделирование в среде MS Excel (на примерах из экологии): Монография / ПетрГУ.  Петрозаводск, 2002. 212 с. ISBN 5-8021-0168-7

 

В книге изложены основы общедоступного метода построения арифметических имитационных моделей в среде Excel. Метод позволяет конструировать описательные и динамические модели любой сложности и оценивать их параметры.

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

Книга адресована широкому кругу читателей, использующих компьютер для расчетов, но в первую очередь научным сотрудникам (не только биологических специальностей) и студентам.

 

УДК 28.08:22.172

 

ISBN 5-8021-0168-7  © А. В. Коросов, 2002

© Петрозаводский государственный

    университет, 2002

 

Издание осуществлено при финансовой поддержке Института “Открытое общество. Фонд содействия” (Фонд Сороса – Россия)

Краткое содержание

 

 

	Предисловие	4
	Введение Освоение методики моделирования за 10 мин.	5
	Постановка проблемы Концепция имитационного моделирования в приложении к экологическим исследовательским задачам: ретроспективная оценка параметров.	13
1	Идеология моделирования Основы системного подхода к моделированию биоэкологических явлений: 7 принципов системного мышления, 4 рекурсивных этапа движения системной мысли, 5 этапов моделирования.	19
2	Процедура моделирования Практикум построения имитационной системы на листе Excel: техника табличного программирования, настройка имитационной модели, оптимизация функции различий,  оценка параметров, расчет статистических ошибок и адекватности модели.	37
3	Приемы моделирования Типология задач имитационного моделирования; опыт решения разнообразных задач: аппроксимация, декомпозиция, ...; структурированное описание модельных понятий.	105
4	Теория оптимизации Математические основы оптимизации: методы Ньютона, градиентов.	183
	Литература Список источников по моделированию.	199
	Основное содержание Дробная рубрикация книги.	208

 

 

 

"…Невозможно написать  учебник  по построению моделей".

Р. Л. Акоф, М. Сасиени1

Предисловие

Лет 15 тому назад автор сформулировал для себя направление поиска приемов моделирования в виде небольшого эссе "Модели — биологу". Уже тогда было понятно — чтобы войти в широкую биологическую практику, моделирование должно быть максимально простым по использованию (в программировании свои бесконечные проблемы), но абсолютно жестким по логическому оформлению (биологические высказывания должны выверяться числом). Работа в этом направлении (Ивантер, Коросов, 1992; Коросов, 1995; Коросов, 1996) позволила в конце концов наметить контуры простой процедуры построения сложных количественных описаний природных объектов. Когда идеи оформились в метод (Коросов, 1999), он был успешно апробирован при подготовке докторской диссертации по зоолого-экологической тематике (Коросов, 2000). Его следует назвать табличное программирование арифметических имитационных моделей в среде Excel. Об этом методе и пойдет речь дальше.

Трудно точно определить жанр предлагаемой книги. Это и монография, поскольку содержит главы, посвященные методологии общедоступного моделирования, описание новых моделей,  оригинальные результаты исследований. Это и учебное пособие, предназначенное для пользователей ЭВМ, желающих освоить новый количественный метод (автор читает учебный курс "Компьютерные методы в экологии"). Это и вариант научно-популярной литературы, вводящей читателя в круг проблем количественной биологии. Книга может быть интересна как научным сотрудникам (биоэкологам), так и студентам (разных специальностей), а также школьникам.

Автор будет признателен всем читателям, которые пришлют свои комментарии и положительные отзывы по адресу: 185640, Республика Карелия, г. Петрозаводск, пр. Ленина, 33. Петрозаводский госуниверситет. Кафедра зоологии и экологии.

E-mail:  korosov@karelia.ru

 

____________________________________________________________

1Акоф Р. Л., Сасиени М. Основы исследования операций. М.: Мир, 1971, С. 84. 

"Честная неудача  не позорна; позорен страх перед  неудачей".

Г. Форд1

Введение

Эта книга написана для читателя, использующего компьютер. Действия по образцу очень быстро привьют навык применения предлагаемого метода имитационного моделирования. Сразу же усвоить общий порядок операций поможет решение небольшой проблемы, например такой: вычислить номер гадюки, которая укусит автора в пятый раз. Идеология метода формировалась в то время, когда автор (по специальности зооэколог) изучал островную популяцию обыкновенной гадюки. Методика мечения требовала отлова множества этих животных, которые иногда кусались. С приобретением опыта возрастала и осторожность, поэтому дистанция между укусами (и временная, и по номерам особей) постоянно увеличивалась. Накопившаяся статистика позволяет заглянуть в будущее и заранее узнать, с каким же следующим номером надо будет обращаться особенно осторожно? Для этого построим простую модель зависимости двух показателей и попробуем сделать предсказание.

Включаем компьютер и запускаем программу MS Excel.

Сначала введем данные на лист Excel (рис. 1).

 

 

Следующий этап – математическое описание модели. Если построить диаграмму, то видно, что искомая зависимость, скорее всего, _____________________________________________________________ 1 Г. Форд. Моя жизнь, мои достижения. М., 1989. С. 24.

криволинейна, и может быть описана, например, степенной функцией:

 У= a×Хb.

Применив наши обозначения, получим предполагаемую модель:

N' = a×Уb,

где

N' – расчетный номер особи,

У – номер укуса по порядку.

Условные приблизительные значения параметров модели (коэффициенты a и b) необходимо задать сразу же, введем их в ячейки листа, С1 = 1, С2 = 1 (рис. 2).

Теперь можно создавать модель на листе Excel – вводить модельные формулы. Правила табличного программирования требуют, чтобы значения модели для каждого объекта выборки (или одного временного шага модели) вычислялись явным образом. Введем в ячейки С3:С9 формулы модели, начиная с С4:

С4  = $С$1*A4^$C$2,

где

  $С$1 - абсолютный адрес ячейки со значением параметра a,

$C$2 - абсолютный адрес ячейки со значением параметра b,

A4 - относительный адрес ячейки со значением величины У.

* ^ - знаки арифметических операций, умножения и возведения в степень, соответствующие конструкции модели  (рис. 3).

После нажатия клавиши Enter ячейка будет содержать рассчитанное (заведомо неточное) значение номера гадюки при текущих уровнях параметров a и b.

          

 

Теперь нужно рассчитать остальные модельные значения, используя процедуру «автозаполнение». Для этого выделяем ячейку С4, наводим курсор мыши на ее правый нижний угол: он из белого креста  превращается в черный крестик (рис. 4.).

 

  

 

Нажав левую кнопку мыши, тащим курсор до нижнего угла ячейки С7, отпускаем. Все ячейки заполнились стереотипными фор-мулами, которые рассчитали модельные значения (рис. 5.).

 

       

 

С помощью двойного щелчка левой кнопкой мыши (еще лучше нажать функциональную клавишу «F2») формула в любой ячейке становится доступной для редактирования. Таким образом можно убедиться, что в каждой из ячеек содержится формула, имеющая правильные абсолютные и относительные ссылки на ячейки со значениями параметров (a и b) и ведущей переменной (У) (рис. 6).

 

       

 

Формула каждой ячейки рассчитывает значение номера кусающейся особи для «своего» номера укуса (из колонки A).

Итак, модельные значения (N') подсчитаны, но они явно плохо согласуются с реальными значениями (N). Например, вторичный укус сделан 99-й отловленной гадюкой, а модель дала N' = 2. Понятно, что принятые нами значения параметров плохо характеризуют соотношение между изучаемыми признаками. Необходимо как-то их улучшить. Для этого в первую очередь рассчитаем суммарное отличие модели от реальности. Эту роль может выполнить квадрат разности между каждой парой значений модель – реальность:

ф = (Мод.–Реал.)2  =  (Ni'–Ni)2.

На листе Excel эта формула примет такой вид, например, для строки 4:

D4 =(C4– B4)^2  = (1–14)2 = -132 = 169.

Используя процедуру «автозаполнение», нетрудно рассчитать такие же значения для всех пар переменных N и N’ (для проверки стоит щелкнуть в D5 и нажать «F2»; рис. 7).

Обозначим полученную графу, например, через «ф». В ячейку D8 введем формулу подсчета суммы всех квадратов различий между реальной и модельной переменными (рис. 8):

Ф = Sф или D8 =СУММ(D4:D7) = 4233603. 

 

 

 

  

 

Значение 4233603, вычисленное в ячейке D8, характеризует обобщенное отличие расчетных модельных значений признака от исходных данных. Столь большая величина определяется, видимо, тем, что произвольно назначенные коэффициенты для модельных уравнений плохо соответствуют специфике реальных зависимостей.

 

 

Можно предположить, что при определенных значениях коэффициентов модель будет точнее описывать реальность и функция отличий (значение в ячейке D8) снизится. Это соображение позволяет начать поиск лучших значений параметров модели, изменяя их и отслеживая снижение функции различий Ф.

Например, можно было бы многократно вводить в ячейки С1 и С2 различные значения параметров, уменьшающих Ф. Так, при а = 100 уровень функции отличий становится немного ниже предыдущего (2456157 против 4233603) (рис. 9).

 

 

К счастью, в среде пакета Excel ручная подгонка не нужна, поскольку там имеется встроенная программа (макрос), выполняющая процедуру поиска лучших параметров, процедуру оптимизации. Вызовем ее командой «Поиск решения» из меню «Сервис» (рис. 10).

 

 

 

Если в меню «Сервис» команды «Поиск решения» обнаружить не удалось, то, скорее всего, эта процедура просто не подключена, либо не установлена. Для подключения макроса «Поиск решения» нужно вызвать окно «Надстройки» меню «Сервис», где поставить галочку напротив заголовка «Поиск решения». После этого соответствующая команда появится в меню «Сервис». Если заголовка «Поиск решения» в окне «Надстройки» нет, нужно воспользоваться установочным диском MS Office, выбрать «Добавить/Удалить / Microsoft Excel / Надстройки»,  поставить галочку в окно «Поиск решения» и после установки подключить процедуру.

Окно макроса, которое появляется по команде «Поиск решения», нужно правильно заполнить (например, с помощью мыши) (рис. 10):

• Установить целевую ячейку: $D$8
• Равной значению: 0
• Изменяя ячейки: $С$1:$С$2 (содержащие значения параметров).