tablas dinámicas y consolidación de datos

                      tablas dinámicas 

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.
   Nota: Los datos no deben tener filas o columnas vacías. Debe tener un solo encabezado de fila.
2. Seleccione Insertar > Tabla dinámica. 
   
   
3. En Seleccione los datos que desea analizar, seleccione Seleccionar una tabla o rango.
   
4. En Tabla o rango, compruebe el rango de celda.
5. 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.
6. 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.
   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.
   
2. Para mover un campo de un área a otra, arrastre el campo al área de destino .

      CÓMO CREAR UNA TABLA DINÁMICA 

Primero, en Excel hay que seleccionar toda la información de nuestros datos. Para ello recomiendo convertir nuestro rango donde encontramos nuestra información a una tabla de datos. ¿Cómo? Muy sencillo. Selecciona el rango y ve a Ventana Insertar >> Grupo Tablas >> Tabla

Una vez que hemos hecho esto la información de nuestra tabla quedará de la siguiente manera:

Esto nos permitirá, que al hacer crecer la información de nuestra tabla en número de filas, al refrescar las tablas dinámicas, todo el rango sea cogido por la tabla dinámica en cuestión.

Pero ahora sí, para crear una tabla dinámica en excel seleccionamos nuestra recién creada tabla y hacemos: Ventana Insertar >> Grupo Tablas >> Tabla Dinámica

Al hacer click en tabla dinámica aparecerá una ventana como la siguiente:

En esta pestaña vemos que el rango/tabla que ha seleccioando. En la siguiente parte nos permitirá elegir donde situaremos nuestra tabla dinámica. En este caso vamos a permitir que cree una hoja nueva y nos coloque la tabla dinámica ahí. Haciendo click en OK lo que veremos será lo siguiente: una nueva hoja con el espacio creado para una tabla dinámica genérica y a la derecha un display para ordenar la información de nuestra tabla dinámica.

ORGANIZAR UNA TABLA DINÁMICA EN EXCEL

En la parte de la derecha vemos que en el recuadro superior se han almacenado todos los títulos de columna de nuestra tabla. Eligiendo el que queramos podremos arrastrarlo a una de las cajitas de abajo.

EN LA TABLA DINÁMICA EXCEL, ¿QUÉ SON LAS CAJITAS DE ABAJO?

• Filtros de reporte: nos permitirá filtrar la tabla entera seleccionando uno o varios elementos de la lista del filtro que hayamos aplicado.
• Columnas: nos permitirá organizar nuestra información por columnas (podremos seleccionar uno o varios elementos de la colección)
• Filas: nos permite organizar nuestra información por filas (podremos seleccionar uno o varios elementos de la colección)
• Valores: serán los valores de cálculo. Podremos visualizar los valores como suma, máximo, media, contar valores…

En la siguiente imagen vemos los filtros que hemos aplicado nosotros:

Hemos colocado nuestras columnas para conseguir el siguiente efecto:

• Valores: Suma de las ventas (precio) conseguido según los filtros seleccionados.
• Filtro de reporte: Mes. De esta manera podremos seleccionar el mes del que queremos conocer los datos de ventas.
• Etiquetas de filas:  hemos puesto las etiquetas de los vendedores para poder ver lo que han vendido en el mes seleccionado.
• Etiquetas de columnas: veremos los productos. De esta manera se desglosará para cada vendedor los productos que ha vendido y la suma de su precio.

Esto, explicado así, es un poco abstracto, lo mejor será ver cómo queda la tabla dinámica Excel una vez que hemos hecho las diferentes selecciones:

En  la imagen anterior vemos como la información de ventas queda organizada para el mes de Enero según los vendedores y los diferentes productos. Además de esto, veremos las columnas de totales de ventas por productos y por vendedores (fila inferior y columna derecha respectivamente).

Como puedes ver, crear tablas dinámicas en Excel es mucho más sencillo de lo que habías pensado nunca y en cuanto le cojas el truco podrás ir a toda velocidad sacando interesantes conclusiones de tus análisis.

Por último, ¿cómo cambio el tipo de valores mostrados? ¿y si me viniera mejor ver cómo se reparten porcentualmente las ventas? Pues es muy sencillo, tan sólo tendremos que seguir los siguientes pasos:

• Hacemos clic sobre la flechita negra en el organizador de tablas dinámicas sobre los valores que queremos cambiar (en nuestro ejemplo el valor suma de precio).
• Elegimos la opción de “Configuración de campo de valor”.
• Elegimos la pestaña de “Mostrar valores como”.
• Del desplegable de “Mostrar valores como” elegimos “% de Gran Total”.

De esta manera el resultado obtenido será:

CONSOLIDACIÓN DE DATOS 

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.

Sugerencia: Si suele consolidar datos, puede serle útil basar las hojas de cálculo en 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 es también un momento ideal para configurar la plantilla con tablas de Excel.

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.

Nota: Los ejemplos de este artículo se crearon con Excel 2016, por lo que la vista puede diferir según qué versión use. En cambio, los pasos son los mismos.

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.
   Nota: Para evitar sobrescribir datos existentes en la hoja de cálculo de destino que contiene los datos que está consolidando, asegúrese de dejar celdas suficientes a la derecha y debajo de esta celda para 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.
   Notas: 

   • No puede crear vínculos si las áreas de origen y destino están en la misma hoja.
   • Si necesita cambiar los rangos una vez los ha agregado, puede hacer clic en cada uno de ellos en el cuadro de diálogo Consolidar, actualizarlos cuando aparezcan en el cuadro Referencia y, después, hacer clic en Agregar. De este modo, se creará una nueva referencia de rango, por lo que tendrá que eliminar la anterior antes de volver a consolidar. Seleccione la referencia anterior y presione Suprimir.
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 
  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:
  las mismas celdas de otras hojas de cálculo