Приложение Н (рекомендуемое). Инструкция по подбору параметров нормального распределения в MS Excel

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

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

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

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

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

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

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

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

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

, (Н.1)

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

- определяют по формуле

. (Н.2)

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

;

.

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

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

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

; - номера ячеек, содержащих значения соответствующих параметров;

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

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

= ^2,

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

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

В ячейку, соответствующую следующей  после  окончания таблицы  строке,  и

графу 5 таблицы результатов контроля заносят значение  суммы  графы  5  с

               /-----\

помощью кнопки |сумма| и путем указания диапазона ячеек графы 5 с помощью

               \-----/

курсора мыши (рисунок Н.3).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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