Monsieur Excel
Pour tout savoir faire sur Excel !

28 avril 2008

Découvrez la fonction Remplacer()

A l’instar de la fonction Substitue(), présentée dans les deux derniers messages, la fonction Remplacer() sert à remplacer une chaîne de caractères par une autre chaîne de caractères.

Sa syntaxe est : =remplacer(texte;n° car;nb. car.;chaîne)

Cette formule va prendre le texte original et, à partir du caractère dont le numéro est n° car, remplacer un nombre de caractères égal à nb. car par la chaîne de remplacement.

Avec remplacer(), la chaîne à remplacer est identifiée par sa position et sa longueur, alors qu’avec substitue(), elle l’était par son contenu et son numéro d’occurrence.

Par ailleurs, substitue() permet d’effectuer plusieurs remplacement d’un coup – quand le n° d’occurrence n’est pas spécifié – alors que remplacer() ne permet d’effectuer qu’un remplacement à la fois.

En conclusion, ces deux fonctions sont complémentaires, chacune offrant des possibilités que l’autre ne propose pas.

24 avril 2008

Récupération de nombres US

On récupère parfois des listes de nombres écrits à l’américaine, c’est-à-dire avec le point en séparateur des milliers et la virgule comme marqueur de décimale. Le problème est alors de récupérer cela de façon utilisable par Excel :
En fait, ce but peut être atteint à l’aide d’une formule unique Voici la formule que nous avons entrée en B2 et recopiée jusqu’en B4 :

=1*substitue(substitue(A2;",";"");".";",")

Cette formule élimine les virgules puis remplace le point éventuel par une virgule. La multiplication par « 1 » transforme le texte ainsi obtenu en valeur : c’est plus simple de faire ce produit par 1 que d’utiliser la fonction Cnum().

Remarque 1 – Notre formule ne marcherait pas dans le cas où il y aurait des
« $ », comme en A5. Pour résoudre ce problème, nous avons créé la formule suivante, qui peut d’ailleurs remplacer sans problème la formule précédente, même quand il n’y a pas de « $ » :

=1*substitue(substitue(substitue(A5;"$";"");",";"");".";",")

Remarque 2 – Les formats numériques des cellules B2:B5 ne sont pas venus tout seuls : il revient à vous à les définir…

20 avril 2008

Découvrez la fonction Substitue()

La fonction Substitue() fait partie de la grande famille des fonctions peu connues et donc pratiquement inutilisées d’Excel.

Sa syntaxe est : =substitue(texte;chaîne_1;chaîne_2[;position])

Nous utilisons la convention informatique selon laquelle les arguments entre crochets sont facultatifs.

L’objet de la fonction est donc, dans un texte donné, de remplacer une première chaîne de caractères par une autre. Si un quatrième argument est fourni, il indique quelle est la seule position de la chaîne – quand elle apparaît plusieurs fois – où le changement sera effectué.

Dans l’exemple ci-dessus, le texte original est en B1 et les formules utilisées en A4:A6 sont reproduites en B4:B6.

Quand la position demandée est supérieure au nombre d’occurrences de chaîne_1, comme c’est le cas en A6, aucune substitution n’a lieu.

Remarque 1 – On peut donc vérifier si, dans un texte, une chaîne apparaît moins de n fois. Si c’est le cas, le texte est le même avant et après substitution de la énième occurrence.

Remarque 2 – La fonction Remplacer(), que nous étudierons bientôt, joue aussi un rôle de remplacement, mais avec des différences notables.

16 avril 2008

Formations en mai et juin

Vous trouverez ci-dessous la liste des formations que je propose durant les mois de mai et juin.

Chacune de ces formations est limitée à huit participants, avec un ordinateur par personne. Vous trouverez la description complète de ces formations en cliquant sur leurs intitulés.

Modélisation avec Excel (2 jours) : mardi 27 mai et mardi 3 juin 2008.

Initiation au Visual Basic (2 jours) : mercredi 18 et jeudi 19 juin 2008.

Formations en intra

Chacune de ces formations peut être animée en intra, c’est-à-dire dans votre entreprise, et peut donc aussi être personnalisée. Ainsi, on peut utiliser comme exemple les modèles de l’entreprise, présents ou à venir.

Le but de la formation "Initiation au Visual Basic" n'est pas d'apprendre tout Visual Basic en deux jours, ce qui serait illusoire. Il s'agit plutôt de donner aux participants un niveau leur permettant de se lancer en Visual Basic et de savoir par la suite où trouver les informations qui leur manquent encore. Le but est donc de les rendre autonomes en Visual Basic.

Notez en particulier que, dans la formation « Modélisation avec Excel », qui connaît un taux de satisfaction proche de 100%, je garantis de transformer en deux journées un utilisateur « lambda » d’Excel en un utilisateur appartenant au top 10%.

Références de formation intra

Voici quelques références de sociétés auprès desquelles j’ai animé ces formations en intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, Lilly France, Marsh, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

13 avril 2008

Une macro de chronométrage

Dans le modèle présenté il y a quatre jours, nous faisions référence à une macro appelée « Chrono » jouant le rôle d’un chronomètre, c’est-à-dire se mettant en marche à la première exécution, et affichant le temps passé à la seconde exécution.

Voici cette macro qui est tellement utile que – pour ma part – je l’ai logée dans mon classeur de macros personnelles, en lui associant en prime le bouton en forme de sablier dans ma barre d’outils « Standard ».

Remarque 1 – On peut noter l’utilisation par la macro de la fonction Texte() d’Excel pour afficher le temps passé dans le format « minutes:secondes ».

Remarque 2 – La variable Time étant publique, sa valeur est préservée d'une exécution de la macro à l'autre.

09 avril 2008

Macro de comparaison de vitesse

Certains lecteurs m’ont demandé comment j’avais fait pour comparer la vitesse des quatre formules présentées dans mes deux derniers messages.

Voici le code de ma macro, qui contient quatre instructions débutant par Range("F2").Formula. A chaque mesure de temps, l’une de ces formules est active, les trois autres étant transformées en commentaires.

La routine « Chrono » est une macro jouant le rôle de chronomètre. Quand on l’exécute une première fois, elle « lance » le chronomètre. Quand on l’exécute une seconde fois, elle l’arrête et affiche le temps passé.

C’est une routine tellement utile que je l’ai logée dans mon classeur de macros personnelles et que je lui ai attribué un bouton dans une barre d’outils.

Dans mon prochain message, je vous présenterai cette macro…

Remarque – A la fin, je remplace les formules de F2:F10000 par les valeurs correspondantes, afin d’alléger le modèle, en temps de calcul comme en place mémoire.

05 avril 2008

Test comparatif de vitesse

Nous avons présenté dans le dernier message quatre formules différentes pour aboutir au même résultat, la dernière étant une formule matricielle.

Fort de ce que m’avaient dit des contacts chez Microsoft, j’avais déclaré que la formule matricielle était moins performante en vitesse que les solutions à base de SommeProd().

J’ai fait un premier essai comparatif, avec 5.000 fois la formule, appliquée à une liste de 10.000 combinaisons aléatoires de poste et département.


Je suis parvenu, pour les trois premières formules, à des temps entre 2’30" et 2’40". Et, oh surprise, à un temps de 1’12" pour la formule matricielle.

Là, j’étais quand même surpris que cette dernière soit deux fois plus rapide. J’ai alors réalisé que, pour les trois premières formules, ma macro entrait la formule directement dans les 5.000 cellules alors que – pour éviter que la colonne entière ait une formule matricielle unique – ma macro entrait la formule matricielle en G2, puis la copiait et la collait dans le reste de la colonne.

J’ai donc modifié ma macro pour que – dans les quatre cas – elle effectue ce copier/coller. Les quatre temps de calcul se sont alors tous situés entre 1’12" et 1’18".

Remarque – Excel calcule deux fois plus rapidement cette formule collée 4.999 fois que la même formule entrée dans les 5.000 cellules. En effet, quand une formule est collée, Excel ne « l’entre » pas dans chaque cellule mais marque ces cellules comme ayant « la même formule que … ». Et cela a un impact sensible sur le temps de calcul.

J’ai ensuite modifié la macro pour que – dans les quatre cas – elle effectue le copier/coller, et ce sur 10.000 cellules. Voici le résultat des courses :

Formule représentée en H2 : 2’38".
Formule représentée en H3 : 2’37".
Formule représentée en H4 : 2’29".
Formule représentée en H5 : 2’29".

Sans ce copier/coller, H2 prend 5’17", H3 prend 5’12" et H4 prend 4’58".

En conclusion, la troisième formule (avec le SommeProd() et « -- ») et la formule matricielle sont un peu plus rapides – mais seulement d’environ 5% - que les deux premières formules.

01 avril 2008

Quatre formules pour un résultat

Nous l’avons déjà dit à plusieurs reprises : Excel est tellement riche qu’il y a souvent de multiples façons d’aboutir à un même résultat.

Pour résoudre le problème posé le 27 mars, on peut distinguer quatre solutions différentes, notre première solution étant représentée en H4 :

En H2, nous avons simplifié un peu en remplaçant le "--" par un "1*".

En H3, nous avons utilisé la seconde syntaxe de la fonction sommeprod(), dans laquelle il n’y a plus qu’un seul argument, mais où l’on peut opérer des opérations matricielles.

En H5 enfin, nous avons utilisé une formule matricielle, donc tapée sans accolades mais validée avec la combinaison [Ctrl]-[Maj]-[Entrée].

Remarque – Je ne vous conseille pas d’utiliser une formule matricielle quand une formule normale peut être utilisée. En effet, le temps de calcul des formules matricielles est plus long.