Combinatie
Facturen die in het grootboek ontbreken met FILTER, ISNB en X.VERGELIJKEN
Toon de facturen op de specificatie van de debiteuren die niet in het grootboek staan: X.VERGELIJKEN zoekt elke factuur op, ISNB maakt van elke misser WAAR en FILTER houdt die rijen over, met een melding als er niets ontbreekt.
Gebruikte functies:FILTERISNBX.VERGELIJKENExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
Uitgewerkt voorbeeld
Facturen van de debiteuren die niet in het grootboek staan
Situatie
Voor de controle van Veldhuis Installatie BV heb je de specificatie van de debiteuren per 30 september 2026 (kolom A tot en met C) en de lijst met verkoopfactuurnummers die je uit de XAF-auditfile van het grootboek hebt gehaald (kolom D).
Je wilt zien welke facturen op de specificatie geen boeking in het grootboek hebben, met klant en openstaand bedrag, zodat je de onderliggende stukken bij de klant kunt opvragen.
De gegevens
| Rij | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Factuur (spec.) | Klant | Openstaand bedrag | Factuur (grootboek) | ||||||
| 2 | 25-0412 | Hofman Techniek BV | €4,250.00€ 4.250,00 | 25-0412 | 25-0420=FILTER(A2:C8, ISNB(X.VERGELIJKEN(A2:A8, D2:D8)), "Niets ontbreekt")=FILTER(A2:C8; ISNB(X.VERGELIJKEN(A2:A8; D2:D8)); "Niets ontbreekt") | Jansen Logistiek BV | €12,600.00€ 12.600,00 | |||
| 3 | 25-0415 | De Bruin Metaal BV | €1,800.00€ 1.800,00 | 25-0415 | 25-0441 | Van Dijk Transport BV | €980.00€ 980,00 | |||
| 4 | 25-0420 | Jansen Logistiek BV | €12,600.00€ 12.600,00 | 25-0433 | ||||||
| 5 | 25-0433 | Kuiper Bouw BV | €3,375.00€ 3.375,00 | 25-0447 | ||||||
| 6 | 25-0441 | Van Dijk Transport BV | €980.00€ 980,00 | 25-0452 | ||||||
| 7 | 25-0447 | Smit Verpakking BV | €7,200.00€ 7.200,00 | 25-0460 | ||||||
| 8 | 25-0452 | Mulder Groep BV | €2,150.00€ 2.150,00 | 25-0461 | ||||||
| 9 |
Veeg opzij om alle kolommen te zien
Formule in G2
=FILTER(A2:C8,ISNB(X.VERGELIJKEN(A2:A8, D2:D8)), "Niets ontbreekt")
=FILTER(A2:C8;ISNB(X.VERGELIJKEN(A2:A8; D2:D8)); "Niets ontbreekt")
Resultaat: 25-0420 | Jansen Logistiek BV | €12,600.00 / 25-0441 | Van Dijk Transport BV | €980.0025-0420 | Jansen Logistiek BV | € 12.600,00 / 25-0441 | Van Dijk Transport BV | € 980,00
Hoe de onderdelen samenwerken
Let op: Dit vindt alleen facturen die op de specificatie staan maar niet in het grootboek. Een factuur die wel in het grootboek staat maar niet op de specificatie (hier 25-0460 en 25-0461) blijft onzichtbaar. Wil je beide kanten controleren, wissel dan de twee bereiken om en voer de formule een tweede keer uit.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=X.VERGELIJKEN(A2:A8,
D2:D8) =X.VERGELIJKEN(A2:A8;
D2:D8) X.VERGELIJKEN XMATCHGeeft de positie van een waarde in een bereik terug.Lees meer over X.VERGELIJKENDruk op Escape om te sluitenzoekwaardeDe waarde waarvan je de positie wilt weten.Kolom Factuur (spec.), 7 cellenDruk op Escape om te sluitenzoeken_matrixDe rij of kolom waarin gezocht wordt. Posities tellen vanaf de eerste cel.Kolom Factuur (grootboek), 7 cellenDruk op Escape om te sluitenZoekt elk van de zeven factuurnummers van de specificatie op in de lijst uit het grootboek. Het resultaat is één kolom: 1, 2, #N/B, 3, #N/B, 4, 5. De #N/B-waarden zijn de facturen
25-0420en25-0441, die niet in het grootboek staan.=ISNB(X.VERGELIJKEN(A2:A8,
D2:D8)) =ISNB(X.VERGELIJKEN(A2:A8;
D2:D8)) ISNB ISNAWAAR als een waarde #N/B is.Druk op Escape om te sluitenX.VERGELIJKEN XMATCHGeeft de positie van een waarde in een bereik terug.Hier gebruikt als: waardeDe waarde of cel die wordt getest.Lees meer over X.VERGELIJKENDruk op Escape om te sluitenzoekwaardeDe waarde waarvan je de positie wilt weten.Kolom Factuur (spec.), 7 cellenDruk op Escape om te sluitenzoeken_matrixDe rij of kolom waarin gezocht wordt. Posities tellen vanaf de eerste cel.Kolom Factuur (grootboek), 7 cellenDruk op Escape om te sluitenISNB maakt van de posities WAAR bij elke #N/B en ONWAAR bij elke positie: ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR, ONWAAR. Dit is de lijst met voorwaarden voor FILTER.
=FILTER(A2:C8,
ISNB(X.VERGELIJKEN(A2:A8, D2:D8))) =FILTER(A2:C8;
ISNB(X.VERGELIJKEN(A2:A8; D2:D8))) FILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.7 rijen bij 3 kolommenDruk op Escape om te sluitenISNB ISNAWAAR als een waarde #N/B is.Hier gebruikt als: opnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Druk op Escape om te sluitenX.VERGELIJKEN XMATCHGeeft de positie van een waarde in een bereik terug.Hier gebruikt als: waardeDe waarde of cel die wordt getest.Lees meer over X.VERGELIJKENDruk op Escape om te sluitenzoekwaardeDe waarde waarvan je de positie wilt weten.Kolom Factuur (spec.), 7 cellenDruk op Escape om te sluitenzoeken_matrixDe rij of kolom waarin gezocht wordt. Posities tellen vanaf de eerste cel.Kolom Factuur (grootboek), 7 cellenDruk op Escape om te sluitenFILTER houdt de rijen van de specificatie over waar de lijst met voorwaarden WAAR is: rij 3 en rij 5. Je krijgt twee rijen van drie kolommen:
25-0420Jansen Logistiek 12.600 en25-0441Van Dijk Transport 980. Was geen enkele rij WAAR, dan toont deze versie #BEREKENEN!.=FILTER(A2:C8,
ISNB(X.VERGELIJKEN(A2:A8, D2:D8)), "Niets ontbreekt") =FILTER(A2:C8;
ISNB(X.VERGELIJKEN(A2:A8; D2:D8)); "Niets ontbreekt") FILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.7 rijen bij 3 kolommenDruk op Escape om te sluitenISNB ISNAWAAR als een waarde #N/B is.Hier gebruikt als: opnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Druk op Escape om te sluitenX.VERGELIJKEN XMATCHGeeft de positie van een waarde in een bereik terug.Hier gebruikt als: waardeDe waarde of cel die wordt getest.Lees meer over X.VERGELIJKENDruk op Escape om te sluitenzoekwaardeDe waarde waarvan je de positie wilt weten.Kolom Factuur (spec.), 7 cellenDruk op Escape om te sluitenzoeken_matrixDe rij of kolom waarin gezocht wordt. Posities tellen vanaf de eerste cel.Kolom Factuur (grootboek), 7 cellenDruk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluitenHet derde argument van FILTER is de tekst die verschijnt als geen enkele rij voldoet. Hier voldoen twee rijen, dus het resultaat zijn dezelfde twee rijen, samen 12.600 + 980 = 13.580. Stond elke factuur in het grootboek, dan toont de cel de melding in plaats van een foutwaarde.
Stap voor stap
X.VERGELIJKEN(A2:A8; D2:D8) zoekt elke factuur van de specificatie op in de grootboeklijst:
25-0412staat op plek 125-0415staat op plek 225-0420is niet gevonden (#N/B)25-0433staat op plek 325-0441is niet gevonden (#N/B)25-0447staat op plek 425-0452staat op plek 5
- ISNB maakt daarvan ONWAAR, ONWAAR, WAAR, ONWAAR, WAAR, ONWAAR, ONWAAR: alleen WAAR voor de twee facturen die niet gevonden zijn.
- FILTER loopt met die lijst door
A2:C8en houdt rij 3 (25-0420, Jansen Logistiek BV, 12,60012.600) en rij 5 (25-0441, Van Dijk Transport BV, 980) over. - Er voldoen twee rijen, dus het derde argument (de melding) wordt niet gebruikt. Het resultaat loopt over
G2:I3. Samen zijn de twee facturen 12,60012.600 + 980 = 13,58013.580 aan openstaande debiteuren zonder boeking in het grootboek.
Waarom het werkt
X.VERGELIJKEN geeft een positie voor een factuur die bestaat en #N/B voor een factuur die niet bestaat, en ISNB maakt precies dat verschil WAAR of ONWAAR.
FILTER heeft per rij van de specificatie één WAAR of ONWAAR nodig, en X.VERGELIJKEN geeft één antwoord voor elke waarde in A2:A8, ongeacht de lengte van de grootboeklijst. Het derde argument vangt het nette geval op waarin het grootboek compleet is.
Let op
- Factuurnummers met een spatie erachter, of in de ene lijst als getal en in de andere als tekst, komen niet overeen. X.VERGELIJKEN meldt ze dan als ontbrekend terwijl ze er wel zijn. Controleer de eerste paar treffers met de hand voordat je de klant om stukken vraagt.
- De cellen naast en onder
G2moeten leeg zijn. Staat er iets in de weg van het overloopbereik, dan toont Excel #OVERLOPEN! in plaats van de lijst.
Varianten
=FILTER(A2:C8,AANTAL.ALS(D2:D8, A2:A8)=0, "Niets ontbreekt")
=FILTER(A2:C8;AANTAL.ALS(D2:D8; A2:A8)=0; "Niets ontbreekt")
De oudere aanpak: AANTAL.ALS telt hoe vaak elke factuur in de grootboeklijst voorkomt en FILTER houdt de nullen over. Dezelfde twee rijen. Ook deze variant heeft FILTER nodig en werkt dus in dezelfde Excel-versies.
=FILTER(A2:C8,ISNB(VERGELIJKEN(A2:A8, D2:D8, 0)), "Niets ontbreekt")
=FILTER(A2:C8;ISNB(VERGELIJKEN(A2:A8; D2:D8; 0)); "Niets ontbreekt")
VERGELIJKEN met 0 als derde argument in plaats van X.VERGELIJKEN. Het resultaat is hetzelfde: twee rijen. X.VERGELIJKEN is korter omdat een exacte overeenkomst de standaard is.
Verwante combinaties
Gebruikte functies
- 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.
- X.VERGELIJKENZoekt een waarde in een rij of kolom en vertelt met een getal op welke positie die staat, eventueel bij benadering, met jokertekens, met een reguliere expressie of vanaf het eind gezocht.
Gebruikte functies
- 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.
- X.VERGELIJKENZoekt een waarde in een rij of kolom en vertelt met een getal op welke positie die staat, eventueel bij benadering, met jokertekens, met een reguliere expressie of vanaf het eind gezocht.