¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Análisis eficaz de datos
  3. Introducción
Extrait - Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365)
Extractos del libro
Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365) Volver a la página de compra del libro

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:

images/OST10_398.png

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:

images/OST10_399.png

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…

  • Fecha de factura

  • Familia de productos

  • Importe de la factura sin IVA

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…

  • Fecha de la factura

  • Nombre del comercial

  • Sector de actividad

  • Importe de la factura sin IVA

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

  • Fecha de la factura

  • Código de cliente

  • Nombre del cliente

  • Importe de la factura sin IVA

Total de gastos por departamento y por tipo de gasto

Software de gestión de gastos, Aplicación Access, Archivo de Excel...

  • Fecha del gasto

  • Departamento

  • Tipo de gasto

  • Importe

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.

images/OST10_400.png

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.

images/OST10_401.png

Ilustración de las diferentes partes:

Zona de filtro

images/OST10_402b.png

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.

images/OST10_403.png

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

images/OST10_404.png

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

images/OST10_405.png

Al igual que sucede con la zona de los rótulos de fila...