Nivel 7 · Desafío final: las ventas de un día, de las líneas a la tabla dinámica y al titular

Un día, tres capas

Una nota de ventas útil tiene tres capas: las líneas (qué pasó), un resumen (a dónde fue el dinero) y un titular (qué decir primero). Este Desafío final construye las tres para el lunes 12 de mayo de 2025: 39 líneas de pedido en A1:F40, con completados, cancelados y reembolsados mezclados.

Paso 1: un campo que sumar

La exportación tiene cantidad, precio y descuento, pero no ingresos. Una tabla dinámica solo puede resumir un campo que exista, así que primero añade uno:

G1   line_total
G2   =D2*E2*(1-F2/100)

Rellenada hasta G40. Con encabezado y sin huecos, la columna G pasa a formar parte del origen de la tabla dinámica, igual que en Excel si los datos fueran una Tabla (Ctrl+T). En Excel también podrías añadir un campo calculado, pero una columna auxiliar es visible y fácil de revisar.

Paso 2: ingresos completados por categoría

Status pertenece a Filtro: la pregunta es solo sobre ventas completadas, no una división por estado.

Filter    status = completed
Rows      category
Values    Sum of line_total

status        completed

category      Sum of line_total
Decor               626.27
Kitchen             149.91
Lighting            207.96
Outdoor           5,419.06
Textiles            493.02
Grand Total       6,896.22

Sin el filtro el Total general sería 7,398.12: las seis líneas canceladas (396.91) y una línea reembolsada (104.99) contarían como ventas.

Paso 3: el titular, leído desde la tabla dinámica

Los valores de la tabla dinámica son celdas normales, así que las fórmulas pueden leerlos. Mantén las filas en orden A→Z y deja que las fórmulas encuentren al ganador, para que el titular no dependa de cómo esté ordenada la tabla dinámica:

M2   =LARGE(J4:J8,1)                          biggest category revenue
M1   =INDEX(I4:I8,MATCH(M2,J4:J8,0))          the label next to it
M3   =M2/J9                                   share of the Grand Total
M4   =INDEX(I4:I8,MATCH(LARGE(J4:J8,2),J4:J8,0))

LARGE(range, k) devuelve el k-ésimo valor más grande, así que k = 2 es el segundo lugar. MATCH(…, 0) encuentra su posición e INDEX devuelve la categoría en la misma posición, el mismo par que viste en el capítulo 2.

En Excel, al hacer clic en una celda de la tabla dinámica mientras escribes una fórmula, se inserta una llamada GETPIVOTDATA(…) en lugar de J4. Lee un valor por nombre de campo, así que sobrevive a una tabla dinámica reordenada; las referencias simples como J4:J8 son más fáciles de leer y son las que admite esta hoja. Puedes desactivar ese hábito en Análisis de tabla dinámica → Opciones → Generar GetPivotData.

Piensa como analista

Outdoor con 78.6% de un lunes de mayo es el comienzo de la temporada de patios, no algo aislado: el siguiente ticket compararía el mismo día de la semana en abril y junio. Y los 501.90 de líneas canceladas o reembolsadas merecen una frase en la nota, porque son la diferencia entre la caja y el informe de ventas.

Errores comunes

• Poner status en Filas en lugar de Filtro: la tabla se divide en tres líneas por categoría.
• Una columna auxiliar sin encabezado, que la tabla dinámica no puede ver.
• Escribir 5,419.06 en M2: el titular queda obsoleto en cuanto cambia la tabla dinámica.
• MATCH sin el 0: una coincidencia aproximada sobre números sin ordenar devuelve la categoría equivocada.