Стандартная ошибка в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

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

Ошибки в Excel: как их найти, понять и исправить

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

Если ячейка полностью заполнена знаками решётки (#), это вовсе не ошибка. В ячейке недостаточно места для отображения результата. Увеличьте размеры ячейки или уменьшите шрифт, чтобы результат мог отобразиться.

Типы ошибок

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

ОшибкаОписание
#ДЕЛ/0!Ошибка возникает при попытке деление на ноль
#ИМЯ?Программа не может распознать введенное имя. Например, вы ошиблись при написании имени функции, или не заключили текстовую строку в кавычки
#Н/ДДанные недоступны. Например, функция поиска не нашла ни одного значения
#ПУСТО!Вы запросили пересечение диапазонов, которые не пересекаются
#ЧИСЛО!Проблема в одном из числовых значений, которые используются в формуле. Например, Вы пытаетесь извлечь квадратный корень из отрицательного числа. В классической математике эта операция лишена смысла
#ССЫЛКА!Формула содержит ссылку, которой нет. Например, вы удалили ячейку, на которую она ссылается
#ЗНАЧ!Формула содержит недопустимые компоненты. Часто такая ошибка возникает при нарушении синтаксиса формул

Когда в ячейке формула с ошибкой, возле нее появляется маркер. Нажав на него, вы можете почитать справку по этой ошибке. А еще можно посмотреть этапы вычисления. Выберите этот пункт, и программа покажет окно, где место ошибки будет подчёркнуто. Это лучший способ определить место, где возникает ошибка.

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

Отслеживание ошибки через этапы вычисления

Циклические ссылки в Excel

Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1 , а в А2 написать =A1 , возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.

Циклическая ссылка в Excel

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

Чтобы отследить такие ошибки, выполните Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки . В выпадающем списке программа выводит адреса ячеек, создающих бесконечный цикл. Остаётся только исправить формулы в этих клетках.

Отслеживание циклических ссылок

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

Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в моей новой статье! Готов ответить на ваши вопросы – пишите в комментариях.

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

Microsoft Excel

трюки • приёмы • решения

Какие существуют ошибки в Excel и как их исправлять

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

  1. #ДЕЛ/О! — данная ошибка практически всегда означает, что формула в ячейке пытается разделить какое-то значение на ноль. Чаще всего это происходит из-за того, что в другой ячейке, ссылающейся на данную, находится нулевое значение или значение отсутствует. Вам необходимо проверить все связанные ячейки на предмет наличия таких значений. Также данная ошибка может возникать, когда вы вводите неправильные значения в некоторые функции, например в ОСТАТ() , когда второй аргумент равен 0. Также ошибка деления на ноль может возникать, если вы оставляете пустые ячейки для ввода данных, а какая-либо формула требует некоторые данные. При этом будет выведена ошибка #ДЕЛ/0!, что может смутить конечного пользователя. Для этих случаев вы можете использовать функцию ЕСЛИ() для проверки, например =ЕСЛИ(А1=0;0;В1/А1) . В этом примере функция вернет 0 вместо ошибки, если в ячейке А1 находится нулевое или пустое значение.
  2. #Н/Д — данная ошибка расшифровывается как недоступно, и это означает, что значение недоступно функции или формуле. Вы можете увидеть такую ошибку, если введете неподходящее значение в функцию. Для исправления проверьте прежде всего входные ячейки на предмет ошибок, особенно если в них тоже появляется данная ошибка.
  3. #ИМЯ? — данная ошибка возникает, когда вы неправильно указываете имя в формуле или ошибочно задаете имя самой формулы. Для исправления проверьте еще раз все имена и названия в формуле.
  4. #ПУСТО! — данная ошибка связана с диапазонами в формуле. Чаще всего она возникает, когда в формуле указывается два непересекающихся диапазона, например =СУММ(С4:С6;А1:С1) .
  5. #ЧИСЛО! — ошибка возникает, когда в формуле присутствуют некорректные числовые значения, выходящие за границы допустимого диапазона.
  6. #ССЫЛКА! — ошибка возникает, когда были удалены ячейки, на которые ссылается данная формула.
  7. #ЗНАЧ! — в данном случае речь идет об использовании неправильного типа аргумента для функции.

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

Обработка ошибок с помощью функции ЕОШИБКА()

Перехватить любые ошибки и обработать их можно с помощью функции ЕОШИБКА() . Данная функция возвращает истину или ложь в зависимости от того, появляется ли ошибка при вычислении ее аргумента. Общая формула для перехвата выглядит так: =ЕСЛИ(ЕОШИБКА(выражение);ошибка; выражение) .

Читайте также:  Построение графика зависимости в Microsoft Excel

Рис. 1. Предупреждающее сообщение о неправильно расставленных скобках

Функция если вернет ошибку (например, сообщение), если при расчете появляется ошибка. Например, рассмотрим следующую формулу: =ЕСЛИ(ЕОШИБКА(А1/А2);””; А1/А2) . При возникновении ошибки (деление на 0) формула возвращает пустую строку. Если же ошибки не возникает, возвращается само выражение А1/А2 .

Существует другая, более удобная функция ЕСЛИОШИБКА() , которая совмещает две предыдущие функции ЕСЛИ() и ЕОШИБКА() : ЕСЛИОШИБКА(значение;значение при ошибке) , где: значение — выражение для расчета, значение при ошибке — возвращаемый результат в случае ошибки. Для нашего примера это будет выглядеть так: =ЕСЛИОШИБКА(А1/А2;””) .

Ошибки в Excel

Если Excel не может правильно оценить формулу или функцию рабочего листа; он отобразит значение ошибки – например, #ИМЯ?, #ЧИСЛО!, #ЗНАЧ!, #Н/Д, #ПУСТО!, #ССЫЛКА! – в ячейке, где находится формула. Разберем типы ошибок в Excel, их возможные причины, и как их устранить.

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

  1. Если в формуле используется имя, которое было удалено или не определено.

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

  1. Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

  1. В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

  1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

  1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

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

Ошибка #ЗНАЧ!

Данная ошибка Excel возникает в том случае, когда в формуле введён аргумент недопустимого значения.

Причины ошибки #ЗНАЧ!:

  1. Формула содержит пробелы, символы или текст, но в ней должно быть число. Например:

Ошибки в Excel – Суммирование числовых и текстовых значений

Устранение ошибки: проверьте правильно ли заданы типы аргументов в формуле.

  1. В аргументе функции введен диапазон, а функция предполагается ввод одного значения.

Ошибки в Excel – В функции ВПР в качестве аргумента используется диапазон, вместо одного значения

Устранение ошибки: укажите в функции правильные аргументы.

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

Устранение ошибки: для завершения ввода формулы используйте комбинацию клавиш Ctrl+Shift+Enter .

Ошибки в Excel – Использование формулы массива

Ошибка #ССЫЛКА

В случае если формула содержит ссылку на ячейку, которая не существует или удалена, то Excel выдает ошибку #ССЫЛКА.

Ошибки в Excel – Ошибка в формуле, из-за удаленного столбца А

Устранение ошибки: измените формулу.

Ошибка #ДЕЛ/0!

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

Ошибки в Excel – Ошибка #ДЕЛ/0!

Устранение ошибки: исправьте формулу.

Ошибка #Н/Д

Ошибка #Н/Д в Excel означает, что в формуле используется недоступное значение.

Причины ошибки #Н/Д:

  1. При использовании функции ВПР, ГПР, ПРОСМОТР, ПОИСКПОЗ используется неверный аргумент искомое_значение:

Ошибки в Excel – Искомого значения нет в просматриваемом массиве

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

  1. Ошибки в использовании функций ВПР или ГПР.

Устранение ошибки: см. раздел посвященный ошибкам функции ВПР

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

Ошибки в Excel – Ошибки в формуле массива

Устранение ошибки: откорректируйте диапазон ссылок формулы с соответствием строк и столбцов или введите формулу массива в недостающие ячейки.

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

Ошибки в Excel – Ошибки в формуле, нет обязательного аргумента

Устранение ошибки: введите все необходимые аргументы функции.

Ошибка #ПУСТО!

Ошибка #ПУСТО! в Excel возникает когда, в формуле используются непересекающиеся диапазоны.

Ошибки в Excel – Использование в формуле СУММ непересекающиеся диапазоны

Устранение ошибки: проверьте правильность написания формулы.

Ошибка ####

Причины возникновения ошибки

  1. Ширины столбца недостаточно, чтобы отобразить содержимое ячейки.

Ошибки в Excel – Увеличение ширины столбца для отображения значения в ячейке

Устранение ошибки: увеличение ширины столбца/столбцов.

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

Ошибки в Excel – Разница дат и часов не должна быть отрицательной

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

Работа с инструментом «Регрессия» в Microsoft Excel

Открыв рабочую книгу и введя в нее исходные данные для построения уравнения регрессии, вызываем надстройку «Регрессия»: ДанныеАнализ данныхРегрессия.

Диалоговое окно «Регрессия». В первое окно «Входной интервал Y» вводим данные объясняемой переменной — у, диапазон должен состоять из одного столбца. Во второе окно «Входной интервал X» вводим данные объясняющих переменных — х. На рис. П.1 представлены у. $С$2:$С$13, х: $В$2:$В$13. Длины интервалов должны быть одинаковы. Если строится уравнение множественной регрессии, то данные объясняющих переменных вводятся в окно «Входной интерват X» соответствующим образом. На рис. П.2 представлены у: $D$2:$D$13, xt—x2: $В2:$С$13. Максималь- ное число независимых объясняющих переменных равно 16.

Рис. П. 1. Задание парной регрессии

Ставим «галочку» в окно «Метки», если в отчете Microsoft Excel требуется знать, к какой из объясняющих переменных относятся результирующие данные.

Если исследователю не требуется константа Ь, то ставим «галочку» в окно «Константа — ноль». Линия регрессии пройдет через начало координат.

Рис. П.2. Задание множественной регрессии

«Уровень надежности». По умолчанию программа строит уравнение регрессии для доверительной вероятности (уровень надежности) 0,95. Если требуется другая величина, ставим «галочку» в окно «Уровень надежности» и в окно, помеченное символом «%», вводим требуемую величину уровня надежности десятичной дробью.

Читайте также:  Восстановление несохраненной книги Excel

«Параметры вывода». Указываем, куда вывести результаты регрессионного анализа: на этом листе, как указано на обоих рисунках, на другой рабочий лист или в новую рабочую книгу.

«Остатки». Выбираем то, что требуется исследователю, и ставим «галочку». Можно одновременно пометить несколько окон. Подробная информация дана в справке но инструменту «Регрессия».

Заполнив диалоговое окно «Регрессия», нажимаем кнопку ОК. Программа выводит отчет «Вывод итогов» в виде трех таблиц (рис. П.З, приведено для двух объясняющих переменных).

Приведем описание таблиц (первых двух — в табл. П1.1 и П1.2 соответственно, третьей – в текстовом виде).

Описание первой таблицы

Наименование в отчете

Коэффициент множественной корреляции, индекс корреляции

Коэффициент детерминации, R 2

Скорректированный К 2

Наименование в отчете

Среднее квадратическое отклонение от модели

Рис. П.З. Результаты работы программы

Описание третьей таблицы

Данные первой строки относятся к коэффициенту уравнения регрессии Ь, данные второй строки — к коэффициенту Ьи третьей — к Ь2 и далее до коэффициента Ьт, но числу объясняющих переменных в уравнении.

Метки, если поставлена галочка в окно «Метки». У-пересечение для коэффициента />, далее но всем объясняющим переменным.

Значения коэффициентов уравнения регрессии Ь, Ьь . Ьт.

Стандартная ошибка коэффициента регрессии 5^, 5Л). Sbm.

Статистическая значимость коэффициента регрессии (^-статистика) для а = 0,05 tw . tbm.

P-значение — это значение уровней значимости, соответствующее вычисленным ^статистикам коэффициентов.

Нижние 95% и Верхние 95% — это нижние и верхние границы 95%-ных доверительных интервалов для коэффициентов уравнения регрессии. Если в окно «Уровень надежности» не вводилось другое значение доверительной вероятности, то последние два столбца дублируют предыдущие два столбца. Если в окно «Уровень надежности» было введено другое значение доверительной вероятности у, то последние два столбца содержат значения соответственно нижней и верхней границы у-процентных доверительных интервалов.

Описание второй таблицы

df — число степеней свободы

SS — сумма квадратов

MS = SS/df — дисперсия на одну степень свободы

Понятие об ошибке выборки.

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

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

(7.1)

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

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

(7.2)

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

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

(7.3)

При бесповторной выборке:

(7.4)

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

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

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

(7.3)

Вероятность того, что параметр совокупности будет принадлежать доверительному интервалу называют уровнем доверительности, который обычно устанавливают равным 95%, хотя часто используют и другие уровни – 90; 99; 99,9%. Чем выше уровень доверительности, тем шире (а значит, и менее полезен) доверительный интервал. Приблизительная обобщенная формулировка утверждения о доверительном интервале имеет следующий вид: мы уверены на 95%, что значение параметра генеральной совокупности находится между значением оценки минус две стандартные ошибки оценки и значением оценки плюс две стандартные ошибки оценки.

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

Формулировка утверждения о двустороннем 95% доверительном интервале для среднего генеральной совокупности имеет следующий вид:

мы уверены, на 95%, что среднее генеральной совокупности m находится между и , где значение t берется из t-таблицы.

(7.4)

Формулировка утверждения о двустороннем 95% доверительном интервале для генеральной доли имеет следующий вид:

мы уверены на 95%, что доля интересующего нас свойства в генеральной совокупности р находится между и , где значение t берется из t-таблицы.

(7.5)

Чтобы получить доверительный уровень, отличный от 95%, следует просто при построении доверительного интервала использовать соответствующее значение. t-таблицу используют для коррекции дополнительной неопределенности, обусловленной тем, что вместо неизвестного точного значения изменчивости генеральной совокупности используют оценку (стандартную ошибку). Когда вы работаете с бесповторной выборкой размера п, число степеней свободы, равное , представляет собой количество независимых элементов информации, использованных при вычислении стандартной ошибки (поскольку при вычислении стандартного отклонения из наблюдаемых значений вычитают среднее). Если известно точное значение стандартной ошибки, используют t-значение для бесконечного числа степеней свободы.

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

(1) данные должны представлять собой случайную выборку из рассматриваемой генеральной совокупности;

(2) измеренные значения должны подчиняться нормальному распределению.

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

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

Читайте также:  Присвоение имени ячейкам Excel

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

мы уверены на 95%, что среднее генеральной совокупности не меньше, чем ; или мы уверены на 95%, что среднее генеральной совокупности не больше, чем .

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

Мы уверены на 95%, что новое наблюдение будет находиться между и .

Формулировка утверждения об интервале предсказания (одностороннем) для значения нового наблюдения будет такой:

Мы уверены на 95%, что новое наблюдение будет не меньше, чем ; или мы уверены на 95%, что новое наблюдение будет не больше, чем .

Выбирая соответствующие t-значение из таблицы, интервалы предсказания для уровней доверительности, отличных от 95%, необходимо помнить, что доверительный интервал дает информацию о среднем генеральной совокупности, в то время как интервал предсказания дает информацию о единственном наблюдении, случайно выбранном из той же генеральной совокупности.

Как рассчитать стандартную ошибку в Excel

1 min

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

Существует два основных варианта – стандартное отклонение для выборки и стандартное отклонение для населения, и оба они включены в Excel. Давайте посмотрим, как рассчитать стандартное отклонение в Excel.

Стандартное отклонение для образца

Стандартное отклонение для образца – одна из двух основных функций стандартного отклонения. MS Excel позволяет рассчитывать для ваших графиков. Он представляет стандартное отклонение от среднего для выбранной выборки данных.

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

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

Стандартное отклонение для населения – это еще одна важная функция стандартного отклонения, которую вы можете рассчитать с помощью MS Excel. В отличие от стандартного отклонения для выборки, стандартное отклонение для совокупности показывает среднее отклонение для всех записей в таблице. Он помечен как STDEV.P в MS Excel.

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

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

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

Способ 1

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

В этом случае мы работаем с диаграммой из десяти столбцов. Вот как это сделать:

  1. Создать или открыть таблицу в MS Excel.
  2. Нажмите на ячейку, в которой вы хотите отобразить значение стандартного отклонения.
  3. Затем введите «= STDEV.P (C2: C11)» или «= STDEV.S (C4: C7)». Значения в скобках обозначают диапазон ячеек, для которых вы хотите рассчитать значение стандартного отклонения. В этом примере вы хотите вычислить STDEV.P для ячеек C2-C11 и STDEV.S для ячеек C4-C7.
  4. Нажмите Ввод”.
  5. Если вы хотите округлить результат до двух десятичных знаков, нажмите на вкладку «Главная».
  6. Нажмите стрелку рядом с «General», чтобы открыть выпадающее меню.
  7. Выберите опцию «Номер».

Способ 2

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

  1. Создать или открыть таблицу в MS Excel.
  2. Нажмите на ячейку, где появится результат отклонения.
  3. Далее щелкните заголовок «Формулы» в главном меню.
  4. После этого нажмите кнопку «Вставить функцию». Он расположен на левой стороне.
  5. Нажмите стрелку рядом с «Или выбрать категорию», чтобы открыть выпадающее меню.
  6. Выберите «Статистика».
  7. Просмотрите список ниже и выберите STDEV.P или STDEV.S
  8. Затем в окне «Аргументы функций» введите диапазон, для которого вы хотите рассчитать стандартное отклонение, в текстовое поле рядом с «Number1». Возвращаясь к примеру метода 1, где мы вычисляли стандартное отклонение для ячеек C2-C11, вы должны написать C2: C11.

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

Способ 3

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

  1. Нажмите на «Файл».
  2. Далее нажмите «Опции».
  3. Нажмите на вкладку «Надстройки» в левой части окна.
  4. Нажмите кнопку «Перейти» в нижней части окна.
  5. Установите флажок «Пакет инструментов анализа».
  6. Нажмите «ОК».

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

  1. Создать или открыть таблицу в MS Excel.
  2. Нажмите на вкладку «Данные».
  3. Выберите «Анализ данных».
  4. Выберите «Описательная статистика».
  5. В поле «Input Range» введите диапазон ячеек, которые вы хотите включить.
  6. Выберите между переключателями «Столбцы» и «Строки».
  7. Проверьте «Метки в первой строке», если есть заголовки столбцов
  8. Укажите, где вы хотите, чтобы результат появился.
  9. Установите флажок «Сводная статистика».
  10. Нажмите кнопку «ОК».

Стандартное отклонение для населения вы найдете в итоговой сводке.

Резюме

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

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