А. Г. Козленко

Информационная культура и/или компьютер на уроке биологии

Продолжение. См. № 17, 18/2008

Учебный план курса

Лекция 3. Математика в биологии: культура числовых заданий

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

Задача 3.1. Фрагмент ДНК имеет длину 8,5 нм:
а) сколько нуклеотидов содержится в этом фрагменте;
б) какова его молекулярная масса?

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

Задача 3.2. Рассказывают, что в ХІХ в. один немецкий студент, влюбившись в девушку, решил преподнести ей очень дорогой подарок – железное кольцо. Железо для этого кольца юноша хотел химическим путем получить из собственной крови. Рассчитайте, основываясь на приведенных в статье данных, какой объем крови понадобился бы для изготовления кольца весом в 2 г, если считать, что юноше удавалось извлечь 60% содержащегося в гемоглобине железа.

Для решения такой задачи, точнее, для того, чтобы превратить красивую легенду в задачу, необходимо привести дополнительные данные или указать направление их поиска и сделать ряд допущений – например, об эффективности извлечения железа из крови.

Попробуем сформулировать набор признаков «хорошей» математико-биологической задачи:

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

Однако недаром говорят, что «умная, красивая, молодая жена – это три человека, а не один»: реализовать все названные признаки при составлении конкретной задачи крайне сложно.

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

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

Задача 3.3. Известно, что длина сперматозоида 60 мкм, а для оплодотворения яйцеклетки ему необходимо проплыть расстояние, в среднем равное 10 см. Какому расстоянию для пловца ростом 1 м 80 см это пропорционально?

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

Но главный смысл применения математических задач в другом. Учениками число (и формула, действия с числами) воспринимается как полумифическая абстракция, не связанная с реалиями мира. Поэтому, определив в результате длительных расчетов, что длина молекулы сахарозы составляет 12,398 мм, редкий ученик усомнится в полученном ответе, хотя абсурдность его видна невооруженным глазом. Эта мифологизированность числа, формулы, диаграммы давно известна специалистам по рекламе и широко используется в прикладных целях манипулирования сознанием потребителя.

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

«Вопреки расхожему мнению, АТФ является не формой запасания энергии, а главным непосредственно используемым источником свободной энергии в биологических системах. В обычной клетке молекула АТФ расходуется в течение одной минуты после ее образования. Оборот АТФ очень высок. Например, человек в покое расходует около 40 кг АТФ за 24 ч. Во время интенсивных упражнений скорость использования АТФ может достигать 0,5 кг/мин».¹

Другой важный аспект демонстрации чисел на уроках – преодоление столь же мифологизированного представления о науке как об области деятельности неких небожителей, недоступной для смертных. Поэтому иногда целесообразно предложить ученикам в приведенных в таблице данных о процентном содержании нуклеотидов в ДНК разных организмов (например, Грин и др., 1990, c. 186) обнаружить увиденную Эрвином Чаргаффом закономерность. Она далеко не так очевидна, особенно если не знать правильный ответ.

Приведем еще один пример, в котором анализ экспериментальных данных представляет основной интерес (а заодно демонстрирует важность сырых, «неприлизанных» данных).

Задача 3.4. «Интересный пример конкурентного исключения был исследован в опытах Т.Парка с мучными хрущаками. Два вида этих жуков из семейства чернотелок (Tribolium confusum) и (T. castaneum) содержали в ящиках с мукой. Эти виды конкурировали за пищу (муку) и, кроме того, могли питаться друг другом, причем жуки обоих видов поедали преимущественно особей вида-конкурента, а не своего вида. Парку удалось подобрать условия, при которых закономерно побеждал либо первый, либо второй вид. Однако интереснее всего оказалось исследовать итог конкуренции при промежуточных условиях, не обеспечивавших безусловного перевеса одного из видов».²

Проанализируйте представленные в табл. 3.1 данные Т.Парка и покажите, как влияют на результат конкуренции влажность и температура по отдельности.

Таблица 3.1. Количество побед двух видов мучных хрущаков в экспериментах Т.Парка

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

«Идею электронных таблиц впервые сформулировал американский ученый Ричард Маттессич, опубликовав в 1961 г. исследование под названием Budgeting Models and System Simulation… Общепризнанным родоначальником электронных таблиц как отдельного класса ПО является Дэн Бриклин, совместно с Бобом Фрэнкстоном разработавший легендарную программу VisiCalc в 1979 г. Этот табличный редактор для компьютера Apple II стал «убойным приложением», превратившим персональный компьютер из экзотической игрушки для технофилов в массовый инструмент для бизнеса».³

Microsoft Excel – мощный процессор электронных таблиц, который входит в состав программного пакета Microsoft Office. Главное назначение Excel – упрощение всяческих расчетов, которые осуществляются как по вводимым пользователем формулами, так и с помощью стандартного набора вычислений, предусмотренных разработчиками программы (суммирование, расчет среднего, процентов, переведение величин из одной размерности в другую и т.д.). Наряду с простыми и часто используемыми стандартными функциями в Microsoft Excel встроена большая подборка статистических, финансовых, логических и инженерных функций. Если Word – очень продвинутый вариант, но все-таки вариант печатной машинки, а PowerPoint – мощный аналог плакатов и флипчартов (сменяемых листов бумаги на специальном мольберте), то для Excel нет прямых аналогов. С определенной натяжкой его можно сравнить с бухгалтерскими книгами, гроссбухами, поэтому файл Excel называют книгой, а отдельную страницу – листом. Вместе с тем возможность представления, визуализации данных (построения диаграмм), одна из самых востребованных составляющих Excel, уже выходит за рамки гроссбуха.

Справедливости ради стоит отметить, что есть и другие программы этого класса, как в альтернативных офисных пакетах (OpenOffice.org Calc), так и самостоятельные (AppleWorks и Gnumeric). Стандартная функциональность: рассчитывать уравнения, создавать диаграммы и возможность открывать созданные файлы MS Excel, говорит о том, что всё-таки Excel – признанный лидер.

Т.к. основой электронной таблицы является ячейка, то важно точно и однозначно ее описать, что и сделано по аналогии с полем для игры в «Морской бой»: столбцы обозначены буквами, строки – цифрами. Аналогично обозначаются группы ячеек, диапазоны: А2 : А12, А2 : К2; А1 : Т6 – найдите их на листе открытой рабочей книги. Строго говоря, полный адрес ячейки может включать и название листа (если используем данные из ячеек других листов), и даже имя файла и путь к нему, если берем значения из других файлов.

Рис. 3.1. Элементы рабочего окна Microsoft Excel: I – строка заголовка, имя открытого файла; II – меню программы; III – панель инструментов Стандартная; IV – панель инструментов Форматирование; V – панель формул; VI – лист рабочей книги; VII – панель управления листами; VIII – панель задач с областью автовычислений; IX – открытое диалоговое окно

Да и названия столбцов и строк могут быть другими: простой нумерацией как строк, так и столбцов. Такой стиль ссылок называется R1C1 (от английских «row» – ряд, строка, и «column» – столбец, колонка). Если полученный файл имеет именно такой стиль обозначения ячеек, его можно отключить: выберите в главном меню пункт Сервис – Параметры, в открывшемся диалоговом окне – закладку Общие и снимите «галочку» в выключателе «Стиль ссылок R1C1» (рис. 3.2).

Рис. 3.2. Диалоговое окно Параметры с выключателем Стиль ссылок R1C1

Так же как и в MS Word, в Excel курсор изменяет свой вид в разных участках окна программы. Попытайтесь самостоятельно определить, в каких случаях какой вид приобретает курсор, и сравните с таблицей в конце статьи.

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

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

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

Конечно, существуют приемы упрощения подсчета в больших выборках, например, счет по десяткам квадратом: последовательно ставятся 4 точки в углах квадрата, потом линии по 4 сторонам и 2 диагонали (рис. 3.3, слева вверху). Такой способ удобен при необходимости быстрой регистрации большого количества объектов. (В студенческие годы автор, дежуря на входе в университет, построил утром вариационную кривую прохождения студентов и преподавателей по пятиминутным отрезкам времени, отмечая десятки людей точками и черточками в квадрате; интересно, что больше трети кривой пришлось на время после звонка.) Применение такой методики записи позволит быстро распределить объекты по выделенным классам значений (рис. 3.3).

Рис. 3.3. Подсчет вариант в аналоговом режиме

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

«Фирма «Гисмо продактс» владеет небольшой фабрикой по производству супергисмо. В правление фирмы входят сам мистер Гисмо (заработок 4800 долл. в неделю), его брат (2000 долл.) и 6 родственников (по 500 долл.). Рабочая сила состоит из 5 бригадиров (по 400 долл.) и 10 рабочих (по 200 долл.в неделю). При приеме нового работника ему сообщают, что средняя зарплата в фирме составляет 600 долл. в неделю. Через некоторое время сотрудник приходит к боссу, т.к., поговорив с рабочими, узнал, что их зарплата не превышает 200 долл. Тут-то и выясняется разница между средними значениями (средним арифметическим: отношением суммы всех заработков – 13 800 долл., к количеству сотрудников – 23 человека), медианой (400 долл. – значение, расположенное посередине ряда всех зарплат) и модой («типичным случаем», наиболее часто встречающимся значением, равным, как убедился работник, 200 долл. в неделю).

Мода, медиана и среднее в случаях с нормальным распределением случайных величин на больших выборках будут приближаться друг к другу, а то и совпадать. Но там, где распределение не случайно (как в примере с зарплатой), они могут весьма и весьма различаться… Столь же обманчивым будет и значение среднего балла учащихся в классе. Впрочем, математика (статистика) в оценивании заслуживает более пристального внимания…

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

Задача 3.5.⁴ Количество щенков в помете 70 серебристо-черных лисиц таково:

5, 4, 4, 4, 9, 3, 4, 4, 5, 6, 6, 4, 5, 5, 4, 8, 4, 4, 5, 4, 4, 7, 3, 5, 5, 4, 3, 3, 3, 6, 4, 4, 5, 4, 4, 5, 5, 4, 6, 3, 4, 4, 3, 4, 4, 7, 4, 3, 5, 2, 5, 4, 7, 3, 2, 3, 1, 5, 4, 2, 6, 6, 4, 4, 6, 4, 8, 3, 5, 4.

Постройте вариационный ряд и вариационную кривую, найдите среднее значение, моду и медиану, рассчитайте среднее квадратичное отклонение и дисперсию, на вариационной кривой покажите участок .

Ответ: .

Определим ход лабораторной работы по решению этой задачи.

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

Таблица 3.2. Пример заполнения таблицы в Excel

2. Построение вариационного ряда. Справа от столбца со значениями (вариантами) строится вариационный ряд, состоящий из двух строк: классы (значения, которые принимает признак, в данном случае – количество щенков, от 1 до 9), количество объектов в каждом классе. Названия рядов таблицы также желательно записать, например «Классы» в ячейке С1, а «Количество» – в ячейке С2. Для красоты (и удобства) можно выделить участок таблицы с вариационным рядом и применить к нему формат прорисовки границ ячеек (Формат – Ячейки… – закладка Границы или выпадающий список Границы (рис. 3.4, 8) на панели инструментов).

Рис. 3.4. Некоторые кнопки панели инструментов Excel

3. Подсчет количества объектов в каждом классе. Его можно выполнить вручную, а чтобы избежать ошибок, можно ячейки каждого из классов заливать своим цветом (заливкой выделенных ячеек – рис. 3.4, 9, выбирая цвет в выпадающем списке, появляющемся при нажатии на треугольник справа от иконки), а первая строка вариационного ряда выполняет роль легенды и имеет такое же выделение заливкой, как и учтенные значения в столбце вариант.

Можно процесс подсчета количества объектов в каждом классе существенно упростить. Для этого нужно выделить столбец со значениями и провести сортировку по возрастанию (Данные – Сортировка или кнопка на панели инструментов – рис. 3.4, 4). После этого надо выделить классы (заливкой выделенных ячеек – рис. 3.4, 9), цветом шрифта (рис. 3.4, 10) или рамкой (рис. 3.4, 8), причем в каждом из случаев можно выбирать цвет или тип рамки в выпадающих списках, появляющихся при нажатии на треугольник справа от иконки). Теперь подсчитать будет значительно проще. Останется внести значения количества объектов в каждом из классов в соответствующие ячейки вариационного ряда. Не забудьте порекомендовать провести проверку: посчитать общее количество объектов (или просто посмотреть на количество рядов со значениями) и сравнить с суммой количества объектов по классам (можно посчитать автосуммой: для этого выделить ячейку справа от строки со значениями в вариационном ряде и дважды нажать на знак Автосуммы (рис. 3.4, 3).

4. Расчет среднего значения признака . Для этого нужно сложить попарно произведения значения признака («Класс») на количество объектов в классе («Количество») и полученную сумму разделить на общее количество объектов. В ячейке С3 набираем слово «Среднее», а в ячейку С4 записываем формулу:

=(D2хD1+E2хE1+F2хF1+G2хG1+H2хH1+I2хI1+J2хJ1+K2хK1+L2хL1)/M2 и нажимаем Enter.

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

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

– Выбрать пункт меню Вставка – Функция и в диалоговом окне Мастера функций найти нужную (она входит в категорию статистических функций, которую нужно выбрать в списке «Категории», и так и называется – МОДА)⁵. На следующем шаге нужно указать, к какому диапазону ячеек будет применена функция: это можно сделать вручную, вводом диапазона ячеек, или выделить нужный диапазон прямо на листе (для этого существует кнопка (рис. 3.5), позволяющая свернуть диалоговое окно и выделить нужные ячейки прямо на листе рабочей книги при помощи мыши) и нажать ОК или [Enter].

Рис. 3.5. Кнопка сворачивания диалогового окна для выделения массива в рабочей книге (вверху) и вид свернутого окна (внизу)

– Впечатать в строке функций ее название и диапазон =МОДА(А1:70) – или только =МОДА(), поставить курсор между скобками и при помощи мыши выделить диапазон ячеек от А1 до А70.

Аналогично находим значения медианы =МЕДИАНА(А1:70); среднего =СРЗНАЧ(А1:70) – как видим, для этого совершенно не обязательно было вводить формулу (см. пункт 4) вручную, однако самоумение составлять и вводить формулы самостоятельно необходимо; дисперсию =ДИСП(А1:70) и среднее квадратичное (стандартное) отклонение
=СТАНДОТКЛОН(A1:70).

6. Построение вариационной кривой. Для этого надо выделить на листе ячейки с представляемыми значениями (D2:L2), и выбрать в меню программы Вставка – Диаграмма... или воспользоваться кнопкой Мастер диаграмм (рис. 3.4, 5) на панели инструментов. В Мастере диаграмм выбирается тип диаграммы (лучше один из раздела График, но не обязательно); на втором шаге можно, хотя здесь это и не критично, указать «Значения по оси Х», выделив нужный диапазон ячеек (D1:L1); настраиваются параметры диаграммы на шаге третьем (например, можно выбрать закладку Легенда и снять «Добавить легенду») и сохранить диаграмму на этот же лист рабочей книги. Для облегчения работы на следующем шаге надо дважды кликнуть на нижней оси диаграммы и в диалоговом окне Формат оси установить переключатель меток значений в разделе «Промежуточные» в позицию «Наружу».

7. Отметить на диаграмме среднее значение и интервал ± 2. Для этого надо выделить диаграмму, скопировать в буфер обмена и вставить в графический редактор Paint (Пуск – Программы – Стандартные – Paint), где и произвести необходимые действия.

Биологически среднее квадратичное отклонение определяет, какая часть изменчивости признака попадает в определенный диапазон; так, ± включает 68% вариант, ± 2 – 95% вариант, ± 3 – 99,75% вариант. Именно для этого предлагается закрасить участок на вариационной кривой. Так работа приобретает предметный смысл – вместо того, чтобы запоминать формулы и вести расчеты вручную, их можно переложить на Excel.

Несколько иная задача с непрерывными признаками: тут не построишь ряд с измеренными значениями, возникает необходимость разделить значения признака на классы. И при выполнении этой работы хотя бы на 3–4 компьютерах уже можно, рассчитав основные характеристики (моду, медиану, среднее и стандартное отклонение) по всей совокупности вариантов, строить вариационные ряды с разным шагом: одна группа разбивает на классы с шагом 3 см, другая – 4 см, третья – 5 см. Подсчет среднего проводится каждой группой по вариационному ряду. Интересно также сравнить среднее, полученное в каждом из экспериментов (и затраченное время), а также вариационные кривые.

Задача 3.6.⁶ Рассчитать основные статистические показатели в группе из 120 старшеклассников, построить вариационный ряд и вариационную кривую.

Рост в сантиметрах: 164, 169, 170, 167, 169, 176, 171, 181, 168, 174, 175, 183, 181, 183, 182, 166, 173, 175, 171, 177, 172, 177, 166, 172, 178, 175, 175, 172, 164, 163, 176, 175, 164, 172, 170, 176, 181; 172, 175, 174, 190, 172, 174, 175, 154, 169, 159, 162, 170, 166, 165, 174, 166, 176, 172, 170, 174, 174, 167, 165, 172, 173, 169, 176, 176, 171, 173, 176, 184, 165, 166, 161, 169, 170, 172, 173, 176, 171, 185, 166, 156, 158, 169, 160, 178, 174, 166, 166, 165, 179, 166, 179, 186, 169, 183, 182, 175, 178, 174, 177, 178, 164, 167, 180, 164, 174, 163, 170, 171, 187, 170, 167, 179, 171, 173, 167, 170, 173, 172, 169.

Ответ: = 171,93; = ±6,55.

Конечно, лабораторную работу лучше проводить на натуральных объектах, но по-своему интересно (и проще проверяемо) на заданных (как в данном примере) выборках – если, конечно, не ставится задача поверить умение старшеклассников пользоваться линейкой при измерении. Впрочем, работу можно разделить на два этапа: в предметном кабинете произвести измерения и получить ряд данных (записать варианты), а в компьютерном классе – обработать их с исследованием самих методов изучения изменчивости.

Логика выполнения работы та же, что и с предыдущей задачей. Если варианты располагаются в столбцах, то можно также провести сортировку по возрастанию (Данные – Сортировка или кнопка на панели инструментов (рис. 3.4, 4). После этого надо выделить классы (заливкой выделенных ячеек, цветом шрифта или рамкой). Рядом располагается сам вариационный ряд (лучше использовать три строки: в первой будут границы классов, во второй – среднее значение класса, используемое в расчете среднего значения, а уже в третьей – количество объектов в каждом классе); первая строка вариационного ряда имеет такое же выделение (заливкой или цветом шрифта), как и в столбце Вариант. Останется посчитать количество объектов в каждом классе (причем если лень это делать вручную, то можно выделить рядом с диапазоном ячейку, ввести формулу =СЧЁТЗ(), а в скобках указать, в каких именно ячейках вас интересует количество значений, и формула выдаст искомое число; более того, это можно делать непосредственно в ячейках вариационного ряда). Не забудьте также провести проверку: подсчитать общее количество объектов (или просто посмотреть количество рядов со значениями) и сравнить с суммой количества объектов по классам при помощи автосуммы (рис. 3.4, 3). При этом нужно предложить проверить, те ли значения нужно суммировать, показав выделенный диапазон ячеек или вводя формулу =СУММ().

Рассчитываемая программой точность значений избыточна, и можно уменьшить разрядность (количество отображаемых знаков после запятой в десятичной дроби): выделив ячейки с числами, нажмите кнопку Уменьшить разрядность (рис. 3.4, 7) на панели инструментов столько раз, сколько нужно, чтобы остались два знака после запятой. Стоит отметить, что число в ячейке не округляется, а только показывается с запрошенной степенью точности, а для округления используется отдельная функция, полезная в тех случаях, когда задача решается в целочисленных величинах, во избежание ответа «полтора землекопа» – =ОКРУГЛ(А1:А15; 0), где в скобках до точки с запятой указывается, к каким ячейкам округление применить, а после точки с запятой – до скольких знаков после запятой округлить, в данном случае – округлить до целого числа; наряду с округлением «по правилам» можно также принудительно округлять до большего или до меньшего значения формулами ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ (Минько, 2007).

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

Задача 3.7.⁷ Перед вами — модель, описывающая ход эпидемии:
b = 0,00002 ВN — 0,00005 В; с = 0,00004 В.
В, С, N — численности (в какой-то момент) соответственно больных, переболевших и не болевших;
b и с — прирост В и С за день.
Вырабатывается ли к этой болезни стойкий иммунитет? Умирают ли люди от этой болезни? Чем заканчивается распространение этой эпидемии в популяции?

Отметим сделанное авторами задачи упрощение обозначений, дабы никого не пугать видом записи, ведь, например, b – это (изменение величины в единицу времени). В Excel решить задачу можно на примере городка с населением в 10 000 жителей.

Введите день развития эпидемии (естественно, 1), а также начальные численности больных (например, 5) и переболевших (0). Чтобы не нумеровать дни вручную, поступим так: в ячейке А3 (под единицей) кликнем левой кнопкой мыши (активизируем ее), введем =A2+1 и нажмем [Enter]. (Т.е. в этой ячейке значение будет на единицу больше, чем в предыдущей.) Теперь к квадратику в правом нижнем углу активной ячейки (А3) подведем курсор мыши, и тогда он примет вид крестика (рис. 3.12, 6). Нажав левую кнопку мыши, растянем эту ячейку вниз на столько дней, на сколько сочтем нужным (эта функция называется автозаполнением). Аналогично введем остальные формулы, не забывая при этом, что в них идет речь о приросте каждой группы, поэтому, например, формула для переболевших для второго дня развития эпидемии в ячейке С3 будет выглядеть так: =0,00004xВ2+С2. Количество неболевших определяется вычитанием больных и переболевших из общей численности населения городка.

Для вящей красоты можно выделить столбцы B–D и настроить отображение числа жителей в целых величинах при помощи кнопки Уменьшить разрядность.⁸

Остается построить диаграмму, для чего выбирается инструмент Мастер диаграмм (рис. 3.4, 5) на панели инструментов программы. Наиболее адекватной задаче будет диаграмма с нормированными областями из набора стандартных диаграмм (она сразу построит график в процентах, рис. 3.6).

Рис. 3.6. Окно Мастера диаграмм с выбранным типом диаграммы

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

Рис. 3.7. Развитие эпидемии – модель задачи в Excel

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

b = 0,00002 ВN – 0,00005 В; с = 0,00004 В.
В, С, N – численности (в какой-то момент) соответственно больных, переболевших и неболевших; b и с – прирост В и С за день.

Решение. Прирост числа больных (0,00002 ВN) происходит за счет заболевших. Судя по тому что он зависит от N – числа неболевших и что при N=0 пополнения больных нет (а также по тому, что в этом слагаемом нет С), можно сделать вывод, что переболевшие приобретают стойкий иммунитет. Прирост числа переболевших происходит за счет выздоровевших (0,00004 В) и в каждый момент зависит от числа больных, что вполне естественно. Убыль числа больных (–0,00005 В) происходит за счет выздоровевших (0,00004 В) и умерших. Поскольку 0,00004 В меньше 0,00005 В, то действительно бывают умершие, которые и дают разницу, равную 0,00001 В.

Поскольку умирают не все заболевшие, а переболевшие не могут заболеть вновь, то рано или поздно эпидемия в популяции прекращается.⁹

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

Особое значение необходимо уделить обязательному наличию обозначений на осях графика, выбору правильной размерности обозначений и шагу шкал, возможно, потратив на это значительный отрезок времени на одном уроке и даже предложив ряд задач с выбором размерности. Стоит отметить, что Excel при построении диаграммы сам выбирает оптимальные значения и строит метки делений, поэтому выработать данный навык придется предварительно и аналогово.

В более мощных пакетах, например статистических, наряду с традиционными и доступными в Excel (рис. 3.8, А–В) есть и особые типы диаграмм, идеи которых могут – при определенной подготовке – быть эффективно использованы на уроках. Это и деревья (Г), и диаграммы, в которых одновременно представляется много признаков (Д, Е): два задают положение в плоскости координат, третий – диаметр объекта, а остальные представляются секторами диаграмм или относительными размерами черт лица.

Рис. 3.8. Визуализация данных в статистических программах

«Моделирование – это установление или раскрытие некоего подобия. Раскрывая или устанавливая подобие, мы уменьшаем разнообразие мира, а тем самым упрощаем его и вместе с тем нечто о нем (о мире, как мы его себе представляем) узнаем. Ибо «узнать что-то о мире» – это то же самое, что «открыть в нем (или создать) некий вид порядка».

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

Таблица 3.3. Заполнение таблицы с данными об эпидемии

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

Моделирование динамики популяции становится более сложной задачей, если попытаться учесть реальные взаимоотношения между видами. Это впервые сделал американский ученый А.Дж. Лотка (A.J. Lotka) в 1925 г., а в 1926 г. независимо от него и более подробно – итальянский ученый В.Вольтерра (V.Volterra). В модели, известной сейчас как уравнение Лотки–Вольтерры, рассматривается взаимодействие двух популяций – хищника и жертвы. Численность популяции жертвы N₁ будет изменяться во времени (и зависеть от численности популяции хищника N₂) по такому уравнению:

где N₁ – численность популяции жертвы, N₂ – численность популяции хищника, r₁ – скорость увеличения популяции жертвы (т.е. рождаемость), p₁ – коэффициент хищничества для жертвы (вероятность того, что при встрече с хищником жертва будет съедена).

Таким образом, увеличение численности жертвы в единицу времени (выражение слева от знака равенства и есть изменение численности dN₁ за единицу времени dt) происходит за счет рождения новых особей (скорость размножения на количество особей), а убыль – за счет поедания хищниками (эта величина пропорциональна численности жертвы, т.к. чем больше особей-жертв, тем выше вероятность их встречи с хищником, численности самого хищника и вероятности того, что жертва при этой встрече погибнет p₁).

Прирост популяции хищника описывается таким уравнением:

где N₁ – численность популяции жертвы, N₂ – численность популяции хищника, d₂ – смертность хищника, p₂ – коэффициент хищничества (некая величина, указывающая на «доход», полученный хищником при поедании жертвы, качество его питания).

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

Здесь, как и в задаче 3.7, можно обратить внимание на то, от каких факторов зависит положительный прирост (увеличение численности) каждой из популяций, а от каких – отрицательный, убыль.

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

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

Теперь введем в ячейку В3 формулу уравнения Лотки–Вольтерры для жертвы (вместо значений N₁ и N₂ пользуемся номерами ячеек, в которых они находятся – В2 и С2 соответственно, а цифровые значения – из табл. 3.4):

Таблица 3.4. Прирост и убыль численности жертвы и хищника

=B2+0,1xB2–0,001xB2xC2 и нажмем [Enter].

Применим формулу ко всему столбцу при помощи автозаполнения.

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

Теперь введем в ячейку С3 формулу для хищника (также вместо значений N₁ и N₂ пользуемся номерами ячеек В2 и С2 и цифровыми значениями из таблицы выше):

=C2+0,00005xB2xC2–0,05xC2 и нажмем [Enter].

Теперь снова растянем активную ячейку (С3) на выбранное число поколений.

Вы видите, что численности установятся на исходных значениях 1000 и 100 особей соответственно, т.е. популяции находятся в равновесии. Разбалансируем систему, изменив, например, численность жертвы (в ячейке В2 введем новое значение, 500, и нажмем [Enter]). Программа сама пересчитает значения во всех ячейках, и уже можно наблюдать колебания численности обоих видов.

Остается представить результат в виде графика. Для этого выделяем столбцы В и С (достаточно провести по их заголовкам с буквами («В» и «С») курсором мыши при нажатой левой кнопке), и нажимаем на иконку Мастер диаграмм на панели инструментов (рис. 3.4, 5) или пункт меню Вставка – Диаграмма. В открывшемся диалоговом окне выбираем График и соглашаемся с предлагаемым по умолчанию графиком с маркерами. После нескольких Далее> без возражений соглашаемся с Готово и рассматриваем результат. Динамика численности есть, но нагляднее было бы представить значения численности обеих популяций в согласованных величинах. Для этого дважды кликните мышкой по любому значению численности хищника и в открывшемся окне (Формат ряда данных) выберите закладку Ось, а в ней в переключателе «Построить ряд» – «по вспомогательной оси» и нажмите ОК (рис. 3.9). Если было просмотрено много поколений (100–200), согласованные колебания численности будут хорошо видны на графике.

Рис. 3.9. Модель Лотки–Вольтерры. Численность хищника отложена по вспомогательной оси (справа)

Обратите внимание на формулы в ячейках, получившиеся в результате автозаполнения. Вы видите, что программа в каждой следующей строке сама прибавляет по единице ко всем номерам строк в адресах ячеек. Это, безусловно, очень удобно… но если хочется изменить сами константы, то было бы желательно сделать так, чтобы формула всегда ссылалась, в т.ч. и при растягивании, на одну ячейку. Это можно сделать, использовав в формуле т.н. абсолютную ссылку: адрес такой ячейки обозначается символами $:

=B2+0,1xB2-0,001xB2xC2 после замены констант на ссылки выглядит как

=B2+$D$2xB2-$E$2xB2xC2.

Выполнив такую замену в обеих формулах, можете смело менять константы в ячейках D2–G2 и оценивать влияние этих изменений на динамику популяций.

Можно также – если покажется проще – присваивать имя конкретной ячейке (пункт меню Вставка – Имя – Присвоить) и задать текстовое имя конкретной ячейки, которое потом использовать в формулах.

Для получения законченной интерактивной модели останется упростить ввод цифр в ячейки (предварительно полезно сделать текстовые описания: что именно и в каких единицах отображается). После этого надо открыть панель инструментов Элементы управления (Вид – Панели инструментов – Элементы управления), выбрать иконку Полоса прокрутки и нарисовать при помощи мыши прямоугольник полосы прокрутки в нужном месте рабочего листа книги. После этого нужно дважды нажать левой кнопкой мыши на изображение полосы прокрутки и в открывшемся диалоговом окне (рис. 3.10) выбрать закладку Элемент управления. Именно эта часть диалогового окна позволит задать основные свойства диапазона, изменяемого при помощи активного элемента (минимальное и максимальное значения, шаг изменений, и, главное, связать с конкретной ячейкой, иными словами – задать адрес той ячейки, куда будет представляться интерактивно задаваемое число).¹⁰

Рис. 3.10. Диалоговое окно Формат элемента управления

Полоса прокрутки позволяет задать только целые числа, поэтому для дробей придется сделать дополнительно ячейку, в которой число из связанной с активным элементом ячейки будет разделено на 100, 1000, 100 000 и т.п. Если все шесть параметров модели задать ползунками, получится настоящая интерактивная исследовательская модель, которую можно в дальнейшем совершенствовать, добавляя резкие колебания численности в определенный момент времени (выбранное поколение), пределы роста популяций, неуничтожимый минимум жертвы в рефугиумах и т.п. – см., например, http://www.kozlenkoa.narod.ru/docs/lotka_full.xls

Совершенно аналогично работают программы, имеющие прямую математическую основу, например программы по расчету пищевых рационов. Так, программа «Считалка калорий» (http://calories.ru/demo.htm) позволяет составить пищевой рацион и оценить его энергетическую ценность (в демоверсии – из очень ограниченного набора продуктов и без возможности сохранить сформированный рацион), в т.ч., используя большой набор единиц измерения: граммы, ложки, стаканы и др.

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

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

Древнейшей имитационной моделью, первые версии которой работали еще в операционной системе DOS, была «Жизнь+» или «Эволюция» Джона Хортона Конвея. Модель базируется на теории клеточных автоматов. В ней каждая клетка выживает – или отмирает (если у нее нет соседей – от одиночества, а если очень много, 5 и более, – от перенаселенности); а если у пустой ячейки есть три соседние, занятые клетками, то там рождается новая клетка. В результате возникают «популяции», которые эволюционируют по определенным правилам, образуя статические, неизменные группы («реликты»), активно распространяющиеся группы, упорядоченные структуры и др. Основная идея заключается в том, чтобы, начав с какого-нибудь простого расположения живых клеток, проследить за эволюцией исходной популяции под действием генетических законов Конвея, которые руководят рождением, гибелью и выживанием клеток. Программы, построенные на модели Конвея, можно скачать бесплатно c ряда сайтов. Особенно удачна программа Life 32 (http://www.mindspring.com/~alanh/lifep.zip, рис. 3.11). Остается подобрать индивидуальные задания по работе с моделью, распечатать и продумать форму итоговой защиты работ на основе заданий в модельной среде.

Рис. 3.11. Окно программы Life 32

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

Рис. 3.12. Изменение курсора мыши в разных участках окна программы Excel.
1. Перемещение объекта (панели инструментов, графического объекта). 2. Выделить строку.
3. Распространить значение, формулу, диаграмму на другие ячейки. 4. Изменить ширину
столбца (строки). 5. Установить курсор текстового ввода в панель формул.
6. Автозаполнение – распространение введенного значения (формулы) на другие ячейки.
7. Копирование содержимого ячейки (какая клавиша клавиатуры при этом должна быть нажата?).
8. Перемещение содержимого ячейки. 9. Выделение активной ячейки.
10. Выделить столбец. 11. Выбор активного элемента (кнопки, элемента списка, меню, иконки на панели инструментов и т.п.)

Рекомендуемая литература

Гарднер М. А ну-ка, догадайся! – М.: Мир, 1984.

Грин Н., Стаут У., Тейлор Д. Биология. В 3-х т. Т.1. – М.: Мир, 1990. (или более поздние издания).

Минько А.А. Функции в Excel. Справочник пользователя. – М.: Эксмо, 2007.

Ответы

Задание 3.4.

1. Активная ячейка.
2. Поле имени (координаты активной ячейки).
3. Горизонтальная полоса прокрутки.
4. Вертикальная полоса прокрутки.
5. Курсор мыши в рабочей области (на листе) при выборе активной ячейки.
6. Курсор мыши при выборе активного элемента.
7. Кнопки прокрутки листов рабочей книги.
8. Закладки (ярлычки) листов.
9. Кнопка Изменить формулу.
10. Поле ввода (соответствует активной ячейке), в т.ч. формул.
11. Кнопки Свернуть, Развернуть, Закрыть программы.
12. Кнопки Свернуть, Развернуть, Закрыть открытого файла (рабочей книги).

Задание 3.6. Не отображаются умершие от этой болезни.

От редакции:

Для тех, кто захочет предложить ученикам самим вывести правило Чаргаффа, но не имеет доступа к соответствующей информации, приводим таблицу из рекомендуемого автором источника (Грин Н. и др., 1990):

Таблица 3.5. Относительные количества оснований в ДНК разных организмов

---

¹ Страйер Л. Биохимия / Пер. с англ. В 3х т. Т.2. – М.: Мир, 1985.

² Шабанов Д.А., Козленко А.Г., Кравченко М.А. ИУМК «Экология. Конструирование биосферы», конкурс НФПК, 2008.

³ «Табличный процессор»: материал из Википедии – свободной энциклопедии.

⁴ Перевод с изменениями из: Збірник задач і вправ з біології: Навч. Посібник /А.Д. Тимченко, Ю.І. Бажора, Л.Г. Кириченко та ін.; За ред. А.Д. Тимченка. – Київ: Вища школа, 1992.

⁵ Если у вас русифицированный Microsoft Office; если нет – английское название MODE. Кстати, регистр ввода значения не имеет, даже лучше печатать строчными (маленькими) буквами – Excel заменит сам, если название функции введено правильно, на большие буквы (т.е. это – способ самопроверки).

⁶ Перевод с изменениями из: Збірник задач і вправ з біології: Навч. Посібник /А.Д. Тимченко, Ю.І. Бажора, Л.Г. Кириченко та ін.; За ред. А.Д. Тимченка. – Київ: Вища школа, 1992.

⁷ Биология в школе. 1993. № 2. С. 57 (с небольшими изменениями).

⁸ Отметим еще раз, что это действие не округляет значения до целых чисел, а только «отсекает лишнее количество» знаков после запятой (в данном случае – все). Однако для данной задачи такое преступление против здравого смысла несущественно.

⁹ Биология в школе. 1993. № 2. С. 57 (с небольшими изменениями).

¹⁰ Описанный прием работает в Excel 2000; в Excel 2003 для редактирования элемента управления нужно нажать на панели инструментов Элементы управления инструмент Свойства (Properties) и в открывшемся окне Properties вручную ввести название ячейки, в которой будет отображаться изменяемая полосой прокрутки цифра (справа от LinkedCell), а также ниже указать пределы изменения (поля Min и Max) и при желании шаг изменений (по умолчанию – 1, поле SmallChange); проверить работу интерактивного устройства можно, выйдя из режима конструирования (отжать левую верхнюю кнопку на панели инструментов с линейкой и треугольником).