Un modèle de données Power Pivot permet de relier plusieurs tableaux financiers entre eux (grand livre, balance, budget, plan comptable) par de vraies relations, comme dans une base de données, plutôt qu’avec des formules RECHERCHEV qui se multiplient et se cassent au moindre changement. Voici comment le construire.
Sommaire
Le problème : des RECHERCHEV entre plusieurs fichiers
En comptabilité et en contrôle de gestion, les données à croiser sont rarement dans un seul fichier : le grand livre est dans un classeur, le plan comptable dans un autre, le budget dans un troisième. Pour les relier, le réflexe classique est d’ajouter une colonne RECHERCHEV qui va chercher l’information d’un fichier à l’autre (retrouver le libellé et la catégorie d’un compte, comparer réalisé et prévisionnel). Ça fonctionne pour un petit volume, mais dès que les fichiers grossissent ou se multiplient, chaque nouveau croisement ajoute une nouvelle colonne de formule, le classeur ralentit, et le jour où la structure d’un des fichiers change, toutes les formules sont à revérifier.
Le principe : des relations plutôt que des formules
Power Pivot permet d’importer plusieurs tables dans un seul modèle de données, puis de les relier entre elles par une relation, sur une colonne commune (par exemple le code compte). Une fois la relation créée, plus besoin de RECHERCHEV : vous pouvez construire un tableau croisé dynamique qui pioche des champs dans plusieurs tables à la fois, comme si elles n’en formaient qu’une seule. C’est exactement le même principe qu’une base de données relationnelle, ou que le modèle utilisé dans Power BI.
Autre avantage, et pas des moindres pour les financiers : Power Pivot repose sur un moteur de calcul en colonnes capable de gérer plusieurs millions de lignes sans ralentir, là où Excel classique commence à souffrir bien avant. C’est ce qui permet de manipuler un grand livre annuel complet, plusieurs années d’historique ou l’ensemble des écritures d’un groupe, directement dans Excel.
Un échange gratuit pour évaluer vos besoins
Envie de structurer votre reporting financier avec Excel et Power BI ? Parlons-en ensemble, sans engagement.
Étape par étape : construire le modèle
La logique est la même quelles que soient vos tables. Prenons un exemple de modèle de ventes : une table de faits TVente reliée à plusieurs tables de dimension (TProduit, TClient, TRégion, et un calendrier) — exactement la même mécanique s’applique pour relier un grand livre à un plan comptable ou à un budget, via un code compte commun. Power Pivot doit être activé au préalable : si ce n’est pas encore fait, suivez d’abord notre article Activer Power Pivot dans Excel.
- Ajoutez chaque table au modèle de données : sélectionnez votre tableau, puis dans l’onglet Power Pivot, cliquez sur « Ajouter au modèle de données ». Répétez pour chaque table à relier (ventes, produits, clients, régions, calendrier…).
- Ouvrez la fenêtre Power Pivot (Power Pivot > Gérer), puis basculez en Vue Diagramme (icône en bas à droite de la fenêtre). Faites ensuite un glisser-déposer de la colonne « Code Produit » de la table TVente vers la colonne « Code Produit » de la table TProduit pour créer la relation : une ligne apparaît entre les deux tables.
- Vérifiez les relations : sur l’image précédente, en Vue Diagramme, on voit déjà plusieurs relations créées autour de la table de faits TVente : avec TProduit (par le code produit), avec TRégion (par la ville) et avec TClient (par le code client), tandis que la table Calendrier est reliée à TVente par la date. C’est exactement ce schéma — une table de faits entourée de tables de dimension — qu’on cherche à reproduire, que vos tables soient des ventes ou un grand livre et un plan comptable.
- Répétez l’opération pour chaque paire de tables à relier.
- Créez un tableau croisé dynamique depuis le modèle : Power Pivot > TCD. Dans la liste des champs, vous verrez désormais toutes vos tables, et vous pouvez combiner des champs de plusieurs tables dans le même tableau croisé.
Ce que ça change pour vos TCD
Une fois le modèle en place, ajouter une nouvelle donnée (un nouveau compte, une nouvelle ligne budgétaire) ne demande plus de retoucher des formules : il suffit d’actualiser. Les tableaux croisés dynamiques restent rapides même avec plusieurs centaines de milliers, voire plusieurs millions de lignes, puisque Power Pivot est conçu pour ça. Et cette même logique de modèle de données est celle que vous retrouverez telle quelle dans Power BI, si vous évoluez vers cet outil par la suite.
Aller plus loin
Construire un modèle de données propre est la base de tout reporting financier fiable, que ce soit dans Excel avec Power Pivot ou dans Power BI. Nos formations couvrent tout le parcours : Excel, Power Query, Power Pivot et Power BI, pour construire un reporting financier complet et fiable.
Découvrir nos formations
Excel, Power Query, Power Pivot, Power BI… des formations pratiques pour structurer et automatiser votre reporting financier.
