A cosa serve una linea di tendenza in Excel? Strumenti di previsione in Microsoft Excel

plottaggio

Analisi di regressione

Equazione di regressione sì a partire dal X chiamata dipendenza funzionale y = f (x) e il suo grafico è una retta di regressione.

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

Di norma, il tracciamento inizia con l'evidenziazione dell'intervallo contenente i dati su cui dovrebbe essere tracciato. Un tale inizio semplifica l'ulteriore corso della trama. Tuttavia, l'intervallo con i dati originali può essere diviso nella seconda fase del dialogo con MAESTRO DI SCHEMI... In Excel 2003 MAESTRO DI SCHEMI situato nel menu come un pulsante o un diagramma può essere creato 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 SCHEMI in Excel 2007

Il modo più semplice per selezionare un intervallo di dati di origine in cui questi dati si trovano in righe adiacenti (colonne o righe) è fare clic sulla cella in alto a sinistra dell'intervallo e quindi trascinare il puntatore del mouse sulla cella in basso a destra dell'intervallo. Quando si selezionano dati situati in righe non contigue, il puntatore del mouse viene trascinato lungo le righe selezionate tenendo premuto il tasto Ctrl. Se una delle serie di dati ha una cella con un nome, anche il resto delle righe selezionate deve avere una cella corrispondente, anche se è vuota.

Il modo migliore per eseguire l'analisi di regressione è utilizzare un grafico a punti (Figura 30). Durante la sua creazione, Excel percepisce la prima riga dell'intervallo selezionato di dati di origine come un insieme di valori dell'argomento delle funzioni, i cui grafici devono essere tracciati (lo stesso insieme per tutte le funzioni). Le seguenti righe sono percepite come insiemi di valori delle funzioni stesse (ogni riga contiene i valori di una delle funzioni corrispondenti ai valori dati dell'argomento situato nella prima riga dell'intervallo selezionato).

In Excel 2007, i nomi degli assi sono posizionati nella scheda del menu DISPOSIZIONE(fig. 32).

Riso. 32. Impostazione dei titoli 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, è necessario 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'oggetto AGGIUNGI LINEA DI TENDENZA apparirà una finestra LINEA DI TENDENZA(fig. 34). Nella scheda TIPO è possibile selezionare i seguenti tipi di linea: lineare, logaritmica, esponenziale, potenza, polinomiale, filtraggio lineare.

Riso. 34. Finestra LINEA DI TENDENZA in Excel 2003

Nella scheda PARAMETRI(Fig. 35) metti il ​​checkbox di fronte alle voci MOSTRA L'EQUAZIONE SUL DIAGRAMMA, poi sul grafico comparirà il 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 del grafico. Quindi, fare clic sul pulsante ok... Sul grafico appariranno la 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, viene visualizzato un elenco di voci di menu, da cui SELEZIONE AGGIUNGI LINEA DI TENDENZA(fig. 36).

Riso. 36. AGGIUNGI LINEA DI TENDENZA

Riso. 37. Tab PARAMETRI DELLA LINEA DI TREND

Imposta le caselle di controllo necessarie e fai clic sul pulsante CHIUDERE.

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

Osservando qualsiasi dataset distribuito nel tempo (serie temporali), possiamo determinare visivamente i cali e i rialzi degli indicatori in esso contenuti. Il modello di alti e bassi è chiamato trend, che può indicare se i nostri dati stanno aumentando o diminuendo.

Forse inizierò una serie di articoli sulle previsioni con il più semplice: costruire una funzione di tendenza. Ad esempio, prendiamo i dati sulle vendite e costruiamo un modello che descriva la dipendenza delle vendite dal tempo.

Concetti basilari

Penso che fin dalla scuola tutti abbiano familiarità con la funzione lineare, sia proprio al centro del trend:

Y (t) = a0 + a1 * t + E

Y è il volume delle vendite, la variabile che spiegheremo nel tempo e da cui dipende, cioè Y (t);

t - numero del periodo (numero ordinale del mese), che spiega il piano di vendita Y;

a0 è il coefficiente di regressione zero, che mostra il valore di Y (t), in assenza dell'influenza del fattore esplicativo (t = 0);

a1 - coefficiente di regressione, che mostra quanto l'indicatore di vendita studiato Y dipende dal fattore di influenza t;

E - perturbazioni casuali che riflettono l'influenza di altri fattori non contabilizzati nel modello, ad eccezione del tempo t.

Costruire il modello

Quindi, conosciamo il volume delle vendite degli ultimi 9 mesi. Questo è il nostro piatto:

La prossima cosa che dobbiamo fare è determinare i coefficienti a0 e a1 per prevedere il volume delle vendite per il decimo mese.

Determinazione dei coefficienti del modello

Costruiamo un programma. Orizzontalmente vediamo i mesi differiti, verticalmente il volume delle vendite:

In Fogli Google, scegli Editor di diagrammi -> aggiuntivo e metti un segno di spunta accanto a Linee di tendenza... Nelle impostazioni, seleziona Etichetta — L'equazione e Mostra R^2.

Se fai tutto in MS Excel, fai clic con il pulsante destro del mouse sul grafico e seleziona "Aggiungi linea di tendenza" nel menu a discesa.

Per impostazione predefinita, viene creata una funzione lineare. A destra, seleziona "Mostra equazione sul diagramma" e "Il valore dell'affidabilità dell'approssimazione R ^ 2".

Ecco cosa è successo:

Sul grafico vediamo l'equazione della funzione:

y = 4856 * x + 105104

Descrive il volume delle vendite in base al numero del mese per il quale vogliamo prevedere queste vendite. Successivamente vediamo il coefficiente di determinazione R ^ 2, che parla della qualità del modello e di quanto bene descrive le nostre vendite (Y). Più vicino a 1, meglio è.

Ho R ^ 2 = 0,75. Questo è un indicatore medio, dice che il modello non tiene conto di altri fattori significativi oltre al tempo t, ad esempio potrebbe essere la stagionalità.

Prevediamo

y = 4856 * 10 + 105104

Otteniamo 153.664 vendite il mese prossimo. Se aggiungiamo un nuovo punto al grafico, vediamo subito che R^2 è migliorato.

Pertanto, puoi prevedere i dati per diversi mesi a venire, ma senza prendere in considerazione altri fattori, la tua previsione si troverà sulla linea di tendenza e non sarà informativa come vorresti. Inoltre, la previsione a lungo termine fatta in questo modo sarà molto approssimativa.

Puoi migliorare la precisione del modello aggiungendo la stagionalità alla funzione di tendenza, cosa che faremo nel prossimo articolo.

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

Diamo un'occhiata a 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. Inseriremo i dati per l'analisi nella tabella:

Una linea di tendenza in Excel è un grafico di una funzione di approssimazione. A cosa serve: fare previsioni sulla base di 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 adattamento lineare ha dato scarsa confidenza e scarsi risultati. La previsione sarà imprecisa.

Attenzione!!! La linea di tendenza non può essere aggiunta ai seguenti tipi di grafici e diagrammi:

• petalo;
• circolare;
• superficie;
• anulare;
• volume;
• impilato.



Equazione della linea di tendenza in Excel

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

Dovresti scegliere il tipo di display che illustra in modo più accurato l'andamento dei cambiamenti nell'input dell'utente. Scopriamo le opzioni.

Approssimazione lineare

La sua immagine geometrica è diritta. Pertanto, viene utilizzata un'approssimazione lineare per illustrare una metrica che aumenta o diminuisce a una velocità costante.

Si consideri il numero condizionale di contratti conclusi dal gestore nel corso di 10 mesi:

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

Seleziona il grafico - "aggiungi una linea di tendenza". Nei parametri selezionare il tipo lineare. Aggiungi il valore della precisione dell'approssimazione e l'equazione della linea di tendenza in Excel (basta selezionare le caselle nella parte inferiore della finestra "Parametri").

Otteniamo il risultato:

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

y = 4.503x + 6.1333

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

La retta nel grafico mostra la crescita stabile della qualità del lavoro del manager. Il valore dell'accuratezza dell'approssimazione è 0,9929, che indica che la retta calcolata è in buon accordo con i dati iniziali. Le previsioni devono essere accurate.

Per prevedere il numero di contratti conclusi, ad esempio, nel periodo 11, è necessario sostituire 11 invece di x nell'equazione. Durante i calcoli, apprendiamo che nell'11° periodo questo manager concluderà 55-56 contratti.

Linea di tendenza esponenziale

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

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

Costruiamo un programma. Aggiungi una linea esponenziale.

L'equazione è la seguente:

y = 7,6403e ^ -0,084x

• dove 7.6403 e -0.084 sono costanti;
• e è la base del logaritmo naturale.

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

Linea di tendenza logaritmica in Excel

Viene utilizzato per i seguenti cambiamenti nell'indicatore: prima, rapida crescita o declino, quindi - stabilità relativa. La curva ottimizzata si adatta bene a questo "comportamento" del valore. L'andamento logaritmico è adatto a prevedere le vendite di un nuovo prodotto che è appena stato introdotto sul mercato.

Nella fase iniziale, il compito del produttore è aumentare la base di clienti. Quando un prodotto ha un cliente, deve essere conservato e sottoposto a manutenzione.

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

R2 è vicino al valore 1 (0,9633), che indica l'errore di approssimazione minimo. Prevediamo i volumi di vendita nei periodi successivi. Per fare ciò, è necessario sostituire il numero del punto nell'equazione invece di x.

Ad esempio:

Periodo	14	15	16	17	18	19	20
Previsione	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Per calcolare i dati previsionali è stata utilizzata una formula del modulo: = 272,14 * LN (B18) +287,21. Dove B18 è il numero del periodo.

Linea di tendenza polinomiale in Excel

Questa curva è caratterizzata da aumento e diminuzione variabili. 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 punti estremi sono il terzo grado, tre sono il quarto.

Una tendenza polinomiale in Excel viene utilizzata per analizzare un set di dati di grandi dimensioni di un valore instabile. Diamo un'occhiata all'esempio della prima serie di valori (prezzi del petrolio).

Per ottenere un tale valore dell'accuratezza dell'approssimazione (0.9256), è stato necessario porre il 6° grado.

Ma questa tendenza permette di fare previsioni più o meno accurate.

Cosa fare se non ci sono misurazioni di tempistiche per determinati volumi/dimensioni di prodotti? Oppure il numero di misurazioni non è sufficiente 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.

Consideriamo una situazione reale: in magazzino, al fine di stabilire il valore del costo del lavoro per la selezione della casella dell'ordine, sono state effettuate osservazioni sui tempi. I risultati di queste osservazioni sono presentati nella Tabella 1 di seguito.

Successivamente si è reso necessario determinare il tempo necessario per la selezione di 0,6 e 0,9 m3 di merce/ordine. A causa dell'impossibilità di effettuare ulteriori studi temporali, il tempo impiegato per la selezione di questi volumi di ordini è stato calcolato utilizzando equazioni di regressione in MS Excel. Per questo, la tabella 1 è stata convertita in tabella 2.

Selezione di un 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 un menu contestuale, nel quale è stata selezionata la voce: "aggiungi linea di tendenza" (fig. 2).

Aggiunta di una linea di tendenza, Fig. 2

Nella finestra visualizzata per l'impostazione del formato della linea di tendenza (fig. 3) sono stati selezionati in sequenza: il tipo di linea è lineare/potenza e sono stati verificati i seguenti elementi: "mostrare l'equazione sul diagramma" e "mettere sul diagramma il valore dell'attendibilità dell'approssimazione (R^2)" (coefficiente di determinazione).

Formato della linea di tendenza, Fig. 3

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

Dipendenza calcolata lineare, Fig. 4

Dipendenza calcolata dalla legge di potenza, Fig. 5

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

Prendiamolo come il principale: una dipendenza calcolata lineare. Quindi i valori del tempo trascorso in base alla quantità di merci 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 le osservazioni temporali sono presentati nella Tabella 3 che segue.

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

Usando la formula: y = 54.511x + 0.1489, imposteremo il consumo di tempo per la quantità di merci per la quale non sono state fatte osservazioni temporali 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 in operazioni che, per vari motivi, non sono state coperte dalle osservazioni temporali.

L'esecuzione delle attività per la creazione di una linea di tendenza si distingue per il fatto che i dati iniziali possono essere un insieme di numeri non correlati tra loro.

La previsione secondo il normale programma è impossibile, poiché il suo coefficiente di determinismo (R ^ 2) sarà vicino allo zero.

Questo è il motivo per cui vengono utilizzate funzioni speciali.

Ora li costruiremo, configureremo e analizzeremo.

Versione facile da costruire

Il processo di costruzione di una linea di tendenza consiste in tre fasi: inserimento dei dati iniziali in excel, costruzione di un grafico, scelta di una linea di tendenza e dei suoi parametri.

Iniziamo inserendo i dati.

1. Creare una tabella con i dati iniziali in Excel.

(Immagine 1)

2. Seleziona le celle B3: B17 e vai alla scheda "Inserisci" e seleziona "Grafico".

(Figura 2)

3. Dopo che il grafico è stato creato, puoi aggiungere didascalie e un titolo.

Innanzitutto, fai clic con il pulsante sinistro del mouse sul bordo del grafico per selezionarlo.

Quindi vai alla scheda "Progettazione" e seleziona "Layout 1".

(Figura 3)

4. Passiamo alla costruzione di una linea di tendenza. Per fare ciò, seleziona nuovamente il grafico e vai alla scheda "Layout".

(Figura 4)

5. Fare clic sul pulsante "Linea di tendenza" e selezionare "approssimazione lineare" o "approssimazione esponenziale".

(Figura 5)

Quindi abbiamo costruito la Trendline primaria, che potrebbe non corrispondere alla realtà.

Questo è il nostro risultato intermedio.

(Figura 6)

E quindi, dovrai regolare i parametri della nostra linea di tendenza o selezionare un'altra funzione.

Versione professionale: scelta di una linea di tendenza e impostazione dei parametri

6. Fare clic sul pulsante "Linea di tendenza" e selezionare "Parametri aggiuntivi e linee di tendenza".

(Figura 7)

7. Nella finestra "Formato della linea di tendenza", mettiamo la casella di controllo accanto a "metti sul diagramma il valore dell'affidabilità dell'approssimazione R ^ 2 e fai clic sul pulsante" chiudi ".

Vediamo sul diagramma il coefficiente R ^ 2 = 0,6442

(Figura 8)

8. Annullare le modifiche. Seleziona il grafico, fai clic sulla scheda "Layout", sul pulsante "linea di tendenza" e seleziona "No".

9. Vai alla finestra "Formato linea di tendenza", ma per selezionare la linea di tendenza "Polinomiale", cambiamo il grado, raggiungendo gli indicatori del coefficiente R ^ 2 = 0.8321

(Figura 9)

Previsione

Se abbiamo bisogno di indovinare quali dati si potrebbero ottenere nella dimensione successiva, nella finestra "Trendline Format" indichiamo il numero di periodi per i quali viene fatta la previsione.

(Figura 10)

Sulla base del pronostico, possiamo ipotizzare che il 25 gennaio il numero di punti segnati sarebbe stato compreso tra 60 e 70.

Conclusione

E in conclusione, se sei interessato alla formula con cui viene costruito il trend, nella casella "Formato della linea di tendenza" spunta la casella accanto a "mostra l'equazione sul diagramma".

Ora sai come completare l'attività e costruire una linea di tendenza, anche in un programma come Excel 2010.

Non esitate a fare domande.