Un graphe pour plusieurs feuilles

Nous avons vu il y a quatre jours comment se débrouiller pour avoir le même graphe sur plusieurs feuilles.

Une autre solution, nettement moins gourmande en mémoire, consiste à créer une feuille de synthèse dans laquelle on regrouperait tous les graphes utiles, et où une cellule permettrait de sélectionner la feuille dont on souhaite afficher les graphes :

Nous avons ainsi mis une validation de données en D2, avec comme choix la liste des noms des feuilles. Dans la colonne B, nous récupérons grâce à la formule affichée en commentaire, les données de la feuille sélectionnée en D2. Le graphe fait bien référence aux données de la feuille sélectionnée.

La fonction Indirect() que nous utilisons ici est bien pratique quand on souhaite modifier rapidement le nom de la feuille ou du classeur dont proviennent les données à analyser.

Un graphe sur plusieurs feuilles

Un lecteur me demandait récemment s’il y avait un moyen de créer dans une feuille un graphe dépendant de la feuille, de façon que – si on le copie dans une autre feuille – il devienne dépendant des cellules placées à la même adresse dans la seconde feuille.

Je ne connais pas pour le moment de réponse à cette question. Peut-être un lecteur pourra-t-il nous faire une suggestion ?

Voici en tout cas une réponse indirecte… Vous souhaitez disposer de plusieurs feuilles similaires – mêmes intitulés de lignes et de colonnes –, avec par exemple une feuille par produit, ou par famille de produits, ou par région, ou par salarié…

Créez donc la première feuille et ajoutez-y tout ce dont vous aurez besoin par la suite, y compris des graphes. Une fois votre modèle de feuille terminé, dupliquez cette feuille autant de fois que nécessaire : vous constaterez aisément que, dans chaque feuille, les graphes font alors appel aux cellules de la feuille où ils sont logés.

Remarque – Si vous avez déjà sous la main les autres feuilles avec leurs données, vous pouvez faire la même chose puis copier (ne coupez surtout pas !) tout le contenu de chaque feuille ancienne pour le coller sur la feuille nouvelle associée, et enfin détruire la feuille ancienne. Bien entendu, cela suppose qu’il n’y ait pas de références croisées entre les feuilles, mais c’est en général le cas pour de tels modèles.

Comment Excel calcule-t-il ?

Dans les deux derniers messages, nous avons vu que la façon dont Excel calcule peut avoir un impact significatif sur les résultats de la feuille de calcul, surtout quand on a recours au calcul itératif. Voici donc quelques remarques sur la façon dont Excel calcule…

Lisez les deux messages précédents avant celui-ci, qui ne fait que les compléter...

L’algorithme de calcul d’Excel a été complètement réécrit pour Excel 97, et d’autres modifications ont été effectuées sur la version 2002. Il y a donc deux façons de calculer dans Excel, le recalcul, avec cet algorithme (c’est ce qui se fait par défaut à chaque modification), et le calcul total, qui calcule tout sans chercher à optimiser (donc réduire) le temps de calcul.

Calcul du plus petit nombre de cellules possible

Pour économiser le temps de calcul, Excel ne recalcule que les cellules, formules, valeurs et noms qui ont été modifiés ou qui ont été marquées par l’algorithme comme ayant besoin d’un recalcul, puis les cellules dépendant de ces éléments, puis les cellules dépendant de ces cellules, puis...

Un nom est réévalué chaque fois qu’une formule qui l’utilise est recalculée. Les noms non utilisés ne sont jamais recalculés.

Forcer le calcul total

Si vous voulez forcer un calcul total, qui prendra alors le pas sur l’algorithme de recalcul, il faut utiliser la combinaison [Ctrl]-[Alt]-[F9]. Il se peut que, dans certains cas, le calcul total soit plus rapide que le recalcul…

Le calcul total s’active automatiquement dès que plus de 65.536 zones de cellules dans une feuille ont des cellules dépendantes, et/ou dès qu’il y a plus de 8.000 cellules qui dépendent d’une même zone.

Remarque 1 – Il semblerait que les deux limites décrites dans le paragraphe précédent aient disparu avec la version Excel 2007.

Remarque 2 – Ces remarques sur le calcul ne sont pas anecdotiques. Comme je l’ai déjà dit dans un message antérieur, la structuration efficace d’un modèle peut avoir un impact significatif sur le temps de calcul : j’ai eu de nombreux cas où – chez mes clients – j’ai réussi à réduire significativement le temps de calcul d’un modèle, l’économie allant de 50% à plus de 99% !

Simulation de dés enfin résolue

Nous continuons l’analyse de la simulation du lancement de dés entamée il y a 8 jours. En lançant la simulation après notre dernière modification, on peut obtenir un résultat tel que le suivant :


Il y avait deux problèmes à résoudre. Pourquoi arrive-t-il que l’on perde un dé comme ici où il n’y en a que neuf ? En fait, cela arrive une fois sur deux en moyenne. Et pourquoi le total en F7 ne parvient-il pratiquement jamais au 100 % attendu normalement ?

Pour comprendre ce problème, il faut savoir qu’Excel calcule ligne par ligne, en partant du haut et – dans chaque ligne – colonne par colonne, en partant de la gauche.

Quand nous calculons l’itération n°10 dans la première ligne, les lignes 2 à 7 en sont donc encore à l’itération n°9. Et le compteur en B3 est donc toujours à la valeur « 9 », ce qui fait que les pourcentages en F1 et F2 sont le résultat de divisions par 9.

Dès que l’on a calculé le début de la troisième ligne, le compteur passe à 10 et les pourcentages suivants (de F3 à F6) sont donc normaux.

Enfin, et pour la même raison, si le dixième dé est inférieur ou égal à 3, il n’est pas conséquent pas pris en compte par la simulation. Auquel cas, donc une fois sur deux, il manquera un dé au total.

Le remède est simple ! Il suffit de décaler le bloc D1:F7 vers le bas, de façon que tous les calculs s’effectuent dans le bon ordre :

Remarque – Nous avons donc vu, avec cet exemple, que même si l’on peut – grâce à la fonction alea() – faire de la simulation probabiliste avec Excel, ce n’est pas une sinécure. Vous devez en effet gérer, un drapeau, un compteur, des statistiques, et enfin – et ce n’est pas le plus simple – vous assurer que tous les calculs s’effectuent bien du haut vers le bas, et de la gauche vers la droite !

Les dés d’Excel sont pipés !

Voici ce que vous avez peut-être obtenu en créant le modèle que je vous ai décrit dans le dernier message et en cliquant sur le bouton « Simuler » pour lancer la simulation :

Manifestement, Excel utilise des dés pipés puisque nous avons obtenu 10 fois le « 5 ». Pourquoi donc ?

La clé se trouve dans la formule de tirage du dé, en B5 : =si(Drapeau=0;"";ent(6*alea()+1)).

Cette formule tire un nouveau dé si le drapeau n’est pas nul. Or, dans la macro lançant la simulation, nous mettons le drapeau à « 1 » puis nous lançons dix itérations. Le problème vient de ce qu'Excel recalcule seulement – à chaque itération – ce qui lui semble pouvoir changer !

Comme le drapeau n’a pas bougé entre la première itération et la seconde, Excel ne recalcule pas le si() à la seconde itération, ni bien entendu lors des suivantes ! C’est pour cela que l’on obtient 10 fois le même dé…

La solution est toute simple : il suffit de faire dépendre le si() d’une cellule qui change de valeur à chaque itération, par exemple le compteur en B3, avec la formule : =si(B3=0;"";ent(6*alea()+1)).

Vous obtenez alors un résultat tel que le suivant :

Certes, il y a du progrès, mais nous sommes encore loin du compte avec le dixième dé perdu et une fréquence cumulée de 91 % au lieu de 100 %. Nous verrons dans le prochain message comment résoudre ces deux problèmes résiduels…

Simuler le lancement de 10 dés

Avant de lire cet article, nous vous conseillons de (re)lire les messages suivants :
● "Création d’une référence circulaire", le 27 avril 2006
● "Paramétrage d’une référence circulaire", le 30 avril 2006
● "Calculer le nombre d’itérations", le 3 mai 2006

En effet, nous allons vous montrer aujourd’hui comment simuler avec Excel une série de 10 lancements de dés, et cet article serait trop long si nous devions répéter le contenu de ces trois messages.

La copie d’écran ci-dessous décrit notre modèle. Dans le drapeau (en B2), nous entrons 1 pour lancer la simulation et 0 pour réinitialiser celle-ci :

Les deux macros commandées par les boutons servent à lancer la simulation et à la réinitialiser. Pour la lancer, en effet, il ne suffit pas d’entrer « 1 » en B2, il faut aussi se mettre en calcul manuel, puis en calcul itératif, et ce pour 10 itérations :

Nous vous laissons découvrir les conséquences d’un clic sur le bouton
« Simulation »…

Nous commenterons ces conséquences dans notre prochain message.

Lancer une simulation probabiliste

Dans mon dernier message, je vous ai montré comment tirer au hasard des événements – dans ce cas, un résultat entier de 1 à 49 – selon leurs probabilités respectives. Et je vous ai dit que c’était le point de départ pour réaliser une simulation probabiliste.

Aujourd’hui, nous allons créer une macro pour utiliser le modèle précédent et simuler le tirage de 10.000 numéros du loto. Nous pourrons ainsi vérifier l’écart entre les probabilités théoriques (en colonne B) et les fréquences obtenues (en colonne D). Voici le tableau obtenu à la fin de la simulation :

La macro permettant d’obtenir ce résultat est assez simple. Elle commence par remettre à 0 la colonne D, puis elle lance 10.000 itérations en ajoutant à chaque fois 1/10.000ème dans la ligne dont on a tiré le numéro. Nous avons donné le nom « Numéro » à la cellule F2.