Оцените медленную функцию с помощью функции Match Worksheet, ссылающейся на несколько столбцов

Обычно функция Excel VBA Evaluate - удобный способ получить результат формулы рабочего листа в VBA без необходимости помещать формулу в ячейку рабочего листа. Однако я обнаружил, что если вычисляемая формула представляет собой функцию ПОИСКПОЗ, работающую в нескольких столбцах, это на удивление медленнее - НАМНОГО медленнее, чем вычисление той же формулы в ячейке листа. Например:

MatchingRow = EVALUATE(MATCH(Sheet1!G6&Sheet1!H6&Sheet1!I6,Sheet2!B:B&Sheet2!C:C&Sheet2!D:D,0))

Любая идея, почему это будет намного медленнее, чем это, в ячейке рабочего листа?

=MATCH(Sheet1!G6&Sheet1!H6&Sheet1!I6,Sheet2!B:B&Sheet2!C:C&Sheet2!D:D,0)

Я почти уверен, что слышу, как кто-то уже набирает ответ, в котором говорится, что все это из-за накладных расходов на вызов функции рабочего листа из среды VBA. Но в этом случае у меня следующий вопрос: Почему EVALUATE не работает медленно и для других функций?


excel vba worksheet-function
person Greg Lovern    schedule 22.09.2015    source источник
comment
Если мне приходилось догадываться, это потому, что EVALUATE рассматривает вашу формулу как формулу массива. Ссылки на целые столбцы (например, Sheet2! B: B & Sheet2! C: C & 'Sheet2! D: D) замедляют его. В качестве теста попробуйте установить ссылки на целые столбцы так, чтобы они были ближе к фактическим диапазонам, которые вам нужно оценить, и посмотрите, получите ли вы лучшую производительность.   -  person sous2817    schedule 22.09.2015
comment
Взгляните на это сообщение Чарльза Вильямса. Среди других интересных наблюдений он поддерживает то, что Application.Evaluate вызывается дважды (это ошибка) по сравнению с Worksheetfunction.Evaluate.   -  person Ioannis    schedule 22.09.2015
comment
sous2817: спасибо, что производительность улучшилась примерно в 10 000 раз.   -  person Greg Lovern    schedule 23.09.2015
comment
lennis: Спасибо, это был интересный пост, на который вы связались.   -  person Greg Lovern    schedule 23.09.2015

Ответы (1)


arrow_upward
2
arrow_downward

Пару вещей.

Область видимости важна при использовании Evaluate (). Вызов метода на листе примерно в два раза быстрее, чем простое использование Evaluate (), которое является сокращением для Application.Evaluate ().

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

Эта переработка формулы с использованием булевой логики в качестве альтернативного механизма для получения того же результата формулы выполняется быстрее и намного ближе по времени к формуле, размещенной на листе:

 MatchingRow =  = Sheet1.[MATCH(1,(Sheet1!G6=Sheet2!B:B)*(Sheet1!H6=Sheet2!C:C)*(Sheet1!I6=Sheet2!D:D),0)]

ОБНОВЛЕНИЕ

Для полноты картины вам также следует серьезно подумать об ограничении глубины диапазона поиска. Это значительно ускорит вычисление формулы либо на листе, либо из VBA. Вот та же формула, что и выше, но с диапазоном поиска, ограниченным всего 100 строками. При необходимости отрегулируйте:

Sheet1.[MATCH(1,(Sheet1!G6=Sheet2!B1:B100)*(Sheet1!H6=Sheet2!C1:C100)*(Sheet1!I6=Sheet2!D1:D100),0)]
person Excel Hero    schedule 22.09.2015
comment
Спасибо, использование метода Evaluate на листе было почти в два раза быстрее, чем метод Evaluate приложения, а умножение отдельных логических результатов для каждого из столбцов улучшило производительность примерно в 3 раза. Также это интересный способ использования MATCH. Тем не менее, я должен сказать, что предложение sous2817 об ограничении диапазонов фактическими необходимыми диапазонами вместо ссылки на целые столбцы улучшило производительность примерно в 10 000 раз. Так что я должен был рассмотреть этот ответ или, по крайней мере, его необходимую часть. - person Greg Lovern; 23.09.2015
comment
Что ж, компонент ограничения всегда применяется во всех ситуациях формулы (за исключением очень немногих высокооптимизированных функций, таких как СУММЕСЛИМН). Мой ответ был сосредоточен на вашей конкретной ситуации. - person Excel Hero; 23.09.2015
comment
За исключением того, что компонент ограничения не имел значения (был таким крошечным ударом по производительности, что я этого не заметил) для формулы в ячейке рабочего листа. Я не согласен с вашим обновлением в том смысле, что вы делаете его одинаково важным в ячейке рабочего листа. Это явно не так. Если бы это было так, мой первоначальный вопрос не имел бы смысла. - person Greg Lovern; 23.09.2015
comment
Это не менее важно, и вам следует по возможности избегать ссылок на всю колонку. Тем не менее, вызовы между двумя доменами, Excel и VBA, усиливают неэффективность. Разделение похоже на микроскоп, позволяющий сосредоточиться на конкретной формуле, увеличивая ее (в данном случае замедляя). Внутренняя неэффективность более очевидна. То, что вы не заметили его на стороне листа, не означает, что его там не было. Сделайте это несколько раз на листе, и будет наблюдаться одно и то же попадание. - person Excel Hero; 23.09.2015
comment
Как вы считаете, что это одинаково важно? Если я сделаю это один раз в VBA и один раз на листе, разница в производительности будет примерно в 10 000 раз. Если я сделаю это 10 раз в VBA и 10 раз на листе, разница в производительности все равно будет примерно в 10 000 раз. Если бы я сделал это миллион раз в VBA и миллион раз на листе, я бы все равно ожидал, что разница в производительности будет примерно в 10 000 раз. (продолжение ...) - person Greg Lovern; 23.09.2015
comment
К вашему сведению, если бы я сделал это достаточное количество раз на листе, я в конце концов заметил бы, что это не совсем мгновенно, конечно, но если бы я сделал это столько же раз в VBA, это заняло бы примерно в 10 000 раз больше времени, чем это , следовательно, примерно в 10 000 раз важнее. - person Greg Lovern; 23.09.2015
comment
Вы знакомы с PrecisionCalc? - person Excel Hero; 23.09.2015