Создание и редактирование сводных таблиц

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

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

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

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

2. Выделите любую ячейку в исходном диапазоне данных.

3. Перейдите на вкладку Сводная таблица верхней панели инструментов и нажмите на кнопку Вставить таблицу .

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

4. Откроется окно Создать сводную таблицу.

   

   • Диапазон исходных данных уже указан. В этом случае будут использоваться все данные из исходного диапазона. Если вы хотите изменить диапазон данных (например, включить только часть исходных данных), нажмите на значок . В окне Выбор диапазона данных введите нужный диапазон данных в формате Лист1!$A$1:$E$10. Вы также можете выделить нужный диапазон ячеек на листе с помощью мыши. Когда все будет готово, нажмите кнопку OK.

   • Укажите, где требуется разместить сводную таблицу.

     • Опция Новый лист выбрана по умолчанию. Она позволяет разместить сводную таблицу на новом рабочем листе.

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

       

       В окне Выбор диапазона данных введите адрес ячейки в формате Лист1!$G$2. Также можно щелкнуть по нужной ячейке на листе. Когда все будет готово, нажмите кнопку OK.

   • Когда местоположение таблицы будет выбрано, нажмите кнопку OK в окне Создать таблицу.

Пустая сводная таблица будет вставлена в выбранном местоположении.

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

Выбор полей для отображения

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

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

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

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

Ниже приводятся примеры использования разделов Фильтры, Столбцы, Строки и Значения.

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

  

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

  

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

  

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

  

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

Упорядочивание полей и изменение их свойств

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

С его помощью можно:

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

Параметры полей из разделов Фильтры, Столбцы и Строки выглядят одинаково:

На вкладке Макет содержатся следующие опции:

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

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

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

• Опция Имя источника позволяет посмотреть имя поля, соответствующее заголовку столбца из исходного набора данных.
• Опция Пользовательское имя позволяет изменить имя выбранного поля, отображаемое в сводной таблице.
• В списке Операция можно выбрать функцию, используемую для вычисления суммирующего значения всех значений из этого поля. По умолчанию для числовых значений используется функция Сумма, а для текстовых значений — функция Количество. Доступны следующие функции: Сумма, Количество, Среднее, Макс, Мин, Произведение, Количество чисел, Стандотклон, Стандотклонп, Дисп, Диспр.
• Опция Дополнительные вычисления позволяет отображать мгновенные пользовательские вычисления вместо добавления формулы и создания вычисляемого поля. Без вычислений — эта опция выбрана по умолчанию, она отображает фактическое значение в поле. Другие опции вычисления: % от общего итога, % от столбца, % от строки, % от, % от суммы по родительской строке, % от суммы по родительскому столбцу, % от родительской суммы, Отличие, Разница (%), С нарастающим итогом в поле, % от суммы с нарастающим итогом в поле, Сортировка от минимального к максимальному, Сортировка от максимального к минимальному, Индекс. Используйте Базовое поле и Базовый элемент, если эти параметры доступны для выбранного вами вычисления (% от, % от родительской суммы, Отличие, Разница (%), С нарастающим итогом в поле, % от суммы с нарастающим итогом в поле).
• Опция Числовой формат позволяет выбрать необходимый числовой формат для поля значения. Нажмите эту кнопку, выберите нужный формат из списка Категория, затем нажмите OK. Чтобы узнать больше о числовых форматах, обратитесь к этой странице.

Группировка и разгруппировка данных

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

Группировка дат

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

• Начиная с — по умолчанию выбирается первая дата в исходных данных. Чтобы ее изменить, введите в это поле нужную дату. Отключите это поле, чтобы игнорировать начальную точку.
• Заканчивая в — по умолчанию выбирается последняя дата в исходных данных. Чтобы ее изменить, введите в это поле нужную дату. Отключите это поле, чтобы игнорировать конечную точку.
• По — опции Секунды, Минуты и Часы группируют данные в соответствии со временем, указанным в исходных данных. Опция Месяцы исключает дни и оставляет только месяцы. Опция Кварталы работает при следующем условии: три месяца составляют квартал, таким образом получаются Кв1, Кв2 и т. д. Опция Годы группирует даты по годам, указанным в исходных данных. Комбинируйте варианты, чтобы добиться желаемого результата.
• Количество дней — установите необходимое значение для определения диапазона дат.
• По завершении нажмите OK.

Группировка чисел

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

• Начиная с — по умолчанию выбирается наименьшее число в исходных данных. Чтобы изменить его, введите в это поле нужное число. Отключите это поле, чтобы игнорировать наименьшее число.
• Заканчивая в — по умолчанию выбирается наибольшее число в исходных данных. Чтобы изменить его, введите в это поле нужное число. Отключите это поле, чтобы игнорировать наибольшее число.
• По — установите необходимый интервал для группировки чисел. Например, «2» сгруппирует набор чисел от 1 до 10 как «1-2», «3-4» и т. д.
• По завершении нажмите OK.

Разгруппировка данных

Чтобы разгруппировать ранее сгруппированные данные:

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

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

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

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

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

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

    

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

    

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

    

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

  • Не повторять все метки элементов — позволяет скрыть метки элементов при наличии нескольких полей в табличной форме.

• В выпадающем списке Пустые строки можно выбрать, надо ли отображать пустые строки после элементов:

  • Вставлять пустую строку после каждого элемента — позволяет добавить пустые строки после элементов.
  • Удалить пустую строку после каждого элемента — позволяет убрать добавленные пустые строки.

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

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

• В выпадающем списке Общие итоги можно выбрать, надо ли отображать общие итоги в сводной таблице:

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

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

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

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

Развертывание и свертывание полей

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

• Развернуть — чтобы показать детали для выбранного элемента.
• Свернуть — чтобы скрыть детали для выбранного элемента.
• Развернуть все поле — чтобы показать детали для всех элементов поля. Аналогичная настройка также доступна на верхней панели инструментов.
• Свернуть все поле — чтобы скрыть детали для всех элементов поля. Аналогичная настройка также доступна на верхней панели инструментов.

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

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

Изменение стиля сводных таблиц

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

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

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

• Заголовки строк — позволяет выделить заголовки строк при помощи особого форматирования.
• Заголовки столбцов — позволяет выделить заголовки столбцов при помощи особого форматирования.
• Чередовать строки — включает чередование цвета фона для четных и нечетных строк.
• Чередовать столбцы — включает чередование цвета фона для четных и нечетных столбцов.

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

Фильтрация, сортировка и создание срезов в сводных таблицах

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

Фильтрация

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

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

• Выбор данных, которые надо отображать

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

  примечание

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

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

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

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

• Фильтрация данных по определенным критериям

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

  • Для Фильтра подписей доступны следующие опции:
    • Для текстовых значений: Равно..., Не равно..., Начинается с..., Не начинается с..., Оканчивается на..., Не оканчивается на..., Содержит..., Не содержит...
    • Для числовых значений: Больше..., Больше или равно..., Меньше..., Меньше или равно..., Между, Не между.
  • Для Фильтра значений доступны следующие опции: Равно..., Не равно..., Больше..., Больше или равно..., Меньше..., Меньше или равно..., Между, Не между, Первые 10.
  • Для Фильтра дат доступны следующие опции: Равно..., Перед..., После..., Между..., Завтра, Сегодня, Вчера, Следующая неделя, Эта неделя, Прошлая неделя, Следующий месяц, Этот месяц, Прошлый месяц, Следующий квартал, Этот квартал, Прошлый квартал, Следующий год, Этот год, Прошлый год, С начала года, Все даты периода (сортировка по кварталам или по месяцам), Пользовательский (выберите нужный параметр и введите дату с помощью выпадающего списка или вручную с помощью календаря).

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

  Нажмите кнопку OK, чтобы применить фильтр.

  

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

  

  В первом выпадающем списке можно выбрать, надо ли отобразить Наибольшие или Наименьшие значения. Во втором поле можно указать, сколько записей из списка или какой процент от общего количества записей требуется отобразить (можно ввести число от 1 до 500). В третьем выпадающем списке можно задать единицы измерения: Элемент, Процент или Сумма. В четвертом выпадающем списке отображается имя выбранного поля. Когда нужные параметры будут заданы, нажмите кнопку OK, чтобы применить фильтр.

Кнопка Фильтр появится в Названиях строк или Названиях столбцов сводной таблицы. Это означает, что фильтр применен.

Сортировка

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

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

Создание срезов

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

Изменение дополнительных параметров сводной таблицы

Чтобы изменить дополнительные параметры сводной таблицы, нажмите ссылку Дополнительные параметры на правой боковой панели. Откроется окно «Сводная таблица — дополнительные параметры»:

На вкладке Название и макет можно изменить общие свойства сводной таблицы.

• С помощью опции Название можно изменить название сводной таблицы.

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

  примечание

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

• В разделе Отображать поля в области фильтра отчета можно настроить фильтры отчета, которые появляются при добавлении полей в раздел Фильтры:

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

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

• Опция Автоматически изменять ширину столбцов при обновлении позволяет включить/отключить автоматическую корректировку ширины столбцов. Эта опция выбрана по умолчанию.

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

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

В окне Выбор диапазона данных введите нужный диапазон данных в формате Лист1!$A$1:$E$10. Также можно выбрать нужный диапазон ячеек на рабочем листе с помощью мыши. Когда все будет готово, нажмите кнопку OK.

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

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

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

1. Выделите всю сводную таблицу с помощью кнопки Выделить на верхней панели инструментов.
2. Нажмите клавишу Delete.

Вычисляемые элементы

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

Чтобы добавить вычисляемый элемент:

1. Выделите нужное поле.

2. Перейдите на вкладку Сводная таблица и нажмите кнопку Вычисляемые элементы на верхней панели инструментов.

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

   

4. Заполните поле Формула, вводя нужные поля из списка ниже или нажимая кнопку с плюсом справа от них, а также вводя нужные математические знаки, например «+» или «-».

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