Excel et bureautique avancée

La plupart des problèmes de ce cours disparaissent si une règle est comprise dès le début : un fichier de tableur est envoyé par courriel, déposé sur un espace de stockage, et ouvert sur un poste qui n'est pas le vôtre. Ce n'est pas un programme, c'est un support de données.

Ce qu'un fichier peut contenir malgré tout

La règle est contre-intuitive : des données à caractère personnel n'ont pas besoin d'être identifiables par vous pour en être. Une liste de noms, d'adresses, de dates de naissance ou de salaires est une donnée à caractère personnel, que la personne ait ou non donné son accord, et que le fichier la rende ou non directement exploitable.

La conséquence est simple : un fichier envoyé par courriel contenant des noms est un traitement de données à caractère personnel, avec les obligations qui s'ensuivent — base légale, information des personnes, durée de conservation, sécurité. C'est le premier réflexe à transmettre à un stagiaire, et il est très souvent absent des formations.

La demande d'accès, qui arrivera toujours

Toute personne concernée peut demander l'accès à ses données. Quand elle le fait, il faut pouvoir dire où elles sont. Un tableur envoyé par courriel, une copie sur un bureau, un fichier dans un dossier partagé : chaque copie est un endroit où il faudra chercher, et un endroit où l'information a pu être dupliquée à votre insu.

La parade opérationnelle est simple : poser une un seul emplacement de référence par jeu de données, et toute copie temporaire supprimée à la fin du travail. Ce n'est pas une contrainte administrative, c'est ce qui rend possible une réponse à une demande d'accès en quelques minutes au lieu de plusieurs jours.

Une référence est soit relative — elle se décale quand on recopie — soit absolue — elle reste pointée sur la même cellule. Une référence mixte verrouille uniquement une partie, ce qui est indispensable dès qu'on fait référence à une ligne ou à une colonne tout en remplissant.

Le symptôme du coefficient multiplié

Une formule contenant un taux de TVA écrit sans dollar, recopiée sur dix lignes, applique le taux à dix cellules différentes. Le résultat est un tableau faux de bout en bout, sans aucun message d'erreur : le tableur calcule ce qu'on lui demande, et ce qu'on lui demande est faux.

La parade est connue de tous et pourtant omise régulièrement : verrouiller la référence des constantes avec un dollar. Et surtout, tester le résultat sur la dernière ligne de la plage, pas sur la première. Une formule correcte en ligne 2 peut être fausse en ligne 200.

Les erreurs qui s'affichent, et celles qui n'affichent rien

Une cellule affiche #DIV/0! quand on divise par une cellule vide. Le message est utile. Les erreurs les plus coûteuses ne s'affichent pas :

• un texte lu comme un nombre, qui se retrouve additionné et produit un résultat faux sans avertissement ;
• une plage décalée d'une ligne, qui exclut la dernière donnée sans le signaler ;
• une cellule fusionnée, qui décale l'alignement des colonnes et fausse toutes les formules voisines ;
• une colonne importée qui devient du texte parce qu'un guillemet s'y est glissé — le symptôme le plus fréquent sur les exports provenant d'un autre outil.

Règle de contrôle : le total de contrôle attendu doit exister quelque part dans le fichier, sous une forme indépendante du calcul. Sans cette contre-vérification, aucun calcul n'est fiable — on ne peut pas savoir s'il est faux.

La plus grande partie du temps passé sur un tableur n'est pas le calcul : c'est le rapprochement de deux sources qui ne se parlent pas.

Croiser deux sources : la recherche qui échoue

Une recherche verticale trouve la ligne correspondant à une clé dans une autre table. Elle renvoie une erreur si la clé est absente — ce qui est correct, et ce qui produit des #N/A en série sur des fichiers réels. Le réflexe qui consiste à remplacer l'erreur par une valeur vide masque le problème : une clé absente est une anomalie à traiter, pas une cellule à remplir.

Les deux solutions, et le choix qui importe

• une recherche stricte exige une correspondance exacte et signale toute divergence. C'est le bon choix par défaut, parce qu'elle rend les anomalies visibles ;
• une recherche approximative trouve l'élément le plus proche. Pratique sur des fautes de frappe, dangereuse par défaut : deux entrées proches seront confondues, et l'erreur ne se voit pas parce que la recherche renvoie un résultat plausible.

La règle de gestion : une recherche approximative doit être suivie d'un contrôle du taux de correspondance, et ses resultats doivent être relus. Si le taux est élevé, ce n'est pas une recherche approximative qu'il faut, c'est un référentiel à remettre à niveau.

Nettoyer avant de calculer

La séquence qui évite 80 % des erreurs de rapprochement : convertir les types de façon explicite, supprimer les espaces en début et fin, uniformiser la casse, puis seulement comparer. L'ordre compte : normaliser la casse après avoir comparé ne sert à rien.

Et un contrôle de cohérence qui prend une minute : compter les lignes avant et après, vérifier qu'aucun identifiant n'est en double, et comparer deux totaux calculés par des méthodes différentes. Si les deux méthodes ne convergent pas, on ne publie pas.

Dès qu'un jeu de données dépasse quelques centaines de lignes, le tableur plat atteint ses limites. Trois alternatives, à choisir consciemment.

Le format tableau : la base de tout le reste

Convertir une plage en tableau change le fonctionnement du tableur : les formules s'étendent automatiquement, les filtres suivent les nouvelles lignes, et un calcul qui référence une colonne s'écrit avec son nom plutôt qu'avec des lettres. C'est le premier réflexe à prendre sur tout fichier de travail, parce qu'il supprime la majorité des recopies de formules — donc la majorité des erreurs de référence.

Power Query : la préparation qui évite le bricolage

Un outil de préparation de données déclaratif, intégré aux tableurs modernes. Il permet de définir une chaîne de transformations — importer, filtrer, nettoyer, renommer, jointes — qui est réexécutable et documentée par sa définition.

Le bénéfice n'est pas la vitesse, c'est la reproductibilité : la préparation est écrite une fois et rejouée à l'identique sur le mois suivant. Un nettoyage fait à la main sur les cellules est refait à la main le mois suivant, et c'est exactement là que les erreurs entrent.

Choisir entre tableur, base de données et analyse

• tableur : quelques dizaines de milliers de lignes, une seule personne qui travaille dessus, des calculs que quelqu'un doit comprendre ;
• base de données : plusieurs personnes, des écritures concurrentes, des règles à faire appliquer par le système et non par chacun ;
• outil d'analyse : l'historique, les agrégations, et le besoin de mesurer des tendances sur des volumes qu'un tableur ne supporte pas.

Le critère de bascule est simple : le jour où deux personnes doivent modifier le même fichier en même temps, ou où une règle doit être appliquée de façon identique par tout le monde, le tableur devient le mauvais outil. Ce basculement se décide souvent trop tard.

Un graphique n'est pas une décoration : c'est une prise de position sur ce qui est important. Le choix du type de graphique affirme une relation, et cette relation peut être fausse.

Chaque graphique affirme quelque chose

• comparaison de catégories : barres, la longueur se compare à l'œil ;
• évolution dans le temps : courbes, la pente porte le sens ;
• part d'un tout : secteurs, à éviter au-delà de trois parts, ils deviennent illisibles ;
• relation entre deux variables : nuage de points, la tendance compte plus que les points isolés.

Choisir un secteur pour montrer une évolution dans le temps est une erreur de fond, pas de rendu : le lecteur cherche une tendance et ne peut pas en voir une.

Les mensonges visuels

Un graphique à axe vertical commençant à 50 alors que la valeur vaut 55 fait paraître une progression spectaculaire. Ce n'est pas une nuance graphique : c'est une affirmation fausse présentée comme vraie, et elle engage celui qui présente.

La règle professionnelle est stricte et simple : un axe de valeur commence à zéro, sauf si le graphique affiche explicitement une rupture d'échelle, et sauf si le sujet s'y prête. Une échelle tronquée est admise dans un graphique de tendance de fond — là où l'on regarde une variation, pas un niveau. Elle ne l'est pas pour comparer des niveaux.

Trop de graphiques, pas assez de graphique

Une planche de six graphiques montre rarement mieux qu'un seul bien choisi. Le réflexe professionnel : un message par graphique, et un titre qui énonce le message plutôt que de décrire les données. Un titre qui dit « le chiffre d'affaires baisse depuis janvier » est infiniment plus utile qu'un titre qui dit « chiffre d'affaires par mois ».

Un tableau de bord qui répond à la mauvaise question coûte plus cher qu'aucun : il fait perdre du temps sans faire décider.

Par où commencer, et quoi éviter

Le point de départ n'est jamais « quelles données avons-nous » mais « quelle décision doit être prise, par qui, et dans quel délai ? » Un indicateur qui ne déclenche aucune décision est un coût, pas un service.

Deux erreurs de construction :

• trop d'indicateurs : au-delà de quelques -uns par écran, personne ne les regarde, et l'attention se dilue. Le tableau de bord devient un paysage et perd sa fonction d'alerte ;
• aucun seuil : afficher une valeur sans dire à partir de quand elle devient préoccupante. Un indicateur sans seuil n'appelle aucune action : on le regarde, on suppose que ça va, et on passe à autre chose.

La donnée et son contexte

Un chiffre seul est ininterprétable : il faut la période, la source, et la définition du calcul. « 1 250 » ne veut rien dire sans « clients actifs sur 30 jours, extrait le 12, calculé ainsi ». Un tableau de bord sans la définition de ses indicateurs est un tableau de bord dont deux lecteurs tireront deux conclusions. Cette définition s'écrit une fois, à côté du tableau, et elle vit avec lui.

Le projet

Construire un tableau de bord de pilotage à partir d'un jeu de données réel ou public, avec la chaîne complète : nettoyage, requêtes de rapprochement, indicateurs, restitution. Le livrable inclut le fichier, une note de méthode d'une page, et la liste des sources.

Les huit critères d'évaluation

1. la source des données est identifiée et datée : d'où viennent les chiffres, et à quelle date. Un chiffre sans source n'est pas publiable ;
2. les types sont normalisés et les doublons détectés avant tout calcul. Un identifiant en double dans une table de référence fausse silencieusement toutes les sommes ;
3. les constantes sont verrouillées et chaque formule est testée sur la dernière ligne de la plage, pas seulement sur la première ;
4. un contrôle de cohérence existe : deux totaux calculés par des voies indépendantes concordent, et un nombre d'anomalies est publié ;
5. les indicateurs répondent à une décision identifiée, avec un seuil et une conséquence écrites ;
6. les graphiques portent un message : le titre énonce la conclusion, et l'échelle n'est pas tronquée sans rupture visible ;
7. la préparation est rejouable : le fichier peut être reconstruit depuis la source sans refaire le travail à la main ;
8. aucune donnée à caractère personnel inutile ne figure dans le livrable, et les identifiants directs ont été remplacés par des identifiants internes. C'est le seul critère qui s'applique aussi bien à un exercice qu'à un vrai travail.

Le critère qui départage

Le jury retient ceci : quelqu'un qui n'a pas participé à la construction peut-il reprendre le fichier, comprendre ce qu'il mesure, et le mettre à jour le mois prochain ? Si la réponse est non, quelqu'un d'autre devra refaire le travail — et c'est exactement ce qui arrive dans la plupart des organisations.