Excel pour journalistes (12) : la fonction Estvide

Trop peu connue, la fonction ESTVIDE est une baguette magique entre les mains du journaliste qui se démène pour nettoyer et ordonner un jeu de données. Exemple d’utilisation avec la liste des mandats belges.

Le 11 août, la Cour des Comptes et le Moniteur belge ont publié la liste des mandats publics, version 2016. Un document pdf  de plus de 5 Mo frôlant les 1000 pages, exploité avec succès et talent depuis des années par le site Cumuleo.

Pour le journaliste, le nettoyage de ce pdf tient du cauchemar, le recours à un logiciel d’OCR puis à OpenRefine est inévitable. Voir à ce sujet l’excellente démonstration d‘Ettore Rizza.

Une partie de ce nettoyage peut néanmoins être faite avec Excel. Et constitue l’occasion d’introduire la fonction ESTVIDE, méconnue mais tellement utile.

Pour convertir ce pdf en xls, j’ai utilisé Abbyy FineReader : un investissement qui n’est pas négligeable (200 €) mais qui est est vite rentabilisé, deux heures à peine suffisant pour transformer les 1000 pages du pdf en un xls plus ou moins exploitable. Un extrait du fichier converti est disponible ici.

On le voit, le résultat de la conversion est loin d’être optimum : impossible, par exemple, de trier les données, d’établir des classements entre cumulards et moins cumulards. Je le répète : OpenRefine est l’outil le mieux adapté à un bon nettoyage des données mais ma série de posts s’intitulant “Excel pour journalistes”, je vais me limiter à l’apport d’un tableur dans le décrassage de ce jeu de données.

Le premier écueil, bien visible sur la capture d’écran ci-dessous, est que certaines cellules sont fusionnées : le nom des mandataires, par exemple, s’étale sur 6 colonnes fusionnées.

Pour y remédier, il suffit de sélectionner tout le tableau (Ctrl+A) et Accueil/Fusionner et Centrer/Annuler Fusionner Cellules. Le nom des mandataires sera dès lors contenu dans une seule et unique cellule de la première colonne. Comme on le voit sur l’illustration ci-dessous  (le nom de la mandataire n’occupe désormais que la cellule A1) :

Un deuxième problème, tout aussi simple à résoudre, est que la mention “(suite/vervolg)” est accolée à certains noms et prénoms, vestige des sauts de pages que contenait le pdf. Pour les supprimer, accéder au menu de remplacement de caractères par Ctrl+H et remplacer la mention (suite/vervolg) par… rien du tout. Et appuyer sur “Remplacer tout”.

Troisième souci – et on en vient au coeur du sujet -, que l’on rencontre fréquemment dans les  jeux de données, est que les noms de mandataires ne sont pas répétés sur chaque ligne à côté de chacun de leurs mandats. Ce qui rend impossible tout travail sur les données.

Les fonction SI et ESTVIDE

C’est ici qu’on va faire intervenir  la fonction ESTVIDE qui, accolée à la fonction SI, permet d’imposer certaines opérations à Excel lorsqu’une cellule déterminée est vide.
On va d’abord créer, en B, une colonne provisoire et en B1, on va dupliquer le nom de la première mandataire, écrit donc en A1.

Puis en B2, on inscrit la formule suivante : =SI(ESTVIDE(A2);B1;A2)

Ce qui signifie : Si la cellule A2 est vide, alors rapporte le contenu de B1 mais si cette même cellule A2 contient du texte, alors rapporte le contenu de A2. Puis on étend la formule jusqu’en bas de la colonne. Le résultat est à la hauteur de nos grandes ambitions :

Après avoir copié le contenu de cette même colonne B, on le colle au même endroit grâce à un collage spécial “Valeurs seules” (bouton droit de la souris), ce qui a pour effet de faire disparaître les formules. La colonne A, devenue inutile, peut être supprimée.

Mais comment inverser la logique? A savoir, imposer comme condition qu’une cellule ne soit pas vide plutôt que vide? Une telle fonction, quelque chose comme ESTNONVIDE, n’existant pas dans Excel, il faut se plier par un subterfuge : SI(A2>” “), qui se traduit par si A2 est plus grand que rien du tout. Dans l’exercice précédent, inverser la logique s’écrira donc, dans la cellule B2,  =SI(A2>” “;A2;B1)
Autrement dit : Si A2 comprend autre chose que du vide, alors rapporte en B2 le contenu de cette cellule A2 et sinon, rapporte le contenu de B1.

Mettre les cellules vides en surbrillance avec ESTVIDE

La même formule peut servir à mettre en évidence les cellules vides d’un tableau ou d’une colonne. Par exemple, sélectionnez – en cliquant sur son en-tête – la colonne C puis dans les onglets d’Excel : Accueil/Mise en forme conditionnelle/Nouvelle règle. Dans la boîte à options qui apparaît, choisissez “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué”. Et dans la barre de formule, indiquez la formule avec pour référence la première cellule utile (dans notre cas, nous voulons voir quelles sont les cellules vides de la colonne B et la première cellule sera donc B1) : =ESTVIDE(B1). Choisissez la mise en forme souhaitée, dans notre cas une surbrillance orange :