Una de las muchas capacidades de Microsoft Excel es la capacidad de comparar dos listas de datos, identificando coincidencias entre las listas e identificando qué elementos se encuentran en una sola lista. Esto es útil al comparar registros financieros o verificar si un nombre en particular está en una base de datos. Puede utilizar la función COINCIDIR para identificar y marcar registros coincidentes o no coincidentes, o puede utilizar el formato de condicionamiento con la función CONTAR. SI. Los siguientes pasos le indican cómo utilizar cada uno para hacer coincidir sus datos.
Pasos
Método 1 de 2: identificación de registros con la función COINCIDIR
Paso 1. Copie las listas de datos en una sola hoja de trabajo
Excel puede trabajar con varias hojas de trabajo dentro de un solo libro de trabajo, o con varios libros de trabajo, pero le resultará más fácil comparar las listas si copia su información en una sola hoja de trabajo.
Paso 2. Asigne a cada elemento de la lista un identificador único
Si sus dos listas no comparten una forma común de identificarlas, es posible que deba agregar una columna adicional a cada lista de datos que identifique ese elemento en Excel para que pueda ver si un elemento de una lista determinada está relacionado con un elemento. en la otra lista. La naturaleza de este identificador dependerá del tipo de datos que intente hacer coincidir. Necesitará un identificador para cada lista de columnas.
- Para los datos financieros asociados con un período determinado, como los registros de impuestos, esto podría ser la descripción de un activo, la fecha en que se adquirió el activo o ambos. En algunos casos, una entrada puede identificarse con un número de código; sin embargo, si no se usa el mismo sistema para ambas listas, este identificador puede crear coincidencias donde no hay ninguna o ignorar las coincidencias que se deben hacer.
- En algunos casos, puede tomar elementos de una lista y combinarlos con elementos de otra lista para crear un identificador, como una descripción del activo físico y el año en que se compró. Para crear un identificador de este tipo, concatenar (agregar, combinar) datos de dos o más celdas usando el signo comercial (&). Para combinar la descripción de un artículo en la celda F3 con una fecha en la celda G3, separada por un espacio, debe ingresar la fórmula '= F3 & "" & G3' en otra celda de esa fila, como E3. Si quisiera incluir solo el año en el identificador (porque una lista usa fechas completas y la otra usa solo años), incluiría la función AÑO ingresando '= F3 & "" & AÑO (G3)' en la celda E3 en su lugar. (No incluya las comillas simples; están ahí solo para indicar el ejemplo).
- Una vez que haya creado la fórmula, puede copiarla en todas las demás celdas de la columna del identificador seleccionando la celda con la fórmula y arrastrando el controlador de relleno sobre las otras celdas de la columna donde desea copiar la fórmula. Cuando suelte el botón del mouse, cada celda sobre la que arrastró se completará con la fórmula, con las referencias de celda ajustadas a las celdas correspondientes en la misma fila.
Paso 3. Estandarice los datos siempre que sea posible
Mientras que la mente reconoce que "Inc." y "Incorporado" significan lo mismo, Excel no lo hace a menos que tenga que volver a formatear una palabra u otra. Del mismo modo, puede considerar valores como $ 11, 950 y $ 11, 999.95 lo suficientemente cercanos como para coincidir, pero Excel no lo hará a menos que usted se lo indique.
- Puede utilizar algunas abreviaturas, como "Co" para "Compañía" e "Inc" para "Incorporado", utilizando la función de cadena IZQUIERDA para truncar los caracteres adicionales. Otras abreviaturas, como "Assn" para "Asociación", pueden ser se soluciona estableciendo una guía de estilo de entrada de datos y luego escribiendo un programa para buscar y corregir formatos incorrectos.
- Para cadenas de números, como códigos postales donde algunas entradas incluyen el sufijo ZIP + 4 y otras no, puede usar nuevamente la función de cadena IZQUIERDA para reconocer y hacer coincidir solo los códigos postales principales. Para que Excel reconozca valores numéricos cercanos pero no iguales, puede usar la función REDONDEAR para redondear los valores cercanos al mismo número y hacerlos coincidir.
- Los espacios adicionales, como escribir dos espacios entre palabras en lugar de uno, se pueden eliminar utilizando la función TRIM.
Paso 4. Cree columnas para la fórmula de comparación
Así como tuvo que crear columnas para los identificadores de lista, deberá crear columnas para la fórmula que realiza la comparación por usted. Necesitará una columna para cada lista.
Querrá etiquetar estas columnas con algo como "¿Falta?"
Paso 5. Ingrese la fórmula de comparación en cada celda
Para la fórmula de comparación, usará la función COINCIDIR anidada dentro de otra función de Excel, ISNA.
- La fórmula toma la forma de "= ISNA (COINCIDIR (G3, $ L $ 3: $ L $ 14, FALSE))", donde una celda de la columna de identificadores de la primera lista se compara con cada uno de los identificadores de la segunda lista para ver si coincide con uno de ellos. Si no coincide, falta un registro y la palabra "VERDADERO" se mostrará en esa celda. Si coincide, el registro está presente y se mostrará la palabra "FALSE". (Al ingresar la fórmula, no incluya las comillas adjuntas).
- Puede copiar la fórmula en las celdas restantes de la columna de la misma manera que copió la fórmula del identificador de celda. En este caso, solo cambia la referencia de celda para la celda del identificador, ya que colocar los signos de dólar delante de las referencias de fila y columna para la primera y última celda en la lista de los identificadores de la segunda celda los convierte en referencias absolutas.
- Puede copiar la fórmula de comparación de la primera lista en la primera celda de la columna de la segunda lista. Luego tendrá que editar las referencias de celda para que "G3" se reemplace con la referencia de la primera celda de identificador de la segunda lista y "$ L $ 3: $ L $ 14" se reemplace con la primera y la última celda de identificador de la segunda lista. (Deje los signos de dólar y los dos puntos en paz). Luego puede copiar esta fórmula editada en las celdas restantes en la fila de comparación de la segunda lista.
Paso 6. Ordene las listas para ver los valores que no coinciden más fácilmente, si es necesario
Si sus listas son grandes, es posible que deba ordenarlas para juntar todos los valores que no coinciden. Las instrucciones de los subpasos siguientes convertirán las fórmulas en valores para evitar errores de recálculo y, si sus listas son grandes, evitarán un tiempo de recálculo prolongado.
- Arrastre el mouse sobre todas las celdas de una lista para seleccionarla.
- Seleccione Copiar en el menú Editar en Excel 2003 o en el grupo Portapapeles de la cinta Inicio en Excel 2007 o 2010.
- Seleccione Pegado especial en el menú Editar en Excel 2003 o en el botón desplegable Pegar en el grupo Portapapeles de la cinta de Inicio de Excel 2007 o 2010s.
- Seleccione "Valores" de la lista Pegar como en el cuadro de diálogo Pegado especial. Haga clic en Aceptar para cerrar el cuadro de diálogo.
- Seleccione Ordenar en el menú Datos en Excel 2003 o el grupo Ordenar y filtrar de la cinta de Datos en Excel 2007 o 2010.
- Seleccione "Fila de encabezado" de la lista "Mi rango de datos tiene" en el cuadro de diálogo Ordenar por, seleccione "¿Falta?" (o el nombre que realmente le dio al encabezado de la columna de comparación) y haga clic en Aceptar.
- Repita estos pasos para la otra lista.
Paso 7. Compare visualmente los elementos que no coinciden para ver por qué no coinciden
Como se señaló anteriormente, Excel está diseñado para buscar coincidencias de datos exactas a menos que lo configure para buscar aproximados. Su no coincidencia podría ser tan simple como una transposición accidental de letras o dígitos. También podría ser algo que requiera una verificación independiente, como verificar si los activos enumerados deben informarse en primer lugar.
Método 2 de 2: formato condicional con COUNTIF
Paso 1. Copie las listas de datos en una sola hoja de trabajo
Paso 2. Decida en qué lista desea resaltar los registros coincidentes o no coincidentes
Si desea resaltar registros en una sola lista, probablemente desee resaltar los registros exclusivos de esa lista; es decir, registros que no coinciden con los registros de la otra lista. Si desea resaltar registros en ambas listas, querrá resaltar registros que coincidan entre sí. Para los propósitos de este ejemplo, asumiremos que la primera lista toma las celdas G3 a G14 y la segunda lista toma las celdas L3 a L14.
Paso 3. Seleccione los elementos de la lista en los que desea resaltar elementos únicos o coincidentes
Si desea resaltar elementos coincidentes en ambas listas, tendrá que seleccionar las listas una a la vez y aplicar la fórmula de comparación (descrita en el siguiente paso) a cada lista.
Paso 4. Aplicar la fórmula de comparación adecuada
Para hacer esto, tendrá que acceder al cuadro de diálogo Formato condicional en su versión de Excel. En Excel 2003, lo hace seleccionando Formato condicional en el menú Formato, mientras que en Excel 2007 y 2010, hace clic en el botón Formato condicional en el grupo Estilos de la cinta Inicio. Seleccione el tipo de regla como "Fórmula" e ingrese su fórmula en el campo Editar la descripción de la regla.
- Si desea resaltar registros exclusivos de la primera lista, la fórmula sería "= CONTAR. SI ($ L $ 3: $ L $ 14, G3 = 0)", con el rango de celdas de la segunda lista representada como valores absolutos y la referencia a la primera celda de la primera lista como valor relativo. (No ingrese las comillas de cierre).
- Si desea resaltar registros exclusivos de la segunda lista, la fórmula sería "= CONTAR. SI ($ G $ 3: $ G $ 14, L3 = 0)", con el rango de celdas de la primera lista representada como valores absolutos y la referencia a la primera celda de la segunda lista como valor relativo. (No ingrese las comillas de cierre).
- Si desea resaltar los registros de cada lista que se encuentran en la otra lista, necesitará dos fórmulas, una para la primera lista y otra para la segunda. La fórmula para la primera lista es "= CONTAR. SI ($ L $ 3: $ L $ 14, G3> 0)", mientras que la fórmula para la segunda lista es CONTAR. SI ($ G $ 3: $ G $ 14, L3> 0) ". Como mencionado anteriormente, selecciona la primera lista para aplicar su fórmula y luego selecciona la segunda lista para aplicar su fórmula.
- Aplique el formato que desee para resaltar los registros que se marcan. Haga clic en Aceptar para cerrar el cuadro de diálogo.
Video: al utilizar este servicio, es posible que cierta información se comparta con YouTube
Consejos
- En lugar de usar una referencia de celda con el método de formato condicional CONTAR. SI, puede ingresar un valor para buscar y marcar una o más listas para las instancias de ese valor.
- Para simplificar los formularios de comparación, puede crear nombres para su lista, como "Lista1" y "Lista2". Luego, al escribir las fórmulas, estos nombres de lista pueden sustituir los rangos de celdas absolutos utilizados en los ejemplos anteriores.
