Как объединить несколько сводных таблиц в одну в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как объединить несколько сводных таблиц в одну в excel
Объединить ячейки в сводной таблице
Для громоздких сводных таблиц , имеющих большое количество столбцов, зачастую необходимо оптимизировать и саму шапку таблицы с заголовками.
Одним из вариантов оптимизации для удобства чтения является объединение единым заголовком группы столбцов, входящих, например, в один определенный период.
В обычных таблицах мы пользуемся кнопкой « Объединить и поместить в центре ».
В сводной таблице данная кнопка работать не будет .
Для того, чтобы выполнить объединение столбцов под одним общим заголовком, не теряя своего собственного наименования, необходимо:
-
Кликнуть правой клавишей на любом месте сводной таблицы.
В меню выбрать «Параметры сводной таблицы».
В открывшемся окне в группе «Разметка и формат» поставить галку «Объединить и выровнять по центру ячейки с подписями».
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Использование нескольких таблиц для создания сводной таблицы
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете создать сводную таблицу.
 |
 |
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Создание сводной таблицы с использованием нескольких таблиц
Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.
Шаг 1. Импорт связанных таблиц из базы данных
Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:
Может потребоваться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Шаг 2. Добавление полей в сводную таблицу
Обратите внимание: список полей содержит несколько таблиц.
Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:
- Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
- Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Шаг 3. Создание связей при необходимости
Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.
Использование модели данных для создания новой сводной таблицы
Примечание Модели данных не поддерживаются в Excel для Mac.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Дополнительные сведения см. в статье Импорт нескольких таблиц из других источников данных.
Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей. Дополнительные сведения см. в статьях Добавление данных с листа в модель данных с помощью связанной таблицы,Создание связи между двумя таблицами иСоздание связей в представлении схемы.
После создания модели данных эти данные можно использовать в анализе. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
- Щелкните любую ячейку на листе.
- Выберите Вставка и щелкните стрелку вниз элемента Сводная таблица.
- Выберите Из внешнего источника данных.
- Нажмите Выбрать подключение.
- На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
См. также
- Создание модели данных в Excel
- Получение данных с помощью надстройки Power Pivot
- Упорядочение полей сводной таблицы с помощью списка полей
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа внешних данных
- Создание сводной таблицы, подключенной к наборам данных Power BI
- Изменение диапазона исходных данных для сводной таблицы
- Обновление данных в сводной таблице
- Удаление сводной таблицы
Одновременная фильтрация нескольких сводных таблиц
При создании сложных отчетов и, особенно, дашбордов в Microsoft Excel, весьма часто возникает необходимость одновременной фильтрации сразу нескольких сводных таблиц. Давайте разберёмся, как такое можно можно реализовать.
Способ 1. Общий срез для фильтрации сводных на одном источнике данных
Если сводные построены по одной исходной таблице данных, то проще всего использовать для их одновременной фильтрации срез — графический кнопочный фильтр, подключенный сразу ко всем сводным таблицам. Чтобы его добавить, выделите любую ячейку в одной из сводных и на вкладке Анализ выберите команду Вставить срез (Analyze — Insert slicer) . В открывшемся окошке пометьте галочками те столбцы, по которым вы хотите фильтровать данные и нажмите ОК: 
Созданный срез будет, по умолчанию, фильтровать только ту сводную, для которой он был создан. Однако, воспользовавшись кнопкой Подключения к отчетам (Report connections) на вкладке Срез (Slicer) мы можем легко добавить к списку фильтруемых таблиц другие сводные: 
Способ 2. Общий срез для фильтрации сводных на разных источниках
Если ваши сводные были построены не по одной, а по разным исходным таблицам данных, то приведённый выше способ не сработает, т.к. в окне Подключения к отчётам отображаются только те сводные, которые были построены по одному источнику. Однако, можно легко обойти это ограничение, если использовать Модель Данных (мы подробно разбирали её в этой статье). Если загрузить наши таблицы в Модель и связать их там, то фильтрация станет распространяться на обе таблицы одновременно. Допустим, что в качестве исходных данных у нас есть две таблицы по продажам и транспортным расходам: 
Предположим, что перед нами стоит задача по каждой из них построить свою сводную и фильтровать их затем одновременно по городам общим срезом.
Делаем следующее: 1 . Превращаем наши исходные таблицы в динамические «умные» с помощью сочетания клавиш Ctrl + T или команды Главная — Форматировать как таблицу (Home — Format as Table) и даём им имена таблПродажи и таблТранспорт на вкладке Конструктор (Design) .
2 . Загружаем по очереди обе таблицы в Модель с помощью кнопки Добавить в модель данных (Add to Data Model) на вкладке Power Pivot. Напрямую связать эти таблицы в Модели не получится, т.к. пока Power Pivot поддерживает только тип связей «один-ко-многим», т.е. требует, чтобы в одной из таблиц не было дубликатов в столбце, по которому мы связываем. У нас же в обеих таблицах в поле Город присутствуют повторения. Так что потребуется создать ещё одну промежуточную таблицу-справочник со списком уникальных названий городов из обеих таблиц. Проще всего это сделать с помощью функционала надстройки Power Query, которая встроена в Excel начиная с 2016 версии (а для Excel 2010-2013 бесплатно скачивается с сайта Microsoft). 3 . Выделив любую ячейку внутри «умной» таблицы, загружаем их по очереди в Power Query кнопкой Из таблицы / диапазона на вкладке Данные (Data — From table/range) и затем в окне Power Query выбираем на Главной команды Закрыть и загрузить — Закрыть и загрузить в (Home — Close&Load — Close&Load to. ) и вариант импорта Только создать подключение (Only create connection) : 
4 . Соединяем обе таблицы в одну командой Данные — Объединить запросы — Добавить (Data — Combine queries — Append) . Совпадающие по названиям в шапке колонки встанут друг под друга (как столбец Город), а не совпадающие будут разнесены в разные столбцы (но для нас это не важно).
5 . Удаляем все столбцы, кроме колонки Город, щёлкнув по её заголовку правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) и затем удаляем все дубликаты названий городов, щёлкнув ещё раз правой кнопкой мыши по заголовку столбца и выбрав команду Удалить дубликаты (Remove duplicates) : 
6 . Созданный список-справочник выгружаем в Модель Данных через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close&Load — Close&Load to. ) и выбираем вариант Только создать подключение (Only create connection) и — самое главное! — включаем флажок Добавить эти данные в модель данных (Add this data to Data Model) : 
7 . Теперь можем, вернувшись в окно Power Pivot (вкладка Power Pivot — кнопка Управление), переключиться в Представление диаграммы (Diagram view) и связать наши таблицы продаж и траспортных расходов через созданный промежуточный справочник по городам (перетаскиванием полей между таблицами): 
8 . Теперь можно создать все требуемые сводные таблицы по созданной модели с помощью кнопки Сводная таблица (Pivot Table) на Главной (Home) вкладке в окне Power Pivot и, выделив любую ячейку в любой сводной, на вкладке Анализ добавить срез кнопкой Вставить срез (Analyze — Insert Slicer) и выбрать для среза в списке поле Город в добавленном справочнике: 
Теперь, нажав на знакомую кнопку Подключения к отчетам на вкладке Срез (Slicer — Report connections) мы увидим все наши сводные, т.к. построены они теперь по связанным исходным таблицам. Останется включить недостающие флажки и нажать на ОК — и наш срез начнёт фильтровать все выбранные сводные таблицы одновременно.
Ссылки по теме
- Преимущества сводной по Модели Данных
- План-факт анализ в сводной таблице с Power Pivot и Power Query
- Независимая группировка сводных таблиц