Excel-gegevensvalidatie: gegevensvalidatie toevoegen, gebruiken, kopiëren en verwijderen in Excel

In Excel is de gegevensvalidatie een handige functie die u kunt gebruiken om te beperken wat een gebruiker in een cel kan invoeren. De gegevensvalidatiefunctie kan u bijvoorbeeld helpen de lengte van tekstreeksen te beperken, of tekst die begint / eindigt met specifieke tekens, of unieke waarden die moeten worden ingevoerd, enzovoort.

In deze zelfstudie zullen we het hebben over het toevoegen, gebruiken en verwijderen van de gegevensvalidatie in Excel, enkele basis- en geavanceerde bewerkingen van deze functie zullen ook in details worden gedemonstreerd.

1. Wat is gegevensvalidatie in Excel?

Het Data Validation functie kan u helpen invoerinhoud in uw werkblad te beperken. Normaal gesproken kunt u enkele validatieregels maken om te voorkomen of toe te staan ​​dat bepaalde gegevens worden ingevoerd in een lijst met geselecteerde cellen.

Enkele basisgebruiken van de functie Gegevensvalidatie:

1. Elke waarde: er wordt geen validatie uitgevoerd, u kunt alles in de opgegeven cellen invoeren. 2. Hele waarde: alleen hele getallen zijn toegestaan. 3. Decimaal: hiermee kunnen zowel gehele getallen als decimalen worden ingevoerd. 4. Lijst: alleen waarden uit de vooraf gedefinieerde lijst mogen worden ingevoerd of geselecteerd. De waarden worden weergegeven in een vervolgkeuzelijst. 5. Datum: alleen data zijn toegestaan. 6. Tijd: alleen tijden zijn toegestaan. 7. Tekstlengte: alleen de opgegeven lengte van de tekst toestaan. 8. Aangepast: maak aangepaste formuleregels voor het valideren van de invoer van gebruikers.

2. Hoe voeg ik gegevensvalidatie toe in Excel?

In Excel-werkblad kunt u gegevensvalidatie toevoegen met de volgende stappen:

1. Selecteer een lijst met cellen waar u de gegevensvalidatie wilt instellen en klik vervolgens op Data > Data Validation > Data Validation, zie screenshot:

2. In de Data Validation dialoogvenster onder het Instellingen tabblad, maakt u uw eigen validatieregels. in de criteriavakken kunt u een van de volgende typen leveren:

• waarden: Typ nummers direct in de criteriavakken;
• Celverwijzing: Verwijs naar een cel in het werkblad of een ander werkblad;
• Formules: Maak complexere formules als voorwaarden.

Als voorbeeld zal ik een regel maken waarmee alleen hele getallen tussen 100 en 1000 kunnen worden ingevoerd, stel hier de criteria in zoals hieronder afgebeeld:

3. Na het configureren van de voorwaarden, kunt u naar de Input Message or Foutmelding om het invoerbericht of de foutwaarschuwing voor de validatiecellen naar wens in te stellen. (Als u de waarschuwing niet wilt instellen, klikt u op OK om direct af te ronden.)

3.1) Invoerbericht toevoegen (optioneel):

U kunt een bericht maken dat verschijnt wanneer u een cel selecteert die gegevensvalidatie bevat. Dit bericht helpt de gebruiker eraan te herinneren wat hij in de cel kan invoeren.

Ga naar uw Input Message tabblad en doe het volgende:

Controleer de Toon invoerbericht wanneer cel is geselecteerd keuze; Voer de gewenste titel en het herinneringsbericht in de overeenkomstige velden in; Klik OK om dit dialoogvenster te sluiten.

Wanneer u nu een gevalideerde cel selecteert, wordt als volgt een berichtvenster weergegeven:

3.2) Maak zinvolle foutmeldingen (optioneel):

Naast het maken van het invoerbericht, kunt u ook foutwaarschuwingen weergeven wanneer ongeldige gegevens worden ingevoerd in een cel met gegevensvalidatie.

Ga naar uw Foutmelding tabblad van de Data Validation dialoogvenster, gaat u als volgt te werk:

Controleer de Toon foutmelding nadat ongeldige gegevens zijn ingevoerd keuze; In het stijl vervolgkeuzelijst, selecteer het gewenste waarschuwingstype dat u nodig hebt: Stop (standaard): Dit waarschuwingstype voorkomt dat gebruikers ongeldige gegevens invoeren. waarschuwing: waarschuwt gebruikers dat de gegevens ongeldig zijn, maar verhindert het invoeren ervan niet. Informatie: informeert gebruikers alleen over een ongeldige gegevensinvoer. Voer de gewenste titel en het waarschuwingsbericht in de overeenkomstige velden in; Klik OK om het dialoogvenster te sluiten.

En nu, bij het invoeren van een ongeldige waarde, wordt het berichtwaarschuwingsvenster weergegeven zoals hieronder wordt weergegeven:

stop optie: U kunt klikken op Opnieuw proberen om een ​​andere waarde te typen of Annuleer om de invoer te verwijderen.

waarschuwing optie: Klik Ja om de ongeldige invoer in te voeren, Nee om het te wijzigen, of Annuleer om de invoer te verwijderen.

Informatie optie: Klik OK om de ongeldige invoer in te voeren of Annuleer om de invoer te verwijderen.

Opmerking:: Als u uw eigen aangepaste bericht niet instelt in de Foutmelding vak, een standaard stop waarschuwingsvenster wordt weergegeven zoals hieronder weergegeven:

3. Basisvoorbeelden voor gegevensvalidatie

Wanneer u deze functie voor gegevensvalidatie gebruikt, zijn er 8 ingebouwde opties waarmee u de gegevensvalidatie kunt instellen. Zoals: elke waarde, gehele getallen en decimalen, datum en tijd, lijst, tekstlengte en aangepaste formule. In deze sectie zullen we bespreken hoe u enkele van de ingebouwde opties in Excel kunt gebruiken?

3.1 Gegevensvalidatie voor gehele getallen en decimalen

1. Selecteer een lijst met cellen waarin u alleen hele getallen of decimalen wilt toestaan ​​en klik vervolgens op Data > Data Validation > Data Validation.

2. In de Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• Selecteer het bijbehorende item Geheel getal or Decimaal functie in het Allow vervolgkeuzelijst.
• En kies vervolgens een van de criteria die u nodig hebt in de Data box (In dit voorbeeld kies ik tussen keuze).
• Tips: De criteria bevatten: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens de Minimum als maximaal waarden die u nodig hebt (ik wil de getallen tussen 0 en 1).
• Eindelijk, klik OK knop.

3. Nu mogen alleen de hele getallen van 0 tot 100 in de door u geselecteerde cellen worden ingevoerd.

3.2 Gegevensvalidatie voor datum en tijd

Om een ​​specifieke datum of tijd te valideren die moet worden ingevoerd, is het eenvoudig om dit te gebruiken Data Validationgaat u als volgt te werk:

1. Selecteer een lijst met cellen waarin u alleen de specifieke datums of tijden wilt toestaan ​​en klik vervolgens op Data > Data Validation > Data Validation.

2. In de Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• Selecteer het bijbehorende item Datum or Time functie in het Allow vervolgkeuzelijst.
• En kies vervolgens een van de criteria die u nodig hebt in de Data doos (hier kies ik) groter dan keuze).
• Tips: De criteria bevatten: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens de Startdatum je nodig hebt (ik wil de datums groter dan 8-20-2021).
• Eindelijk, klik OK knop.

3. Nu mogen alleen de datums hoger dan 8-20-2021 worden ingevoerd in de door u geselecteerde cellen.

3.3 Gegevensvalidatie voor tekstlengte

Als u het aantal tekens wilt beperken dat in een cel kan worden getypt. Als u bijvoorbeeld de inhoud wilt beperken tot niet meer dan 10 tekens voor een bepaald bereik, doet u dit: Data Validation kan je ook een plezier doen.

1. Selecteer een lijst met cellen waarvan u de tekstlengte wilt beperken en klik vervolgens op Data > Data Validation > Data Validation.

2. In de Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Tekstlengte van de Allow vervolgkeuzelijst.
• En kies vervolgens een van de criteria die u nodig hebt in de Data box (In dit voorbeeld kies ik minder dan keuze).
• Tips: De criteria bevatten: tussen, niet tussen, gelijk aan, niet gelijk aan, groter dan, kleiner dan, groter dan of gelijk aan, kleiner dan of gelijk aan.
• Voer vervolgens de maximaal nummer dat u moet beperken (ik wil dat de tekst niet meer dan 10 tekens lang is).
• Eindelijk, klik OK knop.

3. Nu kunnen de geselecteerde cellen alleen de tekstreeks van minder dan 10 tekens typen.

3.4 Gegevensvalidatielijst (vervolgkeuzelijst)

Met deze krachtige Data Validation functie, kunt u ook snel en gemakkelijk een vervolgkeuzelijst in cellen maken. Doe alsjeblieft als volgt:

1. Selecteer de doelcellen waar u de vervolgkeuzelijst wilt invoegen en klik vervolgens op Data > Data Validation > Data Validation.

2. In de Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Lijst van de Allow keuzelijst.
• In het bron tekstvak, typt u de lijstitems direct gescheiden door komma's. Als u bijvoorbeeld de gebruikersinvoer wilt beperken tot drie keuzes, typt u Niet gestart, In uitvoering, Voltooid, of u kunt een lijst met cellen selecteren met de waarden waarop u de vervolgkeuzelijst wilt invoegen.
• Eindelijk, klik OK knop.

3. Nu is de vervolgkeuzelijst in de cellen gemaakt, zoals hieronder afgebeeld:

Klik voor meer gedetailleerde informatie over de vervolgkeuzelijst ...

4. Geavanceerde aangepaste regels voor gegevensvalidatie

In deze sectie zal ik introduceren hoe u enkele geavanceerde aangepaste regels voor gegevensvalidatie kunt maken om uw soorten problemen op te lossen, zoals: maak validatieformules om alleen cijfers of tekstreeksen toe te staan, alleen unieke waarden, alleen gespecificeerde telefoonnummers, e-mailadressen enzovoort .

4.1 Gegevensvalidatie staat alleen cijfers of teksten toe

 Toestaan ​​dat alleen cijfers worden ingevoerd met de functie Gegevensvalidatie

Ga als volgt te werk om alleen getallen in een celbereik toe te staan:

1. Selecteer een celbereik waarvan u wilt dat alleen cijfers worden ingevoerd.

2. Klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan deze formule in: =ISGETAL(A2) in de Formule tekstvak. (A2 is de eerste cel van het geselecteerde bereik dat u wilt beperken)
• Klik OK knop om dit dialoogvenster te sluiten.

3. Vanaf nu kunnen alleen cijfers in de geselecteerde cellen worden ingevoerd.

Opmerking:: Deze ISNUMMER functie staat alle numerieke waarden in gevalideerde cellen toe, inclusief gehele getallen, decimalen, breuken, datums en tijden.

 Sta toe dat alleen tekstreeksen worden ingevoerd met de functie Gegevensvalidatie

Om celinvoer te beperken tot alleen tekst, kunt u de Data Validation functie met een aangepaste formule op basis van de ISTEKST functie, doe dan als volgt:

1. Selecteer een celbereik waarvan u alleen tekstreeksen wilt invoeren.

2. Klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan deze formule in: =ISTEKST(A2) in de Formule tekstvak. (A2 is de eerste cel van het geselecteerde bereik dat u wilt beperken)
• Klik OK knop om dit dialoogvenster te sluiten.

3. Bij het invoeren van gegevens in de specifieke cellen kunnen nu alleen gegevens in tekstindeling worden toegestaan.

4.2 Gegevensvalidatie staat alleen alfanumerieke waarden toe

Voor sommige doeleinden wil je het invoeren van alfabetten en numerieke waarden toestaan, maar beperk je de speciale tekens zoals ~,%,$, spatie, enz. In deze sectie zullen enkele trucjes voor je worden geïntroduceerd.

 Alleen alfanumerieke waarden toestaan ​​met gegevensvalidatiefunctie

Om de speciale tekens te voorkomen, maar alleen alfanumerieke waarden toe te staan, moet u een aangepaste formule maken in de Data Validation functie, doe dan als volgt:

1. Selecteer een celbereik waarvan u alleen alfanumerieke waarden wilt invoeren.

2. Klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik dat u wilt beperken.

3. Nu mogen alleen de alfabetten en numerieke waarden worden ingevoerd, en de speciale tekens zijn beperkt bij het typen, zoals hieronder afgebeeld:

 Sta alleen alfanumerieke waarden toe met een geweldige functie

Misschien is de bovenstaande formule ingewikkeld voor ons om te begrijpen en te onthouden, hier zal ik een handige functie introduceren - Voorkom typen of Kutools for Excel, met deze functie kunt u deze taak snel en gemakkelijk oplossen.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer een celbereik waarvan u alleen alfanumerieke waarden wilt invoeren.

2. Dan klikken Kutools > Voorkom typen > Voorkom typen, zie screenshot:

3. In de pop-out Voorkom typen dialoogvenster, selecteer Voorkom het typen van speciale tekens optie, zie screenshot:

4. Dan klikken Ok en klik in de volgende promptvensters op Ja > OK om de operatie te beëindigen. Nu zijn in de geselecteerde cellen alleen de alfabetten en numerieke waarden toegestaan, zie screenshot:

4.3 Gegevensvalidatie staat toe dat teksten beginnen of eindigen met specifieke tekens

Als alle waarden in een bepaald bereik moeten beginnen of eindigen met een bepaald teken of subtekenreeks, kunt u gegevensvalidatie gebruiken met een aangepaste formule op basis van de functie EXACT, LINKS, RECHTS of AANTAL.ALS.

 Laat teksten beginnen of eindigen met specifieke tekens met slechts één voorwaarde

Ik wil bijvoorbeeld dat de teksten moeten beginnen of eindigen met "CN" bij het invoeren van de tekstreeksen in specifieke cellen, doe dit als volgt:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan ​​die beginnen of eindigen met bepaalde tekens.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• Begin with: =EXACT(LEFT(A2,2),"CN")
  End with: =EXACT(RIGHT(A2,2),"CN")
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, het nummer 2 is het aantal tekens dat u heeft opgegeven, CN is de tekst waarmee u wilt beginnen of eindigen.

3. Vanaf nu kan alleen de tekenreeks die begint of eindigt met de opgegeven tekens in de geselecteerde cellen worden ingevoerd. Anders wordt er een waarschuwing weergegeven om u eraan te herinneren, zoals hieronder wordt weergegeven:

Opmerking:: Het sterretje * is een jokerteken dat overeenkomt met een of meer tekens.

 Laat teksten beginnen of eindigen met specifieke tekens met meerdere criteria (OF-logica)

Als u bijvoorbeeld wilt dat de teksten beginnen of eindigen met "CN" of "UK", zoals hieronder afgebeeld, moet u nog een exemplaar van EXACT toevoegen door een plusteken (+) te gebruiken. Gelieve te doen met de volgende stappen:

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan ​​die beginnen of eindigen met meerdere criteria.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
  End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, het nummer 2 is het aantal tekens dat u heeft opgegeven, CN als UK zijn de specifieke teksten waarmee u wilt beginnen of eindigen.

3. Nu kan alleen de tekenreeks die begint of eindigt met de opgegeven tekens in de geselecteerde cellen worden ingevoerd.

Opmerking:: Het sterretje * is een jokerteken dat overeenkomt met een of meer tekens.

4.4 Gegevensvalidatie toestaan ​​invoer moet wel/geen specifieke tekst bevatten

In deze sectie zal ik het hebben over het toepassen van de gegevensvalidatie om toe te staan ​​dat waarden wel of niet één specifieke subtekenreeks of een van de vele subtekenreeksen in Excel moeten bevatten.

 Invoer toestaan ​​moet een of een van de vele specifieke teksten bevatten

Invoer toestaan ​​moet één specifieke tekst bevatten

Om items toe te staan ​​die bijvoorbeeld een specifieke tekstreeks bevatten, moeten alle ingevoerde waarden de tekst "KTE" bevatten, zoals hieronder afgebeeld, kunt u de gegevensvalidatie toepassen met een aangepaste formule op basis van de FIND- en ISNUMBER-functies. Doe alsjeblieft als volgt:

1. Selecteer een celbereik dat alleen teksten toestaat die bepaalde tekst bevatten.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen uit de vervolgkeuzelijst Toestaan.
• En voer dan een van de onderstaande formules in de Formule tekstvak.
• =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
  =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, de tekst KTE is de tekstreeks die de items moeten bevatten.

3. Als de ingevoerde waarde nu niet de ontworpen tekst bevat, verschijnt er een waarschuwingsvenster.

Invoer toestaan ​​moet een van de vele specifieke teksten bevatten

De bovenstaande formule werkt alleen voor één tekstreeks, als u een van de vele tekstreeksen nodig heeft om in de cellen te worden toegestaan, zoals in het volgende screenshot wordt getoond, moet u de functies SOMPRODUCT, FIND en ISNUMBER samen gebruiken om een ​​formule te maken.

1. Selecteer een celbereik waarin alleen teksten zijn toegestaan ​​die een van de vele items bevatten.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• Voer vervolgens een van de onderstaande formules in zoals u nodig hebt in de Formule tekstvak.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
• En klik dan OK om het dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, C2: C4 is de lijst met waarden waarvan u wilt dat items een van deze bevatten.

3. En nu kunnen alleen de items die een van de waarden in de specifieke lijst bevatten, worden ingevoerd.

 Toestaan ​​mag niet één of één van de vele specifieke teksten bevatten

Invoer toestaan ​​mag niet één specifieke tekst bevatten

Om te valideren dat de vermeldingen geen specifieke tekst mogen bevatten, bijvoorbeeld om waarden toe te staan ​​die niet de tekst "KTE" in een cel mogen bevatten, kunt u de ISERROR- en FIND-functies gebruiken om een ​​gegevensvalidatieregel te maken. Doe alsjeblieft als volgt:

1. Selecteer een celbereik dat alleen teksten toestaat die bepaalde tekst niet bevatten.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan een van de onderstaande formules in de Formule tekstvak.
• =ISERROR(FIND("KTE",A2))                  (Case sensitive)
  =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, de tekst KTE is de tekstreeks die de items niet mogen bevatten.

3. Nu wordt voorkomen dat de vermeldingen die de specifieke tekst bevatten, worden ingevoerd.

Invoer toestaan ​​mag niet een van de vele specifieke teksten bevatten

Om te voorkomen dat een van de vele tekstreeksen in een lijst wordt ingevoerd zoals hieronder wordt getoond, moet u als volgt doen:

1. Selecteer een celbereik waarvan u wilt dat sommige teksten worden voorkomen.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen Voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• Voer vervolgens de onderstaande formule in de Formule tekstvak.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
• En klik dan OK om het dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formules, A2 is de eerste cel van het geselecteerde bereik, C2: C4 is de lijst met waarden die u wilt voorkomen als items een van deze bevatten.

3. Van nu af aan zullen de vermeldingen die een van de specifieke teksten bevatten, worden voorkomen dat ze worden ingevoerd.

4.5 Gegevensvalidatie staat alleen unieke waarden toe

Als u wilt voorkomen dat dubbele gegevens in een celbereik worden ingevoerd, introduceert deze sectie enkele snelle methoden om deze taak in Excel op te lossen.

 Alleen unieke waarden toestaan ​​met gegevensvalidatiefunctie

Normaal gesproken kan de functie Gegevensvalidatie met een aangepaste formule op basis van de AANTAL.ALS-functie u helpen. Voer de volgende stappen uit:

1. Selecteer de cellen of kolom waarvoor u alleen unieke waarden wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =COUNTIF($A$2:$A$9,A2)=1
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2: A9 is het cellenbereik waarvan u alleen unieke waarden wilt toestaan, en A2 is de eerste cel van het geselecteerde bereik.

3. Nu kunnen alleen unieke waarden worden ingevoerd en er verschijnt een waarschuwingsbericht wanneer dubbele gegevens worden ingevoerd, zie screenshot:

 Alleen unieke waarden toestaan ​​met VBA-code

De volgende VBA-code kan u ook helpen voorkomen dat dubbele waarden worden ingevoerd, doe dit als volgt:

1. Klik met de rechtermuisknop op de bladtab die u alleen unieke waarden wilt toestaan ​​en kies Bekijk code vanuit het contextmenu, in de pop-out Microsoft Visual Basic voor toepassingen venster, kopieer en plak de volgende code in de lege module:

VBA-code: alleen unieke waarden in een celbereik toestaan:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
  Dim xRg As Range, iLong, fLong As Long
  If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then
     Application.EnableEvents = False
     For Each xRg In Target
     With xRg
         If (.Value <> "") Then
          If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then
            iLong = .Interior.ColorIndex
            fLong = .Font.ColorIndex
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 6
            MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel"
            .ClearContents
            .Interior.ColorIndex = iLong
            .Font.ColorIndex = fLong
          End If
       End If
     End With
     Next
     Application.EnableEvents = True
  End If
End Sub

Opmerking:: In de bovenstaande code, de A1: A100 als A: A zijn de cellen in de kolom die u duplicaat wilt voorkomen, wijzig ze dan naar uw behoefte.

2. Sla deze code vervolgens op en sluit deze nu, wanneer u een dubbele waarde invoert in cel A1: A100, verschijnt er een waarschuwingsvenster zoals hieronder afgebeeld:

 Sta alleen unieke waarden toe met een handige functie

Als je Kutools for Excel, Met Voorkom duplicatie functie kunt u gegevensvalidatie instellen om duplicaten voor een celbereik te voorkomen met slechts enkele klikken.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het cellenbereik waarvan u dubbele waarden wilt voorkomen, maar alleen unieke gegevens wilt toestaan.

2. Dan klikken Kutools > Voorkom typen > Voorkom duplicatie, zie screenshot:

3. En er verschijnt een waarschuwingsbericht om u eraan te herinneren dat de gegevensvalidatie wordt verwijderd als u deze functie toepast, klik op Ja en klik in het volgende promptvenster op OK, zie screenshots:

4. Wanneer u nu enkele dubbele gegevens in uw opgegeven cellen invoert, wordt een promptvenster weergegeven om u eraan te herinneren dat de dubbele gegevens niet geldig zijn, zie screenshot:

4.6 Gegevensvalidatie staat alleen hoofdletters / kleine letters / juiste hoofdletters toe

Deze gegevensvalidatie is een krachtige functie, het kan ook helpen om een ​​gebruiker in staat te stellen alleen hoofdletters, kleine letters of juiste letters in een celbereik in te voeren. Gelieve te doen met de volgende stappen:

1. Selecteer het cellenbereik waarvan u alleen hoofdletters, kleine letters of tekst in hoofdletters wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer vervolgens een van de onderstaande formules in die u nodig hebt in de Formule tekstvak.
• =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2))                   (only allow uppercase text)
  =AND(EXACT(A2,LOWER(A2)),ISTEXT(A2))                 (only allow lowercase text)
  =AND(EXACT(A2,PROPER(A2)),ISTEXT(A2))               (only allow proper case text)
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Nu worden alleen de vermeldingen die voldoen aan de regel die u hebt gemaakt, geaccepteerd.

4.7 Gegevensvalidatie staat waarden toe die wel/niet bestaan ​​in een andere lijst

Toestaan ​​dat de waarden al dan niet bestaan ​​in een andere lijst om in een reeks cellen te worden ingevoerd, kan voor de meesten van ons een pijnlijk probleem zijn. In feite kunt u de gegevensvalidatiefunctie gebruiken met een eenvoudige formule op basis van de AANTAL.ALS-functie om ermee om te gaan.

Ik wil bijvoorbeeld dat alleen de waarden in het bereik C2: C4 in een celbereik worden ingevoerd, zoals hieronder afgebeeld, om deze taak op te lossen, doet u dit als volgt:

1. Selecteer het cellenbereik waarop u de gegevensvalidatie wilt toepassen.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer vervolgens een van de onderstaande formules in die u nodig hebt in de Formule tekstvak.
• =COUNTIF($C$2:$C$4,A2)>0                (only allow values exist in another column)
  =COUNTIF($C$2:$C$4,A2)=0                (prevent values exist in another column)
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken, C2: C4 is de lijst met waarden die u wilt voorkomen of toestaan ​​als items er een van zijn.

3. Nu kunnen de items alleen worden ingevoerd die voldoen aan de regel die u hebt gemaakt, andere worden voorkomen.

4.8 Gegevensvalidatie forceert alleen in te voeren telefoonnummerformaat

Wanneer u de informatie van uw bedrijfsmedewerkers invoert, moet één kolom het telefoonnummer typen, om ervoor te zorgen dat telefoonnummers snel en nauwkeurig worden ingevoerd. In dit geval kunt u gegevensvalidatie voor de telefoonnummers instellen. Ik wil bijvoorbeeld alleen dat het telefoonnummer met dit formaat (123) 456-7890 in een werkblad mag worden ingevoerd. In dit gedeelte worden twee snelle trucs geïntroduceerd om deze taak op te lossen.

 Forceer alleen telefoonnummerformaat met gegevensvalidatiefunctie

Ga als volgt te werk om alleen een specifiek telefoonnummerformaat in te voeren:

1. Selecteer de lijst met cellen waarvoor u een specifieke telefoonnummernotatie wilt invoeren en klik met de rechtermuisknop, kies Cellen opmaken zie screenshot vanuit het contextmenu:

2. In de Cellen opmaken dialoogvenster onder het Aantal tab, selecteer Eigen links Categorie keuzelijst en voer vervolgens het telefoonnummerformaat in dat u nodig hebt in het tekstvak Type, bijvoorbeeld, ik zal dit gebruiken (###) ### - #### formaat, zie screenshot:

3. Dan klikken OK om het dialoogvenster te sluiten.

4. Nadat u de cellen hebt opgemaakt, gaat u door met het selecteren van de cellen en opent u vervolgens de Data Validation dialoogvenster door te klikken Data > Data Validation > Data Validation, in het uitgeklapte dialoogvenster, onder de Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan deze formule in = EN (ISGETAL (A2), LEN (A2) = 10) in het tekstvak Formule.
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom waarin u het telefoonnummer wilt valideren.

5. Nu, wanneer u een 10-cijferig nummer invoert, wordt het automatisch geconverteerd naar het specifieke telefoonnummerformaat als u dat nodig heeft, zie screenshots:

Opmerking:: Als het ingevoerde nummer geen 10 cijfers is, verschijnt er een waarschuwingsvenster om u eraan te herinneren, zie screenshot:

 Forceer alleen telefoonnummerformaat met een handige functie

Kutools for Excel's Valideer telefoonnummer functie kan u ook helpen om alleen het telefoonnummerformaat te forceren om met slechts enkele klikken in te voeren.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer de lijst met cellen die alleen een specifiek telefoonnummer toestaan ​​en klik vervolgens op Kutools > Voorkom typen > Valideer telefoonnummer, zie screenshot:

2. In de Telefoonnummer dialoogvenster, selecteer de specifieke telefoonnummernotatie die u nodig hebt of u kunt uw eigen notatie maken door te klikken op de Toevoegen knop, zie screenshot:

3. Klik na het selecteren of instellen van de opmaak van telefoonnummers op OK, nu kan alleen het telefoonnummer met de specifieke opmaak worden ingevoerd, anders verschijnt er een waarschuwingsbericht om u eraan te herinneren, zie screenshot:

4.9 Gegevensvalidatie forceer alleen In te voeren e-mailadressen

Stel dat u meerdere e-mailadressen in een kolom van een werkblad moet typen om te voorkomen dat er onjuiste opmaak van e-mailadressen wordt ingevoerd, normaal gesproken kunt u een regel voor gegevensvalidatie instellen om alleen opmaak van e-mailadressen toe te staan.

 Forceer alleen formaat e-mailadressen met gegevensvalidatiefunctie

Door de functie Gegevensvalidatie met een aangepaste formule te gebruiken, kunt u een regel maken om te voorkomen dat de ongeldige e-mailadressen snel worden ingevoerd. Ga hiervoor als volgt te werk:

1. Selecteer de cellen waarvoor u alleen e-mailadressen wilt invoeren en klik vervolgens op Data > Data Validation > Data Validation.

2. In de pop-out Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan deze formule in = ISGETAL (WEDSTRIJD ("*@*.?*", A2,0)) in de Formule tekstvak.
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Als de ingevoerde tekst nu geen e-mailadres is, verschijnt er een waarschuwingsvenster om u eraan te herinneren, zie screenshot:

 Forceer alleen het formaat van e-mailadressen met een handige functie

Kutools for Excel ondersteunt een geweldige functie - Bevestig e-mailadres, met dit hulpprogramma kunt u de ongeldige e-mailadressen met slechts één klik voorkomen.

Na het installeren van Kutools for Excelgaat u als volgt te werk:

1. Selecteer de cellen waarin u alleen e-mailadressen wilt invoeren en klik vervolgens op Kutools > Voorkom typen > Bevestig e-mailadres. Zie screenshot:

2. En dan kan alleen de opmaak van e-mailadressen worden ingevoerd, anders verschijnt er een waarschuwingsbericht om u eraan te herinneren, zie screenshot:

4.10 Gegevensvalidatie dwingt alleen IP-adressen in te voeren

In deze sectie zal ik enkele snelle trucs introduceren om gegevensvalidatie in te stellen om alleen IP-adressen in een reeks cellen te accepteren.

 Forceer alleen formaat van IP-adressen met functie voor gegevensvalidatie

Sta alleen toe dat IP-adressen in een specifiek celbereik worden getypt. Ga als volgt te werk:

1. Selecteer de cellen waarvan u alleen het IP-adres wilt invoeren en klik vervolgens op Data > Data Validation > Data Validation.

2. In de pop-out Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Als u nu een ongeldig IP-adres in de cel invoert, verschijnt er een waarschuwingsbericht om u eraan te herinneren zoals hieronder afgebeeld:

 Forceer alleen formaat van IP-adressen met VBA-code

Hier kan de volgende VBA-code ook helpen om alleen IP-adressen in te voeren en andere invoer te beperken, doe als volgt:

1. Klik met de rechtermuisknop op de bladtab en klik Bekijk code vanuit het contextmenu, in de opening Microsoft Visual Basic voor toepassingen venster, kopieer de onderstaande VBA-code erin.

VBA-code: valideer cellen om alleen IP-adres te accepteren

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by ExtendOffice
Dim xArrIp() As String
Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer
If Intersect(Target, Range("A2:A10")) Is Nothing Then
    Exit Sub
Else
    If Target = "" Then
        Exit Sub
    End If
    xArrIp = Split(Target.Text, ".")
    If UBound(xArrIp) <> 3 Then
        GoTo EIP
    Else
    xIntIP1 = CInt(xArrIp(0))
    xIntIP2 = CInt(xArrIp(1))
    xIntIP3 = CInt(xArrIp(2))
    xIntIP4 = CInt(xArrIp(3))
    If (xIntIP1 < 1) Or (xIntIP1 > 255) _
    Or (xIntIP2 < 1) Or (xIntIP2 > 255) _
    Or (xIntIP3 < 1) Or (xIntIP3 > 255) _
    Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then
    GoTo EIP
     End If
    End If
End If
Exit Sub
EIP:
    MsgBox "Please enter correct IP address"
    Target = ""
End Sub

Opmerking:: In de bovenstaande code, A2: A10 is het celbereik dat u alleen IP-adressen wilt accepteren.

2. Sla deze code vervolgens op en sluit deze, nu kunnen alleen de juiste IP-adressen in de specifieke cellen worden ingevoerd.

 Forceer alleen de indeling van IP-adressen met een eenvoudige functie

Als je Kutools for Excel geïnstalleerd in uw werkmap, zijn Valideer IP-adres functie kan u ook helpen om deze taak op te lossen.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer de cellen waarin u alleen IP-adressen wilt invoeren en klik vervolgens op Kutools > Voorkom typen > Valideer IP-adres. Zie screenshot:

2. Na het toepassen van deze functie kan nu alleen het IP-adres worden ingevoerd, anders verschijnt er een waarschuwingsbericht om u eraan te herinneren, zie screenshot:

4.11 Gegevensvalidatie beperkt waarden die de totale waarde overschrijden

Stel dat u een maandelijkse onkostendeclaratie hebt en het budgettotaal $ 18000 is, nu moet u dat het totale bedrag in de onkostenlijst het vooraf ingestelde totaal van $ 18000 niet overschrijdt, zoals hieronder wordt weergegeven. In dit geval kunt u een gegevensvalidatieregel maken met behulp van de SOM-functie om te voorkomen dat de som van waarden een vooraf ingesteld totaal overschrijdt.

1. Selecteer de lijst met cellen waar u de waarden wilt beperken.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =SUM($B$2:$B$7)<=18000
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, B2: B7 is het celbereik waarvan u de invoer wilt beperken.

3. Als nu de waarden in het bereik B2:B7 worden ingevoerd en het totaal van de waarden minder is dan $ 18000, is de validatie geslaagd. Als een waarde ervoor zorgt dat het totaal boven de $ 18000 uitkomt, verschijnt er een waarschuwingsvenster om u eraan te herinneren.

4.12 Gegevensvalidatie beperken celinvoer op basis van een andere cel

Wanneer u gegevensinvoer in een lijst met cellen wilt beperken op basis van de waarde in een andere cel, kan de functie Gegevensvalidatie ook helpen om deze taak op te lossen. Als cel C1 bijvoorbeeld de tekst "Ja" is, mag het bereik A2:A9 alles invoeren, maar als cel C1 een andere tekst is, mag niets worden ingevoerd in het bereik A2:A9, zoals hieronder getoonde screenshots :

Ga als volgt te werk om dit op te lossen:

1. Selecteer de lijst met cellen waar u de waarden wilt beperken.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =$C$1="Yes"
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, C1 bevat de cel de specifieke tekst die u wilt gebruiken en de tekst "Ja” is de tekst waarop u cellen wilt beperken op basis van, verander ze naar uw behoefte.

3. Als cel C1 nu de tekst "Ja" heeft, kan alles worden ingevoerd in het bereik A2:A9, als cel C1 andere tekst heeft, kunt u geen waarde invoeren, zie onderstaande demo:

4.13 Bij gegevensvalidatie kunnen alleen weekdagen of weekenden worden ingevoerd

Als u alleen weekdagen (van maandag tot vrijdag) of weekends (zaterdag en zondag) wilt invoeren in een lijst met cellen, Data Validation kan je ook helpen, doe het met de volgende stappen:

1. Selecteer de lijst met cellen waar u de weekdagen of weekdagen wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan een van de onderstaande formules in de Formule tekstvak als je nodig hebt.
• =WEEKDAY(A2,2)<6                      (allow only weekdays)
  =WEEKDAY(A2,2)>5                      (allow only weekends)
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Nu kunt u alleen de datum van de weekdag of het weekend in de specifieke cellen invoeren op basis van uw behoefte.

4.14 Gegevensvalidatie staat ingevoerde datum toe op basis van de datum van vandaag

Soms moet u mogelijk toestaan ​​dat alleen de datums groter of kleiner dan vandaag worden ingevoerd in een lijst met cellen. De Data Validation functie met de VANDAAG functie kan u een plezier doen. Doe alsjeblieft als volgt:

1. Selecteer de lijst met cellen waarin u alleen de toekomstige datum (datum groter dan vandaag) wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =A2>Today()
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Nu kunnen alleen de datums groter dan de datum van vandaag in de cellen worden ingevoerd, anders verschijnt er een waarschuwingsbericht om u eraan te herinneren, zie screenshot:

4.15 Gegevensvalidatie staat ingevoerde tijd toe op basis van huidige tijd

Als u bijvoorbeeld gegevens wilt valideren op basis van de huidige tijd, kunt u alleen tijden voor of na de huidige tijd in de cellen typen. U kunt uw eigen formule voor gegevensvalidatie maken, doe dit als volgt:

1. Selecteer de lijst met cellen waarin u alleen de tijden voor of na de huidige tijd wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Time van de Allow keuzelijst.
• Kies dan minder dan om alleen tijden vóór de huidige tijd toe te staan, of groter dan om tijden na de huidige tijd toe te staan ​​zoals u nodig hebt van de Data laten vallen.
• En dan, in de Eindtijd or Begintijd voert u de onderstaande formule in:
• =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken.

3. Nu kunnen alleen de tijden voor of na de huidige tijd in de specifieke cellen worden ingevoerd.

4.16 Gegevensvalidatie de datum van een specifiek of lopend jaar

Om alleen datums in een bepaald jaar of huidig ​​jaar in te voeren, kunt u gegevensvalidatie gebruiken met een aangepaste formule op basis van de functie JAAR.

1. Selecteer de lijst met cellen waarin u alleen de datums van een bepaald jaar wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Eigen van de Allow keuzelijst.
• En voer dan de onderstaande formule in de Formule tekstvak.
• =YEAR(A2)=2020
• Klik OK knop om dit dialoogvenster te sluiten.

Opmerking:: In de bovenstaande formule, A2 is de eerste cel van de kolom die u wilt gebruiken, 2020 is het jaarnummer dat u wilt beperken.

3. En dan kunnen alleen de datums in het jaar 2020 worden ingevoerd, zo niet, dan verschijnt er een waarschuwingsvenster zoals hieronder afgebeeld:

4.17 Gegevensvalidatie de datum in de huidige week of maand

Als u wilt dat de gebruiker de datums van de huidige week of maand in specifieke cellen kan invoeren, introduceert deze sectie enkele formules voor het omgaan met deze taak in Excel.

 Sta toe om de datum van de huidige week in te voeren

1. Selecteer de lijst met cellen waarin u alleen de datums van de huidige week wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Datum van de Allow keuzelijst.
• En kies dan tussen van de Data laten vallen.
• In het Startdatum tekstvak, voer deze formule in: =VANDAAG()-WEEKDAG(VANDAAG(),3)
• In het Einddatum tekstvak, voer deze formule in: =VANDAAG()-WEEKDAG(VANDAAG(),3)+6
• Eindelijk, klik OK knop.

3. Dan kunnen alleen de datums binnen de huidige week worden ingevoerd, andere datums worden voorkomen zoals hieronder afgebeeld:

 Sta toe om de datum van de huidige maand in te voeren

Ga als volgt te werk om alleen de datums van de huidige maand in te voeren:

1. Selecteer de lijst met cellen waarin u alleen de datums van de huidige maand wilt invoeren.

2. Dan klikken Data > Data Validation > Data Validation, in de pop uit Data Validation dialoogvenster onder het Instellingen tabblad, voer de volgende bewerkingen uit:

• kies Datum van de Allow keuzelijst.
• En kies vervolgens uit de Data laten vallen.
• In het Startdatum tekstvak, voer deze formule in: =DATUM(JAAR(VANDAAG()),MAAND(VANDAAG()),1)
• In het Einddatum tekstvak, voer deze formule in: =DATUM(JAAR(VANDAAG()),MAAND(VANDAAG()),DAG(DATUM(JAAR(VANDAAG()),MAAND(VANDAAG())+1,1)-1))
• Eindelijk, klik OK knop.

3. Vanaf nu kunnen alleen de datums van de huidige maand worden ingevoerd in de geselecteerde cellen.

5. Hoe de gegevensvalidatie in Excel bewerken?

Volg de onderstaande stappen om de bestaande regel voor gegevensvalidatie te bewerken of te wijzigen:

1. Selecteer een van de cellen met de gegevensvalidatieregel.

2. Dan klikken Data > Data Validation > Data Validation naar de Data Validation dialoogvenster, in het vak, bewerk of wijzig de regels naar uw behoefte en vink vervolgens aan: Pas deze wijzigingen toe op alle andere cellen met dezelfde instellingen optie om deze nieuwe regel toe te passen op alle andere cellen met de oorspronkelijke validatiecriteria. Zie screenshot:

3. Klikken OK om de wijzigingen op te slaan.

6. Hoe cellen met gegevensvalidatie in Excel zoeken en selecteren?

Als u meerdere regels voor gegevensvalidatie in uw werkblad hebt gemaakt, moet u nu de cellen zoeken en selecteren die de regels voor gegevensvalidatie hebben toegepast, de Ga naar speciaal commando kan u helpen bij het selecteren van alle soorten gegevensvalidatie of een specifiek type gegevensvalidatie.

1. Activeer het werkblad dat u wilt zoeken en selecteer de cellen met gegevensvalidatie.

2. Dan klikken Home > Zoek en selecteer > Ga naar speciaal, zie screenshot:

3. In de Ga naar speciaal dialoogvenster, selecteer Gegevensvalidatie > Alles, zie screenshot:

4. En alle cellen met gegevensvalidatie zijn in één keer geselecteerd in het huidige werkblad.

Tips: Als u slechts één specifiek type gegevensvalidatie wilt selecteren, selecteert u eerst een cel die de bepaalde gegevensvalidatie bevat die u wilt weten, ga dan naar de Ga naar speciaal dialoogvenster en kies Gegevensvalidatie > Dezelfde.

7. Hoe kopieer ik de gegevensvalidatieregel naar andere cellen?

Stel dat u een gegevensvalidatieregel hebt gemaakt voor een lijst met cellen, en nu moet u dezelfde gegevensvalidatieregel toepassen op andere cellen. In plaats van de regel opnieuw te maken, kunt u de bestaande regel snel en gemakkelijk naar andere cellen kopiëren en plakken.

1. Klik om een ​​cel te selecteren met de validatieregel die u wilt gebruiken en druk vervolgens op CTRL + C om het te kopiëren.

2. Selecteer vervolgens de cellen die u wilt valideren, om meerdere niet-aangrenzende cellen te selecteren, houd de . ingedrukt Ctrl toets terwijl u de cellen selecteert.

3. En klik vervolgens met de rechtermuisknop op de selectie, selecteer plakken speciaal optie, zie screenshot:

4. In de plakken speciaal dialoogvenster, selecteer Validatie optie, zie screenshot:

5. Klikken OK knop, nu wordt de validatieregel gekopieerd naar de nieuwe cellen.

8. Hoe gegevensvalidatie gebruiken om ongeldige vermeldingen in Excel te omcirkelen?

Soms moet u regels voor gegevensvalidatie maken voor bestaande gegevens. In dit geval kunnen er ongeldige gegevens in het cellenbereik verschijnen. Hoe de ongeldige gegevens controleren en wijzigen? In Excel kunt u de Omcirkel ongeldige gegevens functie om de ongeldige gegevens te markeren met een rode cirkel.

Om de ongeldige gegevens die u nodig hebt te omcirkelen, moet u de Data Validation functie om een ​​regel voor het gegevensbereik in te stellen. Gelieve te doen met de volgende stappen:

1. Selecteer het gegevensbereik waarvoor u ongeldige gegevens wilt omcirkelen.

2. Dan klikken Data > Data Validation > Data Validation, in de Data Validation dialoogvenster, stel de validatieregel in op uw behoefte, hier valideer ik bijvoorbeeld de waarden groter dan 500, zie screenshot:

3. Dan klikken OK om het dialoogvenster te sluiten. Klik na het instellen van de gegevensvalidatieregel op Data > Data Validation > Omcirkel ongeldige gegevens, dan zijn alle ongeldige waarden die kleiner zijn dan 500 omcirkeld met een rood ovaal. Zie schermafbeeldingen:

Opmerkingen:

• 1. Zodra u ongeldige gegevens corrigeert, verdwijnt de rode cirkel automatisch.
• 2. Dit Omcirkel ongeldige gegevens functie kan maximaal 255 cellen omcirkelen. Wanneer u de huidige werkmap opslaat, worden alle rode cirkels verwijderd.
• 3. Deze cirkels kunnen niet worden afgedrukt.
• 4. U kunt de rode cirkels ook verwijderen door op te klikken Data > Data Validation > Wis validatiecirkels.

9. Hoe gegevensvalidatie in Excel verwijderen?

Om de gegevensvalidatieregels uit een celbereik, het huidige werkblad of de hele werkmap te verwijderen, kunnen de volgende methoden u een plezier doen.

 Verwijder gegevensvalidatie in het geselecteerde bereik met gegevensvalidatiefunctie

1. Selecteer de cellen met gegevensvalidatie die u wilt verwijderen.

2. Dan klikken Data > Data Validation > Data Validation, in het uitgeklapte dialoogvenster, onder de Instellingen tab, klik Wis alles knop, zie screenshot:

3. Dan klikken OK om dit dialoogvenster te sluiten. En de gegevensvalidatieregel die op het geselecteerde bereik is toegepast, is in één keer verwijderd.

Tips: Om de gegevensvalidatie van het huidige werkblad te verwijderen, selecteert u eerst het hele blad en past u vervolgens bovenstaande stappen toe.

 Verwijder gegevensvalidatie in het geselecteerde bereik met een handige functie

Als je Kutools for Excel, het is Beperkingen voor gegevensvalidatie wissen functie kan ook helpen om de gegevensvalidatieregels uit het geselecteerde bereik of het hele werkblad te verwijderen.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Selecteer het celbereik of het hele werkblad bevat de gegevensvalidatie die u wilt verwijderen.

2. Dan klikken Kutools > Voorkom typen > Beperkingen voor gegevensvalidatie wissen, zie screenshot:

3. Klik in het uitgeklapte promptvenster op OK, en de regel voor gegevensvalidatie is naar behoefte gewist.

 Verwijder gegevensvalidatie van alle werkbladen met VBA-code

Om de gegevensvalidatieregels uit de hele werkmap te verwijderen, zullen bovenstaande methoden tijdrovend zijn als er veel werkbladen zijn. Hier kan de onderstaande code u helpen om deze taak snel af te handelen.

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Dan klikken Invoegen > Moduleen plak de volgende macro in het Module venster.

VBA-code: verwijder regels voor gegevensvalidatie in alle werkbladen:

Sub RemoveDataValidation() 'Bijwerken door Extendoffice
  Dim xwsh als werkblad voor elke xwsh in ActiveWorkbook.Worksheets xwsh.Cells.Validation.Delete Next xwsh End Sub

3. Druk vervolgens op F5 sleutel om deze code uit te voeren en alle regels voor gegevensvalidatie zijn onmiddellijk uit de hele werkmap verwijderd.

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
• Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
• Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
• Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
• Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
• Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
• Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
• Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
• Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...

Lees verder... Gratis proefperiode van 30 dagen met volledige functies... Aankoop... 

---

Office-tabblad - brengt interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

• Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!

Lees verder... Gratis proefperiode van 30 dagen met volledige functies... Aankoop...