Apprenez à utiliser des macros Excel pour automatiser des tâches fastidieuses

L'une des fonctions les plus puissantes, mais rarement utilisées d'Excel est la possibilité de créer très facilement des tâches automatisées et une logique personnalisée au sein des macros. Les macros fournissent un moyen idéal de gagner du temps sur les tâches prévisibles et répétitives, ainsi que de standardiser les formats de documents, sans avoir à écrire une seule ligne de code.

Si vous êtes curieux de savoir ce que sont les macros ou comment les créer, pas de problème - nous vous guiderons tout au long du processus.

Remarque: le même processus devrait fonctionner dans la plupart des versions de Microsoft Office. Les captures d'écran peuvent sembler légèrement différentes.

Qu'est-ce qu'une macro?

Une macro Microsoft Office( car cette fonctionnalité s'applique à plusieurs applications MS Office) est simplement du code Visual Basic pour Applications( VBA) enregistré dans un document. Pour une analogie comparable, pensez à un document en HTML et une macro en Javascript. De la même manière que Javascript peut manipuler le HTML sur une page Web, une macro peut manipuler un document.

Les macros sont incroyablement puissantes et peuvent faire à peu près tout ce que votre imagination peut imaginer. Comme une( très) courte liste de fonctions que vous pouvez faire avec une macro:

• Appliquer le style et le formatage.
• Manipuler des données et du texte.
• Communiquer avec des sources de données( base de données, fichiers texte, etc.).
• Créer des documents entièrement nouveaux.
• Toute combinaison, dans n'importe quel ordre, de l'un des éléments ci-dessus.

Création d'une macro: explication par l'exemple

Nous commençons par le fichier CSV de votre jardin. Rien de spécial ici, juste un ensemble de nombres de 10 × 20 entre 0 et 100 avec un en-tête de ligne et de colonne. Notre objectif est de produire une feuille de données bien formatée et présentable qui comprend des totaux récapitulatifs pour chaque rangée.

Comme nous l'avons dit plus haut, une macro est un code VBA, mais l'une des choses intéressantes à propos d'Excel est que vous pouvez les créer / les enregistrer sans codage - comme nous le ferons ici.

Pour créer une macro, allez dans View & gt;Macros & gt;Enregistrer la macro.

Attribuez un nom à la macro( sans espace) et cliquez sur OK.

Une fois cela fait, tous les de vos actions sont enregistrées - chaque changement de cellule, action de défilement, redimensionnement de la fenêtre, vous l'appelez.

Il y a quelques endroits qui indiquent qu'Excel est en mode d'enregistrement. L'un d'entre eux consiste à afficher le menu Macro et à noter que l'option Arrêter l'enregistrement a remplacé l'option pour Enregistrer la macro.

L'autre est dans le coin en bas à droite. L'icône 'stop' indique qu'il est en mode macro et une pression sur ce bouton arrête l'enregistrement( de même, lorsqu'il n'est pas en mode enregistrement, cette icône sera le bouton Enregistrer la macro, que vous pouvez utiliser au lieu du menu Macros).

Maintenant que nous enregistrons notre macro, appliquons nos calculs récapitulatifs. D'abord ajouter les en-têtes.

Ensuite, appliquez les formules appropriées( respectivement):

• = SUM( B2: K2)
• = MOYENNE( B2: K2)
• = MIN( B2: K2)
• = MAX( B2: K2)
• = MEDIAN( B2: K2

Maintenant, mettez en évidence toutes les cellules de calcul et faites glisser la longueur de toutes nos lignes de données pour appliquer les calculs à chaque ligne.

Une fois ceci fait, chaque ligne devrait afficher leurs résumés respectifs.

Maintenant, nous voulons obtenir les données récapitulatives pour la feuille entière, donc nous appliquons quelques autres calculs:

Respectivement:

• = SOMME( L2: L21)
• = MOYENNE( B2: K21) * Cela doit être calculé à travers tousdonnées car la moyenne des moyennes des lignes ne correspond pas nécessairement à la moyenne de toutes les valeurs.
• = MIN( N2: N21)
• = MAX( O2: O21)
• = MEDIAN( B2: K21) * Calculé pour toutes les données pour la même raison que ci-dessus.

Maintenant que les calculs sont terminés, nous appliquerons le style et le formatage. Tout d'abord, appliquez le formatage général des nombres dans toutes les cellules en sélectionnant Tout( Ctrl + A ou cliquez sur la cellule située entre les en-têtes de ligne et de colonne) et sélectionnez l'icône "Style de virgule" dans le menu Accueil.

Ensuite, appliquez une mise en forme visuelle aux en-têtes de ligne et de colonne:

• Gras.
• Centré.
• Couleur de remplissage d'arrière-plan.

Et enfin, appliquez un peu de style aux totaux.

Quand tout est fini, voici à quoi ressemble notre feuille de données:

Puisque nous sommes satisfaits des résultats, arrêtez l'enregistrement de la macro.

Félicitations - vous venez de créer une macro Excel.

Afin d'utiliser notre nouvelle macro enregistrée, nous devons enregistrer notre classeur Excel dans un format de fichier compatible avec les macros. Cependant, avant de faire cela, nous devons d'abord effacer toutes les données existantes pour qu'elles ne soient pas intégrées dans notre modèle( l'idée étant que chaque fois que nous utiliserons ce modèle, nous importerons les données les plus à jour).

Pour ce faire, sélectionnez toutes les cellules et supprimez-les.

Maintenant que les données sont effacées( mais les macros sont toujours incluses dans le fichier Excel), nous voulons enregistrer le fichier en tant que fichier XLTM( macro enabled template).Il est important de noter que si vous enregistrez ce fichier en tant que modèle standard( XLTX), les macros et ne pourront pas être exécutées. Vous pouvez également enregistrer le fichier en tant que fichier de modèle hérité( XLT), ce qui permettra l'exécution de macros.

Une fois que vous avez enregistré le fichier en tant que modèle, allez-y et fermez Excel.

Utilisation d'une macro Excel

Avant de décrire comment appliquer cette nouvelle macro enregistrée, il est important de couvrir quelques points sur les macros en général: Les macros

• peuvent être malveillantes.
• Voir le point ci-dessus.

Le code VBA est en fait assez puissant et peut manipuler des fichiers en dehors de la portée du document actuel. Par exemple, une macro peut modifier ou supprimer des fichiers aléatoires dans votre dossier Mes documents. En tant que tel, il est important de s'assurer que vous seulement exécuter des macros provenant de sources fiables.

Pour utiliser notre macro de format de données, ouvrez le fichier Excel Template créé ci-dessus. Lorsque vous faites cela, en supposant que les paramètres de sécurité standard sont activés, vous verrez un avertissement en haut du classeur indiquant que les macros sont désactivées. Parce que nous faisons confiance à une macro créée par nous-mêmes, cliquez sur le bouton "Activer le contenu".

Ensuite, nous allons importer le dernier ensemble de données à partir d'un CSV( c'est la source de la feuille de calcul utilisée pour créer notre macro).

Pour terminer l'importation du fichier CSV, vous devrez peut-être définir quelques options afin qu'Excel l'interprète correctement( par exemple, délimiteur, en-têtes présents, etc.).

Une fois que nos données sont importées, allez simplement dans le menu Macros( sous l'onglet Affichage) et sélectionnez Afficher les macros.

Dans la boîte de dialogue résultante, nous voyons la macro "FormatData" que nous avons enregistrée ci-dessus. Sélectionnez-le et cliquez sur Exécuter.

Une fois en cours d'exécution, vous pouvez voir le curseur sauter pendant quelques instants, mais comme il le fait, vous verrez les données manipulées exactement comme nous l'avons enregistré.Quand tout est dit et fait, il devrait ressembler à notre original - sauf avec des données différentes.

Regarder sous le capot: ce qui fait fonctionner une macro

Comme nous l'avons mentionné à quelques reprises, une macro est pilotée par le code Visual Basic pour Applications( VBA).Lorsque vous "enregistrez" une macro, Excel traduit réellement tout ce que vous faites dans ses instructions VBA respectives. Pour le dire simplement - vous n'avez pas à écrire de code parce qu'Excel écrit le code pour vous.

Pour afficher le code qui fait fonctionner notre macro, dans la boîte de dialogue Macros, cliquez sur le bouton Modifier.

La fenêtre qui s'ouvre affiche le code source qui a été enregistré à partir de nos actions lors de la création de la macro. Bien sûr, vous pouvez éditer ce code ou même créer de nouvelles macros entièrement à l'intérieur de la fenêtre de code. Alors que l'action d'enregistrement utilisée dans cet article répondra probablement à la plupart des besoins, des actions plus personnalisées ou des actions conditionnelles vous demanderont de modifier le code source.

Prenons notre exemple un peu plus loin. ..

Hypothétiquement, supposons que notre fichier de données source, data.csv, soit produit par un processus automatisé qui sauvegarde toujours le fichier au même endroit( par exemple C: \ Data \ data.csv est toujours le plusdonnées récentes).Le processus d'ouverture et d'importation de ce fichier peut également être facilement transformé en une macro:

1. Ouvrez le fichier Excel Template contenant notre macro "FormatData".
2. Enregistrer une nouvelle macro nommée "LoadData".
3. Avec l'enregistrement de macro, importez le fichier de données comme vous le feriez normalement.
4. Une fois les données importées, arrêtez l'enregistrement de la macro.
5. Supprimez toutes les données de la cellule( sélectionnez tout puis supprimez).
6. Enregistrez le modèle mis à jour( n'oubliez pas d'utiliser un format de gabarit activé par macro).

Une fois cela fait, à chaque ouverture du template il y aura deux macros - une qui charge nos données et l'autre qui les formate.

Si vous vouliez vraiment vous salir les mains avec un peu d'édition de code, vous pourriez facilement combiner ces actions en une seule macro en copiant le code produit à partir de "LoadData" et en l'insérant au début du code depuis "FormatData".

Téléchargez ce modèle

Pour votre commodité, nous avons inclus à la fois le modèle Excel produit dans cet article ainsi qu'un exemple de fichier de données pour vous permettre de jouer avec.

Télécharger le modèle de macro Excel de How-To Geek