Matrici ed Intervalli

 

Come passare dei dati dal foglio in una matrice e viceversa senza ricorrere ad alcun ciclo ma con una semplice assegnazione.

Già in un precedente articolo abbiamo visto che a volte è conveniente lavorare con le matrici piuttosto che direttamente sui dati residenti sui fogli di lavoro soprattutto se i dati da elaborare sono abbastanza corposi.

Poniamo come esempio il problema che ci pone la tabella mostrata qui di seguito e cioè calcolare il totale di ogni articolo. Anche se, per per una tabella così esigua, può sembrare inutile lavorare con le matrici, cimentiamoci in questa impresa fingendo di trovarci di fronte ad una tabella contenente migliaia di articoli.

  E F G H I
8          
9   Articoli Q.tà Prezzo Totale
10   Chiodi 100  €      0,04  
11   Trapano 5  €    38,00  
12   avvitatore 5  €    28,20  
13   Chiodi 100  €      0,02  
14   Tasselli 100  €      0,25  
15   Mola 7  €    38,50  
16   Martello 15  €      3,04  
17   Viti 200  €      3,50  
           

 

Questo potrebbe essere uno dei metodi più elementare che ci viene in mente, ma anche più prolisso e più lento, per trasferire il contenuto di questa tabella in una matrice (nell'esempio la nostra tabella parte dalla cella "F9":

  1. dichiarare una matrice dinamica con
    Dim MiaMatrice()
  2. determinare la prima e l'ultima riga e colonna dell'intervallo che ci interessa calcolare (il riquadro che nella tabella è evidenziato in blue) con
    R1 = Range("F9").Row + 1 '=10
    C1 = Range("F9").Column + 1 '= 7
    R2 = Range("F9").End(xlDown).Row '=17
    C2 = Range("F9").End(xlToRight).Column '= 9
  3. dimensionare opportunamente la matrice con
    ReDim MiaMatrice(1 To R2 - R1 + 1, 1 To C2 - C1 + 1) '8 righe e 3 colonne
  4. quindi raccogliere i dati dal foglio e memorizzarli nella matrice con un doppio ciclo (uno per spazzolare le righe e l'altro le colonne)
    For R = 1 To R2 - R1 + 1
    For C = 1 To C2 - C1 + 1
    MiaMatrice(R, C) = Cells(R1 + R - 1, C1 + C - 1)
    Next
    Next
  5. eseguire i calcoli nella matrice (in questo caso vogliamo conoscere il valore complessivo di ogni singolo articolo e cioè la quantità di ogni articolo per il suo prezzo)
    For R = 1 To UBound(MiaMatrice, 1)
    MiaMatrice(R, 3) = MiaMatrice(R, 1) * MiaMatrice(R, 2)
    Next
  6. riportare i dati elaborati nel foglio con
    C = C2 - C1 + 1
    For R = 1 To R2 - R1 + 1
    Cells(R1 + R - 1, C1 + C - 1) = MiaMatrice(R, C)
    Next

 

Da notare:

  1. i contatori di questi due cicli vanno da 1 al numero massimo di righe e di colonne e puntano alla giusta locazione della matrice
    MiaMatrice(R, C)
    ma bisogna calcolarli per puntare alle giuste locazioni delle celle dell'intervallo in oggetto
    Cells(R1 + R - 1, C1 + C - 1)
  2. il numero di reiterazioni compiute in questo esempio è da calcolarsi con 8 * 3 = 24: immaginate invece di avere una tabella ben più vasta di questa che stiamo prendendo in esame a quante reiterazioni andiamo incontro

Quel che potete vedere cliccando qui potrebbe essere la routine completa ma non raccomandata per il caricamento della matrice e la successiva trascrizione della stessa sul foglio di lavoro:

 

 

Soluzione

Orbene c'è una soluzione per snellire e, soprattutto, velocizzare la procedura. Per caricare i dati nella matrice e poi, una volta elaborati in matrice, riversarli nel foglio di lavoro, c'è la possibilità di eseguire questa operazione in un sol colpo, senza ricorrere ad alcun ciclo, eseguendo una semplice assegnazione del tipo

Ma occorre rispettare queste due semplici regole:

Prima regola

Per raccogliere i dati di una tabella occorre dichiarare la variabile (matrice) non come matrice, ma come una normale variabile di tipo Variant (per ottenere questo è sufficiente non dichiarare affatto il tipo): Dim MiaMatrice, oppure, se proprio si vuol essere pignoli, con Dim MiaMatrice as Variant. Fatto questo è sufficiente un semplice: MiaMatrice = MioIntervallo; avremo così una matrice a base 1 (immagino che oramai è consolidato il fatto che quando diciamo che una matrice è a base 1 o a base 0 intendiamo dire che gli indici inferiori della matrice partono rispettivamente da 1 o da 0)

Dopo aver elaborato la matrice dovremo depositare i suoi dati nuovamente sul foglio. Infatti i dati contenuti nella matrice dopo l'elaborazione non serviranno a nessuno se non li mostrassimo. E qui subentra la

Seconda regola

Per copiare il contenuto di una matrice in un foglio di lavoro occorre che l'intervallo che dovrà ospitare i dati sia della stessa dimensione della matrice altrimenti potremo avere due effetti collaterali:

  • se l'intervallo è più piccolo della matrice i dati eccedenti andranno persi (ossia non vengono visualizzati sul foglio di lavoro pur continuando ad essere memorizzati nella matrice)
  • se l'intervallo è più grande della matrice nelle celle eccedenti vedremo tanti noiosissimi #N/D

 

Tabella uguale alla matrice   Tabella più piccola della matrice: alcuni dati sono persi   Tabella più grande della matrice
Articoli Q.tà Prezzo Totale   Articoli Q.tà Prezzo   Articoli Q.tà Prezzo Totale #N/D
 Chiodi  100 0,04 4   Chiodi 100 0,04   Chiodi 100 0,04 4 #N/D
 Trapano  5 38 190   Trapano 5 38   Trapano 5 38 190 #N/D
 avvitatore  5 28,2 141   avvitatore 5 28,2   avvitatore 5 28,2 141 #N/D
 Chiodi  100 0,02 2   Chiodi 100 0,02   Chiodi 100 0,02 2 #N/D
 Tasselli  100 0,25 25   Tasselli 100 0,25   Tasselli 100 0,25 25 #N/D
 Mola  7 38,5 269,5   Mola 7 38,5   Mola 7 38,5 269,5 #N/D
 Martello  15 3,04 45,6           Martello 15 3,04 45,6 #N/D
 Viti  200 3,5 700           Viti 200 3,5 700 #N/D
                  #N/D #N/D #N/D #N/D #N/D

 

In questo caso ci comportiamo in questa maniera:

  1. dichiariamo una variabile variant che dovrà raccogliere come matrice i dati del foglio:

    Dim MiaMatrice

  2. determiniamo le dimensioni dell'intervallo con:

    R1 = .Rows.Count
    C1 = .Columns.Count

  3. prendiamo in considerazione l'intervallo senza tener conto delle intestazioni di riga e di colonna con:

    Set IntervOrig = .Offset(1, 1).Resize(R1 - 1, C1 - 1)

  4. carichiamo la matrice con:

    MiaMatrice = IntervOrig

  5. elaboriamo la matrice

    For R = 1 To UBound(MiaMatrice, 1)
    MiaMatrice(R, 3) = MiaMatrice(R, 1) * MiaMatrice(R, 2)
    Next

  6. a questo punto per copiare gli elaborati nel foglio possiamo comportarci in due maniere:
    • copiamo solo l'ultima colonna della matrice usando un ciclo che spazzoli contemporaneamente l'ultima colonna del ciclo e l'ultima colonna dell'intervallo:

      For R = 1 To UBound(MiaMatrice, 1)
      Cells(R, 3) = MiaMatrice(R, 3)
      Next

    • oppure copiamo tutta la matrice nell'intervallo di origine:

      IntervDest = MiaMatrice

Questa che segue potrebbe essere una routine di esempio valida con due varianti nella fase finale:

Sub CalcolaTotArt1()
Dim R1, C1, R, MiaMatrice
Dim IntervOrig As Range
' definisco la dimensione dell'intervallo da trattare
With Range("A1").CurrentRegion
R1 = .Rows.Count
C1 = .Columns.Count
Set IntervOrig = .Offset(1, 1).Resize(R1 - 1, C1 - 1)
End With
' raccolgo i dati nella matrice
MiaMatrice = IntervOrig
' eseguo i calcoli
For R = 1 To UBound(MiaMatrice, 1)
MiaMatrice(R, 3) = MiaMatrice(R, 1) * MiaMatrice(R, 2)
Next
' rimetto i dati così elaborati sul foglio di lavoro
'For R = 1 To UBound(MiaMatrice, 1)
'Cells(R, 3) = MiaMatrice(R, 3)
'Next
' oppure
IntervOrig = MiaMatrice
End Sub

 

Per eseguire questo test:

  1. copiare i dati della prima tabella qui sopra e li incolliamo nel foglio di lavoro di Excel a partire dalla cella A1
  2. copiare la routine in un modulo standard
  3. mandarla in esecuzione

 

 

Conclusioni

Per ora ci fermiamo qui. La prossima volta vedremo come lavorare, usando gli stessi indici, sia con le matrici che con gli intervalli, ovunque essi siano posizionati nei fogli di lavoro.

Buon lavoro