Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Ошибка средней в эксель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Ошибка ecp 4098

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

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

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

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

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

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

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

Как посчитать ошибки в Excel с учетом их кодов

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

Как посчитать ошибку в формуле Excel

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

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

  1. В ячейку C1 введите следующую формулу:
  2. Данная формула должна быть выполнена в массиве, поэтому после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Таким образом получаем текущее количество ошибок в таблице.

Читать еще:  0xc0000005 что за ошибка

Разбор формулы для подсчета количества всех ошибок в ячейках Excel:

С помощью функции ЕОШИБКА проверена каждая ячейка диапазона A2:A9 на наличие ошибочных значений. Результаты функции в памяти программы образуют собой массив логических значений ИСТИНА и ЛОЖЬ. После перемножения каждого логического значения на число 1 в результате получаем массив из чисел 1 и 0. Потом все элементы массива суммируются, а формула возвращает количество ошибок.

Как найти первую ошибку в значении Excel

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

Она также должна быть выполнена в массиве поэтому снова для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter.

Первая ошибка находиться в третьей строке рабочего листа Excel.

Рассмотрим, как работает такая формула:

Наподобие первой формулы с помощью функции ЕОШИБКА в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. Далее функция СТРОКА возвращает текущие номера строк листа в диапазоне A2:A9. Благодаря функции ЕСЛИ в массиве с логическими значениями ИСТИНА заменяется на текущий номер строки. После чего функция МИН выбирает наименьшее число из этого же массива.

Как посчитать ошибки Excel с определенным кодом

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

На этот раз формула не должна выполняться в массиве поэтому после ввода для ее подтверждения достаточно просто нажать клавишу Entеr.

Третья формула возвращает количество ошибок деления на 0 (#ДЕЛ/0!). Но она не мене эффективно работает если во втором аргументе функции СЧЕТЕСЛИ указать другой тип ошибки в ячейках Excel. Например, #ИМЯ?

Как видно на рисунке все работает не менее эффективно.

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

Как показано на очередном рисунке, формула возвращает значение 4 которое соответствует номеру строки где впервые встречается ошибка деления на 0.

Коды и типы ошибок Excel

Функция ТИП.ОШИБКИ проверяет каждую ячейку в диапазоне A1:A9, если она наталкивается на ошибку возвращает соответствующий ей номер (например, код ошибки деления на ноль: для типа #ДЕЛ/0! – это код 2). Ниже приведена целая таблица типов и кодов для обработки ошибок Excel:

ТИПКОД
#ПУСТО!1
#ДЕЛ/0!2
#ЗНАЧ!3
#ССЫЛКА!4
#ИМЯ?5
#ЧИСЛО!6
#Н/Д7
#ОЖИДАНИЕ_ДАННЫХ8

Далее создается в памяти массив значений с номерами кодов ошибок. В первом аугменте функции ПОИСКПОЗ мы указываем код ошибки, которую нужно найти. В третьем аргументе мы указываем код 0 для функции ПОИСКПОЗ, который означает что возвращать нужно первое встречающееся значение 2 при наличии дубликатов в массиве.

Внимание! В четвертой формуле мы ссылались на диапазон ячеек начиная с A1 и до A9. Потому как функция ПОИСКПОЗ возвращает текущею позицию значения относительно таблицы, а не целого листа. Поэтому во втором аргументе функции ПОИСКПОЗ следует указывать диапазон просматриваемых значений так, чтобы номера позиций совпадали с номерами строк листа. Другими словами, если бы мы указали адрес диапазона A2:A9, то формула вернула бы значение 5 – что не является правильным.

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

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

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

Читать еще:  Ошибка sql таблица не найдена

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock
detector