18 · Module 6 — Data et Bureautique

Excel avancé avec Claude

VBA, Power Query, modèles multi-feuilles.
Le terrain où Claude amortit son coût en quelques jours.

9 minutesVBA · Power Query · ModèlesPour analystes et contrôleurs

§01 · De la L17 à la L18

Passer de l'usage à la maîtrise

La leçon 17 a couvert l'usage quotidien : formules, mises en forme conditionnelles, analyses de plages. Cette leçon-ci s'adresse aux utilisateurs qui automatisent : macros VBA, transformations Power Query, modèles multi-feuilles complexes. Claude ouvre l'accès à ces fonctions pour les profils qui les évitaient jusqu'ici.

§02 · Premier usage avancé — VBA

Automatiser sans coder en VBA

VBA (Visual Basic for Applications) est le langage de macros d'Excel. Puissant mais avec une syntaxe particulière que beaucoup d'utilisateurs évitent. Claude génère du VBA correct à partir d'une description fonctionnelle.

Vous : Génère une macro VBA qui :
  - parcourt toutes les feuilles du classeur
  - pour chaque feuille, prend la dernière ligne
  - copie le contenu dans une feuille "Récap"
    avec le nom de la feuille source en colonne A

Claude :
Sub Recap()
  Dim ws As Worksheet
  Dim recap As Worksheet
  Set recap = Sheets("Récap")
  Dim rowIdx As Long: rowIdx = 2
  
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Récap" Then
      Dim lastRow As Long
      lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
      recap.Cells(rowIdx, 1).Value = ws.Name
      ws.Rows(lastRow).Copy recap.Cells(rowIdx, 2)
      rowIdx = rowIdx + 1
    End If
  Next ws
End Sub

Sécurité VBA

Une macro VBA peut faire à peu près tout sur votre poste. Toujours lire le code avant de l'exécuter, même quand il vient de Claude. Sur les classeurs partagés, Excel demande confirmation pour activer les macros — ne pas l'accepter aveuglément.

§03 · Deuxième usage — Power Query

Transformer la donnée brute

Power Query est l'outil d'ETL intégré à Excel. Il permet de connecter des sources, transformer les colonnes, fusionner, nettoyer — sans macro. Claude génère du code M (le langage de Power Query) à partir d'une description.

Cas 1

Nettoyer un export brut

« Mon export client contient des espaces en trop, des dates en formats variés, des montants avec virgule décimale ou point selon les lignes. Donne-moi une requête Power Query qui normalise tout. »

Cas 2

Fusionner plusieurs sources

« Fusionne ces 5 fichiers Excel de ventes par région en une seule table avec une colonne Région ajoutée. »

Cas 3

Pivotage et dépivotage

« Transforme cette table mensuelle (12 colonnes par mois) en format long (une ligne par mois). » Opération laborieuse à la main, instantanée en M.

Cas 4

Rafraîchissement automatique

Une fois la requête M créée, elle se rafraîchit à la demande ou automatiquement à l'ouverture du fichier. Le travail manuel mensuel disparaît.

§04 · Troisième usage — modèles multi-feuilles

Concevoir un classeur structuré

Un classeur Excel professionnel n'est pas une accumulation de feuilles. Il a une structure : feuilles d'entrée, feuilles de calcul, feuilles de sortie. Claude vous aide à concevoir cette architecture.

1.

Décrire le besoin métier. « Je dois concevoir un classeur de suivi mensuel des missions : saisie des missions, calculs automatiques de marge par client, dashboard de synthèse. Propose-moi l'architecture des feuilles. »

2.

Claude propose la structure. Typiquement : feuille Données (saisie), feuille Tables (référentiels), feuille Calculs (formules intermédiaires), feuille Dashboard (sortie). Avec le rôle de chaque feuille.

3.

Demander la création progressive. Feuille par feuille, en commençant par les Tables, puis les Données, puis les Calculs, enfin le Dashboard. Tester à chaque étape avant de passer à la suivante.

§05 · Quatrième usage — protection et validation

Empêcher la casse

Un classeur Excel partagé entre plusieurs utilisateurs est rapidement cassé par accident : formule écrasée, colonne supprimée, format modifié. Claude propose les mécanismes de protection adaptés.

Protection 1

Verrouiller les cellules de formules

Les cellules de formules sont protégées, les cellules de saisie sont libres. Claude génère la procédure complète (sélection, déverrouillage des cellules à saisir, protection de la feuille).

Protection 2

Validation des données

Limiter les saisies aux valeurs attendues. « Sur la colonne Statut, n'autorise que les valeurs Actif, Terminé, En attente. » Liste déroulante automatique en bonus.

Protection 3

Mise en forme conditionnelle d'erreur

Signaler visuellement les saisies incohérentes. « Mets en rouge les dates de fin antérieures aux dates de début. »

Protection 4

Masquer les feuilles techniques

Les feuilles de calcul intermédiaire peuvent être très masquées (xlSheetVeryHidden). L'utilisateur ne les voit même pas dans le clic droit. Claude génère la macro.

§06 · Limites importantes

Ce que Claude fait moins bien

Macros complexes

Plus de 100 lignes

Sur les macros VBA très longues, Claude peut perdre la cohérence. Demander des sous-routines découpées plutôt qu'une seule grosse macro.

Performance VBA

Optimisation poussée

Pour les macros qui traitent des centaines de milliers de lignes, la performance fine (désactivation du recalcul automatique, ScreenUpdating, etc.) reste à valider manuellement.

Fonctions custom

UDF — User Defined Functions

Les fonctions personnalisées (CustomFunction en VBA, Lambda en Excel 365) sont générées par Claude mais nécessitent une validation poussée — c'est du code qui s'exécute dans toutes les cellules concernées.

Fin · Leçon 18 acquise

Cap sur la leçon 19

Excel est couvert. La leçon suivante passe sur Word — transformer des documents existants, préserver les templates, refondre une structure.

Exercice — appropriation

Sur un classeur Excel réel : (1) faites générer par Claude une macro VBA qui automatise une tâche que vous faites manuellement chaque mois, (2) faites générer une requête Power Query pour nettoyer un export brut, (3) ajoutez une validation de données sur une colonne. Notez le temps gagné par rapport à la procédure manuelle.

Quiz · Validation des acquis

Quiz · Excel avancé avec Claude

8 questions · une seule bonne réponse par question · vous pouvez recommencer autant de fois que nécessaire.

1 / 8

Question à choix multiple