Comment remplir automatiquement des vides d'un tableau Excel en 1 clic !

Dans ce tutoriel, je vais vous montrer une technique pour optimiser votre travail avec Excel : la recopie automatique de données à l’intérieur d’un tableau.

Cette technique va en effet nous permettre d'économiser un temps précieux et surtout de pouvoir exploiter des bases de données en tant que source d’un tableau croisé dynamique.

Téléchargement

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

Tutoriel Vidéo

Partie 1 : Comment remplir automatiquement les vides d'un tableau ?

Partie 2 : Comment remplir les vides d'un tableau en 1 clic ?

1. Présentation

Pour illustrer ce tutoriel, nous allons prendre l'exemple d'une base de données de ventes mensuelles.

Cette base contient des informations sur les ventes de différents produits pour chaque mois de l'année :

Ensuite, nous allons souhaiter analyser ces informations au travers d’un tableau croisé dynamique, lequel va en effet nous permettre de synthétiser et d'analyser les données de manière plus approfondie.

En effet, avec un tableau croisé dynamique, nous pourrons aisément visualiser les ventes par mois et par produit, identifier les tendances, comparer les performances des différents produits, et bien plus encore.

En bref, cela nous offrira une vue d'ensemble claire et précise de nos données de ventes, facilitant ainsi la prise de décisions stratégiques pour notre entreprise.

Si vous souhaitez en savoir plus sur les TCD et comment les construire facilement vos propres tableaux pour analyser rapidement d’immenses bases de données, je vous ai élaboré une formation dédiée que vous pourrez retrouver en suivant de lien.

Mais avant de pouvoir utiliser cette base comme source du TCD, nous allons devoir la retraiter afin que chaque ligne contienne le mois correspondant.

Actuellement, nos données sont organisées de manière que chaque produit soit répertorié sur une ligne distincte, mais sans que le mois correspondant ne soit mentionné sur chaque ligne.

Cela pose un problème lorsque nous voulons utiliser ces données dans un tableau croisé dynamique, car Excel a besoin que chaque entrée soit associée à une seule catégorie par ligne.

Bien entendu, nous pourrions effectuer cette opération manuellement, mais en fonction du nombre de lignes de votre tableau, cela pourrait demander énormément de temps.

Il sera donc plus indiqué d’utiliser une opération permettant d’effectuer cette opération manuellement.

C’est justement ce que nous allons voir dans ce tutoriel, en découvrant la recopie automatique de données pour remplir les cellules vides avec le mois correspondant, ce qui nous permettra de structurer nos données de manière adéquate pour l'analyse à venir avec notre tableau croisé dynamique.

2. Annuler les éventuelles fusions de cellules

Pour commencer, nous allons nous concentrer concerne la préparation des données.

Cette étape est cruciale car elle garantira que nos données sont bien structurées et prêtes à être analysées.

Et la première étape consiste à sélectionner toutes les cellules de la colonne dans laquelle se trouvent les mois correspondant sur chaque ligne.

De cette manière, si la base de données contient des cellules fusionnées, nous allons pouvoir désactiver ces fusions avant de procéder à la recopie automatique.

En effet, la fusion des cellules peut compliquer la manipulation des données et entraîner des erreurs lors de la recopie.

Pour cela, une fois que les cellules de la colonne sont sélectionnées, nous allons nous rendre dans le menu « Accueil », afin de dérouler « Fusionner et centrer » pour cliquer sur « Annuler Fusionner cellules » :

Pour aller plus vite, nous pouvons également utiliser la séquence de touches Alt, U, U, L.

Maintenant les données sont prêtes à être traitées de manière efficace et précise.

3. Recopie automatique vers le bas

La deuxième étape de la mise en place d’une complétion automatique consiste à sélectionner les cellules vides où nous voulons recopier les mois correspondants.

Pour cela, nous allons utiliser la fonction « Atteindre » d'Excel.

Si les cellules de la colonne ne sont plus sélectionnées, nous commençons par les sélectionner à nouveau, puis nous nous rendons dans l'onglet « Accueil ».

Ensuite, nous déroulons le menu « Rechercher et sélectionner » afin de cliquer sur la fonction « Sélectionner les cellules » dans le groupe Édition.

Il est également possible d’utiliser le raccourci clavier [CTRL]+[T], afin d’afficher la fenêtre « Atteindre » :

Sur celle-ci, nous cliquons ensuite sur le bouton « Cellules ».

Dans la fenêtre « Sélectionner les cellules », il ne reste plus qu’à choisir l'option « Cellules vides » et valider en appuyant sur OK.

Cela aura pour effet de sélectionner toutes les cellules vides de la feuille de calcul.

Maintenant que nous avons sélectionné les cellules vides, nous allons utiliser une formule très simple pour recopier les données vers le bas.

Celle-ci va uniquement récupérer la valeur de la cellule située juste au-dessus de la cellule vide active.

Cette formule assurera que chaque cellule vide sera remplie avec le mois correspondant.

 =CELLULE_DU_DESSUS 

Ici, la cellule active est la cellule A8, son résultat sera donc égal à la valeur de la cellule A7.

Une fois la formule entrée, appuyez sur la combinaison de touches Ctrl + Entrer pour la valider sur toute la sélection de cellules vides.

Cette combinaison de touches est une astuce pratique pour valider rapidement une formule sur toute la sélection de cellules. Cela permet d'économiser du temps et évite d'avoir à valider la formule cellule par cellule.

4. Remplacer les formules par leur résultat

Maintenant que les mois ont bien été récupérés, il est important de noter que les formules peuvent être sensibles aux réorganisations ultérieures du tableau.

Ainsi, pour garantir l'exactitude des données même après une réorganisation, il est recommandé de remplacer les formules par leur résultat.

Pour cela, nous sélectionnons à nouveau toutes les cellules de la colonne.

Puis, nous effectuons un clic droit afin de choisir l’option « Copier ».

Ensuite, nous effectuons un second clic-droit et nous choisissons cette fois-ci « Collage spécial » dans le menu contextuel, puis « Valeurs ».

Cela remplacera les formules par leurs résultats actuels, garantissant ainsi l'exactitude des données même en cas de réorganisation du tableau.

   5.1. Pourquoi effectuer l’opération en 1 clic ?

Maintenant que nous avons vu comment optimiser la préparation de nos données en utilisant la recopie automatique, voyons comment automatiser totalement le processus afin de pouvoir effectuer cette opération d'un simple clic.

Cela présente en effet plusieurs avantages significatifs :

• Tout d'abord, cela permet de gagner un temps considérable en automatisant entièrement le processus. Plutôt que de passer par plusieurs étapes manuelles, telles que la sélection des cellules vides, l'application de la formules et la validation, un simple clic exécute l'ensemble du processus en quelques secondes. Cette automatisation est particulièrement utile lorsque nous travaillons avec de grandes quantités de données, où le temps nécessaire pour effectuer chaque étape manuellement peut s'accumuler rapidement.
• En outre, en réduisant le nombre d'actions manuelles nécessaires, cela réduit également les risques d'erreurs. L'automatisation garantit une cohérence dans le processus, ce qui signifie que chaque fois que vous exécutez la macro, le même ensemble d'opérations est effectué avec précision. Cela contribue à maintenir l'intégrité des données et à minimiser les erreurs potentielles dues à une manipulation manuelle.
• Enfin, l'automatisation en un clic rend le processus plus accessible à un plus grand nombre d'utilisateurs. Même ceux qui ne sont pas familiers avec les fonctions avancées d'Excel peuvent exécuter la macro avec facilité, ce qui élargit l'utilisation de cette technique à l'ensemble de l'équipe ou de l'organisation. Cela favorise la productivité globale en permettant à chacun de bénéficier des avantages de l'automatisation des tâches répétitives.

   5.2. Découvrir VBA en quelques mots

Pour effectuer cette opération, nous allons devoir mettre en place une petite macro-commande en VBA.

VBA est le langage de programmation intégré dans Excel et les autres applications de la suite Office, qui nous permet de créer des mini-programmes pour automatiser des tâches répétitives.

Bien que cela puisse sembler intimidant pour ceux qui n'ont pas d'expérience en programmation, ne vous inquiétez pas, VBA est conçu pour être accessible aux utilisateurs de tous niveaux, avec une syntaxe relativement simple.

Dans ce tutoriel, nous n’aurons pas forcément le temps de nous attarder longuement sur ce sujet, mais si vous souhaitez en savoir plus sur ce langage et découvrir comment celui-ci peut révolutionner votre manière d’utiliser Excel en décuplant les possibilités offertes par celui-ci, je vous invite à découvrir mon livre « Apprendre VBA pour les grands débutants ».

En quelques mots, VBA nous offre la possibilité de transformer nos actions manuelles en scripts automatisés, ouvrant ainsi un monde de possibilités pour augmenter notre efficacité dans Excel.

   5.3. Comment créer une macro sans être développeur avec l’enregistreur

Maintenant que nous avons compris l'importance de VBA et son potentiel pour automatiser nos tâches dans Excel, vous vous demandez peut-être : "Comment puis-je commencer à créer ma propre macro sans avoir de compétences de développement ?".

Pour cela, Excel dispose d'une fonctionnalité puissante appelée l'enregistreur de macro, qui permet à n'importe qui de créer facilement une macro sans écrire une seule ligne de code.

L'enregistreur de macro fonctionne en enregistrant chacune de vos actions dans Excel et en les traduisant en code VBA.

Cela signifie que vous pouvez simplement effectuer les actions que vous souhaitez automatiser, telles que la saisie de données, la mise en forme de cellules ou l'application de formules, et Excel se chargera de générer le code VBA correspondant.

Mais juste avant de lancer la macro, nous allons commencer par sélectionner les cellules correspondantes au mois de janvier.

En effet, le fait de sélectionner les cellules à retraiter avant d’enregistrer la macro va permettre de pouvoir traiter les cellules que l’utilisateur aura préalablement sélectionnées.

Dans le cas contraire, c’est-à-dire que si nous choisissions de sélectionner les cellules une fois l’enregistreur mis en route, alors le traitement s’exécuterait toujours sur ces mêmes cellules.

À présent, pour activer l'enregistreur de macro, il suffit cliquer sur l’icône "Enregistrer une macro" située en bas à gauche de votre interface Excel.

Ensuite, Excel affiche une fenêtre pour que nous puissions attribuer un nom à la macro.

Pour cet exemple, nous allons l’appeler « completionAuto ».

Une fois que l'enregistreur de macro est activé, Excel enregistrera toutes vos actions jusqu'à ce que nous décidions de l'arrêter en cliquant à nouveau sur l’icône située en bas à gauche.

Nous pouvons maintenant répéter l’ensemble des opréations que nous avons vu dans les parties précédentes, c’est-à-dire : désactiver les fusions éventuelles, réduire la sélection aux cellules vides uniquement, récupérer la valeur de la cellule située au-dessus et remplacer les formules par leur valeur.

Bien que l'enregistreur de macro soit un outil puissant pour automatiser des tâches simples, il est important de noter qu'il génère parfois un code VBA volumineux et peu optimisé.

Cependant, pour de nombreuses tâches courantes, l'enregistreur de macro est un excellent point de départ pour automatiser vos processus dans Excel.

Pour retrouver la macro générée, nous pouvons maintenant lancer l’outil de gestion des macros, dénommé VBE en utilisant le raccourci clavier [Alt]+[F11] :

Voici le résultat généré par l’enregistreur :

Sub completionAuto()
'
' competionAuto Macro
'
 
    Selection.UnMerge
    Application.CutCopyMode = False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=R[-1]C"
    Range(Selection,  ActiveCell.SpecialCells(xlLastCell)).Select
    Range("A7:A278").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues,  Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

   5.4. Adapter la macro

Maintenant que nous avons enregistré notre macro à l'aide de l'enregistreur, il est temps de l'adapter à nos besoins spécifiques et de l’optimiser.

En effet, comme nous venons de le voir, le code VBA généré par l'enregistreur peut parfois être plus complexe que nécessaire, et il peut être avantageux de le simplifier ou de le personnaliser pour qu'il corresponde exactement à ce que nous voulons accomplir.

Pour commencer, nous pouvons supprimer les opérations inutiles dans le code, telles que la sélection de cellules ou l'utilisation de "Application.CutCopyMode", lesquelles sont redondantes sont redondantes et peuvent être supprimées.

Nous supprimons donc les lignes suivantes :

…
    Application.CutCopyMode = False
    …
    Application.CutCopyMode = False
    …
    Range("A7:A278").Select
    Application.CutCopyMode = False

Ensuite, nous pouvons encore réduire le code généré automatiquement, en éliminant les sélections qui ne sont pas nécessaires avec la commande « Select ».

Nous pouvons supprimer ces sélections inutiles sont supprimées, car elles n'ont aucun impact sur le fonctionnement de la macro.

Nous pouvons directement travailler avec les cellules sans avoir besoin de les sélectionner au préalable.

Ainsi, les lignes :

    …
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Range(Selection,  ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    …

Deviennent :

    …
    Selection.SpecialCells(xlCellTypeBlanks) =  "=R[-1]C"
    Range(Selection,  ActiveCell.SpecialCells(xlLastCell)).Copy
    …

De plus, en ayant appliqué directement la formule « R[-1]C » sur les cellules vides de la sélection, cette dernière n’a pas été modifiée.

Il est donc inutile de la remettre en place.

La ligne :

    …
    Range(Selection,  ActiveCell.SpecialCells(xlLastCell)).Copy
    …

Devient plus simplement :

    …
    Selection.Copy
    …

Enfin, sur la dernière ligne qui permet d’effectuer le collage spécial en valeur, nous pouvons nous contenter de l’argument Paste qui permet de spécifier le type de collage à mettre en place.

• En effet, l'argument "Operation:=xlNone" spécifie l'opération à effectuer lors du collage, mais dans notre cas, nous n'avons pas besoin de spécifier cette opération car elle est implicite lorsque nous utilisons "xlPasteValues". Par conséquent, cette partie de l'argument peut être supprimée sans affecter le fonctionnement de la macro.
• De même, l'argument "SkipBlanks:=False" spécifie si les cellules vides doivent être ignorées lors du collage. Dans notre cas, nous voulons que les valeurs soient collées même dans les cellules vides, donc cet argument est également redondant et peut être supprimé.
• Enfin, l'argument "Transpose:=False" spécifie si les données collées doivent être transposées, c'est-à-dire si les lignes doivent devenir des colonnes et vice versa. Comme nous ne voulons pas transposer nos données, cet argument est également superflu et peut être retiré.

Ainsi, en supprimant ces arguments inutiles, notre ligne de code finale devient plus concise et plus claire :

    Selection.PasteSpecial Paste:=xlPasteValues,  Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Devient donc :

    Selection.PasteSpecial Paste:=xlPasteValues

   5.5. Tester la macro

Maintenant, pour tester la macro, il nous suffit de revenir sur la feuille de calcul, puis de sélectionner toutes les cellules de la colonne « Mois ».

Ensuite, nous pouvons afficher la liste des macros en utilisant le raccourci clavier [Alt]+[F8], sélectionner la macro que nous venons de créer afin de l’exécuter.