Cómo comparar dos hojas de Excel (para ver las diferencias)

Ver video: Cómo comparar dos hojas de Excel para ver las diferencias

Comparar dos archivos de Excel (o comparar dos hojas en el mismo archivo) puede ser complicado, ya que un libro de Excel solo muestra una hoja a la vez.

Esto se vuelve más difícil y propenso a errores cuando tiene muchos datos que deben compararse.

Afortunadamente, hay algunas características interesantes en Excel que le permiten abrir y comparar fácilmente dos archivos de Excel.

En este tutorial de Excel, le mostraré varias formas de comparar dos archivos (u hojas) de Excel diferentes y verifique las diferencias. El método que elija dependerá de cómo estén estructurados sus datos y del tipo de comparación que esté buscando.

¡Empecemos!

Compare dos hojas de Excel en archivos de Excel separados (una al lado de la otra)

Si desea comparar dos archivos de Excel separados uno al lado del otro (o dos hojas en el mismo libro de trabajo), hay una función incorporada en Excel para hacer esto.

Es el Ver lado a lado opción.

Esto se recomienda solo cuando tiene un conjunto de datos pequeño y es probable que la comparación manual de estos archivos requiera menos tiempo y sea propensa a errores. Si tiene un conjunto de datos grande, le recomiendo usar el método condicional o el método de fórmula que se describe más adelante en este tutorial.

Veamos cómo usar esto cuando tenga que comparar dos archivos separados o dos hojas en el mismo archivo.

Suponga que tiene dos archivos para dos meses diferentes y desea verificar qué valores son diferentes en estos dos archivos.

De forma predeterminada, cuando abre un archivo, es probable que ocupe toda la pantalla. Incluso si reduce el tamaño, siempre verá un archivo de Excel en la parte superior.

Con la opción de ver lado a lado, puede abrir dos archivos y luego organizarlos horizontal o verticalmente. Esto le permite comparar fácilmente los valores sin cambiar de un lado a otro.

A continuación se muestran los pasos para alinear dos archivos uno al lado del otro y compararlos:

1. Abra los archivos que desea comparar.
2. En cada archivo, seleccione la hoja que desea comparar.
3. Haga clic en la pestaña Ver
4. En el grupo de Windows, haga clic en la opción "Ver en paralelo". Esto está disponible solo cuando tiene dos o más archivos de Excel abiertos.

Tan pronto como haga clic en la opción Ver lado a lado, Excel organizará el libro de trabajo horizontalmente. Ambos archivos estarán visibles y usted es libre de editar / comparar estos archivos mientras están ordenados uno al lado del otro.

En caso de que desee organizar los archivos verticalmente, haga clic en la opción Organizar todo (en la pestaña Ver).

Esto abrirá el cuadro de diálogo "Organizar ventanas" donde puede seleccionar "Vertical".

En este punto, si se desplaza hacia abajo en una de las hojas de trabajo, la otra permanecerá como está. Puede cambiar esto para que cuando se desplace en una hoja, la otra también se desplace al mismo tiempo. Esto hace que sea más fácil hacer una comparación línea por línea y detectar cualquier diferencia.

Pero para hacer esto, necesita habilitar Desplazamiento sincrónico.

Para habilitar el Desplazamiento sincrónico, haga clic en la pestaña Ver (en cualquiera de los libros de trabajo) y luego haga clic en la opción Desplazamiento sincrónico. Este es un botón de alternancia (por lo que si desea apagarlo, simplemente haga clic en él nuevamente).

Comparación de varias hojas en archivos de Excel separados (una al lado de la otra)

Con la opción "Ver en paralelo", solo puede comparar dos archivos de Excel a la vez.

En caso de que tenga varios archivos de Excel abiertos, cuando haga clic en la opción Ver lado a lado, le mostrará un cuadro de diálogo "Comparar lado a lado", donde puede elegir qué archivo desea comparar con el libro activo.

En caso de que desee comparar más de dos archivos a la vez, abra todos estos archivos y luego haga clic en la opción Organizar todo (está en la pestaña Ver).

En el cuadro de diálogo Organizar ventanas, seleccione Vertical / Horizontal y luego haga clic en Aceptar.

Esto organizará todos los archivos de Excel abiertos en el orden seleccionado (vertical u horizontal).

Comparar dos hojas (una al lado de la otra) en el mismo libro de Excel

En caso de que desee comparar dos hojas separadas en el mismo libro de trabajo, no puede usar la función Ver una al lado de la otra (ya que solo funciona para archivos de Excel separados).

Pero aún puede hacer la misma comparación lado a lado.

Esto es posible gracias a la "Nueva función de Windows en Excel, eso le permite abrir dos instancias en el mismo libro de trabajo. Una vez que tenga dos instancias abiertas, puede organizarlas una al lado de la otra y luego compararlas.

Suponga que tiene un libro de Excel que tiene dos hojas para dos meses diferentes (enero y febrero) y desea compararlos uno al lado del otro para ver cómo han cambiado las ventas por tienda:

A continuación se muestran los pasos para comparar dos hojas en Excel:

1. Abra el libro de trabajo que tiene las hojas que desea comparar.
2. Haga clic en la pestaña Ver
3. En el grupo Ventana, haga clic en la opción "Nueva ventana". Esto abre la segunda instancia del mismo libro.
4. En la pestaña "Ver", haga clic en "Organizar todo". Esto abrirá el cuadro de diálogo Organizar ventanas.
5. Seleccione "Vertical" para comparar datos en columnas (o seleccione Horizontal si desea comparar datos en filas).
6. Haga clic en Aceptar.

Los pasos anteriores organizarían ambas instancias del libro de trabajo verticalmente.

En este momento, ambos libros de trabajo tendrían seleccionada la misma hoja de trabajo. En uno de los libros de trabajo, seleccione la otra hoja que desea comparar con la hoja activa.

¿Como funciona esto?

Cuando hace clic en Nueva ventana, se abre el mismo libro de trabajo nuevamente con un nombre ligeramente diferente. Por ejemplo, si el nombre de su libro de trabajo es "Prueba" y hace clic en Nueva ventana, el libro de trabajo ya abierto se denominará "Prueba - 1" y la segunda instancia como "Prueba - 2".

Tenga en cuenta que estos siguen siendo el mismo libro de trabajo. Si realiza algún cambio en cualquiera de estos libros de trabajo, se reflejará en ambos.

Y cuando cierra cualquier instancia del archivo abierto, el nombre volverá al original.

También puede habilitar el desplazamiento sincrónico si lo desea (haciendo clic en la opción "Desplazamiento sincrónico" en la pestaña "Ver")

Compare dos hojas y resalte las diferencias (usando formato condicional)

Si bien puede usar el método anterior para alinear los libros de trabajo y revisar manualmente los datos línea por línea, no es una buena manera en caso de que tenga muchos datos.

Además, hacer este nivel de comparación manualmente puede generar muchos errores.

Entonces, en lugar de hacer esto manualmente, puede usar el poder del formato condicional para resaltar rápidamente cualquier diferencia en las dos hojas de Excel.

Este método es realmente útil si tiene dos versiones en dos hojas diferentes y desea comprobar rápidamente qué ha cambiado.

Tenga en cuenta que usted NO PODER compare dos hojas en diferentes libros de trabajo.

Dado que el formato condicional no puede hacer referencia a un archivo de Excel externo, las hojas que necesita comparar deben estar en el mismo libro de Excel. En caso de que no lo sean, puede copiar una hoja del otro archivo al libro de trabajo activo y luego hacer esta comparación.

Para este ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación durante dos meses (enero y febrero) en dos hojas diferentes y desea comparar rápidamente los datos en estas dos hojas y verificar si los precios de estos artículos han cambiado o no.

A continuación se muestran los pasos para hacer esto:

1. Seleccione los datos en la hoja donde desea resaltar los cambios. Como quiero comprobar cómo han cambiado los precios de enero a febrero, he seleccionado los datos en la hoja de febrero.
2. Haga clic en la pestaña Inicio
3. En el grupo Estilos, haga clic en "Formato condicional".
4. En las opciones que aparecen, haga clic en "Nueva regla"
5. En el cuadro de diálogo "Nueva regla de formato", haga clic en "Usar una fórmula para determinar qué celdas formatear"
6. En el campo de fórmula, ingrese la siguiente fórmula: = B2Ene! B2
7. Haga clic en el botón Formato
8. En el cuadro de diálogo Formato de celdas que aparece, haga clic en la "pestaña Rellenar" y seleccione el color en el que desea resaltar los datos que no coinciden.
9. Haga clic en Aceptar
10. Haga clic en Aceptar

Los pasos anteriores resaltarían instantáneamente cualquier cambio en el conjunto de datos en ambas hojas.

¿Como funciona esto?

El formato condicional resalta una celda cuando la fórmula dada para esa celda devuelve VERDADERO. En este ejemplo, estamos comparando cada celda en una hoja con la celda correspondiente en la otra hoja (hecho usando el operador no igual a en la fórmula).

Cuando el formato condicional encuentra alguna diferencia en los datos, resalta que en la hoja de enero (aquella en la que hemos aplicado el formato condicional.

Tenga en cuenta que he usado una referencia relativa en este ejemplo (A1 y no $ A $ 1 o $ A1 o A $ 1).

Cuando utilice este método para comparar dos hojas en Excel, recuerde lo siguiente;

• Este método es bueno para identificar rápidamente las diferencias, pero no puede usarlo de manera continua. Por ejemplo, si ingreso una nueva fila en cualquiera de los conjuntos de datos (o elimino una fila), obtendría resultados incorrectos. Tan pronto como inserto / elimino la fila, todas las filas posteriores se consideran diferentes y se resaltan en consecuencia.
• Solo puede comparar dos hojas en el mismo archivo de Excel
• Solo puede comparar el valor (no la diferencia en la fórmula o el formato).

Compare dos archivos / hojas de Excel y obtenga las diferencias usando la fórmula

Si solo está interesado en comparar e identificar rápidamente las diferencias entre dos hojas, puede usar una fórmula para obtener solo los valores que son diferentes.

Para este método, necesitará tener una hoja de trabajo separada donde pueda buscar las diferencias.

Este método funcionaría si desea comparar dos libros u hojas de trabajo de Excel separados en el mismo libro de trabajo.

Permítanme mostrarles un ejemplo en el que comparo dos conjuntos de datos en dos hojas (en el mismo libro de trabajo).

Suponga que tiene el conjunto de datos como se muestra a continuación en una hoja llamada Jan (y datos similares en una hoja llamada Feb) y desea saber qué valores son diferentes.

Para comparar las dos hojas, primero, inserte una nueva hoja de trabajo (llamemos a esta hoja "Diferencia").

En la celda A1, ingrese la siguiente fórmula:

= IF (Jan! A1Feb! A1, "Valor de enero:" & Jan! A1 & CHAR (10) & "Valor de febrero:" & Feb! A1, "")

Copie y pegue esta fórmula para un rango de modo que cubra todo el conjunto de datos en ambas hojas. Como tengo un pequeño conjunto de datos, solo copiaré y pegaré esta fórmula en el rango A1: B10.

La fórmula anterior usa una condición SI para verificar diferencias. En caso de que no haya diferencia en los valores, volverá en blanco, y en caso de que haya una diferencia, devolverá los valores de ambas hojas en líneas separadas en la misma celda.

Lo bueno de este método es que solo te da las diferencias y te muestra exactamente cuál es la diferencia. En este ejemplo, puedo ver fácilmente que el precio en las celdas B4 y B8 son diferentes (así como los valores exactos en estas celdas).

Compare dos archivos / hojas de Excel y obtenga las diferencias usando VBA

Si necesita comparar archivos u hojas de Excel con bastante frecuencia, es una buena idea tener un código VBA de macro de Excel listo y usarlo siempre que necesite hacer la comparación.

También puede agregar la macro a la barra de herramientas de acceso rápido para que pueda acceder con un solo botón y saber instantáneamente qué celdas son diferentes en diferentes archivos / hojas.

Suponga que tiene dos hojas de enero y febrero y desea comparar y resaltar las diferencias en la hoja de enero, puede usar el siguiente código VBA:

Sub CompareSheets () Dim rngCell como rango para cada rngCell In Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Cells (rngCell.Row, rngCell.Column) Luego rngCell.Interior.Color = vbYellow End Si Next rngCell End Sub

El código anterior usa el ciclo For Next para recorrer cada celda en la hoja de enero (todo el rango usado) y lo compara con la celda correspondiente en la hoja de febrero. En caso de que encuentre una diferencia (que se verifica usando la instrucción If-Then), resalta esas celdas en amarillo.

Puede utilizar este código en un módulo normal en VB Editor.

Y si necesita hacer esto con frecuencia, es mejor guardar este código en el libro de macros personales y luego agregarlo a la barra de herramientas de acceso rápido. De esa manera, podrá hacer esta comparación con un clic de un botón.

Estos son los pasos para obtener el libro de macros personal en Excel (no está disponible de forma predeterminada, por lo que debe habilitarlo).

Estos son los pasos para guardar este código en el Libro de macros personal.

Y aquí encontrará los pasos para agregar este código de macro al QAT.

Uso de una herramienta de terceros: Comparador XL

Otra forma rápida de comparar dos archivos de Excel y verificar coincidencias y diferencias es utilizando una herramienta gratuita de terceros, como XL Comparator.

Esta es una herramienta basada en la web donde puede cargar dos archivos de Excel y creará un archivo de comparación que tendrá los datos que son comunes (o datos diferentes según la opción que seleccionó.

Suponga que tiene dos archivos que tienen conjuntos de datos de clientes (como el nombre y la dirección de correo electrónico) y desea verificar rápidamente qué clientes hay en el archivo 1 y no en el archivo 2.

A continuación, se muestra cómo compara dos archivos de Excel y crea un informe de comparación:

1. Abra https://www.xlcomparator.net/
2. Utilice la opción Elegir archivo para cargar dos archivos (el tamaño máximo de cada archivo puede ser de 5 MB)
3. Haga clic en el botón Siguiente.
4. Seleccione la columna común en ambos archivos. La herramienta utilizará esta columna común para buscar coincidencias y diferencias
5. Seleccione una de las cuatro opciones, ya sea que desee obtener datos coincidentes o datos diferentes (según el Archivo 1 o el Archivo 2)
6. Haga clic en Siguiente
7. Descargue el archivo de comparación que tendrá los datos (según la opción que seleccionó en el paso 5)

A continuación se muestra un video que muestra cómo funciona la herramienta XL Comparator.

Una preocupación que puede tener al utilizar una herramienta de terceros para comparar archivos de Excel es la privacidad. Si tiene datos confidenciales y la privacidad es realmente importante para ellos, es mejor utilizar otros métodos que se muestran arriba. Tenga en cuenta que el sitio web de XL Comparator menciona que eliminan todos los archivos después de 1 hora de hacer la comparación.

Estos son algunos de los métodos que puede utilizar para comparar dos archivos de Excel diferentes (u hojas de trabajo en el mismo archivo de Excel). Espero que hayas encontrado útil este tutorial de Excel.