Cos'è la linea di tendenza in Excel. Calcolo dei parametri dell'equazione di trend

Una linea di tendenza viene utilizzata per illustrare visivamente le tendenze dei prezzi. L'elemento dell'analisi tecnica è una rappresentazione geometrica dei valori medi dell'indicatore analizzato.

Vediamo come aggiungere una linea di tendenza a un grafico in Excel.

Aggiunta di una linea di tendenza a un grafico

Ad esempio, prendiamo i prezzi medi del petrolio dal 2000 da fonti aperte. Inseriamo i dati per l'analisi nella tabella:

Una linea di tendenza in Excel è un grafico di una funzione di approssimazione. Perché è necessario - fare previsioni basate su dati statistici. A tal fine, è necessario estendere la linea e determinarne i valori.

Se R2 = 1, l'errore di approssimazione è zero. Nel nostro esempio, la scelta di un'approssimazione lineare ha dato una bassa confidenza e uno scarso risultato. La previsione sarà imprecisa.

Attenzione!!! Non è possibile aggiungere una linea di tendenza ai seguenti tipi di grafici e grafici:

• petalo;
• circolare;
• superficie;
• anulare;
• volume;
• con accumulazione.

Equazione della linea di tendenza in Excel

Nell'esempio precedente, è stata scelta un'approssimazione lineare solo per illustrare l'algoritmo. Come dimostra il valore dell'affidabilità, la scelta non è stata del tutto vincente.

È necessario scegliere il tipo di visualizzazione che illustra in modo più accurato l'andamento dell'input dell'utente. Diamo un'occhiata alle opzioni.

Approssimazione lineare

La sua rappresentazione geometrica è una linea retta. Pertanto, un'approssimazione lineare viene utilizzata per illustrare un indicatore che aumenta o diminuisce a una velocità costante.

Considera il numero condizionale di contratti conclusi dal gestore per 10 mesi:

Sulla base dei dati nella tabella di Excel, costruiremo un grafico a dispersione (aiuterà a illustrare il tipo lineare):

Seleziona il grafico - "aggiungi una linea di tendenza". Nei parametri selezionare il tipo lineare. Aggiungiamo il valore dell'affidabilità dell'approssimazione e l'equazione della linea di tendenza in Excel (basta selezionare le caselle in fondo alla finestra "Parametri").

Otteniamo il risultato:

Nota! Con il tipo di approssimazione lineare, i punti dati si trovano il più vicino possibile a una linea retta. Questa vista utilizza la seguente equazione:

y = 4,503x + 6,1333

• dove 4.503 è l'indicatore di pendenza;
• 6.1333 - compensazioni;
• y è una sequenza di valori,
• x è il numero del periodo.

La retta del grafico mostra un costante aumento della qualità del lavoro del manager. Il valore di affidabilità dell'approssimazione è 0,9929, che indica un buon accordo tra la retta calcolata ei dati originali. Le previsioni devono essere accurate.

Per prevedere il numero di contratti conclusi, ad esempio, nell'undicesimo periodo, è necessario sostituire nell'equazione il numero 11 anziché x. Nel corso dei calcoli, apprendiamo che nell'11° periodo questo manager concluderà 55-56 contratti.

Linea di tendenza esponenziale

Questo tipo sarà utile se i valori di input cambiano a una velocità in continuo aumento. L'approssimazione esponenziale non viene applicata in presenza di caratteristiche zero o negative.

Costruiamo una linea di tendenza esponenziale in Excel. Prendiamo ad esempio i valori condizionali della fornitura utile di energia elettrica nella regione X:

Costruiamo un grafico. Aggiungi una linea esponenziale.

L'equazione ha la seguente forma:

y = 7,6403^-0,084x

• dove 7,6403 e -0,084 sono costanti;
• e è la base del logaritmo naturale.

L'indice di affidabilità dell'approssimazione era 0,938: la curva corrisponde ai dati, l'errore è minimo, le previsioni saranno accurate.

Registra la linea di tendenza in Excel

Viene utilizzato per i seguenti cambiamenti nell'indicatore: in primo luogo, un rapido aumento o diminuzione, quindi relativa stabilità. La curva ottimizzata si adatta bene a questo "comportamento" della quantità. L'andamento logaritmico è adatto per prevedere le vendite di un nuovo prodotto appena introdotto sul mercato.

Nella fase iniziale, il compito del produttore è aumentare la base di clienti. Quando il prodotto ha un proprio acquirente, deve essere trattenuto, servito.

Costruiamo un grafico e aggiungiamo una linea di tendenza logaritmica per prevedere le vendite di un prodotto condizionale:

R2 ha un valore prossimo a 1 (0,9633), che indica l'errore di approssimazione minimo. Prevediamo i volumi di vendita nei periodi successivi. Per fare ciò, devi sostituire il numero del periodo nell'equazione invece di x.

Ad esempio:

Per calcolare i dati di previsione è stata utilizzata la seguente formula: =272,14*LN(B18)+287,21. Dove B18 è il numero del periodo.

Linea di tendenza polinomiale in Excel

Questa curva ha variabili ascendenti e discendenti. Per i polinomi (polinomi), il grado è determinato (dal numero di valori massimo e minimo). Ad esempio, un estremo (minimo e massimo) è il secondo grado, due estremi sono il terzo grado, tre sono il quarto.

Una tendenza polinomiale in Excel viene utilizzata per analizzare un ampio set di dati su un valore instabile. Diamo un'occhiata all'esempio del primo insieme di valori (prezzi del petrolio).

Per ottenere un tale valore di affidabilità di approssimazione (0,9256), ho dovuto mettere il 6° grado.

Ma una tale tendenza consente di fare previsioni più o meno accurate.

Trend immediatamente dopo aver inserito i dati disponibili nell'array. Per fare ciò, sul foglio con la tabella dati, seleziona almeno due celle dell'intervallo per il quale verrà costruito il grafico e subito dopo inserisci il grafico. È possibile utilizzare tali tipi di grafici come grafico, dispersione, istogramma, bolla, stock. Altri tipi di grafici non supportano la funzione di trend.

Dal menu Grafico, seleziona Aggiungi linea di tendenza. Nella finestra che si apre, nella scheda "Tipo", selezionare il tipo di trend line desiderato, che in termini matematici significa anche metodo di approssimazione dei dati. Quando usi il metodo descritto, dovrai farlo "a occhio", perché. non hai eseguito alcun calcolo matematico per tracciare il grafico.

Quindi pensa solo a quale tipo di funzione si adatta meglio al grafico dei dati disponibili: lineare, logaritmica, esponenziale o altro. In caso di dubbi sulla scelta del tipo di approssimazione, è possibile costruire più righe, e per una maggiore precisione di previsione, nella scheda "Parametri" della stessa finestra, spuntare la casella "metti il ​​valore dell'affidabilità dell'approssimazione (R ^ 2) sul diagramma".

Confrontando i valori di R^2 per linee diverse, puoi scegliere il tipo di grafico che caratterizza i tuoi dati in modo più accurato e, quindi, costruisce la previsione più affidabile. Più il valore di R^2 è vicino a uno, più accuratamente è stato selezionato il tipo di linea. Qui, nella scheda "Parametri", è necessario specificare il periodo per il quale viene effettuata la previsione.

Questo modo di costruire un trend è molto approssimativo, quindi è meglio fare almeno l'elaborazione statistica più primitiva dei dati disponibili. Ciò ti consentirà di costruire una previsione in modo più accurato.

Se si presume che i dati disponibili siano descritti da un'equazione lineare, è sufficiente selezionarli con il cursore e compilare automaticamente per il numero di periodi richiesto o il numero di celle. In questo caso, non è necessario trovare il valore di R^2, perché hai preadattato la previsione all'equazione della retta.

Se ritieni che i valori noti di una variabile possano essere descritti al meglio utilizzando un'equazione esponenziale, seleziona anche l'intervallo iniziale e riempi automaticamente il numero di celle richiesto tenendo premuto il tasto destro del mouse. Con il completamento automatico, non sarai in grado di disegnare altri tipi di linee, ad eccezione delle due indicate.

Pertanto, per la massima precisione nella creazione di una previsione, dovrai utilizzare una delle numerose funzioni statistiche: PREVISIONE, TREND, CRESCITA, REGR.LIN o LGRFPRIBL. In questo caso, dovrai calcolare manualmente il valore per ogni periodo di previsione successivo. Se è necessario eseguire analisi di regressione dei dati più complesse, sarà necessario il componente aggiuntivo "Analysis Pack", che non è incluso nell'installazione standard di MS Office.

Tracciare

Analisi di regressione

equazione di regressione Y a partire dal X chiamata dipendenza funzionale y=f(x), e la sua trama è una retta di regressione.

Excel ti consente di creare grafici e grafici di qualità abbastanza accettabile. Excel dispone di uno strumento speciale: la Creazione guidata grafico, sotto la cui guida l'utente attraversa tutte e quattro le fasi del processo di costruzione di un grafico o di un grafico.

Di norma, la costruzione di un grafico inizia con la selezione di un intervallo contenente i dati su cui deve essere costruito. Un tale inizio semplifica l'ulteriore corso della trama. Tuttavia, l'intervallo con i dati iniziali può essere suddiviso anche nella seconda fase del dialogo con MAESTRO DI DIAGRAMMA. In Excel 2003 MAESTRO DI DIAGRAMMAè nel menu sotto forma di un pulsante o è possibile creare un diagramma facendo clic sulla scheda INSERIRE e nell'elenco che si apre, trova l'elemento DIAGRAMMA. In Excel 2007 troviamo anche la scheda INSERIRE(Fig. 31).

Riso. 31. MAESTRO DI DIAGRAMMA in Excel 2007

Il modo più semplice per selezionare l'intervallo di dati di origine in cui questi dati si trovano in righe adiacenti (colonne o righe) consiste nel fare clic sulla cella in alto a sinistra dell'intervallo e quindi trascinare il puntatore del mouse nella cella in basso a destra dell'intervallo. Quando si selezionano dati in righe non adiacenti, trascinare il puntatore del mouse sulle righe selezionate tenendo premuto il tasto Ctrl. Se una delle serie di dati ha una cella con un titolo, anche l'altra serie selezionata deve avere una cella corrispondente, anche se vuota.

Per l'analisi di regressione, è meglio utilizzare un grafico a dispersione (Fig. 30). Quando lo traccia, Excel tratta la prima riga dell'intervallo di dati di origine selezionato come un insieme di valori per l'argomento delle funzioni i cui grafici devono essere tracciati (lo stesso insieme per tutte le funzioni). Le righe seguenti sono percepite come insiemi di valori delle funzioni stesse (ogni riga contiene i valori di una delle funzioni corrispondenti ai valori degli argomenti dati che si trovano nella prima riga dell'intervallo selezionato).

In Excel 2007, i titoli degli assi sono inseriti nella scheda del menu DISPOSIZIONE(Fig. 32).

Riso. 32. Impostazione dei nomi degli assi del grafico in Excel 2007

Per ottenere un modello matematico, è necessario tracciare una linea di tendenza sul grafico. In Excel 2003 e 2007, fare clic con il pulsante destro del mouse sui punti del grafico. Quindi in Excel 2003 apparirà una scheda con un elenco di elementi da cui selezioniamo AGGIUNGI LINEA DI TENDENZA(Fig. 33).

Riso. 33. AGGIUNGI LINEA DI TENDENZA

Dopo aver cliccato sull'elemento AGGIUNGI LINEA DI TENDENZA apparirà una finestra LINEA DI TENDENZA(Fig. 34). Nella scheda TIPO, è possibile selezionare i seguenti tipi di linee: lineare, logaritmica, esponenziale, esponenziale, polinomiale, filtro lineare.

Riso. 34. Finestra LINEA DI TENDENZA in Excel 2003

Nella scheda PARAMETRI(Fig. 35) spuntare la casella accanto alle voci MOSTRA EQUAZIONE SUL DIAGRAMMA, quindi sul grafico apparirà un modello matematico di questa dipendenza. Mettiamo anche un segno di spunta davanti all'oggetto MOSTRARE SUL DIAGRAMMA IL VALORE DELL'AFFIDABILITA' DI APPROSSIMAZIONE (R^2). Più il valore di confidenza dell'approssimazione è vicino a 1, più la curva selezionata si avvicina ai punti sul grafico. Quindi, fare clic sul pulsante ok. Sul grafico apparirà una linea di tendenza, l'equazione corrispondente e il valore dell'affidabilità dell'approssimazione.

Riso. 35. Tab PARAMETRI

In Excel 2007, dopo aver fatto clic con il pulsante destro del mouse sui punti del grafico, verrà visualizzato un elenco di voci di menu, da cui SCEGLI AGGIUNGI LINEA DI TENDENZA(Fig. 36).

Riso. 36. AGGIUNGI LINEA DI TENDENZA

Riso. 37. Tab PARAMETRI DELLA LINEA DI TREND

Impostare le caselle di controllo richieste e fare clic sul pulsante CHIUDERE.

Sul grafico apparirà una linea di tendenza, l'equazione corrispondente e il valore dell'affidabilità dell'approssimazione.

Le previsioni sono un elemento molto importante in quasi tutti i campi di attività, dall'economia all'ingegneria. C'è un gran numero di software specializzato in questo settore. Sfortunatamente, non tutti gli utenti sanno che un normale foglio di calcolo Excel ha nel suo arsenale strumenti per eseguire previsioni, che non sono molto inferiori ai programmi professionali in termini di efficienza. Scopriamo insieme quali sono questi strumenti e come fare una previsione in pratica.

Lo scopo di qualsiasi previsione è identificare la tendenza attuale e determinare il risultato atteso in relazione all'oggetto in studio in un determinato momento nel futuro.

Metodo 1: linea di tendenza

Uno dei tipi più popolari di previsione grafica in Excel è l'estrapolazione eseguita costruendo una linea di tendenza.

Proviamo a prevedere l'importo del profitto dell'impresa in 3 anni sulla base dei dati su questo indicatore per i 12 anni precedenti.

Metodo 2: operatore PREVISIONE

L'estrapolazione dei dati tabulari può essere effettuata tramite la funzione standard di Excel PREVISIONE. Questo argomento appartiene alla categoria degli strumenti statistici e ha la seguente sintassi:

PREVISIONE(X, valori y noti, valori x noti)

"X"è l'argomento di cui deve essere determinato il valore della funzione. Nel nostro caso, l'argomento sarà l'anno per il quale dovrebbe essere fatta la previsione.

"Valori noti"è la base dei valori noti della funzione. Nel nostro caso, il suo ruolo è l'importo del profitto per i periodi precedenti.

"Valori x noti" sono gli argomenti che corrispondono ai valori noti della funzione. Nel loro ruolo abbiamo la numerazione degli anni per i quali sono state raccolte informazioni sui profitti degli anni precedenti.

Naturalmente, il periodo di tempo non deve fungere da argomento. Ad esempio, può essere la temperatura e il valore della funzione può essere il livello di espansione dell'acqua quando riscaldata.

Quando si calcola in questo modo, viene utilizzato il metodo della regressione lineare.

Diamo un'occhiata alle sfumature dell'utilizzo dell'operatore PREVISIONE su un esempio specifico. Prendiamo lo stesso tavolo. Avremo bisogno di conoscere le previsioni di profitto per il 2018.

Ma non dimenticare che, come nel caso della costruzione di una linea di tendenza, l'intervallo di tempo prima del periodo di previsione non deve superare il 30% dell'intero periodo per il quale è stato accumulato il database.

Metodo 3: operatore TENDENZA

Per la previsione, puoi usare un'altra funzione - TENDENZA. Appartiene anche alla categoria degli operatori statistici. La sua sintassi è molto simile a quella di uno strumento PREVISIONE e si presenta così:

TREND(valori y noti, valori x x noti, valori x nuovi, [const])

Come puoi vedere, gli argomenti "Valori noti" e "Valori x noti" corrispondono pienamente a elementi simili dell'operatore PREVISIONE, e l'argomento "Nuovi valori x" corrisponde all'argomento "X" strumento precedente. Inoltre, a TENDENZA c'è un ulteriore argomento "Costante", ma non è obbligatorio e viene utilizzato solo in presenza di fattori costanti.

Questo operatore viene utilizzato in modo più efficace in presenza di una dipendenza lineare della funzione.

Vediamo come funzionerà questo strumento con lo stesso array di dati. Per confrontare i risultati, definiamo l'anno 2019 come punto di previsione.

Metodo 4: operatore CRESCITA

Un'altra funzione con cui è possibile fare previsioni in Excel è l'operatore CRESCITA. Appartiene anche al gruppo statistico degli strumenti, ma, a differenza dei precedenti, per il calcolo non utilizza il metodo della dipendenza lineare, ma quello esponenziale. La sintassi per questo strumento è simile alla seguente:

CRESCITA(Valori-y_noti, valori-x_noti, valori-x_nuovi, [const])

Come puoi vedere, gli argomenti di questa funzione ripetono esattamente gli argomenti dell'operatore TENDENZA, quindi non ci soffermeremo sulla loro descrizione per la seconda volta, ma si procederà subito all'applicazione pratica di questo strumento.

Metodo 5: operatore REGR.LIN

Operatore LINEST nel calcolo utilizza il metodo dell'approssimazione lineare. Non deve essere confuso con il metodo di relazione lineare utilizzato dallo strumento. TENDENZA. La sua sintassi è simile a questa:

REGR.LIN(y_conosciute, x_note, x_nuove, [const], [statistiche])

Gli ultimi due argomenti sono facoltativi. Abbiamo familiarità con i primi due dei metodi precedenti. Ma potresti aver notato che a questa funzione manca un argomento che punta ai nuovi valori. Il fatto è che questo strumento determina solo la variazione dell'importo del ricavo per unità di periodo, che nel nostro caso è pari a un anno, ma dobbiamo calcolare il totale separatamente, sommando all'ultimo valore effettivo di profitto il risultato del calcolo dell'operatore LINEST moltiplicato per il numero di anni.

Come puoi vedere, il valore del profitto previsto, calcolato con il metodo dell'approssimazione lineare, nel 2019 sarà di 4614,9 mila rubli.

Metodo 6: operatore LFPRIB

L'ultimo strumento che esamineremo sarà LGRFPRIBL. Questo operatore esegue calcoli basati sul metodo di approssimazione esponenziale. La sua sintassi ha la seguente struttura:

LGFPRIB(valori y noti, valori x noti, valori x nuovi, [const], [statistiche])

Come puoi vedere, tutti gli argomenti ripetono completamente gli elementi corrispondenti della funzione precedente. L'algoritmo per il calcolo della previsione cambierà leggermente. La funzione calcolerà una tendenza esponenziale, che mostrerà quante volte l'importo delle entrate cambia in un periodo, cioè in un anno. Dovremo trovare la differenza di profitto tra l'ultimo periodo effettivo e il primo periodo pianificato, moltiplicarla per il numero di periodi pianificati (3) e aggiungi al risultato la somma dell'ultimo periodo effettivo.

L'importo previsto dell'utile nel 2019, calcolato utilizzando il metodo dell'approssimazione esponenziale, sarà di 4639,2 mila rubli, che ancora una volta non differisce molto dai risultati ottenuti calcolando i metodi precedenti.

Abbiamo scoperto in che modo è possibile fare previsioni nel programma Excel. Graficamente, questo può essere fatto attraverso l'uso di una linea di tendenza e analiticamente, utilizzando una serie di funzioni statistiche integrate. A seguito dell'elaborazione di dati identici da parte di questi operatori, si può ottenere un risultato diverso. Ma questo non sorprende, dal momento che utilizzano tutti metodi di calcolo diversi. Se la fluttuazione è piccola, tutte queste opzioni applicabili a un caso particolare possono essere considerate relativamente affidabili.

Cosa fare se non ci sono misurazioni temporali per determinati volumi/dimensioni di prodotti? Oppure il numero di misurazioni è insufficiente e non è possibile effettuare ulteriori osservazioni nel prossimo futuro? Il modo migliore per risolvere questo problema è creare dipendenze calcolate (equazioni di regressione) utilizzando le linee di tendenza in MS Excel.

Si consideri una situazione reale: in un magazzino, al fine di determinare l'importo del costo del lavoro per il picking in scatola di un ordine, sono state effettuate delle osservazioni cronometriche. I risultati di queste osservazioni sono presentati nella seguente tabella 1.

Successivamente si è reso necessario determinare il tempo impiegato per il picking di 0,6 e 0,9 m3 di merce/ordine. A causa dell'impossibilità di condurre ulteriori studi cronometrici, il tempo impiegato per selezionare questi volumi d'ordine è stato calcolato utilizzando le equazioni di regressione in MS Excel. Per fare ciò, la tabella 1 è stata convertita nella tabella 2.

Selezione del grafico a dispersione, fig. uno

Il passo successivo: il cursore del mouse è stato posizionato su uno dei punti del grafico e con il tasto destro del mouse è stato richiamato il menu contestuale, in cui è stata selezionata la voce: "aggiungi linea di tendenza" (Fig. 2).

Aggiunta di una linea di tendenza, fig. 2

Nella finestra di impostazione del formato della linea di tendenza visualizzata (Fig. 3) sono stati selezionati in sequenza: tipo di linea lineare/legge di potenza e sono state impostate le caselle di controllo per le seguenti voci: “mostra l'equazione sul diagramma” e “metti sul diagramma il valore dell'affidabilità di approssimazione (R^2)” (coefficiente di determinazione).

Formato Trendline, fig. 3

Di conseguenza, i grafici presentati in fig. 4 e 5.

Dipendenza calcolata lineare, fig. 4

Dipendenza calcolata dalla potenza, fig. 5

Un'analisi visiva dei grafici indica chiaramente la vicinanza delle dipendenze ottenute. Inoltre, il valore dell'affidabilità di approssimazione (R^2), che è anche chiamato coefficiente di determinazione, nel caso di entrambe le dipendenze è lo stesso valore di 0,97. È noto che più il coefficiente di determinazione è vicino a 1, più la linea di tendenza corrisponde alla realtà. Si può inoltre affermare che la variazione del tempo impiegato per l'elaborazione dell'ordine è del 97% a causa di una variazione della quantità di merce. Pertanto, in questo caso, non importa: quale dipendenza calcolata scegliere come principale per il successivo calcolo dei costi di tempo.

Prendiamo come principale: una dipendenza calcolata lineare. Quindi i valori del tempo impiegato in base alla quantità di merce saranno determinati dalla formula: y = 54,511x + 0,1489. I risultati di questi calcoli per la quantità di merce, per la quale sono state precedentemente effettuate osservazioni cronometriche, sono presentati nella tabella 3 seguente.

Determiniamo la deviazione media dei costi temporali calcolati dall'equazione di regressione dai costi temporali calcolati dai dati delle osservazioni cronometriche: (-0,05+0,10-0,05+0,01)/4=0,0019. Pertanto, i costi temporali calcolati secondo l'equazione di regressione differiscono dai costi temporali calcolati in base ai dati delle osservazioni cronometriche solo di 0,19%. La discrepanza dei dati è trascurabile.

Secondo la formula: y = 54,511x + 0,1489, imposteremo i costi di tempo per la quantità di merce per la quale non sono state effettuate osservazioni cronometriche in precedenza (tabella 4).

Pertanto, la costruzione di dipendenze calcolate utilizzando le linee di tendenza in MS Excel - questo è un ottimo modo per determinare il tempo impiegato per operazioni che, per vari motivi, non sono state coperte dalle osservazioni cronometriche.