Hola! ¿Cómo han estado?
Recientemente uno de nuestros usuarios nos hizo la siguiente pregunta: ¿Que podemos hacer si por ejemplo tenemos 20 libros de MS Excel® y tenemos que consolidar todos los valores contenidos en ellos en un solo libro? La pregunta es muy interesante ya que existen varias maneras de hacerlo no obstante en MS Excel® existe un comando que puede realizar dicha operación de manera eficiente, hacemos referencia en este caso al comando Consolidar, veamos a continuación una breve descripción de cómo emplear este útil comando para consolidar datos de varios libros en uno solo.
El comando Consolidar lo hayamos (para el caso de MS Excel® 2007) en la Cinta de opciones dentro de la ficha Datos en la sección Herramientas de datos tal como se ve en la imagen anexa:
En MS Excel® 2003 lo encontramos en la Barra de herramientas/ Datos/ Consolidar.
Ahora veamos de que manera aplicar el comando mediante un ejercicio, supongamos que tenemos una empresa que tiene sede en tres países distintos (Venezuela, España y Argentina) y la suma total de las ventas se calculan a partir de los datos de tres archivos diferentes donde cada uno contiene la totalidad de las ventas por cada país, es en esta parte donde entra la utilidad del comando Consolidar pues mediante el mismo vamos a obtener los totales de la suma de las ventas de todos los países en un solo libro.
Teniendo en cuenta que son 3 libros cada uno llamado de la siguiente manera:
- Ventas_Venezuela
- Ventas_España
- Ventas_Argentina
Debemos crear un nuevo libro donde vamos a consolidar los totales de las ventas realizadas en esos países, ese archivo lo vamos a nombrar Ventas_Totales. Antes de realizar la consolidación de los valores debemos tener abiertos tanto los libros que contienen las ventas realizadas en los tres países como también el libro de consolidación. Preferiblemente el archivo donde vamos a consolidar la información (Ventas_Totales) debe tener el mismo encabezado que el resto de los archivos.
El primer paso que debemos realizar es posicionarnos en la celda a partir de donde queremos que se consoliden los datos en el libro Ventas_Totales para este caso seleccionamos la celda B2 ya que es donde inicia el rango que vamos a tomar en cuenta en el resto de los libros, lo vemos mediante la siguiente imagen:
En seguida vamos a la Cinta de opciones y en la ficha Datos sección Herramientas de datos seleccionamos el comando Consolidar en donde se desplegara el siguiente cuadro de dialogo:
Explicamos algunos detalles que debemos conocer de las opciones disponibles en el cuadro de dialogo:
Por defecto en Función dentro de la lista desplegable aparece la función Suma para este caso se deja tal cual ya que se está buscando una sumatoria total de ventas realizadas en tres archivos no obstante adicionalmente disponemos de 10 funciones mas con las que podemos trabajar.
En el Campo Referencia es a partir de donde vamos a seleccionar el rango celdas de los otros libros que deseamos consolidar.
En Todas las referencias vamos a visualizar la totalidad de las referencias que tenga el libro.
El Botón Agregar permite agregar una referencia y el botón Eliminar permite suprimir una referencia seleccionada.
En Usar rótulos: Aparecen dos casillas de verificación en este cuadro de diálogo, indicativos de la inclusión de rótulos en la fila superior y/o en la columna izquierda, ya que se pueden dar los dos casos de manera simultánea. Al marcar estas casillas estamos informando al programa de que no deseamos que los datos incluidos en la fila superior y/o en la columna izquierda se tengan en consideración a la hora de realizar las operaciones correspondientes.
En Crear vínculos con los datos de origen: Es importante activar esta casilla si queremos que los datos originales queden vinculados con los datos consolidados, de manera que si en las hojas que contienen los datos de origen se realiza algún tipo de modificación, la hoja resumen actualizará los datos consolidados en la medida que sea necesario. También esta opción crea en la hoja consolidada un esquema que permite identificar el origen de cada uno de los datos consolidados, es decir, de dónde proviene ese dato de resumen en concreto.
Una vez revisados estos puntos procedemos a realizar la consolidación de los totales, con el cuadro de dialogo abierto en el campo Referencia vamos a seleccionar el rango de las celdas que deseamos consolidar del primer libro (Ventas_Venezuela) a partir de la celda B2 hasta la celda G4 tal como se ve en la imagen anexa:
Las casillas Fila Superior, Columna izquierda y Crear vínculos con los datos de origen no la vamos a seleccionar para la consolidación de estos valores.
Por último hacemos clic en el botón Aceptar y los resultados se consolidarán de manera exitosa en el libro Ventas_Totales.
El comando Consolidar es de suma utilidad ya que si tenemos un gran número de libros y queremos consolidarlos todos en uno solo el mismo nos ofrece la posibilidad de hacer esta operación.
Les dejamos los archivos en formato MS Excel® 2007 y MS Excel® 2003 con el ejemplo citado en esta publicación.
¿Cómo se puede colocar una máscara como las de Access a una base de datos en excel?
Gracias
Hola Giovanni
¡Bienvenido!
Siguiendo un poco la definición de mascara de entrada de Access:
“Esta propiedad permite establecer limitaciones con el fin de controlar cada uno de los caracteres, y separadores fijos, que se pueden introducir en el campo, mediante un patrón que se diseñe a tal efecto.”
Debemos considerar algunos puntos importantes:
-MS Excel® es un programa que se concibió como una hoja para realizar cálculos, no es de uso frecuente aplicar “mascaras/formatos/ en celdas de manera previa” a la entrada de los datos (reales).
-Una solución alternativa y simple a este particular es que utilices el comando de Validación de datos (Cinta de Opciones: Fichas Datos/ Validación de datos MS Excel 2007® y Barra de herramientas: Datos/ Validación para MS Excel 2003®) y así forzar al usuario a mantener un estilo de celdas preestablecido y bloquear la entrada de datos no validos.
-No obstante a todo esto se pueden elaborar soluciones mediante Visual Basic que pueden ser aplicados a una base de datos en específico.
Cualquier duda adicional dispones de nuestro correo: info@excellentias.com
Saludos
El Equipo EXCELLENTIAS.COM
[...] 1.- Consolidar datos de varios libros en Excel. [...]
Hola…
Hay alguna forma de que el numero de libros a consolidar sea variable??
O siempre tengo que indicar “en duro” los libros de origen??
Gracias
Hola Rick, un saludo y gracias por tu consulta.
A través de esta opción predeterminada de Excel siempre debes indicar cuales son los libros de origen.
No obstante es posible programar a nivel de macros en VBA alguna rutina más abierta, aunque de mayor complejidad.
Saludos y éxito,
Qué hacer si tengo que elaborar constancias de terminación de estudios de todos los alumnos que egresan de una escuela. Tengo una plantilla del Certificado (en Excel) con las celdas a rellenar, y tengo, por otro lado, una base de datos con los nombres, las materias, calificaciones de cada una, folio, etc…en un archivo diferente (también en Excel)…
No sé si fui claro.
Agradezco de antemano la atención
Hola José Luis primero que todo disculpa la demora en mi respuesta. Al momento manejo la comunidad EXCELLENTIAS de la mano de otras responsabilidades laborales que me han tenido algo ocupado para atender puntualmente las dudas de los usuarios, tal como lo merecen. En relación con tu consulta te indico que si en la base de datos posees algún campo clave que te permita relacionar ambas hojas será muy sencillo hacer lo que deseas. Un campo clave es un campo con una característica especial (la clave) que hace que ese campo no se repita en ningún registro de la Base de Datos, permitiéndonos identificar y localizar un registro de manera ágil y organizada. En nuestro caso este campo debe ser uno que no se repita entre alumnos, tal como la cédula de identidad, un número de ID, número de seguro social, o simplemente un código único que identifique a cada alumno.
Disponiendo de este campo puedes emplear algunas de las funciones de búsqueda y referencia de Excel como BUSCAR, BUSCARV o BUSCARH para recuperar fácilmente los registros asociados a cada alumno, empleándolo como argumento principal.
En nuestro portal puedes encontrar tutoriales detallados entorno a como emplear estas funciones:
http://excellentias.com/2010/01/funcion-excel-buscar/
http://excellentias.com/2009/10/funcion-excel-buscarv/
http://excellentias.com/2010/01/funcion-excel-buscarh/
Si aun con esta información necesitas de algún apoyo adicional no dudes en contactarme, si es posible enviándome algun libro de ejemplo donde pueda ver como esta organizada tu base de datos.
Espero que te sea aun de utilidad, un abrazo y te deseo el mayor de los éxitos.