Загальна формула
(=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))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + 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 - значення, яке слід використовувати для "n-го". Функція MOD повертає залишок для кожного номера стовпця, поділеного на N. Так, наприклад, коли N = 3, MOD поверне такий масив:
(1,2,0,1,2,0,1,2,0,1)
Зверніть увагу, що нулі з’являються у стовпцях 3, 6, 9 тощо, що відповідає кожному 3-му стовпцю. Цей масив порівнюється з нулем з логічним виразом = 0, щоб змусити TRUE, коли залишок дорівнює нулю, і FALSE, коли ні. Ці значення входять у функцію IF як логічний тест. Функція IF фільтрує відповідно, тому лише значення в початковому діапазоні в n-х стовпцях потрапляють до остаточного масиву. Інші значення стають FALSE.
Коли 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)))
