Ошибка среднего на графике

Анализ НОРМАЛЬНОСТИ РАСПРЕДЕЛЕНИЯ | АНАЛИЗ ДАННЫХ #4

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

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

Четыре группы, их средства, стандартные отклонения и стандартные ошибки. На графике показаны групповые средства.

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

= B11 / SQRT (COUNT (B2: B9))

Фокус в том, чтобы получить каждую стандартную ошибку в графике. В Excel 2016 это легко сделать, и оно отличается от предыдущих версий Excel. Начните с выбора графика. Это приведет к появлению вкладок Design and Format. Выберите

Дизайн | Добавить элемент диаграммы | Ошибка баров | Дополнительные параметры ошибок.

Путь к вставке баров ошибок.

В меню «Бары ошибок» вы должны быть осторожны. Один из вариантов — стандартная ошибка. Избегай это. Если вы считаете, что этот выбор указывает Excel на стандартную ошибку каждого значения на графике, будьте уверены, что Excel не имеет абсолютно никакого представления о том, о чем вы говорите. Для этого выбора Excel вычисляет стандартную ошибку набора из четырех средств — не стандартную ошибку в каждой группе.

Дополнительные параметры панели ошибок являются подходящим выбором. Откроется панель «Формат ошибок».

Панель «Ошибки формата».

В области «Направление» панели выберите переключатель рядом с «Оба», а в области «Стиль конца» выберите переключатель рядом с «Кап».

Один выбор в области «Сумма ошибки» — это стандартная ошибка. Избегайте этого. Это не означает, что Excel помещает стандартную ошибку каждого среднего на график.

Прокрутите вниз до области «Сумма ошибки» и выберите переключатель рядом с «Пользовательский». Это активирует кнопку «Укажите значение». Нажмите эту кнопку, чтобы открыть диалоговое окно «Пользовательские ошибки». С помощью курсора в поле «Положительное значение ошибки» выберите диапазон ячеек, который содержит стандартные ошибки ($ B $ 12: $ E $ 12). Вставьте вкладку «Отрицательная ошибка» и сделайте то же самое.

Диалоговое окно «Нестандартные ошибки».

Это поле Negative Error Value может дать вам небольшую проблему. Перед тем, как вводить диапазон ячеек, убедитесь, что он очищен от значений по умолчанию.

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

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

Сводка

В этой статье описывается функция ДОВЕРИТЕЛЬных СОВПАДЕНИй в Microsoft Office Excel 2003 и Microsoft Office Excel 2007, показано, как используется функция, и сравниваются результаты функции для Excel 2003 и для Excel 2007 с результатами более ранней достоверности. версиях Excel.

Значение доверительного интервала часто ошибочно интерпретируется, и мы попробуем получить объяснение допустимых и недопустимых операторов, которые можно выполнить после определения ДОСТОВЕРНОсти данных.

Дополнительные сведения

Функция ДОВЕРИТ (альфа, Сигма, н) возвращает значение, которое можно использовать для создания доверительного интервала для среднего Генеральной совокупности. Доверительный интервал — это диапазон значений, которые центрируются по известной выборочной среднему значению. Наблюдение в образце предполагается из нормального распределения со стандартным отклонением, Сигма, а количество наблюдений в примере равно n.

Синтаксис

Параметры: альфа — вероятность и 0

Пример использования

Представим, что баллы для (IQ) для интеллекта должны следовать нормальному распределению со стандартным отклонением 15. Вы протестируете Икс для примера из 50 учащихся в местном учебном заведении и получите выборочное среднее арифметическое 105. Вы хотите вычислить доверительный интервал 95% для среднего Генеральной совокупности. Доверительный интервал 95% или 0,95 соответствует альфа = 1 – 0,95 = 0,05.

Чтобы продемонстрировать функцию достоверности, создайте пустой лист Excel, Скопируйте приведенную ниже таблицу, а затем выберите ячейку a1 на пустом листе Excel. В меню Правка выберите команду Вставить.

Примечание: В Excel 2007 нажмите кнопку Вставить в группе буфер обмена на вкладке Главная .

Записи в таблице ниже заполняют ячейки a1: B7 на листе.

= ДОВЕРИТ (B1; B2; B3)

= НОРМСТОБР (1-B1/2) * B2/SQRT (B3)

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

Выделив вставляемый диапазон, наведите указатель на пункт столбец в меню Формат , а затем выберите пункт Автоподбор по выделению.

Примечание: В Excel 2007 с выделенным диапазоном ячеек в группе ячейки на вкладке Главная нажмите кнопку Формат и выберите команду Автоподбор ширины столбца.

В ячейке A6 показано значение достоверности. В ячейке A7 показано одно и то же значение, так как при вызове метода ДОВЕРИТ (Alpha; Сигма; n) возвращаются результаты вычислений.

NORMSINV(1 – alpha/2) * sigma / SQRT(n)

Никаких изменений не было сделано, но функция НОРМСТОБР была улучшена в Microsoft Excel 2002, а затем в Excel 2002 и Excel 2007 было внесено больше улучшений. Таким образом, точность может возвращать другие (и улучшенные) результаты в этих более поздних версиях Excel, так как точное СОВПАДЕНИе основывается на НОРМСТОБР.

Это не значит, что в более ранних версиях Excel вы должны потерять уверенность. Неточности в НОРМСТОБР обычно произошел для значений аргумента очень близко к 0 или очень близко к 1. На практике альфа-канал обычно имеет значение 0,05, 0,01 или, возможно, 0,001. Значения Alpha должны быть гораздо меньше, чем, например 0,0000001, до тех пор, пока не будут замечены ошибки округления в НОРМСТОБР.

Примечание: Ознакомьтесь со статьей «НОРМСТОБР» для обсуждения различий вычислений в НОРМСТОБР.

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

826772 Статистические функции Excel: НОРМСТОБР

Интерпретация результатов достоверности

Для Excel 2003 и Excel 2007 файл справки Excel обновлен, так как все предыдущие версии файла справки дали ошибочные рекомендации по интерпретации результатов. В примере «допустим, что в нашем примере для пользователей 50, средняя продолжительность поездки на работу составляет 30 минут со стандартным отклонением Генеральной совокупности 2,5. Мы можем получить 95% уверенности в том, что среднее Генеральной совокупности находится в интервале 30 +/-0,692951 «где 0,692951 — значение, возвращаемое ДОСТОВЕРНОстью (0,05, 2,5, 50).

В этом примере «заключение» считывает «средняя продолжительность поездки на работу равняется 30 ± 0,692951 минутам или 29,3 – 30,7 минут». Предполагается, что это также является оператором для среднего Генеральной совокупности в интервале [30 – 0,692951, 30 + 0,692951] с вероятностью 0,95.

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

Например, классическое статистиЦиан может попытаться выполнить двустороннее Тестирование гипотезы на основе суппоситион нормального распределения с известным стандартным отклонением (например, 2,5), определенным предварительно выбранным значением среднего значения Генеральной совокупности, μ0 и предварительно выбранный уровень значимости (например, 0,05). Результат теста будет основываться на значении наблюдаемого выборочного среднего (например, 30) и нулевой гипотезы о том, что μ0 может быть отклонено на уровне 0,05, если наблюдаемый выборочный пример был слишком далеко от μ0 в обоих направлениях. Если пустая гипотеза отклонена, то интерпретация примера означает, что все, что далеко или дальше от μ0, появлялось бы менее чем на 5% времени в рамках суппоситион, что μ0 является истинным средним Генеральной совокупности. Проведя этот тест, классическое статистиЦиан по-прежнему не может делать никаких инструкций по выборке вероятности для среднего Генеральной совокупности.

Байесиан статистиЦиан, с другой стороны, будет начинаться с предполагаемого распределения вероятности для среднего Генеральной совокупности (именуемого распространением приори), который будет собирать экспериментальные доказательства точно так же, как и классический статистиЦиан, и использовать это свидетельство. чтобы изменить его распределение вероятности для среднего Генеральной совокупности и, таким образом, получить постериори распределение. Excel не предоставляет статистических функций, которые помогут вам Байесиан статистиЦиан. Статистические функции Excel предназначены для использования в классических статистиЦианс.

Доверительные интервалы связаны с проверками гипотез. При использовании экспериментального свидетельства доверительный интервал делает более четким оператором для значений гипотетического математического Генеральной совокупности μ0, который бы мог бы принимать признание пустого предположения о том, что среднее Генеральной совокупности — μ0, и значения μ0, которые выдают отклонения. пустого предположения о том, что среднее Генеральной совокупности — μ0. Классическое статистиЦиан не может делать никаких инструкций о том, что среднее значение Генеральной совокупности попадает в определенный интервал времени, так как она не создает приори допущений об этом распространении и предполагается, что оно было бы обязательным, если бы оно было Используйте экспериментальные доказательства для изменения.

Ознакомьтесь со связью между тестами и доверительными интервалами с помощью примера в начале этого раздела. Связь между ДОСТОВЕРНОстью и НОРМСТОБР, которая указана в последнем разделе, имеет следующие возможности:

CONFIDENCE(0.05, 2.5, 50) = NORMSINV(1 – 0.05/2) * 2.5 / SQRT(50) = 0.692951

Так как выборочное среднее — 30, доверительный интервал составляет 30 +/-0,692951.

Теперь рассмотрим двустороннюю проверку с уровнем значимости 0,05, как описано выше, которое предполагает нормальное распределение со стандартным отклонением 2,5, а также выбор размера 50 и определенного среднего значения для Генеральной совокупности, μ0. Если это среднее значение истинной Генеральной совокупности, то выборочное среднее будет получено из нормального распределения со средним значением μ0 и стандартным отклонением, 2,5/SQRT (50). Это распределение является симметричным для μ0 и вы хотите отклонить пустую гипотезу, если ABS (выборочное среднее — μ0) > какое-либо пороговое значение. Значение порогового значения может быть таким, что если μ0 является истинным средним заполнением, то есть значение выборочное среднее-μ0, превышающее эту отсечение или значение μ0 — выборочное среднее выше, чем эта отсечение, будет возникать с вероятностью 0,05/2. Это пороговое значение:

NORMSINV(1 – 0.05/2) * 2.5/SQRT(50) = CONFIDENCE(0.05, 2.5, 50) = 0. 692951

Поэтому отклоните пустую гипотезу (среднее заполнение = μ0), если выполняется одно из следующих условий:

выборочное среднее — μ0 > 0. 692951
0 — выборочное среднее > 0. 692951

Поскольку выборочное среднее = 30 в нашем примере, эти два оператора становятся следующими операторами:

30-μ0 > 0. 692951
μ0 – 30 > 0. 692951

Переписывая их так, чтобы только μ0 в левой части выходили следующие инструкции:

μ0 30 + 0. 692951

Это именно те значения μ0, которые не входят в доверительный интервал [30 – 0,692951, 30 + 0,692951]. Таким образом, доверительный интервал [30 – 0,692951, 30 + 0,692951] содержит такие значения μ0, где нулевая гипотеза, которой μ0 Генеральной совокупности, не будет отклоняться, получая пример свидетельства. Для значений μ0 за пределами этого интервала значение NULL свидетельствует о том, что основание Генеральной совокупности — μ0 было отклонено при выборке свидетельства.

Заключения

Неточности в более ранних версиях Excel обычно возникают для очень мелких и очень больших значений «p» в НОРМСТОБР (p). УВЕРЕННОСТЬ оцениваются с помощью вызова НОРМСТОБР (p), поэтому точность НОРМСТОБР является потенциальной проблемой для пользователей с уверенностью. Однако значения p, которые используются в упражнениях, вряд ли будут достаточно значительны для того, чтобы вызвать существенные ошибки округления в НОРМСТОБР, и производительность УВЕРЕНности не должна быть важна для пользователей какой-либо версии Excel.

В этой статье рассказывается о том, как интерпретировать результаты УВЕРЕНности. Другими словами, мы задавали вопрос «Каково значение доверительного интервала?» Доверительные интервалы часто непонятны. К сожалению, файлы справки Excel во всех версиях Excel, более ранних, чем Excel 2003, были внесены в это неправильное понимание. Улучшен файл справки Excel 2003.

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

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

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

Расчет ошибки средней арифметической

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

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

Способ 1: расчет с помощью комбинации функций

Прежде всего, давайте составим алгоритм действий на конкретном примере по расчету ошибки средней арифметической, используя для этих целей комбинацию функций. Для выполнения задачи нам понадобятся операторы СТАНДОТКЛОН.В, КОРЕНЬ и СЧЁТ.

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

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

Открывается Мастер функций. Производим перемещение в блок «Статистические». В представленном перечне наименований выбираем название «СТАНДОТКЛОН.В».

Запускается окно аргументов вышеуказанного оператора. СТАНДОТКЛОН.В предназначен для оценивания стандартного отклонения при выборке. Данный оператор имеет следующий синтаксис:

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

Итак, устанавливаем курсор в поле «Число1». Далее, обязательно произведя зажим левой кнопки мыши, выделяем курсором весь диапазон выборки на листе. Координаты данного массива тут же отображаются в поле окна. После этого клацаем по кнопке «OK».

В ячейку на листе выводится результат расчета оператора СТАНДОТКЛОН.В. Но это ещё не ошибка средней арифметической. Для того, чтобы получить искомое значение, нужно стандартное отклонение разделить на квадратный корень от количества элементов выборки. Для того, чтобы продолжить вычисления, выделяем ячейку, содержащую функцию СТАНДОТКЛОН.В. После этого устанавливаем курсор в строку формул и дописываем после уже существующего выражения знак деления (/). Вслед за этим клацаем по пиктограмме перевернутого вниз углом треугольника, которая располагается слева от строки формул. Открывается список недавно использованных функций. Если вы в нем найдете наименование оператора «КОРЕНЬ», то переходите по данному наименованию. В обратном случае жмите по пункту «Другие функции…».

Снова происходит запуск Мастера функций. На этот раз нам следует посетить категорию «Математические». В представленном перечне выделяем название «КОРЕНЬ» и жмем на кнопку «OK».

Открывается окно аргументов функции КОРЕНЬ. Единственной задачей данного оператора является вычисление квадратного корня из заданного числа. Его синтаксис предельно простой:

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

Устанавливаем курсор в поле «Число» и кликаем по знакомому нам треугольнику, который вызывает список последних использованных функций. Ищем в нем наименование «СЧЁТ». Если находим, то кликаем по нему. В обратном случае, опять же, переходим по наименованию «Другие функции…».

В раскрывшемся окне Мастера функций производим перемещение в группу «Статистические». Там выделяем наименование «СЧЁТ» и выполняем клик по кнопке «OK».

Запускается окно аргументов функции СЧЁТ. Указанный оператор предназначен для вычисления количества ячеек, которые заполнены числовыми значениями. В нашем случае он будет подсчитывать количество элементов выборки и сообщать результат «материнскому» оператору КОРЕНЬ. Синтаксис функции следующий:

В качестве аргументов «Значение», которых может насчитываться до 255 штук, выступают ссылки на диапазоны ячеек. Ставим курсор в поле «Значение1», зажимаем левую кнопку мыши и выделяем весь диапазон выборки. После того, как его координаты отобразились в поле, жмем на кнопку «OK».

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

Результат вычисления ошибки средней арифметической составил 0,505793. Запомним это число и сравним с тем, которое получим при решении поставленной задачи следующим способом.

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

Способ 2: применение инструмента «Описательная статистика»

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

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

После того, как открыт документ с выборкой, переходим во вкладку «Файл».

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

Запускается окно параметров Эксель. В левой части данного окна размещено меню, через которое перемещаемся в подраздел «Надстройки».

В самой нижней части появившегося окна расположено поле «Управление». Выставляем в нем параметр «Надстройки Excel» и жмем на кнопку «Перейти…» справа от него.

Запускается окно надстроек с перечнем доступных скриптов. Отмечаем галочкой наименование «Пакет анализа» и щелкаем по кнопке «OK» в правой части окошка.

После выполнения последнего действия на ленте появится новая группа инструментов, которая имеет наименование «Анализ». Чтобы перейти к ней, щелкаем по названию вкладки «Данные».

После перехода жмем на кнопку «Анализ данных» в блоке инструментов «Анализ», который расположен в самом конце ленты.

Запускается окошко выбора инструмента анализа. Выделяем наименование «Описательная статистика» и жмем на кнопку «OK» справа.

Запускается окно настроек инструмента комплексного статистического анализа «Описательная статистика».

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

В блоке «Группирование» оставляем настройки по умолчанию. То есть, переключатель должен стоять около пункта «По столбцам». Если это не так, то его следует переставить.

Галочку «Метки в первой строке» можно не устанавливать. Для решения нашего вопроса это не важно.

Далее переходим к блоку настроек «Параметры вывода». Здесь следует указать, куда именно будет выводиться результат расчета инструмента «Описательная статистика»:

• На новый лист;
• В новую книгу (другой файл);
• В указанный диапазон текущего листа.

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

Далее следует блок настроек определяющий, какие именно данные нужно вводить:

• Итоговая статистика;
• К-ый наибольший;
• К-ый наименьший;
• Уровень надежности.

Для определения стандартной ошибки обязательно нужно установить галочку около параметра «Итоговая статистика». Напротив остальных пунктов выставляем галочки на свое усмотрение. На решение нашей основной задачи это никак не повлияет.

После того, как все настройки в окне «Описательная статистика» установлены, щелкаем по кнопке «OK» в его правой части.

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

Стандартная ошибка средней арифметической

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

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

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

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

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

Расчет дисперсии и стандартной ошибки средней арифметической

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

где x_i – значения переменной,
n – количество значений.

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

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

где σ 2 – это дисперсия, случайной величины, причем генеральная.

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

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

Формула стандартной ошибки средней при использовании генеральной дисперсии

Формула стандартной ошибки средней при использовании выборочной дисперсии

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

Назначение и свойство стандартной ошибки средней арифметической

Стандартная ошибка средней много, где используется. И очень полезно понимать ее свойства. Посмотрим еще раз на формулу стандартной ошибки средней:

Числитель – это стандартное отклонение выборки и здесь все понятно. Чем больше разброс данных, тем больше стандартная ошибка средней – прямо пропорциональная зависимость.

Посмотрим на знаменатель. Здесь находится квадратный корень из объема выборки. Соответственно, чем больше объем выборки, тем меньше стандартная ошибка средней. Для наглядности изобразим на одной диаграмме график нормально распределенной переменной со средней равной 10, сигмой – 3, и второй график – распределение средней арифметической этой же переменной, полученной по 16-ти наблюдениям (которое также будет нормальным).

Судя по формуле, разброс стандартной ошибки средней должен быть в 4 раза (корень из 16) меньше, чем разброс исходных данных, что и видно на рисунке выше. Чем больше наблюдений, тем меньше разброс средней.

Казалось бы, что для получения наиболее точной средней достаточно использовать максимально большую выборку и тогда стандартная ошибка средней будет стремиться к нулю, а сама средняя, соответственно, к математическому ожиданию. Однако квадратный корень объема выборки в знаменателе говорит о том, что связь между точностью выборочной средней и размером выборки не является линейной. Например, увеличение выборки с 20-ти до 50-ти наблюдений, то есть на 30 значений или в 2,5 раза, уменьшает стандартную ошибку средней только на 36%, а со 100-а до 130-ти наблюдений (на те же 30 значений), снижает разброс данных лишь на 12%.

Лучше всего изобразить эту мысль в виде графика зависимости стандартной ошибки средней от размера выборки. Пусть стандартное отклонение равно 10 (на форму графика это не влияет).

Видно, что примерно после 50-ти значений, уменьшение стандартной ошибки средней резко замедляется, после 100-а – наклон постепенно становится почти нулевым.

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

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

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

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

Расчет с помощью комбинаций функций

На примере рассмотрим составленный алгоритм действий по расчету ошибки средней арифметической с использованием комбинаций функций. Для того чтобы выполнить задачу, нужно использовать операторы СТАНДОТКЛОН.В, КОРЕНЬ и СЧЁТ. Выборка будет использоваться из 12 чисел, которые представлены в таблице.

Выделите ячейку, в которой отобразится итоговое значение стандартной ошибки. Кликаете на иконку «Вставить функцию».

Появится Мастер функций, в котором нужно произвести перемещение в блок «Статистические». Появится список наименований, выбираете «СТАНДОТКЛОН.В».

Запустится окно аргументов выбранного оператора, предназначенного для оценивания стандартного отклонения при выборке. У него такой синтаксис — =СТАНДОТКЛОН.В(число1;число2;…). Устанавливаете курсор в полу «Число1». Далее, зажав левую кнопку мыши, выделяете курсором весь диапазон выборки, чтобы координаты этого массива отобразились там же в поле окна. Кликаете на ОК.

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

Должен снова запуститься Мастер функций, в котором нужно перейти в категорию «Математические». Выделяете там «КОРЕНЬ» и кликаете ОК.

Далее должно открыться окно аргументов функции КОРЕНЬ. Его синтаксис простой — =КОРЕНЬ(число). Устанавливаете курсор в поле «Число» и нажимаете на уже знакомый треугольник, чтобы показался список последних использованных функций. Находите «СЧЕТ» и нажимаете на него. Если в списке его нет, тогда нажимаете на «Другие функции…».

Появится раскрывшееся окно Мастера функций, в котором нужно переместиться в группу «Статистические». В ней выделяете «СЧЕТ» и кликаете ОК.

Должно запуститься окно аргументов функции СЧЕТ. Синтаксис функции будет таким — =СЧЁТ(значение1;значение2;…). Ставите курсор в строку «Значение1» и зажимаете левую кнопку мыши, чтобы выделить весь диапазон выборки. Когда координаты отобразятся, жмите ОК.

Когда будет выполнено последнее действие, то не только произведется расчет количества ячеек, которые заполнены числами, но и вычисляется ошибка средней арифметической. Величина будет выведена в ячейку с размещенной сложной формулой, вид которой таков — =СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13)).

Если выборка до 30 единиц, тогда лучше применять немного другую формулу — =СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13)-1).

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

Когда будет открыт документ с выборкой, нужно перейти во вкладку «Файл».

В левом вертикальном меню заходите в раздел «Параметры».

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

В самом низу окна находите «Управление» в выставляете в нем параметр «Надстройки Excel». Кликаете на «Перейти…» справа от него.

В окне надстроек появится список скриптов, которые доступны и нужно отметить галочкой «Пакет анализа», а затем нажать ОК.

Теперь на странице должна появиться новая группа инструментов «Анализ». Для перехода к ней кликаете на вкладку «Данные».

Кликаете на «Анализ данных» в блоке инструментов «Анализ» в самом конце.

Запустится окно выбора инструмента анализа, в котором необходимо выделить «Описательная статистика» и нажать справа на ОК.

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

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

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

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

Четыре группы, их средства, стандартные отклонения и стандартные ошибки. На графике показаны групповые средства.

Для каждой группы вы можете использовать

AVERAGE

для вычисления среднего и

STDEV. S

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


= B11 / SQRT (COUNT (B2: B9))


Фокус в том, чтобы получить каждую стандартную ошибку в графике. В Excel 2016 это легко сделать, и оно отличается от предыдущих версий Excel. Начните с выбора графика. Это приведет к появлению вкладок Design and Format. Выберите

Дизайн | Добавить элемент диаграммы | Ошибка баров | Дополнительные параметры ошибок.

Путь к вставке баров ошибок.

В меню «Бары ошибок» вы должны быть осторожны. Один из вариантов — стандартная ошибка. Избегай это. Если вы считаете, что этот выбор указывает Excel на стандартную ошибку каждого значения на графике, будьте уверены, что Excel не имеет абсолютно никакого представления о том, о чем вы говорите. Для этого выбора Excel вычисляет стандартную ошибку набора из четырех средств — не стандартную ошибку в каждой группе.

Дополнительные параметры панели ошибок являются подходящим выбором. Откроется панель «Формат ошибок».

Панель «Ошибки формата».

В области «Направление» панели выберите переключатель рядом с «Оба», а в области «Стиль конца» выберите переключатель рядом с «Кап».

Один выбор в области «Сумма ошибки» — это стандартная ошибка. Избегайте этого. Это не означает, что Excel помещает стандартную ошибку каждого среднего на график.

Прокрутите вниз до области «Сумма ошибки» и выберите переключатель рядом с «Пользовательский». Это активирует кнопку «Укажите значение». Нажмите эту кнопку, чтобы открыть диалоговое окно «Пользовательские ошибки». С помощью курсора в поле «Положительное значение ошибки» выберите диапазон ячеек, который содержит стандартные ошибки ($ B $ 12: $ E $ 12). Вставьте вкладку «Отрицательная ошибка» и сделайте то же самое.

Диалоговое окно «Нестандартные ошибки».

Это поле Negative Error Value может дать вам небольшую проблему. Перед тем, как вводить диапазон ячеек, убедитесь, что он очищен от значений по умолчанию.

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

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

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

Расчет ошибки средней арифметической

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

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

Способ 1: расчет с помощью комбинации функций

Прежде всего, давайте составим алгоритм действий на конкретном примере по расчету ошибки средней арифметической, используя для этих целей комбинацию функций. Для выполнения задачи нам понадобятся операторы СТАНДОТКЛОН.В, КОРЕНЬ и СЧЁТ.

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

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



2. Открывается Мастер функций. Производим перемещение в блок «Статистические». В представленном перечне наименований выбираем название «СТАНДОТКЛОН.В».



3. Запускается окно аргументов вышеуказанного оператора. СТАНДОТКЛОН.В предназначен для оценивания стандартного отклонения при выборке. Данный оператор имеет следующий синтаксис:

   =СТАНДОТКЛОН.В(число1;число2;…)

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

   Итак, устанавливаем курсор в поле «Число1». Далее, обязательно произведя зажим левой кнопки мыши, выделяем курсором весь диапазон выборки на листе. Координаты данного массива тут же отображаются в поле окна. После этого клацаем по кнопке «OK».



4. В ячейку на листе выводится результат расчета оператора СТАНДОТКЛОН.В. Но это ещё не ошибка средней арифметической. Для того, чтобы получить искомое значение, нужно стандартное отклонение разделить на квадратный корень от количества элементов выборки. Для того, чтобы продолжить вычисления, выделяем ячейку, содержащую функцию СТАНДОТКЛОН.В. После этого устанавливаем курсор в строку формул и дописываем после уже существующего выражения знак деления (/). Вслед за этим клацаем по пиктограмме перевернутого вниз углом треугольника, которая располагается слева от строки формул. Открывается список недавно использованных функций. Если вы в нем найдете наименование оператора «КОРЕНЬ», то переходите по данному наименованию. В обратном случае жмите по пункту «Другие функции…».



5. Снова происходит запуск Мастера функций. На этот раз нам следует посетить категорию «Математические». В представленном перечне выделяем название «КОРЕНЬ» и жмем на кнопку «OK».



6. Открывается окно аргументов функции КОРЕНЬ. Единственной задачей данного оператора является вычисление квадратного корня из заданного числа. Его синтаксис предельно простой:

   =КОРЕНЬ(число)

   

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

   Устанавливаем курсор в поле «Число» и кликаем по знакомому нам треугольнику, который вызывает список последних использованных функций. Ищем в нем наименование «СЧЁТ». Если находим, то кликаем по нему. В обратном случае, опять же, переходим по наименованию «Другие функции…».



7. В раскрывшемся окне Мастера функций производим перемещение в группу «Статистические». Там выделяем наименование «СЧЁТ» и выполняем клик по кнопке «OK».



8. Запускается окно аргументов функции СЧЁТ. Указанный оператор предназначен для вычисления количества ячеек, которые заполнены числовыми значениями. В нашем случае он будет подсчитывать количество элементов выборки и сообщать результат «материнскому» оператору КОРЕНЬ. Синтаксис функции следующий:

   =СЧЁТ(значение1;значение2;…)

   В качестве аргументов «Значение», которых может насчитываться до 255 штук, выступают ссылки на диапазоны ячеек. Ставим курсор в поле «Значение1», зажимаем левую кнопку мыши и выделяем весь диапазон выборки. После того, как его координаты отобразились в поле, жмем на кнопку «OK».



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

   =СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13))

   Результат вычисления ошибки средней арифметической составил 0,505793. Запомним это число и сравним с тем, которое получим при решении поставленной задачи следующим способом.

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

=СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13)-1)

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

Способ 2: применение инструмента «Описательная статистика»

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

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

1. После того, как открыт документ с выборкой, переходим во вкладку «Файл».



2. Далее, воспользовавшись левым вертикальным меню, перемещаемся через его пункт в раздел «Параметры».



3. Запускается окно параметров Эксель. В левой части данного окна размещено меню, через которое перемещаемся в подраздел «Надстройки».



4. В самой нижней части появившегося окна расположено поле «Управление». Выставляем в нем параметр «Надстройки Excel» и жмем на кнопку «Перейти…» справа от него.



5. Запускается окно надстроек с перечнем доступных скриптов. Отмечаем галочкой наименование «Пакет анализа» и щелкаем по кнопке «OK» в правой части окошка.



6. После выполнения последнего действия на ленте появится новая группа инструментов, которая имеет наименование «Анализ». Чтобы перейти к ней, щелкаем по названию вкладки «Данные».



7. После перехода жмем на кнопку «Анализ данных» в блоке инструментов «Анализ», который расположен в самом конце ленты.



8. Запускается окошко выбора инструмента анализа. Выделяем наименование «Описательная статистика» и жмем на кнопку «OK» справа.



9. Запускается окно настроек инструмента комплексного статистического анализа «Описательная статистика».

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

   В блоке «Группирование» оставляем настройки по умолчанию. То есть, переключатель должен стоять около пункта «По столбцам». Если это не так, то его следует переставить.

   Галочку «Метки в первой строке» можно не устанавливать. Для решения нашего вопроса это не важно.

   Далее переходим к блоку настроек «Параметры вывода». Здесь следует указать, куда именно будет выводиться результат расчета инструмента «Описательная статистика»:

   • На новый лист;
   • В новую книгу (другой файл);
   • В указанный диапазон текущего листа.

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

   Далее следует блок настроек определяющий, какие именно данные нужно вводить:

   • Итоговая статистика;
   • К-ый наибольший;
   • К-ый наименьший;
   • Уровень надежности.

   Для определения стандартной ошибки обязательно нужно установить галочку около параметра «Итоговая статистика». Напротив остальных пунктов выставляем галочки на свое усмотрение. На решение нашей основной задачи это никак не повлияет.

   После того, как все настройки в окне «Описательная статистика» установлены, щелкаем по кнопке «OK» в его правой части.



10. После этого инструмент «Описательная статистика» выводит результаты обработки выборки на текущий лист. Как видим, это довольно много разноплановых статистических показателей, но среди них есть и нужный нам – «Стандартная ошибка». Он равен числу 0,505793. Это в точности тот же результат, который мы достигли путем применения сложной формулы при описании предыдущего способа.

Урок: Описательная статистика в Экселе

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

Во многих работах вы часто можете видеть выражения, такие как Среднее ± SD (SE), или использовать SD или SE для представления панели ошибок на графике. Большинство из них используют SD, но многие используют SE. Новички могут легко спутать SD (стандартное отклонение) и SE (стандартная ошибка).

SD

Мы все очень четко понимаем SD, который выражает степень разброса данных. На практике многие данные имеют распределение вероятностей, которое приблизительно нормальное. С SD мы можем приблизительно оценить диапазон данных, например, классический «68-95-99.7 правило«то есть около 68% значений распределены в пределах 1 стандартного отклонения от среднего значения, около 95% значений распределены в пределах 2 стандартных отклонений от среднего значения и около 99,7% значений распределены в пределах среднего расстояния Значение имеет диапазон в пределах 3 стандартных отклонений, как показано ниже:

SE

Что такое SE? Вообще говоря, трудно получить общие данные в природе. Мы можем использовать только выборки (будь то различные эксперименты или выборки из социального опроса) для аппроксимации населения. Здесь возникает проблема. Оценка не точна ( Средняя стоимость)?
Мы можем сделать это теоретически. Поскольку совокупность не может быть получена, мы можем собрать как можно больше (бесконечных) выборок размера n из данных совокупности со стандартным отклонением σ, каждая выборка имеет среднее значение и среднее значение всех выборок. Стандартное отклонение может быть использовано68-95-99.7 правило«Оценка не допускается (Это так называемый доверительный интервал), стандартное отклонение среднего значения выборки может быть доказано следующим образом:

Но поскольку σ обычно неизвестна, стандартное отклонение (S) образцов, полученных в исследовании, можно использовать для оценки:

Это источник SE, а именноВыборочное среднееSD, мы используем демонстрацию программирования MATLAB.

MATLAB демо

В качестве примера возьмем подбрасывание монеты, подбрасывание 100 раз, подсчет количества голов (1), подсчет в общей сложности 1000 раз в качестве образца, а затем мы берем 1000 образцов (программа не боится усталости).
Сначала вставьте результат и поместите код позади. Результаты таковы, мы можем видетьSD среднего значения выборки в основном равно SE образца!

Полный код выглядит следующим образом:

% 1000 образцов
sample_mean = []

for m=1:1000
    
         % Бросьте монету 100 раз, посчитайте количество голов и посчитайте 1000 раз как образец
    sample = []
    for i = 1:1000
        box = randi([0,1],100,1);
        sample = [sample length(box(box==1))];    
    end
    sample_mean = [sample_mean mean(sample)];
end

 Стандартное отклонение и стандартная ошибка% выборки
SD_sample = std(sample)
SE_sample = SD_sample/sqrt(1000)
 Стандартное отклонение среднего значения образца
SD_sample_mean = std(sample_mean)

В заключение

1. Стандартное отклонение (SD) может лучше отражать степень дисперсии.

Бумага нуждается в информации Среднее ± SD, которая удобна для читателей, чтобы судить о дискретности данных, например, как правило, мы отклоняемся от среднего значения 2 или 3 SD как выброс (то есть, выброс).

2. Стандартные ошибки больше подходят для оценки точности или точности.

В документе также может быть предоставлена ​​информация Среднее ± SE по мере необходимости, что удобно для читателей, чтобы оценить неопределенность данных, например, 95% доверительный интервал — Среднее ± 2 * SE.

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

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

На рисунке отображен график Excel с планками погрешностей, которые указывают на диапазон ошибок для каждой точки. В данном случае погрешность основана на процентах – плюс/минус 10 процентов. Планка для первой точки ряда данных (значение 100) находится в пределах от 90 до 110.

Добавление планки погрешности к ряду данных

Чтобы добавить планку погрешности, выделите ряд данных на диаграмме, перейдите по вкладке Работа с диаграммами –> Конструктор в группу Макеты диаграмм, щелкните по кнопке Добавить элемент диаграммы -> Предел погрешностей –> Дополнительные параметры предела погрешностей. К ряду данных будут добавлены планки погрешностей с фиксированным значением (по умолчанию равно 1), слева экрана появится диалоговое окно Формат предела погрешностей.

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

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

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

Относительное значение: Планка погрешности будет смещена от точки ряда данных на заданных процент от значения точки. К примеру, если вы задали относительное значение равным 5%, а точка ряда данных равна 100, предел погрешности будет находиться в диапазоне от 95 до 105. Т.е. в зависимости от значения точки ряда данных, предел погрешности будет различаться.

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

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

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

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

Нестандартное использование планок погрешностей в Excel

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

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

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

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

Скачать файл с примерами использования планок погрешностей.

2.1. Стандартное отклонение среднего выборочного значения (ошибка среднего) и доверительный интервал

Результаты измерений обычно показывают с так называемой «средней статистической ошибкой средней величины» и для нашего случая (см. табл. 1.1) это будет запись: «высота сеянцев в опыте составила 5,0 ± 0,28 см». Словосочетание «средняя статистическая ошибка» обычно сокращают до названия «ошибка среднего» или просто «ошибка», обозначают буквой m и определяют по очень простой формуле. Для итогов упомянутой таблицы, где расчеты по 25 высотам дали значение δ = 1,42 см, эта ошибка составит:

(2.1)

Если объем выборки взять 100 шт., то ошибка снизится в 2 раза: а если увеличить до 10000 шт., то в 10 раз, до 0,014 см.

Рассмотрим эту «среднюю статистическую ошибку» (далее просто ошибка) подробно, так как именно в ней скрыто понимание того, что называют статистическим мышлением. Интуитивно мы понимаем, что малая выборка дает большую ошибку, т.е. неточное определение среднего значения. Последний термин настолько привычен, что мы даже не задумываемся о том, что его правильное и полное название «среднее выборочное значение», т.е. среднее, определяемое в некоторой выборке. И выборки могут быть очень разные по численности. Начнем с самых малых. Например, что произойдет с ошибкой, если объем выборки сократить до 2 измерений? Такие выборки бывают, например, в почвенных исследованиях, когда каждое измерение достается дорогой ценой. Для этого вернемся к рис. 1.1. На нем стандартное отклонение ±δ, которое отражает разброс значений вокруг среднего в левую и правую сторону в виде холма, наблюдается при объеме выборки 1 шт. В этом случае ошибка среднего выборочного значения будет равна стандартному отклонению: m = δ = 1,42. С увеличением N ошибка уменьшается:

при объеме выборки N = 2 ошибка будет

при объеме выборки N = 4 ошибка будет

при объеме выборки N = 16 ошибка будет

Важно понять, что ряд распределения частот этих выборочных средних будет постепенно как бы съеживаться и приближаться к центру, где находится так называемое «генеральное» среднее. Поясним, что в математике генеральное среднее значение называется математическим ожиданием и его обозначают буквой «М». Например, это может быть средняя высота, рассчитанная по всем измеренным в теплице сеянцам, или среднее число семян в 1 шишке у дерева после подсчета семян во всех собранных с дерева шишках (50, 100, 500 и т.д., т.е. весьма небольшая генеральная совокупность). Распределение частот значений выборочных средних, которых может быть множество, будет иметь форму такого же холма, как и распределение единичных значений на рис. 1.1. При этом, если выборка будет из 1 шт., то холм будет в точности таким же, но при выборках из 2 шт. его форма съежится в  = в 1,41 раза; при выборках из 4 шт. –
в  = в 2 раза; при выборках из 9 шт. – в  = в 3 раза и т.д.

Для этих сокращающихся рядов распределения выборочных средних можно рассчитать свое, особое стандартное отклонение. Вероятно, чтобы не путать его со СТАНДОТКЛ, его стали называть по-другому, т.е. «средней статистической ошибкой средней величины». Чем больше по объему выборки, тем короче ряд распределения средних значений этих выборок с его «хвостами» в левую и правую сторону, и тем меньше величина этого особого стандартного отклонения. Закон распределения частот выборочных средних точно такой же, и имеет те же свойства: в пределах ±2m находится 95 % всех значений выборочных средних, в пределах ±3m – 99,5 %, а в пределах ±4m находится 100 % всех значений xср. Форма этого распределения меняется от пологой при малых выборках до очень крутой, вплоть до «схлопывания» в центре при выборках большого объема, когда ошибка среднего стремится к нулю.

Здесь следует пояснить, что, на наш взгляд, словосочетание «средняя статистическая ошибка средней величины», сокращаемое до «ошибки среднего значения» или просто до «ошибки», вводит нас в некоторое заблуждение, так как мы привыкли со школы, что ошибки надобно исправлять. Более правильным, вместо слов «ошибка среднего значения», будет использование слов «стандартное отклонение выборочных средних значений от генерального среднего». Не случайно математики выбрали для обозначения величины этого отклонения букву «m», а для обозначения генерального среднего (математического ожидания) – букву «М». Слова для объяснения этих сложных явлений могут быть разными, но и у математиков, и у биологов есть единодушие в понимании статистического смысла, лежащего за этими буквенными символами. Вообще, лучше было бы ввести некий иной термин вместо слов «ошибка» или «отклонение», так как они изначально имеют в нашем сознании иной смысл; на наш взгляд, более всего подходит слово «скачок» (чем сильнее отскакивает выборочное среднее от генерального среднего, тем реже оно встречается). Но так уж получилось, что не нашлось нейтрального (иностранного) слова, и слово «ошибка» традиционно используют, и мы также будем его использовать; важно понимать его иной, чем в обыденном употреблении, математический и статистический смысл.

Для самого точного определения средней высоты сеянцев нужно измерять все растения в питомнике, и тогда мы получим «генеральное среднее значение». Но так не делают, а измеряют несколько сотен растений в разных местах и этого бывает достаточно для определения среднего выборочного значения с приемлемой точностью. В нашем примере при 100 растениях ошибка его определения составит а ее отнесение к средней высоте сеянцев 5,0 см, выражаемое в %, дает нам так называемую точность опыта: 0,14/5,0×100 = 2,8 %. В биологии точность опыта ±2–3 % считается высокой, ±5 % – достаточной, а ±6–7 % – пониженной, но это весьма упрощенное представление о планировании эксперимента.

Вообще, точность опыта не самоцель; гораздо важнее сократить численность (объем) выборки до минимума. Представим себе, что средняя высота сеянцев xср = 5,0 см, а ее ±δ = 1,42 см, рассмотренные выше, получены при измерении 1000 растений потомства сосны, например, из Кунгура. Поделив ±δ на корень из 1000 получаем ошибку опыта m = ±0,045 см. Далее получаем точность опыта

Р = m/xср×100 = 0,045/5,0×100 = 0,9 %.

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

Рассмотрим два потомства. Первое – это упомянутые сеянцы происхождением из Кунгура (хср1), второе – сеянцы из Кизела с хср2 = 6,0 см и δ2 = ± 1,0 см (превышение высоты на 20 %). Надо это превышение доказать. При выборках из 100 растений ранее определенная ошибка m1 была равна 0,14 см, вторая ошибка m2 после расчетов по формуле (2.1) составит 0,1 см. По закону нормального распределения 99,5 % всех возможных значений этих средних хср1 и хср2 будут в пределах «плюс-минус три ошибки», что можно показать графически (рис. 2.1) или в виде формул:

хср1 ± 3m1 = 5,0 ± 3×0,14 = 5,0 ± 0,4 см

и

хср2 ± 3m2 = 6,0 ± 3×0,1 = 6,0 ± 0,3 см.

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

хср1 ± 3m1 = 5,0 ± 3×0,20 = 5,0 ± 0,6 см;

хср2 ± 3m2 = 6,0 ± 3×0,14 = 6,0 ± 0,3 см.

Рис. 2.1. Средние значения по выборкам из 100 растений и их тройные ошибки (пределы возможных значений выборочных средних в 99,5 % случаев)

Снова вынесем эти пределы на график (рис. 2.2).

Рис. 2.2. Средние значения при N = 50 растений и их тройные ошибки

Как видим, пределы сблизились и если еще сократить выборки, то они перекроются. Можно ли далее снижать объем выборки?

Можно, но здесь вступает в силу так называемое условие безошибочного прогноза. Мы это условие задали на уровне 99,5 % и для этого взяли ±3m для распределения ошибок. Но можно взять уровень пониже, с пределами ±2δ (уровень 95 %) и даже с пределами ±1,7δ (уровень 90 %).

При выборках из 25 штук сеянцев, получаем две ошибки: Тогда пределы значений для этих двух выборочных средних для уровня прогноза в 95 % будут:

хср1 ± 2m1 = 5,0 ± 2×0,28 = 5,0 ± 0,56 см;

хср2 ± 2m2 = 6,0 ± 2×0,20 = 6,0 ± 0,40 см.

Выносим эти пределы опять на график (рис. 2.3).

Рис. 2.3. Средние значения при N = 25 растений и их двойные ошибки (пределы возможных значений средних в 95 % случаев)

Как видим, просвет все еще есть, и поэтому между возможными значениями средних высот сеянцев в других выборках из происхождений Кунгур и Кизел различия будут опять доказаны. Но уровень доказательства понизился до 95 %, и для 5 % оставшихся случаев нет гарантии, что различия будут иметь место при выборке из 25 растений. Их может и не быть, но эту вероятность в 5 % мы допускаем.