Создание электронной книги. Относительная и абсолютная адресация в Excel.

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

Откройте программу MS Excel(Пуск – Программы – MS Office – MS Excel).

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

После выполнения каждого задания продемонстрируйте его преподавателю.

Создание и оформление таблиц. Использование функций.

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

1) Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.

2) Оформите шапку таблицы – ячейки A3:D3 — (Формат/Ячейки): задайте параметр «переносить по словам», выберите горизонтальное и вертикальное выравнивание – по центру.

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

4) Произведите расчеты в графе «Финансовый результат» по следующей формуле:

Финансовый результат = Доход – Расход (в D4 вводим формулу = B4-C4).

5) Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (Формат/Ячейки/вкладка Число//формат Денежный/отрицательные числа – красные; число десятичных знаков задайте равное 2).

6) Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (Вставка/Функция или кнопка ).

7) В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат», можно воспользоваться кнопкой Автосуммирования ( ) или функцией СУММ.

Проведите форматирование заголовка таблицы (A1:D1): объедините ячейки (Формат/Ячейки/вкладка Выравнивание/Объединить ячейки), задайте вертикальное и горизонтальное выравнивание по центру (Формат/Ячейки/вкладка Выравнивание), начертание шрифта – полужирное, цвет – синий (Формат/Ячейки/вкладка Шрифт).

11) Сохраните созданную электронную книгу в своей папке под именем «Работа в Excel».

Задание 2. Заполните таблицу, произведите расчеты, выделите минимальную и максимальную сумму покупки (рис. 4); по результатам расчетов постройте круговую диаграмму суммы продаж.

Задание 3. Заполните ведомость учета брака, произведите расчеты, выделите максимальную, минимальную и среднюю суммы брака, а также средний процент брака; произведите фильтрацию данных по условию процент брака < 8%, построить график отфильтрованных значений изменения суммы брака по месяцам (рис. 5).

Задание 4. Заполните таблицу анализа продаж, произведите расчеты, выделите минимальную и максимальную продажи (количество и сумму); произведите фильтрацию по цене, превышающей 9000р., постройте гистограмму отфильтрованных значений изменении выручки по видам продукции (рис. 6). Формулы для расчета:

Создание электронной книги. Относительная и абсолютная адресация в Excel.

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

Исходные данные представлены на рис. 7, результаты работы – на рис. 8.

1) Создайте новую электронную книгу с именем «Ведомость зарплаты».

2) Создайте таблицу расчета заработной платы по образцу (рис. 7).

3) Произведите расчеты во всех столбцах таблицы. При расчете премии используется формула Премия = Оклад * % премии, в ячейке D5 введите формулу = $D$4*C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Удержание = Всего начислено * Процент удержания (в ячейке F5 введите формулу = $F$4*E5);

4) Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/Категория – Статистические функции).

5) Переименуйте ярлычок листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя.

Рис. 8. Итоговый вид таблицы расчета заработной платы за октябрь.

6) Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Не забудьте для копирования поставить галочку в окошке «Создавать копию».

7) Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32 %. Убедитесь, что программа произвела пересчет формул.

Между колонками «Премия» и «Всего Начислено» вставьте новую колонку «Доплата» (Вставка/Столбец)и рассчитайте значение доплаты по формуле Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%.

9) Измените формулу расчета значений колонки «Всего начислено»:

10) Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта, меньше 7000 – красным, больше или равно 10000 – синим цветом шрифта (Формат/Условное форматирование).

11) Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по — столбец В).

12) Поставьте к ячейке D3 комментарий «Премия пропорциональна окладу» (Вставка/примечание). Конечный вид таблицы представлен на рис. 9

Задание 7. Выполните условное форматирование оклада и премии за ноябрь месяц:

Свыше 10000р. – малиновым цветом заливки, белым цветом шрифта.

Задание 8. Постойте круговую диаграмму начислений суммы к выдаче всех сотрудников за ноябрь месяц.

Задание 9. Сохраните изменения в файле «Ведомость зарплаты».

© 2014-2022 — Студопедия.Нет — Информационный студенческий ресурс. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав (0.014)

[expert_bq id=»1570″]Если интервал не был предварительно выделен, или в третьем пункте нажата Ok, то исправить положение можно выделением интервала для массива после вычисления, установкой курсора в поле содержимого ячейки строки формул и нажатием Shift Ctrl Enter. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для построения диаграммы обычно используют Мастер диаграмм, запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели инструментов Часто удобно заранее выделить область, содержащую данные, которые будут отображаться на диаграмме, но задать эту информацию можно и в ходе работы мастера