Использование библиотечных функций Excel для вычисления статистических характеристик
В библиотеке табличного процессора есть ряд специальных функций для вычисления выборочных характеристик, которые можно по назначению разбить на три группы:
• функции, характеризующие центр распределения;
• функции, характеризующие рассевание;
• функции, позволяющие оценить форму эмпирического распределения.
Первую группу функций составляют:
• функция СРЗНАЧ вычисляет среднее арифметическое из одного или нескольких массивов чисел;
• функция СРГАРМ позволяет получить среднее гармоническое множества данных. Среднее гармоническое – это величина, обратная к среднему арифметическому обратных величин;
• функция СРГЕОМ вычисляет среднее геометрическое значений массива положительных чисел. Эту функцию можно использовать для вычисления средних показателей динамического ряда;
• функция МЕДИАНА позволяет получить медиану заданной выборки. Медиана – это элемент выборки, число элементов со значениями больше которого и меньше которого равно. Например, МЕДИАНА(5; 6; 8; 5; 9; 10; 8; 9) равна 8;
• функция МОДА вычисляет количество наиболее часто встречающихся значений в выборке (наиболее вероятная величина).
Вторую группу функций составляют:
• функция ДИСП позволяет оценить дисперсию по выборочным данным – степень разброса элементов выборки относительно среднего значения;
• функция СТАНДОТКЛОН вычисляет стандартное отклонение – характеризует степень разброса элементов выборки относительно среднего значения;
• функция ПЕРСЕНТИЛЬ позволяет вычислить квантили заданной выборки.
В третью группу функций входят:
• функция ЭКССЦЕСС – вычисляет оценку эксцесса по выборочным данным – степень выраженности хвостов распределения, т.е. частоты появления удаленных от среднего значения;
• функция СКОС позволяет оценить асимметрию выборочного распределения - величину, характеризующую несимметричность распределения элементов выборки относительно среднего значения.
Применение некоторых из перечисленных функций рассмотрим на примере.
Пример 1. В таблице, приведенной ниже, приведены сведения о ежемесячной реализации продукции за периоды до начала и после начала рекламной компании.
Требуется найти средние значения и стандартные отклонения приведенных данных.
Решение
1. На рабочем листе подготовим исходные данные для расчетов (рис. 1 ).
1. В ячейку B9 рабочего листа введем формулу для вычисления среднего значения: =СРЗНАЧ(B2:B8), а в ячейку C9 - формулу =СРЗНАЧ(C2:C8).
2. Для вычисления стандартных отклонений в ячейку B10 введем формулу =СТАНДОТКЛОН(B2:B8), а в ячейку C10 – формулу =СТАНДОТКЛОН(C2:C8).
Полученные результаты вычислений приведены на рис. 2.
Применение инструментов Пакета анализа
Кроме функций в табличном процессоре есть набор инструментов для углубленного анализа данных, которые объединены общим названием Пакет анализа. Одним из них является инструмент Описательная статистика, который вычисляет следующие статистические характеристики: среднее, стандартную ошибку (среднего), медиану, моду, стандартное отклонение, дисперсию выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумму, наибольшее, наименьшее, счет, уровень надежности.
Применение некоторых инструментов рассмотрим на примере.
Пример 2.
Даны выборки зарплат основных групп работников банка в условных единицах: администрации (менеджеров), персонала по работе с клиентами, технических служб. Полученные данные приведены в таблице.
Требуется вычислить основные статистические характеристики в группах данных.
Решение
1. Подготовим исходные данные для вычислений на рабочем листе (рис. 3).
2. Проведем статистическую обработку, для чего на ленте Данные в группе Анализ кликнем на пиктограмме Анализ данных (Data Analysis). В появившемся диалоговом окне Анализ данных (Data Analysis) в списке выберем инструмент Описательная статистика (Descriptive Statistics (рис. 4).
3. В открывшемся окне диалога Описательная статистика (рис. 5) в поле Входной интервал (Input Range) укажем исходный диапазон (A2:C10). В рабочем поле Выходной диапазон укажем ссылку на ячейку, где будет помещаться верхняя левая ячейка таблицы результатов. В разделе Группирование установим переключатель По столбцам (Columns)(исходные данные сгруппированы по столбцам). Установим опцию Метки в первой строке (Labels in First Row) (в результирующую таблицу будут помещены надписи столбцов исходной таблицы), установим переключатель Выходной диапазон и в соответствующем ему поле укажем адрес верхней левой ячейки для вывода итогов. Установим опцию Итоговая статистика (Summary Statistics) (рис.5).
Кликнем на ОК. В указанном диапазоне для каждого столбца исходной таблицы будут выведены соответствующие статистические результаты (рис. 6).
Кроме рассмотренных ранее статистических характеристик в полученной таблице есть еще четыре показателя:
• минимум – значение минимального элемента выборки;
• максимум – значение максимального элемента выборки;
• сумма – сумма значений всех элементов выборки;
• счет – количество элементов выборки.