Calculs Excel sur des taux de TVA et de remises variables

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


Sujets que vous pourriez aussi aimer :


Calculs Excel selon des taux variables - Tva et remises

Dans cette formation Excel, nous proposons de mettre en pratique des formules démontrées au cours de formations précédentes, afin de simplifier la mise en oeuvre des calculs sur la base de TVA ou de remises variables.

Comme l'illustre le tableau présenté ci-dessous, une synthèse des articles vendus sur la période est proposée. Tous ne sont pas régis par la même Tva. C'est leur appartenance à une catégorie qui définit le taux. Et cette correspondance est livrée dans le petit tableau situé en bas à gauche.



Nous pourrions certes exploiter la fonction conditionnelle Si pour envisager et vérifier tous les cas possibles. Mais bien que seuls trois taux de Tva différents soient proposés, sa syntaxe s'alourdit sérieusement, à cause de l'imbrication des fonctions. La compréhension, la lecture et donc la modification du calcul s'en trouvent largement compliqués.

C'est pourquoi nous proposons d'apporter une solution plus efficace lorsque le nombre de critères à analyser augmente. L'astuce consiste à employer la fonction d'extraction RechercheV. Elle doit permettre de faire la correspondance entre les catégories pour appliquer le taux de TVA approprié, selon une syntaxe simplifiée.

Source et présentation de la problématique
Les taux de Tva et de remises sont une illustration permettant de démontrer l'efficacité de l'analyse multicritère, par une fonction d'extraction. Les domaines d'exploitation vont bien au-delà et concernent beaucoup de métiers. Pour exercer les manipulations, nous avons besoin de données.

• Télécharger le classeur calculs-pourcentages-par-categories.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 est constitué de deux feuilles nommées respectivement : Calculs_Tva et Remises_variables.

La seconde synthétise les ventes réalisées pour des produits appartenant à des catégories différentes. Des remises sont en vigueur mais elles diffèrent selon ces catégories. Il s'agit donc de formuler les montants TTC, en considérant la bonne remise.

La première feuille propose la synthèse des ventes d'articles régis par des TVA différentes. L'enjeu consiste à produire le montant TTC avec une formule dynamique, évolutive et à la syntaxe allégée. Nous répliquerons cette technique sur la seconde.

Sur la feuille Calculs_Tva, vous remarquez que les montants TTC sont déjà calculés en colonne G. Ces résultats ont été produits à l'aide de fonctions Si imbriquées, permettant de faire la correspondance entre la catégorie et le taux de TVA à appliquer. Ainsi, nous pourrons juger de l'amélioration que nous décidons d'apporter, lorsque nous aurons finalisé les calculs de cette feuille.

• Double cliquer sur la première cellule du montant TTC calculé, soit la cellule G5,

Cette technique permet d'activer la modification de la cellule, au même titre que la touche F2 du clavier ou encore la barre de formule.



Comme vous le constatez, la syntaxe est déjà complexe et fort peu lisible, bien que seuls trois taux de Tva soit à considérer. Nous comprenons rapidement que s'ils étaient plus nombreux, la fonction Excel Si ne serait pas la solution à employer. Ce calcul est néanmoins totalement dynamique dans la mesure où il est bâti sur des variables, soit des cellules figées grâce aux références absolues.

Le raisonnement que ces fonctions proposent est le suivant : Si la catégorie du produit est Cat1 (B14), alors le calcul du Ttc s'effectue en considérant un pourcentage de 5,5% (C14). Sinon, si la catégorie est Cat2 (B15), alors le calcul du Ttc est produit selon la Tva à 20% (C15). Dans le cas restant (Sinon), le calcul est réalisé sur la dernière Tva (C16).

Extraire la Tva du produit pour le calcul du Ttc
Le calcul du montant Toutes Taxes Comprises, en version factorisée, est le suivant :

=Tht*(1 + Tva)

Cette Tva doit être récupérée à la volée, soit à l'intérieur même du calcul, en fonction de la catégorie du produit en cours. C'est donc la fonction Excel RechercheV qui doit être imbriquée. Elle permet d'extraire une valeur d'un tableau, en fonction d'une donnée cherchée. Sa syntaxe est la suivante :

=RchercheV(Valeur_cherchée ; Tableau_de_recherche ; Num_colonne_retour ; Faux)

La valeur cherchée est la catégorie du produit. C'est elle qui permet de pointer sur le taux de Tva correspondant. Le tableau de recherche est celui qui référence ces taux. Il s'agit de la plage de cellules B14:C16. Le numéro de colonne en retour est la colonne dans laquelle se situe le taux correspondant à rapatrier. Par rapport à la sélection, il s'agit de la deuxième. Nous saisirons donc le chiffre 2. Enfin le booléen Faux en dernier paramètre indique à la fonction RechercheV d'effectuer une recherche selon une correspondance exacte.

• Sélectionner la première cellule du calcul, soit H5,
• Taper le symbole = pour débuter la formule,
• Cliquer sur la cellule du premier montant hors taxes, soit F5,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Ouvrir une parenthèse pour la factorisation du calcul,
• Saisir le chiffre 1 suivi du symbole + pour l'addition, soit 1 +,
• Taper le nom de la fonction d'extraction suivi d'une parenthèse, soit RechercheV(,
• Cliquer sur la cellule de la première catégorie, soit C5, pour désigner l'élément à chercher,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner intégralement le tableau des catégories, soit la plage de cellules B14:C16,
• Enfoncer la touche F4 du clavier pour figer ses bornes, ce qui donne : $B$14:$C$16,

En effet, lorsque nous répliquerons le calcul sur les lignes du dessous, la recherche de la catégorie doit toujours être effectuée entre ces bornes. Les références du tableau de recherche ne doivent donc pas suivre le déplacement de la formule.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Saisir le chiffre 2 pour désigner la deuxième colonne de la sélection, celle des taux de Tva,
• 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 englobe la recherche,
• Valider la formule par le raccourci clavier CTRL + Entrée pour conserver la cellule active,
• Enfin, tirer la poignée du calcul sur la hauteur du tableau, soit jusqu'en cellule H11,

Comme vous le remarquez, nous obtenons strictement les mêmes résultats que ceux fournis par les fonctions Si. Mais la syntaxe est beaucoup plus simple, comme l'illustre cette comparaison :

=SI(C5=$B$14; F5*(1+$C$14); SI(C5=$B$15;F5*(1+$C$15); F5*(1+$C$16)))
=F5*(1 + RECHERCHEV(C5; $B$14:$C$16; 2; FAUX))

De plus, la RechercheV permet un calcul évolutif. Si un nouveau taux de TVA est ajouté, la fonction Si doit intégrer une nouvelle imbrication, alourdissant encore la syntaxe. La fonction RechercheV quant à elle peut s'adapter automatiquement, en modifiant les bornes du tableau de recherche.

Calculs sur des remises variables
Nous souhaitons mettre en oeuvre cette technique afin de faciliter le calcul des ventes avec des remises variables. Et nous allons voir que le résultat est encore plus probant.

• Cliquer sur l'onglet Remises_variables en bas de la fenêtre Excel pour activer sa feuille,

Comme l'illustre la capture ci-dessus, ce tableau propose la synthèse des ventes réalisées par produit. Sur la base du total hors taxes, il s'agit de calculer le montant final remisé. Chaque produit appartient à une catégorie. Et comme vous le remarquez, ces dernières sont énumérées dans un petit tableau situé en bas de la feuille. A chaque catégorie est associé un pourcentage de remise. Ces catégories sont plus nombreuses que dans le cas précédent. Pour résoudre le calcul avec les fonctions conditionnelles, il faudrait envisager quatre fonctions Si imbriquées. L'exploitation de la fonction RechercheV est donc définitivement préconisée, pour des formules multi-critères.

La syntaxe factorisée pour le calcul d'un montant remisé sur la base d'un total hors taxe, est la suivante :

=Tht*(1 - Pourcentage_Remise)

• Sélectionner la première cellule pour le calcul du montant remisé, soit G3,
• Taper le symbole = pour débuter la formule,
• Cliquer sur la cellule du premier total hors taxes, soit F3,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Ouvrir la parenthèse pour la factorisation du calcul,
• Saisir le chiffre 1 suivi du symbole -, soit 1-, pour la soustraction,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit RechercheV(,
• Cliquer sur la première catégorie à rechercher, soit la cellule C3,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Sélectionner le petit tableau des catégories, soit la plage de cellules B17:C21,
• Enfoncer la touche F4 du clavier pour figer ses bornes, ce qui donne : $B$17:$C$21,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de retour,
• Saisir le chiffre 2 pour indiquer la Tva à extraire située en deuxième colonne,
• Taper un point-virgule suivi du paramètre 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 la formule par le raccourci clavier CTRL + Entrée pour conserver la cellule active,
• Enfin, double cliquer sur la poignée de la cellule pour répliquer le calcul sur tout le tableau,

Nous obtenons des résultats tout à fait cohérents puisque les montants remisés sont inférieurs aux totaux hors taxes de départ. La formule n'est pas plus complexe que dans le cas précédent des Tva variables. Pourtant, 5 cas sont à envisager ici, pour les 5 remises possibles. La formule que nous avons bâtie est la suivante :

=F3*(1-RECHERCHEV(C3; $B$17:$C$21; 2; FAUX))

Nous pourrions ainsi envisager un nombre non limité de catégories. Il serait alors nécessaire de rendre dynamique la plage de cellules du tableau de recherche. La formation Excel sur les listes et recherches dynamiques, nous avait appris à exploiter la fonction Decaler. Cette dernière permet de déplacer les bornes d'un tableau en fonction du contenu qui évolue.

Calcul d'une évolution en pourcentage
Même s'il est assez simple de valider la cohérence des résultats par un petit calcul mental, nous proposons d'en apporter la preuve. La colonne confirmation (H) doit afficher le pourcentage de remise effectivement attribué pour chaque vente. Pour l'obtenir, il s'agit de calculer l'évolution entre les deux résultats. La formule d'une évolution est la 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é calculé. Les parenthèses sont nécessaires car la division est prioritaire sur la soustraction. Or c'est bien le résultat de la différence, qui doit être divisé par le montant de départ.

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

Comme l'illustre la capture ci-dessus, les résultats confirment le succès de notre formule impliquant la fonction RechercheV. Chaque pourcentage de remise a parfaitement été appliqué en fonction de la catégorie du produit acheté. Dans la prochaine formation, nous traiterons un cas a priori similaire. Il s'agira d'appliquer des remises en fonction de quantités achetées. Dans ce contexte, difficile de prévoir le nombre de possibilités à envisager. Nous verrons qu'il existe une astuce toute simple associée, une fois encore, à cette incontournable fonction Excel RechercheV.