Funzioni statistiche di Excel: INTERCEPT

Riepilogo

Questo articolo illustra la funzione INTERCEPT in Microsoft Excel, illustra come usare la funzione e confronta i risultati per Excel 2003 e per le versioni successive di Excel con i risultati nelle versioni precedenti di Excel.

Ulteriori informazioni

La funzione INTERCEPT(known_y,known_x) restituisce l'INTERCETTA della retta di regressione lineare utilizzata per stimare i valori y dai valori x .

Sintassi

INTERCEPT(known_y's,known_x's)

Gli argomenti , known_y e known_x, devono essere matrici o intervalli di celle che contengono numeri uguali di valori di dati numerici. Spesso INTERCEPT include 2 intervalli di celle contenenti i dati, ad esempio INTERCEPT(A1:A100, B1:B100).

Esempio di utilizzo

Per illustrare la funzione INTERCEPT, creare un foglio di lavoro di Excel vuoto, copiare la tabella seguente, selezionare la cella A1 nel foglio di lavoro di Excel vuoto e quindi incollare le voci in modo che la tabella seguente riempia le celle A1:D13 nel foglio di lavoro.

• In Microsoft Office Excel 2007 fare clic sulla scheda Home , fare clic su Formato nel gruppo Celle e quindi su Adatta automaticamente larghezza colonna.
• In Excel 2003 scegliere Colonna dal menu Formato e quindi fare clic su Adatta selezione automaticamente.

È possibile formattare le celle B2:B7 come Numero con 0 cifre decimali e celle A9:D13 come Numero con 6 cifre decimali.

Le celle A2:A7 e B2:B7 contengono i valori y e x-values che chiamano INTERCEPT nella cella A10.

Nelle versioni di Excel precedenti a Excel 2003, INTERCEPT può presentare errori di arrotondamento. Excel 2003 e versioni successive di Excel migliorano il comportamento di INTERCEPT. INTERCEPT(known_y, known_x) è il risultato della valutazione di AVERAGE(known_y) – SLOPE(known_y, known_x) * AVERAGE(known_x). Anche se il codice per INTERCEPT non è stato modificato direttamente per Excel 2003 e per le versioni successive di Excel, il comportamento di INTERCEPT viene migliorato grazie al codice migliorato per SLOPE.

Se si dispone di una versione precedente di Excel, è possibile usare il foglio di lavoro creato in precedenza per eseguire un esperimento per individuare quando si verificano errori di arrotondamento. L'aggiunta di una costante positiva a ognuna delle osservazioni in B2:B7 non dovrebbe influire sul valore di SLOPE. Se si tracciano coppiex,y con x sull'asse orizzontale e y sull'asse verticale e quindi si aggiunge una costante positiva a ogni valore x, i dati vengono semplicemente spostati a destra. La retta di regressione più adatta ha ancora la stessa pendenza. Tuttavia, i dati spostati hanno un'intercetta diversa.

Con il valore predefinito 0 in D3, SLOPE in A9 è 0,775280899. La cella A10 mostra il valore di INTERCEPT e la cella A11 mostra il valore dell'espressione valutata durante il calcolo di INTERCEPT:

AVERAGE(known_y's) - SLOPE(known_y's, known_x's) * AVERAGE(known_x's)

I valori nelle celle A9 e A10 sono sempre d'accordo perché il valore in A10 è esattamente ciò che restituisce INTERCEPT. SLOPE non deve variare perché si aggiungono costanti positive diverse a quella del known_x. La cella A11 mostra AVERAGE(known_y's) – 0,775280899 * AVERAGE(known_x).Cell A11 shows AVERAGE(known_y's) – 0.775280899 * AVERAGE(known_x's). Poiché SLOPE non deve cambiare e 0,775280899 è il valore di SLOPE quando D3 = 0, anche i valori di questa espressione in A11 devono essere d'accordo con i valori delle celle A9 e A10.

Se si aumenta il valore in D3, si aggiunge una costante più grande a B2:B7. Se D3 <= 7, non sono presenti errori di arrotondamento visualizzati nelle prime 6 posizioni decimali di SLOPE. Ma se si prova 7.25, 7.5, 7.75 e 8, la pendenza in A9 cambia. Di conseguenza, i valori nelle celle A11 (che sono d'accordo con A10) e A12 sono diversi. Tuttavia, i valori in A11 (o A10) e A12 devono essere uguali perché l'aggiunta di una costante al known_x non deve influire su SLOPE.

D7:D13 mostra i valori restituiti da INTERCEPT e i valori restituiti da INTERCEPT se SLOPE non è stato modificato. Queste coppie di valori vengono visualizzate nei casi in cui D3 = 7,5 e 8 rispettivamente. Gli errori di arrotondamento sono diventati così gravi che la divisione per 0 si verifica quando D3 = 8.

Le versioni precedenti di Excel forniscono risposte errate in questi casi perché gli effetti degli errori di arrotondamento sono maggiori con la formula di calcolo usata da queste versioni. Tuttavia, questo esperimento mostra che i casi in cui si verificano gli errori sono estremi.

Se si dispone di Excel 2003 o di una versione successiva di Excel, non esiste alcuna differenza tra i valori comuni in A10 e A11 e il valore in A12 se si prova l'esperimento. Tuttavia, le celle D7:D13 mostrano gli errori di arrotondamento che si ottengono con le versioni precedenti di Excel.

Risultati nelle versioni precedenti di Excel

L'articolo relativo a SLOPE descrive la formula meno affidabile dal lato numerico usata dalle versioni precedenti. La formula richiede un solo passaggio tra i dati. Solo le carenze di SLOPE in queste versioni causano errori di arrotondamento di INTERCEPT nei casi estremi.

Risultati in Excel 2003 e nelle versioni successive di Excel

Excel 2003 e versioni successive di Excel usa una procedura migliorata per calcolare slope. Di conseguenza, le prestazioni di INTERCEPT migliorano. La procedura migliorata richiede due passaggi tra i dati. Anche in questo caso, l'articolo seguente su SLOPE descrive il miglioramento.

Per altre informazioni sui miglioramenti apportati a SLOPE per Excel 2003 e per le versioni successive di Excel, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

828142 funzioni statistiche di Excel: SLOPE

Conclusioni

Poiché Excel 2003 e versioni successive di Excel sostituiscono un approccio a un passaggio con un approccio a due passaggi, le prestazioni numeriche di SLOPE in Excel 2003 e nelle versioni successive di Excel sono migliori rispetto alle versioni precedenti di Excel. Di conseguenza, le prestazioni numeriche di INTERCEPT sono migliori. I risultati in Excel 2003 e nelle versioni successive di Excel non saranno mai meno accurati dei risultati nelle versioni precedenti di Excel.

In genere, non esiste alcuna differenza tra i risultati in Excel 2003 e nelle versioni successive di Excel e i risultati nelle versioni precedenti di Excel perché i dati non si comportano spesso nel modo insolito illustrato da questo esperimento. È molto probabile che l'instabilità numerica venga visualizzata nelle versioni precedenti di Excel quando i dati contengono molte cifre significative e poche variazioni tra i valori dei dati.

La procedura seguente trova la somma delle deviazioni al quadrato relative a una media campione:

1. Trovare la media dell'esempio.
2. Calcolare ogni deviazione al quadrato.
3. Sommare le deviazioni al quadrato.

Questa procedura è più accurata rispetto alla seguente procedura alternativa (nota anche come "formula calcolatrice" perché è stata adatta per l'uso su una calcolatrice per un numero ridotto di punti dati):

1. Trovare la somma dei quadrati di tutte le osservazioni, la dimensione del campione e la somma di tutte le osservazioni.
2. Calcolare la somma dei quadrati di tutte le osservazioni meno ((somma di tutte le osservazioni)^2)/dimensione del campione).

Sostituendo quest'ultima procedura a un passaggio con la routine a due passaggi che trova la media di esempio al primo passaggio e calcola la somma delle deviazioni al quadrato al suo interno nel secondo passaggio, Excel 2003 e versioni successive di Excel migliorano molte altre funzioni. Un breve elenco di tali funzioni include VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ e STEYX. Microsoft ha apportato miglioramenti simili in ognuno dei tre strumenti di analisi della varianza in Analysis ToolPak.