Установите ArrayFormula во многие ячейки Excel с помощью VBA

ProgramBox

Установите ArrayFormula во многие ячейки Excel с помощью VBA

Post author:admin
Запись опубликована:26 апреля, 2023
Post category:Вопросы по программированию

#excel #vba #array-formulas

#excel #vba #массив-формулы

Вопрос:

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

Чтобы показать вам, что я имею в виду, я использую следующий код:

 With MarginalData
    .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count)).FormulaArray = pullFormula
End With

Чего я хочу, так это результата, который выглядит следующим образом:
желаемый результат

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

Но что я получаю, это: полученный результат

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

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

Формула является:

{=INDEX(BatchResults,MATCH(TTIDamp;CHAR(1)amp;ROW()-1,BatchResultsTTIDSamp;CHAR(1)amp;BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))}

Это должно быть введено как формула массива, потому что она выполняет сопоставление не с одним столбцом, а с двумя объединенными столбцами. Объединение столбцов должно быть возвращено в виде массива, следовательно, формула должна быть введена как формула массива.

Самое простое решение на данный момент, вариант принятого ответа ниже, заключается в следующем:

 Const pullFormula = "=INDEX(BatchResults,MATCH(TTIDamp;CHAR(1)amp;ROW()-1,BatchResultsTTIDSamp;CHAR(1)amp;BatchResultsLayers,0),MATCH(A$1,BatchTTIDData!$1:$1,0))"
With wrksht
    With .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
        .Formula = pullFormula
        .FormulaArray = .FormulaR1C1
    End With
End With

1. Вы должны иметь возможность сделать ‘pullFormula’ зависимым от ячейки, в которой он находится, но для этого нам нужна строка, чтобы показать вам, как это сделать (если это возможно).

2. Как вы можете видеть выше, pullFormula уже зависит от ячейки, в которой он находится. Он учитывает текущую строку и заголовок столбца ( A$1 ), который отличается для каждого столбца.

3. Хороший вопрос. И в соответствии с этой статьей у вас есть наилучший подход

Ответ №1:

Или выберите формулу массива как R1C1, присвоите диапазону как FormulaR1C1, затем назначьте FormulaR1C1 как формулу массива. Предполагается, что формула массива находится в ячейке A2

 Sub test()

With Sheet1
    pullFormula = .Range("A2").FormulaR1C1
    Set Rng = .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))

    Rng.Formula = pullFormula
    Rng.FormulaArray = Rng.FormulaR1C1

End With
End Sub

1. Это решение работает, но происходит самое странное: при установке массива формул равным formular1c1 операция выполняется невероятно медленно — даже медленнее, чем зацикливание всех ячеек вручную, что, по-видимому, происходит, если вы выполняете строку кода с включенным обновлением экрана.

2. @Alain Ой, это довольно медленно в больших диапазонах! Возможно, было бы быстрее использовать мой метод только для первой строки, а затем автоматически заполнять, например, Rng.Назначение автозаполнения:=Rng.Resize(999, Rng.Columns. Количество), введите:=xlFillDefault. Или с помощью Application.Calculation = xlCalculationManual немного быстрее

Ответ №2:

Попробуйте сделать это полуавтоматически. Задайте формулу для первой строки, затем используйте FillDown.

 Private Sub soCopyFormula()

    Dim MarginalData As Worksheet
    Set MarginalData = ActiveWorkbook.Worksheets("Sheet2")
    Dim oRange As Range
    Dim i As Integer

    With MarginalData
        Set oRange = .Range(.Cells(2, 1), .Cells(13, .UsedRange.Columns.Count))
        ' for each column
        For i = 0 To oRange.Columns.Count - 1
            ' set first row
            oRange(1, i).FormulaArray = pullFormula
            ' copy down
            oRange.Columns(i).FillDown
        Next
    End With

End Sub

1. Это решение работает, но оно более запутанное, чем я хочу. Мое временное решение было на самом деле немного проще, чем это, состоящее из установки формулы в одну ячейку, копирования поперек, а затем копирования вниз.

2. @Alain Да, определенно есть обходной путь. На самом деле я не использовал FormulaR1C1, но я запомню это сейчас.

Ответ №3:

Вместо 1 доллара попробуйте

 INDIRECT(ADDRESS(1,COLUMN()))

1. Я не знаю, почему за это так много проголосовали, COL () даже не является допустимой функцией в Excel — функция, которую вы имели в виду, была COLUMN () . Кроме того, ADDRESS возвращает строку «$A$1», «$B $1» и т.д. Функция СОПОСТАВЛЕНИЯ принимает диапазон, а не строку. Размещение этого в функции нарушает ее.

2. Это не все, что в ней не так, чтобы заставить эту функцию работать после преобразования ссылки в строку, приходится использовать КОСВЕННУЮ функцию, которая является изменчивой и означает, что эти дорогостоящие формулы массива будут пересчитываться каждый раз, когда что-либо изменяется в рабочей книге, что ужасно неприемлемо.

3. И даже если кто-то согласится с КОСВЕННОЙ формулой, ваше изменение ВСЕ равно ничего не даст, весь диапазон по-прежнему обрабатывается как имеющий один вывод, и возникает та же повторяющаяся проблема, вместо того, чтобы каждая ячейка получала свое собственное значение.

4. Я менее разочарован вашим ответом и более разочарован тем фактом, что 3 человека бездумно поддержали ответ, который явно был неправильным. Мы все совершаем ошибки, но люди не должны голосовать, если они не проверяют ответы.

5. @Alain, я дал это не как правильный ответ, обратите внимание на слово try . Это была попытка, которую могли протестировать только вы, чтобы помочь вам найти ответ. Иногда подсказка — это все, что мне нужно, когда я ищу ответ.