eliminare valori aberranti in base a grafico

[wallace&gromit]

Ciao,
sono alla ricerca disperata di un sistema semplice per "pulire" una banca dati.
Nel grafico riesco a vedere abbastanza chiaramente che alcuni dati sono aberranti, dovuti a qualche malfunzionamento o momentaneo blackout. Essi si esprimono in picchi verso il basso, che possono raggiungere 0, ma non sempre. Soprattutto, possono esserci valori troppo bassi per quel periodo, che però valgono per altri periodi (questo per dire che non posso agire a livello di filtraggio dei dati).
Nella soluzione ideale vorrei potere cliccare su un determinato dato e correggere il valore in tabella, inserendone uno ragionevole per quel periodo. Per esempio nel grafico di esempio per la data 18.12.2019 figura un valore 5, mentre io vorrei impostare 21. La stessa cosa per quelli vicini, dal 16.12 al 20.12.
Se mi ricordo bene era una cosa che con SPSS si poteva fare abbastanza agevolmente, e parlo della preistoria informatica. In excel non saprei dove mettere mano, a parte. guardare la data corrispondente, scorrere la banca dati fino a quel punto, modificare il valore (molto laborioso).

Qui il file di esempio
https://www.dropbox.com/s/lycfbzeyaur67n8/valori%20aberranti%20grafico.xlsx?dl=0

[Marius44]

Ciao
Sarebbe interessante sapere "come" arrivano quei dati in col.B e "perchè" li ritieni aberranti.
Poi, per quanto riguarda la visualizzazione del Grafico, ti propongo la seguente macro che posiziona il Grafico al top della schermata in base alla cella che hai selezionato

Codice: Seleziona tutto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim tp As Double
tp = Rows(ActiveWindow.ScrollRow).Top + 7
ActiveSheet.Shapes("Grafico 1").Top = tp
Application.ScreenUpdating = True
End Sub

Aggiungo che se cambio un dato nella colonna B il grafico, ovviamente, si adatta al nuovo dato.

Ciao,
Mario

[Anthony47]

Sarebbe interessante sapere [...] "perchè" li ritieni aberranti

Eh he, quei dati sono aberranti perche' rovinano l'armonia del grafico.
Mi sovviene della "costante di Flanaghan", definita come quel valore da aggiungere, o sottrarre, o moltiplicare, o con cui dividere il dato calcolato per farlo tornare col dato atteso...

Quanto al quesito di Wallace (Gromit penso che invece abbia gia' trovato la soluzione) possiamo usare l'istruzione Select del linguaggio macro del vecchissimo Excel 4 (e che rimane ancora nascosto ma disponibile nelle versioni XL piu' recenti), in grado di dare informazioni sull'elemento correntemente selezionato; se l'elemento e' un punto di una serie questa restituira' il numero della serie e il numero del punto.
Inoltre ogni serie e' rappresentata da una Formula, che indica in sequanza il titolo del grafico, l'area dell'asse X, l'area dell'asse Y, la posizione in asse Z (ha senso per i grafici ad area); es

Codice: Seleziona tutto

=SERIE(Foglio1!$B$1;Foglio1!$A$2:$A$906;Foglio1!$B$2:$B$906;1)

Con queste informazioni possiamo sviluppare una macro come questa:

Codice: Seleziona tutto

Sub GotoDataPoint()
Dim SerPoint As String, SnP, ChForm As String, SplForm
'
SerPoint = ExecuteExcel4Macro("selection()")
SerPoint = Replace(Replace(SerPoint, "S", "#", , , vbTextCompare), "P", "#", , , vbTextCompare)
SnP = Split(SerPoint, "#")
If UBound(SnP) = 2 Then
    ChForm = ActiveChart.SeriesCollection(CLng(SnP(1))).Formula
    SplForm = Split(ChForm, ",")
    Sheets(Split(SplForm(UBound(SplForm) - 1), "!")(0)).Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Cells(SnP(2), 1).Select
Else
    Debug.Print SerPoint
    Beep
End If
End Sub


Inseriscila su un modulo standard, poi collegala a una scelta veloce (tipo Contr-m) o ad altro metodo per lanciarla rapidamente.
Seleziona 1 punto sul grafico e avvia la macro: dovrebbe selezionarti la cella dei dati di origine cui quel punto fa riferimento.
Dovrai ripetere per ogni punto anomalo, magari usando la macro di Mario (vedi sopra) per avere sempre il grafico a portata di mano.

Ciao a tutti

[wallace&gromit]

PC-facile, il top, come sempre!!!
Perfetto, ho combinato le vostre due macro e ottengo esattamente quello che volevo:
seleziono un punto aberrante, Ctrl + m, digito il nuovo valore e vedo come varia sul grafico.

A discolpa di Wallace:
quelli presentati sono dati fittizi, ma anche nella realtà non sta falsificando nulla a suo piacimento quelli sono proprio dati aberranti dovuti a manipolazioni errate o volute ma temporanee: provengono da manometri di pressione, se Gromit per qualche motivo apre il rubinetto ci sarà un calo di pressione artificiale che non esprime il dato reale della pressione idrostatica in quel momento, quindi va sostituito con un valore realistico, altrimenti andrebbe a falsare tutti i successivi calcoli di correlazione tra i vari strumenti.

[wallace&gromit]

azz... sembrava tutto bello, ma con la macro di Anthony sorge un problema se il grafico non si trova sullo stesso foglio della banca dati, mi da errore nel metodo range.

[Anthony47]

Allora usiamo Goto invece che Select, modificando le istruzioni If Then:

Codice: Seleziona tutto

If UBound(SnP) = 2 Then
    ChForm = Replace(ActiveChart.SeriesCollection(CLng(SnP(1))).Formula, "'", "")
    SplForm = Split(ChForm, ",")
    Application.Goto Sheets(Split(SplForm(UBound(SplForm) - 1), "!")(0)).Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Cells(SnP(2), 1)
Else


Prova...

[wallace&gromit]

Sì, così funziona, ovviamente perdo la funzionalità di prima, in cui il grafico rimaneva visibile.
Ma capisco che non posso avere la botte piena e la moglie ubriaca, se possibile cercherò di avere i dati sullo stesso foglio e usare la combinazione di macro precedente, altrimenti uso questa.

[Anthony47]

Sì, così funziona, ovviamente perdo la funzionalità di prima, in cui il grafico rimaneva visibile.
Ma capisco che non posso avere la botte piena e la moglie ubriaca, se possibile cercherò di avere i dati sullo stesso foglio e usare la combinazione di macro precedente, altrimenti uso questa.

Ovviamente si perde qualcosa
Si potrebbe ovviare creando due finestre in cui visualizzare due fogli diversi del file, ma la gestione delle finestre da vba non e' così intuitiva.
Una possibile alternativa che mi piace di piu' e' lavorare con una userform che replica i dati sorgente e ne consente la modifica.
Ad esempio:
Questo codice va in un Modulo standard del vba inizialmente vuoto:

Codice: Seleziona tutto

Public lbSource As String

Sub ShowDataPoint()
Dim SerPoint As String, SnP, ChForm As String, SplForm
Dim sFrom As Long, LBAdr As String, grAdr As String
'
SerPoint = ExecuteExcel4Macro("selection()")
SerPoint = Replace(Replace(SerPoint, "S", "#", , , vbTextCompare), "P", "#", , , vbTextCompare)
SnP = Split(SerPoint, "#")
If UBound(SnP) = 2 Then
    ChForm = Replace(ActiveChart.SeriesCollection(CLng(SnP(1))).Formula, "''", "")
    SplForm = Split(ChForm, ",")
    sFrom = CLng(SnP(2)) - 3
    If sFrom < 1 Then sFrom = 1
    grAdr = Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Address
    LBAdr = Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Cells(sFrom, 1).Resize(7, 1).Address
    UserForm1.ListBox1.RowSource = Replace(SplForm(UBound(SplForm) - 1), grAdr, LBAdr)
    lbSource = Replace(SplForm(UBound(SplForm) - 1), grAdr, LBAdr)
    UserForm1.Show vbModeless
    UserForm1.ListBox1.ListIndex = 3
    UserForm1.TextBox1.Text = ""
    UserForm1.ListBox1.Enabled = False
    UserForm1.TextBox1.SetFocus
Else
    Debug.Print "Err_A", SerPoint
    Beep
End If
End Sub


Scarica poi questi due file:
https://www.dropbox.com/s/hb5r8kl8hhg0p ... 1.frm?dl=0
https://www.dropbox.com/s/3fwml5b6bira8 ... 1.frx?dl=0

Sono i file descrittivi di una userform; dal vba fai Menu /File /Importa file; seleziona il file WnG_UserForm1.frm scaricato e completa con Apri.

A questo punto puoi:
-selezionare un punto del tuo grafico
-avviare la Sub ShowDataPoint
Dovrebbe aprirsi la userform; in un ListBox vengono elencati i valori nell'intorno del punto selezionato (3 prima e 3 dopo). Hai un textbox in cui puoi inserire un nuovo valore; il pulsante Update dovrebbe inserire questo "nuovo" valore al posto di quello precedente, il ListBox e il grafico si aggiornano.

I valori nel listbox non sono selezionabili.
Per una nuova modifica vai sul grafico e seleziona un nuovo punto da modificare, ma poi devi cliccare sulla userform per aggiornare il contenuto del ListBox, come viene ricordato da una label (quando non sei sicuro della corrispondenza Userform - punto selezionato clicca sulla userform e non fa male)

Quando hai finito devi chiudere la form con la "X"; se ti accorgi che ci sono altri punti da correggere, torna all'inizio: selezionare un punto del tuo grafico /avviare la Sub ShowDataPoint

Non e' tanto lineare, e in effetti se hai pochi punti da modificare forse e' meglio rimanere con la procedura attuale (Sub GotoDataPoint), che rimane funzionante anche dopo l'aggiunta della nuova procedura con userform

Ciao

[wallace&gromit]

I miracoli di Anthony:
- La botte è piena
- La moglie è ubriaca
- Con l'aggiunta della lista hai ubriacato pure l'ipotetica amante
- Adesso però mi hai ingolosito: è chiedere troppo di avere due freccettine su e giù che mi permettono di navigare tra i valori vicini nella lista? Sarebbe il top dei top!

[Anthony47]

Adesso però mi hai ingolosito: è chiedere troppo di avere due freccettine su e giù che mi permettono di navigare tra i valori vicini nella lista? Sarebbe il top dei top!

Le tue freccettine su e giu' sconvolgerebbero tutta l'impostazione, quindi opto per una scrollbar

La nuova userform ha questo aspetto:


La procedura si avvia sempre selezionando un singolo punto sul grafico e poi eseguendo la Sub ShowDataPoint.
La scrollbar visualizza /seleziona la posizione del punto selezionato; il listbox visualizza il punto selezionato e i punti dell'intorno
Il textbox e il pulsante consentono la modifica del punto selezionato.
Per modificare il punto selezionato, si puo' farlo sul grafico e poi cliccare la userform per aggiornare i dati (come era prima); oppure con la scrollbar.
La scrollbar rappresenta tutti i punti (dal 1°, tutto a sx; all'ultimo, tutto a dx); gli spostamenti possono essere liberi (riposizionare la scrollbar), a incrementi di +/-1 (tramite le freccette), o a blocchi di 1/16.
La scrollbar e' bidirezionale: se spostata aggiorna la posizione sul grafico; se si cambia la posizione sul grafico cliccando sulla form la scrollbar si aggiorna.
Il listbox come sempre visualizza solamente i dati

La nuova userform corrisponde a questi file:
https://www.dropbox.com/s/pncdx6l51gkod ... 1.frm?dl=0 (il file da importare sul vba)
https://www.dropbox.com/s/mu2d2h8uq0x22 ... 1.frx?dl=0

Prima di importare la nuova userform devi eliminare la precedente (Menu /File /Rimuovi Userform1)

Quasi dimenticavo: anche il codice sul modulo standard va rivisto come segue:

Codice: Seleziona tutto

Public lbSource As String, dOff As Long
Public ufTop As Long, ufLeft As Long, igNora As Boolean


Sub ShowDataPoint()
Dim SerPoint As String, SnP, ChForm As String, SplForm
Dim sFrom As Long, LBAdr As String, grAdr As String
'
SerPoint = ExecuteExcel4Macro("selection()")
SerPoint = Replace(Replace(SerPoint, "S", "#", , , vbTextCompare), "P", "#", , , vbTextCompare)
SnP = Split(SerPoint, "#")
If UBound(SnP) = 2 Then
    igNora = True
    ChForm = Replace(ActiveChart.SeriesCollection(CLng(SnP(1))).Formula, "''", "")
    SplForm = Split(ChForm, ",")
    sFrom = CLng(SnP(2)) - 3
   
    If sFrom < 1 Then sFrom = 1
    dOff = sFrom - CLng(SnP(2)) + 3
    grAdr = Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Address
    UserForm1.ScrollBar1.Max = Range(grAdr).Rows.Count
    UserForm1.ScrollBar1.LargeChange = Range(grAdr).Rows.Count / 16
    LBAdr = Range(Split(SplForm(UBound(SplForm) - 1), "!")(1)).Cells(sFrom, 1).Resize(7, 1).Address
    UserForm1.ListBox1.RowSource = Replace(SplForm(UBound(SplForm) - 1), grAdr, LBAdr)
    lbSource = Replace(SplForm(UBound(SplForm) - 1), grAdr, LBAdr)
    UserForm1.Show vbModeless
    UserForm1.ScrollBar1.Value = CLng(SnP(2))
    UserForm1.ListBox1.ListIndex = 3 - dOff
    UserForm1.TextBox1.Text = ""
    UserForm1.ListBox1.Enabled = False
    UserForm1.TextBox1.SetFocus
    igNora = False
Else
    Debug.Print "Err_A", SerPoint
    Beep
End If
End Sub


Cancella tutto il codice precedente, prima di copiare il nuovo

Ciao

[wallace&gromit]

Questa è una delle applicazioni più fantastiche che abbia mai visto, è utilissima anche solo per verificare a schermo i dati di un grafico, che spesso sono talmente ravvicinati da non riuscire a distinguere dove esattamente succede qualcosa (per esempio vedi nettamente che ad un certo punto si instaura un trend diverso, ma capire in quale data succede leggendolo dalla lista dei dati è difficile, così invece diventa un giochetto).
Secondo me potresti mettere questa macro sotto "I vostri lavori", oppure crearne uno nuovo: "le genialate di Anthony"!

[Anthony47]

Questa è una delle applicazioni più fantastiche che abbia mai visto, è utilissima anche solo per verificare a schermo i dati di un grafico, che spesso sono talmente ravvicinati da non riuscire a distinguere dove esattamente succede qualcosa

Mamma mia quanto entusiasmo scatena il vba...
Quanto a farlo diventare un "prodotto", ci provero'; ma la differenza tra un prototipo funzionante (quello esposto) e un prodotto sono notevoli, non e' detto che trovero' tempo e pazienza per riuscirci

Ciao