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

Авторы: Гончар-Зайкин П.П., к.б.н.; Чертов В.Г., к.э.н.

Полный текст на сайте http://www.sniish.ru/proposal_desc.php?id=8

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

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

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

1. Скачать с сайта разработчиков

2. Скачать с нашего сайта

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 П.П. Гончар-Зайкин, В.Г. Чертов.