Valeur cible

Bonjour,

Il aurait peut-être été aussi simple de faire dès le départ une macro à base de valeur cible. Si quelqu'un en a envie.....
Un peu plus simple oui ;-)

Sub nouveauSalaire()
    Dim c As Range
    For Each c In [E2].Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1)
        If c.Offset(, 9) > 0 Then c.Offset(, 7).GoalSeek Goal:=c.Offset(, 9), ChangingCell:=c
    Next c
End Sub

https://www.cjoint.com/c/GHEkTYEnLuw
eric

Bonjour JvDo

C'est top merci beaucoup, je vais l'utiliser.

Je voudrais bien revenir vers toi pour développer le fichier de traitement des salaire avec plus de variable , car tu fait des miracles

Je te remercie

Bonjour à tous,

Pour l'intégration de l'abattement dans la formule que j'avais écrite ....... j'ai préféré abandonner.
J'ai donc écrit une petite fonction qui calcule par dichotomie le salaire de base à partir du salaire net souhaité.
C'est beaucoup moins compliqué qu'une formule.

Il aurait peut-être été aussi simple de faire dès le départ une macro à base de valeur cible. Si quelqu'un en a envie.....

Voilà un fichier ( https://www.cjoint.com/c/GHEeuYgybD6 )avec une zone de contrôle des résultats de la function().
Elle a 4 arguments et il faut l'appeler comme ça :

=Sal_base(P2;F2;G2;Q2)

La fonction en elle-même :

Option Explicit
Const TxSS = 0.09       'taux sécurité sociale
Const Tranche1 = 10000  'borne 1ère tranche
Const Tranche2 = 30000  'borne 2ème tranche
Const Tranche3 = 120000 'borne 3ème tranche
Const Tx1 = 0.2         'taux 1ère tranche
Const Tx2 = 0.3         'taux 2ème tranche
Const Tx3 = 0.35        'taux 3ème tranche
Const AbtMin = 1000     'Abattement minimum
Const AbtMax = 1500     'Abattement maximum
Const AbtTx = 0.4       'taux de calcul de l'abattement

Function Sal_base(sal_net_cible As Currency, Panier As Currency, Transport As Currency, Pirg As Double)
'calcule le salaire de base à partir d'un salaire net cible, de primes de panier et de transport
'Assiette et Pirg sont là pour calculer IRG à la dizaine inférieure
'méthode dichotomique limitée à 50 boucles pour trouver une solution
'la borne inférieure est initialisée à sal_net_cible - Panier - Transport
'la borne supérieure est initialisée à (1 + Tx3) * sal_net_cible

    Dim i As Integer, bInf As Currency, bSup As Currency, Base_imposable As Currency
    Dim Sal_imposable As Currency, IRG As Currency, Abattement As Currency, Sal_dicho As Currency
    Dim sal_net As Currency, k As Integer
    If sal_net_cible > 0 And Panier > 0 And Transport > 0 And Pirg > 0 Then
        sal_net_cible = Round(sal_net_cible, 2)
        bInf = sal_net_cible - Panier - Transport
        bSup = (1 + Tx3) * sal_net_cible: Sal_dicho = -1
        k = 0
        
        While (Abs(sal_net_cible - sal_net) >= 0.01 And k < 50)
            k = k + 1
            Sal_dicho = Round((bSup + bInf) / 2, 2)
            Sal_imposable = Round(Sal_dicho * (1 - TxSS) + Panier + Transport, 2)
            Base_imposable = WorksheetFunction.RoundDown(Sal_imposable / Pirg, -1)
            IRG = Round(-(Base_imposable > Tranche1) * Tx1 * (Base_imposable - Tranche1) _
                        - (Base_imposable > Tranche2) * (Tx2 - Tx1) * (Base_imposable - Tranche2) _
                        - (Base_imposable > Tranche3) * (Tx3 - Tx2) * (Base_imposable - Tranche3), 2)
            Abattement = WorksheetFunction.Max(AbtMin, WorksheetFunction.Min(AbtMax, Round(Base_imposable * AbtTx, 2)))
            sal_net = Sal_imposable - IRG + Abattement
            If sal_net < sal_net_cible Then bInf = Round((bSup + bInf) / 2, 2) Else bSup = Round((bSup + bInf) / 2, 2)
    
        Wend
        
        If k < 50 Then
            Sal_base = Round((sal_net_cible + IRG - Abattement - Panier - Transport) / (1 - TxSS), 2)
        Else
            Sal_base = [na()]
        End If
    Else
        Sal_base = [na()]
    End If
End Function

Cordialement

Bonjour Malek,

Fichier Excel 2007 : https://mon-partage.fr/f/N75esyug/

Il y a 2 feuilles : "(38)" et "(39)" ; la seule différence entre elles
est que sur "(38)", les colonnes K à S sont masquées ; sur la
feuille "(39)" où les colonnes K à S sont affichées, regarde bien
toutes les formules de la ligne 2 (qui ont été recopiées vers
le bas jusqu'en ligne 7) ; exemple pour la ligne 2 :

En E2 : "Salaire de Base" ; si tu n'as rien saisi, tu pourras voir
que la plupart des autres cellules à droite sont vides.

En H2 : "Salaire du Poste" = reprise du "Salaire de Base" ;
tu avais mis =+E2 alors que =E2 suffit !

------------------------------------

En I2 : "Retenue S. Sle" = 9% du "Salaire de Poste", arrondi
à 2 chiffres après la virgule : =ARRONDI(H2*9%;2)

En J2 : "Salaire Imposable" : comme H2 est juste la reprise de E2,
c'est bien plus simple de faire : =SOMME(E2:G2)-I2 ; et complété
ainsi : =SI(E2="";0;SOMME(E2:G2)-I2)

------------------------------------

En X2 : % IRG (que tu saisis)

En K2 : "Base Imposable" : si X2 = 0, pour éviter une erreur de
division par 0, on fait comme si tu avais saisi 1 => on utilise J2
car J2 / 1 = J2 ; si X2 est autre que 0 (et je ne pense pas que
tu vas saisir un nombre négatif), on utilise J2 / X2 ; ce qui est
utilisé (J2, ou J2 / X2) est arrondi à la dizaine inférieure.

------------------------------------

L2 à T2 : calcul de l'IRG retenu (abattement compris).

Il n'y a pas de colonne "B.0" (Base 0 %) car le taux étant nul,
c'est inutile : l'impôt de la 1ère tranche de 0 à 10 000 inclus
est toujours 0 Da.

L2 : Base 20% ; M2 : impôt à 20% correspondant
N2 : Base 30% ; O2 : impôt à 30% correspondant
P2 : Base 35% ; Q2 : impôt à 35% correspondant

En R2 : "Total cumulé" : =M2+O2+Q2

En S2 : "Abattement" : calculé selon tes indications

En T2 : "Retenu IRG" : =R2-S2

------------------------------------

En U2 : "Salaire Net" =
"Salaire Imposable" - "Retenu IRG" : =J2-T2

En V2 : "Augmentation" (que tu saisis)

En W2 : "Nouveau Salaire" : =SI(E2="";0;U2+V2)

------------------------------------

Tu auras noté que les valeurs 0 ne s'affichent pas ; c'est grâce
à ce format personnalisé : # ##0,00;-# ##0,00;

Utilise la feuille que tu préfères et supprime l'autre.
Si besoin, tu peux me demander une adaptation.

Merci de me dire si ça te convient.

Cordialement
 

Bonjour JvDo

Désole pour mon impatience, mais je me voyais traiter employé par employé.

Tu es trop fort , je te remercie.

Peux-tu stp me vérifier le calcul de l'irg car je ne comprends pas comment tu l’as mis en place car le barème de calcule est comme suit :

Montant n’excédant pas 10 000.00 le taux est de 0%
Montant de 10 000.01 à 30 000.00 le taux est de 20%
Montant de 30 000.01 à 120 000.00 le taux est de 30%
Montant supérieur a 120 000.00 le taux est de 35%

Exemple pour une base imposable de 38 000.00 le calcule sera de
Fraction Différence Taux IRG
De 0 à 10 000.00 10 000.00 0% 0 Da
De 10 000.01 à 30 000.00 30 000.00 20% 4 000.00 Da
De 30 000.01 à 38 000.00 8 000.00 30% 2 400.00 Da
Total cumule 6 400.00 Da
Abattement 1 500.00 Da
Retenu IRG 4 900.00 Da
L’abattement se calcule comme suite SI(Base imposable*40%<1000;1000;SI(Base imposable *40%>1500;1500; Base imposable *40%))

Je vais un peu abuser mais dans ta formule je voudrais incorporer le ratio de présence pour calculer l'IRG il est en colonne P

Demain je vais être beaucoup plus détendu au boulot grâce a toi merci

Bonjour,

tu es bien impatient, malek1982-2015.
ça ne se fait pas tout seul et en plus tu calcules IRG sur des arrondi inf à la dizaine.
ça génère une complexité supplémentaire si on veut un résultat précis.

J'ai ajouté des arrondis à la deuxième décimale à droite à gauche.
J'ai simplifié le calcul de l'IRG :

=RECHERCHEV(J2;$S$2:$U$7;2;VRAI)*(ARRONDI.INF((J2/O2);-1)-RECHERCHEV(J2;$S$2:$U$7;1;VRAI))+RECHERCHEV(J2;$S$2:$U$7;3;VRAI)

Tu noteras le paramètre -1 de arrondi.inf() qui remplace tes /10 et *10.

Pour simplifier l'IRG, j'ai créé une table des impôts en S2:U7

Pour le calcul du salaire de base à partir du salaire net augmenté (qui se trouve en colonne N) voilà la formule en P2 :

=ARRONDI((N2+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;2;VRAI)*(ARRONDI.INF((ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2)/O2);-1)-RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;1;VRAI))+RECHERCHEV(ARRONDI((N2-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))*INDEX($S$2:$S$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1))+INDEX($U$2:$U$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)))/(1-INDEX($T$2:$T$7;EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)));2);$S$2:$U$7;3;VRAI)-G2-F2)/0,91;2)

Elle est matricielle. Il faut donc la valider par CTRL+MAJ+ENTER

Tous les

EQUIV(N2;$S$2:$S$7-$U$2:$U$7;1)

cherchent l'indice de ligne dans la table impôts.
En les nommant on simplifie visuellement la formule.

Cordialement

bonjour JvDo

tu as du nouveau pour mon problem

Bonjour

tu as raison j'ai corriger ma formule, je te remercie.

Bonjour,

5 fois que j'essaie de poster un message et ça ne passe pas .......

6ème essai

dans la tranche [28750 - 30000[ de IRG, il devrait y avvoir +2250 au lieu de + 22500.

Cordialement

Bonjour JvDo

Merci pour ton assistance.

Voici le lien pour le fichier:

https://www.cjoint.com/c/GHDkI6YmFwm

Salutations
Malek

Bonjour à tous,

Mets un exemple de ton fichier à disposition (sur cjoint.com par exemple).
Anonymise le bien entendu.
Il faut connaître ta fonction "calcul impôts" (qui, théoriquement, est affine par morceaux) pour identifier les éventuels sauts de tranche.
À partir de là, il devrait être facile de calculer l'augmentation du salaire de base qui donnera celle attendue en net.

Cordialement

Bonjour.

Non, je ne connais pas de formule qui fasse ça.
Je ne suis même pas certain qu'on puisse le réaliser avec une macro.
Ce dont je suis certain, c'est que les augmentations de salaire ne se font jamais sur le net à payer, en France !
Alors utilise ton analyse valeur cible sur le plus petit et le plus gros salaires, pour avoir une indication de départ, et fais comme toutes les entreprises : pourcentage d'augmentation ou somme forfaitaire, sur le salaire de base.

C'est bien, la retraite ! Surtout aux Antilles ... :-) 
Raymond (INSA, AFPA, CF/R)