Анализ распределения данных

При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. В статистике набор данных, полученных путем измерений, называется распределением. Excel предлагает ряд средств, предназначенных для анализа распределения данных. Это встроенные статистические функции, функции для оценки разброса данных, а также инструмент Ранг и персентиль (Rank And Percentile).

ПРИМЕЧАНИЕ

Анализировать распределения данных можно также с помощью инструментов Описательная статистика (Descriptive Statistics) и Гистограмма (Histogram), которые входят в состав надстройки Пакет анализа. Подробнее об этих инструментах рассказывается далее в разделе «Инструменты пакета анализа».

Встроенные статистические функции

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

ПРИМЕЧАНИЕ

Кроме стандартных средств, Excel предлагает набор других более сложных функций: ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), оперирующих массивами данных. Подробнее о них рассказывается в разделе «Линейная и экспоненциальная регрессии» данной главы.

Функция СРЗНАЧ

Функция СРЗНАЧ (AVERAGE) вычисляет среднее арифметическое: ряд числовых значений суммируется, а затем делится на количество значений в этом ряду. Функция имеет следующие аргументы: число 1; число 2; …; количество которых может достигать 30, при этом, если в число аргументов попадают ссылки на пустые ячейки и ячейки, содержащие логические и текстовые значения, ошибки не возникнет — они просто будут проигнорированы. Например, чтобы вычислить среднее значение в ячейках В4:В15 рабочего листа, можно записать выражение

=(В4+В5+Вб+В7+В8+В9+В10+В11+В12+В13+В14+В15)/12,

но гораздо проще применить формулу =СРЗНАЧ(В4:В15).

Функции МЕДИАНА, МОДА, МКС, МИН и СЧЕТ

Аргументами всех этих функций может быть диапазон ячеек или список чисел, разделенных знаком точки с запятой, например: число 1; число 2;…. Их количество не должно превышать 30. В качестве аргументов нельзя использовать текстовые,

«ошибочные» и логические значения, но если аргументом является ссылка на ячейку с текстом, ошибкой или логическим значением, она просто игнорируется. Ниже приводится краткое описание указанных функций.

? Функция МЕДИАНА (MEDIAN) вычисляет медиану исходных чисел. Медиана — это число, являющееся «серединой» множества чисел, то есть половина чисел набора имеют значения большие, чем медиана, а половина чисел — меньшие, чем медиана. Если количество чисел четное, функция возвращает среднее значение между двух чисел, находящихся в середине множества.

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

? Функция МАКС (МАХ) возвращает наибольшее значение из диапазона, заданного аргументами.

? Функция МИН (MIN) возвращает наименьшее значение из диапазона, заданного аргументами.

? Функция СЧЕТ (COUNT) подсчитывает количество чисел в диапазоне, заданном аргументами. Даты и формулы приравниваются к числам.

ПРИМЕЧАНИЕ

Для определения количества непустых ячеек (независимо от их содержимого) используется функция СЧЕТЗ (COUNTA), принадлежащая к разряду дополнительных, общая информация о которых представлена далее в разделе «А-версии статистических функций».

Функции СУММЕСЛИ и СЧЕТ ЕС Л И

Функция СУММЕСЛИ (SUMIF) аналогична функции СУММ, но прежде чем произвести операцию сложения, она проверяет каждую ячейку диапазона суммирования на выполнение условия. Ее аргументы: диапазон; критерий; диапазон суммирования, где в качестве аргумента диапазон задается диапазон проверяемых ячеек, критерий — условие, которое должно выполняться в каждой ячейке проверяемого диапазона, а диапазон

^суммирования — это те ячейки, значения которых будут складываться. Так, если для столбца, в котором записаны названия месяцев, определено имя Месяц, а в смежном столбце с названием Продажи введены данные о продажах за каждый месяц, формула

=СУММЕСЛИ(Месяцы;"Июнь";Продажи) вернет значение объема продаж в июне. Второй пример: по формуле =СУММЕСЛИ(Прода-жи;">=999";Продажи) можно вычислить совокупный объем продаж только для тех месяцев, в которых каждая отдельная выручка превысила $999.

СОВЕТ -

Для составления сложных формул удобно использовать надстройку Мастер суммирования. Подробнее об этом рассказывается в разделе «Создание формул с помощью Мастера суммирования» главы 12.

Функция СЧЕТЕСЛИ (C0UNTIF) аналогичным образом определяет количество ячеек, удовлетворяющих заданному критерию. Она оперирует похожими аргументами: диапазон; критерий. В условиях приведенного выше примера можно узнать количество месяцев, в которых объем продаж оказался ниже $600. Для этого достаточно составить формулу =СЧЕТЕСЛИ(Продажи;"<600").

СМОТРИ ТАКЖЕ

Две последние формулы содержат условные выражения. Их создание и применение подробно рассматривается в разделе «Условные выражения» главы 12. Также в примерах двум столбцам присвоены названия. Этот процесс описывается в разделе «Присвоение имен ячейкам и диапазонам» той же главы.

Функции для вычисления порядковых статистик

Пакет анализа включает в себя несколько функций, предназначенных для вычисления порядковых статистик — характеристик распределения, которые определяются на основе упорядоченного ряда входных значений: ПРОЦЕНТРАНГ (PERCENTRANK), ПЕРСЕНТИЛЬ (PERCENTILE), КВАРТИЛЬ (QUARTILE), НАИМЕНЬШИЙ (SMALL), НАИБОЛЬШИЙ (LARGE) и РАНГ (RANK).

Функция ПРОЦЕНТРАНГ

Функция ПРОЦЕНТРАНГ (PERCENTRANK) возвращает процентную норму значения в наборе данных. С ее помощью, например, можно создать таблицу значений в процентах, привязанную к исходному диапазону так, что при изменении исходной величины соответственно корректировалось значение (ранг) в процентах. Мы применили функцию ПРОЦЕНТРАНГ для оценивания положения студентов в общем рейтинге (столбец Е на рис. 17.1).

Рис. 17.1. Функция ПРОЦЕНТРАНГ связывает процентные значения с входными

Функция ПРОЦЕНТРАНГ имеет следующие аргументы: массив; х; разрядность, где массив — массив или диапазон данных с числовыми значениями (в нашем примере D2:D1001), х — значение, для которого определяется его ранг, а необязательный аргумент разрядность определяет количество значащих цифр для выходного значения. Если этот аргумент опущен, оставляются три цифры (в формате 0,ххх или хх,х%).

А-версии статистических функций

В Excel основной набор встроенных статистических функций дополняется их А-версиями, которые позволяют работать не только с числами, но также с текстовыми и логическими значениями. В их число входят: СРЗНАЧА (AVERAGEA), СЧЕТЗ (CОUNTA), МАКСА (МАХА), МИНА (MINA), СТАНДОТКЛОНА (STDEVA), СТАНДОТКЛОНПА (STDEVPA), ДИСПА (VARA) и ДИСПРА (VARPA). Все они имеют одинаковые аргументы (значение 1; значение 2; …), общее количество которых не должно превышать 30.

Их стандартные аналоги при расчетах не учитывают ячейки, содержащие текстовые значения. Так, если в диапазон из 10 ячеек попала одна строка символов, стандартная функция СРЗНАЧ проигнорирует эту ячейку и при вычислении среднего поделит всю

сумму на 9. А функция СРЗНАЧА будет делить все на 10. Такая особенность А-версий статистических функций может очень пригодиться, если в вычислениях необходимо учитывать все ячейки диапазона, например, когда применяются формулы, возвращающие при выполнении определенных условий текстовые «флажки» типа «нет данных». Подробнее функции СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПА и ДИСПРА рассматриваются далее в этой главе.

Функции ПЕРСЕНТИЛЬ и КВАРТИЛЬ

Функция ПЕРСЕНТИЛЬ (PERCENTILE) используется для определения того, какой элемент входного диапазона соответствует заданному уровню в процентах. Эта функция имеет два аргумента: массив; к. Процентиль к задается в виде десятичной дроби в диапазоне от 0 до 1. Например, чтобы определить, какой балл на листе, изображенном на рис. 17.1, представляет 87-й процентиль, запишем формулу

=ПEPCEHTИЛЬ(D2:D1001;0,87).

Функция КВАРТИЛЬ (QUARTILE), имеющая аргументы массив и часть, работает аналогично функции ПЕРСЕНТИЛЬ, за исключением того, что она может возвращать только минимальный процентиль или любой из квартилей набора данных. Аргумент массив — это массив или диапазон ячеек с числовыми значениями, для которых определяются значения квартилей, а часть — значение, которое требуется вернуть, согласно представленной ниже табл. 17.1.

Таблица 17.1. Значения аргумента «часть» функции КВАРТИЛЬ

Аргумент Возвращаемое значение

1                                                         25-й процентиль

2                                                         Медиана (50-й процентиль)

3                                                         75-й процентиль

4                                                         Наибольшее значение

СОВЕТ

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

Функции НАИМЕНЬШИЙ и НАИБОЛЬШИЙ

Функции НАИМЕНЬШИЙ (SMALL) и НАИБОЛЬШИЙ (LARGE) возвращают соответственно k-e наименьшее и k-e наибольшее значения из набора данных. Обе функции имеют одинаковые аргументы: массив; k, где k определяет номер позиции от наибольшего или наименьшего значения во входном массиве данных. Например, чтобы найти 15-й наибольший балл на листе, изображенном на рис. 17.1, удобно применить формулу

=НАИБОЛЬШИЙ(D2:D1001;15).

Функция РАНГ

Функция РАНГ (RANK) возвращает ранг числа в наборе числовых данных. Ранг числа — это его порядковый номер относительно других значений в списке. (Если список отсортировать, ранг числа станет равносилен его позиции.) Эта функция имеет следующие аргументы: число; ссылка; порядок, где число — число, для которого определяется ранг, ссылка — ссылка на диапазон, содержащий числовые данные (другие значения в диапазоне игнорируются), а порядок — величина, определяющая способ упорядочения. Так, чтобы выяснить, какой ранг имеет балл 1200 на листе, представленном на рис. 17.1, можно применить формулу =PAHГ(1200;D2:D1001).

По умолчанию максимальное значение имеет ранг 1, второе наибольшее — ранг 2 и т. д. Если функция РАНГ не в силах найти точного соответствия между первым аргументом и одним из входных значений, она возвращает ошибку #Н/Д.

Функции для оценки разброса данных

Дисперсия и стандартное отклонение являются статистическими характеристиками разброса множества значений в генеральной совокупности данных. Стандартное отклонение — это квадратный корень из дисперсии. Как правило, около 68% значений случайной величины, имеющей нормальное распределение, находится в пределах одного стандартного отклонения от среднего значения, и около 95% — в пределах удвоенного стандартного отклонения. Большое стандартное отклонение указывает на то, что данные сильно рассеяны относительно среднего значения, а малое — на то, что случайные значения компактно расположены вокруг своего среднего.

Четыре статистические функции ДИСП (VAR), ДИСПР (VARP), СТАНДОТКЛОН (STDEV) и СТАНДОТКЛОНП (STDEVP) — вычисляют дисперсию и стандартное отклонение для набора чисел, ограниченного диапазоном листа. Прежде чем применять эти функции, необходимо определить, являются ли представленные значения генеральной совокупностью данных или же они представляют выборку из этой совокупности. Функции ДИСП и СТАНДОТКЛОН работают только с выборками из генеральной совокупности, а функции ДИСПР и СТАНДОТКЛОНП — со всей совокупностью данных.

Функции ДИСП и СТАНДОТКЛОН

Функции ДИСП и СТАНДОТКЛОН вычисляют, соответственно, дисперсию и стандартное отклонение в предположении, что их аргументы представляют собой выборку из генеральной совокупности данных. Обе функции имеют одинаковые аргументы {число 1; число 2; …), количество которых не должно превышать 30. На листе, изображенном на рис. 17.2, представлены результаты сдачи экзаменов пятью студентами, при этом также предполагается, что оценки в диапазоне В4:Е8 являются частью генеральной совокупности данных.

В ячейке J3 записана формула =ДИСП(В4:Е8), и мы видим дисперсию для этой выборки,

а стандартное отклонение для этой же выборки получено в ячейке J4 по формуле

=СТАНДОТКЛОН(В4:Е8).

Рис. 17.2. Функции ДИСП и СТАНДОТКЛОН позволяют оценить дисперсию экзаменационных оценок по выборке

Предполагая, что экзаменационные оценки студентов укладываются в нормальное распределение, можно ожидать, что около 68% студентов получили балл между 83,65 (среднее значение 89,20 минус стандартное отклонение 5,55) и 94,75 (89,2 плюс 5,55 соответственно).

Функции ДИСПР и СТАНДОТКЛОНП

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

Для примера положим, что значения в ячейках В4:Е8 представляют генеральную совокупность (см. рис. 17.2). Тогда дисперсию и стандартное отклонение для них можно рассчитать по формулам =ДИСПР(В4:Е8) и =СТАНДОТКЛОНП(В4:Е8). Первая из них возвратит значение 29,26, а вторая — 5,41.

СОВЕТ

Функции ДИСП, ДИСПР, СТАНДОТКЛОН и СТАНДОТКЛОНП не включают в расчеты текстовые значения и пустые ячейки. Для того чтобы учесть последние, следует применить Аверсии этих функций: ДИСПА, ДИСПРА, СТАНДОТКЛОНА и СТАНДОТКЛОНПА. Обзор Аверсий функций находится в разделе «А-версии статистических функций» этой главы.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.