Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц
Оценка 4.6

Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц

Оценка 4.6
doc
13.05.2020
Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц
115. Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц.doc

Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц.

 

ЗАДАЧА. Используя таблицу с исходными данными о сбыте и объеме продукции (рис. 13), создать сводные и выборочные таблицы, позволяющие быстро анализировать итоговую информацию о сбыте по видам продукции, регионам, продавцам, годам.

 

Рисунок 13 – Исходная таблица

 

1.                Наберите заданную таблицу. Переименуйте рабочий лист в Исходные данные.

2.                Продолжите таблицу до апреля 2007 года, предусмотрев в ней 4 вида продукции (молоко, мясо, бакалея, напитки), 4 продавцов (Петров, Никитин, Марченко, Сидоров) и 4 региона (Северный, Южный, Восточный, Западный). Используйте автозаполнение ячеек (Сервис — Параметры — Правка — Автозаполнение ячеек). Сохраните таблицу.

3.                Отсортируйте по месяцам, использовав пользовательский порядок сортировки.

4.                Создайте сводную таблицу 1, задав в ней в качестве поля строки — Год, поля столбца — Продукция, поля данных — Сбыт. Используйте для вычисления сбыта функцию СУММ, задаваемую по умолчанию. Для создания сводной таблицы используется Мастер.

Порядок выполнения:

Ø  выделить исходную таблицу с названиями строк и столбцов;

Ø  выполнить команду Данные — Сводная таблица (или вывести на экран панель инструментов Сводная таблица (Вид — Панели инструментов) Сводная таблица — Мастер сводных таблиц;

Ø  указать «в списке или в базе данных МS Ехсе1»; будет выведен диапазон выделенных ячеек;

Ø  создать макет сводной таблицы, перетащив с помощью мыши поле Год в область строки, поле Продукция — в область столбца, поле Сбыт — в область данных;

Ø  выбрать новый лист для помещения на него таблицы;

Ø  переименуйте созданный рабочий лист в Год-Продукция.

 

5.                Измените ориентацию строк и столбцов в сводной таблице: установите курсор на любую ячейку сводной таблицы; вызовите Мастера сводных таблиц; протаскиванием мыши поменяйте местами поле строк Год и поле столбца Продукция.

6.                Измените макет сводной таблицы, заменив поле столбца Год на Район. Для этого удалите поле столбца Год, протащив за пределы сводной таблицы, а поле Район в область столбца.

7.                Создайте самостоятельно еще две сводных таблицы на основе данных следующей таблицы (рис. 14)

Каждую сводную таблицу начинать делать, открыв рабочий лист Исходные данные с выделенной таблицей. Каждую таблицу создавать на отдельном листе, переименовав их в соответствии с содержанием. Для выбора функций Макс и Мин — двойной щелчок на поле Сумма по сбыту в области данных.

8.                Преобразуйте сводную таблицу 2 в отчет, выведя данные за 2006 год

Ø  выведите данные за 2006 год;

Ø  выделите таблицу;

Ø  Правка — Копировать;

Ø  Правка — Специальная вставка — включите опцию Вставить Значения.

 

 

Поле строки

Поле столбца

Поле страницы

Поле данных

Функция

Таблица 2

 

Продукция

Год

Район

продавец

Сбыт

Объем

Сумм

Сумм

Таблица 3

 

Продукция

Год

Район

Сбыт

Сбыт

Макс

Мин

Рисунок 14 – Исходная таблица

 

9.       На основании исходных данных о сбыте и объеме продукции по месяцам создайте сводную ведомость, сгруппировав данные в кварталы.

Для этого:

Ø  откройте лист Исходные данные;

Ø  создайте сводную таблицу, в которой полем строк будет Месяц, полем столбца — Продукция, полем данных — Сбыт;

Ø  выделите месяцы Январь, Февраль, Март;

Ø  щелкните на кнопке Группировка на панели Сводная таблица;

Ø  переименуйте появившееся имя поля группы Месяц2 в Кварталы (вводите непосредственно вместо слова Месяц2), аналогично переименуйте элемент Группа1 в Квартал1;

Ø  объедините в кварталы остальные месяцы года.

 

10.   Сверните данные в таблице, обеспечив вывод итогов по кварталам. Для этого выполните двойной щелчок на имени квартала или установите курсор на имени квартала и щелкните на кнопке Скрыть детали на панели Сводная таблица. Для показа деталей — вывода данных по месяцам — щелкните на Показать детали.

 

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

Ø  откройте лист Исходные данные и скопируйте его на несколько рабочих листов, используя группировку;

Ø  выделите любую ячейку в таблице;

Ø  выполните команды Данные — Сортировка, в поле Сортировать по Продавец — по возрастанию;

Ø  Данные — Итоги, в поле «При каждом изменении выберите Продавец, в поле Использовать функцию — Сумм, Добавить итоги по — Сбыт.

 

12.   Составьте отчет о сбыте и объеме каждого вида продукции для каждого продавца:

Ø  в поле Сортировать выберите Продавец, в поле Затем — Продукция;

Ø  сначала обеспечить получение промежуточных итогов по продукции, затем по Продавцам. При этом следует снять флажок Заменить текущие итоги;

Ø  скрыть (Показать) детализирующие данные в таблице, щелкая на кнопках структурирования таблицы (+, -, 1..4).

 

13.   Составьте выборочный отчет о сбыте одного продавца в одном районе, используя Фильтр:

Ø  откройте рабочий лист Исходные данные;

Ø  выделите одну из ячеек таблицы;

Ø  Фильтр — Автофильтр. На именах столбцов появятся стрелки, открывающие списки, из списка Продавец выберите Марченко;

Ø  из списка Район — Западный.

Для восстановления всех записей — Данные — Показать все или еще раз Данные — Фильтр — Автофильтр.

 

14.   Составьте выборочный отчет при сложном критерии отбора, например, для продавца Петрова, района — Восточный сбыте более 3000:

Ø  откройте рабочий лист Исходные данные;

Ø  выделите две верхних строки таблицы и выполните команды Вставка — Строки, снимите выделение строк;

Ø  введите в эти строки в столбцы А, B, С следующее:

Продавец

Сбыт

Район

Петров

>300

Восточный

Ø  выделите таблицу или одну из ее ячеек;

Ø  Данные — Фильтр — Расширенный фильтр;

Ø  включите опцию Копировать результат в другое место, задайте Исходный диапазон и Диапазон условий (А1:С2), а также куда выводить найденные данные.

 

 


Скачано с www.znanio.ru

Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц

Лабораторная работа по теме Анализ сбыта продукции на основе сводных таблиц

Группировка на панели Сводная таблица; Ø переименуйте появившееся имя поля группы

Группировка на панели Сводная таблица; Ø переименуйте появившееся имя поля группы
Скачать файл