Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

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

Автор Олег*, 11.04.2013, 21:09

« назад - далее »

Олег*

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

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

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

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

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

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

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

Serge 007

Цитата: Олег* от 11.04.2013, 21:09...классическая привычная сводная таблица, но построенная по диапазонам из разных листов. Вопрос: как это сделать?
Без макросов или без помощи SQL - никак

Цитата: Олег* от 11.04.2013, 21:09консолидировать данные из разных диапазонов и даже из разных файлов, но, честно говоря, никогда с этим дело не имел, поэтому для меня идеальным вариантом была бы именно сводная таблица.
Сводную таблицу можно построить из нескольких диапазонов консолидации (третий пункт на первом шаге мастера), однако она будет создана в режиме ограниченной функциональности и работать с ней будет крайне неудобно
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

Цитата: Serge 007 от 11.04.2013, 22:50
Сводную таблицу можно построить из нескольких диапазонов консолидации (третий пункт на первом шаге мастера), однако она будет создана в режиме ограниченной функциональности и работать с ней будет крайне неудобно
На самом деле, это как раз тот редкий случай, когда особых удобств-то и не надо :)
Особо навороченного функционала не требуется. Может быть меня и ограниченный вполне устроит. Надо только попробовать, что называется, в руках повертеть :)

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

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

Serge 007

Цитата: Олег* от 11.04.2013, 23:30
...особых удобств-то и не надо...
Дело не в "особых" удобствах. Дело в том, что удобств в такой сводной нет вообще никаких. С привычной всем сводной общее у неё - практически только одно название :)
Цитата: Олег* от 11.04.2013, 23:30
Надо только попробовать, что называется, в руках повертеть
Вертите, пробуйте, я ведь Вам для этого её в предыдущем посте вложил

Цитата: Олег* от 11.04.2013, 23:30
...как в Экселе 2010 создать такую таблицу?
Так же как и в остальных версиях, ничего принципиально не изменилось

Цитата: Олег* от 11.04.2013, 23:30
Ведь там нет мастера сводных таблиц и, соответветственно, нет третьего шага этого мастера.
И мастер там есть и третий шаг здесь ни при чём.
Нужен
Цитата: Serge 007 от 11.04.2013, 22:50третий пункт на первом шаге мастера
скрин я приложил. Обратите внимание на название скрина ;) Именно этим сочетанием клавиш мастер и вызывается. Или на ПБД кнопку можно вынести, на скрине она в правом верхнем углу видна

Цитата: Олег* от 11.04.2013, 23:30
Или в 2010 это вообще невыполнимая задача? :)
Скрин и файл вложения сделаны в 2010 Excel
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

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

Цитата: Serge 007 от 12.04.2013, 00:08
Дело не в "особых" удобствах. Дело в том, что удобств в такой сводной нет вообще никаких. С привычной всем сводной общее у неё - практически только одно название :)
Так вот я и говорю, может, когда во всех опциях настройки мастера разберусь, может быть как раз мне будет вполне достаточно того, что получится в результате. Пока понял только то, что там вроде можно название для каждого диапазона консолидации вводить, а потом это работает как фильтр для того, чтобы выводить только данные по этому диапазону. Вроде так.

Цитата: Serge 007 от 12.04.2013, 00:08
Вертите, пробуйте, я ведь Вам для этого её в предыдущем посте вложил
Да нет, я имел в виду, повертеть в руках уже "на месте", в смысле, уже в том файле, для которого весь этот замысел предназначен, а не в том пробном примере, которой я здесь выложил :) Мне же важно было только в принципе понять, возможно это или нет. Ну вот, теперь понял, что возможно,  теперь буду вставлять, куда задумано :)

Цитата: Serge 007 от 12.04.2013, 00:08
Скрин и файл вложения сделаны в 2010 Excel
На самом деле, меня смутило расширение Вашего файла: pivot_table_from_consolidated_range.xls :)
Муж это единственный зарегенный юзер, а все остальные это хакеры :)

Serge 007

Цитата: Олег* от 12.04.2013, 01:31
...сочетанием клавиш (Alt+D+P) у меня почему-то не вызывается...
Язык Windows на компе должен быть английский. Мы это уже обсуждали в этой теме
Цитата: Олег* от 12.04.2013, 01:31
...когда во всех опциях настройки мастера разберусь...
А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003
Цитата: Олег* от 12.04.2013, 01:31
...смутило расширение Вашего файла...
Чем смутило?
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

Цитата: Serge 007 от 12.04.2013, 07:12
Цитата: Олег* от 12.04.2013, 01:31
...когда во всех опциях настройки мастера разберусь...
А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003
Не, ну я просто раньше никогда не имел дело с консолидированной сводной таблицей.
Кстати, наверно, все-таки откажусь от этой затеи. Мне там не понравилось, что когда я в таблицы новые данные (строчки) добавляю, а потом на этой сводной таблице ПКМ и говорю "Обновить", в ней новые строчки никак не учитываются. Буду обычными формулами делать. Но все равно, для меня разговор в этой ветке очень полезен был. Буду знать, как эти таблицы делать, если когда-нибудь снова придет идея их использовать.


Цитата: Serge 007 от 12.04.2013, 07:12
Цитата: Олег* от 12.04.2013, 01:31
...смутило расширение Вашего файла...
Чем смутило?
Так ведь расширение-то от старой версии :)
Муж это единственный зарегенный юзер, а все остальные это хакеры :)

Serge 007

Цитата: Олег* от 12.04.2013, 21:33
Цитата: Serge 007 от 12.04.2013, 07:12
А чего в них разбираться-то?! Функции мастера не изменились по сравнению с Excel 2003
...я раньше никогда не имел дело с консолидированной сводной таблицей.
Я Вам про Фому, Вы мне про Ерёму :)
При чём здесь консолидированная сводная?
Я пишу про то что функционал мастера не изменился, следовательно изучать его нет смысла

Цитата: Олег* от 12.04.2013, 21:33
...Мне там не понравилось, что когда я в таблицы новые данные (строчки) добавляю, а потом на этой сводной таблице ПКМ и говорю "Обновить", в ней новые строчки никак не учитываются.
А что, в обычной сводной у Вас они добавляются?  ;D
Рецепты для обеих сводных одинаковы:
-Динамический диапазон
-Списки/Таблицы
-Макросы
-Диапазон, заведомо больший чем кол-во значений

Цитата: Олег* от 12.04.2013, 01:31
Так ведь расширение-то от старой версии :)
Расширение не зависит от версии. Расширение, грубо говоря, это способ сохранения/сжатия файла. Я выбрал xls, Вы вольны выбрать какое-то другое

Цитата: Олег* от 12.04.2013, 21:33
...Буду обычными формулами делать...
Правильно. Зачем ехать за день на машине, когда пешком до Киева всего месяц займёт :)

Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

#8
Цитата: Serge 007 от 12.04.2013, 22:14
Я Вам про Фому, Вы мне про Ерёму :)
При чём здесь консолидированная сводная?
Я пишу про то что функционал мастера не изменился, следовательно изучать его нет смысла
Наверно, я неправильно выразился. Я имел в виду, вот с этими настройками надо будет разобраться. Ну уже вроде разобрался :)



Цитата: Serge 007 от 12.04.2013, 22:14
А что, в обычной сводной у Вас они добавляются?  ;D
Рецепты для обеих сводных одинаковы:
-Динамический диапазон
-Списки/Таблицы
-Макросы
-Диапазон, заведомо больший чем кол-во значений
Да в том-то и дело, что добавляется безо всяких дополнительных танцев с бубном :)
В этом-то и состоит огромное удобство!
А вот в получившейся консолидированной сводной таблице этого удобства как раз-то и нет :)

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

Цитата: Serge 007 от 12.04.2013, 22:14
Расширение не зависит от версии. Расширение, грубо говоря, это способ сохранения/сжатия файла. Я выбрал xls, Вы вольны выбрать какое-то другое
Понял, спасибо за разъяснение.

Цитата: Serge 007 от 12.04.2013, 22:14
Правильно. Зачем ехать за день на машине, когда пешком до Киева всего месяц займёт :)
Да честно говоря, там сводная вообще не нужна. Это я просто вспомнил, что такая возможность вроде бы существует (в смысле, сводная по данным из разных листов), ну вот и решил заодно ума-разума набраться и в деле это попробовать. А так мне там просто нужно суммы одного и того же столбца каждой таблицы (на разных листах) между собой сравнивать, в одно место их переносить, рядом располагать и на диаграмму выводить для наглядности. Но это же все формулами можно сделать, на самом деле. Четыре формулы в соседних ячейках располагаем, которые суммы этих столбцов суммируют, ну вот и готово :)

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



Муж это единственный зарегенный юзер, а все остальные это хакеры :)

Serge 007

Цитата: Олег* от 13.04.2013, 00:10...добавляется.
А вот в консолидированной сводной таблице - нет.

Цитата: Serge 007 от 12.04.2013, 22:14
Рецепты для обеих сводных одинаковы:
...
-Списки/Таблицы
...
Вы дополнительно использовали инструмент Таблица, который является динамическим диапазоном. Об этом я писал ранее. Во вложении 54321.xls сводная, построенная на обычном диапазоне. Попробуйте добавить строку и обновить сводную

Во втором вложении - сводная из нескольких листов. Попробуйте добавить строки на любой из листов и обновить сводную. (если макросы включены - обновлять сводную не надо, это произойдёт автоматически)
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Ю-money: 41001419691823 | WMR:126292472390

Олег*

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

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


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

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

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

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


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

sdxsd

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

vikttur

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

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