ЭЛЕМЕНТЫ ТЕОРИИ ПОГРЕШНОСТЕЙ
АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТИ
Пусть 
– точное значение, 
– приближенное значение некоторого числа.
Абсолютная погрешность приближенного числа равна модулю разности между его точным и приближенным значениями:
Довольно часто точное значение 
неизвестно, поэтому вместо абсолютной погрешности используют понятие границы абсолютной погрешности:
Число 
называется предельной абсолютной погрешностью, оно равно или превышает значение абсолютной погрешности.
Основной характеристикой точности числа является относительная погрешность.
Относительная погрешность – это отношение абсолютной погрешности к приближенному значению числа:
Результат действий над приближенными числами представляет собой приближенное число. Погрешность результата выражается через погрешности первоначальных данных по правилам:
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) Рассчитывается среднее значение
=СРЗНАЧ(число1; число2; . )
число1, число2, . — аргументы, для которых вычисляется среднее.
2) Рассчитывается стандартное отклонение
=СТАНДОТКЛОНП(число1; число2; . )
число1, число2, . — аргументы, для которых вычисляется стандартное отклонение.
3) Рассчитывается абсолютная погрешность
=ДОВЕРИТ(альфа ;станд_откл;размер)
альфа — уровень значимости используемый для вычисления уровня надежности.
( 
, т.е. 
означает надежности 
);
станд_откл — стандартное отклонение, предполагается известным;
размер — размер выборки.
Задание: Обработать заданный набор экспериментальных данных методом Стьюдента, построить экспериментальные кривые методом наименьших квадратов.
Предположим, в ходе эксперимента по измерению электросопротивления были получены следующие данные:
Используя для определения сопротивления закон Ома 
произведем обработку данной серии экспериментальных данных.
| Используемуе формулы |
 |
| Результат расчета |
 |
Для построения графика используем мастер диаграмм.
Полученные экспериментальные данные следует аппроксимировать. Для выполнения этой процедуры в Excel предусмотрен мастер, добавляющий линию тренда, производящий аппроксимацию и сглаживание.
В меню «Диаграмма» выберите пункт «Добавить линию тренда…».
В результате, должен получиться следующий график.
Задание 1.
Просчитать погрешность измерений и построить график ее распределения.
| Задание 1 | Задание 2 | Задание 3 |
| № опыта | № опыта | № опыта |
| 10,3 | 15,55 | 25,65 |
| 10,277 | 15,527 | 25,627 |
| 10,325 | 15,575 | 25,675 |
| 10,285 | 15,535 | 25,635 |
| 10,297 | 15,547 | 25,647 |
| 10,31 | 15,56 | 25,66 |
| 10,35 | 15,6 | 25,7 |
| 10,35 | 15,6 | 25,7 |
| 10,29 | 15,54 | 25,64 |
| 10,38 | 15,63 | 25,73 |
| Задание 4 | Задание 5 | Задание 6 |
| № опыта | №опыта | № опыта |
| 27,65 | 23,65 | 17,3 |
| 27,627 | 23,627 | 17,277 |
| 27,675 | 23,675 | 17,325 |
| 27,635 | 23,635 | 17,285 |
| 27,647 | 23,647 | 17,297 |
| 27,66 | 23,66 | 17,31 |
| 27,7 | 23,7 | 17,35 |
| 27,7 | 23,7 | 17,35 |
| 27,64 | 23,64 | 17,29 |
| 27,73 | 23,73 | 17,38 |
| Задание 7 | Задание 8 | Задание 9 |
| № опыта | № опыта | № опыта |
| 10,3 | 13,55 | 12,65 |
| 10,277 | 13,527 | 12,627 |
| 10,325 | 13,575 | 12,675 |
| 10,285 | 13,535 | 12,635 |
| 10,297 | 13,547 | 12,647 |
| 10,31 | 13,56 | 12,66 |
| 10,35 | 13,6 | 12,7 |
| 10,35 | 13,6 | 12,7 |
| 10,29 | 13,54 | 12,64 |
| 10,38 | 13,63 | 12,73 |
| Задание 10 | Задание 11 | Задание 12 |
| № опыта | №опыта | № опыта |
| 26,65 | 24,65 | 18,3 |
| 26,627 | 24,627 | 18,277 |
| 26,675 | 24,675 | 18,325 |
| 26,635 | 24,635 | 18,285 |
| 26,647 | 24,647 | 18,297 |
| 26,66 | 24,66 | 18,31 |
| 26,7 | 24,7 | 18,35 |
| 26,7 | 24,7 | 18,35 |
| 26,64 | 24,64 | 18,29 |
| 26,73 | 24,73 | 18,38 |
| Задание 13 | Задание 14 | Задание 15 |
| № опыта | № опыта | № опыта |
| 10,3 | 15,55 | 25,65 |
| 10,277 | 15,527 | 25,627 |
| 10,325 | 15,575 | 25,675 |
| 10,285 | 15,535 | 25,635 |
| 10,297 | 15,547 | 25,647 |
| 10,31 | 15,56 | 25,66 |
| 10,35 | 15,6 | 25,7 |
| 10,35 | 15,6 | 25,7 |
| 10,29 | 15,54 | 25,64 |
| 10,38 | 15,63 | 25,73 |
| Задание 16 | Задание 17 | Задание 18 |
| № опыта | №опыта | № опыта |
| 27,65 | 23,65 | 17,3 |
| 27,627 | 23,627 | 17,277 |
| 27,675 | 23,675 | 17,325 |
| 27,635 | 23,635 | 17,285 |
| 27,647 | 23,647 | 17,297 |
| 27,66 | 23,66 | 17,31 |
| 27,7 | 23,7 | 17,35 |
| 27,7 | 23,7 | 17,35 |
| 27,64 | 23,64 | 17,29 |
| 27,73 | 23,73 | 17,38 |
Задание 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%, то ячейка окрашивается в красный цвет.
Спасибо что дочитали, надеюсь кому-нибудь пригодится данная таблица или формулы.
Содержание
- Расчет ошибки средней арифметической
- Способ 1: расчет с помощью комбинации функций
- Способ 2: применение инструмента «Описательная статистика»
- Вопросы и ответы
Стандартная ошибка или, как часто называют, ошибка средней арифметической, является одним из важных статистических показателей. С помощью данного показателя можно определить неоднородность выборки. Он также довольно важен при прогнозировании. Давайте узнаем, какими способами можно рассчитать величину стандартной ошибки с помощью инструментов Microsoft Excel.
Расчет ошибки средней арифметической
Одним из показателей, которые характеризуют цельность и однородность выборки, является стандартная ошибка. Эта величина представляет собой корень квадратный из дисперсии. Сама дисперсия является средним квадратном от средней арифметической. Средняя арифметическая вычисляется делением суммарной величины объектов выборки на их общее количество.
В Экселе существуют два способа вычисления стандартной ошибки: используя набор функций и при помощи инструментов Пакета анализа. Давайте подробно рассмотрим каждый из этих вариантов.
Способ 1: расчет с помощью комбинации функций
Прежде всего, давайте составим алгоритм действий на конкретном примере по расчету ошибки средней арифметической, используя для этих целей комбинацию функций. Для выполнения задачи нам понадобятся операторы СТАНДОТКЛОН.В, КОРЕНЬ и СЧЁТ.
Для примера нами будет использована выборка из двенадцати чисел, представленных в таблице.
- Выделяем ячейку, в которой будет выводиться итоговое значение стандартной ошибки, и клацаем по иконке «Вставить функцию».
- Открывается Мастер функций. Производим перемещение в блок «Статистические». В представленном перечне наименований выбираем название «СТАНДОТКЛОН.В».
- Запускается окно аргументов вышеуказанного оператора. СТАНДОТКЛОН.В предназначен для оценивания стандартного отклонения при выборке. Данный оператор имеет следующий синтаксис:
=СТАНДОТКЛОН.В(число1;число2;…)«Число1» и последующие аргументы являются числовыми значениями или ссылками на ячейки и диапазоны листа, в которых они расположены. Всего может насчитываться до 255 аргументов этого типа. Обязательным является только первый аргумент.
Итак, устанавливаем курсор в поле «Число1». Далее, обязательно произведя зажим левой кнопки мыши, выделяем курсором весь диапазон выборки на листе. Координаты данного массива тут же отображаются в поле окна. После этого клацаем по кнопке «OK».
- В ячейку на листе выводится результат расчета оператора СТАНДОТКЛОН.В. Но это ещё не ошибка средней арифметической. Для того, чтобы получить искомое значение, нужно стандартное отклонение разделить на квадратный корень от количества элементов выборки. Для того, чтобы продолжить вычисления, выделяем ячейку, содержащую функцию СТАНДОТКЛОН.В. После этого устанавливаем курсор в строку формул и дописываем после уже существующего выражения знак деления (/). Вслед за этим клацаем по пиктограмме перевернутого вниз углом треугольника, которая располагается слева от строки формул. Открывается список недавно использованных функций. Если вы в нем найдете наименование оператора «КОРЕНЬ», то переходите по данному наименованию. В обратном случае жмите по пункту «Другие функции…».
- Снова происходит запуск Мастера функций. На этот раз нам следует посетить категорию «Математические». В представленном перечне выделяем название «КОРЕНЬ» и жмем на кнопку «OK».
- Открывается окно аргументов функции КОРЕНЬ. Единственной задачей данного оператора является вычисление квадратного корня из заданного числа. Его синтаксис предельно простой:
=КОРЕНЬ(число)
Как видим, функция имеет всего один аргумент «Число». Он может быть представлен числовым значением, ссылкой на ячейку, в которой оно содержится или другой функцией, вычисляющей это число. Последний вариант как раз и будет представлен в нашем примере.
Устанавливаем курсор в поле «Число» и кликаем по знакомому нам треугольнику, который вызывает список последних использованных функций. Ищем в нем наименование «СЧЁТ». Если находим, то кликаем по нему. В обратном случае, опять же, переходим по наименованию «Другие функции…».
- В раскрывшемся окне Мастера функций производим перемещение в группу «Статистические». Там выделяем наименование «СЧЁТ» и выполняем клик по кнопке «OK».
- Запускается окно аргументов функции СЧЁТ. Указанный оператор предназначен для вычисления количества ячеек, которые заполнены числовыми значениями. В нашем случае он будет подсчитывать количество элементов выборки и сообщать результат «материнскому» оператору КОРЕНЬ. Синтаксис функции следующий:
=СЧЁТ(значение1;значение2;…)В качестве аргументов «Значение», которых может насчитываться до 255 штук, выступают ссылки на диапазоны ячеек. Ставим курсор в поле «Значение1», зажимаем левую кнопку мыши и выделяем весь диапазон выборки. После того, как его координаты отобразились в поле, жмем на кнопку «OK».
- После выполнения последнего действия будет не только рассчитано количество ячеек заполненных числами, но и вычислена ошибка средней арифметической, так как это был последний штрих в работе над данной формулой. Величина стандартной ошибки выведена в ту ячейку, где размещена сложная формула, общий вид которой в нашем случае следующий:
=СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13))Результат вычисления ошибки средней арифметической составил 0,505793. Запомним это число и сравним с тем, которое получим при решении поставленной задачи следующим способом.
Но дело в том, что для малых выборок (до 30 единиц) для большей точности лучше применять немного измененную формулу. В ней величина стандартного отклонения делится не на квадратный корень от количества элементов выборки, а на квадратный корень от количества элементов выборки минус один. Таким образом, с учетом нюансов малой выборки наша формула приобретет следующий вид:
=СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13)-1)
Урок: Статистические функции в Экселе
Способ 2: применение инструмента «Описательная статистика»
Вторым вариантом, с помощью которого можно вычислить стандартную ошибку в Экселе, является применение инструмента «Описательная статистика», входящего в набор инструментов «Анализ данных» («Пакет анализа»). «Описательная статистика» проводит комплексный анализ выборки по различным критериям. Одним из них как раз и является нахождение ошибки средней арифметической.
Но чтобы воспользоваться данной возможностью, нужно сразу активировать «Пакет анализа», так как по умолчанию в Экселе он отключен.
- После того, как открыт документ с выборкой, переходим во вкладку «Файл».
- Далее, воспользовавшись левым вертикальным меню, перемещаемся через его пункт в раздел «Параметры».
- Запускается окно параметров Эксель. В левой части данного окна размещено меню, через которое перемещаемся в подраздел «Надстройки».
- В самой нижней части появившегося окна расположено поле «Управление». Выставляем в нем параметр «Надстройки Excel» и жмем на кнопку «Перейти…» справа от него.
- Запускается окно надстроек с перечнем доступных скриптов. Отмечаем галочкой наименование «Пакет анализа» и щелкаем по кнопке «OK» в правой части окошка.
- После выполнения последнего действия на ленте появится новая группа инструментов, которая имеет наименование «Анализ». Чтобы перейти к ней, щелкаем по названию вкладки «Данные».
- После перехода жмем на кнопку «Анализ данных» в блоке инструментов «Анализ», который расположен в самом конце ленты.
- Запускается окошко выбора инструмента анализа. Выделяем наименование «Описательная статистика» и жмем на кнопку «OK» справа.
- Запускается окно настроек инструмента комплексного статистического анализа «Описательная статистика».
В поле «Входной интервал» необходимо указать диапазон ячеек таблицы, в которых находится анализируемая выборка. Вручную это делать неудобно, хотя и можно, поэтому ставим курсор в указанное поле и при зажатой левой кнопке мыши выделяем соответствующий массив данных на листе. Его координаты тут же отобразятся в поле окна.
В блоке «Группирование» оставляем настройки по умолчанию. То есть, переключатель должен стоять около пункта «По столбцам». Если это не так, то его следует переставить.
Галочку «Метки в первой строке» можно не устанавливать. Для решения нашего вопроса это не важно.
Далее переходим к блоку настроек «Параметры вывода». Здесь следует указать, куда именно будет выводиться результат расчета инструмента «Описательная статистика»:
- На новый лист;
- В новую книгу (другой файл);
- В указанный диапазон текущего листа.
Давайте выберем последний из этих вариантов. Для этого переставляем переключатель в позицию «Выходной интервал» и устанавливаем курсор в поле напротив данного параметра. После этого клацаем на листе по ячейке, которая станет верхним левым элементом массива вывода данных. Её координаты должны отобразиться в поле, в котором мы до этого устанавливали курсор.
Далее следует блок настроек определяющий, какие именно данные нужно вводить:
- Итоговая статистика;
- К-ый наибольший;
- К-ый наименьший;
- Уровень надежности.
Для определения стандартной ошибки обязательно нужно установить галочку около параметра «Итоговая статистика». Напротив остальных пунктов выставляем галочки на свое усмотрение. На решение нашей основной задачи это никак не повлияет.
После того, как все настройки в окне «Описательная статистика» установлены, щелкаем по кнопке «OK» в его правой части.
- После этого инструмент «Описательная статистика» выводит результаты обработки выборки на текущий лист. Как видим, это довольно много разноплановых статистических показателей, но среди них есть и нужный нам – «Стандартная ошибка». Он равен числу 0,505793. Это в точности тот же результат, который мы достигли путем применения сложной формулы при описании предыдущего способа.
Урок: Описательная статистика в Экселе
Как видим, в Экселе можно произвести расчет стандартной ошибки двумя способами: применив набор функций и воспользовавшись инструментом пакета анализа «Описательная статистика». Итоговый результат будет абсолютно одинаковый. Поэтому выбор метода зависит от удобства пользователя и поставленной конкретной задачи. Например, если ошибка средней арифметической является только одним из многих статистических показателей выборки, которые нужно рассчитать, то удобнее воспользоваться инструментом «Описательная статистика». Но если вам нужно вычислить исключительно этот показатель, то во избежание нагромождения лишних данных лучше прибегнуть к сложной формуле. В этом случае результат расчета уместится в одной ячейке листа.
С использованием встроенных функций
Excel расчет доверительного
интервала проводится следующим образом.
1) Рассчитывается среднее значение
=СРЗНАЧ(число1; число2;
…)
число1, число2,
… — аргументы, для которых вычисляется
среднее.
2) Рассчитывается стандартное отклонение
=СТАНДОТКЛОНП(число1; число2;
…)
число1, число2,
… — аргументы, для которых вычисляется
стандартное отклонение.
3) Рассчитывается абсолютная погрешность
=ДОВЕРИТ(альфа ;станд_откл;размер)
альфа —
уровень значимости используемый для
вычисления уровня надежности.
(
,
т.е.
означает надежности
);
станд_откл
— стандартное отклонение, предполагается
известным;
размер — размер выборки.
Лабораторная работа
1
Тема: Обработка прямых
измерений в Excel (2 часа ).
Задание:
Обработать заданный набор экспериментальных
данных методом Стьюдента, построить
экспериментальные кривые методом
наименьших квадратов.
|
Пример |
Используемуе |
|
|
|
Для построения графика используем
мастер диаграмм.
Расчет
погрешности при косвенных измерениях
При измерении
величины косвенным методом предполагается,
что известна математическая модель
связывающая искомую
величину
с величинами
,
измеряемыми непосредственно. Далее
предполагается, выполнена обработка
всех прямых измерений, т. е. определены
доверительные интервалы для величин
:
Погрешность величины у
определяется по формуле:
где
.
Расчет косвенной
погрешности в Maple
Рассмотрим расчет погрешности на примере
функции одной переменной
,
где
Таким образом, найден доверительный
интервал величины
.
В случае, если определяемая в косвенном
измерении величина, является функцией
нескольких переменных, рекомендуем:
-
вычисление погрешности оформить в виде
процедуры
>dy:=proc(y, dx) ……код
процедуры
…… end proc
Код процедуры учащийся должен составить
самостоятельно на основе примера,
рассмотренного выше.
-
параметр dx считать массивом из N
переменных -
для определения списка аргументов и
их количества величины y
можно использовать операторы op() и
nops():
-
Лабораторная работа 2 Тема: Обработка косвенных измерений в Maple (4 часа).
Задание:
Написать программу нахождения погрешности
косвенного измерения в среде Maple.
Выполнение задания
1. Ввести выборку значений измеряемых
величин в матричном виде
2. Определить размерность выборки
3. Задать уровень значимости и определить
степень доверия:
4. Вычислить среднее значение выборки
измеряемой величины:
a) с помощью операций
суммирования
б) с помощью встроенных функций
5. Вычислить значения среднеквадратичного
отклонения.
а) с помощью операций суммирования
,
в) с помощью встроенных функций
6. Вычислить доверительный интервал:
а) Задать коэффициент Стьюдента для
данных размерности выборки и степени
доверия:
.
б) Вычислить абсолютную случайную
погрешность
.
в) Вычислить верхнюю и нижнюю границы
доверительного интервала.
.
7. Учесть приборные погрешности:
а) Задать приборные погрешности
.
б) Вычислить абсолютную случайную
погрешность с учетом приборных
погрешностей
.
8. Представить результат:
а) Абсолютная погрешность:
,
б) Относительная погрешность:
,
в) Верхняя и нижняя границы доверительного
интервала.
.
Примечание. Вычисления провести:
а) в обычном виде,
(См. Дов_инт_01)
б) с помощью операций суммирования,
(См. Дов_инт_02)
в) с помощью встроенных функций.
(См. Дов_инт_03)
2. Вычисление косвенных погрешностей
Выполнение задания
1. Провести аналитические вычисления:
а) Ввести выражение для исследуемой
функции:
,
б) Получить выражение для среднего
значения величины исследуемой функции:
,
в) Получить выражение косвенной
погрешности исследуемой функции в общем
виде и для значения :
,
,
1. Провести численные вычисления:
а) Ввести численные значения постоянных,
б) Ввести средние значения и доверительные
интервалы переменных,
в) Вычислить относительные погрешности
переменных,
г) Вычислить среднее значение исследуемой
функции:
,
г) Вычислить косвенную погрешность
(абсолютную погрешность) исследуемой
функции
,
г) Вычислить относительную погрешность
исследуемой функции
,
в) Вычислить верхнюю и нижнюю границы
доверительного интервала исследуемой
функции:
.
(См. Косв_погр).
3. Построение графиков. Полиномиальная
регрессия
Выполнение задания
1. Ввод выборок значений величин :
2. Вычислить верхнюю и нижнюю границы
доверительного величины Y:
.
3. Полиномиальная регрессия:
а) Задать степень полинома k:
б) Задать число точек данных:
.
в) Задать регрессионную зависимость:
.
г) Определить коэффициенты уравнения
регрессии
:
,
.
4. Построить графики:
а) точечных график данных,
б) кривую регрессии,
в) доверительные интервалы величины Y.
(См. Постр_граф).
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
Содержание
- 0.1 Добавление планки погрешности к ряду данных
- 0.2 Нестандартное использование планок погрешностей в Excel
- 1 Настройка округления как на экране
- 1.1 Включение настройки точности как на экране в современных версиях Excel
- 1.2 Включение настройки точности как на экране в Excel 2007 и Excel 2003
- 1.3 Помогла ли вам эта статья?
- 2 Как же быть?
- 2.1 Какими бывают погрешности
- 2.2 Когда это бывает?
- 3 Наносим линии погрешностей
Ряды данных диаграмм могут включать планки погрешностей, которые предоставляют дополнительную информацию о данных. К примеру, вы можете использовать планки погрешностей для отображения количества ошибок или неопределенностей для каждой точки ряда данных.
На рисунке отображен график Excel с планками погрешностей, которые указывают на диапазон ошибок для каждой точки. В данном случае погрешность основана на процентах – плюс/минус 10 процентов. Планка для первой точки ряда данных (значение 100) находится в пределах от 90 до 110.
Добавление планки погрешности к ряду данных
Чтобы добавить планку погрешности, выделите ряд данных на диаграмме, перейдите по вкладке Работа с диаграммами –> Конструктор в группу Макеты диаграмм, щелкните по кнопке Добавить элемент диаграммы -> Предел погрешностей –> Дополнительные параметры предела погрешностей. К ряду данных будут добавлены планки погрешностей с фиксированным значением (по умолчанию равно 1), слева экрана появится диалоговое окно Формат предела погрешностей.
Если у вас точечная или пузырьковая диаграмма, вы можете определять значения планок погрешностей как для вертикальных, так и для горизонтальных пределов. Чтобы переключиться между ними, необходимо в диалоговом окне Формат предела погрешностей щелкнуть по треугольнику рядом с полем Параметры предела погрешностей и выбрать соответствующий.
Сперва необходимо определиться с направлением планок погрешностей: выше, ниже или в обе стороны от точки ряда данных. Для горизонтальных пределов погрешностей доступны те же опции, только в горизонтальном направлении. Далее определяемся со стилем края, будет ли она ограничиваться чертой на конце планки, либо нет. И выбираем одну из пяти вариантов величины погрешности:
Фиксированное значение: Планка погрешности будет смещена на, указанную вами, фиксированную величину от точки ряда данных. Каждая планка будет одинаковой высоты (или ширины для горизонтальных планок).
Относительное значение: Планка погрешности будет смещена от точки ряда данных на заданных процент от значения точки. К примеру, если вы задали относительное значение равным 5%, а точка ряда данных равна 100, предел погрешности будет находиться в диапазоне от 95 до 105. Т.е. в зависимости от значения точки ряда данных, предел погрешности будет различаться.
Стандартное отклонение: Планки погрешностей будут центрированы по невидимой линии, которая представляет среднее значение ряда данных, выше или ниже на то значение, которое было указано в поле. Для этой опции величина планки погрешностей не зависит от значения точки ряда данных и всегда параллельна оси.
Стандартная погрешность: Размер планки погрешности задается в единицах среднеквадратичной ошибки, которую Excel вычисляет для ряда данных.
Пользовательская: Планки погрешностей определяются значениями диапазона данных, который вы указали. Обычно они содержат формулы.
В Excel 2013 пределы погрешностей можно добавить еще одним способом. Выберите ряд данных и щелкните по иконке справа от диаграммы в виде плюсика. В появившемся справа, выпадающем меню Элементы диаграммы, поставьте галочку напротив поля Предел погрешностей. При необходимости можете задать уточняющие параметры, нажав по стрелке рядом с полем.
Нестандартное использование планок погрешностей в Excel
На самом деле, за все свое знакомство с Excel, я ни разу не воспользовался планками погрешностей по их прямому назначению. Более того, ни разу не встречал людей, которые бы ими пользовались. Если уж дальше развивать эту тему, скажу, что их используют как угодно, но только не так, как задумывалось изначально.
Наиболее распространенный способ использования планок погрешностей в Excel – в виде целевых значений. К примеру, у вас есть пять KPI (показателей эффективности) со своими целевыми значениями и вы хотите отобразить текущее и целевое значение на одном графике. Строим гистограмму с двумя рядами данных.
Меняем тип диаграммы для целевого ряда данных на точечный, чтобы была возможность строить горизонтальные планки погрешностей. Для этого щелкаем правой кнопкой по целевому ряду данных, из выпадающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем тип диаграммы на точечный. Далее к нему добавляем планки погрешностей, любым из описанных выше способов. Удаляем вертикальные пределы. Форматируем планку и точку ряда данных по нашему усмотрению. Получаем диаграмму с пятью фактическими и целевыми значениями.
Данный подход применялся в одной из предыдущих статей, когда мы с вами распределяли показатели на дашборде.
Скачать файл с примерами использования планок погрешностей.
Производя различные вычисления в 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
Включение настройки точности как на экране в современных версиях Excel
Теперь давайте выясним, как включить точность как на экране. Сначала рассмотрим, как это сделать на примере программы Microsoft Excel 2010 и ее более поздних версий. У них этот компонент включается одинаково. А потом узнаем, как запустить точность как на экране в Excel 2007 и в Excel 2003.
- Перемещаемся во вкладку «Файл».
- В открывшемся окне кликаем по кнопке «Параметры».
- Запускается дополнительное окно параметров. Перемещаемся в нем в раздел «Дополнительно», наименование которого значится в перечне в левой части окна.
- После того, как перешли в раздел «Дополнительно» перемещаемся в правую часть окна, в которой расположены различные настройки программы. Находим блок настроек «При пересчете этой книги». Устанавливаем галочку около параметра «Задать точность как на экране».
- После этого появляется диалоговое окно, в котором говорится, что точность вычислений будет понижена. Жмем на кнопку «OK».
После этого в программе Excel 2010 и выше будет включен режим «точность как на экране».
Для отключения данного режима нужно снять галочку в окне параметров около настройки «Задать точность как на экране», потом щелкнуть по кнопке «OK» внизу окна.
Включение настройки точности как на экране в Excel 2007 и Excel 2003
Теперь давайте вкратце рассмотрим, как включается режим точности как на экране в Excel 2007 и в Excel 2003. Данные версии хотя и считаются уже устаревшими, но, тем не менее, их используют относительно немало пользователей.
Прежде всего, рассмотрим, как включить режим в Excel 2007.
- Жмем на символ Microsoft Office в левом верхнем углу окна. В появившемся списке выбираем пункт «Параметры Excel».
- В открывшемся окне выбираем пункт «Дополнительно». В правой части окна в группе настроек «При пересчете этой книги» устанавливаем галочку около параметра «Задать точность как на экране».
Режим точности как на экране будет включен.
В версии Excel 2003 процедура включения нужного нам режима отличается ещё больше.
- В горизонтальном меню кликаем по пункту «Сервис». В открывшемся списке выбираем позицию «Параметры».
- Запускается окно параметров. В нем переходим во вкладку «Вычисления». Далее устанавливаем галочку около пункта «Точность как на экране» и жмем на кнопку «OK» внизу окна.
Как видим, установить режим точности как на экране в Excel довольно несложно вне зависимости от версии программы. Главное определить, стоит ли в конкретном случае запускать данный режим или все-таки нет.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
Некоторые статистические данные могут отображаться на диаграммах, даже без создания отдельных рядов. Многие (но не все) диаграммы позволяют дополнить ряд (ряды) данных полосами погрешностей. Полосы погрешностей отображают дополнительную информацию о данных. Например, их можно использовать для изображения ошибки или неопределенности, связанной с каждой точкой данных.
Например (рис. 1) полосы погрешностей могут изображать диапазоны ошибок измерения каждой точки данных. В этом примере полосы погрешностей выражены в процентах: значение плюс-минус 10% от значения.
Рис. 1. График с полосами погрешностей, выраженных в процентах
Скачать заметку в формате Word или pdf, примеры в формате Excel2013 или Excel2007
Полосы погрешностей поддерживаются рядами следующих типов двухмерных диаграмм:
- диаграммы с областями;
- линейчатые диаграммы;
- гистограммы;
- графики;
- точечные диаграммы;
- пузырьковые диаграммы.
Поскольку точечные и пузырьковые диаграммы имеют две оси значений, полосы погрешностей в них можно выводить как для оси х, так и для оси у (а также для обеих осей).
Добавление полос погрешностей в ряд
Для добавления полос погрешностей выделите ряд данных диаграммы, и пройдите по меню Конструктор – Добавить элемент диаграммы – Предел погрешностей и выберите одну из опций: Стандартная погрешность, Процент или Стандартное отклонение (рис. 2). Если выбрать опцию Дополнительные параметры предела погрешностей, откроется диалоговое окно Формат предела погрешностей (рис. 3). В этом окне, помимо трех упомянутых, можно также задать еще две опции предела погрешностей: Фиксированное значение и пользовательское. На рисунке 3 показ выбор, соответствующий пределу погрешностей, изображенному на рис. 1 – относительное значение 10%.
Рис. 2. Добавление предела погрешностей
Рис. 3. Формат предела погрешностей
Остановимся подробнее на формате полос погрешностей. Полосы могут выводиться над точкой, под точкой или в обоих направлениях от точки данных (область Направление меню Формат предела погрешностей, см. рис. 3). Окончание полосы может быть в виде планки или без оной.
Возможно использование пяти типов предела погрешностей:
- Фиксированное значение. Полосы погрешностей откладываются от каждой точки данных на заданную пользователем фиксированную величину. Все полосы погрешностей имеют одинаковую высоту. Обратите внимание: ошибка выражается не в процентах от значения, а в единицах самого значения (по оси y).
- Относительное значение. Полосы погрешностей откладываются от каждой точки данных на величину, определяемую в процентах от значения точки. Например, если задать в поле ввода значение 5%, а значение точки равно 100, то полоса погрешности будет выведена от 95 до 105. Таким образом, длина полосы погрешности зависит от значения точки.
- Стандартное отклонение. Полосы погрешностей откладываются на величину стандартного отклонения (другое название — среднеквадратическое отклонение), равного корню от суммы квадратов отклонений, деленному на квадратный корень от объема выборки. Для обозначения стандартного отклонения обычно используется символ σ (сигма):где – среднее значение по выборке. Все полосы погрешностей имеют одинаковую высоту и откладываются от среднего значения вверх и вниз на заданное (не обязательно целое) число σ. Обратите внимание: расположение полос погрешностей одинаковое для всех точек на диаграмме, поскольку они откладываются не от конкретной точки, а от среднего всех точек. Пример ниже.
- Стандартная погрешность. Как сообщает справка Excel, полосы погрешностей откладываются от каждой точки на величину: , где ny – число значений в ряду. При этом не сообщается, рассчитанные значения откладываются по обе стороны от точки, или вычисленное значение нужно поделить пополам и только половину отложить в каждую сторону от точки. У меня, кстати, не получилось подтвердить приведенную формулу. Более того, при уменьшении целого ряда значений у, стандартная погрешность только росла… Обратите внимание: размер полос погрешностей одинаковый для всех точек, а вот откладываются полосы погрешностей от каждой отдельной точки (а не от среднего значения по всем точкам)
- Пользовательская. Полосы погрешностей определяются значениями, хранящимися в заданном пользователем диапазоне. Обычно диапазон содержит формулы. Об этом подробнее ниже.
Метод стандартного отклонения проиллюстрирован на рис. 3. Здесь на точечную диаграмму нанесена полоса погрешностей по оси у. В отличие от других типов полос погрешностей полоса типа стандартное отклонение выводится относительно среднего арифметического значения всех точек данных. В примере, показанном на рис. 3, среднее ста точек равно 40, а стандартное отклонение — 10. Поэтому полоса погрешностей выводится вокруг среднего плюс-минус отклонение: 40±10. Благодаря полосе погрешности из диаграммы ясно видно, что большинство точек данных (теоретически 68,2%) отличаются от среднего не более чем на величину стандартного отклонения σ.
На рис. 4 показана точечная диаграмма с полосами погрешностей как по оси у, так и по оси х. Оба набора полос погрешностей выводят для каждого значения соответствующие ошибки — плюс-минус 10%. Полосы погрешностей по осям х и у независимы друг от друга. Для них могут быть установлены разные параметры.
Рис. 4. Точечная диаграмма с полосами погрешностей по осям х и у
Форматирование и модификация полос погрешностей
Для изменения формата полос погрешностей дважды щелкните на любой из них. Появится диалоговое окно Формат предела погрешностей. Во вкладке Заливка и границы можно изменить практически любые параметры планки погрешности (рис. 5).
Рис. 5. Вкладке Заливка и границы диалогового окна Формат предела погрешностей
Делая активной горизонтальную или вертикальную планку погрешности можно в диалоговом окне Формат предела погрешностей выбирать вкладку для параметров X-погрешности или Y-погрешности. В диаграммах, отличных от точечных и пузырьковых, вкладка Х-погрешности отсутствует.
Пользовательские полосы погрешностей
Переключатель пользовательская применяется для создания полос погрешностей, величина которых задастся в ячейках рабочего листа. В большинстве случаев сначала нужно создать формулы, вычисляющие погрешности на основе исходных данных. Затем ячейки с формулами должны быть определены как диапазон (диапазоны), используемый полосами погрешностей.
На рис. 6 показан график, отображающий объемы ежемесячных продаж. Полосы погрешностей изображают изменение объемов продаж по сравнению с предыдущим годом. Если полоса погрешности расположена над точкой данных, то в этом месяце прошлого года объем продаж был выше, а если под точкой — ниже. Такое использование полос погрешностей фактически является альтернативой выводу дополнительного ряда данных.
Рис. 6. Пределы погрешностей изображают объемы продаж в эти же месяцы прошлого года
Столбец D содержит простую формулу, вычисляющую разность данных столбцов В и С. Диапазон D2:D13 используется в качестве диапазона «+», ассоциированного с переключателем пользовательская. Выбран режим вывода Плюс.
Для построения диаграммы выделяем диапазон А1:В13 и вставляем стандартный график с маркерами. Далее добавляем предел погрешности (как на рис. 2). В качестве величины погрешности устанавливаем тип Пользовательская. Жамкаем кнопку Укажите значения, и для Положительное значение ошибки задаем диапазон D2:D13. Поле Отрицательное значение ошибки оставляем пустым.
Заметка написана с использованием материалов книги Джона Уокенбаха Диаграммы в Excel; книга была написана для Excel2003; более поздние издания мне не известны.
В современных версиях наряду с термином полосы погрешностей используются термины планки погрешности и пределы погрешностей. Я буду использовать все эти термины как синонимы.
Недавно я впервые приобрел для дома лицензионную версию MS Excel. Во-первых, очень хотелось поюзать новинки от MS. Во-вторых, MS предлагает вполне бюджетный вариант – Microsoft Office Home and Student 2013 по цене от 2800 руб. (есть Excel, Word, PowerPoint, OneNote + 7ГБ места в облаке!). Так что изложение и иллюстрации основаны на Excel2013.
Такая структура данных была сформирована путем задания в ячейках А1:А100 формулы =НОРМ.ОБР(СЛЧИС();40;10), где СЛЧИС() – вероятность от 0 до 1, 40 –среднее, 10 – стандартное отклонение
Как построить график в Excel с учетом погрешностей? Подобная задача нередко возникает у студента при обработке результатов лабораторных работ. Результаты представляют собой, как правило, два массива данных (в общем случае Х и Y). Пусть, для примера, имеется следующая экспериментальная зависимость:
| Х | 2,0 | 4,0 | 5,0 | 7,0 | 9,0 | 11,0 | 15,0 |
| У | 3,2 | 4,2 | 9,0 | 14,8 | 23,0 | 15,2 | 12,8 |
Требуется представить эту зависимость Y от Х графически. Получится примерно то, что представлено на рисунке.
Например, в ходе лабораторной работы студент измерял зависимость силы тока в одной из ветвей электрической цепи (в Амперах) от напряжения на том или ином ее элементе (в Вольтах). Необходимо отложить данные на графике с учетом погрешностей.
Надо сказать, что сделать построение вручную, как ни парадоксально, в данном случае даже немного проще, чем с использованием, казалось бы, такой мощной и удобной программы, как Excel. Дело в том, что, на самом деле, график, приведенный на предыдущем рисунке… построен неверно.
Почему? Ведь, вроде бы, ничего сложного нет. Нажимаем в Excel кнопку «Мастер диаграмм», Выбираем тип диаграммы – точечная. Затем нажимаем «Далее», задаем массивы для X и Y, затем опять «Далее»… — и вскоре получится то, что приведено на предыдущем рисунке.
Вроде бы, все правильно. Да только график построен строго по точкам. Видно, что зависимость немонотонная, с достаточно острым максимумом – что не всегда имеет место в реальности. Ибо в реальности подавляющее большинство зависимостей могут быть более плавными.
Конечно, здесь надо смотреть, какие конкретно показатели анализируются. Если это, к примеру, динамика биржевого курса ценных бумаг; или – динамика поступления платежей от покупателей, равно как и другой аналогичный экономический показатель, то, конечно, можно с уверенностью сказать, что график вовсе не обязан быть плавным. В самом деле, очень многие экономические показатели меняются, как правило, скачкообразно.
А вот если анализировать данные, скажем, из области психологии, физики, биологии (отчасти), а также из ряда иных отраслей, то там графики экспериментальных зависимостей, за немногим исключением, зачастую являются достаточно плавными (хотя, все относительно, конечно).
Но, вроде бы, и здесь нет никаких проблем: следует провести линию тренда, которых Excel предлагает несколько типов. Так, можно выбрать линейный, степенной, экспоненциальный и т.д. тренд.
Можно… На примере выбранных нами данных, выбор показал, что наиболее близким для выбранной совокупности данных является параболический тренд. Отобразим его на рисунке.
Вроде бы, то, что проведено черной линией (тренд), уже гораздо ближе к истине. Правда, совсем ненамного. Фактически, проведенный тренд достаточно хорошо соответствует исходным данным только для 3, 4 и 7-й точек. Для остальных точек имеются существенные расхождения, причем ошибка доходит до 80%.
Ясно, что построенный тренд, в силу его высокой погрешности, в данном случае никак нельзя принять в качестве качественного графика, отражающего ход зависимости, выявленной экспериментально. Однако, и первоначально построенную (синюю) линию, вероятно, также нельзя принять в качестве такового, ибо, повторимся, она вообще не учитывает погрешностей.
Как же быть?
Конечно, есть возможность провести интерполяцию и уже с ее учетом определить функцию, которая будет наиболее плавной (с учетом погрешностей) и построить ее в качестве графика. Однако, это – задача достаточно сложная, представляющая собой предмет отдельного разговора. Скажем, в том же MatLab, конечно, существуют подобные функции, при помощи которых можно это реализовать.
Однако, тогда, в любом случае, придется писать программу (в MatLab это будет проще, в Excel – посложнее, ибо там придется программировать вручную готовые функции, которые уже имеются в MatLab). А для студента, которому требуется обработать данные лабораторной или (реже) контрольной работы, это может являться сложной, отдельной задачей. В самом деле, вместо анализа результатов он вынужден вначале думать, каким образом построить эти результаты, а потом программировать. Добро, если лабораторная работа проходит на старшем курсе. Однако, зачастую, студент делает их, начиная с самого начала своего обучения, т.е. когда опыта математической обработки, не говоря уже об умении строить интерполяционные многочлены, нет вообще.
Какими бывают погрешности
Погрешности бывают, в общем случае, относительные (в процентах) и абсолютные (выраженные в единицах измеряемой величины). Кроме того, они могут иметь постоянное значение или переменное.
Надо сказать, что старые версии программы Excel не позволяют просто так нанести погрешность на график. А вот, начиная, по крайней мере, с версии Excel 2007, это можно сделать достаточно просто. Вот пример, как наносятся погрешности в Excel.
Да, все вроде бы, замечательно. Но только график, приведенный на сайте (по указанной ссылке), также построен, строго говоря, неверно. Дело в том, следует повторить, что график должен представлять собой, по возможности, плавную линию. Которая где-то пройдет через середину интервала погрешности, а где-то, быть может, через один из его краев. Так вот, стандартные средства Excel даже самых новых версий не позволяют построить такой график автоматически. Тем более, если идет речь о том, что каждая экспериментальная точка может иметь, вообще говоря, разную погрешность.
Когда это бывает?
Например, в случае, когда разные экспериментальные точки были получены на базе исследований, проведенных разными методами.
Например, взять эксперименты по измерению зависимости внутреннего трения (т.е. степени перехода механической энергии в тепловую при упругих деформациях) материалов от частоты упругой деформации.
- Если частота равна нулю (т.е. происходит равномерная деформация материала), то необходимо применять установки для растяжения/сжатия, способные измерить работу, затраченную на нагрев материала в процессе деформации.
- Если говорить о частоте, когда ее значения лежат в пределах 10 Гц… 1000 Гц, то такие измерения проводятся при помощи совсем других установок, например, с использованием так называемых крутильных маятников (т.е. когда образец совершает вынужденные крутильные колебания заданной частоты).
- Если вести речь о диапазоне частот 20 кГц… 200 кГц то здесь необходимо применять ультразвуковые установки.
- Наконец, исследования при гиперзвуковых частотах (более 109 Гц) проводятся при помощи оптических, пьезоэлектрических методов.
Таким образом, даже ориентировочное рассмотрение выявило, что существуют, как минимум, четыре основных диапазона частот, для каждого из которых может быть применен какой-либо свой метод исследования, кардинально отличающийся от других. Соответственно, у каждого из методов может быть свое значение погрешности (относительной или абсолютной).
Как построить плавный график в Excel с учетом погрешностей?
Итак, как построить нормальный, правильный график? Который, с одной стороны, был бы, по возможности, плавным (т.е. содержал бы поменьше нестабильных, немонотонных участков — в пределах погрешности, конечно), а с другой – лежал бы в рамках допустимых погрешностей.
Рассмотрим самый сложный, общий случай – разных погрешностей для каждой из экспериментальных точек и покажем, как можно применить Excel для того, чтобы построить корректный график зависимости.
Пусть погрешности экспериментальных точек равны следующим значениям:
±10%; ±7%; ±5%; ±12%; ±20%; ±17%; ±23%.
Ясно, что как относительная, так и абсолютная погрешности данных будут разными для каждой из экспериментальных точек. Однако, для каждой из них можно определить минимальное и максимальное значения (через интервал которых и должен пройти график). Проведем такой расчет (благо, при помощи Excel это сделать очень легко и быстро):
| Х | 2,0 | 4,0 | 5,0 | 7,0 | 9,0 | 11,0 | 15,0 |
| Y | 3,2 | 4,2 | 9,0 | 14,8 | 23,0 | 15,2 | 12,8 |
| Относительная погрешность (для Y), % | 10 | 7 | 5 | 12 | 20 | 17 | 23 |
| Минимальное значение Y | 2,9 | 3,9 | 8,6 | 13,0 | 18,4 | 12,6 | 9,9 |
| Максимальное значение Y | 3,5 | 4,5 | 9,5 | 16,5 | 27,6 | 17,8 | 15,8 |
Минимальные и максимальные значения Y образуют допустимый интервал (диапазон), в котором может находиться график. Отложим этот диапазон на рисунке.
Нижняя граница диапазона показана зеленой линией, верхняя – черной. Толстая черная линия – это ранее нанесенные тренд. Четко видно, что тренд (даже, повторимся, оптимальный, выбранный из предлагаемого программой Excel перечня) только в двух (из семи) экспериментальных точках проходит в пределах допустимой области. Следовательно, необходимо отказаться от него. Удалим его с рисунка.
Итак, построена допустимая область. Искомый график зависимости должен лежать внутри нее, при этом имея, по возможности, наиболее плавный вид.
Его построение (если расчет оптимального интерполяционного многочлена вызывает сложность) проще провести вручную. Т.е. на бумаге полученный рисунок и уже на нем нанести график. Но, вполне возможно это сделать и при помощи компьютера – чтобы вообще не возиться с пишущими принадлежностями, причем сделать это можно очень быстро.
Для этого удобнее всего использовать программу Inkscape. Она является совершенно бесплатной, если у Вас она еще не установлена, можете скачать ее с официального сервера.
Устанавливаем ее, затем открываем. Копируем рисунок (отметим, лучше это сделать не из Word, а из первоисточника, т.е. из Excel).
Затем, как обычно, нажимаем кнопку «Вставить», размещаем рисунок примерно по центру рабочей области. Если ее размеры не совпадают с рисунком, нужно ее настроить, для чего нажимаем «Файл», «Свойства документа» и там устанавливаем альбомную ориентацию и указываем требуемые размеры (параметры «Ширина» и «Высота»). Примерно вот что должно получиться в итоге.
Так как Excel создает диаграммы в векторном виде, они без проблем редактируются в Incscape. Таким образом, наша задача – вручную таким образом исправить синюю линию, чтобы она стала как можно более плавной.
Нажав клавишу F2, затем, удерживая «Shift», кликаем мышкой по элементам синей кривой так, чтобы на ней появились серые узлы. Когда они появятся на каждом из ее участков, это означает, что мы выделили ее ВСЮ в режиме редактирования узлов.
Затем нажимаем в меню пункт «Контур», «Упростить». Число узлов значительно снизится, останутся лишь основные. Кстати, если кликнуть по каждому узлу в отдельности, его можно будет стереть путем нажатия клавиши «Delete». Но, это будет достаточно долго, поэтому проще использовать команду «Упростить».
Затем, удерживая левую кнопку мыши на соответствующем узле, двигаем его в ту или иную сторону. Так повторяем с другими узлами (при необходимости скорректировать кривизну линии в конкретном узле, можно также двигать рычаги каждого из них).
В итоге линия принимает вид, показанный на рисунке.
Для наглядности (чтобы можно было помнить, где проходила линия графика, построенная в Excel), экспериментальные точки, в виде синих ромбиков, оставлены.
Вставить рисунок из Incscape в Word достаточно просто – как обычно: нажимаем «Правка», «Выделить все» (как уже говорилось, рабочая область должна быть подогнана к размерам рисунка; впрочем, можно и рисунок, путем его деформаций, подогнать к размерам рабочей области программы Inkscape), затем – «Скопировать».
Проверяем, что рисунок выделился, как полагается. Тогда переходим в Word и выполняем вставку из буфера обмена, как обычно, путем нажатия кнопки «Вставить» на панели инструментов. Но, учтите, что, к сожалению, исправить в Excel измененную диаграмму уже не получится (она вставится, как рисунок). Поэтому все, что необходимо сделать на диаграмме в Excel, надо делать заранее, ДО ТОГО, т.е. до преобразования ее в Inkscape. Вместе с тем, при желании, полученный рисунок можно вновь скопировать в Inkscape провести, если нужно, дальнейшее его редактирование.
Таким образом, мы получили сглаженный, более плавный график, чем тот, который был построен автоматически в Excel. Даже не взяв в руки карандаш или иной пишущий инструмент: использовались лишь некоторые клавиши и мышь.
Наносим линии погрешностей
Что же касается линий погрешностей, то их можно провести, используя соответствующие возможности Excel (если он у Вас, как минимум, версии 2007 г.). Правда, так как величина погрешности в нашем примере для каждой из точек – разная, то возможности Excel по построению погрешностей здесь помогут мало.
Так что, в данном случае, можно начертить их вручную (т.е. нарисовать мышкой соответствующие вертикальные линии, проходящие через каждую экспериментальную точку).
Однако, это, на наш взгляд, утомительно. Поэтому, если совсем не хочется возиться с ручным рисованием, выход вполне есть – их можно выполнить, нарисовав соответствующее (в данном случае 7) дополнительных графиков. Повторимся, это следует сделать ДО того, как преобразовывать диаграмму в Inkscape.
Итак, заходим на вкладку «Диаграмма». Нажимаем в панели инструментов «Диаграмма», «Исходные данные». Выбираем вкладку «Ряд», затем нажимаем «Добавить».
Далее, жмем кнопку с красной стрелочкой – там, где значения Х. Переходим на Лист, на котором находятся данные, по которым построена эта диаграмма и указываем, к примеру, четвертое значение Х (равное 7). Затем (внимание!), удерживая клавишу «Ctrl», кликаем вновь мышкой по тому же самому значению Х (т.е. по Х, равном 7). В итоге в строке, где задается адрес диапазона значений Х, адрес ячейки, в которой расположена цифра 7, должен присутствовать ДВАЖДЫ, через точку с запятой. Убедившись в этом, нажимаем, как обычно, красную стрелочку.
Теперь выбираем значения Y. После нажатия красной стрелочки, перейдя на нужный Лист, кликаем на минимальное значения Y, соответствующее Х, равному 7. Затем, удерживая клавишу «Ctrl», кликаем максимальное значение Y. В итоге, через точку с запятой в адресной строке должны появиться адреса двух ячеек, соответствующие минимальному и максимальному значению Y (для Х=7).
Наконец, переходим на диаграмму, дважды кликаем мышкой на получившемся вертикальном отрезке с маркерами, зайдя, тем самым, в свойства только что построенного отрезка. Там устанавливаем вид маркера в виде знака минус («-»). Потом устанавливаем, к примеру, черные цвета линий и маркера и нажимаем «ОК». Результат наших действий приведен на рисунке.
Видим, что для точки Х=7 появилась линия погрешности. Кстати, ширину этой линии можно, при желании, изменять — путем изменения размера маркера.
Поступая таким же образом со всеми остальными точками, можно вскоре построить линии погрешностей для каждой из них (кому не хочется выполнять эту нудную работу, если точек много, можно написать несложный макрос — пишется один раз; правда, некоторым студентам, например, учашимся на младших курсах, это, возможно, будет затруднительно, поэтому проще будет сделать так, как говориось выше). А линии, ограничивающие диапазоны минимальных и максимальных значений (зеленую и черную, соответственно) можно, кстати, потом удалить, если они не нужны. Тогда график примет полностью стандартный вид.
Его можно потом перенести в Inkscape и придать графику (синяя линия) более плавный вид.
С уважением к Вам.
По территориям региона приводятся данные за 200Х г.
| Номер региона | Среднедушевой прожиточный минимум в день одного трудоспособного, руб., х | Среднедневная заработная плата, руб., у |
|---|---|---|
| 1 | 78 | 133 |
| 2 | 82 | 148 |
| 3 | 87 | 134 |
| 4 | 79 | 154 |
| 5 | 89 | 162 |
| 6 | 106 | 195 |
| 7 | 67 | 139 |
| 8 | 88 | 158 |
| 9 | 73 | 152 |
| 10 | 87 | 162 |
| 11 | 76 | 159 |
| 12 | 115 | 173 |
Задание:
1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.
2. Рассчитайте параметры уравнения линейной регрессии
.
3. Оцените тесноту связи с помощью показателей корреляции и детерминации.
4. Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.
5. Оцените с помощью средней ошибки аппроксимации качество уравнений.
6. Оцените с помощью F-критерия Фишера статистическую надёжность результатов регрессионного моделирования.
7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости 
.
8. Оцените полученные результаты, выводы оформите в аналитической записке.
Решение:
Решим данную задачу с помощью Excel.
1. Сопоставив имеющиеся данные х и у, например, ранжировав их в порядке возрастания фактора х, можно наблюдать наличие прямой зависимости между признаками, когда увеличение среднедушевого прожиточного минимума увеличивает среднедневную заработную плату. Исходя из этого, можно сделать предположение, что связь между признаками прямая и её можно описать уравнением прямой. Этот же вывод подтверждается и на основе графического анализа.
Чтобы построить поле корреляции можно воспользоваться ППП Excel. Введите исходные данные в последовательности: сначала х, затем у.
Выделите область ячеек, содержащую данные.
Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.
Рисунок 1 Построение поля корреляции
Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.
2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН.
Для этого:
1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;
Рисунок 2 Диалоговое окно «Мастер функций»
5) Заполните аргументы функции:
Известные значения у – диапазон, содержащий данные результативного признака;
Известные значения х – диапазон, содержащий данные факторного признака;
Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;
Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.
Щёлкните по кнопке ОК;
Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН
6) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем на комбинацию клавиш <Ctrl>+<Shift>+<Enter>.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
| Значение коэффициента b | Значение коэффициента a |
| Стандартная ошибка b | Стандартная ошибка a |
| Коэффициент детерминации R2 | Стандартная ошибка y |
| F-статистика | Число степеней свободы df |
| Регрессионная сумма квадратов
|
Остаточная сумма квадратов
|
Рисунок 4 Результат вычисления функции ЛИНЕЙН
Получили уровнение регрессии:
Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.
3. Коэффициент детерминации 
означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х – среднедушевого прожиточного минимума, а 48% — действием других факторов, не включённых в модель.
По вычисленному коэффициенту детерминации можно рассчитать коэффициент корреляции: 
.
Связь оценивается как тесная.
4. С помощью среднего (общего) коэффициента эластичности определим силу влияния фактора на результат.
Для уравнения прямой средний (общий) коэффициент эластичности определим по формуле:
Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее, и то же самое произведём со значениями у.
Рисунок 5 Расчёт средних значений функции и аргумент
Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.
С помощью инструмента анализа данных Регрессия можно получить:
— результаты регрессионной статистики,
— результаты дисперсионного анализа,
— результаты доверительных интервалов,
— остатки и графики подбора линии регрессии,
— остатки и нормальную вероятность.
Порядок действий следующий:
1) проверьте доступ к Пакету анализа. В главном меню последовательно выберите: Файл/Параметры/Надстройки.
2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.
3) В окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.
• Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.
• Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.
4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия, а затем нажмите кнопку ОК.
5) Заполните диалоговое окно ввода данных и параметров вывода:
Входной интервал Y – диапазон, содержащий данные результативного признака;
Входной интервал X – диапазон, содержащий данные факторного признака;
Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;
Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;
Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;
6) Новый рабочий лист – можно задать произвольное имя нового листа.
Затем нажмите кнопку ОК.
Рисунок 6 Диалоговое окно ввода параметров инструмента Регрессия
Результаты регрессионного анализа для данных задачи представлены на рисунке 7.
Рисунок 7 Результат применения инструмента регрессия
5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.
Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»
Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:
Рисунок 9 Расчёт средней ошибки аппроксимации
Средняя ошибка аппроксимации рассчитывается по формуле:
Качество построенной модели оценивается как хорошее, так как 
не превышает 8 – 10%.
6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера: 
Поскольку 
при 5%-ном уровне значимости, то можно сделать вывод о значимости уравнения регрессии (связь доказана).
8. Оценку статистической значимости параметров регрессии проведём с помощью t-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.
Выдвигаем гипотезу Н0 о статистически незначимом отличии показателей от нуля:
.
для числа степеней свободы 
На рисунке 7 имеются фактические значения t-статистики:
t-критерий для коэффициента корреляции можно рассчитать двумя способами:
I способ: 
где 
– случайная ошибка коэффициента корреляции.
Данные для расчёта возьмём из таблицы на Рисунке 7.
II способ: 
Фактические значения t-статистики превосходят табличные значения:
Поэтому гипотеза Н0 отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы.
Доверительный интервал для параметра a определяется как
Для параметра a 95%-ные границы как показано на рисунке 7 составили:
Доверительный интервал для коэффициента регрессии определяется как
Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:
Анализ верхней и нижней границ доверительных интервалов приводит к выводу о том, что с вероятностью 
параметры a и b, находясь в указанных границах, не принимают нулевых значений, т.е. не являются статистически незначимыми и существенно отличны от нуля.
7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит:
Тогда прогнозное значение прожиточного минимума составит:
Ошибку прогноза рассчитаем по формуле:
где 
Дисперсию посчитаем также с помощью ППП Excel. Для этого:
1) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
2) В окне Категория выберете Статистические, в окне функция – ДИСП.Г. Щёлкните по кнопке ОК.
3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК.
Рисунок 10 Расчёт дисперсии
Получили значение дисперсии 
Для подсчёта остаточной дисперсии на одну степень свободы воспользуемся результатами дисперсионного анализа как показано на Рисунке 7.
Доверительные интервалы прогноза индивидуальных значений у при 
с вероятностью 0,95 определяются выражением:
Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.
Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. – М.: Финансы и статистика, 2003. – 192 с.: ил.