Combinatie

Boekingen met ronde bedragen vinden met FILTER, REST, AFRONDEN en ABS

Markeer grootboekregels waarvan het bedrag een exact veelvoud van 1.000 is (en boven een minimum ligt): ABS haalt het teken weg, AFRONDEN haalt kleine rekenverschillen weg, REST toetst het veelvoud en FILTER toont de regels.

Gebruikte functies:FILTERRESTAFRONDENABSExcel 2021 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Grootboekregels met ronde bedragen

Situatie

Bij de journaalpostentest voor Veldhuis Installatie BV zoek je regels met een rond bedrag: exacte veelvouden van 1,0001.000 zijn een indicator voor frauderisico, zeker in handmatige boekingen.

Je typt het veelvoud in G1 en een minimumbedrag in G2, zodat kleine ronde bedragen zoals 3,0003.000 de lijst niet vervuilen. Creditboekingen staan als negatief bedrag. FILTER heeft Excel 2021, Microsoft 365 of nieuwer nodig.

De gegevens

ABCDEFGHIJK
1DagboekBoekstukDatumOmschrijvingBedragVeelvoud1,0001.000
2MEM25-080131-03-2025Vakantiegeld€18,400.00€ 18.400,00Minimumbedrag5,0005.000
3INK25-041202-04-2025Huurfactuur€52,300.00€ 52.300,00
4MEM25-083330-06-2025Correctie omzet-€96,000.00€ -96.000,00MEM=FILTER(A2:E9, (REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "Geen regels met ronde bedragen")=FILTER(A2:E9; (REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "Geen regels met ronde bedragen")25-083330-06-2025Correctie omzet-€96,000.00€ -96.000,00
5BNK25-059015-07-2025Ontvangst debiteur€25,000.00€ 25.000,00BNK25-059015-07-2025Ontvangst debiteur€25,000.00€ 25.000,00
6MEM25-084515-09-2025Omboeking crediteur€3,000.00€ 3.000,00MEM25-085228-11-2025Omboeking huur€15,000.00€ 15.000,00
7MEM25-087131-12-2025Reservering bonus€41,200.00€ 41.200,00
8MEM25-088031-12-2025Btw-correctie€12,500.50€ 12.500,50
9MEM25-085228-11-2025Omboeking huur€15,000.00€ 15.000,00
10
Formulecel (F4)InvoerOverloopresultaat

Formule in F4

=FILTER(A2:E9, (REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "Geen regels met ronde bedragen")
=FILTER(A2:E9; (REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "Geen regels met ronde bedragen")

Resultaat: MEM | 25-0833 | 30-06-2025 | Correctie omzet | -€96,000.00 / BNK | 25-0590 | 15-07-2025 | Ontvangst debiteur | €25,000.00 / MEM | 25-0852 | 28-11-2025 | Omboeking huur | €15,000.00MEM | 25-0833 | 30-06-2025 | Correctie omzet | € -96.000,00 / BNK | 25-0590 | 15-07-2025 | Ontvangst debiteur | € 25.000,00 / MEM | 25-0852 | 28-11-2025 | Omboeking huur | € 15.000,00

Hoe de onderdelen samenwerken

Let op: Rond bedragen met centen altijd af voordat je ze met REST toetst. Een berekend bedrag kan een kleine afrondingsfout bevatten, en dan wordt een rond bedrag gemist.

De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.

  1. =AFRONDEN(ABS(E2:E9), 2)
    =AFRONDEN(ABS(E2:E9); 2)

    ABS haalt het teken weg en AFRONDEN rondt het bedrag af op twee decimalen. De acht bedragen worden 18.400, 52.300, 96.000, 25.000, 3.000, 41.200, 12.500,50 en 15.000. De creditboeking van -96.000 is nu positief.

  2. =REST(AFRONDEN(ABS(E2:E9), 2), G1)
    =REST(AFRONDEN(ABS(E2:E9); 2); G1)

    REST geeft wat overblijft na deling door het veelvoud in G1 (1.000). De acht resten zijn 400, 300, 0, 0, 0, 200, 500,50 en 0. Een rest van 0 betekent een exact veelvoud.

  3. =(REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)
    =(REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)

    De eerste test is WAAR voor regel 3, 4, 5 en 8 (rest 0). De tweede test, bedrag van minstens het minimum in G2 (5.000), is alleen ONWAAR voor regel 5 (3.000). Vermenigvuldigen geeft 0, 0, 1, 1, 0, 0, 0, 1: drie regels zijn gemarkeerd.

  4. =SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2))
    =SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2))

    Telt de nullen en enen op: 0 + 0 + 1 + 1 + 0 + 0 + 0 + 1 = 3. Dat is het aantal gemarkeerde regels.

  5. =SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))
    =SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))

    Vermenigvuldigt elke markering met het absolute bedrag en telt op: 96.000 + 25.000 + 15.000 = 136.000. Dat is de totale waarde van de gemarkeerde regels, ongeacht debet of credit.

  6. =FILTER(A2:E9, (REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "Geen regels met ronde bedragen")
    =FILTER(A2:E9; (REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "Geen regels met ronde bedragen")

    FILTER houdt de rijen over waar de markering 1 is: regel 3, 4 en 8 met alle vijf de kolommen, 3 rijen bij 5 kolommen. Is geen enkele regel gemarkeerd, dan toont het de melding in plaats van #BEREKENEN!.

Stap voor stap

  1. ABS(E2:E9) haalt het teken weg en AFRONDEN(...; 2) ruimt de decimalen op: 18,40018.400, 52,30052.300, 96,00096.000, 25,00025.000, 3,0003.000, 41,20041.200, 12,500.5012.500,50, 15,00015.000.
  2. REST van elke waarde door G1 (1,0001.000): 18,40018.400 geeft 400, 52,30052.300 geeft 300, 96,00096.000 geeft 0, 25,00025.000 geeft 0, 3,0003.000 geeft 0, 41,20041.200 geeft 200, 12,500.5012.500,50 geeft 500,50, 15,00015.000 geeft 0. Rest = 0 is WAAR voor regel 3, 4, 5 en 8.
  3. ABS(E2:E9)>=G2 toetst aan het minimum van 5,0005.000: WAAR voor elke regel behalve regel 5 (3,0003.000).
  4. De twee testen vermenigvuldigen geeft 0, 0, 1, 1, 0, 0, 0, 1. Regel 5 valt af omdat die onder het minimum ligt, ook al is 3,0003.000 een veelvoud van 1,0001.000.
  5. FILTER houdt regel 3, 4 en 8 over: boekstuk 25-0833 (-96,00096.000), 25-0590 (25,00025.000) en 25-0852 (15,00015.000). Het resultaat loopt vanaf F4 over als 3 rijen bij 5 kolommen.
  6. Aantal en totaal met dezelfde test in SOMPRODUCT: 0 + 0 + 1 + 1 + 0 + 0 + 0 + 1 = 3 regels, en 96,00096.000 + 25,00025.000 + 15,00015.000 = 136,000136.000 in absolute bedragen. Het totaal met teken zou -96,00096.000 + 25,00025.000 + 15,00015.000 = -56,00056.000 zijn, en dat verbergt de omvang van de creditboeking.

Waarom het werkt

REST met een deler van 1,0001.000 geeft alleen 0 als het bedrag zonder rest door 1,0001.000 te delen is, en ABS zorgt dat debet en credit zich gelijk gedragen. Twee WAAR/ONWAAR-testen vermenigvuldigen werkt als EN, zodat FILTER één kolom met nullen en enen krijgt.

Omdat het veelvoud en het minimum in cellen staan, toets je opnieuw op 500 of 10,00010.000 zonder de formule aan te passen.

FILTER heeft Excel 2021, Microsoft 365 of nieuwer nodig; het aantal en het totaal met SOMPRODUCT werken ook in oudere versies.

Let op

  • Valkuil met kommagetallen: een berekend bedrag zoals 4,000.104.000,10 + 4,000.204.000,20 - 0,30 toont als 8,000.008.000,00 maar kan intern als 7,999.9999999999997.999,999999999999 zijn opgeslagen. REST daarvan door 1,0001.000 geeft ongeveer 999,999999999999 en geen 0, dus een rond bedrag wordt gemist. AFRONDEN(...; 2) vóór REST maakt er eerst weer 8,0008.000 van.
  • Laat je ABS weg uit de minimumtest, dan vallen creditboekingen af: -96,00096.000 is niet groter dan of gelijk aan 5,0005.000, dus de correctie omzet zou worden gemist. REST zelf kan hier met negatieve bedragen omgaan, de vergelijking niet.

  • Is G1 leeg of 0, dan deelt REST door nul en geeft de formule #DEEL/0!.

    Een rond bedrag is een indicator en geen bewijs: leg per gemarkeerde regel vast waarom die acceptabel is, of werk hem verder uit.

Varianten

=ALS(EN(REST(AFRONDEN(ABS(E2), 2), $G$1)=0, ABS(E2)>=$G$2), 1, 0)
=ALS(EN(REST(AFRONDEN(ABS(E2); 2); $G$1)=0; ABS(E2)>=$G$2); 1; 0)

Hulpkolom voor oudere Excel-versies zonder FILTER: typ de formule in L2, vul door tot L9 en filter op 1. De markeringen zijn 0, 0, 1, 1, 0, 0, 0, 1, net als in de hoofdformule.

=SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))
=SOMPRODUCT((REST(AFRONDEN(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))

Alleen het totaal van de gemarkeerde regels, zonder ze op te sommen: 136,000136.000. Het werkt in elke Excel-versie.

Alle combinaties