EcoSimulation — 03. Экспоненциальный рост

 

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

Основные понятия Экспоненциальный рост Логистический рост
Имитационное моделирование биосистем-02  Имитационное моделирование биосистем-03 Имитационное моделирование биосистем-04

 

 

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

Рис. 3.1. Динамика экспоненциального роста (Шабанов, Кравченко, 2009)

В показанной на рисунке модели N — численность популяции, t — время, r — репродуктивный потенциал (мальтузианский параметр), dN/dt — изменение (прирост) численности популяции. В целом эта модель отражает простейший факт: возможный прирост численности популяции пропорционален ее численности. Исследование экспоненциального роста начато еще Леонардо Фибоначчи (1170–~1250) и Томасом Мальтусом (1766–1834). На рисунке представлена аналитическая модель. В ней рост описывается с помощью однозначной функции.

Создадим в MS Excel имитационную модель, описывающую экспоненциальный рост. Дальнейшие примеры приводятся для работы с русскоязычной версией Excel из Microsoft Office 10; использование иных версий Excel может иметь свои особенности.

Лист MS Excel — это таблица, состоящая из столбцов и строк. В наиболее распространенном режиме адресации столбцы обозначаются латинскими буквами (или их сочетаниями), а строки — номерами. Существует и иной стиль адресации (т.н. R1C1); переключение между этими стилями происходит в меню "Параметры Excel" (его можно открыть из вкладки "Файл") в разделе "Формулы".

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

Создадим файл Excel. Прежде всего, зададим ячейки, в которые мы будем вводить основные параметры (табл. 3.1).

Таблица 3.1. Начало построения модели, описывающей экспоненциальный рост

  A B C D
1 N0= 1000    
2 r= 0,2    
3        
4 0 1000 {=B1}    
5 1      
6 2      
7 3      

 

Ячейки A1 и A2, в которых указаны обозначения начальных величин, не используются при вычислениях. Они нужны не для самой модели, а для пользователя, чтобы он понимал, где что находится. В соответствующих ячейках столбца B введем значения этих параметров.

В столбце A (отступив некоторое место), введем шкалу времени. Для этого достаточно ввести цифры 0 и 1, выделить их курсором, поместить курсор в правый нижний угол образовавшегося блока и потянуть. Excel заполнит требуемую область продолжением заданной арифметической прогрессии. Если аналогичным образом "растянуть" одну ячейку, в которую введена определенная цифра, те же цифры заполнят весь блок. Если "растянуть" ячейку, в которую введена формула, эта формула может остаться неизменной, а может и закономерно изменяться — в зависимости от того, как она задана (см. далее).

В столбце B разместим имитацию экспоненциального роста. В ячейке, соответствующей началу имитации, введем формулу =B1. Если содержимое ячейки начинается со знака =, Excel рассматривает его, как формулу. В этом конспекте формулы будем показывать в фигурных скобках на желтом фоне, после указания соответствующей величины, которую Excel показывает в данной ячейке. Так, запись "1000 {=B1}" означает, что Excel показывает в этой ячейке величину 1000, и это задается формулой =B1. Когда эта ячейка выделена, а курсор стоит на строке формул, расположенной над таблицей, Excel подсвечивает те ячейки, к которым адресуется формула (рис. 3.2).

Рис. 3.2. В таблице выделена ячейка с формулой; курсор стоит на строке формул; ячейки, на которые ссылается формула, показаны цветной рамочкой

Один из способов ввести формулу в ячейку B4 таков: ввести в нее (выделив ее курсором) знак =, затем перейти в строку формул (щелкнув по ней), и, разместив курсор после знака =, выделить целевую ячейку (B1). После этого важно не щелкать курсором по разным местам таблицы (чтобы Excel не редактировал как попало формулу), а выйти их режима редактирования, нажав Enter.

Теперь определим, какой будет численность популяции после единичного промежутка времени. Для этого введем в нее формулу, соответствующую выражению N1=N0+r*N0 (табл. 3.2).

Таблица 3.2. Заполнение ячеек модели

  A B C D
1 N0= 1000    
2 r= 0,2    
3        
4 0 1000 {=B1}    
5 1 1200 {=B4+B4*B2}    
6 2 1440    
7 3 1728    

 

В ячейке B6 в соответствии с этой логикой должна находиться формула {=B5+B5*B3}. Эти формулы можно ввести в ячейку B6 и все последующие вручную, но это слишком трудоемко. Очевидно, что и все последующие выражения в столбце B аналогичны тому, что введено в ячейку B5. Можно ли "растянуть" его на весь столбец?

Выделите в Excel ячейку B5 и растяните ее на ячейку B6. В ячейке B6 окажется формула {=B5+B5*B3}. При "растягивании" Excel сдвинул все адреса, упомянутые в формуле. Относительно ссылки на предыдущую ячейку (B5 для ячейки B6) это совершенно правильно, а при ссылке на значение параметра r — неверно.

Дело в том, что для ссылок в формуле мы использовали относительную адресацию. Фактически, когда в ячейке B5 стоит формула {=B4+B4*B2} она означает {=на_ячейку_выше+на_ячейку_выше*на_три_ячейки_выше}. Чтобы указать, что нужна именно определенная ячейка, ссылка на которую не зависит от того, в каком месте листа находится формула, следует использовать абсолютную адресацию. В той системе адресации, которую мы рассматриваем в этом примере, она обозначается символом доллара: {=B4+B4*$B$2}. Кстати, в нашем случае приведенной записи аналогичен вариант {=B4+B4*B$2}. Однако во втором из этих случаев, если "растянуть" такую формулу в другой столбец, ссылка будет указывать на вторую строку этого, нового столбца. Очевидно, что ссылку на предыдущую ячейку в ряду вычислений численности популяции следует оставить относительной.

При использовании стиля ссылок R1C1 та же формула будет выглядеть так: {=R[-1]C+R[-1]C*R2C}; относительные ссылки взяты в квадратные скобки, абсолютные — нет. R[-1]C означает в таком случае отсылку к ячейке на одну строку выше в том же столбце, где находится формула, а R2C — ссылку на ячейку во второй строке того же столбца, в котором находится формула.

Впрочем, в нашем примере мы пользуемся традиционными ссылками. В таком случае, "растянув" формулу из ячейки B5 на весь столбец (вероятно, более длинный, чем на показанном в табл. 3.3 примере), мы получим требуемый ряд значений.

Таблица 3.3. Результат "растягивания" формулы с абсолютной адресацией по строкам

  A B C D
1 N0= 1000    
2 r= 0,2    
3        
4 0 1000 {=B1}    
5 1 1200 {=B4+B4*B$2}    
6 2 1440 {=B5+B5*B$2}    
7 3 1728 {=B6+B6*B$2}    

 

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

Поскольку имитируемый нами процесс достаточно прост, диаграмма на весь экран нам не нужна. Более того, если мы будем изменять начальные значения, чтобы увидеть, как они повлияли на динамику, нам придется переходить с листа с вычислениями на лист с диаграммой туда-назад. Щелкните правой клавишей мыши на закладке внизу экрана Excel с только что созданным графиком (вероятно, он называется "Диаграмма 1"), и выберите опцию "Удалить".

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

Попробуйте изменить какое-то из начальных значений (N или r): изменения тут же отразятся на графике (если в указано, что вычисления проводятся автоматически, т.е. в окошке "автоматически", к которому можно добраться по пути "Файл"/"Параметры Excel"/"Формулы"/"Параметры вычислений"/"Вычисления в книге"/"Вычисления в книге" стоит галочка; если выбран вариант "вручную", чтобы пересчитать данные при изменении входных значений переменных, надо нажать F9).

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

В конечном итоге, модель может выглядеть так, как показано на рис. 3.3.

Рис. 3.3. Внешний вид готовой модели

Обратите внимание, что в этой модели видны четыре важнейших функциональных блока:
1. информационное поле с необходимыми пояснениями;
2. входные параметры;
3. система преобразований;
4. визуализация выходных параметров (график).

При доведении модели до "товарного" вида были использованы следующие возможности:

  • перетаскивание блоков (описано выше);
  • изменение положения и размера графика (надо схватить мышкой за рамочку и потянуть на нужное место);
  • расчерчивание границ между ячейками линиями (нужно выделить ячейки, а затем выбрать необходимые линии в меню "Главная"/"Шрифт");
  • подстрочный знак при обозначении N0 (выделить 0 в строке формул, и задать для него нужный стиль в диалоге "Главное"/"Шрифт");
  • изменение границ числового ряда, который отображается на графике (когда график выделен, рамочка показывает отображаемую область; ее можно "взять" за уголок и растянуть);
  • изменение "легенды" — подписи к линии на графике (надо щелкнуть на подписях к шкале на графике правой клавишей, выбрать "Выбор источника данных", нажать "Изменить" над окошком "Элементы легенды (ряды)" и ввести нужное значение);
  • измерение ряда чисел на оси абсцисс (в "Выборе источника данных" в окне "Подписи горизонтальной оси (категории)"задать столбец с отметками времени);
  • объединение ячеек (чтобы разместить название модели): надо выделить объединяемые ячейки и применить к ним команду "Объединить и поместить в центре" (меню "Главное", группа "Выравнивание");
  • настройка переноса текста (названия модели): выделить требуемую ячейку, щелкнуть правой клавишей мыши, выбрать "Формат ячеек" (или выбрать в меню "Главное" и группе "Ячейки" команду "Формат", а там "Формат ячеек"), перейти на вкладку "Выравнивание", в группу "Отображение" и поставить там галочку напротив опции "переносить по словам".

Получившуюся модель можно скачать на сайте, но лучше сделать ее самостоятельно.