Задание сводные таблицы svodnay-v-excel

Сводные таблицы

Сводная таблица в Excel — это мощнейший инструмент для анализа данных, который поможет вам быстро:

• Подготовить данные для отчетов;

• Рассчитать различные показатели;

• Сгруппировать данные;

• Отфильтровать и проанализировать интересующие показатели.

• А также сэкономить вам кучу времени.

Из данной методички вы узнаете:

• Как сделать сводную таблицу;

• Как сгруппировать и перегруппировать данные в сводной таблице

• Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням...

Для начала научимся делать сводные таблицы.

Для того, чтобы сделать сводную таблицу, нам необходимо построить данные в виде простой таблицы. В каждом столбце должен быть  1 анализируемый параметр. Например, у нас 3 столбца:

Дата

Товар

Продажи в руб.

 И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб, см. рис:

После того, как вы подготовили данные для сводной таблицы, устанавливаем курсор в первый столбец в первую ячейку простой таблицы, далее заходим в меню "Вставка" и нажимаем кнопку "Сводная таблица"

Появится диалоговое окно, в котором:

вы можете сразу нажать кнопку "ОК", и сводная таблица выведется в отдельный лист.

а можете настроить параметры вывода данных сводной таблицы:

Диапазон с данными, которые будут выведены в сводную таблицу;

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

Нажимаем "ОК", сводная таблица готова и выведена в новый лист. Назовем лист "Сводная".

В правой части листа вы увидите поля и области, с которыми вы сможете работать. Поля вы можете перетащить в области и они выведутся в сводную таблицу на лист.

В левой части листа сводная таблица.

Теперь, зажимаем левой кнопкой мыши поле "Товар" - перетаскиваем его в "Название строк", поле "Продажи в руб." - в "Значения" в сводной таблице. Таким образом мы получили сумму продаж по товарам за весь период:

Группировка и фильтрация временных рядов в сводной таблице

Теперь, если мы хотим проанализировать и сравнить продажи товаров по годам, кварталам, месяцам, дням, то нам надо добавить соответствующие поля в сводную таблицу. Для этого переходим в лист "Данные", и после даты вставляем 3 пустых столбца. Выделяем столбец "Товар" и нажимаем "Вставить".

Важно, чтобы новые добавленные столбцы были внутри диапазона уже существующей таблицы с данными, тогда нам не надо будет переделывать сводную, чтобы добавить новые поля, достаточно её будет обновить.

Вставленные столбцы называем "Год", "Месяц", "Год-Месяц".

Теперь в каждый из этих столбцов добавляем соответствующую формулу для получения интересующего параметра времени:

В столбец "Год" добавляем формулу =ГОД(со ссылкой на дату);

В столбец "Месяц" добавляем формулу =МЕСЯЦ(со ссылкой на дату);

В столбец "Год - Месяц" добавляем формулу =СЦЕПИТЬ(ссылка на год;" ";ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

Теперь переходим в лист "Сводная", устанавливаем курсор на сводную таблицу, вызываем правой кнопкой мыши меню и нажимаем кнопку "Обновить". После обновления в списке полей у нас появляются новые поля сводной таблицы "Год", "Месяц", "Год - месяц", которые мы добавили в простую таблицу с данными:

Теперь давайте проанализируем продажи по годам.

Для этого поле "Год" мы перетаскиваем в "название столбцов" сводной таблицы. Получаем таблицу с продажами по товарам по годам:

Теперь мы хотим еще более глубже "опуститься" на уровень месяцев и проанализировать продажи по годам и по месяцам. Для этого в "название столбцов" перетаскиваем поле "месяц" под год:

Для анализа динамики месяцев по годам, можем месяцы переместить в область сводной "Название строк" и получить следующий вид сводной таблицы:

В данном представлении сводной таблицы мы видим:

продажи по каждому товару в сумме за целый год (строка с названием товара);

более подробно продажи по каждому товару в каждом месяце в динамике за 4 года.

Следующая задача, мы хотим убрать из анализа продажи за какой-то месяц (например, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.  Для этого в область сводной "Фильтр отчета" перетащим "Год - месяц" 

Нажимаем на появившейся над сводной фильтр и ставим галочку "Выделить несколько элементов". Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

Таким образом вы можете добавлять новые параметры изменения времени и делать анализ тех временных отрезков, которые вам интересны  и в  том виде, в котором вам это надо. Сводная таблица рассчитает показатели по тем полям и фильтрам, которые вы установите и добавите в неё в качестве интересующего поля.

А теперь рассмотрим дополнительные возможности сводных таблиц:

группирование числовых данных (или дат)

расчет количеств, сумм, средних и т.д. по группам и вообще по полям

Для этого дополним нашу таблицу новыми данными и немного изменим (см. лист: «Модифицированные данные»):

добавим менеджеров, совершивших операции по продаже

добавим отделыв которых они работают

усовершенствуем товарный ассортимент от 3 до 10 позиций

добавим клиентов, совершивших покупки

Создадим новую сводную таблицу по данным с листа «Модифицированная таблица» также как создали выше по данным с листа «данные». Т.е. устанавливаем курсор в первый столбец в первую ячейку простой таблицы, далее заходим в меню "Вставка" и нажимаем кнопку "Сводная таблица" и т.д.

Теперь создадим сводную таблицу, где будут сгруппированы продажи товара по интервалам с шагом в миллион рублей. Для этого перетащим в Названия строк поле «Продажи в руб»

Далее нажмем правой кнопкой мыши на колонке А и выберем пункт «Группировать»

И укажем шаг равным 1000000 и OK.

Всё. Названия строк это продажи сгруппированные с шагом в один миллион. Теперь добавим суммы продаж для каждого интервала и количество операций (будем считать каждую продажу как одну операцию, т.е. всего у нас операций по количеству строк – более 3000). Для этого перетащим Продажи в область Значений два раза.

И, нажав на треугольничках установим сумму для первого (1) поля по продажам и количество для второго (2) поля по продажам

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

А если количество операций по отделам, то достаточно просто поменять в Параметрах полей значений Сумму на Количество:

А чтобы сгруппировать по менеджерам и найти ТОП-3 товара по сумме продаж каждого нужна следующая схема:

Но одной схемы недостаточно, задачи немного сложнее. Дополнительно нужны установки фильтра:

Фильтр Товар –> По значению –> Первые 10

Установим три самых лучших товара по продажам, нажмем ОК:

И получим:

А теперь эту же таблицу чуть подправим так, чтобы были не суммы продажи, а доля в процентах. Для этого перейдем в Параметры полей значений нашей суммы продаж в области Значений.

Перейдем на вкладку Дополнительные вычисления и выберем «% от суммы по родительской строке»

А чтобы найти первых двух самых лучших клиентов для менеджеров можно использовать следующую схему:

Здесь аналогично работаем с фильтром, только для поля Клиент.

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

Задание:

1. Выполнить все примеры

2. Поэкспериментировать с вкладкой «Дополнительные вычисления» и значениями %-в

3. Создать сводную таблицу, сгруппированную по датам в диапазоне года в области строк

4. Создать сводную таблицу с двойной группировкой в области строк: по продажам и датам. Шаги группирования подобрать самостоятельно.

Литература

http://www.4analytics.ru