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 !

 

[JHA]

Bonjour à tous,

Je ne suis pas certain d'avoir tout compris.

JHA

 

[pascal82]

Bonjour mintri, JHA

Pas tout compris dans la méthode, calcul en % pour un résultat fixe ?, et modification B12 pour garder la même méthode.
Je n'ai pas utilisé une formule matricielle, mais réalisé avec des tests avec si
A voir si cette solution peut convenir

Cordialement

Nota : le téléversement (perturbant) me gène puisque je n'arrive pas à savoir et voir si le fichier est bien attaché

 

[Hieu]

Slt,

J'ai découplé en plusieurs colonne + rajout d'une colonne "comission" [pas vu, comment faire autrement - si quelqu'un a mieux / sans mettre le vecteur directement dans la formule (beurk) ]
Bien sûr, tu peux tout mettre dans une cellule mais, j'ai laissé comme ça, pour la clarté.

edit : c'est faux... Au reveil, j'viens de comprendre mon erreur...
Du coup, pas de solutions sans macro mais une fonction, c'est réalisable

 

[ROGER2327]

Bonjour à tous.

Au prix d'une petite modification du tableau de barême :​

=SOMME((D$7:D$10-D$6:D$9)*(G5:J5-C$6:C$9+ABS(G5:J5-C$6:C$9));
    (D$12:D$14-D$11:D$13)*(K5-C$11:C$13+ABS(K5-C$11:C$13)))/2

(À valider par Ctrl Maj Entrée.)​

ℝOGER2327
#8315

Dimanche 22 Tatane 143 (Ubu ès Liens - fête Suprême Seconde)
17 Thermidor An CCXXIV, 1,2645h - lin
2016-W31-4T03:02:05Z

 

[Jacky67]

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.

Regarde la pièce jointe 970043
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

Bonjour,
>Par exemple, pour l'équipe A, le total commission serait de 2320€
Heu!!
50+100+50+100+150+120+1900=2470
Non ?
Une proposition supplémentaire avec formule à rallonge

Curieux, je suis ... les bons résultats sont disponible?

Salutations
JJ

 

[Hieu]

Bon, alors moi, j'ai la bonne réponse ^^
Par contre, par fonction personnalisée (on peut dire que c'est pas du vba ?)

Function Commission(ca As Double, tableau As Range)
    ' ca le chf d'affaires
    ' tableau le tableau de comission sur 2 colonnes (A et Taux)
If ca = 0 Then Exit Function
For i = 1 To tableau.Rows.Count
    If ca >= tableau(i, 1) Then
        Commission = Commission + tableau(i, 2) * tableau(1, 1)
    Else
        Commission = Commission + tableau(i, 2) * (ca - IIf(i = 1, 0, tableau(i - 1, 1)))
        Exit For
    End If
Next i
End Function

 

[Jacky67]

Bon, alors moi, j'ai la bonne réponse ^^

La bonne réponse, certainement
Mais les bons résultats.....
Il est parti ..le canadien

Jolie fonction
JJ

 

[ROGER2327]

Re...

Bon, alors moi, j'ai la bonne réponse ^^
(...)

Nous sommes d'accord : voir #5.

Bonne soirée.


ℝOGER2327
#8316

Dimanche 22 Tatane 143 (Ubu ès Liens - fête Suprême Seconde)
17 Thermidor An CCXXIV, 7,2313h - lin
2016-W31-4T17:21:19Z

 

[mintri]

Bonjour à tous, merci pour vos réponses, j'ai réussi avec la formule de Roger, c'est exactement ce que je souhaitais faire, je vais l'adapter pour que cela colle avec mon tableau !

Pas très bon en maths (comble pour un comptable...), merci pour l'ajustement de mon calcul erroné...

Je vais regarder les autres réponses pour essayer de voir ce qui serait le plus facile à répéter mais un grand merci en tous les cas !

Bonne soirée !
Minhtri

 

[Hieu]

@ROGER2327 ,
Ta formule dépasse mes compétences ^^
J'suis dessus, pour l'intrigue, et pour l'instant, j'ai toujours pas compris...

 

[ROGER2327]

Re...

@ROGER2327 ,
Ta formule dépasse mes compétences ^^
J'suis dessus, pour l'intrigue, et pour l'instant, j'ai toujours pas compris...

Pas le temps de rédiger une explication sur l'instant, mais je vous en promets une d'ici demain soir. Vous verrez alors que c'est moins compliqué qu'il vous parait.


Bonne soirée.


ℝOGER2327
#8317

Dimanche 22 Tatane 143 (Ubu ès Liens - fête Suprême Seconde)
17 Thermidor An CCXXIV, 7,6559h - lin
2016-W31-4T18:22:27Z

 

[mintri]

J'ai réussi à l'adapter mais je pense qu'il va me falloir un peu de réflexion pour comprendre comment elle fonctionne... Merci beaucoup Roger en tous les cas !

 

[pascal82]

Bonjour à tous,

Au delà des méthodes différentes des contributeurs pour tenter d'apporter une solution convenable, nous sommes plusieurs à c'être "planter". J'aurai préférer une explication de la part de mintri sur le détail et la méthode de calcul à utiliser pour une équipe en exemple. Bravo à ROGER pour son analyse et formule miracle.

Cordialement

 

[ROGER2327]

Suite... ...en retard !

Je n'ai pas eu le temps de faire la réponse détaillée annoncée. Je dépose un brouillon préliminaire. Si on y trouve des fôtes, merci de les signaler...

Bonne nuit.

ℝOGER2327
#8318

Mercredi 25 Tatane 143 (Saint Panurge, moraliste - fête Suprême Quarte)
20 Thermidor An CCXXIV, 0,4655h - écluse
2016-W31-7T01:07:02Z