Informes tipo tabla dinámica con fórmulas

Una tabla dinámica cruza los datos: filas para una categoría, columnas para otra y totales que llenan la cuadrícula. Un ejemplo clásico es mostrar la Región en el lateral, el Trimestre en la parte superior y las Ventas en cada celda.

Las tablas dinámicas son excelentes, pero necesitan actualizarse manualmente y ocupan un bloque fijo. Una tabla dinámica basada en fórmulas se reconstruye automáticamente cada vez que cambian los datos.

En esta lección organizará los encabezados de fila, los encabezados de columna y un cuerpo de fórmulas SUMIFS que calcularán automáticamente cada intersección.

Utilizaremos una hoja llamada Sales con estas columnas: Región en A, Trimestre en B e Importe en C, en las filas 2 a 500.

El informe que queremos tiene este aspecto:

• Etiquetas de fila: cada Región sin duplicados en la columna E.
• Etiquetas de columna: Q1, Q2, Q3 y Q4 en la fila 1, de F a I.
• Cuerpo: el Importe total de cada combinación de Región y Trimestre.

Cada celda del cuerpo responde a una pregunta: ¿cuánto vendió esta región en este trimestre?

Los encabezados de fila son las regiones sin duplicados. Utilice UNIQUE con SORT para que se desborden hacia abajo en la columna E y permanezcan ordenados.

Coloque esto en E2:

Ahora las regiones llenan E2 y las celdas inferiores por sí solas. Al igual que en las tablas de resumen, esta lista es el ancla a la que apunta toda la cuadrícula.

Los encabezados de columna son los trimestres distribuidos a lo largo de una fila. Puede escribir Q1, Q2, Q3 y Q4 manualmente o desbordarlos horizontalmente con TRANSPOSE alrededor de UNIQUE.

En F1, esto coloca los trimestres sin duplicados en la parte superior:

TRANSPOSE convierte una lista vertical en una horizontal, de modo que una columna de trimestres se convierte en una fila de encabezados. Ahora ambos ejes de la cuadrícula están en su sitio.

Ahora rellene el cuerpo. Cada celda necesita el total correspondiente a la región de su fila y al trimestre de su columna. SUMIFS gestiona fácilmente dos condiciones.

En la primera celda del cuerpo, F2, escriba:

Esto lee el Importe cuando Región es igual a la etiqueta de la izquierda y Trimestre es igual al encabezado superior. Es una única intersección de la tabla dinámica.

Los signos de dólar permiten copiar una fórmula para rellenar toda la cuadrícula. Examine las referencias mixtas:

• $E2 fija la columna en E, pero permite que cambie la fila, de modo que cada fila lee su propia región.
• F$1 fija la fila en 1, pero permite que cambie la columna, de modo que cada columna lee su propio trimestre.
• $C$2:$C$500 está completamente fijada porque el rango de datos nunca cambia de posición.

Copie F2 en todos los trimestres y hacia abajo en todas las regiones; cada celda se ajustará correctamente por sí sola.

Una vez escrita correctamente F2, selecciónela y arrastre el controlador de relleno hacia la derecha, por todas las columnas de trimestres, y después hacia abajo, por todas las filas de regiones. Excel reescribirá las partes relativas por usted.

• La celda G2 se convierte en Región $E2 y Trimestre G$1.
• La celda F3 se convierte en Región $E3 y Trimestre F$1.

El resultado es una tabla cruzada completa con todas las intersecciones totalizadas. No necesita ningún asistente de tabla dinámica y se recalcula en cuanto cambian los datos de Sales.

Una tabla dinámica real muestra totales generales. Añada una columna Total a la derecha y una fila Total en la parte inferior utilizando SUM simple en cada línea.

Para obtener el total de la fila de la primera región, coloque esto en la columna posterior al último trimestre:

Para obtener el total de una columna, sume hacia abajo las celdas del cuerpo correspondientes a ese trimestre. Estos totales de los extremos hacen que el informe parezca completo y permiten a los lectores comprobar rápidamente que las cifras son razonables.

Si su herramienta lo admite, puede evitar copiar las fórmulas introduciendo directamente referencias de desbordamiento en SUMIFS. Utilice los encabezados desbordados como criterios.

Esta única fórmula obtiene el total de todas las intersecciones de región y trimestre:

Aquí, E2# es la lista vertical de regiones y F1# es la lista horizontal de trimestres. Excel las combina para crear una cuadrícula completa de una sola vez. El método de arrastrar es más compatible, pero esta es la elegante versión moderna.

Los informes son más útiles cuando muestran la proporción, no solo los importes. Añada una columna que exprese el total de cada región como porcentaje del total general.

Si el total de la fila de la región está en J2 y el total general se encuentra en J10, escriba:

Fijar el total general con $J$10 permite rellenar la fórmula hacia abajo para todas las regiones, mientras que siempre divide entre el mismo denominador. Dé formato de porcentaje a la columna y los lectores verán al instante qué regiones predominan.

Algunos hábitos mantienen fiable una tabla dinámica basada en fórmulas:

• Haga referencia a rangos completos y amplios, como las filas 2 a 500, para incluir las filas nuevas.
• Fije los rangos de datos con anclajes $ completos; solo deben moverse las referencias de los encabezados.
• Deje espacio en blanco debajo y a la derecha para que los encabezados y totales desbordados tengan sitio.

Si se hace correctamente, este informe no requiere ningún mantenimiento. Introduzca ventas nuevas y la cuadrícula, los totales y las etiquetas se actualizarán por sí solos.

Compruebe cuánto domina las referencias mixtas que hacen posible una tabla dinámica basada en fórmulas.

Ha recreado una tabla dinámica utilizando únicamente fórmulas:

• UNIQUE junto con SORT creó los encabezados de fila en una columna desbordada.
• TRANSPOSE distribuyó los encabezados de columna a lo largo de una fila.
• SUMIFS con las referencias mixtas $E2 y F$1 rellenó todas las intersecciones, ya fuera arrastrando la fórmula o utilizando referencias de desbordamiento como E2# y F1#.
• SUM añadió los bordes de totales generales.

Toda la cuadrícula se recalcula en tiempo real. A continuación hará que el panel sea interactivo con listas desplegables que controlarán las métricas.