Confronto di formule su due fogli di lavoro (Microsoft Excel)
Rick ha due fogli di lavoro che deve confrontare tra loro per evidenziare le differenze. Il confronto non deve confrontare ciò che viene visualizzato, ma le formule in ciascuna delle celle. In questo modo Rick spera di scoprire dove differiscono le formule su ogni foglio di lavoro.
Esistono diversi modi per confrontare le formule. In alcune versioni di Excel hai accesso a un componente aggiuntivo di confronto che può gestire l’attività per te. Se utilizzi Office Professional Plus 2013 o Office 365 ProPlus, puoi utilizzare il componente aggiuntivo Confronta foglio di calcolo.
Le informazioni su questo componente aggiuntivo possono essere trovate qui:
https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986
Se utilizzi Office Professional Plus 2013 o Office 365, puoi anche utilizzare il componente aggiuntivo Spreadsheet Enquire. Le informazioni su questo componente aggiuntivo si trovano qui:
https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42
Riconoscendo che non tutti utilizzano una di queste versioni di Excel o potrebbero non voler utilizzare un componente aggiuntivo, ci sono altre cose che puoi fare. In Excel 2013 e nelle versioni successive è disponibile una pratica funzione del foglio di lavoro chiamata FORMULATEXT. Puoi usare questa funzione per recuperare la formula memorizzata in una cella, in questo modo:
=FORMULATEXT(A7)
Ciò restituisce la formula contenuta nella cella A7 (in questo caso). Se la cella non contiene una formula, restituisce un errore # N / D. È possibile utilizzare questo comportamento per creare in un “foglio di lavoro di confronto” un indicatore che indica se le formule sono uguali o meno. Basta creare il nuovo foglio di lavoro e inserirlo nella cella A1:
=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")
Copia la formula fino in fondo e fino a destra come desiderato. Contrassegna le differenze tra le celle corrispondenti su Sheet1 e Sheet2.
Ricorda che FORMULATEXT è stato introdotto in Excel 2013, quindi questo approccio non funzionerà nelle versioni precedenti di Excel. Se stai usando un’altra versione (o, anche, se stai usando Excel 2013) potresti usare una macro per contrassegnare le differenze tra i fogli di lavoro. Ci sono molti approcci macro che potresti usare; il seguente è un modo breve per fare il confronto.
Sub ComparaFormulas1()
Dim Check As Worksheet Dim Master As Worksheet Dim c As Range
Set Check = ActiveSheet Set Master = Worksheets("Master")
For Each c In Check.UsedRange If c.HasFormula Then If c.Formula <> Master.Range(c.Address).Formula Then c.Interior.Color = RGB(255, 0, 0)
End If End If Next c End Sub
Per utilizzare questa macro, visualizza la cartella di lavoro che desideri confrontare. Si presume che si desideri confrontare con le stesse celle in un foglio di lavoro denominato “Master”.
(Ovviamente puoi cambiarlo nella macro se il tuo foglio di lavoro “standard” ha un nome diverso.) Ogni cella del foglio di lavoro corrente viene confrontata con la cella corrispondente del foglio di lavoro “principale”. Se le celle contengono formule e tali formule sono diverse, il colore di sfondo della cella viene modificato in rosso nel foglio di lavoro corrente.
Un tale approccio ovviamente cambia la formattazione del foglio di lavoro confrontato. Se preferisci non modificare la formattazione, ma desideri semplicemente un elenco di celle con differenze, puoi utilizzare la seguente variazione sulla macro:
Sub ComparaFormulas2()
Dim Check As Worksheet Dim Master As Worksheet Dim c As Range Dim sTemp As String Dim lDif As Long
Set Check = ActiveSheet Set Master = Worksheets("Master")
sTemp = ""
lDif = 0
For Each c In Check.UsedRange If c.HasFormula Then If c.Formula <> Master.Range(c.Address).Formula Then lDif = lDif + 1 sTemp = sTemp & vbCrLf & lDif & ": " & c.Address End If End If Next c If lDif > 0 Then sTemp = "These were the differences" & vbCrLf & sTemp Else sTemp = "There were no differences"
End If MsgBox sTemp End Sub
È inoltre possibile creare una funzione definita dall’utente (UDF) che accetti intervalli per i confronti. In questo modo potresti usarlo in molti modi.
Function CompareFormulas3(rng1 As Range, rng2 As Range) Dim x As Long If rng1.Count <> rng2.Count Then 'Range sizes do not match CompareFormulas = CVErr(xlValue) Else CompareFormulas = True ' Assume all the same For x = 1 To rng1.Count If rng1(x).Formula <> rng2(x).Formula Then 'Formulas do not match CompareFormulas = False x = rng1.Count ' No need to keep comparing End If Next x End If End Function
Se vuoi solo confermare che un intervallo di celle in entrambi i fogli di lavoro ha formule identiche, puoi semplicemente usare qualcosa del tipo:
=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)
La funzione restituisce VERO se tutte le celle hanno formule identiche, FALSO se una delle celle ha formule diverse o errore #Valore se i 2 intervalli non hanno la stessa dimensione.
Se desideri evidenziare le differenze, puoi utilizzare l’UDF all’interno di una regola di formattazione condizionale. Supponendo che tu voglia applicare il formato condizionale alle celle in Sheet1, specifica semplicemente che la regola deve usare una formula e quindi usala come formula:
=NOT(CompareFormulas3(Sheet2!A1,A1))
Se una delle celle in Sheet1 non corrisponde alle celle corrispondenti in Sheet2, vengono formattate in base alla formattazione definita nella regola di formattazione condizionale.
_Nota: _
Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.
ExcelTips è la tua fonte di formazione economica su Microsoft Excel.
Questo suggerimento (13400) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.