Waarom is mijn totaal na het koppelen hoger?

Je koppelt orderregels aan ontvangsten (of facturen aan betalingen, uren aan projecten) met een opzoekformule of een merge, en ineens is de som groter dan in de bron. Dat komt bijna altijd door een sleutel die aan beide kanten meer dan één keer voorkomt. Plak hieronder beide tabellen, dan zie je per sleutel wat er dubbel telt en hoe je het naar rato verdeelt zonder dat er een cent bij komt of af gaat.

Per regel: de sleutel in de eerste kolom, het bedrag in de laatste. Tab, puntkomma of spaties als scheiding, dus rechtstreeks uit Excel plakken werkt. Kopregels zonder bedrag worden overgeslagen. Een punt met drie cijfers erachter («1.234») leest als 1,234; schrijf «1234» of «1.234,00» als je duizenden bedoelt.

Wat er gebeurt bij een koppeling

  1. Eén op één is veilig. Komt een sleutel in A en in B precies één keer voor, dan levert de koppeling één rij op en blijven beide totalen kloppen.
  2. Eén op veel herhaalt de enkele kant. Eén orderregel van € 1.500 met drie ontvangsten wordt drie rijen, en wie daarna de orderkolom optelt, krijgt € 4.500. De kant met meerdere regels telt wel goed.
  3. Veel op veel herhaalt beide kanten. Twee orderregels en drie ontvangsten worden zes rijen: A telt drie keer, B twee keer. Een opzoekformule die alleen de eerste treffer pakt, maskeert dit, maar koppelt dan alles aan die ene regel.
  4. Wat maar aan één kant staat, valt weg. Een gewone (inner) koppeling laat sleutels zonder tegenhanger verdwijnen. Dat maakt het totaal juist lager en leest makkelijk als «klopt bijna».

Naar rato verdelen, en wanneer niet

De nette oplossing voor veel-op-veel is elke combinatie een deel geven: orderregel × ontvangst, met het bedrag verdeeld naar de verhouding aan de andere kant. Dan telt elke kolom precies op tot de bron. Drie uitzonderingen:

Voorbeeld. Orderregel € 1.000 met ontvangsten van 1, 1 en 1 stuks: 333,33 + 333,33 + 333,33 = 999,99. De restcent gaat naar het grootste deel (bij gelijke delen het eerste), zodat het 333,34 + 333,33 + 333,33 wordt.

Alles rekent in je browser; er wordt niets verstuurd of opgeslagen.