Инструкция по подбору параметров нормального распределения в MS Excel

На лист MS Excel в таблицу результатов контроля заносятся следующие данные (рисунок Н.1):

- столбец 1 – истинные значения контролируемого параметра образцов;

- столбец 2 – количество попыток, в которых образец был признан соответствующим;

- столбец 3 – заранее рассчитанная вероятность признания образца соответствующим.

Рисунок Н.1 – Пример заполнения таблицы результатов контроля

Отдельно от таблицы результатов контроля на лист MS Excel заносятся приближенные значения параметров m и s нормального распределения
(см. рисунок Н.1), аппроксимирующего распределение вероятности признания образцов соответствующими.

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

- m определяется как истинное значение параметра образца , для которого вероятность признания образца соответствующим равна 0,5. Если образец, имеющий такое значение параметра, отсутствует, то m рассчитывается по формуле:

, (Н.1)

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

- s определяется по формуле:

. (Н.2)

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

;

.

В столбец 4 таблицы результатов контроля заносятся значения аппроксимирующего нормального распределения (рисунок Н.2), рассчитанные по формуле:

=НОРМРАСП( ;m;s;ИСТИНА) (Н.3)

где – номер ячейки, содержащей предполагаемое истинное значение параметра контролируемого образца, находящейся в той же строке, в столбце 1 таблицы результатов контроля;

m, s – номера ячеек, содержащих значения соответствующих параметров.

«ИСТИНА» – параметр, определяющий применение интегральной функции нормального закона распределения.

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

(Н.3)

где – номер ячейки, содержащей находящейся в той же строке, в столбце 4 таблицы результатов контроля;

– номер ячейки, содержащей находящейся в той же строке, в столбце 3 таблицы результатов контроля.

В ячейку, соответствующую следующей после окончания таблицы строке и столбцу 5 таблицы результатов контроля заносится значение суммы столбца 5 с помощью кнопки и указания диапазона ячеек столбца 5 с помощью курсора мыши (рисунок Н.3).

Рисунок Н.2 – Схема заполнения столбца 4 таблицы результатов контроля

Рисунок Н.3 – Схема вычисления суммы столбца 5

Для поиска аппроксимирующего нормального распределения методом наименьших квадратов необходимо выбрать пункт «Сервис» главного меню, затем подпункт «Поиск решения» (рисунок Н.4). При том на экране отобразится диалоговое окно «Поиск решения» (рисунок Н.5).

Рисунок Н.4 – Выбор подпункта «Поиск решения»

Рисунок Н.5 – Диалоговое окно «Поиск решения»

В поле ввода «Установить целевую» следует указать номер ячейки, в которой содержится сумма столбца 5.

Переключатель «Равной» следует установить на поле «минимальному значению» (см. рисунок Н.5).

В поле ввода «Изменяя ячейки» указываются номера ячеек, содержащих значения m и s.

В поле ввода «Ограничения:» вводится дополнительное ограничение. Для этого необходимо нажать кнопку «Добавить» справа от поля ввода «Ограничения:», после чего на экране отобразится диалоговое окно «Добавление ограничения» (рисунок Н.6).

В поле ввода «Ссылка на ячейку:» указывается номер ячейки, содержащей значение s.

В поле выбора знака с помощью раскрывающегося меню выбрать знак «>=« (рисунок Н.7).

Рисунок Н.6 – Диалоговое окно «Добавление ограничения»

Рисунок Н.7 – Выбор знака в окне «Добавление ограничения»

В поле ввода «Ограничение:» следует с клавиатуры набрать «0».

После ввода всех параметров ограничения следует нажать кнопку <ОК> в левом нижнем углу диалогового окна, после чего произойдет возврат в диалоговое окно «Поиск решения», введенное ограничение отобразится в поле ввода «Ограничения».

В диалоговом окне «Поиск решения» после заполнения всех поле ввода для подбора параметров нормального распределения следует нажать кнопку <Выполнить> в верхнем правом углу диалогового окна. После завершения расчетов на экране отобразится окно «Результаты поиска решения» (рисунок Н.8).

Рисунок Н.8 – Диалоговое окно «Результаты поиска решения»

В диалоговом окне «Результаты поиска решения» следует выбрать опцию «Сохранить найденное решение» и нажать на кнопку <OK> в левом нижнем углу диалогового окна.

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

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

Для этого следует, удерживая нажатой клавишу <Ctrl>, мышью выделить столбцы 1, 3 и 4 таблицы результатов контроля.

Для построения диаграммы по выбранным столбцам необходимо выбрать пункт «Вставка» главного меню, затем подпункт «Диаграмма» (рисунок Н.9). При том на экране отобразится диалоговое окно «Мастер диаграмм» (рисунок Н.10).

Рисунок Н.9 – Выбор подпункта «Диаграмма»

Рисунок Н.10 – Диалоговое окно «Мастер диаграмм»

В диалоговом окне «Мастер диаграмм» следует выбрать тип диаграммы «Точечная» и вид «Точечная диаграмма со значениями, соединенными сглаживающими линиями» (см. рисунок Н.10).

После выбора нужного типа диаграммы рекомендуется нажать кнопку «Готово», после чего диаграмма будет отображена на листе MS Excel.

Приложение П
(обязательное)

Протокол анализа смещения и сходимости средства контроля № ______

Приложение Р
(рекомендуемое)

Таблица значений распределения Стьюдента

В таблице Р.1 приведены значения t-распределения (распределения Стьюдента).

Таблица Р.1

Число степеней свободы k	Уровень значимости a (двусторонняя критическая область)	Число степеней свободы k	Уровень значимости a (двусторонняя критическая область)	Число степеней свободы k	Уровень значимости a (двусторонняя критическая область)
0,10	0,05	0,10	0,05	0,10	0,05
	6,31	12,71		1,77	2,16		1,71	2,06
	2,92	4,30		1,76	2,14		1,71	2,06
	2,35	3,18		1,75	2,13		1,70	2,05
	2,13	2,78		1,75	2,12		1,70	2,05
	2,02	2,57		1,74	2,11		1,70	2,05
	1,94	2,45		1,73	2,10		1,70	2,04
	1,89	2,36		1,73	2,09		1,68	2,02
	1,86	2,31		1,72	2,09		1,67	2,00
	1,83	2,26		1,72	2,08		1,66	1,98
	1,81	2,23		1,72	2,07	¥	1,64	1,96
	1,80	2,20		1,71	2,07	–	–	–
	1,78	2,18		1,71	2,06	–	–	–
	0,05	0,025		0,05	0,025		0,05	0,025
Уровень значимости a (односторонняя критическая область)	Уровень значимости a (односторонняя критическая область)	Уровень значимости a (односторонняя критическая область)

Приложение С
(обязательное)

Протокол исследования средства контроля
экспресс–методом № ______

Приложение Т
(справочное)

Библиография

[1] Русско-англо-французско-немецко-испанский словарь основных и общих терминов в метрологии. Перевод с англ., франц. / Л.К. Исаев, В.В. Мардин. – М.: ИПК «Издательство стандартов», 1998г. – 160 с.

[2] Словарь по качеству под редакцией Адлера Ю.П.; – Самара: НВФ «Сенсоры. Модули. Системы», 1999. – 76 с.

УДК 658:311:006.354 ОКС 03.120.30 Т59 ОКСТУ 0011

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

Исполнители:

От АО «АВТОВАЗ»

Зам. директора по качеству,
канд.техн.наук Г.Л.Юнак

Начальник бюро подготовки и анализа

метрологического обеспечения А.Н. Кузенков

От ЗАО «Академический инжиниринговый центр»

Научный руководитель,

доктор техн.наук. В.Е.Годлевский

Ведущий специалист Е.А.Шабанова

Ведущий специалист О.В.Разживина

От ООО СМЦ «Приоритет»

Руководитель рабочей группы,

главный специалист Е.П.Кочетков

От ОАО «НИЦ КД»

Руководитель рабочей группы,

начальник НИО, канд.техн.наук И.В. Гоноскова

Начальник НИС А.А. Романов