Comment effectuer des calculs en masse (addition, soustraction, division ou multiplication) sur des cellules Excel en 1 clic ?

Dans ce tutoriel, je vais vous montrer comment multiplier ou diviser en masse toutes les valeurs d'une colonne par un nombre dans Excel, et ce, en quelques clics seulement !

Vous en avez assez de passer des heures à modifier manuellement vos données ? Vous avez besoin de gérer rapidement et efficacement des colonnes entières ?

Alors ce tutoriel est fait pour vous, surtout restez bien jusqu'à la fin de ce tutoriel pour découvrir comment gagner un temps précieux et améliorer considérablement votre productivité.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Pourquoi multiplier ou diviser en masse les valeurs d'une colonne

Lorsque nous utilisons Excel, il est fréquent de devoir effectuer des ajustements sur des ensembles de données volumineux.

Qu'il s'agisse de modifier des prix en raison d'une variation des coûts, d'ajuster des pourcentages pour tenir compte de nouvelles informations ou de mettre à jour des données pour refléter les changements de politiques, multiplier ou diviser rapidement les valeurs d'une colonne peut être extrêmement utile.

Cela pour effet permettre de diminuer le risque d’erreur de saisie et surtout de réduire le temps de travail nécessaire pour effectuer l’opération ?

2. Présentation du cas

Dans cet exemple, notre grille tarifaire contient 20 produits courants, principalement des fruits et légumes, avec leur prix de vente respectif.

Nous souhaitons appliquer une hausse de 3% sur l'ensemble des prix de vente. Pour cela, nous allons explorer différentes méthodes pour effectuer cette opération.

Nous verrons ainsi comment effectuer le calcul en utilisant une colonne supplémentaire, en utilisant l’option de collage spécial et les macros VBA.

3. Utiliser une colonne temporaire

Tout d'abord, nous pouvons utiliser une colonne supplémentaire pour effectuer la multiplication. Voici comment procéder :

- Pour commencer, nous allons nous placer sur la première cellule située sur la droite du tableau (la cellule C9)

- Puis nous allons y saisir la formule Excel suivante « =A1 * (1+3%) »

- Une fois le calcul effectué, nous allons étendre la formule sur toutes les cellules de la colonne en utilisant la poignée de recopie (le carré noir situé en bas à droite de la cellule)

- Une fois que les nouveaux prix ont été calculés, nous allons pouvoir copiez les valeurs et les coller en tant que valeurs (avec "Collage spécial") dans la colonne des prix d'origine. Pour ce faire, nous sélectionnons toutes les cellules de la colonne contenant les nouveaux prix, puis nous les copions en appuyant sur [Ctrl]+[C]. Ensuite, nous sélectionnons la première cellule de la colonne des prix d'origine, où nous souhaitons coller les nouvelles valeurs et depuis l'onglet « Accueil » du ruban Excel, nous cliquons sur la petite flèche sous l'icône « Coller » pour ouvrir le menu déroulant du collage spécial. Nous choisissons l'option « Coller les valeurs » (ou appuyez sur [Alt], [L], [V] puis [V]). Cette action collera les nouvelles valeurs calculées dans la colonne des prix d'origine, en remplaçant les anciens prix.

- Pour finir, nous pouvons maintenant supprimer la colonne supplémentaire utilisée pour les calculs, laquelle ne nous sera maintenant plus utile.

4. Méthode avec le collage spécial (multiplication de masse)

Une autre méthode pour effectuer la multiplication en masse est d'utiliser le collage spécial, ce qui nous évitera de devoir créer une colonne intermédiaire.

Voici les étapes à suivre :

- Pour commencer, nous saisissons « 1,03 » (ce qui correspond à une hausse de 3%) dans une cellule vide, située hors du tableau et nous copions cette valeur en appuyant sur [Ctrl]+[C]

- Puis, nous sélectionnons les cellules de la colonne contenant les prix des produits.

- Ensuite, nous effectuons un clic droit sur l’une des cellules sélectionnées afin de choisir « Collage spécial... ».

- Dans la fenêtre « Collage spécial », nous sélectionnons de ne prendre que la valeur de cellule et nous cochons l’option « Multiplication »

- Enfin, nous pouvons valider en cliquant sur le bouton « OK ».

Les prix des produits sont maintenant mis à jour avec la hausse de 3%.

5. Multiplication de masse avec une macro VBA (en utilisant l'enregistreur de macro)

Maintenant, voyons comment effectuer ces opérations de manière encore plus simple, en créant une macro commande en VBA.

Une macro VBA est un ensemble d'instructions automatisées écrites en Visual Basic for Applications, un langage de programmation intégré à Excel et aux autres applications de la suite Microsoft Office.

Cela permet ainsi de démultiplier les possibilités offertes par VBA et d'automatiser des tâches répétitives ou complexes.

Pour que ce tutoriel soit accessible pour tous, nous allons bien détailler chaque opération de construction de la macro.

Mais si vous souhaitez en savoir plus sur les macros VBA, vous pouvez vous procurer mon livre en cliquant ici.

Dans un premier temps, pour simplifier au maximum les opérations, nous allons demander à Excel de créer la macro à notre place !

Pour cela, nous allons pouvoir utiliser un outil extrêmement puissant d'Excel, il s'agit de l'enregistreur de macro.

Celui-ci permet en effet d'enregistrer les actions effectuées dans Excel et de les convertir automatiquement en code VBA.

Voici les étapes de création de cette macro :

- Tout d’abord, nous commençons par saisir le montant de l’augmentation dans une cellule vide (ici nous l’avons déjà saisi dans la partie précédente dans la cellule D8)

- Ensuite, nous sélectionnons les cellules que nous allons souhaiter modifier (les prix de vente)

- Nous allons maintenant attaquer la création de la macro VBA en cliquant sur le bouton « Enregistrer une macro », en bas à gauche dans la barre d'état

- Dans la fenêtre qui s’affiche, nous allons pouvoir donner un nom à la macro (par exemple, "AjusterPrix") et choisir un raccourci clavier pour l'exécuter rapidement, si vous le souhaitez.

- Nous sélectionnons l'option « Ce classeur » dans la liste déroulante « Enregistrer la macro dans » et nous cliquons sur « OK » pour commencer l'enregistrement de la macro.

Maintenant, tout ce que nous allons effectuer dans Excel sera enregistré et traduit en macro VBA de manière automatique.

Il ne reste plus qu’à reproduire les opérations mises en place dans la partie précédente :

- Copiez la cellule D8 qui contient le taux d'augmentation de 3%

- Sélectionnez la plage de cellules contenant les prix de vente (B9:B28)

- Utilisez "Collage spécial" pour multiplier les prix de vente par le taux d'augmentation

Lorsque nous avons terminé, nous pouvons arrêter l'enregistrement de la macro en cliquant à nouveau sur le bouton « Enregistreur de macro » dans la barre d’état.

Pour lancer la macro, nous pouvons appuyer sur [Alt]+[F8] afin de lancer la fenêtre « Gestionnaire de macros » et de choisir "AjusterPrix". Après avoir validé, les prix de vente sont automatiquement ajustés en fonction de l'augmentation définie.

Pour retrouver la macro, nous appuyons sur les touches [Alt]+[F11] afin d'ouvrir l'éditeur VBA qui est l’outil qui permet de créer, modifier et gérer les macros dans les applications de la suite Microsoft Office, telles que Excel, Word et PowerPoint.

La macro générée devrait se trouver dans le « Module1 » et devrait alors ressembler à :

Sub AjusterPrix ()
'
' AjusterPrix 
'
 
'
    Range("D8").Select
    Selection.Copy
    Range("B9:B28").Select
    Selection.PasteSpecial Paste:=xlPasteValues,  Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
End Sub

6. Amélioration de la macro

Maintenant, nous allons souhaiter partir de cette petite macro VBA afin de l’améliorer pour permettre à l'utilisateur de choisir la cellule contenant la valeur à utiliser pour ajuster les prix, et de choisir l'opération à effectuer (multiplication, division, addition ou soustraction).

Cette macro sera ainsi plus flexible et plus conviviale pour l'utilisateur, car elle lui permettra de personnaliser l'ajustement des prix en fonction de ses besoins.

La première étape consiste à déclarer les variables nécessaires à la macro.

Ces variables sont au nombre de trois :

- cOrigine : une variable de type Range qui représente la plage de cellules sélectionnée par l'utilisateur.

- cValeur : une variable de type Range qui représente la cellule contenant la valeur à utiliser pour ajuster les prix.

- operation : une variable de type Integer qui représente l'opération à effectuer (multiplication, division, addition ou soustraction).

Voici comment déclarer ces variables dans la deuxième macro :

Dim cOrigine As Range, cValeur As Range,  operation As Integer

Ensuite, nous allons enregistrer la plage des cellules sélectionnées dans la variable cOrigine, en utilisant le mot-clé d’affectation « Set » :

    Set cOrigine = Selection

Puis, nous allons également enregistrer les coordonnées de la cellule dans laquelle se trouve la valeur qui va permettre d’effectuer l’opération souhaitée

Pour ce faire, nous allons utiliser la fonction InputBox pour demander à l'utilisateur de sélectionner la plage de cellules, que nous allons ensuite stocker dans la variable « cOrigine » :

Set cValeur = Application.InputBox("Sélectionnez  la cellule contenant la valeur à utiliser pour ajuster les prix :",  Type:=8)

L’argument « type » auquel nous donnons la valeur « 8 » permet de vérifier que l’utilisateur à bien sélectionné une cellule.

Maintenant que nous connaissons la plage des cellules dans laquelle se trouve les valeurs à modifier ainsi que la cellule contenant la valeur à utiliser pour le calcul, nous allons souhaiter améliorer la macro afin de permettre à l’utilisateur de choisir le calcul à mettre en place.

Pour cela, nous allons utiliser une nouvelle fois l’instruction « InputBox » pour afficher une boîte de dialogue permettant à l'utilisateur de saisir un nombre compris entre 1 et 4 pour choisir l'opération à effectuer.

Attention de bien respecter l’ordre et les valeurs, car comme nous le verrons juste après, ces dernier sont très importants pour le bon fonctionnement de la macro.

Les différentes opérations sont affichées dans la boîte de dialogue grâce aux chaînes de caractères passées en argument.

    operation = Application.InputBox("Choisissez  l'opération à effectuer :" & vbNewLine & vbNewLine  & _
                                         "1 : Multiplication"  & vbNewLine & _
                                         "2 : Division"  & vbNewLine & _
                                         "3 : Addition"  & vbNewLine & _
                                         "4 : Soustraction",  Type:=1)

Ici, nous retrouvons à plusieurs reprise l’instruction « vbNewLine ». Il s’agit d’une constante qui représente une nouvelle ligne dans une chaîne de caractères.

Elle est utilisée pour ajouter une ligne vide entre chaque opération affichée dans la boîte de dialogue.

Nous utilisons une nouvelle fois l’argument « Type » auquel nous donnons la valeur « 1 » pour indiquer que la fonction attend un nombre entier en entrée.

Si l'utilisateur entre une valeur qui n'est pas un nombre entier, la fonction renverra une erreur.

Maintenant, nous pouvons copier la cellule sélectionnée par l’utilisateur et que nous avions stockée dans la variable « cValeur », en utilisant la méthode « Copy » :

cValeur.Copy

Maintenant, nous allons pouvoir modifier les cellules sélectionnées grâce à une instruction « PasteSpecial » que l’enregistreur de macro avait utilisé dans la partie précédente :

    cOrigine.PasteSpecial Paste:=xlPasteValues,  operation:=operation + 1, _
                SkipBlanks:=False, Transpose:=False

La petite subtilité ici, c'est que nous utilisons la valeur « operation » à laquelle nous ajoutons « 1 », car l'argument « operation » attend une constante qui correspond à l'opération à effectuer.

Cette constante peut prendre les valeurs suivantes :

- « xlAdd » qui a pour valeur « 2 » et qui permet d’effectuer une addition

- « xlSubtract » qui a pour valeur « 3 » et qui permet d’effectuer une soustraction

- « xlMultiply» qui a pour valeur « 4 » et qui permet d’effectuer une multiplication

- « xlDivide» qui a pour valeur « 5 » et qui permet d’effectuer une division

Ici, nous ajoutons donc "1" pour correspondre à ces valeurs.

Il ne reste plus qu'à tester la macro en appuyant sur les touches [Alt]+[F8]. Excel nous affichera alors deux boîtes de dialogue successives pour sélectionner la cellule contenant la valeur à utiliser dans le calcul, ainsi que le calcul à effectuer. Il ne reste plus qu’à tester la macro, comme nous l’avons fait dans la partie précédente, en appuyant sur les touches [Alt]+[F8].

Excel nous affichera alors deux boîtes de dialogues successives pour sélectionner la cellule contenant la valeur à utiliser dans le calcul, ainsi que le calcul à effectuer.

Enfin, il est important de noter que pour que la macro fonctionne correctement et que les modifications apportées soient enregistrées, il est indispensable d'enregistrer le classeur au format "xlsm". Ce format de fichier permet de conserver les données, les formules et les macros enregistrées dans le classeur, et est compatible avec toutes les versions d'Excel à partir de 2007.

En suivant les étapes de ce tutoriel, nous avons appris à multiplier et diviser en masse les valeurs d'une colonne par un nombre dans Excel. Vous êtes maintenant prêt à gérer vos données avec plus d'efficacité et de rapidité. N'hésitez pas à mettre en pratique ces astuces et à partager vos retours d'expérience dans les commentaires. Bonne continuation dans votre découverte d'Excel !