Как создать простейшую сводную таблицу в Excel?

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

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

A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2016 2016-0001 $819 Barnes North
3 01/01/2016 2016-0002 $456 Brown South
4 01/01/2016 2016-0003 $538 Jones South
5 01/01/2016 2016-0004 $1,009 Barnes North
6 01/02/2016 2016-0005 $486 Jones South
7 01/02/2016 2016-0006 $948 Smith North
8 01/02/2016 2016-0007 $740 Barnes North
9 01/03/2016 2016-0008 $543 Smith North
10 01/03/2016 2016-0009 $820 Brown South
11 ... ... ... ... ...

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

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

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

    • Каждый столбец в диапазоне данных должен иметь своё уникальное название;
    • Данные не должны содержать пустых строк.
  2. Кликаем кнопку Сводная таблица (Pivot Table) в разделе Таблицы (Tables) на вкладке Вставка (Insert) Ленты меню Excel.

    Как создать сводную таблицу в Excel

  3. На экране появится диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.

    Как создать сводную таблицу в Excel

    Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который должен быть использован для создания сводной таблицы. Здесь же можно указать, куда должна быть вставлена создаваемая сводная таблица. Можно выбрать существующий лист, чтобы вставить на него сводную таблицу, либо вариант – На новый лист (New worksheet). Кликаем ОК.

  4. Появится пустая сводная таблица, а также панель Поля сводной таблицы (Pivot Table Field List) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.

    Как создать сводную таблицу в Excel

  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

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

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

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

12.02.2016 19:15
5403

Комментарии

Нет комментариев. Ваш будет первым!