Kevin doit créer une formule qui résume chaque quatrième cellule d’affilée. Il sait qu’il peut utiliser une formule telle que = A6 + E6 + I6 + M6, etc., mais cela devient fastidieux s’il y a beaucoup de colonnes dans la feuille de calcul.

Il existe plusieurs façons d’aborder ce problème. Une façon consiste à ajouter des informations supplémentaires à la feuille de calcul pour désigner les cellules à inclure dans la somme. Par exemple, dans l’exemple, vous souhaitez additionner les cellules de la ligne 6 de la feuille de calcul. Si vous pouvez ajouter des indicateurs à la ligne 5, ceux-ci pourraient alors être utilisés comme « déclencheurs » dans une formule. Mettez le numéro 1, par exemple, au-dessus de chaque cellule que vous souhaitez inclure dans la somme (colonnes A, E, I, M, etc.). Ensuite, vous pouvez utiliser une formule telle que la suivante:

=SUMPRODUCT(A5:X5, A6:X6)

La formule multiplie essentiellement ce qui se trouve dans la ligne 5 par rapport à la ligne 6, puis additionne les résultats. Comme il n’y a que des 1 dans les colonnes que vous souhaitez additionner, ce sont tous ceux qui sont inclus dans la somme finale.

Si vous ne souhaitez pas ajouter de ligne d’indicateur à votre feuille de calcul, vous devez rechercher différentes solutions. Vous pouvez toujours utiliser la fonction SOMMEPROD dans une formule telle que la suivante:

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

Cette formule repose sur la fonction MOD pour renvoyer le reste d’une division. Dans ce cas, ce qui est divisé est le numéro de colonne d’une cellule par la valeur 4. Cela entraînera un reste de 0, 1, 2 ou 3. Chaque quatrième cellule dans une ligne aura le même reste. Ainsi, la colonne A (également connue sous le nom de colonne 1) aura une valeur MOD de 1 (1 divisé par 4 vaut 0, avec 1 restant), tout comme les colonnes E, I, M, etc.

Notez que la formule compare si la valeur MOD est 1 ou non. Si tel est le cas, la comparaison renvoie True (1); si ce n’est pas le cas, il renvoie False (0). Ceci est ensuite multiplié par rapport à la cellule de la sixième ligne.

Enfin, SUMPRODUCT fait la somme de toutes ces multiplications et donne le résultat souhaité.

Bien que cette formule fournisse la somme de chaque quatrième cellule de la sixième ligne, elle peut facilement être modifiée pour fournir la somme pour chaque troisième cellule, cinquième cellule ou tout intervalle de votre choix. Changez simplement le 4 dans la fonction MOD à l’intervalle souhaité.

Si vous souhaitez sélectionner une cellule différente dans chaque « cluster » de quatre cellules à additionner, il vous suffit de modifier la valeur comparée dans la fonction MOD. Dans cet exemple, seule la première cellule de chaque groupe de quatre aura un MOD de 1 (A, E, I, M, etc.). Si vous souhaitez à la place faire la somme toutes les quatre cellules en commençant par, disons, la cellule C, vous changeriez la valeur de comparaison de 1 à 3. Pourquoi? Parce que C est la troisième cellule du cluster et aura un MOD de 3, tout comme chaque quatrième cellule par la suite (G, K, O, etc.).

Le seul « gottcha » à cette règle générale est si vous voulez additionner la quatrième cellule dans chaque groupe de quatre cellules. Par exemple, vous pouvez faire la somme des cellules D, H, L, P, etc. Dans ce cas, la valeur de comparaison utilisée ne serait pas 4 car il n’y aura jamais un reste de 4 lors d’une opération MOD qui implique une division par 4 Au lieu de cela, la valeur de comparaison serait 0, comme dans l’exemple suivant:

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

Si vous préférez travailler avec des formules matricielles, vous pouvez utiliser une variation légèrement plus courte de la formule ci-dessus:

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

Notez que la formule doit être saisie en appuyant sur Ctrl + Maj + Entrée.

Il apparaîtra alors dans la barre de formule avec des accolades (\ {}) autour de la formule. Les mêmes notes de modification relatives au diviseur MOD et à la valeur de comparaison s’appliquent ici comme elles l’ont fait avec la fonction SOMMEPROD.

Ces deux approches par formule (SUMPRODUCT et la formule matricielle)

additionnez toutes les quatre cellules de la ligne entière. Si vous souhaitez plutôt limiter les cellules à partir desquelles la somme est dérivée à une partie de la ligne, remplacez simplement 6: 6 (les deux instances) par la plage appropriée. Ainsi, si vous ne vouliez additionner qu’une cellule sur quatre dans la plage A6: Z6, vous utiliseriez cette plage dans la formule.

Si vous faites beaucoup de sommation de cette manière et que vous l’appliquez non seulement aux plages d’une ligne mais aussi aux plages d’une colonne, vous pouvez envisager de créer une fonction définie par l’utilisateur pour effectuer la sommation. La fonction simple suivante fera l’affaire:

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 fonction examine la plage qui lui est transmise, puis additionne toutes les quatre cellules en commençant par la première cellule de la plage. Si vous préférez avoir une somme toutes les deux cellules de la plage, modifiez la valeur de comparaison dans l’instruction If, comme indiqué précédemment dans cette astuce. (Comme l’opération Mod est utilisée dans cette fonction et qu’elle fonctionne de la même manière que la fonction de feuille de calcul MOD, les mêmes valeurs de comparaison entrent en jeu pour déterminer quelle cellule de chaque cluster doit être additionnée.)

La fonction définie par l’utilisateur fonctionnera parfaitement sur les cellules d’une ligne ou les cellules d’une colonne. Vous devez simplement vous assurer que vous lui passez la plage que vous voulez, comme démontré ici:

=SumEveryFourth(C3:C57)

Vous pouvez, si vous le souhaitez, rendre la macro encore plus flexible en faisant de la valeur « step » une variable qui est passée à la fonction. Cette macro modifiée pourrait ensuite être utilisée pour additionner toutes les cellules 2, 3, 4, 5, etc.

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

Cette fonction est appelée de la même manière que la fonction précédente, à l’exception que vous ajoutez la valeur de pas souhaitée. Par exemple, cela entraînera la somme d’une cellule sur trois dans la plage C3: C57:

=SumEveryNth(C3:C57, 3)

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3234) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Summing_Every_Fourth_Cell_in_a_Row [Addition de chaque quatrième cellule d’affilée].