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 de macros. Les macros constituent un moyen idéal de gagner du temps sur des tâches répétitives et prévisibles, ainsi que de normaliser les formats de document - à de nombreuses reprises 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 être 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, imaginons un document au format HTML et une macro au format Javascript. De la même manière que Javascript peut manipuler du 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. En tant que (très) courte liste de fonctions que vous pouvez faire avec une macro:

Appliquer le style et la mise en forme.
Manipuler des données et du texte.
Communiquer avec les 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 ce qui précède.

Créer une macro: une explication par exemple

Nous commençons par votre fichier CSV de variété de jardin. Rien de spécial ici, juste un ensemble 10 × 20 de nombres compris entre 0 et 100 avec un en-tête de ligne et de colonne. Notre objectif est de produire une fiche technique bien formatée et présentable comprenant les totaux récapitulatifs pour chaque ligne..

Comme nous l'avons indiqué ci-dessus, une macro est un code VBA, mais l'une des bonnes choses à propos d'Excel est de pouvoir les créer / enregistrer avec un codage nul, comme nous le ferons ici..

Pour créer une macro, sélectionnez Affichage> Macros> Enregistrer une macro..

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

Une fois que cela est fait, tout de vos actions sont enregistrées - chaque changement de cellule, action de défilement, redimensionnement de la fenêtre, vous le nommez.

Il y a quelques endroits qui indiquent qu'Excel est en mode enregistrement. La première consiste à afficher le menu Macro et à noter que l'option Arrêter l'enregistrement a remplacé l'option d'enregistrement Macro..

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

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

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

= SOMME (B2: K2)
= MOYENNE (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIANE (B2: K2)

Maintenant, mettez en surbrillance 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 que cela est fait, chaque ligne devrait afficher leurs résumés respectifs.

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

Respectivement:

= SOMME (L2: L21)
= MOYENNE (B2: K21) *Ceci doit être calculé pour toutes les donné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)
= MEDIANE (B2: K21) * Calculé dans toutes les données pour la même raison que ci-dessus.

Maintenant que les calculs sont terminés, nous allons appliquer le style et la mise en forme. Commencez par appliquer une mise en forme générale des nombres à toutes les cellules en sélectionnant Tout sélectionner (Ctrl + A ou cliquez sur la cellule située entre les en-têtes de ligne et de colonne), puis 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:

Audacieux.
Centré.
Couleur de fond.

Et enfin, appliquez un peu de style aux totaux.

Lorsque tout est terminé, voici à quoi ressemble notre fiche technique:

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

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

Pour utiliser notre macro nouvellement enregistrée, nous devons enregistrer notre classeur Excel dans un format de fichier compatible avec les macros. Cependant, avant cela, nous devons d'abord effacer toutes les données existantes afin qu'elles ne soient pas intégrées à notre modèle (l'idée étant chaque fois que nous utilisons ce modèle, nous allons importer les données les plus récentes)..

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

Maintenant que les données sont effacées (mais que les macros sont toujours incluses dans le fichier Excel), nous voulons enregistrer le fichier en tant que fichier de modèle activé par macro (XLTM). Il est important de noter que si vous enregistrez ceci en tant que fichier de modèle standard (XLTX), les macros ne pas être capable de s'en échapper. 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, continuez et fermez Excel.

Utiliser une macro Excel

Avant de couvrir la façon dont nous pouvons appliquer cette macro nouvellement enregistrée, il est important de couvrir quelques points concernant 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 de votre dossier Mes documents. En tant que tel, il est important de vous assurer que seulement exécuter des macros à partir de sources fiables.

Pour utiliser notre macro de format de données, ouvrez le fichier de modèle Excel créé ci-dessus. Dans ce cas, en supposant que les paramètres de sécurité standard soient activés, un avertissement s'affiche en haut du classeur pour indiquer que les macros sont désactivées. Parce que nous faisons confiance à une macro créée par nos soins, cliquez sur le bouton "Activer le contenu"..

Ensuite, nous allons importer le dernier ensemble de données à partir d'un fichier CSV (il s'agit de la source utilisée par la feuille de travail pour créer notre macro)..

Pour terminer l’importation du fichier CSV, vous devrez peut-être définir quelques options afin que Excel puisse l’interpréter correctement (par exemple, un délimiteur, les en-têtes présents, etc.)..

Une fois nos données importées, allez simplement dans le menu Macros (sous l’onglet View) et sélectionnez View 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 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é à plusieurs reprises, une macro est gérée par le code Visual Basic pour Applications (VBA). Lorsque vous "enregistrez" une macro, Excel traduit en réalité tout ce que vous faites dans ses instructions VBA respectives. Pour le dire simplement - vous n'avez pas à écrire de code car Excel écrit le code pour vous..

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

La fenêtre qui s'ouvre affiche le code source 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. Bien que l'action d'enregistrement utilisée dans cet article réponde probablement à la plupart des besoins, des actions plus personnalisées ou conditionnelles nécessiteraient que vous modifiiez le code source..

Prenons notre exemple un pas plus loin…

En théorie, supposons que notre fichier de données source, data.csv, est généré par un processus automatisé qui enregistre toujours le fichier au même emplacement (par exemple, C: \ Data \ data.csv correspond toujours aux données les plus récentes). Le processus d’ouverture et d’importation de ce fichier peut également être facilement transformé en macro:

Ouvrez le fichier de modèle Excel contenant notre macro “FormatData”.
Enregistrez une nouvelle macro nommée “LoadData”.
Avec l’enregistrement de macro, importez le fichier de données comme vous le feriez normalement..
Une fois les données importées, arrêtez d’enregistrer la macro.
Supprimer toutes les données de la cellule (tout sélectionner, puis supprimer).
Enregistrez le modèle mis à jour (n'oubliez pas d'utiliser un format de modèle activé par macro).

Une fois que cela est fait, chaque fois que le modèle est ouvert, il y aura deux macros - une qui charge nos données et l'autre qui les formate.

Si vous voulez vraiment vous salir les mains avec un peu d’édition de code, vous pouvez 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 à partir de “FormatData”..

Téléchargez ce modèle

Pour votre commodité, nous avons inclus à la fois le modèle Excel produit dans cet article et un exemple de fichier de données à utiliser..

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