Использование возможностей Microsoft Excel в решении производственных задач (стр. 8 из 12)
Таким образом процедура решения задачи сводится к следующей последовательности шагов:
- проведение натурного эксперимента, для получения линейной модели y=b0+b1x1+b2x2+b3x3 (1), где y=srmax; x1, x2, x3 - факторы k, rм, Vв кодовом масштабе (результаты натурного эксперимента заданы в качестве исходных данных);
- определению коэффициентов biлинейной модели;
- определению составляющих градиента в натуральном масштабе;
- осуществлении крутого восхождения - т.е. подбора такого шага в направлении градиента из центра плана, при котором в формуле (1) y=sВ. Предел прочности материала, используемого в опытах 310 МПа
Методы решения с использованием Excel:
Для определения коэффициентов линейной модели (1) достаточно провести 4 опыта. Интервалы варьирования факторов DXiотносительно центра плана Xi0 в проведенном натурном эксперименте приведены в таблице 1. Матрица плана исходного натурного эксперимента в кодированном масштабе приведена в таблице 2. Кодированные значения факторов связаны с натуральными соотношениями вида:
(2)Таблица 1. Уровни варьирования факторов
| Факторы | 1 | 2 | 3 |
| k | rм, мм | V, мм/с | |
| Xi0 | 1,3 | 3 | 0,5 |
| DXi | 0,1 | 1 | 0,2 |
Таблица 2. Матрица плана исходного эксперимента
| № опыта | x1 | x2 | x3 | y(srmax, МПа) |
| 1 | -1 | -1 | -1 | 189 |
| 2 | 1 | -1 | 1 | 236 |
| 3 | -1 | 1 | 1 | 167 |
| 4 | 1 | 1 | -1 | 223 |
Коэффициенты регрессионной модели (1) определяют с помощью уже известной вам функции ЛИНЕЙН. Если вы забыли синтаксис функции ЛИНЕЙН, воспользуйтесь встроенной справкой Excel.
Исходная точка для крутого восхождения - центр плана с координатами в натуральном масштабе:
X1о=1.3, X2о=3, X3о=0.5 (3)
Составляющие градиента вычисляются по формуле
gi=biDXi (4)
Шаг крутого восхождения в натуральном масштабе по каждой из координат
Xi=Xi0+gi*S (5)
Подбор такой кратности шагов S, одинаковой для всех координат, при котором y=sВ осуществляют с помощью команды меню Excel "Сервис-Подбор параметра".
Последовательность выполнения
34. Запустить EXCEL
35. Переименуйте Лист1 книги в "Крутое восхождение". Остальные листы книги удалите. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Удалить.
36. На листе Крутое восхождение создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):
| A | B | C | D | E | F | G | H | |
| 1 | Определение максимального коэффициента вытяжки | |||||||
| 2 | Уровни варьирования факторов | |||||||
| 3 | 1 | 2 | 3 | |||||
| 4 | Xi0 | 1,3 | 3 | 0,5 | ||||
| 5 | DXi | 0,1 | 1 | 0,2 | ||||
| 6 | Матрица плана исходного эксперимента в кодированном масштабе | |||||||
| 7 | № опыта | x1 | x2 | x3 | srmax, МПа | |||
| 8 | 1 | -1 | -1 | -1 | 189 | |||
| 9 | 2 | 1 | -1 | 1 | 236 | |||
| 10 | 3 | -1 | 1 | 1 | 167 | |||
| 11 | 4 | 1 | 1 | -1 | 223 | |||
| 12 | Коэффициенты регрессионной модели | |||||||
| 13 | b3 | b2 | b1 | b0 | ||||
| 14 | ||||||||
| 15 | Составляющие градиента | |||||||
| 16 | g1 | g2 | g3 | |||||
| 17 | ||||||||
| 18 | С учетом направления восхождения | |||||||
| 19 | g1 | g2 | g3 | |||||
| 20 | ||||||||
| 21 | Крутое восхождение (с помощью "Сервис-Подбор параметра. ") | |||||||
| 22 | Xi=Xi0+S*gi | xi= (Xi-Xi0) /DXi | ||||||
| 23 | S | X1 | X2 | X3 | x1 | x2 | x3 | srmax |
| 24 | ||||||||
37. В диапазон A14: D14 введите формулу для определения коэффициентов регрессии для модели напряжений srmax, используя функцию ЛИНЕЙН и мастер функций. Последовательность действий приведена ниже:
- Выделить A14: D14
- Меню Вставка-Функция (или кнопка Вставка функции)
- Категория - Статистические, Функция - ЛИНЕЙН, Кнопка OK
- Окно Изв_знач_y - диапазон известных значений srmax
- Окно Изв_знач_x - диапазон значений xiв опытах
- Окно Константа - 1
- Окно Стат - 0
- Нажать клавиатурную комбинацию Ctrl-Shift-Enter
- Формула массива вставится в выделенный диапазон и в нем появятся значения коэффициентов
38. Введите в диапазон A17: C17 формулы (4) для определения составляющих градиента. Обратите внимание, что порядок следования составляющих градиента giв диапазоне A17: C17 и порядок следования коэффициентов biв диапазоне A14: С14 не совпадают.
39. Обратите внимание, что полученные значения составляющих градиента для факторов 2 и 3 имеют отрицательные значения. Это означает, что шаги в этом направлении приведут к уменьшению srmax. Поэтому мы должны двигаться в направлении антиградиента по этим факторам (напомним, что направление градиента - это направление увеличения функции). Таким образом в ячейки A20: C20 следует внести следующие формулы:
- A20 - =A17
- B20 - = - C17
- C20 - = - C17
40. Занесите в ячейку A24 значение 1, являющееся начальным значением кратности шагов S
41. Занесите в диапазон B24: D24 формулы (5) для определения значений варьируемых факторов в натуральном масштабе. Составляющие градиента расположены в ячейках A20: C20. Значения центра плана Xi0 - в ячейках B4: D4. Для использования автозаполнения в формулах следует вводить абсолютный адрес ячейки $A$24
42. В ячейки E24: G24 внесите формулы (2) для перехода от натурального масштаба к кодированному.
43. В ячейку H24 введите формулу для определения srmaxпо значениям факторов xiв ячейках E24: G24 в соответствии с моделью (1). Коэффициенты модели bi были определены вами ранее в ячейках A14: D14. При вводе формулы обратите внимание на обратный порядок значений коэффициентов bi в диапазоне A14: D14. Обратите внимание, что величина напряжения получилась значительно большей, чем максимально возможное значение 310 МПа.
44. Меняя значение в ячейке A24 попытайтесь вручную подобрать такую кратность шагов S, при которой значение напряжения в ячейке H24 было бы равно 310 МПа.
45. Верните в ячейку A24 значение 1 и подберите точное значение с помощью встроенных в Excelсредств автоматического подбора значений. Для этого:
- Выполните команду меню Сервис-Подбор параметра
- В открывшемся окне Подбор параметра введите следующие значения, используя мышь или клавиатуру:
- в поле Установить в ячейке: $H$24
- в поле Значение: 310
- в поле Изменяя значение ячейки: $A$24
- Нажмите кнопку OK. Убедитесь, что решение найдено.
46. Решение поставленной задачи можно сформулировать так: "Может быть достигнут коэффициент вытяжки, равный значению в ячейке B24, при использовании матрицы, радиус скругления которой в мм приведен в ячейке C24 и проведении вытяжки со скоростью деформирования, значение которой в мм/с приведено в ячейке C24".
47. Отформатируйте таблицы и графики так, как это показано в приложении. Если сможете - сделайте внешний вид полученных таблиц более привлекательным.
48. Завершить работу, сохранив ее в файле work5. xls.
49. Запустить EXCEL, вернуться к документу work4. xls и предъявить его преподавателю.
50. Предъявить преподавателю краткий конспект занятия.
Приложение:
Занятие 6 - Исследование операции обжима
Цели работы:
- закрепление основных приемов создания и форматирования таблицы, построения графиков различного вида и поиска решений в Excel
- создание модуля для автоматизации обработки результатов эксперимента по исследованию операции обжима.
Постановка задачи:
В ходе настоящей работы необходимо создать электронную таблицу, позволяющую автоматизировать результаты обработки экспериментальных данных по исследованию операции обжима и используя теоретические зависимости исследовать влияние отдельных параметров на напряжение в опасном сечении. Схема операции обжима с образованием цилиндрической части приведена ниже.
Максимальную величину сжимающих напряжений srmax, действующих в недеформируемых стенках заготовки, без учета упрочнения материала можно определить по формуле
(1)Максимальная сила деформирования
(2)