EcoSimulation — 09. Первая модель А.В.Коросова: укусы гадюк и надстройка "Поиск решения"

 

Д.А. Шабанов
Конспект курса
"
Имитационное моделирование сложных биосистем
(с использованием Microsoft Excel)
"

Моделирование конкуренции на уровне отдельных особей и отдельных ресурсных единиц Первая модель А.В.Коросова: укусы гадюк и надстройка "Поиск решения" Вторая модель А.В.Коросова: демографические параметры популяции гадюк
Имитационное моделирование биосистем-08  Имитационное моделирование биосистем-09 Имитационное моделирование биосистем-10

 

Следующий этап работы начнем с детального разбора моделей, которые построим, следуя инструкциям в работах эколога и герпетолога из Петрозаводского университета Андрея Викторовича Коросова.

Начнем с примера, подробно изложенного во введении к следующей книге:
Коросов А. В. Имитационное моделирование в среде MS Excel (на примерах из экологии) / ПетрГУ.  Петрозаводск, 2002. 212 с.

Необходимый нам фрагмент текста будет приведен здесь дословно. Кроме прочего, он позволит напомнить некоторые операции по работе с файлами Excel.

Расчет "рокового укуса"

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

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

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

Рис. 9.1. Номера особей гадюки, кусавших автора

Следующий этап — математическое описание модели. Если построить диаграмму, то видно, что искомая зависимость, скорее всего, криволинейна, и может быть описана, например, степенной функцией: У= a*Хb.

Применив наши обозначения, получим предполагаемую модель: N' = a*Уb, где N' — расчетный номер особи, У – номер укуса по порядку.

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

Рис. 9.2. Ввод первичных значений параметров

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

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

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

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

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

 

Рис. 9.3. Ввод первой модельной формулы

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

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

Рис. 9.4. Начало операции автозаполнения: курсор поменял форму

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

Рис. 9.5. Завершение операции автозаполнения

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

Рис. 9.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»; рис. 9.7). 

Обозначим полученную графу, например, через «ф». В ячейку D8 введем формулу подсчета суммы всех квадратов различий между реальной и модельной переменными (рис. 9.8): Ф = Σф или D8 =СУММ(D4:D7) = 4233603

Рис. 9.7. Расчет отличия

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

Рис. 9.8. Расчет обобщенной функции отличий (Ф)

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

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

Рис. 9.9. Ручная подгонка значений параметров

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

Рис. 9.10. Настройка окна макроса «Поиск решения» 

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

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

— Установить целевую ячейку: $D$8

Равной значению: 0

Изменяя ячейки: $С$1:$С$2 (содержащие значения параметров).

После щелчка по кнопке «Выполнить» появится окно итоговых сообщений (рис. 9.11), предупреждающее, что поиск не может найти решения. Это естественно, ведь задача для модуля состояла в том, чтобы свести функцию отличий к нулю, а макрос смог ее уменьшить лишь до уровня Ф = 117686 (ячейка D8).

С "точки зрения" макроса (подробнее см. раздел Параметры макроса…) найденное решение неудовлетворительно, т. е. полученные значения коэффициентов неточны. Но с точки зрения эколога такое решение может быть вполне приемлемым: значения в столбцах N и N' стали довольно близкими, ну а статистическая значимость их отличий может (и должна) быть исследована статистическими методами. Поэтому есть все основания сохранить найденное решение и рассмотреть полученное уравнение: N' = 63.2 * У2.41.

Это и есть искомая модель. Она, конечно, крайне примитивна и вполне могла бы быть рассчитана другим способом – с помощью регрессионного анализа из блока «Анализ данных» меню «Сервис». 

Тем не менее полученный результат принципиально важен для понимания существа моделирования. Теперь тот пользователь, что выполнил предложенный пример, ужé освоил процедуру построения имитационной модели.

Рис. 11. Окно выполнения макроса «Поиск решения»

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

Кроме того, гибкие средства электронной таблицы Excel содержат в себе возможности достраивать имитационную систему и создавать структуры для оценки статистических характеристик параметров <...>.

Что же касается прогноза, то предоставим читателю самостоятельно определить номер роковой змеи, подсказав направление поиска. Нужно добавить строку 8, ввести А8 = 5 и ... "