Análisis con funciones matriciales

-
El concepto de cuadro de mando proviene del concepto denominado tableau de bord en Francia, que traducido de manera literal,significa tablero de mandos, o cuadro de instrumentos, como los que se encuentran en el salpicadero de un coche.

La gestión de las empresas requiere un sistema de indicadores (en inglés KPIs o Key Performance Indicators) que nos faciliten la toma de decisiones y el control. Se requiere un sistema completo de análisis.

Avanzando en este concepto encontramos también el término Inteligencia empresarial o  inteligencia de negocios o BI (del inglés business intelligence), al conjunto de estrategias, aplicaciones, datos, productos, tecnologías y arquitectura técnicas, los cuales están enfocados a la administración y creación de conocimiento sobre el medio, a través del análisis de los datos existentes en una organización o empresa.

En lo que al análisis de datos se refiere, Excel es un eficaz instrumento, sobre todo desde la incorporación de los complementos: Power Query, Power Pivot, Power View y Power Maps. Complementos que Microsoft ha aglutinado en Power BI; facilitando la transmisión y actualización de datos. 
Más adelante y en este blog abriremos un capítulo para Power BI
Para inaugurar esta sección, propongo la realización de un informe dinámico de ventas que nos muestre la cantidad de vehículos vendidos según día de la semana y mes. a partir de una relación histórica de ventas, pudiendo filtrar por país, distribuidor y concesionario; y lo elaboraremos utilizando funciones matriciales.
Una formula matricial en Excel es una fórmula que trabaja con matrices de datos. Las fórmulas matriciales no trabajan con un solo valor sino con una serie de datos.

El ejemplo.

En primer lugar a nuestro listado de datos compuesto por seis columnas (fecha, país, distribuidor, concesionario, modelo y unidades vendidas) vamos a darles nombre lo que nos facilitará a la hora de establecer las referencias en las fórmulas que utilicemos. Para ello seleccionamos todos los datos (incluidos los encabezados) y elegimos la opción crear desde la selección:

A continuación, creamos la tabla donde se mostrarán los datos, con el objetivo de que cada celda muestre la suma de ventas realizadas en el día de la semana y mes indicados, de modo que podamos saber en cada mes cual es la cantidad que se ha vendido en cada día de la semana, o en un día de la semana concreto saber lo que se vende en los distintos meses, lo que puede ser un dato interesante a la hora de establecer promociones.
Para ello utilizaremos una función que sume, desde la tabla de datos, aquellas cantidades que se han vendido el mes y día de la semana que indique cada una de las celdas. Pero, pero en la tabla de datos solamente tenemos el dato fecha, para poder extraer esta información (mes y día), en primer lugar, utilizaremos la función texto:

=Texto(Fecha;"dddd") que mostrará el día de la semana
=Texto(Fecha;"mmmm") que mostrará el mes

Función matricial.

Para que en la primera celda (donde confluye Enero y Lunes) muestre la suma de las cantidades vendidas todos los lunes de Enero, usamos la siguientes función matricial:
{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4;Cantidad))}

 Antes de continuar voy a desgranar cada una de las partes de esta expresión:
  • TEXTO(Fecha;"mmmm"), el primer argumento que hemos utilizado es el nombre de la columna Fecha, des este modos evitamos tener que seleccionar todo el rango de la columna.
  • TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd"),  concatenar. Juntamos las dos funciones en una (MesDíadelasemana)
  • SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4, si en la tabla de datos la expresión (MesDíadelasemana) es igual a EneroLunes (celdas B5 y C4).
  • SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4;Cantidad), realiza calculo con los datos de la columna Cantidad.
  • =SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4;Cantidad)), el cálculo ha realizar es la suma.
  • Para finalizar, y que sea una función matricial se presiona Control+Shift+Enter y se mostrarán los corchetes que enmarcan la expresión.

Ahora ya podemos copiar la función en el resto de celdas

Filtros.

Pero se trata de un informe estático, para hacerlo dinámico vamos a aplicar una serie de filtros: Año, País, Distribuidor y Concesionario. Para que el usuario pueda detallar a su gusto la información.

Año.

Los datos de nuestro ejemplo (inventado) recoge datos desde el año 2014 al 2018. Para construir este primer filtro, creamos una lista con dichos valores y utilizamos la opción validar datos.De este modo, el usuario con la utilización del desplegable podrá seleccionar el año para el que quiere ver las ventas por día y mes.
Para que el filtro funcione, tenemos que modificar la función matricial, añadiendo que la condición que se debe cumplir es que además de mes y día de la semana debe coincidir con el año seleccionado por el usuario. Así la función matricial quedará de la siguiente forma:

{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)=$B5&C$4&$C$18;Cantidad))}

País.

Vamos a generar la lista de países, para ello:
  • Copiamos la columna países a continuación de la lista de años que hemos creado.
  • Eliminamos duplicados.
  • Ordenamos.
  • Creamos el desplegable con la opción validación de datos.
Volvemos a modificar nuestra función matricial, para incorporar el nuevo filtro.

{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País=$B5&C$4&$C$18&$F$18;Cantidad))}

Distribuidor.

Repetimos los mismos pasos que en el filtro anterior pero con los datos relativos al distribuidor y volvemos a modificar nuestra función matricial.

{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor=$B5&C$4&$C$18&$F$18&$I$18;Cantidad))}

Concesionario

Repetimos los pasos de los filtros anteriores y modificamos la función matricial

{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor&Concesionario=$B5&C$4&$C$18&$F$18&$I$18&$I$20;Cantidad))}

Elegir.

Hemos construido un informe con cinco filtros:

  • Mes y día de la semana,
  • Año,
  • País,
  • Distribuidor, y
  • Concesionario.
Filtros que funcionan simultáneamente, Pero vamos a permitir que el usuario decida cuantos filtros aplicar. Para ello en primer lugar creamos una lista con los cinco niveles de filtro:

1. Mes y día de la semana. La función que utilizábamos era: 
=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4;Cantidad))

2  Mes y día de la semana + Año. Siendo la función:
{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)=$B5&C$4&$C$18;Cantidad))}

3. Mes y día de la semana + Año+ País. Con la función
{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País=$B5&C$4&$C$18&$F$18;Cantidad))}

4. Mes y día de la semana + Año+ País+Distribuidor. En este caso la función quedaba:
{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor=$B5&C$4&$C$18&$F$18&$I$18;Cantidad))}

5. Mes y día de la semana + Año+ País+Distribuidor+Concesionario. Para lo que creamos la fucnión:
{=SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor&Concesionario=$B5&C$4&$C$18&$F$18&$I$18&$I$20;Cantidad))}.

Utilizando la función Elegir permitiremos que el usuario indicando el nivel de filtros (del 1 al 5)  se aplique la función correspondiente así pues tras crear el desplegable de Nº de filtros modificaremos nuestra función matricial del siguiente modo:

{=ELEGIR($I$3;

SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")=$B5&C$4&;Cantidad));

SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)=$B5&C$4&$C$18;Cantidad));

SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País=$B5&C$4&$C$18&$F$18;Cantidad));

SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor=$B5&C$4&$C$18&$F$18&$I$18;Cantidad));
SUMA(SI(TEXTO(Fecha;"mmmm")&TEXTO(Fecha;"dddd")&AÑO(Fecha)&País&Distribuidor&Concesionario=$B5&C$4&$C$18&$F$18&$I$18&$I$20;Cantidad)))}


Formato.

En cuestión de formato vamos a realizar dos acciones. Primero, que se resalte la celda que contiene el valor máximo, y en segundo lugar mostrar gráficamente en cada celda su valor en relación con el máximo. Para ello:
1.- Calculamos el valor máximo para todo el rango de celdas.

 2.- Seleccionamos el rango de celdas y aplicamos formato condicional y resaltar aquella celda cuyo valor sea igual al valor máximo.

3.- Para el valor relativo volvemos a seleccionar el rango de celdas y de nuevo la opción formato condicional, pero en este caso nos decantamos por barra de datos
4.- Si lo dejamos tal y como está la celda que contiene el valor igual al máximo , se ve es cubierta totalmente por la barra, ocultando el formato anterior.

5.- Para solucionarlo, con todo el rango de datos seleccionado, abrimos formato condicional y seleccionamos la opción administrar reglas. Nos posicionamos en la que se refiere a barra de datos y presionamos la opción Editar regla.. En la nueva ventana, bajo la opción barra más corta en tipo seleccionamos número y en valor dejamos o; y para la barra más larga en tipo seleccionamos número y en valor introducimos la celda que refleja el valor máximo y lo multiplicamos por 2.
6.- Para finalizar le incorporamos un gráfico y ya tendremos nuestro análisis dinámico finalizado.



Si quieres intentarlo, desde aquí puedes descargar el fichero Datos iniciales con los que realizar el ejercicio. Y puedes ver el proceso completo en el siguiente vídeo.

Comentarios

Publicar un comentario

Entradas populares de este blog

Tablero KANBAN

-
Imagen
Un tablero Kanban es una herramienta para mapear y visualizar un flujo de trabajo. Las versiones más simples constan de tres columnas: para distinguir las tareas que están pendientes de hacer, las que están en marcha y las que ya están hechas. Mientras que las filas representan los diferentes tipos de actividad. Su lógica es simple, si en una columna entran las tareas más rápido de lo que salen el trabajo comenzará a acumularse y el problema se hará visible para todo el equipo, Habiendo libertad para crear tantas columnas o subsecciones que se necesiten para visualizar un flujo de trabajo, según el proyecto, empresa o actividad; en este post tan solo vamos a mostrar una forma de como construir un modelo, con el fin de que puedas aplicar el modo a tus necesidades. Vamos a desarrollar el trabajo de dos modos en función de si somos usuarios de 365 o no. En ambos casos partimos de la misma tabla  Actividades . Aquí te puedes descargar el archivo. También puedes este post en YouTube El...
Leer más

Buscar y traer múltiples imágenes en Excel

-
Imagen
  La función BUSCARV, nos permite obtener información, en base a un valor buscado, y esa información puede estar en otro libro o en otra hoja de cálculo. En este ejemplo, contamos con una plantilla, en la que, seleccionando el nombre del equipo, se mostrarán los ID, de cada uno de los miembros de dicho equipo, así como la información relativa de cada uno de ellos. Esta información la tenemos disponible en la hoja BASE, así como una imagen de cada uno de ellos. Pero, además, queremos que nos muestre una imagen de cada uno de los integrantes del equipo. Para ello en la hoja Base, tenemos el nombre de la imagen, tal y como está guardada en la carpeta Emoticones. El archivo con el que estamos trabajando está dentro de la carpeta Ejemplo Macros , que a su vez contiene la carpeta Emoticones, donde están las imágenes de cada uno de los jugadores. Observa que los nombres de los archivos de la carpeta Emoticones, coinciden con los que figuran en la columna foto de hoja BASE. Así p...
Leer más

Si(), SI.CONJUNTO() y BUSCARV()

-
Imagen
 Tres formas de resolver el mismo problema. En el ejercicio que planteamos deseamos conocer el importe a pagar por cada uno de los pasajeros del vuelo Dallas-Bogotá.  El precio del billete está marcado en 220 USD, pero se aplica una serie de descuentos en función de la edad del pasajero, datos que tenemos recogidos en la tabla Tarifas: Por otro lado tenemos el listado de pasajeros con su fecha de nacimiento y edad: Solución con la función SI() La función SI(prueba lógica; [valor_si_verdadero];[valor_si_falso]), dispone de tres argumentos: Prueba lógica . En este apartado de la función nos solicita que le planteemos " una pregunta"  cuya respuesta sea "Sí"  o "No" . Valor si verdadero.  En este segundo argumento, nos pide que indiquemos que valor, función u operación deseamos realizar en el caso de que la respuesta a la pregunta sea Sí . Valor si falso. Por último, en el tercer argumento, nos pide que indiquemos que valor, función u operación deseamos reali...
Leer más