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

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

Важно : Группа вкладок Работа со сводными таблицами на ленте содержит две вкладки: Анализ (Excel 2016 и Excel 2013) или Параметры (Excel 2010 и Excel 2010) и Конструктор. Обратите внимание, что в соответствующих пунктах процедур, описанных в этой статье, указываются обе вкладки, Анализ и Параметры.

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

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

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

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

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

    Сводная таблица в табличной форме

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

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

Приведение сводной таблицы к сжатой, структурной или табличной форме

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

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

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

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

      Изображение ленты Excel

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

    Изображение ленты Excel

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

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

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

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

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

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

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

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

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

Добавление полей в сводную таблицу

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

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

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

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

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

Копирование полей в сводной таблице

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

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

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

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

    Примечания : 

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

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

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

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

Изменение положения полей в сводной таблице

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

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

Описание

Сводная диаграмма

Описание

Значения    

Используются для вывода суммарных числовых данных.

Значения    

Используются для вывода суммарных числовых данных.

Названия строк    

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

Поля осей (категории)    

Используются для вывода полей в качестве осей диаграммы.

Названия столбцов    

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

Метки полей легенды (рядов)    

Используются для вывода полей в легенде диаграммы.

Фильтр отчетов    

Используется для фильтрации всего отчета на основе выбранных элементов фильтра отчета.

Фильтр отчетов    

Используется для фильтрации всего отчета на основе выбранных элементов фильтра отчета.

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

Команда

Задача

Вверх   

Перемещение поля в области на одну позицию вверх.

Вниз   

Перемещение поля в области на одну позицию вниз.

Переместить в начало   

Перемещение поля к началу области.

Переместить в конец   

Перемещение поля к концу области.

Переместить в фильтр отчета   

Перемещение поля в область фильтра отчета.

Переместить в названия строк   

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

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

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

Переместить в значения   

Перемещение поля в область значений.

Параметры поля значений, Параметры поля   

Отображение диалогового окна Параметры поля или Параметры поля значений. Дополнительные сведения об этих параметрах можно получить, нажав кнопку "Справка"  изображение кнопки в верхней части диалогового окна.

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

    • В области макета щелкните имя поля и нажмите кнопку Удалить.

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

Изменение макета столбцов, строк и промежуточных итогов

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

    Изображение ленты Excel

  3. В диалоговом окне Параметры поля на вкладке Промежуточные итоги и фильтры в группе Итоги выберите вариант Автоматические или Другие.

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

  4. На вкладке Разметка и печать в группе Макет выберите вариант В виде структуры, а затем выполните одно из указанных ниже действий.

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

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

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

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

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

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

    Пример перемещения элемента в отчете сводной таблицы

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

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

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

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

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

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

    1. Щелкните поле столбца

    2. Перетащите его в область строк

    3. Поле "Спорт" станет полем строки, как и "Регион"

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

Для строк выполните указанные ниже действия.

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

    Изображение ленты Excel

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

Для элементов выполните указанные ниже действия.

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

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

      Примечание : Этот флажок применим только к сводным таблицам, созданным с использованием версий Excel, предшествующих Office Excel 2007.

Изменение и удаление форматирования

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

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

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

      Изображение ленты Excel

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

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

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

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

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

      Изображение ленты Excel

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

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

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

  5. Укажите нужные параметры и дважды нажмите кнопку ОК.

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

Поддержка форматирования сервера OLAP

При подключении к базе данных OLAP служб Microsoft SQL Server Analysis Services можно указывать форматы сервера OLAP для извлечения и отображения данных.

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

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

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

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

    На ленте откроется группа вкладок Работа со сводными таблицами.

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

    Изображение ленты Excel

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×