Как изменить структуру сводной таблицы excel - Исправление ошибок и поиск оптимальных решений проблем

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

По умолчанию текстовые поля добавляются в область Названия строк, числовые поля — в область Значения, а иерархии даты и времени OLAP — в область Названия столбцов.
Чтобы поместить поле в определенную область раздела макета, щелкните его имя правой кнопкой мыши и выберите пункт Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения.
Щелкните имя поля и, удерживая нажатой кнопку мыши, перетащите его из раздела полей в одну из областей раздела макета.

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

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

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

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.
Чтобы удалить поле, в списке полей сводной таблицы выполните одно из следующих действий.
- В списке полей сводной таблицы снимите флажок рядом с именем поля.
  
  Примечание: Снятие флажка в списке полей приводит к удалению из отчета всех экземпляров поля.
- В области макета щелкните имя поля и выберите пункт Удалить поле.
- Щелкните имя поля в разделе макета и, удерживая нажатой кнопку мыши, перетащите его из списка полей сводной таблицы.

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

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

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

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

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

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

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

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

Примечание: Если выбрать вариант Нет, промежуточные итоги будут отключены.
На вкладке Разметка и печать в группе Макет выберите вариант В виде структуры и выполните одно из следующих действий.
- Чтобы промежуточные итоги отображались над суммируемыми строками, установите флажок Промежуточные итоги в заголовке группы. Этот вариант выбран по умолчанию.
- Чтобы промежуточные итоги отображались под суммируемыми строками, снимите флажок Промежуточные итоги в заголовке группы.

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы на вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы столбцы сводной таблицы автоматически изменялись по размеру самого широкого текста или числового значения, установите флажок Автоматически изменять ширину столбцов при обновлении.
- Чтобы сохранить текущую ширину столбца сводной таблицы, снимите флажок Автоматически изменять ширину столбцов при обновлении.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совет: В форме структуры или табличной форме также можно дважды щелкнуть поле строки и перейти к действию 3.
На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.
В диалоговом окне Параметры поля на вкладке Разметка и печать в группе Макет установите или снимите флажок Пустая строка после каждой подписи.

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

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Вывод в группе Вывод выполните одно или несколько из следующих действий.
- Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.
  
  Примечание: Этот параметр доступен только для источника данных OLAP.
- Чтобы отображать или скрывать элементы без значений в столбцах, установите или снимите флажок Показывать элементы без данных в столбцах.
  
  Примечание: Этот параметр доступен только для источников данных OLAP.
- Чтобы отображать или скрывать подписи элементов при отсутствии полей в области значений, установите или снимите флажок Показывать подписи элементов при отсутствии полей в области значений.
  
  Примечание: Этот флажок применим только к сводным таблицам, созданным с использованием версий Excel, предшествующих Office Excel 2007.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.
В диалоговом окне Свойства подключения на вкладке Использование в разделе Форматирование сервера OLAP выполните одно из следующих действий.
- Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
- Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
- Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
- Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы сохранить макет и формат сводной таблицы для применения при каждом выполнении операции со сводной таблицей, установите флажок Сохранять форматирование ячеек при обновлении.
- Чтобы игнорировать макет и формат сводной таблицы и возвращаться к стандартному макету и формату при каждом выполнении операции со сводной таблицей, снимите флажок Сохранять форматирование ячеек при обновлении.
Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

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

Выделив сводную таблицу, на ленте щелкните Сводная таблица > Параметры.
В области «Параметры сводной таблицы» настройте любые из следующих параметров.

Примечание: Раздел «Срез» отображается только в том случае, если к вашей сводной таблице подключен срез.

Чтобы показать общие итоги

Установите или снимите флажок Строки, Столбцы или оба.

Чтобы показать промежуточные итоги

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

Размещение полей из области строк

Выберите Отдельные столбцы, чтобы предоставить отдельные фильтры для каждого поля строк, или Один столбец, чтобы объединить поля строк в один фильтр.

Чтобы показать или скрыть метки элементов

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

Чтобы добавить пустую строку после каждого элемента

Выберите Показать или Не показывать.

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

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

Отображение кнопок развертывания и свертывания

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

Чтобы показать значения ошибок

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

Чтобы показать пустые ячейки

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

Чтобы сохранить исходные данные с файлом

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

Чтобы обновить данные при открытии файла

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

Чтобы добавить заголовок

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

Чтобы добавить описание

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

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

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

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

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

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

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

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

По умолчанию текстовые поля добавляются в область Названия строк, числовые поля — в область Значения, а иерархии даты и времени OLAP — в область Названия столбцов.
Чтобы поместить поле в определенную область раздела макета, щелкните его имя правой кнопкой мыши и выберите пункт Добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения.
Щелкните имя поля и, удерживая нажатой кнопку мыши, перетащите его из раздела полей в одну из областей раздела макета.

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.
Чтобы удалить поле, в списке полей сводной таблицы выполните одно из следующих действий.
- В списке полей сводной таблицы снимите флажок рядом с именем поля.
  
  Примечание: Снятие флажка в списке полей приводит к удалению из отчета всех экземпляров поля.
- В области макета щелкните имя поля и выберите пункт Удалить поле.
- Щелкните имя поля в разделе макета и, удерживая нажатой кнопку мыши, перетащите его из списка полей сводной таблицы.

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

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

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

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

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

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

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

Примечание: Если выбрать вариант Нет, промежуточные итоги будут отключены.
На вкладке Разметка и печать в группе Макет выберите вариант В виде структуры и выполните одно из следующих действий.
- Чтобы промежуточные итоги отображались над суммируемыми строками, установите флажок Промежуточные итоги в заголовке группы. Этот вариант выбран по умолчанию.
- Чтобы промежуточные итоги отображались под суммируемыми строками, снимите флажок Промежуточные итоги в заголовке группы.

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы на вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы столбцы сводной таблицы автоматически изменялись по размеру самого широкого текста или числового значения, установите флажок Автоматически изменять ширину столбцов при обновлении.
- Чтобы сохранить текущую ширину столбца сводной таблицы, снимите флажок Автоматически изменять ширину столбцов при обновлении.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Вывод в группе Вывод выполните одно или несколько из следующих действий.
- Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.
  
  Примечание: Этот параметр доступен только для источника данных OLAP.
- Чтобы отображать или скрывать элементы без значений в столбцах, установите или снимите флажок Показывать элементы без данных в столбцах.
  
  Примечание: Этот параметр доступен только для источников данных OLAP.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.
В диалоговом окне Свойства подключения на вкладке Использование в разделе Форматирование сервера OLAP выполните одно из следующих действий.
- Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
- Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
- Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
- Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

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

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы сохранить макет и формат сводной таблицы для применения при каждом выполнении операции со сводной таблицей, установите флажок Сохранять форматирование ячеек при обновлении.
- Чтобы игнорировать макет и формат сводной таблицы и возвращаться к стандартному макету и формату при каждом выполнении операции со сводной таблицей, снимите флажок Сохранять форматирование ячеек при обновлении.
Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Download Article

After you create a pivot table, you might need to edit it later. This wikiHow will show you how to edit a pivot table in Excel on your computer by adding or changing the source data. After you make any changes to the data for your Pivot Table, you will need to refresh it to see any changes.

Steps

1

Open your project in Excel. To do this, double-click the Excel document that contains your pivot table in Finder (Macs) or File Explorer (Windows). Alternatively, if you already have Excel open, click File > Open and select the file that has your pivot table.
2

Go to the spreadsheet page that contains the data for the pivot table. Click the tab that contains your data (e.g., Sheet 2) at the bottom of the Excel window.

Advertisement
3
Add or change your data. Enter the data that you want to add to your pivot table directly next to or below the current data.
- For example, if you have data in cells A1 through E10, you would add another column in the F column or another row in the 11 row.
- If you simply want to change the data in your pivot table, edit the data here. It won’t be reflected in the pivot table until you refresh the data, though.
4

Go back to the pivot table tab. Click the tab on which your pivot table is listed.
5

Select your pivot table. Click the pivot table to select it.
6
Click the Analyze tab. It’s in the middle of the editing ribbon that’s at the top of the Excel window. Doing so will open a toolbar just below the editing ribbon.
- On a Mac, click the PivotTable Analyze tab here instead.
7

Click Change Data Source. This option is in the «Data» section of the Analyze toolbar. A drop-down menu will appear.
8

Click Change Data Source…. It’s in the drop-down menu. Doing so opens a window.
9

Select your data. Click and drag from the top-left cell in your data group down to the bottom-left cell in the group. This will include the column(s) or row(s) that you added.
10

Click OK. It’s at the bottom of the window.
11
Click Refresh. It’s in the «Data» section of the toolbar.
- If you added a new column to your pivot table, check its box on the right side of the Excel window to display it.^[1]

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Thanks for submitting a tip for review!

References

About This Article

Article SummaryX

1. Open your project in Excel.

2. Go to the spreadsheet that contains the data for the pivot table

3. Add or change your data.
4. Go back to the pivot table tab.

5. Select your pivot table.

6. Click Analyze tab (Windows) or PivotTable Analyze (Mac).
7. Click Change Data Source.

8. Click Change Data Source.
9. Select your data.

10. Click Ok.

11. Click Refresh.

Did this summary help you?

Thanks to all authors for creating a page that has been read 42,041 times.

Is this article up to date?

Источник

Download Article

Steps

1

Open your project in Excel. To do this, double-click the Excel document that contains your pivot table in Finder (Macs) or File Explorer (Windows). Alternatively, if you already have Excel open, click File > Open and select the file that has your pivot table.
2

Go to the spreadsheet page that contains the data for the pivot table. Click the tab that contains your data (e.g., Sheet 2) at the bottom of the Excel window.

Advertisement
3
Add or change your data. Enter the data that you want to add to your pivot table directly next to or below the current data.
- For example, if you have data in cells A1 through E10, you would add another column in the F column or another row in the 11 row.
- If you simply want to change the data in your pivot table, edit the data here. It won’t be reflected in the pivot table until you refresh the data, though.
4

Go back to the pivot table tab. Click the tab on which your pivot table is listed.
5

Select your pivot table. Click the pivot table to select it.
6
Click the Analyze tab. It’s in the middle of the editing ribbon that’s at the top of the Excel window. Doing so will open a toolbar just below the editing ribbon.
- On a Mac, click the PivotTable Analyze tab here instead.
7

Click Change Data Source. This option is in the «Data» section of the Analyze toolbar. A drop-down menu will appear.
8

Click Change Data Source…. It’s in the drop-down menu. Doing so opens a window.
9

Select your data. Click and drag from the top-left cell in your data group down to the bottom-left cell in the group. This will include the column(s) or row(s) that you added.
10

Click OK. It’s at the bottom of the window.
11
Click Refresh. It’s in the «Data» section of the toolbar.
- If you added a new column to your pivot table, check its box on the right side of the Excel window to display it.^[1]

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Thanks for submitting a tip for review!

References

About This Article

Article SummaryX

Did this summary help you?

Thanks to all authors for creating a page that has been read 42,041 times.

Is this article up to date?

Источник

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

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

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

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

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

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

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

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

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

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

Дабы избежать выхода связанных данных за пределы экрана по горизонтали (что, свою очередь, минимизирует использование функции прокрутки), стоит выбрать команду “Показать в сжатой форме”

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

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

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

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

После того, как поле строки будет выбрано, на ленте окажется открытым раздел “Работа со сводными таблицами”.

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

В категории “Параметры” или “Анализ” в группе “Активное поле” воспользуйтесь кнопкой “Параметры поля”.

В диалоговой форме “Параметры поля” перейдите в секцию “Разметка и печать”, после чего в группе “Макет” выполните одну из следующих операций:

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

Источник

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

Что такое сводная таблица
Подготавливаем данные для создания сводной таблицы
Как создать сводную таблицу из обычной таблицы
Где найти панель редактирования сводной таблицы
Как изменить структуру сводной таблицы
Как удалить поле сводной таблицы
Для чего нужно поле Фильтр в сводной таблице
Как обновить сводную таблицу
Как изменить внешний вид сводной таблицы
Значение (пусто) в сводной таблице — как убрать
Где изменить источник сводной таблицы
Как скопировать сводную таблицу
Удаляем сводную таблицу

Что такое сводная таблица

Сводная таблица в Excel (или pivit table, или “пивотная таблица”) — это особым образом организованная и структурированная таблица, которая выводит данные в нужном пользователю разрезе и производит необходимые вычисления без использования формул.

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

Предположим, нужна информация о сумме выручки в разрезе категории товара. Или по месяцам. Конечно, можно взять все категории товара и функцией СУММЕСЛИМН вытянуть нужные данные, но это займет намного больше времени. К тому же, если у вас завтра появится новая категория, ее нужно будет вручную добавить в таблицу (не забыть об этом, как минимум). Если же построить сводную таблицу в excel, они это сделает это за вас.

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

Давайте разберемся, из какой исходной таблицы можно построить сводную таблицу в excel (спойлер: не каждая таблица годится).

Основные требования к таблице, из которой будет создаваться сводная таблица:

Все столбцы в исходной таблице должны иметь уникальные (т.е. не повторяющиеся) заголовки.
Все строки в пределах одного столбца должны иметь однородные данные. Например, если в столбце указан месяц в формате “янв”, то не должно быть данных в формате “Январь” — эксель сочтет их как два разных месяца. Также использование русских и похожих латинских букв (например С и C на глаз не отличишь, но одна из них в латинской раскладке, и эксель также посчитает их разными).
Числовые данные, которым вы будете оперировать, должны быть именно числовыми. Если есть зеленый треугольник в верхнем левом углу ячейки — такое число является текстом, и должно быть преобразовано в число, иначе с ним нельзя делать вычисления.

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

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

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

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

Если мы не предполагаем в дальнейшем добавлять в исходную таблицу данные, то можно выделить только диапазон этой таблицы, например, А1:Н202. Однако, если в исходную таблицу будут добавляться данные, лучше выделить полностью столбцы, входящие в таблицу. Иначе есть риск, что при обновлении сводной таблицы новые данные в нее не попадут.

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

Далее вкладка Вставка — Сводная таблица.

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

Выберите данные для анализа. Здесь уже указан диапазон, который вы выделили, не меняйте его.
Укажите, куда следует поместить отчет сводной таблицы. Новичкам (и не только) советую всегда выбирать Новый лист. Если выбираете “на существующий лист”, то нужно указать ячейку на уже созданном листе, с которой будет начинаться сводная таблица. Только учтите, что пространство нужно выбрать с запасом, чтобы потом сводная таблица не “съела” данные на листе.

Выберем На новый лист и нажмем ОК.

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

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

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

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

Перетащим поле Категория в блок СТРОКИ, поле Месяц — в блок СТОЛБЦЫ, а поле Стоимость — в блок ЗНАЧЕНИЯ.

Обратите внимание!

Возле слова Количество не случайно указан знак вопроса. В большинстве версий excel по умолчанию считает в значениях именно количество элементов. По невнимательности можно оставить так и получить кривой результат.

Нам же нужна именно сумма по полю Стоимость. Нажмем на “треугольник” и выберем Параметры полей значений, и далее — Сумма.

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

Где найти панель редактирования сводной таблицы

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

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

Как изменить структуру сводной таблицы

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

Получаем результат.

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

Теперь нам больше не нужно видеть развертку по наименованию товара в строках.

Для того, чтобы удалить поле из сводной таблицы, есть 3 основных способа:

снять галочку с названия поля, оно автоматически исчезнет
в блоке, где располагается поле, щелкнуть на “треугольник” и выбрать Удалить поле.

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

Для чего нужно поле Фильтр в сводной таблице

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

Перестроим таблицу таким образом: в строки добавим Наименование товаров, в столбцы — месяцы.

Предположим, нужно видеть не все названия товаров, а только по категории Инструмент.

Для этого нужно перетащить поле Категория товара в блок ФИЛЬТРЫ.

После этого над сводной таблицей появляется дополнительная строка с фильтрами.

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

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

К томе же, полей, по которым фильтруем данные, может быть несколько. Чтобы отфильтровать данные в сводной таблице не только по категории товара, но и по другим полям, их также нужно перетянуть в блок ФИЛЬТРЫ.

Как обновить сводную таблицу

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

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

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

Как изменить внешний вид (формат) сводной таблицы

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

В статье кратко остановимся на возможностях.

Изменить внешний вид сводной таблицы можно на вкладке Конструктор.

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

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

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

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

Значение (пусто) в сводной таблице — как убрать

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

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

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

В этом случае Пусто исчезнет и для строк, и для столбцов одновременно.

Обратите внимание! Если вы убираете значение Пусто, то есть следующая опасность: при добавлении новых значений в исходную таблицу (например, появится новая категория товара) они не появятся автоматически при обновлении сводной таблицы. Их нужно будет добавить вручную, установив “галочку” (там же, где снимали “галочку” с Пусто).

Где изменить источник сводной таблицы

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

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

Для этого щелкнем в любом месте сводной таблицы и далее идем на вкладку Анализ — Источник данных.

Откроется лист с исходной таблицей, в котором бегущим выделением будут выделены данные, которые попали в сводную таблицу, а в окне — источник данных.

Далее необходимо в открывшемся окне в поле Таблица или диапазон указать новый диапазон для сводной таблицы: просто выделить необходимый диапазон левой кнопкой мыши (полностью диапазон, включая новый столбец, а не только новый столбец). Нажимаете Ок.

Вернувшись на лист сводной таблицы, вы увидите, что новое поле Продавец появилось в списке полей. Теперь его можно перетаскивать в блоки СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ.

Как скопировать сводную таблицу

Чтобы скопировать сводную таблицу, необходимо выделить ее полностью, вместе со строками фильтров. Далее копировать как обычно (Ctrl+C или кнопка Копировать) и вставить в нужное место (Ctrl + V или кнопка Вставить).

Удаляем сводную таблицу

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

Если сводная таблица расположена на листе, где есть другие данные, самый простой способ ее удалить — выделить полностью сводную таблицу вместе с фильтрами (если они есть) и нажать кнопку Delete.

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

Сообщество Excel Analytics | обучение Excel

Канал на Яндекс.Дзен

Вам может быть интересно:

Источник

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

Цель
работы:
научиться создавать и применять сводные
таблицы

при
работе с данными

Содержание
работы:

1
Мастер сводных таблиц

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

3
Работа со сводной таблицей

Мастер
сводных таблиц

Сводная
таблица (или
свод) – это
таблица
итогов различных видов, составленная
по данным из нескольких таблиц. Сводная
таблица составляется в виде отчёта, в
котором данные сгруппированы по блокам,
для каждого блока и всего свода по
какому-либо полю вычисляется заданная
функция – среднее, сумма и др., причём
таких полей может быть несколько. В
качестве таблиц-источников могут
выступать списки, другие сводные таблицы,
базы данных внешние и внутренние,
отдельные блоки данных электронной
таблицы Excel
и др. Сводная таблица обеспечивает
различные способы агрегирования
информации. Простейший свод составляется
по одной таблице.

Пример
1
Дан список
«Экзаменационная ведомость»
(рис.11.1).

Рисунок
11.1 Список
«Экзаменационная ведомость»

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

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

В
окне Создание
сводной таблицы
(рис. 11.2) нужно ввести следующую информацию:

1Указание
вида источника сводной таблицы :

использование
списка (базы данных Excel);
использование
внешнего источника данных;
использование
нескольких диапазонов консолидации;
использование
данных из другой сводной таблицы.

Рисунок
11.2 Составление сводной таблицы

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

2Указание
диапазона ячеек, содержащего исходные
данные. Список (база данных Ехсеl)
должен обязательно содержать имена
полей (столбцов). Полное имя диапазона
ячеек записывается в виде :

имя_книги]
имя_листа! диапазон ячеек

(параметр
в квадратных скобках необязателен)

Если предварительно
курсор установлен в список, для которого
строится сводная таблица, то интервал
ячеек будет указан автоматически.
Данные из других документов (внешние
базы данных, другие таблицы и их диапазоны)
указываются через кнопку Выбрать
подключение…,
появляется окно, в котором выбирается
диск, каталог и файл рабочей книги,
вводится имя рабочего листа и диапазон
либо имя блока ячеек.

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

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

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

После
нажатия кнопки ОК
будет сформирована сводная таблица со
стандартным именем СводнаяТаблица1
и список её полей (рис. 11.3). Если список
полей не вывелся, нужно щелкнуть правой
мышью по имени СводнаяТаблица1
и выбрать
команду
Показать список полей.

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

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

Рисунок
11.3 Макет отчёта сводной таблицы

Область
Фильтр отчёта –
здесь
размещаются поля, по которым подводятся
итоги, согласно выбранной функции, в
нашем случае — Оценки.
Область нужно определять обязательно.

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

Область
Названия строк
— поля
размещаются сверху вниз, обеспечивая

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

Область
∑
Значения по
умолчанию появляется вариант
Сумма по полю Оценка.
При необходимости использования другой
функции в качестве подведения итогов
свода нужно щелкнуть мышью по строке
Сумма
по полю Оценка и
в раскрывающемся меню выбрать опцию
Параметры
полей значений. В
результате появится окно (рис. 11.4 ), во
вкладке
Операция можно
выбрать одну из 12 функций (количество,
среднее, максимум, произведение, …,
дисперсия), во вкладке Дополнительные
вычисления – задать доли, нарастающий
итог и пр. – см. табл. 1. При использовании
функции сравнения (Отличие, Доля,
Приведенное отличие) выбирается Поле
и Элемент,
с которым будет производиться сравнение.
Список Поле
содержит поля сводной таблицы, с которым
связаны базовые данные для пользовательского
вычисления. Список Элемент
содержит
значения поля, участвующего в
пользовательском вычислении.

Таблица
1 Виды
дополнительных функций в полях области
данных

Функция	Результат
1	2
Отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент
Доля	Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам.
Приведенное отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значению этого элемента
С нарастающим итогом в поле	Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге
Доля от суммы по строке	Значения ячеек области данных отображаются в Процентах от итога строки
1	2
Доля от суммы по столбцу	Значения ячеек области данных отображаются в Процентах от итога столбца
Доля от общей суммы	Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс	При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца)

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

Рисунок
11.4 Окно Параметры
полей значений

Для
выполнения задания примера 1 в окне
списка полей для добавления в отчёт
нужно поставить галочки во всех полях,
затем перетащить мышью поля №
группы, Фамилия И.О.
и №
зач. кн. в
раздел Названия
строк, поле
Код
преп – в
раздел Названия
столбцов,
а поле Оценки
– в Фильтр
отчёта. В
области ∑
Значения по
умолчанию стоит вариант
Сумма по полю Оценка,
поэтому нужно щелкнуть мышью по этой
строке и в появившемся окне во вкладке
Операции
заменить Сумма
на Среднее
(рис. 11.4).

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

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

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

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

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

Рисунок
11.5 Исходная таблица, сводная таблица и
список её полей.

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

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

Контрольные
вопросы

1
Что называется сводной таблицей, сводом?

2
Для чего предназначены сводные таблицы?

3
Что помещается в областях для строк,
столбцов, данных макета?

4
Как задать в сводной таблице вид
вычислительного итога Сумма, Максимум,
Произведение?

5
Как обновить данные в сводной таблице?

6
Как изменить структуру сводной таблицы
(добавить или изменить поля строк,
столбцов, данных?

Задание

Для
таблицы своего варианта из лабораторной
работы №9 «Списки в MS Excel. Сортировка
и фильтрация данных» построить две
сводные таблицы. Поля, помещаемые в
области строк, столбцов и данных выбрать
самостоятельно.

Результаты
(два свода) сохранить на своём носителе
(флэшке).

Содержание
отчёта

1
Название работы

2
Цель работы

3
Содержание работы

4
Задание и своды (на носителе) – две
таблицы.

5
Письменные ответы на контрольные вопросы

6
Выводы по работе

Лабораторная
работа №12

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник