Создание комплексных документов в excel
Практическая работа №1
Создание комплексных документов
в табличном процессоре Microsoft Office Excel
Цель работы: использование информационной технологии для создания комплексных документов в табличном процессоре Microsoft Office Excel и изучение основных приемов работы с многостраничной электронной книгой; использование возможностей табличного процессора Microsoft Office Excel для расчетов, графического представления данных и прогнозирования.
Теоретический материал
Табличный процессор Microsoft Office Excel 2007 дает возможность анализировать, использовать и обрабатывать данные для принятия решений. Он содержит набор инструментов для работы с информацией, представленной в табличной форме. Таблица представляет собой сложный объект, который состоит из элементарных объектов: строка, столбец, ячейка, диапазон ячеек.
Строка — это все ячейки, расположенные на одном горизонтальном уровне. Заголовки строк представлены в виде целых чисел.
Столбец — это все ячейки, расположенные в одном вертикальном ряду таблицы. Заголовки столбцов задаются буквами латинского алфавита.
Ячейка — это элементарный объект электронной таблицы. Адрес ячейки образуется из заголовков столбца и строки, на пересечении которых она находится.
Диапазон ячеек — это группа смежных ячеек, образующих прямоугольную область. Он задается указанием адресов первой и последней его ячеек, разделенных двоеточием.
Электронные таблицы, с которыми работает пользователь, называются рабочими листами. Документы электронных таблиц могут включать несколько рабочих листов и называются рабочими книгами. Книга в Microsoft Office Excel представляет собой файл, используемый для обработки и хранения данных.
В работе с электронными таблицами можно выделить три основных типа данных: число, текст и формула. Текстом в электронных таблицах является любая последовательность символов, состоящая из букв, цифр и пробелов. Число, в отличие от текста, может участвовать в вычислительных операциях. В зависимости от решаемой задачи важно выбрать наиболее подходящий формат представления данных. Для ввода данных в ячейку нужно её выделить (установить курсор на ячейке), ввести данные и нажать клавишу Enter.
Назначение электронной таблицы в первую очередь состоит в автоматизации расчетов над данными. Для этого в ячейки таблицы вводятся формулы, которые являются основным средством для анализа данных. С помощью формул можно складывать, умножать и сравнивать данные, а также объединять значения. Существует стандартный порядок выполнения вычислений, однако его можно изменить с помощью скобок.
Ввод любой формулы начинается со знака равенства. Если его пропустить, то вводимая формула будет воспринята как текст. В формуле используются адреса объектов (ячейки, строки, столбца, диапазона ячеек), которые называются ссылками. Ссылки позволяют связывать между собой любые ячейки электронной таблицы и проводить необходимую обработку табличных данных.
Ссылка на ячейку может быть относительной или абсолютной. Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются. Абсолютными называются ссылки, которые при копировании в составе формулы в другую ячейку не изменяются.
Заканчивается ввод формулы нажатием клавиши Enter. Результатом вычислений по арифметической формуле является число. Редактирование содержимого ячейки можно производить в строке формул.
Строкой формул называется специальная строка, расположенная над заголовками столбцов и предназначенная для ввода и редактирования формул и иной информации. Строка формул состоит из двух основных частей: адресной строки, в которой располагаются адреса выделенной ячейки или диапазона ячеек, и строки ввода, в которой отображается информация в ячейке.
Функции — это заранее определенные формулы, которые выполняют вычисления разной степени сложности по заданным величинам, называемым аргументами, и в указанном порядке. Microsoft Office Excel позволяет использовать большой набор функций, что облегчает расчеты в таблицах. Использование функций производится по следующей схеме:
1. Установить курсор в той ячейке, где ожидается получить ответ и удалить её содержимое.
2. Выполнить команду меню Формулы > Вставить функцию или вызвать Мастер функций нажатием кнопки, находящейся рядом со строкой редактирования.
3. На вкладке Библиотека функций выбрать категорию (финансовая, логическая, текстовая, дата и время, ссылки и массивы, математическая или другая) и название необходимой функции. Нажать кнопку ОК.
4. В появившемся диалоговом окне в соответствующих окошках указать необходимые аргументы функции. Нажать кнопку ОК.
Диаграмма является объектом электронной таблицы и предназначена для представления данных в графической форме. Microsoft Office Excel поддерживает различные типы диаграмм, что позволяет представлять данные наиболее понятным для аудитории способом. Создав диаграмму, можно изменить вид элементов диаграммы или удалить те элементы, которые не требуется отображать.
Линии тренда позволяют наглядно показать тенденции изменения данных и оценить значения, которые находятся за пределами фактических данных, то есть помогают анализировать задачи прогноза. Линию тренда можно добавить к ряду данных на диаграмме без накопления, линейчатой, плоской, биржевой, точечной или пузырьковой диаграмме, диаграмме с областями и гистограмме.
Задание 1
Сформировать и заполнить финансовую сводку за неделю. Построить диаграмму изменения финансового результата.
Последовательность выполнения
1. На компьютере в своей папке создать документ Microsoft Office Excel.
2. Дать документу имя Комплекс и открыть его.
3. Дать рабочему листу имя Результат.
4. Разместить на листе таблицу 1 согласно рисунку. Выделенные значения не нужно вносить с клавиатуры (они появляются после расчета).
5. Для ввода дней недели набрать Понедельник и произвести автокопирование до Воскресенья (левой кнопкой мыши протянуть вниз за маркер автозаполнения в правом нижнем углу ячейки).
6. Произвести расчет в графе «Финансовый результат» для понедельника по формуле:
= Доход – Расход (в ячейке D4 набрать формулу =В4-С4 и нажать клавишу Enter)
7. Скопировать полученную формулу для получения данных финансового результата за все дни недели (двойной щелчок мыши по маркеру автозаполнения в правом нижнем углу ячейки).
8. Изменить цвет отрицательных значений финансового результата на красный цвет.
8.1 Выделить протягиванием диапазон ячеек с результатами расчетов.
8.2 Выполнить команду меню Главная.
8.3 На вкладке Ячейки выбрать команду Формат > Формат ячеек.
8.4 Указать формат ячеек Денежный с выделением отрицательных чисел красным цветом, число десятичных знаков два.
8.5 Нажать кнопку ОК.
9. Рассчитать средние значения дохода и расхода.
9.1 Установить курсор в ячейку B11.
9.2 Выполнить команду меню Формулы.
9.3 На вкладке Библиотека функций выбрать категорию Другие функции > Статистические > СРЗНАЧ
9.4 В появившемся диалоговом окне в качестве первого числа выделить протягиванием диапазон ячеек В4:В10 с данными для расчета среднего значения дохода.
9.5 Нажать кнопку ОК для завершения ввода формулы.
9.6 Аналогично произвести расчет среднего значения расхода.
10. Рассчитать общий финансовый результат за неделю (на вкладке Библиотека функций можно воспользоваться кнопкой Автосумма на панели инструментов).
11. Построить диаграмму для анализа финансовых результатов по дням недели.
11.1 Выделить протягиванием диапазон ячеек с данными финансового результата D4:D10.
11.2 Выполнить команду меню Вставка.
11.3 На вкладке Диаграммы выбрать категорию Линейчатая.
11.4 Выбрать тип диаграммы Линейчатая с группировкой.
11.5 Выполнить команду меню Работа с диаграммами > Макет (диаграмма является активным объектом).
11.6 На вкладке Подписи использовать инструменты меню для внесения названия диаграммы и подписей данных.
12. Сохранить изменения, не закрывая документ Комплекс и программу Microsoft Office Excel.
Задание 2
Осуществить прогноз численности населения России на 2012 год добавлением линии тренда к ряду данных диаграммы.
Последовательность выполнения
1. В открытом документе Комплекс дать новому листу имя Тренд.
2. Разместить на листе таблицу 2 согласно рисунку.
3. Построить диаграмму численности населения.
3.1 Выделить протягиванием диапазон ячеек с числовыми данными о количестве человек (B3:G3).
3.2 Выполнить команду меню Вставка.
3.3 Выбрать тип диаграммы Гистограмма с группировкой.
3.4 Внести название диаграммы и подписи данных. Легенду не добавлять.
4. Создать подпись горизонтальной оси диаграммы.
4.1 Выполнить команду меню Работа с диаграммами > Конструктор (диаграмма является активным объектом).
4.2 На вкладке Данные выполнить команду Выбрать данные.
4.3 В открывшемся окне Выбор источника данных нажать кнопку Изменить в окне Подписи горизонтальной оси (категории).
4.4 Выделить протягиванием диапазон ячеек с годами в таблице 2 согласно приведенному рисунку.
4.5 Нажать кнопку ОК в окне Подписи оси.
4.6 Нажать кнопку ОК в окне Выбор источника данных.
5. Добавить линию тренда к созданной диаграмме.
5.1 Выполнить команду меню Работа с диаграммами > Макет (диаграмма является активным объектом).
5.2 На вкладке Анализ выполнить команду Линия тренда > Дополнительные параметры линии тренда.
5.3 В открывшемся окне Формат линии тренда указать параметры согласно приведенному рисунку.
5.4 Нажать кнопку Закрыть. На диаграмме будут показаны линия тренда и прогноз на один период вперед.
6. Сохранить изменения, не закрывая документ Комплекс и программу Microsoft Office Excel.
Задание 3
Рассчитать удельный вес каждого показателя в общей сумме бюджета в 2012 году.
Последовательность выполнения
1. В открытом документе Комплекс дать новому листу имя Удельный вес.
2. Разместить на листе таблицу 3.
Таблица 3. Влияние каждого показателя на общую сумму бюджета |
||
Показатели |
Поступления, млрд. руб. |
В процентах к итогу |
Страховые взносы |
101,48 |
?? |
Административные платежи |
75,03 |
?? |
Штрафные санкции |
192,77 |
?? |
Налоги на имущество |
100,87 |
?? |
Общая сумма бюджета: |
?? |
100% |
3. Установить форматы Денежный и Процентный для соответствующих ячеек в таблице, число десятичных знаков равным 2.
4. Рассчитать общую сумму бюджета.
5. Рассчитать удельный вес страховых взносов в бюджете по формуле:
= Поступления страховых взносов, млрд. руб. * Общая сумма бюджета
в процентах / Общая сумма бюджета, млрд. руб.
6. Рассчитать удельный вес остальных показателей в общей сумме бюджета, используя математическую пропорцию по аналогии с пунктом 5.
7. Сохранить изменения, не закрывая документ Комплекс и программу Microsoft Office Excel.
Задание 4
Сформировать и заполнить ведомости начисления заработной платы за два месяца.
Последовательность выполнения
1. В открытом документе Комплекс дать новому листу имя Зарплата октябрь.
2. Разместить на листе таблицу 4 для расчета заработной платы за октябрь.
Таблица 4. Ведомость начисления заработной платы |
||||||
За октябрь |
||||||
Табельный номер |
Фамилия И. О. |
Оклад (руб.) |
Премия (руб.) |
Всего начислено (руб.) |
Удержания (руб.) |
К выдаче (руб.) |
27% |
13% |
|||||
201 |
Иванов С. Г. |
4850 |
||||
202 |
Степанов С. И. |
5200 |
||||
203 |
Шорохов С. М. |
5550 |
||||
204 |
Галкин П. О. |
5900 |
||||
205 |
Портнов С. Ж. |
6250 |
||||
206 |
Степкина М. Г. |
6950 |
||||
207 |
Жарова К. Б. |
7300 |
||||
Всего: |
||||||
Минимальный доход: |
||||||
Максимальный доход: |
||||||
Средний доход: |
3. Установить формат Денежный и Процентный для соответствующих ячеек в таблице, число десятичных знаков равным 2.
4. Произвести расчет в графе «Премия» для первого работника по формуле:
= Оклад * Процент премии (27%)
5. Превратить в формуле относительную ссылку на ячейку Процент премии (27%) в абсолютную ссылку.
5.1 В строке формул установить курсор около адреса фиксируемой ячейки (D4).
5.2 Нажать на клавиатуре функциональную клавишу F4 (или ввести перед адресами строки и столбца значки доллара).
5.3 Нажать клавишу Enter.
6. Произвести расчет в графе «Всего начислено» для первого работника по формуле:
= Оклад + Премия
7. Произвести расчет в графе «Удержания» для первого работника по формуле:
= Всего начислено * Процент удержания (13%)
8. Превратить в формуле относительную ссылку на ячейку Процент удержания (13%) в абсолютную ссылку.
9. Произвести расчет в графе «К выдаче» для первого работника по формуле:
= Всего начислено — Удержания
10. Произвести автокопирование формул для получения данных о начислениях заработной платы и удержаниях за октябрь для всех работников.
11. Рассчитать итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» с использованием необходимых функций.
12. Скопировать содержимое листа «Зарплата октябрь» на новый лист.
12.1 На имени листа Зарплата октябрь нажать правую кнопку мыши.
12.2 Выбрать команду контекстного меню Переместить/скопировать.
12.3 Указать параметры согласно рисунку и нажать кнопку ОК.
13. Дать скопированному листу имя «Зарплата ноябрь».
14. Внести изменения в таблицу.
14.1 Исправить название месяца.
14.2 Между колонками «Премия» и «Всего начислено» вставить новую колонку «Доплата».
14.3 Внести в ячейку значение доплаты 5%.
14.4 Установить формат Денежный для ячеек колонки «Доплата».
14.5 Произвести расчет в графе «Доплата» для первого работника по формуле:
= Оклад * Процент доплаты (5%)
14.6 Превратить в формуле относительную ссылку на ячейку Процент доплаты (5%) в абсолютную ссылку.
14.7 Изменить формулу для расчета в графе «Всего начислено» для первого работника:
= Оклад + Премия + Доплата
15. Произвести автокопирование формул для получения данных о начислениях заработной платы за ноябрь для всех работников.
8. Сохранить изменения в документе Комплекс и закрыть программу Microsoft Office Excel.
9. Скопировать созданный во время практической работы файл во флэш-накопитель.
Контрольные вопросы
1. Какова цель практической работы?
2. Что такое элементарные объекты электронной таблицы строка и столбец в Microsoft Office Excel 2007?
3. Как образуется адрес ячейки? Что такое диапазон ячеек и как он задается в Microsoft Office Excel 2007?
4. Что такое рабочий лист и рабочая книга в Microsoft Office Excel 2007?
5. Основные типы данных в работе с электронными таблицами.
6. Назначение электронной таблицы. Как ввести формулу в Microsoft Office Excel 2007?
7. Что такое строка формул в Microsoft Office Excel 2007?
8. Как произвести автокопирование формул в электронной таблице?
9. Что такое ссылка в Microsoft Office Excel 2007? Что она позволяет сделать?
10. Что такое абсолютная и относительная ссылка в Microsoft Office Excel 2007?
11. Как преобразовать относительную ссылку в абсолютную ссылку? Где в практической работе были использованы абсолютные ссылки?
12. Что такое функция в Microsoft Office Excel 2007? Схема использования функций.
13. Какие функции были использованы при выполнении расчетов в практической работе?
14. Назначение объекта диаграмма. Последовательность действий при построении диаграммы в Microsoft Office Excel 2007.
15. Линия тренда и её назначение. Как задать линию тренда в Microsoft Office Excel 2007?
16. Как скопировать содержимое листа на новый лист?
17. Что такое удельный вес показателя? Как была использована математическая пропорция в практической работе?