Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Преобразовать выгрузку из 1С в удобный вид.
 
#1
26.12.2019 16:07:44
Добрый день.
Помогите, пожалуйста, с оптимизацией процедуры, работает очень долго, если в таблице хотя бы 50 тыс. строк. Более 200 тыс. эксель может умереть.
Приложил файл-пример. На первом листе исходные данные, на втором обработанные.
1) Сначала я снимаю объединение ячеек строчкой Cells.UnMerge. Но в большом файле это занимает больше 15 минут. Как можно ускорить данную процедуру?
2) Далее делаю следующее:
Код
Columns("A:A").Copy
Columns("B:B").PasteSpecial

Rows("4:4").AutoFilter
Range("A6").FormulaR1C1 = "=R[-1]C"

iLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("A4:I" & iLastRow).AutoFilter Field:=1, Operator:=xlFilterNoFill
Range("A5:A" & iLastRow).FillDown

ActiveSheet.ShowAllData

Range("A5:A" & iLastRow).Copy
Range("A5:A" & iLastRow).PasteSpecial xlPasteValues

Range("A4:I" & iLastRow).AutoFilter Field:=1, Criteria1:=RGB(238, _
        246, 239), Operator:=xlFilterCellColor
Range("A5:A" & iLastRow).Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete

ActiveSheet.ShowAllData

Мне необходимо получить Контрагента с его договорами в одной строке, а не контрагент сверху от договора как в исходных данных.

Копирую первый столбец во второй, ставлю в ячейку с договором формулу получить значение из верхней ячейки, фильтром по цвету оставляю только договора (строки с контрагентом отличаются по цвету при выгрузке) и протягиваю формулу вниз. Вот это действие протягивание формулы также занимает огромное время, можно ли какую то альтернативу придумать данному действию, чтобы быстрей обработалась таблица? Возможно через массив.

3) После этого фильтром оставляю строки только "зеленые" с контрагентами и удаляю их, т.к. они лишние становятся. Также очень затратный по времени процесс. Как ускорить?

Буду рад любым мыслям. Спасибо.
 
 
 
#2
26.12.2019 16:19:04
В конечном файле обязательно сохранять форматирование?
А вообще нужно менять алгоритм и делать на массивах.
 
 
 
#3
26.12.2019 16:26:29
Михаил Витальевич С.,нет, форматирование конечное не важно. Таблица используется только ВПР-ить из нее данные.
Можно какой-нибудь примерчик? Хотя бы последовательность действий на пальцах. С массивами никогда не работал, только слышал, что они быстрей обрабатываются.
 
 
 
#4
26.12.2019 16:30:35
Если ни кто не покажет - чуть позже.

зы. Вы пишите - 200 000 строк, столбцов сколько?
Комп какой? (сколько памяти?)
Изменено: Михаил Витальевич С. - 26.12.2019 16:32:02
 
 
 
#5
26.12.2019 16:34:42
Если не усложнять на массивы, то для ВПР (хотя не вполне понимаю как/что будете вприть?) данные можно вытянуть на ТРЕТИЙ лист таким кодом:
Код
Sub tt()
    Dim i&, ii&, t$
    Application.ScreenUpdating = False
    For i = 10 To Cells(Rows.Count, 1).End(xlUp).Row - 1
        If Cells(i, 1).Interior.ColorIndex = 2 Then
            t = Cells(i, 1)
        Else
            ii = ii + 1
            Sheets(3).Cells(ii, 1) = t
            Range(Cells(i, 1), Cells(i, 9)).Copy Sheets(3).Cells(ii, 2)
        End If
    Next
    Application.ScreenUpdating = True
End Sub
Изменено: Hugo - 26.12.2019 17:01:58
 
 
 
#6
26.12.2019 16:38:23
Михаил Витальевич С., столбцов сколько в исходном файле. Но по факту нужны только 4: контрагент, договор и последние два дебет и кредит.
В системках показывает 96GB оперативки :) работа идет на удаленном сервере.
 
 
 
#7
26.12.2019 17:11:47
Цитата
whateverlover написал:
Но по факту нужны только 4: контрагент, договор и последние два дебет и кредит.
если структура файла постоянна, и результат - только 4 столбца, то под этот вариант и делаю.
 
 
 
#8
26.12.2019 17:18:56
Счас посмотрел внимательно.... В первом столбце, кроме "Договор (прямой)..." в этих ячейках могут быть другие данные?
 
 
 
#9
26.12.2019 17:30:31
Цитата
Hugo написал:
Если не усложнять на массивы, то для ВПР (хотя не вполне понимаю как/что будете вприть?) данные можно вытянуть на ТРЕТИЙ лист таким кодом:
Попробовал запустить на файле в 400 тыс. строк - за пол часа не выполнил) комп повис. На маленьком файле все работает, спасибо. Но на большом не вышло.
 
 
 
#10
26.12.2019 17:33:10
Цитата
Михаил Витальевич С. написал:
Счас посмотрел внимательно.... В первом столбце, кроме "Договор (прямой)..." в этих ячейках могут быть другие данные?
Нет, там всегда только такие данные. В последствии я вообще убираю "Договор (прямо)" и "от такого-то числа", оставляя только сам номер договора.
 
 
 
#11
26.12.2019 17:34:15
Конечно если эти 400000 нужно обрабатывать каждый день по несколько раз - есть смысл код переписывать на массивы, но тогда как критерий не получится привязка к цвету фона.
Если же работа менее регулярная - возможно достаточно в цикл на каждую сотую строку добавить doevents и вывод в статусбар индикации процесса.
 
 
 
#12
26.12.2019 17:38:53
Hugo, каждый день, да. Обрабатывал каждый день по 5 таких примерно на 20-30 тыс. строк, уходило по 5 минут на каждый, это терпимо. Сейчас дургие организации добавились, файлы разрослись и по пол часа на каждый файл это уже невозможное что-то) да еще и не факт что обработается вообще.
Код
Hugo написал:
Если не усложнять на массивы, то для ВПР (хотя не вполне понимаю как/что будете вприть?)
Это таблица с договорами по которым есть задолженность. Можно в другую таблицу с контаргентами подтянуть задолженность по контрагенту из первого столбца или задолженность по конкретному договору из 2 столбца или если поступил платеж неопознанный на некую сумму, по третьему столбцу найти все варианты догоров с задолженностью на эту сумму (сильно сужает круг поиска).
Изменено: whateverlover - 26.12.2019 17:47:48
 
 
 
#13
26.12.2019 18:07:49
Если заранее известна задача (например подтянуть дебет/кредит к контрагенту/договору) то я бы не возился с преобразованиями таблицы, а сразу из исходной собирал данные в словарь, и затем сразу из этого словаря выгружал результат в другую таблицу.
Например из этой можно сделать два словаря - один с коллекцией договоров на контрагента, второй с данными по контрагент+договор. Или ещё один с ключём договор (если договоры не дублируются).
Из этих словарей затем можно сразу получить дебет/кредит к любому сочетанию контрагент/договор.
Если нужно искать по сумме - то можно и такой словарь заодно собрать.
 
 
 
#14
26.12.2019 18:21:56
Примерно так:
Код
Sub TableProcessing()
    Dim i&, Arr(), myArr(), k&, Nam$
    With Sheets(1)
        Arr = .UsedRange.Value
    End With
    ReDim myArr(1 To UBound(Arr) - 9, 1 To 4)
    k = 1
    For i = 10 To UBound(Arr) - 1
        If Not Arr(i, 1) Like "Договор*" Then
            Nam = Arr(i, 1)
        Else
            myArr(k, 1) = Nam
            myArr(k, 2) = Arr(i, 1)
            myArr(k, 3) = Arr(i, 7)
            myArr(k, 4) = Arr(i, 8)
            k = k + 1
        End If
    Next
    With Sheets(3)
        .Range("A1").Resize(k, 4) = myArr
    End With
End Sub


зы.  Сделал без шапки и строки итогов. Если они нужны - легко добавить.
зы.зы
и да, на 400 000 строк нужно отключать обновление экрана, большой массив будет долго вставляться.
Изменено: Михаил Витальевич С. - 26.12.2019 18:27:25
 
 
 
#15
26.12.2019 18:41:38
Во вложении вариант на Power Query. В параметрах запросов измените путь к папке где лежит файл и имя самого файла.

В принципе если файлов много, то можно одним запросом обработать вагон файлов и собрать их в одну таблицу, и подтянуть данные из других файлов, в общем сделать вообще все расчеты. Но по условию задачи такого не требовалось.
Для начала проверьте быстродействие того что получилось.
Вот горшок пустой, он предмет простой...
 
 
 
#16
26.12.2019 19:46:00
Цитата
Михаил Витальевич С. написал:
и да, на 400 000 строк нужно отключать обновление экрана, большой массив будет долго вставляться.
Неправда ваша. Обновление экрана на время выгрузки в данном случае никак не влияет.
Без отключения обновления экрана сначала выгружается массив, а после, однократно, обновляется экран. При отключении обновления происходит абсолютно то же.
Вот если выгружать циклом поячеечно, тады ой.
 
 
 
#17
26.12.2019 20:23:21
RAN,  на практике проверено, с выключением экрана массив выгружается быстрее.
 
 
 
#18
26.12.2019 20:31:45
Михаил Витальевич С.,  я замерял - у меня получалось что отключение экрана замедляло процесс, конечно на долю секунды но замедляло.
 
 
 
#19
26.12.2019 20:53:07
Может в новых Excel это и так, но в 2010 массив 81190 х 4 выгружается
без отключения
2,601563
2,601563
2,59375
2,601563
2,601563
2,59375
с отключением
2,59375
2,617188
2,601563
2,625
2,609375
2,609375
 
 
 
#20
26.12.2019 22:33:16
RAN, Hugo, спорить не буду, потому как вспомнил...
Примерно год назад я делал файл, на выходе примерно 200-250 тыс строк и около 45 столбцов.
Макрос работал около минуты, при этом массив формировался около 15 сек, а остальное время выгрузка. ЕМНИП - отключение экрана мало чем помогло....
 
 
 
#21
26.12.2019 23:00:14
счас, ради интереса, провел такой тест:
Код
Sub test()
    Dim T, CEL As Range
    T = Timer
    Application.ScreenUpdating = False
    For Each CEL In Range("A:A")
        CEL = CEL.Offset(0, 1).Interior.Color
    Next
    Application.ScreenUpdating = True
    Debug.Print Timer - T
End Sub
на моем компе он отработал примерно за 40 сек. Это я к тому, что вполне для массивов можно делать идентификатор строки. Думаю, что по отступу будет работать не дольше.
 
 
 
#22
26.12.2019 23:13:09
Всем огромное спасибо!
Завтра на работе буду тестить, отпишусь о результатах)
 
 
 
#23
26.12.2019 23:15:05
Результат можно писать не в ячейку, а сразу в массив - думаю на треть должно быть на треть быстрее.
 
 
 
#24
27.12.2019 14:06:52
Цитата
Михаил Витальевич С. написал:
Примерно так:
Вау! Затестил на файле в 280 тыс. строк - ну секунды 3 заняло) Огромное спасибо!
А чисто в теории можно например из одной книги так собрать в массив, потом никуда не выгружая пока что его, из другой книги добавить в этот же массив (и так в зависимости сколько есть книг) и потом выгрузить весь этот массив на лист? Это просто на будущее, так этот варинт за 3 секунд это уже предел моих мечтаний был))
Цитата
PooHkrd написал:
Во вложении вариант на Power Query
Интересно было бы тоже затестировать. Пока этой надстройки нет и не факт, что дадут ее скачать. Но спасибо, это уже тогда дома на своем ПК попробую)
Цитата
Hugo написал:
а сразу из исходной собирал данные в словарь
спасибо, почитаю про словари)
 
 
 
#25
27.12.2019 19:17:52
Цитата
whateverlover написал:
Затестил на файле в 280 тыс. строк - ну секунды 3 заняло
крутой у вас сервер...
На моем компе такой объём обрабатывался бы примерно 25-30 сек; из них 4-5 сек на формирование массива, остальное время - выгрузка на лист.
Цитата
whateverlover написал:
А чисто в теории можно например из одной книги так собрать в массив, потом никуда не выгружая пока что его, из другой книги добавить в этот же массив
Чисто в теории можно. Но имейте ввиду, что RiDim результирующего массива затруднителен (в моем случае он заранее больше, чем нужно); проще выгрузить его на лист, а далее выгрузить ниже.
 
 
 
#26
27.12.2019 19:25:39
Цитата
whateverlover написал:
А чисто в теории можно например из одной книги так собрать в массив, потом никуда не выгружая пока что его, из другой книги добавить в этот же массив (и так в зависимости сколько есть книг) и потом выгрузить весь этот массив на лист?
- зачем? Выгрузить последовательно даже пару десятков массивов по времени не намного дольше, но зато точно более щадяще по памяти.
А без редимов можно обойтись если создавать массив массивов, ну или сразу всё это дело обрабатывать и паковать в словари...
 
 
 
#27
27.12.2019 20:06:13
Цитата
Hugo написал:
если создавать массив массивов, ну или сразу всё это дело обрабатывать и паковать в словари...
В данном случае в этом нет необходимости.
Цитата
Hugo написал:
Выгрузить последовательно даже пару десятков массивов по времени не намного дольше, но зато точно более щадяще по памяти.
я не проверял, но у меня такое ощущение, что время выгрузки увеличивается не прямопропорционально размеру массива, а немного больше (по экспоненте)). ДЛя памяти - точно щадяще; не у всех по 96 гигов оперативки.
Изменено: Михаил Витальевич С. - 27.12.2019 20:12:12
 
 
 
#28
27.12.2019 20:27:03
Ну и я думаю не у всех эксель сможет использовать все эти гиги под массивы. х32 точно не сможет, как там с х64 я не знаю.
 
 
 
Страницы: 1
Наверх