Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.73/15: Рейтинг темы: голосов - 15, средняя оценка - 4.73
1007 / 111 / 2
Регистрация: 26.08.2011
Сообщений: 1,054
Записей в блоге: 2
1

Как быстро удалять лишние строки?

27.05.2016, 09:41. Просмотров 3150. Ответов 29
Метки нет (Все метки)

На листе есть структурированные данные: столбцов 40-250 (иногда больше), строк вообще 100 000-140 000 (и очень часто может быть больше).
Я нарисовал функцию, которая выбирает уникальные строки, остальные с листа удаляет командой
Visual Basic
1
 Rows(i).Delete
Работает это крайне медленно. На обработку 140 000 строк (осталось 28861 уникальных) ушло что-то около 6 часов.
Сделать массив и обработать его не представляется возможным: слишком здоровый получается 140 000 строк * 200 столбцов *64 (тип Variant) = 1792 000 000
Вообщем лучше на листе...

Может у кого есть идеи - как это ускорить?
А то на ночь ставлю - и не факт, что утром будет готово...
0
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
27.05.2016, 09:41
Ответы с готовыми решениями:

Удалять лишние пробелы в поле ввода информации на лету
Ситуация такая как удалять пробелы на лету с начала и конца строки, у меня всё работает только...

Подскажите почему не работает?Она должна удалять лишние пробелы в файле в любой строке.
В Lazarus компилируется,но не работает(возможно подвисает) в Turbo Pascal пишет file not found.Файл...

Как удалять элемент из строки в c++?
Как удалять элемент из строки в c++?

Исправьте пожалуйста,прога должна,удалять лишние пробелы в файле,вместо этого удаляет все содержимое?
Program space; var q:text; s:string; fname:string; begin fname:='finish.txt';...

29
Модератор
Эксперт MS Access
9783 / 3832 / 614
Регистрация: 07.08.2010
Сообщений: 10,836
Записей в блоге: 2
27.05.2016, 09:49 2
удалять надо от последней записи к первой
а как написан ваш макрос
1
6612 / 2631 / 488
Регистрация: 19.10.2012
Сообщений: 7,946
27.05.2016, 10:13 3
Для удаления строк есть быстрый код от ZVI - только вот что-то найти не могу...
Идея в том, чтоб проставить единички в массив, выгрузить рядом с данными, отсортировать, удалить сразу всё одним блоком.

Добавлено через 2 минуты
Нашёл:
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
==============================================================
Мне необходимо удалить строки со нулевыми значиниями из Листа, но там порядка 200 000 строк.
ZVI 
Процедура очистки(удаления) строк в столбце ColNum со значениями DelValue: 
'===================================================================================
' Sub         : DelRows(TableHeader,ColNum,DelValue)
' TableHeader : Range; table header range
' ColNum      : Long; column number with DelValue
' DelValue    : Variant; value of rows to be deleted
'-------------+---------------------------------------------------------------------
' VBA call    : DelRows ActiveSheet.Range("A1:H1"), 5, 0
'-------------+---------------------------------------------------------------------
' Created     : ZVI:2009:12:26 [url]http://www.sql.ru/forum/actualthread.aspx?tid=722758[/url]
'-----------------------------------------------------------------------------------
Sub DelRows(TableHeader As Range, ColNum As Long, DelValue)
  Dim Arr(), r&, rs&, cs&, i&, v, ac
  With TableHeader.CurrentRegion
    rs = .Rows.Count - TableHeader.Row + .Row
    cs = .Columns.Count - TableHeader.Column + .Column
  End With
  With TableHeader.Resize(rs, 1)
    Arr() = .Offset(, cs).Value
    ' Check DelValue
    For Each v In .Offset(, ColNum - 1).Value
      r = r + 1
      If v <> DelValue Then
        i = i + 1
        Arr(r, 1) = 1
      End If
    Next
    If i < rs Then
      ' Freeze on
      With Application
        .ScreenUpdating = False
        .EnableEvents = False
        ac = .Calculation: .Calculation = xlCalculationManual
      End With
      ' Delete rows with DelValue in ColNum
      .Offset(, cs) = Arr
      .Resize(, cs + 1).Sort .Cells(1).Offset(, cs), 1, Header:=xlNo
      .Resize(, 1).Offset(, cs).ClearContents
      .Resize(rs - i, cs).Offset(i).Clear   ' или .Resize(rs - i).Offset(i).EntireRow.Delete
      ' Freeze off
      With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = ac
      End With
    End If
  End With
End Sub
 
 
Тестирование:
 
Sub Test()
  DelRows ActiveSheet.Range("A1:H1"), 5, 0
End Sub
 
Круто, попробовал на миллионе строк в Excel 2007, работает, причем очень быстро.
==
Добавлено через 9 минут
Кстати там в той оригинальной теме я что-то с словарями писал - может там сразу и удаление неуникальных было, детали естественно не помню...
А не, там задача была "удалить все строки в 7 столбце, значение которых не равно 2 или 3" - но думаю можно приспособить для анализа уникальности. Если конечно под Виндовс работаете.
2
634 / 194 / 20
Регистрация: 20.05.2016
Сообщений: 776
Записей в блоге: 12
27.05.2016, 10:45 4
НЕ знаю можно ли давать ссылку на сторонние ресурсы (если нельзя - выложу здесь), но аналогичную тему я поднимал здесь. Посмотрите, возможно это решит вашу проблему.
1
4 / 4 / 3
Регистрация: 19.08.2013
Сообщений: 24
27.05.2016, 11:10 5
Hugo121, спасибо за код, сохраню к себе в памятку. вообще когда появляется необходимость в ручную удалить много строк из еще большего количества(до миллиона) я сначала фильтром выделяю то, что хочу удалить, после последнего столбца ставлю везде 1. делаю по нему сортировку и удаляю.
1
1007 / 111 / 2
Регистрация: 26.08.2011
Сообщений: 1,054
Записей в блоге: 2
27.05.2016, 11:30  [ТС] 6
shanemac51, спасибо за совет. А можно немножко пояснить - почему именно так?
Hugo121, меня немного смутило то, что на SQL -ресурсе опубликовано... но если написано для Excel - то в принципе надо пробовать... Сейчас прога работает - я чуть позже покопаюсь в коде.
Хотя уже сейчас есть вопрос - сортировка... Что обеспечивается командой Sort?
Вообще у меня каждая строка, намеченная на удаление, помечена словом "дубликат". Понимаю. что расточительство. но через время - понимать и код и содержимое листа проще. По идее, есть возможность отфильтровать строки с этой меткой одним движением.
Думаю, лучше будет выделить строки через Union

Добавлено через 2 минуты
mitsakoolt, спасибо... стало понятно зачем сортировка тут... Хотя если несмежные диапазоны выделены - неужели нет способа их удалить без сортировки?
0
6612 / 2631 / 488
Регистрация: 19.10.2012
Сообщений: 7,946
27.05.2016, 11:35 7
Через union дольше, но тоже вариант.
Как раз вчера такое делал (вернее гонял ранее написанное) - само объединение делает за пару секунд, но вот затем удаление происходит раз так в 10 дольше.
А если удалять построчно - то нужно снизу вверх потому что каждая удалённая строка как ни странно удаляется
Т.е. пропадает и на её место сдвигается строка снизу, которую возможно тоже нужно бы удалить...
0
1007 / 111 / 2
Регистрация: 26.08.2011
Сообщений: 1,054
Записей в блоге: 2
27.05.2016, 11:39  [ТС] 8
от жыж блин... А у меня прога работает... щас буду курочить код
0
634 / 194 / 20
Регистрация: 20.05.2016
Сообщений: 776
Записей в блоге: 12
27.05.2016, 11:45 9
Мой не подходит по ссылке? исходник (без правки)
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub ВыделитьСтроки() 'выделить/удалить все НЕ пустые строки не содержащие значение "Баранова О.В."
Dim строка As LongPtr
Dim строки As Range
Dim строки2 As Range
 
     For строка = 3000 To 1 Step -1
        If Cells(строка, 1) <> "Баранова О.В." And Cells(строка, 1) <> "" Then Set строки = Rows(строка)
            If строки2 Is Nothing Then Set строки2 = строки Else Set строки2 = Union(строки2, строки)
     Next строка
  
'Debug.Print строки2.Address 'посмотреть диапазоны адресов
If Not строки2 Is Nothing Then строки2.Select 'выделить
'If Not строки2 Is Nothing Then строки2.Delete Shift:=xlUp 'удалить со свигом вверх
End Sub
0
6612 / 2631 / 488
Регистрация: 19.10.2012
Сообщений: 7,946
27.05.2016, 11:52 10
bedvit, тоже можно, но для количества в сотни тысяч строк как минимум перебирать и анализировать нужно бы данные массива - тут у Вас на каждую строку идёт два обращения к листу, что в 43*2 раза дольше, чем обращение к массиву.
Да и если всё брать в юнион - то не важно с какого конца перебирать, можно и сверху вниз.
0
1007 / 111 / 2
Регистрация: 26.08.2011
Сообщений: 1,054
Записей в блоге: 2
27.05.2016, 11:59  [ТС] 11
bedvit, на основе его и буду колупать... и закомментированную строку с delete попытаюсь использовать... только дождусь когда прога доработает ...
0
634 / 194 / 20
Регистрация: 20.05.2016
Сообщений: 776
Записей в блоге: 12
27.05.2016, 12:48 12
Hugo121, в общем согласен с вами. Выделение 1 млн. строк с поиском по условию 11 сек., это много, но по сравнению с операцией удаления
Цитата Сообщение от AndreA SN Посмотреть сообщение
около 6 часов
, копейки. Плюс Union - медленная функция. Если вам будет интересно здесь максимальной производительности для Union (выделение 250 тыс. несвязных ячеек за 5 сек.)

Добавлено через 23 минуты
Поиск по условию через массив (0,078125 сек.), при отключенном Union. Теперь только вопрос в Union, и самой операции удаления.
Visual Basic
1
2
3
4
5
6
7
8
9
10
Sub ВыделитьСтроки() 'выделить/удалить все НЕ пустые строки не содержащие значение "Баранова О.В."
Dim строка As Long, строки As Range, строки2 As Range, R
R = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Value 'забрать в массив столбец А с первой ячейки до последней заполнненной
     For строка = 1 To UBound(R) 'по последний заполненный
        If R(строка, 1) <> "" And R(строка, 1) <> "Баранова О.В." Then Set строки = Rows(строка)
            If строки2 Is Nothing Then Set строки2 = строки Else Set строки2 = Union(строки2, строки)
     Next строка
If Not строки2 Is Nothing Then строки2.Select 'выделить
'If Not строки2 Is Nothing Then строки2.Delete Shift:=xlUp 'удалить со свигом вверх
End Sub
Добавлено через 4 минуты
С двумя условиями 0,16796875 сек.
0
6612 / 2631 / 488
Регистрация: 19.10.2012
Сообщений: 7,946
27.05.2016, 12:49 13
Visual Basic
1
If R(строка, 1) <> "" And R(строка, 1) <> "Баранова О.В." Then
тут можно ускорить, если сделать цепочку If-Then - ведь нет смысла проверять Баранове, если там пусто, а сейчас проверяется!
0
4 / 4 / 3
Регистрация: 19.08.2013
Сообщений: 24
27.05.2016, 12:53 14
Цитата Сообщение от AndreA SN Посмотреть сообщение
Хотя если несмежные диапазоны выделены - неужели нет способа их удалить без сортировки?
удалить можно, но процесс будет очень долгим. я первый раз с вечера поставил удалять, утром вернулся и удаление еще продолжалось. тогда было около 700 000 строк, из них почти половину нужно было удалить. и диапазоны в среднем были по 1-3 строки. при сортировке когда удаляешь одним диапазоном все происходит за секунды. хотя через макрос наверно будет также и без сортировке если ввести
Visual Basic
1
2
3
4
With Application
.ScreenUpdating = False
 .Calculation = xlManual
 End With
надо потестировать
0
634 / 194 / 20
Регистрация: 20.05.2016
Сообщений: 776
Записей в блоге: 12
27.05.2016, 13:04 15
Но Union нужен, когда строки нельзя сортировать, когда их много и когда их нужно удалить разом (что быстрее чем по одной или по несколько за раз)

Добавлено через 4 минуты
Hugo121, согласен. Можно да же удалить второе условие, ТС возможно нужно только одно условие.
думаю отключение экрана и перерасчет сильно не поможет в моем коде, там и так разом все делается (удаление).

Добавлено через 3 минуты
Каждое условие это 0,08 сек., копейки-копеек по сравнению с другими расчетами)
0
Модератор
Эксперт MS Access
9783 / 3832 / 614
Регистрация: 07.08.2010
Сообщений: 10,836
Записей в блоге: 2
27.05.2016, 14:30 16
Цитата Сообщение от AndreA SN Посмотреть сообщение
слишком здоровый получается 140 000 строк * 200 столбцов *64 (тип Variant) = 1792 000 000
а что скажете про форматирование и формулы
насколько сложно
0
1007 / 111 / 2
Регистрация: 26.08.2011
Сообщений: 1,054
Записей в блоге: 2
28.05.2016, 15:16  [ТС] 17
Ребята. Очень извиняюсь... Сейчас готовлюсь исчезнуть дней на 40... Поэтому работаю с Вашими рекомендациями очень нерегулярно... и отвечаю тоже... Тренируюсь на левшу. Экстренно))) Ибо чую - очень надо это мне на лето.

Галина. Форматирование и формулы в массив не гружу. Гружу командой
Visual Basic
1
mass = Range(Cells(1, 1), Cells(row_, col_)).Value
При выгрузке из массива ничего не считаю. Лист получает только числа в текстовом режиме и текст. Если и идет время на форматирование, то форматирует числа Excel сам - автоматически.

Добавлено через 1 минуту
Выгружаю из массива уже обработанные данные аналогично
Собственно здесь потери времени минимальны

Добавлено через 1 час 1 минуту
Вот что у меня получилось
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
idcolprb = 19 ' перед работой этого кода в 19 колонке делаю пометки дубликатов
 
' ST - проверяемая в данную итерацию строка
' ST2 - собираемый на удаление диапазон срок
 
 Dim ST As Range, ST2 As Range
 i = 2
    Do
        If Cells(i, idcolprb).Value = Empty Then Exit Do
        If Cells(i, idcolprb).Value = "дубликат" Then
            Set ST = Rows(i)
            If ST2 Is Nothing Then Set ST2 = ST Else Set ST2 = Union(ST2, ST)
        End If
        i = i + 1
     Loop
If Not ST2 Is Nothing Then 
    ST2.Select 'ВЫДЕЛИТЬ все отобранные строки
    ST2.Delete Shift:=xlUp 'удалить выделенные строки со сдвигом вверх
end if
Спасибо Вам за помощь
0
Модератор
Эксперт MS Access
9783 / 3832 / 614
Регистрация: 07.08.2010
Сообщений: 10,836
Записей в блоге: 2
28.05.2016, 17:11 18
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Dim ws As Worksheet
Dim j1 As Long, j2 As Long, j3, dt
 
 
With Application
.ScreenUpdating = False
 .Calculation = xlManual
 End With
Set ws = Excel.Worksheets("zrab")
'' заполнение 150000 строк, 150 столбцов
'ws.Range("b2:ee150000") = "wwwwjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"
dt = Timer
j1 = 150000
j2 = 0
j3 = 0
Do While j1 > 0
j2 = j2 + 1
j3 = j3 + 1
 
If j2 = 5 Then  '''каждую 5-ю
ws.Rows(j1).Delete
j2 = 0
If j3 > 1000 Then   '''''трассер
Debug.Print j1; (Timer - dt) \ 1
j3 = 0
End If
 
 
 
End If
j1 = j1 - 1
Loop
Debug.Print Now
MsgBox "выполнение в секундах(около 4мин)=" & (Timer - dt) \ 1
1
6612 / 2631 / 488
Регистрация: 19.10.2012
Сообщений: 7,946
28.05.2016, 23:38 19
Я думаю быстрее будет в union брать не всю строку, а только одну ячейку строки.
А в конце удалять
Visual Basic
1
ST2.EntireRow.Delete
как выше в коде ZVI
1
634 / 194 / 20
Регистрация: 20.05.2016
Сообщений: 776
Записей в блоге: 12
29.05.2016, 18:36 20
Hugo121, хорошая идея. Сегодня протестировал немного. Если строк много (на удаление) несколько десятков тысяч, Union работает медленно и удаление не столь быстрое как хотелось.
AndreA SN, сортировка в условиях задачи разрешается? Формул, условного форматирование нет? Порядок следования строк сохранятся должен?
0
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
29.05.2016, 18:36

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Как удалять строки по найденному слову?
Есть 3753 строки в один столбец. Например если в строке есть слово Житомир(регистр значения не...

Как с массива удалять столбцы или строки?
Как с массива удалять столбцы или строки?

Объясните как удалять, добавлять и сортировать строки
подскажите, пожалуйста,как удалять, добавлять и сортировать строки? мне казалось,что через...

Объясните, как удалять строки из текстового файла?
в цикле гетлайн брать из одного файла, записать в другой и переименовать в старый? напишите свои...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2020, vBulletin Solutions, Inc.