Le 10 funzioni excel più importanti
1. Funzione SE
La funzione SE restituisce un valore se la condizione specificata è VERA, altrimenti un altro valore. In poche parole, la funzione SE può prima testare una condizione e restituire un valore in base al risultato di tale condizione.
Sintassi
SE(test_logico, valore_se_vero, valore_se_falso)
argomenti
- test_logico: la condizione che desideri valutare.
- value_if_true: il valore che vuoi ottenere se questa condizione è TRUE.
- value_if_false: il valore che vuoi ottenere se questa condizione è FALSE.
Commenti
- Il numero massimo di condizioni annidate che è possibile eseguire è 64.
- È possibile utilizzare gli operatori di confronto per valutare una condizione.
Esempio
Nell’esempio seguente abbiamo utilizzato un operatore di confronto per valutare condizioni diverse.
- Abbiamo utilizzato un testo specifico per ottenere il risultato se la condizione è soddisfatta o meno.
- Puoi anche usare TRUE e FALSE per ottenere il risultato.
- Se salti la specifica di un valore per ottenere il risultato, se la condizione è VERA restituirà zero.
- E se salti la specifica di un valore per ottenere il risultato, se la condizione è FALSA, restituirà zero.
Nell’esempio seguente, abbiamo utilizzato la funzione SE per creare una formula di nidificazione.
Abbiamo specificato una condizione e se quella condizione è falsa abbiamo usato un altro IF per valutare un’altra condizione ed eseguire un’attività e se quella condizione è FALSA abbiamo usato un altro IF.
In questo modo, abbiamo utilizzato IF cinque volte per creare una formula di nidificazione. Puoi usare la stessa cosa 64 volte per una formula di nidificazione.
2.Funzione SEERRORE
La funzione SEERRORE restituisce un valore specifico se si verifica un errore. In parole semplici può testare il valore e se quel valore è un errore restituisce il valore specificato.
Sintassi
SEERRORE(valore; valore_se_errore)
argomenti
- valore: il valore di cui si desidera verificare l’errore.
- value_if_error: il valore che desideri recuperare quando si verifica un errore.
Commenti
- La funzione SEERRORE riguarda il verificarsi di un errore, non il tipo di errore.
- Se ignori il valore o value_if_error restituirà 0 nel risultato.
- Può verificare #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? e #NULL!.
- Se valuti un array, restituirà un array di risultati per ogni elemento specificato.
Esempio
Nell’esempio seguente, abbiamo utilizzato la funzione SEERRORE per sostituire #DIV/0! con testo significativo.
IFERROR è compatibile solo con le versioni 2007 e precedenti. Per risolvere questo problema, è possibile utilizzare ISERROR.
3.Funzione TRONCA
La funzione TRUNC restituisce un numero intero dopo aver troncato il numero originale. In termini semplici, rimuove le cifre decimali da un numero con una precisione specifica e quindi restituisce la parte intera del risultato.
Sintassi
TRUNC(numero, [numero_cifre])
argomenti
- numero: il numero che desideri troncare.
- [num_digits]: un numero per specificare la precisione per troncare un numero.
Commenti
- Se ignori la specifica di multipli, verrà restituito un errore.
- Arrotonda da zero.
- Se hai due multipli alla stessa distanza, restituirà il multiplo più alto del numero che stai arrotondando.
Esempio
Nell’esempio seguente, abbiamo utilizzato TRUNC per troncare i dati e rimuovere l’ora dalle date.
4. Funzione SOMMA.SE
La funzione SOMMA.SE restituisce la somma dei numeri che soddisfano la condizione specificata . In termini semplici, considera e calcola solo la somma dei valori che soddisfano la condizione.
Sintassi
SOMMA.SE(intervallo, criteri, [intervallo_somma])
argomenti
- intervallo: un intervallo di celle da cui si desidera verificare i criteri.
- criterio: un criterio che può essere un numero, un testo, un’espressione, un riferimento di cella o una funzione.
- [sum_range]: un intervallo di celle contenente i valori che desideri sommare.
Commenti
- Se intervallo_somma viene omesso, le celle nell’intervallo verranno sommate.
- Assicurati di utilizzare virgolette doppie per specificare criteri di testo o che includano simboli matematici, che devono essere racchiusi tra virgolette doppie.
- La dimensione dell’intervallo dei criteri e dell’intervallo della somma deve avere la stessa dimensione.
Esempio
Nell’esempio seguente, abbiamo specificato A1:A9 come intervallo di criteri e B1:B9 come intervallo di somma e successivamente abbiamo specificato i criteri in A12 che ha il valore C.
Puoi anche inserire criteri direttamente nella funzione. Nell’esempio seguente abbiamo utilizzato un carattere jolly asterisco per specificare un criterio che contiene l’alfabeto “S”.
E, se ignori l’intervallo della somma, ti darà la somma dell’intervallo dei criteri. Ma questo sarà possibile solo se l’intervallo di criteri ha valori numerici.
5. Funzione INDICE
La funzione INDICE restituisce un valore da un elenco di valori in base al suo numero di indice. In termini semplici, INDICE restituisce un valore da un elenco di valori ed è necessario specificare la posizione di quel valore.
Sintassi
INDEX ha due sintassi diverse. Nel primo puoi usare la forma di un array di un indice per ottenere semplicemente un valore da un elenco usando la sua posizione.
INDICE(matrice, numero_riga, [numero_colonna])
Nel secondo , puoi utilizzare un modulo di sponsorizzazione meno utilizzato nella vita reale, ma puoi utilizzarlo se hai più di una gamma da promuovere.
INDEX(riferimento, numero_riga, [numero_colonna], [numero_area])
argomenti
- matrice: un intervallo di celle o una costante di matrice.
- riferimento: un intervallo di celle o più intervalli.
- row_number: numero di riga da cui si desidera ottenere il valore.
- [col_number]: il numero della colonna da cui vuoi ottenere il valore.
- [numero_area]: se ti riferisci a più intervalli di celle (utilizzando la sintassi di riferimento), specifica un numero per fare riferimento a un intervallo tra tutti.
Commenti
- Quando vengono specificati gli argomenti row_num e column_num, verrà restituito il valore nella cella all’intersezione dei due.
- Se specifichi row_num o column_num come 0 (zero), restituirà rispettivamente l’array di valori per l’intera colonna o riga.
- Quando row_num e column_num non rientrano nell’intervallo, verrà restituito #REF! errore.
- Se area_number è maggiore degli intervalli di numeri specificati, restituirà #REF!.
Esempio 1 – Utilizzo di ARRAY per ottenere un valore da un elenco
Nell’esempio seguente abbiamo utilizzato la funzione INDICE per ottenere la quantità relativa al mese di giugno. Nell’elenco, Jun è in sesta posizione (6a riga), ecco perché ho specificato 6 in row_number. INDEX ha restituito il valore 1904 nel risultato.
E se ti riferisci a un intervallo con più di una colonna, devi specificare il numero di colonna.
Esempio 2 – Utilizzo di REFERENCE per ottenere il valore di più elenchi
Nell’esempio seguente, invece di selezionare l’intero intervallo in una volta, l’ho selezionato in tre intervalli diversi. Nell’ultimo argomento abbiamo specificato 2 in area_number che definirà l’intervallo da utilizzare da questi tre diversi intervalli.
Ora nella seconda riga ci riferiamo alla 5a riga e alla 1a colonna. INDICE ha restituito il valore 172 che si trova nella quinta riga della seconda riga.
6.Funzione CERCA.VERT
La funzione CERCA.VERT cerca un valore nella prima colonna di una tabella e restituisce il valore nella stessa riga del valore corrispondente utilizzando il numero di indice. In termini semplici, esegue una ricerca verticale.
Sintassi
CERCA.VERT(valore_ricerca,array_tabella,num_indice_col,ricerca_intervallo)
argomenti
- lookup_value: un valore che desideri cercare in una colonna. Puoi fare riferimento a una cella contenente il valore di ricerca o inserire tale valore direttamente nella funzione.
- table_array: un intervallo di celle, un intervallo denominato da cui si desidera trovare il valore.
- col_index_num: un numero rappresenta il numero di colonna da cui si desidera recuperare il valore.
- range_lookup: utilizza false o 0 per una corrispondenza esatta e true o 1 per una corrispondenza corretta. L’impostazione predefinita è Vero.
Commenti
- Se CERCA.VERT non trova il valore che stai cercando, restituirà un #N/A.
- CERCA.VERT può darti solo il valore che si trova a destra del valore di ricerca. Se vuoi guardare dal lato destro, puoi usare INDEX e MATCH.
- Se utilizzi la corrispondenza esatta, corrisponderà solo al valore primo nella colonna.
- Puoi anche utilizzare i caratteri jolly con CERCA.VERT .
- Puoi usare VERO o 1 se desideri una corrispondenza corretta e FALSO o 0 per una corrispondenza esatta.
- Se utilizzi una corrispondenza corretta (Vero): restituirà il valore più piccolo successivo nell’elenco se non esiste una corrispondenza esatta.
- Se il valore che stai cercando è inferiore al valore più piccolo nell’elenco, CERCA.VERT restituirà #N/D.
- Se c’è un valore esatto che stai cercando, ti darà quel valore esatto.
- Assicurati di aver ordinato l’elenco in ordine crescente.
Esempio
1. Utilizzo di CERCA.VERT per le categorie
Nell’esempio seguente, abbiamo un elenco di studenti con i voti ricevuti e nella colonna dei commenti vogliamo un voto basato sui loro voti.
Nell’elenco dei marchi sopra, vorremmo aggiungere commenti in base alla gamma di categorie sottostante.
In questo abbiamo due opzioni da utilizzare.
La PRIMA è creare una formula di nidificazione con IF, che richiede un po’ di tempo, e la SECONDA opzione è creare una formula con CERCA.VERT con una corrispondenza adeguata. E la formula sarà:
=CERCA.VERT(B2,$E$2:$G$5,3,VERO)
Come funziona
Utilizzo la colonna “MIN MARKS” per abbinare il valore di ricerca e recuperare il valore dalla colonna “Osservazioni”.
Ho già detto che quando usi TRUE e non esiste un valore di ricerca con corrispondenza esatta, verrà restituito il valore più piccolo successivo dal valore di ricerca. Ad esempio, quando cerchiamo il valore 77 nella tabella delle categorie, 65 è il valore più piccolo dopo 77.
Questo è il motivo per cui abbiamo ottenuto il punteggio “Buono” nei commenti.
2. Gestione degli errori nella funzione CERCA.VERT
Uno dei problemi più comuni che si verificano quando si utilizza CERCA.VERT è che verrà visualizzato #N/A ogni volta che non viene trovata alcuna corrispondenza. Ma la soluzione a questo problema è semplice e facile. Lascia che te lo mostri con un semplice esempio.
Nell’esempio seguente abbiamo un elenco di nomi e le loro età e nella cella E6 utilizziamo la funzione CERCA.VERT per cercare un nome nell’elenco. Ogni volta che digito un nome che non è nell’elenco ottengo #N/A.
Ma quello che voglio qui è visualizzare un messaggio significativo invece dell’errore. La formula sarà: =IFNA(CERCA.VERT(D6,Foglio3!$A$1:$B$14,2,0),”Non trovato”)
Come funziona : IFNA può testare un valore per #N/A e se c’è un errore puoi specificare un valore al posto dell’errore.
7. Funzione SENA
La funzione SENA restituisce un valore specifico se si verifica un errore #N/D. A differenza di IFERROR, valuta solo l’errore #N/D e restituisce il valore specificato.
Sintassi
SENA(valore; valore_se_na)
argomenti
- valore: il valore che desideri verificare per l’errore #N/D.
- value_if_na: il valore che desideri restituire se si è verificato un errore.
Commenti
- Se non specifichi alcun argomento, IFNA lo tratterà come una stringa vuota (“”).
- Se un valore è un array, restituirà il risultato come array.
- Ignorerà tutti gli altri errori #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? e #NULL!.
Esempio
Nella funzione CERCA.VERT, #N/A si verifica quando il valore di ricerca non è nell’intervallo di ricerca e per questo abbiamo specificato un messaggio significativo utilizzando IFNA.
Nota: IFNA è stato introdotto in Excel 2013, quindi non è disponibile nelle versioni precedenti.
8. Funzione CASUALE
La funzione CASUALE restituisce un numero casuale compreso tra 0 e 1. In termini semplici, puoi generare un numero casuale compreso tra 0 e 1 (aggiorna il suo valore ogni volta che apporti una modifica al foglio di lavoro).
Sintassi
CASUALE()
argomenti
- Non ci sono argomenti da specificare nelle funzioni RAND
Commenti
- Se metti zero in un multiplo, restituirà zero nel risultato.
- Se ignori la specifica di multipli, verrà restituito un errore.
- Arrotonda da zero.
- Se hai due multipli alla stessa distanza, restituirà il multiplo più alto del numero che stai arrotondando.
Esempio
Oltre ad avere numeri compresi tra 0 e 1, puoi anche utilizzare RAND per numeri casuali tra due numeri specifici. Nell’esempio seguente, l’ho usato per creare una formula che genera un numero casuale compreso tra 50 e 100.
Quando inserisci questa formula in una cella, restituisce un numero compreso tra 100 e 50 moltiplicando i valori restituiti dal RAND con l’equazione che abbiamo utilizzato. Per comprendere questa formula dobbiamo dividerla in tre parti:
- Innanzitutto, quando rileva il numero più basso da quello più alto, ottieni la differenza tra i due.
- Quindi moltiplica questa differenza per il numero casuale restituito dopo la sottrazione.
- E terzo, aggiungi questo numero con il numero rimanente più basso nella terza parte dell’equazione.
Correlato: Come generare rapidamente lettere casuali in Excel
9. Funzione SOMMA
La funzione SUM restituisce la somma dei valori forniti . In termini semplici, con la funzione SOMMA puoi calcolare la somma di una lista di valori (puoi inserire direttamente un valore nella funzione o fare riferimento a un intervallo di celle.
Sintassi
SOMMA(numero1,[numero2],…)
argomenti
- numero1 : numero, intervallo di celle contenenti numeri o singola cella contenente un numero.
- [numero2] : numero, intervallo di celle contenenti numeri o singola cella contenente un numero.
Commenti
- Ignora i valori di testo.
Esempio
Nell’esempio seguente, puoi inserire i numeri direttamente nella funzione separandoli con virgole.
Puoi anche semplicemente fare riferimento a un intervallo per calcolare la somma dei numeri e se è presente del testo, un valore logico o una cella vuota, li ignorerà.
Se è presente un valore di errore in una cella a cui ti riferisci, verrà restituito #N/A nel risultato.
Se hai valori numerici formattati come testo, li ignorerà. Si consiglia di convertirli in numeri prima di utilizzare SOMMA.
10. Funzione OR
La funzione OR restituisce un valore booleano (VERO o FALSO) dopo aver verificato le condizioni specificate. In termini semplici, puoi testare più condizioni con la funzione AND che restituisce VERO se una di queste condizioni (o tutte) è VERA e restituisce FALSO solo se tutte queste condizioni sono FALSE.
Sintassi
OR(logico1, [logico2], …)
argomenti
- logico1: condizione che desideri verificare.
- [logico2]: condizioni aggiuntive che desideri verificare.
Commenti
- I valori verranno ignorati se la cella o la tabella di riferimento contiene una cella o un testo vuoto.
- Il risultato delle condizioni deve essere un valore logico (VERO o FALSO).
- Restituirà un errore se non viene restituito alcun valore logico.
Esempio
Nell’esempio seguente, abbiamo creato una condizione utilizzando la funzione SE secondo cui se uno studente ottiene un punteggio superiore a 60 in una delle due materie, la formula restituisce VERO.
Ora nell’esempio seguente abbiamo utilizzato un numero per ottenere valori logici in una formula. È inoltre possibile eseguire la condizione sopra descritta in ordine inverso.
Puoi usare VERO e FALSO al posto dei numeri. La funzione OR tratta questi valori logici come numeri.
Altri tutorial
Funzioni statistiche / Funzioni data / Stringa – Funzioni testo / Funzioni finanziarie