Come confrontare due intere righe in un foglio

Sono nuovo di VBA. Ho un lavoro in mano per migliorare le prestazioni del codice VBA. Per migliorare le prestazioni del codice, devo leggere l’intera riga e confrontarla con un’altra riga. C’è un modo per farlo in VBA?

pseudocodice:

sheet1_row1=read row1 from sheet1 sheet2_row1=read row1 from sheet2 if sheet1_row1 = sheet2_row1 then print "Row contains same value" else print "Row contains diff value" end if 

 Sub checkit() Dim a As Application Set a = Application MsgBox Join(a.Transpose(a.Transpose(ActiveSheet.Rows(1).Value)), Chr(0)) = _ Join(a.Transpose(a.Transpose(ActiveSheet.Rows(2).Value)), Chr(0)) End Sub 

Cosa sta succedendo:

  • a è semplicemente una scorciatoia per l’ Application per mantenere il codice sotto più facile da leggere
  • ActiveSheet.Rows(1).Value restituisce una matrice 2-D con dimensioni (da 1 a 1, da 1 a {numero di colonne in un foglio di lavoro})
  • Vorremmo condensare la matrice sopra in un singolo valore usando Join() , quindi possiamo confrontarla con una matrice diversa dalla seconda riga. Tuttavia, Join () funziona solo su array 1-D, quindi eseguiamo l’array due volte tramite Application.Transpose() . Nota: se si confrontassero colonne anziché righe, sarà necessario un solo passaggio attraverso Transpose ().
  • L’applicazione di Join() all’array ci fornisce una stringa singola in cui i valori della cella originale sono separati da un “carattere nullo” ( Chr(0) ): selezioniamo questo poiché è improbabile che sia presente in nessuno dei valori della cella stessi.
  • Dopo questo, ora abbiamo due stringhe regolari che possono essere facilmente confrontate

Nota: come indicato da Reafidy nei commenti, Transpose() non può gestire array con più di ca. 65.000 elementi, quindi non è ansible utilizzare questo approccio per confrontare due colonne intere nelle versioni di Excel in cui i fogli hanno più di questo numero di righe (vale a dire qualsiasi versione non antica).

Nota 2: questo metodo ha prestazioni piuttosto scadenti rispetto a un ciclo utilizzato su una matrice variante di dati letti dal foglio di lavoro. Se hai intenzione di fare un confronto riga per riga su un numero elevato di righe, l’approccio sopra sarà molto più lento.

Per il tuo esempio specifico, qui ci sono due modi …

Case Insensitive:

 MsgBox [and(1:1=2:2)] 

Che tiene conto del maiuscolo o minuscolo:

 MsgBox [and(exact(1:1,2:2))] 

Di seguito sono riportate le funzioni generalizzate per confrontare due intervalli contigui.

Case Insensitive:

 Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean RangesEqual = Evaluate("and(" & r1.Address & "=" & r2.Address & ")") End Function 

Che tiene conto del maiuscolo o minuscolo:

 Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean RangesEqual = Evaluate("and(exact(" & r1.Address & "," & r2.Address & "))") End Function 

OK, questo dovrebbe essere abbastanza veloce: interazione minima tra l’interfaccia utente di Excel e VBA (che è dove vive gran parte della lentezza). Si assume che i fogli di lavoro abbiano layout simili da $A$1 e che cercheremo solo di abbinare l’area comune di UsedRange per i due fogli:

 Public Sub CompareSheets(wks1 As Worksheet, wks2 As Worksheet) Dim rowsToCompare As Long, colsToCompare As Long rowsToCompare = CheckCount(wks1.UsedRange.Rows.Count, wks2.UsedRange.Rows.Count, "Row") colsToCompare = CheckCount(wks1.UsedRange.Columns.Count, wks2.UsedRange.Columns.Count, "Column") CompareRows wks1, wks2, rowsToCompare, colsToCompare End Sub Private Function CheckCount(count1 As Long, count2 As Long, which As String) As Long If count1 <> count2 Then Debug.Print "UsedRange " & which & " counts differ: " _ & count1 & " <> " & count2 End If CheckCount = count2 If count1 < count2 Then CheckCount = count1 End If End Function Private Sub CompareRows(wks1 As Worksheet, wks2 As Worksheet, rowCount As Long, colCount As Long) Debug.Print "Comparing first " & rowCount & " rows & " & colCount & " columns..." Dim arr1, arr2 arr1 = wks1.Cells(1, 1).Resize(rowCount, colCount).Value arr2 = wks2.Cells(1, 1).Resize(rowCount, colCount).Value Dim rIdx As Long, cIdx As Long For rIdx = LBound(arr1, 1) To UBound(arr1, 1) For cIdx = LBound(arr1, 2) To UBound(arr1, 2) If arr1(rIdx, cIdx) <> arr2(rIdx, cIdx) Then Debug.Print "(" & rIdx & "," & cIdx & "): " & arr1(rIdx, cIdx) & " <> " & arr2(rIdx, cIdx) End If Next Next End Sub 
 Match = True Row1length = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column Row2length = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column If Row1length <> Row2length Then 'Not equal Match = False Else For i = 1 To Row1length If Worksheets("Sheet1").Cells(1, i),Value <> Worksheets("Sheet2").Cells(1, i) Then Match = False Exit For End If Next End If If Match = True Then Debug.Print "match" Else Debug.Print "not match" End If 

Ecco un po ‘di codice che farà due gamme vettoriali. Puoi eseguirlo su due righe, due colonne.

Non pensare che sia veloce come il metodo della trasposizione x2, ma è più flessibile. L’invocazione della colonna richiede un po ‘più tempo poiché ci sono 1M articoli da confrontare!

 Option Explicit Public Sub Test() 'Check two columns Debug.Print DataAreasAreSame(Columns("a"), Columns("b")) 'Check two rows Debug.Print DataAreasAreSame(Rows(1), Rows(2)) End Sub Public Function DataAreasAreSame(ByVal DataArea1 As Range, ByVal DataArea2 As Range) As Boolean Dim sFormula As String sFormula = "=SUM(If(EXACT(" & DataArea1.Address & "," & DataArea2.Address & ")=TRUE,0,1))" If Application.Evaluate(sFormula) = 0 Then DataAreasAreSame = True End Function 

= Formula ESATTA (B2; D2) e trascina verso il basso, opzione migliore per me.

Metterò qui una risposta da mazza a pietra, per completezza, perché la domanda “Queste due gamme sono identiche?” sta diventando una componente non esaminata di tutte le domande “confronta le mie gamme e poi fai queste cose complicate …” .

La tua domanda è una semplice domanda sulle piccole gamme. La mia risposta è per quelli grandi; ma la domanda è buona, e un buon posto per una risposta più generale, perché è semplice e chiara: e ‘questi intervalli differiscono?’ e “Qualcuno ha manomesso i miei dati?” sono rilevanti per la maggior parte degli utenti commerciali di Excel.

La maggior parte delle risposte alle tipiche domande “Confronta le mie righe” sono letture di celle per cellula e confronti in VBA. La semplicità di queste risposte è encomiabile, ma questo approccio si svolge molto lentamente su un ampio set di dati perché:

  1. Leggere un intervallo di una cella alla volta è molto lento;
  2. Il confronto dei valori coppia per coppia è inefficiente, soprattutto per le stringhe, quando il numero di valori raggiunge le decine di migliaia,

Il punto (1) è importante: richiede a VBA la stessa quantità di tempo per prelevare una singola cella usando var = Range("A1") come fa per raccogliere l’intero intervallo in una volta sola usando var = Range("A1:Z1024")

… E ogni interazione con il foglio richiede quattro volte più tempo di un confronto di stringhe in VBA e venti volte più lungo di un confronto tra decimali a virgola mobile; e questo, a sua volta, è tre volte più lungo di un confronto intero.

Quindi il tuo codice sarà probabilmente quattro volte più veloce, e probabilmente cento volte più veloce, se leggi l’intera gamma in un Range.Value2 e lavori sull’array Range.Value2 in VBA.

Questo è in Office 2010 e 2013 (li ho testati); per la versione precedente di Excel, vedrai tempi tra 1/50 e 1/500 di secondo, per ogni interazione VBA con una cella o un intervallo di celle. Sarà molto più lento perché, in entrambe le vecchie e nuove versioni di Excel, le azioni VBA saranno ancora in numeri a una cifra di microsecondi: il tuo codice verrà eseguito almeno cento volte più velocemente, e probabilmente migliaia di volte più velocemente, se si evitano letture cellula per cella dal foglio nelle versioni precedenti di Excel.

 arr1 = Range1.Values arr2 = Range2.Values 
' Consider checking that the two ranges are the same size ' And definitely check that they aren't single-cell ranges, ' which return a scalar variable, not an array, from .Value2
' WARNING: THIS CODE WILL FAIL IF YOUR RANGE CONTAINS AN ERROR VALUE
For i = LBound(arr1, 1) To Ubound(arr1, 2)
For j = LBound(arr1, 2) To Ubound(arr1, 2)
If arr1(i, j) <> arr2(i, j) Then bMatchFail = True Exit For End If
Next j
If bMatchFail Then Exit For
Next i
Erase arr1 Erase arr2

Noterai che questo esempio di codice è generico, per due intervalli della stessa dimensione presi da qualsiasi luogo, anche da cartelle di lavoro separate. Se stai confrontando due colonne adiacenti, caricando un singolo array di due colonne e confrontando IF arrX(i, 1) <> arrX(i,2) Then dimezzerai il runtime.

La tua prossima sfida è rilevante solo se raccogli decine di migliaia di valori da ampi intervalli: non c’è alcun guadagno in termini di prestazioni in questa risposta estesa per qualcosa di più piccolo.

Quello che stiamo facendo è:

Utilizzando una funzione di hash per confrontare i valori di due grandi intervalli

L’idea è molto semplice, anche se la matematica di base è piuttosto impegnativa per i non matematici: piuttosto che confrontare un valore alla volta, eseguiamo una funzione matematica che “blocca” i valori in un identificatore breve per un facile confronto.

Se si confrontano ripetutamente intervalli con una copia di “riferimento”, è ansible memorizzare l’hash “di riferimento” e questo dimezza il carico di lavoro.

Ci sono alcune funzioni di hashing veloci e affidabili là fuori, e sono disponibili in Windows come parte dell’API di sicurezza e crittografia. C’è un piccolo problema nel fatto che corrono su stringhe, e abbiamo una matrice su cui lavorare; ma è ansible trovare facilmente una funzione rapida ‘Join2D’ che ottiene una stringa dagli array 2D restituiti dalla proprietà .Value2 un intervallo.

Quindi una funzione di confronto veloce per due grandi intervalli sarà simile a questa:

 Funzione pubblica RangeCompare (Intervallo1 come Excel.Range, Intervallo2 Come Excel.Range) AS Booleano
 'Restituisce VERO se gli intervalli sono identici.
 'Questa funzione è sensibile al maiuscolo / minuscolo.
 "Per intervalli con meno di ~ 1000 celle, il confronto cella per cella è più veloce 
'ATTENZIONE: questa funzione fallirà se il tuo intervallo contiene valori di errore.
RangeCompare = False
Se Range1.Cells.Count <> Range2.Cells.Count Then RangeCompare = False ElseIf Range1.Cells.Count = 1 quindi RangeCompare = Range1.Value2 = Range2.Value2 Altro RangeCompare = MD5 (Join2D (Range1.Value2)) = MD5 (Join2D (Range2.Value2)) Finisci se
Fine Funzione

Ho avvolto il sistema Windows System.Security MD5 in questa funzione VBA:

 Funzione pubblica MD5 (arrBytes () As Byte) As String
 'Restituisce un hash MD5 per qualsiasi stringa 
'Autore: Nigel Heffernan Excellerando.Blogspot.com
'Nota il tipo pun: puoi passare una stringa, non c'è conversione di tipo o cast 'perché una stringa viene archiviata come matrice Byte e VBA lo riconosce.
oMD5 As Object 'Imposta un riferimento a mscorlib 4.0 per utilizzare l'associazione anticipata

Dim HashBytes () As Byte Dim i As Integer

Imposta oMD5 = CreateObject ("System.Security.Cryptography.MD5CryptoServiceProvider") HashBytes = oMD5.ComputeHash_2 ((arrBytes))
Per i = LBound (HashBytes) To UBound (HashBytes) MD5 = MD5 & Right ("00" & Hex (HashBytes (i)), 2) Avanti

Imposta oMD5 = Niente 'se lo fai ripetutamente, dichiari a livello di modulo e persistono Cancella HashBytes

Fine Funzione

Ci sono altre implementazioni VBA là fuori, ma nessuno sembra sapere del tipo Byte Array / String – non sono equivalenti , sono identici – quindi tutti codificano conversioni di tipo non necessarie.

Una rapida e semplice funzione di Join2D è stata pubblicata da Dick Kusleika su Daily Dose di Excel nel 2015:

 Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String Dim i As Long, j As Long Dim aReturn() As String Dim aLine() As String ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1)) ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2)) For i = LBound(vArray, 1) To UBound(vArray, 1) For j = LBound(vArray, 2) To UBound(vArray, 2) 'Put the current line into a 1d array aLine(j) = vArray(i, j) Next j 'Join the current line into a 1d array aReturn(i) = Join(aLine, sWordDelim) Next i Join2D = Join(aReturn, sLineDelim) End Function 

Se hai bisogno di asportare le righe vuote prima di fare il confronto, avrai bisogno della funzione Join2D che ho postato su StackOverflow nel 2012 .

L’applicazione più comune di questo tipo di confronto di hash è per il controllo del foglio di calcolo – monitoraggio delle modifiche – e verrà visualizzato Range1.Formula utilizzato anziché Range1.Value2 : ma la tua domanda riguarda il confronto di valori, non di formule.

Nota: ho pubblicato una risposta molto simile altrove. Avrei postato qui prima se avessi visto questa domanda prima.

Excel 2016 ha una funzione TEXTJOIN denominata TEXTJOIN

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Guardando @Tim Williams rispondi e usa questa nuova funzione (che non ha il limite di 65536 righe):

 Sub checkit() MsgBox WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(1).Value) = _ WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(2).Value) End Sub 

Scritto come una funzione:

 Public Function CheckRangeValsEqual(ByVal r1 As Range, ByVal r2 As Range, Optional ByVal strJoinOn As String = vbNullString) As Boolean CheckRangeValsEqual = WorksheetFunction.TextJoin(strJoinOn, False, r1.Value) = _ WorksheetFunction.TextJoin(strJoinOn, False, r2.Value) End Function