¿Cómo crear tablero de KPI de ventas?

Todo Funciones Gráficos Dashboards Macros y VBA Finanzas Plantillas Trucos Formato IA para Excel Transformación digital

Nuestro principal objetivo es mostrar los principales indicadores de desempeño clave y proporcionar una visión comparativa de otros KPI o productos usando elementos visuales interactivos.

En este tutorial, te mostraré cómo crear un panel de ventas en Excel.

Veamos cómo se ve el panel de ventas interactivo final:

Nuestro tablero de ventas de Excel se divide en cuatro partes por productos.

Será fácil comparar los principales indicadores. Para medir el rendimiento y crear el tablero de ventas en Excel es un procedimiento sencillo, aplicaremos la buena función de desplazamiento antiguo y el formato condicional.

Esta plantilla permite al usuario seleccionar la dimensión de los datos y muestra la eficiencia de ventas de inmediato. Esta plantilla dinámica nos permite verificar las ventas por tipo de consumidor o ubicación y examinar los valores con promedios también.

La función DESREF ayuda a determinar el ranking y la agrupación tan pronto como sea posible y con ayuda de formato podemos analizar las diferencias altas y bajas.

Los informes de ventas de Excel más utilizados informan al equipo de ventas ya la dirección de qué productos se venden bien.

Debemos clasificar los productos más altos y más pobres que realizan para determinar qué productos están fallando al auge con sus clientes.

Las tres unidades de nuestro tablero KPI:

KPI es la hoja de cálculo principal de Excel; Resumimos y visualizamos los resultados aquí.
Ahora vaya a la hoja de datos, aquí están los datos principales.
En la hoja de cálculo se puede encontrar el área de cálculo.

Los pasos a seguir lo detallamos a continuación:

Tenemos los datos de KPI sin procesar para 20 vendedores y para 4 productos. Los datos se ven como se muestra a continuación.

Primero cree un rango nombrado para los cálculos adicionales. Seleccione la celda o rango, en este ejemplo usaremos ambos. Seleccione el rango: =datos!$D$3:$D$22 en la hoja de datos.

Define los nombres del rango de datos

Ir a la pestaña FÓRMULAS En el grupo NOMBRES DEFINIDOS, haga clic en DEFINIR NOMBRE. Introduzca un nombre para el rango. Seleccione la celda D4 y agregue ingrese un nombre (Ordenar).

Siga el ejemplo como en la imagen de abajo ->

Utilice la función DESREF para crear un rango dinámico con nombre para cálculos adicionales.

Trae los datos con DESREF

En la hoja de cálculo, escriba: = DESREF (datos!$C3;0;ordenar).

Explicación: ¿Cómo obtener el nombre correcto de la lista sin clasificar? =DESREF (nombre de la cuenta de ventas de la tabla de datos, 0 = misma fila, primera posición)

Utilice la fórmula =D8+$B8/1000000000 para crear una lista única.

Para obtener el valor adecuado en un rango, puede utilizar la función CHOOSE junto con las funciones K. ESIMO.MAYOR y K. ESIMO.MENOR.

=ELEGIR($D$5;K.ESIMO.MAYOR($E$8:$E$27;$B8);K.ESIMO.MENOR($E$8:$E$27;$B8)).

Qué hace cada fórmula, explicada

La fórmula ELEGIR devuelve un valor de una lista utilizando una posición o índice dado. Por ejemplo, ELEGIR (3, «KPI1», «KPI2», «KPI3») devuelve «KPI3», ya que KPI3 es el 3er valor que aparece después del número de índice.

La función K. ESIMO.MAYOR recupera valores numéricos basados en su posición en una lista cuando se ordenan por valor.

La función K. ESIMO.MENOR recupera valores numéricos basados en su posición en una lista cuando se ordenan por valor.

Para obtener la posición de un elemento en una matriz, aplique la función COINCIDIR.

=COINCIDIR(F8;$E$8:$E$27;0)

Ahora use el =DESREF(DATOS!D$2;CÁLCULOS!$G8;0) para calcular la posición para el Producto 1.

¡Salta a la hoja de KPI! Puede insertar botones de opción para facilitar la entrada de datos.

Añade los botones de opción

Los botones de opción son perfectos cuando solo tienes una opción.

Para agregar el botón de opción, haga clic en la ficha DESARROLLADOR, haga clic en Insertar. Finalmente, en CONTROLES DE FORMULARIOS, haga clic en el icono del botón de opción.

Para agregar celda vinculada, haga clic derecho en el botón de opción y, a continuación, haga clic en Formato de control.

Salta a la pestaña Control. En el cuadro de enlace de celda, escriba una referencia de celda que contiene el estado actual del botón de opción.

Utilice la misma celda vinculada (D4 en la hoja de cálculo) para el Producto 1 al Producto 4.

Para asignar una macro a la imagen insertada (icono), ejecute los siguientes pasos:

Haga clic con el botón derecho en la imagen. Elija el comando Asignar macro. Agregar una macro de la lista.

La macro es muy simple. Creo que no es necesario dar más explicaciones.

Puede combinar la herramienta de formato condicional con su configuración de KPI.

Para administrar todas las reglas de formato condicional en esta hoja de cálculo, utilice el Administrador de reglas de formato condicional.

Da color con formato condicional

En la ficha Inicio, haga clic en Formato condicional y, a continuación, haga clic en Administrar reglas.

Seleccione la opción ‘Formatear todas las celdas en función de sus valores’. Esta es la forma más fácil de crear un formato condicional basado en KPI.

Para insertar un control deslizante en Excel, vaya a la pestaña DESARROLLADOR -> INSERTAR -> BARRA DE DESPLAZAMIENTO.

Inserta la barra de desplazamiento

Haga clic en el botón Barra de desplazamiento e insertarlo en la hoja de cálculo.

Haga clic con el botón derecho en la barra de desplazamiento y haga clic en ‘Control de formato’. Esto abrirá un cuadro de diálogo de control de formato.

Vaya a la pestaña ‘Control’ y realice los siguientes cambios para crear una lista de desplazamiento: