 |
|
Инструменты вычисления параметров распределения в Excel
В Excel довольно полно представлены методы описательной статистики, причем для расчетов можно воспользоваться как отдельными статистическими функциями, так набором средств анализа данных (так называемым пакетом анализа). Рассмотрим вначале основные функции, реализующие методы описательной статистики:
· СРЗНАЧ(число1; число2; ...) – вычисляет среднее значение аргументов
· МЕДИАНА(число1;число2;...) - возвращает медиану заданных чисел
· МОДА(число1;число2; ...) - возвращает моду заданных чисел
· ДИСП(число1;число2; ...) - оценивает дисперсию по выборке
· СТАНДОТКЛОН(число1; число2; ...) - оценивает стандартное отклонение по выборке.
· СКОС(число1;число2; ...) - возвращает асимметрию распределения
Число1, число2, ... — от 1 до 30 аргументов, для которых вычисляется мода, аргумент можно задать диапазоном ячеек
· ЭКСЦЕСС(число1;число2; ...) - возвращает эксцесс множества данных
· СЧЁТ(значение1; значение2; ...) (используется для подсчета количества элементов выборки
· ДОВЕРИТ(альфа;станд_откл;размер) - возвращает доверительный интервал для среднего генеральной совокупности
Для применения этих функций достаточно на листе Excel ввести данные (можно в столбец, строку или блоком; лишь бы одна ячейка –одно число); затем установив курсор в пустой ячейке вызвать необходимую функцию, меню Вставка, Функция . В категории Статистические выбрать одну из вышеуказанных функций
Для анализа данных с помощью «Пакета анализа» следует указать входные данные и выбрать параметры; анализ будет выполнен с помощью подходящей статистической макрофункции, а результат будет помещен в выходной диапазон. Другие средства позволяют представить результаты анализа в графическом виде. Они доступны через команду Анализ данных меню Сервис. Если этой команды нет в меню, необходимо загрузить надстройку Пакет анализа.
Раздел анализа «Описательная статистика» служит для создания одномерного статистического отчета, содержащего информацию о центральной тенденции и изменчивости входных данных. При вызове в появившемся окне необходимо указать входной диапазон данных, параметры и выходной диапазон. Для каждого столбца (строки) значений будут рассчитаны числовые характеристики
Для построения рядов распределения на компьютере можно воспользоваться «Мастером функций» и «Мастером диаграмм» . Для этого введите данные в столбец (строку) на листе таблицы, в соседний столбец введите границы интервалов (определив длину интервала по вышеприведенной методике для примера возьмем d =1,3). Выделите столбец для вывода частот. Количество элементов в возвращаемом массиве должно быть на единицу больше числа элементов в массиве интервалов. Затем вызовете из меню Вставить, Функцию и выберите «Частота», из категории «Статистические».
В появившемся окне заполните аргументы функции. Массив данных – первый аргумент функции «Частота», массив интервалов (массив карманов) – второй аргумент функции «Частота». Закончить ввод, необходимо нажатием клавиш Shift+Ctrl+Enter, так как в результате мы получаем не одно значение (число), а массив значений
Для расчета всех числовых характеристик распределения, построения частотных таблиц, гистограмм, диаграмм ящик с усами, ствол с листьями используется надстройка к Excel – StatPlus.
Для установки надстройки необходимо записать пакет на компьютер. Затем войтя в меню Сервис, Надстройки через кнопку Обзоруказать путь к папке, в которой расположен файл с расширением xla и нажать кнопку ОК.В строке меню появится дополнительная команда StatPlus. Команды описательной статистики находятся в меню StatPlus, Discribtion Statistics.
В поле Data указывается диапозон исходных данных, в полеOutPutуказывается выходной диапозон на этом же листе или новый лист. Желательно диапозону данных присвоить имя.
6. Наглядные методы описательной статистики
Построить гистограмму по полученному ряду распределения в Excel, используя инструмент «Мастер диаграмм» можно в том случае, если интервалы группировки –равномерные. После вызова «Мастера диаграмм» :
- на первом шаге надо указать тип диаграммы – гистограмма
- на втором шаге в качестве диапазона данных указать массив (столбец) вычисленный частот, выбрав кнопку ряды «в строках»;
- на третьем шаге задать параметры диаграммы (подписать заголовок, оси и т.д.)
- на четвертом шаге указать, где расположить гистограмму (на этом же листе или новом)
- нажать кнопку «Готово»
Построить гистограмму можно также, используя инструмент «Гистограмма» Пакета анализа Excel . Для этого надо вызвать меню Сервис, Анализ данных (в Excel 2007 меню Данные, Анализ данных)и заполнить элементы дианового окна инструмента «Гистограмма»: входной диапазон, интервал карманов (по -умолчанию создается автоматически), выходной диапазон, Парето (отсортированная диаграмма - Установите флажок, чтобы представить данные в порядке убывания частоты. Если флажок снят, то данные в выходном диапазоне будут представлены в порядке возрастания отрезков, а трех самых правых столбцов с отсортированными данными не будет), Интегральный процент, вывод графика
Диаграмма типа “ящик с усами” (boxplot) изображает важные характеристики описательной статистики на одном компактном рисунке. Он предложен Джоном Таки (John Tukey) в 1977 г. в основополагающей книге Exploratory Data Analysis. Диаграмма типа “ящик с усами” отображает перечисленные ниже характеристики описательной статистики.
1. Первая квартиль, медиана, третья квартиль и интерквантильный диапазон.
2. Минимальное и максимальное значения.
3. Умеренные и экстремальные выбросы.
Диаграмма типа “ящик с усами” дает хорошее визуальное представление изменчивости данных, а также асимметрии распределения. P-персентиль – такое значение заданного распределения, которое больше p -процентов всех значений распределения (Например, урожайность конкретного хозяйства региона относят к 75 персентили, значит урожайность в этом хозяйстве больше 75% урожайности во всех хозяйствах. Квартиль соответствует 25%, 50% и 75% перцентилям.
Первый компонент диаграммы типа “ящик с усами” называется интерквартиль ((IQR), который простирается от первой до третьей квартили. Для создания этого диапазона нарисуйте прямоугольник (“ящик”) от первой до третьей квартили. Внутри ящика нарисуйте горизонтальную линию на уровне медианы (второй квартили). После отображения интерквартильного диапазона можно приступать к вычислению внутреннего и внешнего ограждений. Внутренние ограждения (inner fences) располагаются в области больше третьей квартили + 1,5´IQR или меньше первой квартили – 1,5´IQR. Внешние ограждения (outer fences) располагаются в области больше третьей квартили + 3´IQR или меньше первой квартили – 3´IQR. Все значения, которые лежат в промежутке между внутренним и внешним ограждениями, называются умеренными выбросами (moderate outlier) и обозначаются символами l.
Все значения, которые лежат за пределами внешних ограждений, называются экстремальными выбросами (extreme outlier) и обозначаются символами ¡. Усы - это вертикальные линии, проведенные от ящика до максимального и минимального значения в областях умеренных выбросов. Эти значения не считаются выбросами.
