Aangepaste formules voor gegevensvalidatie

Deze zelfstudie laat u zien hoe u aangepaste formules maakt in Gegevensvalidatie in Excel en Google Spreadsheets

Gegevensvalidatie - Moet beginnen met - Excel

We kunnen een aangepaste formule schrijven om ervoor te zorgen dat de gegevens in een cel met bepaalde tekst beginnen.

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.

3. Selecteer Aangepast in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=EXACT(LINKS(B3,4),”FRU-“)

De formule gebruikt 2 functies EXACT en LINKS om te bepalen of de eerste 4 tekens die in de cel zijn ingevoerd identiek zijn aan "FRU-"

4. Als u een invoerbericht voor uw gebruiker wilt toevoegen, klikt u op Invoerbericht:.

5. Zorg ervoor dat het vinkje “Toon invoerbericht wanneer cel is geselecteerd” is aangevinkt en typ vervolgens de titel en het bericht dat u nodig heeft.
6. Als u een foutmelding wilt toevoegen, klikt u op Foutmelding.

7. U kunt de stijl wijzigen van Stop naar een van beide Waarschuwing of Informatie indien nodig, en typ vervolgens uw titel en foutmelding.
8. Klik Oke.
9. Als je de hebt gebruikt Invoer Berichtoptie, er verschijnt een opmerking op het scherm om de gebruiker op de hoogte te stellen van de regel.

10. Typ "FRI-124" in cel B3. Als je hebt geselecteerd Stop, verschijnt het volgende berichtvenster.

11. Als je hebt geselecteerd Waarschuwing, dan verschijnt dit berichtvenster. Hiermee kunt u doorgaan als u vaststelt dat de gegevens correct zijn.

12. Als je hebt geselecteerd Informatie, dan verschijnt dit berichtvenster.

13. Als u klikt Oke, mag u doorgaan met de onjuiste gegevens die in de cel zijn ingevoerd.
14. Klik Annuleren om het bericht te verlaten of Oke om de tekst in de cel in te voeren.

Gegevensvalidatie Alleen hoofdletters toestaan ​​in Excel

We kunnen een aangepaste formule schrijven om ervoor te zorgen dat de gegevens in een cel alleen hoofdletters toestaan ​​als er tekst in de cel wordt ingevoerd.

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Aangepast in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=EXACT(B3,BOVENSTE(B3))

De formule gebruikt 2 functies EXACT en BOVENSTE om te bepalen of de tekst die in de cel is ingevoerd in hoofdletters is. Cellen met een combinatie van nummer en tekst worden als tekst beschouwd en de nummers worden genegeerd in de regel.

4. Klik Oke.
5. Typ "fru-124" in cel B3.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.
8. Typ "123456" in cel B3.
9. Dit is toegestaan ​​omdat het een nummer is en geen tekst.

In ons volgende voorbeeld zullen we ervoor zorgen dat alleen hoofdletters in de cel kunnen worden ingevoerd.

Gegevensvalidatie Alleen hoofdletters toestaan ​​in Excel

We kunnen een aangepaste formule schrijven in Gegevensvalidatie die kan worden gebruikt om ervoor te zorgen dat de gegevens in een cel alleen hoofdletters toestaan

OPMERKING: als u informatie invoert in een cel die begint met tekst maar cijfers bevat, houdt Excel rekening met de informatietekst.

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Aangepast in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=EN(EXACT(B3,BOVEN(B3)),ISTEXT(B3))

De formule gebruikt 4 functies EN, EXACT, BOVEN en TEKST om te bepalen of de tekst die in de cellen is ingevoerd in hoofdletters is EN om te bepalen of de ingevoerde informatie daadwerkelijk tekst is en geen puur getal.

4. Klik Oke.
5. Typ "fru-124" in cel B3.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.
8. Typ "123456" in cel B3.
9. U krijgt opnieuw de foutmelding.
10. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie Spaties voorkomen in Excel

We kunnen een aangepaste formule schrijven om ervoor te zorgen dat er geen spaties worden ingevoerd in de gegevens die in een celbereik zijn ingevoerd.

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Aangepast in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=B3=VERVANGING(B3, ” ”, “”)

De formule gebruikt de SUBSTITUTE-functie om te controleren of er geen spaties bestaan.

4. Klik Oke.
5. Typ "FRU - 124" in cel B4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie Voorkom duplicaten in Excel

We kunnen een aangepaste formule schrijven om te voorkomen dat we dubbele informatie in een celbereik invoeren.

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=AANTAL.ALS($B$3:$B$8,B3)<2

De formule gebruikt de AANTAL.ALS functie en gebruik ABSOLUUT in het bereik B3:B8 om ervoor te zorgen dat dit de lijst is die de AANTAL.ALS functie kijkt wanneer het controleert of er dubbele waarden zijn.

4. Klik Oke.
5. Typ "FRU-123" in cel D4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie bestaat in lijst in Excel

We kunnen een aangepaste formule schrijven om ervoor te zorgen dat alleen specifieke tekst in een cel wordt ingevoerd.

1. Markeer het gewenste bereik, bijvoorbeeld: D3:D8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Aangepast in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=AANTAL.ALS($F$6:$F$8,D3)>0

De formule gebruikt de AANTAL.ALS functie en gebruik ABSOLUTE in het bereik F3:F8 om ervoor te zorgen dat dit de lijst is die de AANTAL.ALS functie kijkt wanneer het controleert of de juiste tekst wordt ingevoerd.

4. Klik Oke.
5. Typ "Enkel" in cel D4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie bestaat niet in lijst in Excel

We kunnen een aangepaste formule gebruiken om ervoor te zorgen dat er geen specifieke tekst in een cel wordt ingevoerd.

1. Markeer het gewenste bereik, bijvoorbeeld: C3:C8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=AANTAL.ALS($F$6:$F$8,C3)=0

De formule gebruikt de AANTAL.ALS functie en gebruik ABSOLUTE in het bereik F3:F8 om ervoor te zorgen dat dit de lijst is die de AANTAL.ALS functie kijkt wanneer het controleert of de juiste tekst wordt ingevoerd.

4. Klik Oke.
5. Typ "Rundvlees" in cel C4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie Alleen cijfers worden ingevoerd in Excel

We kunnen een aangepaste formule gebruiken om ervoor te zorgen dat alleen een getal in een cel wordt ingevoerd.

1. Markeer het gewenste bereik, bijvoorbeeld: E3:E8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=ISGETAL(F3:F8)

De formule gebruikt de functie ISNUMBER om ervoor te zorgen dat een getal wordt ingevoerd in de cellen in het bereik.

4. Klik Oke.
5. Typ 'negen' in cel F4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie overschrijdt de waarde niet in Excel

We kunnen een aangepaste formule gebruiken om ervoor te zorgen dat de waarden die in een cel worden ingevoerd een opgegeven waarde niet overschrijden.

1. Markeer het gewenste bereik, bijvoorbeeld: E3:E8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=E3<=$G$6

de formule an ABSOLUUT in het bereik G6 om ervoor te zorgen dat dit de waarde is die de regel controleert wanneer gegevens in E3 worden ingevoerd.

4. Klik Oke.
5. Typ "9" in cel E4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Gegevensvalidatie overschrijdt het totaal niet in Excel

We kunnen een aangepaste formule gebruiken om ervoor te zorgen dat de waarden die in een celbereik zijn ingevoerd, een opgegeven totale waarde voor het bereik niet overschrijden

1. Markeer het gewenste bereik, bijvoorbeeld: F3:F8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

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

De formule gebruikt de SOM functie en gebruik ABSOLUTE in het bereik F3:F8 om ervoor te zorgen dat dit de lijst is die de SOM functie kijkt wanneer het controleert of het totaal van het bereik niet groter is dan de waarde die is ingevoerd in H6.

4. Klik Oke.
5. Typ "40" in cel F4.
6. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren om het bericht af te sluiten of op Opnieuw om de juiste tekst opnieuw in de cel in te voeren.

Data Validatie Weekdag Datums Alleen in Excel.

We kunnen een aangepaste formule gebruiken om ervoor te zorgen dat alleen weekdagen worden ingevoerd wanneer datums in Excel worden gebruikt.

1. Markeer het gewenste bereik, bijvoorbeeld: G3:G8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=WEEKDAG(F3,2)<6

De weekdagfunctie controleert of de dag in de datum geen zaterdag of zondag is.

4. Wijzig de datum in G5 om een ​​zaterdag weer te geven (bijv. 9^e mei 2022).
5. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

7. Klik op Annuleren of Opnieuw om een ​​alternatieve datum in te voeren.

Gegevensvalidatie Toekomstige datum Alleen in Excel

We kunnen een aangepaste formule maken zodat de gebruiker alleen een datum in de toekomst kan invoeren.

1. Markeer het gewenste bereik, bijvoorbeeld: G3:G8.
2. Selecteer in het lint Gegevens > Gegevenshulpmiddelen > Gegevensvalidatie.
3. Selecteer Klant in de vervolgkeuzelijst Toestaan ​​en typ de volgende formule:

=G3>VANDAAG()

De formule gebruikt de VANDAAG functie om te controleren of de in de cel ingevoerde datum groter is dan de datum van vandaag.

4. Wijzig de datum in G5 naar gisteren.
5. Als u de optie Error Alert hebt gebruikt, wordt uw aangepaste waarschuwing en foutmelding weergegeven. Als u deze optie niet hebt gebruikt, verschijnt de standaardwaarschuwing.

6. Klik op Annuleren of Opnieuw om een ​​alternatieve datum in te voeren.

Gegevensvalidatie moet beginnen in Google Spreadsheets

1. Markeer het gewenste bereik, bijvoorbeeld: B3:B8.
2. Selecteer in het menu Gegevens > Gegevensvalidatie.

3. Het celbereik is al ingevuld.

4. Selecteer Aangepaste formule komt uit de vervolgkeuzelijst Criteria.

5. Typ de formule in.

=EXACT(LINKS(B3,4),”FRU-“)

6. Selecteer een van beide Waarschuwing weergeven of Invoer weigeren als de gegevens ongeldig zijn.

7. U kunt desgewenst wat Validatie-helptekst intypen.

8. Klik op Opslaan.

9. Klik in B3 om de validatie-helptekst te zien

10. Type FRI-123
11. Als je hebt geselecteerd Waarschuwing weergeven, verschijnt het volgende bericht.

12. Als alternatief, als u hebt geselecteerd Invoer weigeren bij ongeldige gegevens, wordt u verhinderd de gegevens in te voeren en verschijnt het volgende bericht op het scherm.

De rest van de voorbeelden van aangepaste formules in Google Spreadsheets werken op precies dezelfde manier.