Как создать сводную таблицу по данным из разных листов?

[Олег*]

Добрый вечер!

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

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

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

Одним словом, идеальным вариантом для меня была бы классическая привычная сводная таблица, но построенная по диапазонам из разных листов. Вопрос: как это сделать?

Файл с "заготовкой" прилагаю

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

[Serge 007]

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

Без макросов или без помощи SQL - никак

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

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

[Олег*]

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

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

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

Или в 2010 это вообще невыполнимая задача?
Тогда срочно делаю откат на старую версию (шутка)

[Serge 007]

...особых удобств-то и не надо...

Дело не в "особых" удобствах. Дело в том, что удобств в такой сводной нет вообще никаких. С привычной всем сводной общее у неё - практически только одно название

Надо только попробовать, что называется, в руках повертеть

Вертите, пробуйте, я ведь Вам для этого её в предыдущем посте вложил

...как в Экселе 2010 создать такую таблицу?

Так же как и в остальных версиях, ничего принципиально не изменилось

Ведь там нет мастера сводных таблиц и, соответветственно, нет третьего шага этого мастера.

И мастер там есть и третий шаг здесь ни при чём.
Нужен

третий пункт на первом шаге мастера

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

Или в 2010 это вообще невыполнимая задача?

Скрин и файл вложения сделаны в 2010 Excel

[Олег*]

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

Этим сочетанием клавиш (Alt+D+P) у меня почему-то не вызывается. Разыскал в интернете подробное описание, как нужную кнопку добавить на ПБД. И там же, кстати, подробное описание, как можно настраивать поля в той таблице, которая в итоге должна получиться:
http://pivot-table.ru/kak-zapustit-klassicheskij-master-svodnyx-tablic-i-diagramm-v-excel-2010.html
Там много всего наворочено, я сразу не разобрался. Сейчас уже поздно особо ковыряться, спать уже пора ложиться, завтра буду серьезно вникать. Это я к тому, что может быть функционал не такой уж и ограниченный в итоге получится, если поглубже в это дело вникнуть получится и настроить под мою несложную, в принципе, задачу. По крайней мере, на первый взгляд смотрится, как совершенно нормальная сводная таблица

Дело не в "особых" удобствах. Дело в том, что удобств в такой сводной нет вообще никаких. С привычной всем сводной общее у неё - практически только одно название

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

Вертите, пробуйте, я ведь Вам для этого её в предыдущем посте вложил

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

Скрин и файл вложения сделаны в 2010 Excel

На самом деле, меня смутило расширение Вашего файла: pivot_table_from_consolidated_range.xls

[Serge 007]

...сочетанием клавиш (Alt+D+P) у меня почему-то не вызывается...

Язык Windows на компе должен быть английский. Мы это уже обсуждали в этой теме

...когда во всех опциях настройки мастера разберусь...

А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003

...смутило расширение Вашего файла...

Чем смутило?

[Олег*]

...когда во всех опциях настройки мастера разберусь...

А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003

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

...смутило расширение Вашего файла...

Чем смутило?

Так ведь расширение-то от старой версии

[Serge 007]

А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003

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

Я Вам про Фому, Вы мне про Ерёму
При чём здесь консолидированная сводная?
Я пишу про то что функционал мастера не изменился, следовательно изучать его нет смысла

...Мне там не понравилось, что когда я в таблицы новые данные (строчки) добавляю, а потом на этой сводной таблице ПКМ и говорю "Обновить", в ней новые строчки никак не учитываются.

А что, в обычной сводной у Вас они добавляются? 
Рецепты для обеих сводных одинаковы:
-Динамический диапазон
-Списки/Таблицы
-Макросы
-Диапазон, заведомо больший чем кол-во значений

Так ведь расширение-то от старой версии

Расширение не зависит от версии. Расширение, грубо говоря, это способ сохранения/сжатия файла. Я выбрал xls, Вы вольны выбрать какое-то другое

...Буду обычными формулами делать...

Правильно. Зачем ехать за день на машине, когда пешком до Киева всего месяц займёт

[Олег*]

Я Вам про Фому, Вы мне про Ерёму
При чём здесь консолидированная сводная?
Я пишу про то что функционал мастера не изменился, следовательно изучать его нет смысла

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

А что, в обычной сводной у Вас они добавляются? 
Рецепты для обеих сводных одинаковы:
-Динамический диапазон
-Списки/Таблицы
-Макросы
-Диапазон, заведомо больший чем кол-во значений

Да в том-то и дело, что добавляется безо всяких дополнительных танцев с бубном
В этом-то и состоит огромное удобство!
А вот в получившейся консолидированной сводной таблице этого удобства как раз-то и нет

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

Расширение не зависит от версии. Расширение, грубо говоря, это способ сохранения/сжатия файла. Я выбрал xls, Вы вольны выбрать какое-то другое

Понял, спасибо за разъяснение.

Правильно. Зачем ехать за день на машине, когда пешком до Киева всего месяц займёт

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

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

[Serge 007]

...добавляется.
А вот в консолидированной сводной таблице - нет.

Рецепты для обеих сводных одинаковы:
...
-Списки/Таблицы
...

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

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

[Олег*]

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

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

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

Действительно, работает!  Спасибо! Буду использовать!

А самое приятное, что макрос такой маленький,

Код Выделить Развернуть

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("СводнаяТаблица1").PivotCache.Refresh
End Sub

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

[sdxsd]

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

[vikttur]

В таком случае сводная (как инструмент) не нужна. Макросом можно вытянуть данные и составить обычную таблицу.

Об отображаемом имени: Ваш ник видят те, с кем Вы общаетесь. Отображаемое имя, неудобное в произношении, добавляет неудобств собеседнику и уменьшает Ваши шансы на помощь. Плюс к этому: "как Вы шхуну назовете, так она и поплывет"