Pour consolider des fichiers Excel avec Power Query, il faut d'abord savoir si l'on veut empiler des exports de ventes ou rapprocher des ventes et des objectifs. Ce sont deux opérations différentes, souvent confondues, et c'est là que les consolidations deviennent fragiles.
Une méthode rejouable classe les fichiers dans un dossier dédié, normalise leur structure, conserve une trace de leur origine, puis contrôle les totaux avant de produire le tableau de bord. Claude peut aider à formaliser les étapes ou à lire une erreur ; Power Query reste le moteur qui rejoue la transformation.
Ajouter les exports de ventes, fusionner les objectifs
Ajouter consiste à empiler les lignes de fichiers comparables : janvier, février et mars deviennent une table de ventes unique. Les colonnes doivent raconter le même fait, avec les mêmes noms et les mêmes types : date, client, produit, montant, quantité, portefeuille.
Fusionner consiste à rapprocher deux tables par une clé. C'est le bon geste pour rattacher un objectif mensuel à un portefeuille, une famille à une référence ou un commercial à un client. Une fusion ne corrige pas une clé absente ou incohérente : elle rend ce défaut visible sous forme de lignes non rapprochées.
Microsoft indique que la combinaison de fichiers d'un dossier repose sur un même type de fichier et une même structure, y compris les colonnes. Son guide officiel de combinaison de fichiers explique également que Power Query crée une requête d'exemple et une fonction appliquée aux fichiers du dossier.
| Besoin | Opération | Exemple |
|---|---|---|
| Réunir les ventes mensuelles | Ajouter | Un export par mois, mêmes colonnes |
| Ajouter le portefeuille ou la famille | Fusionner | Ventes + table de correspondance client |
| Comparer réalisé et objectif | Fusionner ou relier dans le modèle | Ventes mensuelles + objectifs par portefeuille |
Préparer le dossier et le schéma avant la première requête
Une consolidation de dossier lit tout ce qui correspond au filtre. Le dossier ne doit donc pas contenir un ancien export, une copie manuelle ou un fichier de test portant les mêmes données. Un dossier de dépôt séparé du fichier de tableau de bord évite de créer des doublons par accident.
Avant de connecter Power Query, écrivez le schéma attendu. La liste est courte : nom de l'onglet ou du tableau, en-têtes obligatoires, type de chaque colonne, règle de date et convention de nommage. Une exportation qui remplace CA HT par Montant net doit être repérée avant l'actualisation du comité.
Conserver l'origine de chaque ligne
Gardez le nom du fichier source pendant les tests. Il permet de vérifier que tous les exports attendus sont présents, d'identifier une période chargée deux fois et de revenir au bon fichier lorsqu'un total ne correspond pas. On peut le masquer du tableau final, pas le supprimer de la logique de contrôle.
Choisir la bonne clé de rapprochement
Une clé peut être le mois et le portefeuille, ou le mois, le commercial et le canal. Elle doit être définie au même niveau dans les deux tables. Tenter de rattacher un objectif mensuel global à chaque ligne de facture le répète autant de fois qu'il y a de lignes et gonfle mécaniquement les comparaisons.
Les étapes d'une consolidation de ventes rejouable
Dans Excel, la séquence habituelle commence par Données > Obtenir des données > À partir d'un dossier. Le point important n'est pas le bouton : c'est ce que la requête fait à chaque étape et ce qu'elle suppose de vos fichiers.
- Filtrer la liste de fichiers. Limitez-la au format et au dossier attendus ; retirez les fichiers temporaires ou archivés.
- Choisir le fichier exemple. Vérifiez son onglet, ses en-têtes et ses types avant que Power Query applique la transformation aux autres fichiers.
- Nettoyer les données. Normalisez les dates, les textes et les types numériques, puis gardez les colonnes nécessaires au pilotage.
- Ajouter les exports. La table unique conserve date, client, produit, montant et nom du fichier d'origine.
- Fusionner les tables de référence. Objectifs, portefeuille, famille de produit ou liste d'exclusions sont rapprochés sur les clés validées.
- Charger et contrôler. La sortie alimente une table Excel ou le modèle de données, jamais directement une conclusion non vérifiée.
Power Query affiche les transformations dans Étapes appliquées. Microsoft précise que chaque étape correspond à une formule du langage M. Cette liste est votre journal de fabrication : si le format change, elle indique l'étape exacte qui ne tient plus.
Contrôler avant d'actualiser le tableau de bord
Le mot « automatique » devient dangereux lorsqu'il remplace le contrôle. Une requête peut s'exécuter sans erreur et sortir un chiffre erroné si un export incomplet a remplacé le bon fichier, si des montants sont devenus du texte ou si une clé ne rapproche plus les lignes.
Trois contrôles avant diffusion
Compter les lignes par fichier, réconcilier le total CA avec chaque export et repérer les clés sans correspondance après une fusion. Ces trois gestes trouvent la plupart des défauts avant qu'ils n'arrivent dans une synthèse.
Conservez aussi une règle claire sur les corrections manuelles. Si une ligne doit être exclue, la règle doit vivre dans une table de référence ou une étape de requête documentée, pas dans une cellule retouchée après l'actualisation. Sinon la correction disparaîtra au cycle suivant.
Ce que Claude peut aider à faire, et ce qu'il ne doit pas valider seul
Claude peut transformer une règle métier écrite en étapes testables, expliquer le rôle d'une étape Power Query ou proposer un extrait de M. Il peut aussi aider à préparer une liste de tests : colonnes obligatoires, identifiants absents, périodes dupliquées, montants non numériques.
Mais une réponse qui semble plausible n'est pas une réconciliation comptable. Faites-lui expliquer sa proposition, exécutez-la dans l'éditeur Power Query et comparez le résultat avec des totaux indépendants. L'add-in Claude pour Excel ne remplace d'ailleurs pas Power Query pour une consolidation multi-fichiers ; les limites sont détaillées dans notre article sur Claude pour Excel.
Une fois les ventes consolidées et les objectifs rapprochés, le tableau de bord devient une couche de lecture : CA réalisé, objectif, écart, marge, taux de marge, puis le détail par client, produit ou portefeuille. Les calculs ne doivent plus dépendre d'un copier-coller mensuel.
Le tableau de bord commercial Excel complète cette méthode : quels indicateurs afficher et comment passer du total au sujet qui demande une action. Pour une base Excel qui n'intègre pas ses nouvelles lignes, commencez par traiter le problème de structure plutôt que de multiplier les formules.
FAQ : consolidation de ventes avec Power Query
Power Query peut-il consolider plusieurs fichiers Excel de ventes ?
Oui, à condition qu'ils partagent la structure attendue. Le dossier et ses filtres doivent aussi être maîtrisés pour éviter les doublons ou les anciens exports.
Faut-il mettre les objectifs dans le même fichier que les ventes ?
Non. Une table séparée est souvent plus claire. Elle doit toutefois utiliser les mêmes clés de comparaison : période, portefeuille, commercial, client ou produit.
Pourquoi une actualisation échoue après un nouvel export ?
Un en-tête, un onglet, une date ou un type de données a souvent changé. Les étapes appliquées indiquent où la requête se bloque ; la convention de schéma aide à corriger la cause.
Comment éviter de compter deux fois le même mois ?
Gardez le nom de fichier en colonne, vérifiez les périodes présentes et isolez les fichiers d'archives hors du dossier de dépôt. Le total est ensuite rapproché avec chaque export source.
Claude peut-il créer une requête Power Query ?
Il peut aider à écrire ou expliquer une requête, mais chaque étape doit être testée sur vos fichiers et réconciliée avec des contrôles indépendants.
Quelle différence entre ajouter et fusionner ?
Ajouter empile des lignes de même structure. Fusionner rapproche deux tables selon une clé. Les exports mensuels sont généralement ajoutés ; les objectifs sont ensuite fusionnés ou reliés.
Sources et lectures utiles
- Microsoft Learn : combiner des fichiers avec Power Query.
- Microsoft Support : combiner plusieurs sources de données avec Power Query.
- Pourquoi votre tableau Excel ne se met pas à jour : rendre la source durable avant de l'alimenter.
- Tableau de bord supply chain avec Claude : le même travail de consolidation appliqué aux stocks, commandes et livraisons.
