Formules complexes - plusieurs conditions et cas de figure

Bonjour,

Je cherche à traduire en langage excel des conditions complexes pour incrémenter automatiquement des cellules du tableau en PJ.

L’objectif est que toutes les cellules à partir de la colonne G se calculent automatiquement en fonction des données saisies dans les cellules B à F.

J’ai indiqué manuellement dans le tableau les montants auxquels aboutir sur les 3 lignes d’exemple

En français, les critères et conditions sont les suivants ; si quelqu’un était capable de les traduire en excel, je lui en serai très reconnaissant !

A. Pour les colonnes « montant BP »

Cas 1 : Si l’année d’entrée (colonne C) = l’année N, alors la cellule « montant BP » de l’année N doit être égale à la cellule montant estimé année pleine (colonne B). Si le mois d’entrée (colonne D) n’est pas le mois de janvier, alors la cellule « montant BP » de l’année N doit se calculer au prorata. Par exemple, si le mois d’entrée est avril, la cellule « montant BP » devra être égale à la cellule « montant estimé année pleine » x 9/12

Cas 2 : Si l’année de sortie (colonne E) = l’année N, alors la cellule « montant BP » de l’année N doit être égale au négatif d montant indiqué dans la cellule « montant estimé année pleine » (colonne B).
Si le mois de sortie (colonne F) n’est pas le mois de décembre, alors la cellule « montant BP » de l’année N doit se calculer au prorata. Par exemple, si le mois de sortie est septembre, la cellule « montant BP » devra être égale au négatif de la cellule « montant estimé année pleine » x 4/12

Cas 3 : Si l’année N ne correspond ni à l’année d’entrée ni à l’année de sortie, alors la cellule « montant BP » de l’année N est égale à 0.

Jusque-là, tout va (à peu près) bien, mais les choses se corsent en intégrant le paramètre suivant :

Cas 4 : Si la cellule « montant BP » de l’année N est au prorata du montant indiqué dans la cellule « montant estimé année pleine », alors la différence entre ces 2 montants devra s’incrémenter dans la cellule « montant BP » de l’année N+1, en positif si N = année d’entrée, en négatif si N = année de sortie

- Par exemple : j’ai une entrée prévue en mai 2022 pour un montant en année pleine estimé à 50.000€ -> je dois avoir 33.333€ dans ma cellule « montant BP » 2022 (soit 50.000 x 8/12), et 16.667€ dans ma cellule « montant BP » 2023 (soit 50.000 - 33.333).

- A l’inverse, j’ai une sortie prévue en octobre 2025, un montant année pleine estimé à 70.000€ -> je dois avoir -17.500€ dans ma cellule « montant BP » 2025 (soit 70.000 x 3/12), et -52.500 dans ma cellule « montant BP » 2026 (soit 70.000 – 52.500).

B. Pour les colonnes « coût annuel »

C’est plus simple :

Cas 1 : si l’année N est comprise entre l’année d’entrée et l’année de sortie, la cellule doit reprendre le montant indiqué dans la cellule « montant estimé année pleine»

Cas 2 : Si l’année N est égale à l’année d’entrée ou de sortie, alors la cellule « coût annuel » devra se calculer au prorata. Par exemple : la cellule doit indiquer 30.000€ en 2022 si le mois d’entrée est avril et le montant estimé en année pleine est de 45.000€ (45.000 x 8/12 = 30.000€). Dans ce même exemple, si le mois de sortie est septembre, la cellule doit également indiquer 30.000€ (45.000€ x 8 mois de présence sur 12 = 30.000€)

Cas 3 : Si l’année N est antérieure à l’année d’entrée ou postérieure à l’année de sortie, alors la cellule coût annuel doit être égale à 0

J'espère que mes explications sont suffisamment claires et pas trop embrouillées.. et merci d'avance en tout cas pour votre aide

Bonjour,

J'ai commencé à avoir des formules qui ressemblent à quelque chose, mais toujours pas le bon résultat.

Je tenais à éclaircir avec toi un détail de ton exemple. Pour les colonnes montant BP :

- 2022 pour le mois d'avril, tu donne un résultat = montant * ( 12 - 4 ) / 12

- 2023 pour le mois de juillet, tu donne un résultat = montant * ( 13 - 7 ) / 12

Je suis confus, le négatif n'est pas le même. Ton exemple est-il bon ou ai-je raté quelque chose?

si tu veux jeter un œil sur ce que j'ai commencé

Cordialement,

Bonjour,

merci beaucoup pour ta réponse

Je viens de vérifier, et mon exemple est en effet incorrect, je m'en excuse:

- on considère les mois d'entrée comme plein; donc s'il y a une entrée au mois d'avril, on compte 9 mois sur l'année d'entrée, d'avril inclus à décembre. Du coup la cellule I4 du tableau doit être égale à 33.750€ dans l'exemple que je donne (45.000 * 9/12)

- pour les mois de sortie, on suit la même logique: on considère que la sortie se fait au 1er du mois. Du coup pour une sortie en septembre, on compte 8 mois plein de janvier à août inclus

J'espère avoir répondu à ta question; n'hésite pas si tu as besoin d'autres précisions.

Merci

Bonjour,

Une précision, lorsqu'il n'y a pas de date de sortie, le montant estimé doit être affiché dans les coûts annuels des années suivantes ou doit il s'arrêter au quand le cout annuel = montant estimé?

ton exemple :

proposition :

Aussi, j'ai du mal à comprendre l'histoire de l'exemple à 75 000€.

le but est que le montant passe à 0 l'année de sortie?

Cordialement,

Bonjour,

merci pour ton message

Voici mes réponses ci-dessous en rouge:

lorsqu'il n'y a pas de date de sortie, le montant estimé doit être affiché dans les coûts annuels des années suivantes ou doit il s'arrêter au quand le cout annuel = montant estimé?

-> s'il n'y a pas de date de sortie, le coût annuel est égal au montant estimé en année pleine perpétuellement : la logique, c'est que le coût induit par une entrée représente un coût annuel chaque année tant qu'il n'y a pas de date de sortie.

Aussi, j'ai du mal à comprendre l'histoire de l'exemple à 75 000€.

le but est que le montant passe à 0 l'année de sortie?

-> pour les colonnes coût annuel, oui c'est l'idée (sauf s'il y a eu des mois de présence l'année de sortie : par exemple une sortie le 1er juillet de l'année N entraîne un coût annuel de 6 mois, ou 50% du montant annuel année pleine, sur l'année N)

pour les colonnes montant BP, la logique est différente : ici l'idée est de comparer les montants de BP à BP. Ainsi, une entrée en N pour 50k€ par exemple va engendrer une augmentation du BP de 50k€ par rapport au BP N-1. Du coup, s'il n'y a pas de sortie, le montant des BP des années suivantes va être égal à 0.

S'il y a une sortie en revanche, alors j'aurais -50k€ sur l'année de sortie par rapport au BP N-1.

J'espère avoir répondu à tes questions; n'hésite pas à revenir vers moi si ce n'est pas clair.

Merci

Bonjour,

En lisant entre les lignes de tes conditions, j'ai cru repéré certaines logiques pour faciliter les formules.

Voici un essai de formules. Peux tu me dire si elles s'appliquent correctement à ton tableau?

(peut être prévoir une colonne d'année vierge à la gauche du tableau pour le bon fonctionnement)

Cordialement,

Bonjour,

désolé je viens de m'apercevoir que je n'avais pas envoyé la réponse que j'avais préparé...!

Déjà un grand merci pour la formule que j'aurais été bien incapable de produire moi-même

Les formules répondent à la plupart des cas de figure, avec un souci subsistant néanmoins pour les années suivant les années de sortie:

1. sur les colonnes "montant BP" : la formule fonctionne sur les années N de sortie, mais j'ai un souci pour l'année N+1 : si je prends l'exemple de la ligne 5 du tableau: le salarié sort en septembre 2026. En BP, on va défalquer 4/12 du montant estimé année pleine en 2026 (soit -25k€ ici), et on défalque les 8/12 restant en 2027 (soit -50k€). Sur 2028 et après, on a plus rien sur la colonne BP.
2. sur les colonnes "montant annuel" : la somme fonctionne s'il n'y a pas de sortie mais j'ai le même souci que pour les colonnes BP en cas de sortie.
Si je reprend l'exemple de la ligne 5 : si le salarié sort au 1er septembre 2026, son coût sera du montant de l'année pleine * 8/12 en 2026 (soit 50k€ ici); dans ce cas la formule fonctionne pour l'année N de sortie, par contre pour les années d'après, comme le salarié n'est plus présent, le coût annuel doit être égal à 0.

La formule idoine ne doit plus être très loin... : un grand merci d'ores et déjà pour le temps passé!

NB: tu fais référence à une colonne d'année vierge à la gauche du tableau : quelle serait son utilité pour toi?

Bonjour,

Voici la formule modifiée.

Pour la colonne vierge, c'est car la formule utilise les informations de l'année N-1.

Evidement, pour la colonne 2021 il n'y a pas d'année N-1.

Pour éviter que le formule donne une erreur (voir cas où il y a une sortie sans entrée) je ne l'avais pas mise dans la colonne montant BP 2021.

Libre à toi d'y laisser la formule ou non.

Voila, je pense avoir répondu à tes demandes.

N'hésite pas à revenir si tu trouves un problème.

Cordialement,

C'est parfait, un grand merci!

Bonjour,

je me permets de te déranger de nouveau: en rentrant la formule dans mon grand tableau général, j'ai constaté un souci sur une partie de la formule, liée je pense à la disposition de mon tableau, qui est légèrement différente de celle du fichier que j'avais envoyé préalablement en exemple.

Si je prends la formule que tu avais indiquée cellule I3 du dernier fichier de chiffrage que tu m'as transmis, j'ai un souci sur la partie de la formule en rouge ci-dessous:

=SI($I$1=C3;SI(D3="janvier";B3;B3*(13-EQUIV(D3;références!$B$3:$B$14;0))/12);SI($I$1=E3;-B3*(13-EQUIV(F3;références!$B$3:$B$14;0))/12;SI($I$1=E3+1;-H3;SI($I$1>=C3;SI(SOMME(F3:G3)=0;0;B3-H3);0))))

En effet, si je duplique la formule dans le tableau de chiffrage ci-joint (à partir de la colonne AE, pour les années 2022 et au-delà), cette partie de la formule ne fonctionne plus: je pense que c'est à cause des colonnes concernées par ce calcul qui ne se suivent pas immédiatement dans le fichier complet (et encore, j'ai supprimé des colonnes avant envoi de ce fichier en PJ!)

J'ai essayé en remplaçant la formule SI(SOMME(...) par une formule du type SI([mois de sortie]+[montant BP N-1]), mais dans ce cas j'ai un problème de valeur sur toutes les lignes concernées par une année et un mois de sortie.

Je n'ai dupliqué la formule que sur l'année 2022 pour l'instant; je ne suis pas allé au-delà.

Y a t-il une possibilité de modifier la formule en tenant compte de la disposition du tableau complet, ou faut-il que je revoie la disposition de mes colonnes?

Encore désolé pour la "re-sollicitation"; je pensais que le cas était résolu mais c'était sans penser que la duplication de la formule dans un tableau disposé autrement pouvait remettre en cause le fonctionnement de la dite formule...

Encore merci, et n'hésite pas si mes explications ne sont pas claires.

Bonjour,

après une bonne nuit et avec les yeux en face des trous, j'ai fait la petite modification sur la formule, qui fonctionne désormais.

En testant les différents cas de figure, j'ai constaté qu'il y en avait un qui n'était pas couvert par la formule : il s'agit du cas où il y a une entrée en N et une sortie en N+1

Les montants à obtenir dans ce cas de figure sont indiqués ligne 7 du tableau en PJ (ligne en vert)
La ligne 6 du tableau (en bleu) montre le résultat que l'on obtient avec la formule actuelle.

Concrètement, cela signifie que:

1. pour les colonnes BP : s'il y a une entrée en mai N, on va avoir le montant estimé année pleine x 8/12 en N, et, en N+1, la différence entre le montant estimé année pleine et le montant de l'année N.
On aura 0 en N+2 et au-delà, étant donné que la sortie se fait en N+1

2. pour les colonnes montant annuel :

s'il y a une entrée en N et une sortie en N+1, on a dans ce cas coût annuel N = montant BP N; coût annuel N+1 = montant BP N+1, etc.

Toutes les autres autres conditions restent valables, c'est pourquoi il n'y a rien à changer dans la formule existante, mais que celle-ci est à compléter en tenant compte de ce cas de figure.

Un grand merci par avance; je reste bien sûr disponible pour toute précision.

Bien le bonjour,

Je t'offre cette solution qui m'a retourné le cerveau

J'espère avoir pris en compte tous les cas de figure.

Attention, les formules pour la colonne 2021 sont differentes car il n'y a pas d'année N-1!

Cordialement,

Bonjour,

je crois avoir testé tous les cas de figure possibles, y compris les plus tordus, et j'en arrive à la conclusion suivante: ça marche!

Un grand merci à toi, pour la solution et le retournement de cerveau (j'espère quand même qu'il a pu retrouver sa position habituelle désormais!)