Monsieur Excel
Pour tout savoir faire sur Excel !

14 septembre 2018

Modèle de facturation (b)

Voici l’explication sur la façon dont on doit calculer le prix de vente :
  • le prix de vente est le prix de base, multiplié par l'index fournisseur (table B1:C2), plus une marge de 40% :
  • il vaut donc 1,4*Base*Index fournisseur.
Ce que je vous propose, dans cet article, c’est de vous montrer une première formule en D5, la plus simple que nous puissions entrer pour répondre à la question. La voici :
=1,4*C5*si(E5=1;$C$1;$C$2)

Avant de lire la suite de cet article, réfléchissez bien et essayez d’identifier ce qui est la pire chose dans cette formule, et éventuellement la seconde chose la plus mauvaise.

En fait, nous allons procéder dans cet article comme un expert qui analyse la formule et essaye de l’améliorer étape par étape. Pour cela, il faut bien évidemment commencer par s’attaquer au problème le plus grave, d’où ma question.

Une première amélioration de la formule

Si vous avez répondu « C’est le fait que la formule ne marche qu’avec deux fournisseurs ! », vous aurez certes donné la réponse que j’entends le plus souvent lors de mes formations, mais ce n’est pas la bonne…

Le problème le plus grave provient du début de la formule : le 1,4 est ce que l’on appelle une constante cachée. Cela entraîne deux problèmes. Le premier est que l’utilisateur qui voit le 32,55 en D5 ne peut pas comprendre comment ce résultat a été obtenu s’il ne voit pas la formule. Le second est que, si l’on est amené à modifier la marge, par exemple parce que la concurrence devient plus sévère, il faudra modifier la formule.

La solution consiste à « externaliser » la marge, en entrant « Marge » en G1 et 40% en G2.

La formule de D5 devient : =(1+$G$2)*C5*si(E5=1;$C$1;$C$2).

Remarque – On peut noter que, pour améliorer l’auditabilité du modèle, il serait intelligent d’attribuer le nom Marge à G2 et de remplacer $G$2 dans la formule par Marge.

Seconde amélioration : fournisseurs supplémentaires

Je n’ai eu aucune hésitation pour identifier ce qu’il fallait faire pour la première amélioration, mais je suis un peu plus partagé pour ce qui est la seconde amélioration nécessaire. Ceci dit, la limitation par le SI() à deux fournisseurs n’est pas bonne, ce qui nous amène à la troisième formule :
=(1+$G$2)*C5*index($C$1:$C$2,E5)

Troisième amélioration : des factures exactes

Calculez dans une cellule combien seront facturées 100 unités du premier article. Vous constatez que vous obtenez 3.554,89 € au lieu des 3.555 € attendus.

C’est bien gênant car, ce modèle servant à produire des factures, il se doit d’être irréprochable. Il nous faut donc arrondir le prix de base. Selon votre gourmandise ou votre sens commercial, vous avez alors le choix pour cela entre Arrondi(), Arrondi.Sup et Arrondi.Inf.

Voici la formule : =arrondi((1+$G$2)*C5*index($C$1:$C$2,E5);2)

Une dernière amélioration…

Supposons que nous insérions deux lignes en dessous de la ligne 2 pour prendre en compte deux nouveaux fournisseurs. Nous constatons alors que les deux nouveaux fournisseurs ne sont pas pris en compte dans le tableau $C$1:$C$2.

La solution consiste à utiliser – dans le tableau original, donc avant l’insertion des deux lignes – la formule suivante qui ajoute une ligne banche en bas de la table :
=arrondi((1+$G$2)*C5*index($C$1:$C$3,E5);2)

Du coup, on peut insérer autant de nouveaux fournisseurs que l’on veut, la formule sera toujours bonne !

En guise de conclusion

Ce qui est impressionnant, c’est que – pour cette seule première formule – nous sommes parvenus à définir cinq formules avec quatre améliorations successives !



07 septembre 2018

Modèle de facturation (a)

En tant que consultant, je crée pour mes clients des modèles Excel en leur faisant du « cousu main ». Mais mon activité ne se limite pas à la création de modèles : je fais aussi de l’audit/amélioration de modèles existants. Les raisons possibles pour ce genre d’intervention sont multiples.

Réparation d’un modèle dont on a perdu le contrôle

Cela m’est arrivé pour une grande banque. Elle menait une série d’investissements routiers en association avec une banque italienne et une banque allemande. Des cadres des trois banques avaient chacun effectué des modifications sans se concerter avec leurs partenaires des deux autres pays.

On en était arrivé à ce que le classeur avait des liens avec des classeurs que personne ne savait localiser, et à ce qu’un certain nombre de formules affichent des erreurs que les cadres ne parvenaient pas à corriger. Le modèle était devenu totalement incontrôlable et je représentais la dernière chance pour le réparer.

Amélioration d’un modèle ne donnant pas satisfaction

J’ai eu avec deux clients, une grande société pharmaceutique et un fournisseur de téléphonie, le même genre de problème. Un modèle qui leur servait de façon quotidienne pour le suivi de gestion et était arrivé, à force d’enrichissements successifs aboutissant – par la force des choses – à alourdir progressivement le modèle, à devenir très lent en calcul.

Dans les deux cas, le temps ce calcul du modèle était de l’ordre de 2 minutes. Certes, me direz-vous, cela n’empêche pas l’utilisation du modèle. Mais il faut reconnaître que ce n’est pas pratique.

Dans les deux cas, je suis parvenu à diviser par 100 le temps de calcul, c’est-à-dire a obtenir le calcul en une seconde environ.

Réduction drastique du nombre d’onglets et/ou de classeurs

Pour plusieurs de mes clients, le nombre d’onglets d’un classeur a été réduit de façon drastique, ce qui bien évidemment apporte un gain de type win/win à l’ensemble du classeur : temps de calcul, taille mémoire, fiabilité,…

Pour une grande société pharmaceutique, j’ai réussi à réunir en un seul classeur tous les classeurs (plus de 150) utilisés au départ pour la gestion de son plan clinique.

Test sur un modèle concret

Dans cet esprit, je vous propose de voir, sur un exemple concret, comment on peut créer des formules originales puis, comme pourrait le faire un consultant, améliorer les formules pas à pas pour les rendre plus efficaces : plus justes, plus facilement auditables, plus compactes…

Il s’agit d’un mini-modèle de suivi de stocks et de facturation. Les explications sur l’objectif à atteindre sont fournies dans la colonne K.

Voici le lien pour charger le fichier :




31 août 2018

Contrôle de bagages

Aujourd’hui, nous présentons, dans la série des modèles Excel développés pour l’aéroport de Roissy, un petit modèle simulant des bagages qui passent devant la machine à rayons X. Si le contrôle est positif, le bagage continue en haut à droite vers les avions. Dans le cas contraire, il part vers le bas où il sera décortiqué par les douaniers. Les « bons » bagages sont représentés par un nombre en bleu et les mauvais par un nombre en rouge.

Le curseur vertical dans le grand rectangle permet de modifier l’heure actuelle. Mais, en cliquant dans le bouton Animation, on lance un film qui représente tout ce qui se passe de 8:00 à 8:20.

Comme ce modèle peut être utilisé sur des machines plus ou moins puissantes, j’ai ajouté à droite du rectangle un second curseur qui permet de modifier la vitesse du film.


Une représentation graphique originale

Ce qui est tout à fait original dans ce modèle, par rapport aux modèles précédents sur l’aéroport de Roissy, c’est que – cette fois-ci – le graphe n’est pas illustré par un graphe dans Excel. En fait, nous sommes dans une feuille de calcul et chaque cellule affiche soit rien, soit un nombre positif représenté en bleu, soit nombre négatif représenté en rouge et en positif.

Le format des cellules est donc le format [bleu]0;[rouge]0; qui affiche les nombres positifs en bleu, les nombres négatifs en positif et en rouge, et qui masque les valeurs nulles.

Et nous avons masqué l'affichage des en-têtes et du quadrillage pour cacher le fait que l'on était tout simplement dans une feuille de calcul...

En guise de conclusion

Nous voyons donc que l’on peut aussi représenter un modèle graphique, et même animé, sans passer par les graphes d’Excel…

A titre indicatif, voici la formule de la cellule affichant le 25, juste après le scanner :
=SI(ESTNA(H42);0;INDEX(Bag;H42)*SI(INDEX(Pbm;H42)=1;-1;1))

Le résultat est positif pour un bon bagage, négatif pour un mauvais et 0 (et donc masqué) s’il n’y a pas de bagage là en cet instant.

24 août 2018

Piétons dans terminal (b)

Chacun des blocs représentant le décor graphique de cette simulation, à part le premier bien entendu, a été collé comme une nouvelle série, de la même façon que nous avions ajouté l’hexagone des solutions réalisables dans l’article du 10 août dernier.

Tracé du kiosque central


Ainsi, pour le kiosque central, nous avons répertorié les coordonnées (X,Y) de chacun des points servant à dessiner le kiosque – dans les deux colonnes à droite de la copie d’écran – et le résultat est le graphe qui apparaît à gauche de la copie d’écran.

Représentation des piétons

Les piétons sont représentés par des points bleus, à partir de leurs coordonnées (X,Y). Les formules dans le modèle calculent, à un instant donné, où se trouve le piéton.

Voici, à titre d’exemple, la formule calculant la coordonnée X d’un piéton. Comme vous pouvez le constater, ce n’est quand même pas d’une simplicité biblique :

=SI(I44=0;NA();DECALER(W44;0;2*($F44-1))+(DECALER(W44;0;2*$F44)-DECALER(W44;0;2*($F44-1)))*($E44-$G44)/($H44-$G44))

Ce qu’il faut retenir de cette formule, c’est l’utilisation du NA() : dans un graphique, les points qui ont des coordonnées en #NA! ne sont tout simplement pas affichés. C’est ainsi que l’on ne montre un piéton que si, à l’instant donné, il se trouve physiquement dans l’aérogare…

17 août 2018

Piétons dans un terminal (a)

Dans l’article du 28 juillet dernier, nous avons vu comment représenter dans Excel les droites illustrant de façon graphique un problème d’optimisation.

Dans celui du 10 août, nous avons vu comment inscrire dans cette représentation graphique un hexagone représentant le domaine des solutions réalisables.

J’ai commencé à réaliser des modèles sur Excel pour Aéroports de Paris (AdP) en 2000, et notre collaboration a duré près de 15 ans. Durant tout ce temps, j’ai réalisé des modèles pour simuler les tapis roulants portant les bagages, le déplacement des piétons dans les aérogares, le trafic routier dans la zone aéroportuaire, les réaffectations éventuelles en temps réel de terminaux aux avions en tenant compte de leur avance ou retard prévu, …

L’exemple présenté plus bas est un modèle simulant le déplacement des piétons dans une aérogare. Les avions se trouvent aux points A et B, les passagers arrivant pour prendre un avion, ou voulant sortir de l’aérogare sortant par le point E/S.

Les trois zones d’attente sont utilisées par les passagers en attente d’un vol.


Le modèle permet, en déplaçant le curseur à gauche, de choisir l’heure à laquelle on veut voir les passagers. Enfin, le bouton Animation lance un film affichant l’évolution du trafic piétonnier dans l’aérogare pendant un certain temps.

Grâce à ce modèle, on pouvait tester les conséquences sur le trafic piétonnier de telle ou telle modification dans le dessin de l’aérogare. Par exemple, si l’on met des toilettes pour les passagers qui viennent de débarquer et arrivent près du point E/S, on a un choix. Avec des toilettes d’un seul côté, on risque de provoquer des embouteillages de passagers autour du point E/S. En dupliquant des toilettes à gauche et à droite, c’est plus coûteux, mais on résout ce problème de trafic piétonnier…

Nous ferons dans l’article suivant quelques remarques sur la conception de ce modèle.

10 août 2018

Représentation graphique (c)

Notre graphe serait encore plus joli si l’on pouvait illustrer graphiquement la zone des solutions réalisables limitée par l’hexagone démarrant à l’intersection des deux axes. C’est ce que nous avons fait en la représentant, comme on peut le voir dans la copie d’écran ci-dessous, avec un fond jaune clair.

Pour cela, nous avons utilisé la commande InsertionFormes et sélectionné, comme on peut le voir dans la copie d’écran dans l’encadré rouge, l’avant-dernière commande de la famille Traits.

Nous avons alors cliqué à chacun des six sommets de l’hexagone, en prenant soin de cliquer un tout petit peu à l’intérieur, puis en terminant par un clic sur le point de départ. La surface de l’hexagone apparaît alors aussitôt avec une couleur de remplissage par défaut.

Il ne reste plus qu’à la remplacer par un fond jaune, puis à lui accorder une transparence de 75% afin que la droite à optimiser (en rouge) ne soit pas masquée par le fond jaune.


Remarque – Si vous n’êtes pas sûr de bien viser, avant de créer l’hexagone coloré, zoomez de façon à bien agrandir la zone. Il vous sera alors plus facile de définir ses sommets, avant de revenir à un affichage normal pour bien apprécier le résultat final.

Pour les noms des axes, nous n’avons pas eu recours à la mise en forme du graphe. Nous avons tout simplement inséré des zones de texte avec aucun trait et aucun remplissage. Cela nous permet de les positionner exactement là où nous les voulons.

Ce qui est intéressant, avec cet exemple que nous avons développé en trois articles, est qu’il montre bien l’on peut facilement, et rapidement, créer avec Excel un modèle graphique alliant l’esthétique à l’efficacité.

04 août 2018

Représentation graphique (b)

Pour mieux utiliser le modèle développé dans l’article précédent, ce serait sympa si l’on pouvait déplacer la droite rouge de la fonction économique, parallèlement à sa position actuelle, pour modifier le montant du bénéfice et trouver la combinaison (BR1 ; BR2) qui maximise ce profit.

Pour cela, il suffit de modifier la valeur de la cellule H14, la formule en G15 garantissant que le déplacement de la droite se fera parallèlement à elle-même.

Nous allons insérer un curseur pour pouvoir le faire plus facilement. Pour cela, il faut utiliser une commande de l’onglet Développeur. Cet onglet n’est pas installé par défaut avec Excel. S’il ne l’est pas chez vous, passez par la commande FichierOptionsPersonnaliser le ruban et cochez Développeur dans le bloc à droite.

Dans l’onglet Développeur, la commande Insérer affiche l’image que nous avons reproduite en A9:C17. Nous choisissons alors la troisième commande de la deuxième ligne du bloc Contrôles de formulaire, puis nous traçons la barre de défilement que nous avons placé en colonne J.

Nous faisons alors un clic droit sur la barre de défilement pour accéder à la commande Format de contrôle que nous remplissons comme dans le bloc encadré de vert.

Avec ces réglages, la droite de la fonction économique passe par le dernier point de l’hexagone des solutions réalisables, la solution optimale en (BR1 ; BR2), comme on peut le voir dans la copie d’écran.


Remarque – Le changement de pas est la quantité ajoutée ou soustraite quand on clique dans une des deux flèches de la barre de défilement. Le changement de page est la quantité ajoutée ou soustraite quand on clique dans la bande grisée de la barre de défilement.