Fonction volatile ou non ?

Par défaut, une fonction personnalisée n’est pas « volatile ». Cela signifie qu’elle ne se recalcule pas automatiquement si l’un de ses arguments a changé : pour qu’elle se recalcule, il faut soit l’éditer (par exemple la revalider), soit effectuer un calcul total, donc avec [Ctrl]-[Alt]-[F9] plutôt que seulement [F9].

Si l’on veut que cette fonction se recalcule en permanence, il faudrait – en ôtant l’apostrophe actuelle – activer la commande Application.Volatile qui est pour le moment entrée seulement comme commentaire dans la fonction Somme_2.

Nous avons construit un petit exemple pour illustrer cela. Dans cet exemple, nous avons saisi en A1:A5 la formule =ent(100*alea()) et reproduit en colonne D les formules de la colonne C.

Nous avons une série de valeurs aléatoires en A1:A5. Quand nous lançons une nouvelle série de valeurs en faisant [F9], la valeur de C1 s’actualise, mais pas celle de C2, comme on peut le constater ci-dessus.

Remarque 1 – Si l’on fait [Ctrl]-[Alt]-[F9] pour actualiser, le problème est résolu temporairement.

Remarque 2 – Si l’on réactive la commande Application.Volatile de la fonction Somme_2, le problème de volatilité est résolu de façon définitive.

Deux exemples de fonctions

Dans l’article précédent, nous avons vu que les fonctions personnalisées pouvaient rendre des services, avec l’exemple d’une fonction personnalisée calculant en années, mois et jours l’écart entre deux dates données.

Aujourd’hui, nous verrons deux exemples – modifiés des exemples réels, pour des raisons de confidentialité – de fonctions personnalisées que j’ai développées récemment pour deux clients différents.

Extraction à partir d’une chaîne de caractères

Dans le premier exemple, illustré dans la première partie de l’écran, nous désirons obtenir une valeur numérique en colonne D à partir du texte en colonne A, en suivant l’algorithme suivant :

- élimination de toute séquence « /ML »

- extraction de la valeur placée à gauche de la première occurrence de « ML »

Cette extraction s’effectue depuis le premier espace à gauche, et francise le marqueur de décimale.

La formule de E2 est : =ml(A2).

Calcul d’une commission

Dans le second exemple, illustré dans la seconde partie de l’écran, nous calculons une commission à partir du montant investi, du taux de rémunération et de l’âge du client. Mais cette commission dépend aussi de la date de l’investissement et de la date du jour.

La formule de E16 est : =comm(D16;B16;C16;A16).

Utiliser les macro-fonctions

Nous avons souvent, dans ce blog, utilisé des formules très créatives – le plus souvent d’ailleurs créées avec une validation matricielle par [Ctrl]- [Maj]-[Entrée] – pour atteindre via une formule unique un objectif puissant et/ou compliqué.

Vous en avez vu, récemment encore, un exemple avec les deux articles « Retournement d’un nombre » du 4 janvier et « Un retournement alternatif » du 8 janvier.

Comme je l’ai indiqué dans ce dernier article, on pouvait – avec une macro-fonction ne comportant qu’une seule instruction – atteindre exactement le même objectif.

Les macro-fonctions ou « fonctions personnalisées » sont très utiles dans Excel, et j’y ai fréquemment recours dans le développements des modèles que j’effectue pour mes clients. Elles permettent de réaliser avec un simple appel de fonction un traitement d’une complexité potentiellement sans limite.

Le premier article écrit sur le sujet était « Une fonction personnalisée », le 22 janvier 2006, soit trois mois seulement après la création de ce blog. C’est dire que le sujet est important !

Dans cet article, la fonction calculait l’écart entre deux dates, cet écart étant exprimé en années, mois et jours. Nous voyons ci-dessous ce que cela donne pour le nombre de jours auquel cet auteur et ce blog ont déjà survécu…

La formule utilisée en B4 et reproduite en C4 est illustrée en ligne 8. Nous avons mis en commentaire la formule utilisée en B11, qui utilise la macro-fonction et qui est – c'est rien de le dire – bien plus lisible...

Les tables ont de la mémoire

Ce qui est intéressant avec les tables, c’est qu’elles ont de la mémoire.

Ainsi, après la destruction des 1.000 lignes de données, relatée dans notre dernier article, voyons à quoi ressemble notre table... Comme nous le constatons dans la première image ci-dessous, l’encadrement nous montre que le bloc A2:D2 – malgré la destruction des 1.000 lignes de données – fait toujours partie de la table…

Dès que nous entrons un texte dans la cellule A2 (cf. seconde image ci-dessus), la formule de la cellule D2 apparaît immédiatement. Cela montre bien que la table a de la mémoire et que – malgré la destruction de toutes ses lignes de données – elle n’a pas perdu la formule de la colonne D.

Le contenu de cet article et de l’article précédent m’a été inspiré par le contenu du lien suivant :

http://colinlegg.wordpress.com/2012/09/01/clearing-excel-tables/

Les lacunes de l’enregistrement de macros

L’enregistrement des macros, comme je le constate souvent chez mes clients qui y ont recours – pose trois problèmes majeurs.

Le premier est que le code généré est souvent très peu performant, comme nous l’avons vu dans notre exemple où la macro enregistrée tournait au moins 1.000 fois plus lentement que nécessaire.

La seconde est que le code fait toujours référence aux adresses absolues des cellules pointées, ce qui fait qu’il ne marche plus dès que l’adresse de ces cellules – suite à des insertions ou destructions de lignes ou de colonnes – a été modifiée.

La troisième est que les macros enregistrées oublient complètement d’enregistrer certaines opérations. C’est en particulier le cas, qui pourrait nous concerner ici, de la commande « Convertir en table ».

A ce sujet, je vous conseille de lire l’article "« Convertir en plage » en VBA" du 26 octobre 2009.

.

Macro pour nettoyer une table

L’expérience que nous allons faire aujourd’hui est à la fois simple et inquiétante…

Nous avons en A1:D1001 une simple table dans laquelle, comme nous pouvons le constater dans la barre de formule sur la copie d’écran, la colonne D  effectue la somme des contenus des colonnes B et C.

Nous souhaitons détruire le contenu de la base et, afin de rendre cette opération facilement renouvelable dans le futur, nous enregistrons une macro – sous le nom « Efface_base » - pour effectuer ce travail.

Pour réaliser cela, nous effectuons les opérations suivantes :

-          Sélection du bloc A2:D1001

-          Enregistrement de la macro

-          Clic droit sur la sélection

-          Commande « Supprimer » puis « Lignes de tableau »

-          Arrêt de l’enregistrement

Remarque – Nous aurions certes pu inclure la sélection du bloc dans la macro, mais il aurait alors fallu la rendre élastique… Ce n’est pas ce qui nous intéresse dans cet exemple, ce qui explique pourquoi je ne l’ai pas fait ici.

Nous consultons la macro enregistrée et nous constatons qu’elle est constituée de 1.000 lignes identiques avec chacune la commande « Selection.ListObject.ListRows(1).Delete ».

Cela est certes tout sauf efficace !

Pire encore, si nous rétablissons la table, re-sélectionnons le bloc A2:D1001 puis lançons la macro, nous constatons que son exécution prend au total 15 secondes !

Bon, nous savions déjà que les macros enregistrées sont réputées pour être inefficaces, mais – là ! – il s’agit d’un record toutes catégories !

A titre indicatif, la macro suivante, qui revient exactement au même s’il n’y a rien à droite de la table, prend une seule ligne et s’exécute instantanément…

Rows("2:1001").Delete Shift:=xlUp

Un retournement alternatif

Une autre formule pour le retournement d’un nombre (cf. article précédent) a été proposée par un autre de mes amis, Zoran Stanojević. Cette formule est plus complexe que celle de Roberto modifiée par mes soins, mais elle illustre une autre façon de résoudre le problème, même si – sur le fond – les concepts de base sont les mêmes.

 

Voici la formule magique, elle aussi validée matriciellement :

=somme((stxt(A1;ligne(indirect("1:"&nbcar(A1)));1)&"0e"&ligne(indirect("1:"&nbcar(A1))))%)

Le bloc ligne(indirect("1:"&nbcar(A1))) est évalué ici en {1;2;3;4;5;6;7;8;9;10}.

Le bloc stxt(A1;ligne(indirect("1:"&nbcar(A1)));1) vaut alors {"5";"1";"4";"6";"6";"3";"6";"7";"0";"8"}

Avec &"0e"en plus, on obtient : {"50e";"10e";"40e";"60e";"60e";"30e";"60e";"70e";"00e";"80e"}

Le bloc ligne(indirect("1:"&nbcar(A1))) vaut {1;2;3;4;5;6;7;8;9;10}.

A la fin, le « % » permet de tout diviser par 100 puisque l’on avait multiplié par 10 au départ en ajoutant un « 0 » avant le « e », et qu’un autre facteur 10 était arrivé grâce à l’exponentiation. Et voilà ! Le tour est joué…

Une fois de plus, nous constatons que les formules matricielles servent à jouer dans la feuille de calcul le même rôle que les boucles dans les macros.

Une macro-fonction pour réaliser le retournement…

En fait, ce que nous avons fait à l’aide de nos géniales formules matricielles pouvait être réalisé à l’aide d’une macro-fonction dont le code est constitué d’une ligne unique, comme nous pouvons le voir dans la copie d’écran. La formule de B7 est alors tout simplement =retourne(A1).

Remarque – La fonction StrReverse du VBA est à la base du verlan :)

On obtient dans ce cas un résultat sous forme de texte, comme avec la formule de Roberto. Si l’on avait voulu obtenir le même résultat que le mien ou celui de Zoran, il faudrait une macro-fonction un peu plus longue…

Retournement d’un nombre

Le problème à résoudre aujourd’hui est très simple à formuler, mais bien complexe à réaliser sans macro. Vous devez obtenir en B1 un nombre obtenu en retournant les chiffres du nombre inscrit en A1.

Voici la formule miracle proposée par mon ami Roberto Mensa, saisie en B1 :

=texte(somme((stxt(A1;ligne(1:15);1)&0)%*10^ligne(1:15));rept(0;nbcar(A1)))

Et voici la formule simplifiée que je propose en B3 :

=somme((stxt(A1;ligne(1:15);1)&0)%*10^ligne(1:15))
   

Ces deux formules sont matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée], et supposent qu’il y a au maximum 15 chiffres dans le nombre à retourner.

Décortiquons la formule de Roberto pour mieux la comprendre : stxt(A1;ligne(1:15);1) donne

{"5";"1";"4";"6";"6";"3";"6";"7";"0";"8";"";"";"";"";""}

Avec le bloc (stxt(A1;ligne(1:15);1)&0)%, nous obtenons :

{0,5;0,1;0,4;0,6;0,6;0,3;0,6;0,7;0;0,8;0;0;0;0;0}

Remarque 1 – Notez l’astuce consistant à ajouter un « 0 » à la fin pour que les éléments vides deviennent des valeurs, et l’ajout du « % » pour tout diviser par 100.

Après l’opération, le produit par 10^ligne(1:15), qui vaut ici {10;100;1000;10000;
100000; 1000000;10000000;100000000;1000000000;10000000000;100000000000; 1000000000000;10000000000000;100000000000000;1000000000000000}, entraîne un résultat de 8076366415.

Le bloc rept(0;nbcar(A1)) sert uniquement à indiquer le format numérique à utiliser et donne donc tout simplement ici "0000000000".

Remarque 2 – L’inconvénient de la solution proposée par Roberto est que le résultat est un texte, qui ne pourra donc pas servir à des calculs, sauf si on le multiplie ou le divise par une valeur.

Remarque 3 – L’inconvénient de ma solution – dû au fait que je renvoie justement une valeur – est que des « 0 » finaux éventuels dans le nombre original ne sont pas visibles en tête dans mon nombre final. Je ne peux pas en effet modifier le format numérique par formule pour lui donner le nombre de « 0 » voulu…

On voit en effet en lignes 6 à 8 ce qui se passe quand le nombre original se termine par un « 0 ».

Excel MVP 2013  !

Je vous informe par ailleurs que Microsoft m'a renouvelé sa confiance et que je suis nommé MVP Excel pour l'année 2013.