Как сделать погрешность в excel?

Диаграммы в Excel. Использование полос погрешности

Некоторые статистические данные могут отображаться на диаграммах, даже без создания отдельных рядов. Многие (но не все) диаграммы позволяют дополнить ряд (ряды) данных полосами погрешностей. [1] Полосы погрешностей [2] отображают дополнительную информацию о данных. Например, их можно использовать для изображения ошибки или неопределенности, связанной с каждой точкой данных.

Например (рис. 1) полосы погрешностей могут изображать диапазоны ошибок измерения каждой точки данных. В этом примере полосы погрешностей выражены в процентах: значение плюс-минус 10% от значения. [3]

Рис. 1. График с полосами погрешностей, выраженных в процентах

Скачать заметку в формате Word или pdf, примеры в формате Excel2013 или Excel2007

Полосы погрешностей поддерживаются рядами следующих типов двухмерных диаграмм:

  • диаграммы с областями;
  • линейчатые диаграммы;
  • гистограммы;
  • графики;
  • точечные диаграммы;
  • пузырьковые диаграммы.

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

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

Для добавления полос погрешностей выделите ряд данных диаграммы, и пройдите по меню Конструктор – Добавить элемент диаграммы – Предел погрешностей и выберите одну из опций: Стандартная погрешность, Процент или Стандартное отклонение (рис. 2). Если выбрать опцию Дополнительные параметры предела погрешностей, откроется диалоговое окно Формат предела погрешностей (рис. 3). В этом окне, помимо трех упомянутых, можно также задать еще две опции предела погрешностей: Фиксированное значение и пользовательское. На рисунке 3 показ выбор, соответствующий пределу погрешностей, изображенному на рис. 1 – относительное значение 10%.

Рис. 2. Добавление предела погрешностей

Рис. 3. Формат предела погрешностей

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

Возможно использование пяти типов предела погрешностей:

  • Фиксированное значение. Полосы погрешностей откладываются от каждой точки данных на заданную пользователем фиксированную величину. Все полосы погрешностей имеют одинаковую высоту. Обратите внимание: ошибка выражается не в процентах от значения, а в единицах самого значения (по оси y).
  • Относительное значение. Полосы погрешностей откладываются от каждой точки данных на величину, определяемую в процентах от значения точки. Например, если задать в поле ввода значение 5%, а значение точки равно 100, то полоса погрешности будет выведена от 95 до 105. Таким образом, длина полосы погрешности зависит от значения точки.
  • Стандартное отклонение. Полосы погрешностей откладываются на величину стандартного отклонения (другое название — среднеквадратическое отклонение), равного корню от суммы квадратов отклонений, деленному на квадратный корень от объема выборки. Для обозначения стандартного отклонения обычно используется символ σ (сигма):где – среднее значение по выборке. Все полосы погрешностей имеют одинаковую высоту и откладываются от среднего значения вверх и вниз на заданное (не обязательно целое) число σ. Обратите внимание: расположение полос погрешностей одинаковое для всех точек на диаграмме, поскольку они откладываются не от конкретной точки, а от среднего всех точек. Пример ниже.
  • Стандартная погрешность. Как сообщает справка Excel, полосы погрешностей откладываются от каждой точки на величину: , где ny – число значений в ряду. При этом не сообщается, рассчитанные значения откладываются по обе стороны от точки, или вычисленное значение нужно поделить пополам и только половину отложить в каждую сторону от точки. У меня, кстати, не получилось подтвердить приведенную формулу. Более того, при уменьшении целого ряда значений у, стандартная погрешность только росла… Обратите внимание: размер полос погрешностей одинаковый для всех точек, а вот откладываются полосы погрешностей от каждой отдельной точки (а не от среднего значения по всем точкам)
  • Пользовательская. Полосы погрешностей определяются значениями, хранящимися в заданном пользователем диапазоне. Обычно диапазон содержит формулы. Об этом подробнее ниже.

Метод стандартного отклонения проиллюстрирован на рис. 3. Здесь на точечную диаграмму нанесена полоса погрешностей по оси у. В отличие от других типов полос погрешностей полоса типа стандартное отклонение выводится относительно среднего арифметического значения всех точек данных. В примере, показанном на рис. 3, среднее ста точек равно 40, а стандартное отклонение — 10. [4] Поэтому полоса погрешностей выводится вокруг среднего плюс-минус отклонение: 40±10. Благодаря полосе погрешности из диаграммы ясно видно, что большинство точек данных (теоретически 68,2%) отличаются от среднего не более чем на величину стандартного отклонения σ.

На рис. 4 показана точечная диаграмма с полосами погрешностей как по оси у, так и по оси х. Оба набора полос погрешностей выводят для каждого значения соответствующие ошибки — плюс-минус 10%. Полосы погрешностей по осям х и у независимы друг от друга. Для них могут быть установлены разные параметры.

Рис. 4. Точечная диаграмма с полосами погрешностей по осям х и у

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

Для изменения формата полос погрешностей дважды щелкните на любой из них. Появится диалоговое окно Формат предела погрешностей. Во вкладке Заливка и границы можно изменить практически любые параметры планки погрешности (рис. 5).

Читать еще:  Как сделать шапочку в excel?

Рис. 5. Вкладке Заливка и границы диалогового окна Формат предела погрешностей

Делая активной горизонтальную или вертикальную планку погрешности можно в диалоговом окне Формат предела погрешностей выбирать вкладку для параметров X-погрешности или Y-погрешности. В диаграммах, отличных от точечных и пузырьковых, вкладка Х-погрешности отсутствует.

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

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

На рис. 6 показан график, отображающий объемы ежемесячных продаж. Полосы погрешностей изображают изменение объемов продаж по сравнению с предыдущим годом. Если полоса погрешности расположена над точкой данных, то в этом месяце прошлого года объем продаж был выше, а если под точкой — ниже. Такое использование полос погрешностей фактически является альтернативой выводу дополнительного ряда данных.

Рис. 6. Пределы погрешностей изображают объемы продаж в эти же месяцы прошлого года

Столбец D содержит простую формулу, вычисляющую разность данных столбцов В и С. Диапазон D2:D13 используется в качестве диапазона «+», ассоциированного с переключателем пользовательская. Выбран режим вывода Плюс.

Для построения диаграммы выделяем диапазон А1:В13 и вставляем стандартный график с маркерами. Далее добавляем предел погрешности (как на рис. 2). В качестве величины погрешности устанавливаем тип Пользовательская. Жамкаем кнопку Укажите значения, и для Положительное значение ошибки задаем диапазон D2:D13. Поле Отрицательное значение ошибки оставляем пустым.

[1] Заметка написана с использованием материалов книги Джона Уокенбаха Диаграммы в Excel; книга была написана для Excel2003; более поздние издания мне не известны.

[2] В современных версиях наряду с термином полосы погрешностей используются термины планки погрешности и пределы погрешностей. Я буду использовать все эти термины как синонимы.

[3] Недавно я впервые приобрел для дома лицензионную версию MS Excel. Во-первых, очень хотелось поюзать новинки от MS. Во-вторых, MS предлагает вполне бюджетный вариант – Microsoft Office Home and Student 2013 по цене от 2800 руб. (есть Excel, Word, PowerPoint, OneNote + 7ГБ места в облаке!). Так что изложение и иллюстрации основаны на Excel2013.

[4] Такая структура данных была сформирована путем задания в ячейках А1:А100 формулы =НОРМ.ОБР(СЛЧИС();40;10), где СЛЧИС() – вероятность от 0 до 1, 40 –среднее, 10 – стандартное отклонение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать графики в excel 2016?

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

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

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

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

4 комментария

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

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

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

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

Excel в помощь для определения пределов погрешности

Доброго дня, друзья.

Так как в после прошлого поста несколько человек заинтересовались моей таблицей, решил поделиться с вами еще одной своей таблицей.

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

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

Чтобы было понятно, Результаты испытаний записываются в виде X±Δ
где X – результат анализа;
±Δ – погрешность результатов анализа, в нашем случае воспроизводимость..

То есть для первого испытания на медь для Пробы 1 результат у нас (H7) 1,30±0,12, а у контрагентов (ячейка C7) 4,81±0,12. А разница между результатами 4,81-1,30=3,51

Мы не входим в предел воспроизведения, ячейка M7 окрасилась в красный и сразу видим, что и один из нас хочет другого немного обмануть)) Если бы ячейка стала зеленой, то все норм.

Вот чтобы такие расчеты постоянно не делать, была создана данная таблица.

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

Вот так выглядит рабочая таблица на странице Данные:

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

Также имеется вторая табличка на странице Пределы, где расписаны пределы по диапазонам:

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

Итак погнали. Что тут творится вообще ))

Буду объяснять для пробы 1, результаты Cu, ячейки M7 и N7. Остальное аналогично

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

В N7 вводим следующую формулу:

Тут остановимся, разберем формулу по частям:

Берем значение из ячейки H7 (это наш результат) и ищем на странице Пределы в массиве для Cu пределы значений, куда входит наш результат. Находим, что походит диапазон 1,2-1,6

Ищем номер строки значениея из ячейки H7 в таблице на листе Пределы. В предыдущей формуле мы нашли, что значение относится к пределам 1,2-1,6 и теперь легком можем найти номер строки, где он находится.

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

Тут Пределы!$B$4:$C$13 это массив где мы делаем поиск

ПОИСКПОЗ(ВПР(Данные!H7;Пределы!$A$4:$C$13;3;ИСТИНА);Пределы!$C$4:$C$13;0) — номер строки.

И единичка в конце — номер столбца.

Теперь мы узнали, что наш результат должен быть 1,30±0,12

А разница результатов двух предприятий 3,51. Это означает, что мы не входим в предел воспроизведения.

Чтобы визуально сразу увидеть это, окрасим эту ячейку в красный. Делается это через меню Условное форматирование

Читать еще:  Как сделать базу данных в access из таблицы excel?

Выбираем в меню Условное форматирование — Правила выделения ячеек — Больше (Меньше) и задаем форматирование — окрасить ячейку в красный или зеленый цвет.

Также у нас есть ограничение в поставке продукта. Качество должно быть не менее определенного значения. Чтобы тоже сразу наглядно это увидеть, я через Условное форматирование выбрал пункт Между.. и задал нужные значения

Если отгрузим товар с качеством по меди меньше 1,5%, то ячейка окрашивается в красный цвет.

Спасибо что дочитали, надеюсь кому-нибудь пригодится данная таблица или формулы.

Точность округления как на экране в Microsoft Excel

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

Настройка округления как на экране

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

Включать точность как на экране, нужно в ситуациях следующего плана. Например, у вас стоит задача сложить два числа 4,41 и 4,34, но обязательным условиям является то, чтобы на листе отображался только один десятичный знак после запятой. После того, как мы произвели соответствующее форматирование ячеек, на листе стали отображаться значения 4,4 и 4,3, но при их сложении программа выводит в качестве результата в ячейку не число 4,7, а значение 4,8.

Это как раз связано с тем, что реально для расчета Эксель продолжает брать числа 4,41 и 4,34. После проведения вычисления получается результат 4,75. Но, так как мы задали в форматировании отображение чисел только с одним десятичным знаком, то производится округление и в ячейку выводится число 4,8. Поэтому создается видимость того, что программа допустила ошибку (хотя это и не так). Но на распечатанном листе такое выражение 4,4+4,3=8,8 будет ошибкой. Поэтому в данном случае вполне рациональным выходом будет включить настройку точности как на экране. Тогда Эксель будет производить расчет не учитывая те числа, которые программа держит в памяти, а согласно отображаемым в ячейке значениям.

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

Включение настройки точности как на экране в современных версиях Excel

Теперь давайте выясним, как включить точность как на экране. Сначала рассмотрим, как это сделать на примере программы Microsoft Excel 2010 и ее более поздних версий. У них этот компонент включается одинаково. А потом узнаем, как запустить точность как на экране в Excel 2007 и в Excel 2003.

    Перемещаемся во вкладку «Файл».

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

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

  • После этого появляется диалоговое окно, в котором говорится, что точность вычислений будет понижена. Жмем на кнопку «OK».
  • После этого в программе Excel 2010 и выше будет включен режим «точность как на экране».

    Для отключения данного режима нужно снять галочку в окне параметров около настройки «Задать точность как на экране», потом щелкнуть по кнопке «OK» внизу окна.

    Включение настройки точности как на экране в Excel 2007 и Excel 2003

    Теперь давайте вкратце рассмотрим, как включается режим точности как на экране в Excel 2007 и в Excel 2003. Данные версии хотя и считаются уже устаревшими, но, тем не менее, их используют относительно немало пользователей.

    Прежде всего, рассмотрим, как включить режим в Excel 2007.

    1. Жмем на символ Microsoft Office в левом верхнем углу окна. В появившемся списке выбираем пункт «Параметры Excel».
    2. В открывшемся окне выбираем пункт «Дополнительно». В правой части окна в группе настроек «При пересчете этой книги» устанавливаем галочку около параметра «Задать точность как на экране».

    Режим точности как на экране будет включен.

    В версии Excel 2003 процедура включения нужного нам режима отличается ещё больше.

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

    Как видим, установить режим точности как на экране в Excel довольно несложно вне зависимости от версии программы. Главное определить, стоит ли в конкретном случае запускать данный режим или все-таки нет.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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