Microsoft Excel 2010 для оформления рассчетов
Лабораторная работа №2
Лабораторная работа по информатике для заочного отделения ИФ ГУАП на 2011-2012 учебный год
А. А. Сорокин
01.12.2011
1 ЦЕЛЬ РАБОТЫ
Получить начальное представление об электронных таблицах Microsoft Excel, ознакомиться с созданием и оформлением таблиц, использованием встроенных и произвольных формул, построением диаграмм на основе табличных данных.
2 МЕТОДИЧЕСКИЕ УКАЗАНИЯ
Табличный процессор (или электронная таблица) Excel представляет собой компьютерную программу, предназначенную для обработки данных:
выполнение различных вычислений с использованием аппарата функций и формул;
исследование влияния различных факторов на данные;
решение задач оптимизации;
получение выборки данных, удовлетворяющих определенным критериям;
построение графиков и диаграмм;
статистический анализ данных.
Окно Excel во многом схоже с окном Word (рисунок 1). Разница же в первую очередь связана со спецификой Excel: вся рабочая область– одна таблица, присутствует фиксированные обозначения строк и столбцов (т.н. координаты или адреса ячеек), документ (книга) разбит на листы, управление которыми осуществляется посредством мыши (двойной клик по названию, всплывающее меню правой клавиши мыши).
Рисунок 1 – Окно Microsoft Excel
1
Основным элементом любой таблицы является ячейка. В Excel у каждой ячейки есть два атрибута:
1.Хранимые данные – то, что было записано в ячейку (отображается в строке формул), например, числа, текст, формулы;
2.Отображаемые данные – то, как пользователь видит содержимое ячейки (отображается в ячейке), например, числа, оформленные с помощью формата ячейки, текст, оформленный с помощью меню шрифт, результаты вычисления формул.
Чтобы заполнить ячейку, достаточно нажать на нее курсором и начать вводить текст или числа. В большинстве случаев Excel сам распознает, что именно вы вводите и изменяет формат ячейки под содержимое, но возможны и казусы, когда при вводе числа “1.02” (одна целая две сотых) отображаемое значение становится “01.фев”. Чтобы избежать этого, нужно задать формат ячейки как текстовый. Для этого нужно выделить ячейку или диапазон ячеек, нажать правой кнопкой мыши и выбрать “формат ячеек…” (рисунок 2). В открывшемся меню на вкладке “Число” можно выбрать один из предустановленных форматов, например “Денежный” с 2 знаками после запятой (рисунок 3), а можно задать свой формат (см. справку MS Excel, статья “Пользовательский формат ячеек”).
Рисунок 2 – Меню “формат ячеек”
Рисунок 3 – Список форматов
2
Как уже говорилось выше, в ячейки можно вписывать в т.ч. и формулы. Формулы эти могут работать не только с числами, но и с содержимым ячеек (по их адресам). Любая формула начинается с символа “=”. Формулу можно ввести вручную (рисунок 4) или выбрать из списка готовых (рисунок 5), который открывается кнопкой “fx” (функция), так же существует возможность использовать быстрые формулы (рисунок 6). Готовые формулы разбиты на несколько категорий, в каждой существует множество специализированых формул, например: в математических есть не только СУММ, но СЛУЧМЕЖДУ, которая подставляет в ячейку случайное число в заданном диапазоне, а в логических есть ЕСЛИ, которая проверяет истинность выражения и в зависимости от результата выводит определенное значение или вызывает другую формулу. Параметром любой формулы может быть не только число или адрес ячейки, но и другая формула (рисунок 7). Чтобы указать при вводе формулы ячейку, не обязательно вручную вводить ее адрес, достаточно кликнуть по ней мышкой.
Рисунок 4 – Ручной ввод формулы
Рисунок 5 – Формула СУММ
Рисунок 6 – Быстрые формулы
Кроме готового набора формул Excel позволяет выполнять автозаполнение ячеек. Для его применения нужно выделить ячейку или диапазон, захватить квадратик в правом нижнем углу выделения и переместить или растянуть область выделения. Таким образом можно заполнить ячейки формулами (имеются в виду формулы из исходных ячеек), арифметической прогрессией (рисунок 8): если выделить идущие подряд ячейки со
3
значениями “1” и “2” и заполнить ими область из 100 ячеек, то Excel пронумерует ячейки от 1
до 100.
Рисунок 7 – Вложенные функции
Рисунок 8 – Автозаполнение
При использовании автозаполнения, если в исходных ячейках были формулы с адресами ячеек, Excel сменит Адреса ячеек относительно текущего положения формул: если формулы перемещались по вертикали, то поменяет номер строки, если по горизонтали, то букву столбца. Это не всегда бывает удобно. Иногда в формуле нужно использовать ссылку на определенную ячейку, независимо от размещения формулы. Чтобы не изменять в каждой ячейке формулу, достаточно в изначальной формуле дописать символ “$” перед буквой столбца и номером строки нужной ячейки, например: “F1” -> “$F$1”.
Как и Word, Excel позволяет объединять ячейки и создавать различные границы для таблиц. Для объединения или разделения уже объединенных ячеек достаточно выделить необходимый диапазон и нажать на одну из кнопок из меню объединения ячеек (рисунок 9). Для того, чтобы задать границы, можно воспользоваться меню “Границы” (рисунок 10) или же открыть аналогичную вкладку из меню “Формат ячеек”.
Для удобства восприятия представленных в таблицах данных существуют различные виды диаграмм, найти которые можно на вкладке “Вставка”. Каждый вид диаграмм удобен для определенной цели. Для вставки просто диаграммы любого типа достаточно выделить область с данными в таблице и нажать на соответствующую диаграмму. Если область может быть использована для создания выбранного типа, то Excel вставит на текущем листе диаграмму и добавит группу вкладок “Работа с диаграммами” в ленту меню. На этих
4
вкладках можно изменить внешний вид диаграмм, сменить их тип, поменять входные данные, задать подписи к осям, при необходимости, сменить макет и многое другое. Любой объект на диаграмме (оси, легенда, ряд данных, область построения) обладает свойствами, которые можно изменить, открыв меню “Формат…” нажатием правой кнопки на этом объекте: так для осей меняются названия, шаг, подписи, а для ряда данных цвет, вид объекта и т.д.
Рисунок 9 – Объединение ячеек
Рисунок 10 – Меню “Границы”
5
3ЗАДАНИЕ
Вслучае выполнения данной работы “на дому”, кроме электронной версии работы требуется отчет, оформленный согласно приложению 1. Сдача работы без электронной версии работы, выполненной в Microsoft Excel, или отчета невозможна.
Откройте Microsoft Excel, при необходимости создайте новую пустую книгу. Создайте таблицы аналогично рисунку из приложения 2:
таблицы должны быть на 4 месяца (например, с января по апрель);
в каждой таблице должен быть список из 20 человек;
границы в таблицах должны быть выполнены согласно рисунку из приложения 2 (тонкая, толстая и двойная линия);
все денежные единицы в ячейках выводятся с помощью формата ячеек.
Заполните поля “Фамилия”, “Имя”, “Отчество” произвольными данными. Отсортируйте список по полю “Фамилия” (при необходимости по “Имени” и “Отчеству”). Заполните “Количество часов” случайными числами в диапазоне от 10 до 75 (СЛУЧМЕЖДУ). Заполните “из них сверхурочно” случайными числами от 0 до 15, при условии, что “количество часов” больше 15 (ЕСЛИ, СЛУЧМЕЖДУ). Рассчитайте “Сумму”, учитывая, что сверхурочные часы входят в общее количество часов. Рассчитайте “Итого” для каждого месяца. Оформите каждую таблицу аналогично таблицам с приложения 3. Удалите лишние листы из книги. Назовите текущий лист “Расчеты”.
Постройте гистограмму на новом листе для столбцов “Количество часов” и “из них сверхурочно” для всех четырех таблиц аналогично приложению 4. Сделайте различные фигуры для каждого месяца, оформив их как таблицы (по аналогии связи цветового оформления приложения 3 и приложения 4), смените подписи для горизонтальной оси, настройте легенду, сделайте заголовок. Назовите текущий лист “Количество часов по месяцам”.
Постройте лепестковую диаграмму на новом листе для столбца “Сумма” для всех четырех таблиц аналогично приложению 5. Оформите каждый месяц как таблицы (по аналогии связи цветового оформления приложения 3 и приложения 5), смените подписи категорий, настройте легенду, сделайте заголовок. Назовите текущий лист “Зарплата”.
Постройте круговую диаграмму на новом листе для значения “Итого” для всех четырех таблиц аналогично приложению 6. Оформите каждый месяц как таблицы (по аналогии связи цветового оформления приложения 3 и приложения 6), удалите подписи, настройте вывод процентной доли для каждого месяца, настройте легенду, сделайте заголовок. Назовите текущий лист “Соотношение зарплаты по месяцам”.
6
4 ПРИЛОЖЕНИЯ
Приложение 1. Оформление отчета
Отчет о лабораторной работе оформляется согласно ГОСТ 7.32-2001. Это относится ко всему отчету. Отчет должен содержать:
Титульный лист (действующий вариант титульного листа можно найти на http://standarts.guap.ru)
Цель работы
Задание
Название документа (или документов), в котором хранится выполненное задание
4 рисунка – скриншоты окна Microsoft Excel с разными листами книги
Вывод
Приложение 2. Таблица “Расчет почасовой оплаты труда”
Приведена таблица на 10 человек.
7
Приложение 3. Цветовое оформление таблицы
Частично приведены таблицы на 3 месяца по 10 человек.
8
Приложение 4. Объемная гистограмма “Количество часов по месяцам”
Гистограмма на 10 сотрудников за 3 месяца.
9