Содержание:
ПРАКТИЧЕСКАЯ РАБОТА № 2
Описательная статистика и анализ данных с помощью в Microsoft Excel
Цель: научиться выполнять первичный статистический анализ экспериментальных данных
Выполнение описательной статистики, расчет числовых характеристик выборки
Описательная статистика – это раздел статистической науки, в рамках которого изучаются методы описания и представления основных свойств данных. Она позволяет обобщать первичные результаты, полученные при наблюдении или в эксперименте.
В рамках описательной статистики применяются следующие простейшие техники:
табличное представление данных
графическое представление данных.
Использование обобщающих статистик, таких, как математическое ожидание, медиана, дисперсия и т.д. Обобщающие статистики используются для решения двух основных задач: показать общее в характере совокупности данных; показать, в чём и насколько данные различны.
Ниже, в табл. 1 приведены встроенные функции описательной статистики, которые реализованы в Microsoft Excel. Вызвать любую из них можно с помощью мастера функций, выбрав категорию «статистические».
Таблица 1– Статистические функции Microsoft Excel
Функция Excel Назначение
ДИСП Возвращает дисперсию по выборке
ДИСП.В Возвращает дисперсию выборки
КВАРТИЛЬ Возвращает квартиль набора данных
МАКС Возвращает максимальное значение из списка аргументов
МЕДИАНА Возвращает медиану заданного набора чисел
МИН Возвращает наименьшее значение в списке аргументов
МОДА Возвращает наиболее часто встречающееся значение набора данных
СРЗНАЧ Возвращает среднее (арифметическое) значение
СТАНДОТКЛОН Возвращает стандартное отклонение по выборке
ЭКСЦЕСС Возвращает эксцесс множества данных
СКОС Возвращает асимметрию множества данных
Задание 1
В табл. № 1 представлены экспериментальные данные, полученные в результате тестирования 100 респондентов. Необходимо оценить числовые характеристики выборки, проанализировать форму распределения частот. Данные для задания находятся в файле на портале лист Задача 2-1.
Таблица 2 – Исходные данные и расчетные таблицы для описательной статистики
Выборка Числовые характеристики Интервалы Частоты
60 max
106 min
76 Интервал
126 Кол-во интервалов
64 Среднее
102 Стандартная ошибка
98 Медиана
76 Мода Сумма 100
105 Стандартное отклонение
83 Дисперсия выборки
139 Эксцесс
70 Асимметричность
Числовые характеристики рассчитайте, используя встроенные статистические функции Microsoft Excel, описанные в таблице 1.
Создать массив интервалов (количество интервалов было вами рассчитано). Первый интервал определяется как сумма минимального элемента выборки и цены деления (C), последний элемент не должен существенно превышать максимального элемента выборки.
Выделить ячейки под массив частот (пометить доступными способами). Этих ячеек должно быть столько же, сколько ячеек отведено под массив интервалов.
Вызвать мастер функций . (Под двоичным массивом здесь понимается массив интервалов). Ввести координаты массива данных (вариант) и массива интервалов.
После указания всех аргументов функции нажать комбинацию: Ctrl+Shift+Enter. После этого функция ЧАСТОТА заполнит весь выделенный массив.
Для построения частот используется функция Excel ЧАСТОТА (массив_данных; массив_интервалов). Эта функция относится к классу статистических и производит операции над массивами, в ней массив_данных — это ячейки с данными выборки, массив_интервалов — ячейки, содержащие значения интервалов.
Результатом выполнения функции ЧАСТОТА является массив, содержащий частоты вариантов, попадающие в указанные интервалы. На основе этого результирующего массива строятся гистограмма и полигон частот.
Полигон и гистограмма частот строятся по значениям частоты (рис.1).
Рисунок 1. Полигон и гистограмма частот выборочного распределения
Задание 2. Использование пакета «Анализ данных»
В пакете Microsoft Excel имеется набор мощных инструментов для углубленного анализа данных, называемый «Пакет анализа», который может быть использован для решения задач обработки выборочных данных.
Для установки пакета Анализ данных в Microsoft Excel:
Откройте вкладку Файл и выберите пункт Параметры.
Выберите команду Надстройки, а затем в поле Управление выберите пункт Надстройки Excel, нажмите кнопку Перейти.
В окне Доступные надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.
После загрузки пакета анализа в группе Анализ на вкладке Данные становится доступной команда Анализ данных.
Команды надстройки Анализ данных появится на вкладке Данные в группе Анализ.
Рассмотрим этапы вычисления основных показателей описательной статистики средствами «Пакета анализа» на следующем учебном примере. В табл.3 приведены данные по результатам сдачи экзаменов учащихся.
Таблица 3- Результаты ЕГЭ учащихся по трем предметам
Ученик Математика Русский язык Физика
Агеев Андрей 80 80 83
Александров Михаил 10 25 91
Алексеев Анатолий 43 43 41
Андропов Сергей 74 10 95
Аникиев Анатолий 22 24 24
Атласов Виктор 94 9 21
Афанасьев Юрий 13 12 94
Бароненко Анатолий 100 80 15
Барсуков Александр 81 94 61
Барсуков Анатолий 96 91 100
Бартошкин Эдуард 51 46 72
Барыбин Михаил 59 94 81
Белобородов Андрей 26 97 81
Белов Виктор 20 13 22
Белоглазов Юрий 21 21 87
Белоногов Анатолий 89 71 36
Белорусов Александр 46 15 21
Белых Юрий 96 98 100
1. Выберите на ленте Данные в группе Анализ команду Анализ данных. В появившемся окне (рис. 2) выберите строку Описательная статистика и нажмите кнопку ОК.
Рисунок 2 Окно пакета «Анализ данных»
2. В диалоговом окне Описательная статистика (рис. 3):
укажите входной интервал – ссылки на ячейки, содержащие анализируемые данные;
установите флажок в поле Метка в первой строке (если входной интервал включает заголовки столбцов);
в разделе Группирование переключатель установите в положение по столбцам (так как наши данные расположены по странам в столбцах);
указать выходной интервал – ссылку на ячейку, в которую будут выведены результаты анализа;
установите флажок в поле Итоговая статистика (для того чтобы отчет содержал расчеты средней арифметической, моду, медианы, стандартного отклонения, дисперсии и др. характеристик) и Уровень надежности нажать ОК.
Рисунок 3 Окно «Описательная статистика»
После нажатия кнопки ОК Microsoft Excel представит отчет следующего вида (рис. 4).
Математика Русский язык Физика
Среднее 54,9 Среднее 54,8 Среднее 56,2
Стандартная ошибка 1,2 Стандартная ошибка 1,2 Стандартная ошибка 1,2
Медиана 55,5 Медиана 55,5 Медиана 55,0
Мода 75,0 Мода 94,0 Мода 41,0
Стандартное отклонение 27,1 Стандартное отклонение 26,9 Стандартное отклонение 26,4
Дисперсия выборки 732,7 Дисперсия выборки 725,7 Дисперсия выборки 698,9
Эксцесс -1,2 Эксцесс -1,2 Эксцесс -1,3
Асимметричность 0,0 Асимметричность -0,1 Асимметричность 0,0
Интервал 98,0 Интервал 93,0 Интервал 89,0
Минимум 2,0 Минимум 7,0 Минимум 11,0
Максимум 100,0 Максимум 100,0 Максимум 100,0
Сумма 27468,0 Сумма 27411,0 Сумма 28089,0
Счет 500,0 Счет 500,0 Счет 500,0
Уровень надежности(95,0%) 2,4 Уровень надежности(95,0%) 2,4 Уровень надежности(95,0%) 2,3
Рисунок 4. Отчет описательной статистики
Интерпретация полученных данных
На основании проведенного выборочного исследования и рассчитанных по данной выборке показателей описательной статистики с уровнем надежности 95% можно предположить, что средний бал по математике варьируется в пределах от 52,5 до 57,3 (Среднее± уровень надежности 54,9±2,4). Доверительный интервал для генеральной средней от 52,5 до 57,3, пределы изменения значений средней по генеральной совокупности на уровне надежности 95,0%.
Оценим отклонение среднего от медианы 55,5-54,9=0,6 – незначительное. Величина стандартного отклонения 27,1 также является допустимой, поскольку описывает вариацию значений выборки примерно 27 до 82. При этом коэффициент вариации – отношение стандартного отклонения к среднему арифметическому (формула 1). Рассчитайте его самостоятельно на основе данных отчета.
c_v=?/?•100% (1)
Значение коэффициента вариации более 20% свидетельствует о неоднородности ряда и существенной колеблемости признака. Нулевое значение коэффициента асимметрии свидетельствует о нормальном распределении выборки. Эксцесс как показатель остроты пика графика распределения имея отрицательное значение говорит о том, что данное распределение имеет более заостренный пик, чем нормальное. Подобные ситуации складываются и по другим дисциплинам русскому языку и физике.
Самостоятельно сделайте соответствующие выводы по русскому языку и физике.
Файл с электронными таблицами, содержащий практическую работу на двух листах, названных «Задание 1» и «Задание 2» выложите на портал.