Sommes alternées avec une fonction VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonction VBA pour sommes alternées

Selon la construction des tableaux Excel, il n'est pas rare que certaines colonnes ou certaines lignes soient à ignorer pour fournir les calculs de synthèse en bout de course. Nous avions d'ailleurs démontré des techniques pour réaliser des sommes une ligne sur deux ou une colonne sur 2, voire 3 etc... Pour cela, nous avions engagé la fonction SommeProd dans un premier exemple et la fonction Decaler dans un second.

Ici, nous proposons de résoudre définitivement le cas, en construisant une fonction VBA capable de s'adapter au contexte. Elle doit attendre deux paramètres : Le premier pour la plage du calcul et le second pour le pas à respecter afin d'ignorer les lignes ou colonnes intercalées. Et comme vous le savez, si nous décidions d'enregistrer cette fonction dans la bibliothèque d'Excel, elle serait alors disponible comme n'importe quelle autre fonction de n'importe quel classeur.

Classeur Excel à télécharger
Pour développer cette nouvelle fonction VBA, nous suggérons d'appuyer l'étude sur un classeur doté d'une feuille hébergeant un tableau dans lequel des calculs de synthèse sont attendus.

• Télécharger le classeur fonction-sommes-alternees.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un tableau des ventes réalisées sur les quatre premiers mois.



Une colonne sur trois, le montant total hors taxes (MHT) de chaque article est calculé en fonction du prix unitaire (PUHT) et de la quantité vendue (Qté). Ce sont ces montants hors taxes (MHT) que nous souhaitons consolider en dernière colonne du tableau (Tot. MHT). Il est donc question de réaliser des sommes une colonne sur trois. C'est la raison pour laquelle nous allons créer une fonction VBA Excel adaptative, capable de considérer dynamiquement le nombre de lignes ou de colonnes à sauter.

La fonction et ses arguments
Nous souhaitons donc créer une fonction VBA attendant deux paramètres. Le premier est celui de la plage pour la somme. Le second est le pas pour connaître le saut à effectuer entre chaque addition.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans les propositions, choisir l'option Module,

Nous créons ainsi un nouveau module et sa feuille de code, vierge pour l'instant, apparaît au centre de l'écran.

• Dans cette feuille de code, créer la fonction sommeAlt, comme suit :

Function sommeAlt(Ligne As Range, pas As Byte) As Double

End Function

Nous la nommons sommeAlt pour somme alternée. Nous la typons comme un réel double précision (As Double) pourqu'elle soit capable de gérer les nombres possédant de nombreuses décimales. En premier paramètre, elle attend la plage de la somme (Ligne As Range) à fournir par l'utilisateur à la souris. En second, elle attend le pas de la somme (pas As Byte). Ce numéro est à saisir au clavier par l'utilisateur.

Les variables VBA
Maintenant, nous avons besoin de variables notamment pour piloter la plage de cellules transmise et pour consolider les montants hors taxes.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim Total As Double: Dim compteur As Byte
Dim Plage As Range: Dim cellule As Range

Set Plage = Ligne: compteur = 1
...

Total est la variable qui doit cumuler les totaux. C'est la raison pour laquelle nous la typons comme la fonction, soit comme un réel double. La variable compteur est typée comme un entier court. Nous l'incrémenterons à chaque passage dans une boucle que nous façonnerons pour parcourir chaque cellule de la plage transmise en argument. Nous la confronterons avec le pas à observer pour l'addition, afin de savoir si la cellule en cours d'analyse doit être considérée dans la consolidation. L'objet Plage est typé comme un Range pour piloter la plage de cellules transmise par l'utilisateur. La variable cellule est du même type. Nous l'utiliserons justement pour parcourir chaque cellule de la plage.

Ensuite, nous initialisons (Set) l'objet Plage sur la ligne ou la colonne transmise en premier argument. Puis, nous initialisons notre variable compteur à 1 pour débuter l'analyse à partir de la première de ses cellules.

Parcourir chaque cellule de la plage
Comme notre objet cellule est judicieusement typé comme l'objet représentant la plage de cellules à analyser, nous allons pouvoir l'exploiter dans une boucle For Each pour parcourir toutes les cases composant cette plage.

• A la suite du code VBA, créer la boucle For Each suivante :

...
For Each cellule In Plage

compteur = compteur + 1
Next cellule
...

C'est une boucle For Each classique parcourant chaque cellule de la plage et incrémentant la variable compteur avant d'entamer chaque nouveau passage.

Consolider les totaux alternés
Précisément avant cette incrémentation, nous devons confronter la valeur de cette variable compteur avec le pas transmis en paramètre de la fonction. S'il s'agit d'un multiple de ce pas, nous savons que la cellule est à considérer dans la consolidation. Et pour savoir s'il s'agit d'un multiple, nous pouvons analyser le reste de la division de ce compteur par ce pas. S'il est nul, nous devons intégrer la cellule dans le cumul. Et c'est l'opérateur VBA Mod qui renseigne sur le reste d'une division.

• Dans la boucle et avant l'incrémentation, ajouter l'instruction conditionnelle suivante :

...
If (compteur Mod pas = 0) Then
Total = Total + cellule.Value
End If
...

Si le reste de la division du compteur par le pas est nul, nous consolidons le montant d'un précédent passage dans la boucle avec celui de la cellule en cours d'analyse (MHT). C'est ainsi de fil en aguille que nous obtiendrons la somme alternée finale.

Retourner la somme
Une fois le traitement de la boucle terminé, il ne nous reste plus qu'à retourner le montant total calculé. Pour cela et comme vous le savez, une fonction VBA répond par son propre nom. C'est donc elle que nous devons affecter sur le total consolidé.

• Après la boucle, ajouter l'affectation suivante :

...
sommeAlt = Total
...

Sommer une colonne sur trois
Il ne nous reste plus qu'à tester le bon fonctionnement de cette nouvelle fonction Excel qui peut s'avérer utile dans bien des cas.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille (ALT + Tab),
• Sélectionner la case du premier total à consolider en cliquant sur la cellule O5,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la nouvelle fonction, suivi d'une parenthèse ouvrante, soit : sommeAlt(,
• Désigner toute la première ligne pour la somme alternée, soit la plage de cellules C5:N5,
• Taper un point-virgule (;) pour passer dans l'argument du pas,
• Puis, saisir le chiffre 3 pour sommer toutes les trois colonnes,
• Dès lors, fermer la parenthèse de la fonction SommeAlt,
• Enfin, valider la formule avec le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat. Le premier résultat tombe et semble tout à fait cohérent.

• Double cliquer sur la poignée de cette cellule pour répandre la logique sur la hauteur du tableau,

Ce sont toutes les sommes alternées qui sont ainsi livrées pour chaque ligne.



Il est très simple de vérifier la cohérence de ces résultats. Il suffit de sélectionner ensemble les trois MHT d'une ligne grâce à la souris et à la touche CTRL + maintenue enfoncée. Dès lors, en consultant l'information de synthèse fournie par la barre d'état d'Excel, en bas à droite de la fenêtre, vous constatez qu'elle recoupe parfaitement le résultat de la somme alternée pour cette même ligne.

Cette fonction est donc adaptative dans la mesure où vous pouvez ajuster le pas de l'alternance au contexte. De plus et bien entendu, vous pouvez aussi l'utiliser pour réaliser des sommes sur des lignes alternées.

Le code VBA complet que nous avons construit pour bâtir cette fonction de somme alternée, est le suivant :

Function sommeAlt(Ligne As Range, pas As Byte) As Double
Dim Total As Double: Dim compteur As Byte
Dim Plage As Range: Dim cellule As Range

Set Plage = Ligne: compteur = 1

For Each cellule In Plage
If (compteur Mod pas = 0) Then
Total = Total + cellule.Value
End If
compteur = compteur + 1
Next cellule

sommeAlt = Total

End Function