Datavalidering tilpassede formler

Denne vejledning viser dig, hvordan du opretter tilpassede formler i datavalidering i Excel og Google Sheets

Datavalidering - Skal begynde med - Excel

Vi kan skrive en brugerdefineret formel for at sikre, at dataene i en celle begynder med bestemt tekst.

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.

3. Vælg Brugerdefinerede fra rullemenuen Tillad, og skriv derefter følgende formel:

= EXAKT (VENSTRE (B3,4), ”FRU-“)

Formlen anvender 2 funktioner EKSAKT og VENSTRE for at afgøre, om de første 4 tegn, der er indtastet i cellen, er identiske med "FRU-"

4. Hvis du vil tilføje en inputmeddelelse til din bruger, skal du klikke på Indtast besked.

5. Sørg for afkrydsningsfeltet "Vis inputmeddelelse, når cellen er markeret”Er markeret, og indtast derefter den ønskede titel og meddelelse.
6. Hvis du vil tilføje en fejlmeddelelse, skal du klikke på Fejlmeddelelse.

7. Du kan ændre stilen fra Hold op til enten Advarsel eller Information hvis du har brug for det, og indtast derefter din titel og fejlmeddelelse.
8. Klik på Okay.
9. Hvis du har brugt Input Beskedindstilling, en kommentar vises på skærmen, der informerer brugeren om reglen.

10. Skriv "FRI-124" i celle B3. Hvis du har valgt Hold op, vises følgende meddelelsesboks.

11. Hvis du valgte Advarsel, så vises denne meddelelsesboks. Dette giver dig mulighed for at fortsætte, hvis du finder ud af, at dataene er korrekte.

12. Hvis du har valgt Information, så vises denne meddelelsesboks.

13. Hvis du klikker Okay, får du lov til at fortsætte med de forkerte data, der er indtastet i cellen.
14. Klik på Afbestille for at afslutte meddelelsen eller Okay for at indtaste teksten i cellen.

Datavalidering Tillad kun store bogstaver i Excel

Vi kan skrive en brugerdefineret formel for at sikre, at dataene i en celle kun tillader store bogstaver, hvis der indtastes tekst i cellen.

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Brugerdefinerede fra rullemenuen Tillad, og skriv derefter følgende formel:

= PRÆCIS (B3, ØVRE (B3))

Formlen anvender 2 funktioner EKSAKT og ØVERST for at afgøre, om den tekst, der er indtastet i cellen, er i store bogstaver. Celler med en blanding af tal og tekst betragtes som tekst, og tallene ignoreres i reglen.

4. Klik på Okay.
5. Skriv "fru-124" i celle B3.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.
8. Skriv "123456" i celle B3.
9. Dette er tilladt, da det er et tal og ikke tekst.

I vores næste eksempel vil vi sikre, at der kun kan indtastes store bogstaver i cellen.

Datavalidering Tillad kun store bogstaver i Excel

Vi kan skrive en brugerdefineret formel i Datavalidering kan bruges til at sikre, at dataene i en celle kun tillader store bogstaver

BEMÆRK: hvis du indtaster oplysninger i en celle, der begynder med tekst, men indeholder tal, vil Excel overveje informationsteksten.

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Brugerdefinerede fra rullemenuen Tillad, og indtast derefter følgende formel:

= OG (PRÆCIS (B3, ØVRE (B3)), ISTEXT (B3))

Formlen anvender 4 funktioner OG, PRÆCIS, OVER og TEKST for at afgøre, om den tekst, der er indtastet i cellerne, er i store bogstaver OG for at afgøre, om de indtastede oplysninger faktisk er tekst og ikke et rent tal.

4. Klik på Okay.
5. Skriv "fru-124" i celle B3.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.
8. Skriv "123456" i celle B3.
9. Du får igen fejlmeddelelsen.
10. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering forhindrer mellemrum i Excel

Vi kan skrive en brugerdefineret formel for at sikre, at der ikke indtastes mellemrum i de data, der er indtastet i et område af celler.

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Brugerdefinerede fra rullemenuen Tillad, og indtast derefter følgende formel:

= B3 = SUBSTITUTE (B3, ””, “”)

Formlen bruger SUBSTITUTE -funktionen til at kontrollere, at der ikke findes mellemrum.

4. Klik på Okay.
5. Skriv "FRU - 124" i celle B4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering forhindrer dubletter i Excel

Vi kan skrive en brugerdefineret formel for at forhindre os i at indtaste dublerede oplysninger i en række celler.

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Formlen bruger TÆLLE funktion og anvendelser ABSOLUTTER i området B3: B8 for at sikre, at dette er listen, som TÆLLE funktion ser på, når den kontrollerer, om der er nogen dublerede værdier.

4. Klik på Okay.
5. Skriv "FRU-123" i celle D4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering findes i liste i Excel

Vi kan skrive en brugerdefineret formel for at sikre, at kun specifik tekst indtastes i en celle.

1. Fremhæv det nødvendige område, f.eks .: D3: D8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Brugerdefineret i rullemenuen Tillad, og indtast derefter følgende formel:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Formlen bruger TÆLLE funktion og anvendelser ABSOLUTTER i området F3: F8 for at sikre, at dette er listen, som TÆLLE funktion ser på, når den kontrollerer, om den korrekte tekst indtastes.

4. Klik på Okay.
5. Skriv "Single" i celle D4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering findes ikke i liste i Excel

Vi kan bruge en brugerdefineret formel for at sikre, at specifik tekst ikke indtastes i en celle.

1. Fremhæv det nødvendige område, f.eks .: C3: C8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Formlen bruger TÆLLE funktion og anvendelser ABSOLUTTER i området F3: F8 for at sikre, at dette er listen, som TÆLLE funktion ser på, når den kontrollerer, om den korrekte tekst indtastes.

4. Klik på Okay.
5. Skriv "Oksekød" i celle C4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering Kun tal angives i Excel

Vi kan bruge en brugerdefineret formel til at sikre, at der kun indtastes et tal i en celle.

1. Fremhæv det nødvendige område, f.eks .: E3: E8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= ISNUMBER (F3: F8)

Formlen anvender ISNUMBER -funktionen til at sikre, at der indtastes et tal i cellerne i området.

4. Klik på Okay.
5. Skriv "ni" i celle F4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering overstiger ikke værdien i Excel

Vi kan bruge en brugerdefineret formel for at sikre, at de værdier, der er indtastet i en celle, ikke overstiger en specificeret værdi.

1. Fremhæv det nødvendige område, f.eks .: E3: E8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= E3 <= $ G $ 6

Formlen en ABSOLUT i området G6 for at sikre, at dette er den værdi, reglen kontrollerer, når data indtastes i E3.

4. Klik på Okay.
5. Skriv "9" i celle E4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering overstiger ikke total i Excel

Vi kan bruge en brugerdefineret formel for at sikre, at de værdier, der er angivet i et celleområde, ikke overstiger en specificeret samlet værdi for intervallet

1. Fremhæv det nødvendige område, f.eks .: F3: F8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= SUM ($ F $ 3: $ F $ 8) <= $ H $ 6

Formlen bruger SUM funktion og anvendelser ABSOLUTTER i området F3: F8 for at sikre, at dette er listen, som SUM funktion ser på, når den kontrollerer for at se, at totalværdien af ​​intervallet ikke er større end den værdi, der er angivet i H6.

4. Klik på Okay.
5. Skriv "40" i celle F4.
6. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller for at afslutte meddelelsen eller Prøv igen for at indtaste den korrekte tekst i cellen igen.

Datavalidering Ugedagedatoer kun i Excel.

Vi kan bruge en brugerdefineret formel til at sikre, at der kun indtastes hverdage, når datoer bruges i Excel.

1. Fremhæv det nødvendige område, f.eks .: G3: G8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= WEEKDAY (F3,2) <6

Ugedagsfunktionen kontrollerer, at dagen indeholdt i datoen ikke er en lørdag eller en søndag.

4. Ændre datoen i G5 for at vise en lørdag (f.eks. 9^th Maj 2022).
5. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

7. Klik på Annuller eller Prøv igen for at indtaste en alternativ dato.

Datavalidering Fremtidig dato Kun i Excel

Vi kan oprette en tilpasset formel, så brugeren kun kan indtaste en dato, der er i fremtiden.

1. Fremhæv det nødvendige område, f.eks .: G3: G8.
2. Vælg på båndet Data> Dataværktøjer> Datavalidering.
3. Vælg Kunde i rullemenuen Tillad, og indtast derefter følgende formel:

= G3> I DAG ()

Formlen bruger I DAG funktion for at kontrollere, om datoen, der er indtastet i cellen, er større end dagens dato.

4. Ændre datoen i G5 til i går.
5. Hvis du har brugt indstillingen Fejlvarsel, vises din tilpassede advarsel og fejlmeddelelse. Hvis du ikke har brugt denne mulighed, vises standardadvarslen.

6. Klik på Annuller eller Prøv igen for at indtaste en alternativ dato.

Datavalidering skal begynde med i Google Sheets

1. Fremhæv det nødvendige område, f.eks .: B3: B8.
2. Vælg i menuen Data> Datavalidering.

3. Celleområdet vil allerede blive udfyldt.

4. Vælg Brugerdefineret formel er fra rullelisten Kriterier.

5. Indtast formlen.

= EXAKT (VENSTRE (B3,4), ”FRU-“)

6. Vælg enten Vis advarsel eller Afvis input hvis dataene er ugyldige.

7. Du kan indtaste noget Validation -hjælpetekst, hvis du har brug for det.

8. Klik på Gem.

9. Klik i B3 for at se Hjælp -teksten til validering

10. Type FRI-123
11. Hvis du har valgt Vis advarsel, vises følgende meddelelse.

12. Alternativt, hvis du har valgt Afvis input om ugyldige data, vil du blive forhindret i at indtaste dataene, og følgende meddelelse vises på skærmen.

Resten af ​​de tilpassede formeleksempler i Google Sheets fungerer på nøjagtig samme måde.