Encontrar discrepancias en Excel: 5 técnicas sencillas explicadas

Cuando se trabaja con grandes conjuntos de datos en Microsoft Excel, es inevitable encontrar discrepancias en los datos. Identificar y corregir estas inconsistencias es fundamental para garantizar informes y análisis precisos.

¿Las buenas noticias? Excel viene con una variedad de herramientas y técnicas para ayudarlo a encontrar y resolver estos problemas. En este artículo, aprenderá sobre algunos de los métodos más efectivos. Esto incluye el filtrado, el formato condicional y el uso de fórmulas avanzadas.

Al incorporar estas técnicas en su flujo de trabajo diario, será más eficiente en la gestión y el análisis de sus datos. En última instancia, conduce a una mejor toma de decisiones y mejores resultados para sus proyectos.

Tabla de contenido

• Comprender las discrepancias en Excel
• 5 formas de encontrar discrepancias en Excel
• #1 – Encontrar discrepancias a través de la revisión manual en Excel
• N.º 2: uso de filtros en Excel para detectar y corregir datos inconsistentes
• 3# – Resaltar discrepancias con formato condicional
• #4 – Encontrar discrepancias con funciones avanzadas de Excel
  • Funciones IF y IS
  • BUSCARV, BUSCARH Y BUSCARX
  • FÓSFORO
• #5 – Uso de complementos de Excel para la detección de discrepancias
• ¿Cómo puede crear reglas de validación de datos para evitar discrepancias?
• Nuestra palabra final

Comprender las discrepancias en Excel

Las discrepancias en filas y columnas pueden deberse a errores de entrada humanos, entradas duplicadas o irregularidades en el formato de los datos.

Echa un vistazo a los siguientes datos. Notarás que el Género de Walter White y Wayne Cena está mal escrito: Mael en lugar de Masculino. De hecho, es sencillo notar y corregir los errores en este escenario. Pero ese no es el caso de las hojas de cálculo que contienen miles de filas y columnas.

Para encontrar y administrar estas discrepancias, puede usar las siguientes funciones de Excel :

• Filtros: aplique filtros a columnas específicas para ver valores únicos y detectar rápidamente datos inconsistentes.
• Formato condicional: use reglas de formato para resaltar celdas que cumplan con criterios específicos o datos que no coincidan entre columnas.
• Funciones avanzadas de Excel: utilice funciones como COINCIDIR y BUSCARV para comparar conjuntos de datos e identificar discrepancias entre ellos.

Al comprender cómo usar estas funciones en Excel, puede detectar y rectificar de manera eficiente las discrepancias en sus conjuntos de datos.

5 formas de encontrar discrepancias en Excel

Hay 5 formas principales en las que nos gusta abordar las discrepancias en Excel:

1. Revisión manual
2. Filtración
3. Formato condicional
4. Funciones avanzadas de Excel
5. Complementos de Excel

Repasemos cada uno de ellos para que pueda encontrar la mejor manera que más le convenga.

#1 – Encontrar discrepancias a través de la revisión manual en Excel

Cuando trabaje con Excel , una revisión manual puede ser un método útil para identificar discrepancias en sus datos. Este proceso requiere un examen cuidadoso de los conjuntos de datos para detectar cualquier inconsistencia o error.

Estos son algunos pasos que puede seguir para realizar una revisión manual:

• Comience por comprender claramente los conjuntos de datos con los que está trabajando. Familiarícese con las filas, las columnas y el formato.
• Desplácese por los datos y preste mucha atención a la información presentada en cada celda. Puede utilizar herramientas como el resaltado de celdas con color de relleno, formato de texto o formato condicional para que los datos sean más fáciles de leer.
• Compara una fila con otra o una columna con otra. Busque información que no coincida, entradas duplicadas, etc. que puedan causar inexactitudes en sus hojas de Excel.
• A medida que identifique discrepancias, cree una lista o use comentarios para documentarlas. Posteriormente, estos pueden servir como referencias importantes para corregir los problemas.

Al revisar manualmente los datos en busca de discrepancias en una hoja de cálculo de Excel, la paciencia es la clave. Este proceso requiere mucho tiempo. Pero es un buen punto de partida para identificar posibles errores.

N.º 2: uso de filtros en Excel para detectar y corregir datos inconsistentes

Una de las formas más fáciles de detectar datos inconsistentes es usar el filtro de Excel .

Con Filtro, aislaremos los datos incorrectos en la columna Género en el siguiente conjunto de datos:

1. Seleccione Ordenar y filtrar en la pestaña Inicio .

2. Elija Filtro : asegúrese de que el cursor esté en el rango de los datos.

3. Haga clic en la flecha de la lista desplegable en la columna con datos inconsistentes.

4. Ahora, anule la selección de todos los datos coincidentes correctos. En nuestro caso, es Male . Luego, haga clic en Aceptar para guardar los cambios.

5. Su hoja de cálculo de Excel ahora solo mostrará filas con valores incorrectos. Para solucionarlos, haga clic en Buscar y seleccionar en la pestaña Inicio .

6. Haga clic en Reemplazar cuando aparezca la lista desplegable.

7. Escriba el valor incorrecto en Buscar y el correcto en Reemplazar con . Luego, haga clic en Reemplazar todo .

8. Aparecerá un cuadro de diálogo para confirmar los cambios. Haga clic en Aceptar para continuar.

3# – Resaltar discrepancias con formato condicional

Otra forma de identificar las diferencias entre columnas y filas es mediante el formato condicional.

Puede acceder a reglas como Resaltar celdas, Superior/Inferior, Barras de datos, Escalas de color y Conjuntos de hierro. Además, puede crear una nueva regla y administrar o borrar reglas anteriores.

Usaremos una tabla de muestra para mostrarle el poder del formato condicional:

1. Seleccione su tabla o filas y columnas preferidas. Estamos eligiendo la Columna A (Empresa) y la Columna B (País). Luego, seleccione Formato condicional en la pestaña Inicio .

2. Seleccione un tipo de regla y luego la regla. Estamos eligiendo Resaltar reglas de celdas y Duplicar valores...

3. Ahora, tenemos que seleccionar si resaltar valores Duplicados o Únicos y el Color de Relleno y Fuente para formatear celdas. Una vez hecho esto, haga clic en Aceptar .

4. Todos los valores duplicados ahora están resaltados con Relleno rojo claro con texto rojo oscuro.

#4 – Encontrar discrepancias con funciones avanzadas de Excel

Funciones IF y IS

Para comparar celdas e identificar diferencias, puede utilizar las funciones IF e IS.

Por ejemplo, hemos usado la fórmula =IF(A2=B2,"MATCH","NOT MATCHED")para comparar las celdas A2 y B2. Y hemos arrastrado la fórmula para aplicarla a otras celdas. Entonces, las celdas idénticas están devolviendo COINCIDIR de lo contrario NO COINCIDIR .

Puede combinar funciones IF con IS como ESNUMERO o ISTEXT para verificar tipos de datos específicos. Por ejemplo, la fórmula =IF(ISNUMBER(B2), "Number", "NaN")devolverá Número en la columna c si B2 es numérico y NaN en caso contrario.

BUSCARV, BUSCARH Y BUSCARX

Las funciones BUSCARV (Vertical) y BUSCARH (Horizontal) son útiles cuando se comparan valores en diferentes columnas o filas de dos tablas. Para hacerlo, utilice la siguiente fórmula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) o =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Reemplace lookup_value con el valor que desea encontrar en la otra tabla, table_array con el rango de celdas en la segunda tabla, col_index_num o row_index_num con el número de índice de la columna o fila de la que desea obtener un valor, y [range_lookup] con FALSO para coincidencias exactas o VERDADERO para una coincidencia aproximada.

En el siguiente tutorial, estamos utilizando la función BUSCARV en la columna E para comparar los nombres de los empleados en la columna A y la columna D para obtener sus salarios de la columna B si se encuentra una coincidencia.

BUSCARX es una versión actualizada de BUSCARV y BUSCARH disponible en Excel 2021 y Excel 365. La fórmula de BUSCARX es: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Sustituya lookup_value con el valor que desea encontrar en la otra tabla, lookup_array con el rango de celdas para buscar en la segunda tabla, return_array con la matriz de la que desea obtener un valor y [if_nout_found] con un valor de texto si no hay coincidencia se encuentran los valores.

Aquí hay una muestra rápida de XLOOKUP con las mismas tablas:

FÓSFORO

La función COINCIDIR también se puede usar para comparar dos listas en busca de discrepancias.

La función COINCIDIR, =MATCH(lookup_value, lookup_array, [match_type])busca un elemento específico en un rango y devuelve la posición relativa del elemento dentro de ese rango.

Aquí, estamos encontrando la posición de las sandías en la matriz A2:A8 usando MATCH. Usamos match_type como 0 para encontrar el primer valor que es exactamente igual a lookup_value .

#5 – Uso de complementos de Excel para la detección de discrepancias

Excel ofrece varios complementos y herramientas integradas que pueden ayudarlo a detectar y analizar discrepancias de manera más eficiente. En esta sección, discutiremos algunos de esos complementos útiles.

Primero, considere usar el complemento de consulta de hoja de cálculo . Esto puede ayudarlo a comparar dos libros de trabajo y resaltar las diferencias celda por celda. Para habilitar el complemento Consulta de hoja de cálculo:

1. Haga clic en la pestaña Archivo .

2. Seleccione Opciones y luego haga clic en la categoría Complementos .

3. En el cuadro Administrar , seleccione Complementos COM y haga clic en Ir .

4. Marque la casilla Consultar hoja de cálculo y haga clic en Aceptar .

5. Una vez habilitado, vaya a la pestaña Consultar para usar el comando Comparar archivos .

Otra herramienta útil para la detección de discrepancias es Analysis ToolPak . Este complemento proporciona funciones estadísticas avanzadas y herramientas de análisis de datos, que pueden ser útiles al analizar discrepancias.

Para habilitar el paquete de herramientas para análisis, siga los mismos pasos que para habilitar el complemento Consulta de hoja de cálculo. Pero seleccione Complementos de Excel en el cuadro Administrar y marque la casilla Herramientas para análisis .

En resumen, Excel ofrece varios complementos para ayudarlo a detectar y manejar discrepancias en sus datos. Puede mejorar la precisión de sus datos si se familiariza con estas herramientas y fórmulas.

¿Cómo puede crear reglas de validación de datos para evitar discrepancias?

Para crear reglas de validación de datos en Excel que puedan ayudar a evitar discrepancias, siga estos pasos:

1. Seleccione las celdas que desea restringir. Esto puede ser una sola celda, un rango de celdas o una columna completa.

2. Vaya a la pestaña Datos en la barra de herramientas y haga clic en el botón Validación de datos (marcado por dos cuadros horizontales, una marca de verificación verde y un círculo cruzado rojo).

3. En la ventana Validación de datos, asegúrese de estar en la pestaña Configuración . Aquí tendrá la oportunidad de definir sus criterios de validación en función de sus necesidades.

Hay varios tipos de criterios de validación disponibles en Excel, algunos de los cuales incluyen:

• Número entero: solo permite números enteros dentro de un rango específico.
• Decimal: solo permite números decimales dentro de un rango específico.
• Lista: restringe la entrada a una lista predefinida de valores aceptables.
• Fecha: requiere fechas dentro de un intervalo de fechas específico.
• Hora: acepta solo horas dentro de un rango de tiempo particular.
• Longitud del texto: impone restricciones en la longitud del texto ingresado.
• Personalizado: le permite crear reglas de validación personalizadas utilizando fórmulas de Excel.

Después de seleccionar los criterios de validación apropiados, especifique los parámetros según sea necesario. Por ejemplo, si elige Número entero, deberá establecer el Mínimo y el Máximo con un validador de rango de datos como entre, igual a, menor que, etc.

Además de los criterios de validación, también puede configurar mensajes de error personalizados y sugerencias de entrada para ayudar a los usuarios a comprender y cumplir sus reglas de validación. Para hacer esto, cambie a las pestañas Mensaje de entrada y Alerta de error para ingresar los mensajes deseados.

Al implementar reglas de validación de datos, reduce significativamente la probabilidad de inconsistencias en los datos y mejora la precisión general de sus hojas de cálculo de Excel.

Nuestra palabra final

En este artículo, aprendió varios métodos para encontrar y abordar las discrepancias en sus datos de Excel. Recapitulemos esas estrategias clave:

• Uso de filtros desplegables en columnas para identificar valores únicos y datos incoherentes.
• Empleando formato condicional en celdas seleccionadas para resaltar discrepancias usando un montón de plantillas de reglas.
• Aplicar funciones avanzadas de Excel como COINCIDIR , BUSCARV , BUSCARX , etc. funciones para cotejar datos en dos listas o tablas.
• Uso de complementos de Excel para detectar discrepancias.
• Configuración de reglas de validación de datos para evitar errores en los datos

Con estas técnicas en su conjunto de habilidades de Excel, sus datos serán más precisos y confiables.

Recuerde practicar estos métodos regularmente para ser más eficiente en la detección de discrepancias en sus hojas de cálculo.

Siga perfeccionando sus habilidades registrándose en nuestra y continúe explorando nuevas características y funcionalidades para ser aún más competente en Excel.