Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функции СТОШYX в Microsoft Excel.
Описание
Возвращает стандартную ошибку предсказанных значений y для каждого значения x в регрессии. Стандартная ошибка — это мера ошибки предсказанного значения y для отдельного значения x.
Синтаксис
СТОШYX(известные_значения_y;известные_значения_x)
Аргументы функции СТОШYX описаны ниже.
-
Известные_значения_y Обязательный. Массив или диапазон зависимых точек данных.
-
Известные_значения_x Обязательный. Массив или диапазон независимых точек данных.
Замечания
-
Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
-
Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
-
Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.
-
Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.
-
Если аргументы «известные_значения_y» и «известные_значения_x» содержат различное количество точек данных, то функция СТОШYX возвращает значение ошибки #Н/Д.
-
Если known_y и known_x пустые или имеют менее трех точек данных, steYX возвращает #DIV/0! значение ошибки #ЗНАЧ!.
-
Уравнение для стандартной ошибки предсказанного y имеет следующий вид:
где x и y — выборочные средние значения СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y), а n — размер выборки.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
||
|
Известные значения y |
Известные значения x |
|
|
2 |
6 |
|
|
3 |
5 |
|
|
9 |
11 |
|
|
1 |
7 |
|
|
8 |
5 |
|
|
7 |
4 |
|
|
5 |
4 |
|
|
Формула |
Описание (результат) |
Результат |
|
=СТОШYX(A3:A9;B3:B9) |
Стандартная ошибка предсказанных значений y для каждого значения x в регрессии (3,305719) |
3,305719 |
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Загрузить PDF
Загрузить PDF
В этой статье мы расскажем вам, как в Excel вычислить стандартную ошибку среднего. Для этого стандартное отклонение (σ) нужно разделить на квадратный корень (√) из размера выборки (N).
-
1
Запустите Excel. Нажмите на значок в виде белой буквы «Х» на зеленом фоне.
-
2
Откройте или создайте таблицу Excel. Чтобы открыть готовую таблицу с данными, нажмите «Открыть» на левой панели. Чтобы создать таблицу, нажмите «Создать» и введите данные.
-
3
Вычислите стандартное отклонение. Чтобы сделать это, нужно выполнить несколько действий, но в Excel можно просто ввести следующую формулу: =СТАНДОТКЛОН.В(''диапазон ячеек'').
- Например, если данные находятся в ячейках с A1 по A20, в пустой ячейке введите =СТАНДОТКЛОН.В(A1:A20), чтобы вычислить стандартное отклонение.
-
4
Введите формулу для вычисления стандартной ошибки среднего в пустой ячейке. Формула выглядит так:=СТАНДОТКЛОН.В(''диапазон ячеек'')/КОРЕНЬ(СЧЁТ("диапазон ячеек")).
- Например, если данные находятся в ячейках с A1 по A20, в пустой ячейке введите формулу =СТАНДОТКЛОН.В(A1:A20)/КОРЕНЬ(СЧЁТ(A1:A20)). Так вы вычислите стандартную ошибку среднего.
Реклама
Об этой статье
Эту страницу просматривали 35 090 раз.
Была ли эта статья полезной?
читать 2 мин
Стандартная ошибка среднего — это способ измерить, насколько разбросаны значения в наборе данных. Он рассчитывается как:
Стандартная ошибка = с / √n
куда:
- s : стандартное отклонение выборки
- n : размер выборки
Вы можете рассчитать стандартную ошибку среднего для любого набора данных в Excel, используя следующую формулу:
= СТАНДОТКЛОН (диапазон значений) / КОРЕНЬ ( СЧЁТ (диапазон значений))
В следующем примере показано, как использовать эту формулу.
Пример: Стандартная ошибка в Excel
Предположим, у нас есть следующий набор данных:
На следующем снимке экрана показано, как рассчитать стандартную ошибку среднего значения для этого набора данных:
Стандартная ошибка оказывается равной 2,0014 .
Обратите внимание, что функция =СТАНДОТКЛОН() вычисляет выборочное среднее, что эквивалентно функции =СТАНДОТКЛОН.С() в Excel.
Таким образом, мы могли бы использовать следующую формулу для получения тех же результатов:
И снова стандартная ошибка оказывается равной 2,0014 .
Как интерпретировать стандартную ошибку среднего
Стандартная ошибка среднего — это просто мера того, насколько разбросаны значения вокруг среднего. При интерпретации стандартной ошибки среднего следует помнить о двух вещах:
1. Чем больше стандартная ошибка среднего, тем более разбросаны значения вокруг среднего в наборе данных.
Чтобы проиллюстрировать это, рассмотрим, изменим ли мы последнее значение в предыдущем наборе данных на гораздо большее число:
Обратите внимание на скачок стандартной ошибки с 2,0014 до 6,9783.Это указывает на то, что значения в этом наборе данных более разбросаны вокруг среднего значения по сравнению с предыдущим набором данных.
2. По мере увеличения размера выборки стандартная ошибка среднего имеет тенденцию к уменьшению.
Чтобы проиллюстрировать это, рассмотрим стандартную ошибку среднего для следующих двух наборов данных:
Второй набор данных — это просто первый набор данных, повторенный дважды. Таким образом, два набора данных имеют одинаковое среднее значение, но второй набор данных имеет больший размер выборки, поэтому стандартная ошибка меньше.
Среднее арифметическое, как известно, используется для получения обобщающей характеристики некоторого набора данных. Если данные более-менее однородны и в них нет аномальных наблюдений (выбросов), то среднее хорошо обобщает данные, сведя к минимуму влияние случайных факторов (они взаимопогашаются при сложении).
Когда анализируемые данные представляют собой выборку (которая состоит из случайных значений), то среднее арифметическое часто (но не всегда) выступает в роли приближенной оценки математического ожидания. Почему приближенной? Потому что среднее арифметическое – это величина, которая зависит от набора случайных чисел, и, следовательно, сама является случайной величиной. При повторных экспериментах (даже в одних и тех же условиях) средние будут отличаться друг от друга.
Для того, чтобы на основе статистического анализа данных делать корректные выводы, необходимо оценить возможный разброс полученного результата. Для этого рассчитываются различные показатели вариации. Но то исходные данные. И как мы только что установили, среднее арифметическое также обладает разбросом, который необходимо оценить и учитывать в дальнейшем (в выводах, в выборе метода анализа и т.д.).
Интуитивно понятно, что разброс средней должен быть как-то связан с разбросом исходных данных. Основной характеристикой разброса средней выступает та же дисперсия.
Дисперсия выборочных данных – это средний квадрат отклонения от средней, и рассчитать ее по исходным данным не составляет труда, например, в Excel предусмотрены специальные функции. Однако, как же рассчитать дисперсию средней, если в распоряжении есть только одна выборка и одно среднее арифметическое?
Расчет дисперсии и стандартной ошибки средней арифметической
Чтобы получить дисперсию средней арифметической нет необходимости проводить множество экспериментов, достаточно иметь только одну выборку. Это легко доказать. Для начала вспомним, что средняя арифметическая (простая) рассчитывается по формуле:
где xi – значения переменной,
n – количество значений.
Теперь учтем два свойства дисперсии, согласно которым, 1) — постоянный множитель можно вынести за знак дисперсии, возведя его в квадрат и 2) — дисперсия суммы независимых случайных величин равняется сумме соответствующих дисперсий. Предполагается, что каждое случайное значение xi обладает одинаковым разбросом, поэтому несложно вывести формулу дисперсии средней арифметической:
Используя более привычные обозначения, формулу записывают как:
где σ2 – это дисперсия, случайной величины, причем генеральная.
На практике же, генеральная дисперсия известна далеко не всегда, точнее совсем редко, поэтому в качестве оной используют выборочную дисперсию:
Стандартное отклонение средней арифметической называется стандартной ошибкой средней и рассчитывается, как квадратный корень из дисперсии.
Формула стандартной ошибки средней при использовании генеральной дисперсии
Формула стандартной ошибки средней при использовании выборочной дисперсии
Последняя формула на практике используется чаще всего, т.к. генеральная дисперсия обычно не известна. Чтобы не вводить новые обозначения, стандартную ошибку средней обычно записывают в виде соотношения стандартного отклонения выборки и корня объема выборки.
Назначение и свойство стандартной ошибки средней арифметической
Стандартная ошибка средней много, где используется. И очень полезно понимать ее свойства. Посмотрим еще раз на формулу стандартной ошибки средней:
Числитель – это стандартное отклонение выборки и здесь все понятно. Чем больше разброс данных, тем больше стандартная ошибка средней – прямо пропорциональная зависимость.
Посмотрим на знаменатель. Здесь находится квадратный корень из объема выборки. Соответственно, чем больше объем выборки, тем меньше стандартная ошибка средней. Для наглядности изобразим на одной диаграмме график нормально распределенной переменной со средней равной 10, сигмой – 3, и второй график – распределение средней арифметической этой же переменной, полученной по 16-ти наблюдениям (которое также будет нормальным).
Судя по формуле, разброс стандартной ошибки средней должен быть в 4 раза (корень из 16) меньше, чем разброс исходных данных, что и видно на рисунке выше. Чем больше наблюдений, тем меньше разброс средней.
Казалось бы, что для получения наиболее точной средней достаточно использовать максимально большую выборку и тогда стандартная ошибка средней будет стремиться к нулю, а сама средняя, соответственно, к математическому ожиданию. Однако квадратный корень объема выборки в знаменателе говорит о том, что связь между точностью выборочной средней и размером выборки не является линейной. Например, увеличение выборки с 20-ти до 50-ти наблюдений, то есть на 30 значений или в 2,5 раза, уменьшает стандартную ошибку средней только на 36%, а со 100-а до 130-ти наблюдений (на те же 30 значений), снижает разброс данных лишь на 12%.
Лучше всего изобразить эту мысль в виде графика зависимости стандартной ошибки средней от размера выборки. Пусть стандартное отклонение равно 10 (на форму графика это не влияет).
Видно, что примерно после 50-ти значений, уменьшение стандартной ошибки средней резко замедляется, после 100-а – наклон постепенно становится почти нулевым.
Таким образом, при достижении некоторого размера выборки ее дальнейшее увеличение уже почти не сказывается на точности средней. Этот факт имеет далеко идущие последствия. Например, при проведении выборочного обследования населения (опроса) чрезмерное увеличение выборки ведет к неоправданным затратам, т.к. точность почти не меняется. Именно поэтому количество опрошенных редко превышает 1,5 тысячи человек. Точность при таком размере выборки часто является достаточной, а дальнейшее увеличение выборки – нецелесообразным.
Подведем итог. Расчет дисперсии и стандартной ошибки средней имеет довольно простую формулу и обладает полезным свойством, связанным с тем, что относительно хорошая точность средней достигается уже при 100 наблюдениях (в этом случае стандартная ошибка средней становится в 10 раз меньше, чем стандартное отклонение выборки). Больше, конечно, лучше, но бесконечно увеличивать объем выборки не имеет практического смысла. Хотя, все зависит от поставленных задач и цены ошибки. В некоторых опросах участие принимают десятки тысяч людей.
Дисперсия и стандартная ошибка средней имеют большое практическое значение. Они используются в проверке гипотез и расчете доверительных интервалов.
Поделиться в социальных сетях:
При
большом объёме выборки её анализ требует
большого объёма вычислений, поэтому
естественно проводить его за компьютером.
Имеется большое число программных
средств, как специально предназначенных
для статистического анализа, так и
содержащихся в у
ниверсальных
программах в качестве подпрограмм и
опций. Достаточно возможностей для
этого предоставляет, в частности,
доступная всем программаExcel.
Команды для
проведения статистического анализа
можно найти в меню «Сервис
Анализ
данных» и в меню «Функции
Статистические»
и «Функции
Работа
с базами данных».
Т
аблица
1.
Рассмотрим
работу в этой среде на следующем примере.
В лабораторном практикуме группа из 25
студентов определяла концентрацию
некоторого вещества в выданном им
растворе. Каждый из них сделал по 4
параллельных определения. Их результаты,
округлённые до 0,5 г/л я занёс в таблицу
Excel
(табл.1).У
меня образовался массив, содержащий 4
столбца B,C,D
и E,
и 25 строк с №2 до №26. Далее я хочу найти
минимальное число из этого массива –
нижнюю границу выборки. Я щёлкаю по
пустой ячейке, в которой хочу найти
ответ, затем навожу курсор на «
»,
и нажимаю левую клавишу мыши. Открывается
окно выбора функций – «Мастер функций».
В разделе «Категории» я открываю
«статистические» и нахожу тамМИН.
После
щелчка мышью по этому названию и “OK”
открывается диалоговое окно «Аргументы
функции» с пометкой МИН.
В окошко,
помеченное «Число 1», можно ввести сами
числа, что, конечно, неудобно. Вместо
этого я щёлкаю мышью по крайней левой
верхней клетке массива, затем нажимаю
“Shift”
и одновременно щёлкаю по крайней правой
нижней клетке. При этом в вышеуказанном
окошке появляются границы массива в
виде “$B$2:$E$26.
Ответ «300,5» появляется сразу, а при
щелчке «OK»
— в заготовленной клетке. Точно так же
я могу применить эту функцию к любой
прямоугольной части этого массива,
вызвав саму функцию МИН
(теперь её
позывной можно найти в категории
«Последние») и отметив, как описано
выше, щелчками мыши, клетки в начале и
конце выбранной части массива. Впрочем,
выделять массив можно и движением мыши,
если сначала навести курсор на начало
массива, нажать левую клавишу мыши, и,
не снимая нажатия, провести курсор до
конечной точки массива.
Конечно,
такое подробное описание вызовет улыбку
у продвинутого пользователя, но, возможно,
среди читателей есть и такие, которые
впервые в жизни откроют документ Excel.
Для
краткого описания действий при
использовании других функций будем
использовать следующие обозначения:
ЩАа
– щелчок по клетке начала диапазона,
ЪЩЯя
— щелчок по клетке конца диапазона с
одновременным нажатием Shift,
ЩСс
– щелчок по свободной ячейке, в которой
будет указан результат.
,
серв., дигр., адат, стат. – щелчки по
значкам
,
«сервис», «диаграмма», «анализ данных»,
«статистические» соответственно.
Напомним, что если какая-либо функция
используется повторно, то быстрее найти
её не через «статистические», а через
«последние».
Итак,
считаем, что в таблицу Excel
внесены
данные выборки в виде строки, столбца,
или двумерного массива. Цели и действия
представлены в таблице 2.
Таблица
2.
|
Что |
Действия |
|
Объём |
ЩСс, |
|
Нижнюю |
ЩСс, |
|
Верхнюю |
ЩСс, |
|
Среднее |
ЩСс, |
|
Моду |
ЩСс, |
|
Медиану |
ЩСс, |
|
Нижний |
ЩСс, |
|
Верхний |
ЩСс, |
|
Выборочную |
ЩСс, |
|
|
ЩСс, |
|
Доверительный |
ЩСс, |
|
Асимметрию |
ЩСс, |
|
Эксцесс |
ЩСс, |
,
,
,
,
,
,
,
,
,
,
,
,
,
В
таблице 1 в столбцах F—I
вы видите результаты выполнения
соответствующих функций для каждой из
25 строк массива. При этом нет необходимости
вводить формулу функции в каждую строку
отдельно – достаточно ввести её в первую
строку, а в окошко аргументов ввести
координаты начала и конца этой строки:
После
нажатия «
»
в ячейке, в которую введена данная
формула, появляется соответствующий
результат:
Если
теперь навести курсор на чёрный квадратик
в нижнем правом углу этой ячейки, и при
нажатой левой клавише мыши провести
его вдоль столбца до последней строки
массива данных, то после отпускания
клавиши весь столбец заполнится
результатами, полученными для всех
остальных строк по той же формуле.
Для
группировки данных и получения
интервального ряда можно использовать
функцию ЧАСТОТА.
Для её
применения
сначала
формируем столбец интервалов. Для нашего
примера, в котором объём выборки
,
,
удобно выборку разбить на 7 равных
интервалов шириной 3 . При этом в ячейки
для массива интервалов вводим только
значения верхних границ интервалов.
Так, в ячейку
я внёс число 303 для интервала
,
в
— число 306 для интервала
,
…, в
— 321 для интервала
.
Затем я выделяю свободную ячейку
,
и щёлкаю по
.
Появляется мастер функций, в котором я
нахожуЧАСТОТА
и раскрываю
шаблон для ввода аргументов. После
ввода вышеописанным способом границ
массива данных щёлкаем по окну массив
интервалов и
выделяем для ввода ячейки
.
Обратите внимание, что выделена одна
дополнительная ячейка, как этого требует
синтаксис функции.
После
нажатия
в ячейке
появляется число вариант со значением
,
.
Для вывода остальных значений
надо выделить ячейки
,
после чего нажать клавишу
,
а затем
.
В результате в столбце
и появятся все компоненты вектора
частот.
|
ЧАСТОТА |
|
|
303 |
4 |
|
306 |
12 |
|
309 |
23 |
|
312 |
31 |
|
315 |
21 |
|
318 |
7 |
|
321 |
2 |
Рассмотрим
теперь, какие возможности для первичной
обработки выборки имеются в меню
«сервис
анализ данных». Раскроем диалоговое
окно«описательная
статистика».
Первая
строка «Входной
интервал»
нам уже знакома: данные в неё можно
внести действиями ЩАа, ЪЩЯя, или
движениями мыши с нажатой правой кнопкой,
или непосредственно введя в окошко
номера левой верхней и правой нижней
ячеек массива, разделённые двоеточием
Аа:Яя. Далее предлагается выбрать
группировку – «По
строкам»
или «По
столбцам».
Дело в том, что эта «описательная
статистика»
может обрабатывать одновременно большое
количество выборок, каждая из которых
может быть введена либо в виде строки,
либо в виде столбца. Поэтому, если мы
выделим массив, содержащий 25 строк и 4
столбца, то программа не
будет рассматривать его как одну выборку,
содержащую 100 вариант. Если мы пометим
окошко «По
столбцам», то
программа будет обрабатывать массив
как 4 выборки по 25 вариант в каждой.
Соответственно, при флажке «По
строкам» мы
получим обработку 25 выборок по 4 варианта.
Далее следует окошко
«Метки в первой строке/столбце». Если
его не помечать, то результаты обработки
каждой из выборок будут помечены
надписями «Строка (Столбец) 1», «Строка
(Столбец) 2», «Строка (Столбец)3»… .Если
же мы хотим , чтобы результаты были
обозначены иначе, (например, фамилиями
студентов), то мы при вводе указаний
массива данных в строку Входной
диапазон должны
захватить и стоящий перед ним столбец
(строку) меток (фамилий или номеров
опытов в данном примере). На этом ввод
данных завершается.
Куда
выводить результаты:
|
Строка1 |
|
|
Среднее |
309,875 |
|
Стандартная |
1,599153422 |
|
Медиана |
309,5 |
|
Мода |
#Н/Д |
|
Стандартное |
3,198306844 |
|
Дисперсия |
10,22916667 |
|
Эксцесс |
-0,02453947 |
|
Асимметричность |
0,598903954 |
|
Интервал |
7,5 |
|
Минимум |
306,5 |
|
Максимум |
314 |
|
Сумма |
1239,5 |
|
Счет |
4 |
Параметры
вывода. Обычно при открытии
диалогового окна активизировано окошко
Новый рабочий лист.
Это означает, что результаты будут
выведены на новом листе, номер которого
при желании можно задать, так же как и
номер новой книги в окошке Новая
рабочая книга. Если же
надо поместить результаты на исходном
листе, то надо активизировать окошко
Выходной интервал,
после чего щёлкнуть по свободной ячейке,
которая будет левой верхней ячейкой
выходного массива.
Что
выводить.
При
установке флажка
«Итоговая статистика» для
каждой выборки будет выведена таблица
такого вида:
В
этой таблице под стандартным отклонением
понимается величина выборочного
стандарта
,
под стандартной ошибкой – выборочный
стандарт среднего
,
интервал – разность между максимальным
и минимальным значениями выборки, сумма
– сумма всех значений выборки, счёт –
объём выборки. Остальные термины
пояснения не требуют.
Если
активизировать окошко «Уровень
надёжности»,
то выводится строка со значением
полуширины симметричного доверительного
интервала, соответствующим указанной
в этом окошке доверительной вероятности
и равным произведению
на соответствующий квантиль распределения
Стьюдента:
|
Уровень |
5,089219898 |
Активизация
окошек К-ый
наименьший и
К-ый наибольший позволяет
выводить к-ое в порядке возрастания и
(или) к-ое в порядке убывания значения
в выборке, соответствующие указанным
номерам. Значениям к=1 соответствуют
минимальное и максимальное значения
вариант.
Обратимся
теперь к графическому изображению
данных. Для этого в меню Анализ
данных есть
функция Гистограмма,
в диалоговом окне которой в окошко
Входной
интервал вводим
одним из описанных ранее способов номера
ячеек начала и конца массива данных.
Затем в окошко Интервал
карманов вводим
таким же образом номера массива, в
котором указаны верхние границы
интервалов, на которые мы решили разбить
выборку (см. выше описание функции
Частота).
Флажок Метки
надо устанавливать только в том случае,
если в массив данных включён и столбец
меток. Как и в вышеописанных функциях
ставим флажок Новый
лист или
Новая книга (с
указанием номера или без), или Выходной
интервал. В
последнем случае в активизированное
окошко вводим номер левой верхней ячейки
диапазона вывода результата. Игнорируя
надпись Парето,
помечаем
Интегральный процент и
Вывод графика.
выводит нам
во-первых,
таблицу, два первых столбца, как и после
исполнения функции Частота
представляют
интервальный вариационный ряд, а третий
столбец – аналог интегральной функции
распределения, показывает долю вариант
в выборки, имеющих значение меньшее или
равное указанного в первом столбце.
Кроме этого, появляется и графическое
изображение – гистограмма и график
интегрального процента. Можно редактировать
это изображение, но здесь мы не будем
рассматривать все многочисленные
возможности этого.
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #