Datavalidering anpassade formler

Denna handledning visar dig hur du skapar anpassade formler i datavalidering i Excel och Google Sheets

Datavalidering - Måste börja med - Excel

Vi kan skriva en anpassad formel för att säkerställa att data i en cell börjar med viss text.

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.

3. Välj Beställnings från rullgardinsmenyn Tillåt och skriv sedan följande formel:

= EXAKT (VÄNSTER (B3,4), ”FRU-“)

Formeln använder 2 funktioner EXAKT och VÄNSTER för att avgöra om de fyra första tecknen i cellen är identiska med “FRU-”

4. Om du vill lägga till ett inmatningsmeddelande för din användare klickar du på Inmatningsmeddelande.

5. Se till att kryssrutan ”Visa inmatningsmeddelande när cell är markerad”Är markerad och skriv sedan in titeln och meddelandet du behöver.
6. Om du vill lägga till en felmeddelande klickar du på Felmeddelande.

7. Du kan ändra stilen från Sluta att antingen Varning eller Information om du behöver, och skriv sedan in din titel och felmeddelande.
8. Klick OK.
9. Om du har använt Inmatning Meddelande alternativ, en kommentar kommer att visas på skärmen som informerar användaren om regeln.

10. Skriv "FRI-124" i cell B3. Om du har valt Sluta, kommer följande meddelanderuta att visas.

11. Om du valde Varning, då visas den här meddelanderutan. Detta låter dig fortsätta om du fastställer att uppgifterna är korrekta.

12. Om du har valt Information, då visas den här meddelanderutan.

13. Om du klickar OK, kommer du att få fortsätta med felaktiga uppgifter som matats in i cellen.
14. Klick Avbryt för att avsluta meddelandet eller OK för att mata in texten i cellen.

Datavalidering Tillåt endast versaler i Excel

Vi kan skriva en anpassad formel för att säkerställa att data i en cell endast tillåter versaler om text matas in i cellen.

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj Beställnings från rullgardinsmenyn Tillåt och skriv sedan följande formel:

= EXAKT (B3, ÖVRE (B3))

Formeln använder 2 funktioner EXAKT och ÖVRE för att avgöra om texten i cellen finns i versaler. Celler med en blandning av tal och text betraktas som text och siffrorna ignoreras i regeln.

4. Klick OK.
5. Skriv "fru-124" i cell B3.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.
8. Skriv "123456" i cell B3.
9. Detta är tillåtet eftersom det är ett nummer och inte text.

I vårt nästa exempel kommer vi att se till att endast stor text kan skrivas in i cellen.

Datavalidering Tillåt endast stor text i Excel

Vi kan skriva en anpassad formel i Datavalidering kan användas för att säkerställa att data i en cell endast tillåter versaler

OBS: om du anger information i en cell som börjar med text men innehåller siffror kommer Excel att beakta informationstexten.

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj Beställnings från rullgardinsmenyn Tillåt och skriv sedan följande formel:

= OCH (EXAKT (B3, ÖVRE (B3)), ISTEXT (B3))

Formeln använder fyra funktioner OCH, EXAKT, ÖVRIGT och TEXT för att avgöra om texten som skrivs in i cellerna är versaler OCH för att avgöra om den angivna informationen faktiskt är text och inte ett rent tal.

4. Klick OK.
5. Skriv "fru-124" i cell B3.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.
8. Skriv "123456" i cell B3.
9. Du får återigen felmeddelandet.
10. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering förhindrar utrymmen i Excel

Vi kan skriva en anpassad formel för att säkerställa att inga mellanslag skrivs in i data som matas in i ett cellområde.

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj Beställnings från rullgardinsmenyn Tillåt och skriv sedan följande formel:

= B3 = BYTARE (B3, ””, ””)

Formeln använder SUBSTITUTE -funktionen för att kontrollera att mellanslag inte finns.

4. Klick OK.
5. Skriv "FRU - 124" i cell B4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering förhindrar dubbletter i Excel

Vi kan skriva en anpassad formel för att hindra oss från att mata in dubblettinformation i ett antal celler.

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

= RÄKTA ($ B $ 3: $ B $ 8, B3) <2

Formeln använder RÄKTA funktion och användningsområden ABSOLUTER i intervallet B3: B8 för att säkerställa att detta är listan som RÄKTA funktionen tittar på när den kontrollerar om det finns några dubblettvärden.

4. Klick OK.
5. Skriv "FRU-123" i cell D4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering finns i listan i Excel

Vi kan skriva en anpassad formel så att endast specifik text skrivs in i en cell.

1. Markera det intervall som krävs, t.ex.: D3: D8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj Anpassad i listrutan Tillåt och skriv sedan följande formel:

= RÄKTA ($ F $ 6: $ F $ 8, D3)> 0

Formeln använder RÄKTA funktion och användningsområden ABSOLUTER i intervallet F3: F8 för att säkerställa att detta är listan som RÄKTA funktionen tittar på när den kontrollerar att rätt text matas in.

4. Klick OK.
5. Skriv "Single" i cell D4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering finns inte i listan i Excel

Vi kan använda en anpassad formel för att säkerställa att specifik text inte skrivs in i en cell.

1. Markera det intervall som krävs, t.ex.: C3: C8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

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

Formeln använder RÄKTA funktion och användningsområden ABSOLUTER i intervallet F3: F8 för att säkerställa att detta är listan som RÄKTA funktionen tittar på när den kontrollerar att rätt text matas in.

4. Klick OK.
5. Skriv "Nötkött" i cell C4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering Endast siffror anges i Excel

Vi kan använda en anpassad formel för att säkerställa att endast ett tal anges i en cell.

1. Markera det område som krävs, t.ex.: E3: E8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

= ISNUMBER (F3: F8)

Formeln använder funktionen ISNUMBER för att säkerställa att ett tal matas in i cellerna i intervallet.

4. Klick OK.
5. Skriv "nio" i cell F4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering överskrider inte värdet i Excel

Vi kan använda en anpassad formel för att säkerställa att de värden som anges i en cell inte överstiger ett specificerat värde.

1. Markera det område som krävs, t.ex.: E3: E8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

= E3 <= $ G $ 6

Formeln an ABSOLUT i intervallet G6 för att säkerställa att detta är det värde som regeln kontrollerar när data matas in i E3.

4. Klick OK.
5. Skriv "9" i cell E4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering överstiger inte totalt i Excel

Vi kan använda en anpassad formel för att säkerställa att de värden som anges i ett cellintervall inte överstiger ett specifikt totalt värde för intervallet

1. Markera det intervall som krävs, t.ex.: F3: F8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

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

Formeln använder BELOPP funktion och användningsområden ABSOLUTER i intervallet F3: F8 för att säkerställa att detta är listan som BELOPP funktionen tittar på när den kontrollerar för att se att totalsumman inte är större än värdet som anges i H6.

4. Klick OK.
5. Skriv "40" i cell F4.
6. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt för att avsluta meddelandet eller Försök igen för att skriva in rätt text i cellen igen.

Datavalidering Veckodagar Endast i Excel.

Vi kan använda en anpassad formel för att se till att endast vardagar anges när datum används i Excel.

1. Markera det intervall som krävs, t.ex.: G3: G8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

= VECKODAG (F3,2) <6

Veckodagsfunktionen kontrollerar att dagen som ingår i datumet inte är en lördag eller en söndag.

4. Ändra datumet i G5 för att visa en lördag (t.ex. 9^th Maj 2022).
5. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

7. Klicka på Avbryt eller Försök igen för att ange ett alternativt datum.

Datavalidering Framtidsdatum Endast i Excel

Vi kan skapa en anpassad formel för att endast tillåta användaren att ange ett datum som ligger i framtiden.

1. Markera det intervall som krävs, t.ex.: G3: G8.
2. Välj i menyfliksområdet Data> Dataverktyg> Datavalidering.
3. Välj kund i rullgardinsmenyn Tillåt och skriv sedan följande formel:

= G3> IDAG ()

Formeln använder I DAG funktion för att kontrollera om datumet som anges i cellen är större än dagens datum.

4. Ändra datumet i G5 till igår.
5. Om du har använt alternativet Felvarning visas din anpassade varning och felmeddelande. Om du inte har använt det här alternativet visas standardvarningen.

6. Klicka på Avbryt eller Försök igen för att ange ett alternativt datum.

Datavalidering måste börja med i Google Kalkylark

1. Markera det intervall som krävs, t.ex.: B3: B8.
2. Välj i menyn Data> Datavalidering.

3. Cellintervallet kommer redan att fyllas i.

4. Välj Anpassad formel är från rullgardinsmenyn Kriterier.

5. Skriv in formeln.

= EXAKT (VÄNSTER (B3,4), ”FRU-“)

6. Välj antingen Visa varning eller Avvisa inmatning om uppgifterna är ogiltiga.

7. Du kan skriva in någon hjälptext för validering om du behöver det.

8. Klicka på Spara.

9. Klicka i B3 för att se hjälptexten för validering

10. Skriv FRI-123
11. Om du har valt Visa varning, kommer följande meddelande att visas.

12. Alternativt, om du har valt Avvisa inmatning på ogiltiga data, kommer du att hindras från att mata in data och följande meddelande visas på skärmen.

Resten av de anpassade formelexemplen i Google Kalkylark fungerar på exakt samma sätt.