Excel -datavalidering: lägg till, använd, kopiera och ta bort datavalidering i Excel

I Excel är funktionen Datavalidering ett kraftfullt verktyg som du kan använda för att begränsa vad en användare kan skriva in i en cell. Du kan till exempel ange regler för att begränsa längden på text, begränsa poster till specifika format, tvinga fram unika värden eller se till att text börjar eller slutar med vissa tecken. Dessa funktioner hjälper till att upprätthålla dataintegritet och minska fel i dina kalkylblad.

Denna handledning utforskar hur du lägger till, använder och tar bort datavalidering i Excel. Den täcker både grundläggande och avancerade operationer och ger detaljerad steg-för-steg-guide för att hjälpa dig att effektivt tillämpa den här funktionen på dina uppgifter.

1. Vad är datavalidering i Excel?

Smakämnen Datagransknings -funktionen kan hjälpa dig att begränsa inmatningsinnehåll i ditt kalkylblad. Normalt kan du skapa några valideringsregler för att förhindra eller tillåta att någon typ av data anges i en lista med valda celler.

Några grundläggande användningsområden för datavalideringsfunktionen:

1. Vilket värde som helst: ingen validering utförs, du kan mata in något i de angivna cellerna. 2. Hela värdet: endast heltal är tillåtna. 3. Decimal: tillåter heltal såväl som decimaler. 4. Lista: endast värden från den fördefinierade listan får matas in eller väljas. Värdena visas i en rullgardinsmeny. 5. Datum: endast datum är tillåtna. 6. tid: endast tider är tillåtna. 7. Textlängd: endast tillåta angiven textlängd. 8. Anpassad: skapa anpassade formelregler för att validera användarnas inmatning.

2. Hur lägger jag till datavalidering i Excel?

I Excel -kalkylblad kan du lägga till datavalidering med följande steg:

1. Markera en lista med celler där du vill ställa in datavalidering och klicka sedan på Data > Datagransknings > Datagransknings, se skärmdump:

2. I Datagransknings under dialogrutan Inställningar fliken, skapa dina egna valideringsregler. i kriterierutorna kan du ange någon av följande typer:

• Värden: Skriv nummer i kriterierutorna direkt;
• Cellreferens: Referera till en cell i kalkylbladet eller ett annat kalkylblad;
• Formler: Skapa mer komplexa formler som villkor.

Som ett exempel kommer jag att skapa en regel som tillåter att endast heltal mellan 100 och 1000 anges, här ställer du in kriterierna enligt nedanstående skärmdump:

3. När du har konfigurerat villkoren kan du gå till Input Meddelande or Felavisering fliken för att ställa in inmatningsmeddelandet eller felmeddelandet för valideringscellerna som du vill. (Om du inte vill ställa in varningen klickar du på OK att slutföra direkt.)

3.1) Lägg till inmatningsmeddelande (valfritt):

Du kan skapa ett meddelande som visas när du väljer en cell som innehåller datavalidering. Det här meddelandet hjälper till att påminna användaren om vad de kan mata in i cellen.

Gå till Input Meddelande fliken och gör följande:

Kontrollera Visa inmatningsmeddelande när cell är vald alternativ; Ange önskad titel och påminnelse i motsvarande fält; Klicka OK för att stänga denna dialogruta.

När du nu väljer en validerad cell visas en meddelanderuta så här:

3.2) Skapa meningsfulla felmeddelanden (valfritt):

Förutom att skapa inmatningsmeddelandet kan du också visa felvarningar när ogiltiga data matas in i en cell med datavalidering.

Gå till Felavisering fliken på Datagransknings dialogrutan, gör så här:

Kontrollera Visa felvarning efter att ogiltiga data har angetts alternativ; I Stil rullgardinsmenyn, välj en önskad varningstyp du behöver: Stopp (standard): Denna varningstyp hindrar användare från att ange ogiltiga data. Varning: Varnar användare för att uppgifterna är ogiltiga, men hindrar inte att de matas in. Information: Informerar användare om en ogiltig datainmatning. Ange önskad titel och varningsmeddelande i motsvarande fält; Klicka OK för att stänga dialogrutan.

När ett ogiltigt värde anges visas en varningsruta, som visas på skärmdumpen nedan:

Sluta alternativ: Klicka Försök igen för att ange ett värde igen eller Annullera för att ignorera posten.

Varning alternativ: Klicka Ja för att acceptera den ogiltiga posten, Nej att ändra det, eller Annullera att kassera den.

Information alternativ: Klicka OK för att acceptera den ogiltiga posten eller Annullera att kassera den.

Anmärkningar: Om du inte anger ditt eget anpassade meddelande i Felavisering rutan, en standard Sluta varningsruta kommer att visas, som visas nedan:

3. Grundläggande exempel för datavalidering

När du använder denna datavalideringsfunktion finns det 8 inbyggda alternativ för att ställa in datavalidering. Såsom: valfritt värde, heltal och decimaler, datum och tid, lista, textlängd och anpassad formel. I det här avsnittet kommer vi att diskutera hur du använder några av de inbyggda alternativen i Excel?

3.1 Datavalidering för heltal och decimaler

1. Välj en lista med celler där du bara vill tillåta heltal eller decimaler och klicka sedan på Data > Datagransknings > Datagransknings.

2. I Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välj motsvarande objekt Heltal or Decimal i Tillåt rullgardinsmeny.
• Och välj sedan ett av kriterierna i Data box (I det här exemplet väljer jag mellan alternativ).
• tips: Kriterierna innehåller: mellan, inte mellan, lika med, inte lika med, större än, mindre än, större än eller lika med, mindre än eller lika med.
• Ange sedan Minsta och Maximal värden du behöver (i det här fallet siffror mellan 0 och 100).
• Äntligen klickar du på OK knapp.

3. Nu kan endast heltal mellan 0 och 100 anges i de markerade cellerna.

3.2 Datavalidering för datum och tid

För att validera specifikt datum eller tid som ska anges, är det enkelt att använda detta Datagransknings, gör så här:

1. Välj en lista med celler där du bara vill tillåta specifika datum eller tider och klicka sedan på Data > Datagransknings > Datagransknings.

2. I Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välj motsvarande objekt Datum or Tid i Tillåt rullgardinsmeny.
• Och välj sedan ett av kriterierna i Data box (här väljer jag större än alternativ).
• tips: Kriterierna innehåller: mellan, inte mellan, lika med, inte lika med, större än, mindre än, större än eller lika med, mindre än eller lika med.
• Ange sedan Startdatum du behöver (jag vill att datumen är större än 8/20/2021).
• Äntligen klickar du på OK knapp.

3. Nu kan endast datum som är större än 8-20-2021 anges i de valda cellerna.

3.3 Datavalidering för textlängd

Om du behöver begränsa antalet tecken som kan skrivas in i en cell. Till exempel för att begränsa innehållet till högst 10 tecken för ett visst intervall, detta Datagransknings kan också göra dig en tjänst.

1. Markera en lista med celler där du vill begränsa textlängden och klicka sedan på Data > Datagransknings > Datagransknings.

2. I Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Textlängd från Tillåt rullgardinsmeny.
• Och välj sedan ett av kriterierna i Data box (I det här exemplet väljer jag mindre än alternativ).
• tips: Kriterierna innehåller: mellan, inte mellan, lika med, inte lika med, större än, mindre än, större än eller lika med, mindre än eller lika med.
• Ange sedan Maximal antal du behöver begränsa (jag vill att textlängden inte ska vara mer än 10 tecken).
• Äntligen klickar du på OK knapp.

3. Nu tillåter de markerade cellerna endast textsträngar med färre än 10 tecken

3.4 Datavalideringslista (rullgardinsmeny)

Med denna kraftfulla Datagransknings -funktionen kan du snabbt och enkelt skapa rullgardinsmenyn i celler. Gör så här:

1. Markera målcellerna där du vill infoga listrutan och klicka sedan på Data > Datagransknings > Datagransknings.

2. I Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Lista från Tillåt listrutan.
• I Källa textrutan, skriv listobjekten direkt åtskilda med kommatecken. Till exempel, för att begränsa användarinmatningen till tre alternativ, skriv Not Started, In Progress, Completed, eller så kan du välja en lista med celler som innehåller de värden som du vill infoga rullgardinsmenyn baserat på.
• Äntligen klickar du på OK knapp.

3. Nu har listrutan skapats i cellerna enligt nedanstående skärmdump:

Klicka för att få mer detaljerad information om rullgardinsmenyn ...

4. Avancerade anpassade regler för datavalidering

I det här avsnittet kommer jag att introducera hur man gör några avancerade regler för validering av anpassade data för att lösa dina problem, till exempel: skapa valideringsformler för att endast tillåta nummer eller textsträngar, bara unika värden, endast angivna telefonnummer, e -postadresser och så vidare .

4.1 Datavalidering tillåter endast nummer eller texter

 Tillåt bara att mata in siffror med datavalideringsfunktionen

Om du bara vill tillåta siffror i ett cellområde gör du så här:

1. Välj ett cellområde som du vill att endast siffror ska matas in.

2. Klicka Data > Datagransknings > Datagransknings. I Datagransknings dialogrutan som visas under Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Och skriv sedan in denna formel: = ISNUMBER (A2) i Formel textruta. (A2 är den första cellen i det valda intervallet du vill begränsa)
• Klicka OK för att stänga denna dialog.

3. Från och med nu kan endast siffror anges i de markerade cellerna.

Anmärkningar: Detta ISNUMBER funktion tillåter alla numeriska värden i validerade celler, inklusive heltal, decimaler, bråk, datum och tider.

 Tillåt att endast textsträngar matas in med datavalideringsfunktionen

Om du vill begränsa cellposter till text bara kan du använda Datagransknings funktion med en anpassad formel baserad på ISTEXT funktion, gör så här:

1. Välj ett cellintervall som du vill att endast textsträngar ska anges.

2. Klicka Data > Datagransknings > Datagransknings. I Datagransknings dialogrutan som visas under Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Och skriv sedan in denna formel: = ISTEXT (A2) i Formel textruta. (A2 är den första cellen i det valda intervallet du vill begränsa)
• Klicka OK för att stänga denna dialog.

3. När du matar in data i de specifika cellerna kan endast textformatdata tillåtas.

4.2 Datavalidering tillåter endast alfanumeriska värden

För vissa ändamål kanske du vill tillåta endast alfabet och numeriska värden samtidigt som du begränsar specialtecken som ~, %, $ eller mellanslag. Det här avsnittet introducerar några användbara metoder.

 Tillåt endast alfanumeriska värden med datavalideringsfunktionen

För att förhindra specialtecken och endast tillåta alfanumeriska värden, skapa en anpassad formel i Datagransknings fungerar genom att följa dessa steg:

1. Välj ett cellområde som du vill att endast alfanumeriska värden ska anges.

2. Klicka Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda intervallet du vill begränsa.

3. Nu är det bara tillåtet att ange alfabet och numeriska värden, och specialtecknen kommer att begränsas när du skriver enligt nedanstående skärmdump:

 Tillåt endast alfanumeriska värden med en fantastisk funktion

Formeln ovan kan tyckas komplicerad att förstå och komma ihåg. Här introducerar jag en praktisk funktion som heter Förhindra skrivning från Kutools för Excel, vilket avsevärt förenklar denna uppgift.

1. Välj ett cellområde som du vill att endast alfanumeriska värden ska anges.

2. Klicka sedan Kutools > Förhindra skrivning > Förhindra skrivning, se skärmdump:

3. I poppade ut Förhindra skrivning dialogrutan väljer du Förhindra att du skriver in specialtecken alternativ, se skärmdump:

4. Klicka sedan Ok -knappen, och klicka i följande promptboxar Ja > OK för att avsluta operationen. I de markerade cellerna är det bara alfabet och numeriska värden som är tillåtna, se skärmdump:

4.3 Datavalidering gör att texter börjar eller slutar med specifika tecken

Om alla värden i ett visst intervall måste börja eller sluta med ett specifikt tecken eller delsträng kan du använda datavalidering med en anpassad formel baserad på funktionerna EXACT, LEFT, RIGHT eller COUNTIF.

 Tillåt att texter börjar eller slutar med specifika tecken med endast ett villkor

Om du till exempel vill att textinmatningar i specifika celler ska börja eller sluta med "CN", följ dessa steg:

1. Välj ett cellområde som endast tillåter texter som börjar eller slutar med vissa tecken.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• Begin with: =EXACT(LEFT(A2,2),"CN")
  End with: =EXACT(RIGHT(A2,2),"CN")
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda intervallet, numret 2 är antalet tecken du har angett, CN är texten du vill börja eller sluta med.

3. Från och med nu kan endast textsträngen som börjar eller slutar med de angivna tecknen matas in i de markerade cellerna. Annars kommer en varning att visas, som visas i skärmdumpen nedan:

Anmärkningar: Asterisken * är ett jokertecken som matchar ett eller flera tecken.

 Tillåt att texter börjar eller slutar med specifika tecken med flera kriterier (ELLER logik)

Om du till exempel vill att textinlägg ska börja eller sluta med "CN" eller "UK" enligt skärmbilden nedan, måste du lägga till ytterligare en instans av EXAKT genom att använda ett plustecken (+). Vänligen gör med följande steg:

1. Välj ett cellområde som bara tillåter texter som börjar eller slutar med flera kriterier.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
  End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK")
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda intervallet, numret 2 är antalet tecken du har angett, CN och UK är de specifika texter du vill börja eller sluta med.

3. Nu är det bara textsträngen som börjar eller slutar med de angivna tecknen som kan anges i de markerade cellerna.

Anmärkningar: Asterisken * är ett jokertecken som matchar ett eller flera tecken.

4.4 Datavalidering tillåter poster måste innehålla / får inte innehålla specifik text

I det här avsnittet kommer jag att prata om hur du använder datavalidering för att tillåta värden måste innehålla en specifik delsträng eller en av många underordnade delar i Excel.

 Tillåt poster måste innehålla en eller en av många specifika texter

Tillåt poster måste innehålla en specifik text

För att tillåta poster som innehåller en specifik textsträng, till exempel, bör alla inmatade värden innehålla texten "KTE" enligt nedanstående skärmdump, du kan tillämpa datavalideringen med en anpassad formel baserad på funktionerna HITTA och ISNUMBER. Gör så här:

1. Välj ett cellområde som bara tillåter texter som innehåller viss text.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från rullgardinsmenyn Tillåt.
• Ange sedan en av nedanstående formler i Formel textruta.
• =ISNUMBER(FIND("KTE",A2))             (Case sensitive)
  =ISNUMBER(SEARCH("KTE",A2))         (Non case sensitive)
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda området, texten KTE är textsträngen som posterna måste innehålla.

3. Nu, när det angivna värdet inte innehåller den önskade texten, kommer en varningsruta att dyka upp.

Tillåt poster måste innehålla en av många specifika texter

Ovanstående formel fungerar bara för en textsträng, om du behöver någon av flera textsträngar för att tillåtas i cellerna, som visas i följande skärmdump, bör du använda funktionerna SUMMAPRODUKT, SÖK och ISNUMMER tillsammans för att skapa en formel.

1. Välj ett cellområde som bara tillåter texter som innehåller något av många objekt.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Ange sedan en av nedanstående formler som du behöver i Formel textruta.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0                        (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0                   (Non case sensitive)
• Och klicka sedan på OK för att stänga dialogrutan.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda intervallet, C2: C4 är listan över värden som du vill tillåta poster innehåller någon av dem.

3. Och nu är det bara posterna som innehåller något av värdena i den specifika listan som kan anges.

 Tillåt poster får inte innehålla en eller en av många specifika texter

Tillåt poster får inte innehålla en specifik text

För att validera inmatningarna får inte innehålla specifik text, till exempel för att tillåta värden som inte får innehålla texten “KTE” i en cell kan du använda funktionerna FEL och HITTA för att skapa en datavalideringsregel. Gör så här:

1. Välj ett cellområde som bara tillåter texter som inte innehåller viss text.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Ange sedan en av nedanstående formler i Formel textruta.
• =ISERROR(FIND("KTE",A2))                  (Case sensitive)
  =ISERROR(SEARCH("KTE",A2))                  (Non case sensitive)
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda området, texten KTE är textsträngen posterna inte får innehålla.

3. Nu kommer poster som innehåller den specifika texten att förhindras från att matas in.

Tillåt poster får inte innehålla en av många specifika texter

För att förhindra att någon av många textsträngar i en lista skrivs in, som visas i skärmdumpen nedan, följ dessa steg:

1. Välj ett cellområde som du vill att vissa texter ska förhindras.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar fliken, gör följande:

• Välja Custom från Tillåt listrutan.
• Ange sedan formeln nedan i Formel textruta.
• =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0                     (Case sensitive)
  =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0                 (Non case sensitive)
• Och klicka sedan på OK för att stänga dialogrutan.

Anmärkningar: I ovanstående formler, A2 är den första cellen i det valda intervallet, C2: C4 är listan över värden du vill förhindra om poster innehåller någon av dem.

3. Från och med nu kommer poster som innehåller någon av de specifika texterna att förhindras från att matas in.

4.5 Datavalidering tillåter endast unika värden

Om du vill förhindra att duplicerade data matas in i ett cellintervall, introducerar det här avsnittet några snabba metoder för att lösa den här uppgiften i Excel.

 Tillåt endast unika värden med datavalideringsfunktionen

Normalt kan datavalideringsfunktionen med en anpassad formel baserad på funktionen COUNTIF hjälpa dig, gör följande steg:

1. Markera de celler eller kolumner som du bara vill att unika värden ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =COUNTIF($A$2:$A$9,A2)=1
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2: A9 är det cellintervall som du bara vill tillåta unika värden, och A2 är den första cellen i det valda intervallet.

3. Nu kan endast unika värden anges, och ett varningsmeddelande visas om dubblettdata matas in, som visas i skärmdumpen nedan:

 Tillåt endast unika värden med VBA -kod

Följande VBA -kod kan också hjälpa dig att förhindra att dubblettvärden matas in, gör så här:

1. Högerklicka på arkfliken som du vill tillåta endast unika värden och välj Visa kod från snabbmenyn i popup-fönstret Microsoft Visual Basic för applikationer kopiera och klistra in följande kod i den tomma modulen:

VBA -kod: Tillåt endast unika värden i ett cellområde:

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

Anmärkningar: I ovanstående kod är A1: A100 och A: A är cellerna i kolumnen som du vill förhindra dubbletter, vänligen ändra dem till ditt behov.

2. Spara och stäng sedan denna kod. Nu, när du anger ett dubblettvärde i cellerna A1:A100, visas en varningsruta, som visas i skärmdumpen nedan:

 Tillåt endast unika värden med en praktisk funktion

Om du Kutools för Excel, med dess Förhindra duplicering funktionen kan du snabbt ställa in datavalidering för att förhindra dubbletter i en rad celler med bara några få klick.

1. Välj det cellintervall som du vill förhindra dubblettvärdena men endast tillåta unik data.

2. Klicka sedan Kutools > Förhindra skrivning > Förhindra duplicering, se skärmdump:

3. Ett varningsmeddelande kommer att visas som meddelar dig att datavalidering kommer att tas bort när du använder den här funktionen. Klick Ja, och klicka i den efterföljande uppmaningsrutan OK, som visas i skärmdumparna nedan:

4. Nu, när du anger några dubblettdata i dina angivna celler, visas en snabbruta som påminner dig om att dubblettdatan inte är giltig, se skärmdump:

4.6 Datavalidering möjliggör endast stora / små bokstäver / rätt bokstäver

Funktionen för datavalidering är ett kraftfullt verktyg som kan hjälpa användare att använda versaler, gemener eller korrekta bokstäver i ett antal celler. Vänligen gör med följande steg:

1. Välj det cellintervall som du bara vill att stora eller små bokstäver ska skrivas in.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan en av nedanstående formler som du behöver i Formel textruta.
• =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)
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu kommer endast de poster som överensstämmer med regeln du skapade att accepteras.

4.7 Datavalidering tillåter värden som finns / inte finns i en annan lista

Att tillåta eller förhindra värden baserat på deras närvaro i en annan lista kan vara en utmanande uppgift för många användare. Faktum är att du kan använda datavalideringsfunktionen med en enkel formel baserad på COUNTIF-funktionen för att hantera det.

Till exempel vill jag att endast värdena i intervallet C2: C4 ska anges i ett cellintervall enligt nedanstående skärmdump, för att lösa det här jobbet, gör så här:

1. Välj det cellintervall som du vill använda datavalideringen.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan en av nedanstående formler som du behöver i Formel textruta.
• =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)
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda, C2: C4 är listan över värden du vill förhindra eller tillåta om poster är en av dem.

3. Nu kan endast de poster som överensstämmer med regeln du skapade matas in. andra kommer att förhindras.

4.8 Datavalidering tvingar endast in telefonnummerformat

När du matar in informationen från dina företagsanställda måste en kolumn skriva in telefonnumret, för att du ska kunna mata in telefonnummer snabbt och exakt, i det här fallet kan du ställa in datavalidering för telefonnumren. Till exempel vill jag bara att telefonnumret som detta format (123) 456-7890 ska tillåtas komma in i ett kalkylblad, det här avsnittet kommer att introducera två snabba tricks för att lösa denna uppgift.

 Tvinga endast telefonnummerformat med datavalideringsfunktion

Följ dessa steg för att tillåta att endast ett specifikt telefonnummerformat anges:

1. Markera listan med celler som du vill att specifikt telefonnummerformat ska anges och högerklicka, välj Formatera celler från snabbmenyn, se skärmdump:

2. I Formatera celler under dialogrutan Antal fliken, välj Custom till vänster Kategori listrutan och mata sedan in det telefonnummerformat du behöver i textrutan Typ, till exempel använder jag det här (###) ### - #### format, se skärmdump:

3. Klicka sedan OK för att stänga dialogrutan.

4. Efter att ha formaterat cellerna markerar du dem igen och öppnar Datagransknings dialogrutan genom att klicka Data > Datagransknings > Datagransknings. I popup-dialogrutan, under Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och sedan, ange denna formel = OCH (ISNUMBER (A2), LENN (A2) = 10) i textrutan Formel.
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill validera telefonnumret.

5. Nu, när du anger ett 10-siffrigt nummer, kommer det automatiskt att konverteras till det specifika telefonnummerformatet efter behov, se skärmdumpar:

Anmärkningar: Om det inmatade numret inte är 10 siffror kommer ett varningsmeddelande att dyka upp för att påminna dig, se skärmdump:

 Tvinga endast telefonnummerformat med en användbar funktion

Kutools för ExcelÄr Validera telefonnummer funktionen kan också hjälpa dig att bara tvinga in telefonnummerformat med bara flera klick.

1. Markera listan över celler som endast tillåter specifikt telefonnummer och klicka sedan på Kutools > Förhindra skrivning > Validera telefonnummer, se skärmdump:

2. I Telefonnummer dialogrutan, välj det specifika telefonnummerformat du behöver eller så kan du skapa din egen formatering genom att klicka på Lägg till knapp, se skärmdump:

3. När du har valt eller ställt in telefonnummerformateringen klickar du på OK, nu kan bara telefonnumret med den specifika formateringen anges, annars kommer ett varningsmeddelande att dyka upp för att påminna dig, se skärmdump:

4.9 Datavalidering tvingar bara till att e -postadresser anges

Om du antar att du måste skriva flera e -postadresser i en kolumn i ett kalkylblad, för att förhindra att felaktiga e -postadresser formateras, kan du normalt ställa in en datavalideringsregel för att endast tillåta formatering av e -postadresser.

 Tvinga endast e -postadresser med datavalideringsfunktion

Genom att använda datavalideringsfunktionen med en anpassad formel kan du snabbt skapa en regel för att förhindra att ogiltiga e-postadresser anges. Följ dessa steg:

1. Markera de celler som du vill att endast e -postadresser ska anges och klicka sedan på Data > Datagransknings > Datagransknings.

2. I poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och sedan, ange denna formel = ISNUMBER (MATCH ("*@*.?*", A2,0)) i Formel textruta.
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu, om den angivna texten inte stämmer överens med e-postadressens format, visas en varningsruta för att meddela dig, se skärmdump:

 Tvinga endast e -postadresser med en praktisk funktion

Kutools för Excel stöder en fantastisk funktion - Validera e-postadress, med det här verktyget kan du förhindra ogiltiga e -postadresser med bara ett klick.

1. Markera de celler som du bara tillåter att e -postadresser anges och klicka sedan på Kutools > Förhindra skrivning > Validera e-postadress. Se skärmdump:

2. Och sedan är det bara tillåtet att ange e -postadressformatering, annars kommer en varningsmeddelande att dyka upp för att påminna dig, se skärmdump:

4.10 Datavalidering tvingar endast in IP -adresser

I det här avsnittet kommer jag att introducera några snabba tricks för att ställa in datavalidering för att endast acceptera IP -adresser i ett antal celler.

 Tvinga endast IP -adressformat med datavalideringsfunktion

Följ dessa steg för att tillåta att endast IP-adresser anges i ett specifikt cellintervall:

1. Markera de celler som du vill att endast IP -adress ska anges och klicka sedan på Data > Datagransknings > Datagransknings.

2. I poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
• Klicka OK för att stänga denna dialog.

Notera: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu, om en ogiltig IP-adress skrivs in i cellen, kommer en varningsruta att visas, som visas på skärmdumpen nedan:

 Tvinga endast IP -adressformat med VBA -kod

Följande VBA -kod kan också hjälpa till att endast tillåta IP -adresser och begränsa andra inmatningar, gör så här:

1. Högerklicka på arkfliken och klicka Visa kod från snabbmenyn i öppningen Microsoft Visual Basic för applikationer kopiera nedanstående VBA-kod till den.

VBA -kod: validera celler för att bara acceptera IP -adress

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

Anmärkningar: I ovanstående kod, A2: A10 är det cellintervall du bara vill acceptera IP -adresser.

2. Spara och stäng sedan denna kod. Nu kan endast giltiga IP-adresser anges i de angivna cellerna.

 Tvinga endast IP -adressformat med en enkel funktion

Om du Kutools för Excel installerat i din arbetsbok, dess Verifiera IP-adress funktionen kan också hjälpa dig att lösa denna uppgift.

1. Markera de celler som du bara tillåter att IP -adresser anges och klicka sedan på Kutools > Förhindra skrivning > Verifiera IP-adress. Se skärmdump:

2. Efter att ha använt den här funktionen, nu är det bara IP -adressen som kan anges, annars kommer en varningsmeddelande att dyka upp för att påminna dig, se skärmdump:

4.11 Datavalidering begränsar värden som överstiger det totala värdet

Om du antar att du har en månatlig utgiftsrapport och den totala budgeten är $18000 XNUMX. Du måste se till att det totala beloppet i utgiftslistan inte överstiger denna förinställda summa, som visas i skärmdumpen nedan. I det här fallet kan du skapa en datavalideringsregel genom att använda SUM-funktionen för att förhindra att summan av värden överskrids en förinställd totalsumma.

1. Välj listan över celler där du vill att värdena ska begränsas.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =SUM($B$2:$B$7)<=18000
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, B2: B7 är det cellintervall du vill begränsa poster.

3. När nu värdena i intervallet B2:B7 skrivs in, om summan av värdena är mindre än $18000 18,000, godkänns valideringen. Om något värde gör att summan överstiger XNUMX XNUMX USD, visas en varningsruta för att meddela dig.

4.12 Datavalidering begränsar cellinmatning baserat på en annan cell

När du vill begränsa datainmatningar i en lista med celler baserat på värdet i en annan cell, kan funktionen Datavalidering hjälpa till att lösa detta jobb också. Till exempel, om cellen C1 är texten "Ja", tillåter området A2:A9 alla inmatningar. Men om cell C1 innehåller annan text, är poster i intervallet A2:A9 begränsade, som visas i skärmdumparna nedan:

För att lösa denna lösning, gör så här:

1. Välj listan över celler där du vill att värdena ska begränsas.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =$C$1="Yes"
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, C1 innehåller cellen den specifika texten du vill använda och texten "Ja”Är texten du vill begränsa celler baserat på, ändra dem till ditt behov.

3. Nu, om cell C1 har texten ”Ja”, kan allt anges i intervallet A2: A9, om cell C1 har annan text kommer du inte att kunna ange något värde, se nedan demo:

4.13 Datavalidering gör att endast vardagar eller helger kan anges

Om du bara behöver vardagar (från måndag till fredag) eller helger (lördag och söndag) för att anges i en lista med celler, visas Datagransknings kan också hjälpa dig, gör följande steg:

1. Välj listan över celler där du vill att vardagar eller vardagar ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Ange sedan en av nedanstående formler i Formel textruta som du behöver.
• =WEEKDAY(A2,2)<6                      (allow only weekdays)
  =WEEKDAY(A2,2)>5                      (allow only weekends)
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu kan du bara ange veckodags- eller helgdatum i de angivna cellerna baserat på ditt val.

4.14 Datavalidering tillåter inmatat datum baserat på dagens datum

Ibland kan du behöva tillåta att endast datum som är större eller mindre än idag anges i en cellista. De Datagransknings funktion med I DAG funktion kan göra dig en tjänst. Gör så här:

1. Välj listan över celler där du bara vill att det framtida datumet (datum större än idag) ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =A2>Today()
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu kan endast datum senare än idag anges i cellerna. Annars visas en varningsruta för att meddela dig, se skärmdump:

4.15 Datavalidering tillåter inmatad tid baserat på aktuell tid

Om du vill validera data baserat på aktuell tid, till exempel, kan endast tider före eller efter aktuell tid skrivas in i cellerna. Du kan skapa din egen datavalideringsformel, gör så här:

1. Markera listan över celler där du bara vill att tiderna före eller efter aktuell tid ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Tid från Tillåt listrutan.
• Sedan Välj mindre än att endast tillåta tider före den aktuella tiden, eller större än för att tillåta tider efter den aktuella tiden som du behöver från Data falla ner.
• Och sedan, i Stopptid or Starttid ange följande formel:
• =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda.

3. Nu kan bara tiderna före eller efter den aktuella tiden anges i de specifika cellerna.

4.16 Datavalidering datum för specifikt eller aktuellt år

För att endast tillåta datum under ett visst år eller aktuellt år kan du använda datavalidering med en anpassad formel baserad på YEAR -funktionen.

1. Välj listan över celler där du bara vill att datumen under ett visst år ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Custom från Tillåt listrutan.
• Och ange sedan formeln nedan i Formel textruta.
• =YEAR(A2)=2020
• Klicka OK för att stänga denna dialog.

Anmärkningar: I ovanstående formel, A2 är den första cellen i kolumnen du vill använda, 2020 är det årtal du vill begränsa.

3. Och sedan kan bara datumen år 2020 anges, om inte, kommer en varningsmeddelande att dyka upp som nedanstående skärmdump visas:

4.17 Datavalidering datum i aktuell vecka eller månad

Om du vill tillåta användare att ange datum för den aktuella veckan eller månaden i specifika celler, introducerar det här avsnittet några formler för att utföra denna uppgift i Excel.

 Tillåt att ange datum för aktuell vecka

1. Välj listan över celler där du bara vill att datumen för den aktuella veckan ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Datum från Tillåt listrutan.
• Och sedan, välj mellan från Data falla ner.
• I Startdatum textruta, ange denna formel: = TODAY ()-WEEKDAY (TODAY (), 3)
• I Slutdatum textruta, ange denna formel: = TODAY ()-WEEKDAY (TODAY (), 3) +6
• Äntligen klickar du på OK knapp.

3. Sedan kan endast datumen inom den aktuella veckan anges, andra datum kommer att förhindras enligt nedanstående skärmdump:

 Tillåt att ange datum för aktuell månad

För att endast tillåta datum för den aktuella månaden, gör följande:

1. Välj listan över celler där du bara vill att datumen för den aktuella månaden ska anges.

2. Klicka sedan Data > Datagransknings > Datagransknings, i poppade ut Datagransknings under dialogrutan Inställningar gör följande åtgärder:

• Välja Datum från Tillåt listrutan.
• Och välj sedan mellan Data falla ner.
• I Startdatum textruta, ange denna formel: = DATE (YEAR (TODAY ()), MONTH (TODAY ()), 1)
• I Slutdatum textruta, ange denna formel: = DATE (YEAR (TODAY ()), MONTH (TODAY ()), DAY (DATE (YEAR (TODAY ()), MONTH (TODAY ())+1,1) -1))
• Äntligen klickar du på OK knapp.

3. Från och med nu kan endast datum inom den aktuella månaden anges i de valda cellerna.

5. Hur redigerar jag datavalidering i Excel?

Följ stegen nedan för att redigera eller ändra en befintlig regel för datavalidering:

1. Välj någon av cellerna med datavalideringsregeln.

2. Klicka sedan Data > Datagransknings > Datagransknings för att gå till Datagransknings i rutan, redigera eller ändra reglerna efter dina behov i rutan och kontrollera sedan Tillämpa dessa ändringar på alla andra celler med samma inställningar alternativ för att tillämpa denna nya regel på alla andra celler med de ursprungliga valideringskriterierna. Se skärmdump:

3. Klicka OK för att spara ändringarna.

6. Hur hittar och väljer du celler med datavalidering i Excel?

Om du har skapat flera datavalideringsregler i ditt kalkylblad måste du nu hitta och välja de celler som tillämpade datavalideringsreglerna, Gå till Special kommandot kan hjälpa dig att välja alla typer av datavalidering eller specifik typ av datavalidering.

1. Aktivera det kalkylblad som du vill hitta och välj cellerna med datavalidering.

2. Klicka sedan Hem > Hitta och välj > Gå till Special, se skärmdump:

3. I Gå till Special dialogrutan väljer du Datavalidering > Alla, se skärmdump:

4. Alla celler med datavalidering har nu valts i det aktuella kalkylbladet.

tips: Om du vill välja en specifik typ av datavalidering, välj först en cell som innehåller den önskade datavalideringen och gå sedan till Gå till Special dialogrutan och välj Datavalidering > Samma.

7. Hur kopierar jag datavalideringsregeln till andra celler?

Om du antar att du har skapat en datavalideringsregel för en lista med celler, och nu måste du tillämpa samma datavalideringsregel för andra celler. Istället för att skapa regeln igen kan du snabbt och enkelt kopiera och klistra in den befintliga regeln i andra celler.

1. Klicka för att markera en cell med valideringsregeln du vill använda och tryck sedan på Ctrl + C att kopiera den.

2. Markera sedan de celler du vill validera, markera flera icke-intilliggande celler, tryck på och håll ned ctrl när du markerar cellerna.

3. Och högerklicka sedan på markeringen, välj Klistra in special alternativ, se skärmdump:

4. I Klistra in special dialogrutan väljer du Validering alternativ, se skärmdump:

5. Klicka OK -knappen, nu kopieras valideringsregeln till de nya cellerna.

8. Hur använder jag datavalidering för att ringa in ogiltiga poster i Excel?

Ibland kan du behöva skapa datavalideringsregler för befintliga data, i det här fallet kan vissa ogiltiga data visas i cellintervallet. Hur kontrollerar man ogiltiga data och ändrar dem? I Excel kan du använda Cirkel ogiltiga data funktion för att markera ogiltiga data med en röd cirkel.

För att ringa in den ogiltiga informationen du behöver, bör du tillämpa Datagransknings funktion för att ställa in en regel för dataområdet. Gör med följande steg:

1. Välj det dataområde som du vill ringa in ogiltiga data i.

2. Klicka sedan Data > Datagransknings > DatagranskningsI Datagransknings dialogrutan, ställ in valideringsregeln till ditt behov, till exempel här, jag validerar värdena större än 500, se skärmdump:

3. Klicka sedan OK för att stänga dialogrutan. När du har ställt in datavalideringsregeln klickar du på Data > Datagransknings > Cirkel ogiltiga data, då har alla ogiltiga värden som är mindre än 500 inringats med röd oval. Se skärmdumpar:

Anmärkningar:

• 1. Så snart du korrigerar en ogiltig data försvinner den röda cirkeln automatiskt.
• 2. Detta Cirkel ogiltiga data funktionen kan bara cirkulera högst 255 celler. När du sparar den aktuella arbetsboken kommer alla röda cirklar att tas bort.
• 3. Dessa cirklar går inte att skriva ut.
• 4. Du kan också ta bort de röda cirklarna genom att klicka Data > Datagransknings > Rensa valideringscirklar.

9. Hur tar jag bort datavalidering i Excel?

Använd följande metoder för att ta bort datavalideringsregler från ett cellintervall, det aktuella kalkylbladet eller hela arbetsboken.

 Ta bort datavalidering i valt område med datavalideringsfunktion

1. Markera cellerna med datavalidering som du vill ta bort.

2. Klicka sedan Data > Datagransknings > Datagransknings, i dialogrutan som dök upp, under Inställningar fliken, klicka Rensa alla knapp, se skärmdump:

3. Klicka sedan OK knappen för att stänga denna dialogruta. Och datavalideringsregeln som tillämpas på det valda intervallet har tagits bort direkt.

tips: För att ta bort datavalideringen från det aktuella kalkylbladet, välj först hela bladet först och applicera sedan ovanstående steg.

 Ta bort datavalidering i valt område med en praktisk funktion

Om du Kutools för Excel, dess Rensa begränsningar för datavalidering funktionen kan också hjälpa till att ta bort datavalideringsreglerna från valt intervall eller hela kalkylbladet.

1. Välj cellintervall eller hela kalkylbladet innehåller den datavalidering du vill ta bort.

2. Klicka sedan Kutools > Förhindra skrivning > Rensa begränsningar för datavalidering, se skärmdump:

3. Klicka på i rutan som visas OK, och datavalideringsregeln rensas efter behov.

 Ta bort datavalidering från alla kalkylblad med VBA -kod

För att ta bort datavalideringsreglerna från hela arbetsboken kommer ovanstående metoder att vara tidskrävande om det finns massor av kalkylblad. Här kan koden nedan hjälpa dig att hantera denna uppgift snabbt.

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka sedan Insert > Modulernaoch klistra in följande makro i Modulerna fönster.

VBA -kod: Ta bort datavalideringsregler i alla kalkylblad:

Sub RemoveDataValidation()
'Updateby Extendoffice
  Dim xwsh As Worksheet
  For Each xwsh In ActiveWorkbook.Worksheets
    xwsh.Cells.Validation.Delete
  Next xwsh
End Sub

3. Tryck sedan på F5 nyckel för att köra den här koden, och alla datavalideringsregler har tagits bort från hela arbetsboken omedelbart.