Kevin deve creare una formula che somma ogni quarta cella di seguito. Sa che può usare una formula come = A6 + E6 + I6 + M6, ecc., Ma questo diventa complicato se ci sono molte colonne nel foglio di lavoro.

Esistono diversi modi per affrontare questo problema. Un modo consiste nell’aggiungere alcune informazioni aggiuntive al foglio di lavoro per designare quali celle devono essere incluse nella somma. Ad esempio, nell’esempio sei interessato a sommare le celle nella riga 6 del foglio di lavoro. Se puoi aggiungere alcuni indicatori nella riga 5, questi potrebbero essere usati come “trigger” in una formula. Metti il ​​numero 1, ad esempio, sopra ogni cella che desideri includere nella somma (colonne A, E, I, M, ecc.). Quindi, puoi usare una formula come la seguente:

=SUMPRODUCT(A5:X5, A6:X6)

La formula fondamentalmente moltiplica tutto ciò che è nella riga 5 contro la riga 6, quindi somma i risultati. Poiché ci sono solo 1 nelle colonne che vuoi sommare, questi sono tutti quelli che sono inclusi nella somma finale.

Se non desideri aggiungere una riga dell’indicatore al tuo foglio di lavoro, devi cercare soluzioni diverse. È comunque possibile utilizzare la funzione SUMPRODUCT in una formula come la seguente:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=1)*(6:6))

Questa formula si basa sulla funzione MOD per restituire il resto di una divisione. In questo caso, ciò che viene diviso è il numero di colonna di una cella per il valore 4. Ciò risulterà in un resto di 0, 1, 2 o 3. Ogni quarta cella di una riga avrà lo stesso resto. Pertanto, la colonna A (nota anche come colonna 1) avrà un valore MOD di 1 (1 diviso 4 è 0, con 1 rimasto), così come le colonne E, I, M, ecc.

Nota che la formula confronta se il valore MOD è 1 o meno. In tal caso, il confronto restituisce True (1); se non lo è, restituisce False (0). Questo viene quindi moltiplicato per la cella nella sesta riga.

Infine, SUMPRODUCT somma tutte queste moltiplicazioni e fornisce il risultato desiderato.

Sebbene questa formula fornisca la somma di ogni quarta cella nella sesta riga, potrebbe essere facilmente modificata per fornire la somma per ogni terza cella, quinta cella o qualsiasi intervallo desiderato. Basta cambiare il 4 nella funzione MOD all’intervallo desiderato.

Se si desidera selezionare una cella diversa in ogni “cluster” di quattro celle da sommare, è sufficiente modificare il valore confrontato nella funzione MOD. In questo esempio, solo la prima cella in ogni cluster di quattro avrà una MOD di 1 (A, E, I, M, ecc.). Se invece desideri sommare ogni quarta cella iniziando, ad esempio, dalla cella C, cambierai il valore di confronto da 1 a 3. Perché? Perché C è la terza cella del cluster e avrà una MOD di 3, così come ogni quarta cella successiva (G, K, O, ecc.).

L’unico “gottcha” di questa regola generale è se vuoi sommare la quarta cella in ogni cluster di quattro celle. Ad esempio, potresti voler sommare le celle D, H, L, P, ecc. In questo caso il valore di confronto utilizzato non sarebbe 4 poiché non ci sarà mai un resto di 4 quando si esegue un’operazione MOD che prevede la divisione per 4 Invece, il valore di confronto sarebbe 0, come nel seguente:

=SUMPRODUCT((MOD(COLUMN(6:6),4)=0)*(6:6))

Se preferisci lavorare con le formule di matrice, puoi usare una variazione leggermente più breve sulla formula sopra:

=SUM(IF(MOD(COLUMN(6:6),4)=1,6:6))

Si noti che la formula deve essere inserita premendo Ctrl + Maiusc + Invio.

Apparirà quindi nella barra della formula con parentesi graffe (\ {}) attorno alla formula. Le stesse note di modifica relative al divisore MOD e al valore di confronto si applicano qui come hanno fatto con la funzione SUMPRODUCT.

Entrambi questi approcci stereotipati (SUMPRODUCT e la formula dell’array)

somma ogni quarta cella dell’intera riga. Se invece desideri limitare le celle da cui deriva la somma a una parte della riga, sostituisci semplicemente 6: 6 (entrambe le istanze) con l’intervallo corretto. Pertanto, se si desidera sommare solo ogni quarta cella nell’intervallo A6: Z6, si utilizzerà tale intervallo nella formula.

Se esegui molte somme in questo modo e le applichi non solo agli intervalli in una riga ma agli intervalli in una colonna, potresti prendere in considerazione la creazione di una funzione definita dall’utente per eseguire la somma. La seguente semplice funzione farà il trucco:

Function SumEveryFourth(MyRange As Range)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod 4) = 1 Then             SumEveryFourth = SumEveryFourth + MyRange.Cells(x).Value         End If     Next x End Function

La funzione esamina l’intervallo passato ad essa e quindi somma ogni quarta cella a partire dalla prima cella dell’intervallo. Se si preferisce sommare ogni seconda cella dell’intervallo, modificare il valore di confronto nell’istruzione If, ​​come discusso in precedenza in questo suggerimento. (Poiché l’operazione Mod è utilizzata in questa funzione e funziona come la funzione del foglio di lavoro MOD, entrano in gioco gli stessi valori di confronto per determinare quale cella in ogni cluster deve essere sommata.)

La funzione definita dall’utente funzionerà perfettamente su celle in una riga o celle in una colonna. Devi semplicemente assicurarti di passare l’intervallo che desideri, come dimostrato qui:

=SumEveryFourth(C3:C57)

È possibile, se lo si desidera, rendere la macro ancora più flessibile rendendo il valore “step” una variabile che viene passata alla funzione. Questa macro modificata potrebbe quindi essere utilizzata per sommare ogni cella 2, 3, 4, 5, ecc.

Function SumEveryNth(MyRange As Range, N As Integer)

Dim x As Integer     SumEveryFourth = 0     For x = 1 To MyRange.Cells.Count         If (x Mod N) = 1 Then             SumEveryNth = SumEveryNth + MyRange.Cells(x).Value         End If     Next x End Function

Questa funzione è chiamata come la funzione precedente, con l’eccezione che si aggiunge il valore del passo desiderato. Ad esempio, ciò comporterà la somma di ogni terza cella nell’intervallo C3: C57:

=SumEveryNth(C3:C57, 3)

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (3234) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui:

Summing Every Fourth Cell in a Row.