Grafico modicabile con un tasto

Hai un problema con Excel?

Nuova discussione

Grafico modicabile con un tasto
di NicoPana data: 11/09/2017 12:03:55
In allegato trovate un file, dove su 10 istanti temporali ho messo a confronto una riga di ricavi, con una riga di costi totali, e ho creato un semplice grafico a linee che visualizza le due serie.

La mia domanda è: dato che nel fiel da cui il grafico va a prelevare i dati, i costi totali sono calcolati come sommatoria in ogni istante di analisi dei costi di magazzino, di officina e laboratorio, potrei aggiungere un tasto sul grafico, tipo ad esempio una cella con un elenco a discesa che mi permette di selezionare la serie di dati da confrontare con i ricavi? e quindi confrontarli ad esempio con i soli costi di officina, piuttosto che con quelli di magazzino e laboratorio?

di Marius44 data: 11/09/2017 18:13:47
Ciao
Ho inserito nella cella A11 del Foglio("Dettaglio") una Convalida dati; quindi un Grafico e la macro sottostante.
per completezza ti allego il file. Non dovrebbe essere difficile, al caso, adattare la macro per il Foglio Grafico,

Prova e fai sapere. Ciao,
Mario

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
'cerca in Foglio1 il tipo di costo
    costo = Range("A11").Value
    riga = Application.WorksheetFunction.Match(costo, ActiveSheet.Range("A4:A8"), 0) + 3
'assegna intervallo al grafico
    nomecosto = Range("A" & riga).Value
    valY = "Dettaglio!B" & riga & ":K" & riga

    ActiveSheet.ChartObjects("Grafico 1").Activate
    ActiveChart.SeriesCollection(1).XValues = "Dettaglio!B1:K1"
    ActiveChart.SeriesCollection(1).Values = "Dettaglio!B2:K2"
    ActiveChart.SeriesCollection(1).Name = Range("A2").Value
    
    ActiveChart.SeriesCollection(2).Values = valY
    ActiveChart.SeriesCollection(2).Name = nomecosto
    
End If
End Sub

di Marius44 data: 14/09/2017 17:34:48
Ciao

@NicoPana
Almeno un ... piccolo riscontro (positivo o negativo) potresti darlo.

Ciao,
Mario
di nicopana (utente non iscritto) data: 15/09/2017 07:03:53
Oh scusami davvero. Stranamente non mi era tornato indietro nessun avviso via mail che mi fosse giunta una risposta, mi è arrivato adesso per il tuo sollecito.

Scusami davvero. Guardo subito e testo quello che mi hai scritto e ti do un riscontro.

In ogni caso grazie anticipatamente
di nicopana (utente non iscritto) data: 15/09/2017 08:36:28
Proviamo ad andare passo passo, perché ho provato poi ad applicare questo caso semplice ad uno un bel po' più complesso come organizzazione di dati e non mi ci sono più ritrovato.
Prima parte della macro:

"costo = Range("A11").Value
riga = Application.WorksheetFunction.Match(costo, ActiveSheet.Range("A4:A8"), 0) + 3"

praticamente mi dici che nella cella A11 dovrebbe trovare la tipologia di costo da leggere, andandosi a prendere la riga corrispondente al valore in A11 (nel range A4:A8), ma quel +3 a cosa fa riferimento?
di Marius44 data: 15/09/2017 14:21:45
Ciao
come avevo detto, in Range("A11") ho inserito una Convalida dati. Quando faccio una scelta dall'elenco nella cella vi è una stringa che assegno alla variabile "costo".
Il codice successivo confronta la variabile costo con i dati nell'intervallo A4:A8 e mi restituisce il numero di riga dove ha trovato il riscontro. Ora, poichè l'intervallo inizia dalla riga 4, la variabile riga se trova riscontro alla prima istanza mi dice che è uguale a 1, cioè la prima riga. A questo numero devo aggiungere +3 per ottenere la vera riga dove assumere i dati.

Spero sia chiaro.
Se non ti ritrovi con qualcosa di " più complesso ", domanda e vedremo di darti una risposta.
Ciao,
Mario
di nicopana (utente non iscritto) data: 17/09/2017 12:06:10
Si, infatti il problema è che devo adattare questa macro utilissima ad un foglio ben più complesso, e per non dare ulteriore fastidio vorrei provare ad adattarlo io in base a quello che mi hai costruito su questo esempio semplice.

Però è chiaro che devo capire come lavora; non posso girarvi quel prospetto perchè contiene dati sensibili.

Quindi ok per A11, dovrò inserirvi il codice della cella dove inserisco la convalida, e il +3 lo dovrò invece adattare a partire dalla prima riga rispetto alla prima in cui comincia la mia tabella da dove devo andare a prelevare i dati.

Andiamo avanti se è ok quello che ho scritto...

Le successive due righe le ho capite poco:

nomecosto = Range("A" & riga).Value
valY = "Dettaglio!B" & riga & ":K" & riga

Dovrebbe mettere insieme la colonna A e il valore restituito dalla funzione riga per individuare la tipologia del costo scelta, e poi la funzione valY trova proprio il valore numerico tra la colonna B e la K (che sarebbero le colonne che racchiudono i dati, e sempre il valore della funzione riga?
di Marius44 data: 17/09/2017 15:07:21
Ciao
Hai capito perfettamente!!!

Ora prova a riportare tutti i ragionamenti fatti alla tua realtà.
Aggiungo che, per quanto complesso possa essere, qualsiasi file "sensibile" si può renderlo "insensibile" sostituendo dati-sensibili con dati-di-fantasia.

Fai sapere. Ciao
Mario
di nicopana (utente non iscritto) data: 18/09/2017 07:25:55
Si ci mancherebbe, è chiaro che avrei potuto modificare i dati con alcuni valori non sensibili, ma è cosi complesso il file su cui dovrei lavorare che avrei perso meno tempo a capire come funziona la macro e ad adattarla al mio caso che non a modificare il foglio.

Tra l'altro per quel motivo ho creato quel file d'esempio che ho allegato alla mia prima risposta.

Faccio un'altra prova ed eventualmente ti chiedo info ulteriori
di nicopana (utente non iscritto) data: 18/09/2017 07:40:33
Domande su questa serie di righe

ActiveSheet.ChartObjects("Grafico 1").Activate
ActiveChart.SeriesCollection(1).XValues = "Dettaglio!B1:K1"
ActiveChart.SeriesCollection(1).Values = "Dettaglio!B2:K2"
ActiveChart.SeriesCollection(1).Name = Range("A2").Value

Dove trovo l'esatto nome del grafico che eventualmente creo nel mio foglio? Quello che invece qui è chiamato "Grafico1"?

Poi queste righe mi dicono:
- che come valori delle ascisse di prendere quelli del foglio Dettaglio da B1 a K1
- di visualizzare una prima serie di valori che sono quelli sempre nel foglio Dettaglio da B2 a K2
- non ho invece ben chiaro cosa sviluppa il comando della quarta riga
di Marius44 data: 18/09/2017 08:40:07
Ciao Nico
Vedrai che pian piano arriviamo al traguardo

Hai letto bene tutto quanto.
La quarta riga assegna alla serie il nome della serie stessa che, nell'esempio, è nella cella A2
Si può renderlo dinamico (come in effetti è) agendo sul "numero" (che è la riga).

Ciao,
Mario
di nicopana (utente non iscritto) data: 18/09/2017 08:49:15
Ti ringrazio davvero della pazienza.

Prima di andare avanti però, provando a cambiare il dato nella cella della convalida, il sistema mi restituisce un errore di debug (non riesce a trovare l'oggetto), restituendomi un codice di errore: cliccando sulla cella Debug, si apre la maschera di VBA e mi evidenzia in giallo la riga con il nome del grafico.

Il grafico sul foglio c'è, però in effetti guardando nella parte sinistra della maschera di Debug, dove c'è la struttura del file, non viene esplicitato come elemento singolo.
di Marius44 data: 18/09/2017 10:53:09
Ciao Nico
non avevo davanti il grafico e la macro ed ho commesso un errore.
Riporto la parte della macro che assegna i dati al Grafico e li commento subito dopo

assegna intervallo al grafico
nomecosto = Range("A" & riga).Value
'dopo aver individuato la riga assegna alla variabile "nomecosto" il testo nella cella A + riga
valY = "Dettaglio!B" & riga & ":K" & riga
'stesso discorso per i dati da imputare alla serie
ActiveSheet.ChartObjects("Grafico 1").Activate
'attiva il grafico
ActiveChart.SeriesCollection(1).XValues = "Dettaglio!B1:K1"
'assegna i valori dell'ascissa
ActiveChart.SeriesCollection(1).Values = "Dettaglio!B2:K2"
'assegna i valori dell'ordinata relativa alla serie RICAVI
ActiveChart.SeriesCollection(1).Name = Range("A2").Value
'assegna il nome della serie RICAVI
ActiveChart.SeriesCollection(2).Values = valY
'assegna i valori dell'ordinata relativa alla serie cercata
ActiveChart.SeriesCollection(2).Name = nomecosto
'assegna alla serie cercata il nome relativo

Spero sia più chiaro. Non tenere conto di quanto detto alle 08:40:07
Fai sapere. Ciao,
Mario
di nicopana (utente non iscritto) data: 19/09/2017 07:23:19
Nel provare a selezione un valore dall'elenco, restituisce un valore di run-time 1004 "errore definito dall'applicazione o dall'oggetto", e selezionando il debug mi seleziona in giallo la riga

ActiveChart.SeriesCollection(1).XValues = "Dettaglio!B1:K1"

con una freccetta gialla sulla sinistra che punta sulla riga
di Marius44 data: 19/09/2017 12:51:10
Ciao
Quando dici "nel provare a selezionare una voce dall'elenco" penso tu ti riferisca alla cella A11 (quella dove ci sta la Convalida dati).
Nell'esempio che ti ho allegato i dati "fissi" sono
- per l'asse delle ascisse ( x ) nell'intervallo B1:K1;
- per l'asse delle ordinate ( y ) nell'intervallo B2:K2 (serie 1)
mentre i dati "variabili" sono (fermo restando il riferimento all'asse x) sempre nell'intervallo B:K la cui riga è però relativa al tipo di costo selezionato in A11 (serie 2).

Non può darti quell'errore.
Hai fatto la prova con il file che ti ho allegato? Oppure hai apportato qualche variazione? E se si, dove?

Fai sapere. Ciao,
Mario
di nicopana (utente non iscritto) data: 19/09/2017 13:12:06
"Quando dici "nel provare a selezionare una voce dall'elenco" penso tu ti riferisca alla cella A11 (quella dove ci sta la Convalida dati)" - esatto

per il resto era tutto chiaro, non so perché il programma mi restituisce un errore, ho modificato qualcosa per adattarlo al mio file ma nulla di sostanziale.

Ti riallego il file modificato, dove al posto dei ricavi, come serie diciamo fissa, considero i costi totali, nella riga 19, e la cella di convalida è in A22
di Marius44 data: 19/09/2017 16:03:55
Ciao Nico
l'errore credo sia nel nome assegnato al Foglio.
Fai così: rinomina il Foglio (meglio cambia nome, senza spazi e senza - ) in questo modo

GrafOfficina16_17

cioè togli lo spazio tra GrafOfficina e i numeri e metti _ (underscore - lineetta bassa) invece del - (meno)

Ciao,
Mario
di nicopana (utente non iscritto) data: 20/09/2017 07:29:53
Perfetto, adesso funziona. Probabilmente erano i caratteri speciali a dargli fastidio. Adesso ti chiedo l'ultima cosa e credo che saremmo a posto:

non so se hai notato che i dati si fermano a Luglio, nel senso che a partire da Luglio, essendo poi dati cumulati che pescano da altri fogli, restano uguali. Quindi volevo andare ad aggiungere un'altra cella di controllo questa volta sui mesi, in cui andavo facilmente a scegliere il mese in cui far fermare la tabella da cui pescare i dati. Per esempio in questo caso sceglierei Luglio, che è l'ultimo mese di cui dispongo i dati aggiornati, o al massimo qualche mese precedente, e la tabella dei dati si dovrebbe fermare alla colonna H del foglio, anziché M, e di conseguenza si adatterebbe anche il grafico. E' possibile?
di Marius44 data: 20/09/2017 09:37:23
Ciao
se ho capito bene, è fattibile ma ... dovrai farlo tu

In una cella a tua scelta inserisci la lettera della colonna dove vuoi fermarti.
Nella macro aggiungi una variabile che assuma il valore di detta cella e, al posto della lettera K - in tutte le righe dove c'è, sostituiscila con la variabile.

Fai sapere. Ciao,
Mario
di nicopana (utente non iscritto) data: 20/09/2017 10:19:42
Ci provo, ma credo che sicuramente arriverò a chiederti un altro aiutino.

di nicopana (utente non iscritto) data: 21/09/2017 13:01:51
Nell'allegato in revisione 2 ho provato a modificare la macro in questo modo, creando la funzione colonnamese, che però mi pare non crei molti "movimenti" al grafico:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A22")) Is Nothing Then
'cerca in Foglio1 il tipo di costo
    costo = Range("A22").Value
    riga = Application.WorksheetFunction.Match(costo, ActiveSheet.Range("A4:A18"), 0) + 3
'definisce la colonna relativa al periodo di rilevazione in oggetto
    mese = Range("A24").Value
    colonnamese = Application.WorksheetFunction.Match(mese, ActiveSheet.Range("A1:M1"), 0) + 1
'assegna intervallo al grafico
    nomecosto = Range("A" & riga).Value
    valY = "GrafOfficina16_17!B" & riga & colonnamese & riga

    ActiveSheet.ChartObjects("Grafico 1").Activate
    ActiveChart.SeriesCollection(1).XValues = "GrafOfficina16_17!B1" & colonnamese
    ActiveChart.SeriesCollection(1).Values = "GrafOfficina16_17!B19" & colonnamese
    ActiveChart.SeriesCollection(1).Name = Range("A19").Value
        
    ActiveChart.SeriesCollection(2).Values = valY
    ActiveChart.SeriesCollection(2).Name = nomecosto

End If
End Sub

di Marius44 data: 21/09/2017 15:02:16
Ciao Nico
c'eri molto vicino ma io t'avevo detto di inserire in una cella "la lettera" relativa alla colonna fino alla quale mostrare i dati. Se vuoi farlo con una Convalida va bene lo stesso ma i dati della convalida DEVONO essere le lettere relative alla colonna. Dopo aver fatto questo cambia la tua macro con questa (e annota le differenze).

Fai sapere. Ciao,
Mario

PS . Dimentica di dirti che devi aggiungere la cella A24 nell'intersect altrimenti la macro viene lanciata solo se ci sono variazioni alla cella A22.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A22,A24")) Is Nothing Then
'cerca in Foglio1 il tipo di costo
    costo = Range("A22").Value
    riga = Application.WorksheetFunction.Match(costo, ActiveSheet.Range("A4:A18"), 0) + 3
'definisce il periodo di rilevazione
    mese = Range("A24").Value
'assegna intervallo al grafico
    nomecosto = Range("A" & riga).Value
    valY = "GrafOfficina16_17!B" & riga & ":" & mese & riga

    ActiveSheet.ChartObjects("Grafico 1").Activate
    ActiveChart.SeriesCollection(1).XValues = "GrafOfficina16_17!B1:M1"
    ActiveChart.SeriesCollection(1).Values = "GrafOfficina16_17!B19:" & mese & 19
    ActiveChart.SeriesCollection(1).Name = Range("A19").Value
        
    ActiveChart.SeriesCollection(2).Values = valY
    ActiveChart.SeriesCollection(2).Name = nomecosto

End If
End Sub

di nicopana (utente non iscritto) data: 22/09/2017 07:13:54
mi da un errore di Runtime 1004 su questa riga di testo:

ActiveChart.SeriesCollection(1).Values = "GrafOfficina16_17!B19:" & mese & 19

Forse mi sembra come se la funzione mese non restituisce la lettera della colonna combinandola con il 19?
di Marius44 data: 22/09/2017 09:16:54
Ciao Nico
a me funziona tutto. Ti alle go il file Marius.

Ciao,
Mario
di nicopana (utente non iscritto) data: 22/09/2017 13:47:25
Non avevo compreso io di dover indicare la lettera della colonna, pensavo mi avessi risolto la cosa con la cella di convalida, che era per me di più facile utilizzo.

Se per esempio volessi usare quella, con una funzione sul foglio di lavoro, non necessariamente nella macro, dovrei solo ottenere la lettera della colonna della cella corrispondente al mese scelto nella riga 1.

Se per esempio uso un cerca.orizz nella riga1 per avere il mese desiderato, non potrei tramite le funzioni INDIRIZZO/INDIRETTO recuperare la lettera della colonna da mettere in A24
di Marius44 data: 22/09/2017 15:14:46
Ciao Nico
Diciamo pure che per pigrizia non ho messo la Convalida con le lettere in maiuscolo (ma puoi farlo tu stesso mettendo nella convalida =B;C;D;E;F;G;H;I;J;K;L;M )

Se invece vuoi farlo mettendo in Convalida le date è possibile ma un po' contorto.
Una volta che hai messo le date nella convalida e ne selezioni una, la macro dovrebbe:
a) assumere la data selezionata (facile perchè è = A24)
b) cercare la colonna con la funzione CONFRONTA nell'intervallo A1:M1 (facile anche questo)
c) la risposta in b) però è un numero che bisogna trasformare in lettera per poterlo inserire nella costruzione della stringa relativa alla serie (si può fare).
Certo che si può fare, ma è molto più laborioso del mettere le lettere delle colonne in una Convalida.

Decidi tu.
Ciao,
Mario
di nicopana (utente non iscritto) data: 26/09/2017 15:49:35
ho inserito la convalida con la data (A26)

Ho anche trovato il modo di trasformare il numero della funzione CONFRONTA con la lettera di riferimento della colonna di appartenenza (A29)

Se trasferisco la formula nella cella dove devo inserire la lettera, quindi in A24, come mai secondo te dopo il primo Incolla, il grafico non si modifica più?

ti riallego il file in rev3
di Marius44 data: 26/09/2017 16:31:18
Ciao Nico
non volermene se non ti do la risposta immediata ma, così facendo, spero tu ti renda meglio conto di come procede la macro passo dopo passo.

1°) ogni volta che cambi un riferimento di cella devi "informare" Excel. Mi spiego: nell'evento Change del Foglio vi è questa prima riga di codice (tuo ultimo allegato):
If Not Intersect(Target, Range("A22,A24")) Is Nothing Then
quindi stai "dicendo" ad Excel di fare attenzione ai cambiamenti in due celle. Quali? Non mi sembra div edere il riferimento alla cella dove hai inserito la convalida. Dunque occorre cambiare A24 con A26. Ovviamente questa riga di codice
mese = Range("A24").Value deve diventare
mese = Range("A26").Value

2°) inserisci uno Stop dopo questa riga di codice:
valY = "GrafOfficina16_17!B" & riga & ":" & mese & riga
e lancia la macro. Appena si ferma allo Stop guarda il valore della variabile mese e dimmi se è una lettera!
Se vuoi farlo con le date il codice che "trasforma" il numero del risultato della funzione CONFRONTA in una lettera deve essere inserito nella riga al posto dello Stop

Se non è chiaro ci risentiamo.
Fai sapere. Ciao,
Mario
di NicoPana data: 05/10/2017 07:40:02
1) diciamo che non avevo avuto la necessità di cambiare il riferimento della cella perché la formula l'avevo comunque ricopiata nella tua cella originale A24. Cioè avevo pensato che è vero che mi sono costruito una convalida da qualche altra parte nel foglio, che l'utente usa visivamente per cambiare l'intervallo del grafico, ma volevo lasciare il funzionamento della tua macro, che andava appunto a leggere la lettera creata nella cella A24 che prima andavo ad imputare manualmente, ora, tramite la convalida+SOSTITUISCI+INDIRIZZO+CONFRONTA, comunque mi calcolava cambiando il mese dalla convalida

A questo punto metto la cella A29

2) che cosa intendi per "inserire uno Stop dopo questa riga di codice:"? Devo inserire proprio la dicitura Stop dopo la riga che mi hai selezionato? e lanciare la macro vuol dire premere sul tasto play?

Ti allego la rev 4, ma sembra non dare segni di vita.
di Marius44 data: 05/10/2017 07:57:00
Ciao
ho dato uno sguardo molto veloce all'allegato e a me sembra funzionare.
Stamane non ho tempo, cercherò di approfondire nel pomeriggio.

Ciao,
Mario
di NicoPana data: 05/10/2017 08:00:58
Ma ci mancherebbe, attendo tue.

Sembra funzionare nel senso che vedi il grafico modificarsi?

di Marius44 data: 05/10/2017 09:53:56
Ciao Nico
saltato un impegno, ho trovato il tempo per modificare la macro (che funzionava) con quella che riporto sotto.

Cosa ho modificato?
Le celle che fanno scattare l'azione sono la A22 e la A25 (guarda l'allegato che ho modificato)
Poi veniamo alla macro.
Innanzi tutto ho aggiunto la dichiarazione della variabile mese come Double (avrei dovuto dichiarare anche le altre ma sono pigro )
Quindi ho riferito la variabile mese alla cella A25 (come detto) ed ho aggiunto la variabile colonna per ottenere il numero della colonna fino alla quale vuoi fare apparire i dati.
A questo punto ho "trasformato" il numero restituito dalla Funzione MATCH (cioè CONFRONTA) con la lettera relativa alla colonna e lo ho assegnato alla variabile lettera (vedi che fantasia )
Nelle assegnazioni alle serie del Grafico ho sostituito "mese" con "lettera" e tutto (ritengo) va a meraviglia.

Fai delle prove e fai sapere. Ciao,
Mario

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mese As Double
    If Not Intersect(Target, Range("A22,A25")) Is Nothing Then
'cerca in Foglio1 il tipo di costo
        costo = Range("A22").Value
        riga = Application.WorksheetFunction.Match(costo, ActiveSheet.Range("A4:A18"), 0) + 3
'assume mese
        mese = Range("A25").Value
        colonna = Application.WorksheetFunction.Match(mese, ActiveSheet.Range("B1:M1"), 0) + 1
'trasforma numero colonna in lettera
'valido per lettere singole, da A a Z; non valido per lettere doppie AA, AB, ecc.
        lettera = Replace(Cells(1, colonna).Address(False, False), "1", "")
'assegna intervallo al grafico
        nomecosto = Range("A" & riga).Value
        valY = "GrafOfficina16_17!B" & riga & ":" & lettera & riga
        ActiveSheet.ChartObjects("Grafico 1").Activate
        ActiveChart.SeriesCollection(1).XValues = "GrafOfficina16_17!B1:M1"
        ActiveChart.SeriesCollection(1).Values = "GrafOfficina16_17!B19:" & lettera & 19
        ActiveChart.SeriesCollection(1).Name = Range("A19").Value
        ActiveChart.SeriesCollection(2).Values = valY
        ActiveChart.SeriesCollection(2).Name = nomecosto
    End If
End Sub

di nicopana (utente non iscritto) data: 06/10/2017 08:42:48
Grazie, sembra funzionare tutto perfettamente

Vuoi Approfondire?