Plan de contrôle interne dans Excel : échéances, écarts et corrections

Un plan de contrôle rédigé dans un document Word, des résultats dispersés dans des procès-verbaux, et personne ne sait vraiment si les écarts constatés en mars ont été corrigés en juin. C'est le scénario classique dans un service de contrôle interne, de sécurité ou de ressources humaines : le plan existe, mais le suivi se perd.

Un tableau Excel bien pensé règle l'essentiel du problème. Dans ce guide, vous verrez comment structurer un plan et un registre de contrôles, calculer automatiquement les échéances, signaler les retards avec un feu tricolore et suivre séparément la correction des écarts. Vous pouvez tout reproduire à la main, ou gagner du temps avec un modèle déjà construit, présenté en fin d'article.

Structurer le plan : une ligne par contrôle

La règle d'or : un contrôle, une ligne. Chaque ligne porte les mêmes colonnes, ce qui permet ensuite de filtrer, de compter et de synthétiser sans effort. Prévoyez au minimum :

• le type de contrôle (documentaire, sur site, thématique) ;
• le service contrôlé et le sujet ;
• l'intervenant responsable ;
• la date de début prévue et la durée en jours ;
• la date de fin prévue (calculée) et la date de fin effective (saisie) ;
• le nombre d'écarts constatés, l'échéance de correction et le nombre d'écarts corrigés.

Alimentez les colonnes « type », « service » et « intervenant » à partir de listes déroulantes (Données > Validation des données). Vous éviterez ainsi « Compta », « compta » et « Comptabilité » en trois variantes qui faussent tous vos comptages.

Calculer les échéances et les retards

La fin prévue se déduit du début et de la durée. Si un contrôle commence le 3 mars et dure 5 jours calendaires, il se termine le 7 mars : début + durée − 1.

=D2+E2-1

Le statut se calcule ensuite avec une formule SI imbriquée. Supposons la fin prévue en F2, la fin effective en G2 :

=SI(G2<>"";"Réalisé";SI(F2<AUJOURDHUI();"Hors délai";SI(F2-AUJOURDHUI()<=14;"Échéance proche";"Planifié")))

Le seuil de 14 jours est un exemple : placez-le dans une cellule de paramètres plutôt que dans la formule, pour pouvoir l'ajuster sans toucher aux lignes. Ajoutez une mise en forme conditionnelle (rouge, orange, vert) sur la colonne de statut et vous obtenez votre feu tricolore.

Suivre la correction des écarts

C'est la partie que l'on oublie le plus souvent. Un contrôle « terminé » n'est pas un dossier clos tant que les écarts ne sont pas corrigés. Ajoutez donc deux colonnes : écarts constatés et écarts corrigés, puis calculez le reste :

=H2-I2

Le retard de correction suit la même logique que celui du contrôle : si le reste est supérieur à zéro et que l'échéance de correction est dépassée, la ligne passe en rouge. Gardez les deux feux séparés : un contrôle peut être dans les temps alors que sa correction ne l'est pas.

Un exemple chiffré

Imaginons un plan de 40 contrôles sur l'année. En juin, voici ce que donne le registre :

Le taux de réalisation du plan à cette date est de 17 / 20 = 85 %. Avec les fonctions françaises :

=NB.SI.ENS(F:F;"<="&DATE(2025;6;30);G:G;"<>")/NB.SI(F:F;"<="&DATE(2025;6;30))

La même logique, avec NB.SI.ENS sur la colonne du mois, vous donne la réalisation mois par mois, que vous pouvez ensuite représenter dans un histogramme.

Les erreurs à éviter

• Mélanger jours ouvrés et jours calendaires. Choisissez une règle, écrivez-la dans une cellule de paramètres, et n'en changez pas en cours d'année.
• Saisir « en retard » à la main. Un statut tapé n'est jamais mis à jour. Calculez-le toujours à partir des dates.
• Fusionner des cellules. Elles bloquent les filtres et les tris. Préférez une colonne de plus.
• Oublier la date de référence. Pour relire la situation à une date passée, remplacez AUJOURDHUI() par une cellule « date du rapport » que vous pouvez modifier.
• Ne pas figer l'en-tête. Avec 300 lignes, Affichage > Figer les volets devient indispensable.

Le modèle Excel prêt à l'emploi

Si vous préférez ne pas construire tout cela vous-même, le modèle « Plan et registre des contrôles internes » reprend exactement cette logique. C'est un modèle payant, livré sans macros, qui fonctionne dans Excel 2007 et versions ultérieures ainsi que dans LibreOffice.

Il se compose de trois feuilles. « Listes » regroupe l'année du plan, les types de contrôle, les services, les intervenants et le paramètre d'échéance proche. « Plan et registre » accueille jusqu'à 500 contrôles : dates prévues et effectives, écarts, corrections, avec deux feux tricolores, l'un pour le contrôle, l'autre pour la correction. « Synthèse » affiche la réalisation du plan par mois, les statuts, la charge des intervenants, les écarts non corrigés et deux graphiques.

Résultat : la fin prévue est calculée, les contrôles hors délai se voient d'un coup d'œil, et vous pouvez relire la situation à une date passée en saisissant une date de rapport. Les seuils sont regroupés dans les paramètres ; comparez-les à vos règles internes et à la réglementation locale avant usage. Les calculs sont indicatifs.

FAQ

Faut-il un tableau croisé dynamique pour la synthèse ?

Pas obligatoirement. Les fonctions NB.SI.ENS et SOMME.SI.ENS suffisent pour un plan de quelques centaines de lignes et ne demandent aucune actualisation manuelle, contrairement à un tableau croisé dynamique.

À quelle fréquence mettre à jour le registre ?

Idéalement à chaque événement : fin d'un contrôle, constat d'écarts, correction. Si ce n'est pas possible, fixez un rendez-vous hebdomadaire de dix minutes ; c'est suffisant pour que les feux restent fiables.

Comment traiter un contrôle reporté ?

Modifiez la date de début prévue et conservez l'ancienne dans une colonne « commentaire ». Ne supprimez jamais la ligne : l'historique des reports est précieux pour réviser le plan l'année suivante.

Ce tableau remplace-t-il les procès-verbaux ?

Non. C'est un outil de pilotage des échéances et des corrections. Les procès-verbaux et la procédure de contrôle restent régis par vos règles internes.