10 façons super soignées de nettoyer les données dans les feuilles de calcul Excel

Regarder la vidéo - 10 façons de nettoyer les données dans Excel

Les données constituent l'épine dorsale de toute analyse que vous effectuez dans Excel. Et en ce qui concerne les données, il y a des tonnes de choses qui peuvent mal tourner - que ce soit la structure, le placement, le formatage, les espaces supplémentaires, etc.

Dans cet article de blog, je vais vous montrer 10 façons simples de nettoyer les données dans Excel.

#1 Débarrassez-vous des espaces supplémentaires

Les espaces supplémentaires sont douloureusement difficiles à repérer. Bien que vous puissiez repérer les espaces supplémentaires entre les mots ou les nombres, les espaces de fin ne sont même pas visibles. Voici un bon moyen de se débarrasser de ces espaces supplémentaires - Utilisez la fonction TRIM.

Syntaxe : TRIM(texte)

La fonction Excel TRIM prend la référence de la cellule (ou le texte) comme entrée. Il supprime les espaces de début et de fin ainsi que les espaces supplémentaires entre les mots (sauf les espaces simples).

#2 Sélectionnez et traitez toutes les cellules vides

Les cellules vides peuvent créer des ravages si elles ne sont pas traitées au préalable. Je rencontre souvent des problèmes avec des cellules vides dans un ensemble de données utilisé pour créer des rapports/tableaux de bord.

Vous voudrez peut-être remplir toutes les cellules vides avec « 0 » ou « Non disponible », ou simplement le mettre en surbrillance. S'il y a un énorme ensemble de données, le faire manuellement peut prendre des heures. Heureusement, il existe un moyen de sélectionner toutes les cellules vides à la fois.

1. Sélectionnez l'ensemble des données
2. Appuyez sur F5 (cela ouvre la boîte de dialogue Go To)
3. Cliquez sur le bouton Spécial… (en bas à gauche). Cela ouvre la boîte de dialogue Go To Special
   
4. Sélectionnez Vide et cliquez sur OK
   

Cela sélectionne toutes les cellules vides de votre ensemble de données. Si vous voulez entrer 0 ou Non disponible dans toutes ces cellules, tapez-le simplement et appuyez sur Contrôle + Entrée (n'oubliez pas que si vous appuyez uniquement sur Entrée, la valeur est insérée uniquement dans la cellule active).

#3 Convertir des nombres stockés sous forme de texte en nombres

Parfois, lorsque vous importez des données à partir de fichiers texte ou de bases de données externes, les nombres sont stockés sous forme de texte. De plus, certaines personnes ont l'habitude d'utiliser une apostrophe (‘) avant un nombre pour en faire du texte. Cela pourrait créer de sérieux problèmes si vous utilisez ces cellules dans les calculs. Voici un moyen infaillible de reconvertir ces nombres stockés sous forme de texte en nombres.

1. Dans n'importe quelle cellule vide, tapez 1
2. Sélectionnez la cellule où vous avez tapé 1, et appuyez sur Ctrl + C
3. Sélectionnez la cellule/plage que vous souhaitez convertir en nombres
4. Sélectionnez Coller -> Collage spécial (Raccourci clavier - Alt + E + S)
5. Dans la boîte de dialogue Coller spécial, sélectionnez Multiplier (dans la catégorie des opérations)
   
6. Cliquez sur OK. Cela convertit tous les nombres au format texte en nombres.

Vous pouvez faire beaucoup plus avec les options d'opérations spéciales de collage. Voici diverses autres façons de multiplier dans Excel à l'aide de Collage spécial.

#4 - Supprimer les doublons

Il peut y avoir 2 choses que vous pouvez faire avec des données en double : Mettez-le en évidence ou alors Supprime-le.

• Mettez en surbrillance les données en double :
  • Sélectionnez les données et accédez à Accueil -> Mise en forme conditionnelle -> Règles de mise en évidence des cellules -> Valeurs en double.
  • Spécifiez le formatage et toutes les valeurs en double sont mises en évidence.
    
• Supprimer les doublons dans les données :
  • Sélectionnez les données et accédez à Données -> Supprimer les doublons.
  • Si vos données ont des en-têtes, assurez-vous que la case en haut à droite est cochée.
  • Sélectionnez la ou les colonnes dont vous souhaitez supprimer les doublons et cliquez sur OK.
    

Cela supprime les valeurs en double de la liste. Si vous voulez que la liste d'origine soit intacte, copiez-collez les données à un autre emplacement, puis procédez ainsi.

En rapport: Le guide ultime pour rechercher et supprimer les doublons dans Excel.

#5 Mettre en évidence les erreurs

Il existe 2 façons de mettre en évidence les erreurs dans les données dans Excel :

Utilisation de la mise en forme conditionnelle

1. Sélectionnez l'ensemble des données
2. Allez dans Accueil -> Mise en forme conditionnelle -> Nouvelle règle
3. Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez « Mettre en forme uniquement les cellules qui contiennent »
4. Dans la description de la règle, sélectionnez Erreurs dans la liste déroulante
5. Définissez le format et cliquez sur OK. Cela met en évidence toute valeur d'erreur dans l'ensemble de données sélectionné
   

Utilisation de l'option Aller à spécial

1. Sélectionnez l'ensemble des données
2. Appuyez sur F5 (cela ouvre la boîte de dialogue Aller à)
3. Cliquez sur le bouton spécial en bas à gauche
4. Sélectionnez Formules et décochez toutes les options à l'exception des erreurs
   

Cela sélectionne toutes les cellules qui contiennent une erreur. Maintenant, vous pouvez les mettre en surbrillance manuellement, les supprimer ou y taper n'importe quoi.

#6 Changer le texte en minuscule/majuscule/correcte

Lorsque vous héritez d'un classeur ou importez des données à partir de fichiers texte, les noms ou les titres ne sont souvent pas cohérents. Parfois, tout le texte peut être en minuscules/majuscules ou il peut s'agir d'un mélange des deux. Vous pouvez facilement tout rendre cohérent en utilisant ces trois fonctions :

LOWER() - Convertit tout le texte en minuscules.
UPPER() - Convertit tout le texte en majuscules.
PROPER() - Convertit tout le texte en casse appropriée.

#7 Analyser les données à l'aide de texte en colonne

Lorsque vous récupérez des données à partir d'une base de données ou que vous les importez à partir d'un fichier texte, il peut arriver que tout le texte soit regroupé dans une seule cellule. Vous pouvez analyser ce texte dans plusieurs cellules à l'aide de la fonctionnalité Texte en colonne dans Excel.

1. Sélectionnez les données/texte que vous souhaitez analyser
2. Aller aux données -> Texte en colonne (cela ouvre l'assistant Texte en colonnes)
   • Étape 1 : Sélectionnez le type de données (sélectionnez Délimité si vos données ne sont pas espacées de manière égale et sont séparées par des caractères tels que virgule, tiret, point…). Cliquez sur Suivant
     
   • Étape 2 : Sélectionnez Délimiteur (le caractère qui sépare vos données). Vous pouvez sélectionner un délimiteur prédéfini ou autre chose à l'aide de l'option Autre
     
   • Étape 3 : Sélectionnez le format de données. Sélectionnez également la cellule de destination. Si la cellule de destination n'est pas sélectionnée, la cellule actuelle est écrasée
     

En rapport: Extrayez le nom d'utilisateur de l'identifiant de l'e-mail en utilisant le texte dans la colonne.

#8 Vérification orthographique

Rien ne diminue la crédibilité de votre travail qu'une faute d'orthographe.

Utilisez le raccourci clavier F7 pour exécuter une vérification orthographique de votre ensemble de données.

Voici un tutoriel détaillé sur l'utilisation de la vérification orthographique dans Excel.

#9 Supprimer tout le formatage

Dans mon travail, j'ai utilisé plusieurs bases de données pour obtenir les données dans Excel. Chaque base de données avait son propre format de données. Lorsque vous avez toutes les données en place, voici comment vous pouvez supprimer tout le formatage en une seule fois :

1. Sélectionnez l'ensemble de données
2. Allez dans Accueil -> Effacer -> Effacer les formats
   

De même, vous pouvez également effacer uniquement les commentaires, les hyperliens ou le contenu.

#10 Utilisez Rechercher et remplacer pour nettoyer les données dans Excel

Rechercher et remplacer est indispensable lorsqu'il s'agit de nettoyer les données. Par exemple, vous pouvez sélectionner et supprimer tous les zéros, modifier les références dans les formules, rechercher et modifier la mise en forme, etc.

En savoir plus sur la façon dont Rechercher et remplacer peut être utilisé pour nettoyer les données.

Ce sont mes 10 meilleures techniques pour nettoyer les données dans Excel. Si vous souhaitez en savoir plus sur les techniques, voici un guide de l'équipe MS Excel - Nettoyer les données dans Excel.

S'il y a d'autres techniques que vous utilisez, partagez-les avec nous dans la section commentaires !