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

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

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

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

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

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

Нажмите на стол.
Нажмите вкладку INSERT на ленте.
Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно «Сводная таблица».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

фильтры

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

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

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

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

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

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

Установите флажок « Выбрать несколько элементов» . Флажки появляются для всех значений.
Выберите Юг и Запад, отмените выбор других значений и нажмите ОК.

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

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

Срезы

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

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

Нажмите АНАЛИЗ в разделе СРЕДСТВА СЧЕТА на ленте.

Нажмите Вставить слайсер в группе Фильтр. Откроется окно «Вставить слайсеры». Он содержит все поля из ваших данных.

Выберите поля Регион и месяц. Нажмите ОК.

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

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

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

Суммирование значений по другим расчетам

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

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

Выберите поле Учетная запись.
Отмените выбор суммы заказа поля.

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

Откроется окно «Настройки поля значений». Несколько типов вычислений отображаются в виде списка в поле Суммировать значение с помощью —

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

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

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

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

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

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

ПРОАНАЛИЗИРУЙТЕ
ДИЗАЙН

ПРОАНАЛИЗИРУЙТЕ

Некоторые из команд ленты ANALYZE :

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

ДИЗАЙН

Вот некоторые из команд ленты ДИЗАЙН :

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

Расширяющееся и разрушающееся поле

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

Выбрав символ или же слева от выбранного поля.
Нажав на поле «Развернуть» или «Свернуть» на ленте «АНАЛИЗ».

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

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

Все предметы под Востоком будут свернуты. Свернуть символ слева от Востока меняется на символ Развернуть ,

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

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

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

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

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

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

Нажмите «Развернуть поле» в группе «Активное поле».

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

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

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

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

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

Вы также можете вставить пустые строки на вкладке ДИЗАЙН .

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

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

Сводная таблица в Outline Form с выбранным стилем будет отображаться.

Временная шкала в сводных таблицах

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

Создайте сводную таблицу из этого диапазона с —

Расположение и продавец в строках в таком порядке
Продукт в колоннах
Сумма Суммы в Суммирующих значениях

Нажмите на сводную таблицу.
Нажмите вкладку INSERT.
Нажмите Временная шкала в группе Фильтры. Появятся временные рамки вставки.

Нажмите Дата и нажмите ОК. Появится диалоговое окно «Временная шкала», а на ленте появятся «Инструменты временной шкалы».

В диалоговом окне Timeline выберите MONTHS.
Из раскрывающегося списка выберите КВАРТАЛЫ.
Нажмите 2014 Q2.
Держите нажатой клавишу Shift и перетащите на 2014 Q4.

Сроки выбраны для Q2 — Q4 2014.

Сводная таблица фильтруется на эту временную шкалу.

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