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

[sokol92]

Корректировка документа имеет побочные эффекты

[mikekaganski]

Но кажется, что было бы полезно использовать полное имя в INDIRECT, включая имя документа, и тогда это получает смысл?

Такая формула работает:

Код Выделить Развернуть

=INDIRECT("'file:///C:/Temp/test3.ods'#$Sheet1.B1")

Через сервис FunctionAccess - нет.

Да, действительно. Не разрешено автоматическое открытие внешних ссылок, и нет свойства в SpreadsheetDocumentSettings для настройки. Напишете баг-репорт?

Код Выделить Развернуть

   With CreateUnoService("com.sun.star.sheet.FunctionAccess")

Кстати, насчёт with поосторожнее - она у нас с огромным багом.

[sokol92]

tdf#148040

[sokol92]

Кстати, насчёт with поосторожнее - она у нас с огромным багом.

Спасибо за предупреждение!
Я в Calc обычно использую в конструкции With уже созданный объект и использую эту констукцию как "синтаксический сахар".

В Excel конструкция With очень эффективна, поскольку в таких случаях выполняется специальная оптимизация кода, которая может увеличить быстродействие.

[eeigor]

Неслучайно в Excel этой функции нет: она, кмк, не нужна. INDIRECT позволяет автоматизировать действия на листе, но в коде мы должны избегать непрямых (indirect) путей

[Tigrik]

Прямо МИСТИКА какая та!
Хотя, по моему мнению, под мистикой, чаще всего, "скрываются" нераспознанные и неосознаные СВЯЗИ между самыми обычными действиями и процессами.

Попробуйте создать именованный диапазон и использовать это имя при ссылке на диапазон, расположенный на другом листе.

Благодарю, eeigor, за подсказку про именованный диапазон и макросы по функциям.
Создал именованный диапазон - этот вариант работает.
Попробовал свой старый вариант, который во вложенном файле и приведен в сообщение в начале темы - о ЧУДО! - этот вариант также рабочий.
Последние полтора суток, без сна и отдыха, тестировал и проверял различные варианты, а, до этого, два дня теоретически изучал эту тему по форумам, но постоянно одна и таже ошибка на строке «oRangeSecond = oFuncAcc.callFunction("VLOOKUP", Array(ChoiceRange, oRangeFirst, 2, True))».
Вот я и не могу понять, почему рабочий вариант так долго и настойчиво "притворялся" нерабочим? Чтобы создать тему на форуме?

---

Моё предположение: функцию INDIRECT нельзя вызвать через сервис com.sun.star.sheet.FunctionAccess.
Правда, я не знаю, зачем это нужно.

Но я и не стал разбираться, зачем автору это нужно.

Для решения своей задачи у меня есть отдельный лист с ОТСОРТИРОВАННОЙ Базой значений-слов (для простоты я его называю "Словарь"). Она достаточно большая - около 20 тысяч строк и всего один столбец. Эта База используется для поиска какого-то Значения из 100 тысяч ячеек. Но вся База для этого Значения избыточна - его наличие или отсутствие может быть только в определенном ПОДДИАПАЗОНЕ в этой большой Базе. Этот Поддиапазон определяется первым символом в проверяемом Значение.
Поэтому на этом листе со словарем-Базой есть отдельная таблица для поиска этих Поддиапазонов по первому символу Значения (таблица - 2 столбца по 40 строк). Этот механизм намного убыстряет вычисления.
В Calc - это занимает всего две ячейки: в первой - по первому символу определяется поддиапазон; во второй - ДВССЫЛ преобразует эту строку поддиапазона и "отдает" её, в виде ссылки, ВПР для дальнейшего-окончательного поиска в основной таблице, но только в укороченном поддиапазоне.

Теперь, когда для меня решился вопрос реализации функции ВПР в макросе, вопрос с ДВССЫЛ отходит на второй план. Да, как мне думается, с ней (ДВССЫЛ) было бы несколько проще и легче.
Поддиапазон, который будет получен от первой реализации ВПР, преобразуется для использования во второй реализации, но получается, что без помощи ДВССЫЛ.

Большое спасибо всем кто помог мне разобраться в этой теме.

[sokol92]

Самый эффективный путь для макроса - записать Ваш "Словарь" в настоящий словарь. Для 100 000 поисков потребуется несколько секунд.

[Tigrik]

Владимир, Благодарю.
А где будет храниться этот словарь - в памяти (и при каждом запуске макроса он будет пересоздаваться) или его можно "выложить" на лист, чтобы его "считать" при необходимости?

[mikekaganski]

записать Ваш "Словарь" в настоящий словарь

Или воспользоваться коллекцией Basic.

[Tigrik]

Или воспользоваться коллекцией Basic.

Михаил, Благодарю.
Попробую разобраться.

[eeigor]

В Calc - это занимает всего две ячейки: в первой - по первому символу определяется поддиапазон; во второй - ДВССЫЛ преобразует эту строку поддиапазона и "отдает" её, в виде ссылки, ВПР для дальнейшего-окончательного поиска в основной таблице, но только в укороченном поддиапазоне.

Искать можно и с помощью INDEX (ИНДЕКС) + MATCH (ПОИСКПОЗ) вместо VLOOKUP (ВПР).
Обратите внимание на 4-й параметр функции INDEX:
INDEX(Reference [; Row [; Column [; Range]]])
Range (необязательный параметр): индекс поддиапазона в случае ссылки на составной диапазон. Материал может показаться сложным.

https://help.libreoffice.org/7.3/ru/text/scalc/01/04060109.html#hd_id3151221

Самый эффективный путь для макроса - записать Ваш "Словарь" в настоящий словарь.

@Tigrik, вот Вам вариант использования словаря с типами данных для ключа и значения String-String, но можно использовать и другие типы (числовой, массив).
   .EnumerableMap.create("string", "string")  'KeyType, ValueType

Показано, как загрузить и как перебрать значения. Вероятно, с листа можно загрузить всё разом, а не в цикле - надо смотреть документацию.
Edit:
Но вот я не вижу соответствующего метода для загрузи целого диапазона данных одним присвоением. Python dict позволяет загружать данные.

Код Выделить Развернуть

Sub DumpActionTitles() As Object
''' Assume only 2 UI locales can be used: "en-US" or "ru".
''' An enumerated map is like an associative array (Python dict).
'''
Dim aData  'action titles for en-US & ru UI locales
' Some of action titles.
aData = Array( _
Array("Insert Sheet", "Вставить лист"), _
Array("Append sheet", "Добавить лист"), _
Array("Delete Sheets", "Удалить листы"), _
Array("Rename Sheet", "Переименовать лист"), _
Array("Move Sheets", "Переместить листы"), _
Array("Copy Sheet", "Копировать лист"), _
Array("Protect sheet", "Установить защиту листа"), _
Array("Unprotect sheet", "Снять защиту листа"), _
Array("Hide sheet", "Скрыть лист"), _
Array("Show Sheet", "Показать лист"), _
Array("Color Tab", "Цветной ярлык"), _
Array("Input", "Ввод"), _
Array("Insert", "Вставить"), _
Array("Delete", "Удалить"), _
Array("Copy", "Копировать"), _
Array("Move", "Переместить"), _
Array("Merge", "Объединить"), _
Array("Split", "Разбить"), _
Array("Fill", "Заливка"), _
Array("Group", "Группировать"), _
Array("Ungroup", "Разгруппировать"), _
Array("Hide details", "Скрыть детали"), _
Array("View Details", "Показать детали"), _
Array("Clear Outline", "Очистить разметку"), _
Array("Increase Indent", "Увеличить отступ"), _
Array("Decrease Indent", "Уменьшить отступ"), _
Array("Sort", "Сортировка"), _
Array("Row height", "Высота строки"), _
Array("Optimal Row Height", "Оптимальная высота строки"), _
Array("Column Width", "Ширина столбца"), _
Array("Optimal Column Width", "Оптимальная ширина столбца"), _
Array("Print range", "Диапазон печати"), _
Array("Edit Page Style", "Изменить стиль страницы"), _
Array("Change Database Range", "Изменить диапазон базы данных"), _
Array("Edit range names", "Изменить имена диапазонов"), _
Array("Attributes", "Атрибуты"), _
Array("Attributes/Lines", "Атрибуты/Линии"), _
)

Dim oMap As Object, i%, elements(), key$, value$
' Create the map & fill it.
oMap = com.sun.star.container _
.EnumerableMap.create("string", "string")  'KeyType, ValueType

For i = LBound(aData) To UBound(aData)
elements = aData(i)
key = elements(0)  'en-US action title
value = elements(1)  'ru action title
oMap.put(key, value)
Next

key = "Insert Sheet"  'e.g.
If oMap.containsKey(key) Then
MsgBox """" & key & """ (""" & oMap.get(key) & """) exists" _
, 0, "Check Action Title"
End If

Dim enum As Object, elem As Object, s$
s = "en-US  –  ru"
enum = oMap.createElementEnumeration(False)
Do While enum.hasMoreElements
elem = enum.nextElement  '-> com.sun.star.beans.Pair
' MsgBox "Key:  " & elem.First & Chr(10) & "Value:  " & elem.Second
s = s & Chr(10) & elem.First & "  –  " & elem.Second
Loop

MsgBox s & Chr(10) _
& Chr(10) & "Etc. Choose or find yourself what actions you need", 0, "Action Titles"
oMap.clear
End Sub

Или воспользоваться коллекцией Basic.

Главное неудобство - это ключ надо знать, а вот получить список ключей нельзя. Что загрузили, с тем и работаем.
Использую коллекцию для удаления дубликатов, поскольку ключ уникален.
Но, вроде как, Вам тоже подойдёт. Проверено: работает без сбоев. Проще, чем словарь типа EnumerableMap.
В прикреплённом файле Вы найдёте порядок использования объекта Collection. Успехов.

[Tigrik]

eeigor, ещё раз, Огромное Спасибо.
Со Словарями и Коллекцией я буду разбираться.
Пока, я об этих механизмах, вообще, ничего не знаю. Но, предполагаю, что это типа массив массивов или массив с поддиапазонами. И со своей особой структурой.

Но дело в том, что с ВПР в макросах я разобрался и оказалось всё не так и сложно. С ДВССЫЛ, пока, не понятно, но получается и без него.
На текущий момент, буду реализовывать наиболее понятный мне вариант - через ВПР, но поиск будет по массивам данных, который создается на основе диапазонов на листе.
В каждом "словаре" (таких словарей несколько и они находятся на разных листах файла) есть одна первоначальная таблица-диапазон, где располагаются поддиапазоны для этого конкретного словаря. Такие таблицы стандартные для каждого словаря за исключением количества строк. Будут использоваться три столбца из этой таблице: первый символ; порядковый номер строки в этой таблице; количество строк этого поддиапазона.
Вот эту таблицу в макросе переносится в массив (3 столбца и необходимое количество строк). Это, естественно будет в цикле и для каждой строки будет вложенный цикл, где будет создаваться массивы поддиапазонов (одномерный массив: количество элементов - количество строк в конкретном поддиапазоне) - из ячеек на листе. Так будет создано необходимое количество одномерных массивов-поддиапазонов. Возможно, что это как-то похоже на Словарь или Коллекцию, но намного проще (для моей текущей задачи - этого достаточно).

Пытаюсь понять (проинтуичить) насколько этот вариант будет практичнее и быстрее, чем если поиск из макроса будет проводиться по таблицам на листе.
Первый вариант - не надо будет постоянно "прыгать" из макроса в Calc, но нужно будет время для создания массивов с данными и "ворочать" их при поиске значений.
Второй вариант - всё наоборот: макросу не нужно создавать объемные массивы, но будет постоянное обращение из макроса к таблицам на листе. И сам макрос по этому варианту проще.
Наверное, только сравнение этих двух вариантов в работе поможет выяснять кто «Быстрее, выше, сильнее!». Сначала, попробую сделать первый вариант, а, затем, второй.

Искать можно и с помощью INDEX (ИНДЕКС) + MATCH (ПОИСКПОЗ) вместо VLOOKUP (ВПР).

Очень интересный вариант - то же попробую.

[sokol92]

А где будет храниться этот словарь - в памяти (и при каждом запуске макроса он будет пересоздаваться) или его можно "выложить" на лист, чтобы его "считать" при необходимости?

Я здесь выкладывал тест быстродействия словаря. Создание словаря на основе диапазона ячеек в 40 000 строк займет 1-2 сек. 100 000 чтений это тоже 1-2 сек.

Или воспользоваться коллекцией Basic.

К сожалению 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 итераций не стал.  

Кстати, в Excel VBA гораздо чаще используют словарь Dictionary, чем Collection. Словарь "знает" массив своих ключей, в отличие от коллекции.
В быстродействии словарь в Excel VBA опережает коллекции, если число ключей не больше 1 000 000 (примерно). Далее словари начинают "подтормаживать". Связано это, скорее всего, с небольшой длиной ключа хеширования (ключ хеша можно подсмотреть в свойствах элемента словаря) - все-таки программы писались очень давно...

[eeigor]

Словарь можно создать типа Immutable, тогда, вероятно, будет работать ещё шустрее. Мы ведь не меняем значения в словаре, а только, записав единожды, извлекаем.

[sokol92]

Словарь можно создать типа Immutable

Да, но я это не тестировал (поскольку и так хорошо).