MacroFacili: Confronto fra elenchi

Un esempio di utilizzo di una formula di matrice (sia per i valori di input sia per i risultati) applicato ad un problema che si incontra piuttosto spesso: il confronto fra due elenchi di nomi o di numeri.

Soluzione molto più semplice che non utilizzare funzioni come il cerca verticale su entrambi gli elenchi e che comunque darebbe problemi con i valori doppi.

La funzione confronta due elenchi e restituisce come risultato due colonne:

nella prima ci sono i valori che fanno parte del primo elenco ma non del secondo,
nella seconda i valori presenti nel secondo elenco ma non nel primo.

Nell'esempio a lato i valori diffrenti sono evidenziati in rosso, purtroppo questo non può essere fatto con una funzione per vincolo di Excel sulle funzioni: le modifiche ai formati possono essere fatti solo da procedure.

Una cosa importante da osservare è che scrivendo una funzione in VBA è molto più facile "controllare" i casi anomali come nell'esempio in cui ci sono valori doppi.

Volendo arrivare allo stesso risultato con normali funzioni (ad esempio il cerca verticale) dovremmo prestare attenzione a questi casi particolari, ad esempio inserendo una nuova colonna che indichi il numero di volte in cui un valore compare rendendo il foglio assai più complesso.

Altra cosa particolare è che la funzione è stata scritta in modo da essere utilizzata sia come formula di matrice, selezionando quindi l'intervallo di destinazione e confermando con Ctrl-Maiuscolo-Invio, sia come le formule di matrice dinamiche per chi utilizza Excel 365.

Function Mf_DifferenzeFraElenchi(Range1 As Range, Range2 As Range) As Variant
  Valori1 = Mf_Array_Da_Range(Range1)
  Valori2 = Mf_Array_Da_Range(Range2)

  'cancella tutti gli elementi corrispondenti
  For i = LBound(Valori1) To UBound(Valori1)
    For j = LBound(Valori2) To UBound(Valori2)
      If (Valori1(i) = Valori2(j)) Then
        Valori1(i) = Empty
        Valori2(j) = Empty
        Exit For
      End If
    Next j
  Next i

  'crea la matrice risultato
  Dim arrRes() As Variant
  ReDim arrRes(Application.Caller.Rows.Count - 1, Application.Caller.Columns.Count - 1)

  'imposta le celle della matrice risultato ad un testo vuoto
  For i = LBound(arrRes, 1) To UBound(arrRes, 1)
    For j = LBound(arrRes, 2) To UBound(arrRes, 2)
      arrRes(i, j) = ""
    Next j
  Next i

  ' riporta i dati non vuoti sulla matrice dei risultati
  R1 = 0
  For Each C1 In Valori1
   If (Not (IsEmpty(C1)) And R1 <= UBound(arrRes, 1)) Then
      arrRes(R1, 0) = C1
      R1 = R1 + 1
    End If
  Next C1

  If (UBound(arrRes, 2) >= 1) Then
    R2 = 0
    For Each C2 In Valori2
      If (Not (IsEmpty(C2)) And R2 <= UBound(arrRes, 1)) Then
        arrRes(R2, 1) = C2
        R2 = R2 + 1
      End If
    Next C2
  End If

  Mf_DifferenzeFraElenchi = arrRes
End Function

Volendo utilizzare la funzione anche in modo simile alle formule di matrice dinamiche la parte di creazione della matrice dei risultati dovrà essere modificata in questo modo. Nell'esempio scaricabile la trovate con questa modifica.

'crea la matrice risultato
  Dim arrRes() As Variant

  If (Application.Caller.Rows.Count = 1 And Application.Caller.Columns.Count = 1) Then
    'calcola la dimensione della matrice risultato,
    'nel caso non sia usata come formula di matrice
    NrRis1 = 0
    For Each V In Valori1
      If Not (IsEmpty(V)) Then NrRis1 = NrRis1 + 1
    Next V

    NrRis2 = 0
    For Each V In Valori2
      If Not (IsEmpty(V)) Then NrRis2 = NrRis2 + 1
    Next V

    NrRis = NrRis1
    If (NrRis2 > NrRis) Then NrRis = NrRis2

    ReDim arrRes(NrRis - 1, 1)
  Else
    'nel caso sia utilizzata come formula di matrice
    ReDim arrRes(Application.Caller.Rows.Count - 1, Application.Caller.Columns.Count - 1)
  End If

Una funzione che permette di convertire i valori di un range di celle (che sono sempre matrici bidimensionali) in un array ad una dimensione molto più facilmente utilizzabile in casi come questo.

Function Mf_Array_Da_Range(R As Range) As Variant
  Dim arrRes() As Variant
  ReDim arrRes(R.Rows.Count * R.Columns.Count - 1)

  i = 0
  For Each El In R.Value
    arrRes(i) = El
    i = i + 1
  Next El

  Mf_Array_Da_Range = arrRes
End Function

L'esempio completo é scaricabile da questo link: Es375.xlsm (a questo link le info per attivare le macro se risultano bloccate)

Gli esempi contenuti nel sito sono per uso personale, non é consentito l'uso professionale, commerciale o la riproduzione senza autorizzazione.

Per dubbi o domande potete scriverci a domande@macrofacili.it, sulla nostra pagina FB MacroFacili.it o sul gruppo FB Excel, macro e formule.