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

 

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

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

Excel 2013
Добрый день, всем участникам форума!
В просторах интернета не нашел решения одного своего вопроса, буду признателен если подскажите в какую сторону думать дальше.
Простой алгоритм (по факту больше, но проблема даже с этими 4 строками):

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
    #"Строки с примененным фильтром"


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

Автор - Anis625
Дата добавления - 30.05.2022 в 11:32
прохожий2019 Дата: Понедельник, 30.05.2022, 12:28 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 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. Со вторым пунктом делаю так:

Excel.Workbook(Table.SelectColumns( Folder.Files(Excel.CurrentWorkbook(){[Name="Papka"]}[Content]{0}[Путь к папке]), {"Content"}))


Ошибку выдает.
3. Пример с фильтрацией нашел тут попробую разобраться.
 
Ответить
Сообщениепрохожий2019,
Спасибо Вам за совет. Если есть возможность подскажите где ошибаюсь (ошибку выдает)
1. С первым вроде разобрался. А после буферизации нужно на каком то этапе извлекать из буфера?
2. Со вторым пунктом делаю так:
[vba]
Excel.Workbook(Тable.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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
Цитата Anis625, 30.05.2022 в 15:10, в сообщении № 5 ( писал(а)):
не получится приложить из-за перс.данных =)
никто не просит перс данные. От вас требуется 2-3 файла, в которых ШАПКА как в оригинале и несколько строчек данных про бабочек/цветочки, апельсины/яблоки, главное чтобы форматы данных отображали реальность. Ну и также в общем виде - какая таблица на выходе нужна.
 
Ответить
Сообщение
Цитата Anis625, 30.05.2022 в 15:10, в сообщении № 5 ( писал(а)):
не получится приложить из-за перс.данных =)
никто не просит перс данные. От вас требуется 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
я бы что-то такое написал

let
    from = List.Buffer(Folder.Files("C:\путь к папке")[Content]),
    tr = List.Transform(from,TransformFile),
    to = Table.Combine(tr)
in
    to


функция трансформации:

(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]

К сообщению приложен файл: 0634512.xlsm (133.3 Kb)
 
Ответить
Сообщениея бы что-то такое написал
[vba]
let    from = List.Buffer(Folder.Files("C:\путь к папке")[Content]);    tr = List.Transform(from;TransformFile);    to = Тable.Combine(tr)in    to
[/vba]
функция трансформации:
[vba]
(x)=>[a=Excel.Workbook(x);            b=a{[Name="ФА EBITDA Группы"]}[Data];            c = b{2}[Column3];            d = Тable.Skip(b;6);            e = Тable.PromoteHeaders(d);            f = Тable.SelectRows(e;(x)=>x[null"План/#(lf)Факт"]<>g);            f = Тable.AddColumn(x;"Предприятие";(c)=>h);            g = List.Select(Тable.ColumnЧames(x);(not)=>x Тext.Contains(k;"Column"));            g = Тable.SelectColumns(h;k)][undefined]
[/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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
вот так пробуйте (добавил исключение некорректных файлов):

let
    from = List.Buffer(Folder.Files("C:\путь")[Content]),
    tr = List.Transform(from,TransformFile),
    filtr = List.RemoveNulls(tr),
    to = Table.Combine(filtr)
in
    to


(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]

главная идея индивидуальной обработки - так обычно просто быстрее, оперативу не шкалит
К сообщению приложен файл: 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.RemoveЧulls(tr);    to = Тable.Combine(filtr)in    to
[/vba]
[vba]
(x)=>[a=Excel.Workbook(x);            b=a{[Name="ФА EBITDA Группы"]}?[Data]?;            c = b{2}[Column3];            d = Тable.Skip(b;6);            e = Тable.PromoteHeaders(d);            f = Тable.SelectRows(e;(x)=>x[null"План/#(lf)Факт"]<>g);            f = Тable.AddColumn(x;"Предприятие";(c)=>h);            g = List.Select(Тable.ColumnЧames(x);(not)=>x Тext.Contains(k;"Column"));            g = Тable.SelectColumns(h;{List.Last(h)}&List.RemoveLastЧ(l;1));            if = b null <> then k else null l][undefined]
[/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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1398
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
Цитата Anis625, 31.05.2022 в 09:23, в сообщении № 13 ( писал(а)):
Протестировали PLEXом
на всякий случай - Мерка - бесплатно и с открытым кодом )))
Цитата Anis625, 31.05.2022 в 09:23, в сообщении № 13 ( писал(а)):
Пока конечно Космос
выше давал ссылку на канал - там многое из применённого разобрано


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

Автор - прохожий2019
Дата добавления - 31.05.2022 в 14:22
  • Страница 1 из 1
  • 1
Поиск:

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