Excel : Faciliter vos sous totaux avec les macros

Bonjour, cet article s’adresse à toutes celles et ceux sur Microsoft Excel qui sont adepte du :
=somme(A1:A10)
ou pire
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10
Je vous propose donc ici de réduire ces tâches répétitives et extrêmement chronophages par une petite macro liée à un raccourci clavier.

Pour commencer, je tiens à préciser que cette macro a été réalisée sur la version Excel pour Windows. Je ne peux pas affirmer qu’elle fonctionne sur la version Mac OS.

Pour cette macro, nous allons utiliser la formule « Sous Total » qui est beaucoup plus pratique que la formule « Somme ». Pourquoi me direz vous? Tout simplement parce qu’une formule « Sous total » ne prend jamais en compte une cellule du tableur ou se trouve une autre formule « Sous Total ». Pour faire court, ça évite de comptabiliser deux fois la même chose. Mais ne vous inquiétez pas, nous en reparlerons à la fin de l’article en faisant quelques tests.

Préparation d’Excel

Tout d’abord nous allons préparer Excel afin de pouvoir créer et modifier des macros. Par défaut, Microsoft Excel n’affiche pas l’onglet développeur (Hé oui, vous allez devenir développeur 🙂 ). Pour ce faire cliquer sur le bouton « Fichier » puis sur « Options ». Ensuite déplacer vous sur la partie « Personnaliser le ruban » et cocher la case « Développeur » dans la partie de droite de la fenêtre. Quand c’est fait cliquer sur « Ok » pour valider.

Le classeur personnel

L’intérêt d’une macro comme celle que je vous propose est qu’elle doit pouvoir être réutiliser pour chaque classeur Excel que vous allez utiliser par la suite. Pour ça, il existe dans votre ordinateur un fichier Excel qui s’appelle « PERSONAL.XLSB » et qui est destiné à stocker toutes vos macros réutilisables dans plusieurs fichiers. Mais où est ce fichier? Ne vous inquiétez pas, nous n’allons pas partir à la recherche de ce fichier. Nous allons plutôt créer une macro « lambda » que nous enregistrerons dans ce classeur personnel, que nous retrouverons par la suite dans « Visual Basic ».

Bon, assez parlé! Rendez vous dans l’onglet « Développeur » fraîchement apparu, puis cliquer sur  . Dans la fenêtre qui vient d’apparaître, renseigner les champs suivant :
– Nom de la macro : sousTotal
– Touche de raccourci : q
-Enregistrer la macro dans : Classeur de macros personnelles
– Description : Mettez ici ce que vous voulez 🙂

Cliquer sur « Ok » pour valider et passer en mode enregistrement. A partir de maintenant tout ce que vous allez faire dans Excel va s’enregistrer dans cette macro. Renseigner une valeur dans une cellule pour voir et surtout pour qu’il y ait quelque chose dans cet macro. Lorsque c’est fait, cliquer sur  . Voila vous avez créé votre première macro… Sauf qu’elle ne sert pas à grand chose. Nous allons donc continuer dans « Visual Basic » pour modifier cette macro et surtout la rendre utile.

Visual Basic

Il ne reste donc plus qu’à modifier cette macro. Cliquer sur le bouton « Visual Basic » situé en haut à gauche de votre écran. L’application « Visual Basic pour Application » (VBA) s’ouvre. Sur votre gauche vous pouvez apercevoir une arborescence avec à l’intérieur . Tiens ce ne serait pas notre classeur de macros personnelles décrit plus haut? Eh bien si. Cliquez sur le petit signe + situé à gauche pour développer l’arborescence. Puis faites de même pour le dossier « Modules ». A l’intérieur de ce dossier se trouve « Module1 », double cliquez dessus pour l’ouvrir. Vous pouvez constater qu’à l’intérieur de la fenêtre qui vient de s’ouvrir se trouve le code de la macro que vous avez enregistrer tout à l’heure. Le code peut dépendre de ce que vous avez enregistrer précédemment. Pour moi voici ce qui s’y trouve.

Pour information, voici ce que j’ai fait pendant l’enregistrement de ma macro :
Range("F10").Select J’ai sélectionné la cellule « F10 »
ActiveCell.FormulaR1C1 = "565" Je lui ai attribué « 565 » comme valeur
Range("F11").Select Je me suis déplacé sur la cellule « F11 »

J’ai volontairement mis en vert la description de chaque ligne de code pour vous préciser que dans votre macro tout ce qui est précédé d’un guillemet simple correspond uniquement à du texte de description. Par conséquent « sousTotal Macro » et « Touche de raccourci du clavier: Ctrl+q » sont des commentaires. Mais continuons…

A présent nous allons supprimer tout ce qui ce trouve entre « Sub sousTotal() » et « End Sub » qui définissent les limites de notre macro. En gros tout ce qui se trouve à l’intérieur sera exécuté hormis les commentaires (comme expliqué au dessus).

Coller maintenant le code suivant à l’intérieur de votre macro :


On Error GoTo LastLine
Dim x As Integer
x = ActiveCell.Row - InputBox("A partir de quelle ligne voulez vous commencer le sous total")
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & x & "]C:R[-1]C)"
LastLine:

Cliquez maintenant sur le bouton « Enregistrer » (disquette) ou faites « Fichier » puis « Enregistrer » et fermer « Visual Basic pour Application ».

Test de la macro

Nous revoilà dans Excel. Nous allons maintenant faire des tests afin de vérifier que notre macro fonctionne bien mais également pour vous montrer que la formule « Sous Total » est bien plus sure en terme d’erreur que la formule « Somme ».

Voici l’exemple :

Bon ok j’ai fait simple 🙂 . Tout le monde à bien vu que chaque sous total allait être égal à 50 et que par conséquent le total général serait de 100. Positionnez vous sur le premier sous total dans la cellule « B6 » et faites « Ctrl+q »

Pas mal hein? Donc pour répondre à cette question, nous voulons commencer le sous total à la ligne 1. Entrer 1 et valider en cliquant sur « Ok » ou en tapant sur la touche « Entrée » de votre clavier. Nous obtenons bien le résultat de 50. Jusqu’ici tout va bien. Répéter l’opération sur la cellule « B12 » en commençant le sous total à la ligne 7. Vous obtenez également 50. Cependant, quel résultat allons nous obtenir en pour le Total général? Eh bien avec cette macro et notamment grâce à la formule « Sous Total » vous pouvez créer un autre sous total commençant depuis la ligne 1 qui ne tiendra pas compte des deux autres sous totaux. Voila le résultat :

Pour conclure

Voila comment résoudre les interminables sous totaux dans un bordereau de prix par exemple. Fini les erreurs de calcul en double. En espérant que cet article vous à diverti ou instruit et que surtout cette petite macro vous rendra bien des services et vous fera gagner du temps. Je vous met en complément deux autres macros :

– Une macro très proche de celle présentée mais qui effectue une moyenne (son fonctionnement reste le même)


Sub Moyenne()
On Error GoTo LastLine
Dim x As Integer
x = ActiveCell.Row - InputBox("A partir de quelle ligne voulez vous commencer la moyenne")
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(1,R[-" & x & "]C:R[-1]C)"
LastLine:
End Sub

– Et une plus simple mais assez utile pour passer un texte en majuscule


Sub majuscule()
On Error GoTo LastLine
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=UPPER(" & """" & ActiveCell.Value & """" & ")"
ActiveCell.Value = ActiveCell.Text
LastLine:
End Sub

En vous souhaitant une bonne continuation,
A très bientôt.

Laisser un commentaire