Как посчитать нср в excel

Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов

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

Для изучения связи между значениями – факторный метод. Рассмотрим подробнее аналитические инструменты: факторный, дисперсионный и двухфакторный дисперсионный метод оценки изменчивости.

Дисперсионный анализ в Excel

Условно цель дисперсионного метода можно сформулировать так: вычленить из общей вариативности параметра 3 частные вариативности:

• 1 – определенную действием каждого из изучаемых значений;
• 2 – продиктованную взаимосвязью между исследуемыми значениями;
• 3 – случайную, продиктованную всеми неучтенными обстоятельствами.

В программе Microsoft Excel дисперсионный анализ можно выполнить с помощью инструмента «Анализ данных» (вкладка «Данные» — «Анализ»). Это надстройка табличного процессора. Если надстройка недоступна, нужно открыть «Параметры Excel» и включить настройку для анализа.

Работа начинается с оформления таблицы. Правила:

1. В каждом столбце должны быть значения одного исследуемого фактора.
2. Столбцы расположить по возрастанию/убыванию величины исследуемого параметра.

Рассмотрим дисперсионный анализ в Excel на примере.

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

Внесем данные в таблицу Excel:

1. Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся списке выбираем «Однофакторный дисперсионный анализ» и нажимаем ОК.
2. В поле «Входной интервал» ввести ссылку на диапазон ячеек, содержащихся во всех столбцах таблицы.
3. «Группирование» назначить по столбцам.
4. «Параметры вывода» — новый рабочий лист. Если нужно указать выходной диапазон на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку диапазона для выводимых данных. Размеры определятся автоматически.
5. Результаты анализа выводятся на отдельный лист (в нашем примере).

Значимый параметр залит желтым цветом. Так как Р-Значение между группами больше 1, критерий Фишера нельзя считать значимым. Следовательно, поведение в конфликтной ситуации не зависит от уровня образования.

Факторный анализ в Excel: пример

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

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

Рассмотрим на примере проведение факторного анализа. Допустим, нам известны продажи каких-либо товаров за последние 4 месяца. Необходимо проанализировать, какие наименования пользуются спросом, а какие нет.

1. Посмотрим, за счет, каких наименований произошел основной рост по итогам второго месяца. Если продажи какого-то товара выросли, положительная дельта – в столбец «Рост». Отрицательная – «Снижение». Формула в Excel для «роста»: =ЕСЛИ((C2-B2)>0;C2-B2;0), где С2-В2 – разница между 2 и 1 месяцем. Формула для «снижения»: =ЕСЛИ(J3=0;B2-C2;0), где J3 – ссылка на ячейку слева («Рост»). Во втором столбце – сумма предыдущего значения и предыдущего роста за вычетом текущего снижения.
2. Рассчитаем процент роста по каждому наименованию товара. Формула: =ЕСЛИ(J3/$I$11=0;-K3/$I$11;J3/$I$11). Где J3/$I$11 – отношение «роста» к итогу за 2 месяц, ;-K3/$I$11 – отношение «снижения» к итогу за 2 месяц.
3. Выделяем область данных для построения диаграммы. Переходим на вкладку «Вставка» — «Гистограмма».
4. Поработаем с подписями и цветами. Уберем накопительный итог через «Формат ряда данных» — «Заливка» («Нет заливки»). С помощью данного инструментария меняем цвет для «снижения» и «роста».

Теперь наглядно видно, продажи какого товара дают основной рост.

Двухфакторный дисперсионный анализ в Excel

Показывает, как влияет два фактора на изменение значения случайной величины. Рассмотрим двухфакторный дисперсионный анализ в Excel на примере.

Задача. Группе мужчин и женщин предъявляли звук разной громкости: 1 – 10 дБ, 2 – 30 дБ, 3 – 50 дБ. Время ответа фиксировали в миллисекундах. Необходимо определить, влияет ли пол на реакцию; влияет ли громкость на реакцию.

1. Переходим на вкладку «Данные» — «Анализ данных» Выбираем из списка «Двухфакторный дисперсионный анализ без повторений».
2. Заполняем поля. В диапазон должны войти только числовые значения.
3. Результат анализа выводится на новый лист (как было задано).

Та как F-статистики (столбец «F») для фактора «Пол» больше критического уровня F-распределения (столбец «F-критическое»), данный фактор имеет влияние на анализируемый параметр (время реакции на звук).

Для фактора «Громкость»: 3,16 ↑

Лабораторная работа № 4

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

Бюджет времени – 4 часа.

Количество двухчасовых занятий – 2.

Распределение бюджета времени:

— 1 час на освоение теоретических основ и принципов определения оценок наименьшей существенной разности и D-критерия Тьюки при проведении массового отбора;

— 1 час на расчеты в электронных таблицах Microsoft Excel оценок наименьшей существенной разности (НСР) в равномерном дисперсионном комплексе;

— 1 час на расчеты в электронных таблицах Microsoft Excel оценок D-критерия Тьюки (D) в равномерном дисперсионном комплексе;

— 1 час на расчеты в электронных таблицах Microsoft Excel оценок наименьшей существенной разности (НСР) и оценок D-критерия Тьюки (D) в неравномерном дисперсионном комплексе;

Дидактический материал, необходимый для проведения данной лабораторной работы, приведен в файлах электронных таблиц Excel – «НСР» (Приложение – 4.1).

Вводная часть

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

Надежным средством оценки существенности различий между сравнительно изучаемыми объектами лесной селекции выступают критерии существенности различий: наименьшая существенная разность (НСР) и критерий Тьюки (D).

«Оценка значимости разности между выборочными средними по наименьшей существенной разности (НСР)»

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

t – критерий Стьюдента (табличное значение для конкретного числа наблюдений и заданного уровня значимости);

s_d – ошибка разности средних (вычисляется по материалам дисперсионного анализа, на основе оценки остаточной или средовой дисперсии).

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

1. По данным дисперсионного анализа вычисляют обобщенную ошибку средней:

2. Вычисляют ошибку разности средних для неравномерных комплексов:

n₁ = численность первой из двух сравниваемых совокупностей (выборок, популяций, клонов, пулусибсовых семей и т.п.), участвующих в дисперсионном анализе;

n₂ = численность второй из двух сравниваемых совокупностей;

s_d = ошибка разности средних двух совокупностей дисперсионного комплекса;

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

Данный алгоритм используют при парном сравнении значений анализируемых объектов: каждый из объектов сопоставляется с каждым из остальных, в результате чего формируется комплекс значений НСР.

В случае равенства численностей сравниваемых групп формула примет более простой вид:

n = численность групп (классов, совокупностей) при реализации равномерной схемы дисперсионного анализа, в которой численности всех групп равны;

s_d = средняя ошибка разности средних для всего дисперсионного комплекса (разность двух средних вычисляется при их парном сравнении для всех возможных вариантов сопоставления групп объектов данного дисперсионного комплекса, ошибка вычисляется для всего комплекса в целом).

Вычисленная по данному алгоритму величина s_d является общей для всего равномерного дисперсионного комплекса статистикой. Действительно, в формуле её расчета использованы только обобщенные (для всего равномерного дисперсионного комплекса) величины: σ_z – остаточная (средовая, случайная) дисперсия; n – численность групп при реализации равномерной схемы. Полученная на её основе величина НСР также будет единой и общей для всего равномерного дисперсионного комплекса. Её значение будет использовано для всех случаев парного сравнения и вычисления разности каждого из объектов дисперсионного комплекса с каждым из других.

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

3. Подставляя значение ошибки разности средних в формулу расчета НСР, получим (в абсолютных и относительных величинах):

t₀₅ = табличное значение критерия Стьюдента;

s_d = средняя ошибка разности средних;

M_c = обобщенное среднее значение признака для всего дисперсионного комплекса.

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

Задание 1. Рассчитайте величину наименьшей существенной разности (НСР) по дидактическому материалу, предложенному преподавателем:

— для равномерного дисперсионного комплекса (файл Excel, лист «Равномерный»: Приложение);

— для неравномерного дисперсионного комплекса (файл Excel, лист «Неравномерный»: Приложение);

Исходным дидактическим материалом для данной работы служит файл Excel «Коэффициент наследуемости» или «НСР» Лист 1 для равномерных дисперсионных комплексов и Лист 3 для неравномерных дисперсионных комплексов. В них для работы используют собственно исходные таблицы данных и перечень вычисляемых промежуточных и итоговых статистик.

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

«Оценка значимости (существенности) разностей между выборочными средними по величине D-критерия Тьюки»

Теоретическая платформа применения D-критерия Тьюки для оценки наименьшей существенной разности. Исследования Дж. Тьюки показали, что при числе вариантов больше двух оценка различий между средними по НСР дает несколько преувеличенное количество существенных различий. В связи с этим он предложил метод сравнения выборочных разностей средних с величиной D = Q×s_x. Этот критерий получают путем умножения обобщенной ошибки средней на множитель Q. Обобщенную ошибку средней вычисляют по результатам дисперсионного анализа, а значения коэффициента Q берут из таблицы стандартных значений.

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

D-критерий Тьюки является более чувствительным по сравнению с НСР, так как он базируется не только на числе степеней свободы остаточной дисперсии (как НСР для выбора табличного значения t-критерия Стьюдента), но учитывает и число вариантов в опыте (число градаций действующего фактора в дисперсионном комплексе), в нашем случае при популяционных исследованиях число вариантов соответствует числу растений в выборке, при анализе селекционного качества плюсовых деревьев – их числу.

Особенностью D-критерия Тьюки, отличающей его от критерия НСР, является и то, что D-критерий Тьюки рассчитывается как единая величина для всего дисперсионного комплекса. Это вполне логично, поскольку в основе его расчета лежит единая и общая для всего дисперсионного комплекса (для всех входящих в него групп, существенность различий между которыми вычисляют) величина – остаточная дисперсия, которая используется для вычисления величины обобщенной ошибки среднего в дисперсионном комплексе.

Задание 2. Рассчитайте величину D-критерия Тьюки для равномерного и неравномерного дисперсионных комплексов по дидактическому материалу, предложенному преподавателем.

Исходным дидактическим материалом для данной работы служат файлы Excel «Коэффициент наследуемости» или «НСР» Лист 1 для равномерных дисперсионных комплексов и Лист 3 для неравномерных дисперсионных комплексов. В них для работы используют собственно исходные таблицы данных и перечень вычисляемых промежуточных и итоговых статистик.

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

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:

Методические указания по Excel

Для построения теоретической линии и уравнения регрессии подведем курсор мышки к данным графика (фактическим точкам жира), при нажатии на правую клавишу появляется контекстное меню, выберем Добавить линию тренда (рис. 7.7) Рис. 7.7. Контекстное меню для работы с данными. После выбора Добавить линию тренда появляется всплывающее окно с параметрами линии тренда. Так как мы предполагаем, что наша зависимость носит прямолинейный характер, для аппроксимации и сглаживания эмпирической линии выберем Линейная. Выберем автоматическое сглаживание, галочкой укажем показать уравнение на диаграмме и поместить R^2 (рис.7.8). Рис. 7.8. Формат линии тренда. 41

После выбора параметров линии тренда на листе 8 в автоматическом режиме получаем график зависимости между содержанием жира и массой зерна с наименованием осей У. Х и расшифровкой легенды графика (рис. 7,9). На графике голубыми кубиками отмечено фактическое содержание жира, красными ромбиками предсказанное или теоретическое содержание жира. На графике показано уравнение регрессии: Y= 0,34X – 2.68. Как из таблицы на рис.7.5, так и из этого уравнения видно, что коэффициент регрессии составляет 0,34%. Данный коэффициент свидетельствует, что увеличении массы зерна на 1 г, содержание жира увеличивается на 0,34%. Рис. 7.9. График зависимости между содержанием жира и массой зерна. 8. Дисперсионный анализ данных однофакторного вегетационного и полевого опытов с полной рандомизацией вариантов» Работа 10 . Однофакторный дисперсионный анализ Пример. Влияние азотных удобрений на урожайность овса, г/сосуд

Варианты опыта	Повторность
	1	2	3	4
1.					Без удобрения (st)	15,8	15,5	16,1	15,0
2.	Аммиачная селитра	29,3	30,4	28,1	31,6
3.	Сульфат аммония	25,8	26,8	25,9	24,7
4.	Мочевина	25,7	24,0	23,8	25,7

1. В активный лист программы Excel введем исходные данные вышеприведенного примера, расположив таблицу в следующем виде (рис.8.1): 42

Рис. 8.1. Исходные данные 2. Из Пакета анализа выберем инструмент Однофакторный дисперсионный анализ Рис. 8.2. Диалоговое окно Однофакторный дисперсионный анализ . 3. В появившемся окне укажем входной интервал А3:E6. Входной интервал должен включать только диапазон, состоящий из перечня вариантов и цифровых данных по этим вариантам (рис.8.2). 4. Группирование по строкам (рис.8.2). 5. Укажем метки в первом столбце (рис.8.2). Это необходимо для того, чтобы в выходных таблицах автоматически печатались наименования вариантов. 6. Альфа – выбор уровня значимости 0,05 или 0,01 7. Выбираем выходной интервал для размещения результатов дисперсионного анализа: на данном листе или новом листе и нажимаем ОК (рис. 8.2.) 8. Получаем таблицу дисперсионного анализа «Однофакторный дисперсионный анализ» (рис.8. 3.) 43

Рис. 8.3. Таблица дисперсионного анализа 9. В первой итоговой таблице Excel под Группами подразумевается «Варианты», Счет – это повторность каждого варианта (n =4). 10. Во второй таблице термины и формулы подразумевают: Между группами – «Варианты», Внутри групп – «Остаток», SS – сумма квадратов отклонений ( СКО или С y ), df – степени свободы, MS – средний квадрат отклонений или дисперсия – S 2 . 11. F ф = 135,08; F 05 = 3,49 Так как F ф > F 05 , H 0 ≠0, нулевая гипотеза отвергается – в опыте в целом есть существенные различия, поэтому необходимо рассчитать НСР. В Пакете анализа программы Excel, а также в большинстве других статистических пакетов не предусмотрена оценка существенности средних по НСР – четвертый этап дисперсионного анализа, поэтому ошибку разности ( S d ) можно рассчитать для нашего примера следующим образом:

2 S	2	2 1,0725	0,73
S	e	HCP		=t	05	∙*S	d	= 2,18∙ 0,73 = 1,59 г/ сосуд
	d
n		4	05

t 05 = 2,18 при df (cce) = 12 степенях свободы для остатка К сожалению, необходимо отметить, что в Пакете данных программы Excel невозможно провести дисперсионный анализ как однофакторного полевого опыта, заложенного методом организованных повторений (Работа 11), так и многофакторного полевого опыта с организованными повторениями и расщепленными делянками (Работа 12). Инструменты в Пакете анализа «Двухфакторный дисперсионный анализ с повторениями и без повторений» предназначены для обработки данных двухфакторного вегетационного опыта (опыта с независимыми выборками). 44

9. Дисперсионный анализ данных двухфакторного вегетационного и полевого опытов с полной рандомизацией вариантов. Двухфакторный дисперсионный анализ с повторениями. Пример. В полевом опыте, проведенном методом полной рандомизации (независимые выборки) изучается два фактора: фактор А – полив в 2-х градациях (а 0 – без полива, а 2 – полив), фактор В – дозы минеральных удобрений (в 1 – NPK в 2 – 2NPK в 3 – 3NPK). Опыт проведен в 4- х кратной повторности (n=4). Урожай зерна ячменя в двухфакторном опыте 2х3, ц/га

Орошение, А	Удобрения, В
	NPK	2NPK	3NPK
				24,1	28,4	28,7
Без полива	25,8	29,7	30,4
	23,0	30,1	32,0
				27,0	27,4	27,0
30,7	46,7	59,4
Полив	34,4	45,4	50,7
	34,0	47,1	64,5
31,0				46,3	60,1

1. В активный лист программы Excel введем исходные данные вышеприведенного примера, расположив таблицу в следующем виде (рис.9.1): Рис. 9. 1 Исходные данные 2. Из Пакета анализа выберем инструмент Двухфакторный дисперсионный анализ с повторениями 45

3. В появившемся окне укажем входной интервал А1:D9. Входной интервал должен включать только диапазон, состоящий из перечня вариантов и цифровых данных по этим вариантам (рис.9. 2). 4. В окне Число строк для выборки – укажем 4 (это повторность опыта) (рис.9.2). 5. Альфа – выбор уровня значимости 0,05 или 0,01 7. Выбираем выходной интервал для размещения результатов дисперсионного анализа: выбираем на новом листе и нажимаем ОК (рис. 9.2.) 8. Получаем таблицу дисперсионного анализа «Двухфакторный дисперсионный анализ с повторениями» (рис. 9.3.) 46

Рис.9. 3. Таблица дисперсионного анализа 9. В первой итоговой таблице Excel представлены суммы и средние значения по факторам А и В, которые удобнее представить в виде обобщенной таблицы средних по изучаемым вариантам. Счет – это повторность каждого варианта (n =4). 10. Во второй таблице термины и формулы подразумевают: Выборка – «Фактор А», Столбцы – «Фактор В», Взаимодействие – «Взаимодействие АВ», Внутри – «Остаток». SS – сумма квадратов отклонений ( СКО или С y ), df – степени свободы, MS – средний квадрат отклонений или дисперсия – S 2 . 11. С помощью двухфакторного дисперсионного анализа по критерию Фишера оценивается отдельно существенность изучаемых факторов и их взаимодействия. В нашем примере для фактора А F ф = 249,78; F 05 = 4,41 , для фактора В F ф = 60,66; F 05 = 3,55, для взаимодействия АВ F ф = 29,85; F 05 = 3,55. Так как F ф > F 05 , H 0 ≠0, нулевая гипотеза отвергается – действие и взаимодействие полива и удобрений значимо на 5% ном уровне значимости. Для оценки существенности разности средних необходимо рассчитать НСР.

2 S 2

2 7,8332

1,98

t

S

2,10 1,98 4,15

S

d

e

HCP

d

ц/га.

n

4

05

05

t 05 = 2,10 при df (cce) = 18 степенях свободы для остатка 47

S A	2 S 2			2 7,8332			1,14		HCP A	t	S A 2,10 1,14 2,39 ц/га.
	e
	d			n	b								4 3			05		05	d
2 S 2
				S B		2 7,8332				1,40		HCP B	t	S A 2,10 1,39 2,93 ц/га.
e
d			n	a			4 2			05		05	d
Итоговая таблица
Фактор А –							Фактор В – удобрения							В среднем по
полив			NPK			2NPK	3NPK	фактору А
Без полива							25,0			28,9	29,5	27,8
Полив			32,5			46,4	58,7	45,9
В среднем по							28,8			37,6	44,1
фактору В
				HCP 4,15 ; HCP A 2,39															; HCP B	2,93
05	05		05
С помощью HCP 4,15															оцениваются различия между частными средними (с
05

поливом и без полива при разных дозах удобрений: 28,9 – 25, 0; 58,7 – 29,5; 46,4 – 32,5 и т.д.).

HCP A	2,39 оценивает только главный эффект фактора А ( 45,9 – 27,8), а
05
HCP B	2,93 различия главного эффекта фактора В ( 44,1 – 28,8; 37,6 – 28,8; 44,1 –
05
37,6).

Рекомендуемая литература 1. Microsoft Excel – Викиучебник. http://ru.wikibooks.org/wiki/Microsoft_Excel 2. Макарова Н.В., Трофимец В.Я. Статистика в Excel: Учеб. пособие.– М.: Финансы и статистика, 2002. – 368 с.: ил. 3. Мурашкин С.В., Николаева З.В. Методы учётов и статистическая обработка экспериментальных данных при использовании программы Microsoft Еxcel на примере исследований сосущих вредителей яблони. — Великие Луки: Редакционно-издательский отдел ФГОУ ВПО «Великолукская ГСХА», 2006, 120 с. 4. Обработка экспериментальных данных в MS Excel : методические указания к выполнению лабораторных работ для студентов дневной формы обучения / сост. Е. Г. Агапова, Е. А. Битехтина. – Хабаровск : Изд-во Тихоокеан. гос. унта, 2012. – 32 с. 49

Учебное издание Усманов Раиф Рафикович ВЫПОЛНЕНИЕ ЗАДАНИЙ ПО КУРСУ «ОСНОВЫ НАУЧНЫХ ИССЛЕДОВАНИЙ В АГРОНОМИИ» В ПРОГРАММЕ «EXCEL» Методические указания Подписано в печать 2013 г. Формат Усл. печ. л. Тираж 120 экз. Зак. Издательство РГАУ – МСХА имени К.А. Тимирязева 127550, Москва, Тимирязевская ул., 44 Тел.: 977-00-12, 977-26-90, 977-40-64 50

Надстройка к Excel для статистической оценки и анализа результатов полевых и лабораторных опытов

Нами разработан пакет программ AgCStat в виде надстройки Excel.

В настоящее время пакет включает 12 программ плюс лист с примерами подготовки данных для анализа:

• получение табличных значений критериев Фишера и Стьюдента;
• восстановление выпавших данных
• вычисление статистик выборки;
• однофакторный дисперсионный анализ полевых опытов по Б.А. Доспехову;
• двухфакторный дисперсионный анализ полевых опытов по Б.А. Доспехову;
• двухфакторный дисперсионный анализ неравномерного комплекса по Н.А. Плохинскому;
• трехфакторный дисперсионный анализ равномерного комплекса (оригинальный алгоритм авторов);
• одно, двух и трех факторный анализ качественных признаков по Н.А. Плохинскому;
• парная корреляция и регрессия с полным статистическим анализом результатов;
• оценка разности средних по критерию Стьюдента.

3. Если первые две ссылки не работают, Вы можете скачать Эксель файл AgCStat

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

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

Во-вторых, в версиях Excel младше Excel 2002 ряд функций либо отсутствуют, либо они не доступны, как, например, функции GetFisher и GetStudent – выдающих табличные значения критериев.

В-третьих, и, может быть самое главное, — это типизация. При просмотре «Примеров подготовки данных» видно, что все таблицы данных для анализов выполняются по одному типу, тогда как в стандартном Пакете анализа таблица данных для однофакторного комплекса строится по одному типу, а для двухфакторного — совсем по другому, понять который совсем не просто. По одному же типу построены и все диалоговые окна надстройки AgCSTAT (строка в меню Сервис – CXSTAT). Вся терминология, используемая в пакете, полностью соответствует терминологии принятой в отечественной литературе.

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

Дадим некоторые пояснения по пакету программ.

Восстановление выпавших данных. Выбраковка делянки полевого опыта – обычное дело. Причины самые разные от градобоя до воровства и потравы. Узнать количество пропавшего в принципе нельзя, но вычислить величину, которая не нарушая статистических характеристик комплекса, восстановит его ортогональность для проведения некоторого формального анализа можно [3, 6]. Прием восстановления выпавшего данного применяется и тогда, когда некоторое данное резко отличается от соседних, однако пользоваться этим приемом следует с большой осторожностью и в купе с другими видами анализов о принадлежности данного к выборке.

Напомним, что алгоритмы Б.А. Доспехова привязаны к схеме закладки полевого опыта и повторения рассматриваются как фактор. В связи с этим, обратим внимание на то, что если в диалоговом окне «Однофакторный дисперсионный анализ по Доспехову» установить опцию «Опыт в вегетационных сосудах …», т.е. перейти к общей схеме дисперсионного анализа, то мы получим результаты, совпадающие как с результатами «по Плохинскому», так и однофакторного дисперсионного анализа пакета «Анализ данных».

В доступной нам литературе, мы не нашли четкого алгоритма трехфакторного дис-персионного анализа для количественных признаков (равномерного комплекса), но, поскольку необходимость в нем высока, разработали его сами, опираясь на алгоритмы Н.А. Плохинского [5].

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

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

Иногда исследователя интересует всего лишь величина разности средних двух выборок и ее достоверность. Эту задачу решает последняя в списке программа. Достаточно указать диапазоны, в которых находятся выборки, диапазоны могут быть как смежными, так и несмежными и даже располагаться на разных листах книги Excel.

Для установки книги надстройки на ПК достаточно иметь дискету с двумя файлами:AgCStat.xla и SetUp.exe. Вы запускаете файл SetUp.exe, а все остальное делается в автоматическом режиме. По завершению установки в списке надстроек Excel (меню Сервис — Надстройки, окно Надстройки) появится новая строка: “Agcstat”. Для начала работы с надстройкой ее нужно активизировать, установкой флажка.

Теперь в меню Сервис видим команду СХSТАТ, щелкаем по ней мышкой и на экране монитора появится диалоговое окно с перечнем программ пакета. До начала работы, советуем просмотреть примеры подготовки данных (первая строка списка). Дополнительной информации для работы с пакетом не потребуется.

Важные примечания от администратора vniioh.ru:

1. Надстройка также работает в последних версиях Excel (2007 и 2010) 32-битных. Для единовременного использования надстройки необходимо распаковать архив agstat.zip в любую папку, запустить файл , подтвердить разрешение на включение макросов, и согласиться на установку надстройки. После этого на ленте справа появится вкладка «Надстройки», а в ней CXSTAT.
2. Для постоянного включения надстройки нужно скопировать файл AgCStat.xla в папку :для Excel 2007 — C:\Program Files\Microsoft Office\Office12\Library;
   для Excel 2010 — C:\Program Files\Microsoft Office\Office14\Library Открыть окно свойств папки Library и снять флажок «Только чтение». Проверить атрибуты файла AgcStat.xla флажек «Только чтение» — должен быть снят.Запустите Excel от имени администратора. Нажмите вкладку Файл (для 2007 нажать на кружок) -> пункт Параметры ->Надстройки — внизу Управление (выбрать надстройки Excel) и нажмите Перейти -> отметить галочкой Agcstat и нажмите OK
3. Если у вас возникают ошибки в работе с программой (например ошибка 6 или 9), попробуйте для расчета создать новый файл рабочей книги, и скопируйте туда чистые числовые данные (через Специальную вставку — Вставка только значения). Ошибка должна исчезнуть.Замечено, что надстройка выдаёт ошибку когда данные отформатированы или к ним применено цветовое или условное форматирование. Программа 100% РАБОЧАЯ.
4. UPD/ На 64-битных версиях Office 2010 и Office 365 (2013) запустить не удалось.

• Эрмантраут Э.Р., Гудзъ В.П. Статистический анализ результатов агрономических ис-следований в прикладной программе «EXCEL-2000». //Материалы международной научно-практической конференции «современные проблемы опытного дела», том 2, СПб, 2000, стр.13-134.
• Лапач С.Н., Чубенко А.В., Бабич П.Н. Статистические методы в медико-биологических исследованиях с использованием Excel. Киев «МОРИОН», 2000, 320 с.
• Доспехов Б.А. Методика полевого опыта. 1-5 изд. М., 1965 — 1985
• Лакин Г.Ф. Биометрия. М., Изд. «Высшая школа», 1990, 352с.
• Плохинский Н.А. Биометрия. М., Изд. МГУ, 1970, 368с.
• Снедекор Д.У. Статистические методы в применении к исследованиям в сельском хозяйстве и биологии. М., 1961
• Фишер Р.Э. Статистические методы для исследователей. М., 1958
• Митропольский А.К. Техника статистических вычислений. М., 1971.
• Уэллс Э., Хешбаргер С. Microsoft Excel 97: разработка приложений / Пер. с анг. –СПб., БХВ-Санкт-Петербург, 1998, 624с.

При использовании вышеизложенных материалов необходимо ссылаться на авторов.

Данный материал опубликован в:
Сборнике «Рациональное природопользование и сельскохозяйственное производство в южных регионах Российской Федерации» М. «Современные тетради», 2003, с.559-564 П.П. Гончар-Зайкин, В.Г. Чертов.