Le nettoyage des données Excel est une compétence importante que tous les analystes commerciaux et de données doivent posséder. À l’ère actuelle de l’analyse des données, tout le monde s’attend à ce que l’exactitude et la qualité des données répondent aux normes les plus élevées. Une grande partie du nettoyage des données Excel implique l’élimination des espaces vides, des informations incorrectes et obsolètes. 

Importer des données à partir de fichiers .txt

Vous devez souvent importer des données dans la feuille de calcul Excel à partir d’un fichier texte. Microsoft Excel propose un Assistant Importation de texte pour importer des données à partir des différents formats de fichier texte :

■ Fichiers séparés par des virgules (.csv) ou fichiers séparés par des tabulations (.txt).

■ Fichiers avec des colonnes de largeur fixe sans délimiteurs entre les colonnes de données où les données commençant à des positions fixes de la ligne.

Pour importer des fichiers texte, exécutez les étapes suivantes.

1. Cliquez sur la cellule dans laquelle vous souhaitez placer les données du fichier texte ou CSV, sélectionnons la cellule B4
2. Cliquez sur l’onglet Données, dans le groupe récupérer et transformer des données, cliquez sur à partir d’un fichier texte/CSV. Vous pouvez également cliquer sur le menu déroulant obtenir des données et cliquer sur à partir d’un fichier ensuite a partir d’un fichier texte/CSV

3.Dans la boîte de dialogue Importer des données, localisez et double-cliquez sur le fichier texte à importer, puis cliquez sur Importer. Nous allons importer le fichier Boissons.txt

4a. Une boite de dialogue s’ouvre. Vous pouvez modifier l’origine du fichier ,le délimiteur, et la détection du type de données.

4b. vous pouvez également transformer les données.

Cliquez sur charger dans ….

5.Dans la boîte de dialogue Importer des données, vous pouvez :

1. a) sectionnez la méthode d’affichage. Soit sous forme de :

■ Tableau

■ Rapport de tableau croise dynamique

■ Graphique croise dynamique

■ Ou ne créer que la connexion.

1. b) Où voulez-vous placer les données ? soit :

■ Pour renvoyer les données à l’emplacement que vous avez sélectionné, cliquez sur Feuille de calcul existante.

■ Pour renvoyer les données dans le coin supérieur gauche d’une nouvelle feuille de calcul, cliquez sur Nouvelle feuille de calcul.

1. c) Vous pouvez également cliquer sur Propriétés pour définir les options d’actualisation, de mise en forme et de mise en page pour les données importées.

Cliquez sur OK.

Excel place la plage de données externes à l’emplacement que vous spécifiez.

Résultat:

Supprimer les doublons

L’un des moyens les plus simples de nettoyer les données dans Excel consiste à supprimer les doublons. Il existe une probabilité considérable qu’il duplique involontairement les données à l’insu de l’utilisateur. Dans de tels scénarios, vous pouvez éliminer les valeurs en double.

Ici, vous allez considérer un simple ensemble de données ci-dessus d’une liste des employés qui a des valeurs en double. Vous utiliserez la fonction intégrée d’Excel pour supprimer les doublons, comme indiqué ci-dessous.

L’ensemble de données d’origine a douze lignes en double. Pour éliminer les données en double, vous devez sélectionner l’option de données dans la barre d’outils, et dans le ruban Outils de données, sélectionnez l’option “Supprimer les doublons”. 

Cela vous fournira la nouvelle boîte de dialogue, comme indiqué ci-dessous.

Ici, vous devez sélectionner les colonnes que vous souhaitez comparer pour la duplication. Une autre étape critique consiste à vérifier l’option des en-têtes lorsque vous avez inclus les noms de colonne dans l’ensemble de données. Excel l’analysera automatiquement par défaut.

Ensuite, vous devez comparer toutes les colonnes, alors allez-y et vérifiez toutes les colonnes comme indiqué ci-dessous.

Sélectionnez Ok, et Excel effectue les opérations requises et vous fournit l’ensemble de données après avoir filtré les données en double, comme indiqué ci-dessous.

Dans la prochaine partie du nettoyage des données Excel, vous comprendrez l’analyse des données du texte à la colonne.

Analyse des données du texte à la colonne

Parfois, il est possible qu’une cellule contienne plusieurs éléments de données séparés par un délimiteur de données comme une virgule. Par exemple, supposons qu’il existe une colonne qui stocke les informations d’adresse.

La colonne d’adresse stocke la rue, le district, l’état et la nation. Des virgules séparent tous les éléments de données. Vous devez maintenant diviser la rue, le district, l’état et la nation des colonnes d’adresse en colonnes séparées. 

La fonctionnalité intégrée d’Excel appelée “convertir ” peut y parvenir. Maintenant, essayez un exemple pour le même.

Ici, vous avez le nom complet des employés (noms et prenom)  séparés par un espace comme délimiteur de données. Les données tabulaires sont présentées ci-dessous.

Sélectionnez les données (la plage A2:A12), cliquez sur l’onglet  données  du ruban, puis sélectionnez “convertir”, comme indiqué ci-dessous.

Une nouvelle fenêtre apparaîtra à l’écran, comme indiqué ci-dessous. Sélectionnez l’option de délimiteur et cliquez sur “suivant”. Dans la fenêtre suivante, vous verrez une autre boîte de dialogue.

Dans la nouvelle boîte de dialogue de page, vous verrez une option pour sélectionner le type de délimiteur de vos données. Dans ce cas, vous devez sélectionner “l’espace” comme délimiteur, comme indiqué ci-dessous.

Dans la dernière boîte de dialogue, sélectionnez le format de données de colonne en tant que “Standard”, sélectionnez également la nouvelle destination de vos données , ici c’est la cellule C2 et l’étape suivante devrait être de cliquer sur Terminer, comme indiqué dans l’image suivante.

Les données finales résultantes seront disponibles, comme indiqué ci-dessous.

Supprimer tout le formatage

Un autre bon moyen de nettoyer les données dans Excel consiste à assurer un formatage uniforme ou, dans certains cas, même à supprimer le formatage. Le formatage peut être aussi simple que de colorer vos cellules et d’aligner le texte dans les cellules. Il peut s’agir d’une condition logique appliquée à vos cellules à l’aide de l’option de mise en forme conditionnelle d’Excel à partir de l’onglet d’accueil. 

Cependant, dans les situations où vous souhaitez supprimer le formatage, vous pouvez le faire de la manière suivante. Tout d’abord, essayez d’éliminer le formatage habituel. Dans l’exemple précédent, vous avez pris le cas des tables de données des noms et prénoms  avec des cellules d’en-tête colorées en bleu et le texte centré.

Maintenant, utilisez l’option d’effacement pour supprimer les formats. Sélectionnez les données tabulaires comme indiqué ci-dessus. Sélectionnez l’onglet “accueil” et accédez au groupe “édition” dans le ruban. L’option “effacer” est disponible dans le groupe, comme indiqué ci-dessous.

Sélectionnez l’option “effacer” et cliquez sur l’option “effacer les formats”. Cela effacera tous les formats appliqués sur la table. 

Le tableau de données final apparaîtra comme indiqué ci-dessous.

Maintenant, vous devez apprendre à éliminer la mise en forme conditionnelle pour nettoyer les données dans Excel. Cette fois, considérez une feuille différente. Vous devez utiliser la feuille de calcul des ventes , qui comprend une mise en forme conditionnelle dans Excel.

Pour éliminer la mise en forme conditionnelle dans Excel, sélectionnez la colonne ou le tableau avec la mise en forme conditionnelle comme indiqué ci-dessous. 

Accédez ensuite à l’onglet “Accueil” et sélectionnez la mise en forme conditionnelle.

Ensuite, dans la boîte de dialogue, sélectionnez l’option Effacer les règles. Ici, vous pouvez choisir d’éliminer les règles uniquement dans les cellules sélectionnées ou d’éliminer les règles de toute la colonne.

Après avoir éliminé toutes les conditions, le tableau résultant se présente comme suit.

Vérification orthographique

La fonction de vérification de l’orthographe est également disponible dans MS Excel. Pour vérifier l’orthographe des mots utilisés dans la feuille de calcul, vous pouvez utiliser la méthode suivante. Sélectionnez la cellule, la colonne ou la feuille de données où vous souhaitez effectuer la vérification orthographique.

Maintenant, allez à l’onglet de révision dans le groupe vérification sélectionnez Orthographe comme indiqué ci-dessous.

Microsoft Excel affichera automatiquement l’orthographe correcte dans la boîte de dialogue, comme indiqué ci-dessous. Vous pouvez remplacer les mots selon l’exigence comme indiqué ci-dessous.

Le tableau de données final révisé ressemblera à celui ci-dessous.

Changer la casse – Minuscule/Majuscule/Propre

Vous pouvez manipuler les données dans la feuille de calcul Excel en termes de cas de caractères selon les exigences. Pour appliquer les changements de casse, vous pouvez suivre les étapes suivantes.

Sélectionnez le tableau ou les colonnes dont la casse doit être modifiée, comme indiqué ci-dessous.

Sélectionnez la cellule à côté de la colonne et appliquez la formule selon l’exigence, comme indiqué ci-dessous.

= MAJUSCULE (adresse de cellule) – pour la conversion en majuscules 

= MINUSCULE (adresse de cellule) – pour la conversion en minuscules 

= NOMPROPRE (adresse de cellule) – pour la conversion de cas de phrase 

Maintenant, vous pouvez faire glisser la cellule jusqu’à la dernière ligne, comme indiqué ci-dessous.

Le tableau de données final apparaîtra comme indiqué ci-dessous.

Mettre en surbrillance les erreurs

La mise en évidence des erreurs dans une feuille de calcul Excel est utile pour trouver ou trier facilement les données erronées. Vous pouvez effectuer une mise en surbrillance des erreurs à l’aide de la mise en forme conditionnelle dans Excel. Ici, vous devez considérer l’ensemble de données des employés comme un exemple.

Imaginez que vous interrogez tous les employés. Il y a des critères d’éligibilité. Vous pouvez présélectionner les employés s’ils ont 90 % de réalisation globales. Maintenant, appliquez la mise en forme conditionnelle et triez les employés  éligibles et non éligibles.

Tout d’abord, sélectionnez la colonne pourcentage comme indiqué.

Sélectionnez l’onglet “Accueil”, et dans le groupe Styles, sélectionnez la mise en forme conditionnelle, comme indiqué ci-dessous.

Dans l’option de mise en forme conditionnelle, sélectionnez l’option de surbrillance, et dans la liste déroulante suivante, sélectionnez l’option inferieur a… comme indiqué ci-dessous.

Dans la fenêtre des paramètres, vous trouverez un emplacement pour fournir  le pourcentage inferieur à “90” % et appuyez sur OK.

Excel va maintenant sélectionner et mettre en surbrillance les cellules avec un pourcentage inférieur à 90 %. 

Fonction SUPPRESPACE()

La fonction SUPPRESPACE() est utilisée pour éliminer les espaces excédentaires et les espaces de tabulation dans les cellules de la feuille de calcul Excel. Les espaces vides et les espaces de tabulation excessifs rendent les données difficiles à comprendre. L’utilisation de la fonction ” SUPPRESPACE()” peut éliminer ces espaces blancs excessifs.

Sélectionnez les cellules de données avec des espaces vides et des espaces de tabulation excessifs. Maintenant, sélectionnez une nouvelle cellule adjacente à la première cellule.

Appliquez la fonction SUPPRESPACE() et faites glisser la cellule comme indiqué ci-dessous.

Il montre les données finales après l’élimination de l’espace excédentaire comme suit.

Rechercher et remplacer

Rechercher et remplacer vous aidera à récupérer et à remplacer des données dans l’ensemble de la feuille de calcul pour vous aider à organiser et à nettoyer les données dans Excel. Prenons l’exemple des données sur les employés.

Ici, essayez d’aller chercher un employé avec le nom de Amy et essayez de renommer ou de remplacer son nom par David .

L’option “Rechercher et remplacer” est présente dans l’onglet accueil du  ruban , dans le groupe d’édition, comme indiqué ci-dessous.

Cliquez sur l’option, et une nouvelle fenêtre s’ouvrira, où vous pourrez entrer les données à récupérer et entrer le texte que vous devez remplacer, comme indiqué ci-dessous.

Cliquez sur “remplacer tout”, et il remplacera toutes les occurrences de Amy par David. Le jeu de données final sera comme indiqué ci-dessous.

Correction des formats de date et d’heure

Les dates et les heures doivent être converties et normalisées car il existe de nombreux formats de date distincts et peuvent prêter à confusion s’ils sont signalés de différentes manières. Utilisez ces formules pour normaliser les formats de date :

• =DATE() – Renvoie le numéro de série qui correspond à une date spécifique. Le résultat est formaté en tant que date si le format de cellule était Général avant la saisie de la fonction.
• =DATEVAl() – Convertit une date textuelle en un numéro de série.
• =TEMPS()- Le nombre décimal pour une heure spécifique est renvoyé. Le résultat est formaté en tant que date si le format de cellule était Général avant la saisie de la fonction.
• =TEMPSVAL()- Renvoie la représentation décimale de l’heure sous forme de chaîne de texte. Le nombre décimal représente les heures de 0:00:00 (00:00:00 AM) à 23:59:59 (23:59:59 PM).

Mettre à jour les valeurs manquantes

L’utilisation de l’option “Atteindre” dans Excel est un moyen simple de remplir les valeurs manquantes.

Étape 1 : Pour ouvrir la boîte de dialogue ” Atteindre “, utilisez CTRL+T. Sélectionnez l’option ‘cellule’ (ou)

Étape 2 : Naviguez jusqu’au groupe Édition de l’onglet accueil > Rechercher > Atteindre …, sélectionnez l’option « cellule ».

Étape 3 : Sélectionnez “cellules Vides”, puis cliquez sur OK.

Étape 4 : Sur le clavier, appuyez sur F2 (ou) cliquez sur la barre de formule.

Vous pouvez maintenant remplir les vides avec la valeur que vous désirez. Cette valeur sera affectée à la cellule active (cellules vides). CRTL + Entrée remplira toutes les cellules vides avec la même valeur. 

Mettez en surbrillance 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 de données complet
2. Allez dans l’onglet Accueil -> Mise en forme conditionnelle -> Nouvelle règle
3. Dans la boîte de dialogue Nouvelle règle de formatage, sélectionnez “Formater uniquement les cellules qui contiennent”
4. Dans la description de la règle, sélectionnez Erreurs dans le menu déroulant
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 la fonmction Atteindre

1. Sélectionnez l’ensemble de données complet
2. Appuyez sur CTRL+T (cela ouvre la boîte de dialogue Atteindre)
3. Cliquez sur le bouton cellule en bas à gauche

4. Sélectionnez Formules et décochez toutes les options sauf Erreurs

Cela sélectionne toutes les cellules contenant une erreur. Vous pouvez maintenant les mettre en surbrillance manuellement, les supprimer ou y saisir quoi que ce soit.