Tablas dinámicas y Consolidación de datos

Las tablas dinámicas en Excel son un tipo de tabla que nos permiten decidir con facilidad los campos que aparecerán como columnas, como filas y como valores de la tabla y podemos hacer modificaciones a dicha definición en el momento que lo deseemos.

Las conocemos como tablas dinámicas porque tú decides “dinámicamente” la estructura de la tabla, es decir sus columnas, filas y valores.

Las tablas dinámicas en Excel también son conocidas como tablas pivote debido a su nombre en inglés: Pivot tables. Son una gran herramienta que nos ayuda a realizar un análisis profundo de nuestros datos ya que podemos filtrar, ordenar y agrupar la información de la tabla dinámica de acuerdo a nuestras necesidades.

      ¿Para qué sirven las tablas dinámicas en Excel?

Por la descripción que he dado hasta el momento, pareciera que las tablas dinámicas en Excel son una maravilla. El problema es que muchos usuarios de Excel no las utilizan porque parecieran ser complejas, sin embargo una vez que conoces y entiendes su funcionamiento te darás cuenta de todos sus beneficios.

Las tablas dinámicas en Excel nos ayudan a comparar grandes cantidades de datos e intercambiar fácilmente columnas por filas dentro de la misma tabla y realizar filtros que resulten en reportes que de otra manera necesitaríamos un tiempo considerable para construirlos. Considera el siguiente ejemplo.

En una hoja de Excel tengo la información de ventas de diferentes establecimientos de la empresa así como el nombre del vendedor que realizó la venta.

Tablas Dinámicas Excel – Crear una tabla dinámica en blanco

Para comenzar a hacer una tabla dinámica, seguir los siguientes pasos:

Hacer clic en una celda de la tabla de datos. El clic puede ser en cualquier celda, siempre y cuando los datos cumplan con las reglas descritas anteriormente. De hecho, en este punto es todo o nada: selecciona toda la tabla o sólo una celda de la tabla. No selecciones unas cuantas celdas, ya que Excel puede pensar que tú estás tratando de crear una tabla dinámica Excel a partir de sólo esas celdas seleccionadas.

Hacer clic en el menú Insertar y hacer clic en el botón Tabla dinámica:

Aparecerá el siguiente cuadro de diálogo:

Podemos observar que el campo Tabla/Rango reflejarán automáticamente los datos de la tabla (puedes hacer clic en el botón para cambiar el valor la tabla en caso de que Excel escogió mal). Alternativamente, se puede elegir un origen de datos externo, como una base de datos (que trataremos en otro tutorial)

También hay que tener en cuenta que se puede elegir dónde debe ir la nueva tabla dinámica Excel. De forma predeterminada, Excel sugiere una nueva hoja de cálculo, que desde mi punto de vista es la mejor opción, a menos que se desee tener la tabla en una hoja de cálculo ya existente.

Hay que advertir que si los datos cambian mucho, o si te encuentras cambiando el diseño de la tabla dinámica muy frecuente, la actualización de los datos en la tabla dinámica puede resultar en un cambio de forma de la tabla y una cobertura de un área más grande. Si tienes datos o fórmulas en esa zona, desaparecerán. Por lo tanto, poner una tabla dinámica en la misma página que los datos de origen o junto con otra información puede causar verdaderos dolores de cabeza más adelante, y es por eso que la opción de nueva hoja de cálculo es la opción recomendada.

Una vez que se haya completado la selección, hacer clic en Aceptar. Suponiendo que se eligió la opción Nueva hoja de cálculo, Excel creará una hoja nueva en el libro actual y colocará la tabla dinámica en blanco en esa hoja de cálculo. Ahora estamos listos para diseñar nuestra tabla dinámica de Excel.

Tablas Dinámicas Excel – Diseñando nuestro propio diseño de tabla dinámica

Cuando cambies a la hoja de cálculo con la nueva tabla dinámica, notarás que hay tres elementos de la tabla en pantalla, empezando por el propio informe de la tabla dinámica:

A continuación, verás la lista de campos de la tabla dinámica y debajo de la lista de campos encontrarás el campo de área de diseño. Ten en cuenta que se debe mostrar los encabezados de columna de tu tabla de datos.

Para crear el diseño, primero se deben seleccionar los campos que se desea incluir en la tabla y, a continuación, colocarlos en la ubicación correcta.

Se puede seleccionar las casillas de los campos que se desea incluir y Excel tratará de adivinar en qué área debe ser colocado cada campo. Sin embargo, la tabla dinámica se actualiza cada vez que se activa una de las casillas que pueden hacer lento el trabajo, especialmente si Excel coloca un campo en el lugar equivocado.

Por lo tanto, te recomiendo que arrastres y coloques cada campo al área en la que deseas que se encuentre.

Como ejemplo, aquí muestro la lista de campos y el área de diseño con los campos en el lugar deseado para mostrar un informe con:

Cada día abajo a la izquierda, con cada vendedor mostrado por separado para cada día

Los productos se muestran en la parte superior

La cantidad total de artículos vendidos para cada celda en la tabla dinámica.

Aquí muestro cómo estructuré este informe:

El reporte que la tabla dinámica en Excel generó tiene este aspecto:

Observa cómo la tabla dinámica ha creado automáticamente una lista de los vendedores para cada día cubiertos en los datos de origen.

                     CONSOLIDACIÓN DE DATOS 

Consolidar datos consiste en combinar los valores de varios rangos de datos en uno solo. Por ejemplo, si tienes una hoja de cálculo de cifras de gastos para cada una de sus oficinas regionales, puedes utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización; o si tienes las ventas de cada uno de los vendedores puedes agruparlas en una sola hoja para calcular el total de ventas de toda la compañía.

Para utilizar la herramienta de consolidación de Excel, los rangos a consolidar deben tener una estructura muy similar, o sea, los mismos rótulos de filas y columnas o estar en el mismo orden. 

Por ejemplo: tenemos las ventas del primer semestre para cada uno de los vendedores de la compañía y deseamos calcular las ventas totales de la compañía. 

Cómo consolidar datos

1. En primer lugar, examina los datos y decide si deseas consolidarlos por posición o por categoría.

Posición Si vas a combinar datos que están en la misma celda en varios rangos, puedes consolidar por posición.

Categoría Si tienes varios rangos con diseños diferentes y vas a combinar datos de filas o columnas que tengan rótulos (nombres de fila y/o columna) coincidentes, puedes consolidar por categoría.

2. Configura los datos que va a consolidar.

Asegúrate de que cada rango de datos está en formato de lista: cada columna tiene un rótulo en la primera fila, contiene datos similares y no tiene filas o columnas en blanco.

Coloca cada rango en una hoja de cálculo diferente. Ej: una hoja de cálculo diferente para cada vendedor.

No pongas ningún dato en la hoja de cálculo donde vayas a colocar la consolidación.

Si realizas la consolidación por posición, asegúrate de que cada rango tiene el mismo diseño.

Si realizas la consolidación por categoría, asegúrate de que los rótulos de las columnas o filas que deseas combinar tienen idéntica ortografía y coincidencia de mayúsculas y minúsculas.

Si deseas, puedes asignar un nombre a cada rango.

3. Haz clic en la celda superior izquierda del área donde desees que aparezcan los datos consolidados.

4. En el menú Datos, haz clic en Consolidar. Aparece el siguiente cuadro:

5. En el cuadro Función, haz clic en la función de resumen que deseas que utilice Microsoft Excel para consolidar los datos. Ej: sumar, contar o promediar.

6. Haz clic en el cuadro Referencia, elige la etiqueta de hoja del primer rango que vas a consolidar, escribe el nombre que asignaste al rango o selecciona el rango y, a continuación, haz clic en Agregar. Repite este paso para cada rango.

7. Si deseas actualizar la tabla de consolidación automáticamente cada vez que cambien los datos en cualquiera de los rangos de origen y no estás seguro de si más tarde desearás incluir rangos diferentes o adicionales en la consolidación, activa la casilla de verificación Crear vínculos con los datos de origen.

8. Activa las casillas de verificación bajo Usar rótulos en que indican dónde están localizados los rótulos en los rangos de origen: en la fila superior, la columna izquierda o ambas. Los rótulos que no coincidan con los de las otras áreas de origen producirán filas o columnas independientes en la consolidación.

En el ejemplo, el cuadro quedará más o menos así:

9. Haz clic en aceptar. En nuestro ejemplo: la hoja de consolidación quedará así:

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

Pasos de consolidación

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.

En la hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados.

En la pestaña Datos, del grupo Herramientas de datos, haga clic en Consolidar.

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.

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.

En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos para agregar todos los rangos que quiera.

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.

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.

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: