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

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

Содержание

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

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

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

Занятие 4 - Исследование операции вытяжки цилиндрического стакана из плоской заготовки

Занятие 5 - Обработка данных экстремальных экспериментов на примере исследования операции вытяжки листовых образцов

Занятие 6 - Исследование операции обжима

Занятие 7 - Оптимизация раскроя листового материала

Занятие 8 - Обработка данных экспериментов с несколькими выходными переменными

Цели работы:

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

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

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

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

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

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

r=7850кг/м3 - плотность

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

r0 - расстояние от оси цилиндра до оси вращения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.1.  Рабочее поле - 16384строки, 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 - означает 3´1010). Если необходимо ввести десятичное число - то в качестве разделителя используется либо десятичная точка, либо десятичная запятая (в зависимости от настройки Windows). По умолчанию число прижимается к правому краю.

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

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

·  B4®1,560 (с запятой!) (это - внутренний диаметр)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7.  Заполните ячейки A5¼D12 значениями размеров соответствующих участков вала, разбив его на цилиндрические участки и поместив в столбец 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^2+C4^2) /8+F4*E4^2 и заполните ею диапазон 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кг/м3. В том случае, если бы вал сделали из материала, имеющего другую плотность, нам нужно было бы менять каждую формулу для определения массы. Попытаемся обобщить таблицу на случай произвольного материала

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

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

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

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

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

Цели работы:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7.  Vсрi= Vсрi+1+DVi (3) при i=8¼1, Vср9=0

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

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

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

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

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

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

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

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

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

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

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