Combinatie
Boekstukken waarbij debet niet gelijk is aan credit
Toon de boekstukken waarvan het debet- en creditbedrag verschillen: je pakt de unieke boekstuknummers, telt debet en credit per boekstuk op met SOMMEN.ALS en houdt met FILTER over HOR.STAPELEN de ongelijke over, alles benoemd in één LET.
Gebruikte functies:LETUNIEKSOMMEN.ALSFILTERHOR.STAPELENExcel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
Moeilijkheidsgraad 4 van 5
Uitgewerkt voorbeeld
Boekstukken die niet sluiten
Situatie
Voor Veldhuis Installatie BV heb je de journaalregels van het eerste kwartaal van 2025 uit de XAF-auditfile ontvangen. Voordat je op het grootboek vertrouwt, controleer je dat debet gelijk is aan credit per boekstuk. Je wilt één formule die elk boekstuk toont dat niet sluit, met debet, credit en verschil.
De gegevens
| Rij | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Boekstuk | Rekening | Debet | Credit | Boekstuk | 25-0415 | ||||||
| 2 | 25-0412 | 13001300 | €12,100.00€ 12.100,00 | €0.00€ 0,00 | 25-0415=LET(boeking, UNIEK(A2:A9), debet, SOMMEN.ALS(C2:C9, A2:A9, boeking), credit, SOMMEN.ALS(D2:D9, A2:A9, boeking), FILTER(HOR.STAPELEN(boeking, debet, credit, debet-credit), debet<>credit, "Alle boekstukken sluiten"))=LET(boeking; UNIEK(A2:A9); debet; SOMMEN.ALS(C2:C9; A2:A9; boeking); credit; SOMMEN.ALS(D2:D9; A2:A9; boeking); FILTER(HOR.STAPELEN(boeking; debet; credit; debet-credit); debet<>credit; "Alle boekstukken sluiten")) | €3,500.00€ 3.500,00 | €3,050.00€ 3.050,00 | €450.00€ 450,00 | ||||
| 3 | 25-0412 | 80008000 | €0.00€ 0,00 | €10,000.00€ 10.000,00 | 25-0431 | €5,200.00€ 5.200,00 | €0.00€ 0,00 | €5,200.00€ 5.200,00 | ||||
| 4 | 25-0412 | 18001800 | €0.00€ 0,00 | €2,100.00€ 2.100,00 | ||||||||
| 5 | 25-0415 | 45004500 | €3,500.00€ 3.500,00 | €0.00€ 0,00 | ||||||||
| 6 | 25-0415 | 16001600 | €0.00€ 0,00 | €3,050.00€ 3.050,00 | ||||||||
| 7 | 25-0420 | 11001100 | €8,400.00€ 8.400,00 | €0.00€ 0,00 | ||||||||
| 8 | 25-0420 | 13001300 | €0.00€ 0,00 | €8,400.00€ 8.400,00 | ||||||||
| 9 | 25-0431 | 40004000 | €5,200.00€ 5.200,00 | €0.00€ 0,00 | ||||||||
| 10 |
Veeg opzij om alle kolommen te zien
Formule in F2
=LET(boeking,UNIEK(A2:A9), debet, SOMMEN.ALS(C2:C9, A2:A9, boeking), credit, SOMMEN.ALS(D2:D9, A2:A9, boeking), FILTER(HOR.STAPELEN(boeking, debet, credit, debet-credit), debet<>credit, "Alle boekstukken sluiten"))
=LET(boeking;UNIEK(A2:A9); debet; SOMMEN.ALS(C2:C9; A2:A9; boeking); credit; SOMMEN.ALS(D2:D9; A2:A9; boeking); FILTER(HOR.STAPELEN(boeking; debet; credit; debet-credit); debet<>credit; "Alle boekstukken sluiten"))
LETGeeft tussenwaarden een naam zodat een formule leesbaar wordt en elk stuk één keer rekent.Lees meer over LETDruk op Escape om te sluiten
naam1De naam voor de eerste waarde. Moet met een letter beginnen.Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: naam_waarde1De waarde of expressie waar naam1 voor staat.Lees meer over UNIEKDruk op Escape om te sluiten
matrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
calculatie_of_naam2De uiteindelijke berekening, of de volgende naam die je definieert.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde2De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluiten
optelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Debet, 8 cellenDruk op Escape om te sluiten
criteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitencalculatie_of_naam3 (optioneel)De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde3De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluiten
optelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Credit, 8 cellenDruk op Escape om te sluiten
criteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenFILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: calculatie_of_naam4De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Lees meer over FILTERDruk op Escape om te sluiten
HOR.STAPELEN HSTACKZet bereiken naast elkaar.Hier gebruikt als: matrixDe rijen die je terug wilt: één kolom of een hele tabel.Lees meer over HOR.STAPELENDruk op Escape om te sluiten
matrix1Het eerste bereik of de eerste matrix; die komt in de meest linkse kolommen.Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenmatrix2 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenmatrix3 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenmatrix4 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluiten
creditVariabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten<>Niet gelijk aanDruk op Escape om te sluiten
creditVariabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluiten
Resultaat: 25-0415 | €3,500.00 | €3,050.00 | €450.00 / 25-0431 | €5,200.00 | €0.00 | €5,200.0025-0415 | € 3.500,00 | € 3.050,00 | € 450,00 / 25-0431 | € 5.200,00 | € 0,00 | € 5.200,00
Hoe de onderdelen samenwerken
Let op: Bedragen met decimalen kunnen door rekenafronding een fractie van een cent verschillen, waardoor debet<>credit een boekstuk markeert dat in de praktijk sluit. Toets in plaats daarvan het afgeronde verschil (zie de variatie met AFRONDEN).
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=UNIEK(A2:A9)
=UNIEK(A2:A9)
matrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenDe boekstuknummers elk één keer, in volgorde van eerste voorkomen:
25-0412,25-0415,25-0420,25-0431. Acht journaalregels worden vier boekstukken.=SOMMEN.ALS(C2:C9,
A2:A9, UNIEK(A2:A9)) =SOMMEN.ALS(C2:C9;
A2:A9; UNIEK(A2:A9)) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Debet, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Lees meer over UNIEKDruk op Escape om te sluitenmatrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenHet totaal debet per boekstuk. Omdat het criterium een kolom van vier boekstuknummers is, geeft SOMMEN.ALS vier totalen: 12.100, 3.500, 8.400 en 5.200.
=SOMMEN.ALS(D2:D9,
A2:A9, UNIEK(A2:A9)) =SOMMEN.ALS(D2:D9;
A2:A9; UNIEK(A2:A9)) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Credit, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Lees meer over UNIEKDruk op Escape om te sluitenmatrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenHet totaal credit per boekstuk in dezelfde volgorde: 10.000 + 2.100 = 12.100, daarna 3.050, 8.400 en 0.
=SOMMEN.ALS(C2:C9,
A2:A9, UNIEK(A2:A9)) - SOMMEN.ALS(D2:D9, A2:A9, UNIEK(A2:A9)) =SOMMEN.ALS(C2:C9;
A2:A9; UNIEK(A2:A9)) - SOMMEN.ALS(D2:D9; A2:A9; UNIEK(A2:A9)) SOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Debet, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Lees meer over UNIEKDruk op Escape om te sluitenmatrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten-Trekt afDruk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Credit, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Lees meer over UNIEKDruk op Escape om te sluitenmatrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitenDebet min credit per boekstuk: 0, 450, 0 en 5.200. Elke waarde anders dan 0 is een boekstuk dat niet sluit.
=LET(boeking,
UNIEK(A2:A9), debet, SOMMEN.ALS(C2:C9, A2:A9, boeking), credit, SOMMEN.ALS(D2:D9, A2:A9, boeking), FILTER(HOR.STAPELEN(boeking, debet, credit, debet-credit), debet<>credit, "Alle boekstukken sluiten")) =LET(boeking;
UNIEK(A2:A9); debet; SOMMEN.ALS(C2:C9; A2:A9; boeking); credit; SOMMEN.ALS(D2:D9; A2:A9; boeking); FILTER(HOR.STAPELEN(boeking; debet; credit; debet-credit); debet<>credit; "Alle boekstukken sluiten")) LETGeeft tussenwaarden een naam zodat een formule leesbaar wordt en elk stuk één keer rekent.Lees meer over LETDruk op Escape om te sluitennaam1De naam voor de eerste waarde. Moet met een letter beginnen.Variabele die in deze LET is gedefinieerdGekoppeld aan:UNIEK(A2:A9)Druk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: naam_waarde1De waarde of expressie waar naam1 voor staat.Lees meer over UNIEKDruk op Escape om te sluitenmatrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitencalculatie_of_naam2De uiteindelijke berekening, of de volgende naam die je definieert.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde2De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Debet, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:UNIEK(A2:A9)Druk op Escape om te sluitencalculatie_of_naam3 (optioneel)De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde3De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluitenoptelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Credit, 8 cellenDruk op Escape om te sluitencriteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluitencriterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:UNIEK(A2:A9)Druk op Escape om te sluitenFILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: calculatie_of_naam4De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Lees meer over FILTERDruk op Escape om te sluitenHOR.STAPELEN HSTACKZet bereiken naast elkaar.Hier gebruikt als: matrixDe rijen die je terug wilt: één kolom of een hele tabel.Lees meer over HOR.STAPELENDruk op Escape om te sluitenmatrix1Het eerste bereik of de eerste matrix; die komt in de meest linkse kolommen.Variabele die in deze LET is gedefinieerdGekoppeld aan:UNIEK(A2:A9)Druk op Escape om te sluitenmatrix2 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenmatrix3 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenmatrix4 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluitencreditVariabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Variabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten<>Niet gelijk aanDruk op Escape om te sluitencreditVariabele die in deze LET is gedefinieerdGekoppeld aan:SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluitenLET geeft de boekstukken (boeking), de debettotalen (debet) en de credittotalen (credit) één keer een naam.
HOR.STAPELEN zet boekstuk, debet, credit en verschil naast elkaar in een raster van 4 bij 4, en FILTER houdt de rijen over waar debet<>credit: boekstuk
25-0415en25-0431. Sluiten alle boekstukken, dan verschijnt in plaats daarvan de melding.
Stap voor stap
- boeking = UNIEK(A2:A9) geeft de vier boekstukken
25-0412,25-0415,25-0420en25-0431. debet = SOMMEN.ALS(C2:C9; A2:A9; boeking) telt het debet per boekstuk op:
25-0412: 12,10012.10025-0415: 3,5003.50025-0420: 8,4008.40025-0431: 5,2005.200
credit = SOMMEN.ALS(D2:D9; A2:A9; boeking) telt het credit per boekstuk op:
25-0412: 10,00010.000 + 2,1002.100 = 12,10012.10025-0415: 3,0503.05025-0420: 8,4008.40025-0431: 0 (dat boekstuk heeft geen creditregel)
- debet-credit geeft de verschillen: 12,10012.100 - 12,10012.100 = 0, 3,5003.500 - 3,0503.050 = 450, 8,4008.400 - 8,4008.400 = 0 en 5,2005.200 - 0 = 5,2005.200.
- HOR.STAPELEN(boeking; debet; credit; debet-credit) zet de vier kolommen naast elkaar: een raster van 4 rijen en 4 kolommen, één rij per boekstuk.
- debet<>credit is ONWAAR, WAAR, ONWAAR, WAAR, dus FILTER houdt rij 2 en 4 over:
25-0415(3,5003.500 / 3,0503.050 / 450) en25-0431(5,2005.200 / 0 / 5,2005.200). Het resultaat vultF2:I3.
Waarom het werkt
SOMMEN.ALS accepteert een hele kolom met criteria en geeft per criterium één totaal, dus UNIEK en SOMMEN.ALS samen geven per boekstuk één debet- en één credittotaal zonder hulpkolommen.
LET geeft die drie resultaten één keer een naam, zodat debet en credit twee keer worden gebruikt (in het raster en in de toets) zonder twee keer te rekenen. FILTER kan daarna debet<>credit toetsen op de rijen van het HOR.STAPELEN-raster, omdat beide één rij per boekstuk hebben.
Let op
- Een boekstuk waarvan een regel ontbreekt, zoals hier
25-0431, krijgt credit 0 en wordt gevonden. Maar een boekstuk met gelijk debet en credit op de verkeerde rekeningen sluit wel en wordt niet gemarkeerd: deze toets controleert de totalen, niet de rekeningen. - Heeft het boekstuknummer in sommige regels een spatie aan het eind of een afwijkende notatie (bijvoorbeeld
"25-0412 "naast"25-0412"), dan toont UNIEK het boekstuk twee keer, en telt SOMMEN.ALS alleen de regels die bij elke exacte schrijfwijze passen, waardoor elke helft onevenwichtig lijkt. Maak kolomAeerst schoon met SPATIES.WISSEN.
Varianten
=LET(boeking,UNIEK(A2:A9), debet, SOMMEN.ALS(C2:C9, A2:A9, boeking), credit, SOMMEN.ALS(D2:D9, A2:A9, boeking), FILTER(HOR.STAPELEN(boeking, debet, credit, debet-credit), AFRONDEN(debet-credit, 2)<>0, "Alle boekstukken sluiten"))
=LET(boeking;UNIEK(A2:A9); debet; SOMMEN.ALS(C2:C9; A2:A9; boeking); credit; SOMMEN.ALS(D2:D9; A2:A9; boeking); FILTER(HOR.STAPELEN(boeking; debet; credit; debet-credit); AFRONDEN(debet-credit; 2)<>0; "Alle boekstukken sluiten"))
LETGeeft tussenwaarden een naam zodat een formule leesbaar wordt en elk stuk één keer rekent.Lees meer over LETDruk op Escape om te sluiten
naam1De naam voor de eerste waarde. Moet met een letter beginnen.Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenUNIEK UNIQUEGeeft de unieke waarden van een bereik.Hier gebruikt als: naam_waarde1De waarde of expressie waar naam1 voor staat.Lees meer over UNIEKDruk op Escape om te sluiten
matrixDe waarden waaruit dubbele weg moeten; bij meerdere kolommen telt de hele rij.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
calculatie_of_naam2De uiteindelijke berekening, of de volgende naam die je definieert.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde2De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluiten
optelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Debet, 8 cellenDruk op Escape om te sluiten
criteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitencalculatie_of_naam3 (optioneel)De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenSOMMEN.ALS SUMIFSTelt de getallen op die aan alle opgegeven voorwaarden voldoen.Hier gebruikt als: naam_waarde3De waarde die aan de vorige naam wordt gekoppeld.Lees meer over SOMMEN.ALSDruk op Escape om te sluiten
optelbereikDe cellen met de getallen die worden opgeteld als een rij door alle tests komt.Kolom Credit, 8 cellenDruk op Escape om te sluiten
criteriumbereik1Het bereik waarop je de eerste voorwaarde toetst; even groot als optelbereik.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criterium1De test: een waarde, een cel, of tekst zoals ">=100" of "MEM-*".Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenFILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: calculatie_of_naam4De uiteindelijke berekening, of nog een naam; paren mogen doorgaan.Lees meer over FILTERDruk op Escape om te sluiten
HOR.STAPELEN HSTACKZet bereiken naast elkaar.Hier gebruikt als: matrixDe rijen die je terug wilt: één kolom of een hele tabel.Lees meer over HOR.STAPELENDruk op Escape om te sluiten
matrix1Het eerste bereik of de eerste matrix; die komt in de meest linkse kolommen.Variabele die in deze LET is gedefinieerdGekoppeld aan:
UNIEK(A2:A9)Druk op Escape om te sluitenmatrix2 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluitenmatrix3 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenmatrix4 (optioneel)Nog een bereik of matrix dat rechts komt; voeg er zoveel toe als je nodig hebt.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluiten
creditVariabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenAFRONDEN ROUNDRondt af op een aantal decimalen.Hier gebruikt als: opnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Lees meer over AFRONDENDruk op Escape om te sluiten
getalDe waarde die je afrondt; een getal of een cel met een getal.Variabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(C2:C9, A2:A9, boeking)SOMMEN.ALS(C2:C9; A2:A9; boeking)Druk op Escape om te sluiten-Trekt afDruk op Escape om te sluiten
creditVariabele die in deze LET is gedefinieerdGekoppeld aan:
SOMMEN.ALS(D2:D9, A2:A9, boeking)SOMMEN.ALS(D2:D9; A2:A9; boeking)Druk op Escape om te sluitenaantal_decimalenAantal decimalen dat blijft: 2 voor centen, 0 voor hele getallen, -3 voor duizendtallen.2: voor centen, 0 voor hele getallen, -3 voor duizendtallenDruk op Escape om te sluiten
<>Niet gelijk aanDruk op Escape om te sluiten
GetalVaste waardeDruk op Escape om te sluiten
als_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluiten
Toetst het verschil afgerond op twee decimalen, zodat kleine rekenafrondingen worden genegeerd. Hier dezelfde twee boekstukken.
=SOM.ALS(A2:A9,K1, C2:C9) - SOM.ALS(A2:A9, K1, D2:D9)
=SOM.ALS(A2:A9;K1; C2:C9) - SOM.ALS(A2:A9; K1; D2:D9)
SOM.ALS SUMIFTelt de getallen op die aan één voorwaarde voldoen.Lees meer over SOM.ALSDruk op Escape om te sluiten
bereikDe cellen waarop de voorwaarde wordt getest.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criteriumDe voorwaarde: een waarde, een cel, of tekst zoals ">100" of "MEM-*".Invoercel: 25-0415Druk op Escape om te sluiten
optelbereik (optioneel)De cellen die worden opgeteld; weggelaten betekent het bereik zelf.Kolom Debet, 8 cellenDruk op Escape om te sluiten
-Trekt afDruk op Escape om te sluiten
SOM.ALS SUMIFTelt de getallen op die aan één voorwaarde voldoen.Lees meer over SOM.ALSDruk op Escape om te sluiten
bereikDe cellen waarop de voorwaarde wordt getest.Kolom Boekstuk, 8 cellenDruk op Escape om te sluiten
criteriumDe voorwaarde: een waarde, een cel, of tekst zoals ">100" of "MEM-*".Invoercel: 25-0415Druk op Escape om te sluiten
optelbereik (optioneel)De cellen die worden opgeteld; weggelaten betekent het bereik zelf.Kolom Credit, 8 cellenDruk op Escape om te sluiten
De oudere manier, één boekstuk tegelijk: typ een boekstuknummer in K1 en je krijgt het verschil (450 voor 25-0415). Het werkt in elke Excel-versie, maar je moet het voor elk boekstuk herhalen.
Verwante combinaties
Gebruikte functies
- LETGeeft een waarde of tussenresultaat binnen één formule een naam, zodat je het op naam hergebruikt en Excel het maar één keer berekent.
- UNIEKZet elke waarde uit een bereik één keer onder elkaar, in de volgorde waarin ze voor het eerst voorkomt, als overlopende lijst die de bron volgt.
- SOMMEN.ALSTelt de getallen in één bereik op, maar alleen in de rijen die voldoen aan alle voorwaarden die je op andere bereiken stelt.
- FILTERHaalt alle rijen uit een bereik die aan je voorwaarde voldoen, als lijst die naar de cellen eronder overloopt en zich aanpast zodra de gegevens veranderen.
- HOR.STAPELENZet bereiken of matrices van links naar rechts naast elkaar tot één overlopend blok dat meebeweegt met de bronnen.
Gebruikte functies
- LETGeeft een waarde of tussenresultaat binnen één formule een naam, zodat je het op naam hergebruikt en Excel het maar één keer berekent.
- UNIEKZet elke waarde uit een bereik één keer onder elkaar, in de volgorde waarin ze voor het eerst voorkomt, als overlopende lijst die de bron volgt.
- SOMMEN.ALSTelt de getallen in één bereik op, maar alleen in de rijen die voldoen aan alle voorwaarden die je op andere bereiken stelt.
- FILTERHaalt alle rijen uit een bereik die aan je voorwaarde voldoen, als lijst die naar de cellen eronder overloopt en zich aanpast zodra de gegevens veranderen.
- HOR.STAPELENZet bereiken of matrices van links naar rechts naast elkaar tot één overlopend blok dat meebeweegt met de bronnen.