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.
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
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
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
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
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
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:
Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.
Technorati Tags: MS Excel
Gracias don Jorge, para mi solo una alma amorosa del saber puede expresar didáctica tan plena. Felicidades.
ResponderBorrarCordial saludo y muchas gracias por tan excelente aporte
ResponderBorrarAvalderrama
Desde que conoci sus cursos no me he cansado de admirar su capacidad para hacer entender los temas tan facilmente.
ResponderBorrarmuchas gracias
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.
ResponderBorrarGracias
Jbarrios
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.
ResponderBorrarUn saludo desde guatemala.
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.
ResponderBorrarAhora 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.
Hola Gabriela,
ResponderBorrarsupongo que si, nunca lo intenté. ¿Has considerado usar tablas dinámicas? Me parece más apropiado para tu tarea.
gracias maestro desde Chile
ResponderBorrarSaludos;
ResponderBorrarMe 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.
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?
ResponderBorrarSi, volviendo a activar la consolidación de datos. Si se han agregado (o quitado) filas de las tablas hay que corregir los rango pertinentes.
ResponderBorrarTambién existe la posibilidad de crear vínculos con los datos de origen.
Don Jorge.
ResponderBorrarMuchas 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.
Asegurate de apretar el botón Agregar después de seleccionar cada uno de los rangos.
ResponderBorrarJorge,
ResponderBorrarMe 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.
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
ResponderBorrarObviamente estás comiento algún error en el proceso.
ResponderBorrarexcelente los aportes a todos los interesados
ResponderBorrarExcelente, sus explicaciones se entienden a la perfección. Gracias
ResponderBorrartengo 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
ResponderBorrarSuponiendo que las cotizaciones están en las celdas A1,B1, y C1, para obtener el mínimo en la celda D1 usamos
ResponderBorrar=MIN(A1:C1)
Para marcar la celda con el valor menor usamos formato condicional.
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
ResponderBorrar