Учебное пособие: Использование возможностей Microsoft Excel в решении производственных задач

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

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

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

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

Таблица.

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 с.