Sommare valori di dati uguali. - pagina vista: volte

ovvero: ottenere il totale dei valori di ogni serie di dati uguali presenti in una tabella, ricreando una nuova tabella.

Requisiti: conoscenza dei cicli For..Next e While...Wend


Questo esercizio, in pratica un SOMMA.SE realizzato in vba, differisce dall'articolo sull'altro sito ("SommaSE in Vba") dove, disponendo di due tabelle, si scorrevano i nomi di una tabella (tabella da scorrere), cercando in un'altra tabella (tabella dove cercare) il nome letto in quel momento, sommando i valori che si trovato nella cella a lato del nome trovato.

Caratteristica tipica di quell'esempio era che nell'elenco da scorrere i "nomi" erano "univoci", cioè figuravano una sola volta.

Qui invece ci troviamo con una unica tabella, dove i dati che servono come il "criterio di ricerca" sono formati da più elementi posti in colonne di una stessa riga, e che si possono ripetere più volte . La tabella sotto ci aiuta a capire meglio: ogni "criterio di ricerca" è formato ad esempio, dall'elemento "tavolo", di colore "verde", col codice "FF", posizione di magazzino "A1", righe (evidenziate in verde); e così via per ogni riga, dove dovremo unire, concatenare ( & ) i quattro elementi che formano il "criterio di ricerca" per ricercarli all'interno della stessa tabella, in modo da poter ottenere il totale dei valori presenti nella stessa riga, colonna A (quantità). In pratica cercheremo tavolo+verde+FF+A1 come se fosse un'unica parola.

Il risultato di una simile ricerca dovrà restituire in un'altra tabella, che posizioneremo dove vorremo, una cosa del genere:

 ecc. ecc.

Come procediamo? Intanto, visto che il vba è CaseSensitive e non riconoscerebbe una stringa se scritta in maniera diversa per quanto riguarda maiuscole/minuscole (basta anche un solo carattere diverso per fare la differenza) inseriamo nella sezione "Generale - dichiarazioni" del modulo che ospita la nostra macro, l'istruzione Option Compare Text

poi creeremo quattro cicli: il primo ciclo For..Next che si occuperà di scorrere tutte le righe della tabella, leggendo concatenati i quattro elementi da ricercare, e per ogni riga letta, inseriamo un ciclo While..Wend che cercherà, a partire dalla riga successiva a quella letta dal ciclo esterno, corrispondenze al criterio di ricerca; nel caso trovi una corrispondenza, aggiungerà il valore corrispondente della colonna A ad un totalizzatore iniziato a zero, ed incrementerà un flag di una unità; all'uscita del ciclo interno while..wend, se il flag sarà maggiore del valore iniziale assegnatoli (vuol dire che almeno un altro valore è stato trovato) allora si inizia un terzo ciclo interno while.. wend che cercherà la prima riga vuota dove copiare (altra tabella) il totale ottenuto e i dati che formano il criterio di ricerca; anzichè usare un copia/incolla, uso un quarto ciclo For Next che scorrendo gli indici di colonna dei quattro elementi che formano il criterio, "depositi" in altrettante celle destinazione i relativi dati.

Sembra complicato, in effetti un pò lo è, ma vedremo riga per riga le istruzioni con i commenti, e poi la routine completa.

Importante: perchè i cicli NON rileggano più volte i dati simili già letti, useremo una colonna dove inserire un marcatore (un flag) esterno, io ho usato un asterisco (*), e lo faccio scrivere nella cella a lato di quelle lette, colonna F ;a inizio routine pulisco la colonna F per successivi rilanci. Ognuno potrà usare la colonna che vuole, basterà indicare il numero di colonna appropriato.

Le istruzioni passo passo:

• uro = [A65536].End(xlUp).Row  - è necessario sapere quanto è lungo l'elenco, quindi usiamo la variabile "uro" che restituisce l'ultima riga occupata, colonna A

• Range("F:F").ClearContents   - si pulisce tutta la colonna che avremo usato per inserire il flag asterisco (*), usato come indicatore di riga già letta dai cicli

• For N = 2 To uro   - iniziamo il primo ciclo esterno For Next  partendo dalla riga 2, dove il contatore "N" indicherà il numero di riga da leggere ad ogni ciclo

• If Cells(N, 6) <> "*" Then  - controlliamo subito se la cella riga N, colonna 6, (la colonna F, quella dove faremo scrivere l'asterisco che indica che la riga è già
                                            stata letta)- istruzione determinante: se la riga non è stata letta, quindi diversa da asterisco, si prosegue con le istruzioni sottostanti
                                            altrimenti si passa, con End If (che chiude questa condizione) all'istruzione Next, cioè alla riga N successiva.

• Cosa = Trim(Cells(N, 2) & Cells(N, 3) & Cells(N, 4) & Cells(N, 5))   -  usiamo la variabile "Cosa" per concatenare i dati che sono nella riga N, colonne B,C,D,E
                                            quindi "cosa" ad esempio, all'inizio sarà uguale a "tavoloverdeFFA1", cioè una stringa unica, ed usiamo Trim per eliminare eventuali
                                            spazi iniziali e finali che potrebbero falsare il riconoscimento su righe successive.

• Cells(N, 6) = "*"   -  visto che stiamo leggendo i dati nella riga N, impostiamo subito, stessa riga, nella colonna F (la n. 6) il marcatore asterisco (*).

• tot = Cells(N, 1)  -  impostiamo la variabile "tot" come totalizzatore dei valori letti nella colonna A, stessa riga, questi valori sono le "quantità" di cui ci interessa
                               ottenere appunto il totale dei valori simili alla variabile "cosa".

• riga = N + 1     - ora impostiamo la variabile "riga" al valore che ha il contatore "N" più 1; ci serve per definire il numero di riga dal quale far partire il primo ciclo
                           While...Wend; questo è il ciclo che scorrerà tutto l'elenco, partendo appunto dalla riga sotto a quella letta "N", per cercare stringhe uguali al
                           contenuto della variabile "cosa", e sommare i valori nella colonna A, "riga", alla variabile "tot".

• flag = 1           - la variabile "flag" , che impostiamo a 1 e che verrà incrementato di 1 se il ciclo While troverà stringhe uguali a "cosa", ci serve come marcatore
                          interno per determinare la condizione dell'azione da seguire (lo vediamo più avanti) alla fine del ciclo While..Wend.

• While Cells(riga, 2) <> ""    - iniziamo il primo ciclo While che "girerà" fino a che troverà celle, nella riga "riga", colonna B, diverse da vuoto. Per far "girare",
                                             cioè avanzare un ciclo While, si usa incrementare l'indice di riga di 1 unità prima di ogni Wend. Quando si verificherà una
                                             condizione diversa da quella impostata dal ciclo (quindi si troverà una cella vuota) il ciclo While termina automaticamente.

• If Cells(riga, 6) = "*" Then GoTo 10   -  iniziato il ciclo, si controlla subito che la riga non sia già stata letta, controllando la colonna 6 se contiene un asterisco, in
                                                            quel caso passiamo all'indicatore di "riga codice" 10, saltando le istruzioni sottostanti:

• Trova = Trim(Cells(riga, 2) & Cells(riga, 3) & Cells(riga, 4) & Cells(riga, 5))  - ora usiamo la variabile "Trova" come contenitore della stringa dei valori letti nelle
                                                           colonne B,C,D,E, ma nella riga "riga" letta nel ciclo While. (concatenati come per la var. "Cosa").

• If riga > uro Then GoTo 20   -   quindi controlliamo che il numero di "riga" non sia superiore all'ultima riga occupata, altrimenti saltiamo alla riga indice 20,
                                                saltando l'incremento di "riga" ed uscendo dal primo ciclo While.

• If Cosa = Trova Then     - ora si controlla se le due variabili "Cosa" e "Trova" sono uguali, se sono uguali....

• tot = tot + Cells(riga, 1)  - si incrementa il totalizzatore "tot" con il valore quantità letto, stessa riga di "Trova", colonna A

• Cells(riga, 6) = "*"       - si inserisce nella colonna F, stessa riga, il marcatore asterisco per segnalare la riga come già letta.

• flag = flag + 1       - si incrementa il valore della variabile "flag" di una unità.

• End If    -      fine della condizione If riga...

• 10:       - indice di riga codice

• riga = riga + 1   -  solo a questo punto del ciclo While incrementiamo di 1 il valore di "riga", in modo che con il sottostante Wend il ciclo legga la riga successiva.

• Wend       -  istruzione che serve a rinviare il ciclo al controllo While.

• 20:      -  indice di riga codice; quando siamo qui è perchè è terminato il ciclo While, e/o abbiamo superato il limite inferiore di tabella

• If flag >= 1 Then    - se siamo arrivati a questa istruzione è perchè è terminato il primo ciclo While..Wend, quindi sono state lette tutte le righe, e quindi se ci
                                 saranno stati "doppioni", in marcatore "flag" sarà maggiore di 1

Ci fermiamo un attimo: ciò che vorremo ottenere con la "condizione" "flag" dipende da ciò che vogliamo, mi spiego: nell'elenco che avremo scorso potranno esserci elementi riportati una sola volta, di cui non servirebbe quindi fare un "totale" in quanto unico valore "quantità"; se vorremo ricostruire una tabella con comunque tutti gli elementi letti, totali per i doppioni ma anche elementi singoli, dovremo impostare la condizione If flag >= 1, ciè se il marcatore "flag" è uguale o maggiore di 1, se invece vogliamo costruire una tabella con il riepilogo dei soli elementi presenti più volte, dovremo impostare la condizione If flag > 1, che escluderà quindi tutto ciò che è stato letto una sola volta. proseguiamo:

• ri = 1  -  visto che vorremo costruire una nuova tabella, dovremo cercarci una zona dove inserire i valori letti precedentemente; per questo cerchiamo una prima
               riga libera dove incollare i dati; nell'esempio seguito, uso la colonna 8, la H, come colonna di cui controllare le celle, e a partire dalla colonna G fino alla
               K ho predisposto le stesse intestazioni di colonna; con la variabile "ri" si imposta la riga iniziale che userà il secondo ciclo While..Wend che cercherà la
               prima cella libera.

• While Cells(ri, 8) <> ""    - fino a che la riga "ri", colonna 8, sarà diversa da vuoto (quindi conterrà valori)

• ri = ri + 1     - si incrementa il numero di riga "ri" di una unità

• Wend           - trovata la cella ri, 8 libera, termina il ciclo.

• Cells(ri, 7) = tot   - è nella cella trovata libera, colonna 7 (la G), che depositeremo il valore ottenuto da Tot, cioè il totale delle quantità lette per gli stessi elem.

• For vai = 2 To 5  - quindi usiamo un ciclo interno For Next che gira 4 volte e che ha il compito di leggere i valori che sono nelle colonne B,C,D,E,  riga "N" (non ci
                             scordiamo che siamo ancora alla riga "N" letta dal primo ciclo For..Next esterno), e dovremo depositare ogni singolo nome letto in altrettante
                             colonne della nuova tabella, riga "ri". Per questo aggiungiamo 6 ((tante sono le colonne di differenza tra la B e la H, tra la C e la I, ecc.).

• Cells(ri, vai + 6) = Cells(N, vai)  -   è il contatore "vai" del ciclo che rappresenta il numero di colonna nella sintassi Cells

• Next   - fine ciclo

• End If   - fine della condizione If Flag..

• tot = 0  - si imposta la variabile totalizzatore "tot" a zero per il successivo ciclo

• Trova = ""     - si imposta a vuota la variabile "Trova" per il successivo ciclo

• Cosa = ""      - si imposta a vuota la variabile "Cosa" per il successivo ciclo

• End If        - fine della prima condizione If Cells(N, 6) <> "*"....

• Next    - si passa alla riga "N" successiva. il ciclo scorrerà fino a raggiungere la riga "uro"

Considerazioni: sicuramente la routine è complessa, almeno per i neofiti del vba, ma credo che le spiegazioni fornite mettano in grado di capirne il funzionamento, e di poterla modificare per le proprie esigenze, specialmente nella creazione della seconda tabella, sarà sufficiente modificare gli indici di riga "ri" e/o di colonna in funzione dell'area che vorranno destinarle.

un'ultima cosa: se usate aggiornare con nuovi inserimenti la tabella di origine (Colonne A:E) e vorrete rilanciare le istruzioni, converrà valutare se azzerare, cancellare completamente i dati della seconda tabella per evitare accodamenti di dati; verrebbero infatti inseriti sotto i dati presenti.

la routine completa:

Conclusioni: l'utilizzo dei cicli While...Wend, ove possibile, anche se usati interni ad altri cicli, sono più veloci dei corrispondenti cicli For..Next usati per svolgere le stesse operazioni; da verifiche fatte su una tabella come quella sopra, lunga 500 righe,  la routine con i cicli while è risultata 3 volte più veloce rispetto ad una stessa routine realizzata con tutti cicli For..Next.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org