Generisk formel
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Sammanfattning
För att summera var nionde kolumn kan du använda en formel baserad på funktionerna SUMPRODUCT, MOD och COLUMN.
I exemplet som visas är formeln i L5:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Förklaring
Kärnan använder SUMPRODUCT för att summera värden i en rad som har "filtrerats" med hjälp av logik baserat på MOD. Nyckeln är den här:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Detta utdrag av formeln använder COLUMN-funktionen för att få en uppsättning "relativa" kolumnnummer för intervallet (förklaras i detalj här) som ser ut så här:
(1,2,3,4,5,6,7,8,9)
Detta går in i MOD som så:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
där K5 är värdet för N i varje rad. MOD-funktionen returnerar återstoden för varje kolumnnummer dividerat med N. Så, till exempel, när N = 3, kommer MOD att returnera något så här:
(1,2,0,1,2,0,1,2,0)
Observera att nollorna visas för kolumn 3, 6, 9 osv. Formeln använder = 0 för att tvinga en SANT när resten är noll och en FALSK när inte, då använder vi en dubbel-negativ (-) för att tvinga SANT och FALSKT för enor och nollor. Det lämnar en array som denna:
(0,0,1,0,0,1,0,0,1)
Där 1s nu anger "nth värden". Detta går in i SUMPRODUCT som array1, tillsammans med B5: J5 som array2. SUMPRODUCT gör sedan sin grej, först multiplicerar och sedan summerar produkter från matriserna.
De enda värden som "överlever" multiplikation är de där array1 innehåller 1. På detta sätt kan du tänka på logiken för array1 "filtrering" av värdena i array2.
Summa varannan kolumn
Om du vill summera varannan kolumn, anpassar du bara denna formel efter behov, med tanke på att formeln automatiskt tilldelar 1 till den första kolumnen i intervallet. För att summera EVEN-kolumner, använd:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
För att summera ODD-kolumner, använd:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
