Discussion :

[Wishguu]

Bonjour à toutes et à tous,

Voici mon problème :
J'ai une formule qui me permet de calculer la rémunération d'un salarié en fonction de son chiffre d'affaire et de différents critères.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Function Rémunération(CA, Smin, Smax, Hmin, Hmax, Taux As Single) As Single
Dim Rému As Single
 
If CA <= Hmin Then
    Rému = Smin
ElseIf ((CA >= Hmin) And (CA < Hmax)) Then
    Rému = Smin + Taux * (CA - Hmin)
Else:
    Rému = Smax
End If
 
    Rémunération = Rému
 
End Function

Avec :

Ca = Chiffre d'Affaire
Smin = salaire min
Smax = salaire max
Hmin = Chiffre d'affaire min de déclenchement de la part variable du salaire
Mmax = Chiffre d'affaire max à partir duquel on atteint le plafond de rémunération
Taux = coefficient de la part variable.

La fonction fonctionne, le problème provient de l'application automatique de cette fonction à X cellules et Y colonnes.

J'ai X salariés, chacun ayant des Smin, Smax, Hmin... différents.
Pour chaque salarié, je souhaite avoir l'évolution de sa rémunération en fonction de son Chiffre d'affaire.
Sur la feuille "Catégorie1" son listés en lignes chaque salarié (cellules vides) avec leurs coefficients respectifs.
Sur la feuille "TabCatégorie1" son listé en colonnes chaque salarié avec l'évolution de leur rémunération calculée avec la fonction "Rémunération"

Mon souci : je n'arrive pas à automatiser ce process
(Sur le document, j'ai fait à la main les trois premiers)

Anyone help please ?

(j'ai déjà tenté l'enregistrement de macro, sans succès)

[Daniel.C]

Je ne comprends pas ce que tu veux. Où se trouvent les calculs "à la main" ? Si c'est le calcul de E4 qui te manque, qu'est-ce qui t'empêche de tirer la cellule vers la droite ?

[Wishguu]

Les calculs à la mains sont sur la feuille "TabCatégorie1" et se trouvent dans la cellule :
B4 --> étendu jusqu'à la fin = OK
C4 et B4 sont faites à la main également, puis étendues
Mais je n'arrive pas à étendre vers à droite (D4, E4, etc) car la formule ne prends plus les bonnes données.

[Daniel.C]

Bizarre; en re-tirant la formule, ça redevient correct...

[Wishguu]

Les résultats sont corrects en tirant vers le bas.
Mais en tirant vers la droite ça ne fonctionne pas, en tout cas quand je le fais

[Daniel.C]

En E5, par exemple, j'obtiens ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A5;Catégorie1!F$5;Catégorie1!G$5;Catégorie1!H$5;Catégorie1!I$5;Catégorie1!J$5)

Qu'est-ce qui ne va pas ?

[Wishguu]

Je me suis mal exprimé je pense

En B4 j'ai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A4;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

En B5 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A5;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

En B6 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A6;Catégorie1!D$4;Catégorie1!E$4;Catégorie1!F$4;Catégorie1!G$4;Catégorie1!H$4)

etc...

En C4 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A4;Catégorie1!D$5;Catégorie1!E$5;Catégorie1!F$5;Catégorie1!G$5;Catégorie1!H$5)

etc...

En D4 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A4;Catégorie1!D$6;Catégorie1!E$6;Catégorie1!F$6;Catégorie1!G$6;Catégorie1!H$6)

etc...

Donc je veux en E4 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A4;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

En E5 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A5;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

En E6 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération($A6;Catégorie1!D$7;Catégorie1!E$7;Catégorie1!F$7;Catégorie1!G$7;Catégorie1!H$7)

[Daniel.C]

Bonjour,

Vérifie cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Rémunération(DECALER(TabCatégorie1!$D$1;COLONNE()+1;0);DECALER(TabCatégorie1!$E1;COLONNE()+1;0);DECALER(TabCatégorie1!$F1;COLONNE()+1;0);Catégorie1!F$4;DECALER(TabCatégorie1!$G1;COLONNE()+1;0);DECALER(TabCatégorie1!$H1;COLONNE()+1;0))

[Wishguu]

Merci Daniel c'est ce que je cherchais !

Il me manque toutefois une étape :
J'étends la formule vers le bas sans problème car j'ai un nombre de lignes fixe dans la feuille TabCat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sheets("TabCat1").Select
Range("B4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1<=INDIRECT(""Cat1!$F""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2),IF(RC1>INDIRECT(""Cat1!$F""&COLUMN()+2),MIN(INDIRECT(""Cat1!$E""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2)+((RC1-INDIRECT(""Cat1!$F""&COLUMN()+2))*INDIRECT(""Cat1!$H""&COLUMN()+2)))))"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B404")

Ensuite je dois étendre vers un nombre de colonne variable "Nbr"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Nbr = Range("Cat1!B4").End(xlUp).Offset(1, 0)
Range("B4:B404").Select
    Selection.AutoFill Destination:=Range("C4:" & Nbr)

Ma syntaxe n'est pas bonne

[Daniel.C]

Bonjour,

J'ai un problème avec les deux codes; peux-tu mettre ton classeur actuel en PJ ?

[Wishguu]

Voici, et merci d'avance !

http://www.2shared.com/file/kGXPIe1h/Test5.html

(Attention à bien cliquer sur le petit bouton download en bas et non pas le gros (pub))


Par la suite, je souhaite créer un graphique pour ces données sur l'onglet "CCat1".
J'ai tenter d'utiliser DECALER, mais je n'arrive pas à ce qu'il prenne mon nombre variable de colonnes dans "TabCat1"

[Philippe Tulliez]

Bonjour,
Est-ce que par hasard tu ne ferais pas référence à des cellules contenues dans un tableau croisé dynamique.
Si c'est le cas, il faudrait sans doute voir du côté de cette fonction LIREDONNEESTABCROISDYNAMIQUE

[Daniel.C]

Quel résultat dois-tu obtenir dans la variable "Nbr" ?

[Wishguu]

Dans la variable Nbr j'aimerais "TabCat1!AJ" (pour le cas du fichier Test5.xlm

Le but étant, depuis la sélection de première colonne de calculs ("B4:B404"), d'étendre le calcul jusqu'à "AJ" (donc "AJ4:AJ404" dans ce cas d'exemple)

Je regarde la fonction de Philippe en parallèle.

[Daniel.C]

Essaie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
    Sheets("TabCat1").Select
    nbr = Sheets("Cat1").Cells(Rows.Count, 1).End(xlUp).Row - 2
    Range("B4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC1<=INDIRECT(""Cat1!$F""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2),IF(RC1>INDIRECT(""Cat1!$F""&COLUMN()+2),MIN(INDIRECT(""Cat1!$E""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2)+((RC1-INDIRECT(""Cat1!$F""&COLUMN()+2))*INDIRECT(""Cat1!$H""&COLUMN()+2)))))"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:B404")
    Range("B4:B404").Select
    Selection.AutoFill Destination:=Range("B4:B404").Resize(, nbr)

Ensuite, ôte les "Select" qui ralentissent l'exécution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
With Sheets("TabCat1")
    nbr = Sheets("Cat1").Cells(Rows.Count, 1).End(xlUp).Row - 2
    .Range("B4").FormulaR1C1 = _
        "=IF(RC1<=INDIRECT(""Cat1!$F""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2),IF(RC1>INDIRECT(""Cat1!$F""&COLUMN()+2),MIN(INDIRECT(""Cat1!$E""&COLUMN()+2),INDIRECT(""Cat1!$D""&COLUMN()+2)+((RC1-INDIRECT(""Cat1!$F""&COLUMN()+2))*INDIRECT(""Cat1!$H""&COLUMN()+2)))))"
    .Range("B4").AutoFill Destination:=.Range("B4:B404")
    .Range("B4:B404").AutoFill Destination:=.Range("B4:B404").Resize(, nbr)
End With

[Wishguu]

Impeccable !

Il reste une colonne de "0" en "AK" après la dernière colonne utile "AJ" mais le code est nickel.

Merci encore !

[Wishguu]

Bonjour,

Je reviens sur le sujet car ce bout de code ne fonctionne plus !
j'ai réessayé : il incrémente mais les résultats restent comme celui de la première cellule...

Ceci est le cas également sur mes anciennes versions de mon fichier

[Wishguu]

Tout va bien, l'option "calcul automatique" s'était désactivée, je ne sais pas la raison...