Del Modelo de Datos a la Generación de Informes

El modelo define qué se puede combinar. Si existe una ruta de relación entre dos tablas, cualquier tabla dinámica puede combinar campos de ambas, sin necesidad de fórmulas. Si no existe una ruta, no se puede realizar la conexión.

Las tres preguntas empresariales

1. ¿Qué región genera más ingresos?

• Tabla de origen: Customers;
• Filas: Region;
• Columnas: Category (Products);
• Valores: Total Sales (measure).

2. ¿Cómo evolucionan las ventas mes a mes?

• Tabla de origen: Dates;
• Filas: Year, luego Month Name;
• Valores: Total Sales (measure).

3. ¿Cómo se comparan los segmentos de clientes?

• Tabla de origen: Customers;
• Filas: Segment;
• Valores: Total Sales, Transaction Count, Average Order Value.

Formateo de valores en tablas dinámicas

Los números sin formato en una tabla dinámica son más difíciles de leer que los formateados, especialmente al compartirlos con partes interesadas. Aplicar formato de moneda a cualquier medida monetaria directamente dentro de la tabla dinámica:

1. Hacer clic en cualquier celda de la columna de la medida que se desea formatear;
2. Ir a Analizar tabla dinámica → Configuración de campo;
3. Hacer clic en Formato de número en la parte inferior del cuadro de diálogo;
4. Seleccionar Moneda, elegir el símbolo apropiado y hacer clic en Aceptar.

Ordenar correctamente los nombres de los meses

Los nombres de los meses son valores de texto. Excel ordena el texto alfabéticamente por defecto, lo que coloca abril antes de enero y febrero antes de marzo. En cualquier tabla dinámica basada en el tiempo, esto debe corregirse antes de que los datos sean significativos.

1. Hacer clic derecho en cualquier nombre de mes en el área de filas de la tabla dinámica
2. Seleccionar Ordenar → Más opciones de ordenación;
3. Elegir Ascendente para ordenar de enero a diciembre;
4. Para un control total sobre el orden, usar la opción Manual para arrastrar los meses en la secuencia correcta.

El modelo como motor de informes

Cada una de las tres tablas dinámicas obtiene datos de diferentes tablas en el modelo simultáneamente. La tabla dinámica 1 combina Clientes, Productos y Ventas en una sola vista.

Antes del modelado de datos, combinar los totales de Region, Category y Sales en una sola tabla requería fórmulas VLOOKUP o SUMIFS que debían reescribirse cada vez que los datos cambiaban. Con el modelo implementado, el mismo resultado se obtiene arrastrando tres campos a una tabla dinámica, y se actualiza automáticamente cuando se cargan nuevos datos.

Tarea

El objetivo es construir tres tablas dinámicas, cada una respondiendo a una pregunta de negocio específica. Cada tabla dinámica debe utilizar campos de al menos dos tablas diferentes. Crea cada tabla dinámica en una hoja nueva y nómbrala como se indica.

Tabla dinámica 1 — Ingresos por segmento y categoría (nombre de hoja: PT_Task1)

Pregunta de negocio: ¿Qué segmento de clientes genera más ingresos y la distribución por categoría varía entre segmentos?

Inserta una tabla dinámica basada en el modelo (Insertar → Tabla dinámica → Usar el modelo de datos de este libro) en una hoja nueva llamada PT_Task1, luego:

• Agrega Segment de la tabla Customers a Filas.
• Agrega Category de la tabla Products a Columnas.
• Agrega la medida [Total Sales] de la tabla Sales a Valores.
• Da formato a los valores como moneda con dos decimales.

Tabla dinámica 2 — Conteo mensual de transacciones (nombre de hoja: PT_Task2)

Pregunta de negocio: ¿Cuántos pedidos se realizaron cada mes y qué trimestre tuvo el mayor volumen?

Inserta una segunda tabla dinámica basada en el modelo en una hoja nueva llamada PT_Task2, luego:

• Agrega Quarter de la tabla Dates a Filas.
• Agrega MonthName de la tabla Dates a Filas, anidado debajo de Quarter.
• Agrega la medida [Transaction Count] de la tabla Sales a Valores.
• Da formato a los valores como números enteros (sin decimales).

Verifica: los subtotales por trimestre deben ser iguales a la suma de los valores mensuales de Transaction Count dentro de ese trimestre. Si no es así, revisa que tus filas estén correctamente anidadas (Quarter externo, MonthName interno).

Tabla dinámica 3 — Tres medidas por región (nombre de hoja: PT_Task3)

Pregunta de negocio: ¿Cómo se comparan las cuatro regiones en ingresos totales, número de pedidos y tamaño promedio de pedido?

Inserta una tercera tabla dinámica basada en el modelo en una hoja nueva llamada PT_Task3, luego:

• Agrega Region de la tabla Customers a Filas.
• Agrega [Total Sales], [Transaction Count] y [Avg Order Value] de la tabla Sales a Valores.
• Da formato a Total Sales y Avg Order Value como moneda. Da formato a Transaction Count como número entero.