Hoy continuamos con la segunda parte del Inventario de Productos, en esta ocasión crearemos el Dashboard para tener un resumen de la información importante como: finanzas y control de stock. Filtraremos la información por año, usaremos tablas dinámicas y Query para obtener los datos correspondientes al año seleccionado. Video Tutorial.
{tocify} $title={Tabla de Contenido}
MODIFICAR DATOS: ENTRADAS Y SALIDAS
Teniendo en cuenta todo lo que ya creamos en el tutorial anterior, vamos a la hoja de Ingresos y asignaremos el texto 'Entrada' a cada producto disponible en la lista. Para aplicar esto a todo el rango podemos usar ArrayFormula de la siguiente forma:
=ARRAYFORMULA(SI(FILA($B$11:$B)=11,"TIPO",SI($B$11:$B<>"","ENTRADAS","")))
=ARRAYFORMULA(SI(FILA($B$11:$B)=11,"TIPO",SI($B$11:$B<>"","SALIDAS","")))
COMBINAR DATOS: ENTRADAS Y SALIDAS
En la hoja Inventario, elegimos la celda de nuestra preferencia y colocamos la formula de Query tomando de referencia el rango A11:F de la hoja Ingresos y Egresos, solo nos mostrará: fecha, producto, cantidad y tipo.
👀 VER FORMULA
▼
=QUERY({INGRESOS!A11:F; EGRESOS!A11:F}, "SELECT Col1, Col2, Col3, Col6 WHERE Col2 IS NOT NULL AND Col2 <> 'PRODUCTO' ORDER BY Col1 DESC", 0)
Adicional aplicamos condiciones a Query para ordenar las fechas de forma descendente, si en productos hay datos nulos no los traerá y tampoco traerá los encabezados de los rangos seleccionados. Esta combinación de datos la usaremos de referencia para crear tablas dinámicas y filtrar el inventario por año.
DASHBOARD FINANZAS
MENU DESPLEGABLE
Primero nombramos un rango como 'AÑO' con una lista de años disponibles para crear un menú desplegable. Seleccionamos la celda I2, vamos a Insertar > Menú Desplegable > Menú desplegable de un rango y establecemos =AÑO, en opciones avanzadas elegimos Rechazar entrada y guardamos los cambios.
TABLAS DINAMICAS
En la celda B10 insertaremos una Tabla Dinámica del rango INGRESOS!A11:F9 y completaremos la tabla agregando FECHA en filas y $COMPRA en valores, las fechas las agruparemos por mes y en filtro añadimos el grupo FECHA con filtro por condición > formula personalizada =AÑO(FECHA)=I2.
En la celda B20 insertaremos una Tabla Dinámica del rango EGRESOS!A11:F y completamos la tabla agregando FECHA en filas y $VENTA en valores, las fechas las agrupamos por mes y en filtro añadimos el grupo FECHA con filtro por condición con la formula personalizada =AÑO(FECHA)=I2
GRAFICOS DE BARRAS
Añadiremos gráficos de barras para las dos tablas dinámicas, así que seleccionamos primero el rango B10:C22, luego vamos a Insertar > Grafico, personalizamos los colores, tamaño y posición, por ultimo guardamos los cambios. Haremos lo mismo con el rango B28:C40, con estos gráficos podemos comparar los precios por mes y año.
TOP PRODUCTOS
Para analizar si estamos haciendo una buena inversión, debemos comparar las entradas y salidas en busca de productos populares, por ejemplo, si invertimos en Arroz, Aceite, etc. y resulta que tenemos mas salidas para Bebidas, Frutas, etc. en el próximo reabastecimiento aumentaremos las entradas a Bebidas y disminuiremos las entradas de Arroz.
Haremos consultas rápidas con la función QUERY para crear un top de 15 productos, una formula la colocamos en la celda J8 para entradas y el otro en J26 para las salidas con la siguiente formula:
👀 VER FORMULA
▼
- TOP ENTRADAS:
=SI.ERROR(QUERY(INGRESOS!$A$11:$E, "SELECT B, SUM(C) WHERE YEAR(A) = " &$I$2 & " AND C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC LIMIT 15 LABEL SUM(C) ''", 0), "😅 No hay datos")
- TOP SALIDAS:
=SI.ERROR(QUERY(EGRESOS!$A$11:$E, "SELECT B, SUM(C) WHERE YEAR(A) = " &$I$2 & " AND C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC LIMIT 15 LABEL SUM(C) ''", 0), "😅 No hay datos")
DASHBOARD INVENTARIO
En la celda M8 insertamos una Tabla Dinámica del rango INVENTARIO!F3:I y completamos la tabla agregado a Filas el grupo PRODUCTOS, en Columnas el grupo TIPO, en valores el grupo CANTIDAD y en Filtro el grupo FECHA con filtro por condición de formula personalizada =AÑO(FECHA)=I2.
Para traer los disponibles usaremos una formula con ArrayFormula y para las casillas de conteo fórmulas de Suma y Contar..Si de esta forma:
👀 VER FORMULA
▼
- DISPONIBLES:
=ARRAYFORMULA(SI(M9:M="", "", SI(FILA(M9:M)=9, "DISPONIBLE", SI(M9:M<>"", N9:N-O9:O, ""))))
- CANTIDAD INVENTARIO:
=SUMA(P9:P)
- PRODUCTOS INVENTARIO:
=CONTAR.SI(P9:P,">0")
- STOCK BAJO:
=CONTAR.SI.CONJUNTO(P9:P,"<=30",P9:P,"<>0")
- SIN STOCK
=CONTAR.SI.CONJUNTO(P9:P,"<=30",P9:P,"=0")
Con estas formulas quedaría completa nuestro Dashboard de Inventario 😊.