Formula generica
=SUMPRODUCT(--(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0),rng)
Sommario
Per sommare ogni ennesima colonna, puoi utilizzare una formula basata sulle funzioni SUMPRODUCT, MOD e COLUMN.
Nell'esempio mostrato, la formula in L5 è:
=SUMPRODUCT(--(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5)
Spiegazione
Al centro, utilizza SUMPRODUCT per sommare i valori in una riga che sono stati "filtrati" utilizzando la logica basata su MOD. La chiave è questa:
MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0
Questo frammento della formula utilizza la funzione COLUMN per ottenere un insieme di numeri di colonna "relativi" per l'intervallo (spiegato in dettaglio qui) che assomiglia a questo:
(1,2,3,4,5,6,7,8,9)
Questo va in MOD in questo modo:
MOD((1,2,3,4,5,6,7,8,9),K5)=0
dove K5 è il valore di N in ogni riga. La funzione MOD restituisce il resto per ogni numero di colonna diviso per N. Quindi, ad esempio, quando N = 3, MOD restituirà qualcosa del genere:
(1,2,0,1,2,0,1,2,0)
Nota che gli zeri appaiono per le colonne 3, 6, 9, ecc. La formula usa = 0 per forzare un TRUE quando il resto è zero e un FALSE quando non lo è, quindi usiamo un doppio negativo (-) per forzare TRUE e FALSO a uno e zero. Ciò lascia un array come questo:
(0,0,1,0,0,1,0,0,1)
Dove gli 1 ora indicano "l'ennesimo valore". Questo va in SUMPRODUCT come array1, insieme a B5: J5 come array2. SUMPRODUCT quindi fa la sua cosa, prima moltiplicando, quindi sommando i prodotti degli array.
Gli unici valori che "sopravvivono" alla moltiplicazione sono quelli in cui array1 contiene 1. In questo modo, puoi pensare alla logica di array1 che "filtra" i valori in array2.
Somma ogni altra colonna
Se vuoi sommare ogni altra colonna, adatta semplicemente questa formula secondo necessità, tenendo presente che la formula assegna automaticamente 1 alla prima colonna dell'intervallo. Per sommare EVEN colonne, usa:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0),A1:Z1)
Per sommare le colonne DISPARI, usa:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1),A1:Z1)
