Formule e funzioni di Excel

Formule e funzioni sono il pane quotidiano di Excel. Guidano quasi tutto ciò che è interessante e utile che tu possa mai fare in un foglio di calcolo. Questo articolo introduce i concetti di base che devi conoscere per essere abile con le formule in Excel. Altri esempi qui .

Che cos'è una formula?

Una formula in Excel è un'espressione che restituisce un risultato specifico. Per esempio:

 
=1+2 // returns 3

 
=6/3 // returns 2

Nota: tutte le formule in Excel devono iniziare con un segno di uguale (=).

Riferimenti di cella

Negli esempi sopra, i valori sono 'hardcoded'. Ciò significa che i risultati non cambieranno a meno che non modifichi nuovamente la formula e modifichi un valore manualmente. Generalmente, questa è considerata una cattiva forma, perché nasconde le informazioni e rende più difficile mantenere un foglio di calcolo.

Utilizzare invece i riferimenti di cella in modo che i valori possano essere modificati in qualsiasi momento. Nella schermata sottostante, C1 contiene la seguente formula:

 
=A1+A2+A3 // returns 9

Nota perché stiamo usando riferimenti di cella per A1, A2 e A3, questi valori possono essere modificati in qualsiasi momento e C1 mostrerà comunque un risultato accurato.

Tutte le formule restituiscono un risultato

Tutte le formule in Excel restituiscono un risultato, anche quando il risultato è un errore. Di seguito viene utilizzata una formula per calcolare la variazione percentuale. La formula restituisce un risultato corretto in D2 e ​​D3, ma restituisce un #DIV/0! errore in D4, perché B4 è vuoto:

Esistono diversi modi per gestire gli errori. In questo caso, potresti fornire il valore mancante in B4 o 'catturare' l'errore con il SE ERRORE funzione e visualizzare un messaggio più amichevole (o niente).

Copia e incolla formule

La bellezza dei riferimenti di cella è che si aggiornano automaticamente quando una formula viene copiata in una nuova posizione. Ciò significa che non è necessario inserire ripetutamente la stessa formula di base. Nella schermata sottostante, la formula in E1 è stata copiata negli appunti con Control + C:

Sotto: formula incollata nella cella E2 con Control + V. Notare che i riferimenti alle celle sono cambiati:

Stessa formula incollata su E3. Gli indirizzi delle celle vengono aggiornati di nuovo:

Riferimenti relativi e assoluti

I riferimenti di cella sopra sono chiamati parente Riferimenti. Ciò significa che il riferimento è relativo alla cella in cui vive. La formula in E1 sopra è:

 
=B1+C1+D1 // formula in E1

Letteralmente, questo significa 'cella 3 colonne a sinistra' + 'cella 2 colonne a sinistra' + 'cella 1 colonna a sinistra'. Ecco perché, quando la formula viene copiata nella cella E2, continua a funzionare allo stesso modo.

I riferimenti relativi sono estremamente utili, ma ci sono momenti in cui non vuoi che un riferimento di cella cambi. Un riferimento di cella che non cambierà quando viene copiato è chiamato an riferimento assoluto . Per rendere assoluto un riferimento, usa il simbolo del dollaro ($):

 
=A1 // relative reference =$A // absolute reference

Ad esempio, nella schermata sottostante, vogliamo moltiplicare ogni valore nella colonna D per 10, che viene inserito in A1. Usando un riferimento assoluto per A1, 'blocchiamo' quel riferimento in modo che non cambi quando la formula viene copiata in E2 ed E3:

Ecco le formule finali in E1, E2 ed E3:

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

Notare che il riferimento a D1 si aggiorna quando la formula viene copiata, ma il riferimento a A1 non cambia mai. Ora possiamo facilmente modificare il valore in A1 e tutte e tre le formule vengono ricalcolate. Sotto il valore in A1 è cambiato da 10 a 12:

Questo semplice esempio mostra anche perché non ha senso codificare i valori in una formula. Memorizzando il valore in A1 in un posto e facendo riferimento a A1 con an riferimento assoluto , il valore può essere modificato in qualsiasi momento e tutte le formule associate si aggiorneranno istantaneamente.

Suggerimento: puoi alternare tra sintassi relativa e assoluta con il Tasto F4 .

Come inserire una formula

Per inserire una formula:

1. Seleziona una cella
2. Inserisci un segno di uguale (=)
3. Digita la formula e premi invio.

Invece di digitare i riferimenti di cella, puoi puntare e fare clic, come mostrato di seguito. I riferimenti delle note sono codificati a colori:

Tutte le formule in Excel devono iniziare con un segno di uguale (=). Nessun segno di uguale, nessuna formula:

Come modificare una formula

Per modificare una formula, hai 3 opzioni:

1. Seleziona la cella, modifica nel barra della formula
2. Fare doppio clic sulla cella, modificare direttamente
3. Seleziona la cella, premere F2 , modifica direttamente

Indipendentemente dall'opzione utilizzata, premere Invio per confermare le modifiche al termine. Se desideri annullare e lasciare la formula invariata, fai clic sul tasto Esc.

Video: 20 consigli per inserire le formule

Che cos'è una funzione?

Lavorando in Excel, ascolterai le parole 'formula' e 'funzione' usate frequentemente, a volte in modo intercambiabile. Sono strettamente correlati, ma non esattamente uguali. Tecnicamente, una formula è qualunque espressione che inizia con un segno di uguale (=).

Una funzione, d'altra parte, è una formula con un nome e uno scopo speciali. Nella maggior parte dei casi, le funzioni hanno nomi che riflettono l'uso previsto. Ad esempio, probabilmente conosci il Funzione SOMMA già, che restituisce la somma dei riferimenti dati:

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

Il Funzione MEDIA , come ci si aspetterebbe, restituisce la media dei riferimenti dati:

 
= AVERAGE (1,2,3) // returns 2

E le funzioni MIN e MAX restituiscono rispettivamente i valori minimo e massimo:

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

Excel contiene centinaia di funzioni specifiche . Per iniziare, vedi 101 funzioni chiave di Excel .

Argomenti della funzione

La maggior parte delle funzioni richiede input per restituire un risultato. Questi input sono chiamati 'argomenti'. Gli argomenti di una funzione vengono visualizzati dopo il nome della funzione, tra parentesi, separati da virgole. Tutte le funzioni richiedono parentesi di apertura e chiusura corrispondenti (). Il modello assomiglia a questo:

 
=FUNCTIONNAME(argument1,argument2,argument3)

Ad esempio, il Funzione CONTA.SE conta le celle che soddisfano i criteri e accetta due argomenti, gamma e criteri :

 
= COUNTIF (range,criteria) // two arguments

Nella schermata sottostante, l'intervallo è A1:A5 e il criterio è 'rosso'. La formula in C1 è:

 
= COUNTIF (A1:A5,'red') // returns 2

Video: Come utilizzare la funzione CONTA.SE

Non tutti gli argomenti sono obbligatori. Gli argomenti mostrati tra parentesi quadre sono facoltativi. Ad esempio, il Funzione YEARFRAC restituisce il numero frazionario di anni tra una data di inizio e una data di fine e accetta 3 argomenti:

 
= YEARFRAC (start_date,end_date,[basis])

La data di inizio e la data di fine sono argomenti obbligatori, la base è un argomento facoltativo. Vedi sotto per un esempio di come usare YEARFRAC per calcolare l'età attuale in base alla data di nascita.

Come inserire una funzione

Se conosci il nome della funzione, inizia a digitare. Ecco i passaggi:

1. Immettere il segno di uguale (=) e iniziare a digitare. Excel elencherà le funzioni corrispondenti in base alla digitazione:

Quando vedi la funzione che desideri nell'elenco, usa i tasti freccia per selezionarla (o continua a digitare).

2. Digitare il tasto Tab per accettare una funzione. Excel completerà la funzione:

3. Inserisci gli argomenti richiesti:

4. Premere Invio per confermare la formula:

Combinazione di funzioni (annidamento)

Molte formule di Excel utilizzano più di una funzione e le funzioni possono essere ' nidificato ' l'uno dentro l'altro. Ad esempio, di seguito abbiamo una data di nascita in B1 e vogliamo calcolare l'età attuale in B2:

Il Funzione YEARFRAC calcolerà gli anni con una data di inizio e una data di fine:

Possiamo usare B1 per la data di inizio, quindi utilizzare il Funzione OGGI per fornire la data di fine:

Quando premiamo Invio per confermare, otteniamo l'età attuale in base alla data odierna:

 
= YEARFRAC (B1, TODAY ())

Nota che stiamo usando la funzione TODAY per inserire una data di fine nella funzione YEARFRAC. In altre parole, la funzione TODAY può essere annidata all'interno della funzione YEARFRAC per fornire l'argomento della data di fine. Possiamo fare un passo avanti con la formula e usare il Funzione INT per eliminare il valore decimale:

 
= INT ( YEARFRAC (B1, TODAY ()))

Qui, la formula YEARFRAC originale restituisce 20,4 alla funzione INT e la funzione INT restituisce un risultato finale di 20.

Appunti:

1. La data attuale nelle immagini sopra è il 22 febbraio 2019.
2. Nidificato SE funzioni sono un classico esempio di funzioni di annidamento .
3. Il Funzione OGGI è una rara funzione di Excel senza argomenti richiesti.

Key takeaway: l'output di qualsiasi formula o funzione può essere inserito direttamente in un'altra formula o funzione.

Operatori matematici

La tabella seguente mostra gli operatori matematici standard disponibili in Excel:

Operatori logici

Gli operatori logici forniscono supporto per confronti come 'maggiore di', 'minore di', ecc. Gli operatori logici disponibili in Excel sono mostrati nella tabella seguente:

Video: Come costruire formule logiche

Ordine delle operazioni

Quando risolve una formula, Excel segue una sequenza chiamata 'ordine delle operazioni'. Innanzitutto, vengono valutate tutte le espressioni tra parentesi. Il prossimo Excel risolverà qualsiasi esponente. Dopo gli esponenti, Excel eseguirà moltiplicazioni e divisioni, quindi addizioni e sottrazioni. Se la formula prevede concatenazione , questo accadrà dopo le operazioni matematiche standard. Infine, Excel valuterà operatori logici , se presente.

1. parentesi
2. esponenti
3. Moltiplicazione e Divisione
4. Addizione e sottrazione
5. Concatenazione
6. Operatori logici

Suggerimento: puoi usare il Funzionalità di valutazione per guardare Excel risolvere le formule passo dopo passo.

Converti formule in valori

A volte vuoi sbarazzarti delle formule e lasciare solo i valori al loro posto. Il modo più semplice per farlo in Excel è copiare la formula, quindi incollare, utilizzando Incolla speciale> Valori. Questo sovrascrive le formule con i valori che restituiscono. Puoi usare un scorciatoia da tastiera per incollare i valori oppure utilizzare il menu Incolla nella scheda Home sulla barra multifunzione.

Video: Incolla scorciatoie speciali

come impostare cifre significative in Excel

Qual è il prossimo?

Di seguito sono riportate le guide per aiutarti a saperne di più sulle formule e le funzioni di Excel. Offriamo anche formazione video online .

• 29 consigli per lavorare con le formule e funzioni ( versione video qui )
• 500 esempi di formule con spiegazioni complete
• 101 importanti funzioni di Excel
• Guida a tutte le funzioni di Excel (lavori in corso)
• Errori nelle formule di Excel (esempi e correzioni)
• Criteri formula - 50 esempi
• Formule per la formattazione condizionale
• Come usare F9 per eseguire il debug di una formula (video)
• Errori e correzioni delle formule di Excel (video)