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

• Занятие 1 — Определение инерционных характеристик главного вала горячештамповочного автомата
• Занятие 2 — Обработка эксперимента по определению приведенного модуля объемной упругости жидкости
• Занятие 3 — Расчет коэффициентов математической модели на примере исследования операции вырубки листовых образцов
• Занятие 4 — Исследование операции вытяжки цилиндрического стакана из плоской заготовки
• Занятие 5 — Обработка данных экстремальных экспериментов на примере исследования операции вытяжки листовых образцов
• Занятие 6 — Исследование операции обжима
• Занятие 7 — Оптимизация раскроя листового материала
• Занятие 8 — Обработка данных экспериментов с несколькими выходными переменными

Занятие 1 — Определение инерционных характеристик главного вала горячештамповочного автомата

Цели работы:

познакомиться с основными понятиями электронной таблицы

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

научиться сохранять и загружать таблицы

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

Определить массу и момент инерции главного вала горячештамповочного автомата:

m — масса цилиндра; J — момент инерции цилиндра

r=7850кг/м³ — плотность

V — объем; D — наружный диаметр; d — внутренний диаметр; h — высота

r₀ — расстояние от оси цилиндра до оси вращения

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

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

2. Развернуть окно EXCEL во весь экран и рассмотреть его

(Используйте стандартные кнопки управления окном, расположенные в правом верхнем углу окна)

2.1. Основные элементы окна:

верхняя строка — заголовок окна с кнопками управления

вторая строка — меню EXCEL

третья и четвертая строки — панели инструментов Стандартная и Форматирование

пятая строка — строка редактирования формул

строки между пятой и предпоследней — рабочее поле таблицы, состоящее из пронумерованных ячеек

вторая снизу — ярлыки листов (для переключения между рабочими листами) и полоса прокрутки

последняя строка — строка состояния

2.2. прочитать назначение кнопок панели Стандартная и Форматирование, медлено перемещая курсор по кнопкам.

3. Произвести настройку экрана

3.1. В меню Вид, установить флажки (если их нет) щелчком мыши в следующих элементах: Строка формул, Строка состояния

3.2. В меню Вид выбрать команду Панели инструментов, в открывшемся подменю установить флажки в следующих элементах: Стандартная, Форматирование.

3.3. В меню Сервис, выбрать команду Параметры… и раскрыть вкладку Вид,

3.4. Проверить установку флажков и переключателей

В группе Отображать — Строка формул, строка состояния

В группе Примечания — Только индикатор

В группе Объекты — Отображать.

В группе Параметры окна — все кроме Авторазбиение на страницы, Формулы

Раскрыть вкладку Общие и проверить установку переключателя Защита от макровирусов, установить Листов в новой книге — 3.

Нажать кнопку ОК

4. Основные понятия электронных таблиц

4.1. Рабочее поле — 16 384строки, 256столбцов. Каждое пересечение строки и столбца образует ячейку, в которую можно вводить данные (текст, число или формулу)

4.2. Номер строки (числа на левой границе рабочего поля), буква столбца на верхней границе рабочего поля (A-Z, AA-AZ, BA-BZ…)

4.3. Ячейка — пересечение строки и столбца — может содержать текст, число, формулу, имеет уникальный адрес, состоящий из буквы столбца и номера строки (например B3)

4.4. Указатель ячейки — жирный черный прямоугольник, с точкой в правом нижнем углу, определяющий текущую ячейку. Может перемещаться мышью (щелчок левой клавишей мыши по ячейке) и клавишами управления курсором (на клавиатуре)

· Выделите ячейку D4 — мышью, а затем вернитесь в ячейку A1 с помощью клавиш управления курсором.

4.5. Блок — прямоугольная область смежных ячеек, Адрес блока состоит из координат противоположных углов, разделенных двоеточием (B13: C19)

4.6. Книга — программа на EXCEL, состоит из (по умолчанию) рабочих листов (по умолчанию — 3 для Excel97 и 16 для Excel7), переход от одного листа к другому — щелчком по ярлыку

· перейти к Лист3, вернуться к Лист1

5. Выделение основных элементов

5.1. мышью

строки (столбца) — щелкнуть мышью по цифре (букве) — имени строки (столбца)

нескольких смежных строк (столбцов) — щелкнуть по цифре (букве) первой строки (столбца) и не отпуская кнопку мыши протащить курсор до последней строки (столбца)

блок — щелкнуть мышью по угловой ячейке блока (например, левый верхний угол) и не отпуская кнопки протащить курсор до противоположной угловой ячейки (например, правый нижний угол), либо щелкнуть по первой угловой ячейке блока, а затем с нажатой клавишей Shift по противоположной угловой ячейке блока.

несмежные ячейки (блоки) — выделить первый блок (ячейку, столбец, строку), а затем выделить последующие с нажатой клавишей CTRL

· выделить блок B2: E12 — первым способом, D3: F14 — вторым,

· выделить строки, столбцы 3, F, 1: 5, J: M

· выделить несмежные блоки (A2: B5; D3: E8), (8: 12; D: E)

5.2. клавиатурой: выделение блока;

перейти в ячейку, занимающую угол блока,

нажать SHIFT и раздвигать область клавишами управления курсором

· выделить блок C4: F10

5.3. весь лист — нажать на пустую кнопку в левом верхнем углу на пересечении номеров и строк

6. Основные приемы заполнения таблиц данными (текст, число, дата, время, формула) — общий принцип — переместиться в нужную ячейку, набрать данные (не более 240 символов), нажать ENTER, или перейти в другую ячейку мышью или клавишами

6.1. Текст — если первый символ — буква, то EXCEL автоматически считает данные — текстом, если текст начинается с цифры, то нужно сначала ввести символ апостроф ` (в латинском режиме). По умолчанию текст прижимается к левой границе. При вводе данных в правую ячейку изображение текста будет обрезано, но сам он сохранится

· ввести в ячейку A1 текст:

· Расчет массы и момента инерции главного вала автомата AO339 (в дальнейшем подобные задания будут записаны как:

· A1 Расчет массы и момента инерции главного вала автомата AO339)

· A2 Выполнен

6.2. Числа — начинаются со знака или без него. Если число очень большое (или маленькое) то EXCEL отображает его в экспоненциальной форме (3Е10 — означает 310¹⁰). Если необходимо ввести десятичное число — то в качестве разделителя используется либо десятичная точка, либо десятичная запятая (в зависимости от настройки Windows). По умолчанию число прижимается к правому краю.

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

· A41.780 (с точкой!) (это будет наружный диаметр участка (в метрах))

· B41,560 (с запятой!) (это — внутренний диаметр)

Проверьте в какой ячейке введено десятичное чисто (оно будет прижато к правому краю), исправьте число в ячейке, в которой десятичный разделитель был введен неправильно. Для правильного ввода десятичного разделителя, независимо от настройки, следует пользоваться точкой справа на дополнительной клавиатуре.

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

· С4 0,29 (это — высота участка)

· D4 0 (это — расстояние до оси вращения)

6.3. Дата — через дробь дд/мм/гг, дд-мм-гг, (либо месяц буквами, в зависимости от настройки английским или русскими) — после фиксации в качестве разделителя используется точка.

6.4. EXCEL преобразует дату в число равное количеству прошедших суток от 1 января 1990 г

· B2 текущая дата (например для 12 марта: 12−04)

6.5. Формула — арифметическое выражение, состоящее из последовательности чисел, ссылок на ячейки и функций, соединенных арифметическими знаками и начинающихся со знака =. Завершение ввода формулы — нажатие клавиши ENTER, либо щелчок мышью по кнопке (зеленого цвета) в строке формул. В ячейке после этого появляется результат. Формула отображается в строке формул.

Можно вводить ссылки на ячейки вручную, а можно путем указания на соответствующую ячейку мышью.

Например для ввода в ячейку Е4 формулы для определения массы зубчатого венца =3,14*7850* (A4²-B4²) *C4/4 необходимо выполнить следующую последовательность действий:

установить курсор в ячейку Е4

набрать =3,14*7850* (с клавиатуры щелкнуть по ячейке A4 набрать ^2- щелкнуть по ячейке B4 набрать ^2) * щелкнуть по ячейке C4 набрать /4 щелкнуть по кнопке (зеленого цвета) в строке формул

6.6. Попробуйте заменить какие-нибудь числа в ячейках A4: C4 и убедитесь, что значения в ячейке E4 также изменяются. Проверьте на калькуляторе правильность записанной формулы.

6.7. Число p=3.14 можно заменить его точным значением, использовав встроенную функцию ПИ (). Для этого необходимо двойным щелчком выделить число 3,14 и на этом месте ввести с клавиатуры ПИ (), либо воспользоваться мастером функций, выполнив следующую последовательность действий:

щелкнуть по кнопке fx на панели — Стандартная

в открывшемся окне в группе Категория выбрать — Математические

в группе Функции выбрать функцию ПИ

нажать на кнопку ОК в нижней части окна

щелкнуть по кнопке (зеленого цвета) в строке формул

7. Заполните ячейки A5D12 значениями размеров соответствующих участков вала, разбив его на цилиндрические участки и поместив в столбец A значения наружного диаметра, в столбец B — внутреннего, C — высоты цилиндра.

8. Основные приемы редактирования таблиц

8.1. Операции с ячейками, строками, столбцами, блоками.

Прежде, чем произвести какие-либо действия с элементом его надо выделить.

Действия по перемещению, копированию, удалению и очистке блока можно производить несколькими способами:

с помощью кнопок панели инструментов (Вырезать, Копировать, Вставить)

через меню (в меню Правка команды Вырезать, Копировать, Вставить, Удалить, Очистить, Заполнить)

с помощью мыши, методом Drag and Drop (перетащи и брось) ;

с помощью контекстно-зависимого меню (меню правой кнопки мыши при щелчке по выделенному элементу)

8.2. Копирование

методом Drag and Drop

выделить ЧТО (диапазон ячеек которые копируются),

перевести указатель мыши на границу выделенного диапазона так, чтобы указатель превратился в стрелку, направленную влево под углом

нажать левую клавишу мыши и удерживать клавишу CTRL на клавиатуре. Рядом с указателем появится маленький знак + признак операции копирования.

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

после позиционирования копируемого диапазона на новом месте следует сначала отпустить левую клавишу мыши, а затем клавишу CTRL на клавиатуре.

кнопками панели инструментов — выделить ЧТО, кнопка Копировать, выделить КУДА, кнопка Вставить

· скопировать A1 в A14 методом Drag and Drop

· скопировать A4: E4 в A15: E15 с помощью кнопок панели инструментов. Обратить внимание на изменение формулы в ячейке E15 — ссылки на ячейки с исходными данными автоматически изменились. При выполнении операции копирования, в том случае, если в ячейке записана формула, то ссылки на ячейки в формуле изменяются в соответствии с расстоянием (по столбцам и строкам) на которое был перемещен диапазон.

8.3. Очистка — выделить необходимы диапазон и нажать DEL

· очистить ячейки в 14 и 15 строках

8.4. Перемещение выполняется аналогично копированию, только в методе Drag and Drop не надо держать нажатой клавишу CTRL, а при использовании кнопок панели инструментов вместо кнопки Копировать следует использовать кнопку Вырезать.

· переместить A4: E12 на один столбец вправо.

8.5. Заполнение — выделенные ячейки копируются несколько раз за одно действие. Методика заполнения: Выделить блок, навести курсор на маркер заполнения (жирный квадрат в нижнем правом углу выделенного блока или ячейки), маркер заполнения должен превратиться в крестик, затем раздвигать выделенную область

· E4 E4: E12 (весь столбец должен автоматически заполниться нулями)

· F4 F4: F12 (ячейки должны заполниться значениями, вычисленными по скопированным формулам)

· Занесите в ячейку G4 формулу для вычисления момента инерции =F4* (B4²+C4²) /8+F4*E4² и заполните ею диапазон G4: G12.

· В строчке, соответствующей участку шейки кривошипа занесите в ячейку столбца D значение эксцентриситета. Убедитесь, что значение момента инерции автоматически пересчиталось.

8.6. Удаление — в отличие от очистки удаленные ячейки схлопываются — т. е. другие ячейки занимают место удаленных. Осуществляется через меню Правка, Удалить (или контекстное меню — щелчок правой кнопкой мыши по нужной ячейке и из предложенных действий выбрать нужное).

8.7. Примечание: Если Вы случайно совершили неправильное действие (например удалили лишнюю ячейку), с помощью кнопки Отменить на панели инструментов или команды Отменить меню Правка можно отменить последнее действие (для EXCEL5,7) или несколько последних действий (для EXCEL97)

· Удалить строку 2 через меню Правка

· удалить ячейку A1 через контекстно-зависимое меню

· отменить удаление

8.8. Автозаполнение — позволяет быстро создавать различные типовые последовательности. Работает также, как и обыкновенное заполнение. EXCEL анализирует выделенные ячейки и если находит зависимость, то интерполирует ее на другие ячейки

Арифметическая прогрессия

A4 1; A5 2; выделить A4: A5; маркер заполнения протащить до A12

Дни недели

A17 Понедельник, Заполнить до H17

Месяцы

A18 Январь, Заполнить до K18

Даты

A19 Скопировать дату из ячейки B2, Заполнить до H19

· Удалить строки 17: 19

8.9. Изменение ширины столбцов и высоты строк.

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

С помощью меню — Формат, Строка, Высота (Автоподпор высоты) или Столбец, Ширина (Автоподбор ширины)

· Подберите мышью ширину столбцов таблицы в соответствии со своим вкусом

· Строка 9 высота 30, а затем обратно 12.75 (через меню Формат)

9. Абсолютная относительная и смешанная адресация ячеек и блоков.

Использовавшаяся до сих пор адресация — относительная. При ее использовании EXCEL запоминает расположение относительно текущей ячейки и при копировании автоматически меняет адрес.

Иногда при копировании необходимо сохранить ссылку на конкретную ячейку (столбец, строку) — абсолютная адресация. Перед буквой столбца и номером строки следует вставить символ $ (можно после вставки относительного адреса нажать F4 тогда символы $ вставятся автоматически) — такая адресация называется абсолютной

Иногда при копировании необходимо изменять только один параметр адреса. Тогда символ $ ставится только там, где он необходим (только перед буквой столбца или номером строки) — такая адресация называется смешанной.

В нашем примере при определении массы в формулу вставляли конкретное значение плотности материала 7850кг/м³. В том случае, если бы вал сделали из материала, имеющего другую плотность, нам нужно было бы менять каждую формулу для определения массы. Попытаемся обобщить таблицу на случай произвольного материала

· В ячейку A2 ввести текст Плотность материала

· В ячейку B2 внести значение 7850

· Выделить ячейку F3. В строке формул появится формула для вычисления массы.

· Выделить в формуле число 7850 двойным щелчком левой клавиши мыши, затем щелкнуть по ячейке B2 (в формуле значение 7850 должно замениться на B2) и нажать на клавишу F4 в верхнем ряду клавиатуры (B2 должно измениться на $B$ 2). После этого щелкнуть по кнопке (зеленого цвета) в строке формул.

· Заполнить формулой в ячейке F4 диапазон F4: F12 и убедиться что в каждой формуле есть ссылка $B$ 2 на адрес ячейки со значением плотности.

· Изменить плотность материала и убедиться, что произведен пересчет во всех ячейках одновременно.

· Переместить диапазон A3: G12 на одну строчку вниз и убедиться, что абсолютная адресация в формулах при перемещении и копировании сохраняется, в отличии от относительной.

10. Определим массу и момент инерции вала. Для этого необходимо просуммировать значения, стоящие в соответственно в столбцах F и G. Для суммирования столбца F необходимо выделить ячейку F13, нажать кнопку Автосуммирование (S) на панели инструментов, убедиться, что диапазон суммирования выбран программой правильно и нажать клавишу Enter на клавиатуре. В ячейке F13 появится значение суммы масс по участкам.

· Просуммировать значения в столбце G

11. Добавьте заголовки к столбцам таблицы и заголовок для строки с результатами суммирования

12. Операции с таблицей в целом — сохранить, сохранить как, открыть, создать. Выполняются либо из меню Файл, либо используя соответствующие кнопки панели инструментов.

· сохранить work1. xls в своем каталоге

13. Завершить работу

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

15. Итоги. Проверьте

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

15.2. умеете ли Вы: вводить текст, редактировать данные, изменять размеры ячеек, перемещать, копировать, заполнять, удалять, сохранять

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

Занятие 2 — Обработка эксперимента по определению приведенного модуля объемной упругости жидкости

Цели работы:

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

освоение основных приемов форматирования таблицы (внешнего вида)

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

получение начальных сведений для построения регрессионных зависимостей

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

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

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

Аккумулятор винтового пресса-молота заряжается до начального давления 25 МПА.

Открывается вентиль, через который жидкость (минеральное масло) начинает вытекать в мерный объем (мензурку)

Фиксируется объем вытекшего масла через определенные промежутки текущего давления в аккумуляторе.

Опыт повторяется 3 раза

Модуль объемной упругости жидкости определяли путем обработки результатов эксперимента по следующему алгоритму:

Определяется объем вытекшего масла на каждом интервале замеров давления:

i=1ј9???число точек фиксации объема вытекшего масла p_{i=20,15,10,5,4,3,2,1,0 [МПа] - давления, при которых фиксируется объем масла Vi - объем вытекшего масла, соответствующий i-му давлению в аккумуляторе DVi=Vi+1-Vi, (1) при i=1ј8, DV9=0}

Вычисления по п1 выполняют для каждого из трех опытов

Вычисляют среднее значение изменения объема на каждом интервале:

DVср_{i= (DV1, i+DV2, i+DV3, i) /3, (2) где индексы 1, i 2, i 3, i соответствуют номеру опыта и номеру интервала}

Вычисляют среднее значение объема масла в точках фиксации давления по результатам трех опытов:

Vср_{i= Vсрi+1+DVi (3) при i=8ј1, ?Vср9=0}

Строится график P=f (Vср) и визуально определяют точку n перехода от нелинейного начального к линейному конечному участку графика

Результаты соответствующие точкам i=1јn аппроксимируют прямой p=b0+b1*Vср (4)

Определяют приведенный модуль объемной упругости жидкости по формуле:

k=V_{0* dp/dVср (5) где V0=1,1577*10⁴ см³ - объем аккумулятора dp/dVср=tana= b1, a - угол наклона прямой, аппроксимирующей линейный участок зависимости P=f (Vср)}

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

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

2. Создать таблицу с исходными данными эксперимента и поясняющими надписями в соответствие с прилагаемой ниже таблицей. Обратить внимание, что текст, целиком не помещающийся в ячейку вводится в ячейку, номер которой соответствует началу текста. Например, весь текст «Экспериментальное определение модуля объемной упругости» вводится в ячейку А1

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

Символы DV в тексте пояснений в таблице заменяют символы в DV формулах

3. Занести в ячейку С7 формулу (1), которая будет иметь вид =D4-C4 и распространить ее на диапазон C7: J9.

4. Занести в ячейку С10 формулу для определения среднего значения изменения объема DVср_{i Для этого можно воспользоваться встроенной функцией СРЗНАЧ. Рассмотрим порядок ввода формул с использованием встроенных функций на этом примере:}

установить курсор в ячейку C10

нажать на кнопку (Мастер функции или Вставить функцию) на стандартной панели инструментов в открывшемся окне выбрать Категория: Математические, Функция: СРЗНАЧ и щелкните по кнопке ОК (для Excel97) или Шаг> (для Excel5.0,7.0)

в открывшемся окне диалога в пункте Число1 вместо предложенного мастером функций диапазона занесите диапазон ячеек, для которых вы вычисляете среднее значение C7: C9 и щелкните по экранной кнопке OK (Закончить) Ввод диапазона можно осуществлять как вручную (обратите внимание на то, что клавиатура должна находиться в латинском регистре), так и мышью — что более предпочтительно.

Для ввода диапазона с помощью мыши необходимо просто выделить мышью нужный вам диапазон в таблице (если нужный диапазон закрыт окном диалога, то окно можно передвинуть в другое место экрана методом Drag and Drop)

5. Распространить формулу в ячейке С10 на диапазон

6. Для того, чтобы увидеть точное значение вычисленных величин (если Excel округлил их до целых), выделите диапазон С10: J10 и несколько раз нажмите кнопку (Увеличить разрядность) на панели инструментов Форматирование.

7. В ячейку J12 внести формулу (3), которая будет иметь вид=K12+J10 и распространить ее на диапазон J12: C12. Если это необходимо, то увеличьте разрядность отображения величин.

8. В ячейки C14 и C15 занесите формулы для определения максимальной и минимальной погрешности вычисления объема, которые будут иметь вид:

microsoft excel электронная таблица

9. для C14: =МАКС (С7: C9) — C10 для C15: =C10-МИН (C7: C9) Функции МИН и МАКС находятся в категории Статистические мастера функций.

10. Распространите формулы из диапазона C13: C14 на диапазон C13: K14

11. Построить график зависимости P=f (Vср):

выделите диапазон ячеек B12: K13, на основании которого вы будете строить диаграмму.

щелкните по кнопке Мастер диаграмм стандартной панели инструментов (в зависимости от версии Excel она имеет различный внешний вид, поэтому найдите ее сами с помощью всплывающих подсказок) либо воспользуйтесь командой меню Вставка-Диаграмма.

следуйте указаниям Мастера диаграмм в каждом диалоговом окне, возникающим последовательно после нажатия экранных кнопок Далее> (или Шаг> для ранних версий). Ниже приведена последовательность действий для Excel97 (для ранних версий Excel последовательность и содержание окон несколько другое, но получаемый результат будет идентичен). Если Вас что-то не устраивает в построенной диаграмме, то в последующем ее можно изменить:

Шаг1: Щелчком мыши выберите тип диаграммы Точечная, а вид — со значениями, соединенными сглаживающими линиями. Нажмите кнопку Далее.

Шаг2: Если диапазон данных в окне ввода Диапазон указан верно, а вид диаграммы тот, который вы ожидаете, нажмите кнопку Далее. В противном случае в окне Диапазон введите нужный диапазон данных Шаг3: В окно Название диаграммы сотрите содержащиеся там данные; в окне Ось X введите: V, см3; в окне Ось Y введите: p, МПа. Нажмите кнопку Далее.

Шаг4: В окне Поместить диаграмму на листе выберите пункт Имеющемся, рядом с которым выберите в открывающемся списке пункт Лист2. Нажмите кнопку Готово.

12. Как правило, вид созданной диаграммы мастером диаграмм бывает неудовлетворительным, поэтому диаграмму следует отформатировать. Выполните форматирование диаграммы в соответствие со следующими указаниями:

Щелкните мышью по области диаграммы. Область диаграммы выделится черной рамкой, по углам которой и в середине сторон имеются черные квадратики — размерные маркеры. Кроме того в меню Excel добавится пункт Диаграмма Для изменения размеров диаграммы необходимо буксировать размерные маркеры. Перед буксировкой необходимо навести указатель мыши на один из размерных маркеров. Указатель мыши при этом примет вид двунаправленной стрелки. После чего необходимо нажать левую клавишу мыши и перемещать (буксировать) маркер. Буксировка маркера, расположенного в середине стороны, позволяет изменять горизонтальные и вертикальные размеры диаграммы. Буксировка углового маркера позволяет изменять одновременно вертикальные и горизонтальные размеры.

Для перемещения диаграммы необходимо навести указатель мыши на область диаграммы (указатель примет вид белой стрелки, направленной под углом влево-вверх) и отбуксировать ее на новое место Отбуксируйте диаграмму в левый верхний угол листа, а затем измените ее размеры так, чтобы она занимала примерно ѕ пространства листа.

Диаграмма состоит из нескольких элементов. К ним, в частности, относятся:

область диаграммы (весь чертеж) область построения диаграммы легенда ряды данных названия осей и т. п.

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

Редактирование выделенного элемент осуществляется через меню Формат, в котором появляется пункт для редактирования соответствующего элемента, либо нажатием клавиатурной комбинации Ctrl-1, которая позволяет произвести форматирование текущей ячейки, диапазона, элемента диаграммы или объекта.

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

Выделите диаграмму, щелкнув по области диаграммы Последовательно нажимая клавишу ^ на клавиатуре обратите внимание на элементы диаграммы, присутствующие в построенной вами диаграмме.

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

Отобразите на диаграмме погрешности вычисления. Для этого выделите кривую (она имеет название Ряд «p, МПа»), нажмите клавиатурную комбинацию Ctrl-1. Затем в окне Формат ряда данных выберите вкладку X-погрешности, в который выберите вид показа — обе планки погрешностей, величина погрешностей — пользовательская, в графе «+» введите диапазон положительной погрешности, вычисленной Вами =Лист1! $C$ 14: $K$ 14 (это можно сделать и мышью, выделяя соответствующий диапазон в таблице), а в графе «-» =Лист1! $C$ 15: $K$ 15

Добавьте (если их нет) линии сетки для значений по оси X. Для этого в меню Диаграмма выберите пункт Параметры диаграммы и на вкладке Линии сетки щелкните по пункту ОсьX — основные линии, после чего нажмите кнопку OK

Отформатируйте осьY так, чтобы график занимал всю область построения диаграммы. Для этого после выделения оси и перехода в окно редактирования одним из указанных выше способов (например с помощью клавиатурной комбинации Ctrl-1) выберите вкладку Шкала и в графе Максимальное значение поставьте максимальное значение давления в исходной таблице — 20. Посмотрите другие вкладки в окне форматирования оси. Возможно Вы захотите изменить и другие параметры и внешний вид элементов оси.

Измените название оси категорий так, чтобы размерность объема была поставлена правильно: не см3, а см³. Для это выделите название оси, затем выделите цифру 3 в размерности, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта щелкните мышью по пункту Верхний индекс раздела Эффекты.

13. Проанализируйте, начиная с какой точки график изменения объема становится линейным. По-видимому это будет точка, соответствующая давлению 4 МПа. Определите параметры прямой, которой можно аппроксимировать кривую изменения давления на линейном участке. Для этого следует воспользоваться встроенной функцией ЛИНЕЙН, позволяющей построить функцию вида y=b+m1*x1+ј+mi*xi на основе некоторого массива исходных данных методом наименьших квадратов. Функция ЛИНЕЙН относится к категории статистических. В данном случае воспользуемся «ручным» набором, без использования мастера функций.

Синтаксис функции ЛИНЕЙН (известные_значения_y; известные_значения_x; константа; статистика) Константа — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если константа имеет значение ИСТИНА (1), то b вычисляется обычным образом.

Статистика — это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если статистика имеет значение ЛОЖЬ (0), то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

14. Выделите диапазон ячеек C18: D18, затем в строке формул наберите следующую формулу =ЛИНЕЙН (C13: G13; C12: G12; 1; 0) и нажмите клавиатурную комбинацию Ctrl-Shift-Enter (по этой клавиатурной комбинации вводится единая формула для всего массива). Обратите внимание, что после ввода клавиатурной комбинации Ctrl-Shift-Enter набранная формула заключается в фигурные скобки, что означает, что это формула массива, а не одной ячейки, а в ячейках C18: D18 появляются значения.

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

В ячейку C20 введите формулу =$D$ 18+$C$ 18*C12 (вспомните, что для превращения относительной ссылки в абсолютную не обязательно вручную вводить знаки $ - достаточно нажать клавишу F4)

Распространите формулу из ячейки C20 на диапазон C20: K20

Выделите диаграмму на Листе2, затем в меню Диаграмма выберите пункт Добавить данные Для ввода в окно Диапазон нового ряда данных щелкните по закладке Лист1, в нем курсором мыши выделите диапазон C20: J20 и нажмите кнопку OK — на диаграмме появится график прямой.

16. Вычислите приведенный модуль объемной упругости рабочей жидкости, для чего в ячейку A24 введите формулу (5), которая будет иметь вид =A22*C18

17. Отформатируйте таблицу.

Расположите заголовке по центру диапазона столбцов. Для этого выделите нужный диапазон столбцов и нажмите кнопку (Центрировать по выделению) на панели инструментов Форматирование. Проделайте это для диапазонов A1: K1; C2: K2; A11: K11; A19: K19

В необходимых местах добавьте линии границ ячеек. Выделите ячейки A2: K10, нажмите клавиатурную комбинацию Ctrl-1, выберите вкладку Границы, отметьте Все внешние и внутренние границы. Для ячеек K5, K8 уберите верхнюю и нижнюю границу (выделите ячейки, Ctrl-1, убрать границы). Аналогично оформите таблицы в диапазонах A12: K15; A17: D18; B20: K20.

Измените формат показателей степени в размерностях см3. Для это нужной ячейке выделите цифру 3 в размерности, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта щелкните мышью по пункту Верхний индекс раздела Эффекты. Повторите это для ячеек A5, A8, B12, B22.

В ячейках A8, A10 измените символы DV на DV. Для этого выделите D в соответствующих ячейках, нажмите клавиатурную комбинацию Ctrl-1 и в открывшемся окне форматирования шрифта выберите шрифт Symbol в окне Шрифт

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

19. Проверьте себя: приблизительный внешний вид таблиц и диаграммы приведен в приложении

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

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

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

Занятие 3 — Расчет коэффициентов математической модели на примере исследования операции вырубки листовых образцов

Цели работы:

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

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

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

Постановка задачи Дмитриев А. М., Коробова Н. В., Ступников В. П. Методы факторного планирования эксперимента в обработке давлением: Учебное пособие для вузов. — М.: МГТУ им. Н. Э. Баумана, 1999. — 105 с. :

Исследуется влияние величины зазора Z между пуансоном и матрицей на качество среза и силу P, необходимую для вырубки листовых образцов.

Заготовки после вырубки осматривают и оценивают качество среза по 3-х бальной шкале. Наилучшим срезом, оцениваемым в 3 бала, считается состоящий из трех зон (I — зона скругления, II — блестящий поясок, III — зона скола) При этом зона I должна иметь незначительный размер. Если эта зона возрастает по сравнению с наименьшей, полученной при вырубке заготовки из данного материала, или вырубленный образец имеет заметный прогиб, то качеству среза присваивают оценку 2 бала. Если же поверхность рваная, с дополнительными поясками, то качеству среза присваивают оценку 1 балл.

Вырубаются кружки из стали 45 и меди в матрице диаметром 25 мм. Толщина заготовок 7 мм. Уровни варьирования зазора, по отношению к толщине заготовки составляют 0.03, 0.05 и 0.1.

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

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

При использовании методов планирования эксперимента изучаемый объект представляют в виде некоторого «черного ящика», выходные параметры которого зависят от входных параметров. Математическая модель, отражающая связь между выходными и контролируемыми входными параметрами записывают в виде полинома следующего вида:

(1)

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

Для нахождения коэффициентов моделей типа (1) в Excel применяют встроенную функцию ЛИНЕЙН. Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива. Уравнение для прямой линии имеет следующий вид:

y = b+m₁d₁ + m₂d₂ +. (2)

где зависимое значение y является функцией независимых значений d_i. Значения m_i — это коэффициенты, соответствующие каждой независимой переменной d_i, а b — это постоянная.

Сравнение формул (1) и (4) показывает, что если в качестве переменных d_i использовать значения переменных x_i, а также различные функции от x_i, то коэффициент b в формуле (2) имеет смысл коэффициента b₀ в формуле (1), а коэффициенты m_i — соответственно коэффициентов b_i, b_ij, b_ii

Функция ЛИНЕЙН возвращает массив значений коэффициентов в обратном порядке {m_n; m_n1;.; m₁; b}. ЛИНЕЙН может также возвращать дополнительную регрессионную статистику.

Синтаксис функции: ЛИНЕЙН (Y; D; K; C)

Здесь: Y — множество (обычно столбец) известных значений y

D — множество (обычно диапазон) известных значений d. Если множество Y — столбец, то диапазон D должен иметь столько же строк, сколько столбец значений Y. Количество столбцов диапазона D определяет количество n неизвестных коэффициентов m_i регрессионной модели

K — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если K=1 (имеет значение ИСТИНА), то b вычисляется обычным образом. Если K=0 (имеет значение ЛОЖЬ), то b полагается равным 0.

С — логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если С=1 (ИСТИНА), то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику (дополнительную информацию о регрессионной статистике можно получить воспользовавшись справкой Excel). Если С=0 (ЛОЖЬ) то функция ЛИНЕЙН возвращает только коэффициенты m и постоянную b.

В настоящей работе математическая модель качества среза y1 и силы вырубки y2 записывают в виде полинома следующего вида:

(3)

Здесь x₁ — значение зазора в кодовом масштабе, x₂ — код материала, z₁ — квадратичная функция от x₁.

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

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

(4)

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

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

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

2. Переименуйте листы книги следующим образом: Лист1 — Модель, Лист2 — Сталь, Лист3 — Медь. Для этого необходимо щелкнуть правой клавишей мыши по ярлычку листа и в открывшемся контекстном меню выбрать пункт Переименовать.

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

4. На листе Сталь и Медь

5. Заполните диапазон B13: D18 формулами (4) для перехода от натурального масштаба к кодированному. Для ячейки B13 формула будет выглядеть следующим образом: =100* (B4₀, 06)

6. Заполните диапазон E13: F18 формулами для подсчета произведений x₁x₂ и x₂z₁

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

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

Постановка задачи Попов Е. А. Основы теории листовой штамповки. — М.: Машиностроение, 1968. — 283 с. :

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

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

(1)

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

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

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

Различные исследователи предлагают различные эмпирические формулы для определения рациональной силы прижима. Так, например, И. А. Норицыным предложена простая оценка рациональной силы прижима Зубцов М. Е. Листовая штамповка. — Л: Машиностроение, 1967. — 504 с., как ј от силы вытяжки.

(2)

С учетом упрочнения формула (1) принимает следующий вид Теория ковки и штамповки // Под ред. Е. П. Унксова и А. Г. Овчинникова. — М.: Машиностроение, 1992. — 720 с. :

(3)

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

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

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

Проанализировать вклад различных составляющих формулы (1), отражающих влияние напряженно-деформированного состояния во фланце, сил трения во фланце и изгиба-спрямления на кромке матриц, при изменении силы прижима от Q_р до Q_max с шагом 10 000 Н