Nivel 7 · Desafío final: la tabla resumen de canal × estado

Una fórmula, nueve celdas

Una tabla resumen es una cuadrícula de totales condicionales: una condición desde la etiqueta de la fila, otra desde la etiqueta de la columna. Podrías escribir nueve SUMIFS distintos, pero entonces un cambio significa nueve ediciones, y un error de tecleo se esconde a plena vista. La forma del analista es una sola fórmula, escrita para poder arrastrarla a lo ancho y a lo largo.

La hoja contiene los 51 pedidos del sábado 29 de noviembre en A1:D52. Las etiquetas de la tabla ya están ahí: los canales en F5:F7, los estados en G4:I4, los totales en la columna J y la fila 8.

Paso 1: una celda, con los signos $ correctos

La primera celda son los ingresos completados de la app: canal desde F5, estado desde G4.

G5   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F5,$C$2:$C$52,G$4)

Cada referencia recibe exactamente el bloqueo que necesita:

Referencia	Bloqueo	Por qué
$D$2:$D$52, $B$2:$B$52, $C$2:$C$52	columna y fila	los datos nunca se mueven
$F5	solo la columna	cada celda lee la columna F, pero su propia fila
G$4	solo la fila	cada celda lee la fila 4, pero su propia columna

Las referencias mixtas del capítulo 1 ($F5, G$4) son lo que hace funcionar la cuadrícula. Léelas como “mira siempre a la izquierda, a la columna F” y “mira siempre arriba, a la fila 4”.

Paso 2: arrástrala a lo ancho y a lo largo

Rellena G5 a la derecha hasta I5 y luego rellena G5:I5 hacia abajo hasta la fila 7. Comprueba dos celdas en la barra de fórmulas:

I5   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F5,$C$2:$C$52,I$4)
G7   =SUMIFS($D$2:$D$52,$B$2:$B$52,$F7,$C$2:$C$52,G$4)

I5 son los ingresos reembolsados de la app: 0. G7 son los ingresos completados de la web: 4,991.16. Los ceros de la tabla son reales: ese día el único pedido reembolsado entró por la web.

Lo que sale mal con los otros bloqueos:

• Sin $ en las etiquetas (F5, G4): al rellenar a la derecha, F5 pasa a G5, un número dentro de la tabla, así que H5 busca un canal llamado 8757.22 y muestra 0.
• Bloqueo total en las etiquetas ($F$5, $G$4): cada celda repite el número de app/completed.
• Sin $ en los datos: los rangos se deslizan hacia abajo y a la derecha con la fórmula y se pierden filas.

Paso 3: totales y la comprobación

J5:J7   =SUM(G5:I5)
G8:J8   =SUM(G5:G7)
G10     =SUM(D2:D52)-J8

J8 es el total general, 21,175.70. G10 lo compara con la suma simple de los datos. Cero significa que cada pedido se contó una vez: no falta ningún canal ni estado en las etiquetas, y ninguno se contó dos veces.

Pensar como un analista

La tabla responde a la pregunta de Jordan de un vistazo. Los ingresos completados fueron 19,382.02. Los pedidos cancelados fueron 1,596.70, casi todos de la app (1,376.26). Los reembolsos fueron pequeños: un pedido web de 196.98. Antes de la reunión, la celda de comprobación es lo que señalas cuando alguien pregunta “¿esto incluye todo?”

Una celda de comprobación es un seguro barato en toda tabla resumen. Los datos cambian, las etiquetas tienen errores de tecleo y un estado nuevo aparece en la exportación del mes que viene. Un SUMIFS sobre una etiqueta que no coincide con ninguna fila simplemente muestra 0, así que sin la comprobación, una porción que falta es invisible.

Errores comunes

• Escribir una fórmula distinta por celda en lugar de una fórmula arrastrada.
• Fijar F5 y G4 por completo, de modo que cada celda muestra 8,757.22.
• Tomar los totales de los datos (=SUM(D2:D52)) en J8 en lugar de la tabla: la comprobación compara entonces los datos consigo mismos y siempre dice 0.
• Teclear el estado dentro de la fórmula ("completed"), lo que rompe el arrastre a lo ancho.