Monsieur Excel
Pour tout savoir faire sur Excel !

31 mars 2016

Résolution de problème récursif (c)

Pour résoudre notre problème, trouver la solution avec le plus grand nombre possible de réponses vraies, il y a grosso modo deux approches via le VBA : soit faire une série de boucles avec toutes les réponses possibles pour toutes les questions, soit tirer les réponses au hasard et simuler cela assez de fois pour être quasiment sûr de trouver la meilleure réponse.

Il faut tout d’abord calculer le nombre de réponses possibles, ce que nous avons calculé en A19 avec la formule : ="Il y a "&texte(4^8;"# ##0")&" combinaisons possibles !". Nous avons donc 65.536 réponses possibles, ce qui signifie qu’avec 100.000 itérations nous avons de fortes chances de trouver la solution la meilleure. A ce jour, je l'ai toujours trouvée avant la fin des 100.000 itérations !


J’ai donc ajouté une colonne B pour tirer les réponses au hasard, avec la formule =H1+ent(4*alea()) en B1, reproduite ensuite vers le bas. En C1, la formule =index(val;equiv(B1;H1:K1;0)), elle aussi tirée vers le bas, calcule la réponse correspondante, Val étant le nom du bloc H9:K9.

Tout cela fonctionne très bien et l’on découvre la solution optimale, qui est de 7.

Dans mon prochain blog, je vous livrerai le code de la macro...

26 mars 2016

Résolution de problème récursif (b)

Dans l’article précédent, nous avons construit le tableau de départ et nous vous demandions quelles formules utiliser pour évaluer la qualité des réponses aux questions.

C’est ce que nous avons fait avec le tableau reproduit ci-dessous, dans lequel nous avons légèrement modifié la place des colonnes utilisées. 


Vos réponses sont entrées en colonne C et la réponse exacte est calculée en colonne D. Dans la partie inférieure de la copie d’écran, nous avons listé les formules des colonnes D et L.

Les formules de D3, D6, L5 et L7 sont des formules matricielles, à valider avec [Ctrl]-[Maj]-[Entrée].

La formule de F2, reproduite vers le bas, est : =si(estna(E1);faux;C1=E1). Je n’ai pas utilisé la fonction sierreur() pour que cette formule fonctionne avec toutes les versions d’Excel.

La formule de F9 est =nb.si(F1:F8;vrai()).

Il ne vous reste plus, d’ici mon prochain post, qu’à trouver comment vous ferez, à partir de ce modèle, pour trouver la solution optimale…


21 mars 2016

Résolution de problème récursif (a)

Je suis tombé sur le problème précédent il y a plus de 15 ans. J’ai hélas perdu le nom de l’auteur.  Si par extraordinaire quelqu’un le connaît, je vous serai reconnaissant de bien vouloir me donner son nom afin que je puisse lui rendre hommage dans ce blog.


Ce puzzle est un problème récursif : la réponse à chaque question dépend des réponses apportées aux autres questions. Ainsi, pour la question n°1, si vous entrez « B » en H2, cela signifie que vous pensez que la prochaine question qui aura « B » comme réponse sera la question n°3. Dans la colonne J, on affiche le résultat et, en J10, le score obtenu, c’est-à-dire le nombre de valeurs vraies.

Votre mission est de construire ce premier modèle dans lequel vous entrerez vos réponses en colonne H et consulterez votre résultat en colonne J. Si vous voulez aller plus loin, commencez à réfléchir à la façon dont vous vous y prendrez pour trouver la solution optimale.


16 mars 2016

Alzohis : une nouvelle startup

En dehors de mon activité de professeur (MIT en 1969-70, puis HEC de 1970 à ce jour) et de consultant, où j'ai développé > 1.000 modèles pour > 100 entreprises en > 10 pays, je me suis trouvé impliqué depuis 1980 dans une quinzaine de startups, en étant l’un des fondateurs de 10 d’entre elles.

Dans mon message du 8 novembre 2009,  je vous annonçais la création avec deux associés de la société de conseil Finance 3.1. Cette société est spécialisée dans la modélisation avancée en finance (avec une forte compétence Excel, comme par hasard !) et a créé l’add-in Upslide, que je vous ai présenté en détail dans mes articles du 6 au 18 mai 2011. Cette société connaît un excellent développement et compte à ce jour plus de 20 collaborateurs.

Je suis depuis peu actionnaire de la société Alzohis, créée en 2014, avec laquelle j’ai commencé à travailler il y a 9 mois. Cette société a pour objectif de commercialiser un test de diagnostic Alzheimer à partir d’une simple prise de sang, donc plus léger que l’IRM ou la ponction lombaire, et pourtant avec la même fiabilité de diagnostic.

Vous pouvez à ce sujet consulter le site www.alzohis.com qui, pour le moment, n’est qu’en anglais, mais qui devrait être aussi disponible en français très rapidement et, un peu plus tard, en espagnol.

Pourquoi, vous demanderez-vous peut-être, me suis-je impliqué dans une telle société ? Y a-t-il donc un rapport avec Excel ?

De fait, j’ai connu le créateur d’Alzohis, Romain Verpillot, dans le cadre de mon activité bénévole pour le Réseau Entreprendre, que j’ai débutée après en avoir été lauréat en 2009. Il s’est avéré que je suis parvenu, grâce à des algorithmes que j’ai développés sous Excel, à améliorer de façon significative la précision des diagnostics sur Alzheimer – et sur d’autres maladies neurodégénératives – qu’il avait réalisés auparavant.

Remarque – De façon totalement indépendante, il se trouve que j’ai, durant ma carrière, créé des modèles Excel pour sept sociétés pharmaceutiques, dans l’ordre alphabétique pour ne pas faire de jaloux : Aventis, Boehringher (en Allemagne), Johnson & Johnson, Lilly France, Pasteur Mérieux, SANOFI et Smithkline Beecham (en Suède).

Une preuve de plus qu’Excel mène à tout !

12 mars 2016

Identification des cellules à formule

Un problème récurrent, lorsque l’on audite un modèle, est d’identifier rapidement, dans un bloc, toutes les cellules contenant des formules. Nous avons déjà traité cela dans l’article « Un format pour les formules » du 12 juillet 2008, mais il y a maintenant du nouveau…

Utilisation de la commande « Atteindre »

La commande « Atteindre »  s’obtient en passant, dans l’onglet « Accueil » par la commande « Rechercher et sélectionner ». On y trouve aussi les moyens de sélectionner directement les cellules contenant des formules , des commentaires, des formats conditionnels,… Vous pouvez le voir dans la partie gauche de la copie d’écran ci-dessous.


En revanche, en utilisant plutôt le raccourci  [Ctrl]-t, vous arrivez directement à la commande « Atteindre ».

Un format conditionnel avec une macro

Pour mettre en relief de façon durable toutes les cellules d’une feuille qui contiennent une formule, il faut utiliser un format conditionnel. Nous voyons dans la partie droite de la copie d’écran la macro utilisée par ce format ainsi que le format lui-même, la cellule active étant la cellule C15.

Remarque – Les parenthèses après « VRAI » sont facultatives. Je ne les ai mises que pour mettre en relief l’existence de la fonction vrai().

Une nouveauté d’Excel 2013

Tout cela se simplifie avec l’apparition – dans Excel 2013 – de la fonction EstFormule(). Il n’est dorénavant plus nécessaire de créer une macro pour définir le format conditionnel ci-dessus.

05 mars 2016

Plein de formats conditionnels...

Dans l’article précédent, nous avons renoué avec un sujet important, celui des formats conditionnels. Cette merveilleuse fonctionnalité, apparue avec Excel 97, est extrêmement précieuse pour la modélisation dans Excel, et elle a joué un rôle important dans de nombreux modèles que j’ai développés pour mes clients.

Voici une récapitulation d’articles du blog dans lesquels vous verrez des exemples d’utilisation des formats conditionnels…

« Format conditionnel pour une valeur » du 26 janvier 2007. Comment mettre en relief, dans un bloc de cellules, les 3 plus grandes et/ou les 3 plus petites.

« Le format conditionnel d’Excel 2007 », trois articles du 3 au 11 octobre 2008. Un tutorial un peu général sur les formats conditionnels.

« Les originaux et les doublons… », les 14 et 17 janvier 2014. Mise en relief de doublons et/ou de contenus originaux.

« Masquage des erreurs … », les 20 et 26 décembre 2014. Utilisation des formats conditionnels ou d’une autre solution pour masquer des erreurs à l’écran et/ou à l’impression.

« Les valeurs en double… », les 5 et 11 mars 2015.

« Les formats conditionnels », un tutorial allant du 23 mai au 4 juin 2015.

« Un système expert de diagnostic », le 11 juin 2015. Comment construire un système expert de diagnostic basé principalement sur l’utilisation de formats conditionnels.

« Divers exemples de formats conditionnels », le 17 juin 2015. Encore une liste de références dans ce blog où l’on utilise des formats conditionnels…

Avec toutes ces lectures, vous avez de quoi devenir un expert des formats conditionnels d'Excel !