Formuła ogólna
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Podsumowanie
Aby zsumować każdą n-tą kolumnę, możesz użyć formuły opartej na funkcjach SUMPRODUCT, MOD i COLUMN.
W przedstawionym przykładzie formuła w L5 to:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Wyjaśnienie
Zasadniczo używa SUMPRODUCT do sumowania wartości w wierszu, które zostały „przefiltrowane” przy użyciu logiki opartej na MOD. Klucz jest taki:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
W tym fragmencie formuły użyto funkcji KOLUMNA w celu uzyskania zestawu „względnych” numerów kolumn dla zakresu (wyjaśnionego szczegółowo tutaj), który wygląda następująco:
(1, 2, 3, 4, 5, 6, 7, 8, 9)
To przechodzi do MOD w następujący sposób:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
gdzie K5 jest wartością N w każdym wierszu. Funkcja MOD zwraca resztę z każdego numeru kolumny podzieloną przez N. Na przykład, gdy N = 3, MOD zwróci coś takiego:
(1,2,0,1,2,0,1,2,0)
Zwróć uwagę, że zera pojawiają się w kolumnie 3, 6, 9 itd. Formuła używa = 0, aby wymusić PRAWDA, gdy reszta jest równa zero i FAŁSZ, gdy nie, wtedy używamy podwójnie ujemnego (-), aby wymusić PRAWDA i FAŁSZ na jedynki i zera. To pozostawia taką tablicę:
(0,0,1,0,0,1,0,0,1)
Gdzie 1s oznaczają teraz „n-te wartości”. To trafia do SUMPRODUCT jako tablica1, a B5: J5 jako tablica2. SUMPRODUCT robi wtedy swoje, najpierw mnoży, a następnie sumuje iloczyn tablic.
Jedynymi wartościami, które „przetrwają” mnożenie, są te, w których tablica1 zawiera 1. W ten sposób można sobie wyobrazić logikę, w której tablica1 „filtruje” wartości w tablicy2.
Zsumuj wszystkie inne kolumny
Jeśli chcesz zsumować co drugą kolumnę, po prostu dostosuj tę formułę zgodnie z potrzebami, pamiętając, że formuła automatycznie przypisuje 1 do pierwszej kolumny w zakresie. Aby zsumować kolumny EVEN, użyj:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Aby zsumować kolumny ODD, użyj:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
