Formule personalizzate di convalida dei dati

Questo tutorial ti mostrerà come creare formule personalizzate in Convalida dati in Excel e Fogli Google

Convalida dei dati - Deve iniziare con - Excel

Possiamo scrivere una formula personalizzata per garantire che i dati in una cella inizino con un determinato testo.

1. Evidenziare l'intervallo richiesto, ad es.: B3:B8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.

3. Selezionare Costume dalla casella a discesa Consenti, quindi digitare la seguente formula:

=ESATTO(SINISTRA(B3,4)”,FRU-“)

La formula utilizza 2 funzioni ESATTO e SINISTRA per determinare se i primi 4 caratteri inseriti nella cella sono identici a "FRU-"

4. Se desideri aggiungere un messaggio di input per il tuo utente, fai clic su Messaggio di input.

5. Assicurati che la casella di spunta "Mostra messaggio di input quando la cella è selezionata"è selezionato, quindi digita il titolo e il messaggio richiesti.
6. Se vuoi aggiungere un avviso di errore, fai clic su Avviso di errore.

7. Puoi cambiare lo stile da Fermare per entrambi Avvertimento o Informazione se lo desideri, quindi digita il titolo e il messaggio di errore.
8. Clic ok.
9. Se hai usato il Ingresso Opzione messaggio, sullo schermo apparirà un commento che informa l'utente della regola.

10. Digitare "FRI-124" nella cella B3. Se hai selezionato Fermare, apparirà la seguente finestra di messaggio.

11. Se hai selezionato Avvertimento, verrà visualizzata questa finestra di messaggio. Ciò consente di continuare se si determina che i dati sono corretti.

12. Se hai selezionato Informazione, verrà visualizzata questa finestra di messaggio.

13. Se fai clic su ok, ti sarà permesso di continuare con i dati errati inseriti nella cella.
14. Clic Annulla per uscire dal messaggio o ok per inserire il testo nella cella.

Convalida dati Consenti solo maiuscole in Excel

Possiamo scrivere una formula personalizzata per garantire che i dati in una cella consentano le maiuscole solo se il testo viene inserito nella cella.

1. Evidenziare l'intervallo richiesto, ad esempio: B3:B8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Costume dalla casella a discesa Consenti, quindi digitare la seguente formula:

=ESATTO(B3, SUPERIORE(B3))

La formula utilizza 2 funzioni ESATTO e SUPERIORE per determinare se il testo immesso nella cella è in maiuscolo. Le celle con una combinazione di numeri e testo sono considerate testo e i numeri vengono ignorati nella regola.

4. Clic ok.
5. Digitare "fru-124" nella cella B3.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.
8. Digita "123456" nella cella B3.
9. Questo sarà consentito in quanto è un numero e non un testo.

Nel nostro prossimo esempio, ci assicureremo che solo il testo maiuscolo possa essere inserito nella cella.

Convalida dati Consenti solo testo maiuscolo in Excel

Possiamo scrivere una formula personalizzata in Convalida dati può essere utilizzata per garantire che i dati in una cella consentano solo il testo maiuscolo

NOTA: se si immettono informazioni in una cella che inizia con testo ma contiene numeri, Excel considererà il testo delle informazioni.

1. Evidenziare l'intervallo richiesto, ad esempio: B3:B8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Costume dalla casella a discesa Consenti, quindi digitare la seguente formula:

=AND(ESATTO(B3,MAIUSC(B3)),ISTEXT(B3))

La formula utilizza 4 funzioni E, ESATTO, SUPERIORE e TESTO per determinare se il testo inserito nelle celle è in maiuscolo E per determinare se le informazioni inserite sono effettivamente testo e non un puro numero.

4. Clic ok.
5. Digitare "fru-124" nella cella B3.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.
8. Digita "123456" nella cella B3.
9. Riceverai ancora una volta il messaggio di errore.
10. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati impedisce gli spazi in Excel

Possiamo scrivere una formula personalizzata per garantire che non vengano inseriti spazi nei dati inseriti in un intervallo di celle.

1. Evidenziare l'intervallo richiesto, ad es.: B3:B8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Costume dalla casella a discesa Consenti, quindi digitare la seguente formula:

=B3=SOSTITUTO(B3, ” ” , “”)

La formula utilizza la funzione SOSTITUTO per verificare che non esistano spazi.

4. Clic ok.
5. Digitare "FRU - 124" nella cella B4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati impedisce i duplicati in Excel

Possiamo scrivere una formula personalizzata per impedirci di inserire informazioni duplicate in un intervallo di celle.

1. Evidenziare l'intervallo richiesto, ad es.: B3:B8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=CONTA.SE($B$3:$B$8,B3)<2

La formula utilizza il CONTA.SE funzione e usi ASSOLUTI nell'intervallo B3:B8 per garantire che questo sia l'elenco che il CONTA.SE la funzione guarda quando verifica se ci sono valori duplicati.

4. Clic ok.
5. Digitare "FRU-123" nella cella D4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati esiste nell'elenco in Excel

Possiamo scrivere una formula personalizzata assicurandoci che solo un testo specifico venga inserito in una cella.

1. Evidenziare l'intervallo richiesto, ad es.: D3:D8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Seleziona Personalizzato dalla casella a discesa Consenti, quindi digita la seguente formula:

=CONTA.SE($F$6:$F$8,D3)>0

La formula utilizza il CONTA.SE funzione e usi ASSOLUTI nell'intervallo F3:F8 per garantire che questo sia l'elenco che il CONTA.SE la funzione controlla quando verifica che sia stato immesso il testo corretto.

4. Clic ok.
5. Digita "Singolo" nella cella D4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati non esiste nell'elenco in Excel

Possiamo utilizzare una formula personalizzata per assicurarci che il testo specifico non venga inserito in una cella.

1. Evidenziare l'intervallo richiesto, ad esempio: C3:C8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=CONTA.SE($F$6:$F$8,C3)=0

La formula utilizza il CONTA.SE funzione e usi ASSOLUTI nell'intervallo F3:F8 per garantire che questo sia l'elenco che il CONTA.SE la funzione controlla quando verifica che sia stato immesso il testo corretto.

4. Clic ok.
5. Digita "Manzo" nella cella C4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

Convalida dei dati Solo i numeri vengono inseriti in Excel

Possiamo utilizzare una formula personalizzata per garantire che in una cella venga immesso solo un numero.

1. Evidenziare l'intervallo richiesto, ad esempio: E3:E8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=VAL.NUMERO(F3:F8)

La formula utilizza la funzione VAL.NUMERO per garantire che venga immesso un numero nelle celle dell'intervallo.

4. Clic ok.
5. Digita "nove" nella cella F4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati non supera il valore in Excel

Possiamo usare una formula personalizzata per assicurarci che i valori inseriti in una cella non superino un valore specificato.

1. Evidenziare l'intervallo richiesto, ad esempio: E3:E8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=MI3<=$V$6

La formula e ASSOLUTO nell'intervallo G6 per garantire che questo sia il valore che la regola controlla quando i dati vengono inseriti in E3.

4. Clic ok.
5. Digita "9" nella cella E4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

La convalida dei dati non supera il totale in Excel

Possiamo utilizzare una formula personalizzata per assicurarci che i valori inseriti in un intervallo di celle non superino un valore totale specificato per l'intervallo

1. Evidenziare l'intervallo richiesto, ad esempio: F3:F8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

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

La formula utilizza il SOMMA funzione e usi ASSOLUTI nell'intervallo F3:F8 per garantire che questo sia l'elenco che il SOMMA la funzione guarda quando verifica che il totale del range non sia maggiore del valore inserito in H6.

4. Clic ok.
5. Digita "40" nella cella F4.
6. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla per uscire dal messaggio o Riprova per reinserire il testo corretto nella cella.

Date dei giorni feriali di convalida dei dati solo in Excel.

Possiamo utilizzare una formula personalizzata per garantire che vengano inseriti solo i giorni della settimana quando le date vengono utilizzate in Excel.

1. Evidenziare l'intervallo richiesto, ad esempio: G3:G8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=GIORNO DELLA SETTIMANA(F3,2)<6

La funzione del giorno della settimana verifica che il giorno contenuto nella data non sia un sabato o una domenica.

4. Modificare la data in G5 per mostrare un sabato (es. 9^ns maggio 2022).
5. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

7. Fare clic su Annulla o Riprova per inserire una data alternativa.

Data futura di convalida dei dati solo in Excel

Possiamo creare una formula personalizzata per consentire all'utente di inserire solo una data futura.

1. Evidenziare l'intervallo richiesto, ad esempio: G3:G8.
2. Nella barra multifunzione, seleziona Dati > Strumenti dati > Convalida dati.
3. Selezionare Cliente dalla casella a discesa Consenti, quindi digitare la seguente formula:

=G3>OGGI()

La formula utilizza il OGGI funzione per verificare se la data inserita nella cella è maggiore della data odierna.

4. Modificare la data in G5 a ieri.
5. Se hai utilizzato l'opzione Avviso di errore, verranno visualizzati l'avviso personalizzato e il messaggio di errore. Se non hai utilizzato questa opzione, apparirà l'avviso standard.

6. Fare clic su Annulla o Riprova per inserire una data alternativa.

La convalida dei dati deve iniziare con in Fogli Google

1. Evidenziare l'intervallo richiesto, ad es.: B3:B8.
2. Nel Menu, seleziona Dati > Convalida dati.

3. L'intervallo di celle sarà già compilato.

4. Selezionare Formula personalizzata è dall'elenco a discesa Criteri.

5. Digita la formula.

=ESATTO(SINISTRA(B3,4)”,FRU-“)

6. Seleziona uno dei due Mostra avviso o Rifiuta input se i dati non sono validi.

7. Se necessario, puoi digitare del testo di aiuto per la convalida.

8. Fare clic su Salva.

9. Fare clic su B3 per visualizzare il testo della guida di convalida

10. Tipo FRI-123
11. Se hai selezionato Mostra avviso, apparirà il seguente messaggio.

12. In alternativa, se hai selezionato Rifiuta input su dati non validi, verrà impedito l'inserimento dei dati e sullo schermo apparirà il seguente messaggio.

Il resto degli esempi di formule personalizzate in Fogli Google funzionano esattamente allo stesso modo.