Excel Data Analysis - Convalida dati

La convalida dei dati è uno strumento molto utile e facile da usare in Excel con il quale è possibile impostare le convalide dei dati sui dati immessi che vengono inseriti nel foglio di lavoro.

Puoi farlo per qualsiasi cella del foglio di lavoro

  • Visualizza un messaggio di input su ciò che deve essere inserito in esso.
  • Limita i valori immessi.
  • Fornisci un elenco di valori tra cui scegliere.
  • Visualizza un messaggio di errore e rifiuta l'immissione di dati non validi.

Considerare il seguente Risk Tracker che può essere utilizzato per inserire e tenere traccia delle informazioni sui rischi identificati.

In questo tracker, i dati inseriti nelle seguenti colonne vengono convalidati con vincoli di dati preimpostati e i dati inseriti vengono accettati solo quando soddisfano i criteri di convalida. In caso contrario, riceverai un messaggio di errore.

  • Probability
  • Impact
  • Categoria di rischio
  • Fonte di rischio
  • Status

La colonna Esposizione al rischio avrà valori calcolati e non è possibile inserire alcun dato. Anche la colonnaS. No. è impostato per avere valori calcolati che vengono modificati anche se si elimina una riga.

Ora imparerai come impostare un tale foglio di lavoro.

Prepara la struttura per il foglio di lavoro

Per preparare la struttura per il foglio di lavoro -

  • Inizia con un foglio di lavoro vuoto.
  • Metti l'intestazione nella riga 2.
  • Metti le intestazioni di colonna nella riga 3.
  • Per le intestazioni di colonna Probabilità, impatto ed esposizione al rischio -
    • Fare clic con il tasto destro sulla cella.
    • Fare clic su Formato celle dal menu a discesa.
    • Nella finestra di dialogo Formato celle, fare clic sulla scheda Allineamento.
    • Digitare 90 in Orientamento.
  • Unisci e centra le celle nelle righe 3, 4 e 5 per ciascuna delle intestazioni di colonna.
  • Formatta i bordi per le celle nelle righe 2-5.
  • Regola la larghezza delle righe e delle colonne.

Il tuo foglio di lavoro apparirà come segue:

Impostare valori validi per la categoria di rischio

Nelle celle M5 - M13 inserire i seguenti valori (M5 è l'intestazione e M6 - M13 sono i valori)

Valori di categoria
Utenti finali
Cliente
Gestione
Programma
Programma
Ambiente
Prodotto
Progetto
  • Fare clic sulla prima cella sotto la colonna Categoria di rischio (H6).
  • Fare clic sulla scheda DATI sulla barra multifunzione.
  • Fare clic su Convalida dati nel gruppo Strumenti dati.
  • Seleziona Convalida dati ... dall'elenco a discesa.

Viene visualizzata la finestra di dialogo Convalida dati.

  • Fare clic sulla scheda Impostazioni.
  • In Criteri di convalida, in Allow: elenco a discesa, selezionare l'opzione List.
  • Selezionare l'intervallo M6: M13 nella casella Origine: visualizzata.
  • Seleziona le caselle Ignora vuoto e il menu a discesa In-cell che vengono visualizzati.

Imposta messaggio di input per la categoria di rischio

  • Fare clic sulla scheda Messaggio di input nella finestra di dialogo Convalida dati.
  • Selezionare la casella Show input message quando la cella è selezionata.
  • Nella casella sotto Titolo :, digita Categoria di rischio:
  • Nella casella sotto Messaggio di input: Scegli la categoria del rischio dall'elenco.

Imposta avviso di errore per categoria di rischio

Per impostare un avviso di errore:

  • Fare clic sulla scheda Avviso di errore nella finestra di dialogo Convalida dati.
  • Seleziona la casella Mostra avviso di errore dopo l'inserimento di dati non validi.
  • Seleziona Interrompi in Stile: menu a discesa
  • Nella casella sotto Titolo :, digita Voce non valida:
  • Nella casella sotto Messaggio di errore: digitare Scegli un valore dall'elenco a discesa.
  • Fare clic su OK.

Verificare la convalida dei dati per la categoria di rischio

Per la prima cella selezionata in Categoria di rischio,

  • Vengono impostati i criteri di convalida dei dati
  • Il messaggio di input è impostato
  • L'avviso di errore è impostato

Ora puoi verificare le tue impostazioni.

Fare clic nella cella per la quale sono stati impostati i criteri di convalida dei dati. Viene visualizzato il messaggio di input. Il pulsante a discesa viene visualizzato sul lato destro della cella.

Il messaggio di input viene visualizzato correttamente.

  • Fare clic sul pulsante a discesa sul lato destro della cella. Viene visualizzato l'elenco a discesa con i valori che possono essere selezionati.

  • Verificare i valori nell'elenco a discesa con quelli utilizzati per creare l'elenco a discesa.

Entrambi gli insiemi di valori corrispondono. Nota che se il numero di valori è maggiore, otterrai una barra di scorrimento verso il basso sul lato destro dell'elenco a discesa.

Seleziona un valore dall'elenco a discesa. Appare nella cella.

Puoi vedere che la selezione di valori validi funziona correttamente.

Infine, prova a inserire una voce non valida e verifica l'avviso di errore.

Digita Persone nella cella e premi Invio. Verrà visualizzato il messaggio di errore impostato per la cella.

  • Verifica il messaggio di errore.
  • È possibile scegliere tra Riprova o Annulla. Verifica entrambe le opzioni.

Hai impostato correttamente la convalida dei dati per la cella.

Note - È molto importante controllare l'ortografia e la grammatica dei tuoi messaggi.

Impostare criteri validi per la colonna della categoria di rischio

Ora sei pronto per applicare i criteri di convalida dei dati a tutte le celle nella colonna Categoria di rischio.

A questo punto, devi ricordare due cose:

  • È necessario impostare i criteri per il numero massimo di celle che è possibile utilizzare. Nel nostro esempio, può variare da 10 a 100 in base a dove verrà utilizzato il foglio di lavoro.

  • Non impostare i criteri per l'intervallo di celle indesiderato o per l'intera colonna. Ciò aumenterà inutilmente la dimensione del file. Si chiama formattazione in eccesso. Se ottieni un foglio di lavoro da una fonte esterna, devi rimuovere la formattazione in eccesso, che imparerai nel capitolo su Richiedi in questo tutorial.

Segui i passaggi indicati di seguito:

  • Impostare i criteri di convalida per 10 celle in Categoria di rischio.
  • Puoi farlo facilmente facendo clic nell'angolo in basso a destra della prima cella.
  • Tieni premuto il simbolo + che appare e tiralo verso il basso.

La convalida dei dati è impostata per tutte le celle selezionate.

Fare clic sull'ultima colonna selezionata e verificare.

La convalida dei dati per la colonna Categoria di rischio è stata completata.

Impostare i valori di convalida per l'origine del rischio

In questo caso, abbiamo solo due valori: Interno ed Esterno.

  • Fare clic nella prima cella sotto la colonna Fonte di rischio (I6)
  • Fare clic sulla scheda DATI sulla barra multifunzione
  • Fare clic su Convalida dati nel gruppo Strumenti dati
  • Seleziona Convalida dati ... dall'elenco a discesa.

Viene visualizzata la finestra di dialogo Convalida dati.

  • Fare clic sulla scheda Impostazioni.
  • In Criteri di convalida, nell'elenco a discesa Consenti: selezionare l'opzione Elenco.
  • Digita Interno, Esterno nella casella Origine: visualizzata.
  • Seleziona le caselle Ignora vuoto e il menu a discesa In-cell che vengono visualizzati.

Imposta messaggio di input per origine rischio.

Imposta avviso di errore per fonte di rischio.

Per la prima cella selezionata in Fonte di rischio -

  • Vengono impostati i criteri di convalida dei dati
  • Il messaggio di input è impostato
  • L'avviso di errore è impostato

Ora puoi verificare le tue impostazioni.

Fare clic nella cella per la quale sono stati impostati i criteri di convalida dei dati. Viene visualizzato il messaggio di input. Il pulsante a discesa viene visualizzato sul lato destro della cella.

Il messaggio di input viene visualizzato correttamente.

  • Fare clic sul pulsante freccia a discesa sul lato destro della cella. Viene visualizzato un elenco a discesa con i valori che possono essere selezionati.

  • Controlla se i valori sono gli stessi che hai digitato: Interno ed Esterno.

Entrambi gli insiemi di valori corrispondono. Seleziona un valore dall'elenco a discesa. Appare nella cella.

Puoi vedere che la selezione di valori validi funziona correttamente. Infine, prova a inserire una voce non valida e verifica l'avviso di errore.

Digita Finanziario nella cella e premi Invio. Verrà visualizzato il messaggio di errore impostato per la cella.

  • Verifica il messaggio di errore. Hai impostato correttamente la convalida dei dati per la cella.

  • Impostare criteri validi per la colonna Fonte di rischio

  • Applicare i criteri di convalida dei dati alle celle I6 - I15 nella colonna Fonte di rischio (ovvero lo stesso intervallo di quello della colonna Categoria di rischio).

La convalida dei dati è impostata per tutte le celle selezionate. La convalida dei dati per la colonna Fonte di rischio è stata completata.

Imposta valori di convalida per lo stato

  • Ripeti gli stessi passaggi che hai utilizzato per impostare i valori di convalida per l'origine del rischio.

  • Impostare i valori dell'elenco su Aperto, Chiuso.

  • Applicare i criteri di convalida dei dati alle celle K6 - K15 nella colonna Stato (cioè stesso intervallo di quello della colonna Categoria di rischio).

La convalida dei dati è impostata per tutte le celle selezionate. La convalida dei dati per lo stato della colonna è stata completata.

Impostare i valori di convalida per la probabilità

I valori del punteggio di probabilità di rischio sono compresi tra 1 e 5, 1 è basso e 5 è alto. Il valore può essere qualsiasi numero intero compreso tra 1 e 5, entrambi inclusi.

  • Fare clic nella prima cella sotto la colonna Fonte di rischio (I6).
  • Fare clic sulla scheda DATI sulla barra multifunzione.
  • Fare clic su Convalida dati nel gruppo Strumenti dati.
  • Seleziona Convalida dati ... dall'elenco a discesa.

Viene visualizzata la finestra di dialogo Convalida dati.

  • Fare clic sulla scheda Impostazioni.
  • In Criteri di convalida, nell'elenco a discesa Consenti: selezionare Numero intero.
  • Seleziona tra in Dati:
  • Digita 1 nella casella sotto Minimo:
  • Digita 5 nella casella sotto Massimo:

Imposta messaggio di input per probabilità

Impostare un avviso di errore per probabilità e fare clic su OK.

Per la prima cella selezionata in Probabilità,

  • Vengono impostati i criteri di convalida dei dati.
  • Il messaggio di input è impostato.
  • L'avviso di errore è impostato.

Ora puoi verificare le tue impostazioni.

Fare clic sulla cella per la quale sono stati impostati i criteri di convalida dei dati. Viene visualizzato il messaggio di input. In questo caso, non ci sarà un pulsante a discesa perché i valori di input sono impostati per essere in un intervallo e non da un elenco.

Il messaggio di input viene visualizzato correttamente.

Immettere un numero intero compreso tra 1 e 5 nella cella. Appare nella cella.

La selezione di valori validi funziona correttamente. Infine, prova a inserire una voce non valida e verifica l'avviso di errore.

Digita 6 nella cella e premi Invio. Verrà visualizzato il messaggio di errore che hai impostato per la cella.

Hai impostato correttamente la convalida dei dati per la cella.

  • Impostare criteri validi per la colonna Probabilità.

  • Applicare i criteri di convalida dei dati alle celle E6 - E15 nella colonna Probabilità (ovvero lo stesso intervallo di quello della colonna Categoria di rischio).

La convalida dei dati è impostata per tutte le celle selezionate. La convalida dei dati per la colonna Probabilità è stata completata.

Imposta i valori di convalida per l'impatto

Per impostare i valori di convalida per Impact, ripetere gli stessi passaggi utilizzati per impostare i valori di convalida per la probabilità.

Applicare i criteri di convalida dei dati alle celle F6 - F15 nella colonna Impatto (ovvero lo stesso intervallo della colonna Categoria di rischio).

La convalida dei dati è impostata per tutte le celle selezionate. La convalida dei dati per la colonna Impatto è stata completata.

Impostare la colonna Esposizione al rischio con valori calcolati

L'esposizione al rischio è calcolata come un prodotto della probabilità di rischio e dell'impatto del rischio.

Esposizione al rischio = probabilità * impatto

Digita = E6 * F6 nella cella G6 e premi Invio.

0 verrà visualizzato nella cella G6 poiché E6 e F6 sono vuoti.

Copia la formula nelle celle G6 - G15. 0 verrà visualizzato nelle celle G6 - G15.

Poiché la colonna Esposizione al rischio è destinata ai valori calcolati, non è necessario consentire l'immissione di dati in quella colonna.

  • Seleziona le celle G6-G15

  • Fare clic con il pulsante destro del mouse e nell'elenco a discesa visualizzato, selezionare Formato celle. Viene visualizzata la finestra di dialogo Formato celle.

  • Fare clic sulla scheda Protezione.

  • Seleziona l'opzione Locked.

Questo per garantire che l'immissione di dati non sia consentita in quelle celle. Tuttavia, ciò entrerà in vigore solo quando il foglio di lavoro è protetto, cosa che farai come ultimo passaggio dopo che il foglio di lavoro è pronto.

  • Fare clic su OK.
  • Ombreggiare le celle G6-G15 per indicare che sono valori calcolati.

Formatta i valori del numero di serie

Puoi lasciare all'utente il compito di compilare la colonna S. No.. Tuttavia, se formatti i valori S. No., il foglio di lavoro sembra più presentabile. Inoltre, mostra per quante righe è formattato il foglio di lavoro.

Digita = riga () - 5 nella cella B6 e premi Invio.

1 apparirà nella cella B6. Copia la formula nelle celle B6-B15. Vengono visualizzati i valori 1-10.

Ombreggia le celle B6-B15.

Incartare

Hai quasi finito con il tuo progetto.

  • Nascondi la colonna M che contiene i valori della categoria dati.
  • Formatta i bordi per le celle B6-K16.
  • Fare clic con il tasto destro sulla scheda del foglio di lavoro.
  • Seleziona Proteggi foglio dal menu.

Viene visualizzata la finestra di dialogo Proteggi foglio.

  • Seleziona l'opzione Proteggi foglio di lavoro e contenuto delle celle bloccate.
  • Digita una password sotto Password per rimuovere la protezione del foglio -
    • La password fa distinzione tra maiuscole e minuscole
    • Il foglio protetto non può essere recuperato se si dimentica la password
    • È buona norma tenere da qualche parte un elenco di nomi di fogli di lavoro e password
  • In Consenti a tutti gli utenti di questo foglio di lavoro di: seleziona la casella Seleziona celle sbloccate.

Hai protetto le celle bloccate nella colonna Esposizione al rischio dall'immissione di dati e hai mantenuto modificabili il resto delle celle sbloccate. Fare clic su OK.

Il Confirm Password appare la finestra di dialogo.

  • Reinserisci la password.
  • Fare clic su OK.

Il foglio di lavoro con la convalida dei dati impostata per le celle selezionate è pronto per l'uso.