Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.60/15: Рейтинг темы: голосов - 15, средняя оценка - 4.60
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
1

Работа с большим объемом данных

18.03.2016, 10:30. Показов 2930. Ответов 20
Метки нет (Все метки)

Всем привет!
Работа с большим объемом данных заставляет задуматься над всевозможными способами оптимизации алгоритма. Возьмем простой пример. Допустим мне нужно скопировать из одного файла в другой диапазон "A1:A100000". Далее удалить из этого диапазона все нули и работать с оставшимися данными. Я могу это сделать так:

Тупо ставить в новую книгу скопированный диапазон, поставить фильтр (выделив только нули), удалить нулевые строчки, заглать в переменную Set myNewRange = ... и вот тогда уже начать работать с нужными данными моего диапазона myNewRange.

Но проблема в том, что так делать муторно. Не хочется вставлять на лист данные и скрывать это все от пользователя, устанавливать фильтр, удалять ненужные строчки и т.д. Хочет это сделать все в коде, используя переменные типа Range.

Например, Set myAllRange = ... - заношу сюда все скопированные данные, а потом нужно как-то в диапазоне удалить нули, чтобы получить только нужные данные, а именно, диапазон myNewRange. Можно ли тут использовать что-то типа фильтра? Так как фильтр, на мой взгляд, работает довольно быстро.

Если это делать поиском, типа, Set cell = myAllRange.Find("0", , xlValues), а потом удалять все, что было найдено, то это будет довольно долго.

Как решить эту проблему? Подскажите пожалуйста. Может есть какие-то общие рекомендации или принципы по работе с большим объемом данных в Excel (и пока только в Excel)?
Спасибо!
0
Лучшие ответы (1)
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
18.03.2016, 10:30
Ответы с готовыми решениями:

работа с большим объемом данных
большим я называю от 300000Rx50C судя по информации из диспетчера задач происходит накопление...

Работа с большим объемом данных
Всем привет! Всегда создавал коллекцию List<List<xxx>>, заносил в нее данные из txt файла, а потом...

Работа с большим объёмом данных
Приветствую всех! На днях столкнулся с такой проблемой: необходимо отправить на сервер файл...

Работа с большим объемом данных
Коллеги, подскажите по такому вопросу. Есть выборка, несколько миллионов записей. Каким образом...

20
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
18.03.2016, 10:48 2
Используйте массив, если нужны только данные. Судя по высказанному - так и есть.
Взяли одним действием весь неразрывный диапазон в массив (данные диапазона), далее в цикле можно делать с данными что угодно. Скорость выше перебора ячеек раз так в 40.
0
355 / 161 / 27
Регистрация: 21.06.2011
Сообщений: 350
18.03.2016, 14:29 3
Можно так же одним SQL запросом вывести на лист нужный результат.
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
18.03.2016, 18:43  [ТС] 4
Спасибо за ответы. Что касается массивов, то на мой взгляд, что работа массивом, что с диапазоном - одно и тоже. Я также могу сделать цикл по всем элементам диапазона и удалять из него нулевые элементы. Но мне кажется, что это не самое оптимальное решение, т.к. элементов может быть очень много. Такое ощущение, что фильтр (я имею ввиду AutoFilterMode) сработает быстрее, чем цикл по элементам дапазона. Про перебор ячеек я вообще молчу.

Еще раз попробую сформулировать вопрос: как в можно в переменной типа Range отфильтровать все нулевые значения не используя цикл по всем элементам и не записывая эти элементы на лист, т.е. некий аналог того, как работает фильтр по ячейкам?

Как с помощью SQL запроса загнать результат в переменную типа Range пока не понятно, но идея интересная. Может подскажите в плане реализации? Заносить полученные значения на лист - не хочу, т.к. 1. это тормозит обработку. 2. нужно от пользователя скрывать эти промежуточные вычисления, а это тоже напрягает. Хочет сделать красиво и с максимальной скоростью.
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
18.03.2016, 22:03 5
Цитата Сообщение от master-artyom Посмотреть сообщение
что работа массивом, что с диапазоном - одно и тоже
Цитата Сообщение от master-artyom Посмотреть сообщение
Про перебор ячеек я вообще молчу.
вот тут не понял.
А вообще что спорить - возьмите и проверьте, сравните время перебора массива и диапазона/ячеек.
Цитата Сообщение от master-artyom Посмотреть сообщение
как в можно в переменной типа Range отфильтровать все нулевые значения не используя цикл по всем элементам и не записывая эти элементы на лист, т.е. некий аналог того, как работает фильтр по ячейкам?
- так эта переменная и есть часть листа, т.е. невозможно сделать так, как Вам хочется.
А вот переложить из одного виртуального массива отобранные по критерию элементы в другой массив (или в этот же, но в его начало, что удобнее) - нет проблем.
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
18.03.2016, 22:54  [ТС] 6
Не поленился, сделал 4 варианта обработки столбца, содержащего 1 048 576 строк. Задача состояла в том, чтобы выбрать все значения, которые есть в непустых ячейках и записать результат в отдельный столбец.
Результат тестирования - поразил. То, что по логике должно было сработать максимально быстро - работало очень долго.
Итак, были следующие алгоритмы решения задачи (файл прилагаю):

1. (Полный перебор). Идем по всем ячейкам с исходными данными и если она не пустая, тогда заносим ее в отдельный столбец. Общее время - около 9 секунд. (около - потому, что есть погрешность, при использовании Timer).

2. (С помощью массива, он же диапазон). Сначала весь диапазон с исходными данными заносился в массив, а затем циклом по всем элементам массива была проверка на непустое значение. Общее время - около 6 секунд.

3. (С помощью Find в Range). Сначала весь диапазон с исходными данными заносился в переменную типа Range (пусть будет myRange), затем использовалась функция .Find("*", LookIn:=xlValues) - аналог кнопки "Найти", которая искала непустые значения в myRange. Общее время - около 15 секунд. Т.е. получается, что встроенная функция Find ищет непустые ячейки, примерно, в 3 раза дольше, чем сделать это полным перебором. Очень удивился.

4. (Фильтр+Range). Результат этого алгоритма меня вообще убил. Суть его заключалась в том, что сначала на диапазон с исходными данными устанавливается фильтр (Range("$A:$A").AutoFilter Field:=1, Criteria1:="<>"), который выдает только непустые ячейки. Затем полученный результат заносится в диапазон myRange, а далее двумя циклами (т.к. в результате фильтрации получаются разрывные области), первым по областям, вторым по диапазону внутри области, достаются значения. Общее время - около 400 секунд.

Большая просьба к экспертам VBA. Если есть возможность, посмотрите файл. Есть еще способы сократить время на выполнения кода? (кроме фишек, типа того, что вместо <>"" лучше использовать Len()<>0, т.к. в другом примере критерии могут быть другие).

Спасибо за ответы.
0
Вложения
Тип файла: rar Тест алгоритмов.rar (5.29 Мб, 11 просмотров)
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
18.03.2016, 23:10 7
Лучший ответ Сообщение было отмечено master-artyom как решение

Решение

У меня вариант 2 отработал за 2,52, но это ведь всего лишь "полуиспользование" массива
Я предлагал ведь делать примерно так (отработало за 0,4140625)
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
Sub Вариант22()
 
Dim arr
Dim Row As Long
 
    t = Timer
    Application.DisplayAlerts = True: Application.ScreenUpdating = False ':Application.Calculation = xlManual
       
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range(Cells(2, 1), Cells(lastRow, 1)).Value
   
    For Row = 2 To lastRow - 1
        If arr(Row, 1) <> "" Then
                    j = j + 1
            arr(j, 1) = arr(Row, 1)
        End If
    Next Row
    
    
    Application.DisplayAlerts = True: Application.ScreenUpdating = True ':Application.Calculation = xlAutomatic
    Range("J3").Resize(j, 1) = arr
    Range("J2").Value = Timer - t
    MsgBox "Готово!"
End Sub
4
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
18.03.2016, 23:17  [ТС] 8
Вау! Вот это реально супер!!! Другой способ занесения данных из массива в ячейки и сразу скорость увеличилась в 5 раз для данного примера.
Спасибо!
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
18.03.2016, 23:20 9
Ну так если умеете взять - можете ведь аналогично и выгрузить, зачем поячеечно заполняли?
Тут я показал ещё фишку для экономии памяти - не создаём для результата другой массив, а используем тот же.
0
355 / 161 / 27
Регистрация: 21.06.2011
Сообщений: 350
19.03.2016, 08:16 10
SQL не вариант выдает от 17 до 22 сек...
НО как быть например если в исходно таблице не 1 столбец а 50 например? Такое уже не загонишь в массив Out of Memory...
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
19.03.2016, 15:00 11
Цитата Сообщение от Watcher_1 Посмотреть сообщение
Такое уже не загонишь в массив Out of Memory.
Можно брать частями например по 10 столбцов, или по 10000 строк.
Или брать в массив только то, что анализируем, а остальное оставлять на листе, ладно уж...
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
26.03.2016, 16:22  [ТС] 12
Hugo, приветствую! Можешь подсказать еще один момент? как можно проще сделать. Ты в своем коде непустые значения заносил в тот же массив arr, а затем использовал .Resize. Мне нужно занести непустые значения в другой массив, скажем, arr1. Как правильно его задать? Единственное, что пришло в голову, определить его также, как arr, т.е. arr1 = Range(Cells(2, 1), Cells(lastRow, 1)).Value. Насколько это будет оптимально? Есть ли еще способы.
Пример кода:

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
Sub Вариант22()
 
Dim arr
Dim arr1
Dim Row As Long
 
    t = Timer
    Application.DisplayAlerts = True: Application.ScreenUpdating = False ':Application.Calculation = xlManual
       
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range(Cells(2, 1), Cells(lastRow, 1)).Value
    arr1 = Range(Cells(2, 1), Cells(lastRow, 1)).Value
   
    For Row = 2 To lastRow - 1
        If arr(Row, 1) <> "" Then
                    j = j + 1
            arr1(j, 1) = arr(Row, 1)
        End If
    Next Row
    
    
    Application.DisplayAlerts = True: Application.ScreenUpdating = True ':Application.Calculation = xlAutomatic
    Range("J3").Resize(j, 1) = arr1
    Range("J2").Value = Timer - t
    MsgBox "Готово!"
End Sub
Спасибо!
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
26.03.2016, 16:47 13
Добрый день.
Можно определить массив такого же размера, чтоб точно всё поместилось в лоюбом случае, но не так как в примере (зачем брать с листа), а с помощью Redim:
Visual Basic
1
redim arr1(1 to ubound(arr), 1 to 1)
если столбцов неизвестно сколько - можно использовать ubound(arr,2)
Но если данных много и массивы огромные - может не хватить памяти.
Другой вариант - собирать данные в коллекцию без ключа (если как тут нужен всего один столбец). В финале видим сколько собрали - можно переложить в цикле в созданный под размер массив и его выгрузить на лист, или сразу на лист.
Или собирать в коллекцию индексы этого иссследуемого массива (можно традиционно с ключами - индекс ведь не повторится), затем циклом по собранному брать данные непосредственно из исходного массива.
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
26.03.2016, 20:17  [ТС] 14
Спасибо! Да, действительно, так работает!
С коллекциями пока не работал. Нужно будет почитать.
А вот по поводу массивов методом проб и ошибок заметил такую вещь:
Если нужно загнать диапазон в массив или из массива в диапазон (без использования цикла), тогда массив должен быть минимум двумерный (т.е. с указанием столбца). А вот одномерный массив уже так не сработает, несмотря на то, что из диапазона загоняется один столбец с данными.
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
26.03.2016, 22:04 15
Одномерный по вертикали сработает используя application.transpose, вот например даже так - можно выгрузить один одномерный из двух одномерных:
Visual Basic
1
Sheets("Итог").[a2].Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
А по горизонтали можно вообще не мудрить - просто укажите диапазон/размер выгрузки.
Visual Basic
1
[a1:c1].Value = Array(234, 46, 6789)
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
26.03.2016, 22:57  [ТС] 16
Hugo, где про это все можно почитать? Я много видел статей про массивы, но в них нет такой детализации.
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
26.03.2016, 22:59 17
Так ведь уже прочитали
Если серьёзно - не знаю где, я на форумах читал.
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
13.04.2016, 16:00  [ТС] 18
Коллеги, начал пользоваться быстрой загрузкой и выгрузкой в массив, и подсел на это, так как реально все летает! Появился еще вопрос. Как осуществить быструю вставку в ячейки двумерного массива с помощью приема Range("J3").Resize(j, 1) = arr1?
Например, у меня есть двумерный массив arr(1,2). Мне нужно первый "столбце" массив вставить на лист в столбец "А", а второй "столбец" массива в столбец "С".
Спасибо!
0
6823 / 2765 / 520
Регистрация: 19.10.2012
Сообщений: 8,432
13.04.2016, 16:13 19
Выгружаете почти как написали (только resize(j,2)), затем между столбцами вставляете столбец B
Ну или выгружаете один столбец, затем перекладываете данные массива из второго столбца в первый, снова выгружаете один столбец.
А я обычно в таких случаях сразу завожу два массива, чтоб потом время не терять.
0
0 / 0 / 0
Регистрация: 12.06.2015
Сообщений: 66
13.04.2016, 16:29  [ТС] 20
Понятно. Я думал, что может быть можно как-то из двумерного массива достать одномерный не перезаписывая циклом. Так как проблема в том, что я заведомо не знаю скольки мерный массив получится, поэтому по сути мне нужно выгрузить данных из n-мерного массива в определенные столбцы (не попорядку).
0
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
13.04.2016, 16:29

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

Работа с большим объемом данных
Имеется загруженное изоображение с помощью opencv. Задача: вывести цвета изображения с из...

QtSerialPort Работа с большим объемом данных
Добрый день. Я пишу приложение, которое работает с последовательным портом при помощи...

Cron задание, работа с большим объемом данных
К примеру есть скрипт, который должен делать коррекцию данных на сайте. Данных много и к примеру...

Работа с большим объемом данных в реальном времени
Добрый день! Недавно решил перевести один проект с Delphi на C#. Проект связан с получением и...


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

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

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