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

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ

РОССИЙСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ – МСХА имени К.А. ТИМИРЯЗЕВА

АГРОНОМИЧЕСКИЙ ФАКУЛЬТЕТ

КАФЕДРА ЗЕМЛЕДЕЛИЯ И МЕТОДИКИ ОПЫТНОГО ДЕЛА

ВЫПОЛНЕНИЕ ЗАДАНИЙ ПО КУРСУ «ОСНОВЫ НАУЧНЫХ ИССЛЕДОВАНИЙ В АГРОНОМИИ»

В ПРОГРАММЕ «EXCEL»

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

МОСКВА 2013

1

Выполнение заданий по курсу «Основы научных исследований в агрономии» в программе «EXCEL»: Методические указания / Р.Р. Усманов. – М.: Изд-во РГАУ– МСХА, 2013. – 47 с.

В работе изложены рекомендации по использованию программы «EXCEL» при выполнении практических занятий по курсу «Основы научных исследований в агрономии».

Предназначено для студентов агрономического факультета по направлению подготовки бакалавров ФГОС ВПО 3-го поколения и магистров 110400.62 – «Агрономия».

Рекомендовано к изданию учебно-методической комиссией агрономического факультета (протокол № от 2013 года).

© ФГБОУ ВПО РГАУ – МСХА имени К.А. Тимирязева, 2013 © Издательство РГАУ – МСХА, 2013

2

	СОДЕРЖАНИЕ
1.	Общее представление о программе Excel	4
2.	Расчеты основных статистических показателей выборки (Работа 1) в программе Excel	8
	2.1. Расчеты с использованием формул	9
	2.2. Расчеты с использованием Мастера функций	14
	2.3. Расчеты с использованием Пакета анализа	22

3.Расчеты основных статистических показателей выборки с использованием Описательной

	статистики Пакета анализа на примере работы 1	25
4.	Группировка данных, расчет статических показателей, построение гистограммы и полигона
	(Работа 2)	28
5.	Оценка двух вариантов при количественной изменчивости признаков для независимых
выборок (Работа 4А). Двухвыборочный t-тест одинаковыми дисперсиями		32
6.	Оценка средней разности при количественной изменчивости признаков для зависимых
выборок (Работа 4В). Парный двухвыборочный t-тест для средних		35
7.	Корреляционно-регрессионный анализ. Работа 6	37
8.	Дисперсионный анализ данных вегетационного и полевого опытов с полной рандомизацией
вариантов. Работа 10. Однофакторный дисперсионный анализ		42
9.	Двухфакторный дисперсионный анализ (Работа 12	45

3

1. Общее представление о программе Excel

Обращение к читателям:

Если Вы знакомы с программой Excel, можно пропустить первую и вторую главы и сразу приступить к выполнению работ с использованием Пакета анализа.

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

http://ru.wikibooks.org/wiki/Microsoft_Excel http://www.taurion.ru/excel

Запуск Excel

Для запуска Excel необходимо выполнить следующие действия:

1.Щелкнуть мышью на кнопке Пуск, которая находится в левом нижнем углу экрана.

2.В открывшемся меню выделить элемент Программы.

3.В появившемся подменю выбрать элемент Microsoft Excel.

При запуске Excel на экране монитора появляется окно (Рис.1.1.): и автоматически создается новая книга (файл) под названием Книга1. Документ Excel (файл) имеет расширение "*.xls" ("*.xlsx" в Microsoft Office Excel 2007) и называется рабочая книга. По умолчанию каждая книга состоит из трех рабочих листов, в каждый конкретный момент времени активным может быть только один рабочий лист.

Окно Excel

Рис. 1.1. Окно программы Microsoft Excel

В верхней части окна Excel расположена строка заголовка, в которой указаны имя приложения (в данном случае – Microsoft Excel) и имя книги (Книга1). В левом конце строки заголовка находится кнопка вызова системного меню, а в правом – кнопки управления окном.

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

Строка меню и строка инструментов выполняют такие же функции, что и программе Word. 4

Ниже панелей инструментов расположена строка формул. Она разделена на три части.

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

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

- Первая кнопка (с крестиком) позволяет отменить последнее действие по вводу или редактированию содержимого ячейки.

- Средняя кнопка (с галочкой) служит для подтверждения ввода данных.

- Третья кнопка (со знаком равенства «=») служит для создания формул и активизации панели различных функций.

Правая часть предназначена для отображения содержимого выделенной ячейки Excel. Данные в момент их ввода в ячейку отображаются в правой части строки формул. Для редактирования содержимого ячейки необходимо выполнить щелчок мышью в правой части строки формул. Чтобы отредактировать содержимое прямо в ячейке, следует нажать клавишу [F2] или дважды щелкнуть мышью на этой ячейке.

Окно рабочей книги

Окно рабочей книги Excel с его основными элементами показано на рис. 1.2.

Рис. 1.2. Окно рабочей книги Excel

Лист

Каждый лист представляет собой таблицу. Таблица состоит из столбцов и строк. Столбцов в листе 256, а строк 65536. (В Excel 2007 16384 столбцов и 1048576 строк). Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от "A" до "Z", затем идет "AA-AZ", "BABZ" и т.п. до "IV" (256). Строки обозначаются обычными арабскими числами. На пересечении столбца и строки находится ячейка. Каждый лист содержит 16777216 ячеек.

5

Ячейка

У каждой ячейки есть свои координаты, то есть адрес, который представляет собой название столбца и строки, на пересечении которых и находится данная ячейка. Адрес ячейки является основным элементом ссылки на ячейку. С помощью адреса можно найти любую ячейку в рабочей книге. Адрес состоит из имени столбца и номера строки (например, В2 - это ячейка, которая находится на пересечении столбца В и строки 2). Только одна из ячеек листа в текущий момент времени является активной. Так на рис. 1.2. активной ячейкой является Е10, вокруг активной ячейки видна жирная чёрная рамка с квадратиком в нижнем правом углу (маркером автозаполнения). Ячейка является наименьшей структурной единицей рабочего листа. В каждую ячейку листа можно ввести данные (текст, число, формулы). В ячейку можно поместить не более 32000 символов. Перед вводом данных ячейка должна быть активизирована. Активизация ячейки осуществляется подведением курсора на нужную ячейку и последующего щелчка на ней или вследствие размещения на ней указателя ячейки с помощью клавиш управления курсором или комбинации клавиш.

Выделение ячеек, диапазонов ячеек, столбцов и строк

Чтобы выделить одну ячейку, щелкнете на ней левой кнопкой мыши;

Чтобы выделить связный диапазон ячеек, наведите курсор мыши на ячейку, которая будет началом/концом выделенного диапазона, зажмите левую кнопку мыши, и, не отпуская ее, двигайте мышь до той ячейки, которая будет концом/началом выделенного диапазона, затем отпустите левую кнопку мыши;

Чтобы выделить весь столбец, щелкнете на заголовке столбца ("шапка" таблицы сверху);

Чтобы выделить всю строку, щелкнете на заголовке строки ("шапка" таблицы слева);

Ввод и редактирование данных в ячейках

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

Для изменения данных в ячейке, делаем ее активной и делаем на ней двойной щелчок;

Форматирование содержимого ячеек

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

(рис. 1.3.)

Рис. 1.3. Диалоговое окно Формат ячеек, вкладка Число, выбран элемент Текстовый

6

Выравнивание данных в ячейках Выравнивание текста проводится в диалоговом окне Формат ячеек на вкладке Выравнивание

(рис. 1.4.)

Рис.1.4. Выравнивание текста

Формулы в Microsoft Excel

Формулы - это инструментарий, который делает программу Excel весьма полезной для решения самых разнообразных задач. Формулы являются основным средством анализа и обработки вносимых данных. С их помощью можно производить любые математические операции и сравнивать данные. Формулой в Excel считается все, что начинается со знака "=". Для того, чтобы вставить в формулу адрес ячейки (ссылку на ячейку), не обязательно писать его вручную. Проще поставить знак «=», затем левой кнопкой щелкнуть на нужной ячейке или выделить нужный диапазон ячеек. При этом Excel подставит в формулу ссылку автоматически.

Для создания формул служат арифметические операторы:

«+» — сложение

«-» — вычитание

«*» — умножение

«/» — Деление

«^» — Возведение в степень

«%» — Процент

Результатом вычисления любого арифметического выражения будет число

Функции в Microsoft Excel

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

7

Рис.1.5. Диалоговое окно Мастер функций

2. Расчеты основных статистических показателей выборки (Работа 1) в программе Excel

Работа 1. Произведено измерение глубины вспашки (X, см) в 8 точках (n=8). Необходимо рассчитать основные статические показатели выборки

Значение

От нуля

Необходимо определить основные статистические

признака,

(А=0)

показатели (характеристики) выборки

Х,см

X

X 2

Выборочная средняя

X

x

n

15

( X )2

19

Поправка

С

22

n

24

Сумма квадратов ( X

)2 = X 2 – С =

x

21

( X

)2

23

Дисперсия

S

2

x

20

n 1

18

Стандартное отклонение S S 2

Суммы

X =

X 2=

S

Коэффициент вариации

V

100

x

Ошибка выборочной средней S

S

x

n

95%-й доверительный интервал (ДИ) для генеральной средней ():

x t05 Sx

95%-й доверительный интервал (ДИ) для всей совокупности (любого значения Х): x t05 S

Медиана – центральное значение ранжированной выборки: Ме

Статистические расчеты в программе Excel можно провести 3 способами:

8

-ввод формул в строке формул

-использование Мастера функций

-использование встроенного Пакета данных

2.1. Расчеты с использованием формул

Решение: 1. После запуска программы Excel в Лист 1 заносим таблицу исходных данных, для чего в ячейке В1 введем символ Х, в ячейке С1 – Х^2 , что означает Х2 , а в ячейках столбца В – значения глубины вспашки по 8 точкам. (рис. 2.1. )

Рис. 2.1. Исходные данные по глубине вспашки

2. Для расчета сумм квадратов отклонений необходимо, прежде всего, возвести каждое число в квадрат, а затем найти сумму квадратов. Чтобы возвести в квадрат число 15 и полученное значение записать в ячейку С2, необходимо (рис. 2.2.):

-левой клавишей мыши одним кликом активизировать ячейку С2,

-в строке формул нажмите кнопку со знаком равенства =

-в строке формул после знака равенства = ввести формулу В2^2

При этом введенная формула автоматически отображается в ячейке С2 и одновременно синим цветом выделяется контур ячейки В2. (рис. 2.2.)

Рис. 2.2. Формула – возведения в квадрат

9

После нажатия на клавишу Enter или нажатия на левую клавишу мыши формула закрепляется для данной ячейки и в ячейке С2 получаем результат возведения в квадрат 15 – 225. (рис.2.3. ) Теперь, при введении любого значения в ячейку В2, в ячейке С2 будет получен квадрат этого числа.

Рис. 2.3. Квадрат числа значения ячейки В2 в ячейке С2 (225).

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

С помощью мыши активизируем ячейку С2, левой клавишей подводим к нижнему правому углу выделенного прямоугольника ячейки, появляется значок +, закрепляем левой клавишей мыши и нажимая на нее, выделяем диапазон ячеек С2:С9 (Рис. 2.4. ) – в выделенных ячейках получаем квадраты по всем значениям.

Рис. 2.4. Возведение в квадрат и суммирование

10