Para resumir resultados de hojas de cálculo independientes y elaborar informes con ellos, puede consolidar los datos de cada hoja en una hoja de cálculo principal. Las hojas pueden estar en el mismo libro que la hoja de cálculo principal o en otros libros. Cuando consolida datos, los ensambla para que pueda actualizarlos y agregarlos más fácilmente según sea necesario.
Por ejemplo, si tiene una hoja de cálculo de gastos para cada una de las oficinas regionales, puede usar la consolidación para reunir estas cifras en una hoja de cálculo maestra de gastos corporativos. Esta hoja de cálculo maestra también puede contener las ventas totales y los promedios, los niveles de inventario actuales y los productos más vendidos de toda la empresa.
Recomendación
Si consolida datos con frecuencia, podría resultar útil crear nuevas hojas de cálculo a partir de una plantilla de hoja de cálculo que use un diseño coherente. Para obtener más información sobre las plantillas, consulte: Crear una plantilla. Este también es un momento ideal para configurar la plantilla con tablas de Excel.
Formas de consolidar datos
Hay dos formas de consolidar los datos, ya sea por posición o por categoría.
Consolidación por posición: los datos de las áreas de origen tienen el mismo orden y usan las mismas etiquetas. Use este método para consolidar datos de una serie de hojas de cálculo, como hojas de cálculo de presupuestos de departamento que se han creado a partir de la misma plantilla.
Consolidación por categoría: Cuando los datos en las áreas de origen no se organizan en el mismo orden pero usan las mismas etiquetas. Use este método para consolidar datos de una serie de hojas de cálculo que tienen diferentes diseños pero tienen las mismas etiquetas de datos.
- Consolidar datos por categoría es similar a crear una tabla dinámica. En cambio, con una tabla dinámica, puede reorganizar fácilmente las categorías. Considere la posibilidad de crear una tabla dinámica si necesita una consolidación más flexible por categoría.
Nota
Los ejemplos de este artículo se crearon con Excel 2016. Aunque la vista puede diferir si usa otra versión de Excel, los pasos son los mismos.
Cómo consolidar
Siga estos pasos para consolidar varias hojas de cálculo en una hoja de cálculo maestra:
Si aún no lo ha hecho, configure los datos en cada hoja constituyente mediante los siguientes pasos:
- Asegúrese de que cada rango de datos esté en formato de lista. Cada columna debe tener una etiqueta (encabezado) en la primera fila y contener datos similares. No debe haber filas o columnas en blanco en ninguna parte de la lista.
- Coloque cada rango en una hoja de cálculo independiente, pero no escriba nada en la hoja de cálculo maestra en la que planee consolidar los datos. Excel hará esto por usted.
- Asegúrese de que cada rango tenga el mismo diseño.
En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.
Nota
Para evitar sobrescribir los datos existentes en la hoja de cálculo maestra, asegúrese de dejar suficientes celdas a la derecha y debajo de esta celda para los datos consolidados.
Haga clic en Consolidar datos> (en el grupo Herramientas de datos).
En el cuadro Función , haga clic en la función de resumen que desee que Excel use para consolidar los datos. La función predeterminada es SUMA.
A continuación se muestra un ejemplo en el que se han elegido tres intervalos de la hoja de cálculo:
Seleccione los datos.
A continuación, en el cuadro Referencia , haga clic en el botón Colapsar para reducir el panel y seleccionar los datos en la hoja de cálculo.
Haga clic en la hoja de cálculo que contiene los datos que desea consolidar, seleccione los datos y, a continuación, haga clic en el botón Expandir cuadro de diálogo a la derecha para volver al cuadro de diálogo Consolidar .Si una hoja de cálculo que contiene datos que necesita consolidar se encuentra en otro libro, haga clic en Examinar para buscar ese libro. Después de localizar y hacer clic en Aceptar, Excel escribirá la ruta del archivo en el cuadro de referencia y anexará un signo de exclamación a esa ruta de acceso. A continuación, puede continuar seleccionando otros datos.
Este es un ejemplo en el que se han seleccionado tres rangos de la hoja de cálculo:
En la ventana emergente Consolidar , haga clic en Agregar. Repita este procedimiento para agregar todos los rangos que consolida.
Actualizaciones automáticas frente a actualizaciones manuales: Si quiere que Excel actualice automáticamente la tabla de consolidación cuando cambien los datos de origen, simplemente active la casilla Crear vínculos a datos de origen . Si esta casilla permanece desactivada, puede actualizar la consolidación manualmente.
Nota
- No puede crear vínculos si las áreas de origen y destino están en la misma hoja.
- Si necesita cambiar la extensión de un rango (o reemplazar un rango), haga clic en el rango en la ventana emergente Consolidar y actualícelo siguiendo los pasos anteriores. De este modo, se creará una nueva referencia de rango, por lo que tendrá que eliminar la anterior antes de volver a consolidar. Simplemente elija la referencia anterior y presione la tecla Suprimir.
Haga clic en Aceptar y Excel generará la consolidación automáticamente. Opcionalmente, puede aplicar formato. Solo es necesario dar formato una vez, a menos que se vuelva a ejecutar la consolidación.
- Los rótulos que no coincidan con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.
- Asegúrese de que las categorías que no desee consolidar tengan etiquetas únicas que aparezcan en un solo intervalo de origen.
Usar un informe de tabla dinámica para consolidar datos
Si los datos para consolidar están en celdas diferentes de hojas de cálculo diferentes:
Escriba una fórmula con referencias de celda a las otras hojas de cálculo, una por cada hoja de cálculo independiente. Por ejemplo, para consolidar datos de hojas de cálculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de cálculo maestra, tendría que escribir lo siguiente:
Recomendación
Para escribir una referencia de celda, como Ventas! B4: en una fórmula sin escribir, escriba la fórmula hasta el punto donde necesite la referencia, haga clic en la pestaña de la hoja de cálculo y luego haga clic en la celda. Excel completará automáticamente el nombre de la hoja y la dirección de celda. NOTA: En estos casos, las fórmulas pueden ser propensas a errores, ya que es muy fácil seleccionar accidentalmente una celda incorrecta. También puede ser difícil detectar un error después de escribir una fórmula compleja.
Si los datos a consolidar están en las mismas celdas en hojas de cálculo diferentes:
Escriba una fórmula con una referencia 3D que use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 de Ventas a Marketing inclusive, en la celda E5 de la hoja de cálculo principal debería escribir lo siguiente:
¿Necesitas más ayuda?
Siempre puede preguntar a un experto en Excel Tech Community u obtener soporte técnico en Comunidades.
Consulte también
Información general sobre fórmulas en Excel
Cómo evitar la ruptura de las fórmulas
Buscar y corregir errores en fórmulas
Métodos abreviados de teclado y teclas de función en Excel