Использование возможностей Microsoft Excel в решении производственных задач (стр. 5 из 12)
С - логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если С=1 (ИСТИНА), то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику (дополнительную информацию о регрессионной статистике можно получить воспользовавшись справкой Excel). Если С=0 (ЛОЖЬ) то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.
В настоящей работе математическая модель качества среза y1 и силы вырубки y2 записывают в виде полинома следующего вида:
(3)Здесь x1 - значение зазора в кодовом масштабе, x2 - код материала, z1 - квадратичная функция от x1.
В натуральном масштабе матрица плана эксперимента выглядит следующим образом:
| Номер опыта | X1 | X2 | Качество среза, y1 | Сила вырубки [кН], y2 |
| 1 | 0,03 | 0 | 2 | 197 |
| 2 | 0,05 | 0 | 1 | 195 |
| 3 | 0,1 | 0 | 1 | 192 |
| 4 | 0,03 | 1 | 3 | 100 |
| 5 | 0,05 | 1 | 2 | 98 |
| 6 | 0,1 | 1 | 1 | 96 |
Для обработки данных эксперимента переходят к кодированному масштабу. В данной задаче по методике, изложенной в [1] получены следующие формулы для перехода от натурального к кодированному масштабу:
(4)Оптимизацию полученных регрессионных моделей можно осуществить двумя способами: либо визуально, построив графики, либо используя методы оптимизации, реализованные в Excel.
Последовательность выполнения
1. Запустить EXCEL
2. Переименуйте листы книги следующим образом: Лист1 - Модель, Лист2 - Сталь, Лист3 - Медь. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Переименовать.
3. На листе Модель создать таблицу с исходными данными эксперимента и поясняющими надписями по следующему образцу (при заполнении таблиц пользуйтесь способами копирования и автозаполнения):
| A | B | C | D | E | F | G | H | |
| 1 | Исследование операции вырубки листовых образцов | |||||||
| 2 | Матрица плана в натуральном масштабе | |||||||
| 3 | N опыта | X1 | X2 | |||||
| 4 | 1 | 0,03 | 0 | |||||
| 5 | 2 | 0,05 | 0 | |||||
| 6 | 3 | 0,1 | 0 | |||||
| 7 | 4 | 0,03 | 1 | |||||
| 8 | 5 | 0,05 | 1 | |||||
| 9 | 6 | 0,1 | 1 | |||||
| 10 | Матрица плана в кодированном масштабе | |||||||
| 11 | N опыта | x1 | x2 | z1 | x1x2 | x2z1 | y1 | y2, кН |
| 12 | d1 | d2 | d3 | d4 | d5 | |||
| 13 | 1 | 2 | 197 | |||||
| 14 | 2 | 1 | 195 | |||||
| 15 | 3 | 1 | 192 | |||||
| 16 | 4 | 3 | 100 | |||||
| 17 | 5 | 2 | 98 | |||||
| 18 | 6 | 1 | 96 | |||||
| 19 | Коэффициенты регрессионных моделей вида | |||||||
| 20 | y=b+m1*d1+m2*d2+m3*d3+m4*d4+m5*d5 | |||||||
| 21 | m5 | m4 | m3 | m2 | m1 | b | ||
| 22 | y1 | |||||||
| 23 | y2 | |||||||
4. На листе Сталь и Медь
| A | B | C | D | E | F | G | H | |
| 1 | Варьирование зазором | |||||||
| 2 | X1 | x1 | x2 | z1 | x1x2 | x2z1 | y1 | y2, кН |
| 3 | 0,1 | |||||||
| 4 | 0,09 | |||||||
| 5 | 0,08 | |||||||
| 6 | 0,07 | |||||||
| 7 | 0,06 | |||||||
| 8 | 0,05 | |||||||
| 9 | 0,04 | |||||||
| 10 | 0,03 | |||||||
| 11 | Поиск оптимального зазора | |||||||
| 12 | X1 | x1 | x2 | z1 | x1x2 | x2z1 | y1 | y2, кН |
5. Заполните диапазон B13: D18 формулами (4) для перехода от натурального масштаба к кодированному. Для ячейки B13 формула будет выглядеть следующим образом: =100* (B4‑0,06)
6. Заполните диапазон E13: F18 формулами для подсчета произведений x1x2 и x2z1
7. В диапазон B22: G22 введите формулу для определения коэффициентов регрессии для модели качества среза (y1) с помощью мастера функций. Последовательность действий приведена ниже:
- Выделить B22: G22
- Меню Вставка-Функция (или кнопка Вставка функции)
- Категория - Статистические, Функция - ЛИНЕЙН, Кнопка OK
- Окно Изв_знач_y - G13: G18 (мышью или с клавиатуры)
- Окно Изв_знач_x - B13: F18 (мышью или с клавиатуры)
- Окно Константа - 1
- Окно Стат - 0
- Нажать клавиатурную комбинацию Ctrl-Shift-Enter
- Формула массива вставится в выделенный диапазон и в нем появятся значения коэффициентов
8. Аналогично введите формулу для определения коэффициентов модели для силы вырубки (y2) в диапазон B23: G23. В качестве диапазона известных значений yиспользуйте столбец со значениями y2 в матрице плана в кодированном масштабе.
9. Постройте графики изменения качества реза и силы деформирования для стали на основании полученной модели. Для этого сначала необходимо заполнить таблицу данных на Листе Сталь. Используйте следующую последовательность действий:
- В ячейку Сталь! B3 занесите формулу =100* (A3-0,06) для перехода в кодированный масштаб
- В диапазон Сталь! С3: C10 занесите значение - 1 (минус 1), соответствующее коду стали в кодированном масштабе.
- Скопируйте формулы из диапазона Модель! D13: Модель! F13 в диапазон Сталь! D3: Сталь! F3, для чего:
- Выделите диапазон D13: F13 на листе Модель и нажмите кнопку Копировать
- Выделите ячейку D3 на листе Сталь и нажмите кнопку Вставить
- Распространите формулы диапазона B3: F3 на диапазон B3: F10
- Занесите в ячейку G3 на листе Сталь формулу для определения качества реза:
- =Модель! $G$22+Модель! $F$22*B3+Модель! $E$22*C3+Модель! $D$22*D3+ Модель! $C$22*E3+Модель! $B$22*F3
- Аналогично занесите в ячейку H3 на листе Сталь формулу для определения силы вырубки (вид формулы продумайте сами)
- Распространите формулы из диапазона G3: H3 на диапазон G3: H10
- Проверьте себя: для строк со значениями x1=4,-1,3 величины y1 и y2 должны точно совпадать с исходными данными
10. Постройте точечные графики y1=f (X1), y2=f (X1) на различных диаграммах и расположите их на том же листе Сталь и отформатируйте так, как показано в приложении. Область диаграммы y1=f (X1) должна занимать диапазон A15: D28, а y2=f (X1) - диапазон E15: H28. Указание: Прежде чем начать строить диаграммы с помощью мастера диаграмм выделите сначала диапазон значений аргумента A3: A10, а затем держа нажатой клавишу Ctrlна клавиатуре - диапазон значений аргумента (соответственно G3: G10 для y1 и H3: H10 для y2). Выделение с нажатой клавишей Ctrlпозволяет выделить несмежные диапазоны данных.
11. Проанализируйте график качества среза. Очевидно, что наилучшее качество достигается при минимальном зазоре 0.03. Значение оптимального зазора можно было найти не прибегая к построению графика, воспользовавшись встроенными в Excelсредствами поиска оптимальных решений.
- Скопируйте формулы из диапазона B3: H3 в диапазон B13: H13
- Выполните команду меню Сервис-Поиск решения
- В открывшемся окне Поиск решения занесите в окошки ввода следующие величины (мышью или с клавиатуры):
- Установить целевую - $G$13
- Равной - максимальному значению
- Изменяя ячейки - $A$13
- Ограничения - $A$13>=0.03; $A$13<=0.1 (Воспользуйтесь кнопкой Добавить справа от окна Ограничения. При вводе десятичной точки строго пользуйтесь точкой на основной клавиатуре)
- Нажмите кнопку Выполнить
- Excelвыполнит поиск оптимального решения с заданными ограничениями, после чего появится окно Результаты поиска решений, в котором следует нажать кнопку OK. Убедитесь, что Excelсамостоятельно нашел правильное решение.
- Попытайтесь найти самостоятельно зазор, обеспечивающий наихудшее качество
12. Постройте графики для Меди повторив пункты 9-11 на листе Медь (столбец x2 должен иметь значение 1, соответствующее коду меди). Пункт 12 можно выполнить проще. Если догадаетесь как - то сэкономите себе время.
13. Отформатируйте таблицы и графики так, как это показано в приложении. Если сможете - сделайте внешний вид полученных таблиц более привлекательным.
14. Завершить работу, сохранив ее в файле work3. xls.
15. Запустить EXCEL, вернуться к документу work3. xls и предъявить его преподавателю.
16. Предъявить преподавателю краткий конспект занятия.
Приложение
Лист Модель:
Лист Сталь:
Лист Медь:
Занятие 4 - Исследование операции вытяжки цилиндрического стакана из плоской заготовки
Цели работы:
- закрепление основных приемов создания и форматирования таблицы
- освоение методов построения пространственных графиков и графиков с областями
- освоение основных методов поиска решений средствами Excel
- исследование влияния силы прижима, радиуса матрицы и толщины заготовки на напряжение в опасном сечении.
Постановка задачи [2]:
Исследуется влияние силы прижима, радиуса матрицы и толщины заготовки на напряжение в опасном сечении на основании классических формул, полученных Е.А. Поповым.
Максимальную величину растягивающего напряжения srmax, действующую на границе пластически деформируемой части заготовки, без учета упрочнения материала можно определить по формуле
(1)Здесь ss - напряжение текучести, k=R/r - коэффициент вытяжки, R - радиус заготовки, r=0,5 (dп+s) - радиус вытягиваемого стакана, dп - диаметр пуансона, s - толщина заготовки, Q - сила прижима, rм - радиус кромки матрицы, m - коэффициент трения.
Первое слагаемое в скобках отражает влияние напряженно-деформированного состояния во фланце заготовки, второе - влияние сил трения на фланце заготовки от силы прижима, третье - влияние изгиба-спрямления на кромке матрицы. Дополнительный множитель 1+1,6mучитывает силы трения на кромке матрицы.