Hoe snel ontbrekende datums in een lijst in Excel vinden?
Deze handleiding introduceert verschillende manieren om deze ontbrekende datums efficiënt in Excel te detecteren en aan te vullen:
Ontbrekende datums vinden met Voorwaardelijke opmaak
Ontbrekende datums vinden met formule
Zoek en vul ontbrekende datums in met Kutools voor Excel
Gebruik VBA om automatisch ontbrekende datums te identificeren en invoegen
Markeer ontbrekende datums met behulp van een Draaitabel
Ontbrekende datums vinden met Voorwaardelijke opmaak
Eén manier om gaten in je datumlijst te signaleren, is door gebruik te maken van Excel's Voorwaardelijke opmaak. Deze methode markeert visueel de cellen waar een datum ontbreekt in een reeks, waardoor het gemakkelijk is om discontinuïteiten in één oogopslag te herkennen.
1. Selecteer het bereik met je datums en ga vervolgens naar Start > Voorwaardelijke opmaak > Nieuwe regel. Zie schermafbeelding:
2. In het Nieuwe opmaakregel dialoogvenster, kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt onder de Selecteer een regeltype sectie. Voer de volgende formule in: =A2<>(A1+1) (waarbij A1 de eerste datum is en A2 de volgende datum in je lijst). Zie schermafbeelding:
3. Klik op de Opmaak knop om het Celopmaak dialoogvenster te openen. Onder het Opvullen tabblad, kies een kleur om de ontbrekende datums te markeren. Zie schermafbeelding:
4. Nadat je de opmaak hebt ingesteld, klik OK twee keer om toe te passen. Nu zullen de cellen waar een datum ontbreekt in de reeks gemarkeerd worden.
Opmerking: De laatste datum in je lijst kan ook gemarkeerd worden, omdat deze formule controleert wat na elke datum volgt. Deze methode is vooral handig om grote datasets snel te bekijken, maar vult de ontbrekende datums niet automatisch in.
Ontbrekende datums vinden met een formule
Een andere praktische aanpak is om een Excel-formule te gebruiken om gaten direct in je tabel te identificeren. Deze methode creëert een nieuwe kolom die aangeeft of er een dag ontbreekt na elke datum, wat geschikt is voor het bijhouden van presentielijsten, projectplanningen of inventarisrecords.
In een lege kolom naast je datumslijst (bijvoorbeeld cel B1 als je lijst begint bij A1), voer de volgende formule in: =ALS(A2=A1+1;"";"Ontbrekende volgende dag"). Druk op Enter, en sleep vervolgens de automatisch invulgreep naar beneden om de formule naast alle datums te kopiëren. Zie screenshots:
Waar je "Ontbrekende volgende dag" ziet, weet je dat er een gat in je lijst zit. Dit is een eenvoudige, visuele manier om ontbrekende datums te controleren en kan vooral handig zijn als je de geïdentificeerde gaten wilt filteren of verder verwerken.
Opmerking: Net als bij de vorige methode zal de formule de rij na de laatste datum markeren (aangezien er geen volgende datum is), wat je kunt negeren of wissen indien nodig.
Zoek en vul ontbrekende datums in met Kutools voor Excel
Voor gebruikers van Kutools voor Excel is er een ingebouwde functie die snel ontbrekende datums of volgnummers kan vinden en zelfs invullen. Dit is vooral handig wanneer je niet alleen gaten wilt lokaliseren, maar ook je gegevens automatisch wilt aanvullen voor nauwkeurige berekeningen of audits.
Na gratis installatie Kutools voor Excel, doe het volgende:
1. Selecteer de datumslijst die je wilt analyseren en ga vervolgens naar Kutools > Invoegen > Ontbrekende reeks vinden. Zie schermafbeelding:
2. In het Ontbrekende reeks vinden dialoogvenster, kun je kiezen uit verschillende opties, zoals het vinden of invoegen van ontbrekende nummers, markeren of het maken van een markeringkolom. Zie schermafbeelding:
3. Nadat je je keuze hebt bevestigd, klik OK. Een melding toont het aantal ontbrekende datums dat gevonden is. Zie schermafbeelding:
4. Klik op OK om te voltooien. Nu zal je lijst de ontbrekende datums tonen of zelfs invullen, afhankelijk van de optie die je hebt geselecteerd. Deze aanpak is handig voor grote datasets en minimaliseert fouten door handmatig controleren of formules verplaatsen.
| Voeg ontbrekend volgnummer in | Voeg lege rijen in wanneer ontbrekende volgnummers worden gevonden |
 |  |
| Voeg een nieuwe kolom met volgende ontbrekende markering toe | Achtergrondkleur invullen |
 |  |
Dit hulpmiddel kan veel tijd besparen en is vooral voordelig voor financiële overzichten, aanwezigheidsgegevens of elk scenario waarin een continue datuminformatie vereist is. Zorg ervoor dat je lijst gesorteerd is op datum voor de beste resultaten.
Gebruik VBA om automatisch ontbrekende datums te identificeren en invoegen
Als je werkt met lange of vaak bijgewerkte datumslijsten en het proces volledig geautomatiseerd wilt hebben, kun je een aangepaste VBA-macro in Excel gebruiken. Deze methode scant je datumkolom, vindt ontbrekende datums in de reeks en voegt de ontbrekende datums als nieuwe rijen rechtstreeks in je lijst in.
Dit is vooral handig voor grote datasets, terugkerende rapportages of wanneer nieuwe gegevens regelmatig worden toegevoegd en je volledigheid wilt garanderen zonder handmatig te controleren.
Bewerkingsstappen:
- Klik Ontwikkelaar > Visual Basic om de VBA-editor te openen. In het pop-upvenster Microsoft Visual Basic for Applications, klik Invoegen > Module, en plak de volgende code in het modulevenster:
Sub InsertMissingDates()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim currentDate As Date, nextDate As Date
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
i = 2
While i < lastRow
currentDate = ws.Cells(i, 1).Value
nextDate = ws.Cells(i + 1, 1).Value
If nextDate > currentDate + 1 Then
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = currentDate + 1
ws.Cells(i + 1, 1).NumberFormat = "yyyy-mm-dd"
lastRow = lastRow + 1
End If
i = i + 1
Wend
End Sub- Klik op de
Uitvoeren knop (of druk op F5) om de code uit te voeren. De macro controleert je eerste kolom (Kolom A) op een lijst met datums en voegt automatisch ontbrekende datums in als nieuwe rijen.
Uitvoeren knop (of druk op F5) om de code uit te voeren. De macro controleert je eerste kolom (Kolom A) op een lijst met datums en voegt automatisch ontbrekende datums in als nieuwe rijen.Praktische tips en notities:
– Zorg ervoor dat je datums in oplopende volgorde zijn gesorteerd voordat je de macro uitvoert.
– De macro voegt ontbrekende datums in als nieuwe rijen, dus maak een back-up van je gegevens of test op een kopie indien nodig.
– Als je datums niet in Kolom A staan, pas ws.Cells(i,1) aan naar het juiste kolomnummer.
– In geval van een zeer grote dataset kan de macro enkele momenten nodig hebben om te voltooien.
– Als je een foutmelding ontvangt, controleer dan of alle cellen in je datumkolom daadwerkelijke datumwaarden zijn.
Markeer ontbrekende datums met behulp van een Draaitabel
Als je liever geen formules of code gebruikt, kun je gebruik maken van de ingebouwde Draaitabel-functie van Excel om visueel je werkelijke lijst met datums te vergelijken met een volledige verwachte reeks. Deze methode is vooral geschikt wanneer je presentielijsten, transacties of dagelijkse records wilt analyseren of controleren waarbij elke datum in een bereik zou moeten verschijnen.
Bewerkingsstappen:
- Maak eerst een hulpkolom die een volledige reeks van verwachte datums bevat die je start- en einddatums bestrijken. Voer de eerste datum in een cel in (bijvoorbeeld D2), en sleep vervolgens de vulgreep naar beneden om datums te maken tot je bereik compleet is.
- Kopieer zowel je originele datumslijst als je nieuwe hulpcel met datums naar een nieuw werkblad en stapel ze in één kolom (bijvoorbeeld kolom E).
- Selecteer de gecombineerde lijst en ga naar Invoegen > Draaitabel. Stel in het dialoogvenster de tabel/bereik in en kies een nieuw werkblad voor de output.
- Sleep in de Draaitabelveldenlijst het datumveld naar het Rijgebied en opnieuw naar het Waardengebied, waarbij de aggregatie op Aantal wordt ingesteld. Datums met slechts één verschijning in de aantal-kolom duiden ontbrekende datums aan (d.w.z. die alleen in de volledige reeks voorkomen, maar niet in je werkelijke gegevens).
Tips:
– Deze methode werkt het beste voor het controleren van ontbrekende items over lange periodes.
– Voor optimale resultaten moet je ervoor zorgen dat je datumslijsten geen duplicaten bevatten.
– Je kunt filteren of markeren in de Draaitabel om snel ontbrekende datums te vinden.
– Geavanceerde gebruikers kunnen deze methode combineren met Voorwaardelijke opmaak voor extra zichtbaarheid.
Voordelen: Makkelijk te visualiseren; geen formules of VBA vereist; ideaal voor rapportage.
Nadelen: Vult ontbrekende datums niet automatisch in, maar markeert wat ontbreekt.
Demo: Zoek en voeg ontbrekende datum in lijst in
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen