Учебное пособие: Использование возможностей Microsoft Excel в решении производственных задач
Таблица.
|
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)
Ä B7 à составьте
выражение самостоятельно
и скопируйте их в диапазоны D7: E7; F7: G7; H7: I7
82.
На Листе2 в ячейки D4 и E4
введите формулы (7) для перехода от натурального масштаба к кодированному:
Ä D4 à = (B4-Лист1!
$D$10) /Лист1! $C$10
Ä E4 à составьте
выражение самостоятельно
83.
На Листе2 в ячейки F4: H4
введите формулы (6) для вычисления функций z2,
q2, n2
Например, G4 à
(5/6) *E4^3- (17/6) *E4
84.
На Листе2 в ячейки I4: L4
введите формулы для определения произведения соответствующих функций
85.
Распространите формулы диапазона D4: L4 на диапазон D4: L13
86.
На Листе2 в ячейки F13: I13
введите формулы (3) для перехода от истинных значений механических свойств к условной
шкале Например, G16 à
=Лист1! D$7+Лист1! E$7*C16
87.
На Листе2 в ячейку J16 введите формулу для определения
функции желательности =EXP (-EXP (-F16)) и распространите ее на диапазон J16: M16
88.
На Листе2 в ячейку N16 введите формулу для определения
обобщенной функции желательности = (J16*K16*L16*M16) ^ (0,25)
89.
Распространите формулы диапазона F16: N16 на диапазон F16: N25
90.
В диапазон A28: J28 введите формулу для определения коэффициентов регрессии
для модели обобщенной функции желательности, используя функцию ЛИНЕЙН и мастер функций.
Последовательность действий приведена ниже:
Ä Выделить A28:
J28
Ä Меню Вставка-Функция
(или кнопка Вставка функции)
Ä Категория
- Статистические, Функция - ЛИНЕЙН, Кнопка OK
Ä Окно Изв_знач_y
- диапазон известных значений D
Ä Окно Изв_знач_x - диапазон значений xi и производных от них функций в опытах
Ä Окно Константа
- 1
Ä Окно Стат
- 0
Ä Нажать клавиатурную
комбинацию Ctrl-Shift-Enter
Ä Формула массива
вставится в выделенный диапазон и в нем появятся значения коэффициентов
91.
Присвойте ячейкам диапазона A28: J28 имена, соответствующие названиям коэффициенты,
используя вместо латинских букв "b" русские
"в", например ячейке C28 присвойте имя "в122".
Присвоение имени ячейкам осуществляется следующим образом:
Ä выделите нужную
ячейку
Ä выполните
команду меню Вставка-Имя-Присвоить
Ä в окне Присвоение
имени в поле Имя внесите необходимое название ячейки, а в поле Формула - ссылку
на соответствующий адрес ячейки.
Ä нажмите кнопку
Добавить
92.
В диапазоне F32: O40 создайте
массив данных для построения графика изменения функции желательности в зависимости
от скорости (X1) и температуры (X2). Excel позволяет точно строить пространственные диаграммы только при
пропорциональном изменении данных вдоль каждой строки и столбца. Поэтому мы заранее
ввели значения X1 и X2,
изменяющиеся с некоторым шагом. Для облегчения ввода формул в диапазон F32: O40
следует сначала создать вспомогательные диапазоны изменения факторов в кодированном
масштабе.
93.
В ячейку F30 введите формулу = (F31-Лист1! $D$10) /Лист1! $C$10 для перехода от X1 к x1 и распространите ее на диапазон F30: O30
94.
В ячейку A32 введите формулу = (E32-Лист1! $D$11)
/Лист1! $C$11 для перехода от X2 к x2
95.
В диапазон B32: D32 скопируйте
формулы из диапазона F4: H4
96.
Распространите формулы диапазона A32: D32 на диапазон A32: D40
97.
Введите в ячейку F32 формулу для определения функции
желательности по полученной регрессионной модели =в0+в1*F$30+в2*$A32+в22*$B32+в222*$C32+в2222*$D32+в12*F$30*$A32+в122*F$30*$B32+в1222*F$30*$C32+в12222*F$30*$D32
обратите внимание на использование имен коэффициентов и смешанной адресации (знак
$ стоит только перед именем столбца или номером строки). Смешанная адресация позволяет
распространить формулу из ячейки на весь диапазон
98.
Распространите формулу из ячейки F32 на диапазон
F32: O40
99.
Постройте диаграмму изменения функции желательности в зависимости от скорости
(X1) и температуры (X2). Для этого:
Ä Выделите диапазон
E31: O40
Ä Воспользуйтесь
командой меню Вставка-Диаграмма
Ä Выберите тип
диаграммы: Поверхность, Вид диаграммы - Цветная контурная (в виде цветных сечений
поверхностей уровня)
Ä Следуйте указаниям
мастера диаграмм, ориентируясь на получение диаграммы, приведенной в приложении.
Ä Если внешний
вид диаграммы не соответствует приведенной в приложении - отформатируйте ее.
100.
Перенесите полученную диаграмму с листа 2 на лист 1
101.
Проанализируйте по полученной диаграмме области возможных режимов обработки
материала, памятуя, что допустимый уровень функции желательности D=0,368
102.
Отформатируйте таблицы и графики так, как это показано в приложении. Завершить
работу, сохранив ее в файле work8. xls.
103.
Запустить EXCEL, вернуться к документу work8. xls и предъявить его преподавателю.
104.
Предъявить преподавателю краткий конспект занятия.
Приложение:
Лист 1
Лист 2
[1]
Дмитриев А.М., Коробова Н.В., Ступников В.П. Методы факторного планирования
эксперимента в обработке давлением: Учебное пособие для вузов. - М.: МГТУ им.
Н.Э. Баумана, 1999. - 105 с.
[2]
Попов Е.А. Основы теории листовой штамповки. - М.: Машиностроение, 1968. - 283
с.
[3]
Зубцов М.Е. Листовая штамповка. - Л: Машиностроение, 1967. - 504 с.
[4]
Теория ковки и штамповки // Под ред. Е.П. Унксова и А.Г. Овчинникова. - М.:
Машиностроение, 1992. - 720 с.
[5]
Дмитриев А.М., Коробова Н.В., Ступников В.П. Методы факторного планирования
эксперимента в обработке давлением: Учебное пособие для вузов. - М.: МГТУ им.
Н.Э. Баумана, 1999. - 105 с.
[6]
Дмитриев А.М., Коробова Н.В., Ступников В.П. Методы факторного планирования
эксперимента в обработке давлением: Учебное пособие для вузов. - М.: МГТУ им.
Н.Э. Баумана, 1999. - 105 с.
|