Monsieur Excel
Pour tout savoir faire sur Excel !

29 juin 2016

Le problème d’Einstein (b)

Nous réalisons aujourd’hui la première étape de la résolution du problème présenté dans l’article précédent.

Nous avons commencé par marquer les hypothèses directes, en les mettant ensuite en souligné. Ainsi, avec la phrase « 2 – L’anglais habite la maison rouge », nous avons mis une croix en H5 et un fond jaune aux cellules de la même colonne et de la même ligne dans ce carré.


Quand une seconde certitude était établie dans un carré, nous avons mis la ligne et la colonne en bleu. Dans ce premier carré, nous l’avons fait avec la réponse en K4 (Norvégien dans la maison jaune), qui était le résultat dans les quatre dernières lignes du commentaire, où l’on établit que le Norvégien est dans la maison jaune, fait qui est la conclusion des quatre premières lignes du commentaire et d’autres déclarations.

Quand un carré a été ainsi défini, par exemple le bloc H4:L8, on fait de même avec le bloc symétrique par rapport à la diagonale, dans ce cas le bloc C9:G13.

Le tableau ci-dessus est celui que l’on obtient en utilisant au premier degré toutes les déclarations soulignées, et au second degré ce qui est énoncé dans les quatre dernières lignes de la zone de texte.

Nous ne pouvons pas encore répondre à la question posée dans le premier article, mais nous avons progressé en marquant un certain nombre de cellules avec des « X » et des « - »



22 juin 2016

Le problème d’Einstein (a)

Nous nous attaquons aujourd’hui à un problème bien connu, censé avoir été proposé par Einstein, selon lequel 98% de la population serait bien incapable de le résoudre.

Comme ce problème est archi-connu et que vous pourrez trouver sans difficulté la réponse sur Internet, nous l’avons légèrement modifié afin que ne tombiez pas sur une réponse toute faite !

Nous avons gardé les même hypothèses que celles énoncées par Einstein, mais nous avons modifié la question finale. Einstein demandait « Quel est le pays de la personne possédant le poisson ? ». Nous, nous vous posons la question « Quel est le pays de la personne possédant l’oiseau ? » Pour ma part, je pense qu’Einstein était optimiste et que moins de 2% des personnes sauraient trouver la solution. Serez-vous parmi ces moins de 2% ?

Voici l’intégramme permettant d’analyser ce problème et la liste des hypothèses :


Si vous voulez ensuite vous entraîner à des variantes de ce problème, regardez la définition d’intégramme dans Wikipedia. 

Vous verrez que l’on peut encore poser deux autres questions : Qui boit de l'eau ? Qui élève le zèbre ?

Vous y trouverez aussi d'autres problèmes se prêtant à une résolution par intégramme...

17 juin 2016

Une macro plus rapide ?

L’article précédent a décidément stimulé les lecteurs de ce blog !

Daniel Colardelle a dit : « Il serait intéressant de connaître le temps d'exécution pour ajouter une colonne de cellules séquentielles permettant de retrouver l'ordre initial, de trier, de supprimer les lignes et enfin de retrier pour retrouver l'ordre initial afin de mieux comparer. »

Claude Duigou a dit :  « D. Colardelle a tout à fait raison : pour que la mesure de performance soit valable, il faut que les données retrouvent leur ordre initial. Dans certaines applications, ceci peut être impératif. »

Pierre Dumas a dit : « Je plussoie aux deux commentaires précédents. Ceci étant, 39 secondes pour l'exécution de cette macro, cela me semble bien trop long. J'ai donc fait le test sur mon ordinateur portable, qui n'est pas un foudre de guerre (Celeron à 1,8GHz). Les tests montrent un temps d'environ 4 secondes. Par la méthode de trier auparavant, c'est un peu plus rapide : 2 à 3 secondes. Mais c'est toujours bon à prendre et l'astuce est à conserver. »

Que puis-je en dire ? Contrairement à Pierre Dumas, j’observe un écart énorme entre le temps pris par la macro quand je trie avant et après la destruction des mauvaises lignes, et celui pris sans ces deux tris. Pour vérifier cela et répondre aussi aux deux premiers "commentateurs", j’ai donc modifié mon modèle précédent, en ajoutant une cellule nommée Tri dans laquelle je saisis « Oui » si je fais les tris avant et après le traitement, et « Non » autrement. Je compte bien entendu le temps pris par ces deux tris quand je les effectue. La seule chose dont je ne compte pas le temps, au début de la macro, est la récupération des trois colonnes de départ depuis l’onglet « Base ».

Mon expérience est que le temps moyen de la macro, quand je ne fais pas les tris, est de l’ordre de 24 minutes 25 secondes, les résultats sur 5 essais variant entre 24:18 et 24:34. Quand j’effectue les deux tris, il est en moyenne de 1 seconde, tris compris. Autrement dit, je vais 1.470 fois plus vite quand j’effectue les tris ! Je suis donc très très loin du tout petit écart observé par Pierre Dumas. Je moinssoie !

Si vous voulez faire des tests sur votre PC, envoyez-moi un mail à thiriez@hec.fr et je vous renverrai mon fichier Excel. Vous pourrez alors nous donner vos conclusions en commentaire à cet article.

Voici le code de la macro :

Sub Vitesse()
    Columns("A:C") = Sheets("Base").Columns("A:C").Value
    Call Chrono
    If Range("Tri") = "Oui" Then
        ActiveWorkbook.Worksheets("Test").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Test").Sort.SortFields.Add Key:=Range("A2:A100001" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Test").Sort
            .SetRange Range("A1:C100001")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$C$100001").AutoFilter Field:=1, Criteria1:="Rouge"
    Rows("2:100001").Delete Shift:=xlUp
    Range("A1").AutoFilter
    If Range("Tri") = "Oui" Then
        ActiveWorkbook.Worksheets("Test").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Test").Sort.SortFields.Add Key:=Range("C2:C100001" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Test").Sort
            .SetRange Range("A1:C100001")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    Call Chrono

End Sub

06 juin 2016

Chronométrez dans Excel !

Dans Excel, on se pose parfois des questions sur les mérites de telle ou telle solution en temps de calcul.

Voici une petite macro toute simple, Chrono, qui va pouvoir vous rendre ce service. Dans certains modèles où j’ai besoin de mesurer le temps d’exécution du calcul dans Excel, ou même parfois le temps requis par un certain développement, j’utilise cette macro.

Un premier clic sur l’horloge, à laquelle j’ai associé cette macro, lance le chronomètre. Un second clic affiche le temps écoulé depuis le premier clic.

J’ai trouvé cette macro particulièrement utile chez mes clients lors du lancement d’une macro dont le traitement prend entre 10 secondes et 1 à 2 minutes. J’appelle « Chrono »  au début et à la fin de la macro. Les utilisateurs sont toujours surpris de voir qu’ils ont attendu moins de 30 secondes alors qu’ils avaient l’impression d’avoir patienté bien plus longtemps !


Remarque – Pour activer l’horloge sans que la macro ne se lance, il suffit de la sélectionner en maintenant la touche  [Ctrl] enfoncée. Vous pouvez alors la déplacer ou la redimensionner tranquillement…

01 juin 2016

Un bien mauvais camembert !

En fait, l’exemple que je vous ai donné dans le dernier article nous vient de la sympathique « Excel MVP » Mynda Treacy, dont le blog est à l’adresse suivante : http://www.myonlinetraininghub.com/blog

Elle utilise ce graphe comme exemple type d’un « Really Bad Pie Chart », arguant du fait qu’il est très difficile à interpréter…

Voici la solution qu’elle nous propose à la place. Pour commencer, présentez les informations de façon différente, comme nous pouvons le voir en B2:G15 dans la copie d’écran ci-dessous. Sélectionnez ce bloc et insérez un graphe en « Barres 2D », avec la première option.

Il ne vous reste plus qu’à réaliser les opérations suivantes :
  • double clic sur un nom de l’axe vertical pour l’activer
  • cocher « Abscisses en ordre inverse » et valider
  • via « Sélectionner les données », mettre en premier les deux séries « Total… »
  • pour les séries, choisir un chevauchement à 100%
  • pour les séries, choisir une largeur d’intervalle à 0%
  • ajouter une bordure blanche aux séries 3 et 4
  • ajouter des étiquettes aux séries 3 et 4
  • ajouter des étiquettes aux séries 1 et 2, puis ne
  • garder que l’une d’entre elles en ajoutant le texte…
  • ôter le quadrillage vertical et l’axe des X
Et vous obtenez alors le résultat ci-dessous :


Etes-vous d’accord avec Mynda – et moi ! – pour trouver que ce graphe est nettement plus lisible et facile à interpréter que celui de l’article précédent ?

27 mai 2016

Graphe en “secteurs de secteur”

Nous nous intéressons aujourd’hui à un graphe d’un type particulier, baptisé par Excel « secteurs de secteur », ce qui ne nous avance pas vraiment beaucoup quand on ne sait pas bien de quoi il s’agit…

Il s’agit d’analyser la répartition de « gâteaux » salés ou sucrés en fonction de leurs composants. Nous débutons en sélectionnant le bloc C4:D16 et en insérant le camembert baptisé « secteurs de secteur », le seul d’ailleurs avec deux camemberts, un principal et un secondaire. Nous obtenons alors le graphe ci-dessous :


Pour obtenir le résultat reproduit à la fin de cet article, il nous faut à présent effectuer les étapes suivantes :
  • mettre la légende en bas du graphe ;
  • ajouter des étiquettes de données :
  • pour celles-ci, cocher « Nom de catégorie » et « Pourcentage »
  • puis, en « Mise en forme », leur ajouter un cadre ;
  • remplacer l’étiquette « Autre » par « Sucré » ;
  • replacer les étiquettes chevauchant les autres étiquettes ;
  • ajouter le titre « Gâteaux salés et sucrés » et l’encadrer.
Et voilà ! Vous venez de créer probablement votre premier camembert en  « secteurs de secteur ».


Remarque 1 – Nous sommes en France, il n’est pas surprenant donc que le gâteau (« pie », en anglais) devienne un camembert en traversant la Manche, d’autant que la Normandie, la région de la ville éponyme, est une des premières régions françaises que découvrent les anglais en débarquant…

Remarque 2 – Même avec les améliorations que nous lui avons fait subir, ce camembert n’est peut-être pas la meilleure façon d’illustrer graphiquement nos données. D’ici mon prochain article, dans lequel vous trouverez une réponse à ce sujet, essayez d’imaginer ce que vous pourriez faire pour l’améliorer…

22 mai 2016

Remarques sur la volatilité

La volatilité dans les formules d’Excel est un sujet délicat, comme nous allons le voir avec les trois rubriques ci-dessous.

Déclarations erronées de Microsoft

La formule =cellule("Filename"), selon un article de Microsoft, ne serait pas volatile. Et pourtant elle l’est…

Notez d’ailleurs que dans l’aide d’Excel, Microsoft vous indique que l’argument à utiliser avec un Excel français est "Nom de fichier". Si vous utilisez cet argument au lieu de "Filename", vous obtenez l’erreur #VALEUR !

Un Somme.si() délicat

La formule =somme.si(A1:A4 ;">0";B1) est volatile, alors que la formule  =somme.si(A1:A4 ;">0";B1:B4) ne l’est pas. De plus, de fait, ces deux formules feront référence à B1:B4.

Il semblerait que ce comportement volatile ait été ajouté pour donner des résultats exacts quand le dernier argument est modifié… Cette situation a été découverte par Luke Wisbey.

Une fonction volatile impacte la formule qui la contient

De façon tout à fait surprenante, quand on utilise dans une formule une fonction volatile,  la formule elle-même devient volatile même si la fonction volatile n’est jamais exécutée !

Ainsi, avec la formule =si(2>1;100;maintenant()), on obtient toujours le résultat 100 et on n’exécute donc jamais la fonction maintenant(). Et pourtant, la cellule contenant cette formule est marquée comme volatile dans Excel !

Si maintenant vous entrez =maintenant() en A1, la formule =si(2>1;100;A1) continuera à toujours renvoyer 100, mais ne sera plus marquée comme volatile dans Excel !

Pour en savoir plus sur le calcul dans Excel et sur la volatilité

Une lecture tout à fait intéressante, complémentaire de celles que je vous ai déjà conseillées dans les quatre derniers articles :