Обработка статистических данных средствами электронных таблиц

Введение

     

 

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

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

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

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

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

Объектом исследования данной работы  являются возможности табличного процессора.

Предметом исследования является применение программы MS Excel для решения статистических задач.

Актуальность работы обусловлена недостаточной реализацией возможностей MS Excel для решения статистических задач.

Цель настоящего исследования заключается в формировании устойчивых знаний о возможностях MS Excel для решения статистических задач.

Для достижения поставленной цели потребовалось решить следующие   задачи:

1.       Раскрыть сущность возможностей MS Excel.

2.       Определить способы применения этих возможностей при решении задач статистики. 

В качестве гипотезы выдвигается следующее:

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

 

Глава 1. Применение Microsoft Excel для решения

статистических  задач.

1. Работа  с данными

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

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

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

В процессе анализа данных, как  правило, присутствуют следующие основные этапы:

1. Ввод данных 

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

2. Преобразование  данных

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

3. Визуализация  данных

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

4. Статистический  анализ

Собственно  выбор метода, анализ данных и интерпретация  результатов.

5. Представление  результатов

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

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

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

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

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

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

В качества аргументов в формуле обычно используются числа  и адреса ячеек.  Для обозначения  арифметических операций могут использоваться следующие символы: + (сложение); - (вычитание); * (умножение); / (деление).           

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

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

Программа Excel интерпретирует вводимые данные  либо  как текст  (выравнивается  по  левому  краю),  либо  как  числовое значение (выравнивается по правому краю).  Для  ввода  формулы необходимо  ввести  алгебраическое выражение,  которому должен предшествовать знак равенства (=). [7]

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

 

2. Инструменты пакета анализа в Microsoft Excel

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

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

Однофакторный дисперсионный анализ

  Однофакторный дисперсионный анализ  используется для проверки гипотезы о сходстве средних значений двух или более выборок, принадлежащих одной и той же генеральной совокупности. Этот метод распространяется также на тесты для двух средних (к которым относится, например, t-критерий).

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

Представляет  собой более сложный вариант  однофакторного анализа, включающий более  чем одну выборку для каждой группы данных. Рассмотрим пример применения данной функции Microsoft Excel. Требуется при уровне α=0,05 выяснить, влияют ли на урожайность пшеницы вид удобрений и способ химической обработки почвы. Выборочные данные об урожайности пшеницы, выращенной на участках, на которые вносились различные виды удобрений и которые подвергались различной химической обработке, приведены в таблице ниже.

	B	C	D	E	F	G
32	Номер участка	Вид удобрения	Способ  химической обработки
33			Способ 1	Способ 2	Способ 3	Способ 4
34	Участок1	Удобрение1	21,4	20,9	19,6	17,6
35	Участок2		21,2	20,3	18,8	16,6
36	Участок3		20,1	19,8	16,4	17,5
37	Участок1	Удобрение2	12,0	13,6	13,0	13,3
38	Участок2		14,2	13,3	13,7	14,0
39	Участок3		12,1	11,6	12,0	13,9
40	Участок1	Удобрение3	13,5	14,0	12,9	12,4
41	Участок2		11,9	15,6	12,9	13,7
42	Участок3		13,4	13,8	12,1	13,0
43	Участок1	Удобрение4	12,8	14,1	14,2	12,0
44	Участок2		13,8	13,2	13,6	14,6
45	Участок3		13,7	15,3	13,3	14,0

Рассматриваемый в задаче эксперимент представляет собой факторный эксперимент  типа 4×4, при котором четыре вида удобрений(фактор А) пересекаются с использованием четырёх способов химической обработки почвы(фактор В). Таким образом, в плане эксперимента имеется 16 условий. Но здесь каждому условию  соответствует не одно, а три значения(3 участка земли, засеянных пшеницей).

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

	B	C	D	E	F	G
48	Двухфакторный дисперсионный анализ с повторениями
49
50	ИТОГИ	Способ1	Способ2	Способ3	Способ4	Итого
51	Удобрение1
52	Счёт	3	3	3	3	12
53	Сумма	62,7	61	54,8	51,7	230,2
54	Среднее	20,90	20,33	18,27	17,23	19,18
55	Дисперсия	0,49	0,30	2,77	0,30	3,13
56
57	Удобрение2
58	Счёт	3	3	3	3	12
59	Сумма	38,3	38,5	38,7	41,2	156,7
60	Среднее	12,77	12,83	12,90	13,73	13,06
61	Дисперсия	1,54	1,16	0,73	0,14	0,82
62
63	Удобрение3
64	Счёт	3	3	3	3	12
65	Сумма	38,8	43,4	37,9	39,1	159,2
66	Среднее	12,93	14,47	12,63	13,03	13,27
67	Дисперсия	0,80	0,97	0,21	0,42	0,99
68
69	Удобрение4
70	Счёт	3	3	3	3	12
71	Сумма	40,3	42,6	41,1	40,6	164,6
72	Среднее	13,43	14,20	13,70	13,53	13,72
73	Дисперсия	0,30	1,11	0,21	1,85	0,73
74
75	Итого
76	Счёт	12	12	12	12
77	Сумма	180,1	185,5	172,5	172,6
78	Среднее	15,01	15,46	14,38	14,38
79	Дисперсия	13,26	9,71	6,39	3,52

	B	C	D	E	F	G	H
82	Дисперсионный анализ
83	Источник  вариации	SS	df	MS	F	P-значение	F критическое
84	Выборка	309,26	3	103,09	123,64	1,11Е-17	2,90
85	столбцы	9,97	3	3,32	3,99	0,016	2,90
86	Взаимодействие	25,68	9	2,85	3,42	0,005	2,19
87	Внутри	26,68	32	0,83
88
89	Итого	371,59	47

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

Выборочный  коэффициент детерминации для фактора А

показывает, что 83 процента общей выборочной вариации урожайности пшеницы связано  с влиянием вида удобрения.

Расчётное значение F- критерия фактора В (способ химической обработки) , а критическая область образуется правосторонним интервалом (2,90; +∞) . Так как попадает в критическую область, то гипотезу отвергаем, т.е. считаем, что способ химической обработки почвы также влияет на урожайность пшеницы.

Выборочный коэффициент  детерминации для фактора В

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

Значимость  фактора взаимодействия попадает в критический интервал (2,19;+∞) и указывает на то, что эффективность различных видов удобрения варьируется при различных способах химической обработки почвы. [6]

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

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

Корреляционный и ковариационный анализ.

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