ЗАПРОС

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 значений пустым строкам в заданном диапазоне, например. для противодействия смещению:

МУЛЬТИ

Уровень 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)))

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

лямбда

уровень 5:

с 20 сентября 2022 года мы можем использовать новые функции, упрощающие работу:

• МАКЕРРЕЙ
• УМЕНЬШАТЬ
• БЫРОВ
• БИКОЛ
• СКАНИРОВАНИЕ
• КАРТА
• лямбда

поэтому, чтобы сразу перейти к закрытому диапазону, мы можем взять среднее значение, например:

=IFERROR(BYROW(C2:G9, LAMBDA(x, AVERAGE(x))))

и чтобы получить среднее значение по столбцам, мы просто заменяем BYROW на BYCOL. теперь, чтобы сделать диапазон открытым и по-настоящему динамичным, мы можем изменить приведенную выше формулу следующим образом:

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

мы можем сделать это короче на 12 символов, например:

=IFERROR(BYROW(INDEX(OFFSET(C2,,,
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*ROW(C2:C)), 
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*COLUMN(C2:2)))), LAMBDA(x, AVERAGE(x))))

чтобы исключить нули из вывода:

=INDEX(IFERROR(1/(1/BYROW(OFFSET(C2,,,
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*ROW(C2:C)), 
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*COLUMN(C2:2))), LAMBDA(x, AVERAGE(x))))))

чтобы исключить нули из ввода:

=INDEX(IFERROR(1/(1/BYROW(OFFSET(C2,,,
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*ROW(C2:C)), 
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*COLUMN(C2:2))), LAMBDA(x, AVERAGEIF(x, ">0"))))))

или если пустые ячейки следует рассматривать как нули:

=INDEX(IFERROR(1/(1/BYROW(1*OFFSET(C2,,,
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*ROW(C2:C)), 
 MAX((INDIRECT("C2:"&ROWS(C:C))<>"")*COLUMN(C2:2))), LAMBDA(x, AVERAGE(x))))))

также стоит упомянуть ограничение BYROW в ~ 99990 строк

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

@Erik Tyler уровень:

полной противоположностью предыдущей формулы будет запуск 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:G9 среднее MMULT будет:

=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)''"))))

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

Глядя на ваши окончательные формулы, они должны давать те же результаты (укажите данные в 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))))

Я попытаюсь сделать небольшое дополнение к ответу @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), смотрите подробности в конце.

Чтобы отфильтровать пустые столбцы, мы также используем FILTER с условием 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 для создания столбца или строки 1, чтобы быть явным, для ясности. Например, этот

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)).

Я думаю, что есть простой ответ для среднего по строкам с использованием 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, есть мысли по этому поводу?

ОБНОВЛЕНИЕ: я обновил формулу из моего исходного сообщения. 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 или фильтрах или чем-то еще.

Теперь это проще с BYROW:

=BYROW(C2:G,LAMBDA(r, AVERAGE(r)))

Кусок пирога. Очень просто