Excel au-delà de ses limites apparentes
Un tableau croisé dynamique classique commence à ramer dès 500 000 lignes. Mais Excel peut traiter 10 millions de lignes sans sueur grâce au modèle de données (Power Pivot) et à un usage intelligent des requêtes Power Query. La plupart des analystes ignorent ces techniques et transfèrent inutilement leurs analyses vers des bases SQL.
Pourquoi un TCD classique rame
Le TCD traditionnel charge toutes les données en mémoire cache, même celles non affichées. Sur 1 million de lignes et 20 colonnes, cela consomme plusieurs GB de RAM. Chaque interaction (glisser un champ) déclenche un recalcul complet.
Solution 1 : charger vers le modèle de données
Au lieu de convertir en Tableau Excel classique, importez via Power Query avec l’option Ajouter ces données au modèle de données. Le stockage passe en format colonne compressé (xVelocity), diminuant la taille mémoire de 70-90 pour cent.
Une base CSV de 200 Mo occupe 20 Mo dans le modèle. Le TCD est fluide.
Solution 2 : agrégations préalables
Power Query avant chargement : regroupez par les dimensions nécessaires et agrégez les mesures. 1 million de transactions peuvent devenir 10 000 lignes de totaux par jour × produit. Le TCD tourne alors instantanément.
Accueil > Regrouper par > Avancé : plusieurs dimensions et plusieurs agrégations (Somme, Moyenne, NbDistinct)Solution 3 : DAX plutôt que formules
Dans Power Pivot, créez des mesures DAX au lieu de colonnes calculées :
CA Total := SUM(Transactions[Montant])
Nb Clients := DISTINCTCOUNT(Transactions[ClientID])
Panier Moyen := DIVIDE([CA Total]; [Nb Clients])Les mesures sont calculées dynamiquement selon le contexte, sans stockage.
Optimisations supplémentaires
- Désactiver le recalcul automatique des TCD pendant la construction : Options du TCD > Données > Actualiser manuellement
- Supprimer les colonnes inutiles avant chargement
- Changer les types : entier 32 bits compresse mieux que texte
- Préférer les dates aux chaînes pour les colonnes temporelles
- Indexer par ordre croissant (Power Query Trier avant chargement)
Connexion directe SQL Server
Si vos données sont dans une base SQL, importez en mode Query Direct plutôt qu’en copie. Excel n’embarque pas les données : chaque actualisation interroge la base. Idéal pour dashboards en temps quasi-réel sur des milliards de lignes.
Segments et chronologies
Ajoutez des Segments (Slicers) et Chronologies sur un TCD basé sur modèle de données. Un segment partagé entre plusieurs TCD permet de filtrer simultanément sur une même dimension. Construction de dashboards multi-vues très performants.
Comparaison performance
Sur un jeu de données de 2 millions de lignes :
- TCD classique : 25 secondes de chargement initial, 8 secondes par interaction
- TCD modèle de données : 5 secondes initial, 0,3 seconde par interaction
Quand passer à Power BI
Au-delà de 50 millions de lignes ou pour un partage web interactif, Power BI Desktop est plus adapté. La bonne nouvelle : les modèles Power Pivot migrent vers Power BI sans réécriture DAX.
Conclusion
Excel moderne, bien utilisé, couvre 95 pour cent des besoins d’analyse en entreprise. La connaissance de Power Query + Power Pivot transforme un utilisateur lambda en analyste de niveau professionnel. Investissement : 2 weekends de formation. ROI : illimité sur toute la carrière.
