Как найти уровень надежности в excel


Дадим определение терминам уровень надежности и уровень значимости. Покажем, как и где они используется в

MS

EXCEL

.


Уровень значимости

(Level of significance) используется в

процедуре проверки гипотез

и при

построении доверительных интервалов

.


СОВЕТ

: Для понимания терминов

Уровень значимости и

Уровень надежности

потребуется знание следующих понятий:

  • выборочное распределение среднего

    ;

  • стандартное отклонение

    ;

  • проверка гипотез

    ;

  • нормальное распределение

    .


Уровень значимости

статистического теста – это вероятность отклонить

нулевую гипотезу

, когда на самом деле она верна. Другими словами, это допустимая для данной задачи вероятность

ошибки первого рода

(type I error).


Уровень значимости

обычно обозначают греческой буквой α (

альфа

). Чаще всего для

уровня значимости

используют значения 0,001; 0,01; 0,05; 0,10.

Например, при построении

доверительного интервала для оценки среднего значения распределения

, его ширину рассчитывают таким образом, чтобы вероятность события «

выборочное среднее (Х

ср

) находится за пределами доверительного интервала

» было равно

уровню значимости

. Реализация этого события считается маловероятным (практически невозможным) и служит основанием для отклонения нулевой гипотезы о

равенстве среднего заданному значению

.


Ошибка первого рода

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

ошибки первого рода

задается перед

проверкой гипотезы

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

Чрезмерное уменьшение

уровня значимости α

(т.е. вероятности

ошибки первого рода

) может привести к увеличению вероятности

ошибки второго рода

, то есть вероятности принять

нулевую гипотезу

, когда на самом деле она не верна. Подробнее об

ошибке второго рода

см. статью

Ошибка второго рода и Кривая оперативной характеристики

.


Уровень значимости

обычно указывается в аргументах

обратных функций MS EXCEL

для вычисления

квантилей

соответствующего распределения:

НОРМ.СТ.ОБР()

,

ХИ2.ОБР()

,

СТЬЮДЕНТ.ОБР()

и др. Примеры использования этих функций приведены в статьях про

проверку гипотез

и про построение

доверительных интервалов

.

Уровень надежности


Уровень

доверия

(этот термин более распространен в отечественной литературе, чем

Уровень надежности

) — означает вероятность того, что

доверительный интервал

содержит истинное значение оцениваемого параметра распределения.


Уровень

доверия

равен

1-α,

где α –

уровень значимости

.

Термин

Уровень надежности

имеет синонимы:

уровень доверия, коэффициент доверия, доверительный уровень

и

доверительная вероятность (англ.

Confidence

Level

,

Confidence

Coefficient

).

В математической статистике обычно используют значения

уровня доверия

90%; 95%; 99%, реже 99,9% и т.д.

Например,

Уровень

доверия

95% означает, что событие, вероятность которого 1-0,95=5% исследователь считать маловероятным или невозможным. Разумеется, выбор

уровня доверия

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


Примечание

: Стоит отметить, что математически не корректно говорить, что

Уровень

доверия

является вероятностью, того что оцениваемый параметр распределения принадлежит

доверительному интервалу

, вычисленному на основе

выборки

. Поскольку, считается, что в математической статистике отсутствуют априорные сведения о параметре распределения. Математически правильно говорить, что

доверительный интервал

, с вероятностью равной

Уровню

доверия,

накроет истинное значение оцениваемого параметра распределения.

Уровень надежности в MS EXCEL

В MS EXCEL

Уровень надежности

упоминается в

надстройке Пакет анализа

. После вызова надстройки, в диалоговом окне необходимо выбрать инструмент

Описательная статистика

.

После нажатия кнопки

ОК

будет выведено другое диалоговое окно.

В этом окне задается

Уровень надежности,

т.е.значениевероятности в процентах. После нажатия кнопки

ОК

в

выходном интервале

выводится значение равное

половине ширины

доверительного интервала

. Этот

доверительный интервал

используется для оценки

среднего значения распределения, когда дисперсия не известна

(подробнее см.

статью про доверительный интервал

).

Необходимо учитывать, что данный

доверительный интервал

рассчитывается при условии, что

выборка

берется из

нормального распределения

. Но, на практике обычно принимается, что при достаточно большой

выборке

(n>30),

доверительный интервал

будет построен приблизительно правильно и для распределения, не являющегося

нормальным

(если при этом это распределение не будет иметь

сильной асимметрии

).


Примечание

: Понять, что в диалоговом окне речь идет именно об оценке

среднего значения распределения

, достаточно сложно. Хотя в английской версии диалогового окна это указано прямо:

Confidence

Level

for

Mean

.

Если

Уровень надежности

задан 95%, то

надстройка Пакет анализа

использует следующую формулу (выводится не сама формула, а лишь ее результат):

=СТАНДОТКЛОН.В(Выборка)/КОРЕНЬ(СЧЁТ(Выборка)) *СТЬЮДЕНТ.ОБР.2Х(1-0,95;СЧЁТ(Выборка)-1)

или эквивалентную ей

=СТАНДОТКЛОН.В(Выборка)/КОРЕНЬ(СЧЁТ(Выборка)) *СТЬЮДЕНТ.ОБР((1+0,95)/2;СЧЁТ(Выборка)-1)

где

=СТАНДОТКЛОН.В(Выборка)/КОРЕНЬ(СЧЁТ(Выборка))

– является

стандартной ошибкой среднего

(формулы приведены в

файле примера

).

или


=ДОВЕРИТ.СТЬЮДЕНТ(1-0,95; СТАНДОТКЛОН.В(Выборка); СЧЁТ(Выборка))

Подробнее см. в

статьях про доверительный интервал

.

Источник

history 23 ноября 2016 г.
    Группы статей

  • Статистический вывод

Дадим определение терминам уровень надежности и уровень значимости. Покажем, как и где они используется в MS EXCEL .

СОВЕТ : Для понимания терминов Уровень значимости и Уровень надежности потребуется знание следующих понятий:

Уровень значимости статистического теста – это вероятность отклонить нулевую гипотезу , когда на самом деле она верна. Другими словами, это допустимая для данной задачи вероятность ошибки первого рода (type I error).

Уровень значимости обычно обозначают греческой буквой α ( альфа ). Чаще всего для уровня значимости используют значения 0,001; 0,01; 0,05; 0,10.

Например, при построении доверительного интервала для оценки среднего значения распределения , его ширину рассчитывают таким образом, чтобы вероятность события « выборочное среднее (Х ср ) находится за пределами доверительного интервала » было равно уровню значимости . Реализация этого события считается маловероятным (практически невозможным) и служит основанием для отклонения нулевой гипотезы о равенстве среднего заданному значению .

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

Чрезмерное уменьшение уровня значимости α (т.е. вероятности ошибки первого рода ) может привести к увеличению вероятности ошибки второго рода , то есть вероятности принять нулевую гипотезу , когда на самом деле она не верна. Подробнее об ошибке второго рода см. статью Ошибка второго рода и Кривая оперативной характеристики .

Уровень значимости обычно указывается в аргументах обратных функций MS EXCEL для вычисления квантилей соответствующего распределения: НОРМ.СТ.ОБР() , ХИ2.ОБР() , СТЬЮДЕНТ.ОБР() и др. Примеры использования этих функций приведены в статьях про проверку гипотез и про построение доверительных интервалов .

Уровень надежности

Уровень доверия (этот термин более распространен в отечественной литературе, чем Уровень надежности ) — означает вероятность того, что доверительный интервал содержит истинное значение оцениваемого параметра распределения.

Уровень доверия равен 1-α, где α – уровень значимости .

Термин Уровень надежности имеет синонимы: уровень доверия, коэффициент доверия, доверительный уровень и доверительная вероятность (англ. Confidence Level , Confidence Coefficient ).

В математической статистике обычно используют значения уровня доверия 90%; 95%; 99%, реже 99,9% и т.д.

Например, Уровень доверия 95% означает, что событие, вероятность которого 1-0,95=5% исследователь считать маловероятным или невозможным. Разумеется, выбор уровня доверия полностью зависит от исследователя. Так, степень доверия авиапассажира к надежности самолета, несомненно, должна быть выше степени доверия покупателя к надежности электрической лампочки.

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

Уровень надежности в MS EXCEL

В MS EXCEL Уровень надежности упоминается в надстройке Пакет анализа . После вызова надстройки, в диалоговом окне необходимо выбрать инструмент Описательная статистика .

После нажатия кнопки ОК будет выведено другое диалоговое окно.

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

Необходимо учитывать, что данный доверительный интервал рассчитывается при условии, что выборка берется из нормального распределения . Но, на практике обычно принимается, что при достаточно большой выборке (n>30), доверительный интервал будет построен приблизительно правильно и для распределения, не являющегося нормальным (если при этом это распределение не будет иметь сильной асимметрии ).

Примечание : Понять, что в диалоговом окне речь идет именно об оценке среднего значения распределения , достаточно сложно. Хотя в английской версии диалогового окна это указано прямо: Confidence Level for Mean .

Если Уровень надежности задан 95%, то надстройка Пакет анализа использует следующую формулу (выводится не сама формула, а лишь ее результат):

или эквивалентную ей

где =СТАНДОТКЛОН.В(Выборка)/КОРЕНЬ(СЧЁТ(Выборка)) – является стандартной ошибкой среднего (формулы приведены в файле примера ).

=ДОВЕРИТ.СТЬЮДЕНТ(1-0,95; СТАНДОТКЛОН.В(Выборка); СЧЁТ(Выборка))

Решение задач описательной статистики средствами пакета анализа Microsoft Excel Текст научной статьи по специальности « Компьютерные и информационные науки»

CC BY

Аннотация научной статьи по компьютерным и информационным наукам, автор научной работы — Трущелёв Сергей Андреевич

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

Похожие темы научных работ по компьютерным и информационным наукам , автор научной работы — Трущелёв Сергей Андреевич

Descriptive statistics using the Data Analysis Toolpak in Microsoft Excel

The paper presents a definition of descriptive statistics , and its main indicators. The necessity of their calculation is set out step by step in the procedure of statistical analysis. The message is a training component with.

Текст научной работы на тему «Решение задач описательной статистики средствами пакета анализа Microsoft Excel»

МЕТОДОЛОГИЯ НАУЧНО-ИССЛЕДОВАТЕЛЬСКОЙ ДЕЯТЕЛЬНОСТИ

Уважаемые читатели, коллеги!

В связи с возрастающими требованиями к качеству публикаций результатов научно-исследовательских работ в «Российском психиатрическом журнале» открыта новая рубрика «Методология научно-исследовательской деятельности». Планируется публикация обучающих и информационно-разъяснительных материалов по разным разделам науковедения, организации научной работы, биоинформатике, биостатистике, биоэтике и т.д. Приглашаем ученых и исследователей поделиться опытом в этой области. Надеемся, что наша инициатива будет поддержана не только в научном сообществе, но и воспринята в среде практикующих специалистов.

© С.А. Трущелёв, 2013 Для корреспонденции

УДК 311:004 Трущелёв Сергей Андреевич — кандидат медицинских наук,

доцент, ведущий научный сотрудник ФГБУ «Московский научно-исследовательский институт психиатрии Минздрава России»

Адрес: 107076, г. Москва, ул. Потешная, д. 3 Телефон: (495) 963-25-31 E-mail: sat-geo@mail.ru

Решение задач описательной статистики средствами пакета анализа Microsoft Excel

Descriptive statistics using the Data Analysis Toolpak in Microsoft Excel

The paper presents a definition of descriptive statistics, and its main indicators. The necessity of their calculation is set out step by step in the procedure of statistical analysis. The message is a training component with. Key words: science of science, biostatistics, descriptive statistics, data analysis toolpak, Excel

ФГБУ «Московский научно-исследовательский институт психиатрии Минздрава России»

Moscow Research Institute of Psychiatry

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

Ключевые слова: науковедение, биостатистика, описательная статистика, пакет анализа, Excel

Каждое явление (предмет исследования) определяется многими факторами. В научном исследовании полностью учесть все факторы и обеспечить их стабильность удается редко. Следовательно, явление, определяемое этими факторами, не поддается точному предсказанию — оно приобретает вероятностные черты, т.е. ведет себя случайным образом. Этому подвержены многие явления, поэтому они определяются случайной величиной, которая принимает в результате опыта или наблюдения одно из множества значений. Случайные величины могут быть дискретными (прерывными) и непрерывными. Немаловажно их распределение — правило, которое устанавливает связь между значениями случайной величины и вероятностями (частотами) их появления.

Наглядное представление о распределении случайных величин дает разброс песчинок, образующих кучу при высыпании (рассеивании) из некоторого точечного источника. Его проекция является параметром положения и соответствует математическому ожиданию распределения, если куча симметрична. Разброс песчинок (параметр рассеяния) характеризуется радиусом кучи на высоте примерно 2/3. Такой параметр рассеяния соответствует так называемому стандартному (среднеквадратичному) отклонению случайных величин в распределении. Горизонтальные расстояния песчинок от проекции источника (математического ожидания) моделируют рассеяние случайной величины. Поверхность кучи (ее высоты) соответствует частоте случайных величин на разных расстояниях от центра. Вершина кучи, расположенная под источником, отвечает максимуму частоты. На периферии высота кучи уменьшается до нуля, что соответствует уменьшению частот больших отклонений от центра рассеяния. Статистическая обработка совокупности данных состоит в некоторых осредняющих вычислительных процедурах, погашающих сугубо индивидуальные особенности — отклонения от общей закономерности и подчеркивающих типичные (популяцион-ные) свойства явления в целом. Начальный раздел математической статистики — описательная статистика — занимается характеристикой (описанием) картины случайного рассеяния по совокупности данных. В соответствии с законом распределения данных решаются вопросы выбора и вычислений надлежащих показателей. Описательная статистика включает методы организации, суммирования и описания данных. Дескриптивные (от англ. descriptive — описательный) показатели позволяют быстро обобщать данные. К описательным методам относят частотные распределения, меры централь-

ной тенденции и меры относительного положения [4, с. 95].

К основным показателям описательной статистики относятся среднее значение (среднее арифметическое, медиана, мода), усредненное значение, разброс (диапазон разброса данных), дисперсия, стандартное среднеквадратное отклонение (СКО), квартили, доверительный интервал [2, с. 28].

Статистическая обработка результатов исследований и получение показателей описательной статистики в недалеком прошлом обычно занимали много времени, однако с внедрением средств компьютерной техники многое изменилось — вычислительные процессы стали происходить очень быстро. Для проведения статистических расчетов в электронной таблице Microsoft Excel имеется пакет анализа. Надстройка «Анализ данных» располагается во вкладке «Данные», в крайне правом блоке ленты (рис. 1).

Для демонстрации вычислений будем использовать гипотетический набор данных. Далее приведем пошаговую инструкцию по созданию описательной статистики признака (показателя систолического давления), измеренного до лечения и после него, в группе наблюдения (n=60).

Для проведения вычисления обратитесь к ленте: Данные ^ Анализ данных ^ Описательная статистика ^ ОК. Затем, перейдя в окно инструмента, выберите входной интервал, группирование (по столбцам), поставьте галочку, если в первой строке выделены метки; в параметрах вывода на поле электронной страницы выберите ячейку вывода результатов, установите галочку рядом с итоговой статистикой. Потом нажмите кнопку ОК. После этого вы получите результаты описательной статистики выбранных признаков (рис. 2 и 3).

[й1 A «ï- V m И^ЭгшИ Главная Ш I» 1 Описательная статистика — Microsoft Excel □ 0 й Вставка Разметка страницы Формулы Данные Рецензирование Вид Разработчик Надстройки MetaXL Л □ S3

П внец m 1олучение jних данныхт ч [^Подключения ^Свойства Обновить все т && Изменить связи Подключения A I AIЯ I Я + Я 1А1 Я| Сортировка Со pi ч Ш ^ Очистить ^ Повторить Фильтр ™ № Дополнительно ировка и фильтр S Ii ы» вш а в Текст по Удалить ,—, столбцам дубликаты » Работа сданными Ф Фор» орма Jbi ssprfa ф ^ ^Анализданных Поиск решения Стр^И^ра Анализ

А в с D Е F G У 1 J К 1 L _

1 Номер_исс Признак_1 Признак_2 у

3 2 178 143 Анализ данным lia

Инструменты анализа У _ 1 о, 1

4 3 320 188 Двухфакторный дисперсионный^нализ без повторений Корреляция Л* 3 J d Отмена |

6 5 159 161 Экспоненциальное сглаживание Двухвыборочный Р-тест для дисперсии Анализ Фурье Гистограмма Скользящее среднее 1 Генерация случайных чисел_| Справка

Рис. 1. Пошаговый выбор инструмента анализа данных

Рис. 2. Окно инструмента описательной статистики

Среднее (арифметическое; М; х ) — одна из наиболее распространенных мер центральной тенденции, представляющая собой сумму всех значений, деленную на их количество. Если значения интересующего нас признака у большинства объектов близки к их среднему и с равной вероятностью отклоняются от него в большую или меньшую сторону, лучшими характеристиками совокупности будут само среднее значение и стандартное отклонение. Напротив, когда значения признака распределены несимметрично относительно среднего, совокупность лучше описать с помощью медианы и процен-тилей [1, с. 27].

Стандартная ошибка (т) — показатель надежности расчетного параметра; стандартное отклонение оценок, которые будут получены при многократной случайной выборке данного размера из одной и той же совокупности. Стандартная ошибка — это убывающая функция объема выборки: чем меньше стандартная ошибка, тем более достоверной является оценка параметра. Весьма часто для описания непрерывных количественных данных используют стандартную ошибку, которая (в отличие от СКО) является не характеристикой, описывающей распределение наблюдений исследуемой выборки по области значений, а только мерой точности оценки популяционного среднего и, следовательно, не характеризует дисперсию (разброс) в анализируемой выборке. Однако часто именно стандартную ошибку среднего приводят в качестве параметра описательной статистики, пытаясь продемонстрировать тем самым малую вариабельность своих данных, так как всегда (по определению) т Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

60 Среднее 161,77 Среднее 134,03

61 Стандартная ошибка 12,46 Стандартная ошибка 6.59

62 Медиана 167 Медиана 121,5

63 Мода 72 Мода 141

64 Стандартное отклонение 96.54 Стандартное отклонение 51,03

65 Дисперсия выборки 9320.59 Дисперсия выборки 2604.34

66 Эксцесс 0.89 Эксцесс 2.75

67 Асимметричность 0.96 Асимметричность 1,43

68 Интервал 420 Интервал 254

69 Минимум 50 Минимум 55

70 Максимум 470 Максимум 309

71 Сумма 9706 Сумма 8042

72 Счет 60 Счет 60

73 74 Уровень надежности(95.0%) 24.94 Уровень надежности(95.0%) 13,18

Коэффициент вариации 60% Коэффициент вариации 38%

Рис. 3. Результаты описательной статистики двух признаков

Медиану и интерквартильный размах рекомендуется применять для описания распределения, не являющегося нормальным (а это большинство распределений медико-биологических параметров) [1, с. 34]. Интерквартильный размах указывают в виде процентилей. Рекомендуется указывать уровни 25 и 75%, которые соответствуют верхней границе 1-го и нижней границе 4-го квартилей. Пример описания: Me (25%; 75%) = 60 (23; 78).

Мода (Мо) — значение, которое встречается наиболее часто во множестве. Иногда в совокупности встречается более одной моды. Тогда говорят, что совокупность мультимодальна — свидетельство того, что набор данных не подчиняется нормальному распределению. Мода как средняя величина употребляется чаще для данных, имеющих нечисловую природу. Например, в группе пациентов наибольшая частота тяжести болезни будет равна моде. При экспертной оценке с помощью этого показателя определяют предпочтения участников исследования. Недостаток — показатель не учитывает поведение распределения в других точках.

Стандартное отклонение (синонимы: среднеквадратичное отклонение, квадратичное отклонение; стандартный разброс; СКО; в; о) — в теории вероятностей и статистике наиболее распространенный показатель рассеивания значений случайной величины относительно ее математического ожидания. Измеряется в единицах случайной величины. Равно корню квадратному из дисперсии случайной величины. Стандартное отклонение используют при расчете стандартной ошибки среднего арифметического, построении доверительных интервалов, статистической проверке гипотез, измерении линейной взаимосвязи между случайными величинами. Большое значение СО показывает большой разброс значений в представленном множестве со средней величиной множества; маленькое значение, соответственно, показывает, что значения во множестве сгруппированы вокруг среднего. Если среднее значение измерений сильно отличается от предсказанных теорией значений (большое значение среднеквадратичного отклонения), то полученные значения или метод их получения следует перепроверить.

Дисперсия (D; о2) — мера разброса случайной величины, т.е. ее отклонения от математического ожидания. Квадратный корень из дисперсии называется стандартным отклонением. Дисперсия измеряется в квадратах единицы измерения. Однако в самостоятельном виде (как, например, средняя арифметическая) дисперсия используется редко. Это скорее вспомогательный и промежуточный показатель, который применяют в других методах статистического анализа.

Эксцесс — скалярная характеристика островершинности графика плотности вероятности унимо-

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

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

Интервал — размах показателей, т.е. разность между максимумом и минимумом значений вариант.

Максимум — наибольшее значение вариант.

Минимум — наименьшее значение вариант.

Сумма — сумма значений вариант.

Счет — количество вариант.

Уровень надежности — свойство объекта сохранять в установленных пределах значения всех параметров. Показывает величину доверительного интервала для математического ожидания согласно заданному уровню надежности или доверия. По умолчанию уровень надежности принят равным 95%.

Коэффициент вариации случайной величины -мера относительного разброса случайной величины. Показывает, какую долю среднего значения этой величины составляет ее средний разброс. Исчисляется в процентах. Вычисляется только для количественных данных. В отличие от стандартного отклонения, он измеряет не абсолютную, а относительную меру разброса значений признака в статистической совокупности. В Excel нет готовой функции для расчета коэффициента вариации. Расчет можно провести простым делением стандартного отклонения на среднее значение. Эти значения имеются в таблице описательной статистики. Для вычисления этого важного показателя в ячейке ниже надписи Уровень надежности пишем Коэффициент вариации, затем в ячейке справа делаем запись: =G64/G60. То же необходимо по-

вторить для вычисления коэффициента вариации для другого измерения.

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на панели инструментов в закладке «Главная». Коэффициент вариации, в отличие от других показателей разброса значений, используется как самостоятельный и весьма информативный индикатор вариации данных. В статистике принято считать, что совокупность данных является однородной, если коэффициент вариации менее 33%, неоднородной — если более 33%. Эта информация может быть полезна для предварительного описания данных и определения возможностей проведения дальнейшего анализа. Кроме того, коэффициент вариации, измеряемый в процентах, позволяет сравнивать степень разброса различных данных независимо от их масштаба и единиц измерений.

Анализ показателей описательной статистики

При сравнении значений среднего, медианы, моды в каждом измерении следует отметить, что эти показатели сильно отличаются друг от друга.

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

Для представления результатов сравнения обычно используют формат в виде М (95% ДИ) — значение среднего и указание 95% доверительного интервала. В тексте публикации запись может выглядеть следующим образом: Средний уровень систолического давления в группе пациентов до лечения составил 161,77 мм рт. ст. (95% ДИ от 136,83 до 186,71 мм рт. ст.), после лечения -134,03 мм рт. ст. (95% ДИ от 120,85 до 147,21 мм рт. ст.). Указанные доверительные интервалы имеют зону совмещения, следовательно, существенного различия в изменении признака нет. Исходя из этого с большой долей вероятности можно утверждать, что для данной группы пациентов лекарственный препарат, примененный для снижения уровня систолического артериального давления, был не эффективен.

1. Гланц С. Медико-биологическая статистика / Пер. с англ. -М., Практика, 1998. — 459 с.

2. Ланг Т.А., Сесик М. Как описывать статистику в медицине. Аннотированное руководство для авторов, редакторов и рецензентов / Пер. с англ. под ред. В.П. Леонова. -М.: Практическая медицина, 2011. — 480 с.

3. Леонов В.П. Ошибки статистического анализа биомедицинских данных // Междунар. журн. мед. практики. — 2007. -№ 2. — С. 19-35.

4. Трущелев С.А. Медицинская диссертация: руководство: 3-е изд. / Под ред. проф. И.Н. Денисова. — М.: ГЭОТАР-Медиа, 2009. — 416 с.

Источник

Содержание

  • 1 Использование описательной статистики
    • 1.1 Подключение «Пакета анализа»
    • 1.2 Применение инструмента «Описательная статистика»
    • 1.3 Помогла ли вам эта статья?
    • 1.4 Статистические процедуры Пакета анализа
    • 1.5 Статистические функции библиотеки встроенных функций Excel

описательная статистика в excel 2010 как сделать

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

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

Использование описательной статистики

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

В Экселе существует отдельный инструмент, входящий в «Пакет анализа», с помощью которого можно провести данный вид обработки данных. Он так и называется «Описательная статистика». Среди критериев, которые высчитывает данный инструмент следующие показатели:

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

  1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».
  2. В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».
  3. Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».

описательная статистика в excel 2010 как сделать

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

Применение инструмента «Описательная статистика»

Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

  1. Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».
  2. Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».
  3. После выполнения данных действий непосредственно запускается окно «Описательная статистика».

    В поле «Входной интервал» указываем адрес диапазона, который будет подвергаться обработке этим инструментом. Причем указываем его вместе с шапкой таблицы. Для того, чтобы внести нужные нам координаты, устанавливаем курсор в указанное поле. Затем, зажав левую кнопку мыши, выделяем на листе соответствующую табличную область. Как видим, её координаты тут же отобразятся в поле. Так как мы захватили данные вместе с шапкой, то около параметра «Метки в первой строке» следует установить флажок. Тут же выбираем тип группирования, переставив переключатель в позицию «По столбцам» или «По строкам». В нашем случае подходит вариант «По столбцам», но в других случаях, возможно, придется выставить переключатель иначе.

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

    • Выходной интервал;
    • Новый рабочий лист;
    • Новая рабочая книга.

    В первом случае нужно указать конкретный диапазон на текущем листе или его верхнюю левую ячейку, куда будет выводиться обработанная информация. Во втором случае следует указать название конкретного листа данной книги, где будет отображаться результат обработки. Если листа с таким наименованием в данный момент нет, то он будет создан автоматически после того, как вы нажмете на кнопку «OK». В третьем случае никаких дополнительных параметров указывать не нужно, так как данные будут выводиться в отдельном файле Excel (книге). Мы выбираем вывод результатов на новом рабочем листе под названием «Итоги».

    Далее, если вы хотите чтобы выводилась также итоговая статистика, то нужно установить флажок около соответствующего пункта. Также можно установить уровень надежности, поставив галочку около соответствующего значения. По умолчанию он будет равен 95%, но его можно изменить, внеся другие числа в поле справа.

    Кроме этого, можно установить галочки в пунктах «K-ый наименьший» и «K-ый наибольший», установив значения в соответствующих полях. Но в нашем случае этот параметр так же, как и предыдущий, не является обязательным, поэтому флажки мы не ставим.

    После того, как все указанные данные внесены, жмем на кнопку «OK».

  4. После выполнения этих действий таблица с описательной статистикой выводится на отдельном листе, который был нами назван «Итоги». Как видим, данные представлены сумбурно, поэтому их следует отредактировать, расширив соответствующие колонки для более удобного просмотра.
  5. После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
    • Асимметричность;
    • Интервал;
    • Минимум;
    • Стандартное отклонение;
    • Дисперсия выборки;
    • Максимум;
    • Сумма;
    • Эксцесс;
    • Среднее;
    • Стандартная ошибка;
    • Медиана;
    • Мода;
    • Счет.

описательная статистика в excel 2010 как сделать

Если какие-то из вышеуказанных данных для конкретного вида анализа не нужны, то их можно удалить, чтобы они не мешали. Далее производится анализ с учетом статистических закономерностей.

Урок: Статистические функции в Excel

Как видим, с помощью инструмента «Описательная статистика» можно сразу получить результат по целому ряду критериев, которые в ином случае рассчитывались с применением отдельно предназначенной для каждого расчета функцией, что заняло бы значительное время у пользователя. А так, все эти расчеты можно получить практически в один клик, использовав соответствующий инструмент — Пакета анализа.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

Описательная статистика в Эксель. Описательная статистика в MS Excel позволяет представить статистическую информацию как совокупность данных, для характеристики которых могут быть использованы различные показатели.

Использование инструмента «Описательная статистика» рассмотрим на примере MS Excel 2010, а в качестве данных для анализа возьмем статистическую информацию по изменению курса доллара за месяц:

описательная статистика в excel 2010 как сделать

Для начала на вкладке «Данные» в группе «Анализ» выбрать пункт «Анализ данных»:

описательная статистика в excel 2010 как сделать

В открывшемся окне «Анализ данных» выбрать инструмент для анализа «Описательная статистика»:

описательная статистика в excel 2010 как сделать

В новом окне «Описательная статистика»,

описательная статистика в excel 2010 как сделать

следует выбрать исходные данные для анализа:

• Входной интервал – это диапазон ячеек с исходными данными для анализа. В случае, если в исходный диапазон входит текстовый заголовок, тогда следует поставить галочку в поле «Метки в первой строке»

• Выходной интервал – это адрес верхней левой ячейки диапазона, в котором будут представлены результаты статистического анализа.

• Итоговая статистика – позволяет вывести дополнительные расширенные результаты анализа исходных данных.

• Уровень надежности – показывает вероятность того, что исследуемый исходный интервал содержит истинное значение оцениваемого параметра. В математической статистике обычно используют значения: 90%, 95%, 99%. В нашем случае, по умолчанию установлено значение 95%.

• К-ый наименьший – показывает наименьшее значение из исследуемого исходного интервала.

• К-ый наибольший – показывает наибольшее значение из исследуемого исходного интервала.

В результате использования инструмента «Описательная статистика», на основании наших исходных данных, получим:

описательная статистика в excel 2010 как сделать

Таким образом, для проведения сложного статистического или инженерного анализа, чтобы упростить процесс и сэкономить время, следует использовать инструмент «Описательная статистика» MS Exсel.

Основными средствами анализа статистических данных в Excel являются статистические процедуры надстройки Пакет анализа (Analysis ToolРак) и статистические функции библиотеки встроенных функций. Основные сведения обо всех этих средствах имеются в электронной справочной системе Excel.

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

Статистические процедуры Пакета анализа

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

В Пакет анализа входят следующие статистические процедуры:

  1. генерация случайных чисел (Random number generation);
  2. выборка (Sampling);
  3. гистограмма (Histogram);
  4. описательная статистика (Descriptive statistics);
  5. ранги персентиль (Rank and percentile);
  6. двухвыборочный z-тест для средних (z-Test: Two Sample for Means);
  7. двухвыборочный t-тест для средних с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances);
  8. двухвыборочный t-тест для средних с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances);
  9. парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means);
  10. двухвыборочный F-тест да я дисперсий (F-Test: Two Sample for Variances);
  11. коварнация (Covariance);
  12. корреляция (Correlation);
  13. рецессия (Regression);
  14. однофакторный дисперсионный анализ (ANOVA: Single Factor);
  15. двухфакторный дисперсионный анализ без повторений (ANOVA: Two Factor Without Replication);
  16. двухфакторный дисперсионный анализ с повторениями (ANOVA: Two Factor With Replication);
  17. скользящее среднее (Moving Average);
  18. экспоненциальное сглаживание (Exponential Smoothing);
  19. анализ Фурье (Fourier Analysis).

Для доступа к процедурам Пакета анализа необходимо в меню Сервис (Tools) щелкнуть указателем мыши на строке Анализ данных (Data Analysis). Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа данных (рис. 1).

описательная статистика в excel 2010 как сделать

Рис.1. Диалоговое окно Анализ данных

Для того чтобы запустить в работу нужную статистическую процедуру, нужно выделить ее указателем мыши и щелкнуть на кнопке ОК. На экране появится диалоговое окно вызванной процедуры. На рис. 2 для примера показано диалоговое окно процедуры Описательная статистика (Descriptive statistics).

описательная статистика в excel 2010 как сделать

Рис.2. Диалоговое окно процедуры Описательная статистика

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

К числу общих для большинства процедур элементов управления относятся:

  • поле ввода Входной интервал (Input Range). В это поле вводится ссылка на диапазон, содержащий статистические данные, подлежащие обработке. Входной диапазон может быть столбцом пли группой столбцов (строкой или группой строк);
  • переключатель Группирование (Grouped By). В том случае, когда входной диапазон представляет собой столбец или группу столбцов, переключатель устанавливается в положение по столбцам (Columns). Если же входной диапазон представляет собой строку или группу строк, то переключатель устанавливается в положение по строкам (Rows). Более точным названием этого переключателя было бы название Расположение;
  • флажок Метки (Labels in First Row). Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создает и выводит на экран стандартные названия для данных выходного диапазона (Столбец1, Столбец2,… или Строка 1. Строка2,…);
  • переключатели Выходной интервал/Новый рабочий лист/Новая книга (Output Range/New Worksheet/New Workbook). Эти переключатели определяют место вывода таблицы, содержащей результаты реализации статистической процедуры. В группе может быть выбран только одни переключатель.

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

В положении Новый рабочий лист открывается новый лист рабочей книги. На этот лист, начиная с ячейки А1, и выводится таблица результатов решения. Справа от переключателя имеется поле ввода, в которое в случае необходимости можно ввести имя нового рабочего листа. При выборе переключателя Новая рабочая книга открывается новая рабочая книга. На первый лист этой новой книги, начиная с ячейки А1, выводится таблица результатов решения.

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

Эффективным и очень удобным в использовании средством парного регрессионного анализа и анализа временных рядов является процедура Добавить линию тренда (Add Trendline), входящая в комплекс графических средств Excel.

Статистические функции библиотеки встроенных функций Excel

Табличный процессор Excel имеет библиотеку встроенных функции рабочего листа (Worksheet function). Одним из разделов этой библиотеки является раздел Статистические функции. В этот раздел входят 83 функции, предназначенные для решения некоторых наиболее востребованных задач теории вероятностей и математической статистики.

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

Когда в качестве какого-либо аргумента встроенной статистической функции введен текст, функция выдает сообщение об ошибке #ЗНАЧ! (#VALUE!). Если в качестве аргумента, который по определению должен быть целым числом, введено число не целое, Excel использует в качестве аргумента целую часть этот числа. Никакие сообщения об этом «несанкционированном округлении» на экран не выводятся.

Источник

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

 После
установки
надстройки
Пакет
анализа в меню команды Сервис
появляется
новый пункт – Анализ
данных.

Для
анализа наиболее часто используется
описательная статистика данных,
статистические методы прогнозирования
значений.

Описательная статистика

Это самый
распространенный прием анализа данных,
с помощью которого вычисляются
статистические характеристики массива
значений экономических показателей:

  • Средние
    оценки, имеют ту же размерность, что и
    сама случайная величина, в том числе:

  • Средняя
    арифметическая – математическое
    ожидание случайной величины, соответствует
    встроенной функции СРЗНАЧ.

  • Средняя
    геометрическая – оценка средних темпов
    роста, поиск значения, равноудаленного
    от других значений, соответствует
    встроенной функции СРГЕОМ.

  • Средняя
    гармоническая – оценка средней суммы
    обратных величин, соответствует
    встроенной функции СРГАРМ.

Между средними
величинами существует соотношение:

  • Показатели
    вариации:

  • Общее
    число значений в массиве, соответствует
    встроенной функции СЧЕТ.

  • Сумма
    всех значений переменных в массиве,
    соответствует встроенной функции СУММ.

  • Дисперсия
    случайной величины, соответствует
    встроенной функции ДИСП
    (дисперсия по выборке) или ДИСПР
    (дисперсия
    по генеральной совокупности). Дисперсия
    имеет размерность в квадрате, характеризует
    рассеивание значений случайной величины
    относительно средней арифметической.

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

  • Средний
    модуль отклонений, который нивелирует
    знак отклонения от среднего и является
    показателем силы вариации, соответствует
    встроенной функции СРОТКЛ.

  • Уровень
    надежности (доверительный интервал)
    для среднего значения, соответствует
    встроенной функции ДОВЕРИТ.

  • Средняя
    квадратическая ошибка, вычисляется
    как отношение СТАНДОТКЛОН
    к корню квадратному из числа элементов
    выборки.

  • Минимальное
    значение случайной величины, соответствует
    встроенной функции МИН.

  • Максимальное
    значение случайной величины, соответствует
    встроенной функции МАКС.

  • Интервал
    – размах вариации, равный разности
    максимального и минимального значения
    переменной (МАКС–МИН).

  • Порядковое
    наибольшее значений, соответствует
    встроенной функции НАИБОЛЬШИЙ.

  • Порядковое
    наименьшее значение соответствует
    встроенной функции – функция НАИМЕНЬШИЙ.

  • Мера
    взаимного расположения данных в массиве
    значений, соответствует встроенным
    функциям: МОДА, КВАРТИЛЬ, МЕДИАНА,
    ПЕРСЕНТИЛЬ, ПРОЦЕНТРАНГ.

 Мода
– наиболее
вероятное значение случайной величины.
При симметричном распределении
относительно среднего мода совпадает
с математическим ожиданием. Мода может
отсутствовать, либо распределение может
быть многомодальным.

Квантили распределения
— величина
значения признака, делящая совокупность
на n равных
частей. Различают номера квантилей: 0 –
соответствует минимальному значению
величины; 1 — первая четверть (квартиль)
значений или (25-я персентиль; 2 – медиана
или 50-я персентиль; 3 — третья четверть
(квартиль) или 75-я персентиль; 4 –
максимальное значение величины.

  • Форма
    распределения случайной величины,
    соответствует встроенным функциям
    СКОС,
    ЭКСЦЕСС.

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

Пакет
анализа запускается командой меню
СервисАнализ
данных. В
диалоговом
окне Инструменты
анализа выбирается
Описательная
статистика. Исходные
данные для анализа располагаются в
ячейках строк или столбцов таблицы.

Описательная
статистика Пакета
анализа
вычисляет показатели:

  1. Среднее

  1. Стандартная
    ошибка

  1. Медиана

  1. Мода

  1. Стандартное
    отклонение

  1. Дисперсия
    выборки

  1. Эксцесс

  1. Асимметричность

  1. Интервал

  1. Минимум

  1. Максимум

  1. Сумма

  1. Счет

  1. K–й
    наибольший

  1. K–й
    наименьший

  1. Уровень
    надежности

Результаты
описательной статистики выводятся в
указанное место (текущий лист, другой
лист, новая книга).

Последовательность
действий (исходные данные для анализа
представлены на рабочем листе):

  1. Команда
    меню СервисАнализ
    данных, выбрать
    метод Описательная
    статистика.

  2. Указать
    параметры описательной статистики
    (рис. 16):

  • Входной
    интервал – блок ячеек, содержащий
    анализируемые значения. Можно одновременно
    выделить смежные столбцы исходных
    данных.

Рисунок
16

  • Группирование
    – по столбцам/строкам.

  • Флажок
    Метки в первой строке выбран/не выбран.

  • Выходной
    интервал – Новый рабочий лист/Новая
    книга/Определенная ячейка текущего
    листа.

  • Флажок
    Итоговая статистика – выбран/не выбран
    (выбор флажка обеспечивает вывод всей
    описательной статистики).

  • Уровень
    надежности – 95% (стандартно, но можно
    любой).

  • K-й
    наименьший – 2 (произвольно).

  • K-й
    наибольший – 2 (произвольно).

  • Нажать
    кнопку ОК.

Результаты
описательной статистики – табл. 14.

Таблица
14

Показатель

Массив
1

Массив
2

Среднее

13591,92

9564,75

Стандартная
ошибка

116,0136

127,61271

Медиана

13821

9547,5

Мода

#Н/Д

9540

Стандартное
отклонение

696,0820303

765,6762604

Дисперсия
выборки

484530,1929

586260,1357

Эксцесс

-0,341929136

-0,809054

Асимметричность

-0,823444644

0,016596

Интервал

2441

2587

Минимум

12054

8375

Максимум

14495

10962

Сумма

489309

344331

Счет

36

36

Наибольший(2)

14480

10932

Наименьший(2)

12063

8377

Уровень
надежности
(95,0%)

235,52

259,07
Источник

В этой статье описаны синтаксис формулы и использование функции LINEST в Microsoft Excel. Ссылки на дополнительные сведения о диаграммах и выполнении регрессионного анализа можно найти в разделе См. также.

Описание

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

Уравнение для прямой линии имеет следующий вид:

y = mx + b

или

y = m1x1 + m2x2 +… + b

если существует несколько диапазонов значений x, где зависимые значения y — функции независимых значений x. Значения m — коэффициенты, соответствующие каждому значению x, а b — постоянная. Обратите внимание, что y, x и m могут быть векторами. Функция ЛИНЕЙН возвращает массив {mn;mn-1;…;m1;b}. Функция ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис

ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])

Аргументы функции ЛИНЕЙН описаны ниже.

Синтаксис

  • Известные_значения_y.    Обязательный аргумент. Множество значений y, которые уже известны для соотношения y = mx + b.

    • Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.

    • Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

  • Известные_значения_x.    Необязательный аргумент. Множество значений x, которые уже известны для соотношения y = mx + b.

    • Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то массивы известные_значения_y и известные_значения_x могут иметь любую форму — при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (т. е. интервалом высотой в одну строку или шириной в один столбец).

    • Если массив известные_значения_x опущен, то предполагается, что это массив {1;2;3;…}, имеющий такой же размер, что и массив известные_значения_y.

  • Конст.    Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

    • Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом.

    • Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.

  • Статистика.    Необязательный аргумент. Логическое значение, которое указывает, требуется ли вернуть дополнительную регрессионную статистику.

    • Если статистика имеет true, то LINEST возвращает дополнительную регрессию; в результате возвращается массив {mn;mn-1,…,m1;b;sen,sen-1,…,se1;seb;r2;sey; F,df;ssreg,ssresid}.

    • Если аргумент статистика имеет значение ЛОЖЬ или опущен, функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

      Дополнительная регрессионная статистика.

Величина

Описание

se1,se2,…,sen

Стандартные значения ошибок для коэффициентов m1,m2,…,mn.

seb

Стандартное значение ошибки для постоянной b (seb = #Н/Д, если аргумент конст имеет значение ЛОЖЬ).

r2

Коэффициент определения. Сравнивает предполагаемые и фактические значения y и диапазоны значений от 0 до 1. Если значение 1, то в выборке будет отличная корреляция— разница между предполагаемым значением y и фактическим значением y не существует. С другой стороны, если коэффициент определения — 0, уравнение регрессии не помогает предсказать значение y. Сведения о том, каквычисляется 2, см. в разделе «Замечания» далее в этой теме.

sey

Стандартная ошибка для оценки y.

F

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

df

Степени свободы. Степени свободы используются для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели необходимо сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. Дополнительные сведения о вычислении величины df см. ниже в разделе «Замечания». Далее в примере 4 показано использование величин F и df.

ssreg

Регрессионная сумма квадратов.

ssresid

Остаточная сумма квадратов. Дополнительные сведения о расчете величин ssreg и ssresid см. в подразделе «Замечания» в конце данного раздела.

На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.

Лист

Замечания

  • Любую прямую можно описать ее наклоном и пересечением с осью y:

    Наклон (m):
    Чтобы найти наклон линии, обычно записанной как m, возьмите две точки на строке (x1;y1) и (x2;y2); наклон равен (y2 — y1)/(x2 — x1).

    Y-перехват (b):
    Y-пересечение строки, обычно записанное как b, — это значение y в точке, в которой линия пересекает ось y.

    Уравнение прямой имеет вид y = mx + b. Если известны значения m и b, то можно вычислить любую точку на прямой, подставляя значения y или x в уравнение. Можно также воспользоваться функцией ТЕНДЕНЦИЯ.

  • Если имеется только одна независимая переменная x, можно получить наклон и y-пересечение непосредственно, воспользовавшись следующими формулами:

    Наклон:
    =ИНДЕКС( LINEST(known_y,known_x’s);1)

    Y-перехват:
    =ИНДЕКС( LINEST(known_y,known_x),2)

  • Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель ЛИНЕЙН. Функция ЛИНЕЙН использует для определения наилучшей аппроксимации данных метод наименьших квадратов. Когда имеется только одна независимая переменная x, значения m и b вычисляются по следующим формулам:

    Уравнение

    Уравнение

    где x и y — выборочные средние значения, например x = СРЗНАЧ(известные_значения_x), а y = СРЗНАЧ(известные_значения_y).

  • Функции ЛИННЕСТРОЙ и ЛОГЪЕСТ могут вычислять наилучшие прямые или экспоненциальное кривой, которые подходят для ваших данных. Однако необходимо решить, какой из двух результатов лучше всего подходит для ваших данных. Вы можетевычислить known_y(known_x) для прямой линии или РОСТ(known_y, known_x в) для экспоненциальной кривой. Эти функции без аргумента new_x возвращают массив значений y, спрогнозируемых вдоль этой линии или кривой в фактических точках данных. Затем можно сравнить спрогнозируемые значения с фактическими значениями. Для наглядного сравнения можно отобразить оба этих диаграммы.

  • Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов (ssresid). Затем Microsoft Excel подсчитывает общую сумму квадратов (sstotal). Если конст = ИСТИНА или значение этого аргумента не указано, общая сумма квадратов будет равна сумме квадратов разностей действительных значений y и средних значений y. При конст = ЛОЖЬ общая сумма квадратов будет равна сумме квадратов действительных значений y (без вычитания среднего значения y из частного значения y). После этого регрессионную сумму квадратов можно вычислить следующим образом: ssreg = sstotal — ssresid. Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента определения r2— индикатор того, насколько хорошо уравнение, выданное в результате регрессионного анализа, объясняет связь между переменными. Значение r2 равно ssreg/sstotal.

  • В некоторых случаях один или несколько столбцов X (предполагается, что значения Y и X — в столбцах) могут не иметь дополнительного прогнозируемого значения при наличии других столбцов X. Другими словами, удаление одного или более столбцов X может привести к одинаковой точности предсказания значений Y. В этом случае эти избыточные столбцы X следует не использовать в модели регрессии. Этот вариант называется «коллинеарность», так как любой избыточный X-столбец может быть выражен как сумма многих не избыточных X-столбцов. Функция ЛИНЕЙН проверяет коллинеарность и удаляет все избыточные X-столбцы из модели регрессии при их идентификации. Удалены столбцы X распознаются в результатах LINEST как имеющие коэффициенты 0 в дополнение к значениям 0 se. Если один или несколько столбцов будут удалены как избыточные, это влияет на df, поскольку df зависит от числа X столбцов, фактически используемых для прогнозирования. Подробные сведения о вычислении df см. в примере 4. Если значение df изменилось из-за удаления избыточных X-столбцов, это также влияет на значения Sey и F. Коллинеарность должна быть относительно редкой на практике. Однако чаще всего возникают ситуации, когда некоторые столбцы X содержат только значения 0 и 1 в качестве индикаторов того, является ли тема в эксперименте участником определенной группы или не является ее участником. Если конст = ИСТИНА или опущен, функция LYST фактически вставляет дополнительный столбец X из всех 1 значений для моделирования перехвата. Если у вас есть столбец с значением 1 для каждой темы, если мальчик, или 0, а также столбец с 1 для каждой темы, если она является женщиной, или 0, последний столбец является избыточным, так как записи в нем могут быть получены из вычитания записи в столбце «самец» из записи в дополнительном столбце всех 1 значений, добавленных функцией LINEST.

  • Вычисление значения df для случаев, когда столбцы X удаляются из модели вследствие коллинеарности происходит следующим образом: если существует k столбцов известных_значений_x и значение конст = ИСТИНА или не указано, то df = n – k – 1. Если конст = ЛОЖЬ, то df = n — k. В обоих случаях удаление столбцов X вследствие коллинеарности увеличивает значение df на 1.

  • При вводе константы массива (например, в качестве аргумента известные_значения_x) следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Знаки-разделители могут быть другими в зависимости от региональных параметров.

  • Следует отметить, что значения y, предсказанные с помощью уравнения регрессии, возможно, не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.

  • Основной алгоритм, используемый в функции ЛИНЕЙН, отличается от основного алгоритма функций НАКЛОН и ОТРЕЗОК. Разница между алгоритмами может привести к различным результатам при неопределенных и коллинеарных данных. Например, если точки данных аргумента известные_значения_y равны 0, а точки данных аргумента известные_значения_x равны 1, то:

    • Функция ЛИНЕЙН возвращает значение, равное 0. Алгоритм функции ЛИНЕЙН используется для возвращения подходящих значений для коллинеарных данных, и в данном случае может быть найден по меньшей мере один ответ.

    • Наклон и ОТОКП возвращают #DIV/0! ошибка «#ЗНАЧ!». Алгоритм функций НАКЛОН и ОТОКП предназначен для поиска только одного ответа, и в этом случае может быть несколько ответов.

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

    =ЛИНЕЙН(значения_y, значения_x^СТОЛБЕЦ($A:$C))

    работает при наличии одного столбца значений Y и одного столбца значений Х для вычисления аппроксимации куба (многочлен 3-й степени) следующей формы:

    y = m1*x + m2*x^2 + m3*x^3 + b

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

  • Значение F-теста, возвращаемое функцией ЛИНЕЙН, отличается от значения, возвращаемого функцией ФТЕСТ. Функция ЛИНЕЙН возвращает F-статистику, в то время как ФТЕСТ возвращает вероятность.

Примеры

Пример 1. Наклон и Y-пересечение

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Известные значения y

Известные значения x

1

0

9

4

5

2

7

3

Результат (наклон)

Результат (y-пересечение)

2

1

Формула (формула массива в ячейках A7:B7)

=ЛИНЕЙН(A2:A5;B2:B5;;ЛОЖЬ)

Пример 2. Простая линейная регрессия

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Месяц

Продажи

1

3 100 ₽

2

4 500 ₽

3

4 400 ₽

4

5 400 ₽

5

7 500 ₽

6

8 100 ₽

Формула

Результат

=СУММ(ЛИНЕЙН(B1:B6; A2:A7)*{9;1})

11 000 ₽

Вычисляет предполагаемый объем продаж в девятом месяце на основе данных о продажах за период с первого по шестой месяцы.

Пример 3. Множественная линейная регрессия

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Общая площадь (x1)

Количество офисов (x2)

Количество входов (x3)

Время эксплуатации (x4)

Оценочная цена (y)

2310

2

2

20

142 000 ₽

2333

2

2

12

144 000 ₽

2356

3

1,5

33

151 000 ₽

2379

3

2

43

150 000 ₽

2402

2

3

53

139 000 ₽

2425

4

2

23

169 000 ₽

2448

2

1,5

99

126 000 ₽

2471

2

2

34

142 900 ₽

2494

3

3

23

163 000 ₽

2517

4

4

55

169 000 ₽

2540

2

3

22

149 000 ₽

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Формула (формула динамического массива, введенная в A19)

=ЛИНЕЙН(E2:E12; A2:D12; ИСТИНА; ИСТИНА)

Пример 4. Использование статистики F и r2

В предыдущем примере коэффициент определения (r2)составляет 0,99675 (см. ячейку A17 в результатах для ЛИТН), что указывает на крепкая связь между независимыми переменными и ценой продажи. F-статистику можно использовать для определения случайности этих результатов с таким высоким значением r2.

Предположим, что на самом деле взаимосвязи между переменными не существует, просто статистический анализ вывел сильную взаимозависимость по взятой равномерной выборке 11 зданий. Величина «Альфа» используется для обозначения вероятности ошибочного вывода о существовании сильная взаимозависимости.

Значения F и df в результатах функции LINEST можно использовать для оценки вероятности возникновения более высокого F-значения. F можно сравнивать с критическими значениями в опубликованных F-таблицах или с помощью функции FРАСП в Excel для вычисления вероятности случайного возникновения большего F-значения. Соответствующее F-распределение имеет v1 и v2 степени свободы. Если n — количество точек данных и конст = ИСТИНА или опущен, то v1 = n – df – 1 и v2 = df. (Если конст = ЛОЖЬ, то v1 = n – df и v2 = df.) Функция FIST с синтаксисом FDIST(F;v1;v2) возвращает вероятность возникновения более высокого F-значения, случайного. В этом примере df = 6 (ячейка B18) и F = 459,753674 (ячейка A18).

Предположим, что альфа имеет значение 0,05, v1 = 11 – 6 – 1 = 4, а v2 = 6, критический уровень F составляет 4,53. Поскольку F = 459,753674 значительно больше 4,53, вероятность того, что F-значение этого высокой случайности превышает 4,53, крайне маловероятно. (Если значение «Альфа» = 0,05, гипотеза о том, что между known_y и known_x нет связи, отклоняется при превышении F критического уровня (4,53).) Функцию FDIST в Excel можно использовать для получения вероятности случайного возникновения F-значения. Например, FIST(459,753674, 4, 6) = 1,37E-7, очень небольшая вероятность. Можно сделать вывод о том, что формула регрессии полезна для предсказания оценочного значения офисных зданий в этой области, найдя критический уровень F в таблице или с помощью функции FDIST. Помните, что крайне важно использовать правильные значения 1 и 2, вычисленные в предыдущем абзаце.

Пример 5. Вычисление t-статистики

Другой тест позволяет определить, подходит ли каждый коэффициент наклона для оценки стоимости здания под офис в примере 3. Например, чтобы проверить, имеет ли срок эксплуатации здания статистическую значимость, разделим -234,24 (коэффициент наклона для срока эксплуатации здания) на 13,268 (оценка стандартной ошибки для коэффициента времени эксплуатации из ячейки A15). Ниже приводится наблюдаемое t-значение:

t = m4 ÷ se4 = –234,24 ÷ 13,268 = –17,7

Если абсолютное значение t достаточно велико, можно сделать вывод, что коэффициент наклона можно использовать для оценки стоимости здания под офис в примере 3. В таблице ниже приведены абсолютные значения четырех наблюдаемых t-значений.

Если обратиться к справочнику по математической статистике, то окажется, что t-критическое двустороннее с 6 степенями свободы равно 2,447 при Альфа = 0,05. Критическое значение также можно также найти с помощью функции Microsoft Excel СТЬЮДРАСПОБР. СТЬЮДРАСПОБР(0,05; 6) = 2,447. Поскольку абсолютная величина t, равная 17,7, больше, чем 2,447, срок эксплуатации — это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных.

Переменная

t-наблюдаемое значение

Общая площадь

5,1

Количество офисов

31,3

Количество входов

4,8

Возраст

17,7

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

Источник

Понравилась статья? Поделить с друзьями:
  • Как найти скрытые папки в папку appdata
  • Как найти все страницы на домене
  • Как найти конверт в сбербанк онлайн
  • Как найти девушку в ровно
  • Как найти диапазон шкалы