Datavalidering egendefinerte formler

Denne opplæringen vil vise deg hvordan du lager egendefinerte formler i datavalidering i Excel og Google Sheets

Datavalidering - må begynne med - Excel

Vi kan skrive en tilpasset formel for å sikre at dataene i en celle begynner med bestemt tekst.

1. Fremhev området som kreves, for eksempel: B3: B8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.

3. Plukke ut Tilpasset fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

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

Formelen bruker 2 funksjoner NØYAKTIG og VENSTRE for å avgjøre om de fire første tegnene i cellen er identiske med “FRU-”

4. Hvis du vil legge til en inndatamelding for brukeren din, klikker du på Inngangsmelding.

5. Sørg for at avmerkingsboksen “Vis inndatamelding når cellen er valgt”Er merket og skriv inn tittelen og meldingen du trenger.
6. Klikk på hvis du vil legge til et feilvarsel Feilvarsel.

7. Du kan endre stilen fra Stoppe å enten Advarsel eller Informasjon hvis du trenger det, og skriv deretter inn tittelen og feilmeldingen.
8. Klikk OK.
9. Hvis du har brukt Inngang Meldingsalternativ, en kommentar vil vises på skjermen som informerer brukeren om regelen.

10. Skriv "FRI-124" i celle B3. Hvis du har valgt Stoppe, vises følgende meldingsboks.

11. Hvis du valgte Advarsel, så vises denne meldingsboksen. Dette lar deg fortsette hvis du finner ut at dataene er riktige.

12. Hvis du har valgt Informasjon, så vises denne meldingsboksen.

13. Hvis du klikker OK, får du lov til å fortsette med feil data som er lagt inn i cellen.
14. Klikk Avbryt for å gå ut av meldingen eller OK for å skrive inn teksten i cellen.

Datavalidering Tillat bare store bokstaver i Excel

Vi kan skrive en tilpasset formel for å sikre at dataene i en celle bare tillater store bokstaver hvis tekst legges inn i cellen.

1. Fremhev området som kreves, for eksempel: B3: B8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Plukke ut Tilpasset fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= EKSAKT (B3, ØVRE (B3))

Formelen bruker 2 funksjoner NØYAKTIG og ØVERSTE for å avgjøre om teksten som er angitt i cellen er i store bokstaver. Celler med en blanding av tall og tekst regnes som tekst og tallene ignoreres i regelen.

4. Klikk OK.
5. Skriv "fru-124" i celle B3.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.
8. Skriv "123456" i celle B3.
9. Dette er tillatt ettersom det er et tall og ikke tekst.

I vårt neste eksempel vil vi sikre at bare tekst med store bokstaver kan skrives inn i cellen.

Datavalidering Tillat bare store bokstaver i Excel

Vi kan skrive en egendefinert formel i Datavalidering kan brukes for å sikre at dataene i en celle bare tillater store bokstaver

MERK: Hvis du legger inn informasjon i en celle som begynner med tekst, men inneholder tall, vil Excel vurdere informasjonsteksten.

1. Marker området som kreves, for eksempel: B3: B8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Plukke ut Tilpasset fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= OG (EKSAKT (B3, ØVRE (B3)), ISTEXT (B3))

Formelen bruker 4 funksjoner OG, Nøyaktig, ØVRE og TEKST for å avgjøre om teksten som er lagt inn i cellene er i store bokstaver OG for å avgjøre om informasjonen som er angitt faktisk er tekst og ikke et rent tall.

4. Klikk OK.
5. Skriv "fru-124" i celle B3.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.
8. Skriv "123456" i celle B3.
9. Du får igjen feilmeldingen.
10. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering forhindrer mellomrom i Excel

Vi kan skrive en tilpasset formel for å sikre at ingen mellomrom legges inn i dataene som er angitt i et celleområde.

1. Fremhev området som kreves, for eksempel: B3: B8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Plukke ut Tilpasset fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= B3 = ERSTATNING (B3, ””, “”)

Formelen bruker SUBSTITUTE -funksjonen for å kontrollere at mellomrom ikke eksisterer.

4. Klikk OK.
5. Skriv "FRU - 124" i celle B4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering forhindrer duplikater i Excel

Vi kan skrive en tilpasset formel for å hindre oss i å legge inn duplikatinformasjon i en rekke celler.

1. Marker området som kreves, for eksempel: B3: B8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

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

Formelen bruker TELLING funksjon og bruk ABSOLUTTER i området B3: B8 for å sikre at dette er listen som TELLING funksjonen ser på når den sjekker om det er noen like verdier.

4. Klikk OK.
5. Skriv "FRU-123" i celle D4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering finnes i liste i Excel

Vi kan skrive en tilpasset formel for å sikre at bare spesifikk tekst skrives inn i en celle.

1. Marker området som kreves, for eksempel: D3: D8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Tilpasset fra rullegardinmenyen Tillat, og skriv deretter inn følgende formel:

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

Formelen bruker TELLING funksjon og bruk ABSOLUTTER i området F3: F8 for å sikre at dette er listen som TELLING funksjonen ser på når den sjekker at den riktige teksten skrives inn.

4. Klikk OK.
5. Skriv "Single" i celle D4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering finnes ikke i liste i Excel

Vi kan bruke en tilpasset formel for å sikre at spesifikk tekst ikke legges inn i en celle.

1. Marker området som kreves, f.eks .: C3: C8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

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

Formelen bruker TELLING funksjon og bruk ABSOLUTTER i området F3: F8 for å sikre at dette er listen som TELLING funksjonen ser på når den sjekker at den riktige teksten skrives inn.

4. Klikk OK.
5. Skriv "Beef" i celle C4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Bare datavalidering angis i Excel

Vi kan bruke en tilpasset formel for å sikre at bare et tall er lagt inn i en celle.

1. Marker området som kreves, f.eks .: E3: E8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= ISNUMBER (F3: F8)

Formelen bruker ISNUMBER -funksjonen for å sikre at et tall legges inn i cellene i området.

4. Klikk OK.
5. Skriv "ni" i celle F4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering overstiger ikke verdien i Excel

Vi kan bruke en tilpasset formel for å sikre at verdiene som er angitt i en celle ikke overstiger en spesifisert verdi.

1. Merk området som kreves f.eks. E3: E8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= E3 <= $ G $ 6

Formelen en ABSOLUTT i området G6 for å sikre at dette er verdien som regelen sjekker når data legges inn i E3.

4. Klikk OK.
5. Skriv "9" i celle E4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering overstiger ikke totalt i Excel

Vi kan bruke en tilpasset formel for å sikre at verdiene som er angitt i et celleområde ikke overstiger en spesifisert totalverdi for området

1. Marker området som kreves f.eks. F3: F8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

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

Formelen bruker SUM funksjon og bruk ABSOLUTTER i området F3: F8 for å sikre at dette er listen som SUM funksjonen ser på når den sjekker for å se at totalen for området ikke er større enn verdien som er angitt i H6.

4. Klikk OK.
5. Skriv "40" i celle F4.
6. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt for å gå ut av meldingen eller Prøv på nytt for å skrive inn riktig tekst i cellen igjen.

Datavalidering Ukedager kun i Excel.

Vi kan bruke en tilpasset formel for å sikre at bare ukedager angis når datoer brukes i Excel.

1. Marker området som kreves, for eksempel: G3: G8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= UKEDAG (F3,2) <6

Ukedagsfunksjonen sjekker for å sikre at dagen i datoen ikke er en lørdag eller en søndag.

4. Endre datoen i G5 for å vise en lørdag (f.eks. 9^th Mai 2022).
5. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

7. Klikk Avbryt eller Prøv igjen for å angi en alternativ dato.

Datavalidering Fremtidig dato Bare i Excel

Vi kan lage en tilpasset formel slik at brukeren bare kan angi en dato som er i fremtiden.

1. Marker området som kreves, f.eks .: G3: G8.
2. Velg båndet i båndet Data> Dataverktøy> Datavalidering.
3. Velg Kunde fra Tillat-rullegardinboksen, og skriv deretter inn følgende formel:

= G3> I DAG ()

Formelen bruker I DAG funksjon for å kontrollere om datoen som er angitt i cellen er større enn dagens dato.

4. Endre datoen i G5 til i går.
5. Hvis du har brukt alternativet Feilvarsel, vises den tilpassede advarselen og feilmeldingen. Hvis du ikke har brukt dette alternativet, vises standardadvarselen.

6. Klikk Avbryt eller Prøv igjen for å angi en alternativ dato.

Datavalidering må begynne med i Google Regneark

1. Marker området som kreves, for eksempel: B3: B8.
2. Velg i menyen Data> Datavalidering.

3. Celleområdet vil allerede bli fylt ut.

4. Plukke ut Egendefinert formel er fra rullegardinlisten Kriterier.

5. Skriv inn formelen.

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

6. Velg enten Vis advarsel eller Avvis inngang hvis dataene er ugyldige.

7. Du kan skrive inn noen hjelpetekst for validering hvis du trenger det.

8. Klikk på Lagre.

9. Klikk i B3 for å se hjelpeteksten for validering

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

12. Alternativt, hvis du har valgt Avvis inngang på ugyldige data, vil du bli forhindret i å angi dataene, og følgende melding vises på skjermen.

Resten av de tilpassede formeleksemplene i Google Regneark fungerer på nøyaktig samme måte.