Combinatie

Verschillen boven de rapportagegrens met LET, FILTER, HOR.STAPELEN en ABS

Stem het grootboek per debiteur af op de specificatie van de cliënt en toon alleen de verschillen boven de rapportagegrens: LET geeft de verschillen één keer een naam, HOR.STAPELEN zet er de debiteur naast, FILTER houdt de rijen over waarvan ABS boven de grens uitkomt.

Gebruikte functies:LETFILTERHOR.STAPELENABSExcel 2024 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Debiteurenverschillen boven de grens

Situatie

Bij de controle van Veldhuis Installatie BV vergelijk je het debiteurensaldo per klant in het grootboek met de ouderdomsanalyse van de cliënt per 31 december 2025. De rapportagegrens is vastgesteld op 2,5002.500. Je wilt een lijst met alleen de klanten waarvan het verschil groter is dan dat, in beide richtingen, om aan de cliënt voor te leggen.

De gegevens

ABCDEFG
1RelatieGrootboekSpecificatieRapportagegrens€2,500.00€ 2.500,00
2D1001€18,450.00€ 18.450,00€18,450.00€ 18.450,00
3D1002€12,300.00€ 12.300,00€14,800.00€ 14.800,00D1004=LET(verschil, B2:B7-C2:C7, FILTER(HOR.STAPELEN(A2:A7, verschil), ABS(verschil)>F1, "Geen verschillen boven de grens"))=LET(verschil; B2:B7-C2:C7; FILTER(HOR.STAPELEN(A2:A7; verschil); ABS(verschil)>F1; "Geen verschillen boven de grens"))€4,500.00€ 4.500,00
4D1003€9,875.00€ 9.875,00€9,575.00€ 9.575,00D1006-€2,930.00€ -2.930,00
5D1004€25,600.00€ 25.600,00€21,100.00€ 21.100,00
6D1005€7,420.00€ 7.420,00€7,420.00€ 7.420,00
7D1006€16,050.00€ 16.050,00€18,980.00€ 18.980,00
8
Formulecel (E3)InvoerOverloopresultaat

Formule in E3

=LET(verschil, B2:B7-C2:C7, FILTER(HOR.STAPELEN(A2:A7, verschil), ABS(verschil)>F1, "Geen verschillen boven de grens"))
=LET(verschil; B2:B7-C2:C7; FILTER(HOR.STAPELEN(A2:A7; verschil); ABS(verschil)>F1; "Geen verschillen boven de grens"))

Resultaat: D1004 | €4,500.00 / D1006 | -€2,930.00D1004 | € 4.500,00 / D1006 | € -2.930,00

Hoe de onderdelen samenwerken

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. =B2:B7-C2:C7
    =B2:B7-C2:C7

    Trekt de specificatie af van het grootboek, debiteur voor debiteur. Het resultaat is één kolom van zes verschillen: 0, -2.500, 300, 4.500, 0 en -2.930.

  2. =ABS(B2:B7-C2:C7)>F1
    =ABS(B2:B7-C2:C7)>F1

    ABS haalt het teken weg, dus een tekort van -2.930 telt als 2.930. Vergeleken met de grens van 2.500 in F1 geeft dit ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR. D1002 is precies 2.500 en ligt dus niet boven de grens.

  3. =HOR.STAPELEN(A2:A7, B2:B7-C2:C7)
    =HOR.STAPELEN(A2:A7; B2:B7-C2:C7)

    HOR.STAPELEN zet de debiteurcodes naast de verschillen: een tabel van 6 rijen en 2 kolommen, van D1001 met 0 tot D1006 met -2.930.

  4. =FILTER(HOR.STAPELEN(A2:A7, B2:B7-C2:C7), ABS(B2:B7-C2:C7)>F1, "Geen verschillen boven de grens")
    =FILTER(HOR.STAPELEN(A2:A7; B2:B7-C2:C7); ABS(B2:B7-C2:C7)>F1; "Geen verschillen boven de grens")

    FILTER houdt alleen de rijen van de tabel over waar WAAR staat: D1004 met 4.500 en D1006 met -2.930. Het verschil wordt hier twee keer berekend, en dat is waarom LET zinvol is.

  5. =LET(verschil, B2:B7-C2:C7, FILTER(HOR.STAPELEN(A2:A7, verschil), ABS(verschil)>F1, "Geen verschillen boven de grens"))
    =LET(verschil; B2:B7-C2:C7; FILTER(HOR.STAPELEN(A2:A7; verschil); ABS(verschil)>F1; "Geen verschillen boven de grens"))

    LET rekent B2:B7-C2:C7 één keer uit onder de naam verschil en gebruikt het twee keer. Het resultaat is dezelfde twee rijen: D1004 4.500 en D1006 -2.930. Komt niets boven de grens uit, dan verschijnt de melding.

Stap voor stap

  1. LET bewaart B2:B7-C2:C7 als verschil: 18,45018.450-18,45018.450 = 0; 12,30012.300-14,80014.800 = -2,5002.500; 9,8759.875-9,5759.575 = 300; 25,60025.600-21,10021.100 = 4,5004.500; 7,4207.420-7,4207.420 = 0; 16,05016.050-18,98018.980 = -2,9302.930.
  2. ABS(verschil) geeft 0, 2,5002.500, 300, 4,5004.500, 0 en 2,9302.930, dus elk verschil is nu positief.
  3. Vergelijken met F1 (2,5002.500): 0 niet erboven, 2,5002.500 niet erboven (gelijk), 300 niet erboven, 4,5004.500 erboven, 0 niet erboven, 2,9302.930 erboven. Dat geeft ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR.
  4. HOR.STAPELEN(A2:A7; verschil) bouwt een tabel van 6 rijen: D1001 0, D1002 -2,5002.500, D1003 300, D1004 4,5004.500, D1005 0, D1006 -2,9302.930. Het verschil met teken blijft behouden, niet de ABS-waarde.
  5. FILTER houdt rij 4 en 6 over, waar de test WAAR is: D1004 met 4,5004.500 (grootboek hoger) en D1006 met -2,9302.930 (grootboek lager). Het resultaat loopt over E3:F4.

Waarom het werkt

Het verschil heb je twee keer nodig: als waarde om te tonen en als waarde om te toetsen. LET rekent het één keer uit en geeft het een naam, zodat beide plekken niet uit elkaar kunnen lopen.

ABS zorgt dat de toets werkt voor zowel te hoge als te lage saldi, terwijl de tabel het teken blijft tonen. Dat vertelt je in welke richting je de cliënt moet bevragen. FILTER doet de selectie en het laatste argument vangt het geval op dat alles onder de grens blijft.

Let op

  • Zonder ABS vindt de toets verschil>F1 alleen positieve verschillen. D1006 met -2,9302.930 valt dan zonder melding weg, terwijl juist daar het grootboek lager is dan de eigen lijst van de cliënt.
  • De toets is strikt groter dan de grens. D1002 verschilt precies 2,5002.500 en staat niet in de lijst. Hanteert je dossier 'tot en met' als grens, of wil je de randgevallen zien, gebruik dan >=.
  • Beide lijsten moeten de debiteuren in dezelfde volgorde hebben. Deze formule trekt rij voor rij af en zoekt niet op debiteurcode. Is de specificatie anders gesorteerd, zoek de specificatie dan eerst op (bijvoorbeeld met X.ZOEKEN op kolom A).

Varianten

=FILTER(A2:C7, ABS(B2:B7-C2:C7)>F1, "Geen verschillen boven de grens")
=FILTER(A2:C7; ABS(B2:B7-C2:C7)>F1; "Geen verschillen boven de grens")

Zonder LET en HOR.STAPELEN: FILTER op de brongegevens. Het toont D1004 en D1006 met grootboek en specificatie (25,60025.600 en 21,10021.100; 16,05016.050 en 18,98018.980), maar niet het verschil zelf.

=SOMPRODUCT(--(ABS(B2:B7-C2:C7)>F1))
=SOMPRODUCT(--(ABS(B2:B7-C2:C7)>F1))

De oudere manier om te tellen: SOMPRODUCT met een dubbele min maakt van de WAAR-waarden enen. Het geeft 2, het aantal verschillen boven de grens, maar niet om welke debiteuren het gaat.

=SOMPRODUCT((ABS(B2:B7-C2:C7)>F1)*(B2:B7-C2:C7))
=SOMPRODUCT((ABS(B2:B7-C2:C7)>F1)*(B2:B7-C2:C7))

Het netto totaal van de verschillen boven de grens: 4,5004.500 + -2,9302.930 = 1,5701.570. Handig als controlegetal naast de lijst.

Alle combinaties