Regarder la vidéo - Remplir dans Excel
Souvent, vous pouvez rencontrer un ensemble de données où une seule cellule est remplie de données et les cellules en dessous sont vides jusqu'à la valeur suivante.
Quelque chose comme indiqué ci-dessous :
Bien que ce format fonctionne pour certaines personnes, le problème avec ce type de données est que vous ne pouvez pas l'utiliser pour créer des tableaux croisés dynamiques ou l'utiliser dans des calculs.
Et cela a une solution facile!
Dans ce tutoriel, je vais vous montrer comment rapidement remplir les cellules dans Excel jusqu'à la prochaine valeur remplie.
Vous pouvez facilement le faire à l'aide d'une simple technique de boîte de dialogue spéciale Go-To, VBA ou Power Query.
Alors, commençons!
Méthode 1 - Remplissez à l'aide de Go To Special + Formula
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez remplir les données dans les colonnes A et B.
Dans la colonne B, l'objectif est de remplir « Imprimante » jusqu'à la dernière cellule vide en dessous, puis lorsque « Scanner » démarre, puis remplissez « Scanner » dans les cellules ci-dessous jusqu'à ce que les cellules soient vides.
En dessous des étapes à suivre, accédez à spécial pour sélectionner toutes les cellules vides et remplissez-les à l'aide d'une formule simple :
- Sélectionnez les données que vous souhaitez remplir (A1:D21 dans notre exemple)
- Allez dans l'onglet "Accueil"
- Dans le groupe Édition, cliquez sur l'icône « Rechercher et sélectionner » (cela affichera d'autres options dans la liste déroulante)
- Cliquez sur l'option « Aller à »
- Dans la boîte de dialogue Go-To qui s'ouvre, cliquez sur le bouton Spécial. Cela ouvrira la boîte de dialogue « Aller à spécial »
- Dans la boîte de dialogue Go To Special, cliquez sur Blanks
- Cliquez sur OK
Les étapes ci-dessus sélectionneraient toutes les cellules vides de l'ensemble de données (comme indiqué ci-dessous).
Dans les cellules vides sélectionnées, vous remarquerez qu'une cellule est plus claire que le reste de la sélection. Cette cellule est la cellule active où nous allons entrer une formule.
Ne vous inquiétez pas de l'emplacement de la cellule dans la sélection, car notre méthode fonctionnerait dans tous les cas.
Voici maintenant les étapes pour remplir les données dans les cellules vides sélectionnées :
- Appuyez sur la touche égale à (=) de votre clavier. Cela insérera un signe égal à dans la cellule active
- Appuyez sur la touche fléchée vers le haut. Cela insérera la référence de cellule de la cellule au-dessus de la cellule active. Par exemple, dans notre cas, B3 est la cellule active et lorsque nous faisons ces deux étapes, il entre =B2 dans la cellule
- Maintenez la touche Ctrl enfoncée et appuyez sur la touche Entrée
Les étapes ci-dessus inséreraient automatiquement toutes les cellules vides avec la valeur au-dessus d'elles.
Bien que cela puisse sembler trop d'étapes, une fois que vous aurez maîtrisé cette méthode, vous pourrez rapidement remplir les données dans Excel en quelques secondes.
Maintenant, il y a deux autres choses dont vous devez faire attention lorsque vous utilisez cette méthode.
Convertir des formules en valeurs
La première consiste à vous assurer que vous convertissez les formules en valeurs (afin que vous ayez des valeurs statiques et que les choses ne se gâtent pas au cas où vous modifieriez les données à l'avenir).
Vous trouverez ci-dessous une vidéo qui vous montrera comment convertir rapidement des formules en valeurs dans Excel.
Modifier le format de la date
Si vous utilisez des dates dans vos données (comme je le suis dans les données d'exemple), vous remarquerez que les valeurs remplies dans la colonne de date sont des nombres et non des dates.
Si la sortie dans la colonne de date est au format de date souhaité, vous êtes bon et n'avez rien d'autre à faire.
Mais si ceux-ci ne sont pas dans le format de date correct, vous devez effectuer un ajustement mineur. Tant que vous avez les bonnes valeurs, il vous suffit de modifier le format pour qu'il s'affiche sous forme de date dans la cellule.
Vous trouverez ci-dessous les étapes à suivre :
- Sélectionnez la colonne qui a les dates
- Cliquez sur l'onglet Accueil
- Dans le groupe Nombre, cliquez sur la liste déroulante Format, puis sélectionnez le format de date.
Si vous devez faire ce remplissage de temps en temps, je vous recommande d'utiliser cette technique spéciale et formule Go-To.
Bien qu'il comporte quelques étapes, il est simple et vous donne le résultat directement dans votre ensemble de données.
Mais au cas où vous auriez à le faire assez souvent, je vous suggère de regarder les méthodes VBA et Power Query abordées ensuite
Méthode 2 - Remplissez à l'aide d'un code VBA simple
Vous pouvez utiliser un code macro VBA très simple pour remplir rapidement les cellules dans Excel jusqu'à la dernière valeur vide.
Vous n'avez besoin d'ajouter le code VBA au fichier qu'une seule fois, puis vous pouvez facilement réutiliser le code plusieurs fois dans le même classeur ou même dans plusieurs classeurs sur votre système.
Vous trouverez ci-dessous le code VBA qui parcourra chaque cellule de la sélection et remplira toutes les cellules vides :
'Ce code VBA est créé par Sumit Bansal à partir de TrumpExcel.com Sub FillDown() pour chaque cellule de la sélection Si cellule = "" Then cell.FillDown End If Next End Sub
Le code ci-dessus utilise une boucle For pour parcourir chaque cellule de la sélection.
Dans la boucle For, j'ai utilisé une condition If-Then qui vérifie si la cellule est vide ou non.
Si la cellule est vide, elle est remplie avec la valeur de la cellule ci-dessus, et si elle n'est pas vide, la boucle for ignore simplement la cellule et passe à la suivante.
Maintenant que vous avez le code VBA, laissez-moi vous montrer où mettre ce code dans Excel :
- Sélectionnez les données que vous souhaitez remplir
- Cliquez sur l'onglet Développeur
- Cliquez sur l'icône Visual Basic (ou vous pouvez utiliser le raccourci clavier ALT + F11). Cela ouvrira l'éditeur Visual Basic dans Excel
- Dans Visual Basic Editor, à gauche, vous auriez l'Explorateur de projets. Si vous ne le voyez pas, cliquez sur l'option « Affichage » dans le menu, puis cliquez sur Explorateur de projets
- Si vous avez plusieurs classeurs Excel ouverts, l'Explorateur de projets vous montrera tous les noms des classeurs. Recherchez le nom du classeur où vous avez les données.
- Cliquez avec le bouton droit sur l'un des objets du classeur, accédez à Insérer, puis cliquez sur Module. Cela insérera un nouveau module pour ce classeur
- Double-cliquez sur le "Module" que vous venez d'insérer à l'étape ci-dessus. Il ouvrira la fenêtre de code pour ce module
- Copiez-collez le code VBA dans la fenêtre de code
- Placez le curseur n'importe où dans le code puis exécutez le code de la macro en cliquant sur le bouton vert dans la barre d'outils ou en utilisant le raccourci clavier F5
Les étapes ci-dessus exécuteraient le code VBA et vos données seraient remplies.
Si vous souhaitez réutiliser ce code VBA à l'avenir, vous devez enregistrer ce fichier en tant que classeur Excel prenant en charge les macros (avec une extension .XLSM).
Une autre chose que vous pouvez faire est d'ajouter cette macro à votre barre d'outils d'accès rapide, qui est toujours visible et vous aurez accès à cette macro en un seul clic (dans le classeur où vous avez le code dans le backend).
Ainsi, la prochaine fois que vous aurez des données à remplir, il vous suffira de faire la sélection, puis de cliquer sur le bouton macro dans la barre d'outils d'accès rapide.
Vous pouvez également ajouter cette macro au classeur de macros personnelles, puis l'utiliser dans n'importe quel classeur de votre système.
Cliquez ici pour en savoir plus sur les classeurs de macros personnelles et comment leur ajouter du code.
Méthode 3 - Remplir à l'aide de Power Query
Power Query a une fonctionnalité intégrée qui vous permet de remplir en un seul clic.
L'utilisation de Power Query est recommandée lorsque vous l'utilisez de toute façon pour transformer vos données ou combiner des données à partir de plusieurs feuilles de calcul ou de plusieurs classeurs.
Dans le cadre de votre flux de travail existant, vous pouvez utiliser l'option de remplissage dans Power Query pour remplir rapidement les cellules vides.
Pour utiliser Power Query, il est recommandé que vos données soient au format de tableau Excel. Si vous ne pouvez pas convertir vos données dans un tableau Excel, vous devrez créer une plage nommée pour les données, puis utiliser cette plage nommée dans Power Query.
Ci-dessous, j'ai l'ensemble de données que j'ai déjà converti en un tableau Excel. Vous pouvez le faire en sélectionnant le jeu de données, en allant dans l'onglet Insertion, puis en cliquant sur l'icône Table.
Ci-dessous les étapes pour utiliser Power Query pour remplir les données jusqu'à la valeur suivante :
- Sélectionnez n'importe quelle cellule dans l'ensemble de données
- Cliquez sur l'onglet Données
- Dans le groupe Obtenir et transformer les données, cliquez sur « À partir de la feuille ». Cela ouvrira l'éditeur Power Query. Notez que les cellules vides afficheraient la valeur « null » dans Power Query
- Dans l'éditeur Power Query, sélectionnez les colonnes que vous souhaitez remplir. Pour cela, maintenez la touche Contrôle enfoncée puis cliquez sur l'en-tête des colonnes que vous souhaitez sélectionner. Dans notre exemple, il s'agirait des colonnes Date et Produit.
- Faites un clic droit sur l'un des en-têtes sélectionnés
- Allez dans l'option "Remplir" puis cliquez sur "Bas". Cela remplirait les données dans toutes les cellules vides
- Cliquez sur l'onglet Fichier, puis sur « Fermer et charger ». Cela insérera une nouvelle feuille de calcul dans le classeur avec le tableau résultant
Bien que cette méthode puisse sembler un peu exagérée, l'un des grands avantages de l'utilisation de Power Query est qu'elle vous permet de mettre rapidement à jour les données résultantes au cas où vos données d'origine changeraient.
Par exemple, si vous ajoutez d'autres enregistrements aux données d'origine, vous n'avez pas besoin de refaire tout ce qui précède. Vous pouvez simplement cliquer avec le bouton droit sur la table résultante, puis cliquer sur Actualiser.
Bien que la méthode Power Query fonctionne bien, il est préférable de l'utiliser lorsque vous utilisez déjà Power Query dans votre flux de travail.
Si vous ne disposez que d'un ensemble de données dans lequel vous souhaitez remplir les cellules vides, l'utilisation de la méthode spéciale Go To ou de la méthode VBA (décrite ci-dessus) serait plus pratique.
Voici donc trois moyens simples que vous pouvez utiliser pour remplir les cellules vides jusqu'à la prochaine valeur dans Excel.
J'espère que vous avez trouvé ce tutoriel utile.
