Excel sur Mac n'a pas toujours été aussi puissant que sur Windows. Les macros ne fonctionneraient vraiment pas si elles n'étaient pas créées exclusivement pour le Mac.
À partir de 2013, Microsoft a ramené les macros. Il existe deux types de macros :celles que vous pouvez créer en enregistrant rapidement vos actions et celles qui utilisent VBA pour concevoir des automatisations plus avancées. Avec Office 2016, Excel utilise la même base de code sur toutes les plateformes. Ce changement facilitera le fonctionnement des macros sur toutes les plates-formes.
Voyons donc comment cela fonctionne actuellement sur macOS.
L'utilisation de macros dans Excel sur votre Mac peut ne pas être activée par défaut. Ce paramètre est dû au fait que les macros peuvent être un vecteur de malware possible. Le moyen le plus simple de savoir est de voir si vous avez le développeur onglet disponible sur le ruban dans Excel. Si vous ne le voyez pas, il est simple à activer.
Cliquez sur Excel dans la barre de menus, puis sélectionnez Préférences dans le menu déroulant. Dans le menu, cliquez sur Ruban &Barre d'outils . Dans la liste de droite, Développeur devrait être en bas, cochez la case. Enfin, cliquez sur Enregistrer et vous devriez voir l'onglet Développeur s'afficher à la fin du ruban.
Après avoir créé chaque classeur avec des macros, enregistrez-le dans un nouveau format .xlsm pour utiliser les macros après avoir rouvert le fichier. Si vous oubliez, Excel vous rappellera chaque fois que vous essayez d'enregistrer. Vous devrez également activer les macros chaque fois que vous ouvrirez le fichier.
Bien que vous puissiez coder des macros, cela peut ne pas convenir à tout le monde. Si vous n'êtes pas prêt à commencer à travailler avec VBA, Excel vous permet d'enregistrer les étapes de votre macro dans une feuille existante. Cliquez sur l'onglet Développeur pour voir vos options.
Vous recherchez la troisième option dans le ruban, Enregistrer une macro . Cliquez dessus et une boîte de dialogue apparaît vous permettant de nommer votre macro et de définir un raccourci clavier. Vous pouvez étendre votre macro au Classeur actuel , un nouveau classeur , ou dans votre Classeur de macros personnelles . Le classeur de macros personnelles se trouve dans votre profil utilisateur et vous permet d'utiliser vos macros entre vos fichiers.
Une fois vos actions enregistrées, elles sont disponibles sur ce même onglet. Cliquer sur les macros fera apparaître les macros enregistrées dans votre classeur. Cliquez sur le nom de votre macro et cliquez sur Exécuter pour exécuter vos actions enregistrées.
Pour un exemple de macro, vous allez parcourir une feuille de vente quotidienne, avec les ventes ventilées par totaux horaires. Votre macro va ajouter un total des ventes quotidiennes, puis ajouter une moyenne dans la dernière colonne de chaque période horaire. Si vous travaillez dans le commerce de détail ou dans un autre poste de vente, il s'agit d'une feuille utile pour suivre les revenus.
Nous devons mettre en place la première feuille. L'utilisation de ce premier blanc comme modèle à copier dans un nouvel onglet chaque jour pourrait vous faire gagner du temps. Dans la première colonne/ligne, mettez Heure/Date. En haut, ajoutez du lundi au vendredi.
Ensuite, dans la première colonne, décomposez les totaux horaires de 8 à 5. J'ai utilisé l'heure de 24 heures, mais vous pouvez utiliser la notation AM/PM si vous préférez. Votre feuille doit correspondre à la capture d'écran ci-dessus.
Ajoutez un nouvel onglet et copiez-y votre modèle. Remplissez ensuite vos données de vente pour la journée. (Si vous n'avez pas de données pour remplir cette feuille, vous pouvez saisir =RandBetween(10,1000) dans toutes les cellules pour créer des données factices.) Ensuite, cliquez sur Développeur dans le ruban.
Ensuite, cliquez sur Enregistrer une macro . Dans la boîte de dialogue, entrez le nom sous la forme AverageandSum et laissez-le stocké dans Ce classeur . Vous pouvez définir une touche de raccourci si vous le souhaitez. Vous pouvez entrer une description si vous avez besoin de plus de détails sur ce que fait la macro. Cliquez sur OK pour commencer à configurer la macro.
Au bas des listes horaires, entrez Totaux quotidiens . Dans la cellule à côté, saisissez =SUM(B2:B10) . Ensuite, copiez et collez cela dans le reste des colonnes. Ensuite, dans l'en-tête, ajoutez Average après la dernière colonne. Ensuite, dans la cellule suivante, entrez =Average(B2:F2) . Ensuite, collez-le dans les cellules du reste de la colonne.
Cliquez ensuite sur Arrêter l'enregistrement . Votre macro peut maintenant être utilisée sur chaque nouvelle feuille que vous ajoutez à votre classeur. Une fois que vous avez une autre feuille de données, revenez à Développeur et cliquez sur Macros . Votre macro doit être en surbrillance, cliquez sur exécuter pour ajouter vos sommes et moyennes.
Cet exemple peut vous faire économiser quelques étapes, mais pour des actions plus complexes qui peuvent s'additionner. Si vous faites les mêmes opérations sur des données avec un formatage identique, utilisez des macros enregistrées.
Les macros enregistrées manuellement dans Excel aident avec des données toujours de la même taille et de la même forme. Il est également utile si vous souhaitez effectuer des actions sur la totalité de la feuille. Vous pouvez utiliser votre macro pour prouver le problème.
Ajoutez une autre heure et un autre jour à la feuille et exécutez la macro. Vous verrez que la macro écrase vos nouvelles données. La façon dont nous contournons ce problème consiste à utiliser du code pour rendre la macro plus dynamique à l'aide de VBA, qui est une version allégée de Visual Basic. La mise en œuvre se concentre sur l'automatisation pour Office.
Ce n'est pas aussi facile à comprendre qu'Applescript, mais l'automatisation d'Office est entièrement construite autour de Visual Basic. Ainsi, une fois que vous travaillez avec ici, vous pouvez rapidement vous retourner et l'utiliser dans d'autres applications Office. (Cela peut également être d'une grande aide si vous êtes coincé avec un PC Windows au travail.)
Lorsque vous travaillez avec VBA dans Excel, vous disposez d'une fenêtre distincte. La capture d'écran ci-dessus est notre macro enregistrée telle qu'elle apparaît dans l'éditeur de code. Le mode fenêtré peut être utile pour jouer avec votre code pendant que vous apprenez. Lorsque votre macro est bloquée, il existe des outils de débogage pour examiner l'état de vos variables et données de feuille.
Office 2016 est désormais livré avec l'éditeur Visual Basic complet. Il vous permet d'utiliser le navigateur d'objets et les outils de débogage qui étaient auparavant limités à la version Windows. Vous pouvez accéder au navigateur d'objets en allant dans Affichage> Navigateur d'objets ou appuyez simplement sur Maj + Commande + B . Vous pouvez ensuite parcourir toutes les classes, méthodes et propriétés disponibles. Cela a été très utile pour construire le code de la section suivante.
Avant de commencer à coder votre macro, commençons par ajouter un bouton au modèle. Cette étape permet à un utilisateur novice d'accéder beaucoup plus facilement à votre macro. Ils peuvent cliquer sur un bouton pour appeler la macro plutôt que de fouiller dans les onglets et les menus.
Revenez à la feuille de modèle vierge que vous avez créée à la dernière étape. Cliquez sur Développeur pour revenir à l'onglet. Une fois sur l'onglet, cliquez sur le bouton . Ensuite, cliquez quelque part dans la feuille sur le modèle pour placer le bouton. Le menu des macros s'affiche, nommez votre macro et cliquez sur Nouveau .
La fenêtre Visual Basic s'ouvrira; vous le verrez répertorié comme Module2 dans le navigateur du projet. Le volet de code aura Sub AverageandSumButton() en haut et quelques lignes plus bas End Sub . Votre code doit se situer entre ces deux, car c'est le début et la fin de votre macro.
Pour commencer, vous devrez déclarer toutes vos variables. Ceux-ci sont dans le bloc de code ci-dessous, mais une note sur la façon dont ils sont construits. Vous devez déclarer toutes les variables en utilisant Dim avant le nom, puis comme avec le type de données.
Sub AverageandSumButton()
Dim RowPlaceHolder As Integer
Dim ColumnPlaceHolder As Integer
Dim StringHolder As String
Dim AllCells As Range
Dim TargetCells As Range
Dim AverageTarget As Range
Dim SumCible en tant que plage
Maintenant que vous avez toutes vos variables, vous devez utiliser immédiatement certaines des variables de plage. Les plages sont des objets qui contiennent des sections de la feuille de calcul sous forme d'adresses. La variable Toutes les cellules sera défini sur toutes les cellules actives de la feuille, y compris les étiquettes de colonne et de ligne. Vous l'obtenez en appelant ActiveSheet objet, puis c'est UsedRange propriété.
Le problème est que vous ne voulez pas que les étiquettes soient incluses dans les données de moyenne et de somme. Au lieu de cela, vous utiliserez un sous-ensemble de la plage AllCells. Ce sera la gamme TargetCells. Vous déclarez manuellement sa plage. Son adresse de départ sera la cellule de la deuxième ligne de la deuxième colonne de la plage.
Vous appelez cela en appelant votre AllCells gamme, en utilisant ses Cellules class pour obtenir cette cellule spécifique en utilisant (2,2) . Pour obtenir la dernière cellule de la plage, vous appellerez toujours AllCells . Cette fois en utilisant SpecialCells méthode pour obtenir la propriété xlCellTypeLastCell . Vous pouvez voir ces deux éléments dans le bloc de code ci-dessous.
Set AllCells =ActiveSheet.UsedRange
Définir TargetCells =Range(AllCells.Cells(2, 2), AllCells.SpecialCells(xlCellTypeLastCell))
Les deux prochaines sections de code sont des boucles For Each. Ces boucles traversent un objet pour agir sur chaque sous-ensemble de cet objet. Dans ce cas, vous en faites deux, un pour chaque ligne et un pour chaque colonne. Puisqu'ils sont presque exactement les mêmes, un seul d'entre eux est ici; mais les deux sont dans le bloc de code. Les détails sont pratiquement identiques.
Avant de démarrer la boucle pour chaque ligne, vous devez définir la colonne cible dans laquelle la boucle écrit la moyenne de chaque ligne. Vous utilisez le ColumnPlaceHolder variable pour définir cette cible. Vous le définissez égal au Count variable des Cellules classe de AllCells . Ajoutez-en un pour le déplacer à droite de vos données en ajoutant +1 .
Ensuite, vous allez commencer la boucle en utilisant For Each . Ensuite, vous souhaitez créer une variable pour le sous-ensemble, dans ce cas, subRow . Après le dans , nous définissons l'objet principal que nous analysons TargetCells . Ajouter .Rows à la fin pour limiter la boucle à chaque ligne uniquement, au lieu de chaque cellule de la plage.
Dans la boucle, vous utilisez la méthode ActiveSheet.Cells pour définir une cible spécifique sur la feuille. Les coordonnées sont définies en utilisant subRow.Row pour obtenir la ligne dans laquelle se trouve actuellement la boucle. Ensuite, vous utilisez ColumnPlaceHolder pour l'autre coordonnée.
Vous l'utilisez pour les trois étapes. Le premier que vous ajoutez .value après les parenthèses et égal à WorksheetFunction.Average(subRow) . Cela écrit la formule de la moyenne de la ligne dans votre cellule cible. La ligne suivante, vous ajoutez .Style et définissez-le sur "Currency" . Cette étape correspond au reste de votre feuille. Sur la dernière ligne, vous ajoutez .Font.Bold et définissez-le égal à True . (Notez qu'il n'y a pas de guillemets autour de celui-ci, car il s'agit de la valeur booléenne.) Cette ligne met la police en gras pour faire ressortir les informations récapitulatives du reste de la feuille.
Les deux étapes sont dans l'exemple de code ci-dessous. La deuxième boucle permute les lignes pour les colonnes et change la formule en Sum . L'utilisation de cette méthode lie vos calculs au format de la feuille actuelle. Sinon, c'est lié à la taille au moment où vous enregistrez la macro. Ainsi, lorsque vous travaillez plus de jours ou d'heures, la fonction évolue avec vos données.
ColumnPlaceHolder =AllCells.Columns.Count + 1
Pour chaque sous-ligne dans TargetCells.Rows
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Value =WorksheetFunction.Average(subRow)
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Style ="Devise"
ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Font.Bold =True
Sous-ligne suivante
RowPlaceHolder =AllCells.Rows.Count + 1
Pour chaque sous-colonne dans TargetCells.Columns
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Value =WorksheetFunction.Sum(subColumn)
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Style ="Devise"
ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Font.Bold ="True"
Sous-colonne suivante
Ensuite, étiquetez la nouvelle ligne et la nouvelle colonne, définissez RowPlaceHolder et ColumnPlaceHolder de nouveau. Tout d'abord, utilisez AllCells.Row pour obtenir la première ligne de la plage, puis AllCells.Column+1 pour obtenir la dernière colonne. Ensuite, vous utiliserez la même méthode que la boucle pour définir la valeur sur "Average Sales" . Vous utiliserez également le même .Font.Bold propriété pour mettre en gras votre nouvelle étiquette.
Puis inversez-le, en définissant vos espaces réservés sur la première colonne et la dernière ligne pour ajouter "Total Sales" . Vous voulez aussi le mettre en gras.
Les deux étapes sont dans le bloc de code ci-dessous. C'est la fin de la macro notée par End Sub . Vous devriez maintenant avoir la macro entière et pouvoir cliquer sur le bouton pour l'exécuter. Vous pouvez coller tous ces blocs de code dans l'ordre dans votre feuille Excel si vous voulez tricher, mais où est le plaisir là-dedans ?
ColumnPlaceHolder =AllCells.Columns.Count + 1
RowPlaceHolder =AllCells.Row
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value ="Ventes moyennes"
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold =True
ColumnPlaceHolder =AllCells.Column
RowPlaceHolder =AllCells.Rows.Count + 1
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value ="Total des ventes"
ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold =True
Sous-titre de fin
Les macros enregistrées sont idéales pour une répétition prévisible. Même s'il s'agit de quelque chose d'aussi simple que de redimensionner toutes les cellules et de mettre en gras les en-têtes, cela peut vous faire gagner du temps. Évitez simplement les erreurs de macro courantes.
Visual Basic ouvre la porte aux utilisateurs de Mac Excel pour approfondir la bureautique. Visual Basic n'était traditionnellement disponible que sur Windows. Il permet à vos macros de s'adapter dynamiquement aux données, ce qui les rend plus polyvalentes. Si vous avez la patience, cela peut être la porte d'entrée vers une programmation plus avancée.
Vous voulez plus d'astuces de feuille de calcul pour gagner du temps ? Découvrez comment mettre automatiquement en surbrillance des données spécifiques avec la mise en forme conditionnelle dans Excel et la mise en surbrillance conditionnelle dans Numbers sur Mac.