Repartir proportionnellement un montant

[alex53]

Bonjour le forum,
Apres recherche sur le forum, je n'arrive pas a trouver la solution a mon probleme.

Le probleme est le suivant:
Produit A dans entrepot 1 :valeur 10 euros
Produit A dans entrepot 2 :valeur 20 euros

Valeur a repartir proportionnellement entre les entrepots pour le produit A: 5 euros

Donc ici evidemment la formule a appliquer dans excel est ( par exemple pour Produit A entrepot 1):
10/(10+20)*5 = 1.67 euros

Le probleme est que j'ai beaucoup d'entrepots et beaucoup de produits. je dois donc retaper la formule a chaque fois. Ce qui est treeees long. Je suis persuade qu'il existe un moyen de gagner du temps, mais comment, je suis perdu? Une formule a creer avec VBA? une formule matricielle.

Voici le fichier (simplifie). Je suis ouvert a toute proposition, que ce soit la resolution du probleme grace au fichier ou une direction a suivre, une idee a creuser.

Merci

 

[eriiic]

Re : Repartir proportionnellement un montant

Bonjour,

une fonction personnalisée est faisable.

Est-ce qu'une macro traitant la plage sélectionnée ne serait pas mieux ?
Si oui préfères-tu y avoir la formule ou le résultat brut ?

eric

 

[hoerwind]

Re : Repartir proportionnellement un montant

Bonjour,

Essaie en G2 et copie cette formule vers le bas :

=SI(B2="";"";D2/SOMMEPROD((A$2:A$37=A2)*(ESTTEXTE(B$2:B$37)*D$2:D$37))*RECHERCHEV(A2;info!$B$3:$C$14;2;0))

 

[CISCO]

Re : Repartir proportionnellement un montant

Bonjour

Une possibilité en pièce jointe, avec une formule matricielle à valider avec ctrl+maj+entrer.

@ plus

 

[CISCO]

Re : Repartir proportionnellement un montant

Rebonjour

Une autre possibilité, un peu plus difficile à comprendre, mais sans formule matricielle.

@ plus

P.S : Maintenant, tu as le choix, tu peux même inventer d'autres formules en combinant nos diverses propositions.

 

[alex53]

Re : Repartir proportionnellement un montant

YYYYYYEEEEEEEEESSSSSSSSSS!
Merci pour vos reponses.

hoerwind,
Ta reponse marche unpec. Meme mieux que ce que je voulais, car dans mon exemple j'avais rajoute par calcul la colonne E (total par produit), mais avec ta solution, plus besoin de cette colonne, et en plus meme si les produits et les entrepots ne sont pas tries alors ca marche aussi. Magique!

J'ai cependant 2 questions/remarques:
-Est-ce que tu peux m'expliquer ton calcul,surtout la partie SOMMEPROD((A$2:A$37=A2)*(ESTTEXTE(B$2:B$37)*D$2$37)) stp? Et aussi pourquoi dans l'onglet info colonne D j'ai de nouvelles infos? Que sont ces nombres, a quoi servent-ils?
-J'en demande peut-etre beaucoup,mais c'est pour ma culture excel: Existe-il un moyen pour que la formule se mette automatiquement a jour, c-a-d si je copie cette formule dans un autre tableau avec le meme format mais plus de lignes (ex:800) alors il y ai ecrit 800 a la place de 37 (37 est le nombre de ligne du tableau actuel).

CISCO,
Merci pour ton aide. je vais regarder en detail ta formule. J'espere que ca va m'aider a mieux comprendre les formules matricielles.

Eriic,
Merci pour ta reponse. Bien sur, si tu peux me proposer une macro qui montre le resultat brut, ca m'interesse, pour voir comment il est encore possible de resoudre ce probleme.

Merci a tous, et ci joint je remets mon nouveau fichier avec mon calcul actuel, si cela peut aider d'autres personnes sur le forum

Alex53

 

[hoerwind]

Re : Repartir proportionnellement un montant

Re,

SOMMEPROD((A$2:A$37=A2)*(ESTTEXTE(B$2:B$37)*D$2: D$37))
Le ESTTEXTE() ne servait qu'à éviter des messages d'erreurs dans les lignes de sous-totaux, c'était plus court que NON(ESTVIDE()).
Cette fonction a été supprimée (puisqu'il n'y a plus de sous-totaux) et remplacée par un ESTNUM() en début de formule (pour éviter les messages d'erreur des lignes "Entreprsises").

pourquoi dans l'onglet info colonne D j'ai de nouvelles infos?
Je n'en sait rien, je te jure que ce n'est pas moi, je les ai supprimées et cela fonctionne tout aussi bien !

si je copie cette formule dans un autre tableau avec le même format mais plus de lignes
Pour faire cela il faut nommer les plages de façon dynamique.
C'est fait pour les colonnes A et D, vois le menu Insertion - Nom - Définir - "Prod" pour Produit (colonne A) et Val pour Valeur (colonne D).
Tu peux ajouter des données sous la ligne 28, la formule en tient compte.
Il serait peut-être à conseiller de faire la même chose pour le tableau dans la feuille info (B3:C14).
Je te laisse faire, si cela ne va pas tu sais où j'habite !

Vois la pièce jointe.

 

[eriiic]

Re : Repartir proportionnellement un montant

Re,

Je viens de voir ton nouveau fichier.
Tu as ajouté la notion d'entreprise.
Si produit5 de EntrepriseXX n'est pas à comptabiliser avec produit5 d'EntrepriseYY il faut que tu restreignes les plages des propositions précédentes aux cellules de l'entreprise.

eric

 

[alex53]

Re : Repartir proportionnellement un montant

Re,
ok, j'ai compris la formule. Tres utile. Et la formule dans la V2 est encore plus clair. Merci
Nommer les plages de façon dynamique: effectivement, je ne m'en rappelais plus, alors meme que j'ai l'habitude de le faire avec les TCD.

Merci beaucoup pour l'aide.

Alex53

 

[alex53]

Re : Repartir proportionnellement un montant

Hoerwind, je me permets de reposer une question:

J'aimerais en effet compliquer un peu le probleme. Pour l'instant, un montant est proportionnellement reparti par produit.
Est-il possible de repartir ce montant et par produit, et par entrepot?
par exemple: montant 200 euros, qui correspond au produit 1 et a l'entrepot Lille. Je souhaite repartir (deduire) ce montant proportionnelllement par entre tous les produits 1 de l entrepot Lille.

Une idee? Ca m'a l'air beaucoup plus complique...
Ci-joint le fichier avec les infos par entrepot (onglet info)

Merci
Alex53

 

[eriiic]

Re : Repartir proportionnellement un montant

Re,

tu n'as fait aucun commentaire sur mon post #8, tu l'as vu ?

 

[alex53]

Re : Repartir proportionnellement un montant

Eric,
Oui j'ai vu. C'est clair, merci. Mon nouveau probleme est un peu different. Une idee de solution?

Alex53

 

[eriiic]

Re : Repartir proportionnellement un montant

Ah ben excuse-moi de t'avoir dérangé, je ferme donc tout tes fichiers et laisse tomber la macro comme sa construction dépendait de ta réponse...
eric

 

[hoerwind]

Re : Repartir proportionnellement un montant

Re,

Pour eriiiic
Il me semble qu'alex a tenu tenu de ta remarque (justifiée !) sous sa dernière pièce jointe.

Pour alex
Cela doit être faisable, mais pour être sur d'avoir bien compris, pourrais-tu compléter ta dernière pièce jointe par les résultats à obtenir, calculés manuellement.
Plus facile pour voir si on fait fausse route ou non.

 

[alex53]

Re : Repartir proportionnellement un montant

Voici le fichier. J'ai mis a la main les bonnes reponses.

Eric,
Je ne suis pas sur d'avoir bien compris ton dernier message. Il est evident que si tu as une macro pour resoudre ce probleme alors cela m'interesse.

Alex