Поля сводной таблицы как отобразить

Управление панелью Поля сводной таблицы

В Excel 2013 существует три типа макетов отчета сводной таблицы (рис. 1): в сжатой форме, в форме структуры и в табличной форме. Доступ к изменению макета сводной таблицы можно получить, пройдя по меню Работа со сводными таблицами → Конструктор → Макет отчета. [1] Обратите внимание на визуальные отличия в этих макетах. В сжатой форме оба поля строк (Регион и Рынок сбыта) расположены в одном столбце (А). В форме структуры макет содержит строки заголовков (строки 10, 13, 15 и 21), а поля Регион и Рынок сбыта расположены каждый в своем столбце. Наконец, в табличной форме поля Регион и Рынок сбыта также расположены каждый в своем столбце, но при этом отсутствуют строки заголовков регионов. Вместо последних есть строки с промежуточными итогами (12, 14, 20 и 25). Но, если строки итогов в табличной форме можно отключить, и сводная таблица станет более компактной, то заголовки регионов в форме структуры отключить нельзя.

Рис. 1. Формы представления отчета сводной таблицы

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Рис. 2. Раздельный доступ к фильтрации по полям: (а) Регион, (б) Рынок сбыта

Если для сводной таблицы выбрана сжатая форма, вы не найдете отдельные заголовки Регион и Рынок сбыта. Оба эти поля размещены в столбце А, под заголовком Названия строк (рис. 3а), оснащенным средствами сортировки и фильтрации для поля Регион. Чтобы выполнить фильтрацию или сортировку по полю Рынок сбыта, находящемуся в ячейке АЗ, придется повторно выбирать пункт Рынок сбыта в верхней части раскрывающегося списка (область Выберите поле). При этом приходится выполнять дополнительный щелчок мышью. И если, например, в поле Рынок сбыта вносятся пять последовательных изменений, придется повторно пять раз выбирать поле Рынок сбыта. Одного этого обстоятельства достаточно, чтобы отказаться от использования сжатой формы сводной таблицы.

Рис. 3. В случае сжатой формы: (а) непосредственно в раскрывающемся списке доступна фильтрация по полю Регион; (б) чтобы получить доступ к фильтрации по полю Рынок сбыта, перейдите к окну Поля сводной таблицы

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

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

Прикрепление и отсоединение панели области задач Поля сводной таблицы

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

«Прикрепить» панель обратно весьма затруднительно. Чтобы выполнить эту задачу, необходимо перетащить за пределы правой границы окна не менее 85% диалогового окна. При этом возникает ощущение, будто диалоговое окно вообще удаляется с экрана. Но, как ни странно, этого не происходит: программа послушно восстанавливает привязку списка полей к правой границе окна. Обратите внимание: панель области задач Поля сводной таблицы можно аналогичным образом прикреплять и к левой границе окна программы Excel.

Переупорядочение списка полей

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

Рис. 4. Меню изменения вида списка полей сводной таблицы

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

Раскрывающиеся списки областей сводной таблицы

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

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

Рис. 5. Раскрывающееся меню поля сводной таблицы

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.

Настройка сводной таблицы в MS Excel

Видео материала «Настройка сводной таблицы в MS Excel»

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

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

Добавление сводной таблицы в MS Excel

Для добавления сводной таблицы необходимо:

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

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

7. После выбора запроса нажмите кнопку «Открыть«

Редактирование отчетов в MS Excel.

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

• Для выбора полей для вывода перейдите на закладку меню «Анализ«, пункт «Показать» и подпункт «Список полей«. В открывшемся окне «Поля сводной таблицы» поставьте галочку напротив необходимых полей. Поля перемещаются в блок «Строки«.
• Для определения столбцов сводной таблицы переместите столбцы в блок «Столбцы«.
• Для вывода значений сводной таблицы переместите столбец в блок «Значения«. Рекомендуем в качестве значений использовать числовые столбцы. Текстовые столбцы возможно так же вывести но при этом будет выводится их количество.
• Для установке фильтра по полю таблицы перенесите название столбца в блок «Фильтры«

Для удобства фильтрации данных Вы можете использовать срезы:

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

Редактирования полей сводного отчета в MS Excel.

Для редактирования формы вывода полей сводного отчета:

1. В окне «Поля сводного отчета» в блоке «Значения» выберите поле необходимое для редактирования и нажмите стрелочку вниз в правой части названия столбца.
2. В контекстном меню выберите пункт «Параметры полей значений«.
3. В открытом окне «Параметры поля значений» в строке «Пользовательское имя:» Вы можете изменить имя поля. Имя поля должно быть уникального для этого запроса.
4. В блоке «Операция» Вы можете выбрать операцию над значениями поля. Большинство операция разработаны для числовых типов полей.

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

Как отображать используемые поля

Итог: узнаете, как создать развернутый лист «Показать детали» из сводной таблицы, которая содержит только поля (столбцы), используемые в сводной таблице.

Уровень мастерства: Средний

В листе «Показать подробности» обычно отображаются все поля

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

Это полезно, когда вы связываете числа и хотите увидеть все строки, которые составляют конкретное число.

Лист сведений также содержит ВСЕ столбцы из диапазона исходных данных.

Арис, член сообщества Excel Campus, задал отличный вопрос, можем ли мы создать информационный лист, включающий ТОЛЬКО поля, используемые в сводной таблице?

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

Макрос — Показать детали используемых полей

Следующий макрос группирует и скрывает или удаляет столбцы на листе «Показать детали», которые НЕ используются в сводной таблице. Это означает, что у нас останется лист подробностей показа, который содержит только поля (столбцы), которые используются в сводной таблице. Это дает нам гораздо более тонкую таблицу, которую легче читать.

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

Как работает макрос?

Вот что происходит при запуске макроса:

1. Макрос создает лист ShowDetails для активной ячейки в сводной таблице.
2. Затем он просматривает каждый столбец в таблице (объект списка) нового листа.
3. Он проверяет, используется ли столбец (поле) в какой-либо области в сводной таблице.
4. Если столбец НЕ используется, он группирует столбец (столбцы также могут быть скрыты или удалены).
5. Шаги 3 и 4 повторяются для каждого столбца.
6. Контур столбца свернут, поэтому остаются видимыми только использованные столбцы.
7. Ширина столбцов таблицы автоматически подбирается, что позволяет сохранить еще один шаг с отображением подробных листов.

Вот макрос, который вы можете скопировать и вставить в свою личную книгу макросов или в любой проект VBA.

Sub Show_Details_Used_Fields_Only() 'Создает лист данных для сводной таблицы 'на основе активной ячейки и удаляет или скрывает 'столбцы, которые не используются в сводной таблице. 'Макрос может быть добавлен в ваш личный макрос 'Книга и запуск на любой открытый файл. Dim pt As PivotTable Dim pf As PivotField Dim pfData As PivotField Dim lo As ListObject Dim loCol As ListColumn Dim bVisible As Boolean 'Проверьте, что активная ячейка находится в сводной таблице On Error Resume Next Set pt = ActiveCell.PivotTable On Error GoTo 0 If pt Is Nothing Then MsgBox "Please select a cell inside a pivot table" Exit Sub End If 'Убедитесь, что активная ячейка находится в области значений сводной таблицы If Not Intersect(ActiveCell, pt.DataBodyRange) Is Nothing Then 'Создайте лист деталей шоу ActiveCell.ShowDetail = True 'Set ListObject (Table) на листе подробностей показа Set lo = ActiveSheet.ListObjects(1) 'Удалить неиспользуемые столбцы из листа данных For Each loCol In lo.ListColumns bVisible = False 'Проверьте, что поле не используется в фильтрах, строках или 'Области столбцов For Each pf In pt.PivotFields If pf.Name <> "Values" Then If pf.SourceName = loCol.Name Then If pf.Orientation = xlHidden Then 'Проверьте, что поле не используется в области значений 'Поля данных в области значений имеют скрытую ориентацию For Each pfData In pt.DataFields If pfData.SourceName = loCol.Name Then bVisible = True End If Next pfData Else 'Поле используется в строках, столбцах или фильтрах bVisible = True End If 'Сгруппируйте и сверните столбцы в листе данных If bVisible = False Then 'Раскомментируйте любую из строк ниже, чтобы удалить или скрыть 'столбцы вместо группировки loCol.Range.EntireColumn.Group 'loCol.Delete 'loCol.Range.EntireColumn.Hidden = True End If End If End If Next pf Next loCol 'Свернуть группы ActiveSheet.Outline.ShowLevels ColumnLevels:=1 'Колонки Autofit lo.Range.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit Else MsgBox "Please select a cell in the values area of the pivot table." Exit Sub End If End Sub

Как запустить макрос

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

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

Другой вариант — добавить код в событие приложения для события BeforeDoubleClick. Тогда макрос может автоматически запускаться при двойном щелчке ячейки в сводной таблице.

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

Упорядочение полей сводной таблицы с помощью списка полей

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

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

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

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

Совет: Чтобы изменить способ отображения разделов в списке полей, нажмите кнопку Инструменты и выберите подходящий макет.

Добавление, изменение расположения и удаление полей в списке

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

Примечание: Как правило, нечисловые поля добавляются в область строк, числовые — в область значений, а иерархии даты и времени OLAP — в область столбцов.

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

Поля из различных областей отображаются в сводной таблице описанным ниже образом.

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

Поля из области столбцов отображаются как названия столбцов в верхней части сводной таблицы, как показано ниже.

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

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

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

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

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

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

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

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

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

Добавление, изменение расположения и удаление полей в списке

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

Примечание: Как правило, нечисловые поля добавляются в область строк, числовые — в область значений, а иерархии даты и времени OLAP — в область столбцов.

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

Поля из различных областей отображаются в сводной таблице описанным ниже образом.

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

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

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

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

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

Поля области значений отображаются в виде сводных числовых значений в сводной таблице, как показано ниже:

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

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

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

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

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

Добавление, изменение расположения и удаление полей в списке

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

Примечание: Как правило, нечисловые поля добавляются в область строк, числовые — в область значений, а иерархии даты и времени OLAP — в область столбцов.

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

Поля из различных областей отображаются в сводной таблице описанным ниже образом.

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

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

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

• Поля области строк отображаются как метки строк в левой части сводной таблицы, как показано ниже:
  16c

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

• Поля области значений отображаются в виде сводных числовых значений в сводной таблице следующим образом:
  17c

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

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

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

Добавление, изменение расположения и удаление полей в списке

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

Примечание: Как правило, нечисловые поля добавляются в область Строк , поля даты и времени добавляются в область Столбцы , а числовые поля добавляются в область Значений .

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

Поля из различных областей отображаются в сводной таблице описанным ниже образом.

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

• Поля из области Столбцы отображаются под надписью Названия столбцов в верхней части сводной таблицы, как показано на рисунке:

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

• Поля из области строк отображаются как названия строк в левой части сводной таблицей, как показано ниже.

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

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