Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Объединение нескольких таблиц в одну в Power Query
 
#1
03.08.2017 14:17:20
Доброго времени суток!
Ситуация: есть папка с 30-ю файлами excel. Каждый файл excel это отдельный поставщик. Таблички в каждом файле имеют одинаковые названия столбцов, начинаются таблички с ячейки A1, таблички не являются умными таблицами или именованными диапазонами.
Вопрос: Можно ли объединить все эти таблички в одну через Power Query , если:
1. в некоторых файлах excel есть еще листы, кроме того что с нужной табличкой.( но в таблицу другие листы подтягивать не надо)
2. в некоторых таблицах может отличаться последовательность столбцов ( но названия столбцов между всеми таблицами одинаковые)
3. в некоторых таблицах могут быть скрыты или сгруппированы некоторые столбцы ( но эти столбцы все равно надо подтянуть в общую таблицу)
4. в папке где все 30 файлов excel, есть старые поставщики, которые уже не актуальны(но удалить файл нельзя из папки) и в папку добавляются новые файлы поставщиков, которые было бы желательно, чтобы  добавлялись в объединенную табличку автоматически, при размещении файла в этой папке.
С уважением, Lari.

P.S. если в нескольких таблицах появятся новые столбцы, разные между собой и которых нет в остальных таблицах, они подтянутся в общую таблицу Power Query и не исказят ли они общую таблицу Power Query ?
 
 
 
#2
03.08.2017 14:30:22
Доброе время суток.
Цитата
Lari написал:
Можно ли объединить все эти таблички в одну через Power Query
Сборка таблиц из разных файлов Excel с помощью Power Query
Общий ответ, можно. Вам нужно определиться при наличии нескольких листов, как их различать - по содержимому, по порядковому номеру листа и т. д.. Практически все подвопросы уже обсуждались на форуме. Вы пробовали их просматривать?
Цитата
P.S. если в нескольких таблицах появятся новые столбцы, разные между собой и которых нет в остальных таблицах, они подтянутся в общую таблицу Power Query и не исказят ли они общую таблицу Power Query ?
Если вы правильно зададите при развёртывании табличного столбца слияния список полей, то выведутся все данные. Для тех таблиц в которых нет таких столбцов, будут выведены значения null в этих как-бы существующих у них столбцов. Вопрос уж больно комплексный. :)
Изменено: Андрей VG - 03.08.2017 14:33:20
 
 
 
#3
03.08.2017 14:33:54
1. Объединять будет по одному листу из файла. Имя листа он спросит у вас в процессе объединения.
2. На последовательность столбцов без разницы.
3. На скрытость и сгруппированность без разницы.
4. Если в названии файлов имеются названия поставщиков, то их можно будет отфильтровать из списка до начала объединения. Запрос PQ будет объединять все файлы, которые находятся в папке, если вы предварительно их не отфильруете по каким-либо критериям

З.Ы. добавление в одну из объединяемых таблиц новых столбцов повлечет их добавление в общий массив с пустыми значениями для строк из файлов, где этих столбцов нет. Но эту ситуацию тоже можно обойти, если в шаблонном запросе, который создает PQ предварительно удалить не нужные столбцы.
Изменено: PooHkrd - 03.08.2017 14:36:17
Вот горшок пустой, он предмет простой...
 
 
 
#4
03.08.2017 14:56:47
Андрей, статью читал, и там подробно описаны шаги для версии до обновления, когда нельзя было загружать папку с файлами целиком. И еще там используются именованные диапазоны.
Перечитал комментарии к той статье, получил ответ на 3-й вопрос, скрывать можно, группировать нельзя, и на 1-й, одинаковые названия листов должны быть.(но если названия листов будут разные, а в каждом файле по одному листу, значит ли что  не подтянет?)

В поисках информации на форуме открыл для себя очень интересный сайт Максима Зеленского.Там я  прочитал что, если  диапазон неименованный , то нужно чтобы начиналась таблица с ячейки A1.
По 4-му пункту, я так понимаю, можно убрать галочки с ненужных файлов при загрузке из папки в самом Power Query, но будут ли автоматически подгружаться новые добавленные файлы?
И остается вопрос №2 и P.S.


С уважением, Lari.
 
 
 
#5
03.08.2017 15:04:36
Lari,на вопрос №2 я вам ответил. На З.Ы. вам ответил и я и Андрей VG,
По поводу одинакового названия листов вот вам тема, Power Query. Загрузка данных из активного листа excel-файлатам Андрей дал наводку, что делать, а я сварганил пример запроса как вытаскивать одинаковые таблицы из разноименных листов.
Вот горшок пустой, он предмет простой...
 
 
 
#6
03.08.2017 15:07:38
PooHkrd, спасибо за Ваш ответ. Теперь у меня есть все ответы на вопросы.
Но возникла несостыковка, Вы пишете по 3-му пункту, что на скрытость и группировку без разницы, но Николай Павлов пишет что скрывать можно, а группировать проблемаично http://www.planetaexcel.ru/techniques/24/2152/#7731 или тут есть нюансы?
 
 
 
#7
03.08.2017 15:13:37
У меня считывались данные из сгруппированных источников (в таком виде часто выгружаются отчеты из 1С). Но такие мне попадаются нечасто, думаю у Николая здесь практики больше.
Но были проблемы с группировкой в случае, если сгруппированы столбцы умной таблицы, связанной с запросом PQ. В этом случае при обновлении данных в таблице группировка часто "залипает" и потом эти столбцы невозможно бывает раскрыть.
Вот горшок пустой, он предмет простой...
 
 
 
#8
03.08.2017 15:13:39
PooHkrd, да да, мое сообщение #4 было ответом на сообщение Андрей VG, #2, а мое сообщение #6 на Ваше #3. Ваше сообщение #3 я еще не видел  когда писал свои сообщение #4 .
 
 
 
#9
03.08.2017 15:18:21
Lari, да я без претензий.
Вот горшок пустой, он предмет простой...
 
 
 
#10
03.08.2017 15:37:30
Цитата
PooHkrd написал:
Но были проблемы с группировкой в случае, если сгруппированы столбцы умной таблицы, связанной с запросом PQ. В этом случае при обновлении данных в таблице группировка часто "залипает" и потом эти столбцы невозможно бывает раскрыть.
но это скорее к вопросу выгрузки на лист, а не забора с листа.
F1 творит чудеса
 
 
 
#11
20.10.2017 11:14:49
Доброго времени суток!
Еще возник вопрос по этой теме:
При подключении запроса к файлу excel с расширением xls,
https://hostingkartinok.com/show-image.php?id=7dff0748083f44e09872999db76f4fe6
лист всего 1, но в запросе для выбора их 2 (Лист1 и Лист1$ФильтрБазыДанных). Такая ситуация только когда из xls выгружаю.
Какая между ними разница и каким листом корректнее пользоваться?
У меня Excel 2016.
С уважением, Lari.
 
 
 
#12
20.10.2017 11:22:59
Пользоваться нужно тем листом, на котором вы точно знаете, что лежат нужные данные. Все остальные это скрытые от пользователя листы, на которых Excel хранит разную нужную ему информацию. Её тоже можно использовать при желании, но только нужно знать что и как.
Вот горшок пустой, он предмет простой...
 
 
 
#13
20.10.2017 11:29:41
PooHkrd, уточню, есть файл ексель с единственным листом. когда этот файл в формате xlsx то при подключении к нем PQ в списке этот 1 лист и есть. А если этот же файл сохранить как xls, то в PQ появляется 2 листа. Я пробовал выгружать оба, никакой разницы не заметил. Но вдруг только один из них выгружать правильнее, поскольку он более адаптирован. Вот я и пытаюсь понять какой использовать.

С уважением, Lari.
 
 
 
#14
20.10.2017 11:33:58
Использовать нужно те источники, в содержании которых уверен вы уверены. Если есть какой-то лист, про который вы не знаете что это и откуда, то зачем его использовать?
Вот горшок пустой, он предмет простой...
 
 
 
#15
20.10.2017 11:46:26
PooHkrd, т.е. Ваша логика такая что про один лист я знаю(Лист1), рас у него название совпадает с исходником , а второй (Лист1$_ФильтрБазыДанных) , с измененным названием, это лист неизвестный.
А для меня оба листа неизвестны, поскольку они оба проходят через обработку PQ.
C уважением, Lari.
 
 
 
#16
20.10.2017 12:00:35
Цитата
Lari написал:
Лист1$_ФильтрБазыДанных
Это не лист, это скрытый именованный диапазон, по идее. Без примера по картинке больше не скажешь.
 
 
 
#17
20.10.2017 12:23:54
Андрей VG, добрый день!

Файл с примером во вложении.
 
 
 
#18
20.10.2017 12:53:02
Да, Lari, у вас там скрытое имя диапазона фильтрации, можете сами посмотреть в Immediate
Код
ThisWorkbook.Names(1).Name

Так что смело используйте первое имя. Согласно этому исследованию, первыми идут имена листов, потом таблиц, а уж в конце именованные диапазоны.
 
 
 
#19
20.10.2017 13:47:42
Андрей VG, спасибо за ссылку, добавил в избранное.

Открыл Immediate (Разработчик-Visual Basic-View-Immediate Window) и что-то у меня там везде пусто.
И  скрытое имя диапазона фильтрации, куда оно девается при сохранении этого файла в формате xlsx?
С уважением, Lari.
Изменено: Lari - 20.10.2017 13:48:28
 
 
 
#20
20.10.2017 15:32:40
Цитата
Lari написал:
то-то у меня там везде пусто.
Lari, там нужно набрать ?ThisWorkbook.Names(1).Name и нажать Enter (не думал, что так всё плохо с VBA)
Цитата
Lari написал:
куда оно девается при сохранении этого файла в формате xlsx?
Возможно, Microsoft посчитало, что в новом формате нет нужды в специальном наименовании диапазона фильтрации на листе, поэтому удаляет при сохранении в новом формате. Честно говоря, не в курсе, не знаю.
 
 
 
Страницы: 1
Читают тему
Наверх