OSE : transformer un tableur de 50 000 formules en moteur de calcul

Blog · Laurent Lécluse · 5 septembre 2026

Dans OSE, une « formule » est un classeur entier. Il décrit comment des heures d’enseignement sont converties, réparties et prises en compte dans le service d’un intervenant. Les règles croisent son statut, les types d’enseignement, les composantes universitaires, les pondérations et les priorités de répartition.

Le classeur de l’université de Caen contient plus de 50 000 cellules avec une formule. Son onglet « Calculs » s’étend jusqu’à la colonne DE : 109 colonnes, sur 500 lignes utilisées. C’est à cette échelle que se situe le mécanisme que j’ai conçu pour faire passer les règles du tableur au logiciel.

Un classeur qui décrit un fonctionnement métier

Le classeur public de Caen permet de voir concrètement cette organisation :

L’onglet « Calculs » contient à lui seul 45 286 cellules avec une formule. Beaucoup sont des expressions recopiées sur les lignes : ce nombre mesure l’étendue du classeur, et non autant de règles indépendantes.

Le répertoire public des formules rassemble 53 fichiers ODS à la date de cette analyse. On y trouve des variantes d’établissement et des versions successives. Cette diversité pose un problème d’architecture : comment accueillir ces règles dans un logiciel partagé, sans réécrire son moteur pour chaque variante ?

La difficulté : répartir les heures dans le bon ordre

Dans le modèle de Caen, les calculs distinguent notamment la formation initiale, l’apprentissage et la formation continue. Ils tiennent compte de la composante d’affectation et, pour certaines catégories, de l’enseignement à distance.

Le classeur organise treize blocs de répartition : formation initiale dans la composante d’affectation, dans les autres composantes, enseignement à distance, apprentissage, formation continue, activités de référentiel… Chaque bloc calcule les heures éligibles, celles qui complètent le service et le service restant dû.

Une étape dépend donc de ce qui a déjà été affecté aux étapes précédentes. Modifier une priorité peut déplacer des heures entre catégories, même si le total final paraît identique. Un contrôle limité au total ne permettrait pas de repérer cette différence.

À cela s’ajoutent les références à une ligne précédente, les conditions imbriquées et les sommes portant sur un ensemble de lignes. Comprendre le calcul demande de suivre les relations entre cellules et le sens métier de leur enchaînement.

Mon choix : compiler le modèle vers PHP

J’ai conçu une chaîne qui lit le classeur structuré, retrouve les calculs nécessaires et génère une classe PHP utilisable par OSE. Le tableur reste le support de description et de vérification des règles ; l’application exécute le code produit avec ses données métier.

1. Classeur ODSRègles, paramètres et situation de référence
2. AnalyseVariables, expressions et dépendances
3. Génération PHPCalculs globaux et calculs par ligne
4. ContrôleComparaison avec les résultats du tableur

Retrouver les cellules nécessaires au résultat

Le lecteur identifie les entrées, les sorties et la ligne qui sert de modèle aux volumes horaires. À partir des cellules de résultat, il remonte les dépendances pour retrouver les calculs intermédiaires nécessaires.

Cette analyse distingue les paramètres de l’intervenant, les valeurs propres à chaque volume horaire et les cellules de calcul. Une référence à un nom, à une cellule ou à une plage doit être rattachée au bon élément du modèle.

Préserver le sens des références

Une cellule globale, une cellule de la ligne courante et une cellule de la ligne précédente ont des rôles différents. La traduction doit conserver cette distinction.

La ligne modèle devient un calcul paramétré par le numéro de ligne. Une somme couvrant les lignes de volumes horaires devient une agrégation sur les volumes réellement présents dans OSE. Les centaines de lignes recopiées dans le classeur ne deviennent donc pas des centaines de copies identiques dans le code généré.

Traduire des expressions, puis les exécuter

Le traducteur travaille sur une représentation structurée des expressions. Il transforme les références, les variables, les tests et les fonctions, notamment les conditions et les sommes conditionnelles, avant de produire les méthodes PHP.

Il s’agit d’un format de classeur prévu pour OSE, avec ses conventions et ses contraintes. Cette délimitation permet de relier précisément les expressions du modèle aux données et aux traitements de l’application.

À l’exécution, les résultats intermédiaires sont calculés à la demande et mémorisés pendant le calcul. Plusieurs résultats qui dépendent d’une même cellule peuvent ainsi réutiliser sa valeur.

Cette chaîne est consultable dans le module Formule, notamment dans FormuleTableur, TraducteurService et AbstractFormuleCalcul.

Vérifier la traduction, catégorie par catégorie

Produire du code ne suffit pas : il faut vérifier que le logiciel retrouve les résultats du classeur.

Le mécanisme de contrôle importe la situation décrite dans le tableur, lance le calcul et compare les valeurs obtenues aux valeurs attendues pour chaque volume horaire. Le moteur actuel distingue treize catégories de sortie : service, heures complémentaires et heures non payables, chacune répartie entre quatre types d’activité, auxquelles s’ajoutent les primes.

La comparaison porte sur les valeurs arrondies au centième. Lorsqu’un écart apparaît, le diagnostic peut donner la ligne, la catégorie, les données d’entrée et les valeurs intermédiaires calculées, avec leur correspondance dans le tableur. On dispose alors d’un chemin pour rechercher l’origine de l’écart.

Ces contrôles vérifient la fidélité de la traduction sur les situations testées. La pertinence des règles et le choix des situations à couvrir restent un travail à mener avec les personnes qui connaissent le métier. Le code de cette comparaison est visible dans FormulatorService.

Même les arrondis ont leur propre traitement

Dans un résultat réparti entre plusieurs lignes et catégories, la somme des valeurs arrondies peut différer de l’arrondi de leur somme. Le sujet touche la cohérence entre le détail affiché et les totaux.

Le module comprend un arrondisseur dédié. Selon le mode choisi, il organise les arrondis entre catégories et niveaux de détail, puis répartit les centièmes d’écart. Il conserve également une trace pour le diagnostic.

Cette attention au détail fait partie de la conception du moteur : un résultat doit pouvoir être expliqué jusque dans sa répartition.

Ce que cette expérience apporte à vos projets

Un tableur important peut contenir une véritable connaissance de votre activité : des règles éprouvées, des exceptions, des dépendances et des arbitrages accumulés au fil du temps.

Mon travail consiste à rendre cette connaissance exploitable dans une application : comprendre le modèle, définir ses entrées et ses sorties, organiser son exécution et préparer les moyens de vérifier son comportement. Pour OSE, cela m’a conduit à concevoir un moteur de traduction et de calcul commun à des variantes d’établissement.

Votre activité dépend de classeurs complexes que vous souhaitez intégrer à un logiciel ? Nous pouvons partir d’un classeur représentatif, retracer ses dépendances et identifier les résultats à préserver pour cadrer la solution.

Parlons de votre moteur de calcul · Découvrir ma contribution à OSE

Repères chiffrés : lecture des fichiers ODS du dépôt public au commit cffb2aba, daté du 4 septembre 2026. Le comptage inclut les formules recopiées et exclut les cellules seulement mises en forme. Les 53 fichiers ne représentent pas un nombre d’établissements déployés.