Excel -datavalideringsguide

Introduktion

Datavalidering är en funktion i Excel som används för att styra vad en användare kan skriva in i en cell. Du kan till exempel använda datavalidering för att se till att ett värde är ett tal mellan 1 och 6, se till att ett datum inträffar under de närmaste 30 dagarna eller se till att en textpost är mindre än 25 tecken.

Datavalidering kan helt enkelt visa ett meddelande till en användare som berättar vad som är tillåtet enligt nedan:

Datavalidering kan också stoppa ogiltig användarinmatning. Om en produktkod till exempel misslyckas med validering kan du visa ett meddelande så här:

Dessutom kan datavalidering användas för att förse användaren med ett fördefinierat val i en rullgardinsmeny:

Detta kan vara ett bekvämt sätt att ge en användare exakt de värden som uppfyller kraven.

Kontroller av datavalidering

Datavalidering implementeras via regler som definieras i Excel -användargränssnittet på fliken Data i menyfliksområdet.

hur man sammanfogar text och datum i Excel

Viktig begränsning

Det är viktigt att förstå att datavalidering lätt kan besegras. Om en användare kopierar data från en cell utan validering till en cell med datavalidering förstörs (eller ersätts) valideringen. Datavalidering är ett bra sätt att låta användarna veta vad som är tillåtet eller förväntat, men det är inte ett idiotsäkert sätt att garantera input.

Definiera datavalideringsregler

Datavalidering definieras i ett fönster med tre flikar: Inställningar, Inmatningsmeddelande och Felvarning:

På fliken Inställningar anger du valideringskriterier. Det finns ett antal inbyggda valideringsregler med olika alternativ, eller så kan du välja Anpassad och använda din egen formel för att validera inmatning enligt nedan:

Fliken Inmatningsmeddelande definierar ett meddelande som ska visas när en cell med valideringsregler väljs. Detta inmatningsmeddelande är helt valfritt. Om inget inmatningsmeddelande är inställt visas inget meddelande när en användare väljer en cell med datavalidering validerad. Inmatningsmeddelandet har ingen effekt på vad användaren kan skriva in - det visar helt enkelt ett meddelande för att låta användaren veta vad som är tillåtet eller förväntat.

Fliken Felvarning styr hur validering verkställs. Till exempel, när stilen är inställd på 'Stop', utlöser ogiltiga data ett fönster med ett meddelande och inmatningen är inte tillåten.

Användaren ser ett meddelande så här:

När stilen är inställd på Information eller varning visas en annan ikon med ett anpassat meddelande, men användaren kan ignorera meddelandet och ange värden som inte klarar validering. Tabellen nedan sammanfattar beteendet för varje felvarningsalternativ.

Valideringsalternativ för data

När en datavalideringsregel skapas finns det åtta alternativ tillgängliga för att validera användarinmatning:

Något värde - ingen validering utförs. Obs! Om datavalidering tidigare tillämpades med ett angivet inmatningsmeddelande visas meddelandet fortfarande när cellen väljs, även när valfritt värde är valt.

Heltal - endast heltal är tillåtna. När alternativet för heltal väljs blir andra alternativ tillgängliga för att ytterligare begränsa inmatning. Till exempel kan du kräva ett heltal mellan 1 och 10.

Decimal - fungerar som heltalet, men tillåter decimalvärden. Till exempel, med alternativet Decimal konfigurerat för att tillåta värden mellan 0 och 3, är alla värden som .5, 2.5 och 3.1 tillåtna.

Lista - endast värden från en fördefinierad lista är tillåtna. Värdena presenteras för användaren som en rullgardinsmenykontroll. Tillåtna värden kan hårdkodas direkt på fliken Inställningar eller anges som ett område i kalkylbladet.

Datum - endast datum är tillåtna. Du kan till exempel kräva ett datum mellan 1 januari 2018 och 31 december 2021 eller ett datum efter 1 juni 2018.

Tid - endast tider är tillåtna. Du kan till exempel kräva en tid mellan 09:00 och 17:00, eller bara tillåta tider efter 12:00.

Textlängd - validerar inmatning baserat på antal tecken eller siffror. Till exempel kan du kräva kod som innehåller 5 siffror.

Beställnings - validerar användarinmatning med hjälp av en anpassad formel. Med andra ord kan du skriva din egen formel för att validera inmatning. Anpassade formler utökar kraftigt alternativen för datavalidering. Du kan till exempel använda en formel för att säkerställa att ett värde är versaler, ett värde innehåller 'xyz' eller ett datum är en vardag under de kommande 45 dagarna.

Inställningsfliken innehåller också två kryssrutor:

Ignorera tomt - säger åt Excel att inte validera celler som inte innehåller något värde. I praktiken tycks denna inställning endast utföra kommandot 'cirkel ogiltiga data'. När den är aktiverad cirkuleras inte tomma celler även om de misslyckas med validering.

Tillämpa dessa ändringar på andra celler med samma inställningar - denna inställning uppdaterar validering som tillämpas på andra celler när den matchar (original) validering av cellen (erna) som redigeras.

funktionen dcount kan räkna antingen numeriska fält eller textfält.

Obs! Du kan också manuellt markera alla celler med datavalidering som används med Go To + Special, som förklaras nedan.

Enkel rullgardinsmeny

Du kan tillhandahålla en rullgardinsmeny med alternativ genom att hårdkoda värden i inställningsrutan eller välja ett område på kalkylbladet. Till exempel, för att begränsa poster till åtgärderna 'KÖP', 'HÅLL' eller 'SÄLJ' kan du ange dessa värden separerade med kommatecken enligt nedan:

När den används på en cell i kalkylbladet fungerar rullgardinsmenyn så här:

Ett annat sätt att tillhandahålla värden till en rullgardinsmeny är att använda en kalkylbladreferens. Till exempel, med storlekar (dvs. små, medelstora, etc.) i intervallet F3: F6, kan du ange detta intervall direkt i fönstret för inställning av datavalidering:

Observera att intervallet anges som en absolut adress för att förhindra att den ändras när datavalideringen tillämpas på andra celler.

Tips: Klicka på den lilla pilikonen längst till höger i källfältet för att göra ett val direkt i kalkylbladet så att du inte behöver ange intervallet manuellt.

Du kan också använda namngivna intervall för att ange värden. Till exempel, med det namngivna intervallet som kallas 'storlekar' för F3: F7, kan du ange namnet direkt i fönstret, med början med ett likhetstecken:

Namngivna intervall är automatiskt absoluta, så de ändras inte eftersom datavalideringen tillämpas på olika celler. Om namngivna intervall är nya för dig, den här sidan har en bra översikt och ett antal relaterade tips .

Tips - om du använder en tabell för rullgardinsvärden kommer Excel att fortsätta att expandera eller dra ihop tabellen automatiskt när rullgardinsvärden läggs till eller tas bort. Med andra ord kommer Excel automatiskt att hålla rullgardinsmenyn synkroniserad med värden i tabellen när värden ändras, läggs till eller tas bort. Om du är ny i Excel -tabeller kan du se en demo i den här videon på tabellgenvägar.

Datavalidering med en anpassad formel

Datavalideringsformler måste vara logiska formler som returnerar TRUE när input är giltig och FALSE när input är ogiltig. Till exempel, för att tillåta valfritt tal som inmatning i cell A1, kan du använda funktionen ISNUMBER i en formel så här:

 
= ISNUMBER (A1)

Om en användare anger ett värde som 10 i A1 returnerar ISNUMBER TRUE och datavalidering lyckas. Om de anger ett värde som 'apple' i A1, returnerar ISNUMBER FALSE och datavalidering misslyckas.

För att aktivera datavalidering med en formel, välj 'Anpassad' på fliken Inställningar och ange sedan en formel i formelfältet som börjar med ett likhetstecken (=) som vanligt.

Felsökningsformler

Excel ignorerar datavalideringsformler som returnerar fel. Om en formel inte fungerar och du inte kan ta reda på varför, ställ in dummyformler för att se till att formeln fungerar som du förväntar dig. Dummyformler är helt enkelt datavalideringsformler som anges direkt i kalkylbladet så att du enkelt kan se vad de returnerar. Skärmen nedan visar ett exempel:

När du får dummyformeln att fungera som du vill, kopierar du den och klistrar in den i datavalideringsformelområdet.

Om denna dummyformelidé är förvirrande för dig, kolla på denna videon , som visar hur man använder dummyformler för att perfekta villkorliga formateringsformler. Konceptet är exakt detsamma.

Exempel på datavalideringsformel

Möjligheterna för anpassade formler för datavalidering är praktiskt taget obegränsade. Här är några exempel för att ge dig lite inspiration:

För att bara tillåta 5 teckenvärden som börjar med 'z' kan du använda:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Denna formel returnerar SANT endast när en kod är 5 siffror lång och börjar med 'z'. De två inringade värdena returnerar FALSE med denna formel.

Så här tillåter du bara ett datum inom 30 dagar från idag:

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Så här tillåter du bara unika värden:

 
= COUNTIF (range,A1)<2

För att bara tillåta en e -postadress

 
=ISUMBER( FIND ('@',A1)

Datavalidering för att cirkulera ogiltiga poster

När datavalidering har tillämpats kan du be Excel att cirkulera tidigare angivna ogiltiga värden. På fliken Data i menyfliksområdet klickar du på Datavalidering och väljer 'Cirkel ogiltiga data':

Till exempel visar skärmen nedan värden inringade som misslyckas med validering med den här anpassade formeln:

 
= AND ( LEFT (A1)='z', LEN (A1)=5)

Hitta celler med datavalidering

Om du vill hitta celler med datavalidering har du använt dialogrutan Gå till> Special. Skriv tangentbordsgenvägen Ctrl + G och klicka sedan på knappen Special. När dialogrutan visas väljer du 'Datavalidering':

subtrahera en kolumn från en annan i Excel

Kopiera datavalidering från en cell till en annan

För att kopiera validering från en cell till andra celler. Kopiera de celler som normalt innehåller den datavalidering du vill använda och använd sedan Klistra in special + validering. När dialogrutan visas skriver du 'n' för att välja validering eller klickar på validering med musen.

Obs! Du kan använda tangentbordsgenvägen Ctrl + Alt + V för att anropa Paste Special utan musen.

Rensa all datavalidering

Om du vill rensa all datavalidering från ett cellintervall gör du valet och klickar sedan på datavalideringsknappen på fliken Data i menyfliksområdet. Klicka sedan på knappen 'Rensa alla':

Om du vill rensa all datavalidering från ett kalkylblad väljer du hela kalkylbladet och följer sedan samma steg ovan.