Domanda Excel-VBA_Grafico dinamico con previsione

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Buongiorno a tutti e grazie per l'aiuto che sempre mi dedicate.
Vi espongo il mio problema e allego il file di esempio, premettendo che la discussione potrebbe riguardare sia excel di base sia VBA, a seconda di come uno vuole inquadrare il problema.

Ho una serie di lunghezza varibile che viene aggiornata di volta in volta tramite l'aggiunta di un'osservazione attraverso una userform come nella discussione al link https://www.forumexcel.it/forum/threads/vba_inserire-dati-tramite-useform-e-copia-intervalli-di-celle.30922/#post-248561

Il problema è il seguente.
Contestualmente all'aggiunta della n-esima osservazione avrei bisogno di produrre una previsione a n+1, n+2 e n+3 e il relativo range dell'intervallo di confidenza per poi, sempre di volta in volta, rappresentare il tutto in un grafico come si può vedere nel file.

Il problema sta nella dinamicità che vorrei dare a questa operazione. Mi spiego meglio facendo riferimento all'esempio che ho prodotto. Se ho a disposizione 3 osservazioni, il range "previsione" dovrebbe riguardare i tempi 4 5 e 6 per rappresentare il tutto graficamente. Quando avrò a disposizione la 4 osservazione, il range di previsione dovrebbe shiftarsi di una posizione in avanti cioè riguardare i tempi 5 6 7 e a t=4 quello che era il primo dato "previsto" dovrebbe essere sostituito dal nuovo valore effettivamente osservato. Nel file allegato ho ottenuto questo risultato in maniera statica cioè ripetendo di volta in volta la stessa operazione. Quello di cui avrei bisogno è che si aggiornasse sempre lo stesso grafico e quindi le colonne dei valori previsti man mano che ho a disposizione nuove osservazioni effettive. Visto che il file è pensato utilizzando un codice VBA per gli input sarebbe preferibile che il grafico si potesse aggiornare sempre sempre tramite una macro.
Non so se è possibile fare quanto richiesto, ma vi ringrazio per l'attenzione e auguro una buona domenica.
 

Allegati

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
6.776
115
76
Catania
Excel2010
264
Salve a tutti
Purtroppo non posso aiutarti! Sono fermo alla vers.2010 di Excel e non dispongo della funzione che utilizzi.
Speriamo nell'intervento di qualcuno che abbia quella versione.

Ciao,
Mario

PS - Mi studio il problema per cercare di realizzarlo anche con versioni precedenti.
 

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Ciao Marius44 @Marius44 e grazie. Si la funzione previsione è disponibile credo dalla versione 2016. Penso che lo stesso risultato "statistico" si potrebbe realizzare facendo una regressione sulla serie disponibile e poi utilizzare i parametri per stimare le osservazioni future; il risultato dovrebbe essere lo stesso ma in maniera molto meno compatta. Resterebbe comunque la questione del grafico e dell'aggiornamento dinamico dei parametri che credo prescinde in un certo senso dalla funzione previsione. Comunque aggiorniamoci. Grazie.
 

Bruno

Utente assiduo
Expert
13 Settembre 2015
1.650
115
Italy
365/64 Bit W10
203
Ciao

In cella D3 la formula
=SE($B3>0;SE($B4=0;$B3;#N/D);PREVISIONE.ETS($A3;$B$3:$B$4;$A$3:$A$4;1;1))

In cella E3 la formula
=SE($B3>0;SE($B4=0;$B3;#N/D);PREVISIONE.ETS($A3;$B$3:$B$4;$A$3:$A$4;1;1))

In cella F3 la formula
=SE($B3>0;SE($B4=0;$B3;#N/D);$D3+PREVISIONE.ETS.INTCONF(A3;$B$3:$B$5;$A$3:$A$5;0,95;1;1))

Le formule sono da trascinare in basso per il range da utilizzare
Ora la macro aggiornerà i riferimenti del grafico3

Visual Basic:
Option Explicit

Sub Macro1()
    Dim K As Long, x As Long
    With ActiveSheet
        .ChartObjects("Grafico 3").Activate
        K = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With

    ActiveChart.FullSeriesCollection(1).Values = "=Foglio1!$B$3:$B$" & K
    ActiveChart.FullSeriesCollection(2).Values = "=Foglio1!$D$3:$D$" & K + 3
    ActiveChart.FullSeriesCollection(3).Values = "=Foglio1!$E$3:$E$" & K + 3
    ActiveChart.FullSeriesCollection(4).Values = "=Foglio1!$F$3:$F$" & K + 3
   
    For x = 1 To 4
        ActiveChart.FullSeriesCollection(x).XValues = "=Foglio1!$A$3:$A$" & K + 3
    Next
    ActiveCell.Select
End Sub
 

Allegati

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Ciao Bruno @Bruno e grazie. Il risultato è esattamente quello che tu hai raggiunto, però non ho ben capito se le formule vanno trascinate ogni volta che si inserisce un'osservazione o se, inserita l'osservazione, basta far partire la macro di aggiornamento e si aggiornano i valori della previsione. Attendo un tuo feedback, grazie-...
 

Bruno

Utente assiduo
Expert
13 Settembre 2015
1.650
115
Italy
365/64 Bit W10
203
Ciao

Tu trascina le formule in basso di 1000 righe ed oltre se necessario
La macro aggiorna sempre un più piccolo range in base all'ultima cella in cui hai inserto i dati.
 

ggratis

VBA Expert
Expert
27 Settembre 2015
1.752
65
Lecce - Pisa
Excel 2010
84
Se ho a disposizione 3 osservazioni, il range "previsione" dovrebbe riguardare i tempi 4 5 e 6 per rappresentare il tutto graficamente. Quando avrò a disposizione la 4 osservazione, il range di previsione dovrebbe shiftarsi di una posizione in avanti cioè riguardare i tempi 5 6 7 e a t=4 quello che era il primo dato "previsto" dovrebbe essere sostituito dal nuovo valore effettivamente osservato
...forse sbaglierò anch'io, ma credo che la soluzione cercata (numericamente parlando), sia qualcosa del genere:
=SE(B3="";PREVISIONE.ETS($A3;SCARTO($B$2;CONTA.VALORI($B2:B$3);;-3);SCARTO($A$2;CONTA.VALORI($B2:B$3);;-3);1;1);B3)
...sbaglierò ancora, ma quanto scritto da Ciriaco a lettere, non corrisponde a quanto riportato nel suo esempio numerico, nel senso che gli intervalli di riferimento della funzione previsione.ets si sono dilatati invece di traslare.
Saluto_saluto
 

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Ciao Bruno @Bruno ggratis @ggratis e grazie... Ho rivisto il file di Bruno @Bruno seguendo le tue indicazioni... il "problema" sta nel fatto che il range di osservazioni su cui si calcola le previsioni resta fisso - infatti all'interno della formula PREVISIONE.ETS le celle $B$3:$B$4;$A$3:$A$4 bloccate - mentre di volta in volta si dovrebbe utilizzare l'osservazione in più disponibile per produrre "previsioni migliori". ggratis @ggratis penso di aver risposto anche al tuo appunto: nel mio esempio il range su cui si effettua la previsione si "dilata" al sopraggiungere di una nuova osservazione, ma non ha senso che si trasli senza aumentare di ampiezza perchè statisticamente significherebbe rinunciare alle osservazioni più lontane. Comunque la formula inserita da ggratis @ggratis funziona per quello che era il mio obiettivo. Resta da aggiustare un po' la macro per il grafico (colorazione linee) ad ogni aggiornamento. Ci lavoro in questi giorni e vi do un feedback.
 

ggratis

VBA Expert
Expert
27 Settembre 2015
1.752
65
Lecce - Pisa
Excel 2010
84
ma non ha senso che si trasli senza aumentare di ampiezza perchè statisticamente significherebbe rinunciare alle osservazioni più lontane.
Beh mi pare che sia questo quello che hai scritto...ad ogni modo per ottenere quest'ultimo risultato, nella funzione scarto è sufficiente modificare l'altezza di riferimento, contando i valori delle osservazioni.
Scrivo dal cellulare...
 
Ultima modifica:

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
ggratis @ggratis io ho parlato del range di "previsione" che deve essere fisso di ampiezza 3 (ma shiftarsi in avanti di volta in volta), non del range sui cui effettuo la regressione che anzi deve ampiarsi di 1 ad ogni nuova osservazione. Tanto è vero che nel 1 grafico la formula effettua la regressione sulle celle $B$3:$B$5 (3 osservazioni) mentre nel secondo grafico sulle celle $K$3:$K$6 (4 osservazioni) e - tranne la colonna che è diversa - la riga di partenza è la 3 in entrambi i casi. In ogni caso ti ringrazio dei consigli, nel weekend proverò a sistemare il tutto. :)
 

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Ciao a entrambi....Ho allegato quella che mi sembra la soluzione migliore...Ho usato le formule di ggratis @ggratis per la soluzione numerica del problema e la macro di Bruno @Bruno per aggiornare di volta in volta il grafico. In realtà ci sarebbe un altro modo per fare il grafico dinamico senza macro ma con la funzione SCARTO con l'unico problema che la previsione graficamente non si ferma a n+3 ma copre tutto il range che viene impostato (per questo non l'ho allegata, ma se può servire la carico). Grazie !
 

Allegati

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
6.776
115
76
Catania
Excel2010
264
Salve a tutti
Non è molto professionale ma ... qualcosa la fa.
Nel tuo file ho aggiunto il Foglio2. Prova ad inserire un numero (per provare non troppo grande) nella cella C1 e vedi cosa succede.

Cosa ho utilizzato? Innanzi tutto la Funzione PREVISIONE di Excel 2010 e poi un po' di codice VBA.
Dimmi (ditemi) come sembra. Ovviamente si può migliorare.

Ciao,
Mario
 

Allegati

Roma_89

Utente junior
2 Gennaio 2020
41
6
Excel 2016
0
Ciao Marius44 @Marius44 ho dato uno sguardo perchè hai fatto un bel lavoro in VBA ma necessito di più tempo per capire tutti i passaggi. L'unica cosa che posso dirti adesso è che credo ci sia un problema legato all'inserimento dei dati, non ho ben capito quale possa essere il problema ma se ad esempio inserisco solo due valori e come "valore fisso" metto 3 poi all'inserimento di nuovi dati se faccio girare la macro mi resta bloccato il range di "regressione" sui primi due e l'intervallo non si ampia. Va comunque nella direzione giusta che mi hanno suggerito con l'unica differenza che tu utilizzi l'approccio VBA. Grazie...
 

ggratis

VBA Expert
Expert
27 Settembre 2015
1.752
65
Lecce - Pisa
Excel 2010
84
con l'unico problema che la previsione graficamente non si ferma a n+3 ma copre tutto il range
vorrei essere smentito, non credo si possa fare più di quanto ti ho allegato senza ricorrere al vba...
Saluto_saluto
Un saluto a Marius44 @Marius44 (cappello_saluta)
 

Allegati

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
6.776
115
76
Catania
Excel2010
264
Ciao
Quando vuoi inserire nuovi dati devi PRIMA cancellare quello che erano stati calcolati in precedenza, cioè quelli che hanno il fondo in giallo.

Ciao,
Mario
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!