Страницы: 1
RSS
Как отсортировать два листа, как один? Сортировка 2 млн. строк.
 
Приветствую. На листе есть 1 млн строк, а если есть массив из 2 млн строк, то как отсортировать?
Можно ли два листа отсортировать, как один? То есть я вставляю данные из массива на два листа, а потом отсортирую эти два листа.
Или может быть можно как-то массив отсортировать?
Интересует способ с помощью VBA, поэтому Power Query наверное не подойдет - к нему же нельзя подключиться из VBA?
 
Цитата
Karataev написал:
может быть можно как-то массив отсортировать?
А что мешает?
12 секунд на моём полуживом ноуте-
Код
Option Explicit
Dim a(1999999) As String, x$, y$

Sub test()
Dim I&
For I = 0 To 1999999
  a(I) = Format$(Int(Rnd * 2000000), "0000000")
Next

Dim t#
t = Timer
QuickSort 0, 1999999
Debug.Print Timer - t

End Sub

Sub QuickSort(ByVal L As Long, ByVal U As Long)
Dim I As Long, J As Long
I = L: J = U: x = a((L + U) \ 2)
Do
  While a(I) < x: I = I + 1: Wend: While x < a(J): J = J - 1: Wend 'по возрастанию
'  While A(I) > X: I = I + 1: Wend: While X > A(J): J = J - 1: Wend 'по убыванию
  If I <= J Then
    y = a(I): a(I) = a(J): a(J) = y:    I = I + 1: J = J - 1
  End If
Loop Until I > J
If L < J Then QuickSort L, J
If I < U Then QuickSort I, U
End Sub
 
спасибо, попробую прмиенить
 
Цитата
Karataev написал:
а если есть массив из 2 млн строк, то как отсортировать
а он где есть? просто есть в том числе и SQL и recordset хоть в массив, хоть …..
По вопросам из тем форума, личку не читаю.
 
В vba массиве.
 
Как формируется этот массив и куда дальше пойдет. Какой тип данных в массиве.
«Бритва Оккама» или «Принцип Калашникова»?
 
Массив формируется из данных листа. Дальше массив так и будет использоваться, никуда вставлять его не надо.
 
Какой тип данных будет в массиве? Для чего нужна сортировка?
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, тип данных String, задача сортировки: сблизить одинаковые данные.
 
Karataev, тогда вам просто нужна сортировка массива в VBA. Не пользовался, но вот есть в копилке сортировка в двумерном массиве. или провнрить решение в посте 2 или вот, в архиве. Или множество ещё  в сети.
«Бритва Оккама» или «Принцип Калашникова»?
 
Если вместо традиционного обмена строками y = a(I): a(I) = a(J): a(J) = y сделать обмен указателей на строки, время сокращается на 20%.
Хранение повторяющихся фрагментов base + I * 4, base + J * 4 в переменных эффекта не дает.
Замена a(I) < x на StrComp(a(I), x) = -1  код не ускоряет, но дает возможность легко ввести параметр "по возрастанию/по убыванию", -1/1.
Только для Win32.
Код
Option Explicit
Declare Sub GetMem4 Lib "msvbvm60" (src As Any, dst As Any)

Dim a(1999999) As String, x$, y&, base&
 
Sub test()
Dim I&
For I = 0 To 1999999
  a(I) = Format$(Int(Rnd * 2000000), "0000000")
Next
 
Dim t#
t = Timer
base = VarPtr(a(0))
QuickSort 0, 1999999
Debug.Print Timer - t
 
End Sub
 
Sub QuickSort(ByVal L As Long, ByVal u As Long)
Dim I As Long, J As Long
I = L: J = u: x = a((L + u) \ 2)
Do
  While StrComp(a(I), x) = -1: I = I + 1: Wend: While StrComp(x, a(J)) = -1: J = J - 1: Wend 'по возрастанию
'  While A(I) > X: I = I + 1: Wend: While X > A(J): J = J - 1: Wend 'по убыванию
  If I <= J Then
    GetMem4 ByVal base + I * 4, y                    'y = a(I)
    GetMem4 ByVal base + J * 4, ByVal base + I * 4   'a(I) = a(J)
    GetMem4 y, ByVal base + J * 4                    'a(J) = y
    I = I + 1: J = J - 1
  End If
Loop Until I > J
If L < J Then QuickSort L, J
If I < u Then QuickSort I, u
End Sub
 
Цитата
Karataev написал:
задача сортировки: сблизить одинаковые данные.
- зачем? Тем более что никуда далее этот массив выгружаться не будет.
Может просто собрать в словаре коллекцию номеров строк (можно с указанием на лист, раз уж он формируется из данных листа, и если эта информация будет в массиве) уникальных значений.
Изменено: Hugo - 01.11.2018 19:00:47
 
В C+ кажется есть метод для сортировки (не знаю, есть ли для двумерных массивов), а мы - vba программисты должны такое изобретать.
Возникает мысль, что на C+ проще программировать, чем на VBA.
 
А для одномерного массива подойдет ли данная функция объекта System.Collections.ArrayList?
Код
    Dim i As Long
    Dim t As Double
    'Dim sorted As Variant
    Randomize
    With CreateObject("System.Collections.ArrayList")
        For i = 0 To 1999999
          .Add Format$(Int(Rnd * 2000000), "0000000")
        Next
        t = Timer
        .Sort
        'sorted = .ToArray
        Debug.Print Timer - t
    End With
 
Эти System.Collections в VBA работают медленнее, чем словари и тем более родные коллекции. Как в связке с сортировкой - нужно померить.
 
На stackoverflow есть несколько интересных постов:
Функция сортировки массива ВБА
Сортировка многомерного массива в ВБА
Изменено: bedvit - 02.11.2018 20:48:16
«Бритва Оккама» или «Принцип Калашникова»?
 
bedvit, посты интересные, только чтобы разобраться в таком большом количестве кодов, у меня ума не хватает. Какой из этих кодов выбрать? Кто эти люди, которые написали эти коды - может новички и чего-то не учли.
Лучше бы было, если какая-нибудь известная фирма (типа Microsoft) выложили бы коды и написала, в каких случаях, какой код использовать.
Изменено: Karataev - 02.11.2018 21:01:22
 
Hugo, словарь можно использовать, но по времени это не быстрее сортировки. У меня на моем слабом компьютере 1 млн строк записываются в словарь 2 минуты. А Excel сортирует за секунд 5 - 10.
 
Протестировал десяток алгоритмов на 2 млн. строк. (win10x64, AMD Phenom II X4 Black Edition 955, 3200 MHz, ОЗУ 8 Гбайт).
Отобрал самые, с моей точки зрения, интересные:
1. Решение от Charles H. Pearson - 117 сек.
Скрытый текст


2. Решения (одно из быстрейших) с https://social.msdn.microsoft.com - 17,4 сек.
Скрытый текст


3.Оригинальное решение через System.Collections.ArrayList от Jungl - 20,99 сек. (самый короткий код из всех)
Скрытый текст


4. Решение от Апострофф - 9,47 сек.
5. Решение от Казанский -  к сожалению система х64 - не зашло. нужно переписывать под win х64. К сожалению библиотека устарела. Но идея классная!
Изменено: bedvit - 04.11.2018 01:03:31
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
Karataev написал:
В C+ кажется есть метод для сортировки (не знаю, есть ли для двумерных массивов), а мы - vba программисты должны такое изобретать.Возникает мысль, что на C+ проще программировать, чем на VBA.
Появилось немного времени, набросал новый класс "VBA" в своей COM-библиотеке. Добавил новый метод "ArraySort". Написан на С++ :)
Пришлось распилить VARIANT и SafeArray. Кому интересно, вот как выглядит обычный VBA-й Variant изнутри. Да, COM- есть COM в полной красе:)
Метод "ArraySort" работает через Variant, т.к. на некоторых скриптовых языках SAFEARRAY ходит через границы COM, только в обертке VARIANT (без изобретения костылей).
Тест нового метода:
Код
Sub test_ArraySort()
Dim a As Object: Set a = CreateObject("BedvitCOM.VBA")
Dim testCount As Long: testCount = 1999999
Dim x: ReDim x(0 To testCount) As String
Dim I As Long, t#
 
For I = 0 To testCount
  x(I) = Format$(Int(Rnd * testCount), "0000000")
Next
 
t = Timer
a.ArraySort x 'по возрастанию
'a.ArraySort x, 1 ' по убыванию
Debug.Print Timer - t
 
End Sub

Итого у меня 0,92 сек. на 2 млн. строк. Итого почти на порядок (в 9 раз) быстрее самого быстрого способа на массивах в VBA.
Библа уже не пролезает, скачать можно здесь.
COM упакована в XLL (распаковывается и регистрируется автоматом).
Открываем XLL, пишем код в VBA - пользуемся, тестируем.
Версии х32 и х64.
«Бритва Оккама» или «Принцип Калашникова»?
 
Цитата
bedvit написал:
 обычный VBA-й Variant изнутри .
Не было у бабы печали. Купила баба себе порося...
Изверг!  :excl:
 
RAN, кабанчик подрос!
26/11/2018 - Обновления функционала:
1.Сортировка одномерных и двухмерных массивов
2.Удаление дубликатов в одномерных массивах
3.Вывод по одномерному и двухмерному массивам индексов по строкам или столбцам.
4.Сортировка по столбцам, строкам, целому массиву с выводом строки/столбцы, столбцы/строки.
5.Сортировка по 3 ключам (столбцам, строкам)
6.Размерность массива может начинаться с 0,1,2... и т.д. (любого положительного числа, вывод индексов в такой же размерности)
7.Обработка NULL - строк (перемещение на последние позиции)
8.Использованы параллельные алгоритмы сортировки.

Интерфейс класса:
Код
interface IVBA : IDispatch
{
    [id(1), helpstring("Array sort")] HRESULT ArraySort([in, out] VARIANT* array_in_out, [in,defaultvalue(0)] VARIANT_BOOL sort_order, [in, defaultvalue(-1)] LONG key_1, [in, defaultvalue(-1)] LONG key_2, [in, defaultvalue(-1)] LONG key_3, [in, defaultvalue(0)]  LONG sort_orientation,  [in, defaultvalue(0)]  VARIANT_BOOL delete_duplicates,[in, defaultvalue(0)]  VARIANT_BOOL out_array_index, [in, out, defaultvalue(0)] VARIANT* index_array_out);
 
};


array_in_out - указатель на массив (ввод/вывод).  
sort_order - порядок сортировки : 0-по возрастанию (по умолчанию), 1-по убыванию.
key_1 - ключи сортировки (индекс столбца или строки) по умолчанию - первый столбец/строка
key_2...
key_3...
sort_orientation - ориентация сортировки (0 - по строкам, 1 - по столбцам, 2- целый массив вывод строка-столбец, 3- целый массив вывод столбец-строка)
delete_duplicates - удаляем дубликаты (в одномерных массивах)
out_array_index - выводим индексы (тогда основной массив array_in_out - не меняется, выводятся данные в out_array_index)
out_array_index - одномерный массив с индексами (с учетом всех ключей)

Все параметры метода, кроме входящего массива - опциональные (с значениями по умолчанию - 0 (ключи-1))

Пример тестирования на VBA:
Скрытый текст

И самое интересное!
Результаты тестирования на 2-х млн. строк и 4-м столбцам:
Простая сорт. 1х массива, по возрастанию (по умолч.): 0,21875 сек.
Простая сорт.по убыванию 1x массива с удалением-34,55365%: 0,3046875 сек.
Вывод индексов для 1х массива, сортировка по убыванию:0,25 сек.
Простая сортировка 2х массива: 0,359375 сек.
Сортировка 2х массива по 5й строке: 0,3515625 сек.
Сортировка всего 2х массива - вывод строки/столбцы: 1,117188 сек.
Сортировка всего 2х массива - вывод столбцы/строки 1,195313 сек.
Вывод индексов для 2х массива, по указанному столбцу: 0,296875 сек.
Вывод индексов для 2х массива, по указанноq строке: 0,2890625 сек.
Сортировка 2х массива по 5,6,7й строке: 0,03125 сек.
Сортировка 2х массива по 3,4,5му столбцу: 0,9609375 сек.

Сортируйте в VBA и удаляйте дубликаты на скоростях С++ до 35 раз быстрее самого быстрого алгоритма (QuickSort).
Исходники там же.
Изменено: bedvit - 27.11.2018 09:03:44
«Бритва Оккама» или «Принцип Калашникова»?
 
Сортировка Variant
Теперь сортировка еще проще, можно прямо с листа:
Код
Sub Test_arr_sort()
Dim bVBA As Object: Set bVBA = CreateObject("BedvitCOM.VBA")
arrTmpV = [a1:b200]
bVBA.ArraySortV arrTmpV, 1, 1 ' "Простая сорт. VARIANT 2х массива, по убыванию, по первому столбцу: " 
[c1:d200] = arrTmpV
End Sub

в т.ч. сортировка массивов более 1 048 576 строк, объединяя в массив несколько листов Excel.
Исходники.
Изменено: bedvit - 21.10.2019 10:41:08
«Бритва Оккама» или «Принцип Калашникова»?
Страницы: 1
Наверх