Excel : formules, TCD ou Power Query — quoi apprendre pour arrêter le copier-coller mensuel ?
Chaque mois, vous ouvrez plusieurs fichiers, déplacez des colonnes, recopiez des formules, puis reconstruisez la même synthèse. Le bon choix n’est pas forcément « apprendre Power Query ». Il dépend de l’endroit où se situe le travail répétitif : dans le calcul, dans la synthèse ou avant l’analyse.
- Choisissez les formules et tableaux Excel si la source est déjà propre et si vous devez calculer une règle ligne par ligne.
- Choisissez un tableau croisé dynamique (TCD) si la source est propre et si vous devez surtout regrouper, filtrer et comparer.
- Choisissez Power Query si vous répétez l’import, le nettoyage, la mise au même format ou la combinaison de fichiers.
- Combinez-les si votre processus comporte ces trois étapes : Power Query prépare, les formules calculent et le TCD synthétise.
Cette réponse évite deux erreurs coûteuses : apprendre un outil trop large pour un besoin simple, ou perfectionner des formules alors que le problème vient des données reçues.
La matrice Source–Traitement–Sortie
Ne partez pas du nom d’une fonction. Prenez le dernier reporting terminé et notez chaque geste effectué entre l’ouverture des sources et l’envoi du résultat. Classez ensuite les gestes avec la matrice Source–Traitement–Sortie.
| Zone | Question à poser | Exemple de geste répétitif | Compétence à examiner d’abord |
|---|---|---|---|
| Source | Les données arrivent-elles toujours sous la même forme ? | Ouvrir douze exports, supprimer des lignes, renommer les mêmes colonnes | Power Query |
| Traitement | Faut-il appliquer une règle à chaque ligne ? | Retrouver un tarif, calculer une marge, attribuer un statut | Formules et tableaux Excel |
| Sortie | Faut-il résumer la même table selon plusieurs axes ? | Total par agence, mois et famille avec filtres | TCD |
| Contrôle | Comment prouver que le résultat est complet ? | Comparer le nombre de lignes, les totaux et la période | Contrôles à intégrer dans tous les cas |
Le classement doit porter sur le travail réel, pas sur le fichier final. Deux rapports visuellement identiques peuvent demander des compétences différentes : l’un part d’une table propre, l’autre de vingt fichiers hétérogènes.
1. Apprendre les formules quand la règle est le cœur du problème
Les formules conviennent lorsque chaque ligne de la source doit produire un résultat selon une règle explicite : montant, délai, catégorie, recherche de référence ou alerte. Un tableau Excel est alors préférable à une plage figée : Microsoft indique que les références structurées utilisent les noms du tableau et de ses colonnes, et qu’elles s’ajustent lorsque des données sont ajoutées ou supprimées (Microsoft Support — références structurées).
Exemples adaptés :
- retrouver un prix à partir d’une référence ;
- calculer un écart entre budget et réalisé ;
- attribuer « à relancer » selon une date et un statut ;
- vérifier qu’une clé existe dans un référentiel ;
- produire un message utile lorsqu’un cas n’est pas prévu.
Le signal d’alerte n’est pas la longueur de la formule, mais son rôle. Si la formule commence à importer plusieurs fichiers, reproduire des dizaines d’opérations de nettoyage ou compenser une source instable, vous traitez probablement le mauvais étage du processus.
La formation Excel expérimenté : formules fiables et modèles contrôlables correspond à ce besoin. Le programme vérifié distingue bien les règles de calcul des parcours consacrés aux TCD et à Power Query.
2. Apprendre les TCD quand la question porte sur une synthèse
Un TCD sert à calculer, résumer et analyser une table : comparer des catégories, observer des tendances, regrouper des dates ou modifier rapidement un angle de lecture. Microsoft recommande une source organisée en colonnes avec une seule ligne d’en-tête (Microsoft Support — créer un tableau croisé dynamique).
Le TCD est un bon choix si vous pouvez formuler la demande ainsi :
« À partir de cette table fiable, je veux le montant et le nombre de dossiers par agence, par mois et par statut, avec des filtres visibles. »
Il n’est pas le meilleur premier outil si vous devez, avant chaque actualisation, supprimer trois lignes de titre, convertir des dates stockées en texte et réunir plusieurs fichiers. Le TCD résumera ce qu’on lui donne ; il ne rend pas automatiquement la source juste.
Autre point de contrôle : Microsoft précise qu’un champ numérique est généralement résumé par une somme, mais qu’un champ interprété comme texte peut être compté. Il faut donc vérifier les types et le calcul choisi, pas seulement l’apparence du tableau.
La formation Excel tableaux croisés dynamiques : analyser une source contrôlée est dédiée à cette décision : question d’analyse, qualité de la source, calcul, filtres et recette d’actualisation.
3. Apprendre Power Query quand la préparation se répète
Power Query est indiqué lorsque le même enchaînement revient : connecter une source, transformer les données, combiner plusieurs tables, charger le résultat et l’actualiser. Microsoft décrit précisément ces phases et indique que chaque transformation est enregistrée comme une étape rejouée lors du rafraîchissement (Microsoft Support — Power Query dans Excel).
Exemples adaptés :
- importer chaque mois les fichiers d’un même dossier ;
- aligner les noms et types de colonnes ;
- supprimer les lignes ou champs inutiles selon une règle stable ;
- ajouter les périodes les unes sous les autres ;
- fusionner un export avec un référentiel ;
- dépivoter un tableau de présentation avant analyse.
« Actualiser » ne dispense pas de contrôler. Une requête peut s’exécuter sans erreur tout en oubliant un fichier ou en rejetant des lignes. Conservez au minimum : période attendue, nombre de fichiers, nombre de lignes, total de contrôle, clés absentes et erreurs.
La formation Excel avec Power Query : nettoyer et automatiser les données propose deux niveaux vérifiés : 14 heures pour les fondations et 21 heures pour des requêtes paramétrables et maintenables. Le niveau fondations demande déjà de savoir utiliser tableaux structurés, tris, filtres et formules simples ; aucune connaissance du langage M n’est annoncée comme nécessaire.
La décision tient souvent à un verbe
| Votre verbe principal | Outil de départ | Ce qu’il faut savoir prouver |
|---|---|---|
| Calculer, rechercher, classer | Formules | La règle fonctionne sur cas normal, limite, vide et absent |
| Résumer, comparer, filtrer | TCD | La source, l’agrégation, les filtres et l’actualisation sont contrôlés |
| Importer, nettoyer, combiner | Power Query | Les sources, étapes, rejets et totaux sont traçables |
| Préparer puis analyser | Power Query + TCD | La sortie de la requête alimente une synthèse correctement actualisée |
| Préparer, calculer puis analyser | Power Query + formules + TCD | Chaque règle est placée au bon étage et possède son contrôle |
Une macro VBA n’est donc pas la première réponse à un copier-coller mensuel. Elle peut piloter des actions dans Excel, mais elle n’est pas nécessaire pour une préparation de données que Power Query sait rejouer, ni pour une synthèse que le TCD sait produire.
Simulation fictive : douze agences, un rapport mensuel
Cette situation est une simulation pédagogique, pas un cas client observé.
Une responsable reçoit douze classeurs. Les colonnes sont presque identiques, mais l’une s’appelle « CA » et l’autre « Chiffre_affaires ». Elle doit calculer la marge, puis présenter le total par agence et famille de produits.
La mauvaise réponse serait de chercher un outil unique pour tout faire. La chaîne la plus lisible est :
- Power Query importe les douze fichiers, harmonise les colonnes, ajoute le nom du fichier comme trace et signale les structures non conformes.
- Une formule documentée calcule la marge si cette règle doit rester visible et contrôlable dans la table de sortie. Elle pourrait aussi être créée pendant la transformation : le choix dépend du propriétaire de la règle et de sa réutilisation.
- Un TCD résume montant et marge par agence et famille, avec période et filtres visibles.
- Un contrôle rapproche le nombre de fichiers, le nombre de lignes et le chiffre d’affaires total avec les références disponibles.
La prochaine période devient un test : on ajoute les nouveaux fichiers dans un dossier de test, on actualise, puis on lit les contrôles avant de diffuser.
Test pratique en 20 minutes avant de choisir une formation
Prenez une copie désensibilisée ou fictive de votre processus. N’utilisez pas de données confidentielles sans autorisation.
- Listez vos sources : nombre, format, emplacement, propriétaire et fréquence.
- Rejouez un cycle en notant chaque geste manuel, sans chercher à l’améliorer.
- Marquez S pour les gestes de source, T pour les règles de traitement, O pour les gestes de sortie.
- Comptez les gestes réellement répétés, mais n’en déduisez pas encore un gain financier.
- Choisissez un seul prototype : une formule testée, un TCD contrôlé ou une requête sur deux fichiers.
- Ajoutez un cas anormal : colonne manquante, référence inconnue, date texte ou nouvelle ligne.
- Demandez à une autre personne d’expliquer la mise à jour à partir de votre fiche.
Ce test ne mesure pas une rentabilité. Il produit un brief de formation plus précis et révèle le prérequis manquant.
Exercice corrigé : quel outil apprendre en premier ?
Associez chaque besoin à un point de départ.
- Une table propre contient une ligne par facture ; il faut le total par client et trimestre.
- Chaque ligne doit recevoir un tarif à partir d’un code article, avec une alerte si le code est inconnu.
- Vingt fichiers CSV mensuels doivent être réunis, typés et contrôlés avant analyse.
- Les vingt fichiers doivent ensuite produire une synthèse par région avec filtres.
Correction :
- TCD, car la source est déjà exploitable et le besoin est une synthèse.
- Formules, car il s’agit d’une règle ligne par ligne ; tester aussi les doublons et codes absents.
- Power Query, car l’import, la combinaison et le nettoyage se répètent.
- Power Query puis TCD, car la préparation précède l’analyse.
Si vous avez choisi « Power Query » pour les quatre cas, vous avez probablement choisi une technologie avant d’avoir localisé le problème.
Cas limites à vérifier avant l’inscription
- Version et plateforme : les fonctions, connecteurs et possibilités diffèrent entre Excel Windows, Mac et Web. La page Microsoft indique que Power Query existe sur les trois plateformes, mais la disponibilité concrète des sources et fonctions doit être confirmée pour votre environnement.
- Données sensibles : préparez des fichiers fictifs, anonymisés ou expressément autorisés pour la formation.
- Source instable : si l’émetteur change fréquemment les colonnes ou les règles, il faut un contrat de source et un traitement explicite des écarts.
- Plusieurs tables liées : un modèle de données, Power Pivot ou Power BI peut devenir nécessaire ; ne l’ajoutez pas sans vérifier le niveau et la sortie attendue.
- Processus multi-applications : si le besoin inclut validations, courriels ou actions dans d’autres services, Power Query seul ne couvre pas l’orchestration.
- Financement : prix, éligibilité et conditions dépendent du parcours et de la situation. Ils doivent être vérifiés sur la proposition correspondante ; cet article ne garantit aucun financement.
Le brief à envoyer pour être orienté sans repartir de zéro
Pour demander un parcours, joignez une description sans données personnelles :
- votre version et plateforme Excel ;
- le nombre et le format des sources ;
- cinq gestes manuels réellement répétés ;
- la sortie attendue et son destinataire ;
- les contrôles déjà disponibles ;
- la fréquence d’actualisation ;
- la personne qui devra reprendre le fichier ;
- votre échéance et le format souhaité : à distance, à Strasbourg ou dans l’entreprise selon l’offre.
Présentez votre processus à StraFormation avec la matrice Source–Traitement–Sortie complétée. L’objectif du premier échange est de vérifier le prérequis et de choisir entre formules, TCD, Power Query ou un parcours combiné — sans promettre un résultat avant d’avoir vu le besoin réel.
Sources et parcours vérifiés
4.8/5
225+ avis Google
Qualiopi
Certifié qualité
Éligible CPF
100% finançable
5000+
Apprenants formés
