Confrontare dati tra due tabelle. - dal 04/09/04 pagina vista: volte

Ovvero: controllo e aggiornamento dati  su una tabella confrontandola con un'altra.

Condizione necessaria: lo schema tabellare tra le due tabelle dovrà essere identico: stesso numero di campi (colonne) e stessa sequenza del tipo di dati nei campi. Inoltre entrambe le tabelle dovranno contenere in una colonna, preferibilmente la prima a sinistra, degli ID univoci che servano come chiave di confronto; potremo usare, per ogni tabella,  sia numeri che testo, o combinazioni alfanumeriche (come ID), ma di cui, in una tabella, non esistano duplicati.

L'esercizio si presta per confronti di tabelle poste su fogli diversi nella stessa cartella, ma con poche modifiche può essere usato anche con tabelle su fogli di cartelle diverse.

Suponiamo di avere sul foglio1 di una cartella, una tabella con dati, che chiameremo "Tabella Origine" e di avere una tabella simile su un'altro foglio (foglio2), che chiameremo "Tabella 2", e dove eseguiremo nel tempo modifiche a valori esistenti nelle celle, oppure aggiunta di nuovi record (righe di dati). Chiaramente per le aggiunte, useremo un ID diverso da quelli già presenti nella stessa "Tabella 2"

Vogliamo confrontare le due tabelle, ed aggiornare la "Tabella Origine" con i valori eventualmente modificati e/o aggiunti nella "Tabella 2".

Lo potremo fare in due passaggi, eseguendo prima un doppio ciclo di confronto che legga gli ID sulla "Tabella Origine" e li confronti con gli stessi ID nella "Tabella 2" : per ogni cella della colonna iniziale della "Tabella Origine" (colonna con gli ID), controlleremo se esiste lo stesso ID nella "Tabella2", e se trovato, iniziamo un confronto tra le celle a lato, stessa riga, una per ogni campo; se troveremo una discordanza tra i valori delle stesse celle tra le due tabelle, aggiorneremo il valore solo nella "Tabella Origine" con quello diverso della "Tabella 2".

Il secondo passaggio, che dovrà controllare se esistono nuovi dati, dovrebbe essere svolto leggendo gli ID nella "Tabella 2" (che essendo quella più aggiornata potrà contenere nuovi dati)  invece lo faremo contando le righe occupate delle due Tabelle, e se il numero delle righe della "Tabella 2" sono maggiori (significa che sono stati aggiunti dati nuovi) copieremo questi dati, riga dopo riga, cercando nella "Tabella Origine" la prima riga libera nella colonna A, ed incollando ivi la nuova riga dati.

Vediamo le due tabelle: la prima immagine si riferisce alla "Tabella Origine" posta sul Foglio1, la seconda immagine riguarda la "Tabella 2" posta sul Foglio2, come si nota le colonne A di entrambe le tabelle portano dei valori identici (gli ID), e il posizionamento delle tabelle è lo stesso: si inizia da una stessa riga e da una stessa colonna, oltre a mantenere lo stesso numero di campi (colonne); nella "Tabella 2" appaiono evidenziate in giallo le celle che, per lo stesso ID della colonna A, portano valori diversi tra le due tabelle; in più nella "Tabella 2", sono stati aggiunti nuovi dati: infatti sono occupate tre righe in più rispetto all'altra tabella:

Tabella Origine Tabella 2

Queste le istruzioni, che inseriremo in un modulo standard e che saranno attivate da un pulsante posto sul Foglio1, associato alla macro:

Sub Confronto()
Application.ScreenUpdating = False
Dim CO, CD As Object

'sotto: si inizia con l'impostazione delle variabili che identificano gli intervalli su cui agirà il primo 'ciclo For Each (su Tabella Origine), utilizzando la Funzione End che troverà l'ultima cella 'occupata nella colonna A, partendo da A1.
Set zonavecchia = Sheets(1).Range(Cells(1, 1), Cells(1, 1).End(xlDown))

'poi ci spostiamo sul foglio 2 (tabella 2) ed impostiamo anche qui gli intervalli nella colonna A, 'assegnandoli alla variabile "zonanuova". Anche qui con End troviamo l'intero intervallo di celle 'occupate, poi ritorniamo sul foglio 1
Sheets(2).Activate
Set zonanuova = Sheets(2).Range([A1], [A1].End(xlDown))
Sheets(1).Activate
'quindi si iniziano i due cicli, leggendo per prima le celle della colonna A di Tabella 2, e per ogni 'cella, si cerca in ogni cella della colonna A di Tabella Origine, se il valore dell'una si trova 'nell'altra
For Each CD In zonanuova
For Each CO In zonavecchia
If CD.Value = CO.Value Then
' trovate le celle con valori uguali (stessi ID), si inizia un ciclo For 'Next che leggerà le celle della stessa riga dell ID trovato, spostandosi ad ogni ciclo di "n" colonne 'per ogni tabella; questo è il ciclo che controlla i valori delle stesse cella, stessa riga, cercando 'valori diversi: se il valore nella cella di Tabella 2 è diverso da quello di Tabella Origine (perchè 'aggiornato)
For n = 1 To 6
If CD.Offset(0, n) <> CO.Offset(0, n) Then
CO.Offset(0, n) = CD.Offset(0, n) 
'si rende Tabella Origine uguale a Tabella 2 (stessa cella)
End If
Next
End If
Next
Next
'------------le istruzioni appena viste si sono occupate di verificare le corrispondenze correlate ad 'ogni ID, aggiornando nella Tabella Origine i valori diversi, ora dobbiamo verificare se ci sono 'state aggiunte nella Tabella 2 che non sono presenti in Tabella Origine; per fare questo contiamo 'le righe occupate da dati nella colonna A delle due tabelle, assegnando a due variabili i numeri 'così ottenuti ( x e y )
x = zonavecchia.Rows.Count
y = zonanuova.Rows.Count
If y > x Then
 'se y (cioè il numero di righe in Tabella 2) è maggiore di x (Tab.Origine), allora:

'iniziamo un doppio ciclo For: il primo ciclo "girerà" il numero di volte rappresentato dalla 'differenza tra x (il minore dei numeri) e y (il maggiore dei numeri)
For m = x To y

'e per ogni ciclo "m", usiamo un altro ciclo che "girerà" sei volte (1 to 6) e renderà le celle del 'Foglio 1 (Tabella Origine, (l'istruzione è lanciata da questo foglio)), riga (m + 1), colonna "n", 'uguale al valore della stessa cella del Foglio2 (cioè di Tabella 2)
For n = 1 To 6
Cells(m + 1, n) = Sheets(2).Cells(m + 1, n)
Next n
Next m
End If

End Sub

Per coloro che con i Cicli hanno poca dimestichezza, riassumo il perchè usiamo m + 1 per ottenere il numero di riga nella sintassi Cells: se guardiamo le due tabelle nelle immagini sopra, possiamo contare le righe occupate da dati; in "Tabella Origine" queste righe sono 20 (valore assegnato ad x), mentre in "Tabella 2" sono 23 (valore assegnato ad y). Con la condizione If verifichiamo che 23 è maggiore di 20, quindi iniziamo un ciclo che partirà dalla riga 20 (x) e "girerà" fino alla riga 23. Poichè usiamo la variabile "m" , (che inizialmente è uguale a 20 (x)), per indicare nella sintassi Cells la riga a cui excel dovrà riferirsi per leggere i dati e copiarli da una tabella all'altra,  dobbiamo aggiungere 1 per spostarci sulla riga 21 (poi 22, poi 23). E questo sarà il risultato ottenuto: immagine sotto a destra, Tabella Origine aggiornata (in arancione i campi aggiornati):

Tabella Origine prima Tabella Origine dopo l'aggiornamento

Ovviamente la velocità di esecuzione della macro dipenderà oltre che dalla potenza del computer, dalla quantità (estensione) di dati presenti nelle due tabelle. Variando i riferimenti al foglio che contiene la tabella con i dati aggiornati, potremo puntare a qualunque foglio. Altra accortezza: le colonne che servono come contenitori degli ID non dovranno avere celle vuote altrimenti la funzione End non reperirà tutta l'effettiva area dati, con le ovvie conseguenze. In questo caso sarebbe necessario indicare intervalli precisi da assegnare alle "zone" .

Buon lavoro.

prelevato sul sito www.ennius.altervista.org