Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/PQ ускорить обработку данных с сетевой папки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PQ ускорить обработку данных с сетевой папки (Формулы/Formulas)
PQ ускорить обработку данных с сетевой папки
Anis625 Дата: Понедельник, 30.05.2022, 11:32 | Сообщение № 1
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
Добрый день, всем участникам форума!
В просторах интернета не нашел решения одного своего вопроса, буду признателен если подскажите в какую сторону думать дальше.
Простой алгоритм (по факту больше, но проблема даже с этими 4 строками):
[vba]
Код
let
    Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Papka"]}[Content]{0}[Путь к папке]),
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательский", each Excel.Workbook([Content])),
    #"Развернутый элемент Пользовательский" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Пользовательский", {"Item", "Kind", "Hidden"}, {"Item", "Kind", "Hidden"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент Пользовательский", each ([Item] = "ФА EBITDA Группы"))
in
    #"Строки с примененным фильтром"
[/vba]
Когда путь к папке указан на сетевую папку - отработка происходит оооооооочень долго. Когда тоже количество файлов расположено в папке на локальном компьютере - не всегда моргнуть успеваю как все готово.
Пробовал менять настройки разрешения как текущей книги так и глобальные (этой сетевой папки) поочередно (общий, организационный, частный) - не помогло. Пока решение нашел переноса папки с файлами на локальный компьютер, что не совсем подходит (подходит только для отработки алгоритма). Можете подсказать решение этой задачки?
 
Ответить
СообщениеДобрый день, всем участникам форума!
В просторах интернета не нашел решения одного своего вопроса, буду признателен если подскажите в какую сторону думать дальше.
Простой алгоритм (по факту больше, но проблема даже с этими 4 строками):
[vba]
Код
let
    Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Papka"]}[Content]{0}[Путь к папке]),
    #"Добавлен пользовательский объект" = Table.AddColumn(Источник, "Пользовательский", each Excel.Workbook([Content])),
    #"Развернутый элемент Пользовательский" = Table.ExpandTableColumn(#"Добавлен пользовательский объект", "Пользовательский", {"Item", "Kind", "Hidden"}, {"Item", "Kind", "Hidden"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент Пользовательский", each ([Item] = "ФА EBITDA Группы"))
in
    #"Строки с примененным фильтром"
[/vba]
Когда путь к папке указан на сетевую папку - отработка происходит оооооооочень долго. Когда тоже количество файлов расположено в папке на локальном компьютере - не всегда моргнуть успеваю как все готово.
Пробовал менять настройки разрешения как текущей книги так и глобальные (этой сетевой папки) поочередно (общий, организационный, частный) - не помогло. Пока решение нашел переноса папки с файлами на локальный компьютер, что не совсем подходит (подходит только для отработки алгоритма). Можете подсказать решение этой задачки?

Автор - Anis625
Дата добавления - 30.05.2022 в 11:32
прохожий2019 Дата: Понедельник, 30.05.2022, 12:28 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Ну
1 - первый шаг забуферить;
2 - преобразование делать не через добавление столбца, а через трансформацию, можно сразу с фильтрацией;
3 - таблицу итоговую получать не через экспенд, а через комбайн или FromList

Это общие замечания. В любом случае судя по описанию проблема именно в скорости загрузки информации, разрешения на это никак не влияют


Сообщение отредактировал прохожий2019 - Понедельник, 30.05.2022, 12:33
 
Ответить
СообщениеНу
1 - первый шаг забуферить;
2 - преобразование делать не через добавление столбца, а через трансформацию, можно сразу с фильтрацией;
3 - таблицу итоговую получать не через экспенд, а через комбайн или FromList

Это общие замечания. В любом случае судя по описанию проблема именно в скорости загрузки информации, разрешения на это никак не влияют

Автор - прохожий2019
Дата добавления - 30.05.2022 в 12:28
Anis625 Дата: Понедельник, 30.05.2022, 14:30 | Сообщение № 3
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,
Спасибо Вам за совет. Если есть возможность подскажите где ошибаюсь (ошибку выдает)
1. С первым вроде разобрался. А после буферизации нужно на каком то этапе извлекать из буфера?
2. Со вторым пунктом делаю так:
[vba]
Код
Excel.Workbook(Table.SelectColumns( Folder.Files(Excel.CurrentWorkbook(){[Name="Papka"]}[Content]{0}[Путь к папке]), {"Content"}))
[/vba]
Ошибку выдает.
3. Пример с фильтрацией нашел тут попробую разобраться.
 
Ответить
Сообщениепрохожий2019,
Спасибо Вам за совет. Если есть возможность подскажите где ошибаюсь (ошибку выдает)
1. С первым вроде разобрался. А после буферизации нужно на каком то этапе извлекать из буфера?
2. Со вторым пунктом делаю так:
[vba]
Код
Excel.Workbook(Table.SelectColumns( Folder.Files(Excel.CurrentWorkbook(){[Name="Papka"]}[Content]{0}[Путь к папке]), {"Content"}))
[/vba]
Ошибку выдает.
3. Пример с фильтрацией нашел тут попробую разобраться.

Автор - Anis625
Дата добавления - 30.05.2022 в 14:30
прохожий2019 Дата: Понедельник, 30.05.2022, 15:03 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
нет, извлекать не надо, буферизация нужна для предотвращения повторной загрузки информации - раз уж это лимитирующее
со вторым пунктом вы делаете что-то странное - дайте пример пары файлов в архиве, напишу обработчик
а по фильтрациям и обращениям к полям рекомендую смотреть тут или спросить тут, но пример всё равно нужен, желательно с описанием необходимого конечного результата
 
Ответить
Сообщениенет, извлекать не надо, буферизация нужна для предотвращения повторной загрузки информации - раз уж это лимитирующее
со вторым пунктом вы делаете что-то странное - дайте пример пары файлов в архиве, напишу обработчик
а по фильтрациям и обращениям к полям рекомендую смотреть тут или спросить тут, но пример всё равно нужен, желательно с описанием необходимого конечного результата

Автор - прохожий2019
Дата добавления - 30.05.2022 в 15:03
Anis625 Дата: Понедельник, 30.05.2022, 15:10 | Сообщение № 5
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,
Файлы не получится приложить из-за перс.данных =) а по ссылкам получится только дома пройти т.к. на работе политикой безпасности закрыты =)
Спасибо Вам большое
 
Ответить
Сообщениепрохожий2019,
Файлы не получится приложить из-за перс.данных =) а по ссылкам получится только дома пройти т.к. на работе политикой безпасности закрыты =)
Спасибо Вам большое

Автор - Anis625
Дата добавления - 30.05.2022 в 15:10
прохожий2019 Дата: Понедельник, 30.05.2022, 15:14 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
не получится приложить из-за перс.данных =)
никто не просит перс данные. От вас требуется 2-3 файла, в которых ШАПКА как в оригинале и несколько строчек данных про бабочек/цветочки, апельсины/яблоки, главное чтобы форматы данных отображали реальность. Ну и также в общем виде - какая таблица на выходе нужна.
 
Ответить
Сообщение
не получится приложить из-за перс.данных =)
никто не просит перс данные. От вас требуется 2-3 файла, в которых ШАПКА как в оригинале и несколько строчек данных про бабочек/цветочки, апельсины/яблоки, главное чтобы форматы данных отображали реальность. Ну и также в общем виде - какая таблица на выходе нужна.

Автор - прохожий2019
Дата добавления - 30.05.2022 в 15:14
Anis625 Дата: Понедельник, 30.05.2022, 15:49 | Сообщение № 7
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,

Для примере сделал 2 файла на основании исходных. Листом в них намного больше. Работаю с листом "ФА EBITDA Группы" фильтре еще может быть выбрать параметр "Sheet". Добавляю в отдельный столбец имя файла и извлекаю название предприятия (Предприятие 1, Предприятие 2) с этого листа.

upd. удаляю 6 строк и делаю первую строку заголовком таблицы
К сообщению приложен файл: 8814304.zip (92.5 Kb)


Сообщение отредактировал Anis625 - Понедельник, 30.05.2022, 15:52
 
Ответить
Сообщениепрохожий2019,

Для примере сделал 2 файла на основании исходных. Листом в них намного больше. Работаю с листом "ФА EBITDA Группы" фильтре еще может быть выбрать параметр "Sheet". Добавляю в отдельный столбец имя файла и извлекаю название предприятия (Предприятие 1, Предприятие 2) с этого листа.

upd. удаляю 6 строк и делаю первую строку заголовком таблицы

Автор - Anis625
Дата добавления - 30.05.2022 в 15:49
Anis625 Дата: Понедельник, 30.05.2022, 15:55 | Сообщение № 8
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,
Примерно такую выходную форму
К сообщению приложен файл: 4372814.xlsm (118.7 Kb)
 
Ответить
Сообщениепрохожий2019,
Примерно такую выходную форму

Автор - Anis625
Дата добавления - 30.05.2022 в 15:55
прохожий2019 Дата: Понедельник, 30.05.2022, 16:28 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
я бы что-то такое написал
[vba]
Код
let
    from = List.Buffer(Folder.Files("C:\путь к папке")[Content]),
    tr = List.Transform(from,TransformFile),
    to = Table.Combine(tr)
in
    to
[/vba]
функция трансформации:
[vba]
Код
(x)=>[a=Excel.Workbook(x),
            b=a{[Name="ФА EBITDA Группы"]}[Data],
            c = b{2}[Column3],
            d = Table.Skip(b,6),
            e = Table.PromoteHeaders(d),
            f = Table.SelectRows(e,(x)=>x[#"План/#(lf)Факт"]<>null),
            g = Table.AddColumn(f,"Предприятие",(x)=>c),
            h = List.Select(Table.ColumnNames(g),(x)=>not Text.Contains(x,"Column")),
            k = Table.SelectColumns(g,h)][k]
[/vba]
К сообщению приложен файл: 0634512.xlsm (133.3 Kb)
 
Ответить
Сообщениея бы что-то такое написал
[vba]
Код
let
    from = List.Buffer(Folder.Files("C:\путь к папке")[Content]),
    tr = List.Transform(from,TransformFile),
    to = Table.Combine(tr)
in
    to
[/vba]
функция трансформации:
[vba]
Код
(x)=>[a=Excel.Workbook(x),
            b=a{[Name="ФА EBITDA Группы"]}[Data],
            c = b{2}[Column3],
            d = Table.Skip(b,6),
            e = Table.PromoteHeaders(d),
            f = Table.SelectRows(e,(x)=>x[#"План/#(lf)Факт"]<>null),
            g = Table.AddColumn(f,"Предприятие",(x)=>c),
            h = List.Select(Table.ColumnNames(g),(x)=>not Text.Contains(x,"Column")),
            k = Table.SelectColumns(g,h)][k]
[/vba]

Автор - прохожий2019
Дата добавления - 30.05.2022 в 16:28
Anis625 Дата: Понедельник, 30.05.2022, 16:48 | Сообщение № 10
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,
Ого. Надо изучить как вы это сделали. Попробовал поменять путь ошибку правда выдал, но это скорее всего из-за того что некоторые файлы не содержат такой лист. Когда поэтапно обрабатываю, то получается выявить такие ошибки
 
Ответить
Сообщениепрохожий2019,
Ого. Надо изучить как вы это сделали. Попробовал поменять путь ошибку правда выдал, но это скорее всего из-за того что некоторые файлы не содержат такой лист. Когда поэтапно обрабатываю, то получается выявить такие ошибки

Автор - Anis625
Дата добавления - 30.05.2022 в 16:48
Anis625 Дата: Понедельник, 30.05.2022, 16:56 | Сообщение № 11
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
На одном файле спотыкается. на каком не выявить. С транфсормацией с одной стороны удобно но при условии что все файлы правильные. Первый блок для изучения взял на проработку. Спасибо Вам огромное
 
Ответить
СообщениеНа одном файле спотыкается. на каком не выявить. С транфсормацией с одной стороны удобно но при условии что все файлы правильные. Первый блок для изучения взял на проработку. Спасибо Вам огромное

Автор - Anis625
Дата добавления - 30.05.2022 в 16:56
прохожий2019 Дата: Понедельник, 30.05.2022, 17:23 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
вот так пробуйте (добавил исключение некорректных файлов):
[vba]
Код
let
    from = List.Buffer(Folder.Files("C:\путь")[Content]),
    tr = List.Transform(from,TransformFile),
    filtr = List.RemoveNulls(tr),
    to = Table.Combine(filtr)
in
    to
[/vba]
[vba]
Код
(x)=>[a=Excel.Workbook(x),
            b=a{[Name="ФА EBITDA Группы"]}?[Data]?,
            c = b{2}[Column3],
            d = Table.Skip(b,6),
            e = Table.PromoteHeaders(d),
            f = Table.SelectRows(e,(x)=>x[#"План/#(lf)Факт"]<>null),
            g = Table.AddColumn(f,"Предприятие",(x)=>c),
            h = List.Select(Table.ColumnNames(g),(x)=>not Text.Contains(x,"Column")),
            k = Table.SelectColumns(g,{List.Last(h)}&List.RemoveLastN(h,1)),
            l = if b <> null then k else null][l]
[/vba] главная идея индивидуальной обработки - так обычно просто быстрее, оперативу не шкалит
К сообщению приложен файл: 123.xlsm (133.8 Kb)


Сообщение отредактировал прохожий2019 - Понедельник, 30.05.2022, 17:32
 
Ответить
Сообщениевот так пробуйте (добавил исключение некорректных файлов):
[vba]
Код
let
    from = List.Buffer(Folder.Files("C:\путь")[Content]),
    tr = List.Transform(from,TransformFile),
    filtr = List.RemoveNulls(tr),
    to = Table.Combine(filtr)
in
    to
[/vba]
[vba]
Код
(x)=>[a=Excel.Workbook(x),
            b=a{[Name="ФА EBITDA Группы"]}?[Data]?,
            c = b{2}[Column3],
            d = Table.Skip(b,6),
            e = Table.PromoteHeaders(d),
            f = Table.SelectRows(e,(x)=>x[#"План/#(lf)Факт"]<>null),
            g = Table.AddColumn(f,"Предприятие",(x)=>c),
            h = List.Select(Table.ColumnNames(g),(x)=>not Text.Contains(x,"Column")),
            k = Table.SelectColumns(g,{List.Last(h)}&List.RemoveLastN(h,1)),
            l = if b <> null then k else null][l]
[/vba] главная идея индивидуальной обработки - так обычно просто быстрее, оперативу не шкалит

Автор - прохожий2019
Дата добавления - 30.05.2022 в 17:23
Anis625 Дата: Вторник, 31.05.2022, 09:23 | Сообщение № 13
Группа: Заблокированные
Ранг: Ветеран
Сообщений: 674
Репутация: 31 ±
Замечаний: 20% ±

Excel 2013
прохожий2019,
Протестировали PLEXом скорость обработки моим способом (как научился пока) и ваш способ => ваш способ чуть больше чем в 2 раза быстрее отрабатывает 47 файлов (136,8с / 63,5с).
Пока конечно Космос для меня некоторые строки и в целом тема Table.Combine. Буду разбираться. Спасибо Вам за новые знания!!!


Сообщение отредактировал Anis625 - Вторник, 31.05.2022, 09:59
 
Ответить
Сообщениепрохожий2019,
Протестировали PLEXом скорость обработки моим способом (как научился пока) и ваш способ => ваш способ чуть больше чем в 2 раза быстрее отрабатывает 47 файлов (136,8с / 63,5с).
Пока конечно Космос для меня некоторые строки и в целом тема Table.Combine. Буду разбираться. Спасибо Вам за новые знания!!!

Автор - Anis625
Дата добавления - 31.05.2022 в 09:23
прохожий2019 Дата: Вторник, 31.05.2022, 14:22 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 1241
Репутация: 317 ±
Замечаний: 0% ±

365 Beta Channel
Протестировали PLEXом
на всякий случай - Мерка - бесплатно и с открытым кодом )))
Пока конечно Космос
выше давал ссылку на канал - там многое из применённого разобрано


Сообщение отредактировал прохожий2019 - Вторник, 31.05.2022, 14:26
 
Ответить
Сообщение
Протестировали PLEXом
на всякий случай - Мерка - бесплатно и с открытым кодом )))
Пока конечно Космос
выше давал ссылку на канал - там многое из применённого разобрано

Автор - прохожий2019
Дата добавления - 31.05.2022 в 14:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » PQ ускорить обработку данных с сетевой папки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!