Tabelle Flottanti - Trovarle su un foglio

Tabelle Flottanti - Trovarle su un foglio - dal 04/09/04 pagina vista: volte

Per Tabelle flottanti si intendono quelle tabelle che non hanno dei riferimenti fissi, ma possono essere spostate sul foglio di lavoro, sia per effetto di aree occupate da altri dati, che possono differenziarsi tra foglio e foglio, sia per aggiunta di righe o di colonne che modificano i riferimenti alla loro posizione.

In genere si tratta di tabelle di riferimento, in cui sono contenuti dati che serviranno ad altre formule o funzioni che attingeranno valori preimpostati per lo svolgimento di operazioni, dalla tabella. Potremmo assegnare un "nome" all'area occupata dalla tabella dal menù Inserisci/Nome/Definisci, e sarebbe Excel a mutare, aggiornandoli, i riferimenti alla tabella nel caso di aggiunte di righe o colonne al foglio, consentendo quindi a formule o funzioni che facciano uso di dati contenuti nella tabella, di trovarli comunque.

Ma mentre Excel aggiornerebbe automaticamente i riferimenti alla tabella nel caso di spostamenti, altrettanto non succede se stiamo lavorando con istruzioni in vba, (a meno che non si sia scelto un "nome" da richiamare nelle procedure). Comunque nascerebbero poi dei problemi ad adoperare il "nome" (che rappresenta tutto un'intervallo), come riferimento al primo argomento di una funzione come SCARTO, o altre funzioni che come argomenti richiedono celle e non intervalli di celle. Comunque sia, questo esercizio vuol essere solo un "altro" modo per affrontare il problema dell'identificazione tabella.

Condizione tipica (e necessaria) di queste tabelle, è che mantengano fisse le dimensioni della tabella (stesso numero di righe e stesso numero di colonne), anche se le tabelle si troveranno su posizioni (riferimenti) diverse tra un foglio e l'altro.

Vediamo un esempio di una possibile tabella: è una normale tabella di dimensioni prestabilite, dove abbiamo delle sigle inserite nelle righe, e delle colonne con intestazioni di campo, in questo esempio delle lettere alfabetiche, ma potrà essere qualunque identificativo di campo (servono solo come esempio). Normalmente da una tabella del genere, vorremo ricavare i valori che si trovano nella cella di intersezione tra una riga ed una colonna; se cercheremo in restituzione il valore tra la siglia Gen e la colonna col campo "c", otterremmo il valore 6.

Per questo tipo di ricerche, potremo usare Funzioni del foglio di lavoro, come SCARTO e CONFRONTA, ma condizione necessaria per le funzioni, è la definizione degli intervalli e delle celle in cui eseguire sia lo SCARTO sia il CONFRONTA; ma noi non sapremo quali sono queste celle e intervalli, non sapendo dove la tabella sarà posizionata, possiamo usere il vba per rintracciare l'indirizzo della prima cella che forma la tabella (quella bordata in rosso, sotto):

Queste tabelle vengono dette in genere "tabelle di conversione" oppure "tabelle di riferimento". In questa tabella è stata usata la prima cella (segnata in rosso) per inserire un nome (univoco) che serva per riconoscere la cella iniziale della tabella. E' questo nome che cercheremo, con un ciclo For Each..Next, per trovare la posizione della cella in un foglio di lavoro. E' solo attraverso questo "nome" che sapremo dove caspita si trova la tabella, prendendo l'Address di questa cella. I riferimenti alle celle finali li recuperemo conoscendo da quante righe e quante colonne è composta la tabella.

L'esercizio: vorremo in una qualsiasi cella di qualsiasi foglio di lavoro della cartella, la restituzione del valore di intersezione tra un'intestazione di riga e un'intestazione di colonna (della tabella). Per definire la cella dove ottenere il risultato, useremo la cella attiva che avremo selezionato prima di lanciare la macro (che vedremo sotto).

Se la tabella fosse in una posizione fissa, e si volesse il risultato usando funzioni del foglio di lavoro, basterebbe una formula del genere:

dove con E10 avremo la posizione della prima cella della tabella, con B1 abbiamo un valore da confrontare con i valori presenti nelle righe della prima colonna della tabella E11:E19, e con C1 abbiamo in valore da confrontare con le intestazioni di campo della tabella, poste in F10:H10. Una situazione illustrata nell'immagine sotto dove in B1 abbiamo il valore "Sic" da confrontare, in C1 il valore "b", e nella cella attiva E6 abbiamo la formula vista sopra con la restituzione del valore "-6" (valore che si trova in G15, che è la cella di intersezione tra Sic e b.

Le nostre istruzioni in vba, sfrutterano una formula come quella vista sopra, ma con una complicazione in più: dovremo indicare anche il numero del foglio dove si trova la tabella in cui confrontare i dati,

Per indicare quale valore di riga e di quale colonna della tabella chiediamo il valore di intersezione, useremo due celle del foglio di lavoro dove scriveremo di volta in volta i campi cercati; per rendere più completo l'esercizio, useremo un'altra cella, per scrivere il numero del foglio su cui cercare la tebella ed i valori in essa correlati. Usiamo quindi sul foglio 1, le celle:

Non conoscendo nè la posizione della tabella nè la quantità di celle che conterranno altri dati, conviene usare la proprietà UsedRange del foglio di lavoro, che restituisce tutto l'intervallo di celle utilizzate di un foglio, quindi in questo intervallo comprenderemo sicuramente anche la tabella. Il numero del foglio sul quale svolgere la ricerca lo reperiamo con la variabile "x", che sarà il numero che scriveremo nella cella A1 del Foglio1. Con la variabile "W" reperiamo l'indirizzo dell'ultima cella occupata nell'intervallo UsedRange, sfruttando il metodo SpecialCells che con la costante xlLastCell mira e restituisce con Address l'ultima cella dell'intervallo UsedRange.Cells - Questo ci serve per definire con la variabile "pippo" gli estremi dell'intervallo, sul Foglio "x", dove eseguire il ciclo che cercherà la cella contenente il "nome" che identifica la prima cella della tabella. Questa doppia riga di istruzioni l'ho inserita solo come esempio per indicare un'altro modo di reperire intervalli:

sarebbe infatti sufficiente questa sola istruzione per reperire l'intervallo in cui cercare:

Sub CercaTabella()
Dim W As String
Dim CD As Object
Dim G
x = [A1].Value
W = Sheets(x).UsedRange.Cells.SpecialCells(xlLastCell).Address
Set pippo = Sheets(x).Range("A1:" & W)
For Each CD In pippo 'per ogni cella nell'intervallo pippo
If CD.Value = "Tab" Then 'se la cella è uguale al nome assegnato alla prima cella della tabella
G = CD.Address 'prendiamo con G l'indirizzo della cella trovata, però abbiamo solo il riferimento alla 'cella, ma non abbiamo su quale foglio; usiamo quindi un'altra variabile "H", che "cucirà" insieme il numero 'del foglio (x) e la cella (G)
H = Sheets(x).Range(G).Address
Exit For ' e usciamo dal ciclo
End If
Next
'ora usiamo quattro variabili per reperire tramite Offset, gli indirizzi delle celle di inizio e fine righe della 'tabella (sappiamo che sono 9 oltre la riga iniziale) e di inizio e fine colonne (sono 3 oltre la colonna 'iniziale). Questi sono gli indirizzi che ci servono come argomenti nella funzione CONFRONTA (MATCH 'in inglese).
p = Range(H).Offset(1, 0).Address 'inizio righe tabella da assegnare a Confronta
q = Range(H).Offset(9, 0).Address 'fine righe tabella da assegnare a Confronta
c = Range(H).Offset(0, 1).Address 'inizio colonna tabella da assegnare a Confronta
f = Range(H).Offset(0, 3).Address 'fine colonna tabella da assegnare a Confronta

'ora impostiamo l'istruzione per inserire la formula (già vista sopra) che restituirà il risultato in una cella; io 'ho messo ActiveCell, ma potremo usare un riferimento ad una specifica cella. Attenzione alla sintassi della 'formula; ricordo che la formula viene vista dal vba come una stringa, quindi è necessario l'uso di doppi 'apici (") e dell'operatore di concatenazione ( & ) per definire gli argomenti delle funzioni.
ActiveCell.Formula = _
"=OFFSET(Foglio" & x & "!" & G & "," _
& "MATCH(B1,Foglio" & x & "!" & p & ":" & q & ",False)," _
& "MATCH(C1,Foglio" & x & "!" & c & ":" & f & ",False))"

End Sub

La formula della routine sarà poi vista nella cella scelta come destinazione, così (se avremo scelto il foglio1):

=SCARTO(Foglio1!$E$10;CONFRONTA(B1;Foglio1!$E$11:$E$19;FALSO);CONFRONTA(C1;Foglio1!$F$10:$H$10;FALSO))

=SCARTO(Foglio2!$C$4;CONFRONTA(B1;Foglio2!$C$5:$C$13;FALSO);CONFRONTA(C1;Foglio2!$D$4:$F$4;FALSO))

=SCARTO(Foglio3!$B$13;CONFRONTA(B1;Foglio3!$B$14:$B$22;FALSO);CONFRONTA(C1;Foglio3!$C$13:$E$13;FALSO))

e giusto per avere un'idea del posizionamento delle tabelle sui fogli e avere il riscontro con le formule, queste le immagini degli altri due fogli: