Использование программы Microsoft Excel в биометрии

Биометрия (от греч. bios – жизнь, metron – мера) – наука о применении математических методов в биологических исследованиях при изучении групповых свойств биологических объектов. Содержанием биометрии является обработка данных наблюдений и экспериментов в биологических исследованиях, а ее аппаратом (методом) – теория вероятностей и математическая статистика.

Запуск программы Excel осуществляется так же, как и запуск других приложений пакета Microsoft Office: с помощью команды Microsoft Excel из меню программы или кнопки с таким же именем, расположенной на панели инструментов Microsoft Office.

После запуска Excel открывается окно, изображенное на рис. 1. Меню и панели инструментов Excel очень похожи на соответствующие элементы окна редактора Microsoft Word.

Рис. 1. Главное окно программы Excel

Основные операции с данными в Excel выполняются на рабочем листе. Рабочий лист разделен на строки и столбцы, которые при пересечении образуют ячейки. В ячейки вводится содержимое таблицы. Рабочий лист состоит из 256 столбцов и 65536 строк.

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

Таблица 1. Описание клавиш и комбинации, предназначенные для перемещения по листам рабочей книги

Клавиша	Перемещение
[Ноmе]	В начало текущей строки
[Ctrl+Home]	В ячейку А1
[Ctrl+End]	В последнюю заполненную ячейку таблицы
[↑]	На одну ячейку вверх
[↓]	На одну ячейку вниз
[→]	Ha одну ячейку вправо

Клавиша	Перемещение
[←]	На одну ячейку влево
[Ctrl+↑]	Вверх до первой заполненной ячейки
[Ctrl+↓]	Вниз до первой заполненной ячейки
[Ctrl+→]	Вправо до первой заполненной ячейки
[Ctrl+←]	Влево до первой заполненной ячейки
[Page Up]	Вверх на один экран
[Page Down]	Вниз на один экран
[Alt+Page Up]	Влево на один экран
[Alt+Page Down]	Вправо на один экран
[Ctrl+Page Up]	К следующему листу рабочей книги
[Ctrl+Page Down]	К предыдущему листу рабочей книги

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

Ввод данных завершается нажатием клавиши [Enter] или кнопки с изображением галочки в строке формул. Намного удобнее для завершения ввода использовать клавишу управления курсором. В этом случае после ввода можно сместиться и нужном направлении. Отменить ввод данных позволяют клавиша [Esc] и кнопка с крестиком в строке формул. Введенные числовые значения автоматически выравниваются по правому краю, а текстовые данные — по левому краю. Если длина введенного в ячейку текста превышает ширину этой ячейки, то после завершения ввода текст либо полностью отображается в таблице, закрывая, расположенные справа пустые ячейки, либо урезается по правому краю ячейки, если соседняя ячейка содержит какую-либо информацию. Урезанный текст полностью отображается только в строке формул.

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

Если в ячейку вводится формула, то сразу по завершении ввода выполняются вычисления, и в ячейке отображается результат вычислений. Формула в Excel должна начинаться с математического оператора (например, со знака равенства ). Так, после ввода формулы =1+6 в ячейке появится число 7, но в строке формул отобразится фактическое содержимое ячейки — формула. Запись 1+6 программа интерпретирует как текст.

Вычисление параметров вариационного ряда рассмотрим на примере данных суточного удоя коров. Пример. Имеется выборка (n=100). Требуется разнести по классам данные суточного удоя. Для построения вариационного ряда, прежде всего следует найти лимиты — минимальное и максимальное значения вариант. В приведенной выборке они выделены.

Суточный удой коров

Таблица 2. Пример выборки

21,9	21,4	27,7	17,0	12,3	21,7	23,4	25,7	21,2	20,3
23,8	24,1	25,9	21,4	20,7	18,5	22,5	23,0	18,5	25,7
20,1	21,3	15,7	24,8	19,3	22,2	22,9	14,9	26,1	20,5
14,6	27,8	22,4	16,7	22,9	25,3	22,7	19,7	15,2	21,3
22,1	20,5	19,7	24,5	29,6	22,3	19,1	23,5	25,9;	17,2
15,5	18,1	23,9	25,4	20,4	13,2	19,6	24,4	18,2	24,8
24,2	20,9	21,0	16,5	20,9	23,2	27,2	21,1	26,3	18,6
17,2	17,8	31,2	25,0	20,7	18,3	23,7	16,1	16,2	21,6
23,0	20,7	25,3	13,9	17,3	21,8	14,1	19,0	21,9	18,7
28,5	21,2	19,9	24,8	22,7	16,4	20,6	23,5	22,2	19,5

Числовые значения вводим в рабочее поле Excel начиная с ячейки В2 до В11, далее С2-С11 и т.д. до ячейки К11. В итоге получим таблицу состоящую из 10 строк и 10 столбцов. В ячейку В1 можно ввести название данных текстом, например: написать «Суточный удой коров».

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

Решение:

• 1)    количество данных – в ячейку В13 (обозначение ячеек вводят латинским шрифтом) введем = СЧЕТ(В2:К11), затем нажимаем Enter, в ячейке появится цифра 100. Что означает, всего данных сто.

• 2)    вычисление минимального и максимального значений ряда – в ячейку В14 введем =МИН(В2:К11) «Enter», а в ячейку В15 =МАКС(В2:К11) «Enter». В ячейках В14 и

• В15 появятся цифры 12,3 и 31,2.

• 3)    вычисление остальных величин:

в ячейку В16 введем =СРЗНАЧ (В2:К11) «Enter» – это среднее значение;

среднее квадратическое отклонение – в ячейку В17 введем = ДИСПРА(В2:К11) «Enter»; значение сигма – в ячейку В18 введем =КОРЕНЬ(В17) «Enter»;

ошибка средней – в ячейку В19 введем =В18/КОРЕНЬ(В13) «Enter»;

коэффициент вариации (в %) – в ячейку В20 – введем =(В19*100)/В16 «Enter».

Определение границ доверительного интервала. Определим доверительный интервал для генерального среднего с уровнем надежности 95%. Исходные данные: объем выборки – 100, среднее – 21,237, стандартное отклонение σ – 3,734.

Решение с помощью функции Лапласа.

• 1)    По таблице значений функции Лапласа найдем значение ^t , которое удовлетворяет условию

2Ф(t)=γ,(1)

, , V

Ф (t) = t т.е.                                                           2,(2)

Ф (t) = ⁰⁹⁵ = 0,475

2            .(3)

Мы увидим, что t - ^1,96 .

Если под рукой нет учебника с таблицей функции Лапласа, то можно воспользоваться программой Excel. Применим для вычисления ^t формулу …. первой главы:

Ф(t)=НОРМСТРАСП(t)-0,5.

Мы должны вместо уравнения Ф(t)=0,495 рассмотреть уравнение

НОРМСТРАСП(t)-0,5=0,475

или найти t из соотношения

НОРМСТРАСП(t)=0,977.

Воспользуемся обратной функцией для НОРМСТРАСП(t).

Введем в произвольную ячейку программы Excel (например, G14) формулу

=НОРМСТРАСП(0,975), получим значение 1,959963985, что совпадает приближенно с табличным значением t = 1,96.

• 2)    Теперь найдем границы доверительного интервала

  Л = t      1,96 ^3,734   0,731

  
  

n

Введем в ячейку G16 = ДОВЕРИТ(0,05;3,73;100) «Enter», получим 0,731.

Тогда

хt      x -Л = 21,237 0,731  20,506

n

х+t x +л = 21,237 + 0,731  21,97

n

Рис. 2. Параметры вариационного ряда

Доверительный интервал имеет вид:

(20,506 <Х<21,97). В приведенном примере можно в ячейки записывать и пояснения, выбор ячеек для ввода чисел может быть любым.

Решенное задание имеет вид (рис. 2).

Вычисление коэффициента корреляции

Для примера возьмем таблицу суточного удоя и жирности молока. В ячейки В3, В4, В5 и до В102 вводим значения суточного удоя в кг, а в ячейки D3 до D102 значения жирности молока (рис. 3).

Рис. 3. Вычисление коэффициента корреляции (1-й способ – ввод всех данных в два столбца, на рисунке показаны только первые 20 значений)

Затем в ячейку G11 вводим = КОРРЕЛ(C3:C102;D3:D102) «Enter» получим в этой ячейки значение коэффициента корреляции – 0, 572.

Для более компактного ввода данных можно их вводить как на Рис.3

Тогда в ячейку Е22 вводим = КОРРЕЛ(C3:H19;I3:N19) «Enter». Получим значение 0,62699.

Для проверки значимости коэффициента корреляции вычислим по формуле значение

Тнабл.

Для этого введите в ячейку Е24 формулу=Е22*КОРЕНЬ((100-2)/(1-Е22^2)).

Получим Тнабл=-7,967.

В ячейку Е26 введите формулу =СТЬЮДРАСПОБР(0,01;98), получим значение критерия Стьюдента 2,626.

Так как Тнабл=-7,967>tкр(α;κ)=2,6, нулевую гипотезу отвергаем, т.е. делаем вывод: коэффициент корреляции значим с уровнем доверия 99% (1-0,01=0,99).

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

Рис. 4 Вычисление коэффициента корреляции (2-й способ – ввод всех данных по столбцам, последовательно)

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