Funzioni statistiche di Excel: Tutorial con esempi, terza parte

Excel fornisce un’ampia gamma di funzioni statistiche che eseguono calcoli dalla media alla distribuzione statistica più complessa e funzioni della linea di tendenza.

In questo articolo andremo ad approfondire le funzioni statistiche di Excel, per il calcolo delle funzioni della linea di tendenza.

Tieni presente che alcune funzioni statistiche sono state introdotte nelle versioni recenti di Excel e quindi non sono disponibili nelle versioni precedenti.

Tempo di lettura stimato: 12 minuti

Funzioni della linea di tendenza

Forecast

La funzione Previsione di Excel prevede un punto futuro su una linea di tendenza lineare adattata a un insieme fornito di valori x e y.

Sintassi

= FORECAST( x, known_y's, known_x's )

argomenti

• x: Un valore x numerico per il quale si desidera prevedere un nuovo valore y.
• known_y's: Una matrice di valori y noti
• known_x's: Una matrice di valori x noti

Tieni presente che la lunghezza dell’array di known_x deve essere uguale a quella di known_y e la varianza di known_x non deve essere zero.

Esempio

Nel foglio di calcolo seguente, la funzione FORECAST di Excel viene utilizzata per prevedere un punto aggiuntivo lungo la linea retta più adatta attraverso una serie di valori x e y noti (memorizzati nelle celle F2:F7 e G2:G7).

Come mostrato nella cella F7 del foglio di calcolo, la funzione per calcolare il valore y previsto in x=7 è :=FORECAST( 7, G2:G7, F2:F7 )

Questo dà il risultato 32.666667 .

Intercept

Tra le funzioni di previsione di Excel troviamo la Intercept. La funzione Intercetta di Excel calcola l’intercetta (il valore all’intersezione dell’asse y) della linea di regressione lineare attraverso un insieme fornito di valori x e y.

Sintassi

= INTERCEPT( known_y's, known_x's )

argomenti

• known_y's: Una matrice di valori y noti
• known_x's: Una matrice di valori x noti

Tieni presente che la lunghezza dell’array di known_x deve essere uguale a quella di known_y e la varianza di known_x non deve essere zero.

Esempio

Il foglio di calcolo seguente mostra un esempio della funzione Intercept di Excel utilizzata per calcolare il punto in cui la linea di regressione lineare attraverso le known_x e le known_y (elencate nelle celle F2:F7 e G2:G7) interseca l’asse y.

Le known_x e le known_y vengono tracciate sul grafico nel foglio di calcolo.

Come mostrato nella cella F9 del foglio di calcolo, la formula per la funzione Intercetta è :=INTERCEPT( G2:G7, F2:F7 )

che dà il risultato 2.4 .

Slope

Un’altra funzione funzione di previsione molto interessante è la Pendenza (Slope) di Excel calcola la pendenza della linea di regressione lineare attraverso un set fornito di valori x e y.

La sintassi della funzione è:

Sintassi

= SLOPE( known_y's, known_x's )

argomenti

• known_y's: Una matrice di valori y noti
• known_x's: Una matrice di valori x noti

Tieni presente che la lunghezza dell’array di known_x deve essere uguale a quella di known_y e la varianza di known_x non deve essere zero.

Esempio

Il foglio di calcolo seguente mostra un esempio della funzione Slope (pendenza) di Excel utilizzata per calcolare la pendenza della linea di regressione lineare attraverso le known_x e le known_y, nelle celle F2:F7 e G2:G7.

Le known_x e le known_y vengono tracciate sul grafico nel foglio di calcolo.

Come mostrato nella cella F9 del foglio di calcolo, la formula per la funzione Intercetta è :=SLOPE( G2:G7, F2:F7 )

che dà il risultato 4.628571429.

Trend

Una funzione di previsione di Excel, molto interessante, è la TREND (Tendenza) di Excel calcola la linea di tendenza lineare attraverso un dato insieme di valori y e (facoltativamente), un dato insieme di valori x.

La funzione estende quindi la linea di tendenza lineare per calcolare ulteriori valori y per un ulteriore set fornito di nuovi valori x.

La sintassi della funzione è:

Sintassi

= TREND( known_y's, [known_x's], [new_x's], [const] )

argomenti

• known_y's: Una matrice di valori y noti
• [known_x's]: Uno o più matrici di valori x noti. Questo è un argomento facoltativo che, se fornito, dovrebbe avere la stessa lunghezza dell’insieme di known_y's. Se omesso, l’insieme di [known_x's] assume il valore {1, 2, 3, …}.
• [new_x’s]: Un argomento facoltativo, che fornisce una o più matrici di valori numerici che rappresentano un insieme di nuovi valori x, per i quali si desidera calcolare i nuovi valori y corrispondenti. Ogni array di [new_x’s] dovrebbe corrispondere a un array di [known_x's]. Se l’ argomento [new_x’s] viene omesso, viene impostato come uguale a [known_x's] .
• [cost]: Un argomento logico facoltativo che specifica se la costante ‘b’, nell’equazione lineare y = mx + b , deve essere forzata ad essere uguale a zero. Se [cost] è TRUE (o se questo argomento viene omesso) la costante b viene trattata normalmente;
• Se [cost] è FALSE la costante b viene impostata su 0 e l’equazione della linea retta diventa y = mx .

Esempio

Nel foglio di calcolo seguente, la funzione Trend di Excel viene utilizzata per estendere una serie di valori x e y che si trovano sulla linea retta y = 2x + 10. I valori x e y noti sono memorizzati nelle celle A2-B5 di del foglio di calcolo e vengono visualizzati anche nel grafico del foglio di calcolo.

Si noti che non è essenziale che i punti forniti si adattino esattamente lungo la linea retta y = 2x + 10 (anche se in questo esempio lo fanno). La funzione Trend di Excel troverà la linea più adatta per qualsiasi insieme di valori fornito.

La funzione Tendenza utilizza il metodo dei minimi quadrati per trovare la linea di adattamento migliore e quindi la utilizza per calcolare i nuovi valori y per i nuovi valori x forniti.

In questo esempio, i valori delle [new_x’s] sono archiviati nelle celle A8-A10 e la funzione Tendenza di Excel è stata utilizzata, nelle celle B8-B10, per trovare i nuovi valori y corrispondenti. Come mostrato nella barra della formula, la formula è := TREND( B2:B5, A2:A5, A8:A10 )

Si vede che la funzione Tendenza nella barra della formula è racchiusa tra parentesi graffe { }. Ciò indica che la funzione è stata immessa come formula di matrice .

Growth

Tra le funzioni di previsione di Excel troviamo la Growth. La funzione Growth di Excel calcola la curva di crescita esponenziale attraverso un determinato insieme di valori y e (facoltativo), uno o più insiemi di valori x. La funzione estende quindi la curva per calcolare ulteriori valori y per un ulteriore set fornito di nuovi valori x.

La sintassi della funzione è:

Sintassi

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

argomenti

• known_y's: Una matrice di valori y noti
• [known_x's]: Uno o più matrici di valori x noti. Questo è un argomento facoltativo che, se fornito, dovrebbe avere la stessa lunghezza dell’insieme di known_y's. Se omesso, l’insieme di [known_x's] assume il valore {1, 2, 3, …}.
• [new_x’s]: Un insieme di nuovi valori x, per i quali la funzione calcola i nuovi valori y corrispondenti. Se omesso, si presuppone che l’insieme di [new_x’s] sia uguale a quello di [known_x's] e la funzione restituisce i valori y che si trovano sulla curva di crescita esponenziale calcolata.
• [cost]: Un argomento logico facoltativo che specifica se la costante ‘b’, nell’equazione lineare y = b * m^x , deve essere forzata ad essere uguale a 1. Se [cost] è TRUE (o se questo argomento viene omesso) la costante b viene trattata normalmente; Se [cost] è FALSE la costante b viene impostata su 1 e l’equazione della linea retta diventa y = mx .

Esempio

Nel foglio di calcolo seguente, la funzione di crescita di Excel viene utilizzata per estendere una serie di valori x e y che si trovano sulla curva di crescita esponenziale y = 5 * 2^x. Questi vengono memorizzati nelle celle A2-B5 del foglio di calcolo e vengono visualizzati anche nel grafico del foglio di calcolo.

La funzione Crescita calcola la curva di crescita esponenziale che si adatta meglio ai valori x e y noti forniti. In questo semplice esempio, la curva più adatta è la curva esponenziale y = 5 * 2^x.

Una volta che Excel ha calcolato l’equazione della curva di crescita esponenziale, può utilizzarla per calcolare i nuovi valori y per i nuovi valori x forniti nelle celle A8-A10.

In questo esempio, i valori di [new_x's] sono archiviati nelle celle A8-A10 e la funzione Growth di Excel è stata inserita nelle celle B8-B10. Come mostrato nella barra della formula, la formula per questo è:=Growth( B2:B5, A2:A5, A8:A10 )

Si vede che la funzione Crescita nella barra della formula è racchiusa tra parentesi graffe { }. Ciò indica che la funzione è stata immessa come formula di matrice .

Si noti che, sebbene i punti nell’esempio precedente si adattino esattamente lungo la curva y = 5 * 2^x, ciò non è essenziale. La funzione Growth di Excel troverà la curva più adatta per qualsiasi insieme di valori fornito.

Funzioni finanziarie

Effect

La funzione Effect di Excel restituisce il tasso di interesse annuo effettivo per un determinato tasso di interesse nominale e un determinato numero di periodi di capitalizzazione all’anno.

Tasso di interesse annuo effettivo

Il tasso di interesse annuo effettivo è una misura di interesse che incorpora la capitalizzazione degli interessi ed è spesso utilizzato per confrontare prestiti finanziari con termini di capitalizzazione diversi.

Il tasso di interesse annuo effettivo viene calcolato utilizzando la seguente equazione:

dove nominal_rate è il tasso di interesse nominale e npery è il numero di periodi di capitalizzazione all’anno.

La sintassi della funzione è:

Sintassi

= EFFECT( nominal_rate, npery )

argomenti

• nominal_rate: Il tasso di interesse nominale (deve essere un valore numerico compreso tra 0 e 1)
• npery: Il numero di periodi di capitalizzazione all’anno (deve essere un numero intero positivo).

Esempio

Il foglio di calcolo seguente mostra tre esempi della funzione Effetto Excel:

Se il risultato della funzione Effect viene visualizzato come decimale o mostra 0%, è probabile che entrambi questi problemi siano dovuti alla formattazione della cella contenente la funzione Effect.

Pertanto il problema può essere risolto formattando la cella in percentuale, con cifre decimali.

Per fare questo:

1. Seleziona le celle da formattare come percentuale.
2. Apri la finestra di dialogo “Formatta celle” utilizzando uno dei seguenti metodi:
   • Fare clic con il tasto destro sulla cella o sull’intervallo selezionato e selezionare l’ opzione Formatta celle… dal menu di scelta rapida;
   • Fare clic sul pulsante di avvio della finestra di dialogo nel raggruppamento Numero all’interno della scheda Home della barra multifunzione di Excel;
   • Utilizzare la scorciatoia da tastiera CTRL-1 (ovvero selezionare il tasto CTRL e tenendolo premuto selezionare il tasto “1” (uno)).
3. Nella finestra di dialogo “Formatta celle”:
   • Assicurati che la scheda Numero nella parte superiore della finestra di dialogo sia selezionata.
   • Selezionare Percentuale dall’elenco Categoria sul lato sinistro della finestra di dialogo .Ciò farà apparire ulteriori opzioni sul lato destro della casella di controllo, che ti permetteranno di selezionare il numero di cifre decimali che desideri vengano visualizzate.
   • Una volta selezionato il numero di cifre decimali che si desidera visualizzare, fare clic su OK .

Nominal

La funzione Nominal di Excel restituisce il tasso di interesse nominale per un determinato tasso di interesse effettivo e un determinato numero di periodi di capitalizzazione all’anno.

La sintassi della funzione è:

Sintassi

= NOMINAL( effect_rate, npery )

argomenti

• effect_rate: Il tasso di interesse effettivo (un valore numerico compreso tra 0 e 1).
• npery: Il numero di periodi di capitalizzazione all’anno (deve essere un numero intero positivo).

Esempio

Nel seguente foglio di calcolo, la funzione Nominal di Excel viene utilizzata per calcolare il tasso di interesse nominale di tre prestiti con condizioni diverse.

Ercole Palmeri