Power Query vs formules Excel : quand utiliser l'un ou l'autre

Power Query vs formules Excel : quand utiliser l'un ou l'autre

Vous êtes responsable technique d'un projet où les données s'accumulent. Vos utilisateurs créent des formules RECHERCHEV à rallonge, et le fichier rame. Faut-il migrer vers Power Query ? Ou rester sur des formules classiques, voire modernes comme XLOOKUP ?

La réponse dépend de trois critères : la volumétrie, la fréquence de rafraîchissement, et la complexité des transformations. Après 10 ans sur des projets industriels, j'ai vu des équipes s'embourber dans Power Query pour des besoins simples, et d'autres s'épuiser à maintenir des formules matricielles sur des bases de 200 000 lignes.

Voici comment trancher, concrètement.

Formules Excel : pour la réactivité et la simplicité

Les formules Excel (SOMME.SI.ENS, RECHERCHEX, FILTRE, etc.) excellent quand vous avez besoin de calculs instantanés sur des données stables ou peu volumineuses.

Utilisez des formules quand :

• Vos données sources changent cellule par cellule et vous voulez un recalcul immédiat
• Vous manipulez moins de 50 000 lignes (selon la puissance machine)
• Votre logique métier est simple : agrégations, recherches, filtres basiques
• Vos utilisateurs doivent auditer facilement les calculs (F2 pour tracer la formule)
• Vous devez partager le fichier avec des utilisateurs sans Power Query activé

Exemple typique : Un tableau de bord commercial qui agrège des ventes par région. Données dans un tableau structuré, formules SOMME.SI.ENS pour calculer les totaux. Chaque modification d'une ligne de vente met à jour instantanément le tableau de bord.

Limites :

• Les RECHERCHEV/RECHERCHEX sur de gros volumes ralentissent le fichier
• Impossible de dédoublonner proprement sans VBA ou Power Query
• Les formules matricielles imbriquées deviennent illisibles et fragiles
• Chaque copie de fichier duplique les données sources

Power Query : pour les transformations et les gros volumes

Power Query (l'éditeur Power Query, pas les requêtes Power Pivot) est un moteur ETL intégré à Excel. Il charge, transforme, puis déverse les données dans une feuille ou un modèle de données.

Utilisez Power Query quand :

• Vous consolidez plusieurs sources (fichiers CSV, dossiers, bases SQL)
• Vous devez nettoyer les données (suppression doublons, fractionnement colonnes, pivotage)
• Vos données dépassent 50 000 lignes et les formules ralentissent
• Vous voulez séparer les données brutes des données transformées
• Vous devez automatiser un processus répétitif (refresh quotidien)

Exemple typique : Consolidation mensuelle de 20 fichiers CSV exportés d'un ERP. Power Query charge le dossier, filtre les colonnes inutiles, dédoublonne, change les formats de date, puis charge le tout dans un tableau structuré. Un clic sur Actualiser, et le mois suivant est intégré.

Limites :

• Le refresh n'est pas instantané (quelques secondes à plusieurs minutes selon le volume)
• Les utilisateurs doivent comprendre l'éditeur Power Query pour maintenir
• Debugging plus complexe que F2 sur une cellule
• Impossible de modifier manuellement une cellule dans une table Power Query (elle sera écrasée au prochain refresh)

Les cas hybrides : combiner les deux

Sur les projets industriels matures, on combine souvent les deux approches.

Architecture classique :

1. Power Query charge et nettoie les données brutes (fichiers sources, export ERP)
2. Les données nettoyées alimentent des tableaux structurés
3. Les formules Excel calculent les indicateurs métier sur ces tableaux propres
4. Les résultats alimentent des tableaux croisés dynamiques ou des graphiques

Cette séparation des responsabilités garantit la maintenabilité. Power Query gère la plomberie (ETL), les formules gèrent la logique métier.

Exemple : Un reporting de production industrielle. Power Query consolide les données de 12 usines (CSV). Les formules calculent les TRS (Taux de Rendement Synthétique) selon des règles métier complexes. Les TCD agrègent par ligne de production.

Critères de choix résumés

Conclusion

Ne tombez pas dans le piège du "tout Power Query" parce que c'est moderne. Sur un fichier de suivi projet avec 500 lignes et quelques RECHERCHEX, les formules suffisent largement. En revanche, si vous consolidez des exports hebdomadaires multi-sources, Power Query vous fera gagner des heures.

La règle : formules pour la logique métier réactive, Power Query pour la plomberie de données. Et si votre fichier dépasse 500 000 lignes ou nécessite des jointures complexes, envisagez une vraie base de données.