Création, édition et analyse XLSX
| Tâche | Approche |
|---|---|
| Créer ou éditer avec formules/mise en forme | openpyxl — voir les pièges ci-dessous |
| Données en vrac entrée ou sortie | pandas (read_excel, to_excel) |
| Aperçu rapide d'une feuille | markitdown fichier.xlsx — ## NomFeuille par feuille ; lit aussi .xlsm. Pas de coordonnées de cellules, donc ne planifiez pas d'éditions à partir de celui-ci |
| Lire un modèle (formules et valeurs) | deux passes load_workbook — voir les pièges |
openpyxl,pandasetmarkitdownsont préinstallés — n'exécutez paspip installd'abord ; écrivez le script et importez directement. Uniquement si une importation échoue (ou si la commandemarkitdownest manquante) :pip installle paquet manquant.
Les chemins de scripts ci-dessous sont relatifs au répertoire de cette compétence.
Exigences pour chaque sortie
- Police professionnelle (Arial, Times New Roman) partout, sauf indication contraire de l'utilisateur.
- Zéro erreur de formule. Ne livrez jamais tant que
recalc.pysignaleerrors_found. Si vous pensez qu'une erreur vous préexistait, prouvez-le : chargez l'original avecdata_only=Trueet examinez cette cellule. Une erreur que vous avez introduite ressemble exactement à une erreur héritée. - Utilisez des formules, jamais de résultats codés en dur. Écrivez
sheet['B10'] = '=SUM(B2:B9)', pas le total calculé par Python. La feuille doit se recalculer lorsque ses entrées changent. - Suivez les spécifications de l'utilisateur à la lettre. Noms d'onglets exacts, en-têtes de colonnes exacts, et la formule qu'il/elle a explicitée. Une refonte qui calcule autre chose échoue, aussi élégante soit-elle.
- Documentez chaque hypothèse et nombre codé en dur là où le lecteur le verra — un commentaire de cellule, ou une cellule adjacente en fin de tableau. Citez une source réelle lorsqu'elle existe (
Source : Company 10-K, FY2024, Page 45, Note sur les Revenus, [URL SEC EDGAR]) ; lorsque le nombre provient de l'utilisateur, dites-le clairement. - Un classeur que vous créez pour que quelqu'un le remplisse nécessite une courte légende nommant les cellules à éditer, et une ligne d'exemple avec des valeurs réalistes montrant le format attendu. N'ajoutez jamais une telle ligne à un fichier que l'on vous a demandé d'éditer.
- Édition d'un fichier existant : respectez ses conventions exactement. Elles remplacent toutes les directives ici. Trouvez d'abord ses cellules d'entrée désignées — une couleur de police, un remplissage ou une trame distinctifs les marquent — écrivez uniquement là, et ne touchez à aucune formule existante.
Recalculer (obligatoire chaque fois que le fichier contient des formules)
openpyxl écrit les formules sous forme de chaînes sans valeurs mises en cache. Jusqu'à ce que vous recalculiez, chaque
cellule de formule se relit comme None pour tout ce qui lit les valeurs en cache — pandas,
load_workbook(data_only=True) et la plupart des visionneuses.
python scripts/recalc.py sortie.xlsx [temps_limite_secondes] # défaut 30
LibreOffice calcule chaque formule, le fichier est réécrit sur place, et vous obtenez du JSON :
status (success | errors_found), total_formulas, total_errors, et un
error_summary nommant jusqu'à 100 cellules par type d'erreur (locations_truncated dit combien il a
retenues — faites confiance à total_errors, pas à la longueur de la liste). Corrigez ce qu'il nomme et relancez-le.
JSON avec une clé error au lieu d'un status signifie que rien n'a été recalculé, et
seulement ce cas-là se termine avec un code non nul — errors_found se termine avec 0, donc ne traitez jamais un
code de sortie propre comme un classeur propre.
Un recalcul vert prouve que vos formules s'évaluent, pas qu'elles sont correctes. Une plage décalée d'une ligne ou une référence à la mauvaise ligne produit un fichier propre et sans erreur avec des nombres erronés. Écrivez 2-3 formules d'abord et vérifiez qu'elles tirent les valeurs attendues, avant de construire une grille.
Un classeur qui pointe vers un autre fichier perd ces liens si vous le réenregistrez avec openpyxl et
ensuite recalculez. Une telle formule lit ='[1]Retour Sur Analyse'!$B$2 — le [1] est un index
dans la liste des références externes du classeur, nommant un fichier séparé sur le disque, pas une feuille.
Ce fichier est rarement présent ici, donc la valeur en cache de la cellule est la seule chose qui retient ses
données. openpyxl supprime cette valeur lors de l'enregistrement ; LibreOffice doit alors résoudre la référence
pour de vrai, échoue, écrit #NAME? et supprime tous les liens. recalc.py refuse de s'exécuter dans cet état
— copiez les valeurs de ces cellules hors de l'original avant de sauvegarder par-dessus (--force le remplace
et accepte la perte).
Choisir des formules qui survivent à la vérification
LibreOffice implémente moins de fonctions qu'Excel, et celle qu'il ne peut pas évaluer devient un
littéral #NAME? cuit dans le fichier que vous livrez.
- Privilégiez les fonctions de l'ère Excel 2007 —
SUMIFS,INDEX,MATCH,IFERROR,SUMPRODUCT— qui n'ont besoin d'aucun préfixe. - Six fonctions post-2007 fonctionnent, mais seulement avec un préfixe
_xlfn., car openpyxl écrit votre formule textuellement dans le XML et Excel stocke les noms post-2007 préfixés (son interface cache le préfixe) :_xlfn.TEXTJOIN,_xlfn.CONCAT,_xlfn.IFS,_xlfn.SWITCH,_xlfn.MAXIFS,_xlfn.MINIFS. Écrites nues, chacune produit#NAME?. - N'utilisez jamais
XLOOKUP,XMATCH,SORT,FILTER,UNIQUEouSEQUENCE. Le moteur d'exécution de LibreOffice ne peut pas les évaluer sous aucun préfixe. Les versions plus récentes les évaluent, mais ce sont des fonctions de tableau débordantes et un fichier écrit par openpyxl n'a pas de métadonnées de débordement, donc seule la cellule en haut à gauche de la plage obtient une valeur — etrecalc.pysignaletotal_errors: 0sur le résultat tronqué. UtilisezINDEX/MATCHpour les recherches, et triez, filtrez et dédupliquez en Python avant d'écrire les cellules. - Une formule que LibreOffice n'a pas pu analyser est réécrite en minuscules — un indicateur rapide à côté d'un
#NAME?.
Pièges d'openpyxl
- Lire un modèle nécessite deux chargements.
data_only=Truedonne les valeurs en cache avec les formules disparues ; la valeur par défaut donne les chaînes de formules sans valeurs. Un seul passage ne peut pas fournir les deux. data_only=Trueest destructeur si vous enregistrez. Ce classeur n'a plus de formules, donc l'enregistrement remplace chacune par un littéral — définitivement.data_only=Truesur un fichier qu'openpyxl vient d'écrire renvoieNonepartout — exécutezrecalc.pyd'abord. (Une formule dont le résultat est""se relit aussi commeNone.)- Cellules fusionnées : écrivez uniquement l'ancre en haut à gauche. Chaque autre cellule dans la plage est un
MergedCelldont.valueest en lecture seule. .xlsmperd ses macros sauf si vous passezkeep_vba=Trueàload_workbook.- Un nom de feuille contenant un espace doit être cité dans une référence inter-feuille :
='Hypothèses Entrées'!$B$5. Sans guillemets, elle s'évalue en#VALUE!.
Modèles financiers
Sauf indication contraire de l'utilisateur, ou si le fichier existant fait déjà autre chose.
Couleur : texte bleu (0,0,255) pour les entrées codées en dur et les leviers de scénario · noir pour les formules ·
vert (0,128,0) pour les liens vers une autre feuille · rouge (255,0,0) pour les liens vers un autre fichier ·
remplissage jaune (255,255,0) pour les hypothèses clés et les cellules que l'utilisateur doit remplir.
Nombres : devise $#,##0, avec l'unité nommée dans l'en-tête (Revenus ($mm)) · les zéros
s'affichent comme -, y compris dans les pourcentages ($#,##0;($#,##0);-) · négatifs entre parenthèses ·
pourcentages 0.0%, stockés sous forme de fractions (0.15 affiche 15.0% ; stocker 15 affiche
1500.0%) · multiples de valorisation 0.0x · années sous forme de texte ("2024", jamais 2 024).
Structure : chaque hypothèse dans sa propre cellule étiquetée, référencée par les formules qui l'utilisent
(=B5*(1+$B$6), jamais =B5*1.05) · formules cohérentes sur chaque période de projection, car une
seule cellule éditée au milieu d'une ligne est l'erreur silencieuse la plus courante · protégez les dénominateurs qui peuvent être nuls.
Dépendances
openpyxl, pandas, markitdown (pip, préinstallés — installez seulement si une importation échoue ou si la commande est manquante) · LibreOffice (soffice, auto-configuré pour les environnements sandboxés via scripts/office/soffice.py)