Totale in Tabelle univoche

Il problema che vorremmo risolvere in questa pagina è il seguente:

Ho una tabella con questi campi:

1 2 3 4 5 6 7
giorno cod bolla riferimento qta posa importo posa qta forn importo forn

Nel campo "cod bolla" ho dei codici duplicati che si riferiscono a degli articoli descritti nel terzo campo "riferimento".

Da questa tabella vorrei estrarre, in una nuova tabella, in modo univoco, non ripetuto, tutti i "cod bolla" e in questa tabella vorrei vedere la somma dei valori descritti nei campi: qta posa, importo posa, qta forn, importo forn

ottenendo questa tabella di riepilogo:

1 2 3 4 5
cod bolla qta posa importo posa qta forn importo forn

 

Soluzione

Primo passo: creazione della tabella univoca

Quando parlo di Tabelle univoche o elenchi univoci, tra i vari metodi che esistono in giro e dopo varie esperienze acquisite usando varie tecniche, sono solito orientarmi alla loro creazione facendo uso dell'oggetto VBA Collection già esaminato nel precedente articoloesempio e che mi permette di raggiungere facilmente lo scopo.

 

In questa particolare situazione mi comporto in questo modo.

Definisco i due intervalli: quello di origine e quello di destinazione usando le variabili: IntervOrig e IntervDest:

il primo si riferisce alla tabella di origine, che nell'esempio si trova a partire dalla cella A1, viene individuato con:

With Range("A1").CurrentRegion
Set IntervOrig = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With

il secondo, tenendo conto che la tabella di destinazione la voglio a partire dalla cella A30 e che si estende per 5 colonne, viene individuato con:

Set IntervDest = Range("A30:E30").Offset(1, 0)

Compiute queste operazioni preliminari, posso partire con la creazione del mio elenco univoco riferendomi al campo della seconda colonna della tabella di origine (quella del "cod bolla"). Perciò creo la mia Collection basandomi su questo campo:

On Error Resume Next
For Riga = 1 To IntervOrig.Rows.Count
Elenco.Add IntervOrig(Riga, 2).Value, CStr(IntervOrig(Riga, 2).Value)
Next
On Error GoTo 0

Fin qui siamo nella situazione descritta nel precedente articolo esempio.

 

Secondo passo: elaborazionne e somma delle voci che si riferiscono al campo "cod bolla"

Abbiamo detto all'inizio che la tabella univoca viene creata basandosi sul campo "cod bolla" che non è univoco e vogliamo creare la somma dei campi: qta posa, importo posa, qta forn, importo forn

L'operazione non è complessa e può essere così descritta:

Le istruzioni si possono così riassumere:

For Riga = 1 To Elenco.Count
     Codice = Elenco(Riga)
     IntervDest(Riga, 1) = Codice
     Tot1 = 0: Tot2 = 0: Tot3 = 0: Tot4 = 0
     For R1 = 1 To IntervOrig.Rows.Count
          If IntervOrig(R1, 2) = Codice Then
               Tot1 = Tot1 + IntervOrig(R1, 5)
               Tot2 = Tot2 + IntervOrig(R1, 6)
               Tot3 = Tot3 + IntervOrig(R1, 7)
               Tot4 = Tot4 + IntervOrig(R1, 8)
          End If
     Next
     IntervDest(Riga, 2) = Tot1
     IntervDest(Riga, 3) = Tot2
     IntervDest(Riga, 4) = Tot3
     IntervDest(Riga, 5) = Tot4
Next

Nella routine che segue si dovranno eseguire delle piccole modifiche che suggrisco qui di seguito:

With Range("A1").CurrentRegion è la locazione da dove inizia la tabella di origine. Se la Tabella di origine parte da un'altra locazione occorre cambiare il riferimento alla cella A1

Elenco.Add IntervOrig(Riga, 2).Value, CStr(IntervOrig(Riga, 2).Value) il numero 2 si riferisce alla seconda colonna che si vuole filtrare (la colonna dove ci sono i Cod da filtrare). Se la colonna che si vuole filtrare è un'altra occorre cambiare il numero 2 con uno appropriato

Set IntervDest = Range("A30:E30").Offset(1, 0) è dove va copiata la tabella univoca con questi campi già scritti nell'intervallo indicato: cod / qta posa / importo posa / qta forn / importo forn. Se la tabella deve essere posta in un altro posto diverso da "A30:E30", occorre cambiare questo riferimento

Tot1 = 0: Tot2 = 0: Tot3 = 0: Tot4 = 0 che sono le variabili che uso come totalizzatori per i 4 totali che debbo calcolare per qta posa / importo posa / qta forn / importo forn. Se i totali da calcolare sono neno o di più di 4 occorre diminuire o aumentare i vari TotX

Tot1 = Tot1 + IntervOrig(R1, 5), Tot1 = Tot1 + IntervOrig(R1, 6), ecc.: i numeri 5, 6, 7, 8 si riferiscono alle colonne dove stanno i valori da sommare. Se questi valori stanno in un'altra colonna occorre cambiare questi riferimenti.

 

Detto questo posso mostrare il codice che ho usato.

 

Ma attenzione: ripeto

Prima di mandare in esecuzione la routine occorre fare molta attenzione ai riferimenti alle celle in questa istruzione ed eventualmente aggiustarne i riferimenti:

Set IntervDest = Range("A30:E30").Offset(1, 0)

Se la Tabella di origine va oltre la trentesima riga, quando si andrà a scrivere nella Tabella di destinazione i nuovi dati andranno a sovrascrivere i contenuti della tabella di origine

 

Sub copia_senza_ripetizione()
'routine del sito
Dim Elenco As New Collection
Dim IntervOrig As Range
Dim IntervDest As Range
Dim Riga, R1, Codice, Tot1, Tot2, Tot3, Tot4
' Worksheets("Foglio1").Select
' definisco l'intervallo di origine
With Range("A1").CurrentRegion
     Set IntervOrig = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
' creazione della collection dei dati univoci
' nella colonna 2 stanno i dati che si vogliono rendere univoci
On Error Resume Next
     For Riga = 1 To IntervOrig.Rows.Count
          Elenco.Add IntervOrig(Riga, 2).Value, CStr(IntervOrig(Riga, 2).Value)
     Next
On Error GoTo 0
' definisco l'intervallo di destinazione che è subito sotto le intestazioni di colonna
' ATTENZIONE A QUESTO
Set IntervDest = Range("A30:E30").Offset(1, 0)
' debbo sommare i dati relativi a
' qta posa, importo posa, qta forn, importo forn,
' che nella tabella originale stanno nelle colonne 5, 6, 7, 8
' e che nella tabella di destinazione sono nelle colonne 2, 3, 4, 5
' in questa fase le variabili sono così usate:
' la variabile Riga viene usata per puntare sia alle <righe> della Collection che alle <righe della
' tabella di destinazione che, come estensione è uguale alla Collection
' la variabile R1 punta alle <righe> della tabella di origine

For Riga = 1 To Elenco.Count
     Codice = Elenco(Riga)
     IntervDest(Riga, 1) = Codice
     Tot1 = 0: Tot2 = 0: Tot3 = 0: Tot4 = 0
     For R1 = 1 To IntervOrig.Rows.Count
          If IntervOrig(R1, 2) = Codice Then
               Tot1 = Tot1 + IntervOrig(R1, 5)
               Tot2 = Tot2 + IntervOrig(R1, 6)
               Tot3 = Tot3 + IntervOrig(R1, 7)
               Tot4 = Tot4 + IntervOrig(R1, 8)
          End If
     Next
     IntervDest(Riga, 2) = Tot1
     IntervDest(Riga, 3) = Tot2
     IntervDest(Riga, 4) = Tot3
     IntervDest(Riga, 5) = Tot4
Next
End Sub

Utilizzando ed adattando questa routine alle nostre esigenze potremo creare facilmente tabelle univoche ed operare su di esse qualsiasi elaborazione che noi riteniamo più opportune.

 

Per una più facile consultazione allego il file che ho usato per questo lavoro: file da scaricare