Задание 2. Составить отчет по продажам товаров за год с разбивкой по месяцам.

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

Исходные данные: Количество наименований товара - 10 (число строк в таблице); цена одной единицы товара; количество проданных единиц в каждом месяце.

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

Образец работы: Приводится в конце работы.

Порядок работы:

1. Установить для новой рабочей книги число листов, равное 13. В строке меню найдите Сервис - Параметры - Общие в окне Число листов в новой книге укажите 13;

2. Создайте новую рабочую книгу;

3. Задать для первых 12 листов имена ярлыков по именам месяцев, а для последнего листа - имя ИТОГО (правый щелчок на ярлыке и команда ПЕРЕИМЕНОВАТЬ);

4. Сгруппировать все 12 листов для одновременного оформления всех таблиц: для чего при нажатой клавише SHIFT щелкнуть мышью на ярлыке 1-го и 12-го листов: все ярлыки должны выделиться белым цветом.

5. Оформить шапку таблицы на первом листе следующего вида:

- Наименование

- Цена за единицу ($)

- Продано единиц (шт.)

- Объем продаж в $ % от общего объема

6. Задайте форматы столбцов: для столбца Наименование - текстовый, для Цены за единицу и Объем продаж денежный. Обозначение $, % от общего объема - процентный. В строке меню выберите Формат- Ячейки - Число - Числовой формат;

7. Ввести в левый столбец список товаров из 10 произвольных наименований;

8. В первой ячейке столбца Объем продаж создать формулу для подсчета объема продаж - цена умноженная на количество: начните вводить формулу со знака =;

9. Внизу таблицы создайте формулу для подсчета суммарного объема продаж; Вставка - функция - СУММ, обратите внимание на выделенный диапазон;

10. Скопируйте формулу для всего столбца, для чего зацепите маркер заполнения в правом нижнем углу и протяните его на весь столбец;

11. Создайте и скопируйте формулу для подсчета % от общего объема: % от общего объема = Объем продаж / суммарный объем продаж; Суммарный объем продаж есть величина абсолютная - поставьте значок $ перед заголовком столбца и номером строки;

12. На всех остальных листах шапка, список товаров и все формулы будут созданы АВТОМАТИЧЕСКИ!

13. Разгруппируйте листы, для чего щелкните правой кнопкой на ярлыке любого листа и выберите команду РАЗГРУППИРОВАТЬ, далее все вводимые данные будут относиться к одному листу;

14. Введите исходные данные Цена и Количество проданных единиц отдельно в каждую из 12 месячных таблиц;

15. Оформить лист ИТОГО: скопировать шапку таблицы (без двух последних столбцов) и наименования товаров с любого из 12 листов на лист ИТОГО. В таблице ИТОГО рассчитать количество проданных товаров за год: для этого установите курсор в первую ячейку столбца Продано единиц; выберите меню Вставка - Функция - СУММ; укажите диапазон суммирования по листам - щелкните на закладке листа Январь и, удерживая кнопку SHIFT, щелкните на листе Декабрь, теперь укажите адрес первой ячейкистолбца Продано - щелкните мышкой на этой ячейке;

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

17. Аналогично задайте формулу для подсчета итоговых объемов продаж;

18. Постройте диаграммы помесячных объемов продаж: Выберите в меню Вставка - диаграмма, далее следуйте указаниям мастера диаграмм;

19. Выполните объединение всех 12 таблиц в одну сводную таблицу. Для этого:

- Добавьте новый лист с именем СВОДКА;

- Оформите шапку сводной, оставив только столбцы ПРОДАНО ЕДИНИЦ и ОБЪЕМ ПРОДАЖ;

- Переименовать каждый из 12 листов следующим образом: 01янв, 02фев, 03мар,…,12дек. Сделать это необходимо, иначе при консолидации порядок месяцев изменится.

- В создаваемой сводной таблице выделить диапазон столбцов ПРОДАНО ЕДИНИЦ и ОБЪЕМ ПРОДАЖ;

- Выбрать в меню команду ДАННЫЕ/КОНСОЛИДАЦИЯ

- В диалоговом окне выбрать функцию СУММ;

- Установить маркер в окне Ссылка;

- Щелкнуть мышкой в нижней части листа на закладке 01янв.;

- Выделить диапазон столбцов ПРОДАНО ЕДИНИЦ и ОБЪЕМ ПРОДАЖ и щелкнуть мышью в окне КОНСОЛИДАЦИЯ на кнопке ДОБАВИТЬ, в окне СПИСОК ДИАПАЗОНОВ появится выбранный Вами диапазон;

- Последовательно добавьте диапазоны всех 12 месяцев;

- В окошке СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ п оставьте галочку;

- Если вы добавили диапазоны всех 12 месяцев и не забыли поставить галочку в окне СОЗДАВАТЬ СВЯЗИ С ИСХОДНЫМИ ДАННЫМИ можете смело нажимать на OK;

- Результирующая таблица будет представлена в виде структуры, которую можно открывать или скрывать. Поэкспериментируйте, для чего щелкаете на значках "+" в левой части листа;

20. Введите для первого наименования название месяцев;

21. На основе объединенных данных постройте для первого наименования зависимости количества продаж и объем продаж по месяцам.

 

 

Задание 3. Оформить таблицу в соответствии с образцом и провести расчеты (на отдельном листе)

Найти:

1. сумму, начисленную каждому сотруднику (12 * (оклад) * (число дней) / (расчетное число дней))

2. сумму подоходного налога (12%)

3. сумму, выдаваемую каждому сотруднику

4. премиальные (если отработано >=15; то премиальные – 3% от начислено; иначе - 0)

5. сводные данные: суммарную, среднемесячную, минимальную и максимальную зарплату

6. процент зарплаты каждого сотрудника от общей суммы

7. построить 2 диаграммы различных видов по данным:

а) сотрудники – начислено;

б) сотрудники – к выдаче.