Combinatie

Mini-saldibalans uit journaalregels met HOR.STAPELEN, UNIEK en SOMMEN.ALS

Maak uit ruwe journaalregels een lijst met rekeningen en hun saldo aan boekingen: UNIEK geeft de rekeningen, SOMMEN.ALS telt elke rekening op, HOR.STAPELEN zet beide naast elkaar en SORTEREN zet ze op volgorde.

Gebruikte functies:HOR.STAPELENUNIEKSOMMEN.ALSSORTERENExcel 2024 of nieuwer

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Saldo aan boekingen per rekening uit de journaalregels

Situatie

Voor de controle van Veldhuis Installatie BV heb je vier boekstukken uit de XAF-auditfile geëxporteerd, één rij per regel, met debet als positief en credit als negatief bedrag. Je wilt per grootboekrekening een mini-saldibalans om te vergelijken met de saldibalans van de cliënt, zonder een draaitabel te bouwen.

De gegevens

ABCDEFGH
1BoekstukRekeningOmschrijvingBedrag
225-060145004500Huur oktober€3,200.00€ 3.200,0011001100=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9), SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9))))=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9); SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9))))€1,850.00€ 1.850,00
325-060116001600Huur oktober-€3,200.00€ -3.200,0013001300-€8,400.00€ -8.400,00
425-060211001100Ontvangst klant€8,400.00€ 8.400,0016001600-€3,200.00€ -3.200,00
525-060213001300Ontvangst klant-€8,400.00€ -8.400,0040004000€5,100.00€ 5.100,00
625-060340004000Salarisrun€5,100.00€ 5.100,0045004500€4,650.00€ 4.650,00
725-060311001100Salarisrun-€5,100.00€ -5.100,00
825-060445004500Nutsvoorzieningen€1,450.00€ 1.450,00
925-060411001100Nutsvoorzieningen-€1,450.00€ -1.450,00
10
Formulecel (F2)Overloopresultaat

Formule in F2

=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9), SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9))))
=SORTEREN(HOR.STAPELEN(UNIEK(B2:B9); SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9))))

Resultaat: 1100 | €1,850.00 / 1300 | -€8,400.00 / 1600 | -€3,200.00 / 4000 | €5,100.00 / 4500 | €4,650.001100 | € 1.850,00 / 1300 | € -8.400,00 / 1600 | € -3.200,00 / 4000 | € 5.100,00 / 4500 | € 4.650,00

Hoe de onderdelen samenwerken

Let op: Staan sommige rekeningen als tekst ("1100") en andere als getal, dan toont UNIEK dezelfde rekening twee keer en telt SOMMEN.ALS beide schrijfwijzen bij allebei mee. Zet de kolom met rekeningen eerst om naar één type.

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. =UNIEK(B2:B9)
    =UNIEK(B2:B9)

    Zet elke rekening één keer onder elkaar, in volgorde van eerste voorkomen: 4500, 1600, 1100, 1300, 4000. De acht journaalregels bevatten vijf verschillende rekeningen.

  2. =SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9))
    =SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9))

    Omdat het criterium een lijst van vijf rekeningen is, geeft SOMMEN.ALS vijf totalen terug, één per rekening:

    • 4500 geeft 3.200 + 1.450 = 4.650
    • 1600 geeft -3.200
    • 1100 geeft 8.400 - 5.100 - 1.450 = 1.850
    • 1300 geeft -8.400
    • 4000 geeft 5.100
  3. =HOR.STAPELEN(UNIEK(B2:B9), SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9)))
    =HOR.STAPELEN(UNIEK(B2:B9); SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9)))

    Zet de kolom met rekeningen en de kolom met totalen naast elkaar: een tabel van 5 rijen en 2 kolommen, nog in volgorde van eerste voorkomen (4500 eerst, 4000 laatst).

  4. =SORTEREN(HOR.STAPELEN(UNIEK(B2:B9), SOMMEN.ALS(D2:D9, B2:B9, UNIEK(B2:B9))))
    =SORTEREN(HOR.STAPELEN(UNIEK(B2:B9); SOMMEN.ALS(D2:D9; B2:B9; UNIEK(B2:B9))))

    SORTEREN zet de tabel op volgorde van de eerste kolom, dus de rekeningen lopen van 1100 tot 4500 zoals in een saldibalans:

    • 1100 = 1.850
    • 1300 = -8.400
    • 1600 = -3.200
    • 4000 = 5.100
    • 4500 = 4.650

    De vijf totalen tellen op tot 0, zoals het hoort als elk boekstuk sluit.

Stap voor stap

  1. UNIEK(B2:B9) leest de rekeningen 4500, 1600, 1100, 1300, 4000, 1100, 4500, 1100 en houdt elke rekening één keer: 4500, 1600, 1100, 1300, 4000.

  2. SOMMEN.ALS draait één keer per rekening in die lijst:

    • 4500: 3,2003.200 + 1,4501.450 = 4,6504.650
    • 1600: -3,2003.200
    • 1100: 8,4008.400 - 5,1005.100 - 1,4501.450 = 1,8501.850
    • 1300: -8,4008.400
    • 4000: 5,1005.100
  3. HOR.STAPELEN zet de rekeningen naast de totalen: 4500 | 4,6504.650, 1600 | -3,2003.200, 1100 | 1,8501.850, 1300 | -8,4008.400, 4000 | 5,1005.100.
  4. SORTEREN zet de rijen oplopend op rekeningnummer: 1100, 1300, 1600, 4000, 4500, elk met zijn totaal.
  5. Het resultaat loopt vanaf F2 door over F2:G6. Controle: 1,8501.850 - 8,4008.400 - 3,2003.200 + 5,1005.100 + 4,6504.650 = 0, dus de journaalregels sluiten.

Waarom het werkt

UNIEK geeft de lijst met rekeningen één keer, en dezelfde lijst dient als criterium van SOMMEN.ALS. Een criterium dat een lijst is, laat SOMMEN.ALS per item één totaal teruggeven, in dezelfde volgorde, zodat rekeningen en totalen rij voor rij aansluiten.

HOR.STAPELEN voegt ze samen tot een tabel en SORTEREN zet die in de volgorde van een saldibalans. Verandert het journaal, dan wordt de tabel vanzelf groter of kleiner.

Let op

  • Het overloopbereik F2:G6 moet leeg zijn. Staat er iets in die cellen, dan krijg je de fout #OVERLOPEN!.
  • De formule dekt alleen de regels in B2:D9. Wordt de export langer, breid de bereiken dan uit of gebruik een tabel, anders ontbreken regels in je totalen.

Varianten

=SOMMEN.ALS(D2:D9, B2:B9, 1100)
=SOMMEN.ALS(D2:D9; B2:B9; 1100)

Eén rekening tegelijk: 8,4008.400 - 5,1005.100 - 1,4501.450 = 1,8501.850. Prima voor één rekening, maar je typt elke rekening zelf en kunt er dus een missen.

=HOR.STAPELEN(SORTEREN(UNIEK(B2:B9)), SOMMEN.ALS(D2:D9, B2:B9, SORTEREN(UNIEK(B2:B9))))
=HOR.STAPELEN(SORTEREN(UNIEK(B2:B9)); SOMMEN.ALS(D2:D9; B2:B9; SORTEREN(UNIEK(B2:B9))))

Sorteert eerst de rekeningen en telt ze daarna in die volgorde op. Dezelfde tabel, maar de lijst met rekeningen staat er twee keer in en je kunt alleen op rekening sorteren.

=SOM.ALS(B2:B9, 4500, D2:D9)
=SOM.ALS(B2:B9; 4500; D2:D9)

De oudere SOM.ALS voor één rekening: 3,2003.200 + 1,4501.450 = 4,6504.650. Werkt in elke Excel-versie, maar geeft één getal per formule en geen lijst met rekeningen.

Alle combinaties