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

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

Приложение

Лист Модель:

Лист Сталь:

Лист Медь:

Цели работы:

Ä  закрепление основных приемов создания и форматирования таблицы

Ä  освоение методов построения пространственных графиков и графиков с областями

Ä  освоение основных методов поиска решений средствами Excel

Ä  исследование влияния силы прижима, радиуса матрицы и толщины заготовки на напряжение в опасном сечении.

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

Исследуется влияние силы прижима, радиуса матрицы и толщины заготовки на напряжение в опасном сечении на основании классических формул, полученных Е.А. Поповым.

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

 (1)

Здесь ss - напряжение текучести, k=R/r - коэффициент вытяжки, R - радиус заготовки, r=0,5 (dп+s) - радиус вытягиваемого стакана, dп - диаметр пуансона, s - толщина заготовки, Q - сила прижима, rм - радиус кромки матрицы, m - коэффициент трения.

Первое слагаемое в скобках отражает влияние напряженно-деформированного состояния во фланце заготовки, второе - влияние сил трения на фланце заготовки от силы прижима, третье - влияние изгиба-спрямления на кромке матрицы. Дополнительный множитель 1+1,6m учитывает силы трения на кромке матрицы.

Сила прижима Q препятствует потере устойчивости заготовки во фланце (складкообразование) одновременно увеличивая напряжения в опасном сечении.

Различные исследователи предлагают различные эмпирические формулы для определения рациональной силы прижима. Так, например, И.А. Норицыным предложена простая оценка рациональной силы прижима[3], как ¼ от силы вытяжки.

 (2)

С учетом упрочнения формула (1) принимает следующий вид[4]:

 (3)

Здесь sВ - предел прочности материала, yш - относительное уменьшение площади поперечного сечения к моменту образования шейки.

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

1.  Для заданных геометрических параметров заготовки, матрицы и пуансона, а также свойств материала определить, исходя из формулы (1) максимальную силу прижима, при которой отсутствует разрушение, а также, исходя из формул (1) и (2) - рациональную силу прижима.

2.  Проанализировать вклад различных составляющих формулы (1), отражающих влияние напряженно-деформированного состояния во фланце, сил трения во фланце и изгиба-спрямления на кромке матриц, при изменении силы прижима от »Qр до »Qmax с шагом 10000 Н

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

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

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

Для поиска решений в Excel применяются два основных средства, доступных из меню Сервис: Подбор параметра… и Поиск решения….

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

При выборе этого средства раскрывается диалоговое окно, имеющее несколько полей:

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

-   В поле Значение введите искомый результат.

-   В поле Изменяя значение ячейки введите ссылку на ячейку, содержащую подбираемое значение.

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

При выборе пункта Поиск решения из меню Сервис открывается диалоговое окно следующего вида:

В поле Установить целевую необходимо занести ссылку на зависимую ячейку - т.е. на ту ячейку, значение которой будет подбираться путем изменения значений во влияющих ячейках - их адреса заносятся в поле Изменяя ячейки. Можно осуществить поиск значений влияющих ячеек сообщающих целевой ячейке максимальное, минимальное значения, либо значения, равные конкретному числу (аналог Подбора параметра). Для этого необходимо выбрать нужный пункт в группе радиокнопок Равной. В поле Ограничения следует записать ограничения на влияющие и целевую ячейки. Добавление новых ограничений осуществляется после нажатия на экранную кнопку Добавить. Открывающееся диалоговое окно позволяет накладывать ограничения в виде равенств и неравенств на значения в ячейках, а также требовать их целочисленности. После заполнения всех полей следует нажать экранную кнопку Выполнить.

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

-   Вместо греческих символов ввести латинский эквивалент (наиболее распространенные греческие символы на латинской клавиатуре имеют следующий эквивалент:

-   a - a; b - b; d - d; D - D; e - e; h - h; g - g; l - l; m - m; n - n; w - w; j - j; p - p;

-   y - y; r - r; s - s; t - t; q - q; x - x).

-   Выделить набранный символ и воспользовавшись полем Шрифт панели инструментов Форматирование изменить для этого символа тип шрифта на Simbol

Для создания верхних и нижних индексов следует воспользоваться аналогичной методикой. Сначала набрать символы, помещаемые в индексы, затем выделить их в строке формул, нажать клавиатурную комбинацию Ctrl-1 (либо выполнить команду меню Формат - Ячейки) и в открывшемся диалоговом окне щелкнуть мышью по флажку нижний индекс в группе Эффекты, после чего нажать экранную кнопку OK.

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

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

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

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