Un fichier Excel peut sembler parfaitement exploitable… jusqu’au moment où une date arrive sous forme de texte, où un identifiant disparaît ou où une colonne change de nom sans prévenir. Résultat : un tableau croisé dynamique ne se met plus à jour, une formule renvoie une erreur et chacun cherche le coupable. Souvent, le problème n’est pas Excel : c’est l’absence de règles partagées.

Un contrat de données permet justement de définir ces règles. Il précise quelles informations un fichier doit contenir, sous quelle forme, avec quelles contraintes et à qui revient la responsabilité de les vérifier. Pas besoin d’un service informatique ni d’un document juridique de vingt pages : Excel suffit pour créer un contrôle simple, clair et utile.

Un contrat de données, c’est quoi au juste ?

Un contrat de données est un accord entre la personne qui fournit des données et celle qui les utilise. Il décrit la structure attendue et les conditions que les données doivent respecter. On peut le voir comme le mode d’emploi d’un tableau : il indique ce que chaque colonne signifie et ce qui est autorisé ou non.

Imaginons que votre équipe commerciale transmette chaque semaine un fichier de ventes. Un contrat peut préciser que le fichier comporte les colonnes « Date », « Identifiant client », « Région » et « Montant », que la date doit être une vraie date Excel, que le montant doit être positif et que la région doit appartenir à une liste définie. Si le fichier reçu ne respecte pas ces règles, vous le savez avant de lancer vos analyses.

Le contrat ne garantit pas que chaque information est vraie dans le monde réel. Il ne peut pas deviner qu’une vente a été saisie au mauvais montant. En revanche, il peut repérer des incohérences visibles : une cellule vide, un montant négatif ou une région inconnue. C’est déjà un excellent filet de sécurité.

Pourquoi Excel a besoin de règles explicites

Excel est souple, et c’est l’une de ses grandes forces. Une cellule peut contenir un nombre, du texte, une date ou… une combinaison surprenante des trois. Cette liberté devient risquée lorsque plusieurs personnes alimentent le même fichier ou lorsque des tableaux sont réunis régulièrement.

Par exemple, une personne saisit « Île-de-France », une autre « Ile de France » et une troisième « IDF ». Pour un humain, il s’agit peut-être de la même région. Pour Excel, ce sont trois valeurs différentes. Une synthèse par région risque alors de répartir les ventes en plusieurs groupes.

Un contrat de données apporte des réponses pratiques à des questions simples :

  • Quelles colonnes doivent être présentes ?
  • Quel type de valeur est attendu dans chaque colonne ?
  • Quelles cellules peuvent rester vides ?
  • Quelles valeurs sont permises ou interdites ?
  • Qui fournit le fichier, qui le contrôle et que faire en cas d’anomalie ?

Ces règles évitent aussi les échanges de courriels du type « Tu peux me renvoyer le fichier ? La colonne des dates a encore changé ». Une petite définition partagée aujourd’hui peut épargner beaucoup de réparations demain.

Définir le contrat avant de construire les contrôles

Avant d’écrire une formule, commencez par décrire les données attendues. Le plus simple est de créer une feuille nommée « Contrat » ou « Référentiel » dans le classeur. Elle servira de documentation, mais aussi de base à certaines validations.

Pour chaque colonne, indiquez son nom, sa signification, son format, son caractère obligatoire et ses règles particulières. Voici un exemple pour un fichier de ventes :

  • Date : date de la vente, obligatoire, comprise entre le 1er janvier et le 31 décembre de l’année concernée.
  • Identifiant client : code unique du client, obligatoire, conservé sous forme de texte pour ne pas perdre les zéros au début.
  • Région : région commerciale, obligatoire, choisie dans une liste autorisée.
  • Montant : valeur numérique en euros, obligatoire, supérieure ou égale à zéro.

Ajoutez aussi quelques règles générales : le nom du fichier, la fréquence d’envoi, la personne responsable, la date de mise à jour et la procédure à suivre en cas de problème. Le contrat gagne à rester compréhensible par les personnes qui saisissent les données. Si une règle est trop compliquée à expliquer, elle sera probablement difficile à appliquer.

Un détail souvent oublié : précisez les conventions de format. « Date au format jj/mm/aaaa » est plus utile que « date », surtout si le fichier circule entre plusieurs logiciels ou pays. De même, un identifiant n’est pas forcément un nombre à calculer. S’il commence par zéro, stockez-le comme texte.

Mettre en place les premières règles dans Excel

La première étape consiste à transformer la plage de données en tableau Excel. Sélectionnez les données, puis utilisez Insertion > Tableau. Un tableau facilite l’ajout de lignes et rend les références plus lisibles. Donnez-lui un nom explicite, comme « Ventes ».

Pour limiter les erreurs à la saisie, utilisez la validation des données. Sélectionnez la colonne « Région », ouvrez Données > Validation des données, choisissez le type « Liste » et indiquez les régions autorisées. Excel proposera alors une liste déroulante. Vous pouvez également ajouter un message d’aide, par exemple : « Choisissez une région dans la liste. »

Pour le montant, choisissez une validation qui autorise uniquement un nombre décimal supérieur ou égal à zéro. Pour la date, vous pouvez définir une plage de dates acceptée. Ces contrôles préviennent une partie des erreurs au moment de la saisie, mais ils ne suffisent pas toujours : des données collées depuis un autre fichier peuvent contourner certaines règles. Il faut donc aussi prévoir un contrôle après réception.

Créer une feuille de contrôle qualité

Ajoutez une feuille « Contrôles » qui examine les données importées et indique clairement si chaque ligne respecte le contrat. Supposons que les ventes commencent à la ligne 2, avec la date en colonne A, l’identifiant en B, la région en C et le montant en D.

Dans une nouvelle colonne, vous pouvez vérifier si le montant est numérique et positif. En ligne 2, la formule suivante renvoie « Conforme » ou « À vérifier » :

=SI(ET(ESTNUM(D2);D2>=0);"Conforme";"À vérifier")

Pour repérer une région qui ne figure pas dans une liste autorisée placée en H2:H10, utilisez :

=SI(NB.SI($H$2:$H$10;C2)>0;"Conforme";"Région inconnue")

Pour signaler une date absente ou non reconnue par Excel, vous pouvez commencer par :

=SI(ESTNUM(A2);"Conforme";"Date invalide ou manquante")

Recopiez les formules sur les lignes du tableau. Si vous utilisez un tableau Excel, la formule pourra s’étendre automatiquement lors de l’ajout de nouvelles lignes. Pour repérer les identifiants manquants, une vérification simple suffit :

=SI(B2="";"Identifiant manquant";"Conforme")

Une anomalie ne doit pas seulement être repérable : elle doit être compréhensible. « À vérifier » est un début, mais « Montant absent ou négatif » aidera davantage la personne qui corrige le fichier. Vous pouvez aussi créer une colonne de résultat global qui rassemble les contrôles de la ligne.

Faire ressortir les anomalies sans parcourir chaque cellule

La mise en forme conditionnelle permet de repérer rapidement les problèmes. Sélectionnez une colonne de contrôle, puis créez une règle qui colore en rouge les cellules contenant le texte « À vérifier ». Une autre couleur peut signaler les cellules conformes. L’objectif n’est pas de transformer le classeur en guirlande lumineuse, mais de rendre les écarts visibles en un coup d’œil.

Vous pouvez également afficher un petit bilan en haut de la feuille : nombre de lignes reçues, nombre de lignes conformes et nombre d’anomalies. Par exemple, si la colonne E contient les résultats, la formule =NB.SI(E:E;"À vérifier") compte les lignes à contrôler. Un indicateur clair aide à décider si le fichier peut être utilisé ou s’il doit être renvoyé pour correction.

Pour les cas plus complexes, Power Query peut aider à importer, nettoyer et transformer les données de façon répétable. Il est notamment utile lorsque vous recevez chaque semaine des fichiers construits selon le même modèle. Commencez toutefois par des règles simples dans Excel : un contrôle compréhensible vaut mieux qu’une automatisation mystérieuse que personne n’ose modifier.

Prévoir une procédure quand le contrat n’est pas respecté

Un contrat de données ne sert pas à distribuer des cartons rouges. Il sert à gérer les écarts de manière cohérente. Décidez à l’avance de ce qui se passe lorsqu’une règle n’est pas respectée. Une cellule vide peut parfois être corrigée rapidement ; une colonne manquante ou un format complètement différent peut rendre le fichier inutilisable.

Vous pouvez distinguer les anomalies bloquantes des anomalies non bloquantes. Une date manquante dans chaque ligne peut empêcher le calcul des ventes par période : le fichier doit alors être corrigé avant analyse. Une valeur inhabituelle, mais plausible, peut être signalée pour vérification sans suspendre tout le traitement.

Précisez également qui prend en charge la correction. La personne qui reçoit le fichier peut repérer l’anomalie, mais la personne qui connaît la donnée est souvent la mieux placée pour la corriger. Gardez une trace des problèmes récurrents : si les mêmes erreurs reviennent, il vaut peut-être mieux adapter le formulaire de saisie ou clarifier une règle que corriger encore le fichier à la main.

Les pièges à éviter

  • Des règles trop nombreuses : chaque contrainte doit répondre à un besoin réel. Un contrat impossible à suivre sera rapidement contourné.
  • Des intitulés ambigus : « Date » peut désigner une date de commande, de livraison ou de paiement. Précisez le sens.
  • Des listes codées directement dans les formules : placez plutôt les valeurs autorisées dans une feuille dédiée, plus facile à mettre à jour.
  • Des validations prises pour une garantie absolue : vérifiez aussi les fichiers importés et les données collées.
  • Une absence de responsable : si personne ne sait qui doit corriger une anomalie, elle risque de rester dans le classeur.

Enfin, versionnez votre contrat. Si une colonne change de nom ou si une nouvelle région est ajoutée, notez la date et la modification. Cela évite de comparer des fichiers qui suivent des règles différentes sans s’en rendre compte.

Commencer petit, puis améliorer

Pour votre premier contrat, choisissez un fichier récurrent et concentrez-vous sur trois ou quatre risques importants : colonnes obligatoires, valeurs autorisées, formats attendus et responsable de la correction. Documentez ces règles dans une feuille, ajoutez quelques validations de saisie et créez une feuille de contrôles avec des messages explicites.

Vous n’avez pas besoin de bâtir une usine à gaz. Même un contrôle qui repère les dates manquantes et les montants négatifs peut éviter une erreur dans un rapport mensuel. Et si votre classeur est utilisé par plusieurs personnes, ces règles communes feront gagner du temps à tout le monde : à celles et ceux qui saisissent les données comme à celles et ceux qui les analysent.

Un bon contrat de données dans Excel est finalement une promesse simple : chacun sait quelles informations fournir, chacun sait comment les vérifier et personne n’a besoin de deviner ce que signifie une colonne. Voilà de quoi rendre vos tableaux un peu plus fiables — et vos lundis matin nettement plus calmes.