Monsieur Excel
Pour tout savoir faire sur Excel !

30 juin 2015

Je veux réussir mon examen (a)

L’énigme ci-dessous a été posée par Euro Décision, mon partenaire pour plusieurs des formations que j’anime en entreprise. J’ai un peu modifié le texte original pour le simplifier et le rendre plus clair encore.
  
Eléonore a des examens à passer. Comme elle a pris du retard dans ses révisions, elle aimerait minimiser le nombre de chapitres à apprendre par cœur.

Il y aura 6 questions à l’examen, chacune portant sur un chapitre différent pris parmi les 13 chapitres au programme. Les candidats devront réussir à répondre à au moins 2 de ces questions.

Combien de chapitres Eléonore devra-t-elle réviser pour avoir au moins 9 chances sur 10 de fournir au moins 2 bonnes réponses ?

Solution par les statistiques

Ce type de problème, dans les cours de statistiques, est appelé un « tirage sans remise ».

En effet, l’exemple classique est le tirage de boules de couleurs placées dans des urnes, il s’agit alors d’évaluer la probabilité d’obtenir tant de boules rouges, noires… Dans notre problème, c’est « sans remise » car une boule, après avoir été tirée, n’est pas replacée dans l’urne.

Pour nous ramener au classique des boules, on représente les 13 chapitres par des boules de couleurs. Les chapitres sélectionnés pour l’examen sont représentés par des boules blanches et les autres par des boules noires. Donc 13 boules, 6 blanches et 7 noires.

Soit n le nombre de boules tirées et X le nombre de boules blanches parmi les n tirées, je cherche n pour que P(X ≥ 2) ≥ 90% ou, plus simplement : 1 – P(X=1) – P(X=0) ≥ 0,9.

La loi hypergéométrique (http://fr.wikipedia.org/wiki/Loi_hyperg%C3%A9om%C3%A9trique) donne une formule pour P(X=k), et grâce à Excel, on trouve que pour n = 5, p(X≥2) = 82% et pour n = 6, p(X≥2)= 92%.

La réponse est donc 6 chapitres.


Le tableau ci-dessus montre comment nous avons formulé de problème dans Excel. Le bloc en A1:A5 liste nos paramètres pour la loi hypergéométrique. La cellule C7 indique la probabilité de succès avec le nombre de chapitres révisés inscrit en C2, soit 82% pour 5 chapitres.

En F1, nous avons inscrit la formule =C5 puis nous avons créé une table en prenant C2 comme paramètre en colonne.

Nous constatons que la réponse à l’énigme est bien 6 chapitres à réviser pour avoir au moins 90% de chances de réussite à l’examen.



23 juin 2015

Les différentes versions d’Excel

Des questions sont souvent posées sur les différentes versions d’Excel. 

Voici donc une présentation générale de ces versions. Cliquez sur l'image pour la voir en grand.

La plupart des informations ci-dessous proviennent d’une page du site de John Walkenbach, mais je me suis permis d’aller au-delà de la traduction en ajoutant mon grain de sel :



17 juin 2015

Exemples de formats conditionnels

Pour terminer sur le sujet très riche des formats conditionnels, qui sont pour moi une fonctionnalité majeure d’Excel, voici les références de divers articles illustrant des utilisations de ces formats.

Format conditionnel pour le suivi de licences, le 24 juin 2006

Dans ce premier exemple, on montre comment identifier, pour une série de logiciels, ceux dont la validité de la licence est inférieure à 30 jours, et pour lesquels il faudra donc penser à renouveler la licence à temps.

Format conditionnel pour une valeur, le 26 janvier 2007

Il s’agit dans ce second exemple d’un format conditionnel mettant en relief, dans un bloc de valeurs, les n valeurs les plus grandes et/ou les n valeurs les plus petites.

Un format conditionnel pour identifier les formules, le 12 juillet 2008

Le format conditionnel présenté dans cet article fait immédiatement ressortir toutes les cellules contenant une formule. Certes, on peut déjà sélectionner d’un coup toutes les cellules contenant une formule grâce à la commande Edition – Atteindre – Cellules – Formules. Il est même alors possible, parmi les cellules contenant des formules, de ne garder que celles contenant des nombres, des valeurs logiques, des textes et/ou des erreurs… Mais, avec ce format conditionnel, cela se fait de façon plus durable.

Le format conditionnel d’Excel 2007, les 3 et 7 octobre 2008

La présentation des formats conditionnels d’Excel 2007, ce qui représente un énorme changement par rapport à ce qu’ils étaient avec la version Excel 2003.

Le nouveau format conditionnel, le 6 mars 2012

Diverses remarques sur la modification des formats conditionnels entre la version originale et la version Excel 2007

 Un format conditionnel génial !, le 19 juillet 2012

La mission, dans cet article, est de construire un format conditionnel qui mette de façon alternative en bleu, puis en jaune, et en alternant encore ensuite, chaque groupe de deux (ou plus) noms identiques dans une liste. Non seulement cet article vous propose-t-il la solution, mais c’est un véritable challenge que d’essayer d’y parvenir par vous-même avant de lire la solution.

Nous voyons ci-contre le résultat que nous cherchons à obtenir...

Sauriez-vous créer ce format conditionnel ?


11 juin 2015

Un système expert de diagnostic

Excel 97 est sorti avec les tous premiers formats conditionnels. Assez tôt après cela, je présentais cette nouvelle fonctionnalité lors d’une conférence. A la fin de mon intervention, des participants qui faisaient partie du groupe de recherche d’EdF sur les centrales nucléaires, à Chatou, sont venus me voir.

Ils m’ont expliqué qu’ils avaient le projet de développer en C++ un système expert de diagnostic des tuyaux dans une centrale nucléaire. Pour ces tuyaux, il y a en effet toute une politique de remplacement préventif car les conséquences, en cas de fuite, peuvent être catastrophiques. Et, suite à ma présentation, ils se demandaient s’il était possible de faire avec Excel – en plus rapide et moins cher – ce qu’ils avaient prévu au départ de faire en C++.

Nous nous sommes revus à Chatou et – après une analyse détaillée du cahier des charges – je leur ai confirmé que je pouvais faire cela avec Excel. Ils avaient prévu, pour le faire en C++, six mois de développement et un budget de 500.000 F (environ 76.000 €). Je leur ai fait un devis de six semaines et 150.000 F, donc 3 fois moins cher et 4 fois plus rapidement. Le projet a été réalisé dans le budget et les délais prévus…

Les copies d’écran ci-dessous montrent chacun trois éléments : une partie des paramètres décrivant l’environnement du tuyau étudié, l’impact de ces paramètres sur l’organigramme principal et celui sur l’organigramme associé à ces paramètres.


Il y a trois conséquences finales possibles : « Sensible » pour un tuyau à remplacer préventivement, « Non-sensible » pour un tuyau en bon état et « Expert » pour un tuyau à contrôler à la main. Voici ce qui se passe quand on passe le pH (ligne 3) à 12 :


Pour la petite histoire, la politique EdF à l’époque exigeait que tous les collaborateurs EdF utilisent la même version d’Excel, soit à l’époque Excel 95. Il a fallu obtenir une dérogation spéciale de la part de la direction d’EdF pour avoir le droit d’acheter une licence Excel 97 afin de réaliser et exploiter ce développement.

Pour la petite histoire encore… Il y a trois ans, un jour où je montrais ce modèle à l’un de mes clients, une grande société pharmaceutique, mes interlocuteurs m’ont dit que cela les intéresserait que je développe avec eux, sur le même principe, une application de diagnostic médical. Finalement, qu’est-ce qu’un être humain sinon un ensemble de tuyaux qui, s’ils crèvent, peuvent engendrer de terribles conséquences, tout comme une centrale nucléaire… Nous avons, depuis, réalisé ce système expert de diagnostic médical ensemble.

Si cela vous intéresse, je suis prêt à créer un troisième système expert…

04 juin 2015

Formats conditionnels (c)

Nous illustrons aujourd’hui une façon très efficace d’utiliser les formats conditionnels. Dans l’exemple ci-dessous, nous avons en A1:A5 une liste de produits qui alimente une liste de validation en B10, et en B1:B7 une liste de dates qui alimente une liste de validation en B11.

En B12, nous récupérons à l’aide des fonction index() et equiv() le montant correspondant au produit et à la date, à partir du tableau D1:K6 dans lequel les produits et les dates sont dans un ordre quelconque.

Les deux formats conditionnels reproduits dans la copie d’écran nous permettent de mettre en relief la ligne et la colonne sélectionnées, ainsi que la valeur sélectionnée.


Ce sont des détails comme cela qui donnent à vos modèles Excel un look professionnel…

Remarque – Conformément à ce que j’ai expliqué dans l’article précédent, les très mauvais choix effectués par Microsoft à la sortie d’Excel 2007 font que, contre toute logique, vous devez définir la seconde règle (rouge sur fond bleu) en premier, sinon le format bleu sur fond jaune sera occulté par ce format ! 

Ou alors saisir les deux conditions dans le mauvais ordre, ou alors faire ce qui est indiqué dans le premier commentaire de l'article précédent... En conclusion, vous avez le choix entre trois mauvaises solutions !