VII Международная студенческая научная конференция Студенческий научный форум - 2015

Цель работы: овладеть навыками расчета числовых характеристик выборки с помощью Надстройки Пакет Анализа ЭТ MS Excel.

Краткая теория

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

Надстройка Пакет анализа вызывается командой главного меню Данные → Анализ данных. В появившемся окне Анализ данных выбираем пункт Описательная статистика.

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

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

Группирование. Установите переключатель в положение «По столбцам» или «По строкам» в зависимости от расположения данных во входном диапазоне.

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

Уровень надежности. Установите флажок, если в выходную таблицу необходимо вывести границу доверительного интервала для среднего. В поле введите требуемое значение в процентах. Например, значение 95% вычисляет уровень надежности среднего с уровнем значимости 0,05.

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

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

Выходной диапазон. Введите ссылку на левую верхнюю ячейку выходного диапазона. Этот инструмент анализа выводит два столбца сведений для каждого набора данных. Левый столбец содержит метки статистических данных; правый столбец содержит статистические данные. Состоящий их двух столбцов диапазон статистических данных будет выведен для каждого столбца или для каждой строки входного диапазона в зависимости от положения переключателя Группирование.

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

Если хотим вывести результаты расчета в новой книге, то установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

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

Таблица 2.

Значение	Примечания
Среднее	Выборочное среднее х=1n∙i=1nxi. Функция СРЗНАЧ.
Стандартная ошибка	Оценка среднеквадратичного отклонения выборочного среднего. Вычисляется по формуле 1n∙(n-1)∙i=1n(xi-x)2
Медиана	Число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Функция МЕДИАНА.
Мода	Наиболее часто встречающееся значение в выборке. Если нет одинаковых значений, то возвращается значение ошибки #Н/Д. Функция МОДА.ОДН.
Стандартное отклонение	Оценка среднеквадратичного отклонения генеральной совокупности S=1n-1∙i=1n(xi-x)2. Функция СТАНДОТКЛОН.В.
Дисперсия выборки	Оценка дисперсии генеральной совокупности . Функция ДИСП.В.
Эксцесс	Выборочный эксцесс. Функция ЭКСЦЕСС.
Асимметрич-ность	Коэффициент асимметрии. Функция СКОС.
Интервал	Размах варьирования R = xmax ‒ xmin .
Минимум	Минимальное значение в выборке. Функция МИН.
Максимум	Максимальное значение в выборке. Функция МАКС.
Сумма	Сумма всех значений в выборке. Функция СУММ.
Счет	Объем выборки. Функция СЧЕТ.
Наибольший	k-тое наибольшее значение выборки. Если k=1, то выводится максимальное значение. Функция НАИБОЛЬШИЙ.
Наименьший	k-тое наименьшее значение выборки. Если k=1, то выводится минимальное значение. Функция НАИМЕНЬШИЙ
Уровень надежности	Параметр показывает возможность отклонения среднего по выборке, от среднего для генеральной совокупности, при заданном уровне надежности.

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

1. В качестве значений параметров: Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность – Excel генерирует оценки соответствующих параметров для генеральной совокупности, а не для выборки.

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

3. Появление в ячейке Мода индикатора ошибки #Н/Д указывает на то, что в анализируемых данных нет одинаковых значений признака. В этом случае в качестве моды Мо выбирается то значение признака, которое соответствует максимальной ординате теоретической кривой распределения.

4. Индикатор ошибки #ДЕЛ/0! В ячейке Эксцесс и/или Асимметричность означает, что в результативной таблице стандартное отклонение является нулевым или же заданный входной диапазон данных содержит менее четырех элементов данных

5. Стандартная ошибка ‒ это разность между ожидаемыми и наблюдаемыми значениями исследуемого признака.

Стандартная ошибка или ошибка среднегонаходится из выражения

m=Sn .

Стандартная ошибка – это параметр, характеризующий степень возможного отклонения среднего значения, полученного на исследуемой ограниченной выборке, от истинного среднего значения, полученного на всей совокупности элементов. С помощью стандартной ошибки задается так называемый доверительный интервал. 95%-ый доверительный интервал, равный х ± 2т , обозначает диапазон, в который с вероятностью р = 0,95 (при достаточно большом числе наблюдений п>30) попадает среднее значение генеральной совокупности.

Пример выполнения

Постановка задачи. Приведены объемы дневной выручки (в тыс. руб.) 24 продавцов колбасных изделий, работающих в разных районах города (см. табл.1).

Таблица 1.

20,2	19,3	19,9	23,1	18,8	17,4
19,9	18,3	16,4	17,3	18,3	15,8
20,5	20,6	19,4	18,7	16,3	18,4
21,6	21,2	19,3	19,1	19,3	18,8

Требуется: выполнить описательную статистику выборки с помощью Надстройки Пакет Анализа ЭТ MS Excel.

Решение задачи в среде ЭТ MSExcel. Для решения задачи в среде ЭТ MS Excel необходимо выполнить следующие действия:

1. Идентифицируйте свою работу, переименовав Лист1 в Титульный лист и записав номер лабораторной работы, ее название, кто выполнил и проверил.

2. Переименуйте Лист 2 в Исходные данные и наберите столбец исходных данных.

3. Вычислите величины х_max, х_min, R, n, N, N_{округл.}, Δ и Δ_{округл.} , используя встроенные функции Excel МАКС, МИН, СЧЕТ, КОРЕНЬ и ОКРУГЛ.

4. Сформируйте столбец интервалов группировки. Наберите команду Данные → Анализ данных → Гистограмма и в появившемся диалоговом окне выполните нужные установки. Отформатируйте полученную таблицу и построенную гистограмму выборки.

5. Наберите команду Данные → Анализ данных → Описательная статистика и в появившемся диалоговом окне выполните нужные установки.

6. Щелчок по кнопке «ОК» приводит к появлению результирующей таблицы статистических характеристик выборки.

7. Повторно вычислим найденные характеристики с помощью встроенных функций MS Excel или формул. Сравним полученные результаты.

8. Сделайте выводы и сохраните работу в вашем каталоге.

Исходные данные для самостоятельного решения

Задание. Имеется выборка объема n = 27 (табл. 2).

Требуется: выполнить описательную статистику выборки с помощью Надстройки Пакет Анализа ЭТ MS Excel.

Таблица 2.

№ варианта	Выборка
1	22,5	20,2	19,3	19,9	23,1	18,8	17,4	21,6	19,1
	21,6	19,9	18,3	16,4	17,3	18,3	15,8	21,2	19,3
	17,8	20,5	20,6	19,4	18,7	16,3	18,4	19,3	18,8
2	18,8	20,2	19,3	19,9	23,2	22,5	17,4	21,8	19,2
	19,4	18,7	16,3	18,4	19,3	18,8	19,4	18,7	16,3
	20,5	20,6	19,4	18,7	16,3	18,4	19,3	18,8	17,8
2	20,2	19,3	19,9	23,1	18,8	17,4	21,6	19,1	22,4
	18,7	20,2	19,3	19,9	23,2	22,5	17,4	21,8	19,2
	18,1	19,8	18,2	16,4	17,2	21,8	15,8	21,2	19,2
3	19,4	18,7	16,3	18,4	19,3	18,8	19,4	18,7	16,3
	18,5	20,6	19,4	20,7	16,3	18,4	19,3	18,8	17,8
	20,1	19,3	19,9	23,1	18,8	17,4	21,6	19,1	22,4
4	19,7	20,2	19,3	18,9	23,2	22,5	17,4	21,8	19,2
	18,3	19,8	18,2	16,4	17,2	21,8	15,8	21,2	19,2
	19,7	18,7	16,3	18,4	19,3	18,8	19,4	18,7	16,3
5	19,4	20,7	16,3	18,4	19,3	18,8	17,8	18,7	20,2
	19,9	23,1	18,8	17,4	21,6	19,1	22,4	18,1	19,8
	19,3	18,9	23,2	22,5	17,4	21,8	19,2	19,4	18,7
6	18,7	16,3	18,4	19,3	18,8	19,4	18,7	18,5	20,6
	20,6	19,4	20,7	16,3	18,4	19,3	18,8	18,4	19,3
	19,3	19,9	23,1	18,8	17,4	21,6	19,1	18,4	19,3
7	16,3	18,4	19,3	18,8	19,4	18,7	18,5	20,6	18,7
	19,4	20,7	16,3	18,4	19,3	18,8	18,4	19,3	20,6
	19,9	23,1	18,8	17,4	21,6	19,1	18,4	19,3	19,3
8	19,3	19,9	23,1	18,8	17,4	21,6	19,1	22,5	20,2
	18,3	16,4	17,3	18,3	15,8	21,2	19,3	21,6	19,9
	20,6	19,4	18,7	16,3	18,4	19,3	18,8	17,8	20,5
9	19,4	20,7	16,3	18,4	19,3	18,8	17,8	18,7	20,2
	19,9	23,1	18,8	17,4	21,6	19,1	22,4	18,1	19,8
	19,3	18,9	23,2	22,5	17,4	21,8	19,2	19,4	18,7
10	18,7	16,3	18,4	19,3	18,8	19,4	18,7	18,5	20,6
	20,6	19,4	20,7	16,3	18,4	19,3	18,8	18,4	19,3
	16,4	20,4	20,8	19,4	18,7	17,8	18,4	19,4	18,8

Код для цитирования: Скопировать