Поделиться через

Формула и Формула 2

Range.Formula и Range.Formula2 — это два разных способа представления логики в формуле. Их можно представить на двух диалектах языка формул Excel.

Excel всегда поддерживал два типа вычисления формул: неявное вычисление пересечения ("IIE") и вычисление массива ("AE"). До появления динамических массивов IIE использовался по умолчанию для формул ячеек, в то время как AE использовался везде (условное форматирование, проверка данных, формулы массива CSE и т. д.).

Основное различие между двумя формами оценки заключается в том, как они ведут себя при передаче многоэлементного диапазона (например, A1:A10) функции, которая ожидала одно значение:

  • IIE выберет ячейку в той же строке или столбце, что и формула. Эта операция называется "неявным пересечением".
  • AE вызывает функцию с каждой ячейкой в диапазоне с несколькими ячейками и возвращает массив результатов. Эта операция называется "лифтинг".

Если range.Formula используется для задания формулы ячейки, для оценки используется IIE.

С появлением Dyanamic Arrays ("DA") Excel теперь поддерживает возврат нескольких значений в сетку, и AE теперь используется по умолчанию. Формулы AE можно задать или прочитать с помощью Range.Formula2, который заменяет Range.Formula. Тем не менее, чтобы упростить обратную совместимость, Range.Formula по-прежнему поддерживается и будет продолжать задавать и возвращать формулы IIE. Набор формулы с помощью Range.Formula вызовет неявное пересечение и никогда не может разлиться. Формула, считываемая с помощью Range.Formula, будет по-прежнему молчать о месте неявного пересечения.

Range.Formula фактически сообщает о том, что будет представлено в строке формул в Excel до DA, а Range.Formula2 — формуле, сообщаемой строкой формул в DA Excel.

Excel автоматически преобразуется между этими двумя вариантами формул, поэтому их можно считывать и задавать. Чтобы упростить перевод из Range.Formula (с помощью IIE) в Range.Formula2 (AE), Excel будет указывать, где может происходить неявное пересечение с помощью нового оператора неявного пересечения @. Аналогичным образом, чтобы упростить преобразование из Range.Formula2 (с помощью AE) в Range.Formula (с помощью IIE), Excel удалит операторы @, которые будут выполняться автоматически. Часто между ними нет разницы.

Перевод с Range.Formula на Range.Formula2

В этом примере показан результат настройки Range.Formula, а затем получения Range.Formula2

Dim cell As Range
Dim str As String

Set cell = Worksheets("Sheet1").Cells(2, 1)
ArrayOfFormulas = Array("=SQRT(A1)", "=SQRT(A1:A4)")

For i = LBound(ArrayOfFormulas) To UBound(ArrayOfFormulas)
 cell.Formula = ArrayOfFormulas(i)
 str = "Wrote Range.Formula:" & vbCr & cell.Formula & _
    vbCr & vbCr & _
    "Read Range.Formula2:" & vbCr & cell.Formula2
 MsgBox (str)
Next i
Запись Range.Formula Чтение Range.Formula2 Заметки
=SQRT(A1) =SQRT(A1) Идентично, так как неявное пересечение не могло произойти
=SQRT(A1:A4) =SQRT(@A1:A4) SQRT ожидает одно значение, но получает многоэлементный диапазон. Это активирует неявное пересечение в IIE, поэтому преобразование в AE вызывает, где может происходить неявное пересечение с помощью оператора @

Перевод с Range.Formula2 на Range.Formula

Формула, заданная с помощью Range.Formula2 в Excel используется AE. При сохранении файла DA Excel проверяет формулы в книге, чтобы определить, будут ли они вычислять одинаковые в AE и IIE. В этом случае excel может сохранить его в виде IIE, чтобы уменьшить количество формул массива, которые можно увидеть в версиях Excel, предшествующих DA. Вы можете проверить, будет ли формула сохранена в файле в виде формулы массива, используя Range.SavedAsArray()

Dim cell As Range
Dim str As String

Set cell = Worksheets("Sheet1").Cells(2, 1)
ArrayOfFormulas = Array("=SQRT(A1)", "=SQRT(@A1:A4)", "=SQRT(A1:A4)", "=SQRT(A1:A4)+SQRT(@A1:A4)")

For i = LBound(ArrayOfFormulas) To UBound(ArrayOfFormulas)
 cell.Formula2 = ArrayOfFormulas(i)
 str = "Wrote Range.Formula2:" & vbCr & cell.Formula2 & _
    vbCr & vbCr & _
    "Read Range.Formula:" & vbCr & cell.Formula & _
    vbCr & vbCr & _
    "Read Range.IsSavedAsArray:" & vbCr & cell.SavedAsArray
 MsgBox (str)
Next i
Запись Range.Formula2 Чтение Range.Formula Чтение Range.SavedAsArray Заметки
=SQRT(A1) =SQRT(A1) FALSE SQRT ожидает одно значение, а A1 — одно значение. Поэтому нет различий между IIE и AE. Сохранить как IIE и удалить все @'s
=SQRT(@A1:A4) =SQRT(A1:A4) FALSE SQRT ожидает одно значение, @A1:A4 является одним значением. Поэтому нет различий между IIE и AE. Сохранить как IIE и удалить все @'s
=SQRT(A1:A4) =SQRT(A1:A4) TRUE SQRT ожидает одно значение, A1:A4 — многоэлементный диапазон. IIE и AE могут отличаться, поэтому сохранить как массив
=SQRT(A1:A4)+ SQRT(@A1:A4) =SQRT(A1:A4)+ SQRT(@A1:A4) TRUE Первый SQRT ожидает одно значение, A1:A4 является многоэлементным диапазоном. IIE и AE могут отличаться, поэтому сохранить как массив

Рекомендация

Если используется версия Excel da, следует использовать Range.Formula2 в качестве параметра Range.Formula.

Если используется предварительная и post DA версии Excel, следует продолжать использовать Range.Formula. Если же вы хотите жестко контролировать внешний вид формулы в строке формул пользователей, следует определить, является ли . Формула 2 поддерживается и, если да, используйте . Формула 2 в противном случае используйте . Формула

Заметки

OfficeJS не включает Range.Formula2. Вместо этого Range.Formula всегда сообщает о том, что присутствует в строке формул. Как новый язык с возможностью быстрого развертывания обновлений для надстроек, разработчикам рекомендуется обновлять свои надстройки, если у них возникают проблемы совместимости между AE и IIE.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.