Monsieur Excel
Pour tout savoir faire sur Excel !

23 janvier 2020

Enigme sur les âges (a)

Une façon ludique d’apprendre à utiliser Excel consiste à utiliser notre logiciel favori pour résoudre des énigmes. Pour ma part, j’adore résoudre des énigmes logiques et/ou mathématiques et j’ai plusieurs fois gagné des prix grâce à cela. Voici donc une énigme qui peut vous intéresser…

Les âges de Jean, Anne et Paul

Dans 10 ans, Jean sera deux fois aussi vieux qu'Anne ne l'était quand Paul avait 9 fois l'âge de Jean.

Il y a 8 ans, Paul avait la moitié de l'âge que Anne aura quand elle aura un an de plus que l'âge que Jean aura quand Paul sera 5 fois plus vieux que Jean dans 2 ans.

Quand Jean avait 1 an, Paul était de 3 ans plus vieux que Jean ne le sera quand Anne sera 3 fois aussi vieille que Paul l'était 6 ans avant l'année où Anne a eu la moitié de l'âge que Jean aura quand Paul aura 10 ans de plus que l'âge qu'il avait quand Anne avait le tiers de l'âge que Jean aura quand Paul aura 3 fois l'âge qu'il avait quand Anne est née.

Quels sont leurs âges aujourd'hui ?

Modélisation du problème

Avant de lire la suite, essayez de voir comment vous pourriez formuler ce problème dans Excel.

Prenons le premier paragraphe. Comment le formuler mathématiquement ? Essayez de trouver la solution avant de poursuivre votre lecture.

Je vais vous aider à présent en vous proposant une solution où chaque variable représente l’âge de la personne concernée :

Jean + 10 = 2 (Anne - r)
Paul - r = 9 (Jean - r)

On crée en effet une variable r qui nous permet de formuler en deux contraintes mathématiques qui formulent les conditions posées par le premier paragraphe.

Il ne vous reste plus qu’à formuler de cette façon l’ensemble des contraintes puis de retranscrire cette formulation dans un tableau Excel. Nous pourrons alors résoudre le problème…

17 janvier 2020

Du neuf sur Alzheimer

Vous vous demandez peut-être pourquoi je vous parle d’Alzheimer dans un blog Excel… La raison de cette incursion dans un autre monde est que – grâce à ce que je parviens à faire avec Excel – je suis depuis 3 ans le data scientist d’Alzohis, une start-up qui a créé un diagnostic Alzheimer par simple prise de sang et dont j’ai mis au point les algorithmes.

Et que nous sommes sur le point de mettre le Noratest sur le marché incessamment...

Pour vous replonger dans la genèse de cette aventure, vous pouvez lire le message de ce blog le 16 mars 2016 : « Alzohis : une nouvelle startup » :

Si vous voulez en savoir plus sur le Noratest et sur Alzohis :

Nous venons de passer un accord avec Inovie qui réalise ce test. Vous pourrez voir sur leur site la liste de ses laboratoires concernés :

Le test peut être prescrit par un généraliste ou un neurologue. Préalablement, un test MMSE (mini-mental state examination) – un test cognitif – doit avoir été passé et son résultat soumis en même temps que les résultats de la prise de sang sur le site d’Alzohis.

Le Noratest coûte 250 € non remboursés par la Sécu. C’est une solution plus rapide et moins chère ou pénible que d’autres solutions telles que la ponction lombaire, l’IRM, ou d’autres techniques encore.

07 janvier 2020

Statistiques Feuille/Classeur

Il y a une commande Excel qui est assez peu connue des utilisateurs, mais qui fournit des résultats intéressants. Vous la trouverez dans l’onglet Révision, dans le premier bloc, baptisé Vérification : c’est la commande Statistiques.

Si vous la connaissez déjà, il est cependant fort probable que vous ne connaissiez pas pour autant son raccourci : [Ctrl]-[Maj]-[g].

Certes, j’aurais pu écrire tout simplement [Ctrl]-[G], mais je suis sûr que plus d’un lecteur aurait alors omis de noter que le « g » devait être en majuscule…

Comment se souvenir de raccourci étrange ? Je vous propose un moyen mnémotechnique : « J’ai engrangé les statistiques » (en-grand-g), un moyen dont vous avez quelques chances de vous souvenir.

Vous obtenez alors des statistiques intéressantes sur la feuille de calcul et sur le classeur :


Ce dialogue possède une particularité intéressante. Supposions que vous ne le fermiez pas puis que vous mettiez devant lui une fenêtre d’un autre logiciel qui masque le dialogue tout en laissant une partie de la feuille Excel visible. Il devient alors impossible d’activer la feuille Excel en cliquant dedans. Une bonne petite blague à faire à un collège de bureau…

01 janvier 2020

Vœux pour l’année 2020

Je tiens à souhaiter à toutes mes lectrices et à tous mes lecteurs une …

Une excellente année 2020 !

L’année 2019 a été une année mouvementée, non seulement en France avec les divers mouvements sociaux, mais aussi à notre échelle…

Cette année, j’ai en effet été renouvelé « Microsoft MVP » pour la dixième année consécutive. Cela, c’est le point positif. Le côté négatif de la chose, c’est que Microsoft a hélas décidé d’agglomérer les titres, ce qui fait que nous ne sommes plus que des « Office MVP » là où nous étions auparavant « Excel MVP ».


Je suis le seul consultant en France  qui soit MVP Excel depuis 10 années consécutives ; il y en a quatre autres en Europe.

La société de conseil que j’ai créée il y a 40 ans, Logma, s’est enrichie en fin d’année avec l’arrivée de Frédéric Le Guen, que je connais déjà depuis de longues années. Logma a maintenant dans son équipe les deux seuls consultants Excel en France qui soient de fait MVP sur Excel.

Pour en savoir plus sur notre équipe : https://www.logma.fr/bienvenue/notre-équipe/

Je suis spécialisé dans la création de modèles Excel, leur audit et amélioration. Si vous voulez améliorer vos modèles Excel de façon drastique, je suis votre homme ! J’ai réalisé plus de 1.000 modèles pour plus de 100 entreprises dans plus de 10 pays.

Frédéric est expert en analyse de données (modélisation, agrégation, visualisation). Il maîtrise parfaitement Power BI (Power Query et Power Pivot) pour créer des tableaux de bord modernes et dynamiques.

20 décembre 2019

Un graphe élastique (d)

Prenons le graphe étudié dans le dernier article. La copie d’écran ci-dessous montre ce graphe et l’on peut voir dans la barre de formule que les X sont définis A2:A11 et les Y par B2:B11. En dessous de la copie du graphe, nous voyons comment il convient de modifier la formule du graphe pour le rendre dynamique : il faut remplacer $A$2:$A$11 par Les_X et $B$2:$B$11 par Les_Y, les deux noms élastiques que nous avons définis dans l’article précédent.

Remarque – Il est indispensable de ne pas effacer le nom de la feuille, sinon la modification ne marchera pas !


En bas de la copie d’écran, nous voyons ce que devient la formule du graphe dès que l’on valide la formule précédente. Excel a automatiquement remplacé le nom de la feuille, dans la formule du graphe, par celui du classeur. Pourquoi donc ?

Tout simplement parce que A2:A11 était une adresse dans la feuille de calcul, ce qui explique pourquoi les X étaient représentés par Dynamic!$A$2 :$A$11.

Quand on remplace l’adresse $A$2:$A$11, par Les_X, on change tout ! En effet, un nom est une propriété du classeur et non d’une feuille. Du coup, Excel remplace automatiquement le nom de la feuille par celui du classeur !

Et voilà, vous avez maintenant un graphe totalement élastique, sans avoir besoin de passer par un tableau. Cela fonctionne avec toutes les versions d'Excel !

14 décembre 2019

Un graphe élastique (c)

Reprenons notre modèle en A1 !

En fait, pour être précis, j’ai inséré 5 colonnes avant la colonne 1 afin de garder à droite ce que nous avions fait dans les deux articles précédents pour réaliser un graphe élastique en utilisant un tableau de données. Cela nous permettra d’afficher les deux solutions côte à côte…

Nous créons le graphe de façon tout à fait normale, en le présentant comme lors du premier article de cette série, et nous obtenons le résultat correspondant à la copie d’écran ci-dessous. Nous pouvons constater dans la barre de formule que les X sont bien définis par A2:A11 et les Y par B2:B11.


Un nom élastique

La première étape pour réaliser un graphe élastique consiste à donner un nom élastique aux X et aux Y. Pour cela, nous allons définir les noms Les_X et Les_Y de la façon suivante.

Les_X est défini par =Dynamic!$A$2:decaler(Dynamic!$A$1;nb(Dynamic!$A:$A);0)

Les_Y est défini par =decaler(Les_X;0;1)

Sélection d’un nom élastique

Nous souhaitons bien entendu vérifier si les définitions ci-dessus sont valides. Pour cela, naturellement, nous utilisons le menu déroulant à gauche de la barre de formule (la Zone Nom, dans la syntaxe d’Excel), pour sélectionner ces nouveaux noms et vérifier qu’ils pointent bien dans la bonne direction.

Hélas, cela ne marche pas ! Ils ne sont pas dans la liste… C’est là une particularité d’Excel : les noms dynamiques fonctionnent très bien, mais ils ne sont pas reconnus comme des noms à part entière !

Utilisons donc [Ctrl]-t (la commande Atteindre), et nous constatons là aussi que nos deux noms dynamiques sont absents de la liste. Mais cela ne nous empêche pas de taper le nom Les_X et de valider : nous constatons alors le parfait fonctionnement de ce nom dynamique qui sélectionne bien la zone A2:A11.

Astuce – Plus rapide encore que la commande « Atteindre », il nous suffit de nous placer dans la zone Nom, de taper Les_Y puis de valider pour constater la bonne sélection de nos Y.

Entrons 11/2019 en A12, puis resélectionnons Les_X. Nous constatons aussitôt que la sélection incorpore bien la nouvelle date.

Nous verrons dans le prochain article comment intégrer ces deux noms dynamiques dans la définition du graphe.

07 décembre 2019

Un graphe élastique (b)

Dans le dernier article, nous posions la question « Y a-t-il un problème ? ». Nous apportons la réponse à cette question dans les lignes qui suivent.

Dans l’exemple ci-dessous, nous avons un petit modèle dans lequel les zones A1:F5 et A12:F16 ont été définies comme tableaux. On voit d’ailleurs dans les lignes 1 et 12 les menus déroulants qui apparaissent automatiquement dès que l’on a affaire à un tableau.


Sélectionnons la colonne B avec un clic droit et nous obtenons alors ce qui apparaît dans la partie droite de la copie d’écran. On constate alors que les commandes Insérer et Supprimer sont grisées. Cela signifie que, lorsque deux tableaux ont en commun au moins une colonne, on ne peut plus insérer ni supprimer de colonne.

Cela est très grave ! En effet, dans ce cas, on perd l’indépendance des lignes et des colonnes dans Excel, ce qui est une qualité fondamentale du tableur.

Cliquons à présent dans une – quelconque ! – des cellules du bloc en faisant un clic droit, puis utilisons les commandes Table et Convertir en plage. Nous ôtons alors à ce bloc sa structure de tableau. Puis refaisons un clic droit sur la colonne B : nous observons alors (cf. la partie droite de la copie d’écran) que le problème a disparu : il est de nouveau possible d’insérer ou de supprimer des colonnes.


La solution que nous présentions dans l’article précédent présente donc un danger s’il est possible, dans le modèle concerné, qu’il y ait une intersection non vide dans les colonnes des tableaux de l’onglet. On perdrait alors la précieuse indépendance des lignes et des colonnes du modèle.

Nous verrons dans le prochain article comment faire un graphe élastique sans que ce problème ne puisse apparaître…