Crear panel de costos para las áreas de tu empresa

En este tutorial crearemos un panel de costos para las distintas áreas con funciones interesantes.

Si quieres verificar cuanto a gastado cada área a lo largo de un período este artículo es para ti.

Para empezar, llenaremos la tabla arbitrariamente, pero definiremos nuestras áreas a evaluar, las cuales son: Marketing; Finanzas; Recursos Humanos; Producción; Logística, estas nos sirven para examinar su comportamiento en un período de 12 años.

Luego insertaremos en la parte superior de la tabla la lista desplegable, sigue esta ruta à Pestaña DESARROLLADOR à INDICE à CUADRO COMBINADO (CONTROLES DE FORMULARIO).

En Rango de entrada vamos a seleccionar los datos de la tabla sin encabezados, en la sección vincular con la celda referenciaremos la celda “a1” de la hoja “Dashboard” y en líneas de unión verticales dejamos el valor por defecto.

Vemos como aparecen los números del 1 al 5, ahora para saber qué área es la que indica cada número vamos a insertar la siguiente fórmula en la celda “B1” de la hoja DASHBOARD: =INDICE(B7:B11;A1;1), quedaría algo como esto:

Ahora creamos una nueva hoja que se llamará “Borrador”, en esta vamos a mostrar el área y sus números durante los años, además insertaremos imágenes.

Definimos a los rangos

“A1:C6” como “Marketing”

El proceso lo repetimos para los siguientes rangos:

“D1:F6” como “Finanzas”

“G1:C6” como “RRHH”

“J1:L6” como “Producción”

“M1:O6” como “Logística”

Ahora insertaremos las imágenes que representen a cada una de las áreas, pero estas deben caber dentro de los rangos nombrados como se ve a continuación:

Ahora vamos a mostrar los números de los años para el área seleccionada en la lista desplegable, primero ingresamos todo el encabezado de la tabla. Luego vamos a utilizar dos fórmulas, una para el área y otra para los años.

El área será identificada así: =INDICE(AREAS;DASHBOARD!A1)

En el Año 1 será calculado así: =BUSCARV(B9;DASHBOARD!B7:N11;2;0)

Esta fórmula será jalada hasta el último año.

Ahora con esta data extraída, vamos a insertar un gráfico en la hoja “Dashboard”, en este caso yo he elegido el gráfico “Columnas en 3D” y he eliminado el título y la leyenda que aparece por defecto, el resultado es el siguiente:

Ahora definiremos “IMAGEN”, seguimos la siguiente ruta: pestaña FÓRMULAS à ASIGNAR NOMBRE, aparecerá esta ventana, en ella definimos como nombre IMAGEN y en el campo “Se refiere a” vamos a insertar la siguiente fórmula: =ELEGIR(DASHBOARD!$A$1;MARKETING;FINANZAS;RRHH;PRODUCCIÓN;LOGISTICA)

Finalmente procedemos a colocar la imagen dinámica:

Insertamos cualquier imagen, luego en la barra de fórmulas vamos a ingresar esto: =IMAGEN , para cambiar la imagen según el área que este seleccionada en la lista desplegable.

Ahora para poder resaltar el área seleccionada, nos apoyaremos en la herramienta “Formato Condicional”, vamos a condicionar con las fórmulas representadas en la siguiente imagen:

Recuerda que el formato de fondo es libre. El resultado final de este cuadro sería el siguiente: