Статистические функции Microsoft Excel

Пакет Microsoft Excel не предназначен для комплексного статистического ана­лиза данных в отличие от специализированного статистического программного обеспечения, например, пакета STATISTICA. Однако и на базе электронных таблиц можно провести некоторую статистическую обработку данных для большинства ин-

7. КОМПЬЮТЕРНЫЕ МЕТОДЫ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ ...

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

Установка надстройки «Пакет анализа» производится из меню «Сер­вис/Надстройки», после чего в диалоговом окне «Надстройки» (рис. 7.1) необходимо отметить флажок пункта «Пакет анализа» и нажать кнопку ОК.

Рис. 7.2.Окно анализа данных, вызываемое из меню «Сервис/Анализ данных...»

Рис. 7.1.Установка пакета анализа в Microsoft Excel Если процесс установки завершен успешно, то в меню «Сервис» появляется еще один пункт - Анализ данных (рис. 7.2), а также при создании формул становится доступной новая группа функций - статистические.

7. КОМПЬЮТЕРНЫЕ МЕТОДЫ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ …

В рамках Microsoft Excel с помощью встроенных статистических команд можно провести:

• описательный статистический анализ;

• ранжирование данных;

• графический анализ данных;

• прогнозирование данных;

• регрессионный анализ и др.

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

Таблица 7.2 Статистические функции пакета Microsoft Excel

Функция

Назначение функции и ее аргументы

ВЕРОЯТНОСТЬ

Дисп

ДИСПР ДОВЕРИТ

КВАДРОТКЛ КВПИРСОН

КОРРЕЛ

Возвращает вероятность того, что значение из интервала находится внутри заданных пределов. Если верхний_предел не задан, то возвра­щается вероятность того, что значения в аргументе х_интервал равня­ются значению аргумента нижний_предел.

ВЕРОЯТНОСТЬ(х_интервал; интервал_вероятностей; нижний_предел; верхний_предел). Оценивает дисперсию по выборке ДИСП(число1;число2; ...)

Вычисляет дисперсию для генеральной совокупности ДИСПР(число1;число2; ...)

Возвращает доверительный интервал для среднего генеральной сово­купности

ДОВЕРИТ(альфа; станд_откл;размер)

Возвращает сумму квадратов отклонений точек данных от их среднего КВАДРОТКЛ(число1; число2;...)

Возвращает квадрат коэффициента корреляции Пирсона для точек дан­ных в аргументах известные_значения_у и известные_значения_х КВПИРСОН(известные_значения_у;известные_значения_х) Возвращает коэффициент корреляции между интервалами ячеек мас-сив1 и массив2 КОРРЕЛ(массив1; массив2)

7. КОМПЬЮТЕРНЫЕ МЕТОДЫ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ …

Продолжение табл. 7.2

2

ЛГРФПРИБЛ

ЛИНЕЙН

МАКС

МЕДИАНА

МИН

МОДА

НАКЛОН

НОРМАЛИЗАЦИЯ

В регрессионном анализе вычисляет экспоненциальную кривую, аппрок­симирующую данные, и возвращает массив значений, описывающий эту кривую. Поскольку данная функция возвращает массив значений, она должна вводиться как формула для работы с массивами. Уравнение кривой следующее:

у = b-m^x или у = (b-(m₁^x1)-(m₂^x2)-...-(m_n^xn)) (при наличии нескольких значе­ний х), где зависимые значения у являются функцией независимых зна­чений х. Значения m являются основанием для возведения в степень х, а значения b постоянны. Отметим, что у, хит могут быть векторами. Функция ЛГРФПРИБЛ возвращает массив {m_n;m_n_i; ... ;ту, Ь}. ЛГРФПРИБЛ(известные_значения_у; известные_значения_х; конст; статистика)

Рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим обра­зом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается мас­сив значений, функция должна задаваться в виде формулы массива. ЛИНЕЙН(известные_значения_у; известные_значения_х; конст; стати­стика)

Возвращает наибольшее значение из набора значений МАКС(число1;число2; ...) Возвращает медиану заданных чисел МЕДИАНА(число1; число2; ...)

Возвращает наименьшее значение в списке аргументов МИН(число1;число2; ...)

Возвращает наиболее часто встречающееся или повторяющееся значе­ние в массиве или интервале данных МОДА(число1;число2; ...)

Возвращает наклон линии линейной регрессии для точек данных в ар­гументах известные_значения_у и известные_значения_х. Наклон опре­деляется как частное от деления расстояния по вертикали на расстоя­ние по горизонтали между двумя любыми точками прямой, то есть на­клон - это скорость изменения значений вдоль прямой НАКЛОН(известные_значения_у;известные_значения_х) Возвращает нормализованное значение для распределения, характери­зуемого средним и стандартным отклонением НОРМАЛИЗАЦИЯ(х; среднее; стандартное_откл)

7. КОМПЬЮТЕРНЫЕ МЕТОДЫ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ …

Продолжение табл. 7.2

НОРМОБР

НОРМРАСП

НОРМСТОБР

НОРМСТРАСП

ОТРЕЗОК

ПИРСОН

СРГЕОМ

СРЗНАЧ

СРОТКЛ

СТАНДОТКЛОН

СТАНДОТКЛОНП

СТЬЮДРАСП

СТЬЮДРАСПОБР

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

НОРМСТОБР(вероятность)

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

Вычисляет точку пересечения линии с осью у, используя извест-ные_значения_х и известные_значения_у ОТРЕЗОК(известные_значения_х;известные_значения_у) Возвращает коэффициент корреляции Пирсона г (выборочный коэффи­циент корреляции), безразмерный индекс в интервале от -1,0 до 1,0 включительно

ПИРСОН(массив1; массив2)

Возвращает среднее геометрическое значений массива или интервала положительных чисел СРГЕОМ(число1; число2; ...)

Возвращает среднее арифметическое своих аргументов СРЗНАЧ(число1; число2; ...)

Среднее абсолютных значений отклонений точек данных от среднего СРОТКЛ(число1; число2; ...) Оценивает стандартное отклонение по выборке СТАНДОТКЛОН(число1; число2; ...)

Вычисляет стандартное отклонение по генеральной совокупности СТАНДОТКЛОНП(число1; число2; ...) Возвращает t-распределение Стьюдента СТЬЮДРАСП(х; степени_свободы; хвосты)

Возвращает обратное распределение Стьюдента для заданного числа степеней свободы СТЬЮДРАСПОБР(вероятность;степени_свободы)

7. КОМПЬЮТЕРНЫЕ МЕТОДЫ СТАТИСТИЧЕСКОЙ ОБРАБОТКИ …

Окончание табл. 7.2

ТЕНДЕНЦИЯ

TTECT ФИШЕР ФИШЕРОБР ХИ20БР

ХИ2РАСП

ЧАСТОТА

ЭКСЦЕСС

FPACn

FPACnOBP

Определяет предсказанные значения в соответствии с линейным трен­дом для заданного массива (методом наименьших квадратов) ТЕНДЕНЦИЯ(известные_значения_у; известные_значения_х; но-вые_значения_х; конст)

Возвращает вероятность, соответствующую критерию Стьюдента ТТЕСТ(массив1; массив2; хвосты; тип) Возвращает преобразование Фишера для аргумента х ФИШЕР(х)

Возвращает обратное преобразование Фишера ФИШЕРОБР(у)

Возвращает значение обратное к односторонней вероятности распре­деления г (хи-квадрат) ХИ20БР(вероятность;степени_свободы)

Возвращает одностороннюю вероятность (Р) распределения %² (хи-квадрат, распределения Пирсона) ХИ2РАСП(х; степени_свободы)

Вычисляет частоту появления значений в интервале значений и воз­вращает массив цифр

ЧАСТОТА(массив_данных;массив_карманов) Возвращает эксцесс множества данных ЭКСЦЕСС(число1; число2; ...)

Возвращает F-распределение вероятности (распределение Фишера) РРАСП(х;степени_свободы1;степени_свободы2)

Возвращает обратное значение для F-распределения вероятностей (критерий Фишера) РРАСПОБР(вероятность;степени_свободы1;степени_свободы2)