XL 2010 Ecriture de formule matricielle

mintri · 3 Août 2016

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 · 3 Août 2016

Bonjour à tous,

Je ne suis pas certain d'avoir tout compris.

JHA

pascal82 · 3 Août 2016

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 · 4 Août 2016

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 · 4 Août 2016

Bonjour à tous.

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

Code:

=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 · 4 Août 2016

mintri à dit:
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 · 4 Août 2016

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

VB:

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 · 4 Août 2016

Hieu à dit:
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 · 4 Août 2016

Re...

Hieu à dit:
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 · 4 Août 2016

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 · 4 Août 2016

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

ROGER2327 · 4 Août 2016

Re...

Hieu à dit:
@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 · 4 Août 2016

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 · 5 Août 2016

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 · 7 Août 2016

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

XL 2010 Ecriture de formule matricielle

mintri

XLDnaute Occasionnel

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

pascal82

XLDnaute Occasionnel

Pièces jointes

Hieu

XLDnaute Impliqué

Pièces jointes

ROGER2327

XLDnaute Barbatruc

Pièces jointes

Jacky67

XLDnaute Barbatruc

Pièces jointes

Hieu

XLDnaute Impliqué

Pièces jointes

Jacky67

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

mintri

XLDnaute Occasionnel

Hieu

XLDnaute Impliqué

ROGER2327

XLDnaute Barbatruc

mintri

XLDnaute Occasionnel

pascal82

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

Pièces jointes

Discussions similaires