Monsieur Excel
Pour tout savoir faire sur Excel !

13 décembre 2014

Texte après la dernière occurrence

Dans le dernier article, nous nous sommes intéressés au thème  « Classeurs, fichiers et onglets ».

Dans l’exemple ci-dessous, nous utilisons en B4 une de ces techniques pour récupérer le chemin, le nom du classeur actif et le nom de l’onglet actif dans ce classeur.

Supposons que nous souhaitions en extirper – en B5 – les noms du classeur et de l’onglet, en ignorant le chemin. Il s’agit donc de prendre la partie droite du texte, après la dernière occurrence du « \ ». Le problème est qu’il n’y a pas de fonction Excel qui récupère la dernière occurrence d’une chaîne de caractères…


Nous avons construit à cet effet une petite macro-fonction qui résout le problème de façon tout à fait esthétique en utilisant une fonction peu connue d’Excel, StrReverse. En effet, la dernière occurrence est tout simplement la première occurrence de la chaîne renversée !



07 décembre 2014

Classeurs, fichiers et onglets

Dans l’article précédent, nous avons utilisé la fonction « Cellule », un héritage d’Excel 4. Nous avons déjà utilisé à plusieurs reprises les commandes macros Excel 4 qui – miraculeusement – marchent toujours aujourd’hui !

Ce qui est merveilleux avec ces commandes macro, c’est qu’elles permettent d’obtenir, sans VBA, des résultats qui autrement ne sauraient se passer de code VBA . C’est le cas dans l’exemple ci-dessous où – sans VBA ! – , nous créons trois listes : la liste des classeurs Excel ouverts, la liste des fichiers du répertoire actif, et enfin la liste des onglets du classeur actif.

Nous avons défini trois noms pour cet exemple :
Fenêtres, défini par =fenetres()
Feuilles, défini par =lire.classeur(1)
Fichiers, défini par =fichiers()


Formule de B3 : =sierreur(index(Fenêtres;A3);"")
Formule de C3 : =sierreur(index(Fichiers;A3);"")
Formule de D3 : =index(Feuilles;A3)

Les autres formules sont affichées en commentaire.

Remarque – Les macros Excel 4 ne se recalculent pas avec un simple calcul déclenché par [F9]. Pour les recalculer, il faut un calcul complet, lancé par CalculateFull en VBA ou par [Ctrl]-[Maj]-[F9] en direct.


02 décembre 2014

Classeur, chemin et feuille

Nous avions publié un article appelé « Les noms du classeur, du chemin et de la feuille » le 14 décembre 2006. Voici une actualisation de cet article.

Dans cet exemple, nous utilisons les fonctions cellule() et informations() pour récupérer séparément le nom du classeur, le nom de la feuille, le nom du chemin du classeur et enfin le nom du chemin « actif », c’est-à-dire celui dont on voit la liste des fichiers quand on passe par la commande « Ouvrir » du menu Fichier.


Pour A1 et B7, nous avons mis les formules en commentaire, il est donc superflu de les décrire ici.

Remarque 1 – Notez la présence du second argument dans la formule de la cellule A1 : si nous l’avions omis, A1 ferait référence à la feuille et au classeur de la dernière cellule modifiée, et donc pas nécessairement à cette feuille ni à ce classeur !

Voici donc les formules utilisées dans les autres cellules :
B4 : =stxt(A1;cherche("[";A1)+1;cherche("]";A1)-cherche("[";A1)-1)
B5 : =droite(A1;nbcar(A1)-cherche("]";A1))
B6 : =gauche(A1;cherche("[";A1)-1)

Ces formules sont assez simples car elles font référence à la cellule A1. Si l’on voulait tout calculer à chaque fois en une seule cellule, ces formules deviendraient nettement plus lourdes :
C4 : =stxt(cellule("filename";$A$1);cherche("[";cellule("filename";$A$1))+1;
cherche("]";cellule("filename";$A$1))-cherche("[";cellule("filename";$A$1))-1)
C5 : =droite(cellule("filename";$A$1);nbcar(cellule("filename";$A$1))-cherche("]"; cellule("filename";$A$1)))
C6 : =gauche(cellule("filename";$A$1);cherche("[";cellule("filename";$A$1))-1)

Remarque 2 – Il est intéressant de noter que, si l'on voulait récupérer les noms du classeur, de la feuille, ou le chemin, il faudrait normalement passer par des macros en VBA. Ici, nous obtenons le même résultat directement avec de simples (si l'on peut dire) formules...


26 novembre 2014

Une formule enfin lisible...

Nous apportons dans cet article la solution au problème posé dans l’article précédent.

La première chose que j’ai constatée est que la séquence $D$58+$D$71 +$B$110-$B$125 apparaissait à 11 reprises dans la formule. J’ai donc créé une cellule à gauche, nommée Cal_01, avec cette formule.

J’ai ensuite créé deux autres cellules nommées, la première – ADT – avec la formule =annee(Der_traité) et la seconde – API – avec la formule =annee(Prem_inc).

Enfin, j’ai provoqué un retour chariot après chaque SI principal grâce à la combinaison [Alt]-[Entrée].

Voici le résultat :


Comparez cette formule avec celle de l’article précédent…
C’est le jour et la nuit !

On constate que la formule effectue une double comparaison de API et Year d’une part et de ADT et Year d’autre part. Tout cela se présente donc comme la « simple » somme de six composants.

Remarque 1 – On aurait pu encore raccourcir un peu cette formule en nommant MDT la formule =mois(der_traité) mais cela ne nous a pas paru nécessaire…

Remarque 2 – Cette formule aurait pu être remplacée par une macro-fonction. Ceci dit, j’essaye de les utiliser le moins possible car mes clients – qui ne sont en général pas à l’aise avec le VBA – ont plus de mal à s’approprier un modèle comportant du code VBA.

En conclusion, vous voyez bien dans cet exemple, en comparant la solution ci-dessus à la formule originale, ce que l’on peut gagner avec une procédure d’audit et amélioration d’un modèle…


21 novembre 2014

Une formule très complexe !

Je fais du conseil en modélisation depuis 1967, donc depuis 47 ans déjà. Dans ce cadre, j’ai créé et/ou audité et/ou amélioré les modèles de plus de 100 entreprises dans plus de 10 pays. Parmi ces entreprises, j’ai travaillé pour les sociétés pharmaceutiques suivantes : Aventis, Johnson & Johnson, Lilly France, Pasteur Mérieux, Sanofi et Smithkline Beecham.

Hier, je travaillais chez l’une de ces entreprises et j’ai rencontré une formule qui est probablement la plus complexe que j’aie jamais rencontrée lors de telles interventions. Cette formule était reproduite sur 12 colonnes, une fois par mois de l’année (avec de petites différences en janvier et décembre), et apparaissait dans huit blocs horizontaux consécutifs..

Voici la formule pour le mois de février :


Remarque – Quand on édite la formule, on voit bien les différents éléments dans des couleurs différentes, ce qui est une excellente idée de la part de Microsoft. Ce qui est regrettable, c’est qu’il n’y ait pas la possibilité d’augmenter la taille de la police ou de disposer d’une loupe. En effet, malgré les couleurs, avec de petits yeux comme les miens, il est difficile de bien associer les parenthèses fermantes aux parenthèses ouvrantes.

La question que je vous pose, et à laquelle vous trouverez une réponse dans le prochain article, est donc : « Que feriez-vous dans une telle situation pour rendre la formule plus compréhensible et même – dans la mesure du possible – auditable ? »


16 novembre 2014

Calcul de l’espace libre

Aujourd'hui, nous vous proposons une petite fonction macro dont le seul but est de calculer l’espace libre sur un support donné.

Il suffit d’écrire =libre("C:") pour calculer l’espace libre sur le disque C.

Bous affichons en commentaire, dans la copie d'écran ci-dessous, la formule entrée en B1 et reproduite ensuite vers le bas.


A l’aide de cette fonction, on peut par exemple vérifier si un support contient encore assez de place pour que l’on puisse y enregistrer un fichier donné.

Ce qui est particulièrement intéressant dans cette fonction, c’est que nous avons une preuve de plus du contrôle que nous pouvons avoir – via Excel – sur notre environnement.


10 novembre 2014

La liste déroulante « interactive »

Dans le monde Excel, il est rare qu’une innovation réelle voie le jour.

Cela a été le cas il y a deux ans avec le concept de survol de la souris découvert par Jordan Goldmeier. Vous en aurez tous les détails avec les trois articles suivants :

« Une macro sensible au passage de la souris » du 23 juillet 2012

« Un splendide usage du survol de souris ! » du 28 juillet 2012

« Un survol de souris vraiment bluffant ! » du 19 octobre 2013.

Je suis tombé il y a deux mois sur une autre invention originale, même si sa portée est  moins grande, la notion de « liste déroulante interactive ». Nous prendrons comme départ la liste de 100 acteurs utilisée dans l’article précédent, mais avec cette fois-ci le prénom de l’acteur avant son nom, la liste originale étant quand même triée en fonction du nom.

L’idée totalement innovante que nous allons mettre en œuvre aujourd’hui consiste à créer une liste déroulante interactive : on tape une séquence de caractères en B2, puis on déroule le menu : la liste déroulante est alors limitée à tous les acteurs dont le prénom-nom contient la séquence en question .

Les deux copies d’écran encadrées de bleu montrent ce que devient la liste déroulante quand on entre préalablement en B2 les séquences « oo » ou « ba ».

Pour obtenir ce résultat, nous avons défini le menu déroulant via une validation par liste avec la formule =acteurs. Ce nom est lui-même défini par =decaler('Liste dér.'!$E$2;;;max('Liste dér.'!$D:$D)).

Bien entendu, si l’on fait cela, on ne pourra plus taper une séquence en B2. La clef est donc, lors de la création de la validation par liste, de passer par l’onglet « Alerte d’erreur » et de décocher « Quand des données non valides sont tapées » !

En D2, recopiée vers le bas, nous avons la formule =D1+SI(estnum(cherche($B$2;A2));1;0) .
Et, en E2, copiée vers le bas, la formule =sierreur(index(A:A;equiv(ligne()-1;D:D;0));"").


Par rapport à la solution présentée dans notre dernier article, cette solution est bien entendu plus lourde car elle requiert deux colonnes supplémentaires. Mais le temps qu’elle peut vous gagner lors de la recherche dans de grandes listes déroulantes est sans mesure !

Remarque – Il y a une autre différence. Dans l’article précédent, on se cadrait sur le début du nom de l’acteur. Ici, on cherche simplement une séquence de caractères placée n’importe où dans le prénom-nom.

L’article original est dû à RajExcel :
Video Tutorial : Create a searchable drop down list in Excel 2013

Si vous consultez la vidéo, vous verrez que ma solution est une amélioration de celle proposée par Raj. Ceci dit, je lui tire mon chapeau pour cette innovation qui – à mon sens – est d’un grand intérêt pratique.