Définir ce que le contrôle doit réellement prouver
Un reporting peut afficher un total cohérent et contenir plusieurs erreurs qui se compensent. Une ligne de 500 oubliée et une autre ajoutée deux fois pour 500 donnent le même montant final. Vérifier seulement la dernière cellule ne suffit donc pas. L’objectif de cet atelier est de construire une série de preuves complémentaires : toutes les lignes attendues sont présentes, leurs clés sont valides, les montants correspondent à la source et les formules traduisent les règles prévues.
Le cas fictif concerne un suivi d’interventions informatiques. Un export source contient 240 lignes de prestations pour un montant total de 182 450 unités monétaires. Un classeur de reporting reprend ces données, les enrichit avec une agence et calcule plusieurs indicateurs mensuels. Nous allons contrôler chaque passage du flux. Les seuils cités sont des choix pédagogiques à adapter ; ils ne constituent ni une norme d’audit professionnel ni une validation comptable de vos propres fichiers.
Conserver une source figée et identifier le grain
Copiez l’export reçu dans une zone de dépôt en lecture seule pour le travail courant, avec sa date d’extraction et son nom d’origine. Importez une copie dans Excel, sans corriger discrètement le fichier source. Définissez ensuite le grain : une ligne représente ici une prestation réalisée, identifiée par InterventionID et LigneID. Une intervention peut comporter plusieurs prestations ; InterventionID seul n’est donc pas une clé unique. Cette précision détermine tous les contrôles de doublons qui suivent.
Créez une feuille Contrôles séparée et notez le nombre de lignes, le montant total, la première et la dernière date observées. Ces indicateurs constituent une photographie de référence. Ajoutez le nom de la requête ou de la table utilisée pour chaque calcul. Si vous relancez l’import, vous devez pouvoir expliquer pourquoi le nombre de lignes a changé. Une source mouvante et un reporting ancien ne peuvent pas être rapprochés correctement, même si leurs libellés affichent tous deux le même mois.
Contrôler la structure et les valeurs avant les calculs
Vérifiez les colonnes obligatoires : clés, date de réalisation, agence source, quantité, prix et montant. Comptez les valeurs absentes par colonne plutôt que de supprimer immédiatement les lignes incomplètes. Une agence inconnue peut rester analysable dans une rubrique À compléter ; une clé manquante compromet le rapprochement ligne à ligne. Documentez ce traitement. Le contrôle qualité doit rendre le problème visible et attribuable, pas produire un tableau apparemment propre en faisant disparaître les cas difficiles.
Contrôlez aussi les types : une date saisie comme texte, une quantité décimale inattendue ou un montant contenant un espace invisible peuvent modifier les résultats. Travaillez sur des exemples localisés : une date 03/04 peut signifier deux choses selon la convention retenue. Fixez le format attendu à l’import et vérifiez quelques valeurs ambiguës. Ne remplacez jamais toutes les erreurs par zéro par commodité : zéro est une valeur métier, alors qu’une erreur signale une transformation non résolue.
Détecter les doublons avec une clé métier explicite
Ajoutez une clé de contrôle combinant InterventionID et LigneID avec un séparateur documenté, ou contrôlez directement les deux colonnes avec NB.SI.ENS. Pour chaque ligne, comptez le nombre d’occurrences du couple dans la table. Une valeur supérieure à un déclenche une revue. Attention aux blancs et aux espaces : nettoyer une clé peut faire apparaître deux lignes comme identiques. Conservez alors la valeur originale pour comprendre si la différence était une erreur de saisie ou une information significative.
Dans notre export de 240 lignes, supposons que deux lignes partagent la même clé, mais correspondent à une correction légitime dont le statut est précisé dans une troisième colonne. Supprimer les doublons automatiquement ferait perdre une information. Définissez d’abord la règle : garder la dernière version validée, conserver une écriture d’annulation ou demander une correction à la source. La sortie du contrôle doit distinguer doublon technique confirmé, révision attendue et anomalie encore ouverte, avec un responsable de décision.
Rapprocher les totaux dans les deux sens
Le reporting affiche 182 100 alors que la source affiche 182 450 : l’écart est de moins 350. Cherchez les lignes présentes dans la source mais absentes du reporting, puis celles présentes dans le reporting mais absentes de la source. Les deux recherches sont nécessaires. Un simple contrôle des lignes non retrouvées dans un sens laisse passer les ajouts parasites. Utilisez un rapprochement exact sur les clés, et non une recherche approximative sur les noms d’agences ou de clients.
Imaginons qu’une prestation de 600 manque et qu’une prestation de 250 ait été ajoutée à tort. La variation nette vaut bien moins 350, mais les montants bruts concernés atteignent 850. Cette distinction évite de sous-estimer le problème. Présentez dans le journal les absences, les ajouts, les différences de montant sur clés communes et le solde expliqué. Le rapprochement est terminé lorsque la somme de ces catégories reproduit l’écart global, pas lorsque celui-ci semble suffisamment petit.
Ajouter des contrôles par sous-ensemble indépendant
Un total général ne permet pas de détecter toutes les erreurs d’affectation. Répartissez les 182 450 par agence et par mois, puis rapprochez ces sous-totaux avec une extraction indépendante. Si 2 000 ont été déplacés de l’agence Nord vers Sud, le total général reste identique, mais les deux contrôles locaux montrent des écarts opposés. Comparez aussi le nombre d’interventions et la somme des quantités. Chaque indicateur observe une propriété différente et réduit les angles morts du précédent.
Évitez de fabriquer le contrôle à partir de la même cellule que le résultat. Comparer une synthèse à un graphique qui utilise exactement cette synthèse prouve seulement que le graphique lit sa source. Préférez une formule d’agrégation sur la table d’origine ou un tableau croisé construit séparément, en vérifiant son actualisation. Lorsque les deux résultats divergent, commencez par aligner périodes, filtres, statuts et grain. Des conventions différentes peuvent produire un écart sans qu’aucune addition soit techniquement incorrecte.
Examiner les formules à partir des sorties sensibles
Commencez l’audit des formules par les indicateurs diffusés, puis remontez leurs références. Les outils de repérage des antécédents et des dépendants d’Excel de bureau permettent de visualiser certains liens entre cellules. Leur présence et leurs possibilités varient selon l’environnement ; des classeurs externes fermés et certains objets limitent ce parcours. L’outil facilite la lecture, mais une flèche bleue ne démontre pas que la règle métier ou le périmètre choisi sont corrects.
Contrôlez les références absolues et relatives, les bornes de plage et les cellules remplacées par des constantes. Une formule recopiée jusqu’à la ligne 239 peut oublier la ligne 240 sans générer d’erreur. Affichez les formules d’une zone et recherchez les ruptures de motif. Vérifiez ensuite les règles spéciales : avoirs négatifs, quantités nulles, dates hors période et catégories inconnues. Une référence techniquement cohérente peut encore appliquer le bon calcul à une population incorrecte.
Ajoutez un contrôle de dernière ligne après chaque import : une prestation de test identifiable, placée en fin de table, doit modifier le total du montant attendu. Retirez ensuite cette fixture de la copie de test. Ce contrôle ciblé révèle une plage fixe oubliée, même lorsque toutes les formules visibles dans les premières lignes semblent cohérentes.
Tester les cas limites sans masquer les anomalies
Créez un petit jeu de test distinct du fichier de production : une prestation normale de 100, un avoir de moins 20, une ligne à zéro et une ligne sans agence dont le montant vaut également zéro. Le total attendu vaut 80. Vérifiez que le reporting conserve l’avoir, ne compte pas le zéro comme donnée absente et classe l’agence manquante selon la règle choisie. Ajoutez aussi une prestation à la première et à la dernière date de période pour tester les bornes inclusives.
Sur les montants, définissez la règle d’arrondi et l’unité de comparaison. Deux résultats peuvent différer de quelques centimes selon que l’on arrondit chaque ligne ou seulement le total. Ne fixez pas une tolérance de dix pour faire disparaître des écarts inexpliqués. La tolérance doit correspondre à une convention documentée et laisser visibles le montant brut et le montant arrondi. Pour les identifiants et les nombres de lignes, une tolérance monétaire n’a évidemment aucun sens.
Exercice corrigé : expliquer un écart qui se compense
Exercice : une source contient A pour 100, B pour 200, C pour 300 et D pour 400, soit 1 000. Le reporting contient A pour 100, B pour 250, D pour 400 et E pour 250, soit également 1 000. Le contrôle du total passe. Identifiez les écarts ligne à ligne et proposez les catégories du journal. N’effacez aucune ligne avant d’avoir expliqué pourquoi elle est différente : le but est de démontrer la composition du résultat.
Corrigé : C manque pour moins 300 ; E est ajouté pour plus 250 ; B présente une différence de plus 50. La somme des écarts est nulle, ce qui explique l’égalité trompeuse des totaux. Trois anomalies restent pourtant à résoudre. Le reporting ne compte toujours que quatre lignes, comme la source : le contrôle de volume passe lui aussi. Seul le rapprochement par clé et montant révèle ce cas, ce qui justifie la combinaison de plusieurs contrôles indépendants.
Constituer une preuve de validation réutilisable
Le journal de contrôle contient une référence, une règle, un résultat attendu, un résultat observé, un statut, un commentaire et un responsable. Distinguez les contrôles bloquants des avertissements. Une clé manquante ou un écart inexpliqué sur un indicateur diffusé peut bloquer la publication ; une agence à compléter peut faire l’objet d’une réserve visible si la règle l’autorise. La décision de publier appartient à une personne identifiée, pas à une couleur verte ajoutée automatiquement.
Conservez le fichier source, la version du classeur, le journal et les réserves approuvées pour chaque livraison. À la prochaine période, rejouez les contrôles avant de reprendre les graphiques. Le meilleur résultat de cet atelier n’est pas un classeur sans message d’erreur : c’est une chaîne que quelqu’un d’autre peut refaire, depuis les lignes importées jusqu’aux chiffres commentés. Pour progresser, ajoutez une modification de structure volontaire et vérifiez que vos contrôles la détectent avant toute diffusion.