Cómo calcular el cambio porcentual con tablas dinámicas en Excel

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

logotipo de Excel

Las tablas dinámicas son una increíble herramienta de informes incorporada en Excel. Aunque normalmente se utilizan 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 las ventas cambia mes a mes.

Aquí está la hoja que vamos a usar.

Dos años de datos de ventas para una tabla dinámica

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 vamos a crear una tabla dinámica para hacer y mostrar nuestros cálculos de cambio porcentual.

Dar formato al rango como una tabla

Si su rango de datos aún no está formateado como una 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.

Crear cuadro de diálogo para especificar el rango de celdas

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 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» debajo de Herramientas de tabla e ingrese un nombre en el cuadro provisto al comienzo de la cinta. Esta tabla se ha denominado «Ventas».

Nombra la tabla en Excel

También puede cambiar el estilo de la tabla aquí si lo desea.

Crear una tabla dinámica para mostrar el cambio porcentual

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.

La ventana Crear tabla dinámica

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 «Agrupar».

Agrupar fechas en una tabla dinámica

Seleccione los grupos que desea utilizar. En este ejemplo, solo se seleccionan Años y Meses.

Especificar años y meses en el cuadro de diálogo Grupo

El año y el mes son ahora campos que podemos utilizar para el análisis. Los meses todavía se denominan Fecha de pedido.

Campos de años y fecha de pedido en filas

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 no parezca mucho todavía. Pero eso cambiará muy pronto.

Campo de valor de ventas agregado dos veces a la tabla dinámica

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 formato.

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í:

Dar formato a la primera columna

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».

Mostrar valores como diferencia porcentual

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).

Seleccione Anterior como el elemento base para comparar

La tabla dinámica ahora muestra los valores y el cambio porcentual.

Mostrar valores y 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».

Cambiar el nombre de los encabezados de la tabla dinámica

Agregue 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, realice los siguientes pasos:

  1. Seleccione la opción «Todas las celdas que muestran valores de» Variación «para la fecha de pedido».
  2. Seleccione «Conjuntos de iconos» de la lista Estilo de formato.
  3. Seleccione los triángulos rojo, ámbar y verde de la lista Estilo de icono.
  4. 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.

La tabla dinámica de varianza completa

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.

setTimeout(function(){
!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s) } (window, document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘335401813750447’);
fbq(‘track’, ‘PageView’);
},3000);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.