Глобальный массив в памяти Excel?

Question

У меня есть большой лист Sheet1 в моем Excel, используя CSE я фильтруется его и генерировать массив в диапазоне имени filtered_result в Sheet2 filtered_result будет впоследствии используется многими запросами в Sheet3 до Sheet10 в основном по VLookUp и т.д., все работает отлично.

filtered_result в Sheet2 большой и его вычисление уже медленно, как промежуточный результат , что ускоряет вычисления в Sheet3 до Sheet10

Теперь я хотел бы удалить Sheet2 , чтобы избежать визуализации промежуточного результата. Это все еще возможно, так как я мог бы изменить формулу с Sheet3 до Sheet10 , но тогда Excel становится довольно медленным.

Есть ли способ , чтобы каким - то образом сохранить оригинальный промежуточный результат filtered_result в Sheet2 как что - то в памяти, и более поздние запросы будут все еще быть в состоянии получить его?

Answer 1

Не используйте формулы массива, используйте регулярные формулы.

Например, допустим, у вас есть формула, которая добавляет столбец B, если значение в столбце A больше 10. Не создавайте формулу массива с условным условием для этого. Вместо этого создайте новый столбец C с формулой:

=If( $A > 10, $B, 0 )

Таким образом, третий столбец содержит либо значение B, если условие выполнено, либо 0, если оно не выполнено. Теперь просто сложите столбец C. Этот метод обычно намного быстрее, чем формула массива. Это называется "вспомогательный столбец".

Если вычисление все еще выполняется медленно, используйте макросы или VBA для вычисления значений; таким образом, вам нужно только вычислить их один раз, а не пересчитывать их, если что-то не изменится. Таким образом, в приведенном выше примере вместо условной формулы мы можем использовать макрос (или код VBA) для вычисления формулы If и помещения результата в ячейку. Затем запускайте макрос всякий раз, когда вам нужно вычислить таблицу и сгенерировать значения. Если в самой электронной таблице нет условных выражений (операторов If), она будет вычисляться намного быстрее.

Как сделать все в памяти

Если вы хотите полностью избавиться от листа, вы можете делать все в памяти, используя статические переменные в модуле кода VBA:

Dim MyArray(1000, 2000) As Double

Sub computeMyArray()
   ... compute all the values of MyArray
End sub

Function GetValueFromMyArray( Dim x as Integer, Dim y as Integer )
   GetValueFromMyArray = MyArray( x, y )
End Function

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

=GetValueFromMyArray( 5, 6 )

Это приведет к получению 5-й строки, 6-го массива, который находится в памяти.

Answer 2

Самое простое и глупое решение - просто скрыть лист Sheet2 (ссылка).

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

Answer 3

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

Public Sub MakeArrayGlobal(byRef Range as Range)
    dim MyPublicArray() as Variant
    with Range
        counter = 0
        For each cell in range
            MyPublicArray(counter) = cell.value #Or whatever you need
            counter = counter + 1
        Next
    End with
End sub