Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Консолидация в Power Query. Собрать данные диапазона со всех листов в одну таблицу
 
#1
17.12.2021 14:42:52
Добрый день!

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

Задача:
собрать данные со всех листов в один массив (таблицу) но не кучей а только определенный диапазон (таблицу). И желательно так, чтобы при добавлении листа с новым днем эти данные можно было добавлять в полученный массив..
В примере это таблица 3.

Заранее благодарен.
 
 
 
#2
17.12.2021 15:07:16
чем таблица 3 отличается от таблицы 1 и 2?
 
 
 
#3
17.12.2021 15:19:34
Полностью отличается - разный набор столбцов и другие данные. Во всех 3 таблицах. Их там на самом деле больше, я просто вкратце суть изложил. Вытащить данные надо со всех листов именно той таблицы, которая расположена далеко не вверху и не в начале листа, иначе было бы проще...
 
 
 
#4
17.12.2021 15:55:18
Решить можно если поясните, как понять, какую таблицу с листа тащить. Но не понимаю, зачем:)
Почему все хотят хранить информацию в непотребном виде, а потом извращаться, чтобы получить данные???
Один раз назовите умные таблицы по-разному (Сотрудники, Товары, Клиенты). При копировании листа к наименованию таблицы будет добавляться номер, но начало будет одинаковым.
И простейшим запросом соединяйте нужные таблицы:
Код
let
  allTables = Excel.CurrentWorkbook(),
  filter    = Table.SelectRows ( allTables, each Text.StartsWith ( [Name], "Клиенты" ) ),
  combine   = Table.Combine ( filter[Content] )
in
  combine
 
 
 
#5
18.12.2021 10:45:54
Стащить нужно ту таблицу, которая внизу и смещена далеко вглубь листа. Изначальная информация поступает в строго регламентированном непотребном виде и к сожалению изменить порядок ну никак нельзя. А требуется вытащить данные со всех листов именно третьей таблицы в одну целую, чтобы выгрузить это всё в программу, причем с условием, что каждый день добавляется новый лист и его тоже надо добавлять автоматически в свод.
Попробовал запрос - при каждом обновлении он добавляет одни и те же данные ниже первоначально полученных, то есть происходит полное дублирование...
Кстати, заголовки в третьей таблице - отдельный "шедевр" искусства злого гения (объединение ячеек "рулит" про умные таблицы никто не слышал) но в принципе как я понимаю это при запросе можно исправить.
 
 
 
#6
18.12.2021 13:31:12
Цитата
Игорь Иванов написал:
про умные таблицы
А что тогда Вы называете Таблицей? То, что границами ячеек обведено? :) Если левый верхний угол этой таблицы всегда на одном месте, то сделать можно. Если нет - гуглите функцию Кена Р, распаковывающую архив, с ее помощью можно будет найти информацию о форматировании ячеек.
Цитата
Игорь Иванов написал:
Попробовал запрос - при каждом обновлении он добавляет одни и те же данные ниже первоначально полученных, то есть происходит полное дублирование...
В смысле? Запрос фильтрует из всех таблиц в книге таблицы, наименование которых начинается на Клиенты. Видимо, Вы выгрузили запрос в таблицу и она назвалась так же, как запрос. Просто переименуйте запрос, чтобы он не начинался с "Клиенты".
 
 
 
#7
18.12.2021 14:47:47
Цитата
написал:
А что тогда Вы называете Таблицей? То, что границами ячеек обведено?  Если левый верхний угол этой таблицы всегда на одном месте, то сделать можно.
В данном случае да, это обычный диапазон с обведенными ячейками (условная таблица на листе) и да - диапазон у нее статичный и не меняется никоим образом.
Например А16-А25 - диапазон столбцов, которые не меняют своё расположение.
Я в принципе могу из этих "таблиц" сделать умные таблицы, но хотелось бы функцию с минимальным редактированием исходных данных..
 
 
 
#8
18.12.2021 17:15:48
Добрый вечер.
Как вариант
Код
let
    Source =
        File.Contents(Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1]),
    Sheets = 
        Table.SelectRows(
            Excel.Workbook(Source,null,true),
            each [Kind]="Sheet" and 
                (try Date.From([Name]) otherwise null)<>null
        )[Data],
    Transform =
        List.Transform(Sheets, each let
            fn = 
                each List.Count(List.RemoveNulls(Record.FieldValues(_))),
            fn1 = 
                each Table.LastN(Table.RemoveLastN(_, each fn(_)=0), each fn(_)>0),
            Table = 
                Table.Transpose(fn1(Table.Transpose(fn1(_)))),
            Promoted = 
                Table.PromoteHeaders(Table.Skip(Table,1))
        in Promoted),
    Combined = 
        Table.Combine(Transform)    
in
    Combined
Изменено: Андрей Лящук - 18.12.2021 17:17:34
 
 
 
#9
18.12.2021 17:21:03
Цитата
Игорь Иванов написал:
Я в принципе могу из этих "таблиц" сделать умные таблицы, но хотелось бы функцию с минимальным редактированием исходных данных..
Вы не забывайте, что находитесь в бесплатном разделе помощи :) А не в разделе удобных для Вас решений.
Изменено: surkenny - 18.12.2021 17:21:34
 
 
 
#10
18.12.2021 17:32:39
Андрей Лящук, красиво, а главное - понятно:) Вчерашнюю жесть с веб-запросом все так и не соберусь разобрать :)
Этак мы, чуть меняя Last и First, можем любую угловую таблицу найти. Заберу Ваш код и себе функцию сделаю :)
Изменено: surkenny - 18.12.2021 17:43:09
 
 
 
#11
18.12.2021 23:57:37
Спасибо всем огромное! Оба варианта вроде бы как подходят, буду пробовать изучать матчасть в этом направлении и реализовывать)
 
 
 
#12
20.12.2021 15:16:11
И снова здравствуйте. Вот такой нюанс всплыл при решении текущей задачи..
Умные таблицы - сделаны, НО в исходниках заголовок столбца - динамически изменялся (был зависим) от ячейки в другой таблице. В умной таблице соответственно я не могу прописать формулу чтобы заголовок столбца был равен значению ячейки, .которая в другой таблице.
Если я пропишу все значения в первую строку и сделаю повышение заголовков - данные не объединятся по признаку одинаковых заголовков (они могут быть в разных частях таблицы).
Как можно сделать, чтобы столбцы были друг под другом при консолидации данных?
 
 
 
Страницы: 1
Наверх