Использование возможностей Microsoft Excel в решении производственных задач (стр. 11 из 12)

Занятие 8 - Обработка данных экспериментов с несколькими выходными переменными

Цели работы:

- освоение методов обработки сложных экспериментов с помощью средств Excel

- освоение методов построения пространственных диаграмм в Excel

Постановка задачи [6]:

Рассматривается задача отыскания методов обработки бронзы А10Ж3Мц1,5 (температуры и скорости деформирования), которые бы обеспечивали наилучший комплекс механических свойств (предела текучести s_, предела прочности s_, относительного удлинения d, относительного сужения Ψ).

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

(1)

где d_i - частные функции желательности по каждому из показателей комплекса свойств.

(2)

Здесь y _- условная шкала, имеющая линейную связь со значениями показателей комплекса свойств s

(3)

Коэффициенты a₀, a₁определяют по двум "реперным" точкам, в которых исследователь задает соответствие истинным значениям комплекса свойств определенное с его точки зрения значение функции желательности. Шкала функции желательности выглядит следующим образом: d=0…0,368 (y= - ∞…0) - недопустимый уровень качества; d=0,368…0,63 (y=0…0,77) - допустимый уровень качества; d=0,63…0,8 (y=0,77…1,5) - хороший уровень качества; d=0,8…1 (y=1,5…∞) - превосходный уровень качества. Зависимость d=f (y) приведена на рисунке.

Очевидны следующие соотношения:

(4)

Здесь. y^<i>,s^<i> - пары реперных точек. В нашем случае предлагается следующие значения реперных точек:

Характеристика	sT		sB		d		Y
Значение, s	25	21,5	44	29,6	23	26,2	45	60
Функция желательности, d	0,368	0,63	0,368	0,8	0,368	0,8	0,368	0,95
Условная шкала, y	0	0,77	0	1,5	0	1,5	0	3

Для обобщенной желательности строится в кодированном масштабе математическая модель вида

(5)

где z₂, q₂, n₂ - следующие функции от x₂:

(6)

Уравнения связи между факторами в натуральном и кодированном масштабах

(7)

Фактор X_{1 -} скорость растяжения мм/с, X₂ - температура нагрева ^оС испытываемого на растяжение образца. X_1ср=27,5;. k₁=22,5 мм/с; X_2ср=350; k₂=100 ^оС.

Матрица плана эксперимента и результаты в натуральном масштабе приведена в таблице:

№опыта	скорость,мм/с	температура,оС	sT	sB	d	
1	5	150	29,3	61,5	21,9	36,1
2	5	250	35,1	58,1	20,5	45,2
3	5	350	34,7	54,4	20,2	37
4	5	450	29,2	34,5	27,6	47,5
5	5	550	14,3	17	25,5	57,3
6	50	150	28,5	61,2	21,7	37,6
7	50	250	39,6	58,7	19,7	48,3
8	50	350	36	54,7	19,9	43,8
9	50	450	32	43,5	22,4	42,7
10	50	550	20,8	24,6	24,7	52,9

Методы решения с использованием Excel:

Решение задачи разбивается на несколько шагов с созданием соответствующих таблиц:

1. Определение коэффициентов соответствия механических свойств условной шкале в формуле (3) по зависимостям (4) с учетом данных таблицы 1.

2. Построения матрицы плана эксперимента в кодовом масштабе по формулам (7) (6)

3. Определение значений условной шкалы для результатов каждого опыта по формулам (3), частных функций желательности по формуле (2), а затем обобщенной функции желательности по формуле (1).

4. Определение значений коэффициентов bв модели (5) с использованием функции ЛИНЕЙН.

5. Построение графика изменения функции желательности Dпо уравнению (5) в зависимости от двух варьируемых параметров - скорости и температуры и определения области допустимых режимов обработки.

Последовательность выполнения

78. Запустить EXCEL

79. На листе1 создать таблицы с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):

A	B	C	D	E	F	G	H	I
1	Определение оптимального интервала штамповки
2	Определение коэффициентов перехода к условной шкале
3	Характеристика	sT	sB	d	
4	Значение, s	25	21,5	44	29,6	23	26,2	45	60
5	Условная шкала, y	0	0,77	0	1,5	0	1,5	0	3
6	Коэффициенты перехода	a0	a1	a0	a1	a0	a1	a0	a1
7
8	Варьируемые параметры
9	ki	Xiср
10	Скорость растяжения, мм/с	X1	22,5	27,5
11	Температура нагрева, oC	X2	100	350

80. На листе 2 создать таблицы с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения).

Таблица.

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Матрица плана эксперимента
2	N оп	Натуральн		Кодовый масштаб
3	X1	X2	x1	x2	z2	q2	n2	x1*x2	x1*z2	x1*q2	x1*n2
4	1	5	150
5	2	5	250
6	3	5	350
7	4	5	450
8	5	5	550
9	6	50	150
10	7	50	250
11	8	50	350
12	9	50	450
13	10	50	550
14	Результаты опытов
15	sT	sB	d		ysT	ysB	yd	y	dsT	dsB	dd	d	D
16	1	29,3	61,5	21,9	36,1
17	2	35,1	58,1	20,5	45,2
18	3	34,7	54,4	20,2	37
19	4	29,2	34,5	27,6	47,5
20	5	14,3	17	25,5	57,3
21	6	28,5	61,2	21,7	37,6
22	7	39,6	58,7	19,7	48,3
23	8	36	54,7	19,9	43,8
24	9	32	43,5	22,4	42,7
25	10	20,8	24,6	24,7	52,9
26	Коэффициенты регрессионной модели
27	b12222	b1222	b122	b12	b2222	b222	b22	b2	b1	b0
28
29	Построение графика линий уровней обобщенной функции желательности
30	x1
31	5	10	15	20	25	30	35	40	45	50	X1
32	-2	2	-1	1	150
33	-1,5	0,25	1,44	-8,3	200
34	-1	-1	2	-4	250
35	-0,5	-1,75	1,31	2,95	300
36	0	-2	0	6	350
37	0,5	-1,75	-1,31	2,95	400
387	1	-1	-2	-4	450
39	1,5	0,25	-1,44	-8,3	500
40	2	2	1	1	550
41	x2	z2	q2	n2	X2

81. На Листе1 в ячейки B7 и C7 введите формулы (4) для определения коэффициентов соответствия механических свойств условной шкале в формуле (3): C7 -= (B5-C5) / (B4-C4)