Разнообразие сообщества зависит не только от числа видов, но и от равномерности их численностей. Индекс Симпсона D = Σpi2, где pi — доля i-го вида, равен вероятности того, что две случайно выбранные особи принадлежат к одному виду. Чем меньше D, тем разнообразнее сообщество; поэтому используют также индекс разнообразия 1 − D и эффективное число видов 1 / D — число одинаково многочисленных видов, дающее то же значение D.
Файл t12_simpson.xlsx. В строках 5–164 приведено число особей 160 видов ночных чешуекрылых на двух участках: в ельнике (столбец B) и на вырубке (столбец C). Пустая ячейка означает, что вид на участке не отмечен. Данные модельные.
Задание
- В ячейках B2:C2 вычислите общее число особей N на каждом участке, в ячейках B3:C3 — число видов S (функция СЧЁТ учитывает только заполненные ячейки). Формулы введите в столбец B и скопируйте в столбец C.
- В ячейках D5:E164 вычислите доли видов p = n / N. Введите одну формулу в D5 и скопируйте её на весь диапазон D5:E164: ссылка на N должна закреплять строку 2, но не столбец.
- В ячейках F5:G164 вычислите квадраты долей p2 (одна формула в F5, копирование на весь диапазон).
- В ячейках J5:K9 для каждого участка вычислите: индекс Симпсона D (сумма квадратов долей); индекс разнообразия 1 − D; эффективное число видов 1 / D; индекс Бергера — Паркера (наибольшая численность вида, делённая на N); сумму долей (контрольная величина, равна 1).
Самопроверка. B2 = 563; C2 = 1 145; B3 = 115; C3 = 104.
Требования к выполнению
- Формулы вводятся только в жёлтые ячейки. Число, вычисленное отдельно и введённое вместо формулы, не засчитывается.
- Исходные данные и параметры (голубые ячейки) не изменяются; строки и столбцы не вставляются и не удаляются.
- Параметры и итоговые величины используются в формулах только через ссылки на ячейки.
- Формула для диапазона вводится в первую ячейку диапазона и копируется на весь диапазон.
Сдача. Сохраните файл в формате .xlsx под именем, отличным от исходного, например ivanov_t12.xlsx (фамилия латиницей), и загрузите его в поле ответа.