Monsieur Excel
Pour tout savoir faire sur Excel !

13 mai 2020

Fonctions matricielles dynamiques (a)

Si vous avez une version d’Excel récente, par exemple une licence 365, vous pouvez à présent utiliser les fonctions matricielles dynamiques appelées dynamic arrays en anglais.

Il s’agit d’un changement fondamental dans Excel qui permet entre autres d’écrire une simple formule là où il fallait parfois avoir recours à des formules matricielles, donc validées avec [Ctrl]-[Maj]-[Entrée].










Clic dans une cellule de la nouvelle table

Quand on clique dans l’une quelconque des cellules de ce nouveau bloc, un encadrement entoure l’ensemble des cellules du nouveau bloc et la formule =A4:D17 apparaît en police grise dans la barre de formule, sauf si c’est en I7 que l’on a cliqué auquel cas la police n’est pas grisée.

Destruction d’un bloc de cellules

Si l’on sélectionne un bloc comportant des cellules du bloc I7:L20 mais n’incluant pas la cellule I7 pour l’effacer ou le détruire, tout ce qui a été détruit réapparaît immédiatement !

En revanche, dès que l’on efface ou détruit la cellule I7, ou un bloc de n’importe quelle taille incluant la cellule I7, l’ensemble du bloc I7:L20 disparaît.

Saisie à l’intérieur du bloc de cellules

Si l’on entre quoi que ce soit dans une des cellules du bloc I7:L20 à part la cellule I7, ce que l’on a saisi est bien validé dans la cellule, mais tout le reste du bloc disparaît, sauf la cellule I7 qui affiche à présent le message d’erreur #EPARS! Apparemment, cela signifie « parsing error », ce qui se traduirait par « erreur d’analyse ».

Dès que l’on détruit ou efface la cellule dans laquelle on a effectué la saisie ayant provoqué l’erreur #EPARS!, ou même un bloc incluant cette cellule, le bloc I7:L20 réapparaît tel qu’il était auparavant.

Conclusion

Cette brève introduction nous permet de constater que les fonctions matricielles dynamiques représentent une innovation majeure dans Excel. Nous continuerons donc à explorer cette innovation dans les prochains articles.

08 mai 2020

Analyse graphique du COVID (b)

Cet article fait suite à celui du 1er mai, que je vous conseille vivement de lire en premier si ce n’est déjà fait.

Nous utilisons l’analyse en « Valeurs par jour » pour cet article.
Nous pourrions aussi faire la même étude en prenant l’option « Valeurs cumulées ».

Je maintiens les réserves initiales sur la difficulté – pour ne pas dire – l’impossibilité de trouver une source fiable pour les données quotidiennes. Ceci dit, ces graphes sont intéressants à analyser. Et, comme les données proviennent principalement d’une source unique, cela donne quand même une certain cohérence à l’ensemble.

La France

Pour la France, on constate une rapide montée en puissance depuis le 13 mars avec une culmination au début du mois d’avril.

Ensuite, les valeurs ont une nette tendance décroissante dont nous espérons qu’elle ne souffrira pas trop de la fin du confinement actuel.


L’Italie

L’Italie a connu une croissance très rapide entre le 13 mars et la fin mars. Ensuite, la décroissance a été régulière, particulièrement régulière même, ce qui paraître surprenant quand on connaît le caractère parfois fantasque et imprévisible de nos amis transalpins.

Ensuite, les valeurs manifestent une nette tendance décroissante dont nous espérons qu’elle ne souffrira pas trop de la fin du confinement actuel.


L’Espagne

Pour l’Espagne, comme pour la France et l’Italie, le plafond se situe entre la fin mars et le début du mois d’avril. On observe ensuite, comme pour la France et l’Italie, une décrue régulière mais avec cependant beaucoup moins de régularité que celle observée par l’Italie et – à moindre titre – par la France.


Les Etats-Unis

Sans surprise, les courbes des USA sont décalées vers la droite dans le temps. En revanche, pour le moment, il n’y a pas de claire évolution vers une régression. Est-ce dû à la sagesse proverbiale de leur président ?


Conclusion

Espérons que ces courbes, si jamais j’ai l’occasion de les reproduire dans quelque temps, auront eu la sagesse de bien évoluer.

01 mai 2020

Analyse graphique du COVID (a)

Tous les jours, nous sommes noyés – depuis une multitude de sources – par des chiffres et des graphiques sur la progression du COVID dans le monde. Je n’ai hélas pas encore trouvé une seule source qui soit entièrement fiable et dont les valeurs, jour après jour, restent cohérentes.

La source principale que j’utilise à cet effet est :

Même avec cette source, il arrive souvent (et toujours pour les USA !) que l’écart affiché par rapport à la veille ne colle pas avec la différence entre la valeur du jour et la valeur indiquée la veille ! Quand ces incohérences ne trouvent pas de solution, j’invente une valeur cohérente avec le reste…

Si vous connaissez un endroit où l’on peut trouver des données cohérentes, jour par jour, SVP indiquez-le dans un commentaire !

La structure du modèle

Pour réaliser ce modèle, j’ai utilisé la validation de cellule pour le choix du pays et inséré des contrôles de formulaire (Développeur puis Insérer) pour le choix de valeurs cumulées ou individuelles.

J’ai ensuite utilisé des noms dynamiques pour les X et les Y de chacun des deux graphes.

Ainsi, pour ls série des morts, la formule du graphe est : =SERIE("Morts";Calendrier.xlsm!mes_X;Calendrier.xlsm!Mes_Y2;2)

Mes_X est défini par :
=Corona!$A$4:decaler(Corona!$A$2;NBVAL(Corona!$A$1:$A$52)-1;0)

Et Mes_Y2 par =decaler(Mes_Y1;0;1) et Mes_Y1 par : =decaler(mes_X;0;5).

Les résultats pour la France

Vous trouverez ci-dessous le résultat obtenu pour la France, en valeurs cumulées puis en valeurs par jour.





26 avril 2020

La fonction Equiv()

Cet article complète la série des articles publiés récemment sur les fonctions de recherche d’Excel :
 
22 mars 2020 : La fonction Index()
29 mars 2020 : Index() avec des trous
06 avril 2020 : La vie de la fonction Recherche()
13 avril 2020 : Les fonctions RechercheV et …H

Syntaxe de la fonction Equiv()

La syntaxe de la fonction est : =equiv(valeur_cherchée ;vecteur_recherche[;type])

Une fois de plus, ne vous fiez pas à la syntaxe proposée par Excel (cf ci-dessous) quand vous avez saisi =equiv(. En effet, cette syntaxe comporte deux erreurs. 

Premièrement, ce n’est pas un tableau de recherche, ce qui ferait a priori penser à une matrice, mais en fait un vecteur de recherche.

Ensuite, comme dans pratiquement toutes les syntaxes proposées par Excel pour les fonctions avec au moins un argument facultatif, le [ est mal placé. Si vous mettez le dernier « ; » sans le faire suivre par un type, vous obtenez une erreur. C’est donc la syntaxe que je vous propose ci-dessus qui est la bonne !


Utilisation courante de Equiv()

Dans l’utilisation courante de la fonction, equiv(), on recherche la valeur_cherchée dans le vecteur indiqué. Si l’argument est 1 (ou s’il est omis), on ramène la position dans le vecteur de la dernière valeur inférieure ou égale à valeur_cherchée . Dans notre exemple, quand on cherche le 25 de la cellule E2 dans le vecteur G1:G91 avec le paramètre 1, on trouve bien 4 qui est la position dans la colonne G de la dernière valeur inférieure ou égale à 25.

Si l'argument est 0, on cherche la position de la première valeur égale à celle que l'on cherche.
Si l'argument est -1, on cherche la position de la dernière valeur supérieur ou égale à celle que l'on cherche.


Une utilisation originale de Equiv()

Quand on a bien compris le fonctionnement d’Equiv(), on peut utiliser cette fonction pour atteindre des objectifs originaux. Supposons par exemple que nous souhaitions obtenir la position de la dernière valeur renseignée dans la colonne C, sachant que cette colonne ne peut recevoir que des valeurs positives.

La solution de la cellule E9 s’impose alors avec évidence : la dernière ligne renseignée est tout simplement la dernière ligne avec une valeur supérieure à -1 !

Remarque – Si la colonne C pouvait aussi recevoir des valeurs négatives, il suffirait alors d’utiliser la formule =equiv(10^-20;C:C;-1) à condition que toutes les valeurs de la colonne C soient supérieures ou égales à 10^-20…

16 avril 2020

Tirez profit du Covid-19 !

Nous sommes tous contraints par le Covid-19. Le seul côté positif, si l’on peut dire, est que l’on a – souvent bien malgré soi ! – retrouvé du temps libre. Vous pouvez profiter de ce temps pour essayer de progresser sur Excel, ce qui finalement vous fera gagner du temps dans l’avenir.

J’anime depuis plus de 10 ans au MBA HEC un électif baptisé « Advanced Excel Modelling ». C’est à ma connaissance le seul électif dont l’effectif est toujours plein dès le soir du premier jour où il est proposé aux étudiants. C’est un électif de 6 séances de 3 heures chacune qui correspond à la formation en 2 jours « Modélisation avec Excel » que je propose depuis des années via Logma, la société de conseil que j’ai créée il y a 40 ans et qui compte aujourd’hui une dizaine de consultants, dont les deux seuls consultants en France qui soient MVP Excel de Microsoft (Frédéric le Guen et votre serviteur).

Un premier tableau de bord

Je débute cette formation par la création par les élèves d’un petit tableau de bord assez simple puisque la seule fonction Excel qu’il utilise est la fonction Somme(). Un bon tableau de bord doit être en effet assez  simple pour qu’un patron puisse le comprendre :)

Je donne 10 minutes à mes élèves pour réaliser ce qu’ils peuvent. S’ils terminent avant, ils me font signe et je vais vérifier leur travail.  Ensuite, je le réalise devant eux, sans rien leur expliquer. D’expérience, je le fais 3 fois plus rapidement que le temps moyen des 4 élèves les plus rapides. Enfin, je passe un certain temps à le refaire pas à pas en expliquant toutes les astuces.

Un jour, un participant m’a dit : « Vous avez économisé quelques minutes, cela vous fait une belle jambe ! » Je lui ai répondu que, de la même façon, je pouvais développer en un jour ce qui aurait pris 3 jours aux 4 meilleurs d’entre eux…

Mes formations habituelles

Si vous cliquez dans le bouton « Prochaines formations » dans le bandeau droit de ce blog, vous pourrez voir la liste complète des formations que je propose. Les trois formations qui ont le plus de succès sont les formations « Modélisation avec Excel » et « Création de tableau de bord sous Excel » et « La modélisation du risque (Crystal Ball) ».

A titre indicatif, la formation « Modélisation avec Excel », outre qu’elle marche très fort au MBA d’HEC, a déjà été animée en séminaire intra, pour ne citer que quelques exemples, chez Aéroports de Paris, Arianespace, Aventis, Banques Populaires, Bouygues, Cegelec, CNES, Dassault Systèmes, EdF, EADS, Elf, Ernst & Young, Finacor, France Telecom, HSBC, MBDA, Sacilor, Sanofi, SKF et Total.

Mon offre exceptionnelle « Covid-19 »

Quand un participant s’inscrit à une formation collective (effectif maximum de 8 personnes), il est facturé 800 € HT pour une journée et 1.500 € HT pour deux jours.

Ma proposition exceptionnelle, valable durant le temps du confinement, est la suivante. Je vous propose un cours particulier – à distance bien sûr ! – d’une journée au tarif de 800 € HT, c’est-à-dire le même prix que vous payeriez pour participer à un cours collectif.

Vous pouvez choisir vous-même le sujet de cette formation, à prendre dans mon catalogue, par exemple la moitié de la formation « Modélisation avec Excel ».

La seule condition est que cette formation soit payée d’avance. En effet, pour une telle proposition, je ne peux pas envisager de perdre du temps à récupérer le règlement de la journée.

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

13 avril 2020

Les fonctions RechercheV et RechercheH

Les fonctions RechercheV() et RechercheH() ont été créées pour ajouter de la flexibilité à la fonction Recherche() en permettant à l’utilisateur de choisir la position de la colonne (ou ligne) fournissant le résultat. En plus, elles ne dépendaient plus de la forme de la matrice (verticale ou horizontale), ce qui troublait certains utilisateurs.

La première syntaxe de RechercheV ou …H

La syntaxe de RechercheV était initialement =recherchev(valeur;matrice;n°colonne). En fait, elle fonctionnait comme la fonction recherche() sur une matrice carrée ou verticale, mais en ramenant la valeur de la colonne sélectionnée plutôt que celle de la dernière colonne.

Cela correspondait à la demande de nombreux utilisateurs. Mais une autre demande de nombreux utilisateurs restait insatisfaite, à savoir comment faire pour exiger que la valeur cherchée soit égale à une valeur de la première colonne.

Nous voyons dans l’image suivante, dans la cellule B14, ce qui se passe quand on fait un simple recherchev() pour trouver le nom de la personne à partir de son prénom. Comme il n’y a pas de Jules, Excel renvoie le nom de la personne ayant le dernier prénom antérieur à Jules.


Certes, on pouvait résoudre le problème comme cela a été fait en B15. Mais plus de 90% des utilisateurs d’Excel en étaient totalement incapables.

La solution utilisée en B16, grâce à la seconde syntaxe de RechercheV (avec faux), résout le problème plus aisément !

La seconde syntaxe de RechercheV ou …H

En effet, la seconde syntaxe de RechercheV (ou …H) comporte un dernier argument. Quand ce dernier argument est faux, un message d’erreur s’affiche si le prénom demandé n’appartient pas à la liste des prénoms.

Il y a une différence fondamentale quand on utilise ce dernier argument : Microsoft utilise alors un autre algorithme que l’algorithme traditionnel de Recherche().

L’ancien algorithme compare la valeur cherchée à la valeur courante dans la colonne de recherche et, dès que la valeur cherchée est inférieure à la valeur courante, s’arrêt et remonte d’une ligne pour fournir le résultat. Cela suppose que la première colonne soit rangée en ordre croissant.

Quand on utilise un recherchev() avec faux en dernier argument, c’est un autre algorithme qui est utilisé. On descend alors dans la première colonne jusqu’à ce que l’on trouve la valeur cherchée. Dès qu’on la trouve, on s’arrête ! Ce qui signifie qu’il n’y a plus du tout la nécessité que la première colonne soit rangée en ordre croissant.

En conséquence, dans notre exemple, si l’on cherche Gilles, on trouve Lebleu avec le recherche() tout court ou le recherchev() simple, et Lenoir avec le recherchev() avec faux à la fin !

Tout cela est subtil et, à mon humble avis, l’utilisateur lambda d’Excel n’en est pas conscient…

06 avril 2020

La vie de la fonction Recherche()

La fonction Recherche() est vraiment fondamentale. Elle présente la particularité d’être l’une des 31 fonctions de Visicalc, le premier tableur au monde, sous le nom de Lookup. Vous pourrez le revoir dans l’article Visicalc, le grand-père d’Excel du 28 février.

La première syntaxe de Recherche()

La fonction Recherche() originale d’Excel utilise la syntaxe =recherche(valeur;matrice).

Si la matrice est carrée ou verticale, les valeurs de sa première colonne doivent être en ordre croissant. Excel compare la valeur cherchée à la première colonne de la matrice, identifie la dernière ligne où la valeur reste inférieure ou égale à la valeur cherchée et renvoie alors la valeur de la matrice placée dans la même ligne et la dernière colonne.

Si la matrice est horizontale, le fonctionnement est le même, en inversant ligne et colonne.

Vous trouvez dans la première partie de la copie d’écran ci-dessous ce qui apparaît quand on entre =recherche( dans une cellule. 


Comme c’est souvent le cas avec des arguments facultatifs, Microsoft se trompe sur la syntaxe : il faut lire :
=recherche(valeur_cherchée ;vecteur_recherche[;vecteur_résultat]) 
En effet, le crochet doit être avant le « ; » sinon vous obtenez une erreur quand il n’y a pas de vecteur_résultat !

Je suis en désaccord avec la déclaration de Microsoft dans la copie d’écran ci-dessous : la fonction Recherche() est bien plus simple et plus rapide que les fonctions RechercheH ou RechercheV ou a fortiori que les solutions de type Index(Equi(…)).

La seconde syntaxe de Recherche()

Un certain nombre d’utilisateurs se plaignaient auprès de Microsoft que le résultat de la recherche soit obligatoirement à droite ou en dessous.

En réponse à cette demande, des années plus tard, Microsoft a créé une seconde syntaxe :
=recherche(valeur_cherchée;vecteur_recherche;vecteur_résultat)

Dans ce cas, Excel note la position de la valeur de référence dans le vecteur de recherche et ramène, dans le vecteur de résultat, la valeur qui occupe la même position.

Remarque – Si vous avez l’esprit vicieux, rien ne vous empêche alors d’utiliser un vecteur de recherche vertical et un vecteur de résultat horizontal. C’est en effet idéal pour créer la confusion dans l’esprit de l’utilisateur !