Monsieur Excel
Pour tout savoir faire sur Excel !

09 novembre 2019

Ventes vs. objectif (a)

Nous nous attaquons aujourd’hui à un type de graphe que l’on rencontre facilement en entreprise. Il s’agit de comparer des chiffres d’affaires par rapport aux objectifs qui avaient été fixés auparavant.

Ce que l’on voit souvent en entreprise, c’est le premier des deux graphes représentés dans la copie d’écran en bas de l'article, avec par exemple le chiffre d’affaires en bleu et l’objectif en vert.

Ce que nous souhaitons obtenir, c’est le second graphe.

Etape 1 : Superposition des séries

La première étape consiste à superpose les séries. Pour cela, faisons un clic droit dans une des barres de la seconde série, prenons la commande Mettre en forme une série de données, et entrons 100% dans Superposition de séries et dans Largeur de l’intervalle puis validons.

Etape 2 : Modification des séries

La seconde étape consiste à sélectionner la série des objectifs et à ôter son remplissage pour ne garder qu’un cadre vert. Puis on sélectionne la série des ventes et on enlève sa bordure.

Et nous obtenons ainsi le résultat voulu. Bien entendu, vous pouvez personnaliser ce graphe à votre façon, avec d’autres couleurs et/ou cadres et éventuellement divers effets graphiques.



03 novembre 2019

Création d’un graphe générique (c)

Créons à présent un troisième onglet et entrons-y les éléments suivants. Dans le bloc A1:D5, nous entrons la description des quatre séries, l’adresse des abscisses, celle des ordonnées et le type de donnée : monnaie (€) ou nombre (0).

Pour la définition de B2, par exemple, nous entrons « = », puis nous allons chercher les abscisses de la première série, nous validons, puis nous remplaçons le « = » de la formule par une apostrophe. Le contenu de la cellule débute alors par deux apostrophes côte à côte… Nous faisons alors de même pour le bloc B2:C5 afin d’identifier les 4 séries de coordonnées en X et en Y. Nous créons alors le bloc G1:K2 en utilisant les formules affichées en commentaire.

En D2, nous définissons une liste de validation par =$A$2:$A$5. On peut ainsi sélectionner la série voulue.

Nous définissons alors Mes_X par =indirect(Chart!$H$2) et Mes_Y par =indirect(Chart!$I$2).

Il ne reste alors plus qu’à rendre le graphe dynamique en sélectionnant la série graphique et – dans la barre de formules – en remplaçant ce qui suit le « ! » dans la formule des X par Mes_X et ce qui suit le « ! » dans la formule des Y par Mes_Y.

Et voilà, le tour est joué !



27 octobre 2019

Création d’un graphe générique (b)

Avant de créer un graphe générique, il est bon, dans une première étape, de créer un premier graphe ayant déjà les caractéristiques générales du graphe que nous souhaitons obtenir au final. Pour cela, plaçons-nous dans le premier onglet, celui qui contient 3 des 4 séries, sélectionnons la zone A1:B18 et créons un graphe en courbe 2D.

Nous obtenons alors aussitôt le premier des deux graphes de la copie d’écran ci-dessous. Notre premier objectif est d’améliorer son look pour qu’il ressemble au second graphe dans la copie d’écran.


Tracé de la courbe

Faisons un clic droit sur la série puis sélectionnons la première icône (Remplissage et trait), sélectionnons « Trait plein » dans la famille « Courbe » et prenons la couleur bleue. Sélectionnons alors la famille « Marque » et sélectionnons une marque de forme carrée, de taille 6 et avec un remplissage jaune. A présent, la courbe et ses marques sont comme nous le désirions.

Quadrillage vertical

Nous souhaitons marquer le quadrillage vertical avec un trait bleu. Pour cela, cliquons dans la zone de traçage, sélectionnons le menu « Création de graphique » puis sa première commande, « Ajouter un élément graphique » puis, successivement, « Quadrillage » et « Vertical majeur principal ». Nous avons à présent des lignes verticales associées aux dates. Faisons à présent un clic droit sur l’une de ces barres verticales et sélectionnons la couleur bleue.

Abscisses et ordonnées

Nous pouvons à présent mettre en gras les étiquette des abscisses et des ordonnées. Pour cela, il suffit de cliquer sur les étiquettes de l’axe et d’utiliser les commandes de l’onglet « Accueil ». De la même façon, nous pouvons ôter les « € » de l’axe des Y.

Titre du graphe

Il ne reste plus qu’à sélectionner le titre du graphe et à entrer le nouveau titre à la main. Dans l’article suivant, nous verrons comment automatiser cette tâche.

En guise de conclusion

Nous avons à présent un graphe qui correspond à notre objectif. Il ne reste plus – et nous le verrons dans le prochain article – qu’à définir un menu déroulant par lequel nous sélectionnerons laquelle des quatre séries nous voulons représenter dans le graphe.

20 octobre 2019

Création d’un graphe générique (a)

J’ai été confronté au problème que je vous présente aujourd’hui il y a quelques années, quand je travaillais comme consultant pour Aéroports de Paris. J’ai en effet réalisé pour AdP un grand nombre de modèles entre 2000 et 2012.

Le premier de ces modèles simulait des tapis roulants portant les bagages des passagers. Il se trouvait qu’AdP louait à Air France ces tapis roulants et qu’Air France, estimant que leur efficacité n’était pas à la hauteur, réclamait d’importants dommages et intérêts à AdP.

AdP a donc lancé un appel d’offres pour l’analyse de ces tapis roulants avec deux objectifs : d’une part, trouver des arguments pour contrer les attaques d’Air France et, d’autre part, utiliser le modèle de simulation des tapis roulants pour améliorer leur efficacité.

AdP a eu un problème avec cet appel d’offres. Mon devis était de 300.000 F et le moins cher des concurrents était à 1.500.000 F. J’ai failli ne pas obtenir la commande d’AdP à cause de cet écart mais ce qui m’a sauvé, c’est que, dans la commission analysant l’appel d’offres, un des membres appartenait à une société pour laquelle j’avais réalisé un modèle qui avait très bien marché.

Ils se sont alors dit : « Si l’on prend cette solution et qu’elle ne marche pas, on aura perdu 3 mois et 20% du budget. Si en revanche elle marche, on gagne une année et on économise 80% du budget ». Ils ont pris le risque, et cela a tellement bien marché que j’ai ensuite réalisé des modèles pour AdP durant plus de 10 ans.

Le problème dont je parle aujourd’hui est le suivant. Une responsable d’AdP avait un gros modèle pour analyser, durant la journée, le nombre de piétons – minute par minute – à plusieurs endroits sensibles de l’aéroport. Ce modèle faisait plus de 40 Mo. Quand on l’ouvrait, il plantait Excel une fois sur deux. Quand on le fermait, ou même quand on faisait des modifications, cela prenait du temps…

Il y avait un certain nombre d’onglets, avec plusieurs graphes par onglet.

Quand j’ai terminé mon intervention, bien que j’aie ajouté au modèle initial de nouvelles fonctionnalités, sa taille était tombée à moins de 10 Mo, et tous les problèmes avaient disparu.

Comment y suis-je arrivé ? En fait, c’est tout simple… J’ai éliminé tous les graphes de tous les onglets et j’ai créé un nouvel onglet, avec un graphe unique, mais contenant des menus déroulants et avec lequel on pouvait générer 8.064 graphes différents.

Je vous propose donc de trouver une solution de ce genre. Vous avez quatre séries de données, les trois premières dans un onglet, la dernière dans un autre onglet (photo encadrée de bleu).

Essayez de créer, sur un troisième onglet, un graphe contenant un menu déroulant permettant de choisir la série à représenter. Voici les séries sur lesquelles nous allons travailler, mais rien ne vous empêche d’utiliser quatre autres séries de votre cru :



14 octobre 2019

Les MVPs Excel en Europe

Dans mon article du 27 septembre dernier - Renouvellement comme MVP – je vous ai montré comment l’on pouvait trouver les MVPs en fonction de leur pays et/ou de leur spécialité. Comme je l’ai indiqué, il n’y a dorénavant plus de « MVP Excel ». On doit donc aller à la pêche pour les trouver.

J’ai fait ce travail pour identifier les MVP spécialistes d’Excel dans la plupart des pays européens. Voici donc le résultat de mes recherches avec, derrière le nom de chacun, l’année de sa première nomination et le nombre de fois où il a été nommé MVP.

Dans le reste de l’Europe

UK : Charles Williams (2009, 11) et Roger Govier (2007, 13).
Pays-Bas : Hans Vogelaar (2010, 9), Jan Karel Pieterse (2002, 17), Ron de Bruin (2002, 17), Tony De Jonker (2015, 5).
Russie : Sergei Baklan (2017, 3).
Espagne : Ismael Romero (2014, 6), Segundo Miguéns Romeu (2017, 3), Yolanda Cuesta (2014, 6).
Pologne : Adam Kopeć (2017, 3).
Danemark : Dan Elgaard (2017, 3).
Allemagne, Belgique, Bulgarie, Croatie, Grèce, Hongrie, Italie, Norvège, Portugal, Suède, Suisse, Turquie : Aucun MVP Excel.

Et en France…

L’image suivante montre ce que l’on obtient pour la France :

Il y a trois MVPs spécialistes d’Excel en France : Frédéric Le Guen (2011, 9), Michel Martin (2004, 16) et moi (2009, 11).

Michel Martin n’est pas consultant, il écrit des livres et réalise des vidéos sur une variété de produits Microsoft, dont Excel.

Frédéric Le Guen vient de rejoindre Logma, la société de conseil que j’ai fondée il y a 40 ans.
Il est spécialiste en Power BI (Power Query et Power Pivot).

Je suis pour ma part spécialiste de la création de modèles Excel, de leur audit et amélioration, ainsi que de formations Excel. J’ai créé plus de 1.000 modèles pour plus de 100 entreprises en plus de 10 pays.

Quelque chose qui n’a rien à voir avec Excel…

Lors de la loghorrée médiatique suivant l’arrestation présumée de Xavier Dupont de Ligonnès (XDDL) en Ecosse, j’ai entendu à la télévision, qui planchait non-stop sur le sujet durant la fameuse nuit, un commentaire particulièrement pointu.

On venait d’annoncer un bruit selon lequel le détenu suspect ne ressemblait pas beaucoup à XDDL. 

Et le journaliste télévisuel, à qui je dédie à cette occasion le Sherlock d’or de la Déduction Scientifique (mais sans le nommer, par délicatesse), en a déduit que cela augmentait la probabilité que ce soit bien XDDL car ce dernier était réputé pour sa capacité à changer régulièrement son look de façon notable.

04 octobre 2019

Une fonction personnalisée étrange

Mon collègue MVP Jon Peltier vient de publier une fonction personnalisée étrange, car elle fait deux choses que les fonctions personnalisées ne sont pas censées faire : elle permet de modifier la couleur des cellules servant d’argument à la fonction, ainsi que celle de la cellule contenant la fonction.

Voici le code de la fonction personnalisée :

Public Function UDF_SUM(v As Variant)
  If TypeName(v) = "Range" Then
    Debug.Print v.Address
    v.Font.Color = vbRed ' not supposed to, but does
    v = v.Value
  End If
  Dim i As Long, j As Long
  For i = LBound(v, 1) To UBound(v, 1)
    For j = LBound(v, 2) To UBound(v, 2)
      UDF_SUM = UDF_SUM + v(i, j)
    Next
  Next
  Application.Caller.Font.Color = vbBlue ' not supposed to, but does
End Function

La copie d’écran ci-dessous montre un tableau où la ligne 10 fait appel à cette fonction.

Nous constatons qu’après revalidation de la formule en D10, les arguments de la fonction sont bien passés en rouge, et que le résultat de la fonction est bien passé en bleu.


27 septembre 2019

Renouvellement comme MVP

Cet été, j’ai été – pour la 11ème année de suite
– nommé MVP (Most Valuable Professional) par Microsoft. 
Voici ce que Microsoft dit des MVP :

Les MVP (Most Valuable Professionals) Microsoft sont des experts en technologie qui partagent avec passion leur savoir avec la communauté. Ces experts font toujours partie de « l’avant-garde » et ressentent le besoin de découvrir de nouvelles technologies excitantes. Ils possèdent une connaissance approfondie des produits et services Microsoft tout en étant également capables de combiner diverses plateformes, produits et solutions pour résoudre les problèmes du monde réel. Les MVP représentent une communauté mondiale de plus de 4 000 experts techniques. Ce sont des Community Leaders répartis dans 90 pays/régions et motivés par leur passion, leur esprit de communauté et leur quête de savoir. Avant d’être des experts aux compétences techniques exceptionnelles, les MVP sont surtout des personnes toujours prêtes à aider les autres et c’est ce qui les démarque.

Pour résumer, les MVP ont une double dimension : d’une part, ce sont des professionnels reconnus dans une des spécialités de Microsoft ; d’autre part, ce sont des personnes qui fournissent une aide bénévole (blog, conférences, aide en ligne,…).

La nouveauté du programme, c’est que l’an dernier Microsoft a décidé, pour se simplifier la vie, de regrouper les titres afin d’en réduire significativement le nombre. Dorénavant, il n’y a plus de « MVP Excel » à proprement parler, mais des MVP « Office Apps & Services » qui regroupe les experts d’Excel, de Word, d’Access, de Powerpoint,…

Le seul gagnant dans l’opération est Microsoft. Les MVPs perdent clairement en visibilité. Les utilisateurs perdent aussi du temps car, s’ils cherchent un MVP Excel, ils vont devoir filtrer à travers tous les thèmes regroupés dans « Office Apps & Services ».


Quand vous utilisez le site « Find an MVP » et que vous renseignez les cases comme dans la copie d’écran, vous obtenez 20 personnes. Si maintenant vous entrez le mot-clef « Excel » dans « Keyword » et validez la recherche, le champ se réduit à 8 personnes.

Même cela, c’est loin d’être parfait ! En effet, si vous regardez les références de chacune de ces 8 personnes, vous découvrez que 3 d’entre elles ne citent jamais Excel dans ces références, et que 2 ne citent Excel que de façon très minoritaire. En conclusion, il y a aujourd’hui 3 MVP Excel en France !