Répartition automatisée de frais selon des tranches

Vous remerciant par avance !

Bonjour,

c'est difficille de faire correspondre les date,

par exemple AF1 =2018-01-31

dans la tranche J1:Q1 il n'y as pas cette date, le plus proche avant étant 2018-01-30

et

par exemple AG1 =2018-02-28

dans la tranche J1:Q1 il n'y as pas cette date, le plus proche après étant après 2018-03-30 ou avant 2017-12-30

doit-on faire correspondre les date avant ou après ?

Bonjour,

Merci de votre réponse.

Mea culpa, j'ai partagé un mauvais fichier.

En réalité au niveau des dates, il n'y aura pas de problème car je travaille avec la fonction fin.mois.

Donc pour les dates, je suis bien sur une correspondance exacte c'est à dire que si dans le tableau de "projection" mois par mois la fonction trouve une correspondance avec une des dates de la plage J1:Q1 il récupère le pourcentage dans la table qu'il multiplie avec le total et qu'il divise ensuite par le nombre de sous tranche de la table sous tranche.

Mais si par exemple la fonction récupère pour fin décembre 2017 une tranche de 10% qui s'étale sur 2 mois il faut qu'elle mette 5% en décembre puis 5% en janvier.

Désolé si je ne m'exprime pas très clairement.

Je met en pièce jointe le fichier avec les dates corrigées !

Bonjour RoubenM,

Voyez le fichier joint

Bonjour,

question de néophyte : 30/09 c'est un hasard ou la 1ère tranche sera toujours la fin d'un trimestre ?

Les tranches sont et seront toujours par trimestres sans exception ? (en gros peut-on se contenter de la 1ère date et en déduire les suivantes ?)

eric

Bonjour et merci de vos réponses,

@njhub merci pour votre fichier, je suis en train d'analyser vos formules; j'ai l'impression que vous passez nécessairement par une phase "manuelle" où vous calculez à la main le % des sous tranches dans une autre feuille (la feuille stratégie) avant de les réutiliser à la main dans les formules sur la page de résultat. Je vais voir si je peux utiliser vos idées de solution !

Mon idée était d'avoir une formule unique qui fonctionne de la manière suivante :

  • j'entre le % des tranches
  • j'entre le nombre de sous tranche
  • dans un tableau de "projection de trésorerie" qui se déroule mois par mois (avec idéalement les mois dans les têtes de colonnes pour pouvoir exploiter ces données par la suite), la formule calculerait le montant à payer (en multipliant le pourcentage de la tranche divisé par le nombre de sous-tranche) mais ceci en tenant compte que potentiellement sur un même mois, il peut y avoir une somme de plusieurs sous-tranche (il peut par ex y avoir la tranche 2 qui correspond à 10% en plein, mais aussi en plus une "sous tranche" de la tranche 1 qui serait de 3% donc en tout dans la cellule 13%).
Donc en réalité la formule dont j'aurai besoin pour résoudre mon problème serait un somme.prod.si qui malheureusement n'existe pas. La question est de savoir si c'est remplaçable en utilisant des plusieurs conditions et sous conditions ou pas.

@eriiic au niveau de date pas forcément à la fin d'un trimestre.. disont que le nombre de tranche sera sans doute le même à chaque fois (8 tranches) mais par contre au niveau des dates la 1ère peut être en novembre et la deuxième peut être en décembre comme en février.

voilà ! je vais continuer à chercher ! n'hésitez pas à me dire si vous avez d'autres idées ^^ si jamais je trouve une solution je reviens la poster

Bonjour,

un essai par macro.

J'ai un écart avec toi : 5000 au 31/03 et non au 31/05.

Elle suppose que tu n'as que 8 tranches max, et que AB:CG aura toujours une taille suffisante.

eric

Bonjour Eric,

merci beaucoup pour votre fichier,

Cependant lorsque j'active la macro une erreur variable non définit apparaît (pour la variable dat) et lorsque j'ai déclaré la variable (en format Date) il y a une erreur pour "tableau attendu" qui apparaît; j'ai fait quelque recherche mais je ne comprends pas la provenance de cette erreur.

ça serait génial que ça fonctionne !

Bonjour,

à tester pour dat

crdlt,

André

Bonjour André,

merci de votre réactivité et de votre aide, vous êtes vraiment sympa !

Lorsque j'active le bouton rafraîchir sur ton fichier, il me sort l'erreur suivante :

"Les informations de licence de ce composant sont introuvables. Vous n'avez pas la licence adéquate pour utiliser cette fonctionnalité dans l'environnement de création."

C'est un problème qui viendrait de ma version d'excel ? j'utilise excel pour mac 2017


J'ai redémarrer excel et ça fonctionne, merci infiniment à vous tous !

Je vais tâcher d'adapter le code à mon cas particulier ^^

@eriiic @Andre13

Petite question sur une potentielle amélioration

- si je souhaite que mon tableau de "trésorerie" commence à une date antérieure à la date de la première tranche lorsque j'active la macro il me classe tout de même la première tranche dans la première colonne du tableau, donc les dates ne collent pas ^^

Existe t il la possibilité que la macro compare les dates et affecte la tranche dans la bonne partie du tableau ?

Je suis vraiment désolé de poser une question qui paraîtra sans doute assez bête mais je suis vraiment débutant en VBA et si vous arrivé à facilement me donner un début de solution je suis donc preneur !

Je vais quoi qu'il en soit me pencher sur le code de la macro pour comprendre son fonctionnement et la logique suivant laquelle vous l'avez créé ça sera très instructif je pense

Merci d'avance

re,

pas d'erreur mais à modifier :

For lig = 1 To UBound(fixe)

=====> tr_taux = [J3:Q3].Offset(lig).Value <===

===> tr_nb = [S3:Z3].Offset(lig).Value <====

Cependant lorsque j'active la macro une erreur variable non définit apparaît (pour la variable dat)

J'ai dû tout modifier avant de mettre en ligne, j'était parti de la 1ère date puis de trimestre en trimestre.

Etrange que Option Explicit ne m'ait pas signalé ma nouvelle variable (?!?)

- si je souhaite que mon tableau de "trésorerie" commence à une date antérieure à la date de la première tranche

A-priori il faudrait agir juste sur - Year(dat(1, 1)) * 12 - Month(dat(1, 1))

Mais on va déjà déboguer l'existant. Tu dis si ça te parait ok en fonctionnement et résultats.

Et es-tu sûr que 58 mois seront toujours suffisants ? Une macro ne supporte pas un changement de structure des données sans adaptation.

Pour les autres messages dans Outils/Références tu n'en aurais pas une marquée 'manquante' des fois ? Si oui, décoche-la.

Ci-joint Version avec la variable

version date début variable

à tester...

eric

Super Eric, merci, ça fonctionne effectivement; j'ai hâte de comprendre le fonctionnement du code.

J'avais posté le problème sur un autre forum et j'ai eu une autre solution par formule qui fonctionne également (même si la formule est assez lourde).

Je met le fichier avec la formule en pièce jointe car ça pourrait être utile à quelqu'un !

Merci encore à tous de votre aide !

Bonjour à tous,

Je rebondis sur mon topic car je m'aperçois qu'il y a un degré de finesse au niveau de la répartition des sous-tranches que j'aimerai ajouter et je ne parviens pas à faire "comprendre" au code !

Je m'explique :

Pour le moment si on a un total de 100.000 avec une tranche de 10% qui se divise en 10 sous-tranches, le code part de la date de la tranche de 10% et ensuite pendant ce mois là et les 9 qui suivront il "projette" 1.000 € de dépense.

La où j'ai un soucis c'est qu'en général la date de la tranche est celle de fin d'une phase. Donc dans le cas où il y a un montant à payer en une fois ça fonctionne bien (car on paye en fin de phase).

Si maintenant j'ai une tranche qui se subdivise, elle devrait se subdiviser dès le "début de la phase".

Autrement dis, si j'utilise la date de début de phase je serais bon pour les tranches qui se subdivise mais pas bon sur mes postes à payer en une fois sur la tranche.

Et si j'utilise la date de fin les tranches qui se subdivisent se place au mauvais moment.

Désolé je sais que ça n'est sans doute pas très clair pour vous.

En réalité ce qu'il faudrait que le code fasse est ce qui suit :

  • si la sous-tranche est égal à 1, tu calcule ton montant sur la date en tête de tranche (date de fin de phase);
  • si la sous-tranche est supérieure à 1, tu utilise celle du début de phase comme point de départ (date qu'on pourrait déterminer: -- soit en la mettant dans une cellule -- soit en partant de la date en tête de tranche à laquelle on soustrait un nombre de mois égal au n° de sous tranche).

Je sais que ce problème est soluble en tout bêtement ajoutant une tranche qui serait (tranche début de phase) et une autre de fin de phase; mais je voulais savoir s'il existe la possibilité de faire cela par VBA en conservant qu'une seule tranche pour des raisons de facilité de lecture.

Merci d'avance pour vos idées de solution ^^

Bonjour,

En réalité ce qu'il faudrait que le code fasse est ce qui suit :

  • si la sous-tranche est égal à 1, tu calcule ton montant sur la date en tête de tranche (date de fin de phase);
  • si la sous-tranche est supérieure à 1, tu utilise celle du début de phase comme point de départ (date qu'on pourrait déterminer: -- soit en la mettant dans une cellule -- soit en partant de la date en tête de tranche à laquelle on soustrait un nombre de mois égal au n° de sous tranche).

à laquelle on soustrait un nombre de mois égal au n° de sous tranche)

Non, - nombre de mois +1

Ce qui revient au même pour les 2 cas : la date indiquée est celle du dernier paiement si j'ai bien suivi.

moisRelatif = Year(dat(1, col)) * 12 + Month(dat(1, col)) - Year(dat1) * 12 - Month(dat1) - tr_nb(1, col) + 1

eric

Bonjour Eric,

Merci pour ton message

La modification que tu proposes fait effectivement fonctionner le code mais je m'aperçois que ce que je veux est encore un petit peu plus complexe et nécessite forcément (je crois en tout cas) de passer par une date de début à renseigner dans le code; en effet je m'aperçois dans les contrats fournisseur que la dépense se fait de la manière suivante dans la plupart des cas :

Paiment prévisionnel de la tranche (10%) en 10 fois ==> puis si la période est au final raccourcis à 5 mois par exemple, le solde est payé le 5ème mois.

Donc sur une tranche d'un montant total de 100.000 € on paierait 1.000 les quatre premiers mois et le solde donc 6.000 le cinquième mois.

Le problème est que je ne peux pas me contenter de renseigner 5 sous tranches au lieu de 10 car sinon chaque mois va être à 2.000 € ce qui ne correspondrait pas à la réalité.

puis si la période est au final raccourcis à 5 mois par exemple, le solde est payé le 5ème mois.

Si je comprend bien ce n'est pas prévu à l'avance.

A moins d'une boule de cristal efficace c'est difficile de l'inclure. Tu ne peux pas corriger manuellement ces cas exceptionnels qui doivent être aussi divers que variés ?

Ou si c'est un mode de paiement courant (dans ce cas pourquoi écrire est au final raccourcis ???) il faut prévoir une écriture spécifique style : 10 (4+1)

Mais bon, c'est la 4e fois que tu modifies ta demande, pas très sérieux là. Donc termine ton analyse que tu aurais dû faire avant et revient quand tu seras sûr de toi et avec tous les éléments. On verra si c'est toujours faisable.

eric

Bonjour à tous,

Je souhaiterai savoir s'il est possible de faire comprendre au programme que si la date est antérieure à la date actuelle: il n'agisse pas.

C'est à dire que pour les mois précédents septembre le programme n'efface pas ce qui est déjà entré dans le tableau et ne fasse le calcul que pour la période qui suive.

Merci par avance !

Rechercher des sujets similaires à "repartition automatisee frais tranches"