Общая формула
(=MAX(IF(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0,rng)))
Резюме
Чтобы получить максимум каждого n-го столбца, вы можете использовать формулу массива, основанную на функциях MAX, MOD и COLUMN. В показанном примере формула в M5:
=MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5))
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Эта формула использует функции MOD и COLUMN для фильтрации значений, не входящих в n-й столбец, а затем запускает MAX для результата.
Ключ в этом фрагменте:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Здесь формула использует функцию COLUMN для получения набора «относительных» номеров столбцов для диапазона (подробно объясненного здесь), который выглядит следующим образом:
(1,2,3,4,5,6,7,8,9,10)
Этот массив входит в функцию MOD как числовой аргумент:
MOD((1,2,3,4,5,6,7,8,9,10),K5)=0
где L5 - это значение, используемое для «nth». Функция MOD возвращает остаток для каждого номера столбца, деленного на N. Так, например, когда N = 3, MOD вернет такой массив:
(1,2,0,1,2,0,1,2,0,1)
Обратите внимание, что нули появляются в столбцах 3, 6, 9 и т.д., соответствующих каждому третьему столбцу. Этот массив сравнивается с нулем с логическим выражением = 0, чтобы получить значение ИСТИНА, когда остаток равен нулю, и ЛОЖЬ, когда нет. Эти значения входят в функцию ЕСЛИ в качестве логической проверки. Функция ЕСЛИ фильтрует соответственно, поэтому только значения в исходном диапазоне в n-м столбцах попадают в окончательный массив. Остальные значения становятся ЛОЖНЫМИ.
Когда n = 3, последний массив внутри MAX выглядит так:
MAX((FALSE,FALSE,11,FALSE,FALSE,8,FALSE,FALSE,19,FALSE))
Наконец, функция MAX игнорирует значения FALSE и возвращает максимум оставшихся значений, 19.
Макс каждого второго столбца
Если вы хотите получить максимальное значение для каждого другого столбца, просто адаптируйте эту формулу по мере необходимости, понимая, что формула автоматически присваивает 1 первому столбцу в диапазоне. Чтобы получить максимум ЧЕТНЫХ столбцов, используйте:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0,rng)))
Чтобы получить максимальное количество столбцов ODD, используйте:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1,rng)))
