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

I excel är datavalidering en användbar funktion som du kan använda för att begränsa vad en användare kan skriva in i en cell. Till exempel kan datavalideringsfunktionen hjälpa dig att begränsa längden på textsträngar, eller text som börjar / slutar med specifika tecken, eller unika värden som ska anges och så vidare.

Denna handledning, vi kommer att prata om hur du lägger till, använder och tar bort datavalidering i Excel, några grundläggande och avancerade funktioner för denna funktion kommer också att demonstreras i detaljer.

---

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.

Och nu, när du anger ett ogiltigt värde, kommer meddelandefältet att dyka upp som nedanstående skärmdump visas:

Sluta alternativ: Du kan klicka Försök igen för att skriva ett annat värde eller Annullera för att ta bort posten.

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

Information alternativ: Klicka OK för att ange den ogiltiga posten eller Annullera för att ta bort posten.

Anmärkningar: Om du inte anger ditt eget anpassade meddelande i Felavisering rutan, en standard Sluta varningsmeddelandet visas enligt 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 du behöver 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 (jag vill ha siffrorna mellan 0 och 1 00).
• Äntligen klickar du på OK knapp.

3. Nu får endast hela siffrorna från 0 till 100 matas in i dina valda celler.

---

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 du behöver 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 är det bara tillåtet att ange datum som är större än 8/20/2021 i dina valda celler.

---

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 du behöver 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 bara att skriva textsträngen mindre ä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 poppade ut Datagransknings under dialogrutan 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 poppade ut Datagransknings under dialogrutan 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 vill du bara tillåta alfabet och numeriska värden att matas in, men begränsa specialtecknen som ~,%, $, mellanslag, etc., det här avsnittet kommer att introducera några knep för dig.

 Tillåt endast alfanumeriska värden med datavalideringsfunktionen

För att förhindra specialtecknen men bara tillåta alfanumeriska värden, bör du skapa en anpassad formel i Datagransknings funktion, gör så här:

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

Kan vara att ovanstående formel är komplicerad för oss att förstå och komma ihåg, här kommer jag att introducera en praktisk funktion - Förhindra skrivning of Kutools för Excel, med den här funktionen kan du snabbt lösa detta jobb med lätthet.

När du har installerat Kutools för Excel, gör så här:

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 ska börja eller sluta med ett visst tecken eller en delsträng kan du använda datavalidering med en anpassad formel baserad på funktionen EXAKT, VÄNSTER, HÖGER eller RÄTT.

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

Till exempel vill jag att texterna måste börja eller sluta med "CN" när du anger textsträngarna i specifika celler, gör så här:

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 börjar eller slutar endast textsträngen med de angivna tecknen i de markerade cellerna. Annars kommer en varningsmeddelande att dyka upp för att påminna dig enligt nedanstående skärmdump:

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)

Till exempel, om du vill att texterna måste börja eller sluta med "CN" eller "UK" enligt nedanstående skärmdump, måste du lägga till ytterligare en instans av EXAKT genom att använda ett plustecken (+). 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. När det angivna värdet inte innehåller den utformade texten kommer en varningsmeddelande 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 många textsträngar för att tillåtas i cellerna enligt följande skärmdump, bör du använda funktionerna SUMPRODUCT, FIND och ISNUMBER 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 de poster som innehåller den specifika texten att förhindras att matas in.

---

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

För att förhindra att en av många textsträngar i en lista anges som nedanstående skärmdump, bör du göra så här:

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 förhindras inmatningar som innehåller någon av de specifika texterna.

---

4.5 Datavalidering tillåter endast unika värden

Om du vill förhindra att dubblettdata matas in i ett cellintervall, kommer det här avsnittet att introducera några snabba metoder för att lösa denna uppgift 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 är det bara unika värden som kan matas in, och ett varningsmeddelande kommer att dyka upp när dubblettdata matas in, se skärmdump:

---

 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 bara vill tillåta unika värden och välj Visa kod från snabbmenyn, i poppade ut 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 sedan och stäng den här koden, nu, när du anger duplikatvärde i cell A1: A100, dyker en varningsmeddelande ut som nedanstående skärmdump visas:

---

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

Om du har Kutools för Excel, med dess Förhindra duplicering -funktionen kan du ställa in datavalidering för att förhindra dubbletter för ett antal celler med bara flera klick.

När du har installerat Kutools för Excel, gör så här:

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. Och ett varningsmeddelande kommer att dyka upp för att påminna dig om att datavalideringen kommer att tas bort om du använder den här funktionen, klicka Ja och i följande snabbruta klickar du på OK, se skärmdumpar:

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

Denna datavalidering är en kraftfull funktion, den kan också hjälpa till att låta en användare bara skriva stora, små eller rätta bokstäver i ett cellområde. 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 är det bara posterna som överensstämmer med den regel du skapade kommer att accepteras.

---

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

Att tillåta att värdena existerar eller inte finns i en annan lista som ska anges i ett antal celler kan vara ett smärtsamt problem för de flesta av oss. Faktum är att du kan använda datavalideringsfunktionen med en enkel formel baserad på COUNTIF -funktionen för att hantera den.

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 posterna endast överensstämma med den regel du skapade kan anges, 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

Om du bara vill tillåta ett specifikt telefonnummerformat ska du göra så här:

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, fortsätt markera cellerna och öppna sedan Datagransknings dialogrutan genom att klicka Data > Datagransknings > Datagransknings, i dialogrutan som dök upp, 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.

När du har installerat Kutools för Excel, gör så här:

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 skapa en regel för att förhindra att ogiltiga e -postadresser matas in snabbt, gör så här:

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. Om den inmatade texten inte är e -postadressformat kommer en varningsmeddelande att dyka upp för att påminna 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.

När du har installerat Kutools för Excel, gör så här:

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

Tillåt att bara IP -adresser skrivs in i ett specifikt cellintervall. Gör så här:

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. Om du nu anger en ogiltig IP -adress i cellen kommer en varningsmeddelande att dyka upp för att påminna dig enligt nedanstående skärmdump:

---

 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 den här koden, nu är det bara de rätta IP -adresserna som kan matas in i de specifika cellerna.

---

 Tvinga endast IP -adressformat med en enkel funktion

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

När du har installerat Kutools för Excel, gör så här:

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ånadsutgiftsrapport och budgetsumman är $ 18000, nu behöver du det totala beloppet i utgiftslistan inte överstiger det förinställda totalt $ 18000 enligt nedanstående skärmdump. I det här fallet kan du skapa en datavalideringsregel med hjälp av SUMM -funktionen för att förhindra att summan av värden överskrids en förinställd summa.

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. Nu, när du anger värdena i intervallet B2: B7, om summan av värdena är mindre än $ 18000, godkänns valideringen. Om något värde gör att summan överstiger 18000 XNUMX dollar kommer en varningsmeddelande att dyka upp för att påminna dig.

---

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

När du vill begränsa datainmatningar i en celllista baserat på värdet i en annan cell kan funktionen Datavalidering också hjälpa till att lösa detta jobb. Till exempel, om cellen C1 är texten "Ja", får intervallet A2: A9 ange vad som helst, men om cellen C1 är en annan text, tillåter ingenting att anges i intervallet A2: A9 enligt nedanstående skärmdumpar :

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 datum för vardag eller helg i de specifika cellerna baserat på ditt behov.

---

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 bara datumen som är större än dagens datum anges i cellerna, annars dyker en varningsmeddelande ut för att påminna 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ändaren att kunna ange datum för aktuell vecka eller månad i specifika celler, kommer det här avsnittet att presentera några formler för att hantera 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 välj sedan 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 är det bara datumen för den aktuella månaden som kan matas in i de valda cellerna.

---

5. Hur redigerar jag datavalidering i Excel?

För att redigera eller ändra den befintliga regeln för datavalidering, följ stegen nedan:

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. Och alla celler med datavalidering har valts ut samtidigt i det aktuella kalkylbladet.

---

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?

För att ta bort datavalideringsreglerna från ett cellintervall, aktuellt kalkylblad eller hela arbetsboken kan följande metoder göra dig en tjänst.

 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 har 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.

När du har installerat Kutools för Excel, gör så här:

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 dök upp OKoch datavalideringsregeln har rensats 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.