Combinatie

Hiatentest op factuurnummers met FILTER, REEKS en X.VERGELIJKEN

Vind de ontbrekende nummers in een reeks verkoopfacturen: REEKS maakt elk nummer van het laagste tot het hoogste, X.VERGELIJKEN zoekt ze op in het grootboek, ISNB markeert de missers en FILTER toont ze, met een melding als er geen hiaten zijn.

Gebruikte functies:FILTER REEKS X.VERGELIJKENISNBExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)

Moeilijkheidsgraad

Uitgewerkt voorbeeld

Ontbrekende nummers in de reeks verkoopfacturen

Situatie

Voor de volledigheid van de omzet bij Veldhuis Installatie BV heb je de factuurnummers van het verkoopdagboek (VRK) uit de XAF-auditfile, in de volgorde waarin ze geboekt zijn.

Verkoopfacturen worden doorlopend genummerd in één reeks, dus een ontbrekend nummer kan duiden op een factuur die nergens is geboekt, of die zonder spoor is verwijderd of geannuleerd. Je wilt de lijst met ontbrekende nummers, om de klant om uitleg en onderliggende stukken te vragen.

De gegevens

Fragment van het werkblad: in C2 staat de formule
Rij	A	C
1	Factuurnummer (VRK)
2	2540125401	2540425404=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "Geen hiaten")=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "Geen hiaten")
3	2540225402	2540725407
4	2540325403	2540825408
5	2540525405
6	2540625406
7	2540925409
8	2540925409
9	2541025410
10

Formulecel (C2)Overloopresultaat

Formule in C2

=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "Geen hiaten")

=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "Geen hiaten")

Resultaat: 25404 / 25407 / 2540825404 / 25407 / 25408

Hoe de onderdelen samenwerken

Let op: De reeks loopt van het laagste tot het hoogste nummer in de gegevens, dus een ontbrekende eerste of laatste factuur van de periode is niet te zien. Vergelijk het laagste en hoogste nummer met het eerste en laatste nummer van de periode volgens de klant, of typ het verwachte begin en einde in plaats van MIN en MAX.

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

```
=MAX(A2:A9)-MIN(A2:A9)+1
```
```
=MAX(A2:A9)-MIN(A2:A9)+1
```
De lengte van de reeks die de nummers zouden moeten vormen: het hoogste nummer min het laagste, plus 1 omdat beide uiteinden meetellen. 25410 - 25401 + 1 = 10 nummers.
```
=REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9))
```
```
=REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9))
```
REEKS maakt de complete reeks in één kolom: 10 rijen, 1 kolom, beginnend bij het laagste nummer. Het resultaat is 25401, 25402, 25403, tot en met 25410. Dit is de lijst met nummers die zouden moeten bestaan.
```
=X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)
```
```
=X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)
```
X.VERGELIJKEN zoekt elk van de tien nummers op in de lijst uit het grootboek. Het resultaat is één kolom: 1, 2, 3, #N/B, 4, 5, #N/B, #N/B, 6, 8. De #N/B-waarden zijn 25404, 25407 en 25408. Nummer 25409 wordt op de eerste plek gevonden, 6, ook al staat het er twee keer in.
```
=ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9))
```
```
=ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9))
```
ISNB maakt van elke #N/B WAAR en van elke positie ONWAAR: ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR. Dit is de lijst met voorwaarden voor FILTER, één antwoord per nummer van de reeks.
```
=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), A2:A9)), "Geen hiaten")
```
```
=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); ISNB(X.VERGELIJKEN(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); A2:A9)); "Geen hiaten")
```
FILTER houdt de nummers van de reeks over waar de lijst met voorwaarden WAAR is: plek 4, 7 en 8, dus 25404, 25407 en 25408. Het derde argument is de tekst die verschijnt als niets voldoet; hier voldoen drie nummers, dus je ziet de drie hiaten.

Stap voor stap

MAX(A2:A9) is 25410 en MIN(A2:A9) is 25401, dus de reeks telt 25410 - 25401 + 1 = 10 nummers.
REEKS(10; 1; 25401) maakt de kolom 25401, 25402, 25403, 25404, 25405, 25406, 25407, 25408, 25409, 25410.
X.VERGELIJKEN zoekt die tien nummers op in A2:A9: 25401 op plek 1, 25402 op 2, 25403 op 3, 25404 niet gevonden, 25405 op 4, 25406 op 5, 25407 niet gevonden, 25408 niet gevonden, 25409 op 6 (eerste treffer), 25410 op 8.
ISNB maakt daarvan ONWAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, WAAR, ONWAAR, ONWAAR: alleen WAAR voor 25404, 25407 en 25408.
FILTER houdt die drie nummers uit de reeks over. Er voldoen er drie, dus de melding wordt niet gebruikt. Het resultaat loopt over C2:C4: 25404, 25407, 25408.
Controle door te tellen: 8 rijen, waarvan 25409 dubbel, geeft 7 verschillende nummers. De reeks telt er 10 en 10 - 7 = 3 hiaten, wat klopt met de drie gevonden nummers.

Waarom het werkt

Het grootboek toont alleen de nummers die geboekt zijn, dus de test begint bij de nummers die zouden moeten bestaan. Elke functie heeft één taak:

REEKS bouwt die lijst op uit het laagste en hoogste nummer in de gegevens
X.VERGELIJKEN geeft een positie voor een nummer dat geboekt is en #N/B voor een nummer dat dat niet is
ISNB maakt precies dat verschil WAAR of ONWAAR
FILTER heeft per nummer van de reeks één antwoord nodig, en X.VERGELIJKEN geeft er één per nummer

Het derde argument vangt het nette geval zonder hiaten op.

De formule heeft Excel 2021, Excel voor Microsoft 365 of nieuwer nodig, omdat FILTER, REEKS en X.VERGELIJKEN in oudere versies niet bestaan.

Let op

Twee problemen in de gegevens vertekenen het resultaat:
- een verkeerd getypt nummer (25490 in plaats van 25409) rekt de reeks op en vult het resultaat met valse hiaten
- een lege cel in het bereik telt als extra unieke waarde (een 0), waardoor het aantal hiaten met RIJEN(UNIEK(A2:A9)) één te laag uitvalt
Ruim beide eerst op.
De nummers moeten echte getallen zijn. Een nummer als 25-0412 of een factuurnummer dat als tekst is opgeslagen werkt niet met MIN, MAX en REEKS. Haal eerst het voorvoegsel eraf en zet de rest om naar een getal.
Test ook elke reeks apart: creditnota's of een tweede administratie hebben meestal een eigen nummering.
Een hiaat is een vraag aan de klant, nog geen fout. Het nummer kan een geannuleerde factuur zijn, een testfactuur of een nummer dat het systeem heeft overgeslagen. Vraag om de toelichting en het geannuleerde stuk.
Ook een dubbel nummer, zoals 25409 hier, vraagt om een vraag: het kan twee boekingen van één factuur zijn of een correctie.

Varianten

=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)), AANTAL.ALS(A2:A9, REEKS(MAX(A2:A9)-MIN(A2:A9)+1, 1, MIN(A2:A9)))=0, "Geen hiaten")

=FILTER(REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)); AANTAL.ALS(A2:A9; REEKS(MAX(A2:A9)-MIN(A2:A9)+1; 1; MIN(A2:A9)))=0; "Geen hiaten")

De oudere aanpak: AANTAL.ALS telt hoe vaak elk nummer van de reeks in de lijst uit het grootboek voorkomt en FILTER houdt de nullen over. Dezelfde drie nummers, maar trager bij lange lijsten, en je hebt nog steeds REEKS en FILTER nodig.

=MAX(A2:A9)-MIN(A2:A9)+1-RIJEN(UNIEK(A2:A9))

=MAX(A2:A9)-MIN(A2:A9)+1-RIJEN(UNIEK(A2:A9))

Het aantal hiaten zonder ze op te sommen: de lengte van de reeks min het aantal verschillende nummers. 10 - 7 = 3. Gebruik het als controlegetal naast de lijst. Het gaat uit van alleen hele getallen.

=FILTER(UNIEK(A2:A9), AANTAL.ALS(A2:A9, UNIEK(A2:A9))>1, "Geen dubbelen")

=FILTER(UNIEK(A2:A9); AANTAL.ALS(A2:A9; UNIEK(A2:A9))>1; "Geen dubbelen")

De controle op dubbele nummers: UNIEK geeft de zeven verschillende nummers, AANTAL.ALS telt elk in A2:A9 (1, 1, 1, 1, 1, 2, 1) en FILTER houdt de nummers over die vaker dan één keer voorkomen. Het resultaat is 25409.

Alle combinaties