Consolidar datos en varias hojas de cálculo

Para resumir y registrar resultados de hojas de cálculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de cálculo maestra. Las hojas pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea más fácil actualizarlos y agregarlos de una forma periódica o específica.

Por ejemplo, si tiene una hoja de cálculo de cifras de gastos para cada una de las oficinas regionales, podría utilizar una consolidación para resumir estas cifras en una hoja de cálculo de gastos corporativa. Esta hoja de cálculo maestra podría contener totales de ventas y promedios, niveles de inventario actuales y los productos más vendidos de toda la organización.

Para consolidar datos, utilice el comando Consolidar del grupo Herramientas de datos de la ficha Datos.


¿Qué desea hacer?

Si desea...

Entonces…

Organizar los datos de todas las hojas de cálculo en orden y ubicación idénticos

Consolide por posición

Organizar los datos de forma diferente en las hojas de cálculo independientes pero utilizar los mismos rótulos de fila y de columna para que la hoja de cálculo maestra pueda hacer coincidir los datos

Consolide por categorías

Utilizar fórmulas con referencias de celdas o referencia 3D a otras hojas de cálculo que esté combinando porque no tiene una posición o categoría coherente en la que basarse

Consolide por fórmula

Usar un informe de tabla dinámica en lugar de una consolidación

Usar un informe de tabla dinámica para consolidar datos

Consolide por posición

  1. Configure los datos que se van a consolidar en cada una de las hojas de cálculo independientes.

    Cómo configurar los datos

    • Asegúrese de que cada rango de datos está en formato de referencia 3Dlista: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.

    • Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.

    • Asegúrese de que cada rango tiene el mismo diseño.

    • Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre.

  2. Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados en la hoja de cálculo maestra.

    Nota    Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

  3. En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.

    Imagen de la cinta de Excel

  4. En el cuadro Función, haga clic en la referencia 3Dlistafunción de resumen que desea que utilice Microsoft Office Excel para consolidar los datos.

  5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

    La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

  6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.

  7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:

    • Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.

        Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

    • Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

  8. Deje en blanco las casillas de Usar rótulos en. Excel no copia los rótulos de fila o columna de los rangos de origen a la consolidación. Si desea rótulos para los datos consolidados, cópielos de uno de los rangos de origen o introdúzcalos manualmente.

Volver al principio

Consolide por categorías

  1. Configure los datos que se van a consolidar en cada una de las hojas de cálculo independientes.

    Cómo configurar los datos

    • Asegúrese de que cada rango de datos está en formato de referencia 3Dlista: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco.

    • Coloque cada rango en una hoja de cálculo diferente. No ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación.

    • Asegúrese de especificar rótulos de categoría exactamente iguales, incluidas mayúsculas y minúsculas, en todas las áreas de origen. Por ejemplo, los rótulos Prom. anual y Promedio anual son diferentes y no se consolidarán.

    • Asigne un nombre a cada rango: seleccione todo el rango y, luego, en la ficha Fórmulas, en el grupo Celdas con nombre, haga clic en la flecha situada junto a Asignar nombre a un rango y escriba un nombre para el rango en el cuadro Nombre.

  2. Haga clic en la celda superior izquierda del área donde desee que aparezcan los datos consolidados en la hoja de cálculo maestra.

    Nota    Asegúrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidación. El comando Consolidar rellena el área según proceda.

  3. En el grupo Herramientas de datos de la ficha Datos, haga clic en Consolidar.

    Imagen de la cinta de Excel

  4. En el cuadro Función, haga clic en la referencia 3Dlistafunción de resumen que desea que utilice Excel para consolidar los datos.

  5. Si la hoja de cálculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuación, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar.

    La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación.

  6. Escriba el nombre que le asignó al rango y, a continuación, haga clic en Agregar. Repita este paso para cada rango.

  7. Decida cómo desea actualizar la consolidación. Siga uno de los procedimientos siguientes:

    • Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen, active la casilla de verificación Crear vínculos con los datos de origen.

        Solamente puede activar esta casilla de verificación si la hoja de cálculo se encuentra en otro libro. Cuando active esta casilla de verificación ya no podrá cambiar las celdas y los rangos que se incluyen en la consolidación.

    • Para configurar la consolidación de modo que puede actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla de verificación Crear vínculos con los datos de origen.

  8. Active las casillas de verificación de Usar rótulos en que indican donde se encuentran los rótulos en los rangos de origen, que puede ser: la Fila superior, la Columna izquierda o ambas.

    Notas  

    • 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 tienen rótulos únicos que aparecen solamente en un rango de origen.

Volver al principio

Consolide por fórmula

  1. En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que desee para los datos de consolidación.

  2. Haga clic en la celda en que desea incluir los datos de consolidación.

  3. Escriba una fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de cálculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes:

    Si los datos que se van a consolidar están en celdas diferentes de otras hojas de cálculo    

    • 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:

      Fórmula para consolidar celdas de tres hojas de cálculo que usa referencias de celda

      Sugerencia    Para especificar una referencia de celda como Ventas3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda.

      Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo    

    • Escriba una fórmula con una referencia 3D que utilice una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta Marketing inclusive, en la celda A2 de la hoja de cálculo maestra tendría que escribir lo siguiente:

      Fórmula para consolidar celdas de tres hojas de cálculo que usa referencias 3D

Nota   Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.

Volver al principio

Usar un informe de tabla dinámica para consolidar datos

Puede crear un referencia 3Dlistafunción de resumeninforme de tabla dinámica para rangos de consolidación múltiples. Este método es similar a consolidar por categoría, pero ofrece más flexibilidad para reorganizar las categorías. Para obtener más información, vea el tema sobre cómo consolidar hojas de cálculo múltiples en un informe de tabla dinámica.

Volver al principio

Se aplica a: Excel 2007



¿Le ha sido útil esta información?

No

¿Cómo podemos mejorarlo?

255 caracteres restantes

Para proteger su privacidad, no incluya información de contacto en sus comentarios. Revisar política de privacidad.

¡Gracias por sus comentarios!

Recursos de soporte técnico

Cambiar idioma