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

Содержание

Консолидация нескольких листов в одной сводной таблице

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

Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.

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

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

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

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

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

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

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

Консолидация (доступную на вкладке Данные в группе Работа с данными).

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

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
3. В списке Выбрать команды из выберите пункт Все команды.
4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
В книге щелкните пустую ячейку, которая не является частью сводной таблицы.
Щелкните значок мастера на панели быстрого доступа.
org/ListItem»>
На странице Шаг 1 мастера выберите параметр
в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.
На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.
На странице Шаг 2б сделайте следующее:
1. Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить.
  
  Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
2. В разделе Во-первых, укажите количество полей страницы сводной таблицы введите 0, а затем нажмите кнопку Далее.
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
3. В списке Выбрать команды из выберите пункт Все команды.
4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
В книге щелкните пустую ячейку, которая не является частью сводной таблицы.
На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку
Далее.
На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.
На странице Шаг 2б сделайте следующее:
1. Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить.
  
  Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
Нажмите кнопку Далее.
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.
2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.
3. В списке Выбрать команды из выберите пункт Все команды.
4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.
В книге щелкните пустую ячейку, которая не является частью сводной таблицы.
На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.
На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.
На странице Шаг 2б сделайте следующее:
1. Перейдите в книгу и выделите диапазон ячеек, а затем вернитесь в мастер сводных таблиц и диаграмм и нажмите кнопку Добавить.
  
  Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
2. org/ListItem»>
  В разделе Во-первых, укажите количество полей страницы сводной таблицы щелкните число полей, которые вы хотите использовать.
3. В группе Затем выберите диапазон в списке и укажите метку элемента в каждом из доступных окон полей. Повторите операцию для каждого диапазона для каждого из полей страницы выберите диапазон ячеек, а затем укажите его имя.
  
  Пример
  - Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
  - Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».
4. Нажмите кнопку Далее.
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

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

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

Сводная таблица в Excel

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

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

Самое рациональное решение – это создание сводной таблицы в Excel:

Выделяем ячейку А1, чтобы Excel знал, с какой информацией придется работать.
В меню «Вставка» выбираем «Сводная таблица».
Откроется меню «Создание сводной таблицы», где выбираем диапазон и указываем место. Так как мы установили курсор в ячейку с данными, поле диапазона заполнится автоматически. Если курсор стоит в пустой ячейке, необходимо прописать диапазон вручную. Сводную таблицу можно сделать на этом же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забывайте указывать для них место. На странице появляется следующая форма:
Сформируем табличку, которая покажет сумму продаж по отделам. В списке полей сводной таблицы выбираем названия столбцов, которые нас интересуют. Получаем итоги по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

Первая строка заданного для сведения данных диапазона должна быть заполнена.
В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.

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

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

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

Создадим отчет с помощью мастера сводных таблиц:

Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды». Здесь на вкладке «Настройка» находим «Мастер сводных таблиц». Добавляем инструмент в панель быстрого доступа. После добавления:
Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в «нескольких диапазонах консолидации». То есть нам нужно объединить несколько мест с информацией. Вид отчета – «сводная таблица». «Далее».
Следующий этап – «создать поля». «Далее».
Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон вместе с шапкой – «добавить». Второй диапазон вместе с названием столбцов – снова «добавить».
Теперь в списке выбираем первый диапазон. Ставим птичку у единицы. Это первое поле сводного отчета. Даем ему имя – «Магазин 1». Выделяем второй диапазон данных – ставим птичку у цифры «2». Название поля – «Магазин 2». Нажимаем «Далее».
Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений. У нас получилось так:

Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.

Как работать со сводными таблицами в Excel

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

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

Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:

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

А вот что получится, если мы уберем «дату» и добавим «отдел»:

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

Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.

Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».

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

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

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

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

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.

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

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

Вставка сводной таблицы — служба поддержки Майкрософт

Excel для Интернета

Microsoft 365 для веб-обучения

Excel для Интернета

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

Знакомство с Excel для Интернета
видео
Создание формул
видео
Вставить сводную таблицу
видео
Условное форматирование
видео
Мгновенное заполнение
видео
Получите ценную информацию с Анализом данных
видео
Сотрудничать
видео

Следующий: PowerPoint для Интернета

Попробуйте!

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

Выберите ячейки, из которых вы хотите создать сводную таблицу.
Выбрать Вставить > Сводная таблица .
Под Выберите данные, которые вы хотите проанализировать , выберите Выберите таблицу или диапазон .
В Table/Range проверьте диапазон ячеек.
Под Выберите, где вы хотите разместить отчет сводной таблицы , выберите Новый рабочий лист , чтобы поместить сводную таблицу на новый рабочий лист или Существующий рабочий лист , а затем выберите место, в котором должна появиться сводная таблица.
Выберите OK .
Чтобы добавить поле в сводную таблицу, установите флажок имени поля на панели Поля сводных таблиц.
Примечание. Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в Строки , иерархии дат и времени добавляются к столбцам , а числовые поля добавляются к значениям .
Чтобы переместить поле из одной области в другую, перетащите поле в целевую область.
Выберите Поделиться , введите имя и выберите Отправить , чтобы пригласить коллегу для совместной работы над книгой вместе с вами.

Хотите больше?

Создайте сводную таблицу для анализа данных рабочего листа

Обзор сводных таблиц и сводных диаграмм

Справка по Excel и обучение

Быстрый старт Office для Интернета

Создание сводной диаграммы — служба поддержки Майкрософт

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

Справка по Excel и обучение

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

Создать сводную диаграмму

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

Следующий: Делитесь и соавторствуйте

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Дополнительно. ..Меньше

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

Создать сводную диаграмму

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

Данные о расходах домохозяйства	Соответствующая сводная диаграмма

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

org/ItemList»>
Выберите ячейку в таблице.
Выберите Инструменты сводной таблицы > Анализ > Сводная диаграмма .
Выберите диаграмму.
Выберите OK .

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

Создайте сводную таблицу, если у вас ее еще нет.
Выберите любую ячейку в сводной таблице.
На вкладке Вставка нажмите кнопку, чтобы вставить столбчатую, линейную, круговую или лепестковую диаграмму. Обратите внимание, что другие типы диаграмм в настоящее время не работают со сводными таблицами. Например, древовидные диаграммы, статистические диаграммы и комбинированные диаграммы еще не работают со сводными таблицами.
org/ListItem»>
После того, как вы вставите столбчатую, линейную, круговую или лепестковую диаграмму, вы можете повернуть ее, изменив или переместив поля с помощью списка полей сводной таблицы.
Вы также можете фильтровать данные в сводной таблице и использовать срезы. Когда вы это сделаете, диаграмма также будет отфильтрована.

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

Выберите ячейку в сводной таблице.
org/ListItem»>
На вкладке Вставить выберите раскрывающееся меню Вставить диаграмму , и щелкните любой параметр диаграммы.
Теперь диаграмма появится на рабочем листе. Если щелкнуть в любом месте диаграммы, на ленте появится вкладка Chart . Вы можете использовать любой из вариантов в Диаграмма вкладка для изменения диаграммы.

См. также

Добавление заголовков осей к диаграмме

Изменение меток осей на диаграмме

Нужна дополнительная помощь?

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