Использование функции ВПР(и ДВССЫЛ) в макросе

Автор Tigrik, 16 марта 2022, 01:58

0 Пользователи и 1 гость просматривают эту тему.

mikekaganski

Цитата: sokol92 от 17 марта 2022, 13:54
К сожалению Collection пришлось снять с пробега.
' Тестирование Collection
Sub testCollection
Dim oColl
Dim nmax as Long, i as Long, key as String, t as Long, s as String, v

oColl=New Collection
Nmax=1000
key=String(200, "a")

t=GetSystemTicks()
For i=1 To nmax
    oColl.add i, key & i
Next i   

s="Запись " & nmax & ": " & (GetSystemTicks()-t)
t=GetSystemTicks()

For i=1 To nmax
    v=oColl.Item(key & i)
Next i   

s=s & chr(10) & "Чтение " & nmax & ": " & (GetSystemTicks()-t)
t=GetSystemTicks()

Msgbox s
End Sub


Всего лишь для 1000 записей и чтений время уже 2,5 сек и растет пропорционально квадрату числа итераций. Так что ждать 7 часов для 100000 итераций не стал.  :)

:) Вы очень сильно постарались и сделали максимум для получения corner case: после фикса tdf#144245, для регистронезависимого сравнения строк используется медленная транслитерация. Но при этом сначала сравнивается быстрый хэш, и только для равных хэшей используется медленной полное сравнение.

Ну а быстрый хэш считается по первым шести символам строки. Угадайте, сколько разных хэшей в Вашем коде? ;) И насколько это тестирование показывает реалистичную картину использования в реальных условиях.
С уважением,
Михаил Каганский

sokol92

Цитата: mikekaganski от 17 марта 2022, 17:34Вы очень сильно постарались и сделали максимум для получения corner case:
Честно, не старался и слов таких не знаю.   :)
А пример тот же самый, что и для словаря, и написан тогда же (примерно май 2020). Так что, злого умысла точно не было. :-\
Владимир.

eeigor

Кстати, мой баг-репорт. Приятно, что его пофиксили.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

Tigrik

Цитата: Tigrik от 17 марта 2022, 02:22Так будет создано необходимое количество одномерных массивов-поддиапазонов.
Но, потом, ещё полночи думал - как это создать проиндексированные массивы-поддиапазоны из одного большого массиве, чтобы к ним было удобно обращаться по индексу.
Пришёл к мысли, что, в данном случае, будет удобнее использовать одну строку для "сборки" всего поддиапазона с определенным разделителем между значениями.
Эта строка будет вторым элементом большого массива, где первый элемент - это первый символ, который и определяет этот поддиапазон. Количество строк в этом массиве - количество поддиапазонов.
Сначала, весь Словарь загоняется по строкам-поддипазонам в большой массиве. По первому символу ВПР находит 2 индекс этой таблицы-массива (строку значений в поддиапазоне) и Split-ом "разбирает" его во временный массив, который используется для основного Поиска значения функцией ВПР (VLOOKUP).
"Собрал" пробный макрос:

Sub CreateArrayForVlookup
  Dim oFuncAcc As Object
  Dim arrStrDict() As String
  Dim arrStrDictProba() As String
  Dim strRez As String
  Dim s As String
  Dim lenArrStr As Long
  Dim n As Long

If Not ThisComponent.CurrentSelection.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
oFuncAcc = CreateUNOService("com.sun.star.sheet.FunctionAccess")
s = ""
For n = 0 To 1500
s = s & "=" & ("Проба" & n)
Next
s = REPLACE(s, "=", "", 1, 1)
arrStrDict() = Split(s, "=")
lenArrStr = Ubound(arrStrDict)
ReDim arrStrDictProba(lenArrStr, 1)
For n = 0 To lenArrStr
arrStrDictProba(n, 0) = arrStrDict(n)
arrStrDictProba(n, 1) = n
Next

s = "Проба123"
'=== В двухмерном массиве Поиск с помощью "VLOOKUP" реализуется отлично
strRez = oFuncAcc.callFunction("VLOOKUP", Array(s, arrStrDictProba, 1, True))
n = oFuncAcc.callFunction("VLOOKUP", Array(s, arrStrDictProba, 2, True))
'=== А в одномерном массиве Поиск не получается - выдает ошибку!!!
' strRez = oFuncAcc.callFunction("VLOOKUP", Array(s, arrStrDict, 1, True))

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'========= Создание большого массива (1 000 000 элементов) и поиск с помощью функции "VLOOKUP" в этом массиве =========
lenArrStr = 1000000 : s = ""
ReDim arrStrDict(lenArrStr)
ReDim arrStrDictProba(lenArrStr, 1)
'  Создание строки за 100 000 циклов - около 50 секунд === 1 000 000 - устал ждать - за полтора часа так и не посчиталось!
' For n = 0 To lenArrStr : s = s & "=" & ("Проба" & n) : Next
' arrStrDict() = Split(s, "=")
For n = 0 To lenArrStr '  1 000 000 вычисляет 7 секунд (100 000 - меньше секунды)
' arrStrDictProba(n, 0) = arrStrDict(n) : arrStrDictProba(n, 1) = n
' arrStrDict(n) = "Проба" & n '  Массив для теста Join() и Len()
arrStrDictProba(n, 0) = "Проба" & n : arrStrDictProba(n, 1) = n
Next
' s = JOIN(arrStrDict(), "=")
' n = Len(s)
s = "Проба" & n-1 '   Последнюю строку в 1 000 000 массиве находит за 9 секунд
strRez = oFuncAcc.callFunction("VLOOKUP", Array(s, arrStrDictProba, 1, True))
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End Sub

Но, оказалось, что есть небольшая "засада" - VLOOKUP в макросе не работает с одномерным массивом.
Пришлось создавать двухмерный массив, который в реальности и не нужен.

В макросе, для примера, взято максимальное количество элементов, которое может встречаться в моих поддиапазонах (1500).
А, для теста, решил организовать массив из МИЛЛИОНА элементов. Но создание строки из миллиона элементов устал ждать (за полтора часа - так и просчиталась), 100 тысяч элементов - это 50 секунд.
На каком-то форуме прочитал, что очень большие строки не рекомендуется создавать с помощью присваивание, так как, в этом случае, производятся лишние операции и "разбазаривание" памяти.
Намного лучше - сразу же определить необходимый размер строки и уже пересоздавать её под функцией MID. Не знаю, насколько это верно - не тестировал.

Но, для решения моей задачи, более важно, что создание двухмерного массива из одномерного происходит очень быстро (если миллион элементов: только один двухмерный - за 7 секунд, если ещё и одномерный - то оба за 10 секунд; а мой объем - за доли секунды).
Да, организация массива непосредственно поэлементно намного быстрее, чем создать строку и её "покрошить" в массив. Это позволило мне, все-таки, протестировать строку собранную из миллиона элементов массива - 7 секунд. Но на подсчет длины такой строки ушло около 10 секунд (кстати, получилось около 12 миллионов символов).

VLOOKUP, также, работает очень быстро (миллион значений, а искался последний - за 9 секунд, а в моем максимально большом диапазоне находит меньше, чем за секунду).

Но остается основной вопрос - действительно ли нельзя в функции VLOOKUP в макросе использовать одномерный массив?
Или я, в очередной раз, что-то делаю не так?

---------

Что-то никак не могу найти данные по параметрам для некоторых переменных в basic LO.
Какая может быть максимальная длина строки и максимальное количество элементов в массиве?
Практически, это мне вряд ли пригодится - только если для общего развития.

eeigor

#34
Цитата: Tigrik от 18 марта 2022, 03:53Но остается основной вопрос - действительно ли нельзя в функции VLOOKUP в макросе использовать одномерный массив?
Нельзя. Эта функция призвана работать с диапазоном ячеек, а это всегда массив массивов, даже из одного столбца, а не 1D/2D-массив.
Однако диапазон через его свойство DataArray может принимать как массив массивов, та и 2D-массив (не документировано), а обратно Вы можете получить массив массивов через то же свойство (DataArray). Возможно, это самый быстрый способ преобразования 2D-массива в массив массивов, хотя тут требуется лист в помощь.


Edit:
Судя по Вашим комментариям, я не во всём прав:
arrStrDictProba – 2D-массив, и с ним ВПР работает.

В любом случае обмен данными между массивом и диапазоном должен осуществляться без цикла за один приём.
   aData = oRange.DataArray
где oRange – диапазон из одного столбца (или более).
...("VLOOKUP", Array(s, aData, 1, True))

Видимо, ВПР также может принять 2D-массив (согласно Вашим комментариям)*, как свойство диапазона DataArray (согласно моему замечанию).

* Пишу с телефона в дороге, попробовать возможности нет...
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Цитата: eeigor от 18 марта 2022, 07:23Однако диапазон через его свойство DataArray может принимать как массив массивов, та и 2D-массив (не документировано), а обратно Вы можете получить массив массивов через то же свойство (DataArray). Возможно, это самый быстрый способ преобразования 2D-массива в массив массивов, хотя тут требуется лист в помощь.

Преобразование N-мерного массива в массив массивов - это результат маршалинга из Basic в UNO API. Функции Calc не имеют к этому никакого отношения. Поэтому если и использовать это для преобразования, то примерно так (без дополнительных расходов в Calc):


Function returnItself(x)
 returnItself = x
End Function

Sub UseIt
 Dim a(5,6) As String
 script = ThisComponent.scriptProvider.getScript("vnd.sun.star.script:Standard.Module1.returnItself?language=Basic&location=application")
 b = script.invoke(Array(a), Array(), Array())
 ' b is array(5) of array(6) of String here.
End Sub


Хотя это тоже вопрос, не будут ли расходы в интерпретаторе Basic на выполнение returnItself больше, чем что-то в Calc.
С уважением,
Михаил Каганский

eeigor

#36
Михаил, ввиду определённой сложности этого кода для восприятия, желательно оформить его в виде функции типа ConvertToArrayOfArrays(). И возможно, Владимир найдёт время протестировать её.

"location=application" - это в библиотеке 'My Macros & Dialogs'?

Но выглядит эффектно. Вроде как ничего и не происходит, кроме операции присваивания.

Edit:
Очевидно, что такая функция должна быть в LO Basic по определению, раз требуется этот самый маршалинг.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

#37
Вероятно, наиболее эффективная реализация (и ещё более эффективная при использовании статического конвертера):


Sub TestConversion
 Dim a(5,6) As String
 oConverter = createUnoService("com.sun.star.script.Converter")
 b = oConverter.convertTo(a, "any")
 ' b is array(5) of array(6) of String here.
End Sub


Цитата: eeigor от 18 марта 2022, 09:05Очевидно, что такая функция должна быть в LO Basic по определению, раз требуется этот самый маршалинг.

Она и есть: CreateUnoValue. Но предназначена она исключительно для маршалинга, поскольку её результат предполагается передавать в UNO, и её цель - обеспечить корректность типа. Поэтому на выходе у неё особый тип объекта Basic, который инкапсулирует строго типизированные данные UNO. Использовать эту функцию для преобразования из Basic в Basic нельзя.
С уважением,
Михаил Каганский

Tigrik

Цитата: eeigor от 18 марта 2022, 07:23В любом случае обмен данными между массивом и диапазоном должен осуществляться без цикла за один приём.
eeigor, Вы прямо проинтуичили то, что я хотел узнать - запись данных диапазона в массив. Спасибо Большое.
Как-то, видел это в какой-то из тем, но, сейчас, не могу вспомнить в какой - уже хотел спросить об этом в этой теме.
Теперь, дело у меня пойдет немного веселее - .DataArray намного упрощает алгоритм.

sokol92

Цитата: mikekaganski от 18 марта 2022, 09:47Вероятно, наиболее эффективная реализация (и ещё более эффективная при использовании статического конвертера):
Михаил, спасибо за идею!

Цитата: Tigrik от 18 марта 2022, 12:43Теперь, дело у меня пойдет немного веселее - .DataArray намного упрощает алгоритм.

Я потерял нить происходящего. Чем всё-таки плох словарь? Скоростные характеристики я приводил. Словарю не надо "помогать", разделяя ключи поиска на поддиапазоны и т.д.
Если Вы приведете фрагмент документа с исходными данными, близкими к реальным, то можно будет вести более конкретное обсуждение.
Владимир.

eeigor

Владимир, словарь неплох. Просто автор не успевает усваивать информацию. Надо сделать перерыв... Я словарь тоже не сразу задействовал.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

Tigrik

Цитата: eeigor от 18 марта 2022, 16:30Просто автор не успевает усваивать информацию.
Да, Владимир, eeigor абсолютно прав. На данном этапе, уже близок к завершению вариант с диапазонами, массивами и ВПР - хотелось бы его окончательно доделать.
А, уже затем, приступить к новому инструменту для решения своей задачи.

eeigor

#42
Михаил, спасибо за подсказку с конвертером.
_______
Смысл "обёртки" - сделать более понятным совершаемое действие.
NDArray2AoA: ND - Number of Dimensions (то есть массив с разной размерностью), AoA - Array of Arrays; ну а 2 - это "two" (звучит как "to", что означает "в", при этом визуально отделены левая часть от правой - распространённый приём при именовании конвертеров).
Function NDArray2AoA(aIn()) As Variant
''' Returns: An array of arrays of a given type.

  Dim oConverter As Object, aOut()

  oConverter = createUnoService("com.sun.star.script.Converter")
  aOut() = oConverter.convertTo(aIn, "any")
  NDArray2AoA = aOut
End Function

Sub Test_NDArray2AoA()
  Dim i%(1, 2), d#(1, 2), s$(1, 2), v(1, 2), a(3, 2, 1), AoA()

  AoA = NDArray2AoA(i)  'replace <a> with <d>, <s>, <v> and <a> (3D-Array of Variant)
End Sub

Сделайте останов на последней строке и посмотрите на структуру массива AoA.


Edit:
Работает со всеми типами данных { % | & | ! | # | $ | @ }, кроме Currency.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

eeigor

Михаил, я так понимаю, что процедура демаршалинга не предусмотрена?
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Предусмотрена, и используется при конвертации UNO-значений в значения Basic. То есть используется в коде C++. В Basic она не имеет смысла, поскольку Basic не может работать с данными UNO.
С уважением,
Михаил Каганский