Monsieur Excel
Pour tout savoir faire sur Excel !

26 juillet 2015

Paramétrer une référence circulaire

Cet article fait la suite de notre présentation des références circulaires dans le dernier article.

Quand on active, en cochant « Activer le calcul itératif » dans la commande Fichier – Options – Formules, les références circulaires, on a accès à deux réglages, le nombre maximal d’itérations et l’écart maximal.

Le nombre maximal d’itérations est le nombre d’itérations qui pourront être effectuées lors d’un calcul de la feuille. Fixons-le à « 1 » pour l’instant. Cette commande n’a donc de sens que si l’on a sélectionné l'option « Activer le calcul itératif » comme dans la copie d’écran ci-dessous :


Remarque – Ce serait bien plus clair si les commandes « Nb maximal d’itérations » et « Ecart maximal » étaient grisées tant que l’on n’a pas coché la case « Activer le calcul itératif ».

Effaçons à présent la formule de la cellule B4, lançons un calcul avec [F9] pour que la cellule B3 retrouve sa valeur de 0, puis ré-entrons la formule de sommation en B4.

Le prix de vente est maintenant de 100 € puisque seule la cellule B4 a pu être calculée, au moment où sa formule a été validée :


Lançons un calcul avec [F9] et nous constatons que B3 prend à présent une valeur de 30,00 € et B4 une valeur de 130,00 €.

Lançons un nouveau calcul avec [F9], et nous obtenons 39,00 € en B3 et 139,00 € en B4.

Lançons encore un calcul avec [F9], et nous obtenons 41,70 € en B3 et 141,70 € en B4.

Avec cinq calculs de plus, le résultat final avec 42,86 € en B3 et 142,86 € en B4 est enfin obtenu...

En fait, Excel arrête le calcul dès que soit le nombre maximal d’itérations a été atteint, soit l’écart maximal entre le nème calcul et le (n+1)ème calcul – pour les valeurs de toutes les cellules du classeur – est inférieur à l’écart maximal défini.

21 juillet 2015

Création d’une référence circulaire

Réalisons un petit modèle dans lequel nous entrons un prix d’achat en B2, nous déclarons en B3 que nous allons faire une marge de 30% du prix de vente et, en B4, nous demandons la somme grâce à l’outil de sommation.

Et patatras ! Excel nous annonce qu’il ne peut pas calculer la formule car il y a une référence circulaire :


Il s’agit d’une sécurité normale car, le plus souvent, les références circulaires sont tout à fait involontaires, il s’agit alors d’erreurs de conception qu’Excel signale avec raison.

Il se peut cependant que – c’est d’ailleurs le cas ici – qu’il ne s’agisse pas d’une erreur mais que nous souhaitions effectivement calculer un résultat par convergence, à travers des itérations successives. C’est par exemple indispensable si vous construisez un modèle de calcul d’ISF (Impôt Sur la Fortune) : en effet, dans ce cas, on peut déduire, pour calculer la base imposable, le montant de l’ISF à payer qui dépend de cette même base imposable ! Encore une invention d’énarque...

Pour valider le calcul en références circulaires, il vous suffit d’appeler la commande Fichier – Options – Formules, et de cocher l'option « Activer le calcul itératif », comme nous le voyons ci-dessous.


Le résultat final de 142,86 € apparaît alors aussitôt dans la cellule B4 !

Et voilà, ce n’est pas plus compliqué que cela...

Nous verrons bientôt comment tirer profit des deux réglages suivants, le Nb  maximal d’itérations et l’Ecart maximal.

16 juillet 2015

Je veux réussir mon examen (d)

Dans les trois articles précédents, nous avons découvert trois façons totalement différentes de résoudre le problème d’examen présenté dans l’article du 30 juin : par la statistique, par l’analyse combinatoire, et enfin par simulation.

On peut se poser la question de savoir si l’une de ces trois approches est préférable.

Pour le temps de développement, quand on maîtrise bien ces trois approches – ainsi bien entendu qu’Excel lui-même – il n’y a pas une grande différence. Dans chacun des trois cas, il m’a fallu moins de dix minutes pour développer le modèle.

En terme de précision, les deux premières approches donnent un résultat parfaitement exact. Dans le troisième cas, avec 1.000 itérations, la probabilité de succès avec 6 chapitres révisés oscille le plus souvent entre 90% et 94,5%, mais il m’est arrivé de tomber entre 89% et 90%. Ceci dit, il suffirait de passer à 10.000 lignes pour réduire sensiblement les variations.

En terme de flexibilité, la simulation est de loin la meilleure solution.

Supposons que l’on introduise une nouvelle contrainte, par exemple que l’on ne puisse comprendre le chapitre 8 que si l’on a préalablement révisé le chapitre 3. Avec les deux premières approches, il serait très compliqué (ou impossible) d’intégrer une telle contrainte. Dans le cas de la simulation, la prise en compte d’une contrainte de ce genre est non seulement possible, mais aussi nettement plus simple.

Bon, j’en entends qui ricanent au fond de la salle en se demandant si cela est aussi simple que je le dis. Voilà donc une excellente occasion pour vous de voir si vous y parvenez. Ceux d’entre vous qui y parviendront sont clairement dans le top 5% – et même probablement le top 1% – des utilisateurs d’Excel. Ne lisez donc la suite de cet article qu’après avoir essayé de résoudre ce problème…

Pas de révision du chapitre 8 sans le chapitre 3…

Voici la solution que je vous propose. Le plus simple est de modifier l’ordre des chapitres en mettant les chapitres 3 et 8 en haut de la liste.

J’ai ensuite modifié la formule de D2 :
=si(et(E1=1;C2>grande.valeur($C$1:$C$13;7));1;"")

Puis ajouté une formule en F2 :
=si(et(E1=0;C2>grande.valeur($C$1:$C$13;7));1;0)

Et enfin modifié la formule de D3 (et suivantes) :
=si(C3>grande.valeur($C$1:$C$13;7+$F$2);1;"")

 Et voici le résultat, dans lequel on constate qu’il faut à présent réviser 7 chapitres au lieu de 6 pour avoir au moins 90% de chances de réussite :


Remarque – On constate que le chapitre 8, qui devrait être tiré vu son grand aléa en C2, b'a pas été pris car le chapitre 3 n'était pas dans les élus...

Remarque pour les pros – Si j’ai placé les chapitres 3 et 8 en haut de la liste, c’est pour tenir compte du fait qu’Excel calcule ligne par ligne…

11 juillet 2015

Je veux réussir mon examen (c)

Dans les deux articles précédents, nous avons résolu le problème de l’examen que devait passer Eléonore, la première fois avec une solution purement statistique, la seconde fois en utilisant l’analyse combinatoire.

Solution par simulation

Aujourd’hui, nous utilisons une troisième solution, totalement différente, la simulation.

Dans le bloc A1:D13, nous identifions dans la colonne B les 6 chapitres (parmi les 13 chapitres du programme) qui ont été sélectionnés pour l’examen. Pour cela, nous avons utilisé les formules suivantes, reproduites vers le bas :

B1 : =alea()
C1 : =si(B1>grande.valeur($B$1:$B$13;7);1;"")
D1 : =somme($C$1:C1)

Les chapitres gagnants sont les 6 chapitres avec les plus grandes valeurs aléatoires.

En colonne D, dans la ligne n, nous avons le nombre de bonnes réponses quand on a révisé n chapitres.

En F2, recopiée vers la droite, la formule =INDEX($D$1:$D$13;COLONNE()-4).
Ce qui revient tout simplement à transposer les résultats de la colonne D.

Nous avons ensuite sélectionné le bloc E2:L1001 puis créé une table en mettant le paramètre de colonne en E1.


Il s’agit là d’un détournement très astucieux du principe des tables. On met le paramètre de table (un vecteur vertical vide) dans une cellule qui ne sert à rien, mais cela force Excel à calculer 1.000 fois les résultats de la colonne D. On amène ainsi Excel, sans la moindre ligne de VBA, à effectuer une boucle de calcul.

A chaque appui sur la touche [F9], nous lançons ainsi 1.000 itérations, et nous constatons – comme avec les deux méthodes précédentes – qu’il faut réviser 6 chapitres pour avoir au moins 90% de chances de réussir l’examen.

05 juillet 2015

Je veux réussir mon examen (b)

Dans l’article précédent, nous avons posé le problème de l’examen que devait passer Eléonore, et nous avons résolu ce problème en utilisant la loi hypergéométrique.

Aujourd’hui, nous voyons comment ce problème peut être résolu d’une autre façon, par analyse combinatoire.

Solution par analyse combinatoire

L’analyse combinatoire consiste à effectuer le décompte des cas possibles, puis celui des cas favorables. La probabilité de réussite est alors le rapport de  ces deux résultats.

Nous avons 13 chapitres, dont 6 sont tirés pour l’examen. Le nombre de combinaisons de « k parmi n » est :
n! / k!(n-k)!, soit dans notre cas 13! / (6! * 7!).

Il y a donc 1716 combinaisons différentes possibles à l’examen.

Nous pourrions entrer à cet effet la formule =fact(13)/fact(6)/fact(7), mais le résultat s’obtient plus simplement encore avec la fonction créée précisément dans Excel à cet effet : =combin(13;6).

Pour trouver le nombre de combinaisons avec au moins 2 chapitres révisés, nous allons passer par le complémentaire : toutes les combinaisons, moins celles avec 0 ou 1 chapitre révisé.

Appelons r le nombre de chapitres révisés.

Nombre de tirages avec aucun chapitre révisé : c’est le nombre de tirages de 6 chapitres parmi les 13-r non révisés : (13-r)! / 6!(13-r-6)! C’est la formule =COMBIN(13-C8;6) en B9.

Pour un chapitre donné révisé : nombre de tirages de 5 chapitres parmi les 13-r non révisés : (13-r)! / 5!(13-r-5)!

Ceci est valable pour les r chapitres révisés, donc les combinaisons se s’élèvent à : r * (13-r)! / 5!(13-r-5)!

Cela nous donne la formule =C8*COMBIN(13-C8;5) en B10 dans Excel.

On crée ensuite une table en E1:G10 avec la formule =1-loi.hypergeometrique.n(C1;C2;C3;C4;C5) en F1 et la formule =C11 en G1, en mettant l’argument en colonne en C2.


Quand on fait cela, on obtient des erreurs en G9 et G10, ce qui est normal. Pour les éliminer, il faut remplacer les formules de C9 et C10 par =sierreur(formule;0)

Ce qui est intéressant avec ce second article sur l’examen, c’est que nous trouvons exactement les mêmes résultats avec une approche totalement différente.