Расчет доверительного интервала в Excel

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

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

Вычисление доверительного интервала

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

В Microsoft Excel существует два способа выполнения данной задачки:

  • Оператор ДОВЕРИТ.НОРМ – применяется в вариантах, когда дисперсия известна;
  • Оператор ДОВЕРИТ.СТЬЮДЕНТ– когда дисперсия неведома.

Ниже мы пошагово разберем оба способа на практике.

Способ 1: оператора ДОВЕРИТ.НОРМ

Данная функция в первый раз была внедрена в арсенал программки в редакции Эксель 2010 года (до данной нам версии ее подменял оператор “ДОВЕРИТ”). Оператор заходит в категорию “статистические”.

Формула функции ДОВЕРИТ.НОРМ смотрится так:

Как мы лицезреем, у функции есть три аргумента:

  • “Альфа” – это показатель уровня значимости, который берется за базу при расчете. Доверительный уровень считается так:
    • 1-«Альфа» . Это выражение применимо в случае, если значение “Альфа” представлено в виде коэффициента. К примеру, 1-0,7=0,3, где 0,7=70%/100%.
    • (100-«Альфа»)/100 . Применятся это выражение, если мы считаем доверительным уровень со значением “Альфа” в процентах. К примеру, (100-70)/100=0,3.

    Примечание: У данной функции наличие всех 3-х аргументов является неотклонимым условием.

    Оператор “ДОВЕРИТ”, который применялся в наиболее ранешних редакциях программки, содержит такие же аргументы и делает те же самые функции.

    Формула функции ДОВЕРИТ смотрится последующим образом:

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

    Граница доверительного интервала определяется последующей формулой:

    где Х – это среднее значение по данному спектру.

    Сейчас давайте разберемся, как использовать эти формулы на практике. Итак, у нас есть таблица с разными данными 10-ти проведенных замеров. При всем этом, обычное отклонение совокупы данных приравнивается 8.

    Таблица с данными

    Перед нами стоит задачка – получить значение доверительного интервала с 95%-ым уровнем доверия.

    Вставка функции в ячейку

      Сперва избираем ячейку для вывода результата. Потом кликаем по кнопочке “Вставить функцию” (слева от строчки формул).

    Аргументы функции ДОВЕРИТ.НОРМ

    • в поле “Альфа” указываем уровень значимости. В нашей задачке предполагается 95%-ый уровень доверия. Подставив данное значение в формулу расчета, которую мы разглядывали выше, получаем выражение: (100-95)/100 . Пишем его в поле аргумента (либо можно сходу написать итог вычисления, равный 0,05).
    • в поле “Станд_откл” согласно нашим условия, пишем цифру 8.
    • в поле “Размер” указываем количество исследуемых частей. В нашем случае было проведено 10 замеров, означает пишем цифру 10.

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

    • Для определения правой границы ДИ общая формула будет смотреться так:
      =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .
    • Буквально также и для левой границы, лишь заместо плюса необходимо поставить минус:
      =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)) .

    Способ 2: оператор ДОВЕРИТ.СТЬЮДЕНТ

    Сейчас давайте познакомимся со вторым оператором для определения доверительного интервала – ДОВЕРИТ.СТЬЮДЕНТ. Данная функция была внедрена в программку относительно не так давно, начиная с версии Эксель 2010, и ориентирована на определение ДИ избранной совокупы данных с применением распределения Стьюдента, при неведомой дисперсии.

    Формула функции ДОВЕРИТ.СТЬЮДЕНТ смотрится последующим образом:

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

    1. Поначалу избираем ячейку, куда планируем вывести итог. Потом кликаем по значку “Вставить функцию” (слева от строчки формул).Вставка функции в ячейку
    2. Раскроется уже отлично знакомое окно Мастера функций. Избираем категорию “Статистические”, потом из предложенного перечня функций щелкаем по оператору “ДОВЕРИТ.СТЬЮДЕНТ”, опосля чего же – OK.Выбор оператора ДОВЕРИТ.СТЬЮДЕНТ в Мастере функций
    3. В последующем окне нам необходимо настроить аргументы функции:.
      • В поле “Альфа” как и в первом способе указываем значение 0,05 (либо “100-95)/100”).
      • Перебегаем к аргументу “Станд_откл”. Т.к. по условиям задачки его значение нам непонятно, необходимо произвести надлежащие расчеты, в чем нам поможет оператор “СТАНДОТКЛОН.В”. Щелкаем по кнопочке прибавления функции и потом – по пт “Остальные функции…”.Добавление одной функции в аргументы другой
      • В следующем окне Мастера функций избираем оператор “СТАНДОТКЛОН.В” в группы “Статистические” и кликаем OK.Оператор СТАНДОТКЛОН.В в Мастере функций
      • Мы попадаем в окно опции аргументов функции, формула которой смотрится так: =СТАНДОТКЛОН.В(число1;число2;. ) . В качестве первого аргумента указываем спектр, включающий все ячейки столбца “Значение” (не считая шапки).Аргументы функции СТАНДОТКЛОН.В
      • Сейчас необходимо возвратиться назад в окно с аргументами функции “ДОВЕРИТ.СТЬЮДЕНТ”. Для этого щелкаем по одноименной надписи в поле ввода формул.Аргументы функции ДОВЕРИТ.СТЬЮДЕНТ
      • Сейчас перебегаем к крайнему аргументу “Размер”. Как и в первом способе, тут можно или просто указать спектр ячеек, или вставить оператор “СЧЕТ”. Избираем крайний вариант.
      • Как все аргументы заполнены, нажимаем клавишу OK.Заполненные аргументы функции ДОВЕРИТ.СТЬЮДЕНТ
    4. В избранной ячейке отобразится значение доверительного интервала согласно данным нами характеристикам.Результат функции ДОВЕРИТ.СТЬЮДЕНТ в ячейке
    5. Дальше нам необходимо высчитать значения границ ДИ. А для этого будет нужно получить среднее значение по избранному спектру. Для этого опять применим функцию “СРЗНАЧ”. Метод действий аналогичен тому, что был описан в первом способе.Результат функции СРЗНАЧ в ячейке
    6. Получив значение “СРЗНАЧ”, можно приступать к расчетам границ ДИ. Сами формулы ничем не различаются от тех, что использовались с оператором “ДОВЕРИТ.НОРМ”:
      • Правая граница ДИ=СРЗНАЧ+ДОВЕРИТ.СТЬЮДЕНТ
      • Левая граница ДИ=СРЗНАЧ-ДОВЕРИТ.СТЬЮДЕНТЗначения правой и левой границ доверительного интервала в ячейках таблицы

    Заключение

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

    Доверительные интервалы в Excel: значение, определение, построение и расчет

    Расширенные функции Excel дают неподменные и комфортные способы для разных статистических расчетов и анализа. Одной из таковых особенностей является интервал доверия, который употребляется для выражения степени неопределенности, связанной с исследованием. Доверительные интервалы в excel — это оценка событий в сочетании с верификацией вероятностей. Они обеспечивают возможный спектр выборочной пропорции либо выборочного среднего от настоящей толики / среднего, отысканного в популяции и показываются как: оценка +/- погрешность.

    Функция доверительного интервала

    You will be interested: How dangerous is the new coronavirus?

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

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

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

    Построение среднего значения совокупы

    Чтоб выстроить доверительный интервал для среднего значения совокупы, предоставленной вероятности и размера подборки, необходимо применить функцию «ДОВЕРИТ» в Excel, которая употребляет обычное распределение для вычисления значения доверия. Представим, исследователи случаем избрали 100 человек, измерили их вес и установили средний в 76 кг. Если необходимо выяснить средний показатель для людей в определенном городке, маловероятно, что он для наиболее большой группы будет иметь такое же среднее значение, как и подборка, состоящая всего из 100 человек.

    Еще наиболее возможно, что выборочное среднее в 76 кг быть может примерно равно (неведомому) популяционному среднему, и необходимо знать, как четким является оценочный ответ. Эта неопределенность, сплетенная с оценкой интервалов, именуется уровнем достоверности, обычно 95%. Функция «ДОВЕРИТ» (альфа, сигма, n) возвращает значение, применяемое для построения ДИ среднего числа совокупы. Предполагается, что данные выборок соответствуют обычным обычным распределениям с известной сигмой обычного отличия, а размер подборки равен n. Перед тем как высчитать доверительный интервал в excel 95% уровня, принимают альфу как 1 — 0,95 = 0,05.

    Форматы функции CONFIDENCE

    Функция CONFIDENCE либо ДОВЕРИТ, определяется пределами доверия — это нижняя и верхняя границы ДИ и являются 95% показателями. К примеру, при исследовании предпочтении, было найдено, что 70% людей предпочитают Боржоми , по сопоставлению с Пепси при ДИ в 3% и уровнем доверия 95%, тогда существует 95-процентная возможность того, что настоящая пропорция составляет от 67 до 73%.

    Функции «ДОВЕРИТ» показываются под разными синтаксисами в различных версиях Excel. К примеру, Excel 2010 имеет две функции: «ДОВЕРИТ.НОРМ» и «ДОВЕРИТ.T», которые помогают вычислять ширину «ДИ. ДОВЕРИТ.НОРМ» употребляется, когда понятно обычное отклонение измерения. В неприятном случае применяется «ДОВЕРИТ.T», оценка осуществляется по данным подборки. Доверительные интервалы в excel до 2010 года имели лишь функцию «ДОВЕРИТ». Его аргументы и результаты были подобными аргументам функции «ДОВЕРИТ.НОРМ».

    1-ый как и раньше доступен в наиболее поздних версиях Excel для обеспечения сопоставимости. #NUM! Error — происходит, если альфа меньше либо равна 0, либо больше либо равна 0. Данное обычное отклонение меньше либо равно 0. Обозначенный размер аргумента меньше единицы. #СТОИМОСТЬ! Error — происходит, если хоть какой из предоставленных аргументов не является числовым.

    Функция интерполяции доверия

    «ДОВЕРИТ.» классифицируется по функциям статистики и будет высчитывать и возвращать ДИ для среднего значения. Доверительные интервалы в excel могут быть очень полезными для денежного анализа. Как аналитик, «ДОВЕРИТ.» помогает в прогнозировании и корректировке для широкого круга целей, методом оптимизации принятия денежных решений. Это производится с применением графического отображения данных в наборе переменных.

    Аналитики могут принимать наиболее действенные решения на базе статистической инфы, предоставляемой обычным распределением. К примеру, они могут отыскать связь меж приобретенным доходом и расходами, затрачиваемыми на предметы роскоши. Чтоб вычислить ДИ для среднего значения совокупы, возвращаемое доверительное значение, обязано быть добавлено и вычтено из среднего значения подборки. К примеру, для среднего значения подборки x: Доверительный интервал = x ± ДОВЕРИТ.

    Пример расчета доверительного интервала в excel — представим, что нам даны последующие данные:

    Функция доверительного интервала Excel употребляется для расчета ДИ со значением 0,05 (т. е. уровень достоверности 95%) для среднего времени подборки для исследования времени коммутации в кабинете на 100 человек. Среднее значение эталона составляет 30 минут, а обычное отклонение составляет 2,5 минутки. Доверительный интервал составляет 30 ± 0,48999, что соответствует спектру 29,510009 и 30,48999 (минут).

    Интервалы и обычное распределение

    Более знакомое внедрение доверительного интервала, значит «погрешность ошибок». В опросах погрешность составляет плюс либо минус 3%. ДИ полезны в контекстах, которые выходят за рамки данной нам обычный ситуации. Они могут употребляться с ненормальными распределениями, которые очень искажены. Для вычисления прогноза доверительного интервала в excel требуются последующие строй блоки:

    Перед тем как выстроить доверительный интервал в excel, изучают его вокруг среднего значения подборки, начинают с принятия решения о том, какой будет принят процент остальных средств подборки, если они были собраны и рассчитаны в этом интервале. Если это так , то 95% вероятных образцов будут захвачены ДИ с 1,96 обычных отклонений выше и ниже эталона.

    Обычная ошибка среднего

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

    Доверительные интервалы для среднего значения могут быть получены несколькими методами: при помощи SigmaXL, описательной статистики, гистограмм,1-образного t-теста и интервалов доверия, однобоких диаграмм ANOVA и Multi-Vari. Чтоб графически иллюстрировать ДИ для среднего значения «Удовлетворенность», делают диаграмму Multi-Vari (с 95% CI Mean Options) с внедрением данных Customer Data.xls. Точки соответствуют отдельным данным. Маркеры демонстрируют наибольший доверительный предел 99%, и средний 95%-ый предел.

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

    Расчет при помощи SigmaXL

    Доверительные интервалы весьма важны для осознания приобретенных данных и принятия решений по ним. Чтоб высчитать ДИ для дискретной пропорции, употребляют SigmaXL> Шаблоны и калькуляторы> Главные статистические шаблоны> 1 интервал доверительных отношений. Перед тем как отыскать доверительный интервал в excel, делают последующие деяния:

    Направить внимание, что доверительный интервал в 95% значит: в среднем настоящий параметр популяции (средний, обычное отклонение либо пропорция) будет находиться в интервале 19 раз из 20. Будет представлен юзеру: 95%-ый доверительный интервал для всякого отсчета. Среднее значение (95% CI). Доверительный интервал 95% для обычного отличия (95% CI Sigma — не путать это с уровнем свойства Sigma Process).

    Статистика и уровни доверия

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

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

    Уровень доверия представляет собой гарантированную уверенность. К примеру, с уровнем достоверности 90%, это значит, что 10% риск будет неверным. Обычно, неплохой практикой является выбор достоверности в 95%. Таковым образом, наибольший доверительный уровень является огромным, чем больше размер подборки. Маржинальный коэффициент является индикатором, выведенным конкретно из доверительного порога. В таблице приведены некие примеры для более всераспространенных значений.

    Как высчитать доверительный интервал в Excel

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

    Что такое «Доверительный интервал»

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

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

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

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

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

    Если нормативная черта попадает в обозначенный просвет (его именуют доверительным интервалом с данной вероятностью), то тестируемые изделия были изготовлены отменно.

    Тут есть некоторое количество принципиальных моментов, на которые нужно направить внимание:

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

    На основании изложенных принципов можно вычислить доверительный просвет с данной надёжностью.

    Как высчитать «Доверительный интервал»?

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

    Опосля того, как выбрана подходящая клетка, нужно прибегнуть к помощи мастера. Его можно вызвать, нажав на клавишу, где есть обозначение «fx». Опосля этого покажется окно для выбора. Функции для вычисления доверительного интервала можно отыскать в группы статистических.

    Её итог равен наибольшей величине отличия от среднего.

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

    Функция ДОВЕРИТ.НОРМ

    Эта функция находится в Excel, начиная с кабинета 2010. Ранее заместо неё использовалась ДОВЕРИТ(), которая от неё ничем не различается.

    При использовании мастера её необходимо находить в разделе статистических.

    Для того чтоб её употреблять, нужно ввести числа в столбик и избрать ячейку, в которой будут проводиться вычисления. Потом жмут клавишу вызова мастера функций. Будет нужно ввести три аргумента:

    1. 1-ый именуется «альфа». Тут необходимо указать возможность, с какой случайная величина обязана попадать вовнутрь искомого интервала. Обычно в критериях задачки её указывают в процентах. Пусть речь идёт о 95%. Подходящий параметр получают по формуле (100 — Процент)/100, где Процент =95. В рассматриваемом случае альфа=0,05.
    2. Тут пишут обычное отклонение. К моменту проведения вычислений оно обязано быть понятно. Это число является вторым параметром в формуле.
    3. На 3-ем месте необходимо указать количество приобретенных результатов. Эту цифру можно ввести руками, а можно для этого употреблять СЧЁТ(), которая считает количество ячеек в избранной области.

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

    Сейчас нужно вычислить среднюю величину (Ср). Это делают при помощи мастера функций, используя СРЕДН().

    Сейчас можно найти границы доверительного интервала. Для этого в одной из ячеек вычисляют Ср-Дл (левая граница), а в иной — Ср+Дл Правая граница.

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

    Функция ДОВЕРИТ.СТЮДЕНТ

    Эта функция в первый раз возникла в Excel 2010. Ранее она не использовалась. Её применение почти во всем аналогично применению ДОВЕРИТ.НОРМ(), кроме последующих особенностей:

    1. Тут употребляется не обычное распределение, а распределение Стьюдента.
    2. ДОВЕРИТ.СТЮДЕНТ() применяется в тех вариантах, когда нет инфы о том, чему равно обычное отклонение. При всем этом оно рассчитывается на базе предоставленных значений случайной величины.

    Разница в использовании состоит в том, что при указании второго параметра пишут не число, а формулу для определения из имеющихся значений с внедрением СТАНДОТКЛОН.В().

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

    Как отыскать границу интервала

    При помощи ДОВЕРИТ.СТЮДЕНТ() и ДОВЕРИТ.НОРМ() можно получить отклонение границ интервала (Откл) от среднего значения. При всем этом:

    1. Левая граница доверительного интервала равна (среднее значение — Откл).
    2. Правая — (среднее значение + Откл).

    Новейшие значения случайной величины попадут вовнутрь этого интервала с данной вероятностью.

    Пример расчета

    Для пояснения произнесенного будет приведён последующий пример.

    пример расчета

    Были получены 12 значений. Они были внесены в Excel. Числа указаны на последующем рисунке.

    Цифрой 1 обозначена ячейка где будут происходить вычисления. Цифра 2 соответствует вызову мастера функций.

    Выбор функции

    Дальше происходит выбор функции для помещения в клетку.

    Ввод параметров

    Потом необходимо ввести характеристик для вычисления. Возможность попадания в интервал обязана составлять 97%, отклонение понятно — оно равно 8.

    Ввод формулы

    Для определения количества значений употребляется СЧЁТ().Получена ширина доверительного интервала, равная 5,011609.

    Результат вычислений

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

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

    Интересно почитать:  Верхний индекс в excel
Ссылка на основную публикацию
Adblock
detector