Etudes comparatives de prix avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Etude comparative Excel - Appel d'Offre

Cette mise en pratique Excel propose de réaliser une étude comparative des prix proposés par différents fournisseurs d'une société, qui souhaite renouveler son parc informatique. Après trois appels d'offres, les conditions concédées par les fournisseurs ont évolué. Il s'agit de dresser un tableau de bord qui permette de suivre dynamiquement les propositions, afin de faire ressortir sans équivoque, la meilleure offre.


L'objectif est de mettre en oeuvre des fonctions d'extraction ainsi que des fonctions conditionnelles que nous avons apprises, pour livrer un modèle autonome et adaptable à toute société.

Extraction dynamique des conditions de remises
Comme toujours nous partons d'un tableau de bord pré-saisi afin de nous concentrer sur la mise en oeuvre des calculs.

• Télécharger le classeur appel-offre-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Le calcul du montant de la commande consiste à multiplier pour chaque fournisseur, la quantité achetée par le prix Hors Taxes du matériel. Les totaux avant remise s'en déduiront. Les totaux après remise quant à eux, doivent être calculés en fonction des pourcentages rapatriés en cellules F10, G10 et H10, selon le numéro d'offre saisi par l'administrateur en cellule B5.


Pour que ces taux puissent être extraits dynamiquement du tableau source afin d'être exploités par les calculs, nous devons utiliser une fonction Excel de base de données. Nous avions déjà exploité la fonction RechercheV, pour rechercher des données dans des tableaux en colonnes. Le tableau des appels d'offre entre D3 et G6 est transposé. La fonction Excel RechercheH est l'homologue de la fonction RechercheV. Elle permet d'extraire de l'information de base de données dans des tableaux présentés en lignes. Sa syntaxe est la suivante :

=RechercheH( valeur_cherchee ; tableau_de_recherche ; numero_ligne ; [Faux])

Quatre arguments sont nécessaires, le dernier étant facultatif. Tout d'abord, nous indiquons la valeur cherchée, elle doit nécessairement se trouver dans la première ligne du tableau de recherche. Il s'agit du numéro d'appel d'offre tapé en B5. Ensuite nous indiquons le tableau dans lequel la recherche doit être effectuée. Il s'agira de sélectionner le tableau complet des pourcentages. Puis, nous devons indiquer le numéro de ligne de l'information correspondante à extraire. Par exemple, pour obtenir le pourcentage du premier fournisseur Pc Azur, il s'agira de la ligne 2. Enfin, nous terminons avec l'argument booléen Faux pour indiquer à la fonction de ne pas essayer de se rapprocher du résultat si elle ne le trouve pas.

• Sélectionner la première cellule du calcul, soit F10,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse ouvrante, soit RechercheH(,
• Cliquer alors la cellule B5 pour indiquer la valeur cherchée,
• Taper un point-virgule pour passer à l'argument du tableau de recherche,
• Sélectionner le tableau complet soit la plage de cellules D3:G6,
• Taper un point-virgule pour passer à l'argument de l'indice de ligne,
• Saisir le chiffre 2 correspondant à la ligne du fournisseur Pc Azur,
• Taper un dernier point-virgule suivi du texte Faux,
• Fermer la parenthèse et valider le calcul,

La valeur de 5% est récupérée et s'affiche en cellule F10 sous le nom du fournisseur correspondant. Pour les deux autres prestataires en cellules G10 et H10, il s'agit de répliquer la même formule, en adaptant le troisième argument, celui du numéro de ligne pour l'information à extraire. Respectivement, il s'agit de remplacer le chiffre 2 par les valeurs 3 et 4.

• Adapter les formules de recherche en G10 et H10,

Toutes les informations sont ainsi automatiquement récupérées, grâce à la fonction de recherche. Si vous modifiez le numéro d'appel d'offre en B5, vous remarquez que tous les pourcentages de remises correspondants sont extraits et affichés instantanément. Grâce à cette technique, nous allons pouvoir bâtir des calculs liés qui vont rendre le modèle totalement dynamique et autonome. Chaque société pourra l'adapter à ses contraintes pour réaliser une étude comparative des prix.

Calculs des montants hors taxes - Comparaison des prix
Les montants hors taxes entre F11 et H13 doivent considérer les quantités achetées et les prix respectifs proposés par chaque fournisseur. L'objectif est de réaliser un seul calcul et de le répliquer sur toute la plage. Pour ce faire, la référence de la cellule de la quantité achetée doit être figée en colonne mais pas en ligne. En effet lorsque le calcul sera répliqué sur la droite, le montant HT pour chaque fournisseur sera bien multiplié par la quantité restée figée dans sa colonne. En revanche, pour chaque matériel sur les lignes du dessous, le montant HT sera bien multiplié par la nouvelle quantité correspondante de la ligne du dessous.

• Sélectionner l'ensemble des cellules à calculer, soit F11:H13,
• Taper le symbole = pour initialiser le calcul,
• Cliquer sur la première cellule de la quantité, soit B11,
• Enfoncer trois fois de suite la touche F4 du clavier,

De cette manière, la cellule est figée en colonne dans le calcul. Seul un dollar devant son indice de colonne B persiste ($B11). La cellule reste libre de se déplacer en lignes si la réplication du calcul lui ordonne.

• Taper l'étoile du pavé numérique (*) pour la multiplication,
• Puis cliquer la cellule du premier prix, soit C11,
• Valider ce calcul par le raccourci clavier CTRL + Entrée,

Comme la plage de cellules était présélectionnée, le raccourci CTRL + Entrée permet en même temps de valider le calcul et de le reproduire sur toutes les cellules. Cette technique permet d'éviter de tirer la poignée après calcul.

Le calcul des sommes avant remise est trivial. La méthode la plus efficace consiste à sélectionner tous les montants à sommer et à indiquer dans la sélection, les cellules dans lesquelles déposer les résultats.

• Sélectionner la plage de cellules F11:H14,
• Cliquer sur le bouton Somme automatique du ruban Accueil ou réaliser le raccourci ALT + =,

Tous les totaux sont instantanément livrés dans les cellules qui avaient été présélectionnées à cet effet. Il apparaît que le fournisseur Pc Azur est le plus avantageux avant appel d'offre.


Pour calculer les totaux hors taxes après remise, il s'agit de considérer les pourcentages concédés par les fournisseurs, rapatriés et inscrits en ligne 10. Si un fournisseur accorde 5% de remise, le client ne doit plus payer que 95% du prix, soit 100%-5% ou encore (1-5%). Donc en ligne 15, le calcul à réaliser est le suivant =TOTAL_HT*(1-REMISE). Aucune cellule n'est à figer dans ce calcul, puisqu'à réplication, les pourcentages et totaux des colonnes situés à droite, doivent être considérés.

• Sélectionner les cellules du calcul, soit la plage F15:H15,
• Taper le symbole = pour débuter la formule,
• Sélectionner le premier total hors taxes, soit F14,
• Taper l'étoile du pavé numérique (*) pour la multiplication,
• Ouvrir la parenthèse pour factoriser le calcul,
• Saisir le chiffre 1 suivi du symbole - et cliquer sur la première cellule de la remise, soit F10,
• Fermer la parenthèse et valider par CTRL + Entrée pour le répliquer sur les autres cellules,

La parenthèse était nécessaire dans ce calcul pour le factoriser. En mathématiques en effet, la multiplication est prioritaire sur la soustraction. Pour que la multiplication se réalise bien sur le résultat de la soustraction du pourcentage, cette soustraction doit être réalisée en premier. C'est le rôle des parenthèses. Ainsi le total Hors taxes est bien multiplié par 95% par exemple, soit (1-5%).

Comme nous le disions en préambule, grâce à l'extraction des remises réalisées par la fonction RechercheH, tous les calculs sont bâtis sur des variables. Et notre étude comparative de prix est bien dynamique. Pour preuve, si vous modifiez le numéro d'appel d'offre en B5, tous les totaux se mettent à jour en considérant notamment les nouvelles remises. Et comme l'illustre la capture ci-dessus, pour l'appel d'offre numéro 2, c'est le fournisseur EquipInfo qui devient le plus intéressant au profit de Pc Azur.

Synthèse statistique conditionnelle
Pour que ce tableau de bord soit pleinement efficace pour l'entreprise, il doit livrer dynamiquement l'information finale textuelle. Cette information est le nom du fournisseur préconisé en fonction de l'étude comparative des prix. Ainsi en ligne 16 il s'agit d'afficher le fournisseur qui propose le montant total le plus avantageux, selon l'offre choisie par l'administrateur. Un seul nom doit apparaître. Il s'agit donc de vérifier un critère (Fonction Excel Si) pour savoir si le montant du fournisseur est le plus petit de tous (Fonction Excel Min).

• Sélectionner les cellules du calcul, soit la plage F16:H16,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction conditionnelle suivi d'une parenthèse ouvrante, soit Si(,
• Sélectionner le premier total HT à comparer, soit la cellule F15,
• Taper le symbole = pour initier la comparaison,
• Saisir le nom de la fonction pour extraire le plus petit montant, suivi d'une parenthèse ouvrante, soit min(,
• Sélectionner les trois montants HT, soit la plage de cellules F15:H15,
• Enfoncer la touche F4 du clavier pour figer cette plage,
• Fermer la parenthèse de la fonction Min,

A ce stade, nous avons simplement posé le critère (=si(F15=min($F$15:$H$15)). La formule n'est pas terminée. La comparaison F15=min($F$15:$H$15) consiste à savoir si le premier total HT en F15 est le plus petit des trois : =min($F$15:$H$15). Nous figeons cette plage de cellules afin de pouvoir répliquer le calcul sur les colonnes de droite, en continuant de faire référence toujours aux mêmes cellules pour la comparaison. Si cette condition est remplie, c'est alors le nom du fournisseur inscrit en ligne 9 qui doit apparaître. Dans le cas contraire, la cellule doit rester vide.

• Taper un point-virgule pour passer dans la branche Alors de la fonction Si,
• Cliquer sur la cellule du nom du premier fournisseur, soit la cellule F9,
• Taper un nouveau point-virgule pour passer dans la branche Sinon de la fonction Si,
• Ouvrir et fermer les guillemets pour obtenir une cellule vide de contenu dans ce cas,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée pour le répliquer,

Nous avons construit le calcul suivant : =SI(H15=MIN($F$15:$H$15); H9; ''), permettant d'afficher sans ambiguïté le nom du fournisseur apparaissant comme le plus avantageux, après étude comparative des prix. Une fois encore, cette information sans équivoque se met dynamiquement à jour, si vous modifiez le numéro d'appel d'offre en B5.

Les décisionnaires n'ont pas besoin de lire et comprendre tout le tableau pour tirer des conclusions. Il suffit de prendre en considération seulement l'information finale livrée par ces extractions dynamiques. Notre tableau de bord pour l'étude comparative des prix sur appel d'offre est terminé.