Comment compléter les cellules vides en 1 clic sur Excel

Dans ce tutoriel je vais vous montrer deux méthodes qui vous nous permettre de compléter automatiquement des cellules vides en reprenant le contenu des cellules situées justes au-dessus. Nous y découvrirons dans un premier temps une méthode qui va nous permettre d’effectuer cette opération de manière semi-automatisée, puis dans un second temps une autre méthode pour parvenir au même résultat en un seul clic.

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. Présentation

Comme nous l’avons vu dans l’introduction, de ce tutoriel, nous allons souhaiter remplir automatiquement les cellules vides contenues dans un tableau en récupérant la valeur des cellules situées juste au-dessus.

Cela nous permettra par exemple de pouvoir exploiter un tableau qui ne serait pas prévu pour en tant que base source d’un tableau croisé dynamique.

Pour illustrer cet exemple, nous allons partir de la base de données suivante :

Nous y retrouvons les ventes réalisées par des commerciaux d’une entreprise, pays par pays.

Ces pays sont inscrits dans la première colonne du tableau, et pour éviter les répétitions, les cellules sont fusionnées.

Cela permet d’aérer le tableau pour le rendre pour simple à lire, mais ne permet pas d’exploiter cette base en tant que source d’un tableau croisé dynamique correctement.

Pour nous en rendre compte, nous pouvons simplement le créer :

• Nous commençons par sélectionner l’une des cellules de la base :

• Puis, nous nous rendons dans le menu Insertion > Tableau croisé dynamique :

• Excel nous affiche ensuite une fenêtre pour que nous puissions valider que les données ont bien été sélectionnées. Nous pouvons alors cliquer sur le bouton [OK] pour confirmer la création du TCD sur une nouvelle feuille de calcul :

Une fois le TCD correctement inséré, nous pouvons l’alimenter de manière classique, en faisant simplement glisser les champs depuis le menu « Champs de tableau croisé dynamique », situé sur la partie droite de la fenêtre :

Nous pouvons alors constater le problème sur le TCD :

Chaque pays ne comprend qu’une seule ligne, toutes les lignes suivantes étant réunies dans la section « (vide) ».

Pour régler ce problème, nous allons donc devoir compléter automatiquement les cellules vides.

2. Compléter les cellules vides dans un tableau manuellement

Pour commencer, voyons comment récupérer automatiquement la valeur de la cellule située juste au-dessus.

La première chose à faire va alors consister à supprimer toutes les fusions présentes au sein du tableau.

Nous commençons par sélectionner toutes les cellules du tableau, soit à l’aide de la souris (en sélectionnant la première cellule, puis en faisant glisser le curseur jusqu’à la dernière cellule de ce tableau), soit en utilisant le raccourci clavier [Ctrl]+[A] :

Ensuite, nous effectuons un clic-droit > Format de cellule :

Dans l’onglet Alignement, l’option « Fusionner les cellules » est activée avec un carré noir, ce qui signifie que certaines cellules sont fusionnées :

Nous cliquons une première fois pour activer la fusion sur toutes les cellules sélectionnées, puis une seconde fois pour ôter la sélection :

Puis nous validons en appuyant sur la touche [Entrée].

Nous pouvons maintenant sélectionner les cellules qui étaient fusionnées jusque-là :

Maintenant, voyons comment remplir ces cellules vides.

Nous pourrions dans un premier temps envisager d’effectuer un copier-coller de la cellule située juste au-dessus :

Cela fonctionne effectivement, par contre si le tableau étudié est composé de nombreuses cellules à traiter, il faudra alors réaliser cette opération pour TOUTES les cellules vides, ce qui risque de prendre énormément de temps…

Nous allons donc préférer utiliser une méthode qui permette de compléter toutes les cellules directement !

Pour cela, nous allons procéder de la manière suivante :

Pour commencer, nous sélectionnons toutes les cellules du tableau, comme nous l’avons fait un peu plus tôt.

Ensuite, nous allons ouvrir la fenêtre de sélection de cellules particulières, pour cela, nous pouvons une fois encore choisir parmi plusieurs solutions :

• Soit nous rendre dans le menu Accueil, puis tout à droite dérouler le menu Rechercher et sélectionner afin de cliquer sur Sélectionner les cellules…

• Soit nous appuyons simplement sur la touche [F5] du clavier pour lancer la fenêtre atteindre, à partir de laquelle nous allons appuyer sur le bouton Cellules :

Une fois la fenêtre Sélectionner les cellules à l’écran, nous pouvons réduire la sélection active aux seules cellules vides en choisissant l’option « Cellules vides » :

Puis en validant avec le bouton [OK] :

À partir de là, il ne reste plus qu’à demander à chacune de ces cellules de récupérer la valeur contenue dans la cellule située juste au-dessus.

Étant donné que la cellule active est la cellule A10, nous allons donc utiliser la formule :

=A9

Ensuite, il ne reste plus qu’à valider la formule en appuyant sur les touches [Ctrl]+[Entrée], ce qui permettra de valider cette formule sur l’ensemble de toutes les cellules sélectionnées en même temps :

Pour finir, si nous le souhaitons nous pouvons transformer ces cellules en valeur à la place des formules.

Pour cela, nous sélectionnons toutes les cellules de la colonne pour les copier [Ctrl]+[C] :

Et nous en effectuons un collage spécial en valeur en effectuant un clic-droit > Collage « Valeurs » :

Nous pouvons maintenant actualiser le TCD pour constater que celui-ci correspond maintenant au résultat souhaité initialement :

3. Compléter des cellules vides en 1 clic

Maintenant voyons une seconde possibilité, pas forcément plus compliquée malgré la mise en place d’une macro-commande en VBA, et qui va nous permettre de réaliser cette opération en un seul clic.

Pour cela, nous commençons par créer un bouton en utilisant un objet Zone de texte (menu Insertion > Zone de texte) :

Puis nous dessinons cet objet sur la feuille de calcul :

Ensuite, nous saisissons un texte au bouton et nous le mettons en forme (en utilisant simplement un style prédéfini : Menu Mise en forme, puis nous sélectionnons un style depuis le menu déroulant) :

Une fois ces opérations terminées, nous pouvons simplement effectuer un clic-droit sur le bouton, pour choisir l’option Affecter une macro.

Nous saisissons un nom que nous souhaitons donner à la macro-commande (par exemple « remplirVides », évidemment sans utiliser d’espace, ni d’accent), puis nous cliquons sur le bouton Nouvelle pour qu’Excel créé automatiquement une nouvelle procédure au sein du projet, laquelle sera appelée dès que nous cliquerons sur le bouton :

Excel a alors inséré la nouvelle macro dans un module :

Il suffit alors de saisir nos lignes de code entre les lignes ajoutées pour que celles-ci soient lancées automatiquement lors de chaque clic sur le bouton.

La macro que nous allons mettre en place est très simple : nous allons passer en revue chaque cellule de la sélection pour en analyser la valeur.

Si celle-ci est vide (donc son résultat est égal à ""), alors nous pouvons lui affecter la valeur de la cellule située juste au-dessus.

Pour passer en revue les cellules de la sélection, nous commençons par déclarer une nouvelle variable que nous appelons r et que nous typons en tant qu’objet Range :

Sub remplirVides()
    Dim r As Range    
End Sub

Ensuite, nous allons pouvoir utiliser une boucle For Each qui permet de boucler sur toutes les cellules contenues dans la plage de cellule retournée par Selection (vous trouverez toutes les informations sur la boucle For Each en cliquant ici) :

    For Each r In Selection
    
    Next

Et pour finir, il ne reste plus qu’à effectuer le test permettant de savoir si la cellule en cours d’analyse (rendue par la variable r est vide), puis à récupérer la valeur de la cellule contenue juste au-dessus en utilisant l’instruction Offset qui permet d’effectuer un décalage de cellule (en l’occurrence, un décalage d’une cellule vers le haut, soit une valeur de -1 sur le premier argument) :

    For Each r In Selection
        If r = "" Then r = r.Offset(-1,  0)
    Next

Pour tester, nous revenons sur la feuille de calcul, nous supprimons les cellules dupliquées dans la première partie, nous sélectionnons toutes les cellules de la première colonne et nous cliquons sur le bouton :

Et voilà, le résultat est alors identique, mais il suffit ici de ne cliquer que sur un bouton pour effectuer cette opération :

Bien entendu, pensez à bien enregistrer le fichier en *.xlsm pour que la macro puisse être correctement enregistrée !