Несколько сводных таблиц на одном листе excel
Сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет "на лету" делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:
Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:
- Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
- На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.
Способ 1. Сборка таблиц для сводной с помощью Power Query
Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.
Сначала создадим новый пустой файл в Excel - в него будет происходить сборка и в нем же потом будет создаваться сводная таблица.
Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос - Из файла - Excel (Get Data - From file - Excel) и укажем исходный файл с таблицами, которые надо собрать:
В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :
Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого - Источник (Source) :
Теперь мы видим общий список всех листов. Если в файле кроме листов с данными есть еще какие-то побочные листы, то на этом шаге наша задача - отобрать только те листы, с которых нужно загрузить информацию, исключив все остальные с помощью фильтра в шапке таблицы:
Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :
Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):
Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:
Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:
Сохраним всё проделанное с помощью команды Закрыть и загрузить - Закрыть и загрузить в. (Close & Load - Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :
Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка - Сводная таблица (Insert - Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:
Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data - Refresh All) .
Способ 2. Объединяем таблицы SQL-командой UNION в макросе
Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.
Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert - Module и копируем туда следующий код:
Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) .
Минусы такого подхода:
- Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
- При изменении количества листов необходимо правки в код макроса (массив SheetNames).
Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:
И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft - Microsoft Access Database Engine 2010 Redistributable
Способ 3. Мастер консолидации сводных таблиц из старых версий Excel
Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:
В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands) :
После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:
А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:
Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.
Нередко исходные данные хранятся не в одном диапазоне данных, а в нескольких, или на разных листах, а то и в различных книгах… Не говоря уже данных, хранящихся не в Excel, а в текстовых файлах, таблицах Access или SQL Server. В этой заметке будет рассмотрены приемы работы с множественными диапазонами, т.е. с отдельными наборами данных, расположенными в одной рабочей книге. Эти наборы либо разделены пустыми ячейками (рис. 1), либо находятся на разных рабочих листах. [1] В следующей заметке будут рассмотрено создание сводной таблицы на основе внешних источников данных.
Рис. 1. Лист с тремя диапазонами данных, которые нужно свести вместе, чтобы проанализировать их как единую группу
Если нужно проанализировать данные, находящиеся в нескольких диапазонах, можно воспользоваться сводными таблицами, созданными на основе нескольких диапазонов консолидации. Концепция нескольких диапазонов консолидации появилась в версиях, предшествующих Excel 2003, когда каждый лист Excel включал не более 65 536 строк. В те уже далекие времена большие таблицы данных, превышающие предельное количество строк, приходилось разбивать на несколько таблиц. Это позволяло хранить данные в одной рабочей книге, но в то же время делало невозможным создание стандартной сводной таблицы, основанной на наборе данных, поскольку эти данные находились на разных листах.
Также можно было создавать сводные таблицы на основе нескольких диапазонов консолидации. При этом данные помещались в единый кеш сводной таблицы. В результате облегчалось выполнение анализа данных. Однако в более ранних версиях Excel возможности консолидация была весьма ограничены (см., например, Excel. Сводная таблица на основе нескольких листов).
Предположим, что в вашем распоряжении имеются три диапазона, которые требуется свести вместе для анализа в качестве единой группы (см. рис. 1). Можно создать сводную таблицу, используя множественные диапазоны консолидации. Это позволяет легко и быстро консолидировать все данные из выбранных диапазонов в отдельной сводной таблице.
Чтобы приступить к сведению данных в одну таблицу, запустите классический мастер сводных таблиц и диаграмм. Для выполнения этой задачи нажмите комбинацию клавиш Alt+D+P. К сожалению, эта комбинация клавиш предназначена для англоязычной версии Excel 2013. В русскоязычной версии ей соответствует комбинация клавиш Alt+Д+Н. Но она по неизвестным мне причинам не работает. Тем не менее, можно вывести старый добрый мастер сводных таблиц на панель быстрого доступа, см. Использование мастера сводных таблиц. После запуска мастера установите переключатель в нескольких диапазонах консолидации (рис. 2). Кликните Далее.
Рис. 2. Мастер сводных таблиц и диаграмм; окно 1 из 3
На следующем шаге мастера нужно решить, позволить программе создать одно поле страницы или несколько собственных полей страницы. Вы заметите, что поля страницы, создаваемые в Excel, чаще всего малопонятны или вообще бессмысленны, поэтому практически во всех случаях предпочтительнее создавать собственные поля страниц (рис. 3). Щелкните на кнопке Далее.
Рис. 3. Мастер сводных таблиц и диаграмм; окно 2 из 3
Теперь нужно указать каждый набор данных. Выделите диапазон первого набора данных и щелкните на кнопке Добавить (рис. 4). Чтобы сводная таблица создавалась корректно, первая строка каждого диапазона должна включать в себя заголовки столбцов. Выделите остальные диапазоны и добавьте их в список. Обратите внимание на то, что каждый набор данных представляет отдельный регион (Север, Юг и Запад). Когда сводная таблица объединит все три набора данных, вам потребуется заново проанализировать каждый регион. Чтобы иметь такую возможность, пометьте каждый диапазон в списке отдельным именем, идентифицирующим набор данных этого диапазона. В результате будет создано поле страницы, позволяющее выполнять фильтрацию данных для каждого региона.
Рис. 4. Выделяйте по одному диапазону и щелкайте на кнопке Добавить
Прежде чем создавать поле страницы Регион, укажите общее количество создаваемых полей страницы. Для идентификатора регионов требуется создать только одно поле страницы, так что щелкните на переключателе 1 (рис. 5). При этом активизируется текстовое поле Первое поле. Можно создать до четырех полей страницы. На следующем этапе нужно пометить каждый диапазон. Щелкните на первом диапазоне в списке и введите в поле Первое поле имя региона – Север. Повторите эту операцию для остальных регионов. Определившись с регионами, щелкните на кнопках Далее.
Рис. 5. Создание поля страницы Регион
На последнем шаге определите местоположение сводной таблицы. Выберите переключатель Новый лист и щелкните на кнопке Готово. Итак, вы успешно объединили три источника данных в одной сводной таблице (рис. 6).
Рис. 6. Сводная таблица, содержащая данные из трех источников
Структура сводной таблицы с несколькими диапазонами консолидации
Поле Строка всегда создается на основе первого столбца источника данных. Отметим, что на рис. 1 первым в источнике данных был столбец Направление деятельности. Поле Строка в новой сводной таблице как раз соответствует ей.
Поле Столбец включает остальные столбцы источника данных. Сводные таблицы, использующие несколько диапазонов консолидации, комбинируют все поля из исходных наборов данных (без первого столбца, который используется полем Строка) в некое «суперполе» с именем Столбец. Поля исходных наборов данных становятся элементами данных поля Столбец. В сводной таблице, представленной на рис. 6, в поле Столбец изначально применяется функция КОЛИЧЕСТВО. Если задать для поля Столбец функцию СУММ, это повлияет на все элементы данных поля Столбец.
Рис. 7. Элементы данных в поле Столбец интерпретируются как один объект. Замена функции КОЛИЧЕСТВО поля Столбец функцией СУММ выполняется по отношению ко всем элементам поля
Поле Значение содержит значения для всех данных поля Столбец. Обратите внимание на то, что даже те поля, которые изначально в наборе данных были текстовыми, трактуются как поля с числовыми значениями. Ярким примером является поле Менеджер направления (см. рис. 7). Несмотря на то что это поле содержало имена и фамилии менеджеров из исходного набора данных, теперь эти записи трактуются в сводной таблице как числа.
Как уже говорилось ранее, сводные таблицы, использующие несколько диапазонов консолидации, объединяют поля исходных наборов данных (кроме первого поля), делая их элементами данных поля Столбец. Поэтому, хотя вы и можете определять такие поля, как Менеджер направления, в качестве текстовых с собственными элементами данных, они не будут больше хранить эти данные. Эти поля будут преобразованы в элементы данных со значениями. В результате поля, изначально содержащие текст или даты, отображают в сводной таблице ничего не значащие числовые значения. Обычно эти поля лучше всего скрыть во избежание конфликтов.
[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.
Если вы строите несколько сводных таблиц на основе одного источника, то, возможно, уже сталкивались с одним мелким, но неприятным моментом: Excel не позволяет независимо группировать данные в таких сводных.
Допустим, мы построили две отдельных сводных таблицы для анализа выручки и объемов продаж:
Но выручку мы хотим видеть по месяцам, а объем продаж по кварталам, поэтому дальше логично применить группировку. Щелкаем правой кнопкой мыши по датам в первой сводной, выбираем Группировать - Месяцы - ОК и выручка действительно начинает отображаться по месяцам, как мы и хотели. Но и во второй таблице даты тоже сгруппировались по месяцам, хотя мы этого совершенно не просили:
И наоборот, если попытаться аналогичным способом сгруппировать даты во второй сводной по кварталам, то перегруппируется и первая сводная, теряя текущую группировку по месяцам.
Дело в том, что при построении любой сводной таблицы Microsoft Excel создает в файле так называемый кэш (Pivot Cache) - область памяти, куда загружаются исходные данные для анализа и вычислений. И когда вы делаете вторую, третью и т.д. сводные по той же исходной таблице Excel услужливо использует уже сформированный ранее для первой сводной кэш, а не создает новый.
С одной стороны, это правильно: данные не дублируются, файл меньше весит и быстрее считается. С другой, мы имеем ту самую проблему с группировкой, изменение которой в одной из сводных влияет на все остальные. Что же делать?
Способ 1. Строим сводную правильно
Чтобы построить сводную таблицу на новом кэше придется воспользоваться старым добрым Мастером сводных таблиц и диаграмм (Pivot Table and Chart Wizard) из версий Excel 97-2003. Как ни странно, но этот "устаревший" инструмент умеет при создании сводной выбирать - делать ли независимую сводную или сводную на основе уже имеющегося кэша. Кнопку для этого Мастера убрали из интерфейса еще в 2007 году, но он до сих пор доступен для совместимости и вполне работоспособен.
Щелкните правой кнпокой мыши по панели быстрого доступа в левом верхнем углу и выберите команду Настройка панели быстрого доступа (Customize Quick Access Toolbar) . Затем в выпадающем списке в верхней части окна выберите Все команды (All Commands) , найдите Мастер сводных таблиц и диаграмм и добавьте на панель:
Теперь можно строить сводную. Ставим активную ячейку в таблицу с данными или выделяем ее, жмем на добавленную кнопку и проходим последовательно шаги Мастера, выбрав на первом создание именно независимого отчета на собственном кэше:
Excel будет настойчиво предлагать сделать сводную на том же кэше - отказываемся и доходим до финала:
Все, теперь группировка этой сводной не будет влиять на соседнюю и обе таблицы можно сгруппировать по-разному:
Способ 2. Отвязываем уже созданную сводную от общего кэша
Если у вас уже есть готовая сводная, и создавать ее заново с помощью Мастера вы не хотите, то можно отвязать ее от общего кэша, используя тактическую хитрость:
- Скопируйте сводную в новый пустой файл
- Обновите сводную (правой кнопкой мыши по ней - Обновить)
- Скопируйте ее обратно на старое место
Такой бессмысленный, на первый взгляд, "финт ушами" заставляет Excel создать для сводной отдельную копию кэша, т.е. делает ее независимой.
Если поставить активную ячейку в сводную и запустить этот макрос с помощью кнопки Макросы на вкладке Разработчик или сочетания клавиш Alt+F8, то текущая сводная таблица будет отвязана от общего кэша, и вы сможете группировать ее независимо.
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.
Можно импортировать несколько таблиц из других источников данных, в том числе из текстовых файлов, веб-каналов данных, данных листа Excel и т. д. Вы можете добавить эти таблицы в модель данных в Excel, создать связи между ними, а затем создать сводную таблицу с помощью модели данных.
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.
Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.
В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.
В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.
Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.
Выберите необходимые для работы таблицы вручную, если вы знаете, какие именно нужны вам. Или же выберите одну или две, а затем щелкните Выбор связанных таблиц для автовыбора таблиц, связанных с уже указанными.
Если установлен флажок Импорт связи между выбранными таблицами, оставьте его, чтобы разрешить Excel воссоздать аналогичные связи таблиц в книге.
Нажмите Готово.
В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.
Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле "ОбъемПродаж" из таблицы "ФактПродажиЧерезИнтернет".
Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Работа с другими типами баз данных
Для использования других реляционных баз данных, например Oracle, может понадобиться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Вы можете импортировать несколько таблиц из приложения Access. Подробнее об этом можно узнать в учебнике по импорту данных в Excel и созданию модели данных.
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
Щелкните любую ячейку на листе.
Выберите Вставка > Сводная таблица.
В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.
Выберите вариант Выбрать подключение.
На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
Читайте также: