TABLAS DINÁMICAS Y CONSOLIDACIÓN DE DATOS

   ¿QUE ES UNA TABLA DINÁMICA?

Una tabla dinámica es una potente herramienta para calcular, resumir y analizar datos que le permite ver comparaciones, patrones y tendencias en ellos.

Crear una tabla dinámica

1. Seleccione las celdas a partir de las que desea crear una tabla dinámica.
2. Seleccione Insertar > Tabla dinámica.
3.  
   
4. En Seleccione los datos que desea analizar, seleccione Seleccionar una tabla o rango.
   
5. En Tabla o rango, compruebe el rango de celda.
6. En Elija dónde desea colocar el informe de tabla dinámica, seleccione Nuevahoja de cálculo para colocar la tabla dinámica en una hoja de cálculo nueva.
7. Seleccione Aceptar.

Creación de la tabla dinámica

1. Para agregar un campo a la tabla dinámica, seleccione la casilla de nombre de campo en el panel Campos de tabla dinámica.
2. Nota: Los campos seleccionados se agregan a sus áreas predeterminadas: los campos no numéricos se agregan a Filas, las jerarquías de fecha y hora se agregan a Columnas y los campos numéricos se agregan a Valores.
   
3. Para mover un campo de un área a otra, arrastre el campo al área de destino.

CONSOLIDAR DATOS EN EXCEL CON TABLAS DINÁMICAS 

Frecuentemente necesitamos consolidar datos en Excel pero la técnica que utilicemos depende mucho del tipo de consolidación a realizar así como la manera en como se encuentre las fuentes de datos.
En esta ocasión mostraré la técnica de consolidación de datos en Excel con tablas dinámicas. Este método no solamente consolidará los datos sino que tendrás un reporte flexible al mismo tiempo sin mayor esfuerzo.

Consolidar datos en Excel

Para este ejemplo, tengo tres hojas de Excel. Cada una de las hojas tiene las ventas de los tres vendedores en la empresa en los primeros 2 meses del año.

El reporte que necesito realizar es sobre las ventas del mes de enero y febrero. Para iniciar debo abrir el Asistente para tablas y gráficos dinámicos con el atajo de teclado ALT+T+B.

Seleccionamos la opción Rangos de consolidación múltiples y también la opción Tabla dinámica. Al pulsar el botón siguiente deberé seleccionar la opción Crear un solo campo de página.

En el siguiente paso deberás agregar los rangos de cada una de las tablas en las diferentes hojas de Excel:

Finalmente hago clic en Siguiente y en el último paso selecciono la opción Hoja de cálculo nueva que significa que Excel creará una nueva hoja en mi libro donde pondrá el resultado. A continuación puedes ver los datos consolidados:

Puedes dar el detalle final a esta tabla dinámica aplicando un formato de número a las celdas.

Filtro de tabla dinámica

Adicionalmente podrás observar que el filtro de la tabla dinámica tiene las opciones Elemento 1, Elemento 2 y Elemento 3.

Cada opción corresponde a una de las hojas que hemos consolidado, de manera que si selecciono la opción Elemento  1 observaré en la tabla dinámica los resultados de venta de la Hoja 1 que corresponden a Hugo:

Hemos visto la técnica para consolidar datos en Excel con tablas dinámicas. Existen otras técnicas que he descrito en artículos anteriores y que puedes encontrar en los vínculos de abajo.

¿QUE ES CONSOLIDACIÓN DE DATOS?

CONSOLIDACIÓN DE DATOS EN VARIAS HOJAS DE CALCULO

Para resumir los resultados de hojas de cálculo independientes y elaborar informes con ellos, puede consolidar datos de cada una de ellas en una hoja de cálculo maestra. Las hojas de cálculo pueden estar en el mismo libro que la hoja de cálculo maestra o en otros libros. Al consolidar datos, une datos de modo que puede actualizar y agregar según sea necesario con mayor facilidad.

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.

Hay dos formas de consolidar datos: por categoría o por posición.

Consolidación por posición: Cuando los datos en las áreas de origen se organizan en 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. Si quiere una consolidación más flexible por categoría, considere la posibilidad de crear una tabla dinámica en su lugar.

Pasos de consolidación:

1. Si aún no lo ha hecho, en cada hoja de cálculo que contiene los datos que quiere consolidar, configure los datos del siguiente modo:
   • Asegúrese de que cada rango de datos está en formato de lista, de modo que cada columna tenga una etiqueta en la primera fila, contenga datos similares y no tenga filas o columnas en blanco.
   • Coloque cada rango en una hoja de cálculo independiente, pero no escriba nada en la hoja de cálculo maestra donde vaya a colocar la consolidación; Excel la rellenará.
   • Asegúrese de que cada rango tiene el mismo diseño.
2. En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.
3. En la pestaña Datos, del grupo Herramientas de datos, haga clic en Consolidar.
   
4. En el cuadro Función, haga clic en la función de resumen que quiere que use Excel para consolidar los datos. La función predeterminada es SUMA.
   El siguiente es un ejemplo con tres rangos de hoja de cálculo seleccionados.
   
5. Seleccionar los datos
   • Si la hoja de cálculo que contiene los datos que quiere consolidar está en otro libro, haga clic primero en Examinar para buscar ese libro y, después, haga clic en Aceptar para cerrar el cuadro de diálogo Examinar. Excel introducirá la ruta del archivo en el cuadro Referencia seguida de un signo de exclamación y puede seguir seleccionando los datos.
   Después, en el cuadro Referencia, haga clic en el botón Contraer diálogo para seleccionar los datos de la hoja de cálculo.
   
   Haga clic en la hoja de cálculo que contiene los datos que quiere consolidar, seleccione los datos y después haga clic en el botón Expandir diálogo de la derecha para volver al cuadro de diálogo Consolidar.
6. En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos para agregar todos los rangos que quiera.
7. Actualizaciones automáticas frente a actualizaciones manuales: Si quiere que Excel actualice la tabla de consolidación automáticamente cuando cambie el origen de datos, active la casilla Crear vínculos con los datos de origen. Si está desactivada, puede actualizar la consolidación de forma manual.
8. Haga clic en Aceptar y Excel generará la consolidación. No tendrá formato, tendrá que encargarse usted de él, pero solo tiene que hacerlo una vez, a menos que vuelva a ejecutar la consolidación.
   • Las etiquetas que no coincidan con las 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 desea consolidar tienen rótulos únicos que aparecen solamente en un rango de origen.

Usar un informe de tabla dinámica para consolidar datos

• 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:
  
  Sugerencia: Para introducir una referencia de celda, como Ventas!B4, en una fórmula sin escribir, escriba la fórmula hasta el punto en que necesite la referencia, haga clic en la pestaña de la hoja de cálculo y, después, haga clic en la celda; Excel rellena automáticamente el nombre de la hoja y la dirección de la celda. Tenga en cuenta que las fórmulas como esta pueden ser propensas a errores, porque es muy fácil seleccionar por accidente la celda incorrecta. También puede resultar difícil localizar un error una vez que se ha introducido la fórmula.
• 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 use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 desde Ventas hasta Marketing (ambos incluidos), tendría que escribir lo siguiente en la celda E5 de la hoja de cálculo maestra: