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

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

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

  • имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
  • необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
  • этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, Расширенного фильтра;
  • исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

 

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

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

  • каждый столбец должен иметь заголовок;
  • в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» - названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
  • в таблице должны отсутствовать полностью незаполненные строки и столбцы;
  • в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
  • избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц.

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

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

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

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

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

  • Товар – наименование партии товара, например, «Апельсины»;
  • Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
  • Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

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

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

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

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все;
  • Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
  • Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

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

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

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

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

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все;
  • Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
  • выделите единственное значение поля Сбыт в Сводной таблице, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество.
  • Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

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

  • Выделите одно значение Сводной таблицы в столбце Названия строк;
  • В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю;
  • Появившееся окно заполните, как показано на рисунке ниже;

  • Нажмите ОК.

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

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

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

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

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

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.4 (22 оценок)

Комментарии

Гость (не проверено)

клево

marslena (не проверено)

отлично! Понятно и доступно!

алексей (не проверено)

СПАСИБО!=)

Оксана (не проверено)

Очень понятно и ооооочень полезно:))))) Спасибо!

Владимир (не проверено)

Очень полезная статья! Спасибо автору! )))

Яна (не проверено)

Очень интересно и познавательно, несмотря на то, что я и до этого пользовалась сводными таблицами.

Кристина (не проверено)

спасибо, ёмко и просто

Татка (не проверено)

Наверное, оч интересно и познавательно. еще не прочитала, но хочу изучить дома. спс

Ерема (не проверено)

Очень хороший материал.Спасибо большое!!!Очень помогло!!)))

Катрин (не проверено)

Доброго времени суток! А как перевести СТ в значение (в такую же таблицу, как сводная, но уже не привязанную к облавсти данных), сохранив формат и группировку?

Creator

Вы можете удалить исходную таблицу, СТ останется без изменений, но она останется сводной таблицей, с ней можно продолжать работать. Это происходит потому, что в ячейках СТ находятся только значения. Если Вы все же хотите преобразовать СТ в простой диапазон ячеек, то в полной мере это сделать не удастся (например, фильтр отчета не будет работать). Но, для несложной СТ можно в соседний диапазон или лист скопировать данные СТ и вставить их как значения, затем с помощью инструмента Формат по образцу (Главная/ Буфер обмена) отформатировать диапазон, затем применить Автофильтр и вставить формулы итогов.

Владимир (не проверено)

Хорошая статья, спасибо. У меня вопрос. Я отформатировал СТ, а потом понадобилось добавить новый столбец в исходную таблицу. Стролбец я добавил, но в списке столбцов СТ он не появился. Пришлось удалять СТ и создавать по-новой. Как можно добавить в СТ новый столбец в этом случае?

Creator

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

Ольга Комарова (не проверено)

А если новый столбец в СТ появился, а вот числовые данные из диапазона не выводятся в СТ.
Форматы данных проверяла.

Creator

Без файла трудно понять, пришлите на creator@excel2.ru

Викуся (не проверено)

Спасибо за отличную статью! Все доступно и понятно изложено)

Алексей (не проверено)

А как быть? Если данные в PivotTable загружаются из MS SQL и их столько, что размер файла превышает 100Mb. Каким образом уменьшить количество данных и оставить возможность пользователю самому выбирать диапазон для работы?
Возможно использовать параметризированный запрос к SQL с выборкой параметров лист:ячейка ?

Creator

Проще в SQL Server создать таблицы, содержащие данные только из определенного диапазона. Затем с помощью Мастера подключения данных в MS EXCEL создайте нужные файлы подключения данных и используйте их для подсоединения к серверу. Старайтесь использовать стандартные средства как SQL Server, так и EXCEL.

Александр Я. (не проверено)

Подскажите пожалуйста! Ситуация такая: по одному исходному файлу создаются 3 сводных таблицы одинаковой структуры(меняются только показатели). Можно ли как-то сделать так, чтобы, выбирая фильтр на одном отчете, данные менялись соответственно на двух других? Т.е. выбрали Магазин №1 в отчете с прибылью, соответственно этот же магазин автоматически выходил и на отчете и с товарооборотом, и с количеством. Отчеты по структуре одинаковы, меняются только показатели.

Creator

Без VBA это сделать нельзя, но можно сделать аналог Сводной таблицы с помощью формул и настроить фильтры в 3-х таблицах так, чтобы при изменении в первой таблице фильтр переустанавливался в 2-х других. Об этом читайте в статье Отчеты в MS EXCEL

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

Александр Я. (не проверено)

Хм, спасибо за подсказку. А если вывести фильтр на отдельную страничку? Т.е. на любом другом листе (не сводная таблица) будет фильтр, который будет действовать на 3 сводных таблицы соответственно. Или это тоже только через VBA&

Creator

В случае использования формул фильтр можно расположить в любом сесте книги. Фильтр можно сделать через Выпадающий список (см. статью Выпадающий список в MS EXCEL на основе Проверки данных).

Алексей (не проверено)

Я только начинаю сводные таблицы изучать. Эта статья мне понравилась!

Creator

Мне она самому нравится )))

Надя (не проверено)

Почему в базе данных добавляются данные,а в сводную таблицу они видоизменяются? Хотя итоги посчитано правильно

Creator

Поясните, что значит видоизменяются? или вышлите файл на creator@excel2.ru с пояснениями.

Александр Я. (не проверено)

а можно в сводной таблице рассчитать долю от промежуточных итогов?

Creator

Промежуточные итоги, например, сумма или среднее, лишь число, вычисляемое по определенной формуле. Не совсем понятно, что значит "доля от промежуточных итогов"?

Никита (не проверено)

Большое спасибо автору! Очень понятно и доступно.

Олег

У меня есть в качестве исходных данных Отчет по проводкам, экспортированный из 1С, необходимо создать СТ в нужной конфигурации. Но Отчет по проводкам содержит массу пустых ячеек и массу лишней инфы... Из-за этого СТ выходит корявая. Как быть?

Creator

Лишние столбцы и строки можно удалить. Пустые ячейки заполните 0 или значениями, подходящими по смыслу.

Лидия

Только начала изучать сводные таблицы и сразу возник вопрос.. Как создать сводную таблицу, если есть несколько таблиц с исходными данными?

Creator

Никак. Нужно предварительно объединить 2 таблицы в 1. см. раздел Объединение и разделение списков

Роман

Добрый день. Подскажите пожалуйста, у меня есть сводная таблица. Допустим, в ней продажи по годам и по регионам. То есть, если нажать на год, он разворачивается на регионы и я вижу продажи в разрезе регионов, если год сворачиваю, вижу суммарную продажу за весь год. Данные по продажам - это сумма и количество(2 столбца). Я хочу сделать вычисляемое поле - средняя цена, то есть сумму разделить на количество. Но при агрегатной функции Суммирование, сводная таблица вначале делит каждую строку(по регионам), а потом суммирует, а не наоборот. Как можно в сводной таблице организовать столбец, в котором бы образно говоря, каждая его ячейка всегда делила то что находится от неё слева на 2 столбца, на то, что находится от неё на один столбец левее. Типа =RC[-2]/RC[-1]. Чтоб при развёрнутом отчёте я видел среднюю цену по каждому региону, а при свёрнутом, среднюю цену за год(а не сумму средних цен по каждому региону)

Creator

Все проще, см. раздел статьи Изменение функции итогов. Выберите вместо функции Сумма функцию Среднее (на картинке в этом разделе это видно)

Роман

Да, в данном примере Среднее подойдёт. Но я его привёл в пример просто чтоб было на чём объяснять что я хочу узнать. Но если пример будет сложнее, скажем будет ещё один столбец Роялти, который для каждой строки будет равен символической единице, и формула будет =RC[-2]/RC[-1]*RC[-3], где RC[-3] как раз наш новый параметр(который равен 1 и при умножении на 1, формула по идее не должна давать другой результат), то вот среднее, по этой формуле уже будет выдавать не тоже самое если просто нарисовать такую же формулу ссылаясь на конкретные ячейки итоговых значений. Пример можно посмотреть вот тут:
https://yadi.sk/i/abiS1rXAkwBco