Автор: Индык Игорь Викторович e-mail: exelentc@yandex.ru

СМОТРЕТЬ УКРАИНОЯЗЫЧНЫЙ ВАРИАНТ СТАТЬИ

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

 

Итак, как мы уже писали ранее, для создания сводной таблицы Вам нужна простая базовая таблица. Вы можете создать её сами, или воспользоваться нашим примером, загрузив его по ссылке (простая таблица.xlsx).

 

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

 
 

Откроется следующее диалоговое окно:

 
 

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

 

Будьте внимательны — первая строка указанного диапазона не должна быть пустой — в этом случае Excel сообщит об ошибке. Также мы советуем Вам обязательно создать заголовки для каждого столбца базовой таблицы — это сделает настройку сводной таблицы намного удобней.

 

Помимо выбора исходной таблицы Excel предоставляет возможность использовать в качестве источника данных базы данных и таблицы, созданные в других программах (Access, SQL Server и других).

 

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

 

Нажав кнопку Ок после настройки нужных нам условий, мы получаем следующий рабочий лист:

 
 

В левой части находится область размещения сводной таблицы. Справа мы видим окно настройки сводной таблицы под названием «Список полей сводной таблицы». Если Вы случайно закрыли это окно, Вам достаточно кликнуть по области размещения — и окно настройки снова откроется.

 

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

 

а) в верхней части окна настроек отмечаем все названия необходимых нам столбцов:

 
 

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

 

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

 
 

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

 
 

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

 

Настройка нашей таблицы должна выглядеть вот так:

 
 

Тогда наша сводная таблица будет иметь следующий вид:

 
 

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

 

Excel автоматически обновляет макет и данные сводной таблицы. Если Вам необходимо сначала настроить макет, а потом вывести результат, воспользуйтесь опцией в окне настроек Отложить обновление макета.

 
 

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

 

Рассмотрим дополнительную задачу. Допустим, нам нужно узнать данные по объемам продаж не только в разрезе магазинов, но и в разрезе торговых марок, и даже отдельных моделей.

 

Для этого достаточно в окне настройки отметить галочками два дополнительных поля — Марка телефона и Модель телефона, и перетянуть эти поля в область Фильтр отчета. Excel добавит в сводную таблицу два новых фильтра, которые помогут нам быстро узнать нужную информацию:

 
 

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

В начало страницы

В начало страницы

В этой части самоучителя подробно описано, как создать сводную таблицу в Excel. Данная статья написана для версии Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?

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

Date	Invoice Ref	Amount	Sales Rep.	Region
01/01/2016	2016-0001	$819	Barnes	North
01/01/2016	2016-0002	$456	Brown	South
01/01/2016	2016-0003	$538	Jones	South
01/01/2016	2016-0004	$1,009	Barnes	North
01/02/2016	2016-0005	$486	Jones	South
01/02/2016	2016-0006	$948	Smith	North
01/02/2016	2016-0007	$740	Barnes	North
01/03/2016	2016-0008	$543	Smith	North
01/03/2016	2016-0009	$820	Brown	South
…	…	…	…	…

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

1. Выбираем любую ячейку из диапазона данных или весь диапазон, который будет использоваться в сводной таблице.ВНИМАНИЕ: Если выбрать одну ячейку из диапазона данных, Excel автоматически определит и выберет весь диапазон данных для сводной таблицы. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
   • Каждый столбец в диапазоне данных должен иметь своё уникальное название;
   • Данные не должны содержать пустых строк.
2. Кликаем кнопку Сводная таблица (Pivot Table) в разделе Таблицы (Tables) на вкладке Вставка (Insert) Ленты меню Excel.
3. На экране появится диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который должен быть использован для создания сводной таблицы. Здесь же можно указать, куда должна быть вставлена создаваемая сводная таблица. Можно выбрать существующий лист, чтобы вставить на него сводную таблицу, либо вариант – На новый лист (New worksheet). Кликаем ОК.
4. Появится пустая сводная таблица, а также панель Поля сводной таблицы (Pivot Table Field List) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.
5. В панели Поля сводной таблицы (Pivot Table Field List):
   • Перетаскиваем Sales Rep. в область Строки (Row Labels);
   • Перетаскиваем Amount в Значения (Values);
   • Проверяем: в области Значения (Values) должно быть значение Сумма по полю Amount (Sum of Amount), а не Количество по полю Amount (Count of Amount).

   В данном примере в столбце Amount содержатся числовые значения, поэтому в области Σ Значения (Σ Values) будет по умолчанию выбрано Сумма по полю Amount (Sum of Amount). Если же в столбце Amount будут содержаться нечисловые или пустые значения, то в сводной таблице по умолчанию может быть выбрано Количество по полю Amount (Count of Amount). Если так случилось, то Вы можете изменить количество на сумму следующим образом:

   • В области Σ Значения (Σ Values) кликаем на Количество по полю Amount (Count of Amount) и выбираем опцию Параметры полей значений (Value Field Settings);
   • На вкладке Операция (Summarise Values By) выбираем операцию Сумма (Sum);
   • Кликаем ОК.

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

Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).

В результате сводная таблица примет вот такой вид:

Обратите внимание, что формат валюты, используемый по умолчанию, зависит от настроек системы.

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

В последних версиях Excel (Excel 2013 или более поздних) на вкладке Вставка (Insert) присутствует кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables). Этот инструмент на основе выбранных исходных данных предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Вернемся к таблице продаж, которую Вы уже сделали. Такие таблицы могут быть огромными, ведь каждый день могут совершаться десятки операций, и соответственно, каждый день в таблице будут добавляться десятки новых строк.

А теперь представьте себе, что Вам нужно сделать отчет о проделанной работе. Например, Вам нужно показать, сколько и каких товаров было продано в каждом месяце. Или кто из менеджеров сколько продал, и на какую сумму? Сколько было продано с каждого склада за определенные месяцы и так далее? Что делать в этом случае? Вручную считать долго и неудобно. На помощь приходит один из самых мощных инструментов Excel 2007 — сводная таблица.

Выделите хотя бы одну ячейку в таблице и нажмите Вставка — Сводная таблица. Будет показан диапазон данных для сводной таблицы (Ваша исходная таблица), и Вам предложат два варианта ее размещения: на новый лист или на уже существующий. Оставьте вариант на новый лист, и нажмите ОК.

У Вас появится новый лист Excel, а в меню появится два новых пункта: Конструктор и Параметры. Справа Вы увидите окно, в котором вверху будут прописаны названия столбцов таблицы, а внизу четыре поля: Фильтр отчета, Названия столбцов, Названия строк и Значения.

Теперь достаточно ставить галочки напротив тех названий столбцов, которые Вам нужны. Например, Вы хотите составить отчет по сумме продаж каждого менеджера. Для этого ставите галочки напротив строк Продано и Менеджер. Эти названия попадут в области ниже: Менеджер в область Названия строк, а Продано — в область значений. А слева Вы увидите готовый отчет.

По умолчанию сортировки идет по фамилиям менежденов. Если Вы хотите сделать сортировку по продажам, нажмите на раскрывающийся список левого заголовка, и выберите пункт Дополнительные параметры сортировки. Укажите параметры сортировки, например, по убыванию по полю Сумма по полю продано, и нажмите ОК.

Заголовки сводной таблицы также можно переименовывать. Выделите, например, заголовок Названия строк и в строке формул  напишите Менеджер. Заголовок будет переименован.

Давайте теперь сделаем сводную таблицу Excel, которая покажет нам, какой менеждер сколько продал товара, но и в какой месяц. Для этого поставьте галочку напротив строки Месяц. Эта строка попадет в область Названия строк. Перетащите ее в область Названия столбцов. Теперь отчет содержит информацию о деятельности каждого менеджера в течении месяца.

Теперь перетащите строку Менеджер назад, в область Названия строк, но на этот раз поставьте ее сверху, перед строкой Менеджер. Сводная таблица Excel приобретет совсем другой вид: в нем сначала сумма будет раскладываться по месяцам, а затем внутри каждого месяца будет показываться итог деятельности каждого менеджера.

Рассмотрим теперь возможность фильтрации данных в сводной таблице Excel. Поставьте галочку напротив строки Склад. И перетащите эту строку из области Названия строк в область Фильтр отчета. Вверху в сводной таблице Excel появится строка Склад, в которой будет возможность выбора: все, склад 001 или склад 002.

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

Видео о том, как создавать сводные таблицы в Excel 2007

Более подробные сведения Вы можете получить в разделах «Все курсы» и «Полезности», в которые можно перейти через верхнее меню сайта. В этих разделах статьи сгруппированы по тематикам в блоки, содержащие максимально развернутую (насколько это было возможно) информацию по различным темам.

Также Вы можете подписаться на блог, и узнавать о всех новых статьях.
Это не займет много времени. Просто нажмите на ссылку ниже:
    Подписаться на блог: Дорога к Бизнесу за Компьютером