KHIZHNYAKOVRapport automatique à partir d'un export de données dans Excel
Rapport automatique à partir d'une extraction de données — modèle Excel — Voir le modèle →
Chaque début de mois, le même rituel : on exporte les données du logiciel de gestion ou de caisse, on les colle dans Excel, on refait les totaux par catégorie, on compare au mois précédent, on retouche les graphiques. Quarante minutes, parfois plus, et le risque d'oublier une ligne.
Un classeur construit une fois suffit à supprimer cette corvée : vous collez l'export, la synthèse se met à jour. Cet article explique l'architecture d'un tel classeur, les formules à utiliser et les contrôles qui évitent les mauvaises surprises.
L'architecture : trois couches
- Les paramètres : l'année du rapport et le nom des colonnes de votre export.
- Les données : une feuille où vous collez l'export tel quel, accompagnée de colonnes techniques qui normalisent (mois, année).
- La synthèse : une feuille qui lit uniquement les données et calcule tout par formules.
Cette séparation est la clé : on ne touche jamais à la synthèse, on ne colle que dans les données.
Normaliser avant de totaliser
Un export contient souvent des dates en texte, des montants avec espace ou des majuscules incohérentes. Ajoutez, à côté des données collées, des colonnes techniques :
- le mois :
=MOIS(A2); - l'année :
=ANNEE(A2); - une clé combinant catégorie et mois pour accélérer les recherches.
Si la date est en texte, convertissez-la avec CNUM ou DATEVAL avant de calculer le mois. Un simple test =ESTNUM(A2) repère les lignes à corriger.
Totaux par mois et par catégorie
La fonction à connaître est SOMME.SI.ENS :
=SOMME.SI.ENS(Données!E:E;Données!C:C;$A5;Données!H:H;B$4)
E contient les montants, C la catégorie, H le mois. Copiée dans une grille « catégories × mois », elle donne la matrice complète. Un exemple :
| Catégorie | Janvier | Février | Mars |
|---|---|---|---|
| Boissons | 4 200 € | 3 900 € | 4 650 € |
| Épicerie | 6 100 € | 6 450 € | 6 000 € |
| Hygiène | 1 300 € | 1 250 € | 1 480 € |
Mesurer l'évolution
Deux comparaisons répondent à la plupart des questions : par rapport au mois précédent, et par rapport au même mois de l'année précédente.
=SIERREUR(C5/B5-1;"")
Boissons : 3 900 € en février, 4 650 € en mars, soit une hausse de 19,2 %. SIERREUR évite l'affichage d'une erreur lorsque le mois de référence est vide.
Classements : les 5 premiers
Pour un classement dynamique sans fonction récente, combinez GRANDE.VALEUR et INDEX/EQUIV :
=GRANDE.VALEUR(totaux;1)
=INDEX(noms;EQUIV(B2;totaux;0))
Un point d'attention : si deux catégories ont exactement le même total, EQUIV renvoie toujours la première. Ajoutez une infime valeur de départage (une fraction proportionnelle à la ligne) à vos totaux techniques.
Contrôler les données avant de se fier au rapport
Une synthèse n'est utile que si l'export est propre. Prévoyez une feuille de contrôle qui compte :
- les dates vides :
=NB.VIDE(A2:A5000); - le texte à la place de nombres :
=SOMMEPROD(--ESTTEXTE(E2:E5000)); - les dates hors de l'année du rapport ;
- les doublons :
=SI(NB.SI.ENS(A:A;A2;B:B;B2;E:E;E2)>1;"Doublon";"").
Chaque compteur différent de zéro est un avertissement à traiter avant de présenter les chiffres.
Les erreurs à éviter
- Modifier la synthèse à la main. À la prochaine actualisation, vos corrections disparaissent.
- Coller par-dessus sans effacer. Si le nouvel export est plus court, d'anciennes lignes restent en bas.
- Dépasser la capacité. Notez le nombre maximal de lignes que vos formules couvrent.
- Trop de catégories. Au-delà d'une trentaine, les graphiques deviennent illisibles.
- Ne pas dater le rapport. Indiquez la période couverte et la date de l'export sur la synthèse.
Le modèle Excel prêt à l'emploi
Le modèle « Rapport automatique à partir d'une extraction de données » est un fichier payant, sans macros ni fonctions récentes, compatible avec Excel 2007 et versions ultérieures et avec LibreOffice.
La feuille « Paramètres » reçoit l'année du rapport et les intitulés des colonnes de votre extraction. « Données » accueille l'en-tête et 5000 lignes à coller, avec des colonnes techniques de normalisation. « Synthèse » affiche les totaux par mois, l'évolution par rapport au mois précédent et au même mois de l'année précédente, le top 5 des catégories, le top 10 des tiers, la matrice « catégorie × mois » et des graphiques en barres et en secteurs. « Choix de période » donne le montant et la quantité sur n'importe quelle période pour les catégories choisies. « Contrôle des données » signale les dates vides, le texte à la place de nombres, les dates hors période et les doublons.
Limites : jusqu'à 5000 lignes, 30 catégories et 200 tiers. Tout tableau avec date, catégories, quantité et montant peut être collé, quel que soit le logiciel d'origine.
FAQ
Faut-il un tableau croisé dynamique plutôt ?
Un tableau croisé dynamique est excellent pour explorer, mais il demande une actualisation et une mise en forme à chaque fois. Une synthèse par formules se met à jour dès que vous collez les données et garde sa présentation.
Comment rendre le rapport plus lisible pour la direction ?
Limitez-le à une page : totaux, deux ou trois graphiques, le top 5 et une phrase de commentaire. Le détail reste dans les feuilles suivantes.
Que faire quand l'export change de format ?
Modifiez uniquement les intitulés de colonnes dans les paramètres. Si le nouvel export ajoute des colonnes, collez uniquement celles dont vous avez besoin.
Peut-on cumuler plusieurs années ?
Conservez un fichier par année, ou ajoutez une colonne « année » dans vos totaux. L'important est de ne jamais mélanger deux années dans une même matrice mensuelle.
