Создание электронной книги. Относительная и абсолютная адресация в Excel.
Практическая работа №1.
Работа в MS EXCEL.
Цель работы.Изучение информационной технологии использования встроенных функций Excel для финансового анализа, относительной и абсолютной адресации в Excel.
Откройте программу MS Excel(Пуск – Программы – MS Office – MS Excel).
Все выполненные в данной практической работе задания сохраняйте в созданной Вами папке.
После выполнения каждого задания продемонстрируйте его преподавателю.
Создание и оформление таблиц. Использование функций.
Задание 1. Создать таблицу финансовой сводки за неделю (рис. 1), произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных.
Рис. 1 Исходные данные для задания 1
Порядок выполнения задания.
1) Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.
2) Оформите шапку таблицы – ячейки A3:D3 - (Формат/Ячейки): задайте параметр «переносить по словам», выберите горизонтальное и вертикальное выравнивание – по центру.
3) Введите названия колонок и исходные данные согласно рис. 1.
4) Произведите расчеты в графе «Финансовый результат» по следующей формуле:
Финансовый результат = Доход – Расход (в D4 вводим формулу = B4-C4).
5) Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (Формат/Ячейки/вкладка Число//формат Денежный/отрицательные числа – красные; число десятичных знаков задайте равное 2).
|
|
6) Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (Вставка/Функция или кнопка ).
7) В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат», можно воспользоваться кнопкой Автосуммирования ( ) или функцией СУММ.
8) Проведите форматирование заголовка таблицы (A1:D1): объедините ячейки (Формат/Ячейки/вкладка Выравнивание/Объединить ячейки), задайте вертикальное и горизонтальное выравнивание по центру (Формат/Ячейки/вкладка Выравнивание), начертание шрифта – полужирное, цвет – синий (Формат/Ячейки/вкладка Шрифт).
Конечный вид таблицы приведен на рис. 2.
Рис. 2 Таблица расчета финансового результата
9) Постройте диаграмму (линейчатого типа) изменения финансового результата по дням недели с использованием мастера диаграмм. Для этого выделите интервал ячеек финансового результата D4:D10, выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы – линейчатая, на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели A4:A10. Далее введите название диаграммы и подписей осей, дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера. Конечный вид диаграммы приведен на рис. 3.
|
|
Рис. 3 Конечный вид диаграммы задания 1
10) Произведите фильтрацию значений дохода, превышающих 4000р. (Данные/Фильтр/Автофильтр). В заголовках полей появятся стрелки выпадающих списков. Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся записей этого поля. Выберите команду фильтрации – Условие. В открывшемся окне «Пользовательский автофильтр» задайте условие «Больше 4000». Произойдет отбор данных по этому условию.
11) Сохраните созданную электронную книгу в своей папке под именем «Работа в Excel».
Задание 2. Заполните таблицу, произведите расчеты, выделите минимальную и максимальную сумму покупки (рис. 4); по результатам расчетов постройте круговую диаграмму суммы продаж.
Формулы для расчета:
Сумма = Цена * Количество;
Всего = сумма значений колонки «Сумма».
Рис. 4 Исходные данные для задания 2
Задание 3. Заполните ведомость учета брака, произведите расчеты, выделите максимальную, минимальную и среднюю суммы брака, а также средний процент брака; произведите фильтрацию данных по условию процент брака < 8%, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 5).
|
|
Формула для расчета:
Сумма брака = процент брака * Сумма зарплаты.
Рис. 5 Исходные данные для задания 3.
Задание 4. Заполните таблицу анализа продаж, произведите расчеты, выделите минимальную и максимальную продажи (количество и сумму); произведите фильтрацию по цене, превышающей 9000р., постройте гистограмму отфильтрованных значений изменении выручки по видам продукции (рис. 6). Формулы для расчета:
Всего = Безналичные платежи + Наличные платежи.
Выручка от продажи = Цена * Всего.
Рис. 6 Исходные данные для задания 4.
Создание электронной книги. Относительная и абсолютная адресация в Excel.
Задание 5. Создать таблицу ведомости начисления заработной платы за два месяца на разных листах электронной книги, произведите расчеты, форматирование, сортировку данных.
Исходные данные представлены на рис. 7, результаты работы – на рис. 8.
Порядок выполнения задания.
1) Создайте новую электронную книгу с именем «Ведомость зарплаты».
2) Создайте таблицу расчета заработной платы по образцу (рис. 7).
Рис. 7. Исходные данные для задания 5
|
|
3) Произведите расчеты во всех столбцах таблицы. При расчете премии используется формула Премия = Оклад * % премии, в ячейке D5 введите формулу = $D$4*C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.
Формулы для расчетов:
Всего начислено = Оклад + Премия;
Удержание = Всего начислено * Процент удержания (в ячейке F5 введите формулу = $F$4*E5);
К выдаче = Всего начислено – Удержания.
4) Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/Категория – Статистические функции).
5) Переименуйте ярлычок листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя.
Рис. 8. Итоговый вид таблицы расчета заработной платы за октябрь.
6) Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке «Создавать копию».
7) Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32 %. Убедитесь, что программа произвела пересчет формул.
8) Между колонками «Премия» и «Всего Начислено» вставьте новую колонку «Доплата» (Вставка/Столбец)и рассчитайте значение доплаты по формуле Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%.
9) Измените формулу расчета значений колонки «Всего начислено»:
Всего начислено = Оклад + Премия + Доплата.
10) Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта, меньше 7000 – красным, больше или равно 10000 – синим цветом шрифта (Формат/Условное форматирование).
11) Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по - столбец В).
12) Поставьте к ячейке D3 комментарий «Премия пропорциональна окладу» (Вставка/примечание). Конечный вид таблицы представлен на рис. 9
Рис. 9 Конечный вид зарплаты за ноябрь.
13) Сохраните файл «Ведомость зарплаты».
Задание 6. Сделайте примечания к двум-трем ячейкам.
Задание 7. Выполните условное форматирование оклада и премии за ноябрь месяц:
До 2000р. – желтым цветом заливки;
От 2000 до 10000р. – зеленым цветом шрифта;
Свыше 10000р. – малиновым цветом заливки, белым цветом шрифта.
Задание 8. Постойте круговую диаграмму начислений суммы к выдаче всех сотрудников за ноябрь месяц.
Задание 9. Сохраните изменения в файле «Ведомость зарплаты».
Дата добавления: 2018-05-13; просмотров: 2605; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!