Formula generică
(=MAX(IF(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0,rng)))
rezumat
Pentru a obține maximul fiecărei coloane a n-a, puteți utiliza o formulă matrice bazată pe funcțiile MAX, MOD și COLUMN. În exemplul prezentat, formula din M5 este:
=MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
Această formulă folosește funcțiile MOD și COLUMN pentru a filtra valorile care nu se află în coloanele n-a, apoi rulează MAX pe rezultat.
Cheia este acest fragment:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Aici, formula folosește funcția COLUMN pentru a obține un set de numere de coloană "relative" pentru interval (explicat în detaliu aici) care arată astfel:
(1,2,3,4,5,6,7,8,9,10)
Această matrice intră în funcția MOD ca argument numeric:
MOD((1,2,3,4,5,6,7,8,9,10),K5)=0
unde L5 este valoarea de utilizat pentru „nth”. Funcția MOD returnează restul pentru fiecare număr de coloană împărțit la N. Deci, de exemplu, când N = 3, MOD va returna o matrice ca aceasta:
(1,2,0,1,2,0,1,2,0,1)
Rețineți că apar zerouri pentru coloanele 3, 6, 9 etc., corespunzătoare fiecărei a treia coloane. Această matrice este comparată cu zero cu expresia logică = 0 pentru a forța un TRUE atunci când restul este zero și un FALSE când nu. Aceste valori intră în funcția IF ca test logic. Funcția IF se filtrează în consecință, deci numai valorile din intervalul original din coloanele n-o intră în matricea finală. Celelalte valori devin FALSE.
Când n = 3, matricea finală din MAX arată astfel:
MAX((FALSE,FALSE,11,FALSE,FALSE,8,FALSE,FALSE,19,FALSE))
În cele din urmă, funcția MAX ignoră valorile FALSE și returnează maximul valorilor rămase, 19.
Max din fiecare altă coloană
Dacă doriți să obțineți maximul oricărei alte coloane, trebuie doar să adaptați această formulă după cum este necesar, înțelegând formula atribuie automat 1 primei coloane din interval. Pentru a obține maximum de EVEN coloane, utilizați:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0,rng)))
Pentru a obține maximum de coloane ODD, utilizați:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1,rng)))
