Статья опубликована в рамках: LXXIII Международной научно-практической конференции «Экспериментальные и теоретические исследования в современной науке» (Россия, г. Новосибирск, 26 января 2022 г.)

Наука: Математика

Скачать книгу(-и): Сборник статей конференции

Библиографическое описание:

Ковальчук В.В., Бурзун М.С. РАСЧЕТ СРЕДСТВАМИ EXCEL МЕТОДА НАИМЕНЬШИХ КВАДРАТОВ В ОЦЕНКЕ ПАРАМЕТРОВ НАДЕЖНОСТИ ПРИ НОРМАЛЬНОМ ЗАКОНЕ РАСПРЕДЕЛЕНИЯ ОТКАЗОВ // Экспериментальные и теоретические исследования в современной науке: сб. ст. по матер. LXXIII междунар. науч.-практ. конф. № 1(66). – Новосибирск: СибАК, 2022. – С. 10-18.

Проголосовать за статью

Дипломы участников

У данной статьи нет
дипломов

РАСЧЕТ СРЕДСТВАМИ EXCEL МЕТОДА НАИМЕНЬШИХ КВАДРАТОВ В ОЦЕНКЕ ПАРАМЕТРОВ НАДЕЖНОСТИ ПРИ НОРМАЛЬНОМ ЗАКОНЕ РАСПРЕДЕЛЕНИЯ ОТКАЗОВ

Ковальчук Владимир Васильевич

проф. кафедры математики, информационных систем и программного обеспечения, Мурманского государственного технического университета,

РФ, г. Мурманск

Бурзун Марина Сергеевна

аспирант кафедры математики, информационных систем и программного обеспечения, Мурманского государственного технического университета,

РФ, г. Мурманск

CALCULATION BY MEANS OF EXCEL OF THE LEAST SQUARES METHOD IN THE ESTIMATION OF RELIABILITY PARAMETERS UNDER THE NORMAL LAW OF DISTRIBUTION OF FAILURES

Vladimir Kovalchuk

professor, Department of Mathematics, Information Systems and Software, Murmansk State Technical University,

Russia, Murmansk

Marina Burzun

graduate student, Department of Mathematics, Information Systems and Software, Murmansk State Technical University,

Russia, Murmansk

АННОТАЦИЯ

В статье показан пример расчета показателей надежности работы системы методом наименьших квадратов средствами Excel..

ABSTRACT

The article shows an example of calculating the reliability indicators of the system by the least squares method using Excel.

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

Keywords: uptime, confidence interval, tests, least squares method, normal distribution law, number of failures.

При нормальном законе распределения отказов сначала необходимо определить оценки математического ожидания и среднеквадратичного отклонения, а затем рассчитать вероятность отказа, частоту и интенсивность отказов [1].

Для определения математического ожидания и среднеквадратичного отклонения для каждого разряда статистического ряда подсчитывают q_i с использованием выражения и по таблице квантилей нормального закона распределения определяют значения квантилей [2, 3].

Листинг фрагмента программы расчета показателей при нормальном законе распределения:

'Вычислим 11 строку таблицы(13)=============================qi

СтрокаТаблицы = 13

СтолбецТаблицы = 4

СуммаВышедшихЗаПериод = 0

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

СуммаВышедшихЗаПериод = СуммаВышедшихЗаПериод + Sheets("ОсновнаяТаблица").Cells(4, n).Value

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, n).Value = СуммаВышедшихЗаПериод / КоличествоЭлементов

'Вычислим 12 строку таблицы(14)=============================p

СтрокаТаблицы = 14

СтолбецТаблицы = 4

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, n).Value = 1 - Sheets("ОсновнаяТаблица").Cells(13, n).Value

'Вычислим 13 строку таблицы(15)=============================Uqi

СтрокаТаблицы = 15

СтолбецТаблицы = 4

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

p = Sheets("ОсновнаяТаблица").Cells(14, n).Value

СтрокаТаблицыПриложения = 2

' проверим попадают ли входные данные в значения таблицы

While Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 1).Value <> ""

СтрокаТаблицыПриложения = СтрокаТаблицыПриложения + 1

Wend

If p <= Sheets("Квантили норм распределения").Cells(2, 1).Value Then

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, n).Value = Round(Sheets("Квантили норм распределения").Cells(2, 2).Value * (-1), 3)

GoTo СледующийПоиск

End If

If p >= Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения - 1, 1).Value Then

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, n).Value = Round(Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения - 1, 2).Value * (-1), 3)

GoTo СледующийПоиск

End If

СтрокаТаблицыПриложения = 2

While Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 1).Value <> ""

If Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 1).Value = p Then

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, n).Value = Round(Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 2).Value * (-1), 3)

GoTo СледующийПоиск

End If

If p < Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 1).Value And p > Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения - 1, 1).Value Then

If Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения, 1).Value - p < p - Sheets("Квантили норм распределения").Cells(СтрокаТаблицыПриложения - 1, 1).Value Then

Else

End If

GoTo СледующийПоиск

End If

СтрокаТаблицыПриложения = СтрокаТаблицыПриложения + 1

Wend

СледующийПоиск:

Результаты расчета представлены в таблице Excel (Таблица 1).

Таблица 1.

Результаты расчета основных показателей испытаний

	Разряды
	1	2	3	4	5	6	7	8	9	10
qi	0,05	0,08	0,13	0,15	0,17	0,2	0,23	0,26	0,31	0,34
Uqi	-1,645	-1,405	-1,126	-1,036	-0,954	-0,842	-0,739	-0,643	-0,496	-0,412

В случае усеченной выборки, при проведении испытаний, когда в результате испытаний получены r значений наработки (r < N) для отказавших изделий t₁ , …, t_r, а N – r изделий остались исправными после испытаний, параметры T_ср и σ можно оценить по методу квантилей.

(1)

(2)

Считая, что за время t_i вероятность выхода из строя испытуемых изделий составляет , где i– число отказавших изделий за время t_i. Для этой вероятности находят квантили Uq_i , по таблице квантилей нормального распределения и составляют систему уравнений:

(3)

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

(4)

Второе нормальное уравнение получают суммированием исходной системы уравнений (3)

(5)

Уравнения (4) и (5) решают относительно неизвестных T*_ср и σ* и находят их оценки.

'Составляем системы уравнений

СтолбецТаблицы = 4

СтрокаПВ = 1

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value = Sheets("ОсновнаяТаблица").Cells(3, n).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 2).Value = "="

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Value = 1

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 4).Value = "Тср"

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Value = Sheets("ОсновнаяТаблица").Cells(15, n).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 6).Value = Sheets("Оформление").Cells(1, 5).Value

СтрокаПВ = СтрокаПВ + 1

'Суммируем систему уравнений

СтрокаПВ = 1

СуммаВремИнтервалов = 0

СуммаТср = 0

СуммаСигм = 0

While Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value <> ""

СуммаВремИнтервалов = СуммаВремИнтервалов + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value

СуммаТср = СуммаТср + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Value

СуммаСигм = СуммаСигм + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Value

СтрокаПВ = СтрокаПВ + 1

Wend

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value = СуммаВремИнтервалов

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 2).Value = "="

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 2).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Value = СуммаТср

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 4).Value = "Тср"

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 4).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Value = СуммаСигм

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 6).Value = Sheets("Оформление").Cells(1, 5).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 6).Font.Bold = True

'Умножаем на соответствующий квантиль uqi левые и правые части уравнений, получим новую систему уравнений.

СтолбецТаблицы = 4

СтрокаПВ = 1

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value * Sheets("ОсновнаяТаблица").Cells(15, n).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 11).Value = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 2).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Value = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Value * Sheets("ОсновнаяТаблица").Cells(15, n).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 13).Value = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 4).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Value = Round(Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Value * Sheets("ОсновнаяТаблица").Cells(15, n).Value, 3)

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 15).Value = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 6).Value

СтрокаПВ = СтрокаПВ + 1

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

СтрокаПВ = 1

СуммаВремИнтервалов = 0

СуммаТср = 0

СуммаСигм = 0

While Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value <> ""

СуммаВремИнтервалов = СуммаВремИнтервалов + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value

СуммаТср = СуммаТср + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Value

СуммаСигм = СуммаСигм + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Value

СтрокаПВ = СтрокаПВ + 1

Wend

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value = СуммаВремИнтервалов

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 11).Value = "="

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 11).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Value = СуммаТср

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 13).Value = "Тср"

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 13).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Value = СуммаСигм

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Font.Bold = True

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 15).Value = Sheets("Оформление").Cells(1, 5).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 15).Font.Bold = True

'Решаем систему уравнений вида

'a=b*X+c*Y

'n=m*X+k*Y

'находим строку в таблице где у нас находятся уравнения

СтрокаПВ = 1

While Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value <> ""

'СуммаВремИнтервалов = СуммаВремИнтервалов + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value

'СуммаТср = СуммаТср + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Value

'СуммаСигм = СуммаСигм + Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Value

СтрокаПВ = СтрокаПВ + 1

Wend

СтрокаПВ = СтрокаПВ - 1

a = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 1).Value

b = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 3).Value

c = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 5).Value

n = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 10).Value

m = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 12).Value

k = Sheets("Промежуточные вычисления").Cells(СтрокаПВ, 14).Value

Сигма = (b * n - m * a) / (b * k - m * c)

Tcp = (a - c * Сигма) / b

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 2, 1).Value = "Tcp"

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 2, 2).Value = "="

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 2, 3).Value = Tcp

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 3, 1).Value = Sheets("Оформление").Cells(1, 5).Value

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 3, 2).Value = "="

Sheets("Промежуточные вычисления").Cells(СтрокаПВ + 3, 3).Value = Сигма

10	=	Тср	+	-1,645	σ
20	=	Тср	+	-1,405	σ
30	=	Тср	+	-1,126	σ
40	=	Тср	+	-1,036	σ
50	=	Тср	+	-0,954	σ
60	=	Тср	+	-0,842	σ
70	=	Тср	+	-0,739	σ
80	=	Тср	+	-0,643	σ
90	=	Тср	+	-0,496	σ
100	=	Тср	+	-0,412	σ

Суммируя систему уравнений, определяют второе нормальное уравнение:

550= Т_ср- 9,298 σ, (6)

Умножая на соответствующий квантиль u_qi левые и правые части уравнений, получают новую систему уравнений:

-16,45	=	-1,645	Тср	+	2,706	σ
-28,1	=	-1,405	Тср	+	1,974	σ
-33,78	=	-1,126	Тср	+	1,268	σ
-41,44	=	-1,036	Тср	+	1,073	σ
-47,7	=	-0,954	Тср	+	0,91	σ
-50,52	=	-0,842	Тср	+	0,709	σ
-51,73	=	-0,739	Тср	+	0,546	σ
-51,44	=	-0,643	Тср	+	0,413	σ
-44,64	=	-0,496	Тср	+	0,246	σ
-41,2	=	-0,412	Тср	+	0,17	σ

Складывая все полученные уравнения, в результате получают первое нормальное уравнение:

-407= -9,298Т_ср+ 10,015 σ, (7)

Решив совместно уравнения (6) и (7), получают

Т_ср= 125,86 ч.

σ = 76,21 ч.

'Заполним 14 строку таблицы(16)=============================Тср

СтрокаТаблицы = 16

СтолбецТаблицы = 4

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

СтолбецТаблицы = 4

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, СтолбецТаблицы).Value = Round(Tcp, 2)

Sheets("ОсновнаяТаблица").Range(Cells(СтрокаТаблицы, 4), Cells(СтрокаТаблицы, n - 1)).MergeCells = True

Sheets("ОсновнаяТаблица").Range(Cells(СтрокаТаблицы, 4), Cells(СтрокаТаблицы, n - 1)).HorizontalAlignment = xlCenter

'Заполним 15 строку таблицы(17)=============================Сигма

СтрокаТаблицы = 17

СтолбецТаблицы = 4

For n = СтолбецТаблицы To (КоличествоСтолбцовТаблицы + СтолбецТаблицы - 1)

СтолбецТаблицы = 4

Sheets("ОсновнаяТаблица").Cells(СтрокаТаблицы, СтолбецТаблицы).Value = Round(Сигма, 2)

Sheets("ОсновнаяТаблица").Range(Cells(СтрокаТаблицы, 4), Cells(СтрокаТаблицы, n - 1)).MergeCells = True

Sheets("ОсновнаяТаблица").Range(Cells(СтрокаТаблицы, 4), Cells(СтрокаТаблицы, n - 1)).HorizontalAlignment = xlCenter

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

Список литературы:

Коваленко, В. Н., Новиков, А. А. Надежность устройств железнодорожной автоматики, телемеханики и связи. учеб. пособие. – Екатеринбург: УрГУПС, 1995. – с. 78.
Основы теории надежности автоматических систем управления : учеб. пособие для вузов / Л. П. Глазунов, В. П. Грабовецкий, О. В. Щербаков. – Л. : Энергоатомиздат, Ленинградское отд-ние, 1984. – 208 с.
Бронштейн И. Н., Семендяев К. А. Справочник по математике для инженеров и учащихся ВТУЗов. – М. : Наука, 1980. – 976 с.

Проголосовать за статью

Дипломы участников

У данной статьи нет
дипломов

РАСЧЕТ СРЕДСТВАМИ EXCEL МЕТОДА НАИМЕНЬШИХ КВАДРАТОВ В ОЦЕНКЕ ПАРАМЕТРОВ НАДЕЖНОСТИ ПРИ НОРМАЛЬНОМ ЗАКОНЕ РАСПРЕДЕЛЕНИЯ ОТКАЗОВ

Оставить комментарий