Основные возможности пакета Excel для анализа статистических данных

1. Общие сведения

Microsoft Excel предоставляет широкие возможности для анализа статистических данных. При решении простых задач всегда под руками встроенные функции, такие как СРЗНАЧ(), МЕДИАНА() и МОДА(). Если же их оказывается недостаточно, следует обратиться к Пакету анализа.

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

В статистике совокупность результатов измерений называют распределением. Microsoft Excel дает возможность анализировать распределения, используя встроенные статистические функции, функции анализа выборок и генеральной совокупности, а также инструменты Описательная статистика, Гистограмма, Ранг и Персентиль.

2. Основные встроенные статистические функции

Функция СРЗНАЧ() вычисляет среднее арифметическое или просто среднее для последовательности чисел: суммируются числовые значения в интервале ячеек и результат делится на количество этих значений. Эта функция игнорирует пустые, логические и текстовые ячейки.

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

Функция МОДА() возвращает наиболее часто встречающееся значение во множестве чисел.

Функция МАКС() возвращает наибольшее значение среди заданных чисел.

Функция МИН() возвращает минимальное значение среди заданных чисел.

Функция СУММПРОИЗВ() возвращает сумму произведений соответствующих членов двух и более массивов-аргументов (но не более 30 аргументов). Встречающиеся в аргументах нечисловые значения интерпретируются нулями.

Функция СУММКВ() возвращает сумму квадратов аргументов.

3. Анализ выборок и совокупности

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

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

Функция СУММСУММКВ() вычисляет сумму сумм квадратов соответствующих элементов в массивах.

Функция СУММКВРАЗН() вычисляет сумму квадратов разности соответствующих элементов в массивах.

4. Инструмент анализа Описательная статистика

Этот инструмент дает возможность построить таблицу параметров описательной статистики для одного или более наборов входных данных. Для каждого набора входных данных в выходном интервале строится таблица со следующей информацией: Среднее, Стандартная ошибка, Медиана, Мода, Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счет, Наибольший (k), Наименьший (k) (для любого заданного k) и Уровень надежности (доверительный интервал). Статистической обработке подвергается один или несколько наборов данных, располагаемых в интервале, ссылка на который задается в поле Входной интервал. Переключатель Группирование дает возможность уточнить, как размещаются данные: по столбцам или по строкам. Если столбцы или строки данных имеют метки, то при установленном флажке Метки в первой строке / Метки в первом столбце они используются в качестве заголовков столбцов статистических параметров выходной таблицы. Адрес верхней левой ячейки для этой таблицы задается в поле Выходной интервал. При установленном флажке Итоговая статистика создается подробная выходная таблица, установив соответствующие флажки, можно поместить в нее дополнительные данные.

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

5. Инструмент Гистограмма

Гистограммы или линейчатые диаграммы – удобное средство для обработки результатов измерений. Область значений измеряемой величины разбивается на несколько интервалов, называемых также карманами, в которых в виде столбцов откладывается количество попавших в этот интервал измерений, называемое частотой. Карманы не обязательно должны быть равными, но они должны располагаться по возрастанию границ. В принципе поле Интервал карманов можно оставить пустым. Microsoft Excel равномерно распределит карманы в интервале от минимального до максимального значения исходных данных. Число карманов будет равно корню квадратному из количества исходных значений.

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

В столбце Частота выводится число исходных значений, которые больше или равны левой границы кармана, но меньше левой границы следующего кармана. Последним значением столбца является число исходных значений, больших или равных левой границе последнего кармана. Столбец интервалов для карманов дублируется в столбец Карман. Это удобно, если выходной интервал для результатов анализа задан в другом месте, а не рядом с интервалом карманов. Нельзя выходной интервал совместить с исходным интервалом карманов. Так как интервал карманов копируется, то его лучше заполнить числовыми константами, а не формулами. Если все же нужны формулы, то в них обязательно должны использоваться абсолютные ссылки, иначе результаты копирования могут оказаться неверными.

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

Инструмент Гистограмма генерирует таблицу числовых констант. В тех случаях, когда необходима связь с исходными значениями, следует использовать формулы с табличной функцией ЧАСТОТА(массив данных; массив карманов).

6. Ранг и Персентиль

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

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

Функция ПЕРСЕНТИЛЬ(массив, k) используется для определения элемента во входном множестве массив с указанным уровнем персентиля k, который задается в виде десятичной дроби между 0 и 1.

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

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

7. Генерирование случайных чисел

Существует встроенная функция СЛЧИС(), предназначенная для генерирования равномерно распределенных случайных чисел в интервале от 0 до 1. Другими словами, при каждом обращении к функции СЛЧИС() она с равной вероятностью возвращает случайное число между 0 и 1.

Средства генерации случайных чисел из Пакета анализа дают возможность получать и неравномерные распределения. Эти массивы случайных чисел могут использоваться при решении задач методом Монте-Карло. Реализованы следующие типы распределений: Равномерное, Нормальное, Бернулли, Биномиальное, Пуассона и Дискретное. Седьмой тип – Модельное дает возможность заполнить числами массив по определенному закону.

8. Построение выборок из генеральной совокупности

Инструмент анализа ВЫБОРКА дает возможность извлекать подмножества чисел из заданного большого множества (генеральной совокупности) чисел. Из входного интервала ячеек можно выбрать определенное количество величин либо случайным образом, либо каждое n-ое значение и поместить их в заданный выходной интервал.

9. Вычисление скользящего среднего

Скользящее среднее – это методика прогнозирования, которая упрощает анализ тренда (тенденции), путем сглаживания флуктуаций результатов измерений по некоторому периоду времени. Эти флуктуации могут быть вызваны случайным «шумом», т.е. побочным результатом методики измерения.

10. Линейная и экспоненциальная регрессии

В Excel имеется пять функций для линейной регрессии ЛИНЕЙН(), ТЕНДЕНЦИЯ(), ПРЕДСКАЗ(), НАКЛОН() и СТОШYX() и две функции для экспоненциальной регрессии ЛГРФПРИБЛ() и РОСТ(). Эти функции вводятся как табличные формы и возвращают результат в виде интервала массива. Каждая из функций может иметь один или несколько аргументов.

Линейная регрессия дает возможность наилучшим образом провести прямую линию через точки одномерного массива.

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

Поиск по сайту: