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

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

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

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

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

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

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

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

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

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

Используя для определения сопротивления закон Ома произведем обработку данной серии экспериментальных данных.

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

Результат расчета

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

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

В меню «Диаграмма» выберите пункт «Добавить линию тренда…».

В результате, должен получиться следующий график.

Задание 1.

Просчитать погрешность измерений и построить график ее распределения.

Задание 2.
Определить является ли 3-е измерение промахом.

Источник

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

ЭЛЕМЕНТЫ ТЕОРИИ ПОГРЕШНОСТЕЙ

АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТИ

Пусть – точное значение, – приближенное значение некоторого числа.

Абсолютная погрешность приближенного числа равна модулю разности между его точным и приближенным значениями:

Довольно часто точное значение неизвестно, поэтому вместо абсолютной погрешности используют понятие границы абсолютной погрешности:

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

Основной характеристикой точности числа является относительная погрешность.

Относительная погрешность – это отношение абсолютной погрешности к приближенному значению числа:

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

1.

2.

3.

4.

Общая формула для оценки предельной абсолютной погрешности функции нескольких переменных имеет вид:

где –предельная абсолютная погрешность числа .

Пример: Известно, что где

Найти , , ,

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

Рис. 1. Вид экрана для вычисления абсолютной и относительной погрешностей

Исходные данные вводятся в блок А1:B6 (рис. 1). В ячейки С1:С6вводятся формулы для вычисления частных производных искомой функции. В ячейку Е8записывается формула . Модуль вводится с использованием функции =abs().

В ячейках D1:E6рассчитываются верхние и нижние оценки значений переменных по формулам (аналогично для других переменных).

В ячейках B8:B10вычисляются верхняя и нижняя оценки значений функции и само значение функции отличие вычисляемых функций в используемом наборе аргументов.

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

В ячейку Е10записывается формула для вычисления относительной погрешности

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

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

Задания для самостоятельного выполнения.

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

Контрольные вопросы

1. Как записать основные математические функции в Excel.

2. Сформулируйте определение абсолютной и относительной погрешностей.

3. Запишите формулы для вычисления предельной абсолютной и предельной относительной погрешностей.

4. Основные правила вычисления абсолютной и относительной погрешностей.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Только сон приблежает студента к концу лекции. А чужой храп его отдаляет. 8833 – | 7547 – или читать все.

78.85.5.224 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)
очень нужно

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

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

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

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

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

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

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

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

Используя для определения сопротивления закон Ома произведем обработку данной серии экспериментальных данных.

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

Результат расчета

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

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

В меню «Диаграмма» выберите пункт «Добавить линию тренда…».

В результате, должен получиться следующий график.

Задание 1.

Просчитать погрешность измерений и построить график ее распределения.

Задание 2.
Определить является ли 3-е измерение промахом.

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

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

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

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

Чтобы было понятно, Результаты испытаний записываются в виде 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. Это означает, что мы не входим в предел воспроизведения.

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

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

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

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

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

Источник