Taux de remises variables selon la quantité achetée

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


Sujets que vous pourriez aussi aimer :


Taux de remises variables selon quantités achetées

Dans cette formation Excel, nous emboîtons le pas de la précédente qui nous avait permis de réaliser des calculs de totaux, en considérant des taux de Tva et taux de remises variables, à appliquer selon la catégorie des produits achetés. Nous avions à ce titre démontré les limites de la fonction conditionnelle Si, lorsque le nombre de conditions à vérifier devenait important. Pour pallier la difficulté, nous avions donc mis en oeuvre la fonction d'extraction RechercheV qui nous avait permis d'extraire, directement depuis le calcul, le taux de remise correspondant à la catégorie, pour obtenir le résultat dynamique, tout en simplifiant considérablement la syntaxe du calcul.

Ici, le problème se corse. Il s'agit d'appliquer des taux de remises sur le montant total de la facture, en fonction de la quantité achetée. Il n'y a plus de règle stricte, comme la correspondance entre les catégories pour extraire le bon taux de remise. Toutes les quantités ne peuvent être listées à l'avance. Des remises sont accordées par tranches ou intervalles. C'est ce qu'illustre le petit tableau en bas à gauche de la capture ci-dessous.



Et c'est encore la fonction RechercheV qui apporte la solution, capable d'envisager un nombre illimité de conditions pour produire le bon taux à appliquer. Mais la subtilité est importante. Elle réside dans le dernier paramètre qu'elle attend.

Sources et présentation de la problématique
La figure ci-dessus présente le résultat de l'application finalisée. Pour concentrer nos travaux sur l'élaboration de la formule, nous proposons de le récupérer, sans les calculs bien sûr.

• Télécharger le classeur calculs-remises-selon-quantites.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur n'est constitué que d'une seule feuille nommée Remises_quantites. Le premier tableau représente les ventes réalisées par article. Pour chacun, le montant total hors taxes est calculé en tenant compte du prix unitaire et de la quantité vendue. Et précisément, un deuxième tableau énumère les remises qui doivent être appliquées, selon ces quantités.

Chaque quantité achetée ne peut être prévue précisément à l'avance. C'est pourquoi, ces remises sont proposées par paliers. On parle aussi de représentations par tranches. Dans le calcul, il ne s'agit plus de trouver précisément la quantité correspondante dans le petit tableau, afin d'appliquer la bonne remise. Il s'agit de trouver la tranche dans laquelle elle se situe, pour récupérer le pourcentage correspondant. Nous avions solutionné un tel cas dans la formation Excel permettant de calculer les primes des salariés selon leur ancienneté.

Extraire une valeur d'un intervalle
Il va de soi que la fonction Si n'est pas la solution. Elle ne l'était d'ailleurs pas plus dans un cas plus simple. Dans la formation précédente, les remises étaient appliquées selon les catégories des produits. Et bien que la correspondance fût exacte, nous avions prouvé à quel point la fonction RechercheV était plus efficace. Le calcul d'un total remisé s'effectue selon la syntaxe factorisée suivante :

=Tht*(1 - pourcentage_remise)

Le pourcentage de remise doit être renseigné dynamiquement par la fonction RechercheV. Elle doit donc être imbriquée dans la formule. La syntaxe de la fonction RechercheV est la suivante :

=RechercheV(Valeur_à_chercher ; Tableau_de_recherche ; Numero_colonne_retour ; Faux)

La valeur cherchée est la quantité achetée pour le produit en cours de calcul. Elle doit être trouvée dans le tableau de recherche, celui des tranches, soit la plage de cellules B12:C17. C'est elle qui doit permettre d'identifier la remise correspondante. Et précisément, le numéro de colonne en retour, correspond à la colonne dans laquelle se situe cette remise. Nous indiquerons donc le chiffre 2 pour la désigner. Enfin, le dernier argument est un paramètre booléen. Nous le laissons réglé à False ici, comme c'est le cas dans la majorité de ses utilisations. Il permet de réaliser une recherche selon une correspondance exacte.

• Sélectionner la première cellule pour le calcul du total remisé, soit G3,
• Taper le symbole = pour débuter la formule,
• Sélectionner le premier total hors taxes, soit la cellule F3,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Ouvrir une parenthèse pour la factorisation,
• Taper le chiffre 1 suivi du symbole - pour la soustraction, soit 1-,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit RechercheV(,
• Sélectionner la première quantité, soit la cellule E3 pour désigner la valeur cherchée,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner la plage de cellules B12:C17 des remises sur quantités,
• Enfoncer la touche F4 du clavier pour figer ses bornes,

En effet, lorsque nous répliquerons le calcul sur les lignes du dessous, les références du tableau de recherche, ne doivent pas s'adapter. Les bornes doivent rester figées car les éléments à trouver sont toujours situés aux mêmes emplacements.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Saisir le chiffre 2 pour désigner la deuxième colonne dans la sélection du tableau,
• Taper un point-virgule suivi du paramètre Faux, soit : ;Faux, pour une correspondance exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Puis, fermer la parenthèse de la factorisation qui l'englobe,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour garder la cellule active,

Comme vous le constatez, une erreur est retournée : #N/A. Elle signifie Not Availiable en américain, soit non disponible. L'élément cherché n'a vraisemblablement pas été trouvé. Nous l'avons générée volontairement pour mieux comprendre cette fonction. Nous saurons ainsi mieux appréhender les cas dans lesquels sa syntaxe doit être adaptée.

• Double cliquer néanmoins sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Aussi étonnant que cela puisse paraître, certains totaux remisés sont parfaitement calculés. La raison est pourtant évidente. C'est le dernier argument de la fonction RechercheV qui est en cause. Le paramètre booléen Faux ordonne à la fonction de réaliser une recherche selon une correspondance exacte. Et les deux résultats correctement calculés proposent des quantités rondes, précisément référencées dans le tableau des remises. Elles sont donc trouvées et la remise correspondante est retournée. De fait, le calcul aboutit.

Dans tous les autres cas, il s'agit de quantités non référencées. Elles ne peuvent pas être toutes prévues. Par contre, elles appartiennent bien à une tranche. Et c'est ainsi que nous devons demander à la fonction RechercheV de raisonner. Elle doit considérer la valeur la plus proche dans l'intervalle.

• Sélectionner de nouveau la première cellule du calcul, soit G3,
• Enfoncer la touche F2 du clavier pour activer sa saisie,
• Remplacer le paramètre FAUX par VRAI,
• Valider la modification par le raccourci CTRL + Entrée,
• Puis, double cliquer sur la poignée du nouveau résultat pour répliquer la formule,

Comme vous le constatez, cette simple modification corrige le défaut. Lorsque la fonction RechercheV ne trouve pas la valeur demandée, elle se cale sur la plus proche, directement inférieure. De fait, elle retourne le pourcentage de remise correspondant à la tranche. Ce dernier étant exploité dans le calcul, le montant total remisé est parfaitement restitué.



Le raisonnement de la fonction RechercheV nous convient très bien. Pour pouvoir prétendre à une remise supplémentaire, il est nécessaire de franchir un palier de quantités. Comme la RechercheV pointe sur la valeur proche directement inférieure, tant que ce palier n'est pas franchi, la remise du dessus n'est pas octroyée.

Remarque : Nous avons utilisé la touche F2 du clavier pour activer la modification de la formule, dans la cellule. Nous aurions aussi pu double cliquer sur cette dernière ou encore opérer les modifications depuis sa barre de formule. Dans tous les cas, un changement doit être validé avec la touche Entrée, jamais à la souris.

Calcul d'une évolution de prix
Tous les totaux remisés calculés semblent tout à fait cohérents. Ils sont inférieurs aux montants hors taxes du départ. Un calcul mental rapide suffirait à le confirmer. Mais par esprit de rigueur, nous allons exploiter la dernière colonne du tableau, la colonne Confirmation. Il s'agit d'y trouver le pourcentage de remise effectivement accordé, sur la base des résultats. Pour cela, il faut réaliser un calcul d'évolution entre le montant de départ et le montant à l'arrivée, selon la syntaxe suivante :

=(Montant_de_départ - Montant_final)/Montant_de_départ

Le montant de départ est le total hors taxes. Le montant final est le total remisé. Comme la division est prioritaire sur la soustraction, nous encadrons le calcul de la différence entre parenthèses. Ainsi, la division sera bien réalisée sur ce résultat.

• Sélectionner la première cellule pour le calcul de l'évolution, soit H3,
• Taper le symbole = pour débuter la formule et ouvrir la parenthèse,
• Sélectionner le montant de départ, soit le total hors taxes situé en F3,
• Taper le symbole - pour initier la soustraction,
• Sélectionner le premier montant final, soit le total remisé en G3,
• Fermer la parenthèse et taper le symbole slash (/) pour enclencher la division,
• Sélectionner de nouveau le montant de départ, soit le total hors taxes en F3,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour la répliquer sur tout le tableau,

Tous les écarts en pourcentages apparaissent.



Comme vous le constatez, ils confirment les remises appliquées, correctement restituées par la fonction RechercheV, selon l'intervalle dans lequel elles se trouvent.

La formule d'évolution que nous avons créée, est la suivante :

=(F3-G3)/F3

Il est donc fondamental de retenir que lorsqu'une recherche doit être effectuée dans des tranches de valeurs, le dernier paramètre de la fonction RechercheV doit être réglé sur Vrai.