Quelques livres sur Excel

Les livres présentés ci-dessous proviennent tous des Editions ENI. Pour chacun de ces ouvrages, il est possible de télécharger les exemples du livre depuis le site des Editions ENI. Donc, outre l’aspect pédagogique de ces livres, vous avez la possibilité de récupérer des applications déjà développées.

Calculs mathématiques, statistiques et financiers avec Excel 2010 – Claude Duigou – 227 pages – 21,95 €.

Les lecteurs de ce blog connaissent bien Claude, le lecteur du blog ayant produit le plus grand nombre de commentaires depuis sa création en 2005. Autant dire qu’il est un excellent spécialiste d’Excel ! Le premier quart du livre est une présentation des fonctionnalités d’Excel, le reste fournit de multiples exemples : trigonométrie, nombres complexes, calcul matriciel, calculs financiers, statistiques, graphiques, scénarios, et enifn un peu de VBA.

Gestion comptable avec Excel – Faïza Moumen Piasco – 254 pages – 21,95 €.

Après une introduction à des notions de base sur Excel en une vingtaine de pages, on passe tout de suite à la réalisation guidée de plusieurs applications complètes, expliquées en détail : Note de frais automatisée, Suivi des factures clients en devises, Suivi des recettes journalières et Gestion des immobilisations. Chaque chapitre est l’occasion de découvrir des fonctions et fonctionnalités particulières d’Excel.

Apprenez à gérer le temps – Faïza Moumen Piasco – 224 pages – 21,95 €.

Après une introduction à des notions de base sur Excel en une vingtaine de pages et un chapitre sur la notion de temps dans Excel, on passe tout de suite à la réalisation guidée de plusieurs applications complètes, expliquées en détail : Ossature d’un planning annuel, Gestion des absences, Gestion des échéances, Gestion des heures supplémentaires, Calculette dates de livraison. Chaque chapitre est l’occasion de découvrir des fonctions et fonctionnalités particulières d’Excel.

Microsoft Excel 2013 – Pas de nom d’auteur – 494 pages – 17,90 €.

L’auteur a-t-il eu honte de devoir rédiger 494 pages pour un livre vendu moins cher que ceux de 200 et quelques pages ? Toujours est-il que cet ouvrage n’est pas signé…

Il s’agit d’un mode d’emploi d’Excel 2013 en 13 parties : L’environnement Excel 2013, La gestion des fichiers, La saisie & modification des données, Les feuilles de calcul, Les calculs, Les outils d’analyse, La présentation des données, La réorganisation des données, L’impression des données, Les graphiques, Les objets graphiques, Les tableaux de données et TCD, Le travail collaboratif, et Les fonctions avancées diverses.

Excel 2010 Expert – Guide de préparation à l’examen Microsoft Office Specialist – 348 pages – 24,90 €.

Encore un auteur anonyme, mais ceci est plus compréhensible vu qu’il s’agit d’un ouvrage de préparation à un examen de qualification Microsoft.  Avec le livre est offert un examen blanc en ligne.

La particularité de cet ouvrage est que chaque chapitre se termine par une série d’exercices, suivie par les corrigés de ces exercices. On peut donc facilement tester la progression de ses connaissances.

En tant qu’expert Excel, je suis un peu perplexe quand je vois ce qu’il faut savoir pour passer cet examen, et l’écart qu’il y a entre cela et ce qui est pour moi une bonne modélisation Excel. L’examen prévoit par exemple que l’on soit à l’aise avec le format XML, ou que l’on sache faire un certain nombre de choses avec les propriétés d’un classeur, ce qui – selon mon expérience – a peu d’utilité par rapport aux besoins de la grande majorité des utilisateurs d’Excel. En revanche, il ne demande pas que l’on sache définir un nom élastique – ce qui est fondamental pour des modèles professionnels – ni définir un titre dynamique pour un graphe, ou réaliser un graphe élastique, toutes choses fort utiles dans la pratique…

Le 1er samedi en début de mois

Une lectrice du blog m’a posé la question suivante : « Comment puis-je identifier le premier mois de l’année qui commence par un samedi ? »

Essayez donc de voir si vous trouvez la réponse avant de lire cet article…

Dans le tableau ci-dessous, nous avons entré une formule en D2, reproduite dans tout le rectangle. Cette formule est : =joursem(date(D$1;ligne()-1;1)).

Elle calcule, pour chaque mois de chaque année, le numéro (dans la semaine) du premier jour de ce mois. La fonction joursem() renvoie de 1 à 7 pour dimanche à samedi.

Nous avons mis en B1 une zone de validation par liste basée sur la référence =$D$1 :$L$1.

La solution en B2 utilise la formule =equiv(7;joursem(date(B1;ligne(1:12);1));0).

Cette formule est une formule matricielle, donc validée avec [Ctrl]-[Maj]- [Entrée].

La formule date(B1;ligne(1:12);1) est évaluée en {40909;40940;40969;41000;41030; 41061;41091;41122;41153;41183;41214;41244}. Elle donne les valeurs des dates du premier jour de chaque mois de l’année B1.

Rappelons que ces dates sont le nombre de jours écoulés depuis la naissance de la grand-mère de Bill Gates, cf. l’article  « La grand-mère de Bill Gates » du 14 décembre 2005.

La fonction equiv(7,…) trouve la position du premier jour de semaine en « 7 » dans cette liste, donc la position du premier mois de l’année démarrant par un samedi. CQFD…

Le tableau de D en L permet simplement de vérifier que l'on obtient bien le bon résultat pour chaque année de  2006 à 2014, en modifiant la valeur de B1...

Simulation avec Crystal Ball

Le modèle de lancement de dés que nous avons développé dans les derniers articles nous a montré comment monter une simulation stochastique avec Excel. Il nous a aussi permis de découvrir les pièges liés au mode de calcul d’Excel.

Même pour un habitué d’Excel, la construction de ce très simple modèle de simulation demandera entre 15 et 30 minutes, macros comprises. Et il lui aura fallu bien connaître Excel, et son mode de calcul, pour déjouer tous les pièges.

Avec un add-in de simulation probabiliste, la construction d’un tel modèle prendra moins d’une minute et – en prime – vous donnera accès à de multiples autres résultats : statistiques, graphes,…

Il y a une dizaine d’add-ins de simulation probabiliste sur le marché, depuis les add-ins gratuits jusqu’aux deux leaders du marché, Crystal Ball et @risk. Pour de nombreuses raisons, expliquées dans un document que je peux vous mailer si vous me le demandez, Crystal Ball est clairement le meilleur des deux.

C'est donc Crystal Ball, que je distribue depuis plus de 20 ans, et dont j’ai francisé plusieurs versions. C'est aujourd'hui l'outil le plus utilisé au monde pour faire des simulations probabilistes.

Exemple d'utilisation de Crystal Ball 

Nous voyons, dans la copie d’écran ci-dessous, le modèle très simple réalisé avec Crystal Ball. On entre 0 en A1, et la formule =A1 en A2. On définit l’hypothèse A1 comme une loi discrète uniforme allant de 1 à 6, on  définit A2 comme prévision – il suffit pour cela de lui donner le nom « Dé », et on lance la simulation en cliquant dans le bouton « Start ».

En 0,37 seconde, on a effectué 10.000 itérations, on a des grahiques et des statistiques détaillées permettant d’analyser ces résultats en détail.

En conclusion, il a fallu moins d’une minute au total pour créer le modèle, simuler 10.000 lancements de dés, et obtenir tous les résultats graphiques et statistiques.

Avec Crystal Ball, j’ai développé plus de 100 modèles de simulation probabiliste pour des entreprises, dont voici quelques exemples :

Aéroports de Paris : Simulation du système de tri des bagages de Roissy

Aéroports de Paris : Simulation du déplacement des piétons dans une aérogare

Arianespace : Simulation des conséquences des pannes envisageables sur les 30 lanceurs Ariane

Caisse des dépôts : Simulation d’un gros projet en équipements routiers

Electricité de France : Simulation des incidents possibles sur la durée de vie d’une centrale

Sociétés de péage : Simulation d’une gare de péage et des fils d’attente à chaque porte

Sociétés de venture capital : Business plans pour des start-ups

Pour en savoir plus sur la simuation probabiliste, je ne peux que vous conseiller le livre « La modélisation du risque », que j’ai publié aux Editions d’Organisation. Il est normalement vendu – avec tous les fichiers du livre, donc de nombreux modèles Excel intéressants – à 45 €.

Si cela vous intéresse, contactez-moi à : thiriez@hec.fr.

Formations à Crystal Ball

Pour ceux que cela intéresse, j'organise bientôt à Paris une formation « La modélisation du risque avec Crystal Ball » le mardi 23 avril et une formation « Utilisation de Crystal Ball Pro » le mercredi 24 avril.

Pour avoir le programme détaillé et/ou vous inscrire, contactez-moi à : thiriez@hec.fr.

Une macro pour la simulation...

Il est un peu fastidieux de devoir modifier à la main, chaque fois que l’on souhaite relancer une simulation ou revenir en mode normal, le statut de calcul : itératif ou pas, automatique ou non, …

D’autant plus que la commande « Options Excel » n’est pas d’un accès immédiat…

Avec les deux macros représentées ci-dessous, nous pouvons automatiser le processus : réinitialiser ou lancer la simulation.

Il suffit à présent d’un clic sur le bouton adéquat pour effectuer l’opération désirée.

Une simulation enfin sans défaut

Dans l’article précédent, nous expliquions qu’Excel avait la particularité – lors du recalcul d’un classeur – de n’effectuer que les calculs qui, selon lui, étaient nécessaires.

Il y a une autre chose fondamentale, concernant le calcul par Excel, qu’il faut savoir. C’est qu’Excel ne peut pas calculer toutes les cellules à la fois, il faut bien qu’il procède par ordre. Et cet ordre est le suivant :

- Excel calcule ligne par ligne, du haut vers le bas ;

- dans chaque ligne, Excel calcule de gauche à droite.

Bien entendu, quand les formules l’y obligent, Excel est un peu obligé d’aller voir ailleurs pour les évaluer, mais cela lui complique la tâche.

Ceci explique qu’un modèle où chaque formule ne dépend que de cellules placée au-dessous d’elle et/ou à sa gauche se calcule plus vite qu’un modèle où cette règle n’est pas respectée.

Reprenons notre dernier modèle :

Quand nous débutons le calcul de l’itération n°10 en calculant la ligne n°1, toute la partie du modèle ci-dessus placée en dessous, c’est-à-dire l’ensemble des lignes 2 à 7, se trouve encore en l’état de l’itération n°9. Donc le compteur en B3 est toujours à 9. C’est pour cela que le pourcentage en F1 – et celui en F2, mais on ne le voit pas ici car il est nul – est obtenu en divisant la valeur de la colonne E par 9.

Quand le calcul passe à la ligne n°3, B3 passe à 10 et les pourcentages de F3 à F6 sont tous justes.

Quand le calcul passe à la ligne n°4, B4 passe à 2, mais ce dé est ignoré car le test en E2 pour vérifier si l’on avait obtenu un 2 a déjà été calculé. On perd donc le dixième dé quand celui-ci est un 1, un 2 ou un 3 car, dans ces cas, le dixième test en E1, E2 ou E3 ne peut s’effectuer.

Le remède est donc tout simplement de déplacer le bloc D1:F7 de façon que, désormais, tous les calculs s’effectuent bien du haut vers le bas, et de gauche à droite.

Voici ce que l’on aurait obtenu, avec les tirages précédents, si l’on avait fait cela :

Remarque – La morale de l’histoire est que, même si Excel – avec la fonction alea() et le calcul itératif – vous permet de créer un modèle de simulation stochastique (joli mot pour dire aléatoire), vous devez prendre un certain nombre de précautions pour être sûr que ce modèle obtienne des résultats exacts !

Comment Excel recalcule-t-il ?

La clef du problème rencontré dans l’article précédent est la façon dont Excel procède lors d’un recalcul.

La formule de B4, celle qui tire le dé, est : =SI(B2=0;"";ENT(6*ALEA()+1)).

Pour éviter de perdre son temps, Excel essaye – lors d’un calcul itératif – de ne calculer que les cellules qui – selon lui – ont besoin d’être recalculées.

Quand il se trouve au début de l’itération n°2, Excel analyse la formule et raisonne ainsi :

- B2 n’a pas changé depuis l’itération n°1…

- le reste de la formule ne dépend d’aucune cellule, et donc n’a pas changé non plus

- en conséquence, il n’y a aucune raison de se fatiguer à recalculer la formule !

Le remède est simple ! Pour que le dé se recalcule à chaque itération, il ne faut pas qu’il dépende de B2 mais plutôt d’une cellule qui – elle – change à chaque itération. Ce qui, heureusement pour nous, est le cas du compteur d’itérations :)

Et voilà la solution ! Changeons maintenant la formule de B4 en =SI(B3=0;"";ENT(6*ALEA()+1)).

Réinitialisons le tableau en mettant le drapeau à 0 et en faisant [F9]. Puis remettons le drapeau à 1 et lançons la simulation. Nous obtenons alors – par exemple – le tableau suivant :

Bon, il y a un net progrès, nous ne sommes plus en train de jouer avec un mafieux et son dé pipé ! Mais il reste quand même deux problèmes :

- pourquoi les % en F1:F6 ne sont-ils pas tout des multiples de 10 % ?

- le dernier dé – un 2 – n’a pas été pris en compte !

Nous vous donnerons les réponses à ces questions, et la parade à ces problèmes, dans le prochain article.

Simuler 10 lancers de dé

Nous nous attaquons aujourd’hui, forts de notre nouvelle expérience en gestion du calcul itératif – cf. les trois derniers articles – dans la simulation de 10 lancements d’un dé. Nous avons construit à cet effet le joli modèle suivant :

Voici les formules de ce modèle :

B3 : =SI(B2=0;0;B3+1)

B4 : =SI(B2=0;"";ENT(6*ALEA()+1))

E1 (recopiée jusqu’en E6) : =SI($B$2=0;0;E1+SI($B$4=D1;1;0))

F1 (recopiée jusqu’en F6) : =E1/$B$3

E7 (recopiée en F7) : =SOMME(E1:E6)

Nous sommes pour le présent en calcul automatique, comme c’est le cas par défaut dans Excel.

Si tout se passe bien, nous pourrons ensuite nous mettre en calcul manuel, avec un maximum de 10 itérations, entrer 1 en B2, puis faire [F9] pour lancer la simulation des 10 dés.

Eh bien, si vous le faites, vous allez avoir la surprise de votre vie !

Voici donc un exemple de résultat obtenu après une telle simulation :

Manifestement, nous devons être en train de jouer avec un truand qui a un dé pipé !

Dans le prochain article, nous découvrirons d’où vient le problème, puis comment le résoudre…