Formules, références et erreurs silencieuses

Formules, références et erreurs silencieuses

1. Références absolues et relatives : le mécanisme qui trahit

   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.

2. Recherche, correspondance et nettoyage des données

   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.

3. Tableaux, requêtes et listes de référence

   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.