Cómo comparar dos hojas de Excel para duplicados: 5 formas rápidas

Cuando se trabaja con grandes conjuntos de datos en Excel, es común tener varias hojas de trabajo que contienen datos similares. Comparar estas hojas en busca de registros duplicados puede ser todo un desafío, especialmente cuando los datos están dispersos en varias columnas y filas.

Los métodos más comunes para encontrar duplicados en dos hojas de Excel son utilizar:

1. Funciones BUSCARV, CONTAR.SI o EXACTO

2. Formato condicional

3. consulta de poder

4. Herramientas y complementos externos

5. Comprobaciones visuales de duplicados

Este artículo explica paso a paso estos cinco métodos para identificar y manejar duplicados en varias hojas de trabajo. Al final, verá lo fácil que es administrar valores duplicados.

¡Empecemos!

Tabla de contenido

• Ejemplos de hojas de trabajo y datos
• 1. Uso de las funciones BUSCARV, CONTAR.SI o EXACTO para buscar duplicados
  • A. ¿Cómo se usa la función BUSCARV para buscar duplicados en dos hojas?
    • ¿Qué sucede si está manejando diferentes libros de trabajo?
  • B. ¿Cómo se usa la función CONTAR.SI para encontrar duplicados en las hojas de trabajo?
  • C. ¿Cómo se utiliza la función EXACTA para encontrar duplicados en las hojas de trabajo?
• 2. Cómo usar el formato condicional para filas duplicadas
  • Cómo usar el administrador de reglas de formato condicional
• 3. Cómo usar Power Query para encontrar duplicados en hojas de trabajo
• 4. Herramientas y complementos para identificar duplicados en hojas de trabajo
• 5. Cómo comprobar visualmente si hay duplicados en dos hojas
• 3 consejos para preparar sus hojas de cálculo de Excel
• Cómo manejar errores e inconsistencias
• Pensamientos finales

Ejemplos de hojas de trabajo y datos

Para seguir los ejemplos de este artículo, cree un nuevo libro de Excel con estos datos en la columna A de la primera hoja:

• Manzana

• Naranja

• Pera

• Fresa

Pon estos datos en la columna A de la segunda hoja:

• Pera

• Fresa

• Manzana

• Piña

Sus hojas de trabajo se ven así:

Una vez que haya configurado sus hojas de trabajo, ahora podemos repasar las diferentes formas de encontrar duplicados en dos hojas, comenzando con las funciones BUSCARV, CONTAR.SI y EXACTO.

¡Vamos!

1. Uso de las funciones BUSCARV, CONTAR.SI o EXACTO para buscar duplicados

Excel tiene tres funciones integradas que pueden facilitar la búsqueda de duplicados: BUSCARV , CONTAR.SI y EXACTO .

Estas funciones están diseñadas para ayudarlo a encontrar, contar y comparar datos dentro de sus hojas de cálculo, lo que las convierte en herramientas ideales para encontrar entradas duplicadas.

Cada función tiene un propósito único y comprender cómo usarlas puede optimizar significativamente su proceso de análisis de datos.

En las siguientes secciones, exploraremos cómo usar las funciones BUSCARV, CONTAR.SI y EXACTO para identificar efectivamente los duplicados en sus hojas de cálculo de Excel.

A. ¿Cómo se usa la función BUSCARV para buscar duplicados en dos hojas?

BUSCARV significa Búsqueda vertical. Utilice esta función para encontrar valores duplicados entre dos columnas. Esta es la sintaxis:

=BUSCARV(valor_buscado, matriz_tabla, núm_índice_col, [búsqueda_rango])

• lookup_value : el valor que desea buscar en la primera columna de table_array.

• table_array : el rango de celdas que contiene los datos en los que desea buscar.

• col_index_num : el número de columna en table_array del que desea devolver el valor.

• range_lookup : Opcional. Es VERDADERO (coincidencia aproximada) o FALSO (coincidencia exacta). El valor predeterminado es VERDADERO.

Para usar la función en dos hojas de trabajo en un archivo de Excel, debe saber cómo hacer referencia a una hoja separada en una fórmula. Para hacerlo, escriba el nombre de la hoja seguido del signo de exclamación (!) y su celda o rango de celdas.

Por ejemplo, esto hace referencia a las celdas A2 a A5 en la hoja 2 del mismo libro: Hoja 2!$A$2:$A$5.

Para usar la función BUSCARV en su hoja de cálculo de muestra, siga estos pasos:

1. Seleccione la celda B2 para mostrar el primer resultado de la comparación.

2. Escriba esta fórmula: =BUSCARV(A2,Hoja2!$A$2:$A$5, 1, FALSO).

3. Presione Entrar para mostrar el resultado de la comparación.

4. Complete la fórmula para comparar los valores del resto de las filas en la primera hoja.

Los resultados se parecerán a la siguiente imagen:

Puede ordenar la pantalla mostrando un mensaje fácil de usar en lugar de un error cuando no se encuentra un duplicado.

Por ejemplo, esta fórmula mostrará "Sí" o "No" para los valores encontrados y no encontrados, respectivamente:

=SI(ISNA(BUSCARV(A2, Hoja 2!$A$2:$A$5, 1, FALSO)), “No”, “Sí”)

Hay más ejemplos de esta función en nuestro artículo sobre el uso de BUSCARV para comparar dos columnas .

¿Qué sucede si está manejando diferentes libros de trabajo?

Si sus hojas de trabajo están en libros de trabajo separados, el uso de la función es el mismo con dos archivos de Excel. Sin embargo, hacer referencia a la segunda hoja de trabajo es un poco más complejo.

Necesitas:

• Encierre el nombre del libro de Excel entre paréntesis

• Siga con el nombre de la hoja de trabajo

• Encierre el libro y la hoja de trabajo entre comillas.

Por ejemplo, si las celdas están en una hoja llamada Hoja2 en un libro de trabajo llamado "WB 2.xlsx", el formato se verá así:

'[WB 2.xlsx]Hoja2'!$A$2:$A$5

Antes de ingresar la fórmula, cierre el segundo libro. De lo contrario, obtendrá un error.

B. ¿Cómo se usa la función CONTAR.SI para encontrar duplicados en las hojas de trabajo?

La función CONTAR.SI en Excel se usa para contar el número de celdas dentro de un rango específico que cumple con un criterio dado.

Para comparar varias hojas, puede contar el número de celdas en la segunda hoja de trabajo que coincide con una celda en la primera hoja de trabajo.

Esta es la sintaxis de la función:

=CONTAR.SI(rango, criterio)

• Rango : el rango de celdas que desea contar en función de los criterios especificados.

• Criterio : la condición que debe cumplirse para que una celda sea contada.

Para usar la función con los datos de muestra, siga estos pasos:

1. Seleccione la celda B2 para mostrar el primer resultado de la comparación.

2. Escriba esta fórmula: =CONTAR.SI(Hoja2!$A$2:$A$5, A2)

3. Presione Entrar para mostrar el resultado de la comparación.

4. Complete la fórmula para comparar los valores del resto de las filas en la primera hoja.

La función encontrará una coincidencia para algunas de las celdas y ninguna para otras. La celda de comparación muestra el recuento. Aquí está el resultado de los datos de muestra:

La función CONTAR.SI se puede usar para otras tareas útiles, como contar celdas que no están en blanco en Excel .

C. ¿Cómo se utiliza la función EXACTA para encontrar duplicados en las hojas de trabajo?

La función EXACTO en Excel también se puede usar para buscar duplicados dentro de las mismas celdas en dos hojas de cálculo de Excel diferentes. La sintaxis es:

=EXACTO(texto1, texto2)

• text1 es la primera cadena de texto que desea comparar.

• text2 es la segunda cadena de texto que desea comparar.

Sigue estos pasos:

1. Seleccione la celda B2.

2. Escriba la fórmula =EXACTO(A2, Hoja2!A2)

3. Presione Entrar para mostrar el resultado de la comparación. La fórmula devolverá VERDADERO si ambos valores son idénticos o FALSO en caso contrario.

4. Complete la fórmula para comparar los valores del resto de las filas en la primera hoja.

Tenga en cuenta que este método no busca duplicados en un rango de celdas. En cambio, solo está buscando coincidencias basadas en la misma celda en una hoja diferente.

Esto puede ser útil con datos ordenados donde solo espera algunas excepciones.

Las funciones BUSCARV, CONTAR.SI y EXACTO son útiles para encontrar duplicados, pero Excel es un programa versátil y existen otras opciones. En la siguiente sección, veremos cómo puede usar el formato condicional para identificar duplicados en dos hojas.

2. Cómo usar el formato condicional para filas duplicadas

En esta sección, aprenderá a usar el formato condicional para buscar y resaltar filas duplicadas en dos hojas de cálculo de Excel.

Para crear una regla de formato condicional, siga estos pasos:

1. Selecciona el rango de celdas que contienen los datos (A2:A5 en este caso).

2. Haga clic en la pestaña "Inicio" en la cinta de Excel.

3. Haga clic en "Formato condicional" en el grupo "Estilos".

4. Elija "Nueva regla" en el menú desplegable.

La siguiente tarea es proporcionar una fórmula para que la use su regla. Sigue estos pasos:

1. Elija "Usar una fórmula para determinar qué celdas formatear" en el cuadro de diálogo

2. Introduzca la siguiente fórmula: =CONTAR.SI(Hoja2!$A$2:$A$5, A2) > 0

Finalmente, aplica el formato que prefiera para las celdas duplicadas.

1. Haga clic en el botón Formato para abrir el cuadro de diálogo Formato de celdas.

2. Elija un formato, por ejemplo, rellene los duplicados con un color de fondo amarillo.

3. Haga clic en Aceptar.

Sus datos duplicados ahora están resaltados en amarillo.

Cómo usar el administrador de reglas de formato condicional

Una vez que haya creado la regla de formato condicional, puede administrarla mediante el Administrador de reglas de formato condicional.

Para acceder al administrador:

1. Vaya a la pestaña Inicio.

2. Haga clic en Formato condicional.

3. Elija "Administrar reglas".

Verá una lista de todas las reglas de formato condicional aplicadas a la hoja seleccionada. Puede editar, eliminar o cambiar el orden de las reglas seleccionando la regla y haciendo clic en los botones correspondientes.

Para aplicar la misma regla a la otra hoja, siga estos pasos:

1. Seleccione el rango que desea comparar en la segunda hoja.

2. Vaya al Administrador de reglas de formato condicional.

3. Seleccione la regla, haga clic en Duplicar regla y luego presione Editar regla.

4. Reemplace "Hoja2" con el nombre de la primera hoja para comparar.

Ahora que ha aplicado la regla de formato condicional a ambas hojas, los duplicados se resaltarán según el formato que haya elegido.

Asegúrese de ajustar el rango y las referencias de celda en las fórmulas según sea necesario para cubrir todos los datos que desea comparar.

El formato condicional puede parecer un poco primitivo. Si desea un control más preciso, ¡Power Query puede ser la respuesta! En la siguiente sección, cubrimos cómo puede usar Power Query para encontrar duplicados.

3. Cómo usar Power Query para encontrar duplicados en hojas de trabajo

Power Query es una herramienta de transformación y preparación de datos en Microsoft Excel. Identificar los mismos valores es solo una de las muchas tareas de análisis que puede realizar con la herramienta.

Para hacerlo, primero debe importar los datos de las dos hojas de trabajo en tablas separadas. Siga estos pasos dentro de cada hoja:

1. Haga clic derecho en el rango de celdas.

2. Elija "Obtener datos de la tabla/rango".

3. Modifique el nombre de la tabla a algo apropiado.

Después de importar ambas hojas, la primera tarea es fusionar los datos:

1. Vaya a la pestaña Datos.

2. Haga clic en "Obtener datos".

3. Seleccione "Combinar consultas".

4. Elija "Fusionar" y seleccione las dos tablas.

5. Haga clic en las dos columnas clave.

6. Elija "Interior" como "Tipo de unión" y haga clic en Aceptar.

El Editor de Power Query se abrirá con los datos combinados de ambas tablas en su hoja de Excel. Verá dos columnas, una de cada tabla. Como solo le interesan los valores duplicados, puede eliminar la segunda columna.

Puede hacer clic en "Cerrar y cargar" en el Editor de Power Query para cargar los duplicados en una nueva hoja de trabajo.

Para explorar más aspectos de esta poderosa función, siga los ejemplos de nuestro artículo sobre cómo usar Power Query en Excel .

Excel también tiene herramientas y complementos de terceros que agregan la capacidad de encontrar duplicados sin problemas, así que echemos un vistazo a algunas de esas herramientas en la siguiente sección.

4. Herramientas y complementos para identificar duplicados en hojas de trabajo

Las herramientas y los complementos externos pueden ofrecer funciones avanzadas que pueden no estar disponibles en las características nativas de Excel. Estas herramientas pueden agilizar aún más el proceso de comparación de hojas en busca de duplicados.

Spreadsheet Compare es una herramienta de Microsoft que le permite comparar dos libros de trabajo uno al lado del otro, resaltando las diferencias e identificando fácilmente los duplicados. Puede descargarlo desde el sitio web de Microsoft.

Hay varios complementos que puede instalar para automatizar el proceso de búsqueda de duplicados. Un ejemplo es “ Eliminador de Duplicados ”. Para instalar un complemento:

1. Vaya a la pestaña Insertar.

2. Haga clic en "Obtener complemento".

3. Busque "Duplicado".

4. Haga clic en "Agregar" en la herramienta de su elección.

5. Cómo comprobar visualmente si hay duplicados en dos hojas

Si todo lo demás falla, ¡usa tus ojos! El cuadro de diálogo Organizar ventanas en Excel le permite ver varias hojas de trabajo o libros de trabajo uno al lado del otro.

Si bien no encuentra duplicados directamente, puede ayudarlo a comparar datos visualmente en hojas de trabajo o libros de trabajo para detectar duplicados. Sigue estos pasos:

1. Haga clic en la pestaña "Ver" en la cinta de Excel.

2. Haga clic en "Organizar todo" en el grupo "Ventana".

3. Elija una opción de disposición, por ejemplo, "Vertical" u "Horizontal".

Esto mostrará ambas hojas una al lado de la otra o una encima de la otra. Ahora puede comparar manualmente los datos en cada hoja para identificar duplicados.

Debe desplazarse por los datos e inspeccionar visualmente cada valor para encontrar coincidencias.

Tenga en cuenta que este método no es eficaz para grandes conjuntos de datos, ya que requiere una comparación manual. Usar los otros métodos de este artículo será más efectivo para encontrar duplicados en conjuntos de datos más grandes.

¡Y ese es el último de nuestros métodos comunes para encontrar valores duplicados en hojas de Excel! En la siguiente sección, le daremos algunos consejos para preparar sus hojas de trabajo.

3 consejos para preparar sus hojas de cálculo de Excel

Antes de comenzar a comparar varias hojas, asegúrese de tener las columnas y filas de sus conjuntos de datos alineados correctamente.

Asegúrese de que ambas hojas de Excel tengan la misma estructura y los mismos nombres de encabezado. Si es necesario, puede reorganizar las columnas de ambas hojas para que coincidan entre sí.

Aquí hay tres sugerencias para asegurar comparaciones precisas:

1. Organice sus datos en el mismo orden en ambas hojas. Esto facilita que las funciones de Excel funcionen de manera efectiva.

2. Normalice sus datos mediante el uso de formatos, mayúsculas y tipos de datos coherentes . Esto evitará entradas no coincidentes debido a diferencias menores.

3. Elimine filas o columnas en blanco innecesarias, ya que pueden interferir con el proceso de comparación.

Puede evaluar cuánta duplicación tiene en un conjunto de datos contando los valores únicos. Este video explica varios métodos:

Cómo manejar errores e inconsistencias

Las inconsistencias en sus datos pueden afectar el proceso de comparación. Aquí hay cuatro consejos para resolver las inconsistencias:

1. Compruebe si hay discrepancias en los tipos de datos, como mezclar texto y valores numéricos en la misma columna.

2. Asegúrese de que se utilice un formato uniforme para fechas, números y otros tipos de datos.

3. Examine sus datos en busca de entradas faltantes o incorrectas, y actualícelos si es necesario.

4. Estandarice las abreviaturas o las convenciones de nombres inconsistentes dentro de sus conjuntos de datos.

Pensamientos finales

Encontrar duplicados en dos hojas de cálculo de Excel es una tarea esencial para la gestión y el análisis de datos, lo que garantiza la integridad y precisión de los datos. Excel ofrece múltiples técnicas para identificar duplicados, cada una con sus propias ventajas y limitaciones.

La elección del método depende de las necesidades del usuario, el tamaño y la complejidad del conjunto de datos y el resultado deseado. Para conjuntos de datos más pequeños y comparaciones sencillas, puede ser suficiente usar VLOOKUP , COUNTIF o el formato condicional.

Para conjuntos de datos más grandes o transformaciones de datos más complejas, Power Query es una herramienta poderosa y flexible que puede manejar una amplia gama de tareas de preparación de datos, incluida la búsqueda de duplicados.

Para concluir, comparar hojas de Excel en busca de duplicados es una habilidad muy útil para tener en su caja de herramientas. Con los trucos de este artículo, puede detectar esos molestos duplicados y mantener sus datos impecablemente limpios.

Confíe en nosotros, a medida que mejore en esto, podrá realizar sus tareas de datos e impresionar a todos los que lo rodean.