Статистический анализ

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

Для работы с инструментами анализа статистические данные следует представить в виде строк или столбцов рабочего листа. Совокупность ячеек, содержащих анализируемые данные, называется входным диапазоном. Для проведения анализа данных следует указать входной и выходной диапазоны для размещения результатов анализа. Выходной диапазон задается ссылкой на левую верхнюю ячейку. Результаты анализа могут быть представлены в графическом виде.

9.4.1. Оценка параметров распределения

При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. Для этого используются встроенные статистические функции Excel и команды из списка команд Анализ данных:

· Описательная статистика ( команда );

· Гистограмма;

· Ранг и персентиль.

Часто используемыми статистическими функциями являются функции, которые вычисляют:

· СРОТКЛ - среднее абсолютных значений отклонений точек данных от среднего;

· СРЗНАЧ - среднее арифметическое своих аргументов;

· МАКС - максимальное значение из списка аргументов;

· МЕДИАНА - медиану заданных чисел, т.е. значение в середине массива данных;

· МИН - минимальное значение из списка аргументов;

· МОДА - значение моды массива данных, т.е. наиболее часто встречающееся значение;

· СЧЁТ - количество чисел в списке аргументов, игнорируя пустые ячейки и ячейки с текстом;

· СЧЁТЗ - количество значений в списке аргументов, т.е. число непустых ячеек;

· СТАНДОТКЛОН - оценку стандартного отклонения по выборке, т.е. оценку того, насколько широко разбросаны точки данных относительно их среднего.

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

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

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

Команда Ранг и персентиль используется для вывода таблицы, содержащей порядковый и процентный ранги для каждого значения в наборе данных. Ранг – это порядковый номер некоторого значения в упорядоченном ряду входных данных. Процентный ранг – это процент входных значений, которые меньше указанного значения.

9.4.2. Генерация случайных чисел

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

Команда Анализ данных/Генерация случайных чисел открывает окно диалога для установки вида и параметров распределения. Команда Анализ данных/Дискретное распределение предназначена для создания пользовательского распределения на основе заданных значений исходов и соответствующих им вероятностей. Команда Анализ данных/Выборка позволяет извлечь подмножество чиселиз некоторого входного диапазона случайным или неслучайным образом, например каждого 7 -го значения. Команда Анализ данных/Скользящее среднее предназначена для сглаживания колебаний некоторых измерений (за счет случайного шума) по временным интервалам.

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

Регрессия – это статистический метод, позволяющий найти уравнение, которое наилучшим образом описывает множество данных, представленное набором точек в n-мерном пространстве. Термин регрессия в статистике используется в основном для предсказания тенденции некоторых данных. Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Например, на спортивные качества атлета влияют несколько факторов, включая возраст, рост и вес. Регрессия пропорционально распределяет меру качества по этим трем факторам на основе данных функционирования атлета. Результаты регрессии впоследствии могут быть использованы для предсказания качеств нового, непроверенного атлета.

Регрессионный анализ использует сложные уравнения для анализа больших множеств данных и построения соответствующих кривых зависимости. Функции регрессионного анализа ЛИНЕЙН и ЛГРФПРИБЛ вводятся в виде формулы массива и возвращают массив результатов.