Formule de conditions avec multicritères et dates

Bonjour,

Normalement, je fais mes recherches sur internet et je trouves moi-même des formules satisfaisantes pour mon usage. Toutefois, je sèches pour ce que je veux faire ici... Si une bonne âme pourrait m'aider, je lui en serait reconnaissant.

Je vous joins un fichier pour plus de compréhension et pour expliquer ce que je veux faire.

Je souhaiterais que ma colonne D fasse la différence entre les colonnes B et C en fonction des dates en colonnes A et E.

Par exemple ligne 6 : Les frais ont été payé le 04/08/2015. Mon critère année est en A1 et les frais à payer sont en B1. Je n'aurais que ces deux critères, ça ne me poserait pas de soucis, mais je dois rajouter les critères pour les années suivantes également... Dès lors, le résultat obtenu par ma formule n'est pas le bon; 3 qui correspond à 8 - 5 (frais à payer de 2016) au lieu de 2 (=7 - 5).

En outre, j'ai rajouté mes critères jusque 2027, n'y a-t-il pas une formule qui prendrait en compte les colonnes en entier afin de pouvoir rajouter des critères le cas échéant ?

J'ai essayé Sommeprod mais je n'arrive pas à l'utiliser avec la fonction ANNEE et je ne vois pas quelle fonction je pourrais utiliser.

En bonus, sur un autre fichier, j'ai la formule {=SI(A2="";"";MAX(('Savings Movements'!B2:B150=A2)*('Savings Movements'!E2:E150<0)*'Savings Movements'!A2:A150))} appliquée sur des dates.

Explication :

A2 = Cellule de référence pour faire la liaison avec ma seconde feuille (Saving Movements en colonne B)

Saving Movements colonne E : représente des mouvements ou l'absence de mouvements (en valeur comptable)

Saving Movements colonne A : Ce sont les dates

L'idée est de reprendre la date du dernier mouvement négatif relatif à la cellule de référence.

Ma formule fonctionne parfaitement sauf quand la cellule de référence ne comprend aucun mouvements négatifs, la date qui apparaît alors correspond à 01/01/1900. Manque-t-il un élément à ma formule pour que le 01/01/1900 n'apparaissent pas ?

Merci d'avance

Cdlt

Gold

Hum !

Jusqu'au 12 mai 2015, on fait la balance avec 2015. Jusque là je peux comprendre.

Au 4 août on est toujours en 2015, mais on fait la balance avec 2016. Pourquoi donc ? Quelle règle justifie ça ?

Au 10 septembre on fait toujours balance avec 2016, mais rien ne se cumule.

Au 21 juin qui arrive ensuite (?) la balance revient avec 2015.

Puis on passe au 19 janvier 2016, balance avec 2016 ? mais non ? se solde par balance avec 2015 !

Et pourquoi ces versements irréguliers ?

C'est un jeu ésotérique ?

Peux-tu formuler une règle complète et cohérente, qui permette en recensant tous les cas de positionnements absolus ou relatifs de tes "variables" de construire une table de décision qui ne laissera pas de case vide ?

Pour le moment, j'utiliserais volontiers ALEA dans une formule, ce serait drôle et changerait à chaque recalcul.

Cordialement

Alors non, ce n'est pas un jeu ésotérique ;)

Pour expliquer les versements irréguliers :

Je suis actuellement en train d'établir un fichier qui retrace des versements et des retraits par de multiples clients. Ceux-ci sont entrés manuellement sur un premier onglet.

Sur un deuxième onglet, je fais un résumé par client de tous les mouvements entrants et sortants. Jusque là, je n'ai pas eut de problème avec mes formules, tout semble concorder.

Les clients, au premier versement, doivent payer des frais d'entrée.

Alors par rapport aux données fournies dans mon fichier, la colonne E correspond à la date du premier versement. Celle-ci est nécessaire afin de déterminer les frais à payer.

Les colonnes A et B sont mes critères ajustés ou non manuellement chaque année.

La colonne C correspond aux frais payés encodés manuellement dans mon premier onglet. Il y a normalement une formule (qui est correcte dans mon fichier d'origine).

La colonne D est une colonne de vérification pour savoir si tout a bien été versé.

Le problème dans ma formule, c'est la partie en gras: =SI(C2=0;0;SI(ET(ANNEE(E2)=ANNEE($A$2);$B$2-C2=0);$B$2-C2;SI(ET(ANNEE(E2)=ANNEE($A$3);$B$3-C2=0);$B$2-C2;$B$3-C2)))

Au 4 août 2015, on passe sur 2016 parce la condition (ET(ANNEE(E2)=ANNEE($A$2);$B$2-C2=0) n'est plus respectée. Il passe donc à la suite de ma formule qui passe sur les critères de 2016.

Au 21 juin, mes critères (ET(ANNEE(E2)=ANNEE($A$2);$B$2-C2=0) pour 2015 sont respectés donc le calcul s'arrête là.

Au 19 janvier 2016, erreur dans ma formule au niveau du VRAI du dernier SI. Il s'agit de B3-C2 en fait. (Oops) Merci de me l'avoir pointé du doigt ;)

Alors, dans les colonnes A et B, j'ai entré des critères jusque 2027 mais je me suis arrêté à 2016 dans ma formule parce qu'au final, j'aurais obtenu le même résultat. Ce qu'il me manque, c'est le moyen de clôturer ma formule par la soustraction du montant payé par les frais de l'année correspondante.

J'imagine que l'imbrication des SI ne fonctionne pas dans mon cas vu que je n'arrive pas à clôturer la formule. Le hic c'est que je ne sais pas laquelle utiliser :(

J'espère avoir été assez clair lol

Cordialement

Bonjour,

Il ne manque pas de sujets bizarres sur les forums, sur lesquels je passe souvent car, de mon point de vue, le "problème" posé n'a pas d'intérêt et sa discussion n'en serait même pas amusante... Mais dans ton cas, un parfum d'intrigue m'a incité à chercher à savoir ce qu'il y avait dessous (chacun ses travers, péchés mignons, voire fantasmes...)

Je ne suis pas déçu sur un point : ça prend des allures d'énigme policière !

Tu expliques les versements irréguliers :

Et tu décris longuement ce que tu fais... Après avoir expurgé tes propos de tout ce qui te concerne, car la psychanalyse de ton comportement formuleur est un autre sujet, que reste-t-il comme information :

• des clients font des versements et retraits multiples
• lors du premier versement ils doivent payer des frais d'entrée.

Du diable si ça explique pourquoi les versements sont irréguliers !

Ils pourraient l'être pour des tas de raisons :

• parce qu'on ne leur impose pas de versement régulier
• parce qu'il n'y a pas de règle à cet égard, c'est un état de fait
• parce qu'il y a des échéances fixes mais qu'on note la date du paiement effectif qui présente quelque fluctuations par rapport à la date d'échéance

Très heureux d'avoir mis le doigt sur une anomalie 2016 (je jure que tout soupçon d'erreur m'était étranger).

Alors, pourquoi au 4 août 2015 passe-t-on à une balance appréciée sur 2016 ?

Parce que la double condition AnnéeE=AnnéeA et B-C=0 n'est plus satisfaite !!!

Bravo ! Soit tu veux en remontrer aux politiciens dans l'art de répondre péremptoirement à côté de la question, soit tu veux m'illustrer les carences de l'enseignement depuis plusieurs années ou décennies, dans sa capacité à développer chez les élèves des facultés de raisonnement logique.

J'ai lu tes formules. Moi ! Il ne m'a pas échappé que cette condition n'était pas satisfaite. Mais quelle règle du jeu implique qu'il y ait lieu de distinguer précisément ceux qui satisfont cette condition de ceux qui ne la satisfont pas. Mystère !

Je n'ai aucune idée de l'activité dont il est question, et c'est tout à fait indifférent, le but n'est pas que je m'informe là-dessus. La question formulée au sens strict est de bâtir des formules de calcul pour obtenir un résultat attendu. Tu construis des formules qui ne te donnent pas satisfaction. Ces formules tu les construis en traduction mathématique des règles fonctionnelles ou comportementales régissant l'activité considérée. Elles n'aboutissent pas au résultat souhaité, soit parce que la traduction n'est pas la bonne ou qu'elle est incomplète, soit parce que les règles sont partiellement défectueuses. Il faut en tout cas partir des règles déterminant des conditions formelles de décision pour pouvoir formuler des conditions. Alors si tu cesses de te chatouiller le nombril et de patauger dans tes formules qui ne t'en diront pas plus que ce que tu y as déjà mis, le problème pourra être posé de façon rationnelle à partir des règles à prendre en considération pour les traduire en formules mathématiques.

Bien cordialement

Bonjour

Alors je ne suis pas un politicien qui cherche à éviter les questions qui pourraient m'aider à résoudre mon problème...

Que les paiements soient réguliers ou non n'est pas le problème en soit vu qu'une ligne correspond à un client... On peut prendre un autre exemple par l'absurde... Chaque ligne correspond à une naissance...

Donc je vais mettre en français intégral ce que je souhaite obtenir :

Je souhaiterais vérifier que chaque personne née dans une année donnée ait bien payé la cotisation de naissance fixée lors de l'année de sa naissance. Si cette cotisation n'a pas été payée ou payée partiellement, j'aimerais que le solde à payer apparaisse comme résultat.

C'est ce que j'ai vainement essayé d'expliquer sur base de mon fichier et en expliquant le contexte.

Mes critères sont clairement définis, il s'agit de l'année de référence et et de la cotisation de l'année de référence. La condition est que la cotisation soit entièrement payée.

Je sais également très bien que ma formule n'est pas viable ! Je l'ai partagée pour essayer de démontrer ce que je souhaitais faire (grosse erreur apparemment...)

Si tu as compris où je veux en venir (ce dont je ne doute pas), cesse de démontrer que je suis l'illustration des carences de l'enseignement, ce n'est pas ce que j'ai demandé

Je te remercie encore par avance de m'accorder un peu de ton temps pour résoudre cette énigme insoluble (ou rendue insoluble )

Cordialement

Une information supplémentaire : chaque ligne correspond à un client.

Ceci n'étant pas évident : il semblait plutôt que chaque ligne était coupée en 2 parties bien distinctes, sur la gauche une sorte de barème de cotisation par année de référence, sur la droite effectivement cela pouvait être une partie client, sans qu'on puisse affirmer s'il s'agissait de clients distincts les uns des autres ou pas.

Autre information induite par une ligne = un client est une seule cotisation à régler.

Ces éléments doivent être clairement confirmés mais sont encore bien insuffisants à définir l'ensemble des règles.

Cordialement

En effet, une ligne = un client = une cotisation. Desolé, j'aurais dû être plus clair à ce niveau...

Quels autres éléments sont manquants?

Plus clair !?

Une ligne = un client, on a donc un client, une cotisation et une année propre à chaque client !

Un client par an, ça limite les clients, on peut s'occuper de chacun tout à fait individuellement.

Comment tu arrives à un client par an avec ce que j'ai énoncé ?

Une ligne = un client

Il y a une cotisation par année.

Le client paye la cotisation de l'année correspondante à sa date d'entrée donc chaque ligne reprend la cotisation payée par le client correspondant.

Cela ne veut pas dire pour autant que j'ai un client par année... Il peut y en avoir des dizaines, des centaines de clients.

J'ai retravaillé un peu le premier fichier en terme de présentation.

Si avec ça, c'est pas plus clair, je ne vois pas comment je pourrais m'expliquer plus clairement et je ne veux pas te faire perdre ton temps plus longtemps

Bien à toi

C'est vrai que là, c'est un peu plus clair en ce sens que le barème est distingué.

Les chiffres mentionnés laissent penser que si le client a réglé les droits correspondant au barème annuel, il est à jour (solde=0), mais que s'il ne s'en est pas complètement acquitté on évalue le solde sur une année réactualisée, mais pourquoi 2016 dans ce cas puisque nous sommes toujours en 2015.

Et pour les clients entrés en septembre ou en janvier, c'est de l'anticipation !

L'imprécision c'est : le solde à quelle date ? Un solde sans date ça n'a aucun sens.

Et les conditions de son calcul en fonction de la date qui ne sont nulle part exprimées et qui devraient pourtant l'être sous une forme concise, précise et traduisible en modalités de calcul.

Cordialement

Réponse dans le message.

MFerrand a écrit :

C'est vrai que là, c'est un peu plus clair en ce sens que le barème est distingué.

Les chiffres mentionnés laissent penser que si le client a réglé les droits correspondant au barème annuel, il est à jour (solde=0) Exact, mais que s'il ne s'en est pas complètement acquitté on évalue le solde sur une année réactualisée, mais pourquoi 2016 dans ce cas puisque nous sommes toujours en 2015. C'est là qu'est mon problème dans la formule. Je ne souhaite pas réactualiser l'année.

Et pour les clients entrés en septembre ou en janvier, c'est de l'anticipation !lol oui... Je construits le fichier en fonction d'une activité future. Les dates auraient pu être dans le passé. En soit, cela n'a pas d'impact sur la formule

L'imprécision c'est : le solde à quelle date ? Un solde sans date ça n'a aucun sens. Pas nécessairement si on en fait un suivi journalier. Ce qui importe dans mon cas c'est que le montant soit soldé. J'ai des frais à payer à partir de ma date initiale et des frais payés éventuellement partiellement. Ce qui m'intéresse c'est de voir le montant restant à payer

Et les conditions de son calcul en fonction de la date qui ne sont nulle part exprimées et qui devraient pourtant l'être sous une forme concise, précise et traduisible en modalités de calcul.

Cordialement

En fait, avec toute cette discussion, je me suis rendu compte que j'ai voulu compliquer les choses inutilement en trouvant le solde à payer directement alors que j'aurais du simplifier les choses en rajoutant une colonne intermédiaire (voir le fichier ci-joint).

L'opération que je souhaitais obtenir est surement faisable mais pas lisible et surtout en dehors de mes capacités.

Finalement, je me suis aussi rendu compte grâce à toi que même si je sais ce que je souhaite obtenir, j'ai du mal à l'expliquer clairement. A l'avenir, je ferai un effort sur la présentation du sujet en évitant les informations inutiles et en répondant aux questions demandées

Désolé de t'avoir fait perdre ton temps et merci pour les conseils et ma remise en place.

Cordialement

Je ne demandais pas d'autocritique, mais seulement que tu définisses la règle...

Le solde peut être calculée sur n'importe quelle date, passée, présente ou future, mais il a nécessairement un rapport avec la date d'entrée et la date actuelle. [Si tu calcules un solde futur, il ne sera exigible qu'à la date pour laquelle il a été calculé, et si entretemps le client modifie la situation (puisque cette date n'est pas encore arrivée) le solde futur s'en trouvera modifié...].

Prenons l'hypothèse que le règlement doit intervenir dans l'année. Tu apprécies le règlement fait par rapport à l'année d'entrée, mais s'il est incomplet, tu calcules le solde sur le barème correspondant à l'année d'AUJOURDHUI(). On peut faire une formule...

Si le règlement est exigible à la date d'entrée, ou dans un délai plus court que la fin de l'année (éventuellement), tu apprécies par rapport à cette date d'exigibilité et à AUJOURDHUI.

Si une règle est définie on peut bâtir une formule avec, sinon on reste dans le doute.

Bon courage.

Et bonne soirée.

Dans ce cas ci, la règle, c'est que le règlement est exigible à la date d'entrée.

Il fallait le dire plus tôt !

Ta formule devrait donc être :

=SI(D2>=C2;C2-D2;RECHERCHEV(SI(B2>=AUJOURDHUI();B2;MOIS.DECALER(B2;12*ARRONDI.SUP(FRACTION.ANNEE(B2;AUJOURDHUI());0)));$H$2:$I$5;2;1)-D2)

Pas de difficulté pour la première partie : règlement complet => solde 0 ; règlement dépassant les frais à régler => solde créditeur (affiché avec signe -).

La seconde tient compte de tes anticipations :

• Si la date d'entrée n'est pas encore dépassée, on prend la date d'entrée pour évaluer les frais et établir le solde.
• Si dépassée, on incrémente la date d'un an par année ou fraction d'année écoulée depuis cette date.

Il faudrait que tu nomme la plage H2:I5 en champ dynamique, pour utiliser le nom et ne plus avoir à modifier la formule.

Cordialement

Ferrand

Après avoir décortiqué la formule, je comprends.

Je ne connaissais pas la fonction fraction.année. Ça devrait m'être encore utile en d'autres occasions.

Merci pour ton aide.

Cordialement.

Bonne continuation.