Как сделать стандартное отклонение на графике в excel по данным таблицы

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

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

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

График с пределом погрешностей 10 %

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

Примечание: Office 2013 и более поздних версий применяются следующие процедуры. Ищете действия Office 2010?

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

Щелкните в любом месте диаграммы.

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

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

Параметры планок погрешностей

Выберите предопределенный параметр планок погрешностей, такой как Стандартная погрешность, Относительное отклонение или Стандартное отклонение.

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

Область

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

Формулы для расчета величины погрешности

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

i = номер точки в ряду s;

m = номер ряда для точки y на диаграмме;

n = число точек в каждом ряду;

yis = значение данных ряда s и i-й точки;

ny = суммарное число значений данных во всех рядах.

i = номер точки в ряду s;

m = номер ряда для точки y на диаграмме;

n = число точек в каждом ряду;

yis = значение данных ряда s и i-й точки;

ny = суммарное число значений данных во всех рядах;

M = среднее арифметическое.

Добавление, изменение и удаление ошибки погрешностей на диаграмме в Office 2010

В Excel можно отобразить планки погрешностей, с помощью стандартной погрешности, процентное значение (5%) или стандартное отклонение.

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

i = номер точки в ряду s;

m = номер ряда для точки y на диаграмме;

n = число точек в каждом ряду;

yis = значение данных ряда s и i-й точки;

ny = суммарное число значений данных во всех рядах.

i = номер точки в ряду s;

m = номер ряда для точки y на диаграмме;

n = число точек в каждом ряду;

yis = значение данных ряда s и i-й точки;

ny = суммарное число значений данных во всех рядах;

M = среднее арифметическое.

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

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

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

Щелкните в любом месте диаграммы.

Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

На вкладке Макет в группе Анализ нажмите кнопку Планки погрешностей.

Выполните одно из указанных ниже действий.

Выберите предопределенный параметр планок, такие как Планки погрешностей со стандартными ошибками, Ошибка погрешностей с относительными ошибками или Планки погрешностей со стандартными отклонениями.

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

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

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

Щелкните в любом месте диаграммы.

Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

На вкладке Макет в группе Анализ нажмите кнопку Планки погрешностей и нажмите кнопку Дополнительные параметры панели ошибки.

В разделе Отображение выберите направление панели ошибки и завершить стиль, который вы хотите использовать.

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

Щелкните в любом месте диаграммы.

Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

На вкладке Макет в группе Анализ нажмите кнопку Планки погрешностей и нажмите кнопку Дополнительные параметры панели ошибки.

В разделе Величина погрешности выполните одно или несколько из указанных ниже действий.

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

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

Нажмите кнопку задать значение.

В полях Положительное значение ошибки и Отрицательное значение ошибки укажите диапазон ячеек, который вы хотите использовать в качестве величины погрешностей, или введите значения, которые вы хотите использовать, разделенных запятыми. Например введите 0,4; 0,3; 0,8.

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

Примечание: Диалоговое окно Настраиваемые планки погрешностей может не отображать кнопку Свернуть диалоговое окно в Microsoft Office Word 2007 или Microsoft Office PowerPoint 2007, и можно вводить текст только величины погрешностей, которые вы хотите использовать.

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

Щелкните в любом месте диаграммы.

Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.

Выполните одно из указанных ниже действий.

На вкладке Макет в группе Анализ нажмите кнопку Планки погрешностей и выберите пункт Нет.

Нажмите клавишу DELETE.

Совет: Вы можете удалить планки погрешностей, сразу же после их добавления в диаграмму, нажав клавиши CTRL + Z или, нажав кнопку Отменить на панели Быстрого доступа.

Выполните одно из указанных ниже действий.

Выражение погрешности в виде процентной доли, стандартного отклонения или стандартной ошибки

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

Например, щелкните одну из линий графика. Будут выделены все маркеры данных этого ряда.

На вкладке " Конструктор диаграмм n" нажмите кнопку Добавить элемент диаграммы.

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

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

s — номер ряда;
I — номер точки в ряду s;
m — количество рядов для точки y на диаграмме;
n — количество точек в каждом ряду;
y — значение данных ряда s и I-й точки;
n y — общее число значений данных во всех рядах.

Применение процентной доли значения к каждой точке данных в ряду данных

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

s — номер ряда;
I — номер точки в ряду s;
m — количество рядов для точки y на диаграмме;
n — количество точек в каждом ряду;
y — значение данных ряда s и I-й точки;
n y — общее число значений данных во всех рядах;
M — арифметическое среднее.

Выражение погрешностей в виде пользовательских значений

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

На вкладке Конструктор диаграмм нажмите кнопку Добавить элемент диаграммы и нажмите кнопку Дополнительные параметры планок погрешностей.

В области Формат планок погрешностей на вкладке Параметры панели ошибок в разделе Величина погрешности выберите другое и нажмите кнопку Задать значение.

В разделе Величина погрешности выберите пункт Настраиваемая, а затем — пункт Укажите значение.

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

В полях Положительное значение ошибки и Отрицательное значение ошибки введите нужные значения для каждой точки данных, разделенные точкой с запятой (например, 0,4; 0,3; 0,8), и нажмите кнопку ОК.

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

Добавление полос повышения и понижения

На диаграмме выберите ряд данных, который вы хотите добавить вверх повышения и понижения.

На вкладке Конструктор диаграмм нажмите кнопку Добавить элемент диаграммы, выберите команду Понижения и нажмите кнопку полосы повышения и понижения.

В зависимости от типа диаграммы некоторые параметры могут быть недоступны.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

планки погрешностей Excel лого

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

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

Планки погрешностей Excel

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

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

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

выбор горизонтальной планки погрешности

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

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

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

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

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

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

формат предела погрешностей excel

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

добавление планок погрешностей

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

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

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

планки погрешностей в виде цели

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

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

Вам также могут быть интересны следующие статьи

  • Создание пулевой диаграммы (bullet graph)
  • Создание диаграммы в виде спидометра в Excel
  • Создание простейшего дашборда в Visio — импорт данных из Excel в Visio
  • Что такое Treemap и как его сделать в Excel
  • Бесплатный шаблон дашборда в Excel — потребленная энергетическая ценность продуктов
  • Создание динамической диаграммы в Excel с помощью именованных диапазонов
  • Диаграмма водопад (waterfall chart) в Excel
  • Диаграмма Ганта в Excel
  • Воронка продаж в Excel
  • Создание графика в Excel с отрицательными и положительными значениями

6 комментариев

Добрый день!Подскажите,пож-та,как можно построить диаграмму с использованием планки погрешностей,для данных в прикрепленном файле?На Ваш сайт забрела случайно,нахожу его интересным!

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

Здравствуйте.
Мне требуется вычислить и добавить к графику данные, вычисленные по следующим формулам (на фото, вставлен в excel — иначе не прикрепляется).
Насколько я понимаю, стандартная погрешность в статье совпадает с функцией среднеквадратичной ошибки, которая вычисляется по формуле среднего квадратичного отклонения.
Есть ли стандартные действия для вычисления средней квадратичной ошибки из фото?
Буду благодарна за Ваш ответ.

Подскажите, пожалуйста, как добавить погрешность для каждого значения, если для графика я беру среднее.
Например, мoи числа 5, 6, 7. Среднее 6, но я хотела бы показать на графике индивидуальную погрешность, то есть не +/- 5%, а именно, что это среднее между тремя числами.

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

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