Table des matières
Excel a une capacité puissante, mais en même temps très rarement utilisée, de créer des séquences automatiques d'actions à l'aide de macros. Une macro est une solution idéale si vous traitez le même type de tâche qui se répète plusieurs fois. Par exemple, le traitement de données ou la mise en forme de documents selon un modèle standardisé. Dans ce cas, vous n'avez pas besoin de connaître les langages de programmation.
Êtes-vous déjà curieux de savoir ce qu'est une macro et comment elle fonctionne ? Alors allez-y avec audace - ensuite, nous ferons étape par étape tout le processus de création d'une macro avec vous.
Qu'est-ce que Macro ?
Une macro dans Microsoft Office (oui, cette fonctionnalité fonctionne de la même manière dans de nombreuses applications du package Microsoft Office) est un code de programme dans un langage de programmation Visual Basic pour applications (VBA) stocké à l'intérieur du document. Pour clarifier les choses, un document Microsoft Office peut être comparé à une page HTML, puis une macro est un analogue de Javascript. Ce que Javascript peut faire avec des données HTML dans une page Web est très similaire à ce qu'une macro peut faire avec des données dans un document Microsoft Office.
Les macros peuvent faire à peu près tout ce que vous voulez dans un document. En voici quelques-uns (une très petite partie):
- Appliquer des styles et une mise en forme.
- Effectuez diverses opérations avec des données numériques et textuelles.
- Utiliser des sources de données externes (fichiers de base de données, documents texte, etc.)
- Créez un nouveau document.
- Faites tout ce qui précède dans n'importe quelle combinaison.
Créer une macro – un exemple pratique
Par exemple, prenons le fichier le plus courant CSV. Il s'agit d'un simple tableau 10 × 20 rempli de nombres de 0 à 100 avec des en-têtes pour les colonnes et les lignes. Notre tâche consiste à transformer cet ensemble de données en un tableau au format présentable et à générer des totaux dans chaque ligne.
Comme déjà mentionné, une macro est un code écrit dans le langage de programmation VBA. Mais dans Excel, vous pouvez créer un programme sans écrire une ligne de code, ce que nous allons faire tout de suite.
Pour créer une macro, ouvrez Voir (Type) > Macros (Macro) > Enregistrer une macro (Enregistrement macro…)
Donnez un nom à votre macro (sans espace) et cliquez sur OK.
A partir de ce moment, TOUTES vos actions avec le document sont enregistrées : modifications de cellules, défilement du tableau, voire redimensionnement de la fenêtre.
Excel signale que le mode d'enregistrement macro est activé à deux endroits. Tout d'abord, au menu Macros (Macros) – au lieu d'une chaîne Enregistrer une macro (Enregistrement d'une macro…) la ligne est apparue Arrêter l'enregistrement (Arrête d'enregistrer).
Deuxièmement, dans le coin inférieur gauche de la fenêtre Excel. Icône Arrêter (petit carré) indique que le mode d'enregistrement macro est activé. Cliquer dessus arrêtera l'enregistrement. Inversement, lorsque le mode d'enregistrement n'est pas activé, une icône permet d'activer l'enregistrement de macros à cet emplacement. Cliquer dessus donnera le même résultat que d'activer l'enregistrement via le menu.
Maintenant que le mode d'enregistrement de macro est activé, passons à notre tâche. Tout d'abord, ajoutons des en-têtes pour les données récapitulatives.
Ensuite, saisissez les formules dans les cellules en fonction des noms des rubriques (des variantes des formules pour l'anglais et les versions d'Excel sont données, les adresses des cellules sont toujours des lettres et des chiffres latins) :
- =SOMME(B2:K2) or =SOMME(B2:K2)
- =MOYENNE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MÉDIANE(B2:K2) or =MÉDIANE(B2:K2)
Sélectionnez maintenant les cellules avec des formules et copiez-les dans toutes les lignes de notre tableau en faisant glisser la poignée de remplissage automatique.
Une fois cette étape terminée, chaque ligne doit avoir les totaux correspondants.
Ensuite, nous résumerons les résultats pour l'ensemble du tableau, pour cela nous effectuons quelques opérations mathématiques supplémentaires :
Respectivement:
- =SOMME(L2:L21) or =SOMME(L2:L21)
- =MOYENNE(B2:K21) or =СРЗНАЧ(B2:K21) – pour calculer cette valeur, il faut prendre exactement les données initiales du tableau. Si vous prenez la moyenne des moyennes des lignes individuelles, le résultat sera différent.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MÉDIANE(B2:K21) or =MÉDIANE(B2:K21) – nous envisageons d'utiliser les données initiales du tableau, pour la raison indiquée ci-dessus.
Maintenant que nous en avons fini avec les calculs, faisons un peu de formatage. Tout d'abord, définissons le même format d'affichage des données pour toutes les cellules. Sélectionnez toutes les cellules de la feuille, pour cela, utilisez le raccourci clavier Ctrl + Aou cliquez sur l'icone Sélectionner tout, qui se trouve à l'intersection des en-têtes de ligne et de colonne. Puis clique Style de virgule Onglet (Format délimité) Accueil (Maison).
Modifiez ensuite l'apparence des en-têtes de colonne et de ligne :
- Style de police gras.
- Alignement central.
- Remplissage de couleur.
Et enfin, définissons le format des totaux.
Voici à quoi cela devrait ressembler à la fin:
Si tout vous convient, arrêtez d'enregistrer la macro.
Toutes nos félicitations! Vous venez d'enregistrer vous-même votre première macro dans Excel.
Pour utiliser la macro générée, nous devons enregistrer le document Excel dans un format prenant en charge les macros. Tout d'abord, nous devons supprimer toutes les données de la table que nous avons créée, c'est-à-dire en faire un modèle vide. Le fait est qu'à l'avenir, en travaillant avec ce modèle, nous y importerons les données les plus récentes et les plus pertinentes.
Pour effacer toutes les cellules des données, faites un clic droit sur l'icône Sélectionner tout, qui se trouve à l'intersection des en-têtes de ligne et de colonne, et dans le menu contextuel, sélectionnez Supprimer (Effacer).
Maintenant, notre feuille est complètement effacée de toutes les données, tandis que la macro reste enregistrée. Nous devons enregistrer le classeur en tant que modèle Excel prenant en charge les macros et portant l'extension XLMC.
Un point important ! Si vous enregistrez le fichier avec l'extension XLTX, la macro ne fonctionnera pas dedans. Au fait, vous pouvez enregistrer le classeur en tant que modèle Excel 97-2003, au format XLT, il prend également en charge les macros.
Lorsque le modèle est enregistré, vous pouvez fermer Excel en toute sécurité.
Exécuter une macro dans Excel
Avant de révéler toutes les possibilités de la macro que vous avez créée, je pense qu'il convient de prêter attention à quelques points importants concernant les macros en général :
- Les macros peuvent être nuisibles.
- Relisez le paragraphe précédent.
Le code VBA est très puissant. En particulier, il peut effectuer des opérations sur des fichiers en dehors du document courant. Par exemple, une macro peut supprimer ou modifier n'importe quel fichier dans un dossier Mes documents. Pour cette raison, n'exécutez et n'autorisez que les macros provenant de sources de confiance.
Pour exécuter notre macro de formatage des données, ouvrez le fichier de modèle que nous avons créé dans la première partie de ce didacticiel. Si vous avez des paramètres de sécurité standard, lorsque vous ouvrez un fichier, un avertissement apparaîtra au-dessus du tableau indiquant que les macros sont désactivées, et un bouton pour les activer. Puisque nous avons fait le modèle nous-mêmes et que nous nous faisons confiance, nous appuyons sur le bouton Autoriser le contenu (Inclure le contenu).
L'étape suivante consiste à importer le dernier ensemble de données mis à jour à partir du fichier CSV (sur la base d'un tel fichier, nous avons créé notre macro).
Lorsque vous importez des données à partir d'un fichier CSV, Excel peut vous demander de configurer certains paramètres afin de transférer correctement les données vers le tableau.
Lorsque l'importation est terminée, allez dans le menu Macros Onglet (Macros) Voir (Afficher) et sélectionnez une commande Afficher les macros (Macro).
Dans la boîte de dialogue qui s'ouvre, nous verrons une ligne avec le nom de notre macro FormatDonnées. Sélectionnez-le et cliquez Courir (Exécuter).
Lorsque la macro commence à s'exécuter, vous verrez le curseur du tableau passer d'une cellule à l'autre. Après quelques secondes, les mêmes opérations seront effectuées avec les données que lors de l'enregistrement d'une macro. Lorsque tout est prêt, le tableau doit ressembler à l'original que nous avons formaté à la main, mais avec des données différentes dans les cellules.
Regardons sous le capot : Comment fonctionne une macro ?
Comme mentionné plus d'une fois, une macro est un code de programme dans un langage de programmation. Visual Basic pour applications (VBA). Lorsque vous activez le mode d'enregistrement de macros, Excel enregistre en fait chaque action que vous effectuez sous la forme d'instructions VBA. En termes simples, Excel écrit le code pour vous.
Pour voir ce code de programme, vous devez dans le menu Macros Onglet (Macros) Voir (voir) cliquer Afficher les macros (Macros) et dans la boîte de dialogue qui s'ouvre, cliquez sur Modifier (Changement).
La fenêtre s'ouvre. Visual Basic pour applications, dans lequel nous verrons le code de programme de la macro que nous avons enregistrée. Oui, vous avez bien compris, ici vous pouvez modifier ce code et même créer une nouvelle macro. Les actions que nous avons effectuées avec le tableau dans cette leçon peuvent être enregistrées à l'aide de l'enregistrement automatique de macros dans Excel. Mais les macros plus complexes, avec une séquence et une logique d'action finement réglées, nécessitent une programmation manuelle.
Ajoutons une étape de plus à notre tâche…
Imaginez que notre fichier de données d'origine données.csv est créé automatiquement par un processus et est toujours stocké sur disque au même endroit. Par exemple, C:Datadata.csv – chemin d'accès au fichier avec les données mises à jour. Le processus d'ouverture de ce fichier et d'importation de données à partir de celui-ci peut également être enregistré dans une macro :
- Ouvrez le fichier de modèle où nous avons enregistré la macro - FormatDonnées.
- Créez une nouvelle macro nommée Charger les données.
- Pendant l'enregistrement d'une macro Charger les données importer des données à partir d'un fichier données.csv – comme nous l'avons fait dans la partie précédente de la leçon.
- Une fois l'importation terminée, arrêtez l'enregistrement de la macro.
- Supprimer toutes les données des cellules.
- Enregistrez le fichier en tant que modèle Excel prenant en charge les macros (extension XLTM).
Ainsi, en exécutant ce modèle, vous avez accès à deux macros - l'une charge les données, l'autre les formate.
Si vous souhaitez vous lancer dans la programmation, vous pouvez combiner les actions de ces deux macros en une seule - simplement en copiant le code de Charger les données au début du code FormatDonnées.