Monsieur Excel
Pour tout savoir faire sur Excel !

28 avril 2011

L’intérêt du Visual Basic (VBA)

Le fait de savoir un peu programmer en Visual Basic est précieux. Avec quelques petits programmes rapidement développés, vous pouvez automatiser de nombreuses tâches : sélection de diverses zones ou feuilles et impression, présentations diverses de la même feuille, récupération et transformation d’imports depuis un fichier extérieur,…

Pour faire tout cela, il vous faut connaître le VBA, mais il n’est pas nécessaire d’en être un spécialiste. Le VBA est un langage ; on n’apprend pas à parler anglais en deux jours, et cela est tout aussi valable pour un langage de programmation, même si son apprentissage est plus rapide que celui d’une langue parlée.

En revanche, en deux jours, partant de zéro (en VBA, pas en Excel !), n’importe quelle personne ayant un peu de sens logique peut apprendre les bases du VBA. Certes, elle ne connaîtra pas toutes les arcanes de ce langage, ni même la plus grande partie de son vocabulaire. En revanche, elle sera autonome pour développer en VBA et – surtout ! – capable de trouver par la suite les connaissances dont elle aura besoin et qu’elle ne possédera pas encore.

Tout cela, c’est l’objet de la formation « Découverte du Visual Basic » que je propose les vendredis 13 et 20 mai à Paris (voir le bouton « Prochaines formations » dans le bandeau droit). J’offre à tous les lecteurs du blog qui s’inscriraient à cette formation – à titre exceptionnel – un abonnement d’un an au blog à titre de cadeau.

Cette formation sera pour vous l'occasion de découvrir quelques belles applications d'Excel et du VBA et – si vous venez avec vos modèles – de pouvoir repartir avec quelques macros déjà opérationnelles sur ces modèles.

23 avril 2011

Sélection multiple et copie

Vous avez déjà certainement remarqué qu’il n’est pas possible, dans Excel, de faire à l’aide de la touche [Ctrl] une sélection multiple et de la copier pour la coller ailleurs.

Dès que vous essayez de copier une sélection multiple avec [Ctrl]-c, le message « Impossible d’exécuter cette commande sur des sélections multiples » surgit...

Eh bien, en fait, ce n’est pas toujours impossible… Si vous sélectionnez des blocs qui ont la même hauteur et se trouvent dans les mêmes lignes, comme ci-dessous avec les trois plages A1:B8, D1:D8 et F1:F8, la copie ne pose plus aucun problème !

La copie en revanche ne serait pas possible si par exemple le troisième bloc était F1:F7 ou F2:F8…

Au moment du collage, on retrouve les trois blocs contigus, collés les uns aux autres. Cette fonctionnalité est peu connue et peut se révéler bien utile :)

Copier seulement les cellules visibles

Supposons à présent que nous ayons masqué la ligne 5. Sélectionnons A4:B6 puis utilisons le raccourci [Alt]-; pour sélectionner uniquement les cellules visibles (encore un raccourci peu connu !).

Si à présent nous copions puis collons le résultat ailleurs, nous n’aurons la copie que des quatre cellules visibles de la sélection !

Si nous n’avions pas sélectionné les seules cellules visibles, le collage nous aurait donné des copies des six cellules sélectionnées…

18 avril 2011

Une utilisation de indirect()

Le problème suivant a été fourni par un lecteur du blog. Une entreprise doit suivre les activités de ses clients, avec un onglet par client. Chacun de ces clients peut passer au maximum deux commandes par jour. L’objectif est de regrouper dans un onglet de récapitulation la liste des commandes des clients.

Nous voyons dans l’écran ci-dessous la présentation de la récapitulation avec – en insert – une photo de l’onglet Lerouge.

La formule miracle, saisie en C4 et reproduite dans les colonnes C à E, est la suivante :

=SI($A4<>$A3;index(indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0));
index (indirect(C$2&"!B:B");equiv($A4;indirect(C$2&"!A:A");0)
+equiv($A4;decaler(indirect(C$2&"!A1");
equiv($A4;indirect(C$2&"!A:A");0);0):indirect(C$2&"!A2000");0)))

Si vous avez bien compris les articles précédents sur les fonctions de recherche, vous n’aurez aucune difficulté à comprendre cette formule :)

Remarque – Vous pouvez noter en particulier que cette formule utilise 7 fois la fonction indirect() que nous avons étudiée il y a quatre jours…

14 avril 2011

Tutorial sur la fonction indirect()

Pour terminer sur l’adressage dans Excel, après les fonctions recherche(), index() et equiv() puis, plus récemment, decaler(), nous étudions aujourd’hui la dernière des fonctions d’adressage, la fonction indirect().

Supposez qu’en B1, vous ayez récupéré (cf. formule reproduite en B8) un montant de dépense provenant d’un autre classeur. Vous souhaitez rendre cette recherche paramétrable, en vous gardant la possibilité de modifier le nom du classeur, celui de la feuille, ou l’adresse de la cellule concernée.

Cela se fait très bien en utilisant la solution proposée en B2 (cf. formule en B9), grâce à la fonction decaler(). L’argument de la fonction sert uniquement à reconstituer, caractère par caractère, la formule que nous avions en B1…

Remarque 1 – Si le classeur référencé dans la formule n’est pas ouvert, le résultat de la formule est le message d’erreur #REF !

Remarque 2 – Dans le cas ci-dessus, qu’il y ait $B$129 ou B129 en B6 revient strictement au même.

Syntaxe de la fonction

La syntaxe de la fonction est =decaler(référence[;A1]).

Si l’argument facultatif A1 est FAUX, la référence doit être de type L1C1.

S’il est VRAI ou absent, la référence doit être de type A1, c’est-à-dire une ou plusieurs lettres pour la colonne et un nombre pour la ligne.

10 avril 2011

Sécurisez-vous avec decaler() !

La fonction decaler() est particulièrement utile pour sécuriser les formules en les rendant plus flexibles. Nous en avons un excellent exemple ci-dessous avec le modèle de la Figure 1 : nous avons des valeurs en colonne A et nous calculons en colonnes B et C les pourcentages d’évolution d’une ligne à l’autre.

Formule de B2 : =A2/A1-1
Formule de C2 : =A2/decaler(A2;-1;0)-1

Cas de la suppression d’une ligne

Si par exemple nous détruisons la ligne 7, nous obtenons le résultat de la Figure 2. Pour la colonne C, aucun problème ! En revanche, la colonne B affiche un message d’erreur…

Cas de l’insertion d’une ligne

Si en revanche nous insérons une ligne au-dessus de la ligne 8, nous obtenons le résultat de la Figure 3.

Dès que l’on entre une valeur en A8 (cf. Figure 4), deux choses se passent : la colonne C reçoit automatiquement la bonne formule et elle affiche les bons résultats en C8 et C9.

Dans le cas de la colonne B, si l’on se contente de recopier la formule de B7 en B8, B8 affichera le bon résultat, mais B9 continuera à afficher le faux résultat 9,1% ! Pour éviter ce problème, il faut penser à recopier la formule de B7 jusqu’en B9…

En guise de conclusion…

La formule de la colonne C est plus longue et plus complexe, mais bien plus sûre que celle de la colonne B, que ce soit lors de la suppression ou lors de l’insertion d’une ligne.

De la même façon, quand on effectue la somme d’un bloc avec en ligne 1 des titres et en ligne 5 le total, la formule classique =somme(A2:A4) n’offre aucune élasticité : toute ligne insérée entre la ligne 1 et la ligne 2, ou entre la ligne 4 et la ligne 5 sera ignorée dans le total.

Si en revanche on utilise dans ce cas la formule =somme(A1:decaler(A5;-1;0)), on dispose alors d’une formule totalement élastique qui prend automatiquement en compte les valeurs de lignes insérées juste au-dessus de la ligne 2 ou de la ligne 5.

Comme quoi il faut parfois utiliser des formules plus lourdes pour bénéficier d’un taux de sécurité maximal !

05 avril 2011

Tutorial sur la fonction decaler()

Pour faire suite aux tutoriaux récents sur les trois fonctions fondamentales d'Excel, recherche(), index() et equiv(), il est approprié de jeter maintenant un coup d’œil sur la fonction decaler() qui a elle aussi une grande utilité.

La syntaxe est =decaler(référence;nb_lig;nb_col[;hauteur[;largeur]])

… où les crochets droits représentent des arguments facultatifs…

… et où hauteur et largeur sont le nombre de lignes et de colonnes à ramener…

La référence peut être la référence à une cellule ou à un bloc rectangulaire de cellules.

L’utilisation habituelle de la fonction decaler()

La syntaxe la plus courante est la formule =decaler(adresse;nb_lig;nb_col), grâce à laquelle on récupère le contenu de la cellule placée nb_lig en dessous et nb_col à droite de la cellule dont l’adresse est indiquée en premier argument.

Nous voyons une utilisation tout à fait classique de cette syntaxe dans les articles « Introduction à la fonction Decaler() » du 5 janvier 2007 et
« Paiement à 15 ou à 45 jours » du 9 janvier 2007, dont je vous conseille vivement la lecture.

Utiliser decaler() en deux dimensions

Il y a deux façons d’utiliser la fonction decaler() en deux dimensions :

● soit en prenant comme premier argument la référence à un bloc de cellules plutôt qu’à une cellule unique

● soit en utilisant les arguments facultatifs hauteur et largeur.

Ainsi, =decaler(A1:B3;2;3) renvoie la matrice D3:E5.

Si l’on entre cette formule normalement dans une cellule, on obtient le message #VALEUR !

Si l’on entre cette formule matriciellement (donc avec [Ctrl]-[Maj]-[Entrée]) dans une cellule, on obtient la valeur de D3.

Si enfin on l’entre matriciellement cette formule dans un bloc de trois lignes et deux colonnes, on récupère le contenu de la matrice A1:B3.

Maintenant si, dans un bloc de trois lignes et deux colonnes, vous entrez matriciellement la formule =decaler(A1;2;3;3;2), vous obtenez exactement le même résultat !

Donc, =decaler(A1:B3;2;3) et =decaler(A1;2;3;3;2) sont totalement équivalents, ce qui fait que je ne vois pas bien l’intérêt des deux derniers arguments, sauf peut-être dans une macro dans laquelle on n'a pas envie de s'embêter avec des lettres comme identificateurs de colonnes …

Appel aux lecteurs du blog !

Un lecteur pourra peut-être nous montrer une utilisation intelligente de la double double dimension, c’est-à-dire un exemple intéressant où le premier argument représente une matrice et où les deux arguments facultatifs sont utilisés (avec des valeurs différentes de 1 !)

01 avril 2011

L’heure et la date au pied levé

On peut à tout moment entrer dans une cellule la date du jour avec la combinaison [Ctrl]-; ou l’heure avec la combinaison [Ctrl]-:.

La date est bien celle du jour, mais à 0:00.

L’heure, quant à elle, ne tient pas compte des secondes : elle se situe automatiquement au début de la minute active.

Il se peut que vous souhaitiez disposer de la date avec l’heure, c’est-à-dire la valeur de =maintenant() au lieu de celle de la formule =aujourdhui().

De même, il se peut que vous souhaitiez avoir un tampon avec l’heure précise, à la seconde près, plutôt que l’heure arrondie au début de la minute.

Pour cela, il suffit de mettre dans votre classeur de macros personnelles les deux macros suivantes puis de leur associer les raccourcis [Ctrl]-m pour
« maintenant » et [Ctrl]-h pour l’heure.

Remarque – Il n’est hélas pas possible de leur accorder les raccourcis [Ctrl]-; ou [Ctrl]-: car les codes doivent être des lettres...