Las tablas dinámicas son una increíble herramienta de informes incorporada en Excel. Aunque normalmente se utiliza para resumir datos con totales, también puede utilizarlos para calcular el porcentaje de cambio entre valores. Aún mejor: es fácil de hacer.
Puede usar esta técnica para hacer todo tipo de cosas, prácticamente en cualquier lugar donde le gustaría ver cómo se compara un valor con otro. En este artículo, usaremos el ejemplo sencillo de calcular y mostrar el porcentaje por el cual el valor total de ventas cambia mes a mes.
Aquí está la hoja que vamos a usar.
Es un ejemplo bastante típico de una hoja de ventas que muestra la fecha del pedido, el nombre del cliente, el representante de ventas, el valor total de las ventas y algunas otras cosas.
Para hacer todo esto, primero vamos a formatear nuestro rango de valores como una tabla en Excel y luego crearemos una tabla dinámica para hacer y mostrar nuestros cálculos de cambio porcentual.
Tabla de contenido
Dar formato al rango como una tabla
Si su rango de datos aún no tiene el formato de tabla, le recomendamos que lo haga. Los datos almacenados en tablas tienen múltiples beneficios sobre los datos en rangos de celdas de una hoja de trabajo, especialmente cuando se usan tablas dinámicas (leer más sobre los beneficios de usar tablas).
Para formatear un rango como una tabla, seleccione el rango de celdas y haga clic en Insertar> Tabla.
Verifique que el rango sea correcto, que tenga encabezados en la primera fila de ese rango, y luego haga clic en «Aceptar».
El rango ahora tiene el formato de tabla. Nombrar la tabla hará que sea más fácil hacer referencia a ella en el futuro al crear tablas dinámicas, gráficos y fórmulas.
Haga clic en la pestaña «Diseño» en Herramientas de tabla e ingrese un nombre en el cuadro provisto al comienzo de la cinta. Esta tabla se ha denominado «Ventas».
También puede cambiar el estilo de la tabla aquí si lo desea.
Crear una tabla dinámica para mostrar el cambio de porcentaje
Ahora sigamos con la creación de la tabla dinámica. Desde dentro de la nueva tabla, haga clic en Insertar> Tabla dinámica.
Aparece la ventana Crear tabla dinámica. Habrá detectado automáticamente su mesa. Pero puede seleccionar la tabla o el rango que desea usar para la tabla dinámica en este momento.
Agrupar las fechas en meses
Luego, arrastraremos el campo de fecha por el que queremos agrupar al área de filas de la tabla dinámica. En este ejemplo, el campo se denomina Fecha de pedido.
A partir de Excel 2016, los valores de fecha se agrupan automáticamente en años, trimestres y meses.
Si su versión de Excel no hace esto, o simplemente desea cambiar la agrupación, haga clic con el botón derecho en una celda que contenga un valor de fecha y luego seleccione el comando «Grupo».
Seleccione los grupos que desea utilizar. En este ejemplo, solo se seleccionan Años y Meses.
El año y el mes son ahora campos que podemos usar para el análisis. Los meses todavía se denominan Fecha de pedido.
Agregar los campos de valor a la tabla dinámica
Mueva el campo Año de Filas al área Filtro. Esto permite al usuario filtrar la tabla dinámica durante un año, en lugar de saturar la tabla dinámica con demasiada información.
Arrastre el campo que contiene los valores (Valor total de ventas en este ejemplo) que desea calcular y presente el cambio al área Valores dos veces.
Puede que todavía no parezca mucho. Pero eso cambiará muy pronto.
Ambos campos de valor se habrán predeterminado para sumar y actualmente no tienen formato.
Los valores de la primera columna nos gustaría mantenerlos como totales. Sin embargo, requieren formateo.
Haga clic con el botón derecho en un número en la primera columna y seleccione «Formato de número» en el menú de acceso directo.
Elija el formato «Contabilidad» con 0 decimales en el cuadro de diálogo Formato de celdas.
La tabla dinámica ahora se ve así:
Crear la columna de cambio de porcentaje
Haga clic con el botón derecho en un valor en la segunda columna, seleccione «Mostrar valores» y luego haga clic en la opción «% de diferencia de».
Seleccione «(Anterior)» como el elemento base. Esto significa que el valor del mes actual siempre se compara con el valor de los meses anteriores (campo Fecha de pedido).
La tabla dinámica ahora muestra los valores y el cambio porcentual.
Haga clic en la celda que contiene las etiquetas de fila y escriba «Mes» como el encabezado de esa columna. Luego haga clic en la celda del encabezado de la segunda columna de valores y escriba «Varianza».
Agregar algunas flechas de variación
Para pulir realmente esta tabla dinámica, nos gustaría visualizar mejor el cambio porcentual agregando algunas flechas verdes y rojas.
Estos nos proporcionarán una forma encantadora de ver si un cambio ha sido positivo o negativo.
Haga clic en cualquiera de los valores en la segunda columna y luego haga clic en Inicio> Formato condicional> Nueva regla. En la ventana Editar regla de formato que se abre, siga los siguientes pasos:
Seleccione la opción «Todas las celdas que muestran valores de» Variación «para la fecha de pedido».
Seleccione «Conjuntos de iconos» de la lista Estilo de formato.
Seleccione los triángulos rojo, ámbar y verde de la lista Estilo de icono.
En la columna Tipo, cambie la opción de lista para decir «Número» en lugar de Porcentaje. Esto cambiará la columna Valor a ceros. Exactamente lo que queremos.
Haga clic en «Aceptar» y el formato condicional se aplica a la tabla dinámica.
Las tablas dinámicas son una herramienta increíble y una de las formas más sencillas de mostrar el cambio de porcentaje a lo largo del tiempo para los valores.