Лабораторная работа задание 15 учет продажи книг в Excel, продается
В книжном магазине ведется учет поступления и продажи книг по трем отделам (проза, стихи, сказки) по авторам: Пушкин А.С., Лермонтов М.Ю., Толстой А.К., Толстой А.Н.
С начала текущего месяца вводится коэффициент индексации цены продажи книг 1,3.
При составлении таблиц ячейки не объединять!
Все постоянные значения и коэффициенты помещаются в первых строках (перед Таблицей 1) и для них устанавливается абсолютная адресация при использовании в формулах.
1) На отдельных Листах составить Таблицы 1, 2, 3 «Учета продажи книг по отделу … » по данным на первое число текущего месяца по каждому отделу по форме: отдел, дата поступления, автор, название книги, остаток на начало дня, количество поступивших экземпляров, закупочная цена, цена продажи (на 2% выше закупочной цены и с учетом индексации цен), количество проданных книг, остаток на конец дня, сумма чистой прибыли (= закупочная цена - цена продажи).
2) На отдельном Листе по данным Таблиц 1, 2, 3 составить Таблицу 4 «Учет продажи книг по магазину» по данным на первое число текущего месяца.
3) Установить ФИЛЬТР по столбцу Автор книги в Таблице 4. Для этого сделать копию Листа с Таблицей 4 - правой кнопкой мыши щелкнуть на вкладку с названием Листа, выбрать в контекстном меню ПЕРЕМЕСТИТЬ \ КОПИРОВАТЬ и отметить галочкой - Создать копию. Переименовать Лист с копией - Фильтр. В Копии Таблицы 5 установить ФИЛЬТР в менюДАННЫЕ \ СОРТИРОВКА И ФИЛЬТР \ ФИЛЬТР по столбцу Автор – выбрать Пушкин А.С. -, и проверить работу фильтра.
4) В Таблице 4 в столбце Дата поступления изделий установить в меню ФОРМАТ \ УСЛОВНОЕ ФОРМАТИРОВАНИЕ – выбрать условие: «между» - Дата поступления более … (10-е число предыдущего месяца, например: 10.02.13) и менее …(20-е число предыдущего месяца, например 20.02.13) \ Формат \ Вид – выбрать цвет для выделения - это поступления за вторую декаду предыдущего месяца.
5) На новом Листе на основе Таблицы 4 создать Таблицу 5 «Итоговая информация по отделам» по данным на первое число текущего месяца: отдел, суммарная чистая прибыль по отделу - используйте функцию СУММЕСЛИ(). Итоговой строкой рассчитать сумму чистой прибыли - Всего по магазину в целом. Построить Гистограмму соотношения чистой прибыли по отделам.
6) Составить Таблицу 6 «Учета продажи книг по магазину по фамилиям авторов книг» по всему магазину: автор, количество проданных книг (функция СЧЕТЕСЛИ()), суммарная чистая прибыль от продажи книг (функция СУММЕСЛИ()), средняя прибыль. Построить круговую диаграмму по средней прибыли от продажи книг отдельно по каждому автору.
7) Отсортировать данные Таблицы 6 по фамилиям авторов по алфавиту. Для этого сделать копию Листа с Таблицей 4. Переименовать Лист - Сортировка. Предварительно вместо столбцов с формулами вывести их ЗНАЧЕНИЯ. Для этого выделить столбец с формулами, поместить в буфер обмена – пиктограмма «копировать», выбрать в меню ПРАВКА \ СПЕЦИАЛЬНАЯ ВСТАВКА \. и отметить точкой ЗНАЧЕНИЕ. Сортировка осуществляется через меню ДАННЫЕ \ СОРТИРОВКА.
8) По данным Таблицы 4 (создав ее копию) составить Таблицу 7 «Учета продажи книг по магазину на 01.10.10» с учетом коэффициента индексации Цены продажи книг – добавить вычисляемый столбец, используя функцию матричного умножения. Подсчитать суммарную чистую прибыль после индексации.
9) Используя данные Таблицы 7 составить Таблицу 8 по форме: Автор, название книги, Цена1, Цена2, чистая прибыль1, чистая прибыль2. Итоговой строкой рассчитать итоговую чистую прибыль 1 и 2. Построить Гистограмму соотношения итоговой чистой прибыли 1 и 2.
10) Составить «Сводную таблицу» по Таблице 7 - (курсор внутри Таблицы 7) - в EXCEL 2007 - Меню ВСТАВКА \ Сводная таблица \ макет Сводной таблицы задается перетаскиванием полей из исходной Таблицы: в области Строк задайте вывод данных по полю Автор книги, а в области Значений – Стоимость продажи книги, в области Фильтр отчета – Отдел. Задать вывод максимального значения в поле Стоимость продажи книг, для этого щелкнуть два раза левой кнопкой мыши на заголовке этого поля, появится окно - Вычисление поля сводной таблицы - выбрать кнопку «Дополнительно» и задать значение «максимум». Расположить данные в поле Стоимость продажи книг в порядке убывания: щелкнуть два раза левой кнопкой мыши на заголовке поля Автор книги - появится окно Вычисление поля сводной таблицы - выбрать кнопку Дополнительно, задать значение по убывания для поля Стоимость продажи книг.
11) Построить на отдельном Листе «Сводную диаграмму»: в Строках задайте вывод по полю Отдел, а в области Значений – Стоимость продажи книги.
В случае изменения данных исходной таблицы воспользуйтесь кнопкой - ! Обновить.
Выполнить анализ результатов расчётов и сделать выводы.
Распечатать таблицы и графики в виде отчета.