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

La consolidación de datos es una forma útil de combinar datos de orígenes diferentes en un solo informe. Por ejemplo, si tiene una tabla dinámica de cifras de gastos para cada una de las oficinas regionales, puede usar la consolidación de datos para reunir estas cifras en un informe de gastos corporativos. Este informe 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.

Consolidación de varios rangos

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 resultante usa campos genéricos como 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.

Configurar el origen de datos

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.

Uso de campos de página

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.

Usar rangos con nombre

Si es probable que el rango de datos cambie la próxima vez que consolide los datos (es decir, puede 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.

Uso de referencias 3D o el comando Consolidar

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).

Consolidación de varios rangos

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.

Consolidar los datos sin usar campos de página

Para combinar los datos de todos los rangos y crear una consolidación que no tenga campos de página, haga lo siguiente. 

1. Para abrir el Asistente para tablas y gráficos dinámicos, presione ALT+D y, después, P.

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

   1. Seleccione la flecha situada junto a la barra de herramientas y, después , seleccione Más comandos.
   2. Seleccione Personalizar barra de herramientas de acceso rápido (...) en la esquina inferior izquierda debajo de la cinta de opciones y, después , seleccione 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, seleccione Agregar y, después, aceptar.

3. Seleccione una celda en blanco (que no forme parte de una tabla dinámica) en el libro.

4. Seleccione en el icono del Asistente para tablas y gráficos dinámicos de la barra de herramientas de acceso rápido.

5. En la página del asistente Paso 1 , seleccione Varios rangos de consolidación (para combinar datos de varios rangos) y, después, seleccione Siguiente.

6. En la página del asistente Paso 2a , seleccione Crearé los campos de página y, a continuación, seleccione Siguiente.

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

   1. Volver al libro y seleccione el rango de celdas. A continuación, en el Asistente para tablas y gráficos dinámicos, seleccione Agregar.

      Recomendación

      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?, seleccione 0 y, a continuación, seleccione Siguiente.

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

Consolidar datos con un solo campo de página

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. Seleccione la flecha situada junto a la barra de herramientas y, después , seleccione Más comandos.
   2. Seleccione Personalizar barra de herramientas de acceso rápido (...) en la esquina inferior izquierda debajo de la cinta de opciones y, después , seleccione 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, seleccione Agregar y, después, aceptar.

2. Seleccione una celda en blanco (que no forme parte de una tabla dinámica) en el libro.

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

4. En la página del asistente Paso 2a , seleccione Crear un campo de página única para mí y, a continuación, seleccione Siguiente.

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

   1. Volver al libro y seleccione el rango de celdas. A continuación, en el Asistente para tablas y gráficos dinámicos, seleccione Agregar.

      Recomendación

      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. Seleccione Siguiente.

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

Consolidar datos con varios campos de página

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. Seleccione la flecha situada junto a la barra de herramientas y, después , seleccione Más comandos.
   2. Seleccione Personalizar barra de herramientas de acceso rápido (...) en la esquina inferior izquierda debajo de la cinta de opciones y, después , seleccione 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, seleccione Agregar y, después, aceptar.

2. Seleccione una celda en blanco (que no forme parte de una tabla dinámica) en el libro.

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

4. En la página del asistente Paso 2a , seleccione Crearé los campos de página y, a continuación, seleccione Siguiente.

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

   1. Volver al libro y seleccione el rango de celdas. A continuación, en el Asistente para tablas y gráficos dinámicos, seleccione Agregar.

      Recomendación

      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?, seleccione el número de campos de página que desea 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. Seleccione Siguiente.

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