Gemeenschappelijke aankopen
- 05/01/2012
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Tijdens (o.a.) de Kerstperiode gebeurt het vaak dat een groep mensen geschenken voor mekaar koopt. Ieder betaalt een welbepaald bedrag (dit kan gelijk zijn of verschillend) en zo worden de cadeaus gefinancierd die aan eenieder gegeven worden. Daarbij is het gangbaar dat ieder aangeeft wat hij / zij graag zou willen hebben, en dat dan gekeken wordt wie dit praktisch het makkelijkst kan gaan kopen. De effectief betaalde kosten worden dan daarna verdeeld en de saldi vereffend. Kan Excel ons bij de vereffeningen helpen?
Ja! Niet alleen kan je nagaan wie wat betaalt heeft, wie te veel of te weinig betaald heeft, en hoeveel ieder nog moet ontvangen of bijdragen om te komen tot de juiste uitkomst. Dit artikel gaat dan ook verder dan mijn eerdere artikel over de rekening terugbetalen.
Uitgangssituatie
- 6 personen: Peter, Martine, Tommy, Elke, Marcel, Laurens
- elke persoon moet een cadeau geven van 10 € aan elke andere persoon
- er wordt afgesproken wie wat wenst te krijgen, en ook wie welke cadeau gaat aanschaffen
De Excel toepassing bovenaan deze pagina is generieker: niet iedereen hoeft hetzelfde te geven, en niet iedereen hoeft hetzelfde te krijgen. Daarnaast, als je de hele toepassing ontleedt en uitkleedt, ga je ook meer of minder personen in de analyse kunnen betrekken. Minder personen is echter makkelijk: zet voor de overbodige personen alles op 0.
Dit zijn de betalingen:
Met SOM.ALS() functies (Engels: SUMIF()) kan je dan eenvoudig de totalen oplijsten: wat ieder betaald heeft, wat nog aangekocht moet worden, en wat ieder te veel of te weinig betaald heeft:
Eindresultaat
Merk op dat er 9 individuele aankopen of betalingen gedaan werden. Dit is mooi. Want als er 6 mensen elk 5 cadeaus moeten kopen zijn dit 30 aankopen. Door overleg is die 30 verminderd kunnen worden tot 9. Maar hoe maken we personen blij die geld voorschoten ten behoeve van de gemeenschap? Zij willen het te veel betaalde geld terugvorderen. Wel, de bedragen in de blauwe kolom zijn van groot belang; die zullen de basis vormen voor de vereffening, waarna iedereen 10 € betaald heeft. En dit zonder enige VBA-code en al bij al eenvoudige functies. Ik laat alvast het resultaat zien:
Aan de rechterkant van de tabel zien we bij wijze van overzicht dat:
- Er voor 300 € aankopen gedaan werden (6 personen geven elk 10 € aan 5 andere personen)
- er 5 terugbetalingen moeten gedaan worden om te vereffenen
- voor een waarde van 1/3 van het totaal
De aankopen en bedragen werden door mij bewust gekozen om toch een zekere spreiding te vertonen. Als 1 persoon alle (9) aankopen zou doen, dan is het probleem triviaal. De diagonaal vertoont enkel 0: niemand hoeft aan zichzelf te betalen of van zichzelf te ontvangen. Geloof het of niet, maar het is zo dat alle cellen in de tabel met 1 formule berekend worden. Uiteraard verschilt de formule van cel tot cel door het gebruik van relatieve en absolute celverwijzingen, de logica echter is volledig gelijk. We geven de formule in door het bereik van linksboven tot rechtsonder te selecteren (in het bestand is dit B15:G20), de formule in te typen en dan Ctrl + Enter te drukken. Niet gewoon Enter (dat is voor 1 cel), en ook niet Ctrl + Shift + Enter (matrixformule).
De formule is:
=ALS($A15=B$14;0;ALS($G$3="FOUT";0;MAX(0;MIN(-$C5-SOM($A15:A15);
B$12-SOM(B$14:B14)))))
De 2 ALS-functies gaan over de bedragen op de diagonaal en de juiste invoer van
de individuele betalingen. Het echt spannende deel van de formule volgt vanaf
MAX. Aan jullie om hem te ontleden op basis van het bestandje.
In deze topic ga je een vergelijkbare opzet
vinden. Succes!
