3.3 Formules les plus courantes pour le nettoyage de données

TABLE DES MATIÈRES

• 3.3.1 Supprimer les espaces inutiles
• 3.3.2 Pivoter les données
• 3.3.3 Fusionner / fractionner les colonnes
• 3.3.4 Rechercher et remplacer du texte
• 3.3.5 Joindre des données
• 3.3.6 Copier le résultat des formules

---

Il peut arriver que l’on ait à traiter des bases de données contenant des données mal saisies ou importées de pages Web, provenant d’un autre système informatique ou encore d’applications utilisant un codage des caractères particulier et non standardisé. Ainsi, il arrive que des espaces surnuméraires, des caractères de contrôle invisibles viennent polluer les données que l’on souhaite exploiter rendant leur exploitation dans Excel complètement impossible. Le nettoyage des données est alors indispensable.

3.3.1 Supprimer les espaces inutiles

La formule SUPPRESPACE() / TRIM() permet de supprimer tous les espaces du texte à l’exception des espaces simples entre les mots. On utilise cette fonction pour nettoyer du texte dont l’espacement est irrégulier.

En B2, on a du texte qui a été saisi avec des espaces en trop au début. La formule SUPPRESPACE() permet de faire le ménage en éliminant tous ces espaces en trop comme on peut l’observer dans le résultat affiché en cellule C2.

3.3.2 Pivoter les données

Il arrive parfois que l’on souhaite transposer les lignes et les colonnes, c’est-à-dire que les lignes deviennent des colonnes et les colonnes deviennent des lignes.

Vous pouvez effectuer ceci en utilisant un copier-coller en commençant par sélectionner la plage de données que vous souhaitez pivoter puis en vous positionnant sur la nouvelle cellule où vous souhaitez que votre plage de données transposée débute (assurez-vous qu’il y ait suffisamment d’espace pour coller vos données et ne pas écraser des données existantes) faites un clic droit puis sélectionnez Transposer.

3.3.3 Fusionner / fractionner les colonnes

Une tâche courante du nettoyage de données consiste à fusionner plusieurs colonnes en une ou à fractionner une colonne en deux ou plusieurs colonnes. Par exemple, vous pouvez avoir besoin de créer une seule colonne Nom complet en combinant les colonnes Prénom et Nom.

L’exemple 1 combine les noms et prénoms ci-dessus, séparés par un espace. Il est également possible d’ajouter le caractère que vous souhaitez comme séparateur, comme un tiret par exemple (exemple 2).

Vous pouvez également avoir besoin de fractionner une colonne Nom complet en deux colonnes avec les prénoms et les noms séparément. Il existe plusieurs techniques sur Excel pour aboutir à ce résultat :

Première option : fractionner du texte en plusieurs colonnes à l’aide de l’Assistant Conversion

Sélectionnez les données à convertir puis dans Données, cliquez sur Convertir. Dans l’Assistant Conversion, sélectionnez Délimité > Suivant et sélectionnez les Séparateurs de vos données. Par exemple, Virgule et Espace. Un aperçu de vos données apparaît dans la fenêtre Aperçu des données. Si l’aperçu vous convient, sélectionnez Suivant puis la destination des fractions de données sur votre feuille de calcul et enfin terminer l’opération.

Pour le prénom, la formule GAUCHE() renvoie la chaine de caractères située avant l’espace, ce dernier étant identifié grâce à la formule CHERCHE(). Pour le nom, la formule extrait les caractères correspondants dans la cellule A2, en commençant par la droite avec la formule DROITE() et en s’arrêtant à l’espace.

Vous pouvez utiliser les formules de texte GAUCHE(), DROITE(), CHERCHE() et NBCAR() pour manipuler des chaînes de texte dans vos données. Par exemple, vous pouvez distribuer le prénom, le deuxième prénom et le nom d’une seule cellule dans trois colonnes distinctes.

Comment saisir les questions à choix multiples dans une base de données ?

Dans le cas des questions à choix multiples (c’est-à-dire lorsque plusieurs réponses sont possibles), il faut attribuer une colonne pour chacune des réponses afin de faciliter l’analyse et les statistiques descriptives, telles que les calculs de fréquences.

L’exemple ci-dessous est tiré d’une enquête WASH au Myanmar : l’une des variables collecte les différentes méthodes de traitement de l’eau des ménages, avec plusieurs réponses possibles.

La variable est stockée dans la base de données sous forme de variable dichotomique, “1” si elle est sélectionnée ou “0” si elle ne l’est pas, pour laquelle chaque réponse possible dispose de sa propre colonne. Cet enregistrement permet l’utilisation de fonctions mathématiques pour l’analyse statistique. Ces données auraient également pu être enregistrées avec un “Oui” pour “1” et un “Non” pour “0”.

Certains outils de collecte de données stockent les données liées aux questions à choix multiples dans une même cellule. Dans ce cas, les données doivent être réparties dans des cellules distinctes sur la même ligne. Les données ci-dessous illustrent ce cas de figure : la variable waterTreatmentMethod réunie l’ensemble des réponses dans une même cellule.

Utiliser la fonction Convertir pour scinder la cellule :

Dans l’onglet Données, rubrique Outils de données, la fonction permet de répartir dans plusieurs cellules distinctes le contenu d’une même cellule. Il suffit d’indiquer le délimiteur, c’est-à-dire le caractère utilisé pour séparer chaque réponse (dans l’exemple ci-dessus un espace) et le lieu de destination des résultats.

3.3.4 Rechercher et remplacer du texte

Il est possible de remplacer ou de supprimer une chaîne de texte qui est obsolète ou inutile . Pour cela, il suffit de rechercher des instances de ce texte et de les remplacer par aucun texte ou par un autre texte. Dans l’onglet Accueil, dans le groupe Édition, cliquez sur Rechercher et sélectionner, puis sur Remplacer et entrez le texte à rechercher et remplacer.

Dans l’exemple ci-dessus, chaque occurrence du critère recherché “ë” est remplacée par “e” lorsque l’on clique sur Remplacer tout, tandis que Remplacer met à jour une occurrence à la fois.

Il est également possible de remplacer un caractère spécifique (dans l’exemple ci-dessus, un tiret) par un espace. Dans ce cas il faut spécifier l’espace dans le champ Remplacer par.

3.3.5 Joindre des données

Il arrive que vous ayez besoin de récupérer des informations contenues dans d’autres fichiers Excel et de les intégrer à votre base de données au sein d’une seule et même feuille. La formule RECHERCHEV() permet de faire une “jointure” dans Excel. À partir de votre base de données d’origine, une cellule appelle la formule RECHERCHEV() qui va rechercher la présence d’un élément de la base de données originale dans un autre tableur. Il n’est absolument pas nécessaire que les bases de données aient la même taille (ni en nombre de lignes ni en nombre de colonnes).

Dans l’exemple ci-dessous, on a été cherché la variable “Nombre de centres de santé actifs” dans la base de données 1 afin de l’ajouter dans la base de données 2. On a donc inséré dans une colonne vide et renseigné en C2 la formule suivante :

=RECHERCHEV(B2,'Base de données1'!$A$1:$B$37,2,FAUX)

Avec comme arguments :

• B2 : l’élément recherché (une cellule en général).
• 'Base de données1'!$A$1:$B$37 : la plage de recherche (la table périphérique) qui est fixée avec le signe $. Il est important que l’élément recherché se trouve dans la première colonne de la plage de recherche.
• 2 : l’index de retour. Il s’agit de la colonne dans cette plage de cellules qui contient la valeur que vous recherchez, 1 étant le numéro de la première colonne que vous avez selectionné dans la plage de recherche.
• FAUX : le booléen (VRAI ou FAUX) permet de savoir si la recherche peut être approchée ou exacte. La plupart du temps on fait une recherche exacte et le booléen prend la valeur FAUX.

Il existe certaines limitations dans le cas de la fonction =RECHERCHEV, cette dernière pouvant uniquement rechercher une valeur de gauche à droite. Cela signifie que la colonne contenant la valeur que vous recherchez se trouve toujours à gauche de la colonne contenant la valeur de retour. Si votre feuille de calcul n’est pas conçue de cette façon, n’utilisez pas =RECHERCHEV et préférez plutôt la combinaison des fonctions =INDEX et =EQUIV.

L’exemple ci-dessous montre que la valeur sur laquelle rechercher la province correspondante ne se trouve pas dans la colonne la plus à gauche. À la place, nous utiliserons donc la fonction =EQUIV pour trouver la province dans la plage B1:B37.

3.3.6 Copier le résultat des formules

Lorsque vous copiez et collez dans Excel, tous les paramètres de la cellule (données, mise en forme, formules, etc.) sont collés dans la cellule de destination. Lorsque vous copier-coller des cellules avec des formules :

1. Si vous souhaitez conservez ces formules, vous devrez peut–être corriger les références de cellules.
2. Si le processus de nettoyage de la base de donnée est terminé, vous pouvez coller le résultat des formules au lieu des formules en elles-mêmes afin de limiter le risque de recalcul automatique en cas de modifications ultérieures de la base de données.