Работа по статистической обработки.

I. ПОДГОТОВИТЕЛЬНЫЙ ЭТАП.................................................................4 1.1 Запуск Excel и подготовка персональной папки студента.........................4 1.2 Формирование индивидуальных исходных данных...................................4 1.3 Настройка МS Excel для выполнения лабораторной работы....................6 II. ЗАДАНИЕ ДЛЯ ВЫПОЛНЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ № 1...................................................................7 2.1 Задание по статистическому анализу выборочной совокупности...............7 2.2 Задание по статистическому анализу генеральной совокупности...............8 III. ПОРЯДОК ВЫПОЛНЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ...............8 3.1. Выявление и удаление из выборки аномальных единиц наблюдения...8 3.2 Оценка описательных статистических параметров совокупности..............11 3.2.1 Показатели описательной статистики.................................................11 3.2.2 Оценка ошибок выборки......................................................................17 3.2.3 Запуск и особенности реализации средств описательной статистики в надстройке ПАКЕТ АНАЛИЗА.....................................................................19 3.2.4. Расчет предельной ошибки выборки.................................................23 3.2.5. Использование статистических функций инструмента МАСТЕР ФУНКЦИЙ........................................................................................23 3.3 Построение и графическое изображение интервального вариационного ряда распределения единиц совокупности по признаку «Среднегодовая стоимость основных производственных фондов».............30 3.3.1 Краткие теоретические сведения.........................................................30 3.3.2. Особенности построения интервальных вариационных рядов распределения средствами инструмента ГИСТОГРАММА..........................33 IV. ЗАКЛЮЧИТЕЛЬНЫЙ ЭТАП................................................................40 4.1 Анализ обобщающих показателей описательной статистики.................40 4.2 Анализ типа закономерности распределения............................................42 4.3.Рекомендации к анализу статистической совокупности..........................44 4.4 Отчетность по работе...................................................................................46 СПИСОК ЛИТЕРАТУРЫ..............................................................................47 ПРИЛОЖЕНИЕ 1.............................................................................................49 ПРИЛОЖЕНИЕ 2.............................................................................................50
4
I. ПОДГОТОВИТЕЛЬНЫЙ ЭТАП
С целью организации индивидуальной рабочей среды необходимо произвести следующие действия: 1.1 Запуск Excel и подготовка персональной папки студента Для выполнения расчетов обобщающих показателей и подготовки отчета по лабораторной работе студент формирует персональную папку с именем, в котором указывается его фамилия и инициалы. Эта папка должна содержать два файла: расчетный с именем Лaб1.xls и отчетный с именем Отчет1.dос. Для создания рабочего файла проделать следующие действия: 1. Загрузить файл с исходными данными и макетами таблиц по следующему алгоритму: – на рабочем столе активизировать Мой компьютер; – в диалоговом окне выбрать файл Datadisk на «primary» (Е:)\БУНиА\Статистика\stat_lab.xls. 2. Сохранить файл с исходными данными в качестве рабочего файла по алгоритму: – Файл -> Сохранить как...; – в диалоговом окне Сохранение документа выбрать путь: Datadisk на «primary» (Е:)\БУНиА\Статистика\ \; – сохранить файл в указанной папке под именем JIaб1.xls. Для создания отчетного файла проделать следующие действия: – загрузить файл Формат отчета.doc из директории Datadisk на «primary» (Е:)\БУНиА\Статистика; – сохранить файл по алгоритму: – Файл -> Сохранить как...; – в диалоговом окне Сохранение документа выбрать путь: Datadisk на «primary» (Е:)\БУНиА\Статистика\ \\ Отчет1.dос.
1.2 Формирование индивидуальных исходных данных При проведении статистического наблюдения за деятельностью предприятий 10% механической выборкой получены следующие данные о среднегодовой стоимости основных средств и выручке от продаж продукции за год по 32 машиностроительным предприятиям. Для проведения автоматизированного статистического анализа совокупности выборочные данные представлены в формате электронной таблицы процессора Excel в диапазоне ячеек В4:С35. Для демонстрационного примера выборочные данные приведены в таблице 1. Лабораторная работа выполняется по вариантам, которые определяются начальной буквой фамилии студента (таблица 2).
5
Таблица 1
Исходные данные демонстрационного примера А В С 3 Порядковый номер единицы наблюдения Среднегодовая стоимость основных средств, млн руб. Выручка от продаж продукции, млн руб. 4 1 94 110 5 2 107 101 6 3 134 120 7 4 157 81 8 5 163 80 9 6 167 114 10 7 173 161 11 8 173 90 12 9 177 178 13 10 179 107 14 11 200 125 15 12 201 108 16 13 205 133 17 14 208 124 18 15 212 201 19 16 213 161 20 17 214 151 21 18 216 169 22 19 218 149 23 20 230 180 24 21 234 148 25 22 237 162 26 23 241 166 27 24 248 168 28 25 45 224 29 26 276 171 30 27 290 191 31 28 298 220 32 29 167 114 33 30 205 133 34 31 330 53 35 32 260 224
Порядок определения исходных данных по вариантам следующий. Первый вариант, используя данные таблицы 3, меняет значения показателей таблицы 1 под порядковыми номерами, заканчивающимися на цифру 1; второй вариант заменяет заканчивающиеся на цифру 2 и т.д. Например, третий вариант заменяет показатели № 3, 13, 23. Соответственно, четвертый вариант изменит показатели под порядковыми номерами 4, 14, 24.
6
Таблица 2
Определение вариантов лабораторной работы Номер варианта Начальная буква фамилии студента Первый А, Б, В, Г Второй Д, Е, Ж, З, И, Третий К, Л, М, Н, О Четвертый П, Р, С, Т Пятый У, Ф, Х, Ц, Ч, Ш, Щ, Э, Ю, Я
Таблица 3 Дополнительные данные для выполнения лабораторной работы Номер варианта Порядковый номер единицы наблюдения Среднегодовая стоимость основных производственных фондов, млн руб. Выручка от продаж продукции, млн руб. Первый 1 104 112 2 150 115 3 178 102 4 204 125 Второй 1 108 113 2 215 152 3 202 134 4 172 151 Третий 1 135 123 2 231 179 3 221 153 Четвертый 1 158 84 2 237 180 3 169 121 Пятый 1 161 83 2 260 192 3 288 186
Для записи исходных данных варианта в отчетный файл Отчет1.dос необходимо скопировать сформированную таблицу 1 из рабочего файла в файл Отчет1.dос в выделенное для этой цели место.
1.3 Настройка МS Excel для выполнения лабораторной работы Проверка наличия в Excel надстройки ПАКЕТ АНАЛИЗА Для выполнения лабораторной работы необходимо наличие в Excel программной надстройки Пакет анализа. В случае, если Пакет анализа установлен, то меню Сервис будет содержать пункт подменю Анализ данных. Если же этот пункт в меню Сервис отсутствует, необходимо активизировать инструмент Пакет анализа действиями:
7
1) Сервис -> Надстройки; 2) в диалоговом окне Надстройки отметить пункт Пакет анализа; 3) ОК. Установка форматов чисел на компьютере Перед началом работы следует убедиться, что компьютер настроен на формат дробных чисел и разделителей, применяемый в алгоритмах лабораторной работы, а именно: – дробная часть числа должна отделяться от целой части знаком «запятая» (,); – аргументы функций (разделители списков) должны отделяться знаком «точка с запятой» (;). Иная настройка форматов чисел на компьютере (например, дробная часть отделяется от целой знаком «точка» (.) или же аргументы функции (разделители списков) перечисляются через запятую) будет приводить к ошибкам при вводе в электронные таблицы Excel формул, указанных в алгоритмах задания 2. Установить в компьютере совместимый с текстами алгоритмов формат чисел можно следующим образом: 1. Пуск -> Настройка -> Панель управления -> Язык и стандарты. 2. Язык и стандарты -> Числа. 3. В поле Разделитель целой и дробной части ввести символ «,». 4. В поле Разделитель элементов списка ввести символ « ; ».
II. ЗАДАНИЕ ДЛЯ ВЫПОЛНЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ № 1
2.1 Задание по статистическому анализу выборочной совокупности 1. Выявить наличие среди исходных данных резко выделяющиеся значения признаков (аномальные) с целью исключения их из выборки. 2. Рассчитать обобщающие статистические показатели совокупности по изучаемым признакам: среднюю арифметическую величину (х), моду (Мо), медиану (Me), размах вариации (R), дисперсию ( 2 n? ), среднее линейное отклонение (d), среднее квадратическое отклонение (?n), коэффициент вариации (V?), структурный коэффициент асимметрии К. Пирсона (Asn). 3. На основе рассчитанных показателей и предположении, что распределения единиц по обоим признакам близки к нормальному, оценить: а) степень колеблемости значений признаков в совокупности; б) степень однородности совокупности по изучаемым признакам; в) устойчивость индивидуальных значений признаков;
8
г) количество попаданий индивидуальных значений признаков в диапазоны (х ± ?),(х ± 2?),(х ± 3?). 4. Дать сравнительную характеристику распределений единиц совокупности по двум изучаемым признакам на основе анализа: а) вариации признаков; б) количественной однородности единиц; B) надежности (типичности) средних значений признаков; г) симметричности распределении в центральной части ряда. 5. Построить интервальный вариационный ряд и гистограмму распределения единиц совокупности по признаку Среднегодовая стоимость основных производственных фондов и установить тип этого распределения. Рассчитать моду Мо полученного интервального ряда и сравнить ее с показателем Мо несгруппированного ряда данных. 2.2 Задание по статистическому анализу генеральной совокупности 1. Рассчитать генеральную дисперсию 2 N? , генеральное среднее квадратическое отклонение ?N И ожидаемый размах вариации признаков RN. Сопоставить значения этих показателей для генеральной и выборочной дисперсий. 2. Для изучаемых признаков рассчитать: а) среднюю ошибку выборки; б) предельные ошибки выборки для уровней надежности Р = 0,683, Р = 0,954, Р = 0,997 и границы, в которых будут находиться средние значения признака генеральной совокупности при заданных уровнях надежности. 3. Рассчитать коэффициенты асимметрии As и эксцесса Еk. На основе полученных оценок сделать вывод о степени близости распределения единиц генеральной совокупности к нормальному распределению.
III. ПОРЯДОК ВЫПОЛНЕНИЯ ЛАБОРАТОРНОЙ РАБОТЫ
3.1. Выявление и удаление из выборки аномальных единиц наблюдения Выявление аномальных значений признака наиболее удобно производить графическим методом. Для визуального анализа разброса единиц совокупности можно использовать различные типы графиков, в том числе точечный график. По расположению точек на точечном графике легко выявить значения признака, которые резко выделяются из общей, однородной массы значений признаков единиц совокупности. Для выявления аномальных значений этих признаков можно построить график для каждого из признаков в отдельности, однако анализ упростится, если использовать диаграмму рассеяния (рис. 1).
9
0 10 20 30 40 50 60 70 80
0 50 100 150 200 250 300 350
Ряд1
Рис. 1 Аномальные значения признаков на диаграмме рассеяния
При построении диаграмм рассеяния по оси X следует расположить значения признака Среднегодовая стоимость основных средств, а по оси Y – соответствующие значения признака Выручка от продаж продукции. Каждый «выброс» из основной массы точек означает аномальность единицы наблюдения либо по признаку X, либо по признаку Y. В обоих случаях такие единицы наблюдения (предприятия) подлежат удалению из первичных данных. Построение диаграммы рассеяния в среде Excel осуществляется с помощью инструмента построения графиков Мастер диаграмм: 1) выделить мышью оба столбца исходных данных в диапазоне В4:С35; 2) Вставка ->Диаграмма -> Точечная -> Готово. В результате выполнения этих действий на рабочем листе Excel появится диаграмма рассеяния. При построении точечного графика в режиме Мастер диаграмм данные первого выделенного столбца (Стоимость основных производственных фондов) автоматически сопоставляются оси X, данные второго выделенного столбца (Выручка от продаж продукции) – по оси Y. Полученный график можно произвольно перемещать по полю рабочего листа, изменять его размеры. Для перемещения графика в удобное для просмотра место следует установить курсор на произвольное место белой области графика, нажать левую кнопку мыши и, удерживая ее, переместить график в требуемое место, затем отпустить клавишу. Для изменения размеров графика производятся аналогичные действия, однако местом «захвата мышью» должен быть один из углов графика. 2. Для поиска аномальных наблюдений на построенной диаграмме рассеяния визуально находятся аномальные точки. При подведении к ним курсора появляется надпись, содержащая значения признаков этого наблюдения в формате (X; У) (рис. 2).
10
Рис. 2 Отображение надписи на диаграмме
Для демонстрационного примера такая надпись выглядит следующим образом: «Обозначение (330; 53) означает, что выбранная аномальная точка соответствует наблюдению (предприятию), которое имеет среднегодовую стоимость основных средств, равную 330 млн. руб., и объеме реализации продукции, равным 53 млн. руб.». 3. Единица наблюдения, соответствующая выявленной аномальной точке, отыскивается в исходных данных таблицы 1 визуально либо с помощью поисковых средств Excel. 4. Для фиксации выявленных аномальных единиц наблюдения в рабочем файле персональной папки студента выделена таблица (таблица 2), которая располагается в диапазоне ячеек А37-С41. Формат таблицы 2 совпадает с форматом исходной таблицы 1. Перед исключением аномальных единиц из первичных данных информацию о них следует скопировать в таблицу 2. 5. Для удаления из исходных данных таблицы 1 строк с аномальными данными необходимо выделить мышью соответствующую адресную строку вместе с ее номером. Для демонстрационного примера это адресная строка с номером 34, содержащая под порядковым номером 31 значения 330 и 53 (рис. 3):
А В С 29 167 114 33 30 205 133 34 31 330 53 35 Рис. 3 Фрагмент таблицы с исходными данными
Визуальный анализ диаграммы рассеяния, выявление и фиксация аномальных значений признаков, их удаление из первичных данных 1. Найти на графике точку, соответствующую аномальному наблюдению. Если таких точек нет, то перейти к действию 7, если есть – к действиям 2 –6. 2. Подвести курсор к точке на диаграмме рассеяния, соответствующей аномальному наблюдению. После непродолжительного времени возле точки автоматически появится надпись, содержащая значения признаков этого наблюдения в формате (X, У).
11
3. В исходных данных визуально (либо с помощью поисковых средств Excel) найти в таблицк 1 строку, соответствующую выявленной аномальной единице наблюдения (предприятию). Скопировать эту строку в таблицу 2. 4. Выделить мышью всю адресную строку с данными, подлежащими удалению. 5. Правка -> Удалить. 6. Выполнять действия 1–5 до полного удаления всех аномальных наблюдений. 7. Переместить диаграмму рассеяния в область ячеек, начиная с ячейки F4.
3.2 Оценка описательных статистических параметров совокупности 3.2.1 Показатели описательной статистики Обобщающие статистические показатели совокупности исчисляются на основе анализа вариационных рядов распределения. Однако пакет Excel позволяет рассчитать многие из этих показателей непосредственно по первичным данным наблюдения, используя инструмент Описательная статистика надстройки Пакет анализа, а также статистические функций инструмента Мастер функций. Описательная (дескриптивная) статистика является инструментом статистического описания данных, представляющих всю наблюдаемую совокупность в целом. Цель описательной статистики – получение сводных (обобщающих) показателей, характеризующих исходную совокупность данных как генеральную (а не как выборку из некоторой другой совокупности большего объема). Для численной оценки обобщающих показателей совокупности используются так называемые описательные статистики, представляющие собой однозначные функции на множестве наблюдаемых данных, определяющие значения оцениваемых обобщающих показателей совокупности. Описательные статистики рассчитываются по несгруппированным данным и реализуют точные функциональные зависимости значений показателей от исходных данных (в отличие от приближенных статистических оценок, выводимых с заданным уровнем надежности). Показатели, вычисляемые с помощью описательных статистик, можно разбить на 3 группы – показатели положения вариантов значений признака, вариации признака и особенностей формы его распределения. 1. Показатели положения описывают положение в первичном ряду данных тех или иных вариантов значений признака, характеризующих ряд. К ним относятся: – максимальное хmax и минимальное хmin значения признака; – средняя арифметическая величина x (выступающая в качестве
12
статистической оценки математического ожидания М [x] средней величины признака); – мода Мо – наиболее часто встречающийся вариант значений признака или тот вариант, который соответствует максимальной ординате эмпирической кривой распределения; – медиана Me – серединное значение ранжированного ряда вариантов значений признака; – нижний и верхний квартили Q1 и Q3 ограничивающие центральную зону ранжированного ряда, в которую попадают 50% вариантов значений признака: 25% вариантов значений, меньших серединного значения Me, и 25% вариантов значений, больших Me. Среди показателей этой группы наиболее часто используются показатели центра распределения – x, Мо и Me. При этом x рассчитывается для первичного ряда наблюдаемых данных, Мо и Me – для ранжированного (упорядоченного) ряда. Для х и Me характерны свойства: () ? = ?= n i i xx 1 0 , () ? = ?= n i i xMe 1 min. (1) В зависимости от целей статистического исследования в качестве центра распределения выбирается один из показателей x, Мо или Me. В случае однородной совокупности (с нормальным или близким к нему распределением единиц) в качестве центра чаще всего используется средняя величина x, характеризующая типичный уровень значений признака. Для неоднородной совокупности (не поддающейся нормальному закону распределения) роль центра распределения обычно выполняет медиана Me. 2. Показатели вариации (колеблемости) признака описывают степень рассеяния вариантов значений признака относительно своего центра x (или Me). Различают показатели размера и интенсивности вариации. К показателям размера вариации относятся: – размах вариации R = хmax –хmin, устанавливающий предельное значение амплитуды колебаний признака; – межквартильный размах Q3 – Q1, определяющий максимальную амплитуду колебаний в центральной зоне ряда (ограниченной квартилями Q1, и Q3); – среднее линейное отклонение d, вычисляемое как среднее арифметическое из абсолютных отклонений xx i ? :
n xx
d
n
i i? = ? = 1 ; (2)
13
– дисперсия 2 ? (или D), рассчитываемая как среднее арифметическое из квадратов отклонений xx i ? : () n xx n i i 2 12 ? = ? ?= ; (3) – среднее квадратическое (стандартное) отклонение ?, вычисляемое как корень квадратный из дисперсии 2 ? :

() n xx
n
i i? = ? ?= 1
2
. (4)
Интенсивность вариации признака измеряется относительными показателями
x V ? =? ,
x dV d = , x RV R = , x MeV Me = . Показатели R, d и ? являются величинами именованными и выражаются в тех же единицах, что и изучаемый признак. Дисперсия 2 ? считается безразмерной величиной. Относительные показатели интенсивности вариации, как правило, измеряются процентах. В статистической практике для оценки вариации наиболее широко применяются показатели размера вариации 2 ? , ? и показатель интенсивности вариации ? V . Показатели 2 ? , ? , основанные на учете отклонений (хi – x) индивидуальных значений признака хi от средней арифметической x, являются обобщающими характеристиками различия в значениях признака. Дисперсия 2 ? оценивает средний квадрат отклонений (хi – x). Величина ? очень чутко реагирует на вариацию признака (за счет возведения отклонений в квадрат) и органически вписывается в аппарат математической статистики (дисперсионный, корреляционный анализ и др.). На расчете дисперсии основаны многие статистические показатели. Среднее квадратическое отклонение ? показывает, на сколько в среднем отклоняются индивидуальные значения признака хi от их средней величины x. Размерность отклонения ? совпадает с размерностью самого признака. Отклонения, выраженные в ? , принято считать стандартными. Интенсивность вариации обычно измеряют коэффициентом вариации ?V , который выражается в процентах и вычисляется по формуле
*100 x V ? =? . (5)
14
Величина ? V .оценивает интенсивность колебаний вариантов относительно их средней величины. Принята следующая оценочная шкала колеблемости признака: – 0% Ме > Мо, (9) что означает преимущественное появление в распределении более высоких значений признака. Если же вершина кривой сдвинута вправо и левая часть оказывается длиннее правой, то асимметрия левосторонняя (рис. 4,б), для которой справедливо неравенство х < Me 0, при левосторонней As 0, то вершина кривой распределения располагается выше вершины нормальной кривой, а форма кривой является более островершинной, чем нормальная (рис. 5,а). Это говорит о скоплении значений признака в центральной зоне ряда распределения, т.е. о преимущественном появлении в данных значений, близких к средним; – если Ek < 0, то вершина кривой распределения лежит ниже вершины нормальной кривой, а форма кривой более пологая по сравнению с нормальной (рис. 5,б). Это означает, что значения признака не концентрируются в центральной части ряда, а достаточно равномерно рассеяны по всему диапазону от xmax до xmin. Для нормального распределения Ek = 0, поэтому чем больше абсолютная величина |Ek|, тем существеннее распределение отличается от нормального. В частности, большая отрицательная величина Ek означает преобладание у признака крайних значений, причем одновременно и более низких, и более высоких. При этом в центральной части распределения может образоваться
17
«впадина», превращающая распределение в двухвершинное ( U-образной формы), что является индикатором неоднородности совокупности.
3.2.2 Оценка ошибок выборки Применение выборочного метода наблюдения связано с измерением степени достоверности статистических характеристик генеральной совокупности, полученных по результатам выборочного наблюдения. Достоверность генеральных параметров зависит от репрезентативности выборки, т.е. от того, насколько полно и адекватно представлены в выборке статистические свойства генеральной совокупности. Как правило, статистические характеристики выборочной и генеральной совокупностей не совпадают, а отклоняются на некоторую величину ?, которую называют ошибкой выборки (ошибкой репрезентативности). Ошибка выборки – это разность между значением показателя, который был получен по выборке, и генеральным значением этого показателя. Например, разность ?
=?? ??? ~
определяет ошибку репрезентативности для средней величины признака. Значения признаков единиц выборочной совокупности являются случайными величинами, следовательно, ошибки выборки также случайны и могут принимать для разных выборок одной и той же генеральной совокупности разные значения. Ввиду этого принято вычислять среднюю и предельную ошибки выборки. Для среднего значения признака средняя ошибка выборки ? ? ? (ее называют также стандартной ошибкой) выражает среднее квадратическое отклонение ? выборочной средней ~ ? от математического ожидания
? ? ?
? ? ?? ? ? генеральной средней ?
? . Величина ошибки ? ? ?
зависит от объема выборки n и от величины вариации признака ?: чем больше n и меньше?, тем меньше ошибка ? ? ? . Предельная ошибка выборки ~ ? ? определяет границы, в пределах которых лежит генеральная средняя ? ? . Эти границы задают так называемый доверительный интервал генеральной средней ? ? – случайную область значений, которая с вероятностью Р, близкой к 1, гарантированно содержит значение генеральной средней. Эту вероятность называют доверительной вероятностью или уровнем надежности.
18
Наиболее часто используются уровни надежности Р = 0,954; Р = 0,997; P = 0,683. В математической статистике доказано, что предельная ошибка выборки ~ ? ? кратна средней ошибке ? ? ? с коэффициентом кратности t, зависящим от значения доверительной вероятности Р: .~ ~ ?? t ? ?=? Величина коэффициента t (называемого также коэффициентом доверия) является нормированным отклонением, которое вычисляется по формуле
? ?
=
? ~ ?? t
и выражается не в натуральных единицах, а в сигмах: 1?, 2?, З? и т.д. Значения t подсчитаны для различных уровней надежности Р и протабулированы (хранятся в таблицах интегральной функции Лапласа). Для вышеприведенных уровней надежности Р коэффициенты доверия t задаются следующим образом:
Р 0,683 0,954 0,997 t 1 2 3
Например, если t = 2, то с вероятностью Р = 0,954 можно утверждать, что расхождение между выборочной и генеральной средними ? ? ?? ~ не превысит двукратной величины средней ошибки выборки:
2.
~
~ ?
? ?? ?? =?? ?
Таким образом, предельная ошибка выборки ~ ? ? позволяет определить предельные значения показателей генеральной совокупности и их доверительные интервалы. Для генеральной средней предельные значения и доверительные интервалы определяются выражениями:
, ~ ~ ? ??±? = ?
(15)
~~ ~~ ?? ??? +? ???? ?
. Что касается величины дисперсии генеральной совокупности 2 N? , то она может быть оценена непосредственно по выборочной дисперсии 2 n? .
19
В математической статистике доказано, что при малом числе наблюдений (особенно при ? n 40–50) для вычисления генеральной дисперсии 2 N? по выборочной дисперсии 2 n? следует использовать формулу 2 N? = ? ?1n n 2 n? . (16) При достаточно больших n значение поправочного коэффициента
1?n n близко к 1 (при n = 100 его значение равно 1,101,а при n = 500 – 1,002 и т.д.). Поэтому при достаточно больших n можно приближенно считать, что обе дисперсии совпадают: 22 Nn ??? .
3.2.3 Запуск и особенности реализации средств описательной статистики в надстройке ПАКЕТ АНАЛИЗА Запуск инструмента Описательная статистика осуществляется следующей последовательностью действий: Сервис ? Анализ данных ? Описательная статистика ? ОК. В появившемся диалоговом окне инструмента (рис. 6) задаются следующие параметры:
Рис.6. Диалоговое окно инструмента Описательная статистика
В появившемся диалоговом окне инструмента (рис. 7) задаются следующие параметры: 1. Поле Входной интервал – вводится ссылка на диапазон ячеек, содержащих значения анализируемого признака. В качестве входного интервала может быть указан диапазон, который содержит ряды значений сразу нескольких анализируемых признаков. В таком случае показатели Описательной статистики будут рассчитаны для каждого ряда и представлены в единой таблице в виде отдельных столбцов (рис. 7).
20
2. Переключатель Группирование: по столбцам/строкам – устанавливается в положение по столбцам или по строкам в зависимости от того, в каком направлении располагаются анализируемые данные во входном диапазоне – вертикальном (по столбцам) или горизонтальном (по строкам). 3.Флажок Метки в первой строке – устанавливается в активное состояние, если первая строка во входном диапазоне содержит заголовки. Если заголовки отсутствуют, поле не активизируется. В этом случае будут автоматически созданы стандартные названия для данных выходного диапазона. 4. Поле Выходной интервал – вводится ссылка на ячейку заголовка первого столбца выходной результативной таблицы. Размер выходного диапазона ячеек определяется автоматически. В случае возможного наложения выходного диапазона на другие данные на экране появится соответствующее сообщение. 5. Переключатели Новый рабочий лист и Новая рабочая книга – устанавливаются в активное положение при необходимости открытия соответственно нового листа или новой книги. В новом листе результаты анализа располагаются начиная с ячейки А1, в новой книге – на первом листе начиная с ячейки А1. 6. Флажок Итоговая статистика – устанавливается в активное состояние, если для данных входного диапазона необходимо произвести расчет основных показателей, перечисленных в макете результативной таблицы на рисунке 6. 7. Флажок Уровень надежности – устанавливается в активное состояние, если в результативную таблицу необходимо включить строку для оценки предельной ошибки выборки ( ~ ? ? ) с заданной доверительной вероятностью. Значение уровня надежности выражается в процентах и задается в поле напротив флажка Уровень надежности. Уровень надежности 95,0% (что равносильно доверительной вероятности Р = 0,95 или же уровню значимости ? = 0,05) фиксируется в поле автоматически. 8. Флажки К-тый наименьший и K-тый наибольший – активизируются, если в результативную таблицу необходимо включить строку соответственно для k-го наименьшего (начиная с минимума min x ) и k-го наибольшего (начиная с максимума max x ) значений элементов в выборке. В этом случае в поле, расположенном напротив каждого флажка, вводится число k .При k = 1 выходные строки будут содержать соответственно minx и max x . В Пакете анализа инструмент Описательная статистика используется для генерации одномерного статистического отчета, который включает ряд показателей положения, вариации и формы распределения признаков выборочной и генеральной совокупностей, а также среднюю и предельную ошибки выборки для средней (рис. 7).
21
Столбец 1 Столбец 2 Среднее Среднее Стандартная ошибка Стандартная ошибка Медиана Медиана Мода Мода Стандартное отклонение Стандартное отклонение Дисперсия выборки Дисперсия выборки Эксцесс Эксцесс Асимметричность Асимметричность Интервал Интервал Минимум Минимум Максимум Максимум Сумма Сумма Счет Счет Уровень надежности (95,4%) Уровень надежности (95,4%) Рис. 7. Макет результативной таблицы инструмента
Описательная статистика Между терминологией инструмента Описательная статистика и терминами, принятыми в отечественной статистике, имеется ряд расхождений. Согласование терминологии приводится в таблице 4. Таблица 4 Статистическая интерпретация параметров описательной статистики Параметр инструмента Описательная статистика Статистический показатель Обозначение
Среднее
Средняя арифметическая величина признака в выборке, вычисленная по несгруппированным данным
~ ?
Стандартная ошибка
Средняя ошибка выборки – среднее квадратическое отклонение выборочной средней ~ ? от математического ожидания генеральной средней ? ?
~ ? ?
Медиана Значение признака, приходящееся на середину ранжированного ряда выборочных данных Me Мода Значение признака, повторяющееся в выборке с наибольшей частотой Mo Стандартное отклонение Генеральное среднее квадратическое отклонение, оцененное по выборке N ?
22
Окончаеие табл. 4
Дисперсия выборки Генеральная дисперсия, оцененная по выборке
2 n?
Эксцесс
Коэффициент эксцесса, оценивающий по выборке значение эксцесса в генеральной совокупности
EkN
Асимметричность
Коэффициент асимметрии, оценивающий по выборке величину асимметрии в генеральной совокупности
AsN Интервал Размах вариации в выборке R Минимум Минимальное значение признака в выборке min ? Максимум Максимальное значение признака в выборке max ? Сумма Суммарное значение элементов выборки i ?? Счет Объем выборки n Уровень надежности (95,0%) Предельная ошибка выборки, оцененная с заданным уровнем надежности ~ ? ?
Вычисленные значения всех вышеперечисленных показателей представляются в единой результативной таблице на рабочем листе Excel. При этом показатели могут рассчитываться сразу для нескольких рядов данных в соответствии с заданным входным диапазоном ячеек. Так, для входного диапазона с двумя рядами данных результативная таблица будет состоять из двух столбцов значений описательных параметров (рис. 7). Именно такой формат имеет таблица 3; зарезервированная в рабочем файле персональной папки студента или показателей, рассчитываемых в режиме Описательная статистика. Следует обратить внимание на то, что расчет параметров в режиме Описательная статистика имеет ряд важных особенностей: 1. В качестве значений параметров: Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность – Excel генерирует оценки соответствующих параметров для генеральной совокупности, а не для выборки. 2. Для применения Описательной статистики предварительное ранжирование исходных данных не требуется: при вычислении показателей ранжирование выполняется автоматически. 3. Появление в ячейке Мода индикатора ошибки #Н/Д указывает на то, что в анализируемых данных нет одинаковых значений признака. В этом случае в качестве моды Мо выбирается то значение признака, которое соответствует максимальной ординате теоретической кривой распределения. 4. Индикатор ошибки #ДЕЛ/0! в ячейке Эксцесс и/или Асимметричность означает, что в результативной таблице стандартное отклонение является нулевым или же заданный входной диапазон данных содержит менее четырех элементов данных.
23
3.2.4. Расчет предельной ошибки выборки Расчет предельной ошибки выборки осуществляется в режиме Описательная статистика. Значение ~ ? ? для уровня надежности 95,0% автоматически выводится в результативной таблице. Для расчета ~ ? ? при уровнях надежности 99,7 и 68,3% необходимо дважды обратиться к инструменту Описательная статистика и в диалоговом окне активизировать поля Входной интервал, Группирование, Выходной интервал, Уровень надежности, внеся в них соответствующие значения. Поскольку флажок Итоговая статистика при этом не активизируется, дублирования вычислений описательных показателей не происходит. Для предельных ошибок выборки при уровнях надежности 68,3 и 99,7% в рабочем файле персональной папки студента зарезервированы таблицы 4,а и 4,б соответственно, имеющие следующий формат (таблица 5).
Таблица 5
Предельная ошибка выборки Столбец 1 Столбец 2 Уровень надежности (...%) Уровень надежности (...%)
3.2.5. Использование статистических функций инструмента МАСТЕР ФУНКЦИЙ 1. Вызвать Мастер функций, нажав кнопку ? f на панели инструментов. 2. В Мастере функций среди предлагаемых категорий функций выбрать категорию Статистические, а затем – необходимую функцию (СТАНДОТКЛОНП, ДИСПР или СРОТКЛ). 3. В появившемся диалоговом окне в поле Число 1 указать диапазон ячеек, содержащих значения анализируемого признака. Помимо показателей, определяемых в режиме Описательная статистика, для анализа статистических свойств изучаемой совокупности необходимы дополнительно следующие выборочные показатели (которые либо вообще не вычисляются инструментом Описательная статистика, либо вычисляются для генеральной совокупности): – выборочное среднее квадратическое отклонение n ? ; – выборочная дисперсия 2 n? ; – выборочное среднее линейное отклонение ? d ; – коэффициент вариации признака в выборке ? V ; – коэффициент асимметрии Пирсона ? As . Для этих пяти показателей в рабочем файле персональной папки студента зарезервирована таблица 6 следующего формата.
24
Таблица 6
Выборочные показатели совокупности Первый признак Второй признак Стандартное отклонение n ? Стандартное отклонение n ? Дисперсия 2 n? Дисперсия 2 n? Среднее линейное отклонение _ d Среднее линейное отклонение _ d Коэффициент вариации ? V Коэффициент вариации ? V Коэффициент асимметрии ? As Коэффициент асимметрии ? As
Для вычисления показателей n ? , 2 n? и
_ d используются соответствующие статистические функции инструмента Мастер функций: 1. СТАНДОТКЛОНП (Диапазон ячеек) – оценивает среднее квадратическое отклонение ? в предположении, что исходные данные представляют всю совокупность (не являются выборкой из генеральной совокупности). 2. ДИСПР (Диапазон ячеек) – оценивает дисперсию 2 ? в предположении, что исходные данные представляют всю совокупность. 3. СРОТКЛ (Диапазон ячеек) – рассчитывает для выборочных данных среднее линейное отклонение _ d. Для расчета коэффициента вариации ? V по формуле (5) необходимо использовать среднее арифметическое значение ~ ? из результативной таблицы Описательной статистики (табл. 3) и выборочное среднее квадратическое отклонение n ? из таблицы выборочных показателей (табл. 5). Для расчета коэффициента асимметрии Пирсона ? As по формуле (11) необходимо использовать среднее арифметическое значение ~ ? и моду Мо из результативной таблицы Описательной статистики (табл. 3), а также выборочное среднее квадратическое отклонение n ? из таблицы выборочных показателей (таблица 5).
Расположение данных на рабочем листе Excel Исходные данные и их описательные статистические характеристики располагаются в таблицах рабочего файла персональной папки студента в соответствии с таблицей 3. При этом учитывается следующая особенность автоматического построения выходных таблиц инструментом Описательная статистика. Инструменты Пакета анализа строят выходные таблицы со стандартными заголовками столбцов (например, таблица описательных статистик на рисунке 7 имеет стандартные заголовки Столбец 1, Столбец 2). При этом размещение выходных таблиц на рабочем листе осуществляется
25
начиная с ячейки заголовка первого столбца (в примере это ячейка с заголовком Столбец 1). Именно адрес этой ячейки и должен задаваться в поле Выходной интервал диалогового окна при формировании выходных таблиц.
Таблица 7
Расположение данных на рабочем листе Excel
Исходные данные и их статистические характеристики
Признак Среднегодовая стоимость основных производственных фондов, млн руб.
Признак Выручка от продажи продукции, млн руб.
Исходные данные после удаления «аномальных» значений – Таблица 1
В4:ВЗЗ С4:СЗЗ
Описательные статистики – Таблица 3 с ячейкой заголовка первого столбца А46 С46 Предельная ошибка выборки при Р = 0,683 – Таблица 4,а с ячейкой заголовка первого столбца А67 С67 Предельная ошибка выборки при Р = 0,997 – Таблица 4,б с ячейкой заголовка первого столбца А75 С75 Среднее квадратическое отклонение выборки n ? в Таблице 5 В83 D83 Дисперсия выборки 2 n? в Таблице 5 В84 D84 Среднее линейное отклонение _ d a Таблице 5 В85 D85 Коэффициент вариации ? V в Таблице 5 В86 D86 Коэффициент асимметрии n As в Таблице 5 В87 D87
Алгоритм выполнения задания 2 Этап 1. Расчет описательных параметров выборочной и генеральной совокупностей с использованием инструмента ОПИСАТЕЛЬНАЯ СТАТИСТИКА Алгоритм расчета описательных статистик 1. Сервис ? Анализ данных ? Описательная статистика? ОК. 2. Входной интервал ? диапазон ячеек таблицы, выделенный согласно таблице 7 для значений признаков Стоимость основных фондов и Выпуск продукции. 3. Группирование ? по столбцам. 4. Итоговая статистика – Активизировать. 5. Уровень надежности – Активизировать. 6. Уровень надежности ? 95,4.
26
7. Выходной интервал ? адрес ячейки заголовка первого столбца таблицы 3 (таблица 7). 8. ОК. 9. При появлении окна с сообщением «Выходной интервал накладывается на имеющиеся данные» ? ОК. В результате указанных действий Excel осуществляет вывод таблицы описательных статистик в заданный диапазон рабочего файла (для демонстрационного примера (таблица 8). Интерпретация терминов таблицы в принятых статистических терминах приведена выше.
Таблица 8 А В С D 43 44 Описательные статистики 45 Среднегодовая стоимость основных производственных фондов, млн. руб. Выручка от продаж, млн. руб 46 Столбец 1 Столбец 2 47 48 Среднее 203,2333333 Среднее 144,6666667 49 Стандартная ошибка 8,804737927 Стандартная ошибка 7,071772174 50 Медиана 206,5 Медиана 148.5 51 Мода 167 Мода 114 52 Стандартное отклонение 48,22553575 Стандартное отклонение 38,73369141 53 Дисперсия выборки 2325,702299 Дисперсия выборки 1500,298851 54 Эксцесс 0,18281271 Эксцесс –0,602481285 55 Асимметричность –0,185105228 Асимметричность 0,218561586 56 Интервал 204 Интервал 144 57 Минимум 94 Минимум 80 58 Максимум 298 Максимум 224 59 Сумма 6097 Сумма 4340 60 Счет 30 Счет 30
61
Уровень надежности (95,4%) 18,356223 Уровень надежности (95,4%) 14,74331526 Этап 2. Оценка предельных ошибок выборки для различных уровне надежности в режиме ОПИСАТЕЛЬНАЯ СТАТИСТИКА Алгоритм расчета предельной ошибки выборки при Р = 0,683 1. Сервис ? Анализ данных ? Описательная статистика ? ОК. 2. Входной интервал ? диапазон ячеек таблицы, выделенный согласно таблице 7 для значений признаков Стоимость основных фондов