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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для этого:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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