Combinatie
Boekingen in het weekend en op feestdagen met FILTER, WEEKDAG en AANTAL.ALS
Toon de journaalposten met een datum op zaterdag, zondag of een Nederlandse feestdag: WEEKDAG markeert de weekenden, AANTAL.ALS toetst de datum aan een feestdagenlijst en FILTER houdt de gemarkeerde rijen over.
Gebruikte functies:FILTERWEEKDAGAANTAL.ALSExcel 2021 of nieuwer (Deze combinatie vereist Excel 2021 of nieuwer)
Uitgewerkt voorbeeld
Journaalposten met een datum in het weekend of op een feestdag
Situatie
Bij de controle van Veldhuis Installatie BV voer je de journaalpostentest uit. Boekingen met een datum op zaterdag, zondag of een feestdag zijn ongebruikelijk voor een bedrijf dat op werkdagen werkt, dus ze hebben een hoger frauderisico en je wilt ze als eerste zien.
Je hebt de journaalregels uit de XAF-auditfile en in E2:E8 een lijst met de feestdagen van 2025. Hiervoor heb je Excel 2021, Microsoft 365 of nieuwer nodig, vanwege FILTER.
De gegevens
| Rij | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Boekstuk | Datum | Omschrijving | Bedrag | Feestdagen 2025 | |||||
| 2 | 25-0790 | 14-03-2025 | Salarissen maart | €14,200.00€ 14.200,00 | 01-01-2025 | 25-0812=FILTER(A2:D9, (WEEKDAG(B2:B9, 2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0, "Geen weekend- of feestdagboekingen")=FILTER(A2:D9; (WEEKDAG(B2:B9; 2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0; "Geen weekend- of feestdagboekingen") | 21-04-2025 | Huurfactuur | €18,400.00€ 18.400,00 | |
| 3 | 25-0812 | 21-04-2025 | Huurfactuur | €18,400.00€ 18.400,00 | 21-04-2025 | 25-0819 | 26-04-2025 | Correctie omzet | €96,500.00€ 96.500,00 | |
| 4 | 25-0819 | 26-04-2025 | Correctie omzet | €96,500.00€ 96.500,00 | 26-04-2025 | 25-0838 | 29-05-2025 | Btw-correctie | €7,250.00€ 7.250,00 | |
| 5 | 25-0838 | 29-05-2025 | Btw-correctie | €7,250.00€ 7.250,00 | 29-05-2025 | 25-0863 | 12-10-2025 | Reservering bonus | €41,200.00€ 41.200,00 | |
| 6 | 25-0851 | 10-09-2025 | Onderhoudsfactuur | €52,300.00€ 52.300,00 | 09-06-2025 | |||||
| 7 | 25-0863 | 12-10-2025 | Reservering bonus | €41,200.00€ 41.200,00 | 25-12-2025 | |||||
| 8 | 25-0877 | 21-11-2025 | Omboeking crediteur | €3,150.00€ 3.150,00 | 26-12-2025 | |||||
| 9 | 25-0880 | 31-12-2025 | Omzet jaareinde | €27,800.00€ 27.800,00 | ||||||
| 10 |
Veeg opzij om alle kolommen te zien
Formule in F2
=FILTER(A2:D9,(WEEKDAG(B2:B9, 2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0, "Geen weekend- of feestdagboekingen")
=FILTER(A2:D9;(WEEKDAG(B2:B9; 2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0; "Geen weekend- of feestdagboekingen")
Resultaat: 25-0812 | 21-04-2025 | Huurfactuur | €18,400.00 / 25-0819 | 26-04-2025 | Correctie omzet | €96,500.00 / 25-0838 | 29-05-2025 | Btw-correctie | €7,250.00 / 25-0863 | 12-10-2025 | Reservering bonus | €41,200.0025-0812 | 21-04-2025 | Huurfactuur | € 18.400,00 / 25-0819 | 26-04-2025 | Correctie omzet | € 96.500,00 / 25-0838 | 29-05-2025 | Btw-correctie | € 7.250,00 / 25-0863 | 12-10-2025 | Reservering bonus | € 41.200,00
Hoe de onderdelen samenwerken
Let op: Een feestdag die in de lijst ontbreekt, wordt nooit gemarkeerd, en de formule geeft geen waarschuwing.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=WEEKDAG(B2:B9,
2)>5 =WEEKDAG(B2:B9;
2)>5 serieel_getalDe datum waarvan je de dag van de week wilt weten.Kolom Datum, 8 cellenDruk op Escape om te sluitentype_getal (optioneel)Welke dag 1 is: 1 = zondag (standaard), 2 = maandag.2: maandag 1 tot en met zondag 7Druk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluitenWEEKDAG met het tweede argument 2 telt maandag als 1 tot en met zondag als 7, dus een waarde boven 5 betekent zaterdag of zondag. De acht datums geven 5, 1, 6, 4, 3, 7, 5, 3 en de test geeft ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR. Twee datums vallen in een weekend: 26 april (een zaterdag) en 12 oktober (een zondag).
=AANTAL.ALS(E2:E8,
B2:B9)>0 =AANTAL.ALS(E2:E8;
B2:B9)>0 AANTAL.ALS COUNTIFTelt cellen die aan één voorwaarde voldoen.Lees meer over AANTAL.ALSDruk op Escape om te sluitenbereikDe cellen die worden getest.Kolom E, 7 cellenDruk op Escape om te sluitencriteriumDe voorwaarde waaraan een cel moet voldoen om mee te tellen.Kolom Datum, 8 cellenDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluitenAANTAL.ALS telt hoe vaak elke boekingsdatum voorkomt in de feestdagenlijst
E2:E8. Omdat het tweede argument een bereik van acht datums is, krijg je acht aantallen: 0, 1, 1, 1, 0, 0, 0, 0. Boven 0 betekent dat de datum in de lijst staat: 21 april, 26 april en 29 mei.=(WEEKDAG(B2:B9,
2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0 =(WEEKDAG(B2:B9;
2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0 serieel_getalDe datum waarvan je de dag van de week wilt weten.Kolom Datum, 8 cellenDruk op Escape om te sluitentype_getal (optioneel)Welke dag 1 is: 1 = zondag (standaard), 2 = maandag.2: maandag 1 tot en met zondag 7Druk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten+Telt opDruk op Escape om te sluitenAANTAL.ALS COUNTIFTelt cellen die aan één voorwaarde voldoen.Lees meer over AANTAL.ALSDruk op Escape om te sluitenbereikDe cellen die worden getest.Kolom E, 7 cellenDruk op Escape om te sluitencriteriumDe voorwaarde waaraan een cel moet voldoen om mee te tellen.Kolom Datum, 8 cellenDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluitenTelt de twee testen bij elkaar op (WAAR telt als 1) en controleert of de som boven 0 ligt. Dat werkt per rij als OF. De sommen zijn 0, 1, 2, 1, 0, 1, 0, 0, dus het resultaat is ONWAAR, WAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR, ONWAAR. Boekstuk
25-0819scoort 2 omdat 26 april 2025 zowel een zaterdag als Koningsdag is, maar het blijft één gemarkeerde rij.=SOMPRODUCT(((WEEKDAG(B2:B9,
2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0)*1) =SOMPRODUCT(((WEEKDAG(B2:B9;
2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0)*1) SOMPRODUCT SUMPRODUCTVermenigvuldigt bereiken paarsgewijs en telt de uitkomsten op.Lees meer over SOMPRODUCTDruk op Escape om te sluitenWEEKDAG WEEKDAYDe dag van de week als getal.Hier gebruikt als: matrix1Het eerste bereik of de eerste matrix; alle andere moeten even groot zijn.Lees meer over WEEKDAGDruk op Escape om te sluitenserieel_getalDe datum waarvan je de dag van de week wilt weten.Kolom Datum, 8 cellenDruk op Escape om te sluitentype_getal (optioneel)Welke dag 1 is: 1 = zondag (standaard), 2 = maandag.2: maandag 1 tot en met zondag 7Druk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten+Telt opDruk op Escape om te sluitenAANTAL.ALS COUNTIFTelt cellen die aan één voorwaarde voldoen.Lees meer over AANTAL.ALSDruk op Escape om te sluitenbereikDe cellen die worden getest.Kolom E, 7 cellenDruk op Escape om te sluitencriteriumDe voorwaarde waaraan een cel moet voldoen om mee te tellen.Kolom Datum, 8 cellenDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten*VermenigvuldigtDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluitenZet de acht waarden WAAR en ONWAAR om in 1 en 0 en telt ze op: 0 + 1 + 1 + 1 + 0 + 1 + 0 + 0 = 4 gemarkeerde boekingen.
=SOMPRODUCT(((WEEKDAG(B2:B9,
2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0)*D2:D9) =SOMPRODUCT(((WEEKDAG(B2:B9;
2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0)*D2:D9) SOMPRODUCT SUMPRODUCTVermenigvuldigt bereiken paarsgewijs en telt de uitkomsten op.Lees meer over SOMPRODUCTDruk op Escape om te sluitenWEEKDAG WEEKDAYDe dag van de week als getal.Hier gebruikt als: matrix1Het eerste bereik of de eerste matrix; alle andere moeten even groot zijn.Lees meer over WEEKDAGDruk op Escape om te sluitenserieel_getalDe datum waarvan je de dag van de week wilt weten.Kolom Datum, 8 cellenDruk op Escape om te sluitentype_getal (optioneel)Welke dag 1 is: 1 = zondag (standaard), 2 = maandag.2: maandag 1 tot en met zondag 7Druk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten+Telt opDruk op Escape om te sluitenAANTAL.ALS COUNTIFTelt cellen die aan één voorwaarde voldoen.Lees meer over AANTAL.ALSDruk op Escape om te sluitenbereikDe cellen die worden getest.Kolom E, 7 cellenDruk op Escape om te sluitencriteriumDe voorwaarde waaraan een cel moet voldoen om mee te tellen.Kolom Datum, 8 cellenDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten*VermenigvuldigtDruk op Escape om te sluitenVerwijzingKolom Bedrag, 8 cellenDruk op Escape om te sluitenVermenigvuldigt elke 1 of 0 met het bedrag in kolom D en telt de producten op: 18.400 + 96.500 + 7250 + 41.200 = 163.350. De bedragen van niet-gemarkeerde regels worden met 0 vermenigvuldigd en vallen weg.
=FILTER(A2:D9,
(WEEKDAG(B2:B9, 2)>5)+(AANTAL.ALS(E2:E8, B2:B9)>0)>0, "Geen weekend- of feestdagboekingen") =FILTER(A2:D9;
(WEEKDAG(B2:B9; 2)>5)+(AANTAL.ALS(E2:E8; B2:B9)>0)>0; "Geen weekend- of feestdagboekingen") 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.8 rijen bij 4 kolommenDruk op Escape om te sluitenWEEKDAG WEEKDAYDe dag van de week als getal.Hier gebruikt als: opnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Lees meer over WEEKDAGDruk op Escape om te sluitenserieel_getalDe datum waarvan je de dag van de week wilt weten.Kolom Datum, 8 cellenDruk op Escape om te sluitentype_getal (optioneel)Welke dag 1 is: 1 = zondag (standaard), 2 = maandag.2: maandag 1 tot en met zondag 7Druk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten+Telt opDruk op Escape om te sluitenAANTAL.ALS COUNTIFTelt cellen die aan één voorwaarde voldoen.Lees meer over AANTAL.ALSDruk op Escape om te sluitenbereikDe cellen die worden getest.Kolom E, 7 cellenDruk op Escape om te sluitencriteriumDe voorwaarde waaraan een cel moet voldoen om mee te tellen.Kolom Datum, 8 cellenDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluiten>Groter danDruk op Escape om te sluitenGetalVaste waardeDruk op Escape om te sluitenals_leeg (optioneel)Wat je ziet als geen rij voldoet; zonder dit krijg je #BEREKENEN!.Druk op Escape om te sluitenFILTER houdt de rijen over waarvoor de gecombineerde test WAAR is, met alle vier de kolommen: 4 rijen bij 4 kolommen. Het zijn de boekstukken
25-0812,25-0819,25-0838en25-0863. Is er geen enkele boeking gemarkeerd, dan toont de cel de meldingstekst in plaats van #BEREKENEN!.
Stap voor stap
- WEEKDAG(B2:B9; 2) geeft de weekdagnummers 5, 1, 6, 4, 3, 7, 5, 3 (vrijdag, maandag, zaterdag, donderdag, woensdag, zondag, vrijdag, woensdag). De test >5 geeft ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR, WAAR, ONWAAR, ONWAAR: 26 april en 12 oktober zijn weekenddagen.
- AANTAL.ALS(E2:E8; B2:B9) zoekt elke boekingsdatum op in de feestdagenlijst en geeft 0, 1, 1, 1, 0, 0, 0, 0. De treffers zijn 21 april (tweede paasdag), 26 april (Koningsdag) en 29 mei (Hemelvaartsdag). De test >0 geeft ONWAAR, WAAR, WAAR, WAAR, ONWAAR, ONWAAR, ONWAAR, ONWAAR.
- De twee testen per rij bij elkaar optellen geeft 0, 1, 2, 1, 0, 1, 0, 0. De test van de som >0 geeft ONWAAR, WAAR, WAAR, WAAR, ONWAAR, WAAR, ONWAAR, ONWAAR. Het derde boekstuk (
25-0819) scoort 2, omdat 26 april 2025 een zaterdag en Koningsdag is, maar telt één keer mee. - FILTER houdt de vier rijen met WAAR en laat
25-0790(vrijdag 14 maart),25-0851(woensdag 10 september),25-0877(vrijdag 21 november) en25-0880(woensdag 31 december) vallen. Dat is 8 - 4 = 4 rijen. - Het resultaat loopt vanaf
F2over als 4 rijen bij 4 kolommen. De rijen tellen geeft 4 gemarkeerde boekingen en hun bedragen optellen geeft 18,40018.400 + 96,50096.500 + 7250 + 41,20041.200 = 163,350163.350.
Waarom het werkt
Elke test geeft één WAAR of ONWAAR per journaalregel, dus je kunt de weekendtest en de feestdagtest combineren zonder hulpkolommen. Optellen werkt als OF voor elke rij apart (de functie OF geeft één antwoord voor het hele bereik). FILTER houdt daarna hele rijen over, zodat boekstuk, omschrijving en bedrag naast de gemarkeerde datum blijven staan, klaar om een steekproef uit te kiezen.
Let op
De feestdagenlijst moet volledig zijn en het hele controlejaar dekken.
De lijst bevat de feestdagen van 2025 die op een werkdag of zaterdag vallen (inclusief tweede pinksterdag op 9 juni en tweede kerstdag op 26 december); eerste paasdag en eerste pinksterdag ontbreken omdat de weekendtest die al markeert. Controleer de datums aan de hand van een officiële kalender en let op dat Goede Vrijdag en 5 mei niet voor elke werkgever een vrije dag zijn.
- Staan datum en tijd samen in één veld, of zijn de datums tekst, dan vindt AANTAL.ALS geen treffers voor de feestdagen, want 21 april 2025 om 14:30 is niet gelijk aan 21 april 2025. WEEKDAG op tekstdatums kan ook falen. Zet de kolom eerst om naar echte datums, bijvoorbeeld met INTEGER voor het tijdsdeel.
- Een gemarkeerde boeking is een risico-indicator, geen fout. Weekendboekingen zijn normaal bij een cliënt die in ploegen werkt of bij geautomatiseerde batchboekingen, dus vraag naar het gebruikelijke patroon voordat je een conclusie trekt. Let ook op dat de boekdatum in de auditfile kan afwijken van de invoerdatum.
Varianten
=FILTER(A2:D9,(WEEKDAG(B2:B9, 2)>5)+ISGETAL(VERGELIJKEN(B2:B9, E2:E8, 0))>0, "Geen weekend- of feestdagboekingen")
=FILTER(A2:D9;(WEEKDAG(B2:B9; 2)>5)+ISGETAL(VERGELIJKEN(B2:B9; E2:E8; 0))>0; "Geen weekend- of feestdagboekingen")
VERGELIJKEN met ISGETAL in plaats van AANTAL.ALS: VERGELIJKEN geeft de positie (2, 3, 4) of #N/B en ISGETAL maakt daar WAAR of ONWAAR van. Dezelfde vier rijen. Handig als je ook de positie van de feestdag in de lijst wilt.
=ALS(OF(WEEKDAG(B2,2)>5, AANTAL.ALS($E$2:$E$8, B2)>0), 1, 0)
=ALS(OF(WEEKDAG(B2;2)>5; AANTAL.ALS($E$2:$E$8; B2)>0); 1; 0)
De oudere aanpak voor Excel-versies zonder FILTER: een hulpkolom in een vrije kolom van het blad (bijvoorbeeld kolom H) met deze formule naar beneden gekopieerd, en daarna het blad filteren op 1 met de gewone filterknop. De functie OF werkt hier omdat de formule één rij tegelijk test.
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.
- WEEKDAGZet een datum om in het nummer van de dag van de week; type_getal bepaalt welke dag nummer 1 is.
- AANTAL.ALSTelt hoeveel cellen in een bereik aan één voorwaarde voldoen, zoals gelijk aan een waarde, boven een getal of beginnend met bepaalde letters.
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.
- WEEKDAGZet een datum om in het nummer van de dag van de week; type_getal bepaalt welke dag nummer 1 is.
- AANTAL.ALSTelt hoeveel cellen in een bereik aan één voorwaarde voldoen, zoals gelijk aan een waarde, boven een getal of beginnend met bepaalde letters.