Analyste reporting Excel VBA, les questions sur les macros et Power Query

Le poste d'analyste reporting Excel VBA tient à des fichiers dont dépendent des décisions. Il importe des exports d'ERP, consolide les fichiers des filiales, calcule les indicateurs et envoie les tableaux à date fixe. Les questions d'entretien doivent vérifier que ces fichiers tiendront quand la personne sera en congé ou partie. On rencontre ce profil en contrôle de gestion, en administration des ventes, aux achats, en RH et dans les fonctions support des PME comme des grands groupes.

Le risque vient des macros enregistrées puis retouchées. Elles marchent tant que les colonnes ne bougent pas et cassent le jour où un export change. Un bon analyste écrit du code structuré, gère les erreurs, sait quand Power Query fait mieux qu'une boucle VBA et contrôle ses totaux contre la source. Il sait aussi que les macros sont bloquées par défaut dans les fichiers venus d'internet, et ce que cela change pour la diffusion.

Apportez si possible un vrai fichier de reporting, anonymisé, et faites-le commenter pendant l'entretien. La façon dont la personne cherche d'où vient un total en dit plus que ses réponses théoriques.

Les outils Excel d'un analyste reporting, de VBA à Power Pivot

VBA et l'éditeur de macros

Le langage de programmation intégré à Excel et à la suite Office. Il automatise ce qu'un utilisateur ferait à la main : ouvrir des fichiers, copier des plages, créer des onglets, exporter en PDF, envoyer des e-mails par Outlook. Le code vit dans des modules d'un fichier .xlsm ou d'un complément. VBA ne tourne pas dans Excel pour le web, seulement dans l'application de bureau.

Le modèle objet d'Excel

Les classeurs, les feuilles, les plages, les tableaux structurés et les tableaux croisés dynamiques que le code manipule. Un code solide s'appuie sur des tableaux structurés et des noms, pas sur des adresses fixes comme A1:H5000. C'est ce qui fait la différence entre une macro qui survit à un changement de format et une macro qui casse.

Power Query

L'outil d'import et de transformation intégré à Excel (Données, Obtenir et transformer). Il lit des fichiers, des dossiers entiers, des bases ou des listes SharePoint, nettoie et combine les données, puis se rafraîchit d'un clic. Les étapes sont enregistrées en langage M. Pour la consolidation de fichiers, il remplace souvent avantageusement une macro.

Power Pivot et le modèle de données

Le moteur qui relie plusieurs tables (ventes, produits, calendrier) dans Excel et calcule des mesures en DAX, le même langage que Power BI. Il dépasse la limite d'un peu plus d'un million de lignes d'une feuille. Un analyste qui le maîtrise passe sans grande difficulté à Power BI.

Formules et tableaux croisés dynamiques

RECHERCHEX, SOMME.SI.ENS, les tableaux dynamiques comme FILTRE, UNIQUE ou TRIER, et LET ou LAMBDA pour nommer des calculs. Les tableaux croisés dynamiques restent l'outil de restitution le plus courant. Beaucoup de besoins se règlent par une formule bien construite, sans macro.

Sources de données

Exports CSV ou Excel d'un ERP (SAP, Sage, Cegid…), fichiers envoyés par les filiales, bases SQL Server ou Access interrogées par ODBC, listes SharePoint. Demandez d'où viennent les chiffres du poste et comment la personne vérifie qu'un export est complet avant de calculer.

Sécurité des macros

Microsoft bloque par défaut les macros des fichiers téléchargés ou reçus par e-mail. Pour diffuser un outil, on passe par des emplacements approuvés, une signature numérique reconnue par la DSI ou un complément installé. Aucun mot de passe ne doit traîner en clair dans le code.

Office Scripts, Power Automate et Power BI

Les alternatives à surveiller. Office Scripts automatise Excel pour le web en TypeScript et s'enchaîne avec Power Automate. Power BI prend le relais quand le reporting doit être partagé, rafraîchi à heure fixe et sécurisé par utilisateur. Un bon profil sait dire quand un outil VBA mérite d'y passer.

Le poste prend des formes variées. Dans une PME, l'analyste reporting est souvent le seul à maîtriser les fichiers de pilotage, ce qui rend la documentation et la reprise par un autre très sensibles. Dans un grand groupe, il produit des reportings pour une direction financière ou commerciale, à partir d'exports d'ERP, en attendant ou en complément d'un outil BI. En ESN, les missions VBA portent souvent sur la reprise ou la migration d'outils existants vers Power Query, Power BI ou la Power Platform. La certification TOSA, courante en France, mesure un niveau sur Excel ou sur VBA par un test adaptatif. Elle ne dit pas si la personne écrit un code qu'un collègue pourra reprendre.

Le niveau Excel et VBA à vérifier en entretien

• VBA structuré et gestion d'erreurs
• Performance et automatisation des macros
• Power Query et langage M
• Power Pivot, DAX et tableaux croisés
• Formules avancées et tableaux dynamiques
• Fiabilisation, contrôle et sécurité des fichiers

Un fichier de reporting à reprendre : cinq questions pour l'analyste

Chaque question vient avec ce qu'une bonne réponse contient et le signal qui doit vous alerter. Pas besoin d'être technique pour les poser : écoutez si le candidat raconte du vécu.

1. Question 1 · confirmé

   Une macro met vingt minutes à traiter 80 000 lignes. Que regardez-vous dans le code ?

   Une bonne réponseLes Select et Activate, et les boucles qui lisent et écrivent cellule par cellule. La bonne méthode charge la plage dans un tableau en mémoire, fait le calcul, puis réécrit le résultat en une fois. Il coupe l'actualisation de l'écran et passe le calcul en manuel pendant le traitement, en les rétablissant à la fin. Pour les recherches, un Dictionary remplace les boucles imbriquées. Parfois, un filtre ou une requête Power Query fait le travail sans boucle.

   Signal d'alerte« J'ajoute ScreenUpdating = False » comme seule réponse, ou « il faut un PC plus puissant ». Ne pas voir le problème des Select.

2. Question 2 · confirmé

   Chaque mois, douze filiales envoient un fichier aux colonnes presque identiques. Comment les consolidez-vous ?

   Une bonne réponsePower Query avec une source « à partir d'un dossier » : une même transformation appliquée à chaque fichier, les colonnes renommées et typées par leur nom, une colonne qui garde le nom du fichier d'origine, puis l'ajout des tables. Chaque mois, on dépose les fichiers et on actualise. Il ajoute un contrôle du nombre de lignes et des totaux par filiale pour repérer un fichier manquant ou incomplet. Sans Power Query, une boucle VBA sur le dossier avec les mêmes contrôles.

   Signal d'alerteDes copier-coller manuels, ou une macro enregistrée avec des plages fixes. Aucun moyen de savoir quelle filiale n'a pas envoyé son fichier.

3. Question 3 · confirmé

   Comment gérez-vous les erreurs dans une macro qui tourne sans vous, la nuit ou chez un collègue ?

   Une bonne réponseUn On Error GoTo vers un bloc de traitement dans chaque procédure importante, qui enregistre le numéro et le message d'erreur avec l'étape en cours dans un onglet ou un fichier journal. Un bloc de sortie qui rétablit les réglages d'Excel et ferme les fichiers ouverts. Des vérifications en amont : le fichier existe, l'onglet aussi, les en-têtes attendus sont présents. Un message compréhensible pour l'utilisateur.

   Signal d'alerteOn Error Resume Next en tête du module, qui masque toutes les erreurs et laisse produire des chiffres faux en silence.

4. Question 4 · junior à confirmé

   Quand passez-vous à Power Pivot et à des mesures DAX plutôt qu'à une feuille remplie de RECHERCHEX ?

   Une bonne réponseQuand les données dépassent ce qu'une feuille supporte confortablement, ou quand plusieurs tables doivent être reliées (ventes, produits, clients, calendrier). Il construit un modèle en étoile, écrit des mesures (SUM, CALCULATE, DIVIDE) réutilisables dans plusieurs tableaux croisés et ajoute une table calendrier pour les cumuls. Il cite le lien avec Power BI, qui utilise le même moteur.

   Signal d'alerteUne seule feuille à plat avec des RECHERCHEV sur des centaines de milliers de lignes, ou des colonnes calculées partout à la place de mesures. Ne pas savoir ce qu'est le modèle de données.

5. Question 5 · confirmé

   Vous reprenez un fichier de reporting construit par quelqu'un qui a quitté l'entreprise. Comment vérifiez-vous qu'il calcule juste avant d'y toucher ?

   Une bonne réponseUn inventaire : onglets masqués, noms définis, liaisons vers d'autres classeurs, requêtes Power Query, macros et ce qui les déclenche. Il repère les valeurs saisies en dur dans les formules. Il reproduit le dernier reporting publié à partir des mêmes sources et compare. Il garde une copie datée avant toute modification et documente ce qu'il découvre.

   Signal d'alerteModifier tout de suite parce que « ça marche depuis des années », ou tout refaire de zéro sans comparer avec l'existant.

Questions de recruteurs sur les profils Excel et VBA

Pour structurer vos autres entretiens, consultez aussi nos pages sur les questions d'entretien de recrutement, la méthode de l'entretien structuré et la grille d'évaluation d'entretien.