Consolidar varias hojas de cálculo en una tabla dinámica

Para resumir resultados de rangos de hojas de cálculo independientes y elaborar informes con ellos, puede consolidar los datos de cada rango de las hojas de cálculo independientes en una tabla dinámica en una hoja de cálculo principal. Los rangos de las hojas de cálculo independientes pueden encontrarse en el mismo libro que la hoja de cálculo principal o en uno distinto. Cuando consolida datos, está ensamblándolos para poder actualizar y agregar datos con más facilidad regularmente o según sus necesidades.

El informe de tabla dinámica consolidada resultante puede tener los siguientes campos en la Lista de campos de tabla dinámica, que puede agregar a la tabla dinámica: fila, columna y valor. Además, el informe puede tener hasta un máximo de cuatro campos de filtro de página, denominados Página1, Página2, Página3 y Página4.

Se debería organizar cada rango de datos en formato de tablas de referencias cruzadas, con nombres de fila y columna coincidentes para los elementos que quiera resumir en conjunto. Al especificar los datos para el informe, no incluya filas ni columnas totales de los datos de origen. El ejemplo siguiente muestra cuatro intervalos en formato de tablas de referencias cruzadas.

La consolidación de datos puede usar los campos de página que contengan elementos que representan uno o varios rangos de origen. Por ejemplo, si está consolidando datos de presupuestos de los departamentos de Fabricación, Ventas y Marketing, puede incluir un campo de página en un elemento para mostrar los datos de cada departamento, además de un elemento para mostrar los datos combinados. En el siguiente ejemplo se muestra una tabla dinámica consolidada que tiene un campo de una página y varios elementos seleccionados.

Si es probable que el rango de datos cambie la próxima vez que consolide los datos (es decir, podría cambiar el número de filas), considere la posibilidad de definir un nombre para cada rango de origen en las hojas de cálculo independientes. Después, use esos nombres cuando consolide los intervalos en la hoja de cálculo maestra. Si se amplía un rango de origen, puede actualizar el rango para el nombre en la hoja de cálculo independiente para que incluya los nuevos datos antes de actualizar la tabla dinámica.

Excel también proporciona otras maneras de consolidar datos que funcionan con datos en varios formatos y diseños. Por ejemplo, puede crear fórmulas con referencias 3D y puede usar el comando Consolidar (en la pestaña Datos, en el grupo Herramientas de datos).

Puede usar el Asistente para tablas y gráficos dinámicos para consolidar varios rangos. En el asistente, puede elegir entre no usar ningún campo de página, usar un único campo de página o varios campos de página.

Para combinar los datos de todos los rangos y crear una consolidación sin campos de página, siga estos pasos:

1. Agregue el Asistente para tablas y gráficos dinámicos a la barra de herramientas de acceso rápido. Siga estos pasos:

   1. Haga clic en la flecha que aparece junto a la barra de herramientas y, después, haga clic en Más comandos.

   2. Haga clic en Customize la barra de herramientas de acceso rápido (…) en la esquina inferior izquierda debajo de la cinta de opciones y, a continuación, haga clic en Más comandos.

   3. En Comandos disponibles en, seleccione Todos los comandos.

   4. En la lista, seleccione Asistente para tablas y gráficos dinámicos, haga clic en Agregar y, después, haga clic en Aceptar.

2. Haga clic en una celda en blanco del libro (que no forme parte de una tabla dinámica).

3. Haga clic en el icono del Asistente para tablas y gráficos dinámicos en la Barra de herramientas de acceso rápido.

4. En la página del asistente Paso 1, haga clic en Varios rangos de consolidación y, después, haga clic en Siguiente.

5. En la página del asistente Paso 2a, haga clic en Campos de página personalizados y luego haga clic en Siguiente.

6. En la página del asistente Paso 2b, haga lo siguiente:

   1. Vuelva al libro y seleccione el rango de celdas. Después, en el Asistente para tablas y gráficos dinámicos, haga clic en Agregar.

      Sugerencia: Si el rango de celdas se encuentra en otro libro, abra el libro en primer lugar para que sea más fácil seleccionar los datos.

   2. En ¿Cuántos campos de página desea?, haga clic en 0 y, después, haga clic en Siguiente.

7. En la página del asistente Paso 3, seleccione entre agregar la tabla dinámica a una hoja de cálculo existente o una nueva y, después, haga clic en Finalizar.

Para incluir un único campo de página que tenga un elemento para cada rango de origen más un elemento que consolide todos los rangos, siga este procedimiento:

1. Agregue el Asistente para tablas y gráficos dinámicos a la barra de herramientas de acceso rápido. Siga estos pasos:

   1. Haga clic en la flecha que aparece junto a la barra de herramientas y, después, haga clic en Más comandos.

   2. Haga clic en Customize la barra de herramientas de acceso rápido (…) en la esquina inferior izquierda debajo de la cinta de opciones y, a continuación, haga clic en Más comandos.

   3. En Comandos disponibles en, seleccione Todos los comandos.

   4. En la lista, seleccione Asistente para tablas y gráficos dinámicos, haga clic en Agregar y, después, haga clic en Aceptar.

2. Haga clic en una celda en blanco del libro (que no forme parte de una tabla dinámica).

3. En la página del asistente Paso 1, haga clic en Varios rangos de consolidación y, después, haga clic en Siguiente.

4. En la página del asistente Paso 2a, haga clic en Crear un solo campo de página y luego haga clic en Siguiente.

5. En la página del asistente Paso 2b, haga lo siguiente:

   1. Vuelva al libro y seleccione el rango de celdas. Después, en el Asistente para tablas y gráficos dinámicos, haga clic en Agregar.

      Sugerencia: Si el rango de celdas se encuentra en otro libro, abra el libro en primer lugar para que sea más fácil seleccionar los datos.

6. Haga clic en Siguiente.

7. En la página del asistente Paso 3, seleccione entre agregar la tabla dinámica a una hoja de cálculo existente o una nueva y, después, haga clic en Finalizar.

Puede crear varios campos de página y asignar sus propios nombres de elemento a cada rango de origen. Esto le permite crear consolidaciones completas o parciales. Por ejemplo, un campo de página que consolide Marketing y Ventas independientemente de Fabricación y otro campo de página que consolide los tres departamentos. Para crear una consolidación que use varios campos de página, haga lo siguiente:

1. Agregue el Asistente para tablas y gráficos dinámicos a la barra de herramientas de acceso rápido. Siga estos pasos:

   1. Haga clic en la flecha que aparece junto a la barra de herramientas y, después, haga clic en Más comandos.

   2. Haga clic en Customize la barra de herramientas de acceso rápido (…) en la esquina inferior izquierda debajo de la cinta de opciones y, a continuación, haga clic en Más comandos.

   3. En Comandos disponibles en, seleccione Todos los comandos.

   4. En la lista, seleccione Asistente para tablas y gráficos dinámicos, haga clic en Agregar y, después, haga clic en Aceptar.

2. Haga clic en una celda en blanco del libro (que no forme parte de una tabla dinámica).

3. En la página del asistente Paso 1, haga clic en Varios rangos de consolidación y, después, haga clic en Siguiente.

4. En la página del asistente Paso 2a, haga clic en Campos de página personalizados y luego haga clic en Siguiente.

5. En la página del asistente Paso 2b, haga lo siguiente:

   1. Vuelva al libro y seleccione el rango de celdas. Después, en el Asistente para tablas y gráficos dinámicos, haga clic en Agregar.

      Sugerencia: Si el rango de celdas se encuentra en otro libro, abra el libro en primer lugar para que sea más fácil seleccionar los datos.

   2. En ¿Cuántos campos de página desea?, haga clic en el número de campos de página que quiera usar.

   3. En ¿Qué rótulos de elemento desea que use cada campo de página para identificar el rango de datos seleccionado?, seleccione para cada campo de la página el rango de celdas y, después, escriba un nombre para ese intervalo.

      Ejemplo

      • Si ha seleccionado 1 en ¿Cuántos campos de página desea?, seleccione cada rango y luego escriba un nombre en el cuadro Campo Uno. Si tiene cuatro intervalos y cada uno corresponde a un trimestre diferente del año fiscal, seleccione el primer rango, escriba T1, seleccione el segundo rango, escriba T2 y repita el proceso para T3 y T4.

      • Si ha seleccionado 2 en ¿Cuántos campos de página desea?, haga lo mismo que el ejemplo anterior en el cuadro Campo Uno. Después, seleccione dos rangos y escriba el mismo nombre, como H1 y H2, en el cuadro Campo Dos. Seleccione el primer rango, escriba H1, seleccione el segundo rango, escriba H1, seleccione el tercer rango, escriba H2, seleccione el cuarto rango y escriba H2.

   4. Haga clic en Siguiente.

6. En la página del asistente Paso 3, seleccione entre agregar la tabla dinámica a una hoja de cálculo existente o una nueva y, después, haga clic en Finalizar.