Funzioni statistiche di Excel: GROWTH

Riassunto

Questo articolo descrive la funzione GROWTH in Excel, illustra come viene usata la funzione e confronta i risultati della funzione per Excel 2003 e per le versioni successive di Excel con i risultati di GROWTH nelle versioni precedenti di Excel. GROWTH viene valutato chiamando la funzione correlata, LINEST. Vengono riepilogate le modifiche estese apportate a LINEST per Excel 2003 e per le versioni successive di Excel e le relative implicazioni per GROWTH.

Informazioni su Microsoft Excel 2004 per Macintosh

Le funzioni statistiche in Excel 2004 per Mac sono state aggiornate utilizzando gli stessi algoritmi usati per aggiornare le funzioni statistiche in Excel 2003 e nelle versioni successive di Excel. Tutte le informazioni contenute in questo articolo che descrivono il funzionamento di una funzione o la modalità di modifica di una funzione per Excel 2003 o per le versioni successive di Excel si applicano anche a Excel 2004 per Mac.

Altre informazioni

La funzione GROWTH(known_y, known_x, new_x, costante) viene usata per eseguire un'analisi di regressione in cui viene adattata una curva esponenziale. Viene utilizzato un criterio dei minimi quadrati e GROWTH cerca di trovare il miglior adattamento in base a tale criterio. Known_y rappresentano i dati sulla "variabile dipendente" e known_x rappresentano i dati in una o più "variabili indipendenti". Il file della Guida GROWTH illustra rari casi in cui il secondo o il terzo argomento può essere omesso.

Supponendo che siano presenti p variabili predittive, GROWTH chiama essenzialmente LOGEST. LOGEST si adatta a un'equazione della forma:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

I valori dei coefficienti, b, m1, m2, ..., mp sono determinati per offrire il miglior adattamento ai dati y.

Se l'ultimo argomento "constant" è impostato su TRUE, si vuole che il modello di regressione includa il coefficiente moltiplicativo b nel modello di regressione. Se impostato su FALSE, b viene escluso impostandolo essenzialmente su 1. L'ultimo argomento è facoltativo; se l'argomento viene omesso, viene interpretato come TRUE.

Per semplificare l'esposizione nella parte restante di questo articolo, si supponga che i dati siano disposti in colonne in modo che known_y sia una colonna di dati y e known_x sia una o più colonne di dati x. Le dimensioni (lunghezze) di ognuna di queste colonne devono essere uguali. si presuppone che anche New_x sia disposto in colonne e che vi sia lo stesso numero di colonne per new_x di known_x. Tutte le osservazioni riportate di seguito sono ugualmente vere se i dati non sono disposti in colonne, ma è più facile discutere il singolo caso (usato più di frequente).

Dopo aver calcolato il modello di regressione più adatto (chiamando essenzialmente la funzione LOGEST di Excel), GROWTH restituisce i valori stimati associati a new_x.

Questo articolo usa esempi per illustrare in che modo GROWTH è correlato a LOGEST e per segnalare problemi con LOGEST nelle versioni di Excel precedenti a Excel 2003 che si traducono in problemi con GROWTH. GROWTH chiama in modo efficace LOGEST, esegue LOGEST, usa i coefficienti di regressione nell'output LOGEST nel calcolo dei valori y stimati associati a ogni riga di new_x e presenta questa colonna di valori y stimati. Pertanto, è necessario conoscere i problemi nell'esecuzione di LOGEST. Quando viene chiamato LOGEST, a sua volta chiama in modo efficace LINEST. Anche se il codice per GROWTH e LOGEST non è stato riscritto per Excel 2003 e per le versioni successive di Excel, sono state apportate modifiche estese (e miglioramenti) nel codice LINEST.

Come complemento a questo articolo, è altamente consigliato l'articolo seguente su LINEST. Contiene diversi esempi e documenti relativi ai problemi relativi a LINEST nelle versioni di Excel precedenti a Excel 2003.

Per altre informazioni su LINEST, selezionare il numero di articolo seguente per visualizzare l'articolo nella Microsoft Knowledge Base:

828533 Descrizione della funzione LINEST in Excel 2003 e in Excel 2004 per Mac

È consigliabile anche il file della Guida LINEST, come modificato per Excel 2003.

L'articolo seguente su LOGEST illustra in che modo LOGEST interagisce con LINEST. Questi dettagli vengono omessi qui.

Per altre informazioni, vedere Funzioni statistiche di Excel: LOGEST.

Poiché l'attenzione di questo articolo riguarda i problemi numerici nelle versioni di Excel precedenti a Excel 2003, questo articolo non include molti esempi pratici dell'uso di GROWTH. Il file della Guida in GROWTH contiene esempi utili.

Sintassi

GROWTH(known_y's, known_x's, new_x's, constant)

Gli argomenti, i known_y, i known_x e i new_x devono essere matrici o intervalli di celle con dimensioni correlate. Se known_y è una colonna per m righe, allora known_x è composto da c colonne per m righe, dove c è maggiore o uguale a uno. C è il numero di variabili del predictor; m è il numero di punti dati. New_x deve quindi essere di c colonne per r righe, dove r è maggiore o uguale a uno. Le relazioni simili nelle dimensioni devono essere rispettate anche se i dati sono disposti in righe anziché in colonne. La costante è un argomento logico che deve essere impostato su TRUE o FALSE (o 0 oppure 1 che Excel interpreta rispettivamente come FALSE o TRUE). Gli ultimi tre argomenti per GROWTH sono tutti facoltativi; vedere il file della Guida GROWTH per le opzioni di omettere il secondo argomento, terzo argomento o entrambi; omettendo il quarto argomento viene interpretato come TRUE.

L'utilizzo più comune di GROWTH include due intervalli di celle che contengono i dati, ad esempio GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Poiché in genere è presente più di una variabile predictor, il secondo argomento di questo esempio contiene più colonne. In questo esempio sono presenti 100 soggetti, un valore variabile dipendente (known_y) per ogni oggetto e cinque valori di variabile dipendenti (known_x) per ogni oggetto. Esistono otto soggetti più ipotetici in cui si vuole usare GROWTH per calcolare i valori y stimati.

Esempio di utilizzo

Viene fornito un esempio di foglio di lavoro di Excel per illustrare i concetti chiave seguenti:

• In che modo GROWTH interagisce con LOGEST
• Problemi che si verificano con GROWTH (o LOGEST e LINEST) a causa di known_x collineari nelle versioni di Excel precedenti a Excel 2003

Annotazioni

Una discussione approfondita del secondo punto elenco nel contesto di LINEST è fornita nell'articolo riguardante LINEST.

Per illustrare la funzione GROWTH, 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:K35 nel foglio di lavoro.

Un	B	C	D	E	F	G	H	Io	J	Okay
y:	X:
=EXP(F2)	1	2	1		1
=EXP(F3)	3	4	1		2
=EXP(F4)	4	5	1		3
=EXP(F5)	6	7	1		4
=EXP(F6)	7	8	1		5
nuovi x di:	9	11
	12	14
CRESCITA usando le colonne B, C:				Valori per Excel 2002 e per le versioni precedenti di Excel:
			Valori per Excel 2003 e per le versioni successive di Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!				472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!				3400.16400895377
CRESCITA usando solo la colonna B
=CRESCITA(A2:A6;B2:B6;B7:B8;VERO)				472.432.432.563.203				472,432432563203
=CRESCITA(A2:A6;B2:B6;B7:B8;VERO)				3400.16400895377				3400.16400895377
Valori adattati dai risultati LOGEST in Excel 2003 e nelle versioni successive di Excel
Uso delle colonne B, C		Utilizzo di Col B
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7)		=EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8)		=EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST con le colonne B,C:				Valori per Excel 2002 e per le versioni precedenti di Excel:				Valori per Excel 2003 e per le versioni successive di Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		1	1,9307233720034	1,26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0	0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0.986842105263158	0.209426954145848	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		225	3	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		9.86842105263158	0.131578947368421	#N/A
LOGEST utilizzando solo colonna B
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			1,9307233720034	1,26724101129183			1,9307233720034	1,26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.0438596491228071	0.206652964726136			0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.986842105263158	0.209426954145848			0.986842105263158	0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			224.999999999999	3			225	3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			9.86842105263158	0.131578947368421			9.86842105263158	0.131578947368421

Annotazioni

Dopo aver incollato questa tabella nel nuovo foglio di lavoro di Excel, seleziona il pulsante Opzioni Incolla e poi seleziona Formato di destinazione. Con l'intervallo incollato ancora selezionato, usare una delle procedure seguenti, a seconda della versione di Excel in uso.

• In Microsoft Office Excel 2007 selezionare la scheda Home , selezionare Formato nel gruppo Celle e quindi selezionare Adatta larghezza colonna.
• In Excel 2003 scegliere Colonna dal menu Formato e quindi selezionare Selezione automatica.

I dati per GROWTH si trovano nelle celle A1:C8. Le voci nelle celle D2:D6 non fanno parte dei dati, ma vengono usate per l'illustrazione seguente. I risultati di GROWTH per due modelli diversi per entrambe le versioni precedenti di Excel e per le versioni successive di Excel vengono presentati rispettivamente nelle celle E10:E16 e I10:116. I risultati nelle celle A10:A16 corrispondono alla versione di Excel usata. Per il momento, concentrarsi sui risultati per Excel 2003 e per le versioni successive di Excel quando si esamina come GROWTH chiama LOGEST e come GROWTH usa i risultati LOGEST.

GROWTH e LOGEST possono essere visualizzati come interagiscono nei passaggi seguenti:

1. Utilizzare la funzione GROWTH(known_y's, known_x's, new_x's, costante)
2. GROWTH chiama LOGEST(known_y, known_x, costante, TRUE)
3. I coefficienti di regressione da questa chiamata a LOGEST vengono ottenuti. Questi coefficienti vengono visualizzati nella prima riga della tabella di output di LOGEST.
4. Per ogni riga di new_x, il valore y stimato viene calcolato in base a questi coefficienti LOGEST e ai valori dell'new_x in tale riga.
5. Il valore calcolato nel passaggio 4 viene restituito nella cella appropriata per l'output GROWTH che corrisponde alla riga di tale new_x.

Se GROWTH restituisce risultati appropriati, LOGEST deve generare risultati appropriati nel passaggio 3. Poiché la valutazione di LOGEST nel passaggio 3 richiede una chiamata a LINEST, è essenziale che LINEST sia ben comportato. I problemi relativi a LINEST nelle versioni di Excel precedenti a Excel 2003 provengono dalle colonne del predittore collineari. Esistono altri problemi con LINEST e LOGEST nelle versioni precedenti di Excel che si verificano quando l'ultimo argomento di GROWTH è impostato su FALSE. Tuttavia, questi problemi non influiscono sui risultati della CRESCITA e non vengono discussi qui.

Le colonne predittive (known_x) sono collineari se almeno una colonna, c, può essere espressa come somma di multipli di altre colonne, c1, c2 e ulteriori colonne. La colonna c viene spesso chiamata ridondante perché le informazioni contenute possono essere costruite dalle colonne c1, c2 e altre colonne. Il principio fondamentale dell'esistenza della collinearità è che i risultati non devono essere influenzati dal fatto che una colonna ridondante sia inclusa nei dati originali o rimossa dai dati originali. Poiché LINEST nelle versioni di Excel precedenti a Excel 2003 non ha cercato collinearità, questo principio è stato facilmente violato. Le colonne predittori sono quasi collineari se almeno una colonna, c, può essere espressa come quasi uguale alla somma di multipli di altre, c1, c2 e altre colonne. In questo caso "quasi uguale" indica una piccola somma delle differenze quadrate tra gli elementi di c e quelli corrispondenti nella somma ponderata di c1, c2 e altre colonne. "Molto piccolo" potrebbe essere minore di 10^(-12), ad esempio.

Il primo modello, nelle righe da 10 a 12, usa le colonne B e C come predittori e richiede a Excel di modellare la costante (ultimo argomento impostato su TRUE). Excel inserisce quindi in modo efficace una colonna predictor aggiuntiva simile alle celle D2:D6. È facile notare che le voci nella colonna C nelle righe da 2 a 6 sono esattamente uguali alla somma delle voci corrispondenti nelle colonne B e D. Di conseguenza, la collinearità è presente perché la colonna C è una somma di multipli degli elementi seguenti:

• Colonna B
• Colonna aggiuntiva di Excel di 1s inserita perché il terzo argomento di LOGEST è stato omesso o TRUE (caso "normale")

Ciò causa problemi numerici che le versioni di Excel precedenti a Excel 2003 non possono calcolare i risultati. Pertanto, la tabella di output GROWTH viene compilata con #NUM!.

Il secondo modello, nelle righe da 14 a 16, è uno che qualsiasi versione di Excel può gestire correttamente. Non esiste alcuna collinearità e l'utente richiede di nuovo a Excel di modellare la costante. Questo modello è incluso qui per i motivi seguenti:

• In primo luogo, è più comune nei casi pratici che non vi sia alcuna collinearità. Questi casi vengono gestiti sufficientemente in tutte le versioni di Excel. Dovrebbe essere rassicurante sapere che i problemi numerici non si verificano nel caso pratico più comune se si dispone di una versione precedente di Excel.
• In secondo luogo, questo esempio viene usato per confrontare il comportamento di Excel 2003 e delle versioni successive di Excel nei due modelli. La maggior parte dei principali pacchetti statistici analizza la collinearità, rimuove una colonna che è una somma di più di altri dal modello e avvisa l'utente con un messaggio come "la colonna C dipende in modo lineare da altre colonne del predictor ed è stata rimossa dall'analisi".

In Excel 2003 e nelle versioni successive di Excel, tale messaggio viene trasmesso non in un avviso o in una stringa di testo, ma nella tabella di output LOGEST. GROWTH non ha alcun meccanismo per recapitare tale messaggio all'utente. Nella tabella di output LOGEST, un coefficiente di regressione che è uno e il cui errore standard è zero, corrisponde a un coefficiente per una colonna che è stata rimossa dal modello. Le tabelle di output LOGEST sono incluse nelle righe da 23 a 35 corrispondenti all'output GROWTH nelle righe da 10 a 16. Le voci nelle celle I24:I25 mostrano che una colonna predittore ridondante è stata eliminata. In questo caso, LOGEST ha scelto di rimuovere la colonna C (coefficienti nelle celle I24, J24, K24 corrispondono rispettivamente alle colonne C, B e alla colonna costante di Excel). Quando è presente collinearità, è possibile rimuovere una delle colonne coinvolte e la scelta è arbitraria.

Nel secondo modello nelle righe da 30 a 35 non c'è collinearità e nessuna colonna rimossa. È possibile notare che i valori y stimati sono uguali in entrambi i modelli. Questo problema si verifica perché la rimozione di una colonna ridondante che corrisponde a una somma di più di altri non riduce la correttezza dell'adattamento del modello risultante. Tali colonne vengono rimosse proprio perché non rappresentano alcun valore aggiunto nel tentativo di trovare il miglior adattamento ai minimi quadrati. Inoltre, se si esamina l'output LOGEST nelle celle I23:K35 in Excel 2003 e nelle versioni successive di Excel, si noterà che le ultime tre righe delle tabelle di output sono le stesse. Inoltre, le voci nelle celle I31:J32 e J24:K25 coincidono. Dimostra che gli stessi risultati vengono ottenuti quando la colonna C è inclusa nel modello, ma risulta ridondante (output nelle celle I24:K28) come quando la colonna C è stata eliminata prima dell'esecuzione di LOGEST (output nelle celle I31:J35). Questo soddisfa il principio fondamentale dell'esistenza della collinearità.

Nelle celle A18:C21, Microsoft usa i dati di Excel 2003 e delle versioni successive di Excel per illustrare come GROWTH accetta l'output LOGEST e calcola i valori y stimati pertinenti. Esaminando le formule nelle celle A20:A21 e celle C20:C21, è possibile vedere in che modo i coefficienti LOGEST vengono combinati con i dati di new_x nelle celle B7:C8 per ognuno dei due modelli (usando le colonne B, C come predittori; usando solo la colonna B come predictor).

Collinearity è identificato in LOGEST in Excel 2003 e nelle versioni successive di Excel perché LOGEST chiama LINEST. LINEST usa un approccio diverso per risolvere i coefficienti di regressione. Questo approccio è la scomposizione QR. L'articolo LINEST contiene una spiegazione dettagliata dell'algoritmo di scomposizione QR per un piccolo esempio.

Riepilogo dei risultati nelle versioni precedenti di Excel

I risultati di GROWTH sono influenzati negativamente nelle versioni di Excel precedenti a Excel 2003 a causa di risultati imprecisi in LOGEST che, a loro volta, derivano da risultati imprecisi in LINEST.

LINEST è stato calcolato usando un approccio che non prestava attenzione ai problemi di collinearità. L'esistenza della collinearità ha causato errori di arrotondamento, errori standard inappropriati dei coefficienti di regressione e gradi di libertà inappropriati. A volte i problemi di arrotondamento sono così gravi che LINEST ha riempito la tabella di output con #NUM!. Se, come nella maggior parte dei casi in pratica, è possibile essere certi che non ci fossero colonne dei predittori collineari (o quasi collineari), LINEST in genere fornirà risultati accettabili. Pertanto, gli utenti di GROWTH possono essere rassicurati in modo analogo se possono vedere l'assenza di colonne predittive collineari (o quasi collineari).

Riepilogo dei risultati in Excel 2003 e nelle versioni successive di Excel

I miglioramenti apportati a LINEST includono il passaggio al metodo di scomposizione a matrice per determinare i coefficienti di regressione. La scomposizione QR presenta i vantaggi seguenti:

• Migliore stabilità numerica (in genere, errori di arrotondamento più piccoli)
• Analisi dei problemi di collinearità

Tutti i problemi relativi alle versioni di Excel precedenti a Excel 2003 illustrate in questo articolo sono stati corretti per Excel 2003 e per le versioni successive di Excel. Questi miglioramenti in LINEST si traducono in miglioramenti in LOGEST e GROWTH.

Conclusioni

Le prestazioni di GROWTH sono state migliorate perché LINEST è stato notevolmente migliorato per Excel 2003 e per le versioni successive di Excel. I miglioramenti apportati a LINEST influiscono anche su LOGEST, perché LOGEST viene chiamato da GROWTH. Gli utenti di versioni precedenti di Excel devono verificare che le colonne predictor non siano collineari prima di usare GROWTH.

Gran parte del materiale presentato in questo articolo e nell'articolo LINEST potrebbe inizialmente sembrare allarmare gli utenti di versioni di Excel precedenti a Excel 2003. Tuttavia, va notato che la collinearità è un problema solo in una piccola percentuale di casi. Le versioni precedenti di Excel offrono risultati GROWTH accettabili quando non c'è collinearità.

Fortunatamente, i miglioramenti apportati a LINEST influiscono anche sullo strumento di regressione lineare dello Strumento di analisi (questo strumento chiama LINEST) e altre due funzioni di Excel correlate: LOGEST e TREND.