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:



21 comments:

Anónimo,  01 enero, 2010 23:13  

Gracias don Jorge, para mi solo una alma amorosa del saber puede expresar didáctica tan plena. Felicidades.

Anónimo,  19 enero, 2010 05:58  

Cordial saludo y muchas gracias por tan excelente aporte

Avalderrama

Anónimo,  22 febrero, 2010 18:04  

Desde que conoci sus cursos no me he cansado de admirar su capacidad para hacer entender los temas tan facilmente.
muchas gracias

Anónimo,  17 marzo, 2010 17:03  

Es un valioso aporte, me he ahorrado 2 horas de arduo trabajo para lograrlo en un tiempo record de 5 minutos.- inimaginable hace unos dias, hoy es posible.

Gracias
Jbarrios

Anónimo,  12 abril, 2010 20:33  

Estimado Jorge, admiro tu capacidad de poder resolver cada inquietud que acá se te presentan, me gusta mucho tu BLOG y mil gracias por esa paciencia que tienes para revisar cada caso y resolverlo.
Un saludo desde guatemala.

gabriela,  18 mayo, 2010 15:45  

Buenos días, recientemente he descubierto este blog y la verdad no me canso de leerlo. Me parece excelente, no solo los contenidos, sino principalmente la forma tan didáctica de explicar las cosas.
Ahora viene una consulta con respecto a esta entrada. Si yo tengo una hoja de un libro con una consolidacion de datos ya realizada, puedo volverla a consolidar con una nueva hoja?
Semanalmente emito informes de cuentas corrientes y me gustaría tener en una hoja en donde las filas son los clientes y las columnas la fecha, el saldo de cada uno. NO se si me expliqué bien.

Desde ya, muchas gracias por su atención, y felicitaciones por el blog, nuevamente.

Gabriela.

Jorge L. Dunkelman 18 mayo, 2010 19:18  

Hola Gabriela,
supongo que si, nunca lo intenté. ¿Has considerado usar tablas dinámicas? Me parece más apropiado para tu tarea.

Anónimo,  23 mayo, 2010 07:12  

gracias maestro desde Chile

Anónimo,  01 julio, 2010 01:25  

Saludos;

Me parece excelente este trabajo, y admiro su capacidad para desarrollar y seguir desarrollando cuestiones prácticas y apegadas a la realidad de lo que en verdad se peude hacer con Excel, seguiré su ejemplo y comenzaré mi propio blog, para publicar tambien mis experiencias y que espero podamos compartir proximamente. A.P.

Anónimo,  20 julio, 2010 17:41  

Jorge, una vez que he obtenido la hoja 3 con la comparación...¿hay alguna manera de refrescar los datos de la hoja 3 si aplico algún cambio en hoja 1 o hoja 2 a posteriori?

Jorge L. Dunkelman 20 julio, 2010 21:01  

Si, volviendo a activar la consolidación de datos. Si se han agregado (o quitado) filas de las tablas hay que corregir los rango pertinentes.
También existe la posibilidad de crear vínculos con los datos de origen.

manolo 15 noviembre, 2011 15:03  

Don Jorge.
Muchas gracias por su desinteresada colaboración.
Sigo los mismos pasos de este ejemplo, pero al consolidar en vez de que me aparezcan 3 columnas, me aparecen sólo 2 (sucursal y la suma de las ventas por sucursal). Qué puede ser?
Muchas gracias,
manuel.

Jorge L. Dunkelman 16 noviembre, 2011 07:21  

Asegurate de apretar el botón Agregar después de seleccionar cada uno de los rangos.

Jaime 09 febrero, 2012 18:41  

Jorge,

Me valiste un contrato fijo en Paris hace algunos meses y ahora me has ayudado mucho a un ascenso. Mil gracias por tu desinteresada ayuda. Un abrazo.

Marco Ariel Mávil Torres 22 junio, 2012 23:55  

Sigo los pasos al pié de la letra pero me manda un mensaje que dice "No se consolidó ningun dato" y no hace nada :S

Jorge L. Dunkelman 23 junio, 2012 20:31  

Obviamente estás comiento algún error en el proceso.

Anónimo,  12 noviembre, 2012 21:43  

excelente los aportes a todos los interesados

Anónimo,  17 noviembre, 2012 17:51  

Excelente, sus explicaciones se entienden a la perfección. Gracias

Diego Paniagua Murillo 24 julio, 2013 23:58  

tengo que hacer una tabla comparativa de una cotización de tres proveedores diferentes, necesito que me marque con color la celda con la cantidad mas baja y que en una celda nueva me ponga el monto menor de las tres

Jorge Dunkelman 25 julio, 2013 07:17  

Suponiendo que las cotizaciones están en las celdas A1,B1, y C1, para obtener el mínimo en la celda D1 usamos

=MIN(A1:C1)

Para marcar la celda con el valor menor usamos formato condicional.

Anónimo,  23 noviembre, 2013 16:36  

Súper muy buen truco tuve un error y sólo sumaba, tuve que consultar otros tutoriales pero dado que ninguno me hablaba de usar consolidar para comparar, regreso a mi ejemplo y observo que Ventas1 era el mismo nombre para ambas pestañas no nombre la otra columna como Ventas 2. Muchísimas gracias muyyyy útil

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP