Que fait 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 masse en 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 cellule, donc ne pas planifier d'éditions à partir de cela |
| 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 que 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 existait avant vous, prouvez-le : chargez l'original avecdata_only=Trueet regardez 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é en 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 a énoncé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 à la fin d'un tableau. Citez une source réelle lorsqu'elle existe (
Source : Rapport 10-K de l'entreprise, exercice 2024, page 45, note sur les revenus, [URL SEC EDGAR]) ; lorsque le nombre vient de l'utilisateur, dites-le clairement. - Un classeur que vous créez pour que quelqu'un le remplisse a besoin d'une courte légende indiquant quelles cellules éditer, et une ligne d'exemple avec des valeurs réalistes montrant le format attendu. N'ajoutez jamais une telle ligne à un fichier qu'on vous a demandé d'éditer.
- Édition d'un fichier existant : respectez exactement ses conventions. 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 distincts les marque — écrivez uniquement là, et laissez chaque formule existante intacte.
Recalcul (obligatoire lorsque le fichier contient des formules)
openpyxl écrit les formules sous forme de chaînes sans valeurs mises en cache. Tant que vous ne recalculez pas, chaque cellule de formule renvoie None à tout ce qui lit les valeurs mises en cache — pandas, load_workbook(data_only=True), et la plupart des visualiseurs.
python scripts/recalc.py sortie.xlsx [délai_en_secondes] # par 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 indique combien ont été 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 de status signifie que rien n'a été recalculé, et seul ce cas se termine avec un code non nul — errors_found se termine par 0, donc ne considérez jamais une sortie propre comme un classeur propre.
Un recalc 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 donne un fichier propre et sans erreur avec des nombres erronés. Écrivez d'abord 2–3 formules et vérifiez qu'elles extraient les valeurs attendues, avant de construire une grille.
Un classeur qui lie un autre fichier perd ces liens si vous le réenregistrez avec openpyxl puis recalculez.
Une telle formule lit ='[1]Returns Analysis'!$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 mise en cache de la cellule est la seule chose qui contient 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 depuis l'original avant de sauvegarder par-dessus (--force passe outre et accepte la perte).
Choisir des formules qui survivent à la vérification
LibreOffice implémente moins de fonctions qu'Excel, et une qu'il ne peut pas évaluer devient un #NAME? littéral intégré dans le fichier que vous livrez.
- Privilégiez les fonctions de l'époque Excel 2007 —
SUMIFS,INDEX,MATCH,IFERROR,SUMPRODUCT— qui n'ont besoin d'aucun préfixe. - Six fonctions post-2007 fonctionnent, mais uniquement avec un préfixe
_xlfn., car openpyxl écrit votre formule dans le XML telle quelle 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 runtime 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 reçoit une valeur — etrecalc.pysignaletotal_errors: 0sur le résultat tronqué. UtilisezINDEX/MATCHpour les recherches, et triez, filtrez et dédoublonnez 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 mises en cache sans les formules ; la valeur par défaut donne les chaînes de formules sans valeurs. Une seule passe ne peut pas donner les deux. data_only=Trueest destructeur si vous enregistrez. Ce classeur n'a plus de formules, donc l'enregistrement remplace chaque formule par une valeur littérale — définitivement.data_only=Truesur un fichier qu'openpyxl vient d'écrire renvoieNonepartout — exécutez d'abordrecalc.py. (Une formule dont le résultat est""renvoie aussiNone.)- Cellules fusionnées : écrivez uniquement l'ancre en haut à gauche. Chaque autre cellule de la plage est une
MergedCelldont la propriété.valueest en lecture seule. .xlsmperd ses macros sauf si vous passezkeep_vba=Trueàload_workbook.- Un nom de feuille contenant un espace doit être entre guillemets dans une référence inter-feuilles :
='Assumptions Inputs'!$B$5. Sans guillemets, il 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);-) ·
les négatifs entre parenthèses · pourcentages 0,0%, stockés comme fractions (0,15 affiche 15,0% ; stocker 15 affiche 1500,0%) ·
multiples de valorisation 0,0x · années comme 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, puisqu'une
cellule modifiée isolé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 uniquement si une importation échoue ou si la commande est manquante) · LibreOffice (soffice, auto-configuré pour les environnements sandbox via scripts/office/soffice.py)