Lumpics lumpics.ru

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

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

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

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

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

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

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

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

Выборка в Microsoft Excel
  1. Выделяем ячейку, в которой будет выводиться итоговое значение стандартной ошибки, и клацаем по иконке «Вставить функцию».
  2. Переход в Мастер функций в Microsoft Excel
  3. Открывается Мастер функций. Производим перемещение в блок «Статистические». В представленном перечне наименований выбираем название «СТАНДОТКЛОН.В».
  4. Переход в окно аргументов функции СТАНДОТКЛОН.В в Microsoft Excel
  5. Запускается окно аргументов вышеуказанного оператора. СТАНДОТКЛОН.В предназначен для оценивания стандартного отклонения при выборке. Данный оператор имеет следующий синтаксис:

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

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

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

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

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

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

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

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

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

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

  16. Окно аргументов функции СЧЁТ в Microsoft Excel
  17. После выполнения последнего действия будет не только рассчитано количество ячеек заполненных числами, но и вычислена ошибка средней арифметической, так как это был последний штрих в работе над данной формулой. Величина стандартной ошибки выведена в ту ячейку, где размещена сложная формула, общий вид которой в нашем случае следующий:

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

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

Результат вычисления стандартной ошибки в сложной формуле в Microsoft Excel

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

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

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

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

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

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

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

  1. После того, как открыт документ с выборкой, переходим во вкладку «Файл».
  2. Переход во вкладку Файл в Microsoft Excel
  3. Далее, воспользовавшись левым вертикальным меню, перемещаемся через его пункт в раздел «Параметры».
  4. Перемещение в раздел Параметры в Microsoft Excel
  5. Запускается окно параметров Эксель. В левой части данного окна размещено меню, через которое перемещаемся в подраздел «Надстройки».
  6. Переход в подраздел надстройки окна параметров в Microsoft Excel
  7. В самой нижней части появившегося окна расположено поле «Управление». Выставляем в нем параметр «Надстройки Excel» и жмем на кнопку «Перейти…» справа от него.
  8. Переход в окно надстроек в Microsoft Excel
  9. Запускается окно надстроек с перечнем доступных скриптов. Отмечаем галочкой наименование «Пакет анализа» и щелкаем по кнопке «OK» в правой части окошка.
  10. Включение пакета анализа в окне надстроек в Microsoft Excel
  11. После выполнения последнего действия на ленте появится новая группа инструментов, которая имеет наименование «Анализ». Чтобы перейти к ней, щелкаем по названию вкладки «Данные».
  12. Переход во вкладку Данные в Microsoft Excel
  13. После перехода жмем на кнопку «Анализ данных» в блоке инструментов «Анализ», который расположен в самом конце ленты.
  14. Переход в Анализ данных в Microsoft Excel
  15. Запускается окошко выбора инструмента анализа. Выделяем наименование «Описательная статистика» и жмем на кнопку «OK» справа.
  16. Переход в описательную статистику в Microsoft Excel
  17. Запускается окно настроек инструмента комплексного статистического анализа «Описательная статистика».

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

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

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

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

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

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

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

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

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

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

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

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

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

14 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Аноним
1 мая 2020 22:33

Изучаю возможности excel. Очень понятое объяснение! Спасибо! Теперь только к Вам!

Ольга
1 июля 2017 22:24

А ничего, что для малой выборки ошибка средней арифметической считается по другой формуле?

Андрей
30 мая 2018 20:01
Ответить на  Максим Тютюшев

Добрый день! А мне нужно вычислить средний возраст с отклонением. Результат получится в виде 27,8+/- 3,2 лет. Где тут будет плюс, а где минус? прошу подсказать! Спасибо! Использую второй метод

Георгий
27 января 2018 13:47

Превосходно. Просто и полезно. Благодарность и почтение автору. И, в особенности, за хладнокровие и вежливость в ответе Ольге.

Андрей
30 мая 2018 20:01

Добрый день! А мне нужно вычислить средний возраст с отклонением. Результат получится в виде 27,8+/- 3,2 лет. Где тут будет плюс, а где минус? прошу подсказать! Спасибо! Использую второй метод.

Екатерина
27 июля 2018 10:04

Большая благодарность автору! Пришлось проводить статистику большого объема числовых данных в excel (обычно пользуюсь Statistika) — второй метод очень помог и ускорил процесс. Согласна с Георгием

Лилит
31 августа 2018 20:21

здравствуйте! в каковых пределах должна быть стандартная ошибка, чтобы исследование считалось достоверным (при использовании данного метода)?

Маргарита
29 мая 2020 17:59

Добрый день! Прошла по ссылкам вплоть до «Пакет анализа», ОК. Потом зашла в «Данные», но столь желанной группы инструментов под названием «Анализ» на ленте так и не обнаружила… Горько плачу…

Виталий Каиров
29 мая 2020 18:33
Ответить на  Маргарита

Маргарита, здравствуйте. Уточните, какая версия Excel и/или Microsoft Office вами используется? По умолчанию (и после выполнения необходимых настроек) эта группа инструментов добавляется в конец вкладки «Данные» и состоит из всего одной кнопки — «Анализ данных», как это и показано в статье.

Сергей
9 декабря 2020 01:31

Добрый день! Большое спасибо за столь доступное объяснение!
А возможно ли использовать второй способ анализа данных для малой выборки? Либо только первый вариант?

Александр
1 июля 2022 08:13

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

Николай
31 января 2023 03:36

Очень помогла вспомнить всё полузабытое, что 25 лет назад было обычным инструментом работы. Спасибо.

Михаил
20 сентября 2018 13:46

Прекрасное описание порядка действий. Спасибо автору.

Задать вопрос