Личная страница Козленко А.Г.

Навигация

Числовые задачи и Microsoft Excel

Главная
Об авторе
     Урочное:
Биология + компьютер:
         полные уроки по типам
Компьютерные программы
         на уроках
 Биософт                 Планшет
 Учебные рисунки и
           карикатуры
 Фотографии           Проекты
 Художественная
         литература на уроке
 Наглядные пособия, игры
 Олимпиадное
 Книги и статьи
 ''Биология-10'': практикум +
         попытка учебника
 ''Игра и биология''
 ''Информационная
       культура и/или компьютер
       на уроках биологии''
 ИУМК ''Экология.          Конструирование
         биосферы''
 Проектно-ролевая игра
         ''Генная инженерия''
 ''Библейская генетика''
 Рабочие тетради
         по общей биологии
 Педагогическое
 Блог
Подробно: Математика в биологии: культура числовых заданий (лекция 3, 1 часть (лекции 1-4).
Фото слева - Nikki Graziano (подборка Photographer Loves Math, Graphs Her Images в
Wired), справа - отсюда. См. также чудесную 3D-анимацию "Nature by Numbers".
Тут выложено несколько моделей и файлов по темам: "Наследственность и изменчивость", "Экология", "Человек", другие; а также ссылки о представлении данных.

Наследственность и изменчивость:

1. Вариационный ряд и вариационная кривая для задачи:
Задача. Рассчитать основные статистические показатели в группе из 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.

Перевод с изменениями из: Збірник задач і вправ з біології: Навч. Посібник /А.Д. Тимченко, Ю.І. Бажора, Л.Г. Кириченко та ін.; За ред. А.Д. Тимченка. – Київ: Вища школа, 1992, 391 с.: іл. – С. 108.
Фото справа: распределение по росту студентов Сельскохозяйственного колледжа штата Коннектикут в 1914 г. (вверху) и студентов того же учреждения (теперь это Университет штата Коннектикут) в 1997 г. (внизу, девушки в светлой одежде). Средний рост юношей изменился с 67,3 дюйма в 1914-м до 70,1 дюйма в 1997-м. Статья в Journal of Heredity ( PDF-версия)
.

Гауссиана и ящик Гальтона (Galton Box)

Как оказалось, Ф. Гальтон, двоюродный брат Ч. Дарвина, основоположник анализа родословных, дерматоглифики, близнецового анализа, тестового метода исследования личности, эвгеники, семейных фотопортретов и т. д., предложил для демонстрации нормального (гауссового) распределения ящик со штырями (рис. справа и фото внизу, взяв за идею популярную настольную игру), по которому скатывается большое количество шариков (ящик Гальтона).
Есть модель в Excel (автор Serge Garneau, отсюда), в которой можно запустить эксперимент и сравнить с теоретически ожидаемым результатом (остановить - Esc нажать и подержать, потом выбрать "Нет"; макрос также надо включить). Но интереснее работать с настоящим ящиком.

1 2 3 4 5 6 7 8

1 - отсюда, 2-3 - Парижский музей науки, отсюда, 4 - отсюда, 5 - отсюда, 6-7 - отсюда, 8 - видео это), а также сосульки.

См. также Визуализация температуры.

2. Расщепление при скрещивании дигетерозигот при сцепленном наследовании.
Обычно задачи на дигибридное скрещивание сцепленных генов строятся по принципу анализирующего скрещивания: двойная гетерозигота скрещивается с рецессивной гомозиготой. Однако в некоторых задачах предлагается провести скрещивание дигетерозигот.
Решение в Excel предлагается в двух вариантах:  с фиксированным расстоянием между генами и в общем виде, где можно задавать расстояние между генами произвольно.
Задача. Проводили скрещивание двух чистых линий кукурузы: у растений одной линии были гладкие окрашенные семена, а у растений другой – морщинистые неокрашенные. Все потомство первого поколения имело гладкие окрашенные семена. Если скрестить гибридов первого поколения с растениями чистой линии, имеющими морщинистые неокрашенные семена, то из 1000 потомков 484 имели гладкие окрашенные семена, 480 – морщинистые неокрашенные, 17 – гладкие неокрашенные, и 19 – морщинистые окрашенные. Рассчитайте, какие потомки и в каком соотношении получатся, если скрестить тех же гибридов первого поколения друг с другом.

3. Частоты фенотипов по группам крови АВ0.
Расчет частот генотипов и фенотипов по формуле Бернштейна для трех аллелей. Выбираются частоты аллелей, находятся частоты фенотипов.Добавлены также схемы для двух аллелей (резус-фактор, MN) и для трех аллелей со ступенчатым доминированием (ср. с задачей о цвете глаз).

3а. Вероятность рождения ребенка с определенной группой крови
Расчет вероятности рождения ребенка у родителей с разными группами крови, если генотип родителей неизвестен (например, II (A) группа крови - IAIA  или IAi0?): берутся среднепопуляционные (для русских) частоты аллелей и рассчитывается вероятность.

3б. Частоты аллелей групп крови у разных рас
Обычно бывает нужно показать, как различаются частоты аллелей некоторых генов у разных рас. Чтобы не искать - иллюстрация на эту тему по Ф.Айале (усредненные частоты по АВ0, резус-фактору и Даффи; резус-фактор показан как полигенный признак с тремя вариантами доминантных и тремя - рецессивных аллелей).
См. также морфологические признаки рас.

4. Популяция говорунов.
Задача. Говоруны бывают желтые, синие и зеленые. Признак определяется одним геном, доминирование отсутствует — гетерозиготы зеленые (желтый и синий цвета). Скрещиваются только особи разных цветов, в остальном цвет не влияет на выбор партнера. Вскоре после оплодотворения пары расстаются, и самцы могут спариваться со «свободными» самкам» снова. Изначально в популяции поровну особей каждого цвета. Каким будет процент желтых, синих и зеленых особей в первом, втором, пятом поколениях, если пары образуют только представителя одного поколения?
В чем причины отклонения от закона Харди-Вайнберга? Какую величину, характеризующую генетическую структуру популяции, можно рассчитать по этим данным?
(Биология в школе, 1993, №2, апрель-март, С. 64.)

5. Близнецы и коэффициент наследуемости.
В общем виде (по коэффициентам конкордантности МБ и ДБ) и для задачи:
Задача. Для определения конкордантности в отношении курения у женщин использовали 71 пару близнецов (53 монозиготных и 18 дизиготных). Среди монозиготных в 44 парах курили обе, в 9 – одна. Среди дизиготных в 9 парах курили обе, в 9 – одна. Оцените степень влияния наследственности и факторов среды на развитие курения у женщин по материалам данного исследования.
Атраментова Л.А. Генетика человека: Учеб. пособие. – Харьков, ХГУ, 1990 – 91 с. - C. 79-80, с изм.

Экология:

6. Абиотические факторы: взаимодействие: модель, показывающая одновременное действие температуры, влажности, рН почвы, концентраций солей азота и тяжелых металлов; общая трехмерная диаграмма показывает распределение растений по участкам территории. Можно менять значения каждого из факторов относительно стартового распределения, заданного "по умолчанию".
Модель оптимизирована под разрешение 1280*1024 пикс.(+ файл в формате Excel 2007 - с новым условным форматированием и заготовками для трехмерного графика из задания ниже).
Добавлены урожайность и продукция биомассы (в условных величинах).
Из модели удобно сделать трехмерный график, выбрав два набора характеристик ("столбцы" и "строки"), распечатав и собрав (а также распечатав показатели абиотических факторов на участке как подложку). И спрашивать, где что лимитировало...

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

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

9. Численность человечества и описание ее разными математическими моделями. Нужно подобрать к реальным точкам подходящую кривую.

Человек: Обмен веществ и энергии:

10. Расчет основного обмена и общих энергозатрат. Калькулятор, позволяющий рассчитать площадь поверхности тела, основной обмен (количество энергии, необходимое для поддержания нормальной жизнедеятельности в полном покое) и общий уровень энергозатрат, учитывающий степень физической активности.
Фото справа (площадь кожи) - из "Иллюстрированных фактов" Музея науки в Ванкувере: 1, 2. См. также тут.

10а. Формула красоты Ландау
- формула, приписываемая небезызвестному академику Ландау: зависимость женской красоты от основных антропометрических параметров ( отсюда): формула, калькулятор в Excel. См. также центили - зависимость "рост-вес" для девочек (и не только).
(Вопрос Ландау о наследовании приобретенных признаков см. тут, а об изолиниях красоты Гамова-Ландау-Ресовского - тут).

Интересный объект для анализа формулы как математической модели (ниже - часть обсуждения в ЖЖ, в т.ч. с ошибками в анализе):

krasarbi4: Это параметры Венеры Милосской. у неё тоже 6. (NB. Фото, параметры: рост 164 см, пропорции 86х69х93 - тут)
jursla: Проверил ручку от швабры. (4 * 4 * 160) / ((4^2) * 0.8) = 200
kmmbvnr: Ага, красота значится измеряется в сантиметрах на килограмм
biglebowsky: Формула Ландау прикольна, но очень своеобразна - она не масштабириуется. Точнее, при масштабировании, результат падает как квадрат роста человека. Высокие результаты по формуле Ландау должны быть либо у "миниатюрных" взрослых, либо у детей. У новорожденных, вообще, ~ 15 будет :-)))

Интересный вариант работы - посчитать "красоту" детских кукол, например, Барби (постер отсюда; для своих кукол - решение пропорций и определение массы тела через измерение объема, как для динозавров).

11. Таблицы: (1) суточной потребности человека в калориях, белках, витаминах и микроэлементах и (2)  содержания калорий и веществ в продуктах питания (по: Грин Н., Стаут У., Тейлор Д. Биология: В 3-х т. Пер. с англ./Под ред. Р. Сопера – М.: Мир, 1990. - т. 2, с. 10-12). См. также колоссальный список пищевых продуктов с подробнейшим составом и расчетом на любые порции Foods List (англ.). Справа - шоколад с калорийностью на кусочках: 1, 2, отсюда.
GDA. Статья "Пищевые рационы: старая работа на новый лад" (журнал «Биология» - «Первое сентября», № 1 (949), 01.2013 – С. 26–29) + коллекция сканов содержания разных компонентов в порции пищи в процентах от суточной нормы потребления – GDA, см. краткое описание; официальный сайт (англ.), в т.ч. презентацию  Portion Sizes For Purposes of Nutrition Labelling.

1 2 3 4 5 6 7 8 9

1 - батончики "Mars" и "Nuts", 2 - "Coca-Cola", 3 - поджаренный хлеб "Danesita", 4 - хлебцы и овсяная каша "Nordics"; 5 - Биг-Мак и картошка по-деревенски в Макдоналдсе (см. также Таблицы энергетической и пищевой ценности продукции Макдоналдс ( Россия, Украина, июль 2012 г.), у которого, кстати, более наглядная маркировка, чем стандартная GDA, см. справа); 6 - яйца (с природным содержанием каротиноидов, селена и витамина Е), тут, 7 - болгарское печенье, 8 - равиоли (мини-варенички) с сыром и шпинатом, печенье и шоколад, Ашан-Италия, 9 - французские конфеты Roshers noirs. О пищевых добавках тут.

Хорошее задание для лабораторной работы - составить такой "обед" в Макдоналдс, используя калькулятор суточного рациона на сайте Макдоналдс, который полностью покрывал бы суточную потребность человека. Окажется, что это совсем немного, если считать в батончиках и сэндвичах!..
См. также классическую статью Р. Дж. Вуртман, Дж. Дж. Вуртман Углеводы и депрессия ("В мире науки",1989,№3, C. 46-54) - о сезонной депрессии, тяге к углеводам в определенные часы и фотопериодизме.

12. Замечательный сборник антиалкогольных задач ("Пособие для преподавания арифметики в низших школах всех ведомств" 1914 г.) в блоге junet. Актуальны и сейчас (см. пример задачи, все  задачи).
 

Разное:

13. Тест "Логические мыслительные задачи"
Из определенного расположения заполненных [ 1 ] и пустых [ 0 ] клеток в левой части делается вывод, представленный в правой части высказывания (справа от =). Это условие. Далее на строке приводится задание (левая часть высказывания), и необходимо сделать вывод, проставив [ 1 ] или [ 0 ] в ячейках последнего квадрата (цвет ячеек будет меняться автоматически благодаря условному форматированию). Если задание выполнено правильно, вместо логического вывода ЛОЖЬ (FALSE) появится сообщение о правильном решении - ИСТИНА (TRUE).

14. Фермент и субстраты
Слева – активный центр фермента (красный – отрицательно заряженный участок, синий – положительно заряженный, желтый – гидрофобный), справа – четыре возможных субстрата. Какой из них подходит к ферменту?
Иллюстрация того, что комплементарность – не просто пространственное соответствие. Сделано в Excel.

Хороший сайт с советами по Excel (плюс  шпаргалка по горячим клавишам и трюкам в Excel).
  Представление данных
Статьи о представлении данных тут (1, 2 и 3), а также алгоритм выбора диаграммы. См.также представление данных в виде трехмерного графика из бумаги.
См. также интерактивную "Периодическую таблицу методов визуализации" (эскиз слева; хотя некоторые примеры притянуты за уши ради уподобления шедевру Д.И. Менделеева, есть несколько очень эффектных/эффективных примеров). См. также задание 3 по митозу (диаграмма Ганта) и сравнение (диаграмма Венна).
Справа - классическая корреляция из сообщения в журнале "В мире науки" (Scientific American на русском языке), номер-страница на картинке внизу.
Visual complexity
Каталог приемов визуализации данных, в т. ч. в биологии (см. также постеры)

Пословицы в диаграммах и графиках
Интересный пример использования визуализации данных для представления пословиц и поговорок (  отсюда,  архив, пример справа).

См. также записи с тегом Инфографика в блоге.