Анализ данных Excel - сводные таблицы

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

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

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

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

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

Щелкните по таблице.
Щелкните вкладку ВСТАВИТЬ на ленте.
Щелкните Сводная таблица в группе Таблицы. Откроется диалоговое окно сводной таблицы.

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

В поле Таблица / диапазон введите имя таблицы.
Щелкните Новый лист, чтобы указать Excel, где хранить сводную таблицу.
Щелкните ОК.

Появятся пустая сводная таблица и список полей сводной таблицы.

Поля сводной таблицы

Заголовки в вашей таблице данных появятся в виде полей в сводной таблице.

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

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

Вы даже можете мгновенно изменить макет своей сводной таблицы. Для этого можно использовать области сводной таблицы.

В областях сводной таблицы вы можете выбрать -

Какие поля отображать в виде строк
Какие поля отображать в виде столбцов
Как обобщить ваши данные
Фильтры по любому из полей
Когда обновлять макет сводной таблицы
- Вы можете обновить его мгновенно, перетаскивая поля по областям, или
- Вы можете отложить обновление и получить его только при нажатии на ОБНОВЛЕНИЕ

Мгновенное обновление поможет вам поэкспериментировать с различными макетами и выбрать тот, который соответствует требованиям вашего отчета.

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

Вложение в сводную таблицу

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

Месяцы в столбцах.
Регион и продавец в строках в указанном порядке. т.е. значения продавца вложены в значения региона.
Обобщение производится по сумме заказа.
Фильтры не выбраны.

Результирующая сводная таблица выглядит следующим образом:

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

Порядок вложения меняется, и результирующая сводная таблица выглядит следующим образом:

Note- Вы можете четко видеть, что макет с порядком размещения - «Регион», а затем «Продавец» дает лучший и компактный отчет, чем макет с порядком размещения - «Продавец» и затем «Регион». В случае, если продавец представляет более одной области, и вам необходимо суммировать продажи по продавцу, тогда второй макет был бы лучшим вариантом.

Фильтры

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

Перетащите область из строк в фильтры в областях сводной таблицы.

Фильтр с меткой «Регион» отображается над сводной таблицей (если у вас нет пустых строк над сводной таблицей, сводная таблица сдвигается вниз, чтобы освободить место для фильтра.

Вы можете это увидеть -

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

Щелкните стрелку в поле справа от области фильтра. Появится раскрывающийся список со значениями области поля.

Отметьте опцию Select Multiple Items. Флажки появляются для всех значений.
Выберите Юг и Запад, отмените выбор других значений и нажмите ОК.

Данные, относящиеся только к Южному и Западному регионам, будут суммированы, как показано на снимке экрана, приведенном ниже -

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

Слайсеры

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

Нажмите АНАЛИЗИРОВАТЬ в разделе ИНСТРУМЕНТЫ Сводной таблицы на ленте.
Нажмите «Вставить слайсер» в группе «Фильтр». Появится поле «Вставить срезы». Он содержит все поля из ваших данных.
Выберите поля Регион и месяц. Щелкните ОК.

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

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

Выбранные элементы в слайсерах выделяются. Отобразится сводная таблица со сводными значениями для выбранных элементов.

Обобщение значений другими расчетами

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

В списке полей сводной таблицы

Выберите учетную запись поля.
Снимите выделение с поля «Сумма заказа».

Перетащите поле «Учетная запись» в область «Суммарные значения». По умолчанию отображается сумма счета.
Щелкните стрелку в правой части поля.
В появившемся раскрывающемся списке щелкните Параметры поля значений.

Появится окно "Параметры поля значения". Несколько типов вычислений отображаются в виде списка в поле Суммировать значение по -

Выберите Count в списке.
Пользовательское имя автоматически изменится на «Количество учетных записей». Щелкните ОК.

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

Инструменты сводной таблицы

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

Выберите сводную таблицу.

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

ANALYZE
DESIGN

АНАЛИЗИРОВАТЬ

Несколько из ANALYZE Команды ленты -

Установить параметры сводной таблицы
Параметры поля значения для выбранного поля
Развернуть поле
Свернуть поле
Вставить слайсер
Вставить временную шкалу
Обновить данные
Изменить источник данных
Переместить сводную таблицу
Порядок решения (если есть еще расчеты)
PivotChart

ДИЗАЙН

Несколько из DESIGN Команды ленты -

Макет сводной таблицы
- Варианты промежуточных итогов
- Варианты общего итога
- Формы макетов отчетов
- Параметры пустых строк
Параметры стиля сводной таблицы
Стили сводной таблицы

Расширяющееся и сворачивающееся поле

Вы можете развернуть или свернуть все элементы выбранного поля двумя способами:

Выбрав символ

или

слева от выбранного поля.
Щелкнув развернуть поле или свернуть поле на ленте АНАЛИЗ.

Выбрав символ «Развернуть»

или «Свернуть»

слева от выбранного поля.

Выберите ячейку, содержащую Восток, в сводной таблице.
Щелкните символ Свернуть

слева от Востока.

Все элементы под Востоком будут свернуты. Символ Свернуть

слева от Востока изменится на символ Развернуть

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

Щелкните символ «Развернуть»

слева от Востока. Будут отображаться все элементы ниже Востока.

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

Вы можете свернуть или развернуть все элементы сводной таблицы одновременно с помощью команд «Развернуть поле» и «Свернуть поле» на ленте.

Щелкните ячейку, содержащую Восток, в сводной таблице.
Щелкните вкладку АНАЛИЗ на ленте.
Щелкните Свернуть поле в группе Активное поле.

Все элементы поля Восток в сводной таблице свернутся.

Щелкните Развернуть поле в группе Активное поле.

Будут отображаться все предметы.

Стили представления отчетов

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

Щелкните Восток в сводной таблице.
Щелкните АНАЛИЗ.
Щелкните Параметры поля в группе Активное поле. Откроется диалоговое окно "Параметры поля".
Щелкните вкладку Layout & Print.
Установите флажок Вставлять пустую строку после каждой метки элемента.

Пустые строки будут отображаться после каждого значения поля «Регион».

Вы можете вставить пустые строки из DESIGN вкладка тоже.

Щелкните вкладку ДИЗАЙН.
Щелкните Макет отчета в группе Макет.
В раскрывающемся списке выберите Показать в форме структуры.

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

Отобразится сводная таблица в форме структуры с выбранным стилем.

Хронология в сводных таблицах

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