Combinatie
De vijf grootste handmatige boekingen met NEMEN, SORTEREN en FILTER
Toon de vijf grootste handmatige (MEM) journaalregels voor de journaalpostentest: FILTER houdt de MEM-regels over, SORTEREN zet het grootste bedrag bovenaan en NEMEN houdt de eerste vijf rijen.
Gebruikte functies:NEMENSORTERENFILTERExcel 2024 of nieuwer (Deze combinatie vereist Excel 2024 of nieuwer)
Uitgewerkt voorbeeld
Top vijf handmatige boekingen voor de journaalpostentest
Situatie
Bij de controle van Veldhuis Installatie BV voer je de journaalpostentest uit. Handmatige boekingen (dagboek MEM) hebben een hoger risico op doorbreking door de directie, dus je wilt de vijf grootste als eerste zien.
De journaalregels komen uit de XAF-auditfile, met verkoop- (VRK) en inkoopregels (INK) ertussen.
De gegevens
| Rij | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Dagboek | Boekstuk | Datum | Omschrijving | Bedrag | ||||||
| 2 | MEM | 25-0801 | 31-03-2025 | Reservering vakantiegeld | €18,400.00€ 18.400,00 | MEM=NEMEN(SORTEREN(FILTER(A2:E9, A2:A9="MEM"), 5, -1), 5)=NEMEN(SORTEREN(FILTER(A2:E9; A2:A9="MEM"); 5; -1); 5) | 25-0833 | 30-06-2025 | Correctie omzet | €96,500.00€ 96.500,00 | |
| 3 | INK | 25-0412 | 02-04-2025 | Huurfactuur | €52,300.00€ 52.300,00 | MEM | 25-0871 | 31-12-2025 | Reservering bonus | €41,200.00€ 41.200,00 | |
| 4 | MEM | 25-0833 | 30-06-2025 | Correctie omzet | €96,500.00€ 96.500,00 | MEM | 25-0852 | 28-11-2025 | Omboeking huur | €27,800.00€ 27.800,00 | |
| 5 | MEM | 25-0845 | 15-09-2025 | Omboeking crediteur | €7,250.00€ 7.250,00 | MEM | 25-0801 | 31-03-2025 | Reservering vakantiegeld | €18,400.00€ 18.400,00 | |
| 6 | VRK | 25-0460 | 10-10-2025 | Verkoopfactuur | €148,000.00€ 148.000,00 | MEM | 25-0845 | 15-09-2025 | Omboeking crediteur | €7,250.00€ 7.250,00 | |
| 7 | MEM | 25-0871 | 31-12-2025 | Reservering bonus | €41,200.00€ 41.200,00 | ||||||
| 8 | MEM | 25-0880 | 31-12-2025 | Btw-correctie | €3,150.00€ 3.150,00 | ||||||
| 9 | MEM | 25-0852 | 28-11-2025 | Omboeking huur | €27,800.00€ 27.800,00 | ||||||
| 10 |
Veeg opzij om alle kolommen te zien
Formule in F2
=NEMEN(SORTEREN(FILTER(A2:E9,A2:A9="MEM"), 5, -1), 5)
=NEMEN(SORTEREN(FILTER(A2:E9;A2:A9="MEM"); 5; -1); 5)
Resultaat: MEM | 25-0833 | 30-06-2025 | Correctie omzet | €96,500.00 / MEM | 25-0871 | 31-12-2025 | Reservering bonus | €41,200.00 / MEM | 25-0852 | 28-11-2025 | Omboeking huur | €27,800.00 / MEM | 25-0801 | 31-03-2025 | Reservering vakantiegeld | €18,400.00 / MEM | 25-0845 | 15-09-2025 | Omboeking crediteur | €7,250.00MEM | 25-0833 | 30-06-2025 | Correctie omzet | € 96.500,00 / MEM | 25-0871 | 31-12-2025 | Reservering bonus | € 41.200,00 / MEM | 25-0852 | 28-11-2025 | Omboeking huur | € 27.800,00 / MEM | 25-0801 | 31-03-2025 | Reservering vakantiegeld | € 18.400,00 / MEM | 25-0845 | 15-09-2025 | Omboeking crediteur | € 7.250,00
Hoe de onderdelen samenwerken
Let op: Bedragen die als tekst zijn opgeslagen, staan bij aflopend sorteren boven alle getallen, zodat de top vijf die tekstregels toont in plaats van de grootste bedragen.
De formule uit elkaar gehaald, van binnen naar buiten. Je kunt elk onderdeel in een eigen cel typen om te zien wat het teruggeeft.
=A2:A9="MEM"
=A2:A9="MEM"
VerwijzingKolom Dagboek, 8 cellenDruk op Escape om te sluiten=Gelijk aanDruk op Escape om te sluitenTekstVaste waardeDruk op Escape om te sluitenVergelijkt elke dagboekcode in
A2:A9metMEM. Het resultaat is een kolom van acht waarden: WAAR, ONWAAR, WAAR, WAAR, ONWAAR, WAAR, WAAR, WAAR. Zes van de acht regels zijn handmatig.=FILTER(A2:E9,
A2:A9="MEM") =FILTER(A2:E9;
A2:A9="MEM") 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 5 kolommenDruk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Kolom Dagboek, 8 cellenDruk op Escape om te sluiten=Gelijk aanDruk op Escape om te sluitenTekstVaste waardeDruk op Escape om te sluitenHoudt de rijen over waarvoor de test WAAR is, met alle vijf de kolommen: 6 rijen bij 5 kolommen. De bedragen staan nog in de volgorde van het blad: 18.400, 96.500, 7.250, 41.200, 3.150 en 27.800.
=SORTEREN(FILTER(A2:E9,
A2:A9="MEM"), 5, -1) =SORTEREN(FILTER(A2:E9;
A2:A9="MEM"); 5; -1) SORTEREN SORTGeeft een bereik gesorteerd op een van zijn kolommen.Lees meer over SORTERENDruk op Escape om te sluitenFILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: arrayHet bereik of de matrix die je sorteert; de bron zelf blijft zoals hij is.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.8 rijen bij 5 kolommenDruk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Kolom Dagboek, 8 cellenDruk op Escape om te sluiten=Gelijk aanDruk op Escape om te sluitenTekstVaste waardeDruk op Escape om te sluitensort_index (optioneel)Op welke kolom van de matrix je sorteert, geteld vanaf 1; standaard de eerste.Druk op Escape om te sluitensort_order (optioneel)1 sorteert oplopend (standaard), -1 sorteert aflopend.-1: aflopendDruk op Escape om te sluitenSorteert de 6 MEM-rijen op kolom 5 (het bedrag), grootste eerst (-1 betekent aflopend). De bedragen lopen nu 96.500, 41.200, 27.800, 18.400, 7.250, 3.150.
=NEMEN(SORTEREN(FILTER(A2:E9,
A2:A9="MEM"), 5, -1), 5) =NEMEN(SORTEREN(FILTER(A2:E9;
A2:A9="MEM"); 5; -1); 5) NEMEN TAKEDe eerste of laatste rijen of kolommen van een bereik.Lees meer over NEMENDruk op Escape om te sluitenSORTEREN SORTGeeft een bereik gesorteerd op een van zijn kolommen.Hier gebruikt als: matrixHet bereik of de matrix waaruit je rijen of kolommen neemt.Lees meer over SORTERENDruk op Escape om te sluitenFILTERGeeft elke rij van een bereik die aan een voorwaarde voldoet.Hier gebruikt als: arrayHet bereik of de matrix die je sorteert; de bron zelf blijft zoals hij is.Lees meer over FILTERDruk op Escape om te sluitenmatrixDe rijen die je terug wilt: één kolom of een hele tabel.8 rijen bij 5 kolommenDruk op Escape om te sluitenopnemenEen WAAR/ONWAAR per rij, even hoog als matrix (even breed voor kolommen): wat blijft.Kolom Dagboek, 8 cellenDruk op Escape om te sluiten=Gelijk aanDruk op Escape om te sluitenTekstVaste waardeDruk op Escape om te sluitensort_index (optioneel)Op welke kolom van de matrix je sorteert, geteld vanaf 1; standaard de eerste.Druk op Escape om te sluitensort_order (optioneel)1 sorteert oplopend (standaard), -1 sorteert aflopend.-1: aflopendDruk op Escape om te sluitenrijenHoeveel rijen je houdt; negatief telt vanaf het einde.3 = de eerste 3 rijen. -3 = de laatste 3 rijen. Leeg = alle rijen.Druk op Escape om te sluitenNEMEN houdt de eerste 5 rijen van de gesorteerde lijst en laat de zesde (3.150) vallen. Het resultaat is 5 rijen bij 5 kolommen, met boekstuk
25-0833van 96.500 bovenaan.
Stap voor stap
- A2:A9="MEM" test de acht dagboekcodes: MEM, INK, MEM, MEM, VRK, MEM, MEM, MEM geeft WAAR, ONWAAR, WAAR, WAAR, ONWAAR, WAAR, WAAR, WAAR.
- FILTER laat de INK-regel (52,30052.300) en de VRK-regel (148,000148.000) vallen, zodat 8 - 2 = 6 rijen overblijven met de bedragen 18,40018.400, 96,50096.500, 7,2507.250, 41,20041.200, 3,1503.150 en 27,80027.800.
- SORTEREN met 5 en -1 zet die 6 rijen op bedrag, grootste eerst: 96,50096.500, 41,20041.200, 27,80027.800, 18,40018.400, 7,2507.250, 3,1503.150.
- NEMEN met 5 houdt de eerste vijf rijen en laat de laatste vallen, boekstuk
25-0880van 3,1503.150. - Het resultaat loopt vanaf
F2over als 5 rijen bij 5 kolommen. De bovenste rij is25-0833, de correctie omzet van 96,50096.500, en de onderste rij is25-0845van 7,2507.250.
Waarom het werkt
Elke functie neemt een hele tabel en geeft een hele tabel terug, dus je kunt ze stapelen zonder hulpkolommen. FILTER bepaalt welke regels meetellen, SORTEREN bepaalt de volgorde en NEMEN bepaalt hoeveel je ziet.
De volgorde telt: eerst sorteren en dan filteren werkt ook, maar eerst nemen en dan sorteren knipt de lijst op de verkeerde plek af.
Let op
- De sorteerkolom is een positie binnen de gefilterde tabel (5 is de bedragenkolom), geen kolomletter van het blad. Filter je minder kolommen, dan moet het getal mee veranderen.
- Heeft geen enkele regel dagboek MEM, dan geeft FILTER #BEREKENEN! en toont de formule een fout. Geef FILTER een derde argument met een korte meldingstekst als het bestand mogelijk geen handmatige boekingen bevat.
- Controleer of de bedragkolom echte getallen bevat voordat je de top vijf vertrouwt.
Varianten
=GROOTSTE(ALS(A2:A9="MEM",E2:E9), REEKS(5))
=GROOTSTE(ALS(A2:A9="MEM";E2:E9); REEKS(5))
De aanpak met GROOTSTE, die ook werkt in Excel 2021 waar NEMEN ontbreekt: je krijgt alleen de vijf bedragen (96,50096.500, 41,20041.200, 27,80027.800, 18,40018.400, 7,2507.250), niet de boekstuknummers of omschrijvingen, dus je hebt nog een zoekactie nodig om te zien om welke boekingen het gaat.
=NEMEN(SORTEREN(FILTER(A2:E9,A2:A9="MEM"), 5, 1), -5)
=NEMEN(SORTEREN(FILTER(A2:E9;A2:A9="MEM"); 5; 1); -5)
Sorteert oplopend en neemt met een negatief getal de laatste vijf rijen. Dezelfde vijf boekingen, maar met de grootste onderaan.
Verwante combinaties
Gebruikte functies
- NEMENHoudt alleen de eerste of laatste rijen en kolommen van een bereik of matrix over en laat ze als blok overlopen, waarbij een negatief aantal vanaf het einde telt.
- SORTERENMaakt een gesorteerde kopie van een bereik, op een van de kolommen, oplopend of aflopend, als overlopend blok dat opnieuw sorteert zodra de gegevens veranderen.
- 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.
Gebruikte functies
- NEMENHoudt alleen de eerste of laatste rijen en kolommen van een bereik of matrix over en laat ze als blok overlopen, waarbij een negatief aantal vanaf het einde telt.
- SORTERENMaakt een gesorteerde kopie van een bereik, op een van de kolommen, oplopend of aflopend, als overlopend blok dat opnieuw sorteert zodra de gegevens veranderen.
- 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.