XL 2013 Ventilation de dépense entre plusieurs personnes (vacances)

pat87

XLDnaute Nouveau
Bonjour à tous,

je voudrais avoir par Excel, avoir la répartition automatique de la somme que chaque personne négative doit donner aux personnes positive.
Voici le tableau de référence:


Sylvie
Cathy
Monique
Laurence
Claudie
-18,7
-170,19
219,6
59,14
-89,86

Monique doit recevoir de la part de:

Sylvie
18,7
Cathy
170,19
Claudie
30,71


et Laurence doit recevoir de la part de:

Claudie
59,15


merci d'avance pour votre aide.
 
Dernière édition:

zebanx

XLDnaute Accro
Bonjour Pat87, le forum

Un fichier fourni par REYNEU (merci!) sur le lien suivant

J'ai rajouté quelques informations de contrôle mais le fichier et qui me parait bien pratique pour la répartition de frais et jusqu'à 15 personnes de surcroit.
Peut-être que d'autres répondront suivant la clé demandé ou que d'autres fichiers existent.

@+
 

Fichiers joints

pat87

XLDnaute Nouveau
Bonjour,
Merci pour ta réponse.

les fichiers sont très intéressant mais trop compliqué pour mon niveau.
En pièce jointe, le fichier sur lequel je travaille.
Je veux garder toute la structure du classeur et avoir uniquement la formule qui fait la répartition

A payer
Sylvie
Cathy
Monique
Laurence
Claudie
-18,70
-170,19
219,60
59,14
-89,86
---------
-----------
…......
….........
Monique
219,6
Reçois de
Sylvie
18,7
Reçois de
Cathy
170,19
Reçois de
Claudie
30,71
Laurence
59,14
Reçois de
Claudie
59,15



D'avance merci .
Pat
 

Fichiers joints

zebanx

XLDnaute Accro
Re-

bonjour à Jbarbe ;)

Une autre proposition de ventilation répartition mais avec un travail restant manuel.

L'idée est là même mais on utilise des "rangs" et on suit l'ordre de paiement.
On s'occupe d'abord de celle qui doit recevoir le plus et on affecte par montant sur celle qui doit donner le plus. Et ainsi de suite en regardant le reliquat restant après chaque affectation.

Si quelqu'un a envie d'ailleurs d'automatiser le deuxième tableau en prenant en compte cet effet de rang / montant dû / reliquat... je serai ravi d'avoir sa proposition et l'en remercie par avance.


@+
 

Fichiers joints

Dernière édition:

pat87

XLDnaute Nouveau
Bonjour,
Merci pour le fichier.
les résultats ne sont pas bon si on ajoute une somme dans le tableau des dépenses.

Dans le fichier ci-joint, il y a un tableau qui fait cela.
Cependant, je ne peux pas l'insérer tel quel car je doit garder ma structure que j'ai et de pouvoir gérer la répartition avec un nombre de part différent suivant chaque personne (famille avec composition différente).
l'idée, c'est de partir des avances faite par chaque personnes. zone V19:Z21 dans mon fichier
Sylvie
Cathy
Monique
Laurence
Claudie
-18,70
-170,19
219,60
59,14
-89,86


Mais vu mon niveau, je ne sais pas faire….

Bon courage et d'avance merci.

Pat
 

Fichiers joints

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à tous,

En général, ce genre de méthode (chacun donne à chacun) est vouée à l'échec et prend de nombreux mois (c'est dû à la nature humaine).
Le plus simple est qu'un seul membre (en général ceux capables de le faire, on les connait. Ce sont ceux qui dans un groupe en font le plus - et d'ailleurs, ce sont toujours les mêmes) prenne en charge la cagnotte (ou pot commun).

Alors le calcul est simple. On calcule la dépense moyenne. On soustrait cette dépense moyenne à la dépense de chacun:
  1. si la différence est positive, la personne prend du pot cette différence (recevra de la personne gestionnaire du pot)
  2. si la différence est négative, la personne verse au pot cette différence (versera à la personne gestionnaire du pot)
  3. les remboursements ne commencent que lorsque ceux qui doivent verser au pot l'ont tous fait (règle1 indispensable)
  4. une seule personne prend en charge le pot

Les avantages de cette méthode sont :
  • les calculs sont simplissimes et sans macro
  • la règle 3 fait que les retardataires au versement au pot (et plus on est nombreux, et plus ils sont nombreux) se font "enguirlandés" par l'ensemble des autres membres qui attendent leur remboursement (et ça, c'est efficace)
  • on ne gère pas de la monnaie dans tous les sens - les seuls flux sont ceux de et vers le pot
  • si une personne ne rembourse pas et qu'on décide de liquider malgré tout le pot, aucun flux n'a été fait. Il suffit de refaire le calcul très simple : nouvelle moyenne des dépenses sans la personne défaillante, on en déduit les nouvelles sommes à être versées ou retirées du pot et on le fait dans la foulée puisque seule la personne responsable possède toute la cagnotte. Comment ferait-on si des personnes avaient déjà donné ou d'autres été remboursées ?
  • c'est plus rapide et efficace. L'autogestion monétaire au sein d'un groupe, je n'y crois pas!;)
 
Dernière édition:

CHALET53

XLDnaute Barbatruc
Re,

Je suis d'accord avec l'approche de Mapomme
C'est d 'ailleurs la méthode que j'utilise en général. Une seule personne assure la compensation entre les membres à partir du net dû par chacun
C'est ce que permet la feuille synthèse de mon fichier
J'avais complété à la demande de certains par le QuiDoitQuoiAQui : c'est quand même un peu tordu

a+
 

CHALET53

XLDnaute Barbatruc
J'essaie d'apporter ma modeste contribution quand je le peux.
Malgré tout, je reste un petit par rapport aux très très grands qui interviennent sur ce forum
Bonne journée
 

pat87

XLDnaute Nouveau
Bonjour à tous,

En général, ce genre de méthode (chacun donne à chacun) est vouée à l'échec et prend de nombreux mois (c'est dû à la nature humaine).
Le plus simple est qu'un seul membre (en général ceux capables de le faire, on les connait. Ce sont ceux qui dans un groupe en font le plus - et d'ailleurs, ce sont toujours les mêmes) prenne en charge la cagnotte (ou pot commun).

Alors le calcul est simple. On calcule la dépense moyenne. On soustrait cette dépense moyenne à la dépense de chacun:
  1. si la différence est positive, la personne prend du pot cette différence (recevra de la personne gestionnaire du pot)
  2. si la différence est négative, la personne verse au pot cette différence (versera à la personne gestionnaire du pot)
  3. les remboursements ne commencent que lorsque ceux qui doivent verser au pot l'ont tous fait (règle1 indispensable)
  4. une seule personne prend en charge le pot

Les avantages de cette méthode sont :
  • les calculs sont simplissimes et sans macro
  • la règle 3 fait que les retardataires au versement au pot (et plus on est nombreux, et plus ils sont nombreux) se font "enguirlandés" par l'ensemble des autres membres qui attendent leur remboursement (et ça, c'est efficace)
  • on ne gère pas de la monnaie dans tous les sens - les seuls flux sont ceux de et vers le pot
  • si une personne ne rembourse pas et qu'on décide de liquider malgré tout le pot, aucun flux n'a été fait. Il suffit de refaire le calcul très simple : nouvelle moyenne des dépenses sans la personne défaillante, on en déduit les nouvelles sommes à être versées ou retirées du pot et on le fait dans la foulée puisque seule la personne responsable possède toute la cagnotte. Comment ferait-on si des personnes avaient déjà donné ou d'autres été remboursées ?
  • c'est plus rapide et efficace. L'autogestion monétaire au sein d'un groupe, je n'y crois pas!;)
 

pat87

XLDnaute Nouveau

Bonjour
mapomme,

Merci pour ta réponse mais aujourd'hui avec les paiements par carte, le liquide ne cours pas trop.
Suivant le groupe que tu gères et du montant des sommes, une seule personne ne peux tout avancer mais une seule pour la gestion oui.
Mon fichier à la base peut suivre aussi bien le liquide mis dans un pot commun ou tout autre achat fait par carte ou cheque et aussi le nombre de part de chaque participant
Je gère avec ce fichier depuis plusieurs années et pas de problème. La répartition de qui doit à qui est simple à faire à la main et de prend que quelque minutes, mais aujourd'hui avec l'informatique, on doit pouvoir automatiser cela.
Dans mon dernier post, j'ai mis un fichier >> https://www.excel-downloads.com/attachments/130815_repartition-frais-xls.1032807/ qui contient une formule complexe mais qui fonctionne bien dans ce fichier.
le but maintenant est d'adapter cette formule à mon fichier >> https://www.excel-downloads.com/attachments/fichier-test-pour-repartition-automatique-des-depenses-xls.1032759/

Bonne journée
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @pat87 :)

Merci pour ta réponse mais aujourd'hui avec les paiements par carte, le liquide ne cours pas trop.
Où vois tu des espèces dans ma méthode ?

[mode humour] Rassure toi! Je ne suis pas un perdreau de l'année. Malgré cela, j'ai déjà entendu parlé de la carte bancaire (qui est bleue non? Je ne sais plus :confused:) et aussi des sites qui gèrent les cagnottes. Après comment s'en servir? Faudrait qu'on me forme.:p [/mode humour]
 
Dernière édition:

zebanx

XLDnaute Accro
Bonjour à tous (@mapomme ;), chalet53:cool:)

Merci @mapomme pour ton poste du 11.

Tu as bien raison à la fois sur le côté de suivi d'une "cascade" (waterfall) de flux et, ce qui m'a le plus intéressé, sur l'éclairage mis concernant la séparation entre les dépenses engagées (expenses) et les paiements.

Je ne trouve pas que cela soit simplissime comme tu l'as indiqué (et à moins que tu ne me prouves le contraire mon ami!;)) mais il apparait nécessaire de bien distinguer les données :

1. Un suivi des frais engagés (sur base de BDD "frais") et la répartition par participant (tableau double entrée qui paye - qui doit être payé) comme fait au #5 (on peut imaginer une formule - plus simple - de clé de répartition sur les montants restants dûs par "agent payeur")

2. Une base de donnée des remboursements avec les montants payés en fonction du 1. (exemple : Cathy a réglé à Monique un montant de X euros) qui vient alimenter un tableau construit à l'identique du 1 mais qui vient indiquer les montants remboursés. Là, besoin du VBA pour aller taper pour chaque payeur / receveur le flux qui lui est remboursé.

3. Un autre tableau en-dessous qui matérialise le solde entre ce qui doit être payé et ce qui a été effectivement remboursé.

Cela parait fastidieux mais la lisibilité me parait bien utile de cette manière pour suivre sur une durée non figée les mouvements des uns et des autres.
suivi expenses-refund.png
@CHALET53 : gros tableau (mais trop gros pour moi)

----
Et bonne finalisation @pat87, yakafokon effectivement :)p)

Mais j'ai trouvé fort utile d'utiliser un nombre de parts.
 

CHALET53

XLDnaute Barbatruc
J'ai repris ton exemple dans mon fichier
Pour les règlements : soit la feuille synthèse avec une personne qui assure la compensation, soit les feuilles QuipayeA ou QuiRecoitDe
Il est gros pour pouvoir être utilisé pour une centaine de participants
La feuille Factures permet d'envisager pour un bloc donné de modifier la répartition par défaut pour être adapté à un groupe de payeurs plus restreint
Chaque bloc permet d'enregistrer 12 factures pour un payeur et si ce n'est pas suffisant, il est possible d'utiliser 2 blocs(ou plus) pour un payeur
A+
 

Fichiers joints

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @zebanx :)
Je ne trouve pas que cela soit simplissime comme tu l'as indiqué
Simplissime parce que je ne m’intéresse pas aux dépenses. Ce qui m'intéresse, c'est uniquement les paiements: L'argent que chacun a sorti et s'il en a sorti plus ou moins que les autres par rapport au paiement moyen que chacun aurait dû réaliser.

(et à moins que tu ne me prouves le contraire mon ami!;))
Et en plus tu veux me faire bosser :oops:
 

Discussions similaires


Haut Bas