Определите диапазон, используя формулу

Question

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

=AVERAGE(Sheet2!$A$1:Sheet2!$A$100)

В зависимости от того, где используется код выше, мне нужно поменять столбец Aна другую букву, например, столбец D. Для этого я создал функцию:

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)

который выведет D.

Моя проблема в том, что вместо того, чтобы написать:

=AVERAGE(Sheet2!$D$1:Sheet2!$D$100)

Я хочу использовать:

=AVERAGE(Sheet2!$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)$1:Sheet2!$MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)$100)

но Excel не принимает это. Как я могу использовать функции рядом с Sheet2!?

Answer 1

Вы можете использовать КОСВЕННЫЙ:

=AVERAGE(INDIRECT ("Sheet2!$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"$1:Sheet2!$" & MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",4,1)&"$100"))

Answer 2

OFFSET и INDIRECT изменчивы, мы можем использовать INDEX

=AVERAGE(INDEX(Sheet2!$A$1:$Z$100,0,4))

Сообщает 0INDEX о необходимости возврата всех строк, а это 44-й столбец. Таким образом, все строки от 1 до 100 в столбце 4 будут усреднены.

Согласно вашим комментариям:

=AVERAGE(INDEX(Sheet2!$A$1:$Z$100,0,(COLUMN(A:A)-1)*3))

Answer 3

Вот один из способов:

=AVERAGE(OFFSET(Sheet2!$A:$A,0,(COLUMN()-1)*3))

Обратите внимание, что OFFSETэто изменчиво и увеличит вычислительную нагрузку в вашей рабочей книге.