Excel

Excel datavalideringsguide

Excel Data Validation Guide

Valideringsformler | Afhængige dropdown -lister | Generelle formler

Introduktion

Datavalidering er en funktion i Excel, der bruges til at styre, hvad en bruger kan indtaste i en celle. For eksempel kan du bruge datavalidering til at sikre, at en værdi er et tal mellem 1 og 6, sørge for, at en dato forekommer i de næste 30 dage, eller sørge for, at en tekstindtastning er mindre end 25 tegn.



Datavalidering kan ganske enkelt vise en meddelelse til en bruger, der fortæller dem, hvad der er tilladt som vist nedenfor:

Eksempel på datavalideringsmeddelelse vises, når celle er valgt





Datavalidering kan også stoppe ugyldig brugerinput. For eksempel, hvis en produktkode mislykkes validering, kan du vise en meddelelse som denne:

datavalideringsfejl advarsel ugyldig produktkode eksempel



Derudover kan datavalidering bruges til at præsentere brugeren for et foruddefineret valg i en rullemenu:

Eksempel på dropdown -menu til datavalidering

Dette kan være en bekvem måde at give en bruger nøjagtigt de værdier, der opfylder kravene.

Datavalideringskontroller

Datavalidering implementeres via regler defineret i Excel's brugergrænseflade på fanen Data på båndet.

ikke lige tegn i excelformel

Datavalideringskontroller på datafanen på båndet

Vigtig begrænsning

Det er vigtigt at forstå, at datavalidering let kan besejres. Hvis en bruger kopierer data fra en celle uden validering til en celle med datavalidering, ødelægges (eller erstattes) valideringen. Datavalidering er en god måde at lade brugerne vide, hvad der er tilladt eller forventet, men det er ikke en idiotsikker måde at garantere input.

Definition af datavalideringsregler

Datavalidering er defineret i et vindue med 3 faner: Indstillinger, Inputmeddelelse og Fejladvarsel:

Datavalideringsvinduet har tre hovedfaner

Fanen Indstillinger er, hvor du angiver valideringskriterier. Der er en række indbyggede valideringsregler med forskellige muligheder, eller du kan vælge Brugerdefineret og bruge din egen formel til at validere input som vist nedenfor:

Datavalideringsindstillinger fanebladet eksempel

Fanen Inputmeddelelse definerer en meddelelse, der skal vises, når der vælges en celle med valideringsregler. Denne inputmeddelelse er fuldstændig valgfri. Hvis der ikke er angivet en inputmeddelelse, vises der ingen meddelelse, når en bruger vælger en celle med datavalidering valideret. Inputmeddelelsen har ingen effekt på, hvad brugeren kan indtaste - den viser simpelthen en meddelelse for at lade brugeren vide, hvad der er tilladt eller forventet.

Fanen Datavalidering

Fanen Fejlvarsel styrer, hvordan validering håndhæves. For eksempel, når stil er sat til 'Stop', udløser ugyldige data et vindue med en besked, og input er ikke tilladt.

Fanen Datavalideringsfejl

Brugeren ser en meddelelse som denne:

Eksempel på datavalideringsfejlmeddelelse

Når stil er angivet til Information eller advarsel, vises et andet ikon med en brugerdefineret meddelelse, men brugeren kan ignorere meddelelsen og indtaste værdier, der ikke består validering. Tabellen nedenfor opsummerer adfærd for hver fejlalarm.

Advarselstil Opførsel
Hold op Stopper brugere i at indtaste ugyldige data i en celle. Brugere kan prøve igen, men skal indtaste en værdi, der passerer datavalidering. Vinduet Stop -alarm har to muligheder: Prøv igen og Annuller.
Advarsel Advarer brugere om, at data er ugyldige. Advarslen gør intet for at stoppe ugyldige data. Advarselsvinduet har tre muligheder: Ja (for at acceptere ugyldige data), Nej (for at redigere ugyldige data) og Annuller (for at fjerne de ugyldige data).
Information Informerer brugerne om, at data er ugyldige. Denne meddelelse gør intet for at stoppe ugyldige data. Informationsadvarselvinduet har 2 muligheder: OK for at acceptere ugyldige data, og Annuller for at fjerne dem.

Valideringsmuligheder for data

Når der oprettes en datavalideringsregel, er der otte muligheder for at validere brugerinput:

Enhver værdi - der foretages ingen validering. Bemærk: hvis datavalidering tidligere blev anvendt med en indstillet inputmeddelelse, vises meddelelsen stadig, når cellen er markeret, selv når Enhver værdi er valgt.

Helt tal - kun hele tal er tilladt. Når hele nummeret er valgt, bliver andre muligheder tilgængelige for yderligere at begrænse input. For eksempel kan du kræve et helt tal mellem 1 og 10.

Decimal - fungerer som indstilling for hele tal, men tillader decimalværdier. For eksempel, med indstillingen Decimal konfigureret til at tillade værdier mellem 0 og 3, er værdier som .5, 2.5 og 3.1 alle tilladt.

Liste - kun værdier fra en foruddefineret liste er tilladt. Værdierne præsenteres for brugeren som en dropdown -menu kontrol. Tilladte værdier kan hardkodes direkte på fanen Indstillinger eller angives som et område på regnearket.

Dato - kun datoer er tilladt. Du kan f.eks. Kræve en dato mellem 1. januar 2018 og 31. december 2021 eller en dato efter 1. juni 2018.

Tid - kun tider er tilladt. Du kan f.eks. Kræve en tid mellem 9:00 og 17:00, eller kun tillade tider efter 12:00.

Tekstlængde - validerer input baseret på antal tegn eller cifre. For eksempel kan du kræve kode, der indeholder 5 cifre.

Brugerdefinerede - validerer brugerinput ved hjælp af en brugerdefineret formel. Med andre ord kan du skrive din egen formel for at validere input. Tilpassede formler udvider i høj grad mulighederne for datavalidering. For eksempel kan du bruge en formel til at sikre, at en værdi er stor, en værdi indeholder 'xyz', eller en dato er en hverdag i de næste 45 dage.

Fanen Indstillinger indeholder også to afkrydsningsfelter:

Ignorer blank - fortæller Excel om ikke at validere celler, der ikke indeholder nogen værdi. I praksis synes denne indstilling kun at udføre kommandoen 'cirkel ugyldige data'. Når den er aktiveret, cirkuleres tomme celler ikke, selvom de mislykkes validering.

Anvend disse ændringer på andre celler med de samme indstillinger - denne indstilling opdaterer validering, der anvendes på andre celler, når den matcher (original) validering af cellen (erne), der redigeres.

hvordan man fjerner et bestemt antal tegn i Excel

Bemærk: Du kan også manuelt vælge alle celler med datavalidering valget ved hjælp af Go To + Special, som forklaret nedenfor.

Enkel rullemenu

Du kan angive en rullemenu med muligheder ved at kode værdier i indstillingsboksen eller vælge et område på regnearket. For eksempel for at begrænse poster til handlingerne 'KØB', 'HOLD' eller 'SÆLG' kan du indtaste disse værdier adskilt med kommaer som vist nedenfor:

Dropdown -menu til datavalidering med hardkodede værdier

Når den anvendes på en celle i regnearket, fungerer rullemenuen sådan:

Dropdown -menu til datavalidering, hårdkodede værdier i brug

En anden måde at levere værdier til en rullemenu er at bruge en regnearksreference. For eksempel med størrelser (dvs. små, mellemstore, osv.) I området F3: F6, kan du levere dette område direkte inden for vinduet til datavalidering:

Datavalidering dropdown -menuværdier med regnearksreference

Bemærk, at området er angivet som en absolut adresse for at forhindre den i at ændre sig, efterhånden som datavalideringen anvendes på andre celler.

Tip: Klik på det lille pilikon yderst til højre i kildefeltet for at foretage et valg direkte på regnearket, så du ikke behøver at indtaste området manuelt.

Du kan også bruge navngivne områder at angive værdier. For eksempel med det navngivne område kaldet 'størrelser' for F3: F7 kan du indtaste navnet direkte i vinduet, startende med et lighedstegn:

Datavalidering dropdown -menuværdier med navngivet område

Navngivne områder er automatisk absolutte, så de ændres ikke, da datavalideringen anvendes på forskellige celler. Hvis navngivne områder er nye for dig, denne side har et godt overblik og en række relaterede tips .

Du kan også oprette afhængige rullelister med en brugerdefineret formel.

Tip - hvis du bruger en tabel til rullemenuer, bliver Excel ved med at udvide eller indgå tabellen automatisk, når dropdown -værdier tilføjes eller fjernes. Med andre ord vil Excel automatisk holde rullemenuen synkroniseret med værdier i tabellen, når værdier ændres, tilføjes eller fjernes. Hvis du ikke er ny i Excel -tabeller, kan du se en demo i denne video på tabelgenveje.

Datavalidering med en tilpasset formel

Datavalideringsformler skal være logiske formler, der returnerer SAND, når input er gyldigt og FALSKT, når input er ugyldigt. For eksempel, for at tillade et hvilket som helst tal som input i celle A1, kan du bruge funktionen ISNUMBER i en formel som denne:

 
= ISNUMBER (A1)

Hvis en bruger indtaster en værdi som 10 i A1, returnerer ISNUMBER SAND, og ​​datavalidering lykkes. Hvis de indtaster en værdi som 'æble' i A1, returnerer ISNUMBER FALSKT, og datavalidering mislykkes.

For at aktivere datavalidering med en formel, skal du vælge 'Brugerdefineret' på fanen Indstillinger og derefter indtaste en formel i formellinjen, der begynder med et lighedstegn (=) som normalt.

Fejlfindingsformler

Excel ignorerer datavalideringsformler, der returnerer fejl. Hvis en formel ikke fungerer, og du ikke kan finde ud af hvorfor, skal du oprette dummyformler for at sikre, at formlen fungerer som du forventer. Dummyformler er simpelthen datavalideringsformler, der er indtastet direkte på regnearket, så du nemt kan se, hvad de returnerer. Skærmen herunder viser et eksempel:

Test af datavalidering med dummyformler

Når du får dummy -formlen til at fungere som du vil, skal du blot kopiere og indsætte den i datavalideringsformelområdet.

Hvis denne idéformel -idé er forvirrende for dig, se denne video , som viser, hvordan man bruger dummy -formler til at perfektionere betingede formateringsformler. Konceptet er nøjagtig det samme.

Datavalideringsformeleksempler

Mulighederne for tilpassede datavalideringsformler er stort set ubegrænsede. Her er et par eksempler for at give dig lidt inspiration:

For kun at tillade 5 tegnværdier, der begynder med 'z', kan du bruge:

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

Denne formel returnerer kun SAND, når en kode er 5 cifre lang og starter med 'z'. De to cirkelværdier returnerer FALSK med denne formel.

Sådan tillader du kun en dato inden for 30 dage efter i dag:

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

Sådan tillader du kun unikke værdier:

 
= COUNTIF (range,A1)<2

For kun at tillade en e -mail -adresse

 
=ISUMBER( FIND ('@',A1)
Klik for flere formeleksempler og detaljerede forklaringer

Datavalidering til cirkel af ugyldige poster

Når datavalidering er anvendt, kan du bede Excel om at krydse tidligere indtastede ugyldige værdier. Klik på Datavalidering på fanen Data på båndet, og vælg 'Cirkel ugyldige data':

Omkring ugyldige værdier med datavalidering - menu

Skærmen herunder viser f.eks. Værdier, der er cirkuleret, og som mislykkes validering med denne brugerdefinerede formel:

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

Ugyldige værdier for datavalidering cirkuleret på regnearket

Find celler med datavalidering

For at finde celler med datavalidering, skal du bruge dialogboksen Gå til> Speciel. Skriv tastaturgenvejen Control + G, og klik derefter på knappen Special. Når dialogen vises, skal du vælge 'Datavalidering':

Gå til specialknap

hvordan man bruger autofit på Excel

Vælg datavalidering med gå til særlig dialog

Kopier datavalidering fra en celle til en anden

For at kopiere validering fra en celle til andre celler. Kopiér cellen / cellerne normalt, der indeholder den ønskede datavalidering, og brug derefter Indsæt special + validering. Når dialogboksen vises, skal du skrive 'n' for at vælge validering, eller klikke på validering med musen.

Brug indsæt special til at kopiere datavalidering

Bemærk: Du kan bruge tastaturgenvejen Ctrl + Alt + V til at påkalde Paste Special uden musen.

Ryd alle datavalidering

For at slette al datavalidering fra et område af celler skal du foretage markeringen og derefter klikke på knappen Datavalidering på fanen Data på båndet. Klik derefter på knappen 'Ryd alle':

Brug knappen Ryd alle til at fjerne datavalideringimg/excel/59/excel-data-validation-guide-16.png

For at slette al datavalidering fra et regneark skal du vælge hele regnearket og derefter følge de samme trin ovenfor.

Forfatter Dave Bruns


^