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

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.  Предъявить преподавателю краткий конспект занятия.

Приложение:

Цели работы:

Ä  закрепление основных приемов создания и форматирования таблицы, построения графиков различного вида и поиска решений в Excel

Ä  создание модуля для автоматизации обработки результатов эксперимента по исследованию операции обжима.

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

В ходе настоящей работы необходимо создать электронную таблицу, позволяющую автоматизировать результаты обработки экспериментальных данных по исследованию операции обжима и используя теоретические зависимости исследовать влияние отдельных параметров на напряжение в опасном сечении. Схема операции обжима с образованием цилиндрической части приведена ниже.

Максимальную величину сжимающих напряжений srmax, действующих в недеформируемых стенках заготовки, без учета упрочнения материала можно определить по формуле

 (1)

Максимальная сила деформирования

 (2)

Здесь ss - напряжение текучести, m - коэффициент трения,

r0=0,25 (dн+dв), (3), Rз=0,5 (Dн+s0). (4)

При проведении эксперимента по обжиму трубы Dн=28 мм, s0=2 мм были получены следующие результаты

При проведении занятия необходимо средствами Excel решить следующие задачи:

5.  Используя формулу (2) определить экспериментальное значение для различных углов конусности матрицы srmax.

6.  Используя формулу (1) для ss =250 МПа, m=найти расчетные значения srmax

7.  Определить ошибку расчета по сравнению с экспериментом.

8.  Построить графики изменения максимального напряжения от угла конусности по результатам расчета и эксперимента

9.  Определить оптимальное значение угла конусности для значения r0=10 мм исходя из минимального значения напряжений по формуле (1)

10.  Построить пространственный график взаимного влияния толщины заготовки и радиуса обжима r0 на величину максимального напряжения при найденном оптимальном значении угла конусности.

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

Методы решения поставленных задач с использованием Excel описаны в предыдущих работах. В настоящей работе все действия по созданию и форматированию таблицы производятся с самостоятельно

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

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

52.  На лист 1 занести заголовок, исходные данные для расчета (см. приложение).

53.  Определить радиус заготовкиRз по формуле (4)

54.  Занести в таблицу результаты эксперимента (см. приложение)

55.  Для a=10° определить

55.1.  r0 по формуле (3)

55.2.  srmax экспериментальное по формуле (2)

55.3.  srmax расчетное по формуле (1) (обратить внимание на необходимость применения как абсолютной, так и относительной адресации, в противном случае будет невозможно пользоваться методом автозаполнения)

55.4.  определить относительную ошибку расчета e в %

56.  Распространить формулы, записанные в строке для a=10° на строки с остальными значениями углов. Прежде, чем двигаться дальше, сравните Ваши результаты с данными преподавателя, и исправьте ошибки, если они есть.

57.  На основании полученных данных постройте графики изменения максимальных напряжений в зависимости от угла конусности матрицы по результатам расчета и эксперимента. (см. приложение).

58.  Используя команду Сервис-Поиск решения определите оптимальный угол конусности для r0=10 по формуле (1).

59.  Создайте вспомогательную таблицу для построения пространственного графика зависимости максимального напряжения от толщины заготовки s0=1,2,3,4,5 мм и радиуса обжима r0=8,9,10,11,12 мм. Внешний вид такой таблицы приведен в приложении.

60.  Заполните вспомогательную таблицу. Это можно сделать записав в одной из ячеек общую формулу с использованием абсолютных, относительных и смешанных адресов, а затем распространив эту формулу на всю таблицу. Сравните полученные результаты с данными преподавателя и исправьте ошибки если они есть.

61.  Постройте пространственный график по данным таблицы

62.  Отформатируйте таблицу и графики так, как это показано в приложении.

63.  Завершить работу, сохранив ее в файле work6. xls.

64.  Запустить EXCEL, вернуться к документу work6. xls и предъявить его преподавателю.

65.  Предъявить преподавателю краткий конспект занятия.

Приложение: Пример форматирования итоговой таблицы и графиков (результаты в таблицах не показаны).

Цели работы:

Ä  освоение методов решения задач оптимизации с использованием Excel

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

В ходе настоящей работы необходимо создать электронную таблицу, позволяющую произвести оптимизацию раскроя листа для вырубки круглых заготовок. Схема, поясняющая постановку задачи приведена ниже.

Необходимо выбрать наилучшие размеры и найти наилучший вариант раскроя листа шириной B и длиной L. Диапазон возможного изменения длин листа: L=2000…3000 мм с шагом 100 мм, диапазон возможного изменения ширины листа: В=600…1100 мм с шагом 50 мм. Предварительно выбрана схема двухрядного косого раскроя, что определяется размерами штампового пространства используемого прессового оборудования.

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

Здесь N - количество заготовок, помещающихся на листе.

Для сравнения вариантов, одинаковых по величине коэффициента использования металла, можно использовать дополнительный показатель - площадь концевого отхода, остающегося при раскрое полосы. Можно ожидать, что чем больше эта площадь, тем с большей эффективностью можно использовать концевой отход для других производственных нужд. Для приведения этого критерия к безразмерному виду можно отнести величину площади концевого отхода к максимальной площади листа, который может быть использован. Как следует из изложенного выше, максимальная площадь листа составляет Bmax´Lmax=1100´3000 мм2. Тогда критерий площади концевого отхода может быть рассчитан как:

В качестве обобщенной целевой функции F (критерия качества), может быть использована аддитивная функция, в которой критерий площади концевого отхода z взят с весовым коэффициентом 0.1, как менее важный, по сравнению с критерием использования материала h.

В такой постановке целевая функция зависит от ширины и длины исходного листа, которые могут изменяться с определенным шагом и угла косого раскроя a, который может изменяться в общем случае в пределах от 0 до 90 градусов непрерывно.

Таким образом, задача оптимизации сформулирована следующим образом (см. курс "Основы автоматизированного проектирования"):

-   Целевая функция: F® max

-   Вектор управляемых параметров: B, L, a

-   Ограничения на управляемые параметры: B=600…1100 с шагом 50, L=2000…3000 с шагом 100, 0£a£90

Ниже приведен возможный алгоритм вычисления целевой функции в рамках поставленной задачи. Для определенности варьируемым (управляемым) параметрам заданы начальные значения. Величина ширины перемычек a и a1 (см. чертеж) приняты постоянными для диаметров детали в пределах 100…200 мм.

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11