Расчет неопределенности результатов измерений пример в excel

Обновлено: 07.01.2025

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

В этой статье мы рассмотрим практический пример расчета неопределенности выполненных измерений на примере обычного люксметра-пульсметра еЛайт02.

Расчёт неопределённости измерений достаточно трудоёмкое занятие, даже если использовать калькулятор или формулы, забитые в электронные таблицы. Обычно, при работе с обычным прибором, пользователь вынужден вручную производить несколько измерений в каждой точке, из которых потом также вручную рассчитывает неопределенность измерений. Однако сейчас уже выпускаются измерительные приборы, в которых реализован встроенный калькулятор для расчёта неопределенности измерений. Например профессиональный прибор для измерения освещённости "еЛайт01" или совсем недорогой профессиональный цифровой люксметр "еЛайт-мини". Это стало возможным совсем недавно, благодаря использованию в таких приборах цифровой обработки сигнала, позволяющей обрабатывать тысячи промежуточных измерений и сопровождающих их факторов для получения итогового результата.

Автоматический расчёт неопределённости измерений в приборе еЛайт01.

На рисунке представлен результат автоматического расчёта неопределённостей измерений прибором "еЛайт01", а именно:

Максимальное значение измеренной освещённости,
Минимальное значение измеренной освещённости,
Среднее значение измеренной освещённости,
Неопределенность измерений освещённости по типу Б,
Неопределенность измерений освещённости по типу А,
Суммарная стандартная неопределённость измерения освещённости,
Расширенная неопределённость результата измерения освещённости,
Максимальное значение измеренного коэффициента пульсации,
Минимальное значение измеренного коэффициента пульсации,
Среднее значение измеренного коэффициента пульсации,
Неопределенность измерений коэффициента пульсации по типу Б,
Неопределенность измерений коэффициента пульсации по типу А,
Суммарная стандартная неопределённость измерения коэффициента пульсации,
Расширенная неопределённость результата измерения коэффициента пульсации

Оценка неопределёности в люксметре еЛайт-мини.

В недорогом цифровом люксметре с поверкой "еЛайт-мини" оценка неопределённости выглядит попроще, чем в еЛайт01 но, тем не менее, предоставляет исчерпывающий результат:

Все перечисленные выше типы неопределённостей и способы их расчёта подробно описаны в статье "Понятие и типы неопределенностей. ГОСТ 34100.3-2017"

Пример расчета неопределенности измерений "вручную".

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

Исходные данные:

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

Например, если при измерениях освещенности на рабочем месте использовать обычный прибор - люксметр-пульсметр "еЛайт02" (допускаемая основная относительная погрешность измерений освещенности – 8%), то придется провести несколько замеров. Например, пусть на указанном рабочем месте получены следующие 6 значений осещённости: 388, 377, 369, 369, 370, 372 лк.

Вычисление неопределенности.

1. Вычисляем среднее арифметическое значение освещенности из всех измерений в данной точке:

$$ E=\frac \sum_^n E_i \qquad (1) $$

$$ E=\frac (388 + 377 + 369 + 369 + 370 + 372) = \frac = 374 \,лк $$

2. Для источников неопределенности случайного характера вычисляем неопределенность по типу А:

3. Для источников неопределенности систематического характера (приборная погрешность) вычисляем неопределенность по типу Б:

где ±ΔЕ – пределы допускаемой приборной погрешности,а качестве значения освещенности берем среднее значение освещенности 374 лк, вычисленное в п.1 , с учетом погрешности 8% прибора «еЛайт02».

4. Вычисляем суммарную стандартную неопределенность:

5. Для доверительной вероятности (вероятности охвата) P = 0.95 (рекомендуется в Руководстве по расчету неопределенности) задаем коэффициент охвата k = 2 и вычисляем расширенную неопределенность измерений:

$$ u = ku_c \qquad (5)$$

$$ u = 2 \times 17.55 = 35.1\,лк\;(или \frac = 9.4\%) $$

Результат расчета неопределенности измерений освещенности для люксметра «еЛайт02»:

Расширенная неопределенность результатов измерений освещенности прибором «еЛайт02» U(E) = 9.4%

Как то раз, подруга попросила сделать ей нормальный калькулятор для расчета неопределенности в Эксель. За неимением оного, калькулятор я сделал в опен офисе, но в экселе он тоже работает. О том, что это за штука и для чего она, я писать не буду, кто в теме, тот поймет. Может кому интересно будет.

В книге 3 листа:

1) сам калькулятор;

2) список измерительных приборов;

3) список определяемых параметров.

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

MS, Libreoffice & Google docs

466 постов 12.7K подписчика

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Если это вы для себя - то нормально. А если для непонимающего в екселе сотрудника- то всё плохо. Защита от изменений не стоит, ячейки, где нужен ввод, а где менять нельзя не защищены и могут быть просмотрены/отредактированы.

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

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

Добрый день! Вещь удобная, тоже примерно такие же файлы себе склепали. Вопрос, вы это делали просто потому что в Excel шарите или как-то связаны с метрологической/лабораторной деятельностью?) Если второй вариант, дайте знать, пожалуйста, хотелось бы по паре вопросов посоветоваться :)

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

интересные табличка, я не очень дружу с этими формулами, однако было бы здорово если бы во смогли допилить табличку с прибором Экофизика 110А. расчет неопределенности u и u+ для вибрации.

в смысле кто в теме? а остальным как? а если мне надо, но я не знаю, что это именно оно? разъясняйте уже нормально.

А вопрос задать можно? Мне нужно прописать формулу, чтобы от заданного числа получалось случайное число более на от 3 до 5%. Написал вот так: А1*(СЛЧИС()*(1,05-1,03)+1,03), работает, но формула пересчитывает результат при каждом изменении документа и показатель меняются, а мне надо один раз найти рандомные числа и чтобы они оставались неизменными. Можно что-нибудь придумать?

Если я не могу определиться что прибухнуть, пивка или сэма, куда что забивать что бы определиться? Заколебала меня эта неопределенность.

Невероятно!

Тибетский монах был обнаружен в горах Непала. Он считается самым старым человеком на планете с возрастом в 201 год. Он находится в состоянии глубокого транса, или медитации, под названием "такатет".

Когда его обнаружили в пещере, спасатели сначала подумали, что это просто мумия.

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

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

Баянометр молчал, извините если уже было :)

Не анекдот, а Писание.

Чат мой, люди знакомые, ставлю пятничное моё;)

Само зло

Экономия на спичках

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

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

Сегодня их порезали на половинки. Народ стал брать по четыре.

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

Маркетинг

Илон Маск умеет интриговать

Уравнение: BLM - 2,5 = 0

Ответ на пост «Уборщица»

Примерно 15 лет назад я работал в мэрии города на довольно высокой в иерархии муниципальных служащих должности. Был молод и глуп в делах житейских, но прислушивался к советам старших. Моя мама, в первый день моей работы напутствовал меня словами: "в первую очередь здоровайся с уборщицей и тех персоналом, не игнорируй простых людей, они ещё всех вас там переработают." И я воодушевленный наставлениями мамы, да и воспитанием наученный прежде всего, всегда здоровался с уборщицами, поздравлял их с праздниками, иногда дарил небольшие презенты в виде шоколадок или пирожных. Очень хорошие, трудолюбивые люди и мне искренне было приятно с ними иногда переброситься парой слов. Так вот, отработал я там лет пять, уволился, ушел на другую работу. История поросла быльём, но. Недавно зашёл в мэрию документы сдать по нашей фирме и как назло паспорта нет с собой, грозный охранник не пропустил и я расстроенный уже собирался уходить, но тут вышла одна из тех самых уборщиц, узнала меня, очень искренне порадовалась встрече, рыкнула на охранника: наш человек, почему не пропускаешь! Тот что-то промямлил, мол не положено, но пропустил. Сдал я документы, ушел, а на душе приятно. Вот так вот, всех переработала и мэров и сэров и пэров, да и охрану гоняет.)))

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

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

Мы будем использовать следующие примеры данных с этими формулами.

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

Среднее арифметическое значений

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

Это легко сделать в Excel с помощью функции AVERAGE.

Мы можем использовать следующую формулу на примере данных выше.

Стандартное отклонение значений

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

Excel имеет несколько различных функций стандартного отклонения для различных целей. Двумя основными являются STDEV.P и STDEV.S.

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

В этом примере мы используем все пять наших значений в наборе данных, поэтому мы будем работать с STDEV.P.

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

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

Рассчитать стандартную ошибку

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

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

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

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

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

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

Выбрав диаграмму, нажмите «Дизайн»> «Добавить элемент диаграммы».

Затем выберите один из доступных типов ошибок.

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

Для этого примера мы решили показать процент.

Есть еще несколько вариантов, чтобы настроить ваши панели ошибок.

Дважды щелкните панель ошибок на диаграмме, чтобы открыть панель «Формат ошибок». Выберите категорию «Параметры панели ошибок», если она еще не выбрана.

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

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

К неопределённостям типа А относят любые неопределённости, которые, по своей природе, могут быть посчитаны только статистически. Результатом подсчёта является закон распределения p(q), для которого выполняются условия:

Статистические оценки

Статистическая оценка среднего значения μ_q при n замеров в одинаковых условиях:
q = 1/n Σ n _k=1 q_k (1)

Экспериментальная дисперсия - статистическая оценка дисперсии σ 2 :
s 2 (q_k) = 1/(n-1) Σ n _j=1 (q_j - q ) 2 (2)

Статистическая оценка дисперсии среднего значения σ( q ) 2 = σ 2 /n:
s 2 ( q ) = s 2 (q_k)/n (3)

Значение неопределённости

Неопределённость u(x_i) статистической оценки среднего значения n замеров величины X_i равна s( X _i) (формула 3).

Степень свободы v_i для значения u(x_i), равная n-1 (n - количество измерений величины x_i) обязательно указывается в документации к определению неопределённости типа А.

Среднее значение неопределённости

Статистическая оценка искомой величины Y, обозначаемая y, рассчитывается основываясь на статистических оценках величин x₁, x₂, . x_n: y = f(x₁, x₂, . x_n). Иногда предпочтительнее рассчитать статистическую оценку Y по формуле:

Пример расчет неопределенности по типу А

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

Рассмотрим пример замера диаметра цилиндра, номинальным диаметром 25.45см с помощью микрометра.

Статистическая оценка среднего значения 37 независимых измерений легче всего определяется как среднее арифметическое, по формуле:

Статистическая оценка дисперсии генеральной совокупности:

s 2 (q_k) = [(25.328 - 25.459) 2 + (25.438 - 25.459) 2 + . + (25.108 - 25.459) 2 ] / 36 = 0.054

Мы получили статистическую оценку дисперсии и значение σ = √s 2 - экспериментальное значение стандартного отклонения.

Наилучшей статистической оценкой стандартного отклонения среднего значения является σ 2 ( q ) = σ 2 /n, которую мы получим по формуле стандартной ошибки:

Данное значение, s 2 ( q ), описывает интервал, в котором ожидается значение μ_q.

Таким образом, для величины диаметра, полученного в результате 37 независимых измерений, неопределённость типа А среднего значения является u(q) = s( q ):

Важно!

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

Неопределённость типа А в эксель

Реализация в эксель очень проста, здесь потребуется только формулы СУММ и КОРЕНЬ. Параметры рассчитываются как в примере выше:

Статистическая оценка среднего значения - отношение суммы результатов к их количеству
Статистическая оценка дисперсии генеральной совокупности - по формуле q = 1/n (Σ n _k=1q_k)
Стандартное отклонение среднего значения, s_q - отношение дисперсии к количеству результатов минус один
Стандартная неопределённость типа А - корень из стандартного отклонения среднего значения

Неопределённость измерения типа Б

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

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

Примеры неопределённости типа Б

Неопределённость типа Б - это общее понятие, поэтому количество примеров может быть неограниченным, но общая идея - это интервал, например, "Доверительный интервал с уровнем доверия 82%", или "Неопределённость в пределах трёх стандартных отклонениях".

Пример 1. Неопределённость в стандартных отклонениях

В сертификате о калибровке указано, что действительное значение массы образца из нержавеющей стали, номинальным весом 1 кг, равно 1000,000325 г и "Неопределённость массы равна 240 мкг в пределах трёх стандартных отклонениях".

Таким образом, стандартная неопределённость: u = 240 мкг/3 = 80 мкг. Ожидаемая дисперсия: u 2 = (80 мкг) 2 = 6,4 • 10 -9 г 2 .

Пример 2. Неопределённость в доверительном интервале

В сертификате о калибровке указано, что сопротивление образца R_s, с номинальным сопротивлением 10 Ом, равно 10,000742 Ом ± 129 мкОм и неопределённость 129 мкОм покрывает доверительный интервал с уровнем доверия 99%.

Стандартная неопределённость u(R_s) = (129 мкОм)/2,58 = 50 мкОм (про число 2,58 и доверительный интервал описано в статье). Относительная неопределённость u(R_s)/R_s = 5,0 • 10 -6 . Ожидаемая дисперсия: u 2 (R_s) = (50 мкОм) 2 = 2,5 • 10 -9 Ом 2 .

Читайте также: