Per ridurre le imprecisioni nell'immissione dei dati, utilizzo spesso elenchi basati su un intervallo di celle. Consideriamo l'utilizzo di questo strumento utilizzando l'esempio della conservazione della scheda attività.
Per cominciare, su un foglio separato (non è necessario), inseriremo un elenco di valori accettabili in una colonna o riga (Fig. 1a); vedi anche file Excel, foglio “Lista”.
Riso. 1. Elenco dei cognomi: (a) in ordine casuale; (b) in ordine alfabetico.
Scarica in formato, esempi in formato
Questo elenco di cognomi verrà visualizzato nell'elenco a discesa nell'ordine in cui appaiono in questa colonna. È chiaro che per facilitare la ricerca è meglio ordinarli in ordine alfabetico (Fig. 1b).
Diamo alla nostra lista il nome dell'intervallo. Per fare ciò, seleziona un intervallo; nel nostro caso questa è l'area A2:A21 e inserisci il nome dell'intervallo, come mostrato in Fig. 2; nel nostro caso si tratta di “cognomi”:
Riso. 2. Denominazione dell'intervallo
Selezioniamo l'area in cui inseriremo i cognomi (vedi file Excel, foglio “Input”). Nel nostro esempio – A2:A32 (Fig. 3). Andiamo nella scheda Dati, gruppo Lavoro Dati, selezioniamo il comando Convalida Dati:
Riso. 3. Verifica dei dati
Nella finestra di dialogo "Verifica dei valori immessi", vai alla scheda Parametri (Fig. 4). Nel campo “Tipo di dati”, seleziona “Elenco”. Nel campo “Sorgente” indichiamo: (a) l'area di celle in cui è memorizzata la lista; questa opzione è adatta se l'elenco si trova sullo stesso foglio Excel; (b) nome dell'intervallo; Questa opzione può essere utilizzata sia quando la lista si trova sullo stesso foglio Excel, sia quando la lista si trova su un altro foglio Excel (come nel nostro caso). In entrambi i casi, assicurati che ci sia un segno uguale (=) prima del collegamento o del nome.
Riso. 4. Selezione di una fonte dati per l'elenco: (a) sullo stesso foglio; (b) su qualsiasi foglio
E circa altre due opzioni nella scheda "Opzioni":
- Ignora le celle vuote. Se la casella è selezionata, Excel ti consentirà di lasciare la cella vuota. Se la casella non è selezionata, puoi uscire dalla cella solo dopo aver selezionato uno dei nomi nell'elenco. La particolarità dell'opzione è che Excel ti consentirà di spostarti tra le celle (ad esempio utilizzando il tasto Invio o le frecce su/giù), ma non potrai iniziare a digitare, quindi cancellare tutti i caratteri e spostarti in un'altra cella.
- Elenco di celle valide. Se non è presente alcun segno di spunta, quando posizioni il cursore in una cella di input, l'icona dell'elenco non verrà visualizzata accanto alla cella e quindi non sarai in grado di selezionare dall'elenco. Sebbene tutte le altre proprietà per lavorare con un elenco saranno attive, Excel non ti consentirà di inserire un valore arbitrario in una cella.
Andiamo alla scheda "Messaggi di input" nella finestra "Verifica dei valori di input". Seleziona la casella "Visualizza descrizione comando se la cella è corrente". Inserisci il titolo e il testo del messaggio negli appositi campi (Fig. 5). Successivamente, quando l'utente si posiziona su una delle celle dell'area di input (nell'esempio in Fig. 5 - nella cella A6), verrà visualizzato il messaggio da noi creato.
Riso. 5. Impostazione del messaggio di ingresso
Nella finestra “Verifica dei valori immessi”, andare alla scheda “Messaggio di errore” (Figura 6). Seleziona la casella "Mostra messaggio di errore". Nel campo "Tipo", seleziona il tipo di messaggio. Inserisci il titolo e il testo del messaggio. Il messaggio che abbiamo creato apparirà ogni volta che l'utente tenta di inserire un valore non valido.
Riso. 6. Impostazione dei messaggi di errore
Tipi di messaggi di errore accettabili (Fig. 7):
- Fermare– impedisce l'inserimento di dati non validi; pulsante Ripetere consente di tornare al pulsante di input Cancellare Ripetere.
- Avvertimento– avverte dell'inserimento di dati non validi, ma non vieta tale inserimento; pulsante SÌ ti consente di accettare input non validi; pulsante NO permette di continuare a digitare (il valore precedentemente digitato nella cella diventa disponibile per la modifica); pulsante Cancellare cancella una cella e ti consente di ricominciare a digitare o passare a digitare altre celle; Il pulsante è selezionato per impostazione predefinita NO.
- Messaggio– avvisa dell'inserimento di dati non validi; anche se permette che vengano presentati. Questo tipo di messaggio è il più flessibile. Quando viene visualizzato un messaggio informativo, l'utente può premere il pulsante OK per accettare l'immissione di dati non validi oppure premere il pulsante Cancellare cancellare l'iscrizione; Il pulsante è selezionato per impostazione predefinita OK.
Riso. 7. Selezione del tipo di messaggio di errore
Alcuni commenti. 1. Se hai inserito un testo troppo lungo nella finestra Messaggio della scheda Messaggio di errore, la finestra del messaggio di errore sarà troppo ampia (come in Fig. 7); utilizzare l'interruzione di riga Maiusc + Invio nel punto del messaggio in cui si desidera dividere le righe (Figura 8).
Riso. 8. Finestra di messaggio di errore con larghezza ridotta
2. La larghezza dell'elenco a discesa è determinata dalla larghezza della cella a cui viene applicata la convalida dei dati. Mantieni le celle di input sufficientemente larghe per evitare di troncare voci valide più larghe dell'elenco a discesa. Altrimenti, puoi fare la scelta sbagliata in base alle prime lettere della parola.
3. Il numero massimo di voci nell'elenco a discesa è limitato, anche se non troppo :), ovvero al numero 32.767.
4. Se non desideri che gli utenti modifichino l'elenco di revisione, posizionalo su un foglio separato, quindi nascondi e proteggi questo foglio.
Per rendere il foglio di lavoro più semplice da utilizzare per gli utenti, aggiungi elenchi a discesa alle celle. Gli elenchi a discesa consentono agli utenti di selezionare elementi da un elenco creato.
Video
Seleziona la cella del foglio di lavoro in cui desideri posizionare l'elenco a discesa.
Sulla barra multifunzione aprire la scheda Dati e premere il pulsante Verifica dei dati.
Nota: Se il pulsante Verifica dei dati non disponibile, il foglio può essere protetto o condiviso. Sblocca aree specifiche della cartella di lavoro protetta o annulla la condivisione del foglio di lavoro, quindi ripeti il passaggio 3.
Sulla scheda Opzioni nel campo Tipo di dati seleziona l'elemento Lista.
Fare clic sul campo Fonte ed evidenziare l'intervallo dell'elenco. Nell'esempio, i dati si trovano nel foglio Città nell'intervallo A2:A9. Tieni presente che la riga di intestazione non è nell'intervallo perché non è una delle opzioni disponibili per la selezione.
Se puoi lasciare la cella vuota, seleziona la casella di controllo Ignora le celle vuote.
Seleziona la casella Elenco dei valori validi
Apri la scheda Suggerimento di input.
Apri la scheda Messaggio di errore.
Non so quale opzione selezionare nel campo Visualizzazione?
In un nuovo foglio di lavoro, inserisci i dati che desideri visualizzare nell'elenco a discesa. È auspicabile che gli elementi dell'elenco siano contenuti in una tabella Excel. In caso contrario, puoi convertire rapidamente l'elenco in una tabella selezionando una cella qualsiasi nell'intervallo e premendo CTRL+T.
Note:
Lavorare con un elenco a discesa
se la larghezza delle colonne e l'altezza delle righe cambiano durante la visualizzazione di tutte le voci.
Download di esempi
Ti suggeriamo di scaricare un libro di esempio con diversi esempi di verifica dei dati, simile all'esempio in questo articolo. Puoi usarli o creare i tuoi script di verifica dei dati. Scarica esempi di convalida dei dati Excel.
Puoi inserire i dati in modo più rapido e accurato limitando i valori in una cella alle opzioni da un elenco a discesa.
Innanzitutto, crea un elenco di elementi validi nel foglio di lavoro, quindi ordinali o disponili nell'ordine desiderato. Questi elementi possono successivamente fungere da fonte per un elenco a discesa di dati. Se l'elenco è piccolo, puoi facilmente farvi riferimento e inserire gli elementi direttamente nel controllo dati.
Vedi anche
Dopo aver creato il menu a discesa, assicurati che funzioni correttamente. Ad esempio, si consiglia di controllare
La funzione OR restituisce VERO se almeno uno degli argomenti è VERO; restituisce FALSO se tutti gli argomenti sono FALSI.
Sintassi
OR(valore_booleano1; valore_booleano2; ...)
Boolean_value1, Boolean_value2, ... - da 1 a 30 condizioni da testare, che possono essere VERE o FALSE.
Attenzione!
Gli argomenti devono essere valori booleani (TRUE o FALSE) o essere array o riferimenti contenenti valori booleani. Un array è un oggetto utilizzato per ottenere più valori da una singola formula o per lavorare con un insieme di argomenti situati in celle diverse e raggruppati per righe o colonne. L'intervallo dell'array utilizza una formula generale; una costante di array è un gruppo di costanti utilizzate come argomenti.
Se l'intervallo specificato non contiene valori booleani, la funzione OR restituisce il valore di errore #VALORE!
È possibile utilizzare la funzione OR come formula di matrice per verificare se sono presenti valori nell'array. Per immettere una formula di matrice, premere CTRL+MAIUSC+INVIO.
Esempio
| UN | B | |
| 1 | Formula | Descrizione (risultato) |
| 2 | =OR(VERO) | Un argomento è VERO (VERO) |
| 3 | =OR(1+1=1;2+2=5) | Tutti gli argomenti restituiscono FALSO (FALSO) |
| 4 | =OR(VERO, FALSO, VERO) | Almeno un argomento è VERO (TRUE) |
Ulteriori informazioni su Excel.
Determinare quali dati possono essere inseriti nelle celle
1. Seleziona la cella che vuoi controllare.
2. Selezionare Convalida dal menu Dati, quindi selezionare la scheda Opzioni.
3. Determinare il tipo di test richiesto.
Consenti l'immissione solo di valori dall'elenco
1. Nell'elenco Tipo di dati, selezionare Elenco.
2. Fare clic nel campo Origine ed effettuare una delle seguenti operazioni:
per definire una lista in locale inserire i valori della lista, separati da virgole;
Per utilizzare un intervallo di celle a cui è stato assegnato un nome, digitare un segno uguale (=) seguito dal nome dell'intervallo;
3. Selezionare la casella di controllo Elenco dei valori validi.
Consentire l'immissione di valori entro i limiti specificati
3. Immettere il valore minimo, massimo o specifico consentito.
Consenti numeri senza restrizioni
1. Dall'elenco Tipo di dati, selezionare Intero o Reale.
2. Nell'elenco Valore, selezionare il vincolo richiesto. Ad esempio, per impostare i limiti inferiore e superiore, selezionare Tra.
3. Immettere i valori minimo e massimo consentiti oppure definire un valore.
Consenti date e orari entro un intervallo di tempo specifico
1. Nel campo Consenti, selezionare Data o Ora.
2. Nel campo Dati, selezionare il vincolo richiesto. Ad esempio, per consentire date successive a un giorno specifico, seleziona Maggiore di.
3. Immettere un inizio, una fine oppure una data o un'ora specifica.
Consenti testo di una certa lunghezza
1. Selezionare il comando Lunghezza testo nella finestra Tipo di dati.
2. Nel campo Dati, selezionare il vincolo richiesto. Ad esempio, per impostare un numero specifico di caratteri, selezionare Minore o Uguale a.
3. Specificare una lunghezza minima, massima o specifica per il testo.
Calcola valori validi in base al contenuto di un'altra cella.
1. Selezionare il tipo di dati richiesto dall'elenco Tipo di dati.
2. Nel campo Dati, selezionare il vincolo richiesto.
3. Nel campo o nei campi sotto il campo Dati, seleziona la cella che desideri utilizzare per definire valori validi. Ad esempio, per consentire l'immissione delle informazioni sull'account solo se il totale non supera il budget, selezionare Numero di cifre decimali nell'elenco a discesa Tipo di dati, selezionare Minore o uguale a nell'elenco a discesa Dati e in nella casella Massimo, seleziona la cella che contiene l'importo del budget.
Utilizzo di una formula per calcolare le ipotesi
1. Selezionare il tipo Altro nella finestra Tipo di dati.
2. Nel campo Formula, inserire una formula per calcolare il valore booleano (VERO per dati validi o FALSO per dati errati). Ad esempio, per consentire l'inserimento di un valore in una cella per un conto picnic solo se non viene finanziato nulla da un conto discrezionale (cella D6) e anche il budget totale (D20) è inferiore ai RUR 40.000 allocati, è possibile inserire = E(D6=0;D20
4. Determinare se la cella può rimanere vuota.
Se sono consentiti valori vuoti (nulli), selezionare la casella di controllo Ignora celle vuote.
Se non si consente l'immissione di valori vuoti (zero), deselezionare la casella di controllo Ignora celle vuote.
Nota. Se i valori validi sono definiti da un intervallo di celle con un nome assegnato che contiene una cella vuota, selezionando la casella di controllo Ignora celle vuote sarà possibile inserire qualsiasi valore nella cella sottoposta a test. Questo vale anche per tutte le celle a cui fanno riferimento le formule di convalida: se una cella a cui si fa riferimento è vuota, è possibile inserire qualsiasi valore nella cella da testare se è selezionata la casella di controllo Ignora celle vuote.
5. Per visualizzare un messaggio aggiuntivo da digitare quando si seleziona una cella, andare alla scheda Messaggio e selezionare la casella di controllo Mostra suggerimento se la cella è la casella corrente, quindi fornire un titolo e immettere il testo per il messaggio.
6. Determinare in che modo Microsoft Excel segnalerà l'immissione di dati errati.
Istruzioni
1. Vai alla scheda Messaggio di errore e seleziona la casella di controllo Mostra messaggio di errore.
2. Selezionare una delle seguenti opzioni per il campo Visualizza.
Per visualizzare un messaggio informativo che non impedisca l'inserimento di dati errati, selezionare Dettagli.
Per visualizzare un avviso che non impedisca di inserire dati errati, selezionare Avviso.
Per evitare che vengano inseriti dati errati, selezionare Interrompi.
3. Specificare un titolo e immettere il testo per il messaggio (fino a 225 caratteri).
Nota. Se non vengono immessi un titolo e un testo, il titolo predefinito è "Microsoft Excel" e viene visualizzato il messaggio "Il valore inserito non è corretto. L'insieme di valori che possono essere inseriti in una cella è limitato."
Nota. La convalida dei valori immessi in una cella non formatta la cella.
Se stai creando un foglio di lavoro che richiede agli utenti di inserire dati, puoi limitare l'input a un intervallo specifico di date o numeri oppure assicurarti che vengano immessi solo numeri interi positivi. Excel può limitare l'immissione di dati in determinate celle utilizzando la convalida dei dati, richiedere di immettere dati validi quando una cella è evidenziata e visualizzare un messaggio di errore quando l'utente inserisce dati non validi.
Limitare l'immissione dei dati
Limitare l'immissione dei dati
Richiedi voci valide
Quando gli utenti fanno clic su una cella che presenta requisiti di immissione dati, è possibile visualizzare un messaggio che spiega quali dati sono validi.
Visualizzazione di un messaggio di errore quando si immettono dati non validi
Se sono presenti restrizioni sui dati e l'utente inserisce dati non validi in una cella, è possibile visualizzare un messaggio che descrive l'errore.
Aggiungi la convalida dei dati a una cella o a un intervallo di celle
Nota: I primi due passaggi in questa sezione servono per aggiungere qualsiasi tipo di convalida dei dati. I passaggi da 3 a 7 sono specifici per la creazione di un elenco a discesa.
Seleziona una o più celle a cui vuoi applicare il controllo.
Sulla scheda Dati nel gruppo Lavorare con i dati fare clic sul pulsante Verifica dei dati.
Sulla scheda Opzioni nella sezione Permettere fare clic sul pulsante Lista.
Nel campo Fonte inserire i valori separati da punto e virgola. Ad esempio, inserisci " basso", "medio", "alto".".
Assicurati che la casella di controllo sia selezionata Elenco dei valori validi. In caso contrario, la freccia del menu a discesa non verrà visualizzata accanto alla cella.
Per specificare come gestire i valori vuoti (null), selezionare o deselezionare la casella di controllo Ignora le celle vuote.
Verificare che la convalida dei dati funzioni correttamente. Prova a inserire dati validi e poi non validi in una cella per assicurarti che le impostazioni di convalida vengano applicate come desideri e che i tuoi messaggi vengano visualizzati al momento giusto.
Note:
Utilizzo di altri tipi di convalida dei dati
La tabella elenca altri tipi di convalida dei dati e come applicarli ai dati nel foglio di lavoro.
|
Effettuare le seguenti operazioni: |
|
|---|---|
|
Consentire l'immissione solo di numeri interi da un intervallo specifico |
Sulla lista Permettere selezionare il valore Intero. Nel campo Dati selezionare il tipo di restrizione richiesto. Ad esempio, per impostare i limiti superiore e inferiore, selezionare Limite Allineare. Immettere il valore minimo, massimo o specifico consentito. Puoi anche inserire una formula che restituisca un valore numerico. Ad esempio, supponiamo che tu stia controllando i valori nella cella F1. Per impostare l'importo minimo delle detrazioni pari al valore di questa cella moltiplicato per 2, selezionare Maggiore o uguale a nel campo Dati e inserisci la formula =2*F1 nel campo Valore minimo. |
|
Consenti l'immissione solo di numeri decimali compresi in un determinato intervallo |
|
|
Consenti l'inserimento solo di date comprese in un intervallo di tempo specificato |
Seguire i passaggi 1-2 sopra. Nel campo Permettere selezionare il valore Data. Nel campo Dati selezionare il tipo di restrizione richiesto. Ad esempio, per consentire date successive a un giorno specifico, seleziona la restrizione Di più. Inserisci una data di inizio, fine o specifica consentita. Puoi anche inserire una formula che restituisca una data. Ad esempio, per impostare l'intervallo di tempo tra la data corrente e una data 3 giorni successiva alla data corrente, selezionare Fra nel campo Dati, quindi inserisci =OGGI() nel campo data di inizio e poi entra =OGGI()+3 nel campo Data di completamento. |
|
Consenti l'inserimento solo dell'ora entro un intervallo specificato |
Seguire i passaggi 1-2 sopra. Nel campo Permettere selezionare il valore Tempo. Nel campo Dati selezionare il tipo di restrizione richiesto. Ad esempio, per consentire il tempo fino a un'ora specifica del giorno, selezionare la restrizione meno. Specificare un'ora di inizio, un'ora di fine o un'ora specifica da consentire. Se vuoi inserire l'ora esatta, usa il formato hh:mm. Ad esempio, se la cella E2 ha un'ora di inizio alle 8:00 e la cella F2 ha un'ora di fine alle 17:00 e desideri limitare le riunioni a quell'ora, seleziona fra nel campo Dati e poi entra =E2 nel campo Ora di inizio E =F2 nel campo Ora di fine. |
|
Consentire l'inserimento solo di testo di una certa lunghezza |
Seguire i passaggi 1-2 sopra. Nel campo Permettere selezionare il valore Lunghezza del testo. Nel campo Dati selezionare il tipo di restrizione richiesto. Ad esempio, per impostare un determinato numero di caratteri, selezionare il limite Minore o uguale a. In questo caso vorremmo limitare l'immissione a 25 caratteri, quindi seleziona la casella inferiore o uguale a nel campo dati ed entra 25 nel campo massimo . |
|
Calcola un valore valido in base al contenuto di un'altra cella |
Seguire i passaggi 1-2 sopra. Nel campo Permettere selezionare il tipo di dati richiesto. Nel campo Dati selezionare il tipo di restrizione richiesto. Nel campo o nei campi situati sotto il campo Dati, seleziona la cella che desideri utilizzare per definire valori validi. Ad esempio, per consentire l'inserimento delle informazioni sulla fattura solo quando il totale non supera il budget nella cella E1, selezionare Numero di cifre decimali sulla lista Permettere, il vincolo "Minore o uguale a" si trova nell'elenco "Dati" e nel campo Valore massimo inserire >= =E1. |
Esempi di formule per la validazione dei dati
Note:
Gli esempi seguenti utilizzano l'opzione personalizzata durante la creazione di formule condizionali. In questo caso il contenuto del campo "Dati" non ha importanza.
Gli screenshot in questo articolo provengono da Excel 2016; ma la funzionalità è la stessa in Excel per il Web.
A | Inserisci la formula |
|---|---|
|
Il valore nella cella contenente il codice prodotto (C2) inizia sempre con il prefisso standard "ID-" ed è lungo almeno 10 (più di 9) caratteri. |
= AND (SINISTRA (C2; 3) = "ID-"; LUNGHEZZA (C2) >9)  |
|
=ETESTO(D2)  |
|
|
Il valore nella cella contenente la data di nascita di qualcuno (B6) era maggiore del numero di anni elencati nella cella B4. |
=SE(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)  |
|
Tutti i dati nell'intervallo di celle A2:A10 contenevano valori univoci. |
Un punto importante nella creazione di un foglio di lavoro leggibile è garantire che i valori immessi nel foglio di lavoro siano corretti. Sebbene sia impossibile individuare ogni errore di battitura o di ortografia, puoi impostare una regola di convalida per garantire che le informazioni inserite nelle celle soddisfino determinati standard.
Per creare una regola di convalida, aprire la finestra di dialogo Esame valori di input nel menu Dati , paragrafo Esame .
Finestra di dialogo Convalida dei valori immessi può essere utilizzato per determinare il tipo di dati che devono essere immessi in una cella particolare e quindi, a seconda del tipo di dati selezionato, per impostare le condizioni che le informazioni devono soddisfare prima di poter essere visualizzate nella cella. La figura seguente mostra come fare in modo che, ad esempio, in una cella possano essere inseriti solo valori compresi tra 1000 e 2000.
Naturalmente, sarebbe irragionevole vietare l'inserimento di informazioni inappropriate in una cella senza visualizzare un suggerimento per aiutare l'utente a capire in quale cella inserire determinati dati. Excel ti consente di creare descrizioni comandi che indicano all'utente quali informazioni devono essere inserite in una cella, ecc. se non soddisfa queste condizioni, visualizza un messaggio di errore.
È possibile disattivare la convalida dei dati in una cella facendo clic sul pulsante Chiaro tutto nell'angolo inferiore sinistro della finestra di dialogo Esame valori immessi nella scheda Opzioni .
Esercizio: in questo esercizio creerai una regola di convalida che limita le letture del contatore immesse tra 0 e 200. Aggiungi un messaggio sui limiti di intervallo e crea un messaggio di errore che viene visualizzato quando tenti di inserire un importo inferiore a 0 e maggiore di 200.
Vai al foglio " IO _settimana »
Seleziona il gruppo di celle C8:Z14.
Nel menu Dati seleziona l'elemento Esame . Esame Apparirà una finestra di dialogo inserito valori Opzioni .
, apri in una scheda In linea Tipo di dati fare clic sul pulsante dell'elenco e selezionare Numeri interi . Sotto la linea Senso appariranno i campi Minimo .
E . Massimo Fra .
, apri in una scheda Senso Fare clic sul pulsante dell'elenco nella riga Minimo e seleziona
immettere 0. Nella riga inserisci 200. .
Deseleziona la casella Messaggio Ignora le celle vuote
, apri in una scheda Vai alla scheda per l'input. Intestazione .
, apri in una scheda Messaggio comporre
Deseleziona la casella Entra nella lettura .
, apri in una scheda Visualizzazione entrare. Avvertimento Messaggio di errore
, apri in una scheda Vai alla scheda selezionare facendo clic sul pulsante dell'elenco. L'icona sotto la linea cambierà in un'icona di avviso..
entrare Errore Consiglio. Se esci dal campo Messaggio di errore ?"
vuoto. Excel visualizzerà il messaggio predefinito: " OK Il valore inserito non è corretto. I valori che possono essere inseriti in una cella sono limitati. Continuare Intestazione Clic .".
Una descrizione comando con il nome " 2501 "e testo" (Inserisci la lettura dello strumento ). Nella cella C8, inserisci facendo clic sul pulsante dell'elenco. L'icona sotto la linea cambierà in un'icona di avviso. e premere
vuoto. Excel visualizzerà il messaggio predefinito: " Ripetere Entra
Viene visualizzato un messaggio di avviso con il titolo e testo predefinito. e inserisci un valore valido compreso tra 0 e 200. Barra degli strumenti B .
