Лабораторная работа 15. Основы статистического анализа

Лабораторная работа 15. Основы статистического анализа

Цель работы. Закрепить навыки выполнения основных  операций статистического анализа данных.

Задание

По таблице, содержащей данные о среднегодовой стоимостью основных производственных фондов (ОПФ) и выпуском валовой продукции в сопоставимых ценах для 30 заводов, выполнить:

Выбрать из таблицы 1 номера заводов для выполнения задания. Выбрать из таблицы 2 данные, соответствующие номерам заводов для Вашего варианта. Произвести группировку заводов по среднегодовой стоимости ОПФ и построить гистограмму и кумуляту распределения, применив инструмент «Гистограмма» из Пакета анализа Excel без указания диапазона карманов. Пример – Упражнение 1. Вычислить основные статистические характеристики и определить доверительный интервал для выборочной средней двумя способами: посредством стандартных функций Excel и с помощью инструмента «Описательная статистика» » из Пакета анализа Excel. Пример – Упражнение 2. Оценить степень связи между двумя признаками: Среднегодовая стоимость ОПФ и Валовая продукция, вычислив значение линейного коэффициента корреляции. Использовать два метода: функция КОРРЕЛ и инструмент «Корреляция» из Пакета анализа Excel. Пример – Упражнение 3.

Варианты заданий

Таблица 1

Номер варианта	Номера заводов	Номер варианта	Номера заводов
1	1-30	6	26-55
2	6-35	7	31-60
3	11-40	8	36-65
4	16-45	9	41-70
5	21-50	10	46-75

Таблица 2

Номер завода	Среднегодовая стоимость ОПФ, млн. руб.	Валовая продукция, млн. руб.
6,9	10,0
8,9	12,0
3,0	3,5
5,7	4,5
3,7	3,4
5,6	8,8
4,5	3,5
7,1	9,6
2,5	2,6
10,0	13,9
6,5	6,8
7,5	9,9
7,1	9,6
8,3	10,8
5,6	8,9
4,5	7,0
6,1	8,0
3,0	2,5
6,9	9,2
6,5	6,9
4,1	4,3
4,1	4,4
4,2	6,0
4,1	7,5
5,6	8,9
3,4	3,5
3,1	3,3
3,5	3,5
4,1	4,5
5,8	7,5
5,2	6,9
3,8	4,3
4,1	5,9
5,6	4,8
4,5	5,8
4,2	4,6
6,1	8,4
6,5	7,3
2,0	2,1
6,4	7,8
4,0	4,2
8,0	10,6
5,1	5,8
4,9	5,3
4,3	4,9
5,8	6,0
7,2	10,4
6,6	6,9
3,0	3,5
6,7	7,2
4,0	4,2
8,0	10,4
5,1	5,8
4,9	5,3
6,3	8,0
7,5	9,4
6,6	11,2
3,3	3,4
6,7	7,0
3,4	2,0
3,3	3,3
3,9	5,4
4,1	5,0
5,9	7,0
6,4	7,9
3,9	6,4
5,6	4,6
3,5	4,1
3,0	3,8
5,4	8,5
2,0	1,8
4,5	4,6
4,8	5,2
5,9	9,0
7,2	8,6

Упражнение 1. Построение выборочной функции распределения

Для построения выборочных функций распределения в Excel используют инструмент Гистограмма из Пакета анализа. При этом весь диапазон изменения случайной величины разбивают на интервалы равной ширины, называемые карманами. Число карманов обычно 5-15. Вычисляется число попаданий значений случайной величины в каждый карман. По ним вычисляются статистические (относительные) частоты - отношение числа попаданий в карман m к общему числу испытаний n (m/n), по которым и строится гистограмма.

Пример. Построим выборочное распределение по данным о продажах товара за 2 месяца (табл. 1).

Разместим данные в диапазоне A3:E14.

Здесь же разместим диапазон карманов – граничных значений. При подсчете в карман включаются значения на правой границе интервала и не включаются значения на левой границе. В нашем случае данные будут группироваться в интервалы 0-170, 171-175, 176-180, ..., 226-230.

Карманы для разбиения разместим в ячейках G2:G14.

Таблица 1

Дневные продажи	Карманы
выборка за 2 месяца	170
174	206	176	208	211	175
202	200	197	209	192	180
191	195	201	200	208	185
209	178	202	195	210	190
202	187	203	212	227	195
199	197	203	206	230	200
196	190	190	217	203	205
181	190	204	214	197	210
209	223	199	201	194	215
211	199	196	189	195	220
191	188	184	199	210	225
182	170	203	193	180	230

Построим выборочное распределение дневных продаж с помощью инструмента Гистограмма из Пакета анализа  Microsoft Excel (вызов через меню Данные - Анализ данных). На рис. 1 показано заполнение параметров диалогового окна инструмента Гистограмма.

Входной интервал $А$3:$Е$14 - это диапазон исследуемых данных;

Интервал карманов $G$2:$G$14 - это границы, в которые группируются входные данные;

Выходной интервал $I$1 – это ячейка, начиная с которой будет выведен результат.

Установим также флажки Вывод графика и Интегральный процент.

Флажок Интегральный процент устанавливают, если надо вычислить проценты частот с накоплением и вывести график интегральных процентов (кумуляту).

Результат работы инструмента показан на рис. 2.

Рис. 1

Рис. 2

Примечание. Интервал карманов можно не задавать. В этом случае программа выполнит разбиение на своё усмотрение.

Упражнение 2. Расчет основных статистических характеристик и определение доверительного интервала для выборочной средней

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

Применим инструмент Описательная статистика к выборке, представленной в табл. 1. Для этого скопируем данные выборки на Лист 2 электронной таблицы и разместим их в столбце A (рис. 4).

Выберем инструмент Описательная статистика через меню Данные - Анализ данных. На рис. 3 показано заполнение параметров инструмента.

Входной интервал $А$1:$А$61 – это диапазон анализируемых данных. Здесь данные выборки расположены по столбцам, поэтому установлен переключатель По столбцам. Флажок Метки в первой строке установлен для вывода в результирующей таблице заголовка «Дневные продажи».

Выходной интервал $C$1 – это ячейка, начиная с которой будет выведен результат.

Установим также флажок Итоговая статистика – в выходном интервале для каждого столбца будут рассчитаны все статистические показатели.

Поле Уровень надежности позволяет установить требуемый уровень доверительной вероятности; по умолчанию 95%, что соответствует уровню значимости 0.05. Результат работы инструмента показан на рис. 4 в столбцах С и D.

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

Рис. 3

Рис. 4

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

Инструмент Описательная статистика вычисляет предельную ошибку выборочной средней: на рис. 4 она равна 3,15698. Округлив до 2-х знаков после запятой, получим значение 3,16. Таким образом, можно утверждать, что в 95% случаев значение генеральной средней  попадёт в интервал [192,8-3,16; 192,8+3,16]=[189,64; 195,96].

Функция ДОВЕРИТ. СТЬЮДЕНТ также вычисляет предельную ошибку выборочной средней по заданному уровню значимости, стандартному отклонению и числу значений в выборке. В нашем случае формула имеет вид: =ДОВЕРИТ. СТЬЮДЕНТ(0,05;D7;D15).

Упражнение 3. Корреляция

Степень связи двух выборок (случайных величин X и Y) оценивается коэффициентом корреляции R. Коэффициент корреляции R принимает значения от –1 до 1. Если R=0 – зависимости нет, R>0 – зависимость прямо пропорциональная, R<0 – зависимость обратно пропорциональная.

Функция Excel КОРРЕЛ и инструмент Корреляция Пакета анализа MS Excel вычисляет степень линейной взаимозависимости между выборками.

Если коэффициент корреляции |R|>0.6, то линейную зависимость между выборками считают выявленной, при |R|<0.4 – не выявленной.

Пример. Определим степень взаимосвязи между доходом семьи и числом посещений супермаркета в месяц – рис. 5. Здесь же показан результат функции

КОРРЕЛ(A2:A12;B2:B12)= –0.981225708.

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

Рис. 5

Теперь добавим третий параметр – среднюю сумму одной покупки (рис. 6) и применим инструмент Корреляция: меню Данные - Анализ данных…. Параметры заполним как на рис. 7.

Результат показан в правой части рис. 6: в ячейках E1:H4 вычислена корреляционная матрица, на пересечении столбцов и строк которой записаны коэффициенты корреляции между параметрами (столбцами).

В результате анализа выявлены:

сильная степень обратной линейной зависимости между столбцом 1 и столбцом 2
(R= –0,9812257); сильная степень прямой линейной зависимости между столбцом 1 и столбцом 3 (R= 0,99497); сильная степень обратной линейной зависимости между столбцом 2 и столбцом 3
(R= –0,982206);

Рис. 6

Рис. 7