Hoy os vamos a enseñar a presentar datos en graficos de tendencias de manera que podamos analizar de un vistazo los periodos en los que ha ido aumentando o disminuyendo, en este caso, las ventas diarias en unidades de producto.
A menudo cuando se presentan gráficos de ventas se cae en el error de presentarlos con un gráfico de línea el cual no aporta más que información de que aquí subió y aquí bajó. Sin embargo, si queremos analizar el impacto que haya tenido una acción promocional (ya sea acción de marketing o comercial) necesitamos quizás ver que periodos han sido de crecimiento de ventas y cuales de descenso de las mismas.
Partimos de una tabla de ventas diarias (en un periodo de 6 meses) del que solo os muestro unos pocos datos:
Ahora añadimos una columna adicional, donde vamos a indicar si se produce un cambio de tendencia (obtendremos verdadero o falso) para ello usamos la formula MEDIANA, así:
Observad que le preguntamos a excel si el valor analizado está en medio de los valores anterior y posterior. Con esto vamos a poder dividir los valores de ventas en dos series distintas (crece y decrece) para así completar nuestros graficos de tendencias.
Antes de seguir, he creado nombres en excel para estas tres primeras columnas (fecha, ventas y cambio), tal y como vimos en el articulo correspondiente a nombres en excel.
Ahora creamos 4 columnas (ID, Fecha, Crece y Decrece), las cuales tienen la siguiente información:
- ID: Un valor numérico consecutivo que identifica el numero de días (en este caso, hasta 181, los días que tiene el primer semestre del año)
- Fecha: Formulamos un INDICE sobre el nombre fecha correspondiente al ID
- Crece: Formulamos para que haya valor en la columna si las ventas han crecido o cero si han decrecido
- Decrece:Formulamos también pero a la inversa
Una vez rellenamos esto arrastramos hacia abajo y creamos un grafico de área (obviamente cambiamos los colores a nuestro gusto, así como el eje de valores) y queda algo así:
Sin embargo, este gráfico no es muy bonito, ya que ambas series tienen un montón de valores cero y claro, queda un gráfico de puntas que no refleja exactamente lo que hablábamos. Por ello, vamos a crear otras series de datos, pero haciendo que las mismas tengan valores para todas las fechas.
Repetimos las cuatro columnas antes mencionadas, pero formulándolas así:
- ID: los dos primeros valores a mano, pero en el tercero ponemos en la fila 4 (suponiendo que ID es la columna J) SI(Y(J3<>J31;INDICE(cambio;J3));J3;J3+1)
Fijaros que lo que hacemos es no subir un valor el ID anterior solo sí los dos anteriores son distintos y hay cambio de tendencia - Fecha:Formulamos un INDICE sobre el nombre fecha correspondiente al ID, de manera que como hay ID duplicados hay también fechas duplicadas.
- Crece formulado tal como sigue: SI($J4=$J3;INDICE(crece;$J4);INDICE(decrece;$J4), es decir, si se trata de un ID igual al anterior, toma el valor de la columna decrece, y si es distinto, de la columna crece
- Decrece, exactamente igual que crece, cambiando obviamente el orden de los indices, para que coja el valor de la columna crece si el ID es repetido o de la columna decrece si no lo es
Ahora creamos otro de los graficos de tendencias con estos valores obtenidos (en mi caso, hay 293 filas de valores, es decir, hay 112 días con valor en ambas columnas.
Como veis, esto ya es otra cosa, el gráfico nos indica a primera vista que periodos ha habido con tendencia ascendente y cuales descendente. ¡MUY UTIL!
Si todavía no os habéis aburrido, o abandonado, os dejo un bonus! Ahora imaginad que queremos este gráfico pero con tendencias más a largo plazo.
Se puede hacer, pero para ello hay que crear 8 columnas nuevas (4 para obtener la media móvil, y 4 para la nueva serie de valores):
- Para la media móvil, las columnas Media Móvil, Cambio, Crece_mm y decrece_mm, a los cuales asignamos también nombres en excel.
- Media Móvil(en mi caso con de 15 días): SI(FILAS($A2:A$4)<$P$2;NA();MEDIA(B2:INDICE(ventas;FILAS($A2:A$4)-$P$2+1)))
- Cambio: Analizamos si la media móvil cambia de tendencia así: SI.ERROR(O4<>MEDIANA(H3:H5);FALSO)
- Crece_mm: Obtenemos valores con la formula: SI(RESIDUO(CONTAR.SI($P$4:P4;VERDADERO);2)=0;ventas;0) que parece una formula muy complicada pero en realidad está contando si el numero de cambios es par o impar para en función de eso asignar el valor adecuado
- Decrece_mm: Simplemente le damos valor a las filas donde crece_mm es cero, SI(crece_mm=0;ventas;0)
- Para la nueva serie de valores, hacemos lo mismo que con la segunda gráfica, cuatro columnas, que se formulan excatamente igual
Creamos el último de los graficos de tendencias, y ahora nos queda así:
Como véis, la cosa cambia, ya que sólo muestra cambios para aquellas caidas o subidas que són más continuadas.
Si tenéis alguna duda, podeis dejar vuestro comentario, por si acaso el libro de excel que he trabajado para que jugeis con el. Ojo, ya que si cambiaís el numero de días de la media movil, los valores de las columnas T:W podrían aumentar o disminuir.
Saludos a todos
