#excel #vba #excel-formula #volatile #calculation
#excel #vba #excel-формула #изменчивый #вычисление
Вопрос:
Вопрос
Возможно ли эффективно имитировать результат application.calculate из VBA, но игнорируя изменяемые функции?
Подробнее
Определение: Энергонезависимый эквивалент: Для любой энергозависимой книги определите энергонезависимый эквивалент как книгу, в которой все ячейки с энергозависимыми ссылками были заменены их исходными значениями.
Определение: Различия в энергонезависимых вычислениях: Предположим, у нас есть любая рабочая книга. Теперь предположим, что мы взяли его энергонезависимый эквивалент и выполнили для него полный пересчет. Возьмите ячейки, значения которых изменились в энергонезависимом эквиваленте, и их значения. Это различия в энергонезависимых вычислениях.
Вопрос заключается в следующем. Есть ли способ эффективно применять только энергонезависимые различия для любой книги с потенциально изменяемыми ссылками в ней? Ключевым моментом здесь является эффективность — это единственная причина, по которой мы хотим игнорировать изменяемые функции — смотрите Пример использования ниже. Таким образом, любое решение, которое не превосходит полный пересчет, бесполезно.
Пример использования
У нас есть рабочая книга, которая изобилует INDIRECT использованием. INDIRECT является встроенной в Excel изменяемой функцией. В книге их около 300 000, что в общей сложности составляет около 1,5 миллионов используемых ячеек. У нас нет возможности изменить их. В результате этих INDIRECT обычаев пересчет в нашей книге занимает много времени. Около 10 секунд. Итак, мы отключили автоматическое вычисление, и пользователи периодически нажимают вручную на повторный расчет, чтобы обновлять данные по ходу работы.
Наших бизнес-пользователей это устраивает, но некоторые из новых функций VBA, которые мы добавляем, могли бы использовать что-то более эффективное, чем 10-секундное ожидание.
Что мы пробовали до сих пор
Sheet.Calculate— это удобно в некоторых случаях. И мы действительно используем это. Но оно обрабатывает данные на всех других листах как значения, а не формулы. Таким образом, если есть какие-либо зигзагообразные ссылки, это не дает полностью согласованного результата. Например. представьте ссылку с листа A -> листа B -> листа A: тогда нужно было бы вычислить лист A, затем B, затем A. Количество зигзагов произвольно. И это только один случай с двумя листами. Чтобы решить эту проблему, нужно было бы существенно переписать все вычисления Excel.
Ответ №1:
Есть одна вещь, которая приходит мне на ум, и она не идеальна. Возможно, вы можете создать свою собственную функцию, которая не является изменяемой и хранится в электронной таблице, которая выполняла бы именно то, что делает INDIRECT (или любые формулы, которые вы используете). Вы могли бы назвать это INDIRECT2 или что-то в этом роде и просто заменить эти функции в своей электронной таблице.
К сожалению, эта функция недоступна для Application.WorksheetFunction.functionName , но есть способы обойти ее. Это всего лишь моя общая идея.
Комментарии:
1. Хвала идее, но я не думаю, что у нас это сработает! Косвенные ссылки изменчивы по определенной причине: небезопасно предполагать, что ссылки, на которые они ссылаются, в целом не изменились. При замене INDIRECT на энергонезависимый эквивалент некоторые изменения в исходных данных могут не быть приняты. Мы не можем рисковать по всем направлениям, хотя это, вероятно, сработало бы для 299 000 из наших 300 000 ячеек. Святой грааль заключается в том, чтобы иметь возможность эффективно указывать косвенным ссылкам не вычислять, когда мы захотим… но они все равно вычисляются по умолчанию.
2. Это очень хороший момент, именно поэтому я сказал, что он не идеален! У меня есть другая идея 😉 Вы можете создать событие worksheet_change с ячейками, которые влияют на косвенные функции, в качестве целевой области, и всякий раз, когда они меняют значение, повторно вычислять электронную таблицу. Таким образом, вычисление все равно займет столько же времени, но данные будут автоматически обновляться только тогда, когда это необходимо. Немного поработав, вы могли бы сузить область поиска только до затронутых ячеек, чтобы сделать его более эффективным; получите адрес целевой ячейки, найдите его в косвенных эквивалентных функциях и обновите только те, которые затронуты.
3. Это совсем не плохая идея. У меня есть похожая идея. Мы могли бы добавить к энергонезависимому UDF явный параметр, который переоценивает лист / диапазон, на который опирается. Таким образом, если что-либо на этом листе / диапазоне изменяется, то же самое происходит и с косвенным. Это похоже на вашу идею, но не требует никакой обработки событий. Посмотрим. Однако на данный момент у нас нет возможностей для такого рефакторинга, поэтому, я думаю, мы застряли с медленным пересчетом на некоторое время! Спасибо за ваше вмешательство.
4. Мне это нравится, дайте нам знать, когда вы это сделаете, это довольно интересно, и я хотел бы знать, что это работает и эффективно!
Ответ №2:
Использовать энергонезависимую пользовательскую функцию, которая выполняет ту же работу, что и INDIRECT ?
Public Function INDIRECT_NV(ByVal ref_text As String, Optional ByVal a1 As Boolean) As Variant
'Non-volatile INDIRECT function
'Does not accept R1C1 notation - the optional is purely for quick replacement
INDIRECT_NV = CVErr(xlErrRef) 'Defaults to an error message
On Error Resume Next 'If the next line fails, just output the error
Set INDIRECT_NV = Range(ref_text) 'Does the work of INDIRECT, but not for R1C1
End Function
В качестве альтернативы, в зависимости от варианта использования, может оказаться возможным использовать INDEX и MATCH (энергонезависимые функции) для замены INDIRECT запросов