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

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах. Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы (Вставка/ Таблицы/ Сводная таблица) могут пригодиться, если одновременно выполняются следующие условия:

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, …), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Несколько облегчит процесс построения Сводной таблицы, тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 (Вставка/ Таблицы/ Таблица) Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

В таблице имеются столбцы:

Через Диспетчер имен (Формулы/ Определенные имена/Диспетчер имен) откорректируем Имя таблицы на «Исходная_таблица».

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

Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».

Имея исходную таблицу в формате EXCEL 2007, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/Конструктор/ Сервис выбрать пункт Сводная таблица

В появившемся окне нажмем ОК. согласившись с тем. что Сводная таблица будет размещена на отдельном листе

На отдельном листе появится заготовка Сводной таблицы и Список полей размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы)

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

Заполним сначала раздел Названия строк т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец - синонимы).

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже).

Теперь поставим галочку в Списке полей у поля Продажи

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу).

Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

Детализация данных Сводной таблицы

Если возникли вопросы о том. какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

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

Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице. Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/Данные/ Обновить. Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы, вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить

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

Удалить Сводную таблицу можно несколькими способами. Первый - просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete

Изменение функции итогов

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

Если требуется, например, подсчитать количество проданных партий каждого Товара то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/Количество.

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки - наиболее важный товар, поэтому его нужно выводить в первой строке Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.

Изменение формата числовых значений

Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи) Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат.

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

Добавление новых полей

Предположим, что необходимо подготовить отчете продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/Параметры/Активное поле/ Свернуть все поле, можно свернуть Сводную таблицу, чтобы отобразить только продажи по Регионам

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная та,лица примет следующий вид

Меняем столбцы местами

Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место

Удаление полей

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

Другой способ - снять галочку напротив удаляемого поля в верхней части Списка полей. Но. в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой - для убыточных. Для этого:

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все

  • Ставим галочки в Списке полей у полей Группа. Продажи и Прибыль

  • Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета:

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке В1 (поле Прибыль) можно например, построить отчет о продажах Групп Товаров, принесших прибыль. После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры.

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

Несколько итогов для одного поля

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/Действия/ Очистить/ Очистить все

  • Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения:

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

2 столбца подсчитывающими суммы продаж: • в Сводной таолице выделите люоое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/Количество Задача решена.

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/Конструктор/ Макет/ Общие итоги. Не забудьте предварительно выделить любую ячейку Сводной таблицы.

Группируем числа и Даты

Предположим, что требуется подготовить отчете сроках сбыта В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д Для этого:

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/Действия/ Очистить/ Очистить все

  • Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей Поле Сбыт будет автоматически помещено в область Значения:

  • выделите единственное значение поля Сбыт в Сводной таблице, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/Количество

  • Перетащите мышкой еще одну копию поля Сбыт в область Названия строк:

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5. 6. 7…. дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

  • Выделите одно значение Сводной таблицы в столбце Названия строк:

  • В меню Работа со сводными таблицами/Параметры/Гоуппировать выберите пункт Гоуппировка по полю:

  • Появившееся окно заполните, как показано на рисунке ниже:

• Нажмите OK.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Гоуппировка по полю будет выглядеть так:

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

Условное форматирование ячеек Сводной таблицы

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

Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого.

  • Выделите все ячейки содержащие значения продаж;

  • Выберите пункт меню Гпавная/ Стили/ Условное форматирование/Правила отбора первых и последних значений/10 первых элементов:

  • Нажмите ОК.

Задание 1

1 Создать сводную таблицу, выводящую для каждого товара среднюю цену и суммарное количество. Создать 2 группы данных: для 2011 и 2012 годов (Произвести группировку по годам).

2. Создать сводную таблицу, выводящую для каждого товара среднюю цену с НДС (требуется предварительно создать вычисляемое поле — Цена с НДС). Создать группы данных для каждого Продавца.

Задание 2

В представленной ниже таблице, используя инструмент Excel Сводная таблица, рассчитать минимальный оклад сотрудников различных отделов по каждой должности. Составить сводную таблицу так, чтобы она отображала требуемую информацию по каждому уровню образования На основе полученной Сводной таблицы создать линейчатую диаграмму с группировкой. Отфильтровать информацию по сотрудникам Администрации и Бухгалтерии, имеющим высшее образование.