XL 2019 Automatiser des pourcentages de répartition en fonction de budgets

[Arkana54]

Bonjour,

Je me casse les dents sur une recherche de formule Excel. J'ai essayé plusieurs combinaisons, mais à ce jour rien de répond à ma problématique et je souhaite éviter l'utilisation de Macros.

Je souhaite estimer le pourcentage de répartition de mes temps de travaux mensuels sur différents projets en fonction de leurs budgets disponibles respectifs.

Chaque projet (Projet 1, Projet 2 & Projet 3) dispose d'une durée limitée dans le temps et d'un budget défini.
Le but ultime étant de m'assurer du "financement" de mon coût mensuel chaque mois et de maximiser la consommation des budgets de chaque projet, sinon je sur-facturerai ou sous-facturerais mes clients.

Je joins un exemple en PJ: j'ai indiqué le résultat approximatif que je souhaite obtenir dans les cellules en jaune, mais que je souhaiterais donc automatiser.

Mes points de contrôle seraient :
- en colonne K: je souhaiterais que les montants de la colonne K tendent vers 0, mais ne doivent pas nécessairement être égaux à 0.
- sur la ligne 8: la ligne devrait nécessairement être à 100%

J'espère être clair et que quelqu'un pourra m'aider...

Merci d'avance pour vos réponses .

 

[Compte Supprimé 979]

Bonjour Arkana54

Pour moi, en K5, mettre la formule

=D5-($B$1*SOMMEPROD(E5:J5))

Puis recopie vers le bas

Trop de temps passé sur le Projet 2

@+

 

[eastwick]

Bonjour, au risque de passer pour un être au Q.I. déficient, j'ai du mal à comprendre votre répartition.

 

[Arkana54]

Pour moi, en K5, mettre la formule

=D5-($B$1*SOMMEPROD(E5:J5))

Puis recopie vers le bas

Trop de temps passé sur le Projet 2

Bonjour BrunoM45,
Merci pour votre retour; en effet la formule que vous proposez est plus correcte que l'initiale proposée.

Mais ma problématique est principalement d'obtenir automatiquement les pourcentages des cellules "en jaune" en tenant compte des budgets disponibles et de la périodicité des projets; je les ai renseigné "en dur" dans les cellule pour le moment ne trouvant pas de solution.

De fait, le Projet 2 ayant la plus longue durée et le plus gros budget, le temps à y consacrer devrait bien être le plus élevé ; c'est ce que je souhaite automatisé .

Merci encore et pardon si mes explications manquent de clareté

 

[Arkana54]

Bonjour, au risque de passer pour un être au Q.I. déficient, j'ai du mal à comprendre votre répartition.

Bonjour Eastwick,
Non pas du tout, je doute moi-même de la clarté de mes explications .

Le coût mensuel des 6000 EUR doit être répartit chaque mois sur mes projets en cours.
Je souhaite obtenir automatiquement ces pourcentages de répartition en fonction des budgets disponibles et de la périodicité des projets.

Ex : au mois de Janvier 2021, seul le projet 2 est en cours et le budget disponible est de 20 000 EUR, donc 100% du coût mensuel doit lui être imputé.
Au mois de Février 2021, les projet 2 & 3 sont ouverts, je dois donc répartit le coûts mensuel sur les 2 en fonction des budgets disponibles (reste alors 6 000 EUR sur le projet 1 pour 2 mois, 14 000 sur le projet 2 pour 5 mois et 10 EUR sur le projet 3 pour 5 mois)

J'espère être plus clair et merci pour votre aide

 

[Nairolf]

Salut tout le monde,

J'ai regardé depuis hier, et je vois un moyen pas trop complexe en utilisant le VBA (via un calcul itératif).
Je n'ai pas trouvé le moyen, pour le moment de le faire directement par des formules (pas simple car beaucoup de contraintes à prendre en compte).

 

[Nairolf]

Re,

@Arkana54, je ne sais pas si une solution VBA t'intéressera, mais en voici une dans le fichier joint.
A noter que je ne l'ai pas testée pour des cas différents de ceux que tu proposais à l'origine.
Le code prend en compte le nombre de mois et de projets à traiter, j'ai ajouté des formules afin de simplifier certains traitement.

 

[Arkana54]

Bonjour Nairolf,
Pardon pour mon délai de réponse.
Oui ça fonctionne bien! Je souhaitais éviter de passe par des macros et VBA, mais cela me semble nécessaire en fin de compte.
Je vais partir de votre travail pour tenter de le dupliquer.

Merci à tous pour l'aide et le temps passé