лабораторная работа 9. Статистика в Excel Графическое представление выборки

• Название: Статистика в Excel Графическое представление выборки
• Дата: 28.05.2022
• Размер: 215 Kb.
• Имя файла: лабораторная работа 9.doc
• Тип: Документы; #554555

Статистика в Excel

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

Электронные таблицы Excel имеют огромный набор средств для анализа статистических данных. Наиболее часто используемые статистические функции встроены в основное ядро программы, то есть эти функции доступны с момента запуска программы. Другие более специализированные процедуры статистической обработки данных входят в дополнительную подпрограмму, называемую Пакетом анализа. Команды и функции Пакета анализа называются Инструментами анализа. Мы ограничимся изучением нескольких основных встроенных статистических функций и наиболее часто используемых Инструментов анализа.
§1.1. Выборки, варианты и частоты.

Группировка данных
Выборки, варианты и частоты.

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

Для статистической обработки результаты исследования объектов представляют в виде чисел x₁, x₂, …, x_k. Если значение x₁ наблюдалось n₁ раз, значение x₂ наблюдалось n₂ раз, и т.д., то наблюдаемые значения x_i называются вариантами, а числа их повторений n_i называются частотами. Процедура подсчета частот называется группировкой данных.

Объем выборки n равен сумме всех частот n_i:

. (1.1)

Относительной частотой значения x_i называется отношение частоты этого значения n_i к объему выборки n:

. (1.2)

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

			…
			…

Распределением относительных частот называется перечень вариант и соответствующих им относительных частот.
Статистическое определение вероятности.

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

Свойство статической устойчивости относительной частоты: при проведении в одинаковых условиях опытов из большого числа испытаний относительная частота события колеблется (группируется) около некоторой постоянной величены.

Статическое определение вероятности события: статической вероятностью события (или вероятностью в статическом смысле) называется постоянное число, возле которого группируются относительные частоты (1.2) при повторении опытов с большим числом испытаний. Другими словами, при большом количестве повторений опыта относительные частоты стремятся к числу, называемому статической вероятностью:

. (1.3)
§1.2. Полигон и гистограмма. Пакет анализа
Полигон.

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

Полигоном частот называется ломанная, состоящая из точек (x₁, n₁), (x₂, n₂),…, (x_k, n_k). Для построения полигона частот на оси абсцисс откладываются варианты x_i, а на оси ординат – соответствующие им частоты n_i, затем полученные точки соединяются отрезками прямой.

Полигоном относительных частот называется ломанная, состоящая из точек (x₁, w₁), (x₂, w₂),…, (x_k, w_k). Для построения полигона относительных частот на оси абсцисс откладываются варианты x_i, а на оси ординат – соответствующие им относительные частоты w_i, затем полученные точки соединяются отрезками прямой.
Гистограмма.

Статистическое распределение выборки по частотам может графически изображаться с помощью гистограммы. Для ее построения все значения наблюдаемой величины разбиваются на несколько интервалов [x_i,x_i+1), называемых частичными интервалами (или карманами).

Длина i-ого частичного интервала равна h=x_i+1-x_i. Если объем выборки большой, то можно выбрать k частичных интервалов одинаковой длины h=(x_max-x_min)/k, где x_maxи x_min – наибольшая и наименьшая варианты соответственно.

Гистограммой частот называется ступенчатая фигура, состоящая из прямоугольников с высотами, равными частотам n_i, и с основаниями длины h. Для построения гистограммы частот по оси абсцисс откладываются частичные интервалы, а над ними на высоте n_i проводятся отрезки, параллельные оси абсцисс.
Пакет анализа.

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

Пакет анализа без дополнительных установок автоматически не загружается при запуске Excel. Он входит в так называемую Надстройку – набор дополнительных подпрограмм, к которым относятся, например, уже известные вам Мастер диаграмм и Мастер функций.

Для загрузки Пакета анализа необходимо:

1. 
   в Основном меню выбрать пункт Сервис;
   
2. 
   выбрать пункт Надстройки;
   
3. 
   в появившемся списке Надстроек активизировать переключатель Пакет анализа и AnalysisToolPak-VBA, затем нажать ОК.
   

После этого в меню Сервис добавится пункт Анализ данных (последний пункт). К этому пункту следует обращаться для вызова Пакета анализа. Последовательность вызова Пакета анализа в дальнейшем будет записываться в виде СервисАнализ данных.

Процедуры статистической обработки данных, входящие в Пакет анализа, называются Инструментами анализа. Эти инструменты позволяют: быстро вычислить основные точечные характеристики выборки (Описательная статистика); построить гистограмму (Гистограмма); провести прямую линейной регрессии и вычислить ее коэффициенты (Регрессия); провести проверки статистических гипотез о равенстве дисперсий (Двухвыборочный F-тест для дисперсии), о равенстве средних в различных случаях (Двухвыборочный t-тест с одинаковыми дисперсиями, Двухвыборочный t-тест с различными дисперсиями, Парный двухвыборочный t-тест для средних) и многое другое.

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

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

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

1. 
   в начале задать частичные интервалы разбиения;
   
2. 
   затем в меню Сервис выбрать команду Анализ данных и указать инструмент анализа – Гистограмма и нажать ОК;
   

Вид диалогового окна Гистограмма:



В этом окне следует указать:

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

После нажатия на ОК, инструмент Гистограмма выводит два столбца: Карман и Частота. В столбце Карман дублируются заданные ранее частичные интервалы, а в столбце Частота содержаться вычисленные для каждого частичного интервала частоты. Сама гистограмма выводится правее столбца частот. Форматирование гистограммы производится так же, как и любой диаграммы в Excel.

Замечание: При построении гистограммы необходимо задавать частичные интервалы самим. Автоматизировать эту процедуру можно, если воспользоваться автозаполнением, для чего следует набрать минимальное значение выборки, выделить эту ячейку и выполнить команду ПравкаЗаполнитьПрогрессия. В появившемся диалоговом окне Прогрессия установить длину частичного интервала в поле Шаг и максимальное значение выборки в поле Предельное значение.

В Excel также имеются стандартные функции нахождения минимального и максимального значений выборки (МАКС и МИН), о которых будет идти речь в следующей главе.

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

	5	6	7	8	9	10	11	12
	4	7	13	15	7	9	6	5

Вычислить относительные частоты. Построить полигоны частот и относительных частот.

Для выполнения этого задания проделайте следующие пункты.

1. В ячейку А1 наберите название столбца, который будет содержать данные : «Варианты». Заполните данными ячейки А2:А9.

2. В ячейку В1 наберите название столбца, который будет содержать данные : «Частоты». Заполните данными ячейки В2:В9.

3. Вычислите объем выборки. Для этого в ячейку А10 наберите: «n=» и выровняйте ее содержимое по правому краю (нажать на соответствующую кнопку выравнивания абзаца По правому краю на панели инструментов). Поместите курсор в ячейку В10 и нажмите на кнопку Автосумма, а затем на Enter. В результате в этой ячейке появится сумма всех частот (сумма чисел диапазона В2:В9). Выровняйте это число по левому краю. Должно получиться n=66.

4. Вычислите относительные частоты. Для этого поместите курсор в ячейку С2 и наберите в ней формулу: =B2/$B$10 (ссылка на объем выборки должна быть абсолютной!). Выделите эту ячейку и скопируйте набранную формулу с помощью Маркера заполнения в ячейки от С2 до С9 включительно.

5. В ячейку С1 наберите заголовок вычисленных данных: «Относительные частоты» и сделайте Автоподбор ширины столбца.

6. Если эти данные необходимо вывести на печать, то желательно сделать обрамление этой таблицы. Для этого выделите диапазон с данными, включая заголовок, А1:С9, затем выполните команду ФорматЯчейкиГраница. В появившемся диалоговом окне нажмите на кнопки Внешние и Внутренние, а затем на ОК.

7. Теперь постройте полигон частот. Его можно быстро построить с помощью обычного Мастера диаграмм. Для этого выделите диапазон ячеек А2:В9 и вызовите Мастер диаграмм.

8. В появившемся диалоговом окне Мастера диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в группе Тип выберите Точечная, а затем в группе Вид выберите Точечная диаграмма, на которой значения соединены отрезками. Для того, чтобы посмотреть как будет выглядеть полигон на данном этапе, прижмите мышью кнопку Просмотр результата в этом же диалоговом окне. В результате на месте группы Вид появится поле Образец, в котором будет показан полигон. Он будет виден, пока прижата кнопка Просмотр результата.

9. Видно, что по умолчанию диаграммы в Excel строятся на сером фоне с горизонтальными линиями сетки. Это не очень подходит для вывода на печать. Если полигон необходимо будет распечатать, то желательно его подредактировать. Для этого нажмите кнопку Далее.

10. В следующем окне Мастера диаграмм (шаг 2 из 4): источник данных ничего менять не нужно, только убедитесь, что отмечено Ряды в столбцах, и сразу нажмите Далее.

11. В следующем окне Мастера диаграмм (шаг 3 из 4): параметры диаграммы на вкладке Заголовки наберите:

• 
  в поле Название диаграммы заголовок: «Полигон частот»;
  
• 
  в поле Ось Х (категорий): название оси X: «Варианты»;
  
• 
  в поле Ось Y(значений): название оси Y: «Частоты»;
  

(из поля в поле можно переходить либо установкой курсора мышью, либо нажатием на клавишу Tab).

12. На вкладке Линии Сетки снимите галочку с переключателя Ось Y(значений): основные линии, если линии сетки вам не нужны (их рекомендуется убрать).

13. На вкладке Легенды снимите галочку с переключателя Добавить легенду и нажмите на кнопку Готово.

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

15. Для этого, чтобы убрать серый фон диаграммы, следует щелкнуть дважды мышью по серой области. В результате появится окно Формат области построения, в котором в группе Заливка отметьте Прозрачная и нажмите ОК .

16. Для того, чтобы увеличить шрифт надписей на осях следует: щелкнуть мышью по соответствующей оси, в появившемся диалоговом окне Формат оси на вкладке Шрифт установить нужные вам размер начертания и тип шрифта: задайте 10 пт; на вкладке Шкала можно изменить цену деления оси: поставьте число 3 в поле Цена основных делений.

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



18. Теперь постройте полигон относительных частот. Для этого выделите интервал ячеек с вариантами А2:А9, а затем прижмите клавишу Ctrl и мышью выделите интервал ячеек с относительными частотами С2:С9.

19. Вызовите Мастер диаграмм и проделайте все те же действия, что и при построении полигона частот, за исключением, подписей. В окне Мастер диаграмм (шаг 3 из 4): параметры диаграммы на вкладке Заголовки в поле Название диаграммы наберите: «Полигон относительных частот». Здесь же необходимо набрать другое название оси Y: «Относительные частоты», название оси Х остается такое же, как и в полигоне частот.

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

• 
  щелкнуть дважды мышью по этой оси;
  
• 
  в появившемся диалоговом окне Формат оси выбрать вкладку Число;
  
• 
  в группе Числовые форматы выбрать Числовой и установить Число десятичных знаков : 2 (оно установлено по умолчанию).
  
• 
  нажать ОК.
  

Готовый к печати полигон относительных частот должен иметь вид:


Чем отличаются построенные полигон частот и полигон относительных частот? Сохраните на диске файл с результатами выполнения этого задания.

1.2. Группировка данных и построение гистограммы с помощью Пакета анализа
В группе из 29 студентов специальности «Стоматология» получены следующие оценки на экзамене по Эндодонтии (число «0» означает, что студент не явился на экзамен, число «1» означает, что студент не допущен до экзамена):

2	5	4	2	1	1	3
4	3	3	2	0	3	4
3	2	3	1	5	5	2
4	5	3	0	4	1	2	4

Составить распределение числа студентов по оценкам и построить гистограмму этого распределения.
Для выполнения этого задания проделайте следующие пункты.

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

2. Наберите исходные данные в любом порядке, можно точно в таком, как они представлены в условии задания. Тогда эти данные займут интервал ячеек А1:Н4.

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

4. В ячейку А5 наберите минимальное значение выборки, которое легко находится и равно нулю. Нажмите Enter, а затем выделите эту ячейку.

5. Выполните команду ПравкаЗаполнитьПрогрессия. В появившемся диалоговом окне Прогрессия отметьте:

• 
  в группе Расположение переключатель По столбцам;
  
• 
  в группе Тип переключатель Арифметическая;
  
• 
  установите 1 в поле Шаг;
  
• 
  установите 5 в поле Предельное значение;
  
• 
  нажмите ОК.
  

6. В результате появятся числа от 0 до 5, заполняющие интервал А5:А10.

7. Вызовите Пакет анализа, выполнив команду СервисАнализ данных и выберите в списке Инструментов анализа необходимый вам Гистограмма. Затем нажмите ОК.

8. В появившемся диалоговом окне Гистограмма в группе Входные данные в поле Входной интервал наберите адрес интервала ячеек с данными А1:Н4 или выделите указанный интервал мышью, тогда в поле, где находится курсор, адрес выделенного интервала появится автоматически.

9. В поле Интервал карманов поместите адрес частичных интервалов А5:А10, набрав его или выделив мышью, как вам удобнее.

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

11. Отметьте галочкой поле Вывод графика и нажмите ОК.

12. В результате появится таблица из двух колонок, название которых Карман и Частота:

Карман	Частота
0	2
1	4
2	6
3	7
4	6
5	4
Еще	0

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

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

14. Выделите всю гистограмму и щелкните по ней правой кнопкой мыши. В результате появится контекстное меню, позволяющее редактировать гистограмму.

15. В контекстном меню выберите пункт Параметры диаграммы. В появившемся диалоговом окне:

• 
  во вкладке Заголовки в поле Название диаграммы наберите: «Распределение по оценкам»;
  
• 
  в поле Ось Х (категорий) наберите: «Оценки»;
  
• 
  в поле Ось Y(значений)наберите: «Число студентов»;
  
• 
  во вкладке Легенда снимите галочку с поля Добавить легенду;
  
• 
  во вкладке Подписи данных в группе Подписи значений активизируйте переключатель Значение и нажмите ОК.
  

16. Щелкните дважды мышью по серому фону гистограммы. В результате появится диалоговое окно Формат области построения. В группе Рамка активизируйте переключатель Невидимая, а в группе Заливка активизируйте переключатель Прозрачная и нажмите ОК.

17. Увеличьте шрифт меток на осях, подписей осей, названия гистограммы (отмените полужирное начертание и установите размер 10 пт. также, как и при выполнении задания 1.1, п.16-17).

18. Теперь щелкните дважды левой кнопкой мыши по одному из столбцов гистограммы. В результате появится диалоговое окно Формат ряда данных.

19. В этом окне в группе Вид можно выбрать цвет столбца (если гистограмму необходимо распечатать, то рекомендуется выбрать светло-серый цвет для обычного принтера).

20. В группе Параметры можно отрегулировать расстояние между столбцами. Принято, чтобы между столбцами гистограммы зазора не было, поэтому в поле Ширина зазора установите нуль и нажмите ОК.

21. В результате выполнения всех действий гистограмма должна иметь вид:


Задания для самостоятельного выполнения

1. 
   Построить дискретный вариационный ряд и полигон распределения 35 пациентов по числу значений величины пульса, полученных в результате медицинского осмотра: 79; 80; 81; 80; 82; 81; 80; 79; 82; 84; 80; 83; 82; 81; 83; 79; 82; 81; 82; 79; 81; 77; 83; 81; 78; 83; 82; 81; 80; 78; 84; 80; 79; 81;80.
   

удары в минуту, xi	77	78	79	80	81	82	83	84
Число пациентов, ni	1	2	5	7	8	6	4	2

Составить распределение относительных частот и построить его полигон. Сделать соответствующие подписи на осях.

2. 
   При измерении роста девушек некоторого института была получена следующая выборка (объема n  30):
   

178	153	157	173	179	171
160	167	175	182	165	175
154	186	170	167	156	173
183	163	166	171	179	164
155	155	159	169	158	172

Составить распределение частот (с помощью процедуры автозаполнения) и построить его полигон. Затем построить гистограмму данной выборки, содержащей 6 частичных интервалов. Длину частичного интервала вычислить по указанной в формуле §1.2. (округлить до целого числа). Для нахождения минимального и максимального значений выборки использовать функции МАКС(диапазон) и МИН(диапазон), где диапазон – интервал ячеек, в которых содержится выборка. Гистограмму оформить так же, как и при выполнении практического задания 1.2.

3. 
   Данные о количестве пациентов кардиологического отделения Демидовской больницы приведены в таблице.
   

62 54 84 59 75 43 49 89 28 49

40 53 18 18 55 51 26 68 76 65

43 39 47 65 55 29 33 42 51 95

85 46 45 42 48 6 73 54 70 56

69 66 33 100 58 42 89 41 36 72

54 50 54 45 48 11 62 33 32 61

36 31 84 61 26 53 64 50 66 63

77 31 84 61 26 53 64 50 66 63

9 30 69 60 9 30 4 27 74 62

19 42 55 79 77 31 92 30 39 96

Составить распределения частот и относительных частот (с помощью процедуры автозаполнения) и построить полигон относительных частот. Затем построить гистограмму данной выборки, содержащей 8 частичных интервалов. Гистограмму оформить так же, как и при выполнении практического задания 1.2.

Результаты самостоятельного выполнения заданий сохранить в файле с ранее выполненными заданиями данной темы
Контрольные вопросы

1. 
   Что называется выборочной и генеральной совокупностью?
   
2. 
   Что называется вариантами, частотами, относительными частотами?
   
3. 
   Что называется распределением выборки по частотам, относительным частотам?
   
4. 
   Сформулируйте свойство статистической устойчивости относительной частоты и статистическое определение вероятности.
   
5. 
   Что называется полигоном, какие они бывают?
   
6. 
   Что называется гистограммой? Опишите способ ее построения.
   
7. 
   Для чего предназначен Пакет анализа и как его вызвать?
   
8. 
   Что такое инструменты анализа? Перечислите наиболее часто используемые инструменты анализа, и для чего они предназначены.
   
9. 
   Каким основным недостатком обладает Пакет анализа?
   
10. 
    Опишите построение гистограммы с помощью Пакета анализа.
    
11. 
    Что такое Карманы?
    
12. 
    Как произвести автоматический набор частичных интервалов?
    
13. 
    Как изменить шрифт подписей на осях, меток осей, названия гистограммы?
    
14. 
    Как изменить цвет и расстояния между столбцами гистограммы?
    
15. 
    Как вывести числовые значения для каждого столбца гистограммы?