Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Обработка больших массивов содержащих формулы, оптимизировать работу кода
 
#1
08.11.2016 20:25:36
Добрый день, столкнулся с проблемой при обработке больших таблиц. Есть БД, данные их которой выгружаются в EXCEL, на листе формируется таблица состоящая из 60 тысяч строк (иногда меньше - до 30 тысяч) и содержащая 20-25 столбцов. В этой таблице необходимо в смежных сроках сравнивать ячейки и если эти ячейки совпадают, то эти строки сохранять, остальные удалять!
Написал 2 макроса, все работает, только если строк в таблице не более 10-15 тысяч, если больше - все ужасно тормозит, макрос выполняется час и более.Можно-ли это как-то решить.
P.S. все что может тормозить процесс из таблицы убрано имхо как: очищены форматы во всей таблице, убраны все возможные автофильтры, файл сохраняется как двоичная книга.
Изменено: Влад Турбин - 09.11.2016 20:57:33
 
 
 
#2
08.11.2016 21:02:57
Цитата
Гигантские таблицы
Те, кто имеет дело с миллионами строк сейчас улыбнулись.
Изменено: kalbasiatka - 09.11.2016 20:55:16
 
 
 
#3
08.11.2016 21:04:05
Макроса в файле , не увидел. В чем проблема, посмотреть и проанализировать можно? Нет материала для анализа - нет и проблем. В вашем файле, нет ни чего, кроме данных.
Изменено: gling - 08.11.2016 21:04:55
 
 
 
#4
08.11.2016 21:35:24
Файл с макросом вложен!
 
 
 
#5
09.11.2016 11:36:33
Вообще, по хорошему, наилучшим решением было бы изначально сравнивать строки в самой выгрузке из БД. а только потом обработанные данные отправлять на лист excel.
Насколько понял, сравниваются все данные, кроме первой колонки?
Код
Sub Первый()
    
    Dim temp_arr(), fill_arr()
    Dim stroki As New Collection
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    massiv = Range("A7:T" & lrow)
    
    ReDim temp_arr(1 To lrow - 6, 1 To 2)
    For i = lrow - 6 To 1 Step -1
        g = Join(Application.Transpose(Application.Transpose(Application.Index(massiv, i, 0))), "|")
        g = Right(g, Len(g) - InStr(1, g, "|"))
        temp_arr(i, 1) = i
        temp_arr(i, 2) = g
    Next
    i = 1
    While i < lrow - 6
        If temp_arr(i, 2) = temp_arr(i + 1, 2) Then
            On Error Resume Next
            stroki.Add (i), CStr(i)
            stroki.Add (i + 1), CStr(i + 1)
        End If
        i = i + 1
    Wend

    ReDim fill_arr(1 To stroki.Count, 1 To 20)
    For i = 1 To stroki.Count
        For j = 1 To 20
           fill_arr(i, j) = massiv(stroki(i), j)
        Next
    Next
    Range("A7:T" & lrow).ClearContents
    Range("A7:T" & 6 + stroki.Count) = fill_arr
    

End Sub
Изменено: heso - 09.11.2016 20:48:37
 
 
 
#6
09.11.2016 18:39:02
Вариант.
Изменено: Мотя - 09.11.2016 21:02:11
 
 
 
#7
09.11.2016 20:55:47
Нет, сравниваются только значения, содержащиеся в ячейках находящихся в столбцах С, E, H, T.
Изменено: Влад Турбин - 09.11.2016 20:56:36 (Грамматическая ошибка)
 
 
 
#8
09.11.2016 21:06:38
Цитата
kalbasiatka написал: Те, кто имеет дело с миллионами строк сейчас улыбнулись
Ну не с миллионами! А что, в Excel есть возможность создать таблицу с 1 048 577 строк? Я всегда думал, что количество строк ограничено!
 
 
 
#9
09.11.2016 21:15:30
Цитата
Влад Турбин написал: А что, в Excel есть возможность создать таблицу с 1 048 577 строк?
"Встаньте" на любом листе в Excel на "перекрестие бордюров" и увидите мощь Excel 2010!
 
 
 
#10
09.11.2016 21:22:02
Мотя, там о последней циферке речь :)

Влад Турбин, миллионы строк можно обрабатывать, получив массив из других приложений, и выгружать в Excel или на разные листы, или в несколько столбцов. А может, после обработки миллионов строк останется сотня...
 
 
 
#11
09.11.2016 21:22:46
Уважаемый vikttur!
Тема название которой Вы поменяли в первый раз более соответствовало моим  критериям. Мне действительно необходимо обрабатывать большие массивы  содержащие формулы и при этом ускорить обработку массива макросом, чем  то название, которое тема имеет сейчас "При несовпадении данных в  смежных строках эти строки удалять". Мне необходимо из таблицы, в одном  случае, получать выборку по совпадающим данным, а в другом случае  удалять одну из строк с совпадающими данными.
 
 
 
#12
09.11.2016 21:24:05
Я Вас просил несколько раз САМОСТОЯТЕЛЬНО придумать нормальное название темы. Вы считаете, что это моя блажь?

Предложите сейчас. Поменяю.
 
 
 
#13
09.11.2016 21:31:42
Уважаемый Влад Турбин!
Ваше описание алгоритма: это просто - набор слов.
 
 
 
#14
10.11.2016 09:28:32
Я уже предлагал название "Обработка больших массивов содержащих формулы"
Основное условие: Ускорить работу макроса при обработке больших массивов содержащих формулы в смежных ячейках не смежных столбцах!
Это необходимо для разностороннего анализа данных в таблице, при чем, анализ может проводится по разным диапазонам данных.
Изначально дано: Массив данных в котором ячейки, содержащиеся в столбцах С, E, H, T проверяются на совпадение значений в смежных ячейках с помощью функции "ЕСЛИ(И" и "ЕСЛИ(ИЛИ", совпадают-ли значения в ячейке С8 с ячейкой С9, С9 сравнивается с С10, Е8 с Е9, Е9 с Е10 и т.д. При этом, в массиве не обязательно, при совпадении значений С9 и С10 совпадут Н9 и Н10, т.е. условия по котором строка сохраняется - это полное совпадение значений в 4 смежных ячейках, расположенных в НЕСМЕЖНЫХ столбцах С, E, H, T.
Строки, в которых это совпадение 100% сохраняются, остальные удаляются.
В зависимости от ЗАДАЧИ поставленной перед пользователем, который проводит анализ с помощью функций "ЕСЛИ(И" и "ЕСЛИ(ИЛИ", совпадают-ли значения в смежных ячейках не смежных столбцов, (это не обязательно могут быть столбцы С, E, H, T) макрос должен из большого массива данных удалять только те строки, определенным ячейкам в которых, с помощью функций  "ЕСЛИ(И" и "ЕСЛИ(ИЛИ" присвоены значения "УДАЛИТЬ" или "ОСТАВИТЬ"
 
 
 
#15
10.11.2016 09:31:00
Цитата
vikttur написал: миллионы строк можно обрабатывать, получив массив из других приложений, и выгружать в Excel или на разные листы, или в несколько столбцов
К сожалению используемая ERP не позволяет выгружать данные так, как Вы предлагаете.
 
 
 
#16
10.11.2016 09:41:45
И последнее! Проводящийся анализ выгружаемых из ERP данных необходим для определения возникающей системной ошибки, т.е. записи в выгружаемой таблице изначально не должны дублироваться. Дублирующиеся записи - это ошибка, и тут необходимо выяснить является-ли это ошибка специализированного ПО при обработке и выгрузке данных или это ошибка железа этой ERP (считыватели, стойки, шлагбаумы)
 
 
 
#17
10.11.2016 11:27:50
Посмотрите пример во вложении. Откройте файл и выполните макрос "Main".
Проверьте на своих "больших" файлах. Обработка 60000 строк должна занять 3...5 сек.
Все ли так, как требовалось?
Чем шире угол зрения, тем он тупее.
 
 
 
#18
10.11.2016 12:14:18
Цитата
Влад Турбин написал: используемая ERP не позволяет выгружать данные так, как Вы предлагаете.
Я Вам ничего не предлагал, это был ответ на вопрос о миллионах строк.
 
 
 
#19
10.11.2016 13:44:44
Кнопка цитирования не для ответа [МОДЕРАТОР]

Уважаемые форумчане! Мне не нужно сравнивать значения (это выполняется с помощью функций "И(ИЛИ"), мне нужно только:
- или оптимизировать код чтобы при обработке массивов содержащих формулы макрос выполнялся быстрее;
- или сказать, что это невозможно.
 
 
 
#20
10.11.2016 13:49:12
Чтоб работало быстрее:
- отключите перерасчет формул
- не используйте Select
- минимальное использование Cells & Range (сделать все на массивах) и/ил словарях и/или коллекциях
Изменено: Ivan.kh - 10.11.2016 13:49:33
 
 
 
#21
11.11.2016 05:23:14
Цитата
Влад Турбин написал:
Мне не нужно сравнивать значения (это выполняется с помощью функций "И(ИЛИ"), мне нужно только...
...оптимизировать код чтобы при обработке массивов содержащих формулы макрос выполнялся быстрее;
Влад Турбин, давайте еще раз, но по-медленнее.
1. Если я Вас правильно понял, то макрос должен удалить строки по определенным условиям. Если так, то для этого макрос, как минимум, должен проверить эти условия. В данном случае, сравнить определенные ячейки в строках.
2. Если Вы используете макрос, то зачем применять на листе "кучу" формул (функций)? Удалите все формулы и поручите все вычисления макросу. Это значительно "облегчит" файл и ускорит работу макроса.
3. Приложенный Вами пример исходного файла вообще не содержит формул. Для корректной отладки кода макроса, прикрепите пример из 10...20 строк с Вашими формулами.
4. Посмотрите приложенный файл с примером макроса. Я его протестировал на 60000 строк (просто много раз скопировал и добавил имеющиеся в Вашем примере строки с данными). Время работы макроса составило единицы секунд. Перед обработкой макрос отключает пересчет листа, а по окончании работы - включает. Как это скажется на время выполнения при реальных Ваших данных (с формулами), я не могу проверить.
Чем шире угол зрения, тем он тупее.
 
 
 
#22
11.11.2016 11:01:41
Помедленнее:
Дано:
1.БД созданная с помощью специального ПО из которой выгружаются (импортируются) данные в файл Эксель, данные можно выгрузить только на один лист Экселя.
2. Или при выгрузке данных из БД в Эксель, или при записи данных в БД специальном ПО возникает ошибка - некоторые записи дублируются но не полностью т.е. нельзя просто взять и сравнить строки.
3. Выгруженные данные имеют форму таблицы, состоящей из 30 000 - 60 000 строк и 29 столбцов (в файле примера таблица не полная, столбцов меньше)
Требуется:
Провести анализ значений по разным критериям, таким как: совпадениям значений в определенных смежных ячеек или не совпадений значений в одних ячейках и совпадений в других. Т.к. пока не понятно почему одни записи дублируются, а другие нет, невозможно точно сказать, что макрос должен сравнивать значения в ячейках расположенных, допустим, в столбцах А В С на совпадение, а в ячейках столбцов D E F на несовпадение. Т.е. после обработки таблицы с помощью функций "И(ИЛИ", полученный результат необходимо сравнить по другим критериям (опять же на совпадение или не совпадение). Следовательно я не могу точно определить (пока не получу результат первого сравнения) ячейки в каких столбцах я должен сравнивать. Поэтому, для сравнения значений в ячейках используется формула. Из-за того, что таблица имеет большое количество строк, ковыряться в ней ища строки, которые отобраны по заданным критериям используя перемещение с помощью движка или курсора нереально! Поэтому макросом строки удаляются. Пытался использовать и условное форматирование+автофильтр (при этом скрываются подходящие или не подходящие по условиям строки) но строки-то скрываются, а при изменении функции сравнения или вставке функций "И(ИЛИ" в другие ячейки обрабатывается вся таблица целиком включая и скрытые ячейки. Т.е. мне надо обработать таблицу, получить результат и обработать этот результат в зависимости от того что я увижу.
ПОЭТОМУ мне просто нужен макрос обрабатывающий большие массивы с формулами т.к. я не могу до получения результатов от первого сравнения сказать что мне необходимо сравнивать дальше!
 
 
 
#23
11.11.2016 20:07:55
Цитата
Влад Турбин написал: Поэтому макросом строки удаляются.
Полагаю, здесь "зарыта" Ваша проблема - в удалении строк...
Измените алгоритм Вашего макроса: не следует удалять строки, нужно просто построить "протокол" контроля с выводом информации из строк не в режиме "значения", а в режиме "формулы".
Этот протокол Вы сможете использовать для визуального анализа своей проблемы.
 
 
 
#24
12.11.2016 10:09:38
Проблема в том, что удаление строк происходит медленно и вопрос был как ускорить работу макроса, который удаляет строку по значению в ячейке, из таблицы с большим количеством строк, в случае если условие по которому удаляется строка записано в ячейку с помощью функций.
Строки ОБЯЗАТЕЛЬНО должны удаляться!
Вопрос: можно ускорить или нельзя, если можно, прошу помочь!
 
 
 
#25
12.11.2016 10:14:09
Цитата
удалять только те строки, определенным ячейкам в которых, с помощью функций "ЕСЛИ(И" и "ЕСЛИ(ИЛИ" присвоены значения "УДАЛИТЬ" или "ОСТАВИТЬ"
Фильтровать по значению, копировать, вставить на другой лист - так не пойдет?

Еще вариант: заносить строки в диапазон по Union, удалять одним махом.
 
 
 
#26
12.11.2016 10:19:36
Цитата
Т.е. после обработки таблицы... результат необходимо сравнить по другим критериям... Следовательно я не могу точно определить (пока не получу результат первого сравнения) ячейки в каких столбцах я должен сравнивать. Поэтому, для сравнения значений в ячейках используется формула.
Изначально ошибочный путь. Все, что в цитате, можно проделать без участия формул, в коде (см. сообщение №21 от SAS888)

На макрос heso и вариант от Моти Вы вообще ничего не ответили...
 
 
 
#27
12.11.2016 10:44:43
О названии темы.
Цитата
Обработка больших массивов содержащих формулы
Почему название темы должно говорить о формулах? Ваш макрос проверяет не формулы - значения. Неважно, как они получены.
Основная задача - удаление строк.
Цитата
При несовпадении данных в смежных строках эти строки удалять
Почему это название Вы отвергли? На мой взгляд, такое определение точнее отражает смысл.
 
 
 
#28
12.11.2016 10:45:41
Использовать фильтры и автофильтры я пытался, фильтр скрывает строки или делает высоту строки = 0 (я сначала пытался решить проблему удаления строк именно с помощью автофильтра - скрывал строки и удалял скрытые строки или те, высота которых = 0 с помощью макроса, тормозило еще больше. Если фильтровать по значению и копировать то копируется вся таблица, если использовать расширенный фильтр, у которого встроена возможность "скопировать результат в другое место", то Эксель ругается и говорит, что копирование возможно только на тот же лист из которого берутся данные!
А про диапазон Union можно поподробнее, как для чайника?!
 
 
 
#29
12.11.2016 10:56:25
Код
Dim ArrData()
Dim rRng As Range
Dim lRws As Long
Dim i As Long
        lRws = ActiveSheet.UsedRange.Rows.Count
        ArrData = Range("A1:X" & lRws).Value ' данные в массив
        
        For i = 1 To  lRws
            If условие _совпадения_выполнено Then
                If rRng Is Nothing Then
                    Set rRng = Cells(i, 1) ' формируем диапазон
                Else
                    Set rRng = Union(rRng, Cells(i, 1)) 'пополняем диапазон
                End If
            End If
        Next i
        
        Application.ScreenUpdating = False
        If Not rRng Is Nothing Then rRng.EntireRow.Delete ' если есть, удаляем
        Application.ScreenUpdating = True

В файл не заглядывал, поэтому диапазон, заносимый в массив, указан произвольно.
Cells(i, 1) - любая ячейка строки.
Если известна первая удаляемая строка (можно перед таблицей вставить пустую), нужно вынести из цикла формирование диапазона:
     
Код
       Set rRng = Cells(2, 1) ' формируем диапазон (строка 2 под удаление)

        For i = 1  lRws
            If условие _совпадения_выполнено Then
                 Set rRng = Union(rRng, Cells(i, 1)) 'пополняем диапазон
            End If
        Next i
 
 
 
#30
12.11.2016 17:57:20
Извините, но я отвечал, мне не подходит сравнение диапазона А1:Х!
Сравниваются значения, содержащиеся в смежных ячейках находящихся в определенных столбцах допустим С, E, H, T. и в зависимости от полученного результата, необходимо в полученном результате опять провести сравнение в одном случае, допустим, в столбцах D, G, Y, а в другом случае это может быть в столбцах A, F, W, критерий по которому будет проходить второе и последующее сравнение зависит от результата предыдущего! Я пытаюсь понять почему возникает ошибка (или при выгрузке данных из БД в эксель или при записи данных в БД сторонним ПО), систему по которой происходит дублирование некоторых записей. Запись представляет из себя строку в ячейки которой заносятся различные параметры, запись производится по определенному событию. То, что в двух смежных строках записаны данные об одном и том же событии подтверждается видеонаблюдением, проблема в том, что запись дублируется не полностью т.е. в двух смежных строках в некоторых ячейках значения идентичны, а в некоторых отличаются (ПРИ ЭТОМ СОБЫТИЕ ПРОИСХОДИТ ОДНО!!! А ЗАПИСЕЙ ДВЕ!!! И ИНОГДА ТРИ!!!!!), при чем эти некоторые, отличающиеся  ячейки могут находится в разных столбцах.
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх