Funzioni statistiche di Excel: GROWTH

Riepilogo

Questo articolo descrive la funzione GROWTH in Microsoft Office Excel 2003 e nelle versioni successive di Excel, illustra come viene utilizzata la funzione e confronta i risultati della funzione per Excel 2003 e per le versioni successive di Excel con i risultati della crescita nelle versioni precedenti di Excel. GROWTH viene valutato chiamando la funzione correlata LINEST. Sono riepilogate le modifiche estese apportate a LINEST per Excel 2003 e per le versioni successive di Excel e vengono annotate le relative implicazioni per LA CRESCITA.

Informazioni su Microsoft Excel 2004 per Macintosh

Le funzioni statistiche in Excel 2004 per Mac sono state aggiornate usando 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 come funziona una funzione o come è stata modificata una funzione per Excel 2003 o per le versioni successive di Excel si applicano anche a Excel 2004 per Mac.

Ulteriori informazioni

La funzione GROWTH(known_y, known_x, costante di new_x) viene usata per eseguire un'analisi di regressione in cui viene adattata una curva esponenziale. Viene usato un criterio dei minimi quadrati e GROWTH cerca di trovare la soluzione migliore 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 casi rari in cui il secondo o il terzo argomento possono essere omessi.

Supponendo che siano presenti variabili del predittore p, GROWTH chiama essenzialmente LOGEST. LOGEST si adatta a un'equazione del formato:

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

I valori dei coefficienti b, m1, m2, ..., mp sono determinati in modo da fornire la migliore corrispondenza ai dati y.

Se l'ultimo argomento "costante" è impostato su TRUE, si desidera che il modello di regressione includa il coefficiente moltiplicativo b nel modello di regressione. Se impostato su FALSE, b viene escluso essenzialmente impostandolo 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. Naturalmente, le dimensioni (lunghezze) di ognuna di queste colonne devono essere uguali. New_x verrà anche considerato disposto in colonne e per new_x deve essere presente lo stesso numero di colonne di known_x. Tutte le osservazioni seguenti sono ugualmente vere se i dati non sono disposti in colonne, ma è più facile discutere di questo 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 la relazione di GROWTH con LOGEST e per segnalare i problemi relativi a LOGEST nelle versioni di Excel precedenti a Excel 2003 che si traducono in problemi di CRESCITA. GROWTH chiama in modo efficace LOGEST, esegue LOGEST, usa coefficienti di regressione nell'output LOGEST nel calcolo dei valori y stimati associati a ogni riga di new_x e presenta all'utente 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 supplementi di questo articolo, l'articolo seguente su LINEST è altamente consigliato. Contiene diversi esempi e documenti relativi ai problemi relativi a LINEST nelle versioni di Excel precedenti a Excel 2003.

Per altre informazioni su LINEST, fare clic sul numero dell'articolo della Microsoft Knowledge Base seguente:

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 come LOGEST interagisce con LINEST. Questi dettagli vengono omessi qui.

Per altre informazioni, fare clic sul numero dell'articolo seguente per visualizzare l'articolo della Microsoft Knowledge Base:

828528 funzioni statistiche di Excel: LOGEST

Poiché l'attenzione in questo articolo riguarda i problemi numerici nelle versioni di Excel precedenti a Excel 2003, questo articolo non contiene 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, known_y, known_x e new_x devono essere matrici o intervalli di celle con dimensioni correlate. Se known_y è una colonna per m righe, known_x è c colonne per m righe dove c è maggiore o uguale a uno. C è il numero di variabili del predittore; m è il numero di punti dati. New_x deve quindi essere colonne c per r righe dove sono maggiori o uguali a una. Le relazioni simili nelle dimensioni devono essere contenute se i dati sono disposti in righe anziché in colonne. Costante è un argomento logico che deve essere impostato su TRUE o FALSE (o su 0 o 1 che Excel interpreta rispettivamente come FALSE o TRUE). Gli ultimi tre argomenti di GROWTH sono tutti facoltativi; vedere il file della Guida GROWTH per le opzioni di omissione del secondo argomento, del terzo argomento o di entrambi; L'omissione del quarto argomento viene interpretata 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 sono presenti più variabili del predittore, il secondo argomento di questo esempio contiene più colonne. In questo esempio sono presenti 100 soggetti, un valore di variabile dipendente (known_y) per ogni soggetto e cinque valori variabili dipendenti (known_x) per ogni soggetto. Sono disponibili otto argomenti ipotetici aggiuntivi 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:

• Interazione di GROWTH 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

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.

I dati per LA CRESCITA 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 corrisponderanno alla versione di Excel in uso. 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 interazione nei passaggi seguenti:

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

Se GROWTH deve restituire 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 si comporti correttamente. I problemi relativi a LINEST nelle versioni di Excel precedenti a Excel 2003 derivano da colonne predittive collineari. Esistono altri problemi con LINEST e LOGEST nelle versioni precedenti di Excel che si verificano quando l'ultimo argomento su GROWTH è impostato su FALSE. Tuttavia, tali problemi non influiscono sui risultati della CRESCITA e non sono illustrati qui.

Le colonne predictor (known_x) sono collineari se almeno una colonna, c, può essere espressa come somma di multipli di altre colonne, c1, c2 e altre. La colonna c viene spesso definita ridondante perché le informazioni che contiene possono essere costruite dalle colonne c1, c2 e da 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 cercavano la collinearità, questo principio è stato facilmente violato. Le colonne del predittore sono quasi collineari se almeno una colonna, c, può essere espressa come quasi uguale a una somma di multipli di altre colonne, c1, c2 e altre. In questo caso "quasi uguale" significa una piccola somma di deviazioni al quadrato delle voci in c dalle voci 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 del predittore 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. Pertanto, esiste una 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 (il caso "normale")

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

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

• In primo luogo, è più tipico dei casi pratici: che non c'è collinearità presente. Questi casi vengono gestiti in modo sufficiente in tutte le versioni di Excel. Dovrebbe essere rassicurante sapere che non è probabile che si verifichino problemi numerici 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 rappresenta una somma di multipli di altri dal modello e avvisa l'utente con un messaggio come "la colonna C dipende in modo lineare da altre colonne del predittore 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 un messaggio di questo tipo all'utente. Nella tabella di output LOGEST un coefficiente di regressione pari a uno e il cui errore standard è zero corrisponde a un coefficiente per una colonna 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 una colonna del predittore ridondante eliminata. In questo caso, LOGEST ha scelto di rimuovere la colonna C (i coefficienti nelle celle I24, J24, K24 corrispondono rispettivamente alle colonne C, B e alla colonna costante di Excel). Quando è presente la collinearità, è possibile rimuovere una qualsiasi delle colonne coinvolte e la scelta è arbitraria.

Nel secondo modello nelle righe da 30 a 35 non è presente alcuna collinearità e nessuna colonna rimossa. È possibile notare che i valori y stimati sono gli stessi in entrambi i modelli. Questo problema si verifica perché la rimozione di una colonna ridondante che rappresenta una somma di multipli di altri non riduce la bontà di adattamento del modello risultante. Tali colonne vengono rimosse precisamente perché non rappresentano alcun valore aggiunto nel tentativo di trovare la migliore corrispondenza con i 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 nelle celle J24:K25 coincidono. Ciò dimostra che gli stessi risultati vengono ottenuti quando la colonna C è inclusa nel modello, ma risulta ridondante (output nelle celle I24:K28) rispetto a quando la colonna C è stata eliminata prima dell'esecuzione di LOGEST (output nelle celle I31:J35). Ciò 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 rilevanti. Esaminando le formule nelle celle A20:A21 e C20:C21, è possibile vedere come 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 predittore).

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

Riepilogo dei risultati nelle versioni precedenti di Excel

I risultati della crescita 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 non accurati in LINEST.

LINEST è stato calcolato usando un approccio che non ha prestato 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 sufficientemente gravi che LINEST ha riempito la tabella di output con #NUM!. Se, come nella maggior parte dei casi in pratica, si può essere certi che non ci siano colonne predittive collineari (o quasi collineari), linest fornirebbe in genere risultati accettabili. Pertanto, gli utenti di GROWTH possono essere rassicurati allo stesso modo 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 a matrice 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 illustrati 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 in LINEST influiscono anche su LOGEST, perché LOGEST viene chiamato da GROWTH. Gli utenti delle versioni precedenti di Excel devono verificare che le colonne del predittore non siano collineari prima di usare GROWTH.

Gran parte del materiale presentato in questo articolo e nell'articolo LINEST potrebbe inizialmente sembrare allarmi agli 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 DI CRESCITA accettabili quando non è presente alcuna collinearità.

Fortunatamente, i miglioramenti in LINEST influiscono anche sullo strumento di regressione lineare di Analysis ToolPak (questo strumento chiama LINEST) e altre due funzioni di Excel correlate: LOGEST e TREND.