Разработка макета и формата сводной таблицы

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

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

  

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

  

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

  

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Конструктор в группе Макет выберите Макет отчета, а затем выполните одно из следующих действий:
   
   

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

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

   • Чтобы очертить данные в классическом стиле сводной таблицы, выберите Показать в форме структуры.

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

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

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

   Также в форме структуры или табличной форме можно дважды щелкнуть поле строки и перейти к действию 3.

2. На вкладке Анализ сводной таблицы в группе Активное поле выберите Параметры поля.

   

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

   • Чтобы отобразить элементы полей в форме структуры, выберите Показать метки элементов в форме структуры.

   • Чтобы отобразить или скрыть метки из следующего поля в том же столбце в компактной форме, выберите Показать метки элементов в форме структуры, а затем выберите Отображать метки из следующего поля в том же столбце (компактная форма).

   • Чтобы отобразить элементы полей в табличной форме, выберите Показать метки элементов в табличной форме.

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

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

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

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

Выполните одно или несколько из указанных ниже действий.

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

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

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

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

В сводной таблице, основанной на данных с листа Excel или внешних данных из источника, не относящегося к OLAP, можно добавить в область Значения несколько копий одного поля, чтобы отображать разные вычисления с помощью возможности Дополнительные вычисления. Например, можно сравнить вычисления (такие как коэффициенты валовой и чистой прибыли, минимальные и максимальные показатели продаж или количество клиентов и процент от их общего числа) расположив их рядом. Дополнительные сведения см. в статье Отображение различных вычислений в полях значений сводной таблицы.

1. Щелкните и удерживайте имя поля в разделе поля, а затем перетащите его в область Значения в разделе макета.

2. Повторите действие 1 столько раз, сколько нужно копий поля.

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

   Примечания: 

   • При добавлении в область значений двух или нескольких полей (как копий одного поля, так и разных полей) список полей автоматически добавляет в область Значения название столбца значений. С помощью этого поля можно перемещать поле вверх и вниз в области Значения. Название столбца значений можно даже переместить в область Названия столбцов или Названия строк. Однако переместить название столбца значений в область фильтров отчетов нельзя.

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

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

   • Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

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

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

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

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

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

1. Щелкните сводную таблицу.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

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

3. Чтобы удалить поле, в списке полей сводной таблицы выполните одно из следующих действий.

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

     Примечание: Снятие флажка в списке полей приводит к удалению из отчета всех экземпляров поля.

   • В области Макет выберите имя поля, а затем выберите Удалить поле.

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

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

Включение и отключение заголовков полей столбцов и строк

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

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

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

Отображение промежуточных итогов выше или ниже их строк

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

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

   Совет: В контурной или табличной форме можно также дважды щелкнуть поле строки, а затем перейти к шагу 3.

2. На вкладке Анализ сводной таблицы в группе Активное поле выберите Параметры поля.

   

3. В диалоговом окне Параметры поля на вкладке Промежуточные итоги & Фильтры в разделе Подытоги выберите Автоматически илиПользовательский.

   Примечание: Если выбрать вариант Нет, промежуточные итоги будут отключены.

4. На вкладке Макет & Печать в разделе Макет выберите Показать метки элементов в форме структуры, а затем выполните одно из следующих действий:

   • Чтобы промежуточные итоги отображались над суммируемыми строками, установите флажок Промежуточные итоги в заголовке группы. Этот вариант выбран по умолчанию.

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

Изменение порядка элементов строк и столбцов

Выполните любое из следующих действий.

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

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

  

Настройка ширины столбцов при обновлении.

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Сводная таблица выберите Параметры.

   

3. В диалоговом окне Параметры сводной таблицы на вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.

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

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

Перемещение столбца в область названий строк или строки в область названий столбцов

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

Используйте команду правой кнопки мыши

Выполните любое из следующих действий.

• Щелкните правой кнопкой мыши поле строки, наведите указатель мыши на пункт Переместить имя поля <>, а затем выберите Переместить имя поля <> в столбцы.

• Щелкните поле столбца правой кнопкой мыши и выберите команду Переместить <имя поля> в строки.

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

1. Переключитесь в классический режим, поместив указатель на сводную таблицу, выбрав Анализ сводной таблицы > Параметры, выбрав вкладку Отображение, а затем выбрав Классический макет сводной таблицы.

2. Перетащите поле строки или столбца в другую область. На рисунке ниже показано, как переместить поле столбца в область названий строк.

   А. Выбор поля столбца	б) Прокрутите списки Избранное и Контактные лица для веб-поддержки. Перетащите его в область строки	В. Поле "Спорт" станет полем строки, как и "Регион"

Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

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

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Сводная таблица выберите Параметры.

   

3. В диалоговом окне Параметры сводной таблицы выберите вкладку Макет & Формат, а затем в разделе Макет выберите или снимите флажок Объединить и поцентровые ячейки с метками проверка.

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

Изменение способа отображения ошибок и пустых ячеек

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Сводная таблица выберите Параметры.

   

3. В диалоговом окне Параметры сводной таблицы выберите вкладку Макет & Формат , а затем в разделе Формат выполните одно или несколько из следующих действий.

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

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

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

Отображение и скрытие пустых строк после строк или элементов

Для строк выполните следующие действия.

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

  На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

  Совет: В контурной или табличной форме можно также дважды щелкнуть поле строки, а затем перейти к шагу 3.

• На вкладке Анализ сводной таблицы в группе Активное поле выберите Параметры поля.

  

• В диалоговом окне Параметры поля на вкладке Разметка и печать в группе Макет установите или снимите флажок Пустая строка после каждой подписи.

Для элементов выполните следующие действия.

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

  На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

• На вкладке Конструктор в группе Макет выберите Пустые строки, а затем установите флажок Вставить пустую строку после каждой метки элемента или Удалить пустую строку после каждой метки элемента проверка.

  

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

Изменение способа отображения элементов и подписей без данных

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Сводная таблица выберите Параметры.

   

3. На вкладке Вывод в группе Вывод выполните одно или несколько из следующих действий.

   • Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.

     Примечание: Этот параметр доступен только для источника данных OLAP.

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

     Примечание: Этот параметр доступен только для источников данных OLAP.

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

Можно выбрать стиль из большой коллекции стилей сводных таблиц. Кроме того, можно управлять чередованием в отчете. Быстрым способом применения единого формата во всем отчете является изменение числового формата поля. Также можно добавлять чередование (темного и светлого фона) строк и столбцов. Чередование может упростить восприятие и поиск данных.

Применение стиля для форматирования сводной таблицы

Быстро изменить внешний вид и формат сводной таблицы можно с помощью одного из готовых стилей сводных таблиц (или экспресс-стилей).

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Конструктор в группе Стили сводной таблицы выполните одно из следующих действий:

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

   • Чтобы просмотреть все доступные стили, нажмите кнопку Быстрые стили в нижней части полосы прокрутки.

     

     Если вы хотите создать собственный пользовательский стиль сводной таблицы, выберите Новый стиль сводной таблицы в нижней части коллекции, чтобы открыть диалоговое окно Новый стиль сводной таблицы .

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

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Конструктор в группе Параметры стиля сводной таблицы выполните одно из следующих действий.

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

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

   • Чтобы включить заголовки строк в стиль повязки, выберите Заголовки строк.

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

     

Удаление стиля или формата чередования из сводной таблицы

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Конструктор в группе Стили сводной таблицы нажмите кнопку Быстрые стили в нижней части полосы прокрутки, чтобы просмотреть все доступные стили, а затем нажмите кнопку Очистить в нижней части коллекции.

   

Условное форматирование данных в сводной таблице

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

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

• Область действия условного формата для полей в области Значения может быть основана на иерархии данных и определяется всеми видимыми дочерними элементами (нижестоящим уровнем в иерархии) родительского объекта (вышестоящим уровнем в иерархии) в строках для одного или нескольких столбцов или в столбцах для одной или нескольких строк.

  Примечание: В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

• Задать область действия условного формата для полей в области Значения можно тремя способами: по выделению, по соответствующему полю и по полю значения.

Дополнительные сведения см. в статье Применение условного форматирования.

Изменение числового формата поля

1. Выделите в сводной таблице нужное поле.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Активное поле выберите Параметры поля.

   

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

3. Выберите Числовой формат в нижней части диалогового окна.

4. В диалоговом окне Формат ячеек в списке Категория выберите нужный формат.

5. Выберите нужные параметры, а затем дважды нажмите кнопку ОК .

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

Включение форматирования сервера OLAP

Если вы подключены к базе данных Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP), вы можете указать, какие форматы сервера OLAP следует извлекать и отображать вместе с данными.

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

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

   

3. В диалоговом окне Свойства подключения на вкладке Использование в разделе Форматирование сервера OLAP выполните одно из следующих действий.

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

   • Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.

   • Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.

   • Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

Сохранение и игнорирование форматирования

1. Щелкните в любом месте сводной таблицы.

   На ленте отображаются вкладки Анализ сводной таблицы и Конструктор .

2. На вкладке Анализ сводной таблицы в группе Сводная таблица выберите Параметры.

   

3. На вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.

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

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

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