Une macro de totalisation (c)

Il manque un garde-fou à la macro de totalisation développée dans nos articles du 18 et du 22 septembre. Il peut en effet arriver que – dans le feu de l’action – vous lanciez cette macro à un moment où la cellule active ne se trouve pas dans le bloc dont vous voulez créer les totaux.

La macro ci-dessous résout ce problème. Nous avons seulement inséré un test sur le nombre de lignes et de colonnes du bloc, le reste de la macro demeurant inchangé :

Nous avons supposé ici qu’il devait y avoir au moins trois lignes et trois colonnes dans le bloc de la cellule active pour que celui-ci puisse être considéré comme valide.

Remarque – L’utilisation du « : » pour mettre deux instructions bout à bout nous évite d’avoir à créer une structure If…Then…Else complète, ce qui aurait été plus lourd.

Testez votre vitesse en Excel !

Quand je forme des cadres à la modélisation avancée avec Excel, j’explique au début de la formation qu’il y a deux dimensions dans lesquelles les participants vont progresser :
1. Apprendre à faire ce qu’ils savaient déjà faire, mais à le faire mieux et bien plus vite…
2. Apprendre à faire avec Excel des choses qu’ils ne savaient pas faire ou pensaient impossibles

Vous allez pouvoir vous tester vous-même à l’aide du petit exemple ci-dessous, concernant la vente de trois produits dans quatre régions :


Il s’agit, partant de ce petit tableau, de créer le tableau ci-dessous, c’est-à-dire :
1. Ajouter les totaux
2. Créer le tableau des chiffres d’affaires de 2009
3. Créer le tableau des ventes de 2010
4. Créer le tableau des chiffres d’affaires de 2010
5. Créer le tableau des pourcentages en 2010
6. Formater l’ensemble proprement…

Quand je forme les participants du MBA du Groupe HEC, de jeunes cadres performants avec 5 à 6 années d’expérience professionnelle, j’ai en moyenne 3 à 4 participants sur 20 qui parviennent à faire ce travail en moins de 10 minutes. Pour ma part, mon temps moyen est inférieur à 2 minutes.

Si vous voulez apprendre à développer en Excel 5 fois plus vite qu’un jeune cadre performant, et donc 10 à 20 fois plus vite qu’un cadre normal, venez suivre la formation que j’anime à Paris les 12 et 19 octobre (monsieur-excel.blogspot.com/2010/06/formations-du-mois-doctobre.html), ou demandez à votre patron de me faire venir chez vous pour une formation intra…

Une macro de totalisation (b)

Pour rendre notre macro de totalisation entièrement flexible, il faut créer des variables identifiant la première ligne et la première colonne du bloc, ainsi que le nombre de lignes et de colonnes. C’est ce que nous avons réalisé ci-dessous.

La clef de l’opération est la commande Selection.CurrentRegion.Select qui correspond au raccourci [Ctrl]-* grâce auquel l’ensemble du bloc autour de la cellule active est sélectionné d’un coup. Cela suppose bien entendu que, à l’appel de la macro, la cellule active soit l’une quelconque des cellules du bloc et que ce bloc soit séparé physiquement (par au moins une ligne et une colonne) de toute autre cellule de la feuille.

Remarque 1 – Si, pour des raisons esthétiques, on ne souhaite pas que le bloc reste sélectionné à la fin de la macro, il suffit d’ajouter à la fin la commande cells(lig_1,col_1).select…

Remarque 2 – Telle quelle, cette macro crée automatiquement tous les totaux d’un bloc, quelles que soient sa position et ses dimensions. Vous pouvez mettre cette macro dans votre classeur de macros personnelles afin de la rendre toujours accessible…

Une macro de totalisation (a)

Avec les deux derniers articles, nous avons appris à enregistrer une macro, puis à la retoucher afin de la rendre plus compacte et plus flexible. Nous allons nous attaquer aujourd’hui à une macro plus ambitieuse, dont l’objectif est d’ajouter automatiquement une ligne et une colonne de totalisation à un tableau.

Nous voulons que la macro crée automatiquement toutes les cellules à police bleue dans la copie d’écran ci-dessous. Après le tableau, nous avons reproduit la macro enregistrée :

Pour rendre la macro vraiment flexible, il faudrait qu’elle s’adapte à toute position de la table, et à toute dimension (nombre de lignes et de colonnes) de celle-ci.

Nous verrons dans le prochain article comment résoudre ces problèmes.

Amélioration de la macro

Aujourd'hui, nous allons améliorer la macro de création de tableau présentée il y a quatre jours.

L’enregistrement de macros pose toujours deux problèmes :
● les adresses enregistrées sont toujours absolues : ainsi, la référence de la table en A2:I13 est-elle fixe et ne fonctionnera plus si l’on insère des lignes ou des colonnes en amont de la table ;
● le code enregistré est toujours verbeux : on peut le réduire de façon significative en en réécrivant une partie de la macro.

Flexibilité par rapport à la position de la table

Pour résoudre le problème représenté par les adresses enregistrées dans la macro, il suffit de remplacer ces adresses par des noms, par exemple :
Table pour A2:I13
Années pour C16
Intérêt pour C17
Tab_int pour B3:I13

La redéfinition des noms, après des insertions de lignes ou de colonnes en amont de la table sera alors automatique. Plus de problème pour la macro !

Réécriture du Copier-Collage des valeurs

Quand on enregistre une macro, pour remplacer un bloc de cellules par ses valeurs, il faut bien passer par un Copier suivi d’un Collage spécial des valeurs. Quand on écrit une macro à la main, cela n’est plus nécessaire comme vous pouvez le voir dans le code ci-dessous…

Remarque 1 – Nous avons encore économisé une instruction (et du temps, epsilonesque sur cet exemple) en évitant de sélectionner la zone de la table avant d’appliquer l’instruction « Table ».

Remarque 2 – Le code est nettement plus compact à présent, avec seulement deux instructions là où il y en avait cinq auparavant, soit une économie de 60%. Il s’exécute en outre bien plus vite, quoi que l’on ne puisse pas en voir ici la différence.

Une macro de calcul de table

Dans la plupart des situations concrètes où l’on utilise des tables, il n’est pas nécessaire que celles-ci se recalculent en permanence. Certes, on pourrait utiliser le mode de calcul « Automatique sauf les tables » mais cette solution ne m’emballe pas…

La solution que j’utilise quand je développe des modèles pour mes clients consiste à réaliser une macro pour le calcul de la table, le lancement du calcul étant alors effectué à l’aide d’un bouton.

Reprenons la table créée à l’occasion de l’article « Remboursement d’un prêt » du 24 août dernier.

Avec Excel 2003 ou XP, utilisez la commande « Outils » - « Macros » - « Nouvelle macro » .

Avec Excel 2007 ou 2010, onglet « Développeur », commande « Enregistrer une macro ».

Il ne vous reste plus qu’à créer la table, à copier le bloc des résultats et à coller les valeurs, puis à arrêter la macro. Voici le code résultant :

Nous n’avons fait qu’enregistrer la macro, ce qui fait qu’elle n’offre pour le moment aucune flexibilité. Si l’on insère des lignes ou des colonnes avant la table, la macro ne sera plus bonne…

Remarque – Je n’ai retouché qu’une chose sur la macro enregistrée : j’ai inséré des sauts de ligne avec « _ » afin de réduire la largeur du cliché pour le rendre plus lisible.

Nous verrons dans le prochain article comment améliorer une macro de ce genre.

Variation du montant empruntable

Reprenons le modèle de remboursement de prêt depuis l’article publié il y a quatre jours. Il ne nous reste plus qu’à créer un curseur à partir duquel on peut aisément faire varier le montant du budget.

Pour cela, nous créons une barre de défilement, à partir de la barre d’outils
« Formulaire » d’Excel 2003 ou XP. Pour en savoir plus sur la barre de défilement, ou pour savoir comment la trouver dans Excel 2007 ou 2010, vous pouvez relire l’article « Zoom sur une partie d’un graphe » du 7 décembre 2007…

Nous voyons, dans la copie d’écran ci-dessus, la barre de défilement en E17:I17, ainsi que son paramétrage dans le dialogue de la commande…

Le « changement de pas » est ce qui arrive quand on clique dans la flèche à gauche ou dans celle à droite.

Le « changement de page » correspond à un clic dans la bande grisée à gauche ou à droite du curseur.

Remarque – Rappelons à cet égard que la barre de défilement est un outil bidirectionnel : quand on déplace le curseur, la valeur en F15 change ; quand on saisit une nouvelle valeur en F15, le curseur se place à l’endroit correspondant…

Comparaison avec un budget

Reprenons l’exemple de calcul de remboursement de prêt du 24 août dernier. Ajoutons cette fois en E15:F15 l’indication du montant que nous sommes en mesure de payer chaque mois.

Sélectionnons l’intérieur de la table, le bloc B3:I13 et donnons un format particulier aux cellules qui respectent la condition =B3<=$F$15.

Voici le résultat : Toutes les combinaisons de durée de prêt et de taux d’intérêt que nous pouvons nous permettre avec un budget mensuel de 600 € apparaissent aussitôt sur fond jaune !

Comme quoi un petit coup de format conditionnel enrichit rapidement un modèle…