Модели и технологии решения задач статистического анализа

Сегодня новые информационные технологии широко используются во всех областях знаний и во всех сферах человеческой деятельности. Многообразие функциональных возможностей обусловливает использование информационных технологий и для качественного анализа различных экономических и финансовых ситуаций. Для анализа данных широко используются методы классической статистики: факторный и кластерный анализ, описательная статистика, гистограммы, экспоненциальное сглаживание и др. Анализ данных сопровождается выполнением большого объема разнообразных вычислений: модального значения и медианы, средних величин, дисперсии, корреляции, ковариации, ранга, персентили, квартили, квантили и др.

Кроме того, в ходе анализа выполняются различные виды оценок, группировок, сравнений и сортировок эмпирических данных; нахождение минимального, максимального, среднего значений и ряд других операций. Корректное применение этих методов позволяет выявить функциональные закономерности в различных массовых социальных процессах, строить математические модели и др.

Табличные процессоры позволяют осуществить все это многообразие видов аналитической работы с помощью встроенных функций (категория Статистические, рис. 9.40) и надстройки "Пакет анализа" (рис. 9.43)^[1].

Применение статистических функций облегчает пользователю статистический анализ данных. Количество доступных статистических функций в MS Excel 2010 увеличилось, ряд функций был оптимизирован в целях повышения точности, имена некоторых статистических функций приведены в соответствие с терминами, используемыми научным со-

Рис. 9.40. Мастер функций, категория Статистические

обществом, а также с другими именами функций MS Excel. Например, алгоритм функции БЕТАРАСП заменен новым, более точным (новое название функции БЕТА.РАСП). Кроме того, новые имена функций лучше описывают их назначение. Например, функция КРИТБИНОМ возвращает обратное значение биномиального распределения, поэтому имя БИНОМ.ОБР является для нее более подходящим. В функции СЛЧИС теперь используется новый алгоритм получения случайных чисел. Для того чтобы иметь возможность использовать все статистические функции, следует загрузить надстройку "Пакет анализа".

Если необходимо использовать электронную таблицу совместно с другими пользователями, у которых не установлено приложение MS Excel 2010, вместо переименованных функций можно использовать функции совместимости. Предыдущие версии MS Excel распознают старые имена функций и возвращают правильные результаты. Однако если нет необходимости обеспечивать обратную совместимость, рекомендуется использовать переименованные функции. Чтобы увидеть все функции совместимости, необходимо в мастере функций выбрать категорию Совместимость и указать нужную функцию.

В табл. 9.1 приведено описание некоторых наиболее распространенных статистических функций (по алфавиту), применяемых при анализе финансовых и экономических данных. Информацию о других функциях пользователь может найти в справочной подсистеме.

Таблица 9.1

Описание некоторых статистических функций

Название	Описание
БИНОМ.ОБР	Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше или равно заданному
БИНОМ.РАСП	Возвращает отдельное значение биномиального распределения
ДИСП.В	Оценивает дисперсию по выборке
ДИСП.Г	Вычисляет дисперсию для генеральной совокупности
ДИСПА	Оценивает дисперсию но выборке, включая числа, текст и логические значения
ДИСПРА	Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения
КВАРТИЛЬ.ВКЛ	Возвращает квартиль множества данных
КВАРТИЛЬ.ИСКЛ	Возвращает квартиль набора данных на основе значений процентиля от 0 до 1, исключая эти числа
МАКС	Возвращает наибольшее значение в списке аргументов
МЕДИАНА	Возвращает медиану заданных чисел
МИН	Возвращает наименьшее значение в списке аргументов
МОДА.НСК	Возвращает вертикальный массив из наиболее часто встречающихся (повторяющихся) значений в массиве или диапазоне данных
МОДА.ОДН	Возвращает значение моды множества данных
НОРМ.ОБР	Возвращает обратное нормальное распределение
ПОРМ.РАСП	Возвращает нормальную функцию распределения
ПЕРЕСТ	Возвращает количество перестановок заданного числа объектов, которые выбираются из общего числа объектов
ПРОЦЕНТИЛЬ.ВКЛ	Возвращает k-ю персентиль для значений диапазона
ПРОЦЕНТРАНГ.ВКЛ	Возвращает процентную норму значения в множестве данных
РАНГ.РВ	Возвращает ранг числа в списке чисел
СРЗНАЧ	Возвращает среднее арифметическое аргументов
СТАНДОТКЛОН.В	Оценивает стандартное отклонение но выборке
СТАНДОТКЛОН.Г	Вычисляет стандартное отклонение по генеральной совокупности
СЧЕТ	Подсчитывает количество чисел в списке аргументов
СЧЕТЕСЛИ	Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию
СЧЕТЕСЛИМН	Подсчитывает количество ячеек внутри диапазона, удовлетворяющих нескольким условиям
СЧИТАТЬПУСТОТЫ	Подсчитывает количество пустых ячеек в диапазоне
ТЕНДЕНЦИЯ	Возвращает значения в соответствии с линейным трендом
ЧАСТОТА	Возвращает распределение частот в виде вертикального массива

Разберем несколько примеров, в которых применяются приведенные функции.

Пример 9.23. В супермаркете есть три точки для размещения товаров. На каждой точке размещаются товары одного вида. Сколько существует способов для размещения товаров пяти видов, если один вид товара размещается не более чем на одной точке?

Решение

Размещениями называются конечные упорядоченные множества из элементов данного множества. Для вычисления числа размещений в библиотеке функций табличного процессора есть специальная функция ПЕРЕСТ(число; число_выбранных), относящаяся к категории статистических функций (рис. 9.41). Аргумент "число" в данной задаче представляет "Количество видов товаров", аргумент "число выбранных" – "Количество точек".

Рис. 9.41. Применение функции Перест

Пример 9.24. Дан набор случайных значений дискретной случайной величины: 10, 14, 5, 6, 18, 6, 13. Требуется вычислить математическое ожидание, дисперсию, стандартное отклонение, медиану, моду, верхнюю квартиль и квантиль со значением 0,1.

Прежде чем приступать к решению примера, дадим определения вычисляемых характеристик.

Математическое ожидание – среднее значение, одна из важнейших характеристик распределения вероятностей случайной величины. Определяется по функции СРЗНАЧ(число1; число2; ...).

Дисперсия (от лат. dispersio – рассеяние) – в математической статистике и теории вероятностей наиболее употребительная мера рассеивания, т.е. отклонения от среднего. Для выборочной совокупности дисперсия определяется по функции ДИСП.В(число1; число2; ...).

Стандартное отклонение – понятие теории вероятностей и математической статистики. Мера разброса случайной величины вокруг ее среднего значения. Для выборочной совокупности стандартное отклонение определяется по функции СТАНОТКЛОН.В(число1; число2; ...).

Медиана – это серединное значение признаках Медиана разбивает выборку на две равные части. Половина значений переменной лежит ниже медианы, половина – выше. Медиана даст общее представление о том, где сосредоточены значения переменной, иными словами, где находится ее центр. Медиана определяется по функции МЕДИАНА(число1; число2; ...).

Мода представляет собой максимально часто встречающееся значение переменной. Определяется но функции МОДА.ОДН(число1; число2;...). Если набор данных не содержит повторяющихся точек данных, функция МОДА.ОДН возвращает значение ошибки #Н/Д.

Квартили представляют собой значения, которые делят две половины выборки (разбитые медианой) еще раз пополам (от слова "кварта" – четверть).

Различают верхнюю квартиль, которая больше медианы и делит пополам верхнюю часть выборки (значения переменной больше медианы), и нижнюю квартиль, которая меньше медианы и делит пополам нижнюю часть выборки.

Нижнюю квартиль часто обозначают символом 25%, это означает, что 25% значений переменной меньше, чем нижняя квартиль.

Верхнюю квартиль часто обозначают символом 75%, это означает, что 75% значений переменной меньше, чем верхняя квартиль.

Таким образом, три точки – нижняя квартиль, медиана и верхняя квартиль – делят выборку на четыре равные части.

Квартиль определяется по функции КВАРТИЛЬ.ВКЛ (массив; часть). Аргумент часть может принимать только пять следующих значений: 0 – наименьшая величина, 1 величина 25-го персентиля, 2 – медиана (величина 50-го персентиля), 3 – величина 75-го персентиля, 4 – наибольшая величина.

Персентиль (перцентиль, процентиль) – это сотая доля объема измеренной совокупности, выраженная в процентах, которой соответствует определенное значение признака.

Вместо функции КВАРТИЛЬ.ВКЛ (массив; часть) для получения наименьшего и наибольшего значений можно использовать функции МИН(число1; число2; ...) и МАКС (число 1; число2;...) соответственно, а для получения медианы – функцию МЕДИАНА(число1; число2;...). Эти функции вычисляются быстрее, чем функция КВАРТИЛЬ.ВКЛ, особенно в случае больших массивов данных.

Квантиль – это точка на числовой оси измеренного признака, которая делит всю совокупность упорядоченных измерений на две группы с известным соотношением их численности. К квантилям относятся медиана (квантиль со значением 0,5), квартили (например, верхняя квартиль – это квантиль со значением 75%, или 0,75), персентили. Вычисляется квантиль по функции ПРОЦЕНТИЛЬ.ВКЛ (массив; k), где массив – совокупность значений; k – значение процентиля в интервале от 0 до 1 включительно.

Решение примера 9.24

Речь идет о выборочной совокупности, т.е. случайно выбранной из генеральной совокупности некоторой ее части. Исходя из этого, будем применять функции, относящиеся именно к выборочной совокупности (рис. 9.42).

Рис . 9.42. Решение примера 9.24

MS Excel 2010 в своем составе содержит компоненты, предоставляющие доступ к дополнительным функциям и командам. Эти компоненты получили название "надстройки". "Пакет анализа" является одной из наиболее популярных надстроек, которая реализует функции расширенного анализа данных. Чтобы использовать надстройки, их необходимо установить и активировать. Для этого выполняются действия вкладка Файл/раздел Параметры/Надстройки/Перейти. В появившемся диалоговом окне Надстройки (рис. 9.43) следует установить флажок в поле Пакет анализа и нажать кнопку ОК.

Рис. 9.43. Диалоговое окно Надстройки

На вкладке Данные будет добавлена группа Анализ, которая содержит кнопку для инструмента "Анализ данных" (рис. 9.44). При нажатии на данную кнопку появится диалоговое окно Анализ данных (рис. 9.45) для выбора инструментов анализа.

Рис. 9.44. Вкладка Данные/группа Анализ/ кнопка Анализ данных

Рис. 9.45. Окно Анализ данных

Пример 9.25. Даны выборки зарплат основных групп работников банка: администрации (менеджеров), персонала по работе с клиентами, технических служб. Полученные данные приведены в таблице на рис. 9.46. Требуется вычислить основные статистические характеристики в группах данных.

Рис. 9.46. Выборка зарплат работников банка для примера 9.25

Для решения задачи воспользуемся надстройкой "Описательная статистика" из анализа данных. В состав описательной статистики входят такие характеристики, как среднее, стандартная ошибка, медиана, мода, стандартное отклонение, дисперсия выборки, эксцесс, асимметричность, интервал, минимум, максимум, сумма, количество. Цель описательной статистики – обобщить первичные результаты, полученные в результате наблюдений и экспериментов.

Стандартная ошибка характеризует колебания средней. При этом необходимо отметить, что чем больше объем выборки, тем меньше разброс средних величин.

Эксцесс есть степень крутости эмпирического распределения но отношению к нормальному. Обычно если эксцесс положителен, то пик заострен, если отрицательный, то пик закруглен. Эксцесс нормального распределения равен нулю.

Асимметричность – коэффициент асимметрии в теории вероятности – величина, характеризующая асимметрию распределения данной случайной величины. Принято считать, что асимметрия выше 0,5 (независимо от знака) считается значительной. Если асимметрия меньше 0,25, она считается незначительнй.

Решение примера 9.25

Выбираем инструмент "Описательная статистика": вкладка Данные/группа Анализ/кнопка Анализ данных (рис. 9.47).

Рис. 9.47. Анализ данных (описательная статистика)

Заполняем диалоговое окно Описательная статистика (рис. 9.48). Входной интервал – таблица "Выборка зарплат" вместе с шапкой; группирование – "по столбцам"; первую строку таблицы берем в качестве меток первой строки; задаем параметры вывода – верхнюю левую точку выходного интервала; итоговую статистику – для создания подробной выходной таблицы; уровень надежности – 95%. Результат можно разместить на существующем листе, или новом рабочем листе, или новой рабочей книге.

В результате получаем таблицу следующего вида (рис. 9.49).

Рис. 9.48. Диалоговое окно "Описательная статистика"

Рис. 9.49. Основные статистические характеристики

Пример 9.26. Магазин продает мужские костюмы. Распределение спроса по размерам является нормальным с математическим ожиданием М = 48 и стандартным отклонением σ = 2. Вычислить процент спроса на 52-й размер.

Решение

Воспользуемся функцией НОРМ.РАСП(х, среднее, стандартное_откл, интегральная), которая возвращает нормальную функцию распределения для указанного среднего и стандартного отклонения. Эта функция очень широко применяется в статистике, в том числе при проверке гипотез.

Аргументы функции НОРМ.РАСП:

• х – значение, для которого строится распределение. В нашем примере размер х = 52.

• Среднее – среднее арифметическое распределения. В нашем примере это М = 48.

• Стандартное_откл – стандартное отклонение распределения. В нашем примере σ = 2.

• Интегральная – логическое значение, определяющее форму функции. Если аргумент "интегральная" имеет значение ИСТИНА, функция НОРМ.РАСП возвращает интегральную функцию распределения; если этот аргумент имеет значение ЛОЖЬ, возвращается весовая функция распределения. В нашем примере значение ЛОЖЬ, так как речь идет о весовой функции распределения и мы ищем результат для определенной точки х.

Создадим модель и заполним данными задачи (рис. 9.50).

Таким образом, получили ответ, что спрос на 52-й размер составляет 2,7%.

Рис. 9.50. Функция НОРМ.РАСП