Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[excel] Macro per schematizzare i valori di una colonna

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

[excel] Macro per schematizzare i valori di una colonna

Postdi Ocimin » 21/01/07 16:21

Premetto che il mio problema è quello di riuscire ad ottimizzare la velocità del codice in quanto sul mac il VBA per alcune azioni è lentissimo. Quindi sono già riuscito a fare quello che chiedo ma speravo di trovare un metodo alternativo che contraesse i tempi di esecuzione.
Lo scopo che voglio raggiungere è il seguente. Ho un foglio di lavoro con una serie di nomi (uno per riga) e ad ogni nome una colonna associa una città di provenienza. In un altro foglio vorrei avere una colonna con tutte le città citate nel primo foglio e vicino il numero di volte che tale città ricorre. E fin qua non ci sono problemi. La difficoltà sta nel fatto che le città non sono statiche ma possono aggiungersene via via, quindi ad ogni lancio della macro devo controllare che nella lista di città presenti nel foglio 2 siano citate tutte quelle presenti nel foglio 1 ed eventualmente aggiunge le città nuove non ancora presenti. Ulteriore ostacolo il fatto che di colonne con il numero di ricorrenze nel foglio 2 ce ne siano una per settimana (una sorta di storico) e che quindi debbano mantenersi le associazioni tra le colonne per non scompaginare i riferimenti numero - città.
Il problema della lentezza è associato proprio al controllo delle città presenti nel foglio 1 e 2 in quanto il conteggio delle ricorrenze con la funzione conta.se è immediata. La macro lenta, anche se perfettamente funzionante, che ho fatto per ora fa così. Controlla che il contenuto di ogni singola cella della colonna città del foglio 1 sia presente anche nel 2. Se lo trova passa alla cella successiva altrimenti la aggiunge in fondo alla lista. E' un doppio ciclo che semplificato fa così:
Codice: Seleziona tutto
For cella_foglio1 = 1 to ultima_cella_foglio1
      contenuto_cella_f1 = Cells(cella_foglio1, 2).Value
      Sheets("Foglio2").select
      ultima_cella_foglio2 = Range("A65536").End(xlUp).Row
            For cella_foglio2 = 1 to ultima_cella_foglio2
                   contenuto_cella_f2 = Cells(cella_foglio2, 1).Value
                   if contenuto_cella_f2 = contenuto_cella_f1 Goto prossima
                   else
                   endif
            Next
            {scrive il contenuto della cella_foglio1 che non ha trovato nella lista di quelle già esistenti}
        prossima:
      Sheets("Foglio1").select
Next

Come potete vedere il ciclo controlla ogni cella presente nel foglio 1 anche se il suo contenuto è uguale ad altre già controllate (es. se la colonna riportasse 400 volte Città_A il ciclo controllerebbe comunque 400 volte se tale città è presente nel foglio 2).
Per ottimizzare un po' il tutto avevo pensato di affrontare la cosa in un altro modo ma mi mancano le basi teoriche per sapere se è possibile ed eventualmente come. La logica sarebbe: controllo il termine Città_A quante volte ricorre nella colonna del foglio 1. Per ogni ricorrenza inserisco il numero della cella doppia in una variabile tipo matrice (esiste?). Il controllo sulla cella successiva viene fatto solo se il suo numero non è presente in questa variabile matrice, altrimenti passa alla cella successiva e così via, continuando ad inserire nella stessa variabile matrice tutti i doppioni trovati nei controlli.
Se sapete aiutarmi su questa strada o avete suggerimenti differenti fatevi avanti.
Grazie in anticipo!

Ocimin
Ocimin
Utente Junior
 
Post: 15
Iscritto il: 05/11/06 16:55

Sponsor
 

Postdi Anthony47 » 21/01/07 19:13

Non voglio "destabilizzare" la tua soluzione, ma io userei una tabella pivot che, sulla base dei dati contenuti del foglio 1, calcoli quante occorrenze ci sono per ogni citta'.
Successivamente, elaborerei i dati della tabella pivot per generare un riepilogo di nomi citta' e relativo numero di occorrenze; il vantaggio di questa soluzione e' che la macro lavora su un elenco ridotto di citta' e il counter di occorrenza e' gia' pronto.

Spero che ti dia uno spunto utile; ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Ocimin » 21/01/07 21:47

Come dicevo sono ben accette anche soluzioni alternative!
Fino a pochi minuti fa pivot lo associavo ad un ruolo di gioco nella pallacanestro, quindi devo cercare un po' di informazioni a riguardo :-P
Appena avrò le idee un po' più chiara mi faccio risentire.
Grazie e ciao!

Ocimin
Ocimin
Utente Junior
 
Post: 15
Iscritto il: 05/11/06 16:55

Postdi Anthony47 » 22/01/07 00:32

Per la tabella pivot parti cosi':
-mi pare che la localita' sia in colonna 2 di foglio1; supponiamo che l' intestazione di colonna (cella B1) sia "Città"
-seleziona tutta la colonna B, e poi Menu/Dati/Rapporto tabella pivot; dai Avanti e ancora Avanti
-sullo step 3 di 3, scegli Layout
-ti compare una tabella vuota, con l' area Riga e l' area Colonna; a dx dovrebbe comparire un box "Città" (l' intestazione della nostra Colonna)
-trascina col mouse Città nell' area Riga; poi nel riquadro vuoto sotto Colonna; ti comparira' la scritta "Conteggio di Città" (se non compare "Conteggio di .." fai doppioclick sulla scritta e poi scegli Conteggio nel riquadro "Riepiloga per:", e poi conferma con Ok)
-conferma con Ok e tornerai nello step 3 di 3; conferma con Fine e la tabella sara' creata in un nuovo foglio di lavoro.
Per aggiornare il contenuto della tabella, seleziona sulla tabella la scritta Città, tasto destro, scegli Aggiorna. Puoi anche registrare una macro in cui selezioni Città e poi fai Aggiorna, e poi la associ a un pulsante.
Ultimo: le pivot tendono ad aumentare la dimensione del file, fai una prova per vedere l' effetto. Se questo da' fastidio, seleziona Città, tasto dx, scegli Opzioni tabella; nella finestra Opzioni togli la spunta su "Salva i dati con il layout tabella" e mettila su "Aggiorna all' apertura". MA SIA CHIARO che in questo modo quando apri il file la tabella viene aggiornata, e non so se questo e' compatibile con il modo di lavorare; forse e' meglio che questa economia di byte la facciamo alla fine quando tutte le fasi di lavoro sono chiare.

Ciao,
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Ocimin » 22/01/07 03:20

Niente male le tabelle pivot! Grazie per averle citate (e avermele quindi fatto conoscere :-)). Ho letto un po' in giro e ho fatto delle prove e sembrano veramente molto potenti.
Purtroppo mi ero dimenticato di una limitazione che non so se tali tabelle contemplano. Non devono essere conteggiate tutte le occorrenze per una determinata città e più precisamente devono essere escluse quelle colorate di rosso (la macro tra le altre cose colora di rosso le righe scadute). Con la macro che avevo fatto non era difficile escluderle, bastava mettere una condizione che controllasse l'oggetto Font.ColorIndex. Sai dirmi se con le pivot è possibile aggiungere una simile condizione? Da una prima occhiata non ho trovato nulla di utile.
Grazie ancora!

Ocimin
Ocimin
Utente Junior
 
Post: 15
Iscritto il: 05/11/06 16:55

Postdi Anthony47 » 23/01/07 02:48

Stavo dimenticando questo tema...

In effetti il tema si complica, comunque potresti valutare il seguente approccio:
-ti crei una funzione che ti restituisce il colore del font della cella:
Codice: Seleziona tutto
Function GFC(Cella As Range)   'Get Font Color
Application.Volatile
GFC = Cella.Font.ColorIndex
End Function

-Accanto alla citta' metti una colonna (es la C) che intitoli Valido e usi in C2 la formula
Codice: Seleziona tutto
=SE(GFC(B2)=3;0;1)
La funzione ti restituisce il colore del font; 3=Rosso; quindi se il font e' rosso la formula ti mette 0, altrimenti ti mette 1. Se il tuo colore e' diverso, usa =GFC(Indirizzo) per leggere il codice del colore che usi.
-cambi il layout della tabella pivot, scegliendo come range le colonne B:C, in riga metti "Città" e in colonna metti "Somma di Valido"; e' probabile che invece di Somma di.. ti compaia Conteggio di..; vedi la descrizione fatta ieri "sera" per come cambiare questa scelta.
-con la tua macro vai a colorare le date scadute; mi raccomando di non usare la formattazione condizionale! il cui colore non sarebbe leggibile da funzione (non semplicemente, almeno), a alla fine fai un "aggiorna pivot"

In questo modo compariranno in elenco anche le citta' con la data "scaduta", che pero' non saranno conteggiate (o saranno conteggiat solo per le date non scadute).

Non so se la cosa comincia a sembrarti troppo complessa, valuta tu.

Intanto potresti vedere come migliora la tua macro inserendo un
Codice: Seleziona tutto
Application.ScreenUpdating = False
subito all' inizio e
Codice: Seleziona tutto
Application.ScreenUpdating = True
appena prima dell' End Sub

Ciao,
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea

Postdi Ocimin » 04/02/07 16:30

Finalmente ho avuto tempo di metterci mano...
Prima di tutto grazie dei consigli. L'idea della funzione GFC non è male se non fosse che durante l'applicazione della macro, appena si attiva la funzione (ovvero al primo cambiamento che la macro produce nel foglio di lavoro) la macro stessa viene interrotta. E' normale? Ho risolto utilizzando una funzione condizionale che controlla un altro parametro....insomma ho tenuto buona l'idea ma ho cambiato metodo di attuazione :-P.
Per quanto riguarda l'utilizzo di Application.ScreenUpdating è stato un toccasana! In questo momento non ho la possibilità di fare un confronto con excel sotto windows comunque rispetto a prima ci mette la metà del tempo ad operare considerando anche che con questo nuovo blocco di istruzioni ho aggiunto un bel po' di cose. Grazie ancora!!

Ocimin
Ocimin
Utente Junior
 
Post: 15
Iscritto il: 05/11/06 16:55

Postdi Anthony47 » 04/02/07 23:32

Problema risolto, e fa piacere che a volte basta solo uno spunto per trovare la soluzione idonea.

A scopo didattico:
durante l'applicazione della macro, appena si attiva la funzione (ovvero al primo cambiamento che la macro produce nel foglio di lavoro) la macro stessa viene interrotta. E' normale?

No, non e' normale; ma cosa intendi per "viene interrotta"? ti va in debug, ti da' un errore tipo #VALUE o simili, o semplicemente non aggiorna il dato?
In questa ultima ipotesi, tieni presente che l' indice Font.ColorIndex si aggiorna solo quando c' e' un ricalcolo, e il solo cambiamento del colore non provoca ricalcolo.
Come pure la funzione non e' in grado di intercettare un colore applicato con formattazione condizionale.

Ciao,
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13892
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "[excel] Macro per schematizzare i valori di una colonna":


Chi c’è in linea

Visitano il forum: Nessuno e 20 ospiti