Ediciones ENI Editorial | líder en informática
Introducción
Introducción
Ideas preliminares
Esta obra le va a enseñar a diseñar y crear tablas dinámicas con Excel 2019 a partir de datos múltiples y variados. En primer lugar, va a estudiar los elementos básicos que intervienen en la planificación de este tipo de tablas a partir de ejemplos sencillos, y a continuación, será capaz de crear con rapidez informes de tablas dinámicas complejas.
Todos los ejemplos que aparecen en el libro se pueden descargar desde la página Información. Esto le evitará tener que crear sus propios archivos y le permitirá corregir sus resultados. Le recomendamos que siga el orden del libro, que ha sido diseñado para que el lector pueda avanzar de forma gradual. Los ejercicios del nivel superior hacen referencia a los elementos trabajados en los ejercicios anteriores. Este método le permitirá consolidar lo aprendido a medida que vaya avanzando.
Los ejemplos del libro son variados y le van a permitir implementar las técnicas de creación de tablas dinámicas en numerosos ámbitos.
Cuando haya realizado todos los ejemplos que se proponen en el libro, será capaz de crear sus propias tablas dinámicas complejas adaptadas a lo que necesite.
Si ya utiliza las tablas dinámicas con versiones anteriores, Excel 2019 le permitirá importar y relacionar grandes cantidades de datos que provengan de diferentes orígenes con la ayuda de un modelo de datos integrado. De este modo, podrá generar modelos de datos que le servirán de base para sus tablas o gráficos dinámicos.
El modelo de datos de Excel tiene como puntos fuertes:
-
El tratamiento rápido de un gran volumen de datos.
-
Una gestión de tamaño de archivos mejorada.
-
Portabilidad de los datos, ya que se almacenan dentro del libro.
Excel 2019 tiene integrado el componente PowerPivot, un espacio de trabajo que permite tratar los datos de varios millones de líneas de orígenes múltiples y diversos (bases de datos, cubo OLAP...). PowerPivot tiene un lenguaje específico DAX (Data Analysis Expressions) para establecer relaciones, cálculos y sumatorios.
Objetivo
Una tabla dinámica permite obtener con rapidez un resumen a partir de una base de datos. Se trata de una herramienta con muchas prestaciones, integrada en Microsoft Excel, que le ayudará a idear análisis eficaces...
Las tablas dinámicas
Definiciones
|
Base de datos |
Conjunto de información o datos estructurados guardados en un disco. Este conjunto de datos se puede consultar y modificar. |
|
Registro |
Cada registro está compuesto por un conjunto de datos relativos a un elemento de la base de datos. |
|
Campo |
Un campo contiene uno de los datos del registro. Se necesitan varios campos para completar un registro. Por ejemplo, en el caso de un cliente: Código cliente, Razón social, Dirección, Código postal, Población, Provincia, Sector, Cifra de negocios... |
|
Filtro de informe |
Permite filtrar los datos que se desean mostrar en función del elemento seleccionado en el filtro del informe. Un filtro de informe sirve para representar con rapidez un subconjunto de datos en un informe de tabla dinámica. Cuando los datos de origen son abundantes, el filtro permite trabajar con una parte de los datos de origen del informe. También es posible mostrar los resúmenes relativos a una familia de productos, un periodo de tiempo o una categoría específica de personal. |
|
Segmentos |
Permite utilizar botones para segmentar y filtrar rápidamente los datos. |
|
Modelo de datos |
Colección de tablas y de sus relaciones entre ellas. |
|
Rótulos de fila |
Permiten mostrar los campos en forma de filas en el lado izquierdo del informe. Una fila de posición inferior queda anidada en la columna que se aparece inmediatamente por encima de ella. |
|
Rótulos de columna |
Permite mostrar los campos en forma de columnas en la parte superior del informe. Una columna de posición inferior queda anidada en la columna que se aparece inmediatamente a la izquierda. |
|
Campos de valores |
Sirven para mostrar datos de resumen numéricos. Se pueden utilizar diversas funciones de resumen (suma, promedio, mínimo, máximo, contar...). |
Los límites de las tablas dinámicas
|
Funcionalidad |
Límite máximo |
|
Número máximo de tablas dinámicas por hoja |
En función de la cantidad de memoria disponible |
|
Número máximo de elementos únicos por campo |
1 048 576 |
|
Número máximo de campos de fila o de columna |
En función de la cantidad de memoria disponible |
|
Número máximo de filtros de informe en un informe de tabla dinámica |
256 (limitación posible en función de la memoria disponible) |
|
Número máximo de campos de valor en un informe de tabla dinámica |
256 |
|
Número máximo de fórmulas en un informe de tabla dinámica |
En función de la cantidad de memoria disponible |
|
Número máximo de filtros |
256 (limitación posible en función de la memoria disponible) |
|
Número máximo de campos de valor |
256 |
|
Fórmulas de elementos calculados en un informe de gráfico dinámico |
En función de la cantidad de memoria disponible |
|
Número máximo de elementos en una lista de filtro |
10000 |
Los datos de origen
Los datos origen de una tabla dinámica deben tener siempre la misma estructura:
-
Una fila, que representa un registro de la base de datos.
-
Una columna, que representa un campo.
Vea un ejemplo con las primeras filas de los datos de origen, extraídas de una hoja de cálculo de Excel:
La primera fila debe contener los títulos (nombres de los campos). Para que se puedan cruzar los datos. La base de datos debe contener al menos dos campos para cruzar, más un campo de datos numéricos.
Recomendaciones
Para evitar problemas a la hora de crear tablas dinámicas, tenga en cuenta los siguientes consejos:
Las celdas de la fila de los títulos no se deben combinar.
No puede haber dos campos con el mismo nombre.
Un nombre de campo nunca debe estar vacío.
La base de datos no debe contener ninguna fila o columna vacía ni oculta.
No se deben introducir filas de subtotales en la base de datos.
En las columnas de valores numéricos, es preferible rellenar las celdas vacías con ceros.
Análisis
A menudo, tendrá que definir cuáles son los datos que se tienen que utilizar antes de crear un informe de tabla dinámica. Por ejemplo, si exporta información desde un programa de gestión comercial, deberá seleccionar la información (campos) que se deba tener en cuenta.
Si desea crear con Excel una aplicación partiendo de cero, debería efectuar previamente un análisis preciso y completo de las estadísticas que desee obtener más adelante. Imaginemos, por ejemplo, que es el propietario de una pequeña tienda de vino. Con cada venta, introducirá la información cronológicamente en una hoja de cálculo de Excel.
La tabla en la que introducir los datos se podría parecer a la siguiente:
Las columnas ORIGEN y COLOR tienen cada una un área de lista desplegable que facilita la introducción de información para evitar que la misma información se escriba de varias maneras distintas (Excel no trata de la misma manera "ROSADO" que "Rosado").
Una tabla de introducción como esta le permite obtener:
-
La cifra de negocios por origen.
-
La cifra de negocios mensual por origen.
-
El número total de botellas vendidas mensualmente por tipo.
La siguiente tabla muestra algunos ejemplos de datos que hay que integrar en función de las necesidades.
|
Objetivo del análisis Resultados que se desea obtener |
Origen |
Datos que deben indicarse |
|
Comparar las ventas N y N-1 por familias de productos en períodos equivalentes |
Software de gestión comercial, Facturación, Aplicación Access, Archivo de Excel… |
|
|
Cifra de negocios realizada por comercial y por sector de actividad en el año en curso |
Gestión comercial, Facturación, Aplicación Access, Archivo de Excel… |
|
|
Establecer la lista de los cinco mejores clientes teniendo en cuenta la cifra de negocios |
Software de gestión comercial, Facturación Aplicación Access, Archivo de Excel... |
|
|
Total de gastos por departamento y por tipo de gasto |
Software de gestión de gastos, Aplicación Access, Archivo de Excel... |
|
Las diferentes zonas de una tabla dinámica
Un informe de tabla dinámica está compuesto por cinco zonas:
-
Zona de filtro
-
Zona de valores
-
Zona de rótulos de fila
-
Zona de rótulos de columna
-
Zona de totales
Tomemos como ejemplo la lista de datos cuyas primeras filas presentamos a continuación.
A continuación, se muestra un ejemplo de tabla dinámica creada a partir del archivo utilizado anteriormente. Esta tabla muestra las ventas de sorbetes en Barcelona por comercial y por tipo de cliente.
Ilustración de las diferentes partes:
Zona de filtro
Esta zona (opcional) aparece siempre en la parte superior del informe. Puede incluir una o varias filas que permiten filtrar los datos de la tabla de origen y que se usarán para efectuar los cálculos de resumen.
Es posible seleccionar todas las filas de la tabla de origen (Todas - opción por defecto) o bien seleccionar uno o más elementos.
Zona de rótulos de fila
Excel cataloga aquí de forma única la información que contiene un campo de la fuente. Es primordial que los datos relativos a una misma información se escriban siempre de la misma forma en la tabla de origen. Si el nombre de un comercial se ha escrito de dos formas diferentes, Excel creará dos filas en la tabla dinámica aunque, ambas se refieran a la misma información.
Esta zona permite filtrar la información que se va a visualizar. También será posible visualizar solo la información de determinados comerciales.
Zona de rótulos de columna
Al igual que sucede con la zona de los rótulos de fila...
