Использование сводной таблицы

Отчеты сводной таблицы – или сводные таблицы – сделайте данные в ваших листах более управляемыми, суммируя данные и позволяя вам манипулировать им по-разному. Сводные таблицы могут быть незаменимым инструментом при использовании с большими и сложными электронными таблицами, но их можно использовать и с более крупными электронными таблицами.

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

Необязательно: этот пример можно загрузить для дополнительной практики.

Использование сводной таблицы для ответа на вопросы

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

Статистика продаж компании

Предположим, мы хотели ответить на вопрос. Какова сумма, продаваемая каждым продавцом? Это может занять много времени, потому что каждый продавец появляется в нескольких строках, и нам нужно будет добавить все суммы заказа для каждого продавца. Конечно, мы могли бы использовать функцию Subtotal,чтобы добавить их, но у нас все равно будет много данных для просеивания.

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

Готовая сводная таблица

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

Чтобы создать сводную таблицу:

  1. Выберите таблицу или ячейки, включая заголовки столбцов, содержащие данные, которые вы хотите использовать.
  2. На вкладке « Вставка » нажмите команду « Сводная таблица» .
    Команда PivotTable
  3. Появится диалоговое окно Create PivotTable . Проверьте правильность настроек и нажмите « ОК» .
    Диалоговое окно Create PivotTable
  4. Пустой PivotTable появится слева, и Список полей будет отображаться справа.
    Бланк сводной таблицы и списка полей

Чтобы добавить поля в сводную таблицу:

Вам нужно будет определить, какие поля добавить в сводную таблицу. Каждое поле является заголовком столбца из исходных данных. Может быть полезно вспомнить вопрос, на который вы пытаетесь ответить. В этом примере мы хотим узнать общую сумму, проданную каждым продавцом , поэтому нам понадобятся поля Order Amount и Salesperson .

  1. В списке полей поместите галочку рядом с каждым полем, которое вы хотите добавить.
  2. Выбранные поля будут добавлены в одну из четырех областей под списком полей. В этом примере поле Salesperson добавляется в область Row Labels , а сумма заказа добавляется в область значений . Если поле не находится в нужной области, вы можете перетащить его на другой.
  3. Теперь сводная таблица показывает сумму, проданную каждым продавцом .
    Добавление полей в сводную таблицу

Как и в случае с обычными данными в электронной таблице, вы можете сортировать данные в сводной таблице с помощью команды « Сортировка и фильтр» на вкладке « Главная ». Вы также можете применить любой тип форматирования, который вы хотите. Например, вы можете изменить формат чисел на валюту . Однако имейте в виду, что некоторые типы форматирования могут исчезнуть при изменении Сводной таблицы.

Если вы измените какие-либо данные на вашем исходном листе, сводная таблица не будет обновляться автоматически . Чтобы вручную обновить его, выберите «Сводная таблица», а затем перейдите в « Параметры обновления» .

Поворот данных

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

В нашем примере мы использовали сводную таблицу для ответа на вопрос. Какова общая сумма, продаваемая каждым продавцом? Теперь мы хотели бы ответить на новый вопрос: Какова общая сумма, продаваемая за каждый месяц ? Мы можем сделать это, изменив метки строк .

Чтобы изменить метки строк:

  1. Перетащите любые существующие поля из области Row Labels , и они исчезнут.
    Перетаскивание поля из ярлыков Row
  2. Перетащите новое поле из списка полей сводной таблицы в область ярлыков строк . В этом примере мы будем использовать поле Month .
    Перетаскивание нового поля в Row Labels
  3. Сводная таблица будет настраиваться для отображения новых данных. В этом примере теперь он показывает общую сумму заказа за каждый месяц .
    Обновленная сводная таблица

Чтобы добавить метки столбцов:

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

  1. Перетащите поле из списка полей сводной таблицы в область « Ярлыки столбцов» . В этом примере мы будем использовать поле Region .
    Добавление поля в ярлыки столбцов
  2. Теперь PivotTable будет иметь несколько столбцов. В этом примере для каждого региона есть столбец .
    Обновленная сводная таблица

Использование фильтров отчетов

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

Чтобы добавить фильтр отчета:

  1. Перетащите поле из списка полей в область фильтра отчетов . В этом примере мы будем использовать поле Salesperson .
    Добавление фильтра отчета
  2. Фильтр отчетов появляется над сводной таблицей. Нажмите стрелку раскрывающегося списка в правой части фильтра, чтобы просмотреть список элементов.
  3. Выберите элемент, который хотите просмотреть. Если вы хотите выбрать более одного элемента, поставьте галочку рядом с пунктом « Выбрать несколько элементов» и нажмите « ОК» . В приведенном ниже примере мы выбираем четырех продавцов.
    Использование фильтра отчетов
  4. Нажмите « ОК» . Сводная таблица будет корректироваться, чтобы отражать изменения.
    Обновленная сводная таблица

Срезы

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

Чтобы добавить слайсер:

  1. Выберите любую ячейку в сводной таблице. На ленте появится вкладка « Параметры » .
  2. На вкладке « Параметры » нажмите команду « Вставить слайсер» . Появится диалоговое окно.
    Команда Insert Slicer
  3. Выберите нужное поле. В этом примере мы выберем Salesperson . Затем нажмите « ОК» .
    Выбор поля
  4. Слайсер появится рядом с сводной таблицей. Каждый выбранный элемент будет выделен синим цветом . В приведенном ниже примере слайсер содержит список разных продавцов, и в настоящее время четыре из них выбраны.
    Слайсер с четырьмя выбранными элементами

Использование слайсера:

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

  • Чтобы выбрать один элемент, щелкните его.
  • Чтобы выбрать несколько элементов, удерживайте клавишу Control (Ctrl) на клавиатуре, а затем щелкните каждый элемент, который вы хотите.
  • Вы также можете выбрать несколько элементов, нажав и перетащив мышь. Это полезно, если нужные элементы смежны друг с другом или если вы хотите выбрать все элементы .
  • Чтобы отменить выбор элемента, нажмите клавишу Control (Ctrl) на клавиатуре, затем щелкните элемент.
Ctrl-клик для выбора нескольких элементов

Использование PivotChart

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

Чтобы создать PivotChart:

  1. Выберите любую ячейку в сводной таблице. На ленте появится вкладка « Параметры » .
  2. На вкладке « Параметры » нажмите команду « PivotChart» .
    Команда PivotChart
  3. В диалоговом окне выберите нужный тип диаграммы (например, 3-D Clustered Column ), затем нажмите OK .
    Выбор типа диаграммы
  4. PivotChart появится на рабочем листе. Если вы хотите, вы можете переместить его, щелкнув и перетащив.
    PivotChart

Если вы внесете какие-либо изменения в сводную таблицу, PivotChart автоматически настроится.

Задание!

  1. Откройте существующую книгу Excel 2010 . Если вы хотите, вы можете использовать этот пример .
  2. Создайте сводную таблицу, используя данные в книге.
  3. Эксперимент с различными метками строк и заголовками столбцов.
  4. Отфильтруйте отчет с помощью слайсера .
  5. Создайте PivotChart .
  6. Если вы используете пример , используйте сводную таблицу, чтобы ответить на вопрос, какой продавец продал самую низкую сумму в январе? Подсказка: сначала решите, какие поля вам нужны, чтобы ответить на вопрос.

Залишити відповідь