XL 2010 Ecriture de formule matricielle

[mintri]

Bonjour,

Je viens solliciter votre assistance car je rencontre actuellement un petit blocage dans l'écriture d'une formule.

Je dispose de deux tableaux,
- l'un avec des taux de commissions en fonction des tranches de chiffre d'affaires et de la périodicité
- l'autre avec les totaux de chiffre d affaires par équipe / par période.

Je souhaiterais calculer la commission totale dans une seule colonne en mettant une formule.

Par exemple, pour l'équipe A, le total commission serait de 2320€

Trim 1 = 150
- De 5001 à 10000 = 1% de commission (50€) +
- De 10001 à 15000 = 2% de commission (100€)

Trim 2 = 0

Trim 3 =
- De 5001 à 10000 = 1% de commission (50€) +
- De 10001 à 15000 = 2% de commission (100€)
- De 15001 à 20000 = 3% de commission (150€)
- plus de 20000 = 4% de commission (3000*0.04 = 120€)

Trim 4 = 0

Annuel = 38000 * 5% = 1900

Je recherche une formule au plus simple en gardant à l'esprit que les tranches et taux peuvent changer et que je préfererais éviter le VBA. J'ai essayé d'écrire une formule en matriciel mais sans succès (le concept m'échappe encore un peu). Pourriez-vous éclairer ma lanterne sur ce sujet?

Merci beaucoup pour votre aide !

 

[mapomme]

Bonjour à tous,

J'avais commencé, je termine. Un essai avec une formule matricielle en O6 à tirer vers le bas. Six nom dynamiques ont été définis.
Elle n'est ni aussi jolie, ni aussi courte, ni aussi géniale, ni aussi éléborée, ni aussi mathématiquement élégante que la formule de ROGER2327 ; je ne suis qu'un laborieux, moi 🙁.

 

[ROGER2327]

Re...

Bonjour à tous,

J'avais commencé, je termine. Un essai avec une formule matricielle en O6 à tirer vers le bas. Six nom dynamiques ont été définis.
Elle n'est ni aussi jolie, ni aussi courte, ni aussi géniale, ni aussi éléborée, ni aussi mathématiquement élégante que la formule de ROGER2327 ; je ne suis qu'un laborieux, moi 🙁.

Et que suis-je donc d'autre que laborieux ? Tant qu'à faire, soyons-le jusqu'au bout. Votre solution peut-être simplifiée comme vous le verrez dans les colonnes M à P du classeur joint. On arrive à ceci :

=SOMME((SI(G6:J6<Tsup;G6:J6;Tsup)>Tinf)*Tcoef*(SI(G6:J6<Tsup;G6:J6;Tsup)-Tinf);
       (SI(K6   <Asup;K6;   Asup)>Ainf)*Acoef*(SI(K6   <Asup;K6;   Asup)-Ainf))

Difficile de faire mieux, sauf à chercher à diminuer le nombre de tests de comparaison.
Sur la base de la note (4) de mon «document explicatif», on obtient la colonne R du classeur joint. mais c'est au prix de l'adjonction d'une ligne supplémentaire dans le tableau de barème (ligne 11). À notre ami de voir si c'est rédhibitoire ...​

Bonne soirée.


ℝOGER2327
#8319

Mercredi 25 Tatane 143 (Saint Panurge, moraliste - fête Suprême Quarte)
20 Thermidor An CCXXIV, 6,1333h - écluse
2016-W31-7T14:43:12Z

 

[mapomme]

Bonsoir ROGER2327,

ROGER2327, un grand merci pour la simplification de la formule et la description des étapes pour y aboutir. Du coup, ça devient limpide. La formule simplifiée est vraiment plus satisfaisante.

Bonne soirée.

 

[mapomme]

Re ROGER2327,
une petite réduction supplémentaire (un peu capillotractée 😉) en utilisant la fonction SIERREUR().

=SOMME(SIERREUR(Tcoef*(EXP(LN(SI(G6:J6<Tsup;G6:J6;Tsup)-Tinf)));0);
SIERREUR(Acoef*(EXP(LN(SI(K6<Asup;K6;Asup)-Ainf)));0))