Как посчитать погрешность в excel

Расчёт случайной погрешности

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

В четвёртом столбце приведены квадраты отклонений , которые нужны для вычисления по формуле (1.8) стандартного отклонения. Внизу столбца записано значение дисперсииD(R).

В последнем (пятом) столбце указаны доверительная вероятность, коэффициент Стьюдента, взятый из таблицы 1.1, и результаты оценки случайной погрешности: стандартное отклонение (R) и случайная погрешность_с(R).

Итог: .

Оценка случайной погрешности с помощью программы excel

• Первая из них СТАНДОТКЛОН.В вычисляет стандартное отклонение, её аргументы – столбец результатов измерений (в таблице 1.2 это числа, приведённые во втором столбце).
• Вторая функция – ДОВЕРИТ.СТЬЮДЕНТ. Она вычисляет случайную погрешность _с. Аргументы функции: уровень значимости, который равен (1 –p), стандартное отклонениеи объём серии измеренийn.

Погрешность косвенного измерения

Если X– величина, измеряемая косвенно, то результат её измеренияx– это функция одного или нескольких прямых измерений. Рассмотрим каждый из этих вариантов. А) x– функцияоднойпеременной. Пусть a– результат прямого измерения величиныA, погрешность этого результата известна и равна(a). Пусть далееx=x(a), и надо найти(x). Считая, что погрешности(a) и(x)малы, можно воспользоваться формулой, связывающей дифференциалыdaиdx: (1.10) Знак модуля | | здесь поставлен потому, что в отличие от дифференциалов погрешности всегда положительны. Примеры использования формулы (1.10) сведены в таблицу 1.3. Таблица 1.3.

Погрешности некоторых косвенных измерений

Вид функции x(a)	Абсолютная погрешность (x)	Относительная погрешность (x)

Б) x– функция нескольких переменных. Пусть x=x(a, b, c, . ), гдеa, b, c, . – результатпрямыхизмерений величинA, B, C,. соответственно. Погрешности этих результатов известны и равны(a),(b),(c), . . Найти надо(x). Общее правило, по которому решается эта задача, выражается следующей формулой: (1.11) где ,,и т.д. – это так называемыечастные погрешности, которые определяются формулами, аналогичными (1.10): (1.12) Можно вместо (1.11) использовать более простую формулу: (1.13) Эта формула, правда, даёт несколько завышенное значение погрешности. Примеры использования формул (1.11) – (1.13) сведены в таблицу 1.4. Таблица 1.4.

Оценка погрешностей измерения параметров прямой линии

Простейшая методика оценки (k) и (b) состоит в следующем.

• Провести на графике (временно) две вспомогательные прямые линии. Обе они должны пройти через доверительные интервалы экспериментальных точек, но при этом для первой из вспомогательных линий параметры k и b должны быть максимально возможными, поэтому её надо провести как можно круче и выше. Для второй вспомогательной линии значения k и b должны быть минимально возможными, то есть её надо провести как можно более полого и как можно ниже. Образец показан на рисунке 4.3.
• И змерить описанным выше графическим методом параметры вспомогательных прямых (k_max, b_max) и (k_min, b_min).
• Определить погрешности (k) и (b) по формулам:

• Удалить с графика вспомогательные прямые 1 и 2.

Построение графиков с помощью программы excel

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

Вкладка «Вставка», группа «Диаграммы», выбрать пункт «Точечная».

В меню «Формат ряда данных» – «Параметры маркера» выбрать пункт «Встроенный», тип – ⃝ (кружок), размер – 5.

1. Нанесение доверительных интервалов

Вкладка «Макет», группа «Анализ», выбрать пункт «Планки погрешностей».

1. Проведение линии по экспериментальным точкам

Выделить экспериментальные точки, нажав мышкой на любую из точек, и выбрать в динамическом меню пункт «Добавить линию тренда». Откроется окно «Формат линии тренда», выбрать в нём нужное.

1. Определение параметров экспериментальной линии

В окне «Формат линии тренда» поставить флажок против опции «Показывать уравнение на диаграмме». В частности, если линия тренда – прямая, то уравнение линии даёт параметры k и b.

Есть и второй способ определения параметров прямой линии – с помощью функции ЛИНЕЙН. Аргументы функции: столбец значений (x_i) и столбец значений (y_i). Результат: два числа k и b. Эти числа программа ЛИНЕЙН находит методом наименьших квадратов, описанным в п. 4.2. Если надо узнать только значение углового коэффициента k, то можно воспользоваться функцией НАКЛОН, а если надо узнать только значение свободного члена b, то можно воспользоваться функцией ОТРЕЗОК.

Формулировка выводов

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

Прежде всего, прочитайте цель работы и задание (задания). Поймите, достигнута ли в ходе лабораторной работы цель и выполнено ли задание (задания). Если да, то последним пунктом выводов может быть утверждение: «Таким образом, все задания выполнены, и цель работы достигнута». Если цель работы не достигнута, или достигнута не полностью, то надо написать именно это – например, «Таким образом, задание 1 выполнено, но задание 2 из-за недостатка времени не выполнено, так что цель работы достигнута частично».

Главные принципы при формулировке выводов: структурированность, лаконичность, точность и аргументированность.

1. Структурированность означает, что выводы сформулированы в виде нескольких пунктов 1) … 2) … и т.д. Каждый пункт – это отдельный вывод.
2. Все утверждения в выводах должны быть аргументированы.

В частности, написать фразу «Цель работы достигнута» можно только после того, как приведён конкретный результат, полученный в ходе экспериментов. Пример.

1. В ходе работы исследована зависимость амплитуды затухающих колебаний физического маятника A от времени t. График экспериментальной зависимости lnA от t имеет вид прямой линии, что подтверждает экспоненциальный характер убывания амплитуды колебаний со временем.
2. Экспериментально установлено, что период затухающих колебаний физического маятника T с течением времени не изменяется – см. таблицу ….
3. Таким образом, оба поставленных задания выполнены, и цель работы достигнута.

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

Например, если цель работы – измерение объёма детали и в процессе измерений использовался штангенциркуль, то не надо писать фразу «В ходе лабораторной работы я научился пользоваться штангенциркулем». Гораздо более информативной является такая фраза: «Объём детали измерен с погрешностью 5%, то есть точность измерений – средняя. Главный источник погрешности – штангенциркуль, класс точности которого 0,1 мм. Если использовать штангенциркуль с более высоким классом точности или микрометр, то погрешность может быть в несколько раз ниже».

Другой пример. Цель работы: проверка второго закона Ньютона, в выводе написано: «Второй закон Ньютона состоит в том, что ускорения, которые приобретают тела, пропорциональны сумме сил, действующих на эти тела. Таким образом, …» То, что сформулировал Ньютон, это – результат его работы, поэтому формулировать закон Ньютона можно (и нужно!) в разделе «Краткая теория», но не в выводах. В выводе же можно написать, например, так.

1. В ходе лабораторной работы экспериментально исследована зависимость между ускорением a, приобретаемым грузом, и результирующей силой F, действующей на груз. График на рисунке … показывает, что эта зависимость прямо пропорциональная. Следовательно, проделанные эксперименты подтверждают второй закон Ньютона (…).
2. Задание выполнено, и цель работы достигнута.

Третий пример. «Проведя лабораторную работу, мы экспериментальным путём провели измерения и вычисления, которые записали в таблицу 5.2. Затем мы построили два графика, которые требуются согласно заданию 3. …» Во-первых, в этом фрагменте выводов есть совершенно лишние слова «экспериментальным путём провели измерения», ведь измерения всегда проводятся только экспериментальным путём. Во-вторых, в этом фрагменте есть явная ошибка: «экспериментальным путём провели … вычисления». В третьих, не надо перечислять, что экспериментатор делал, а надо указать, что он получил в результате своих действий. Вот образец лаконичного и ясного вывода: «График экспериментальной зависимости показателя преломления света n от его длины волны  показывает, что стекло, из которого сделана призма, обладает в оптическом диапазоне нормальной дисперсией, так как с ростом  значение n уменьшается».

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

Пример. «Измерения диода и транзистора показали, что p—n переход даёт обратный ток, коллекторную и сеточную характеристику. А также сопротивление p—n перехода растёт и дырки диффундируют.»

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

В-вторых, попытка разобрать эту кучу оказывается безрезультатной из-за неточности формулировок. А) «Измерения диода и транзистора …» – это бессмыслица, так как измерять можно не предмет, а какие-то физические величины. Например, можно измерять силу тока через диод или напряжение между эмиттером и базой в транзисторе. Б) «p—n переход даёт обратный ток, коллекторную и сеточную характеристику» – это сказано очень туманно и неконкретно. Следовало бы данную фразу превратить в три конкретных вывода. Первый вывод – о том, что дало исследование обратного тока в диоде. Второй вывод – о том, какой характер имеет коллекторная характеристика транзистора. Третий вывод можно было бы посвятить сеточной характеристике, но понятие сеточной характеристики неприменимо ни для диода, ни для транзистора, это – явная ошибка. В) «Сопротивление p—n перехода растёт…» Что это значит? Сопротивление растёт с течением времени? Или, может, оно растёт при изменении каких-то параметров схемы? Г) «Дырки диффундируют.» Куда они диффундируют, откуда это следует и зачем об этом написано?

1. Все лабораторные работы в зависимости их цели можно разделить на три типа: исследовательские работы, измерительные и комплексные. Для каждого из этих типов и выводы имеют свои особенности.

Если работа – исследовательская, то в выводах надо написать, что исследовано, о чём свидетельствуют результаты исследований и чем они объясняются. Например, эксперименты подтвердили какой-то физический закон или закономерность. Или в ходе экспериментальных исследований получен результат, причиной которого является … (указать конкретную причину). Образцы приведены в пунктах 6.3 и 6.4.

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

• грубый метод, если δ > 10%,
• метод средней точности, если 1% < δ < 10%,
• метод высокой точности, если δ < 1%.

Пример выводов в измерительной работе.

1. В ходе лабораторной работы измерен методом Чайлда-Ленгмюра удельный заряд электрона. Результат измерения:

1. Полученный результат согласуется со справочной информацией. Например, в задачнике И.Е. Иродова приведено следующее значение удельного заряда электрона: (И.Е. Иродов. Задачи по общей физике. – СПб.: изд-во «Лань», 2001), и это число попадает в доверительный интервал, указанный в выводе 1.
2. Так как погрешность измерения больше 10%, то метод Чайлда-Ленгмюра измерения является грубым. Правда, причина низкой точности измерений состоит, возможно, не в самом методе, а в том, что геометрические размеры диода и его элементов даны с погрешностью 10%.
3. Задание выполнено, и цель работы достигнута.

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

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

Расчет случайной погрешности средствами Excel

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

1) Рассчитывается среднее значение

=СРЗНАЧ(число1; число2; . ) число1, число2, . — аргументы, для которых вычисляется среднее.

2) Рассчитывается стандартное отклонение

=СТАНДОТКЛОНП(число1; число2; . ) число1, число2, . — аргументы, для которых вычисляется стандартное отклонение.

3) Рассчитывается абсолютная погрешность

=ДОВЕРИТ(альфа ;станд_откл;размер) альфа — уровень значимости используемый для вычисления уровня надежности.

(, т.е. означает надежности); станд_откл — стандартное отклонение, предполагается известным; размер — размер выборки.

Лабораторная работа 1

Тема: Обработка прямых измерений в Excel (2 часа ).

Задание: Обработать заданный набор экспериментальных данных методом Стьюдента, построить экспериментальные кривые методом наименьших квадратов.

Пример расчета

Используемуе формулы

Для построения графика используем мастер диаграмм.

Расчет погрешности при косвенных измерениях

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

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

Погрешность величины у определяется по формуле:

где .

Расчет косвенной погрешности в Maple

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

, где

Таким образом, найден доверительный интервал величины .

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

1. вычисление погрешности оформить в виде процедуры

>dy:=proc(y, dx) ……кодпроцедуры …… end proc Код процедуры учащийся должен составить самостоятельно на основе примера, рассмотренного выше.

1. параметр dx считать массивом из N переменных
2. для определения списка аргументов и их количества величины y можно использовать операторы op() и nops():

Функция СТОШYX

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции СТОШYX в Microsoft Excel.

Описание

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

Синтаксис

Аргументы функции СТОШYX описаны ниже.

• Известные_значения_y Обязательный. Массив или диапазон зависимых точек данных.
• Известные_значения_x Обязательный. Массив или диапазон независимых точек данных.

Замечания

• Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
• Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
• Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.
• Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.
• Если аргументы «известные_значения_y» и «известные_значения_x» содержат различное количество точек данных, то функция СТОШYX возвращает значение ошибки #Н/Д.
• Если known_y и known_x пустые или имеют менее трех точек данных, steYX возвращает #DIV/0! значение ошибки #ЗНАЧ!.
• Уравнение для стандартной ошибки предсказанного y имеет следующий вид: где x и y — выборочные средние значения СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y), а n — размер выборки.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Известные значения y

Известные значения x