Mostrando las entradas con la etiqueta Comparar Listas. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Comparar Listas. Mostrar todas las entradas

Comparar listas con Excel y Access

miércoles, enero 01, 2014

Una tarea frecuente es comparar listas con Excel, ya sea para detectar valores duplicados o valores faltantes en una u otra lista.

Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).

Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)

El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.

Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.

Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.

cuadernos con listas de países


Nos piden crear los siguientes reportes:

1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.

Para dificultar las cosas las listas no están ordenadas.


Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos

nueva base de datos en Access


cambiamos el nombre por defecto y elegimos la carpeta



Al terminar el proceso, veremos esto en la pantalla:



Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.

Segundo paso: insertar las tablas de datos en la base de datos.

Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel



Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access



Al apretar Aceptar se abre el asistente para importación de hojas de cálculo



Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"



Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".

En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.


En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"



En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)



y al apretar Finalizar, Access creará la tabla.



El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.

Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas



Tercer paso: crear las consultas.

Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.

En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"


En el asistente elegimos la opción "Búsqueda de no coincidentes"



y apretamos Continuar

En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual





En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>



El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).



Apretamos Finalizar y "abracadabra"!!



Repetimos el proceso para comparas Lista B con A



Cuarto paso (opcional): exportar las consultas a hojas de Excel.

En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos



Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).



Todo lo que queda por hacer es apretar Aceptar.

Seguir leyendo...

Comparación rápida de tablas con Datos-Consolidar

miércoles, noviembre 11, 2009

Una tarea común en Excel es comparar tablas (o listas) para encontrar diferencias o cambios. En varias notas de este blog hemos mostrado distintas técnicas para hacerlo. Existen varios complementos (Add Ins), algunos gratuitos, para este tipo de tareas.

Hoy mostraremos un pequeño truco.

Cuando queremos comparar qué datos han sido agregados, quitados o cambiados, una posibilidad es usar funciones de búsqueda como BUSCARV o COINCIDIR. Pero esta técnica tiene el inconveniente que sólo puede encontrar lo que existe en la lista de búsqueda o informarnos que lo que buscamos no se encuentra (o no coincide) con un resultado #NA.

Con Datos-Consolidar podemos ir más lejos. Supongamos que recibimos un reporte de ventas y unos días más tarde una segunda versión.




comparar datos con Excel
comparar datos con Excel














Como podemos apreciar, en el informe 1 faltan las sucursales 3, 4 y 5; también los datos de las sucursales 1, 2 y 9 son distintos.

El primer paso que damos es cambiar el encabezamiento de la columna B en ambas hojas. En lugar de Ventas pondremos Ventas1 y Ventas2 respectivamente.

El segundo paso es agregar una tercer hoja, “comparación” (podemos darle cualquier nombre que queramos). Seleccionamos la celda A1 de la nueva hoja y abrimos el menú Datos-Consolidar


comparar datos con Excel

En el formulario que se abre marcamos las opciones “Fila Superior” y “Columna Izquierda” de “Usar Rótulos en”; en la ventanilla “Examinar” seleccionamos el rango relevante de la hoja Informe1



Apretamos el botón Agregar y seleccionamos el rango relevante en la hoja Informe2


comparar datos con Excel

Volvemos a apretar Agregar y luego Aceptar. Excel crea en forma automática esta tabla




Podemos ver con facilidad con facilidad todos los cambios entre ambas listas. Con unas fórmulas sencillas tenemos en segundos un informe detallado de las diferencias



comparar datos con Excel

Una complicación puede surgir cuando tenemos más de una columna descriptiva a la izquierda de los datos que queremos comparar. Por ejemplo, si en nuestras tablas además de las sucursales también existe un campo (columna) con la zona



Como Excel consolida en referencia a la columna izquierda, el detalle de las sucursales no es tomado en cuenta. La solución es crear una columna auxiliar concatenando los valores de ambos campos

comparar datos con Excel

Enseguida explicaremos por qué separamos los valores con una coma. Ahora volvemos al proceso de consolidar partiendo de la columna auxiliar



El resultado es el siguiente

comparar datos con Excel

La coma que hemos agregado en la concatenación nos ayuda a separar la columna auxiliar en las dos originales usando Datos-Texto en columnas.

Primero insertamos una columna en blanco entre las columnas A y B


Luego seleccionamos el rango con valores en la columna A y usamos el menú Datos-Texto en columnas con la opción de separadores “coma”



El resultado:

comparar datos con Excel


Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.



Technorati Tags:



Seguir leyendo...

Comparar listas en Excel

viernes, octubre 06, 2006

Una de los temas más corrientes en Excel es el de la comparación de listas, y su "hermano gemelo" la eliminación o prevención de duplicados.
Ya he tratado el tema en diversas oportunidades, por ejemplo
como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas


En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos





Para facilitar nuestro trabajo hemos definido dos nombres

lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11

1 - Encontrar duplicados usando la función COINCIDIR

En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es




Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula

=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")

El resultado es




Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición



La dirección de la celda de referencia debe ser relativa (sin los signos $)

2 – Señalar duplicados usando formato condicional.

Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0





En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0



El resultado es idéntico al método anterior



3 – Generar una lista de valores comunes a ambas listas

Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:

=SI(CONTAR.SI(lista2,A2)>0,A2,"")




Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.

Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en
esta nota.

También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango

=SI(CELDA("contenido",F2)="",0,FILA())




Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).

El próximo paso es usar la función JERARQUIA en la columna I

=JERARQUIA(H2,$H$2:$H$11)

En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.




Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.

El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,

=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))

que nos da esta lista




El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando

=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))

es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.




4 – Generar una lista de valores únicos

Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser

=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))

donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.


Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: ,

Seguir leyendo...

Comparar listas en Excel con Consolidación de Datos, una variante

jueves, marzo 02, 2006

Esta entrada es un complemento a la entrada de ayer sobre el tema. Sucede que un compañero de trabajo, después de leer la nota, me dijo que muy bien, pero él tiene las listas en una sola hoja. Cómo hace, entonces, para comparar entre ellas con el método Datos--->Consolidar?
Supongamos que tenemos estas tres listas en una sola hoja (Comparar Listas en Excelles invito a bajar el archivo con el ejemplo aquí):



Para hacer la comparación con el método explicado ayer (agregamos a cada lista el numero 1, 2 o 4 al lado del nombre de cada miembro), seguimos los siguientes pasos:
1 – Agregamos tres columnas a la izquierda de la hoja, donde se anotarán los resultados de la consolidación
2 – como siempre recomiendo, definimos nombres para los rangos de las tres listas (cómo hacerlo pueden ver en esta nota).



3 – Seleccionamos la celda A1 y activamos el menú Datos--->Consolidar. En la ventanilla "referencia" anotamos los nombres de los rangos (lista_1, lista_2). La forma más práctica de hacerlo es pulsando F3 y eligiendo el nombre.



4 – Pulsamos "aceptar" y obtenemos la tabla de resultados como en el ejemplo de ayer



El significado de los resultados, como a lo indicamos, es el siguiente:
1 = aparece en lista 1
2 = aparece en lista 2
3 = aparece en las dos listas

Para comparar entre las tres listas, definimos un nombre para el rango de la lista 3 (en nuestro ejemplo será lista_3), lo agregamos en el menú de Datos--->Consolidar



Y obtendremos una nueva lista de comparación. Sobre el significado de los resultados vean esta nota.



Categorías: Manejo de Datos_


Technorati Tags: ,


Seguir leyendo...

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP