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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - remplir cellules vides - 01

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 :

Excel formation - remplir cellules vides - 02

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

Excel formation - remplir cellules vides - 03

  • 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 :

Excel formation - remplir cellules vides - 04

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 :

Excel formation - remplir cellules vides - 05

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

Excel formation - remplir cellules vides - 06

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] :

Excel formation - remplir cellules vides - 07

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

Excel formation - remplir cellules vides - 08

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 :

Excel formation - remplir cellules vides - 09

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 :

Excel formation - remplir cellules vides - 10

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

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

Excel formation - remplir cellules vides - 11

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 :

Excel formation - remplir cellules vides - 12

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

Excel formation - remplir cellules vides - 13

  • 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 :

Excel formation - remplir cellules vides - 14

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 » :

Excel formation - remplir cellules vides - 15

Puis en validant avec le bouton [OK] :

Excel formation - remplir cellules vides - 16

À 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

Excel formation - remplir cellules vides - 17

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 :

Excel formation - remplir cellules vides - 18

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] :

Excel formation - remplir cellules vides - 19

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

Excel formation - remplir cellules vides - 20

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

Excel formation - remplir cellules vides - 21

 

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) :

Excel formation - remplir cellules vides - 22

Puis nous dessinons cet objet sur la feuille de calcul :

Excel formation - remplir cellules vides - 23

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) :

Excel formation - remplir cellules vides - 24

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

Excel formation - remplir cellules vides - 25

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 formation - remplir cellules vides - 26

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

Excel formation - remplir cellules vides - 27

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 :

Excel formation - remplir cellules vides - 28

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

Excel formation - remplir cellules vides - 29

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

 

 

 



Articles qui pourraient vous intéresser

Comment exporter une feuille dans un classeur (ouvert ou fermé) sur Excel ?
Sortez du lot : comment créer un CV moderne et impactant avec Excel
Comment ajouter une apostrophe en début de cellule sur Excel ?
Comment cacher des données dans des cellules Excel ?
Comment certifier gratuitement une macro VBA sur Excel ?
Comment remplacer un texte dans une cellule avec la fonction REMPLACER d’Excel ?
Les arguments de valeurs et de références (ByVal et ByRef) de VBA sur Excel
Comment créer un menu dynamique sur Excel ?
Comment traduire un texte dans toutes les langues en utilisant une fonction Excel
Comment utiliser les fonctions de base d’Excel (Addition, soustraction, multiplication, division)
Comment trouver et contrôler toutes les formules d'une feuille de calcul Excel
Comment calculer des sous-totaux dans un tableau Excel : la fonction SOUS.TOTAL

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.