Сводные таблицы Excel – поля
Поля сводной таблицы – это панель задач, связанная с сводной таблицей. Панель задач «Сводная таблица» состоит из полей и областей. По умолчанию панель задач отображается в правой части окна с полями, отображаемыми над областями.
Поля представляют столбцы в вашем диапазоне данных или в таблице Excel и будут иметь флажки. Выбранные поля отображаются в отчете. Области представляют макет отчета и расчеты, включенные в отчет.
В нижней части панели задач вы найдете параметр «Отложить обновление макета» с кнопкой «Обновить» рядом с ним.
По умолчанию этот параметр не выбран, и любые изменения, внесенные в выбор полей или в параметры макета, мгновенно отражаются в сводной таблице.
Если вы выберете это, изменения в ваших выборках не будут обновлены, пока вы не нажмете кнопку ОБНОВИТЬ .
По умолчанию этот параметр не выбран, и любые изменения, внесенные в выбор полей или в параметры макета, мгновенно отражаются в сводной таблице.
Если вы выберете это, изменения в ваших выборках не будут обновлены, пока вы не нажмете кнопку ОБНОВИТЬ .
В этой главе вы поймете детали о полях. В следующей главе вы поймете детали об Областях.
Панель задач полей сводной таблицы
Вы можете найти панель задач полей сводной таблицы на рабочем листе, где у вас есть сводная таблица. Чтобы просмотреть панель задач полей сводной таблицы, щелкните сводную таблицу. Если панель задач полей сводной таблицы не отображается, проверьте наличие ленты:
- Перейдите на вкладку ANALYZE в разделе «Сводные инструменты» на ленте.
- Проверьте, выбран ли список полей (т.е. выделен ли он) в группе Показать.
- Если список полей не выбран, щелкните по нему.
Панель задач полей сводной таблицы будет отображаться в правой части окна с заголовком – Поля сводной таблицы.
Перемещение области задач полей сводной таблицы
Справа от заголовка Поля сводной таблицы панели задач сводной таблицы находится кнопка 
, Это представляет параметры панели задач. Нажмите кнопку 
, Параметры панели задач – Переместить, Размер и Закрыть отображаются в раскрывающемся списке.
Вы можете переместить панель задач сводных таблиц в любое место в окне следующим образом:
Нажмите Move в выпадающем списке. 
кнопка появляется на панели задач.
Нажмите на
значок и перетащите панель в положение, где вы хотите разместить его. Вы можете разместить панель задач рядом со сводной таблицей, как показано ниже.
Нажмите Move в выпадающем списке. 
кнопка появляется на панели задач.
Нажмите на 
значок и перетащите панель в положение, где вы хотите разместить его. Вы можете разместить панель задач рядом со сводной таблицей, как показано ниже.
Вы можете разместить панель задач в левой части окна, как показано ниже.
Изменение размера панели задач полей сводной таблицы
Вы можете изменить размер панели задач сводной таблицы, то есть увеличить / уменьшить длину и / или ширину панели задач следующим образом.
Нажмите на панели задач – 
это находится на правой стороне заголовка – Поля сводной таблицы.
Нажмите на Размер в раскрывающемся списке.
Используйте символ ⇔, чтобы увеличить / уменьшить ширину панели задач.
Используйте символ ⇕, чтобы увеличить / уменьшить ширину панели задач.
Нажмите на панели задач – 
это находится на правой стороне заголовка – Поля сводной таблицы.
Нажмите на Размер в раскрывающемся списке.
Используйте символ ⇔, чтобы увеличить / уменьшить ширину панели задач.
Используйте символ ⇕, чтобы увеличить / уменьшить ширину панели задач.
В области ∑ VALUES, чтобы сделать сумму суммы заказа полностью видимой, вы можете изменить размер панели задач, как показано ниже.
Поля сводной таблицы
Список полей сводной таблицы состоит из всех таблиц, связанных с вашей книгой, и соответствующих полей. Именно путем выбора полей в списке полей сводной таблицы вы создадите сводную таблицу.
Таблицы и соответствующие поля с флажками отражают ваши данные сводной таблицы. Поскольку вы можете проверять / снимать флажки в случайном порядке, вы можете быстро изменить сводную таблицу, выделив обобщенные данные, которые вы хотите сообщить или представить.
Как вы можете заметить, если есть только одна таблица, имя таблицы не будет отображаться в списке полей сводной таблицы. Только поля будут отображаться с флажками.
Над списком полей вы найдете действие Выберите поля для добавления в отчет. Справа вы найдете кнопку – 
это представляет инструменты.
- Нажмите на кнопку Инструменты.
В раскрывающемся списке вы найдете следующее –
Пять различных вариантов макета для полей и областей.
Два варианта порядка сортировки полей в списке полей –
Сортировать от А до Я.
Сортировка в порядке источника данных.
Пять различных вариантов макета для полей и областей.
Два варианта порядка сортировки полей в списке полей –
Сортировать от А до Я.
Сортировка в порядке источника данных.
Как видно из приведенного выше списка полей, порядок сортировки по умолчанию – т.е. в порядке источника данных. Это означает, что это порядок, в котором отображаются столбцы в вашей таблице данных.
Обычно вы можете сохранить порядок по умолчанию. Однако иногда вы можете встретить много полей в таблице и не знать их. В таком случае вы можете отсортировать поля в алфавитном порядке, нажав – Сортировать от A до Z в раскрывающемся списке Инструменты. Затем список полей сводной таблицы выглядит следующим образом:
Поля сводной таблицы как отобразить
Как скрыть / показать список полей сводной таблицы в Excel?
Когда вы вставляете сводную таблицу, в правом разделе рабочего листа появляется список полей сводной таблицы. Иногда этот список полей сводной таблицы может скрывать данные в правой части листа. Теперь я расскажу, как скрыть список полей сводной таблицы в Excel.
Скрыть / показать список полей сводной таблицы щелчком правой кнопкой мыши
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы скрыть список полей сводной таблицы, достаточно одного шага.
Щелкните правой кнопкой мыши любую ячейку сводной таблицы и выберите Скрыть список полей в контекстном меню.
Если вы хотите снова отобразить список полей, щелкните правой кнопкой мыши сводную таблицу и выберите Показать список полей.
Скрыть / отобразить список полей сводной таблицы с помощью VBA
Если вас интересует VBA, вы можете скрыть или показать список полей сводной таблицы, выполнив следующие действия:
1. Нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии скопируйте приведенный ниже код в окно.
VBA: скрыть список полей.
3. Нажмите Run или F5, то список полей сводной таблицы скрыт.
Функции: Для отображения списка полей с VBA вы можете использовать следующий код:
Сводные таблицы в Excel
Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:
- имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
- необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
- этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра ( CTRL+SHIFT+L ), формул , Расширенного фильтра ;
- исходная таблица удовлетворяет определенным требованиям (см. ниже).
Пользователи часто избегают использования Сводных таблиц , т.к. уверены, что они слишком сложны. Действительно, для того чтобы освоить любой новый инструмент или метод, требуется приложить усилия и потратить время. Но, в результате эффект от освоения нового должен превзойти вложенные усилия. В этой статье разберемся, как создавать и применять Сводные таблицы .
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
- каждый столбец должен иметь заголовок;
- в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
- в таблице должны отсутствовать полностью незаполненные строки и столбцы;
- в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
- избегайте таблиц с «неправильной» структурой (см. рисунок ниже).
Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .
Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
- при добавлении в таблицу новых значений новые строки автоматически добавляются к таблице;
- при создании таблицы к ней применяется форматирование, к заголовкам – фильтр, появляется возможность автоматически создать строку итогов, сортировать данные и пр.;
- таблице автоматически присваивается Имя .
В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.
В таблице имеются столбцы:
- Товар – наименование партии товара, например, « Апельсины »;
- Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
- Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
- Дата поставки – Дата поставки Товара Поставщиком;
- Регион продажи – Регион, в котором была реализована партия Товара;
- Продажи – Стоимость, по которой удалось реализовать партию Товара;
- Сбыт – срок фактической реализации Товара в Регионе (в днях);
- Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
Имея исходную таблицу в формате EXCEL 2007 , для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица .
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:
Заполним сначала раздел Названия строк . Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец — синонимы).
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).
Теперь поставим галочку в Списке полей у поля Продажи.
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».
Обновление Сводной таблицы
Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице . Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы , вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить .
Удаление Сводной таблицы
Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .
Изменение функции итогов
При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
Изменение порядка сортировки
Теперь немного модифицируем наш Сводный отчет . Сначала изменим порядок сортировки названий Товаров: отсортируем их в обратном порядке от Я до А. Для этого через выпадающий список у заголовка столбца, содержащего наименования Товаров, войдем в меню и выберем Сортировка от Я до А .
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …
В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов .
Добавление новых полей
Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.
Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.
Добавление столбцов
Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.
Сводная таблица примет следующий вид.
Меняем столбцы местами
Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.
Удаление полей
Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.
Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).
Добавление фильтра
Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
- Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;
Вид получившейся Сводной таблицы должен быть таким:
Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.
После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.
Обратите внимание, что в Списке полей Сводной таблицы напротив поля Прибыль появился значок фильтра. Удалить фильтр можно сняв галочку в Списке полей.
Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .
Также стандартный механизм фильтрации данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.
Несколько итогов для одного поля
Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
- Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;
- в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.
Отключаем строки итогов
Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .
Группируем числа и Даты
Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:
- Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
- Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
- выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
- Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;
Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:
- Выделите одно значение Сводной таблицы в столбце Названия строк;
- В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю ;
- Появившееся окно заполните, как показано на рисунке ниже;
- Нажмите ОК.
Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.
Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .
Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.
Условное форматирование ячеек Сводной таблицы
К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.