Comment rechercher et remplir les valeurs manquantes dans Excel (sans deviner)
Localisez rapidement les cellules vides, décidez si vous souhaitez les remplir, les marquer ou les laisser, et utilisez des formules ou l'IA pour compléter les données manquantes dans Excel — avec une piste d'audit de ce qui a changé.
Les valeurs manquantes constituent le moyen le plus discret pour une feuille de calcul de vous mentir. Une cellule vide dans une colonne de coût ne perd pas seulement un chiffre : elle réduit silencieusement chaque AVERAGE, fausse chaque pivot et transforme les calculs de bénéfices en erreurs.
Voici un flux de travail discipliné : recherchez chaque espace, décidez ce que chacun d'entre eux est signifie et remplissez uniquement ceux qui doivent être remplis, avec un enregistrement de ce qui a changé.
Réponse rapide
Identifiez d'abord si une cellule est vraiment vide, un résultat de formule vide, un espace, un zéro ou « non applicable ». Remplissez ensuite uniquement les valeurs qui peuvent être récupérées à partir d'une règle ou d'une source approuvée. Placez les cas non résolus dans une colonne d'état, conservez les données d'origine et validez les totaux après le remplissage. Ne remplacez jamais chaque blanc par zéro ou une moyenne par défaut.
Étape 1 : Rechercher toutes les valeurs manquantes
Trois méthodes rapides, la plus rapide en premier :
Aller à Spécial. Sélectionnez votre plage de données, appuyez sur F5 → Spécial… → Blancs → OK. Chaque cellule vide de la plage est désormais sélectionnée ; donnez-leur une couleur de remplissage pour qu'ils soient visibles.
Comptez-les par colonne :
=COUNTBLANK(B2:B1000)
Filtrez-les. Ajoutez un filtre (Ctrl+Maj+L), ouvrez la liste déroulante d'une colonne et vérifiez (vierges) pour voir exactement quelles lignes sont affectées.
Surveillez également les blancs faux : cellules contenant un espace ou une chaîne vide "" renvoyée par une formule. COUNTBLANK compte "" mais Aller à Spécial → Blancs ne le sélectionne pas. Cette inadéquation est une source classique de confusion :
=SUMPRODUCT(--(TRIM(B2:B1000)=""))
compte à la fois les vrais blancs et les cellules contenant uniquement des espaces.
Étape 2 : Décidez de la signification de chaque espace
C'est l'étape que la plupart des gens sautent. Un blanc peut être :
| Signification | Action juste |
|---|---|
| Les données existent mais n'ont pas été saisies | Remplir à partir de la source |
| Vraiment zéro | Saisissez explicitement 0 |
| Sans objet | Marquez N/A (sous forme de texte) donc c'est délibéré |
| Inconnu / nécessite un suivi | Signalez-le, n'inventez pas de numéro |
Remplir « inconnu » avec un nombre inventé est pire que de le laisser vide : vous avez converti une incertitude visible en erreur invisible.
Étape 3 : Remplissez ceux qui doivent être remplis
Remplir par le haut (commun pour les exports de rapports où une catégorie apparaît une fois par groupe) : sélectionnez la plage, F5 → Spécial → Blancs, tapez = puis appuyez sur la flèche haut, et validez par Ctrl+Entrée. Chaque espace copie désormais la valeur située au-dessus. Convertissez ensuite en valeurs avec Collage spécial.
Calculer à partir d'autres colonnes. Si le coût est manquant mais que les revenus et les bénéfices existent :
=IF(B2="", C2-D2, B2)
Recherchez-le sur une autre feuille :
=IF(B2="", XLOOKUP(A2, Ref!A:A, Ref!B:B, "no match"), B2)
Étape 4 : Conserver une piste d'audit
Quelle que soit la manière dont vous remplissez les blancs, enregistrez les cellules qui ont été modifiées : une couleur de surbrillance, une colonne d'état "remplie" ou un journal des modifications. À l'avenir, vous devrez distinguer les données originales des données reconstruites.
Un tableau d'audit utile contient :
| Champ | Exemple |
|---|---|
| Clé de ligne ou d'enregistrement | Order-1042 |
| Colonne modifiée | Cost |
| Valeur originale | vierge |
| Nouvelle valeur | 42.50 |
| Source ou règle | Prices!B:B via SKU |
| Statut de l'examen | Verified |
Pour les grands ensembles de données, comptez les valeurs manquantes avant et après par colonne. Un nombre de blancs inférieur ne suffit pas : le nombre de valeurs non résolues et remplies doit correspondre au total d'origine.
Méthodes à utiliser et quand
- Remplir par le haut : uniquement lorsque les cellules vides héritent d'une étiquette de groupe de par leur conception.
- Recherche à partir d'une table de référence : est préférable lorsqu'il existe une clé stable et une source faisant autorité.
- Calculer à partir d'autres champs : sécurisé lorsque la relation est une identité comptable ou commerciale.
- Imputation statistique : approprié pour les modèles d'analyse, mais généralement incorrect pour les enregistrements opérationnels, à moins que la méthode ne soit documentée.
- Laisser vide et signaler : corrige lorsque la valeur est véritablement inconnue.
Si vous ne pouvez pas expliquer d'où vient une valeur remplie, ne la réécrivez pas comme un fait.
La version à une instruction
L'ensemble de ce flux de travail est une requête unique adressée à un assistant qui travaille dans votre classeur. Avec IA pour Excel ouvert dans la barre latérale :
"Trouvez toutes les valeurs manquantes dans ce tableau. Remplissez les coûts à partir de la feuille de référence lorsque cela est possible, définissez les vrais zéros sur 0, marquez le reste dans une nouvelle colonne Statut et dites-moi ce que vous avez modifié."
Le complément lit la plage, applique chaque remplissage, écrit un statut par ligne et résume le résultat - comme la démo sur notre page d'accueil, où un coût manquant est complété et la colonne de profit est réécrite. Parce qu'il capture le classeur avant de l'écrire et vérifie ce qu'il a écrit, « L'IA a rempli mes données » ne signifie jamais « J'ai perdu la trace de mes données ».
Conseils Excel associés
Les valeurs manquantes font généralement partie d'un travail de nettoyage plus large. Continuez avec le liste de contrôle complète pour le nettoyage des données Excel et examinez le principales façons de nettoyer les données dans Excel du Microsoft.
FAQ
Comment mettre en surbrillance toutes les cellules vides dans Excel ?
Sélectionnez la plage, appuyez sur F5, choisissez Spécial → Blancs, puis appliquez une couleur de remplissage pendant qu'ils sont sélectionnés. Le formatage conditionnel avec la formule =ISBLANK(A2) maintient automatiquement les futurs blancs en surbrillance.
Les valeurs manquantes doivent-elles être nulles ou vides ?
Entrez 0 uniquement lorsque la valeur est réellement nulle. Un blanc signifie « aucune donnée » et le traiter comme zéro change les moyennes et les ratios. Si une valeur est inconnue, marquez-la comme inconnue plutôt que d’inventer un nombre.
L'IA peut-elle remplir automatiquement les données manquantes dans Excel ?
Oui, mais insistez sur trois garanties : l'outil doit indiquer où d'où provient chaque valeur remplie, marquer les cellules remplies afin qu'elles puissent être distinguées des originaux et sauvegarder la feuille avant d'écrire. L'IA pour Excel effectue les trois et vous permet d'annuler l'intégralité de la modification si nécessaire.