Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как совместить данные из нескольких листов в один
 
#1
24.08.2018 16:20:29
Есть документ Excel.На нем есть листы с данными.Все данные расположены в одинаковых столбцах на каждом листе.Нужно что при использование макроса создавался новый лист и выводились данные с предыдущих листов.
 
 
 
#2
24.08.2018 16:25:43
Demis, используйте надстройку Plex
 
 
 
#3
24.08.2018 16:34:03
Demis, если версия excel позволяет, то посмотрите Power Query (PQ)
там ваша задача практически в одно действие решается
 
 
 
#4
24.08.2018 16:38:36
Demis,можно использовать функциональность Запросы (Данные - Получить данные - Из других источников - Из таблицы/диапазона), а там - Главная - Добавить запросы.
Это решение без макроса
Изменено: den82spb - 24.08.2018 16:39:08
 
 
 
#5
24.08.2018 23:33:48
Вариант: тривиальный макрос.
1. Количество листов в Вашем файле для макроса - не критично.
2. Макрос создает папку OUT, в которую "убирает" файл "Itog" с Вашей "Хотелкой".
3. Макрос всегда должен присутствовать в файле - Demis.xlsb.
P.S.
"По Уму": надо иметь папку IN с входными файлами - макросный файл (с одним листом) должен создавать новые листы и копировать на них входные файлы.
Как-то так...
Изменено: Мотя - 25.08.2018 00:14:51
 
 
 
#6
30.10.2019 15:17:28
Здравствуйте форумчане!

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

Макрос собирает в сборную таблицу данные с листов. Но вставляет не значения, а формулы.
И так как многие данные в листах берутся из других мест или являются результатом действия формул, на сборном листе выводятся ошибки.

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

Power Qwery тоже пытался приспособить под это дело, но не разобрался как он собирает с непостоянного количества листов.

Может кто подскажет, как изменить этот макрос, чтобы он вставлял только значения?

Сделал простой пример с этим макросом.  
 
 
 
#7
30.10.2019 15:20:13
Цитата
LAV75 написал:
Сделал простой пример с этим макросом.  
Изменено: LAV75 - 30.10.2019 15:22:18 (туплю)
 
 
 
#8
30.10.2019 15:44:39
LAV75, замените
Код
Range(.Cells(4, 1), .Cells(13, 7)).Copy Cells(RW, 1)
на
Код
Range(.Cells(4, 1), .Cells(13, 7)).Copy
Cells(RW, 1).PasteSpecial Paste:=xlPasteValues
 
 
 
#9
30.10.2019 16:11:40
в PQ
 
 
 
#10
30.10.2019 16:41:23
artyrH, по моему опыту делать запросы менее зависимыми от фиксированных наименований столбцов очень облегчает работу в дальнейшем. Тогда запросы получаются универсальными и их легко переделывать в функции для обработки разных источников но по одному и тому же алгоритму.
Код
let
    Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="adress"]}[Content]{0}[Column1]), null, true),
    FilteredRows = Table.SelectRows(Source, each ([Name] <> "сборка") and ([Kind] = "Sheet"))[[Data]],
    AddedCustom = Table.AddColumn(FilteredRows, "Headers", each Table.PromoteHeaders([Data])),
    Combine = Table.Combine( AddedCustom[Headers] ),
    RemovedColumns = Table.RemoveColumns( Combine, List.Select( Table.ColumnNames( Combine ), each Text.Contains( _, "Column" ) ) ),
    ReplacedErrors = Table.ReplaceErrorValues( RemovedColumns, List.Transform( Table.ColumnNames( RemovedColumns ), each { _, null} ) )
in
    ReplacedErrors
Вот горшок пустой, он предмет простой...
 
 
 
#11
30.10.2019 19:20:00
PooHkrd, да, все верно. и вправду запрос становится универсальным без упоминания названий столбцов.
спасибо за этот вариант
 
 
 
#12
30.10.2019 23:00:30
artyrH, вот только этот шаг
Код
RemovedColumns = Table.RemoveColumns( Combine, List.Select( Table.ColumnNames( Combine ), each Text.Contains( _, "Column" ) ) ),

лучше бы заменить на
Код
RemovedColumns = Table.SelectColumns( Combine, List.Select( Table.ColumnNames( Combine ), each not Text.Contains( _, "Column" ) ) ),

Ибо если левых столбцов не будет то может вылезти ошибка, а наличие корректных столбцов по идее должно быть обеспечено всегда.
З.Ы. Хотя, щас проверил  Table.RemoveColumns без ошибок отрабатывает если список столбцов на удаление пустой. Так что необязательно.
Изменено: PooHkrd - 30.10.2019 23:03:23
Вот горшок пустой, он предмет простой...
 
 
 
#13
30.10.2019 23:22:02
PooHkrd, спасибо. Возьму на заметку
 
 
 
#14
31.10.2019 08:23:02
casag,
работает!
Спасибо!
Если я правильно понял - в исходной строке кода
"Copy" это команда на копирование необходимого,
а следом через пробел "Cells" это уже команда на вставку с указанной ячейки?
 
 
 
#15
31.10.2019 08:38:36
artyrH,
вы сделали сборку через Power Qwery?
Я пытался разобраться как собрать данные, но увы, не понял как надстройку заставить просматривать все листы, а не те которые были на момент сборки.
В вашем файле добавил лист с данными (т.к. их количество не постоянное - увеличивается),
обновил сводную и не увидел в ней данных с добавленного листа.
Или я все таки не так что-то делаю, или работа с данной надстройкой для пользователя сложнее чем кажется и нужно еще что-то допиливать.

Поясню, я пытаюсь сделать документ, в котором данные с листов собираются для анализа и визуализации на графиках в сводную таблицу.
Чтобы пользователь мог открыв лист "сборка" увидеть в динамике, как ведут себя данные в течение времени (от листа к листу, от даты к дате).
Ну или чтобы для этого ему нужно было максимум нажать большую волшебную кнопку на экране, чтобы таблица обновилась.
 
 
 
#16
31.10.2019 08:42:56
Цитата
LAV75 написал:
добавил лист с данными (т.к. их количество не постоянное - увеличивается), обновил сводную и не увидел в ней данных с добавленного листа
перед обновлением нажмите Ctrl+S
 
 
 
#17
31.10.2019 08:45:33
PooHkrd, artyrH,

вы обсуждали некий универсальный способ - если не трудно, можно пояснить для меня, написанный PooHkrd код и далее обсуждение по нему, это изменение алгоритма сборки в PQ?
Это нужно вставить или заменить где-то?
 
 
 
#18
31.10.2019 08:47:44
Цитата
LAV75 написал:
Это нужно вставить или заменить где-то?
нужно вставить вместо моего в Расширенном редакторе
Изменено: artyrH - 31.10.2019 08:49:01
 
 
 
#19
31.10.2019 08:54:05
Не судите строго,
я прекрасно представляю, каково это, пытаться объяснить что-то явное и для себя, кому-то кто далек от темы)))
В своей работе стремлюсь сделать документы удобными и универсальными для простых пользователей, используя то, что нахожу на форумах, что-то получается сделать рекордером.
Но чтобы прочитать и понять код не хватает элементарных знаний азов.
 
 
 
#20
31.10.2019 09:11:46
Цитата
LAV75 написал:
Не судите строго
это все понятно. вы запрос по шагам можете просматривать?
 
 
 
#21
31.10.2019 10:08:48
Цитата
LAV75 написал:
Если я правильно понял
Да, вы верно поняли.
 
 
 
#22
31.10.2019 10:36:49
Цитата
LAV75 написал:
Это нужно вставить или заменить где-то?
Во вложении файл с моим кодом.
Логика запроса artyrH,  простая я в нем только слегка заменил обращение к столбцам таблиц с фиксированного на динамическое:
1. из файла обращаемся к содержимому этого же файла сохраненному на диск (отсюда вывод - прежде чем обновлять таблицу после внесения изменений необходимо сохранить файл, иначе запрос не увидит этих изменений.
2. из списка всех листов убираем лист "сборка"
3. собираем друг под друга содержимое остальных листов
4. заменяем ошибки в формулах на пустые ячейки.
5. ПРОФИТ!
Вот горшок пустой, он предмет простой...
 
 
 
#23
31.10.2019 13:24:57
artyrH,
Цитата
artyrH написал:
вы запрос по шагам можете просматривать?
Думаю да, нужно покопаться и научусь)
Спасибо за помощь!

PooHkrd,
спасибо большое! Буду разбираться.
Как кто-то четко подметил: "без теории практика слепа, а без практики теория мертва".))
Т.ч. углублюсь в теорию.

casag, спасибо!
 
 
 
#24
31.10.2019 13:45:15
Цитата
LAV75 написал:
"без теории практика слепа, а без практики теория мертва".)
вот недорогой вариант для теории и практики
 
 
 
#25
01.11.2019 15:20:34
наткнулся на нее вчера - заинтересовала фраза "а что, так можно было?!")))
Смотришь, как одну и ту же задачу решают разными способами и понимаешь, что ничего не знаешь.
Спасибо за подсказку!

Цитата
PooHkrd написал: ПРОФИТ!
Разобрался почти!
Только получается если на компе пользователя нет надстройки PQ то и обновление ему недоступно?
Я попытался обновить файл с отключенным PQ - ошибка макроса refreshall...(
 
 
 
#26
01.11.2019 15:57:06
LAV75, то есть вы дополнили вчерашнее сообщение и нет никакой надписи, как, например, в #18, о том что внесено изменение в сообщение. интересно
 
 
 
#27
01.11.2019 16:06:43
Цитата
LAV75 написал:
Только получается если на компе пользователя нет надстройки PQ то и обновление ему недоступно?
Само собой.
Вот горшок пустой, он предмет простой...
 
 
 
#28
22.11.2019 14:16:26
Цитата
artyrH написал:
то есть вы дополнили вчерашнее сообщение и нет никакой надписи, как, например, в #18, о том что внесено изменение в сообщение
artyrH, здравствуйте. Не совсем понял Ваше сообщение...
Разбирался как работает сборка кода в PQ.
Разобрался вроде. Настроил код, мою задачу выполняет.
Но так как конечный пользователь сидит на старом Excel, PQ ему не ставится.
Думаю сижу над макросами теперь...
 
 
 
#29
22.11.2019 14:44:45
Цитата
LAV75 написал:
конечный пользователь сидит на старом Excel
этот юзер так и будет сидеть на старом до пенсии и далее. ему что, в лом скачать и установить excel поновее. из-за него вам приходится теперь думать да сидеть)
Цитата
LAV75 написал:
Не совсем понял Ваше сообщение..
да вы не причем.. помню вы создали сообщение в один день, а на следующий день изменили это сообщение. только автоматической пометки о изменении сообщения нету. ну да ладно  
 
 
 
#30
22.11.2019 15:27:44
Цитата
artyrH написал:
ему что, в лом скачать и установить excel поновее
Он бы рад и пиратку воткнуть, чтобы удобнее работать было.
Да IT закономерно не дают такой возможности.
А лицензий для всех нет....
 
 
 
Страницы: 1 2 След.
Наверх