ArrayFormula of Average on Infinite Truly Dynamic Range в Google Таблицах

Question

как например:

     A       B      C     D     E     F     G     ∞
  |======|=======|=====|=====|=====|=====|=====|=====
1 |      |AVERAGE|     |     |     |     |     |        
  |======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 |       |   1 |   2 | 0.5 |  10 |     |        
  |======|=======|=====|=====|=====|=====|=====|=====
3 | xx 2 |       |   7 |   1 |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
4 |      |       |   0 |     |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
5 | xx 3 |       |   9 |   8 |   7 |   6 |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
6 | xx 4 |       |   0 |   1 |   2 |   1 |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
7 |      |       |   1 |     |   4 |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
8 | xx 5 |       |     |     |     |     |     |       
  |======|=======|=====|=====|=====|=====|=====|=====
9 |      |       |     |     |     |     |   5 |           
  |======|=======|=====|=====|=====|=====|=====|=====
∞ |      |       |     |     |     |     |     |       

какой наиболее оптимальный способ получить AVERAGEдля каждой действительной строки в динамическом смысле терминов (неизвестное количество строк и неизвестное количество столбцов)?

Answer 1

ЗАПРОС

1-й уровень:

если все 5 ячеек в диапазоне C2: G имеют значения:

=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )

если нет, то строки пропускаются:

если пустые ячейки считаются нулями:

=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))

для удаления нулевых значений мы используем IFERROR(1/(1/...))обертку:

=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, 
 "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))

чтобы сделать Colссылки динамическими, мы можем:

=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, 
 "select "&
 "("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)), 
 "offset 1", ))))

---

уровень 2:

если пустые ячейки не считаются нулями и пропускать их нельзя:

=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I), 
 "select "&TEXTJOIN(",", 1, IF(A2:A="",,
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)

обратите внимание, что это зависит от столбца A, поэтому отсутствующие значения в столбце A смещают результаты

забавный факт !! мы можем переключиться avgна maxили min:

чтобы освободить его от ограничения столбца A и заставить его работать для любой допустимой строки:

=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
 IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:G),,9^9)))="", C2:G*0, C2:G)), 
 "select "&TEXTJOIN(",", 1, 
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)

если в диапазоне присутствуют 0, не следует усреднять, мы можем добавить небольшой оператор IF:

=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
 IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(C2:G>0, C2:G, )),,9^9)))="", C2:G*0, 
 IF(C2:G>0, C2:G, ))), 
 "select "&TEXTJOIN(",", 1, 
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)

здесь мы использовали так называемое «вертикальное разбивание запросов», которое берет все значения в заданном диапазоне и концентрирует их в одном столбце, где все ячейки в каждой строке объединяются с пустым пространством в качестве побочного продукта:

=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))

кроме этого, есть еще "горизонтальный разбой запросов" :

=QUERY(C2:G,,9^9)

а также "окончательный двойной запрос на 360 °", который помещает все ячейки из диапазона в одну единственную ячейку:

=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)

и, наконец, "печально известное отрицательное двойное обращение с двойным запросом на 360 °", в котором столбцы отдают приоритет строкам:

=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)

все имена запросов, конечно, защищены авторским правом

возвращаясь к теме ... как упоминалось выше, все ячейки в строке в диапазоне объединяются с пустым пространством, даже если эти пустые ячейки, поэтому мы получили ситуацию, когда мы получаем двойные или множественные пробелы между значениями. чтобы исправить это, мы используем TRIMи вводим простой IFоператор для присвоения 0 значений пустым строкам в заданном диапазоне, например. для противодействия смещению:

---

MMULT

Уровень 3:

MMULT- это своего рода формула тяжелого класса, которая может выполнять сложение, вычитание, умножение, деление, даже в целом по массивам / матрицам ... однако, чем больше набор данных, тем медленнее расчет формулы (потому что MMULTдаже в пустых строках требуется время для выполнения + - × ÷операции ) ... если мы не используем действительно бесконечный динамический диапазон в обоих направлениях ...

чтобы получить последнюю строку со значениями заданного диапазона:

=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
 INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))

чтобы получить последний столбец со значениями заданного диапазона:

=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))

теперь мы можем построить его простым способом:

=INDIRECT("C2:"&ADDRESS(9, 7))

что то же самое, что:

=INDEX(INDIRECT("C2:"&ADDRESS(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
 INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))), 
 MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))))

или более короткая альтернатива:

=INDEX(INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))

поэтому упрощенная формула MMULT будет:

=ARRAYFORMULA(IFERROR(
 MMULT(N(   C2:G9),           ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
 MMULT(N(IF(C2:G9<>"", 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))

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

=ARRAYFORMULA(IFERROR(
 MMULT(N(   C2:G9),         ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
 MMULT(N(IF(C2:G9>0, 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))

уровень 4:

собрать все вышеперечисленное, чтобы сделать его бесконечно динамичным и по-прежнему ограниченным допустимым набором данных:

=INDEX(IFERROR(
 MMULT(N(   INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))),           ROW(INDIRECT("C1:C"&
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)/
 MMULT(N(IF(INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))<>"", 1, )), ROW(INDIRECT("C1:C"&
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)))

опять же, не считая ячеек с нулями в диапазоне:

---

почетные упоминания:

@ Уровень Эрика Тайлера :

полярная противоположность предыдущей формулы будет запустить MMULTна

• общая площадь вместоC2:? (all rows, all columns)
• действительная область, которая позволяет избежать массовых расчетовC2:? (excluding empty rows and columns)0 × 0 = 0

включая нули:

=INDEX(IFERROR(
 MMULT(   INDIRECT("C2:"&ROWS(C:C))*1,         SEQUENCE(COLUMNS(C2:2))^0)/ 
 MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"", 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))

без нулей:

=INDEX(IFERROR(
 MMULT(   INDIRECT("C2:"&ROWS(C:C))*1,       SEQUENCE(COLUMNS(C2:2))^0)/ 
 MMULT(IF(INDIRECT("C2:"&ROWS(C:C))>0, 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))

@kishkin уровень:

для фиксированного диапазона среднего будет:C2:G9MMULT

=INDEX(IFERROR(
 MMULT( C2:G9*1,    FLATTEN(COLUMN(C:G))^0)/ 
 MMULT((C2:G9>0)*1, FLATTEN(COLUMN(C:G))^0)))

=INDEX(IFNA(VLOOKUP(ROW(C2:C), 
 QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&C2:J), "×"),
 "select Col1,avg(Col2)
  where Col2 is not null
  group by Col1"), 2, )))

@MattKing уровень:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
 "select avg(Col2) 
  group by Col1  
  label avg(Col2)''"))

без нулей:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
 "select avg(Col2)
  where Col2 <> 0 
  group by Col1  
  label avg(Col2)''"))

включая пустые ячейки:

=INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)*1), "×"),
 "select avg(Col2)
  group by Col1  
  label avg(Col2)''"))))

Answer 2

Вы вкладываете в это кучу времени. Я надеюсь, что люди это оценят, тем более, что вы сделали это для всех, а не для себя.

Глядя на ваши окончательные формулы, они должны дать те же результаты (укажите данные в C2 :?, как в ваших примерах):

В B2 (включая нули):

=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"",1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))

В B2 (без нулей):

=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>0,1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))

Answer 3

ОБНОВЛЕНИЕ: я обновил формулу из своего исходного сообщения. ROW () всегда должен идти первым, чтобы отсутствующие значения в данных не приводили к разбиению.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))

Должно работать, если я не понимаю вопрос.

Нет необходимости в vlookups, mmults, фильтрах или чем-то еще.

Answer 4

Я постараюсь сделать небольшое дополнение к ответу @ player0. И я буду очень признателен за любые комментарии по оптимизации этого.

---

Если внутри диапазона данных много пустых строк и столбцов, их также можно исключить MMULT.

Шаг 1. Отфильтруйте пустые строки

У нас есть диапазон данных: от C2конца до последней строки и справа до последнего столбца (который есть J:J). Я буду использовать C2:K, см. Подробности ниже для объяснения.

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

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)

Итак, чтобы отфильтровать пустые строки из диапазона данных, который мы используем, FILTERкоторый проверит, находится ли строка в нашем массиве сверху, и уйдет, если будет в этом случае:

=ARRAYFORMULA(
  FILTER(
    C2:K*1,
    MATCH(
      ROW(C2:K),
      UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
      0
    )
  )
)

Шаг 2. Отфильтруйте пустые столбцы

Чтобы получить массив только непустых номеров столбцов, мы можем использовать почти ту же формулу:

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)

Почему SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))используется, а не COLUMN(C2:K)подробнее см. В конце.

Чтобы отфильтровать пустые столбцы, мы также используем условие FILTERwith MATCHдля поиска номеров столбцов в нашем массиве:

=ARRAYFORMULA(
  FILTER(
    C2:K*1,
    MATCH(
      SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      0
    )
  )
)

А чтобы отфильтровать пустые строки и пустые столбцы, мы просто используем два FILTERs:

=ARRAYFORMULA(
  FILTER(
    FILTER(
      C2:K*1,
      MATCH(
        ROW(C2:K),
        UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
        0
      )
    ),
    MATCH(
      SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      0
    )
  )
)

Исходный диапазон данных внутренне станет:

Шаг 3 - Сделайте MMULT

Теперь мы можем использовать MMULTэтот набор данных для вычисления среднего:

=ARRAYFORMULA(
  MMULT(
    FILTER(
      FILTER(
        C2:K*1,
        MATCH(
          ROW(C2:K),
          UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
          0
        )
      ),
      MATCH(
        SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
        UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
        0
      )
    ),
    SEQUENCE(
      ROWS(
        QUERY(
          UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
          "WHERE Col1 <> 0"
        )
      ),
      1,
      1,
      0
    )
  ) /
  MMULT(
    FILTER(
      FILTER(
        (C2:K <> "")*1,
        MATCH(
          ROW(C2:K),
          UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
          0
        )
      ),
      MATCH(
        SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
        UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
        0
      )
    ),
    SEQUENCE(
      ROWS(
        QUERY(
          UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
          "WHERE Col1 <> 0"
        )
      ),
      1,
      1,
      0
    )
  )
)

Это немного не так в отношении исходных строк данных.

Шаг 4 - Заполните столбец СРЕДНЕЕ

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

=ARRAYFORMULA(
  IFNA(VLOOKUP(
    SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
    {
      QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
      MMULT(
        ...
      ) /
      MMULT(
        ...
      )
    },
    2,
    0
  ))
)

Где

• SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))представляет собой массив номеров строк от 2-го до последнего непустого. Мы не будем заполнять все строки пустыми строками.
• QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")представляет собой массив непустых номеров строк с 0отфильтрованными номерами, которые используются в качестве ключей для поиска.
• IFNA вернет пустую строку для размещения рядом с пустой строкой данных.

ЗАКЛЮЧИТЕЛЬНАЯ ФОРМУЛА

Собираем все вместе:

=ARRAYFORMULA(
  IFNA(VLOOKUP(
    SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
    {
      QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
      MMULT(
        FILTER(
          FILTER(
            C2:K*1,
            MATCH(
              ROW(C2:K),
              UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
              0
            )
          ),
          MATCH(
            SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
            UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
            0
          )
        ),
        SEQUENCE(
          ROWS(
            QUERY(
              UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
              "WHERE Col1 <> 0"
            )
          ),
          1,
          1,
          0
        )
      ) /
      MMULT(
        FILTER(
          FILTER(
            (C2:K <> "")*1,
            MATCH(
              ROW(C2:K),
              UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
              0
            )
          ),
          MATCH(
            SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
            UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
            0
          )
        ),
        SEQUENCE(
          ROWS(
            QUERY(
              UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
              "WHERE Col1 <> 0"
            )
          ),
          1,
          1,
          0
        )
      )
    },
    2,
    0
  ))
)

---

Несколько деталей

• INDEXможет использоваться вместо ARRAYFORMULAдля краткости (спасибо @ player0, научил меня этому несколько месяцев назад), но мне нравится однозначность ARRAYFORMULA.
• Я использую SEQUENCEдля создания столбца или строки 1s, чтобы быть явным, для ясности. Например, этот

SEQUENCE(
  ROWS(
    QUERY(
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      "WHERE Col1 <> 0"
    )
  ),
  1,
  1,
  0
)

можно заменить на

SIGN(
  QUERY(
    UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
    "WHERE Col1 <> 0"
  )
)

что немного короче. @ Player0 также демонстрирует способ возведения в степень 0:

QUERY(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
  "WHERE Col1 <> 0"
)^0

но (это всего лишь мои предположения) я думаю, что SEQUENCEвнутренняя реализация должна быть проще, чем операция возведения в степень.

• Я использую диапазон, C2:Kкоторый на один столбец больше, чем существует на листе. Он не только дает диапазон всех столбцов справа C2и всех строк ниже, но также обновляется в случае добавления еще одного столбца справа от листа: демонстрация . Хотя это не особо выделяется. Это C2:Kможет почти идеально (возникнет проблема, если ZZZна листе действительно есть столбец), заменить эти подходы:

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

• У использования есть небольшой недостаток C2:K: =ARRAYFORMULA(COLUMN(C2:K))вернет массив номеров столбцов даже для несуществующих столбцов, поэтому нам нужно использовать =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))вместо него.

Answer 5

Я думаю, что есть простой ответ на построчное среднее с использованием VLOOKUPи QUERY.

Это в B2:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      ROW(B2:B),
      QUERY(
        {
          FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
          FLATTEN(C2:J)
        },
        "SELECT Col1, AVG(Col2)
         WHERE Col2 IS NOT NULL
         GROUP BY Col1"
      ),
      2,
      0
    )
  )
)

• Это можно легко изменить для max, min, sum, count - просто измените функцию агрегирования внутри QUERYоператора.
• Тот же подход можно использовать для агрегации по столбцам.
• FLATTEN(C2:J) можно изменить на:
  • FLATTEN(--C2:J)обрабатывать пустые ячейки как 0s;
  • FLATTEN(IFERROR(1/(1/C2:J)))исключить 0s из среднего.
• Если промежуточных пустых строк нет, VLOOKUPих можно удалить как из формулы, так и Col1из SELECTоператора.
• Есть более короткая версия (спасибо @MattKing!) Без VLOOKUPи WHERE Col...:

=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
      FLATTEN(IFERROR(1/(1/C2:J)))
    },
    "SELECT AVG(Col2)
     GROUP BY Col1
     LABEL AVG(Col2) ''"
  )
)

Я использую C2:Jдиапазон с столбцами до I:I, некоторые подробности об этом:

• Диапазон, C2:Jкоторый на один столбец больше, чем фактически существует на листе. Он не только дает диапазон всех столбцов справа C2и всех строк ниже, но также обновляется в случае добавления еще одного столбца справа от листа: демонстрация . Хотя это не особо выделяется. Это C2:Jможет почти идеально (возникнет проблема, если ZZZна листе действительно есть столбец), заменить эти подходы:

INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))

• У использования есть небольшой недостаток C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))вернет массив номеров столбцов даже для несуществующих столбцов (умноженных на 0), поэтому нам нужно использовать =SEQUENCE(1, COLUMNS(C2:J),,)вместо него.

---

@ player0, есть мысли по этому поводу?