Jack Famous написал: Про QR код только вот это сохранил
там же написано Languages Visual Basic .NET 100.0% Про join это задача интересная и масштабная, если сделать аля аналог SQL. Но настолько же и трудоемкая, а кто этим будет пользоваться, кроме нас, проще sql-команды использовать. Там и условия можно разнообразные задавать, и без пустых и с уникальными... ReDim звучит неплохо, когда-то и до него дойдут руки. Если в VBA еще останутся спецы...
bedvit: Про join это задача интересная и масштабная. Но настолько же и трудоемкая
Вариант на VBA, как я это вижу. Клонировал циклы, чтобы не тратить время на постоянное определение параметров внутри
Код
Option Base 1
Option Explicit
'==================================================================================================
Sub Test()
Dim arr, r&, c&
arr = Array(1, 2, 1, 2, 3, 4)
MsgBox JoinFast(arr, vbLf, , , , True)
ReDim arr(3, 2)
For c = 1 To UBound(arr, 2)
For r = 1 To UBound(arr, 1)
arr(r, c) = r & c
Next r
Next c
MsgBox JoinFast(arr, vbLf, , , 2, True)
End Sub
'==================================================================================================
Function JoinFast(arr, Optional ByVal Sep$ = " ", Optional ByVal LB& = -1, Optional ByVal UB& = -1, Optional nDimGet&, Optional OnlyQniq As Boolean, Optional WithEmpty As Boolean) As String
Dim tx$, res$, n&
Static Dic As Dictionary, fSt As Boolean
If OnlyQniq Then If fSt Then Dic.RemoveAll Else fSt = True: Set Dic = New Dictionary
If nDimGet = 0 Then ' 1D-Array. One Dimension
If LB = -1 Then LB = LBound(arr)
If UB = -1 Then UB = UBound(arr)
If OnlyQniq Then
For n = LB To UB
tx = arr(n): If Dic.Exists(tx) Then GoTo nx1
Dic.Add tx, 0: If Not WithEmpty Then If Len(tx) = 0 Then GoTo nx1
res = res & Sep & tx
nx1:
Next n
Else
For n = LB To UB
tx = arr(n): If Not WithEmpty Then If Len(tx) = 0 Then GoTo nx2
res = res & Sep & tx
nx2:
Next n
End If
Else ' Many Dimensions
If LB = -1 Then LB = LBound(arr, nDimGet)
If UB = -1 Then UB = UBound(arr, nDimGet)
If OnlyQniq Then
For n = LB To UB
tx = arr(n, nDimGet): If Dic.Exists(tx) Then GoTo nx3
Dic.Add tx, 0: If Not WithEmpty Then If Len(tx) = 0 Then GoTo nx3
res = res & Sep & tx
nx3:
Next n
Else
For n = LB To UB
tx = arr(n, nDimGet): If Not WithEmpty Then If Len(tx) = 0 Then GoTo nx4
res = res & Sep & tx
nx4:
Next n
End If
End If
JoinFast = Mid$(res, Len(Sep) + 1)
End Function
'==================================================================================================
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Я использовал sql-запрос и join к таблицам, которые лежат на sql-серваке. Или делал таблицы в Excel и делал join через Power Pivot. Возможно, можно еще через PQ, но я здесь не большой спец, ребята делали через Table.Join
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Тестовый вариант, не оптимизированный, понять стоит ли пилить далее. Прошу потестировать. ArrayFilterV ArrayFilterV(VARIANT* array_in, VARIANT_BOOL array_out_index, LONG key_1, VARIANT value_1, LONG compare_1, VARIANT* array_out) array_in - Массив входящий array_out_index - режим вывода (индексы,массив) 0-по умолчанию массив, 1-массив индексов (пока работает только массив индексов=1) key_1 - номер столбца (начало с 1) value_1 - задать условия - значение compare_1- режим сравнения(compare): 0-по умолчанию, 1-равенство, 2 -не равенство, 3-маска (пока работает вариант 1 и 2) array_out - массив результатов
Тестовый код
Код
Sub TestArrayFilterV()
'Dim bVBA As New BedvitCOM.VBA 'раннее связывание
Dim bVBA As Object: Set bVBA = CreateObject("BedvitCOM.VBA") 'позднее связывание
Dim i, j, t, testSize As Long: testSize = 100
Dim ArrV: ReDim ArrV(1 To testSize, 1 To 9)
Dim arrRes
For i = 1 To testSize
For j = 1 To 9
ArrV(i, j) = CLng(Rnd * 2)
Next
Next
bVBA.ArrayFilterV ArrV, 1, 1, 1, 1, arrRes
'Cells(1, 1).Resize(UBound(arrRes, 1), UBound(arrRes, 2)) = arrRes
End Sub
bedvit, приветствую и спасибо. Пока не очень понятно: в примере не очень хорошо параметры "1, 1, 1, 1" указывать при демонстрации (хотя бы подписать их рядом). Непонятно ни хрена, что, куда и зачем - прошу расписать. Фильтрует ли одномерный? Как это выглядит? Равенство, неравенство, понятно, но для маски тоже нужно не только "соответствует", но и "не соответствует". Также очень полезно будет реализовать поиск всех подстрок в строке: чтобы по переданной маске "маша*вася" или переданному массиву Array("маша", "вася") подошла строка "маша и вася", "вася и маша", "весёлые вася и маша" — и так далее.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Sub TestArrayFilterV()
'Dim bVBA As New BedvitCOM.VBA
Dim bVBA As Object: Set bVBA = CreateObject("BedvitCOM.VBA")
Dim i, j, t, arrRes
Dim testSize1 As Long: testSize1 = 10000000 'строки
Dim testSize2 As Long: testSize2 = 5 'столбцы
Dim ArrV: ReDim ArrV(1 To testSize1, 1 To testSize2)
For i = 1 To testSize1
For j = 1 To testSize2
ArrV(i, j) = CLng(Rnd * 2)
Next
Next
t = Timer
bVBA.ArrayFilterV ArrV, 1, 1, 1, 1, arrRes 'фильтруем по первому столбцу, значения=1
Debug.Print Timer - t
End Sub
Общий пример использования нового функционала
Код
Sub TestArrayFilterV()
'Dim bVBA As New BedvitCOM.VBA
Dim bVBA As Object: Set bVBA = CreateObject("BedvitCOM.VBA")
Dim i, j, t, arrRes
Dim testSize1 As Long: testSize1 = 10 'строки
Dim testSize2 As Long: testSize2 = 5 'столбцы
Dim ArrV: ReDim ArrV(1 To testSize1, 1 To testSize2)
For i = 1 To testSize1
For j = 1 To testSize2
ArrV(i, j) = CLng(Rnd * 2)
Next
Next
Cells.ClearContents
Cells(1, 1).Resize(UBound(ArrV, 1), UBound(ArrV, 2)) = ArrV 'печатаем исходные условия
t = Timer
bVBA.ArrayFilterV ArrV, 1, 1, 1, 1, arrRes 'фильтруем по первому столбцу, значения равными = 1, выводим массив индексов в массив arrRes
Debug.Print Timer - t
bVBA.Array1Dto2D arrRes, 1, 1, 1 'преобразуем в двухмерный с 1 столбцам, нижние границы измерений = 1, порядок данных сохраняется исходный
Cells(1, 7).Resize(UBound(arrRes, 1), UBound(arrRes, 2)) = arrRes 'выводим массив полученных индексов, совпавших с условием по фильтру
bVBA.Array2Dto1D ArrV 'преобразуем ArrV в одномерный (нижняя граница = 0 - по умолчанию)
bVBA.Array1Dto2D ArrV, 1, 1, 10 'преобразуем ArrV в двухмерный с 10ю столбцами, нижние границы измерений = 1, порядок данных сохраняется исходный
Cells(1, 9).Resize(UBound(ArrV, 1), UBound(ArrV, 2)) = ArrV 'печатаем нового размера массив ArrV с сохранением исходного порядка следования элементов
End Sub
bedvit, благодарю - стало понятнее. Вопрос по фильтрации одномерного массива остаётся открытым. Также жду версию с выводом массива значений.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
bedvit, спасибо за внимание к теме, интересное дополнение, к сожалению у меня новые функции не работют, позже буду поразбираться с установкой, наверное в этом дело.
testuser, нужно установить новую xll, по ссылке выше. Сообщение 96. Для этого нужно отключить старую xll, закрыть Excel, открыть Excel, положить в папку настроек и включить новую xll. Если Excel не закрывать, могут остатся линки на СОМ библиотеку и она просто не обновится, т.к. будет доступна только для чтения и xll не сможет распаковать новую.
Jack Famous написал: для маски тоже нужно не только "соответствует", но и "не соответствует".
Разве этого нельзя задать маской?
Цитата
Jack Famous написал: фильтрации одномерного массива остаётся открытым.
Ничего тебе не мешает из одномерного очень быстро сделать двухмерный с 1м столбцом и по нему получить массив индексов.
Цитата
Jack Famous написал: жду версию с выводом массива значений
Это вывод отфильтрованного массива? Это в разработке. Еще в разработке аозможность задать условия фильтра для любого кол-ва столбцов. Наверное будет передаватся массив. Пока не придумал удобного способа.
несколько массивов (или массив массивов): массив номеров столбцов для фильтрации, массив критериев и/или масок, массив = или <> (или $ для перебора) для каждого критерия или маски. Ещё не забыть про возможность отключения чувствительности к регистру - для фильтра каждого из полей.
И придумать, как передавать массив слов для поля, чтобы перебирать вхождение каждого: например, маска "маша*вася*ира" с критерием $ должна означать поиск вхождения каждой из 3ёх подстрок. Если все 3 найдены, то строка соответствует. Аналог сочетания масок "маша*вася*ира" Or "маша*ира*вася" Or "вася*маша*ира" Or "вася*ира*маша" Or "ира*маша*вася" Or "ира*вася*маша".
Как будет время, накидаю на VBA что-то подобное — может тебе проще будет по готовому алгоритму.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: дополнительное действие + медленнее, чем встроенный
в данный момент это копейки, т.к. в моем методе меняются только два свойства массива - количество и размер размерностей. Это пару переменных. Данные в массиве не трогаю. Поэтому это очень быстро вне зависимости от размера массива.
Цитата
Jack Famous написал: несколько массивов (или массив массивов)
думаю это будет один двухмерный массив, строки-условия, столбцы - столбцы
Цитата
Jack Famous написал: Аналог сочетания масок "маша*вася*ира" Or "маша*ира*вася" Or "вася*маша*ира" Or "вася*ира*маша" Or "ира*маша*вася" Or "ира*вася*маша".
опять регулярки. Ну тогда мало кому твой инструмент будет нужен. Регулярки хороши только как дополнительный инструмент фильтрации, но никак не замена маски.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Не хочешь регулярки? Я думал это твои любимые "маски". Простор для творчества просто отличный. Можно их и не использовать, тем более они медленные, а я уже придумал пушку... главное время где взять на реализацию... да и нужно ли это кому, кроме пару-тройке спецов. Думаю так - данный инструмент нужен, что бы закрыть функционал по контейнерам/массивам в VBA/COM полностью. Быстрейшие контейнеры есть, суперсортировка на параллельных алгоритмах есть (с условиями для любого количества столбцов), изменения размера измерений массива есть, преобразования размерностей массива есть (1D,2D), будет сортировка с кучей настраиваемых условий для любого количества столбцов... профит... уже можно переходить с С++ на VBA))
P.S. да забыл.. быстрое транспонирование любого размера массива - тоже есть, без глюков как стандартный.
это точно - комплект впечатляет Только ещё нужно обязательно проработать момент внедрения библы в файл, чтобы человек на любом компе мог юзать макросы разработчика без телодвижений по подключению/отключению и прочего… Мне этого не хватает - автономные файлы пока делаю без библы
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Функция работает, резултат есть ) Правда третий параметр в виде переменной принимает только на раннем связывании, на позднем только в виде прямого указания значения. Добавил проверку в "Общий пример"
Код
Dim Rng As Range, RngRes As Range, Ub As Long, App As Application, fv As Variant
fv = 2 'искомое значение
Set App = Application
Set Rng = Cells(1).Resize(UBound(ArrV, 1), UBound(ArrV, 2))
t = Timer
Set RngRes = Rng.Find(fv) 'без финда ни куда)
If Not RngRes Is Nothing Then
For i = 1 To testSize2
bVBA.ArrayFilterV ArrV, 1, i, fv, 1, arrRes
Ub = UBound(arrRes)
If Ub >= 1 Then
With Rng.Columns(i)
For j = 1 To Ub
Set RngRes = App.Union(RngRes, .Cells(arrRes(j)))
Next
End With
End If
Next
RngRes.Select
End If
Debug.Print "Время формирования диапазона искомых ячеек: " & Timer - t
Stop
testuser, спасибо за тест! Union() очень медленная функция. Сейчас зайдет Алексей и все разложит по полкам! )) С ней лучше не тестировать мои быстрые инструменты
С третьей переменной - посмотрю. Вообще-то разницы не должно быть между ранним или поздним связыванием.
Проблема с задаваемыми параметрами решена, теперь передача по значению или по ссылке обрабатывается правильно (напрямую или через переменную). Так же прошел 1 этап оптимизации функционал по выводу индексов. Обращаю внимание что отсчет индексов идет от границы массива. Если размерность массива начинается с -1 , массив индексов будет начинатся с такой же размаерности и содержать значения индексов начиная с -1. Завтра протестирую. Выложил по исходной ссылке выше.
ArrayFilterV поиск по 10млн. строк с найденными 5 млн. - 4 сек. В улучшенном варианте (1 этап оптимизации) - 1,76 сек. тест:
Код
Sub TestArrayFilterV2()
Dim bVBA As Object: Set bVBA = CreateObject("BedvitCOM.VBA")
Dim i, J, t, arrRes
Dim testSize1 As Long: testSize1 = 100000000 'строки
Dim testSize2 As Long: testSize2 = 1 'столбцы
Dim arrV: ReDim arrV(1 To testSize1, 1 To testSize2)
For i = 1 To testSize1
For J = 1 To testSize2
arrV(i, J) = CLng(Rnd * 2)
Next
Next
t = Timer
bVBA.ArrayFilterV arrV, 1, 1, 1, 1, arrRes 'фильтруем по первому столбцу, значения равными = 1, выводим массив индексов в массив arrRes
Debug.Print Timer - t
Debug.Print UBound(arrRes)
End Sub
bedvit: Union() очень медленная функция. Сейчас зайдет Алексей и все разложит по полкам! ))
изи: нет смысла засекать скорость автомобиля на трассе, если 99% всего времени вы плывёте на барже. В таком случае "скорость" феррари и жигуля будет отличаться на доли процента, что для болида всё-таки, обидно
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Сделал отдельную тему для этого инструмента, что бы не загружать эту. Здесь ведем разговор о том, чего не хватает в Excel, VBA и хотелось бы иметь. Реализациям в отдельных темах.
Это лишь попытка адаптировать новую функцию под затеи, далекие от самолетов, хотя впрочем как выяснилось имеет мало практического смысла, гораздо меньше функций способны обслужить такой сегментный диаппазон, ни массив значений с него ни взтять ни положить, ни скопировать, разве что перебрать циклом, но это лучше сделать с массивом. Говоря же самом ниструменте (в купе с осталным комплектом) это безусловно, что-то ставящее vba на новый уровень. Тут иногда люди справшивают как быстрее распарсить n миллионов строк, наверное это их случай )