Plusieurs fois, vous pouvez rencontrer un jeu de données Excel dans lequel une seule cellule est remplie de données et les cellules en dessous sont vides jusqu'à la valeur suivante.
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.
C'est facile à réparer !
Dans ce tutoriel, je vais vous montrer commentRemplir rapidement les cellules jusqu'à la prochaine valeur de remplissage dans Excel.
Vous pouvez le faire facilement en utilisant de simples techniques de dialogue spéciales Go-To, VBA ou Power Query.
Alors, commençons!
Table des matières
Méthode 1 - Remplir à l'aide de la formule Go To Special +
Supposons que vous disposiez d'un ensemble de données comme indiqué ci-dessous et que vous souhaitiez remplir les données des colonnes A et B.
Dans la colonne B, le but est de remplir "l'imprimante" jusqu'à la dernière cellule vide en dessous, puis lorsque le "scanner" démarre, puis de remplir le "scanner" dans la cellule en dessous jusqu'à ce que la cellule soit vide.
Les étapes ci-dessous utilisent aller à spécial pour sélectionner toutes les cellules vides et remplir avec une formule simple :
- Sélectionnez les données à remplir (A1:D21 dans notre exemple)
- Allez dans l'onglet "Accueil"
- Dans le groupe d'édition, cliquez sur l'icône Rechercher et sélectionner (cela révélera plus d'options dans le menu déroulant)
- Cliquez sur l'option "Aller"
- Dans la boîte de dialogue Aller à 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 Aller à spécial, cliquez sur Vide
- Cliquez sur OK
Les étapes ci-dessus sélectionneront toutes les cellules vides dans l'ensemble de données (voir ci-dessous).
Parmi les cellules vides sélectionnées, vous remarquerez qu'une cellule est plus lumineuse que le reste de la sélection.Cette cellule est la cellule active dans laquelle nous voulons entrer la formule.
Ne vous souciez pas de la position de la cellule dans la sélection, car notre méthode fonctionnera dans tous les cas.
Maintenant, voici les étapes pour remplir les données dans les cellules vides sélectionnées :
- Cliquez sur la touche signe égal (=) 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 Control enfoncée et appuyez sur Entrée
Les étapes ci-dessus inséreront automatiquement toutes les cellules vides avec les valeurs ci-dessus.
Bien que cela puisse sembler trop d'étapes, une fois que vous aurez compris, vous pourrez remplir rapidement des données dans Excel en quelques secondes.
Maintenant, il y a deux autres choses dont vous devez être conscient lorsque vous utilisez cette méthode.
Convertir la formule en valeur
La première consiste à vous assurer que vous convertissez les formules en valeurs (afin que vous ayez des valeurs statiques et que vous ne bousilliez pas lorsque vous modifiez les données à l'avenir).
Modifier le format de date
Si vous utilisez des dates dans vos données (comme je l'ai fait dans mon exemple de données), vous remarquerez que les valeurs renseignées dans la colonne de date sont des nombres au lieu de dates.
Si la sortie dans la colonne de date est dans le format de date souhaité, alors tout va bien et vous n'avez rien d'autre à faire.
Mais si ces dates ne sont pas au format correct, un réglage fin est nécessaire.Tant que vous avez la bonne valeur, il vous suffit de modifier le format pour qu'il apparaisse comme une date dans la cellule.
Voici les étapes à suivre :
- Sélectionner les colonnes contenant des dates
- Cliquez sur l'onglet Accueil
- Dans le groupe Nombres, cliquez sur le menu déroulant Format et sélectionnez un format de date.
Si vous avez besoin de faire ce rembourrage de temps en temps, je vous recommande cette technique Go-To spéciale et formule.
Bien qu'il comporte quelques étapes, il est simple et vous donne des résultats dans l'ensemble de données.
Mais si vous devez le faire souvent, je vous suggère de regarder VBA et la méthode Power Query décrite ci-dessous.
Méthode 2 - Remplir avec un simple code VBA
Vous pouvez utiliser un code de macro VBA très simple pour remplir rapidement des cellules dans Excel jusqu'à la dernière valeur vide.
Vous n'avez besoin d'ajouter qu'une seule fois du code VBA à un fichier, puis vous pouvez facilement réutiliser ce code plusieurs fois dans le même classeur ou même dans plusieurs classeurs de votre système.
Voici le code VBA qui parcourra chaque cellule de la sélection et remplira toutes les cellules vides :
Sub FillDown() Pour chaque cellule de la sélection Si cellule = "" Alors cellule.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'utilise une condition If-Then pour vérifier si la cellule est vide.
Si la cellule est vide, elle est remplie avec la valeur de la cellule du dessus, sinon, la boucle for ignore la cellule et passe à la cellule suivante.
Maintenant que vous avez le code VBA, laissez-moi vous montrer où mettre ce code dans Excel :
- Sélectionnez les données à 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 l'éditeur Visual Basic sur la gauche, vous aurez l'explorateur de projet.Si vous ne le voyez pas, cliquez sur l'option Affichage dans le menu, puis cliquez sur Explorateur de projet
- Si plusieurs classeurs Excel sont ouverts, l'Explorateur de projets affiche tous les noms de classeurs.Recherchez le nom du classeur dans lequel vous avez les données.
- Cliquez avec le bouton droit sur n'importe quel objet 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 dans les étapes ci-dessus.Cela ouvrira la fenêtre de code pour ce module
- Copiez et collez le code VBA dans la fenêtre de code
- Placez le curseur n'importe où dans le code et 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écuteront le code VBA et vos données seront remplies.
Si vous souhaitez réutiliser ce code VBA ultérieurement, vous devrez enregistrer ce fichier en tant que classeur Excel prenant en charge les macros (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 pouvez accéder à cette macro en un seul clic (dans un classeur avec du code dans le backend).
Ainsi, la prochaine fois que vous aurez des données à remplir, vous pourrez simplement faire une sélection et cliquer sur le bouton macro dans la barre d'outils d'accès rapide.
Vous pouvez également ajouter cette macro à votre classeur de macros personnel, puis l'utiliser dans n'importe quel classeur de votre système.
Cliquez ici pour en savoir plus sur votre classeur de macros personnel et sur la façon d'y ajouter du code.
Méthode 3 - Remplir à l'aide de Power Query
Requête d'alimentationPossède une fonctionnalité intégrée qui vous permet de le remplir en un seul clic.
Il est recommandé lorsque vous utilisez encore Power Query pour transformer des données ou combiner des données provenant de plusieurs feuilles de calcul ou de plusieurs classeurs.
Dans le cadre d'un flux de travail existant, vous pouvez rapidement remplir des cellules vides à l'aide de l'option Remplir dans Power Query.
Pour utiliser Power Query, il est recommandé que vos données soient au format tableau Excel.Si vous ne pouvez pas convertir les données en tableau Excel, vous devez créer une plage nommée pour les données, puis utiliser cette plage nommée dans Power Query.
Ci-dessous, j'ai un ensemble de données qui a été converti en tableau Excel.Pour ce faire, sélectionnez le jeu de données, accédez à l'onglet Insertion et cliquez sur l'icône Table.
Voici les étapes à suivre 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 de calcul.Cela ouvrira l'éditeur Power Query.Notez que les cellules vides afficheront la valeur "null" dans Power Query
- Dans l'éditeur Power Query, sélectionnez la colonne que vous souhaitez remplir.Pour ce faire, maintenez la touche Contrôle enfoncée et cliquez sur l'en-tête de la colonne que vous souhaitez sélectionner.Dans notre exemple, ce seront les colonnes de date et de produit.
- Faites un clic droit sur n'importe quel titre sélectionné
- Accédez à l'option Remplir et cliquez sur Bas.Cela remplira toutes les cellules vides avec des données
- Cliquez sur l'onglet Fichier, puis cliquez sur Fermer et charger.Cela insérera une nouvelle feuille de calcul avec le tableau de résultats dans le classeur
Bien que cette approche puisse sembler exagérée, l'un des grands avantages de l'utilisation de Power Query est qu'elle vous permet de mettre à jour rapidement les données résultantes lorsque les données d'origine changent.
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 et 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 n'avez qu'un seul ensemble de données avec des cellules vides à remplir, il est plus pratique d'utiliser la méthode spéciale Aller à ou la méthode VBA (décrite ci-dessus).
Vous pouvez donc utiliser ces trois méthodes simples pourRemplir les cellules vides jusqu'à la valeur suivante dans Excel.
J'espère que vous avez trouvé ce tutoriel utile.
