Monsieur Excel
Pour tout savoir faire sur Excel !

30 mai 2010

Application aux chaînes de Markow

Les chaînes de Markow sont des outils de modélisation très pratiques pour analyser l’évolution d’un système dans le temps. Supposons qu’il y ait cinq états possibles et qu’à chaque étape de temps, on ait une certaine probabilité de passer d’un état à un autre. Ces probabilités sont représentées par la matrice A1:F6. On a ainsi par exemple une probabilité de 15% (cellule C4) de passer de l’état 3 à l’état 2.

La matrice A10:B14 montre quel est le vecteur (en colonne B) indiquant les probabilités au temps « 1 » d’être dans chaque état. Dans notre exemple, cette probabilité est de 20% pour chaque état.

Si l’on veut connaître la répartition des états à chaque étape suivante, le temps « 2 », le temps « 3 »…, il suffit de multiplier à chaque fois le vecteur du temps précédent par la « matrice de transition », soit B2:F6.

Voici les formules entrées dans les cellules jaunes qui font tout le travail. Il a suffi ensuite de copier les formules de C10:C14 vers la droite pour obtenir la répartition des états jusqu’au temps « 10 ».

En C10 : =somme(B$10:B$14*$B$2:$B$6)
En C11 : =somme(B$10:B$14*$C$2:$C$6)
En C12 : =somme(B$10:B$14*$D$2:$D$6)
En C13 : =somme(B$10:B$14*$E$2:$E$6)
En C14 : =somme(B$10:B$14*$F$2:$F$6)

Toutes ces formules sont matricielles, donc validées avec la combinaison [Ctrl]-[Maj]-[Entrée].

Les processus markoviens sont par exemple utilisés en gestion du personnel dans les grandes entreprises pour prévoir l’évolution des effectifs dans le temps. Ils ont été utilisés pour prouver la validité du principe de Peter selon lequel tôt ou tard on finit par se retrouver dans un poste où l’on est inefficace.

Quand je fais encore du développement, à mon âge avancé (!), au lieu de seulement diriger des équipes ou mon entreprise, c’est une façon de lutter contre le principe de Peter…

26 mai 2010

Le produit matriciel dans Excel

Supposons que vous désiriez obtenir une matrice C = A * B, c’est-à-dire qui soit le produit de deux autres matrices A et B. Dans l’exemple ci-dessous, nous voyons un exemple de ce produit matriciel.

La formule saisie de façon matricielle – donc avec la combinaison [Ctrl]-[Maj]-[Entrée] – dans les cellules A9:C11 est- la formule =produitmat(A3:B5;D3:F4).

Si l’on en croit les informations proposées par Microsoft OnLine (à l'adresse http://office.microsoft.com/fr-fr/excel/HP052091811036.aspx), « PRODUITMAT renvoie l'erreur #VALEUR! » quand soit « Le nombre de colonnes de matrice1 est différent du nombre de lignes de matrice2 », soit « La taille de la matrice résultante est égale ou supérieure à un total de 5 461 cellules ».

Nous avons testé le premier cas en entrant la formule pour la matrice C dans A9:E13 au lieu de A9:C11 et constaté que les cellules excédentaires affichaient alors l'erreur « #N/A! » au lieu de l'erreur « #VALEUR! ».

Pour les 5.461 cellules, nous n’avons pas bien compris la raison de cette limite puisque cela ne tombe ni sur un carré exact (le carré le plus proche est 5.476, le carré de 74), ni près d’une puissance de 2. Ceci dit, nous avons constaté que le produit de deux matrices 73*73 se passait bien et que l’on obtenait bien l'erreur « #VALEUR! avec le produit de deux matrices 74*74.

Le seul rapport entre la limite acceptable de 5.460 cellules, un carré parfait, et une puissance de 2 est que 5.460 = 74^2 – 16 soit 74^2 – 2^2^2… Mais alors pourquoi le 74 ?

Si quelqu’un peut nous expliquer la raison de cette limite de 5.460, je suis preneur :)

22 mai 2010

« VBA Excel 2010 », chez ENI

« VBA Excel 2010 – Programmer sous Excel : Macros et Langage VBA » est écrit par Michèle Amelot et publié aux Editions ENI, 414 pages, 29,90 €.

Un classeur d’exercices peut être téléchargé à partir du site ENI.

Liste des chapitres

1. Présentation
2. Le langage VBA
3. La programmation objet sous Excel
4. Les objets d’Excel
5. Les boîtes de dialogue
6. Les formulaires
7. Amélioration de l’interface utilisateur
8. Gestion des événements
9. Débogage et gestion des erreurs
10. Communication avec les applications Office 2010
11. Internet
12. Programmation Windows
13. Code d’une mini-application
14. Annexes

Commentaires sur le contenu

Comme on le voit dans la table des matières, le contenu de ce livre est assez exhaustif et la mise à disposition des fichiers facilite la mise en œuvre et l’apprentissage.

La mini-application concerne une gestion de devis : création à partir d’un modèle, recherche d’un devis à partir de plusieurs critères, création et recherche de clients. Une base de données Access est fournie pour la gestion des clients.

Ce qui me manque toujours dans ce type d’ouvrage est une présentation de ce qui – avec Excel 2010 – diffère de la dernière version, Excel 2007 en la matière. Cela gagnerait bien du temps de savoir tout de suite ce qui a pu changer d’une version à l’autre !

Ceci dit, l’ouvrage est clair et sera utile aux développeurs.

18 mai 2010

Les méthodes de l’objet « RexExp »

Dans l’article précédent, nous avons découvert l’objet RegExp du vbscript.

Dans la page Web dont la référence est donnée ci-dessous, vous trouverez un classeur Excel démontrant l’utilisation des méthodes « Execute », « Replace » et « Test » de cet objet.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=68

La première feuille de ce classeur (cf. extrait ci-dessus) vous aiguille vers les autres feuilles, dont voici le contenu synthétisé :

1. Extraction de la partie numérique d’une chaîne de caractères
2. Retournement d’une séquence de mots
3. Test de la validité d’une adresse e-mail et éclatement en parties
4. Test pour vérifier si les trois premiers caractères d’une chaîne se retrouvent inversés à la fin de la chaîne
5. Conversion (relatif/absolu) de la énième référence dans une formule
6. Test de la validité d’un format numérique
7. Elimination des doublons dans une chaîne de caractères

Remarque – Dans le cas de la feuille n°6, ce sont bien entendu les critères de formats numériques américains qui sont appliqués. Il vous restera à adapter la macro pour les formats numériques français, par exemple la « , » à la place du point, ou l’espace à la place de la « , »…

14 mai 2010

Extraction des chiffres seuls

Nous disposons en colonne A d’une série de codes comportant des chiffres et des lettres. Notre objectif est d’obtenir en colonne B l’extraction des chiffres uniquement.

La solution que nous proposons est la fonction personnalisée Chiffres, dont le code est reproduit dans la fenêtre de Visual Basic ci-dessous. En B2, nous avons simplement entré la formule =chiffres(A2).

Remarque – Si nous voulions obtenir un résultat sous la forme de chaîne de caractères plutôt que comme valeur numérique, il suffirait d’ôter ci-dessus la partie « 1 * » de la dernière instruction.

10 mai 2010

Tricher avec un graphe 2007-2010

Devant le tollé suscité par la disparition de la fonctionnalité (cf. les deux articles précédents) qui permettait de directement modifier la valeur d’un point d’une série graphique, Microsoft a décidé de « réparer les dégâts ».

En effet, même si, sur le plan éthique, il peut sembler malsain de pouvoir modifier un graphe en tirant sur un point d’une série, cela peut se révéler réellement utile – sans qu’il n’y ait nécessairement de problème éthique associé – dans certaines situations pratiques.

Vous trouverez, dans le site dons l’adresse Internet est fournie ci-dessous, le complément « Excel Add-In for Manipulating Points on Charts (MPOC) » proposé par Microsoft.

blogs.msdn.com/excel/archive/2009/11/02/excel-add-in-for-manipulating-points-on-charts-mpoc.aspx

Ce complément fonctionne bien. Il ajoute (cf. copie d'écran ci-dessous) un bouton « Manipulate Points » dans l’onglet « Disposition » de l’onglet « Outils de graphique ».

Quand on clique sur ce bouton, le dialogue affiché ci-dessous apparaît :

Remarque 1 – Dès que l’on modifie un élément dans le dialogue, la modification s’effectue aussitôt dans le tableau et dans le graphe. Il faut simplement fermer le dialogue quand on a terminé. C’est un peu déroutant comme mode d’emploi : normalement, les modifications effectuées dans un dialogue ne s’opèrent qu’à la fermeture du dialogue via un bouton « OK ».

Remarque 2 – On peut regretter que le second menu déroulant ne reconnaisse pas les étiquettes des points et qu’il affiche ainsi « Point 2 » au lieu de « Export autre ». Cela peut se révéler délicat quand il y a de nombreux points.

06 mai 2010

Activation de la valeur cible

Reprenons l’exemple présenté il y a quatre jours.

Supposons à présent que la cellule C3 contienne la formule =10*F1 et que F1 contienne la valeur 30, ce qui nous donne le même résultat de 300 que dans la copie d’écran antérieure.

Si nous lâchons maintenant la souris après avoir « monté » la valeur du point à 640, cela déclenche automatiquement la valeur cible d’Excel, comme nous le voyons dans la copie d’écran ci-dessous :

Si nous identifions F1 comme « Cellule à modifier » puis validons, Excel converge immédiatement en plaçant la valeur 64 en F1, et le tour est joué !

Remarque – Bien évidemment, si nous identifions n’importe quelle autre cellule comme « Cellule à modifier », Excel sera dans l’impossibilité de converger !

02 mai 2010

Comment tricher dans un graphe…

Une fonctionnalité tout à fait originale d’Excel est la possibilité de modifier directement sur un graphe la valeur d’un point donné de la série active.

Dans le graphe ci-dessous, cliquons dans l'une des quatre barres de la série
« Pentium B » : la série entière est alors sélectionnée, comme cela se voit par les carrés apparaissant dans ce cas au milieu de chaque barre de son histogramme.

Cliquons ensuite dans la seconde valeur de la série, celle de la série « Export autre ». Seule la barre correspondante est alors sélectionnée, ce qui nous permettrait par exemple de mettre cette barre, et elle seule, dans une couleur spécifique.

Appuyons à présent avec la souris sur le haut de la barre et tirons vers le haut. Comme le montre la copie d’écran ci-dessous, la valeur augmente aussitôt. Si nous lâchons alors la souris, la barre monte à 640 et la valeur de C3 devient 640 !

Remarque 1 – Bien entendu, cela ne fonctionne directement que si la cellule C3 contient une constante !

Remarque 2 – Cette fonctionnalité géniale a hélas disparu d’Excel 2007 et 2010 mais – heureusement ! – je vous expliquerai dans un article prochain comment résoudre ce problème.