Применение MS Excel для расчетов

Тема контрольной работы «Применение MS Excel для расчетов» по дисциплине «Информатика». Современные ПК могут успешно использоваться при решении самых разных научно — технических и инженерных задач. Дисциплины механического цикла составляют важную часть обучения в высших технических заведениях. Решение задач теоретической механики, сопротивления материалов, прикладной механики требуют проведения большой и подчас монотонной вычислительной работы. Использование ПК позволяет не только автоматизировать вычисления, но и дает возможности оптимизировать выбор наилучшего варианта конструкции или детали машин. Совокупность знаний и навыков в области вычислительной техники может помочь при выполнении лабораторных работ (обработка результатов наблюдений), курсовых и контрольных работ. Приобретенные навыки будущий специалист должен использовать и в своей профессиональной деятельности.

Цель работы ;

1. Овладеть приемами создания таблиц

2. Научиться разрабатывать структуру таблицы, согласно условию задачи

3. Научиться вводить и редактировать данные, простые формулы, итоговые функции.

4. Овладеть приемами форматирования

5. Научиться использовать итоговые функции

1. Обработка данных средствами электронных таблиц

1.1 Краткие теоретические сведения

Для представления данных в удобном виде используют таблицы. Класс программ, который позволяет не только представлять данные в удобном виде, но и обрабатывать их называется электронными таблицами (ЭТ).

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

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

Программа Microsoft Excel для работы с таблицами данных. При формировании таблицы выполняют ввод, редактирование и форматирование данных и формул.

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

Рабочий лист состоит из строк и столбцов. Столбцы обозначаются латинскими буквами: A, B, C, D. .. Z, а затем используются двухбуквенные обозначения: AA, AB… AZ, BA, BB… IV. Всего Рабочий лист может содержать 256 столбцов. Строки последовательно нумеруются цифрами от 1 до 65 536.

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

Одна из ячеек на Рабочем листе всегда является текущей (активной).Для того, чтобы сделать ячейку активной, необходимо щелкнуть мышью по нужной ячейке. Текущая ячейка обведена широкой рамкой, а ее номер (и содержимое) приведены в строке формул.(См. Рис.1)

Рис. 1

1.3 Ввод данных

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

1. Сделать ячейку текущей (активной)

2. С клавиатуры ввести необходимые данные

Закончить ввод нажав клавишу Enter или кнопку Enter в строке формул. Вводимые данные отображаются как в ячейке, так и в строке формул.

1.4 Редактирование содержимого ячеек

Исправления допущенных ошибок можно сделать следующими способами:

1. Щелкните на нужной ячейке и введите новое содержимое, нажмите клавишу Enter. (Новое содержимое заменит прежнее, поэтому нет необходимости специально удалять старое содержимое).

2. Если нужно заменить только определенную часть содержимого ячейки, то щелкните по ячейке, содержимое которой нужно изменить, а затем щелкните в строке формул. Теперь можно изменить содержимое, используя навигационные клавиши и клавишу Delete для удаления символа, расположенного справа от курсора ввода, или клавишу (Backspace) .

3. При редактировании содержимого ячейки в строке формул отображаются три кнопки.

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

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

4. Нажатие клавиши DELETE приводит к удалению всего содержимого ячейки.

1.5 Автоматизация ввода чисел

Автозаполнение числами.

Ввести начальное число в ячейку (например в ячейку A5 число -1). В правом нижнем углу ячейки имеется черный квадратик — это маркер заполнения. При наведении на него указателя мыши он приобретает вид черного крестика. Перетаскивание маркера заполнения в вертикальном или горизонтальном направлении рассматривается как операция «размножения» содержимого.

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

Если навести указатель мыши на маркер заполнения (ячейка — A5) и перетащить маркер заполнения правой кнопкой мыши, чтобы рамка охватила A5, A6 …A12, отпустить правую кнопку мыши, то появится меню. Если в этом меню выбрать пункт Копировать ячейки, то все ячейки будут содержать — 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа -1,2,3…8.

Заполнение прогрессией.

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

1. Ввести в ячейку число.

2. Убедиться в том, что ячейка, содержащая начальное число является активной.

3. Дать команду Правка Заполнить Прогрессия (Откроется диалоговое окно Прогрессия).

4. В этом окне выбирается:

a. тип прогрессии,

b. величина шага,

c. предельное значение.

1.6 Выбор ячеек

В некоторых операциях могут одновременно участвовать несколько ячеек. Такую группу ячеек называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении последовательно идущих строк и столбцов. Диапазон ячеек обозначают, указывая через двоеточия имена ячеек, расположенных в противоположных углах прямоугольника, например D5: F12, или A4: F4. Для того, чтобы произвести такую операцию, нужные ячейки необходимо выбрать (выделить).

Выделить диапазон можно несколькими способами:

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

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

Если необходимо выбрать несвязные диапазоны, то удерживают клавишу Ctrl.

Выбранная группа ячеек выделяется на экране:

— содержимое ячеек отображается белым цветом

— вся группа выделенных ячеек обводится толстой рамкой.

1.7 Операции с ячейками

С выбранным диапазоном в программе Excel можно работать так же, как и с выбранным фрагментом текста в программе Word, т. е. копировать, перемещать, удалять.

Для того, скопировать (переместить) диапазон ячеек :

1. Выбрать диапазон

2. Подать команду ПравкаКопировать (или ПравкаВырезать)

3. Щелкнуть по ячейке, которая будет являться левой верхней ячейкой в области вставки.

4. Подать команду ПравкаВставить

Копировать (перемещать) можно и с помощью кнопок на Стандартной панели инструментов

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

1.8 Форматирование ячеек

Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числовые — по правому краю. Чтобы изменить формат отображения данных в ячейке (диапазоне):

· Выбрать ячейку (выделить диапазон) Дать команду ФорматЯчейки (появится диалоговое окно)

· Вкладка Число этого окна позволяют:

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

· Вкладка Выравнивание позволяет:

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

· Вкладка Шрифт позволяет изменить гарнитуру, размер и начертание шрифта

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

· Вкладка Вид предлагает палитру для заливки.

Наиболее часто употребляющиеся элементы форматирования вынесены на Панель форматирования.

1.9 Вычисления в электронных таблицах

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

Ввод формулы всегда начинается с символа (знак равенства)

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

Если сделать ячейку текущей, то сама формула будет отображаться в строке формул.

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

Ссылки на ячейки.

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

Значение, отображаемое в ячейке с формулой, пересчитывается при изменении ячейки, на которую указывает ссылка.

Ссылку на ячейку можно задать разными способами:

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

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

Этот способ более удобный, так как нет необходимости переводить клавиатуру на латынь.

1.10 Абсолютные и относительные ссылки

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

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

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

Если формула будет скопирована в ячейку F6, то ссылка на D6 указывает на ячейку, расположенную на два столбца левее, а ссылка на E6 — на столбец левее, относительно F6

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

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

— выделить ссылку,

— и нажать клавишу F4

Элемента имени ячейки использующие абсолютную адресацию предваряются символами $ (знак доллара).

1.11 Автозаполнение формулами

При работе с таблицами часто возникает необходимость «размножить» формулу в горизонтальном или вертикальном направлении. Поясним это на примере. В ячейку F5 введена формула =D5*E5. Аналогичную формулу необходимо ввести и в ячейки F6, F7…F12. Для того, чтобы «размножить» формулу :

1. Сделать ячейку F5 активной

2. Установить указатель мыши на маркер заполнения

3. С помощью левой кнопки мыши протянуть вниз (в нужном направлении)

1.12 Использование стандартных функций

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

При работе с функциями нужно всегда строго следовать правилам обращения к функции (синтаксису).

Для любой функции определены следующие понятия:

Имя функции — задавая в формуле имя функции, вы указываете программе, о каких вычислениях идет печь (Примеры имен функций — SIN, СРЗНАЧ, МАКС и др.

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

Результат - значение, полученное при вычислении функции.

Пример: СРЗНАЧ (F5:F12), СУММ (F5:F12)

Вставка функции

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

Но в этом случае необходимо точно знать синтаксис функции.

Для задания функции можно использовать Мастера функций — на Стандартной панели имеется кнопка .

1.13 Графические возможности MS Excel

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

В программе MS Excel термин диаграммы используется для обозначения всех видов графического представления данных

Наглядность и легкость восприятия диаграмм позволяет принять быстрое и обоснованное решение по дальнейшей обработке данных числового типа. Существенным достоинством MS Excel является то, что при работе с ним при изменении исходных данных, по которым построены диаграмма, автоматически изменяется и их изображение.

Удобным средством для создания графических представлений в MS Excel является .

Мастер строит диаграмму по табличным данным (следовательно, прежде чем вызывать Мастера, необходимо выделить данные)

2. Практическая часть

2.1 Задание

Создать ведомость начисления заработной платы для бригады ремонтников, состоящей из 8 человек.

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

Дано:

· Фамилии работника Должность работника

· Тарифная ставка (почасовая оплата)

· Отработанное время (в часах) Требуется:

1. Составить ведомость начисления заработной платы.

2. Подсчитать сумму начислений заработной платы за месяц

3. Вычислить средний, максимальный и минимальный заработки в бригаде

2.3 Выполнение задания

электронный таблица ячейка вычисление

Разработать структуру таблицы Количество столбцов будущей таблицы определяется количеством однотипных данных:

1. № п/п

2. Фамилии

3. Должность

4. Тарифная ставка

5. Отработанное время

6. Заработная плата Итого — 6 столбцов Кол-во строк таблицы определяется количеством членов бригады

Загрузить программу Excel (Пуск Программы Microsoft Excel и сохранить в собственной папке под именем

Практическая .работа № 1_фамилия

(Практическая работа № 1_Петренко) Создать шапку таблицы и отформатировать

Сделать активной ячейку A4 и внести в нее — № п/п

Сделать активной ячейку B4 и внести в нее — Фамилии Сделать активной ячейку C4 и внести в нее — Должность Введите в ячейку D4 — Тариф. ставка Введите в ячейку E4 — Отработ. время Введите в ячейку F4 — Зараб. плата Форматирование шапки таблицы

1. Выделить методом протягивания диапазон A3: F3

2. Дать команду Формат Ячейки На вкладке Выравнивание задать: Выравнивание по горизонтали По центру Выравнивание по вертикали По центру Установить флажок

Переносить по словам На вкладке Границы задать: внешние и внутренние жирные линии На вкладке Шрифт задать: размер шрифта — 10 Полужирный Ввести исходные данные (независимые)

1. В ячейки B5: B12 ввести фамилии рабочих

2. В ячейки С5: C12 — должности

3. В ячейки D5: D12 — тарифные ставки В ячейки Е5: Е12 — отработанное время В ячейку A5 внести -1, и методом автозаполнения «размножить», создавая порядковые номера.

Ввести формулы:

1.Для начисления заработной платы в ячейку F5 формулу вводим формулу: = D5*E5

2.Методом автозаполнения «размножить» формулу в ячейки E7: E12. Для этого наводят указатель мыши на маркер заполнения в правом нижнем углу рамки, нажимают левую кнопку мыши и протаскивают вниз (рамка охватывает нужный диапазон).

Разработаем формулы для определения результатов:

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

1.В ячейку D14 ввести следующий текст Всего начислено

2.В ячейке F14 нужно получить результат суммирования содержимого ячеек F5: F12. Для этого :

Сделать текущей ячейку F14

Щелкнуть на кнопке на стандартной панели инструментов

Программа автоматически просуммировала диапазон F5: F13, можно щелкнуть в строке формул и изменить F5: F13 на F5: F12 и нажать клавишу Enter. (В ячейке F14 — результат вычисления по формуле =СУММ (F5:F12)

Автоматически выбранный диапазон F5: F14 включает и ту ячейку, которая содержит сумму. Выделите методом протягивания правильный диапазон F5: F12 и нажмите клавишу Enter. (В ячейке F15 — результат вычисления по формуле = СРЗНАЧ (F5:F12) В ячейку D16 ввести следующий текст Макс. Заработок В ячейку F16 необходимо ввести функцию, которая бы вычисляла максимальное значение из диапазона F5: F12. Повторить действия пунктов, выбирая нужную функцию МАКС. Таким образом в ячейке F16 должен появиться результат вычислений по формуле =МАКС (F5:F12) В ячейку D17 ввести следующий текст Миним. заработок В ячейку F17 необходимо ввести функцию, которая бы вычисляла минимальное значение из диапазона F5: F12 .

Для придания таблице вида необходимо ввести заголовок таблицы и отформатировать содержимое ячеек и диапазонов:

В ячейку B2 ввести текст Начисление заработной платы за Выделить методом протягивания диапазон B2: E2 и дать команду Формат Ячейки. На вкладке Выравнивание задать выравнивание По центру и установить флажок Объединение ячеек.

В ячейку F2 ввести название месяца, за который начисляется заработная плата — май

Сделать ячейку F2 активной и подать команду Формат Ячейки и на вкладке Границы установить широкую нижнюю рамку (границу).

Выделить ячейки D14: E14 установить шрифт Наклонный, на вкладке Выравнивание установить флажок Объединение ячеек (или щелкнуть по кнопке) Аналогично поступить и с диапазонами D15: E15, D16: E16, D17: E17.

Выделить диапазон F14: F17 и с помощью команды Формат Ячейки и соответствующих вкладок установить шрифт-размер 12, и широкую нижнюю рамку.

Измените содержимое ячейки E12 (Отработанное время Иванова) и убедитесь, что автоматически пересчитываются все зависимые ячейки — F5, F14, F15)

Для просмотра таблицы в формульном виде (для того, чтобы просмотреть формулы во всех ячейках таблице) подают команду СервисПараметры и на вкладке Вид устанавливают флажок Формулы, после нажатия кнопки OK диалогового окна Параметры Таблица примет вид:

Для того, чтобы вернуть к числовому виду подают команду СервисПараметры и на вкладке Вид снимают флажок Формулы

1. Воробьев В. В. Microsoft Excel 2000: Пособие для начинающихК.: 2000, 36 с.

2. Симонович С., Евсеев Г. Практическая информатика: универсальный курс. — М.: АСТ-ПРЕСС; Инфорком-Пресс, 2009,480 с. Симонович С., Евсеев Г., Алексеев А. Специальная информатика: универсальный курс. — М.: АСТ-ПРЕСС; Инфорком-Пресс, 2009,480 с.

3. Симонович С. Информатика: базовый курс. — СПб.: Питер, 2009, 640 с.

4. Рычков В. Самоучитель Excel 2000. — СПб.: Питер, 2009, 512 с.