Статистические расчеты средствами Excel
Вычисление статистических характеристик
В Excel имеется несколько способов вычисления статистических характеристик:
по формулам;
с помощью статистических функций;
с помощью надстройки Пакет анализа.
Таблица 3
Статистическая величина |
Формула |
Функция |
Сумма |
|
СУММ() |
Среднее арифметическое |
|
Срзнач() |
Среднее линейное отклонение |
|
Сроткл() |
Дисперсия по генеральной совокупности |
|
Диспр() |
Дисперсия по выборке |
|
Дисп() |
Среднее квадратичное отклонение |
|
Стандотклонп() |
Смещенное среднее отклонение (по выборке) |
|
Стандотклон() |
В таблице 3 приведены формулы и соответствующие им функции для вычисления некоторых статистических характеристик.
Кроме того, имеются функции для вычисления следующих статистических характеристик:
количество значений – СЧЕТ();
максимум ‑ Макс();
минимум ‑ Мин();
мода ‑ Мода();
медиана ‑ Медиана().
Чтобы воспользоваться надстройкой Пакет анализа, надо:
На вкладке Данные в группе Анализ выбрать команду Анализ данных.
В открывшемся диалоговом окне выбрать строку Описательная статистика.
В поле Входной интервал указать диапазон данных, для которых надо получить статистические оценки.
Если диапазон данных выделен вместе с заголовком, установить флажок Метки в первой строке;
Выбрать вариант размещения выходных данных: текущий рабочий лист, новый рабочий лист или новая рабочая книга.
В случае размещения выходных данных на текущем листе включить режим Выходной интервал и указать левую верхнюю ячейку диапазона, в который должны быть выведены результаты.
Установить флажок Итоговая статистика.
Щелкнуть по кнопке ОК.
Экстраполяция
Экстраполяция – это прогнозирование неизвестных значений путем продолжения функции за границы области известных значений.
Экстраполяцию динамического ряда в Excel можно выполнить различными способами.
I способ – про помощи операции автозаполнения:
Выделить ряд данных.
Правой кнопкой мыши протащить маркер заполнения на нужное количество ячеек.
В открывшемся контекстном меню выбрать нужный пункт:
Линейное приближение – для заполнения ячеек значениями, вычисленными на основе аппроксимации исходных данных линейной функцией;
Экспоненциальное приближение ‑ для заполнения ячеек значениями, вычисленными на основе аппроксимации исходных данных экспоненциальной функцией;
Прогрессия – заполнение ячеек арифметической или геометрической прогрессией.
II способ – про помощи встроенных функций:
ПРЕДСКАЗ() – линейная экстраполяция для отдельной точки;
ТЕНДЕНЦИЯ() – линейная экстраполяция для массива точек;
РОСТ() – экспоненциальная экстраполяция для массива точек.
Построение линии тренда
Линия тренда – графическое представление направления изменения данных в ряде данных. Линии тренда используются при прогнозировании.
Для построения на диаграмме линии тренда надо:
Щелкнуть правой кнопкой мыши по любому маркеру диаграммы.
В открывшемся контекстном меню выбрать команду Добавить линию тренда;
В открывшемся диалоговом окне на вкладке Параметры линии тренда в группе Построение линии тренда выбрать нужный вариант: экспоненциальная, линейная, логарифмическая, полиномиальная, степенная и т.д.
В группе Прогноз указать, на сколько периодов вперед и (или) назад надо выполнить прогноз.
При необходимости установить флажок Показывать уравнение на диаграмме.
При необходимости изменить форматы линии на вкладках Тип линии, Цвет линии и Тень.
Закрыть диалоговое окно.
Корреляционно-регрессионный анализ
Целью корреляционно-регрессионного анализа является изучение зависимостей между двумя или несколькими показателями.
Корреляция характеризует тесноту связи между случайными величинами. Если коэффициент корреляции равен +1 или -1, то связь считается функциональной, Если коэффициент корреляции равен 0, считается, что связь отсутствует.
Различают парную корреляцию, когда исследуется зависимость показателя от одного параметра, и множественную корреляцию, когда показатель зависит от нескольких параметров.
Для определения коэффициента парной корреляции в Excel предназначена функция КОРРЕЛ(), аргументами которой являются массивы значений случайных величин.
Коэффициент корреляции можно определить с помощью надстройки Пакет анализа:
На вкладке Данные в группе Анализ выбрать команду Анализ данных.
В открывшемся диалоговом окне выбрать строку Корреляция.
В открывшемся диалоговом окне:
указать входной интервал;
выбрать способ группирования данных: по строкам или по столбца;
указать левую верхнюю ячейку выходного интервала.
Щелкнуть по кнопке ОК.
Регрессионный анализ предназначен для выявления аналитической зависимости между показателями, т.е. для нахождения уравнения регрессии.
Для нахождения уравнения регрессии в Excel предназначена функция ЛИНЕЙН(). С помощью этой функции вычисляются коэффициенты уравнения прямой, которая наилучшим образом аппроксимирует имеющиеся данные.
В случае n переменных уравнение регрессии имеет вид
.
Функция
ЛИНЕЙН() возвращает массив коэффициентов
.
Аргументами функции являются массив
значений y и массив
значений переменных
.
Если y есть функция одной переменной, то массивы значений x и y могут иметь любую форму (один столбец, одна строка, несколько столбцов и строк) при условии, что они имеют одинаковую размерность.
Если y есть функция нескольких переменных, то массив значений y должен быть одномерным, т.е. занимать один столбец (или одну строку), а массив значений x должен занимать несколько столбцов (или строк), при этом каждый столбец (или строка) будут интерпретироваться как отдельная переменная.
Кроме того, функция ЛИНЕЙН() имеет логический аргумент Конст, который определяет значение свободного члена b: если Конст=ЛОЖЬ, то полагается b=0.
Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику. Для этого надо присвоить логическому аргументу Статистика значение ИСТИНА.
Поскольку функция ЛИНЕЙН() возвращает массив значений, поэтому перед вводом формулы надо выделить n+1 ячейку, а закончить ввод формулы – нажатием клавиш Ctrl+Shift+Enter.
Коэффициенты уравнения регрессии и регрессионную статистику можно получить с помощью надстройки Пакет анализа:
На вкладке Данные в группе Анализ выбрать команду Анализ данных.
В открывшемся диалоговом окне выбрать строку Регрессия.
В открывшемся диалоговом окне:
указать входной интервал значений y;
указать входной интервал значений x;
выбрать способ вычисления константы b (0: да или нет);
указать левую верхнюю ячейку выходного интервала.
Щелкнуть по кнопке ОК.
Частотный анализ
Распределение частот в Excel можно создать несколькими способами:
с помощью функции ЧАСТОТА();
с использованием надстройки Пакет анализа;
с помощью сводных таблиц.
Функция ЧАСТОТА() возвращает количество значений из диапазона данных, попадающих в каждый интервал группировки.
Аргументами этой функции являются массив данных и массив интервалов группировки.
Массив верхних границ интервалов группировки можно определить по формуле:
=МИН(массив)+{1:2:…:n}*(МАКС(массив)-МИН(массив))/n
Здесь массив – диапазон данных; n – количество интервалов группировки.
Формула массива верхних границ и функция ЧАСТОТА() возвращают массив ячеек, поэтому перед их вводом надо выделить столбец из n ячеек, а закончить ввод– нажатием клавиш Ctrl+Shift+Enter.
Чтобы создать распределение частот с помощью надстройки Пакет анализа, надо:
На вкладке Данные в группе Анализ выбрать команду Анализ данных.
В открывшемся диалоговом окне выбрать строку Гистограмма.
В открывшемся диалоговом окне:
в поле Входной интервал указать диапазон данных;
в поле Интервал карманов указать массив верхних границ интервалов;
в поле Выходной интервал указать левую верхнюю ячейку выходного интервала;
для графического отображения распределения частот (гистограммы) установить флажок Вывод графика.
Щелкнуть по кнопке ОК.
Распределение частот можно получить, создав сводную таблицу с группировкой по полю, содержащему числовые данные. При этом в качестве начального значения задается минимальное значение диапазона, конечного значения – максимальное, шага – интервал группировки, равный (МАКС(массив)-МИН(массив))/n.