Afletteren in Excel: 2 handige voorbeelden

Een regelmatige en tijdrovende klus is het manueel afletteren van balansposten. Gelukkig kan Excel je hierbij goed van dienst zijn. In dit artikel zal ik twee voorbeelden toelichten.

Het afletteren van debiteurenbedragen

Ik heb een download van debiteurenbedragen per transactie en ik wil de bedragen afvinken per debiteur die op nul uitkomen. We gaan uit van het volgende overzicht, waarin je een aanvullende kolom Matched toevoegt. De set is als een tabel opgemaakt met als naam: Debiteuren.

Je plaatst in cel E4 de volgende formule: =AFRONDEN(SOMMEN.ALS([Amount];[Debtor];[@Debtor]);2)=0

Het resultaat leidt tot een WAAR of ONWAAR positie.

Het zou mooi zijn als alle posten die gladlopen (WAAR) voorzien worden van een afwijkende kleur.  Daartoe kies je uit het menu Start à Stijlen à Voorwaardelijke Opmaak à Nieuwe regel en vul hem als volgt in:

Als opmaak geef je een lichtgele kleur in. De formule ziet erop toe, dat alle posten die als WAAR worden gekwalificeerd, worden voorzien van de afwijkende kleur. Het resultaat ziet er als volgt uit:

Afletteren van bankposten

Ik heb een download set met cijfers waarvan sommige bedragen tegen meerdere bedragen kunnen wegvallen. We gaan uit van de volgende indeling:

In kolom B zie je een Excel-tabel met diverse cijfers genaamd OpenAmounts. In kolommen D tot en met F vindt de reconciliatie plaats met behulp van de volgende dynamische matrixformules:

D4=OpenAmounts[Amount]

E4=ALS(D4#<0;-D4#&”-“&AANTAL.ALS(D$4#:D4#;D4#);D4#&”-“&AANTAL.ALS(D$4#:D4#;D4#))

F4=ALS(AANTAL.ALS($E$4#;E4#)=2;”x”;””)

De formules worden automatisch naar beneden doorgetrokken omdat we te maken hebben met dynamische matrixformules, die u kunt herkennen aan de blauwe omkadering.

Het hashtag-teken # achter een cel-adres in de formule verwijst naar een cel-adres welke deel uitmaakt van een dynamisch formulebereik, waardoor de uiteindelijke formule zelf ook de eigenschap van deze dynamiek erf en een dynamische matrixformule wordt.

Analyseer, presenteer en rapporteer efficiënter en effectiever met Excel

Werk je regelmatig met Excel? Ben je daarbij veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek in de cursus Excel voor financieel professionals vele mogelijkheden die jou direct voordeel opleveren. Win tijd, verbeter je analyses en presenteer effectiever.

Meer informatie | Meld je direct aan