Rick tiene dos hojas de trabajo que necesita comparar entre sí para resaltar las diferencias. La comparación no debe comparar lo que se muestra, sino las fórmulas en cada una de las celdas. De esta manera, Rick espera descubrir dónde difieren las fórmulas en cada hoja de trabajo.

Hay varias formas de comparar fórmulas. En ciertas versiones de Excel, tiene acceso a un complemento de comparación que puede manejar la tarea por usted. Si usa Office Professional Plus 2013 u Office 365 ProPlus, puede usar el complemento de comparación de hojas de cálculo.

Puede encontrar información sobre este complemento aquí:

https://support.office.com/en-us/article/Overview-of-Spreadsheet-Compare-13fafa61-62aa-451b-8674-242ce5f2c986

Si está usando Office Professional Plus 2013 u Office 365, también puede usar el complemento Consulta de hoja de cálculo. La información sobre este complemento se encuentra aquí:

https://support.office.com/en-nz/article/What-you-can-do-with-Spreadsheet-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

Reconociendo que no todo el mundo está usando una de estas versiones de Excel o es posible que no desee utilizar un complemento, hay otras cosas que puede hacer. En Excel 2013 y versiones posteriores hay una función de hoja de trabajo útil llamada FORMULATEXT. Puede utilizar esta función para recuperar la fórmula almacenada en una celda, de esta manera:

=FORMULATEXT(A7)

Esto devuelve la fórmula contenida en la celda A7 (en este caso). Si la celda no contiene una fórmula, devuelve un error # N / A. Puede utilizar este comportamiento para crear en una «hoja de trabajo de comparación» un indicador de si las fórmulas son iguales o no. Simplemente cree la nueva hoja de trabajo y colóquela en la celda A1:

=IF(FORMULATEXT(Sheet1!A1)=FORMULATEXT(Sheet2!A1),"","Different")

Copie la fórmula hacia abajo y hacia la derecha como desee. Marca las diferencias entre las celdas correspondientes en Sheet1 y Sheet2.

Recuerde que FORMULATEXT se introdujo en Excel 2013, por lo que este enfoque no funcionará en versiones anteriores de Excel. Si está usando otra versión (o, incluso, si está usando Excel 2013), puede usar una macro para marcar las diferencias entre las hojas de trabajo. Hay muchos enfoques macro que puede utilizar; la siguiente es una forma breve de hacer la comparación.

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

Para utilizar esta macro, muestre el libro de trabajo que desea comparar. Se asume que desea comparar con las mismas celdas en una hoja de trabajo llamada «Maestra».

(Obviamente, puede cambiar eso en la macro si su hoja de trabajo «estándar» tiene un nombre diferente). Cada celda en la hoja de trabajo actual se compara con la celda correspondiente en la hoja de trabajo «maestra». Si las celdas contienen fórmulas y esas fórmulas son diferentes, entonces el color de fondo de la celda se cambia a rojo en la hoja de trabajo actual.

Este enfoque obviamente cambia el formato de la hoja de trabajo que se compara. Si prefiere no cambiar el formato, sino que simplemente desea una lista de celdas con diferencias, puede usar la siguiente variación en la 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

También puede crear una función definida por el usuario (UDF) que acepte rangos para las comparaciones. De esa manera, podría usarlo de varias maneras.

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

Si solo desea confirmar que un rango de celdas en ambas hojas de trabajo tiene fórmulas idénticas, puede usar algo como:

=CompareFormulas3(Sheet1!A1:Z1000,Sheet2!A1:Z1000)

La función devuelve VERDADERO si todas las celdas tienen fórmulas idénticas, FALSO si alguna de las celdas tiene fórmulas diferentes, o error #Valor si los 2 rangos no son del mismo tamaño.

Si desea resaltar las diferencias, puede usar la UDF dentro de una regla de formato condicional. Suponiendo que desea aplicar el formato condicional a las celdas en Sheet1, solo especifique que la regla debe usar una fórmula y luego use esto como fórmula:

=NOT(CompareFormulas3(Sheet2!A1,A1))

Si alguna de las celdas de Sheet1 no coincide con las celdas correspondientes de Sheet2, se formateará de acuerdo con el formato que haya definido en la regla de formato condicional.

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13400) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.