Вопрос или проблема
У меня есть двумерный массив примерно из 100 строк и не более 12 столбцов. Я хочу преобразовать этот массив в накопленное среднее для каждой строки. Другими словами, аккумулятор будет сбрасываться в 0, когда мы начинаем новую строку.
Я придумал решение, которое работает (см. ниже), однако оно слишком медленное. Занимает около 10 секунд для массива 100 x 12. Надеюсь, есть более быстрое решение?
=MAKEARRAY(ROWS(array), 12, LAMBDA(r, c, AVERAGE(DROP(INDEX(array, r), c - 12))))
Ответ или решение
Теория:
Обработка двумерных массивов в Excel требует учета оптимизации формул для повышения производительности, особенно при работе с большими массивами данных. В данном случае речь идет о вычислении накопленного среднего значения по каждой строке массива 100 на 12 элементов. Проблема в том, что используемая формула работает медленно из-за своей сложности и пересчетов. Ключевым моментом является использование более эффективного способа расчета среднего, чтобы уменьшить время обработки.
Пример:
Изначальная формула использует функцию MAKEARRAY, которая создает таблицу, заполняемую формулой LAMBDA. Вложенная функция AVERAGE(DROP(INDEX(array, r), c - 12)) вызывает проблемы с производительностью, поскольку каждый раз выполняется операция DROP, изменяющая массив, что вводит дополнительную вычислительную нагрузку при обращении к значениям через INDEX.
Применение:
Более эффективным подходом будет использование функций Excel, которые уменьшают количество повторных пересчетов. Например, можно применить функции, работающие с диапазонами без излишнего индексирования. Одним из методов является использование AVERAGE совместно с OFFSET или INDIRECT, чтобы минимизировать динамическое изменение размеров диапазона.
Вместо использования DROP и LAMBDA, можно воспользоваться прямым обращением к диапазонам, соответствующим текущей строке:
-
На одной из ячеек строки вы можете использовать следующую формулу, чтобы получить накопленное среднее для всех предыдущих столбцов в строке:
=AVERAGE($A2:A2)Здесь предположено, что первая строка данных начинается со второго ряда и первый столбец – это
A. Копируя эту формулу через все столбцы строки, вы получите накопленные средние для каждого значения в строке. -
Распространите формулу на всю таблицу остальными строками, начиная с первой ячейки каждой строки:
- Выделите ячейку с формулой.
- Распространите формулу вдоль строки и через нужное количество строк.
Этот метод значительно снизит нагрузку на Excel за счет работы с фиксированными диапазонами, что уменьшает объем переиндексирования и общей вычислительной нагрузки. Этот метод позволит быстрее получить результаты для массива 100 на 12.