Control de almacén en Excel

Llevar el control de almacén es importante cuando tienes un negocio de venta de productos. Te ayuda a llevar el control del stock de tu almacén y saber en todo momento con cuantos productos cuentas. En este tutorial veremos paso a paso cómo llevar un inventario de productos con lotes.

Organización del inventario

En este inventario vamos a introducir algunas modificaciones con respecto al anterior tutorial de inventario y stock que varios usuarios han pedido.

El inventario está dividido en 3 hojas; inventario, entradas y salidas.

En la hoja de inventario se muestra:

  • Código del producto
  • Descripción del producto
  • Lote del producto. Para un mismo producto tendremos diferentes lotes.
  • Entradas, salidas y stock. Estos tres campos estarán automatizados con fórmulas.
  • Coste unitario e importe inventariado. Estas dos columnas son opcionales, por lo que no vienen incluidas en la plantilla.
control de almacén en Excel

En la hoja de entradas y salidas se muestra:

  • Número de factura, es opcional
  • Fecha de la entrada o salida, es opcional pero recomendamos incluirla.
  • Código de producto, que debes introducir manualmente.
  • Descripción del producto, este campo está automatizado.
  • Lote, debes introducirlo manualmente.
  • Cantidad de entrada o salida que también debes introducir.
control de almacén

Paso 1. Convertir rangos en tablas.

El primer paso para que el inventario funcione correctamente es convertir todos los rangos de datos en tablas.

Para ello, coloca el cursor del ratón en cualquiera de las celdas que contenga datos y presiona CTRL+T. Aparecerá una ventana emergente, debes marcar la opción “Mi tabla tiene encabezados” y pulsa Aceptar.

Una vez creada, en la pestaña Diseño en la parte izquierda donde dice Nombre de la tabla escribe Productos para la primera tabla de inventario.

Debes realizar esta operación en las tres hojas con los tres rangos de datos y asignar los nombres correspondientes a cada una; Productos, entradas y salidas.

Aprende más sobre las tablas en el tutorial tablas de Excel y sus ventajas

Automatiza las entradas y salidas de control de almacén

En la tabla de entradas debes introducir el número de factura, la fecha y el código del producto.

Para que se muestre de forma automática la descripción del producto debes utilizar la función BUSCARV.

La sintaxis de la función: BUSCARV(valor_buscado; matriz_tabla; indicador_columnas; rango) donde:

  • Valor buscado será el código de esa fila.
  • La matriz_tabla será el rango de datos donde se encuentre tanto el valor buscado como el valor que queremos que nos arroje la función. En este caso la tabla de Productos. Puedes escribir directamente el nombre en este argumento.
  • Indicador_columnas se refiere a la posición de la columna donde está el dato que nos debe arrojar la función dentro del rango establecido. En este caso 2.
  • Rango FALSO para obtener correspondencia exacta. Si dejas este argumento de la función vacío Excel aplica por defecto el valor VERDADERO.

La función quedaría de la siguiente forma:

=BUSCARV([@[CÓDIGO PRODUCTO]];Productos;2;0)

control de almacén

En la tabla de salidas aplicaremos también la función BUSCARV para que de forma automática Excel muestre la descripción del producto según el código introducido.

En este caso aplica de nuevo la función BUCARV al igual que lo hiciste en Entradas.

Automatizar inventario de control de almacén

En la tabla de productos queremos que se muestren de forma automática las entradas y salidas por producto y lote.

Es decir, para que se sume una entrada o se reste una salida es necesario que coincida tanto la descripción del producto como el lote del producto.

Para ello utilizaremos la función sumar.si.conjunto que nos permite utilizar más de un criterio para la suma.

En la primera celda de entradas de la tabla Productos introduce la función SUMAR.SI.CONJUNTO cuya sintaxis es:

(rango_suma;rango_criterio1;criterio1;rango_criterio2;criterio2…) donde:

  • Rango_suma es la columna de cantidad en la tabla entradas. Ve a la hoja entradas y selecciona todos los valores de la columna Cantidad.
  • Rango_criterio1 es el rango que quieres usar para aplicar el primer criterio. Selecciona todos los valores de la columna Descripción en la tabla Entradas.
  • Criterio1 es la “norma” que quieres aplicar al primer rango_criterio. Selecciona la celda que contiene la descripción en la tabla Productos.
  • Rango_criterio2. Selecciona todos los valores de la columna Lote en la tabla Entradas.
  • Criterio2. Selecciona la celda que contiene el lote en la tabla de Productos.

La función quedaría así:

=SUMAR.SI.CONJUNTO(Entradas[CANTIDAD];Entradas[DESCRIPCIÓN];[@DESCRIPCIÓN];Entradas[LOTE];[@LOTE])

Para automatizar las salidas introduce de nuevo una función SUMAR.SI.CONJUNTO en la primera celda de salidas totales y construye la función de la misma forma que en el caso de las entradas pero utilizando los valores de la tabla Salidas. Quedando la función así;

=SUMAR.SI.CONJUNTO(Salidas[CANTIDAD];Salidas[DESCRIPCIÓN];[@DESCRIPCIÓN];Salidas[LOTE];[@LOTE])

Automatizar el stock

Una vez que tanto las entradas como las salidas se reflejan automáticamente en tu tabla de Productos solo tienes que automatizar el stock.

En la primera celda de la columna Stock de la tabla Productos debes introducir una función sencilla que reste al valor de entradas el valor de salidas, quedando de esta forma:

=[@[ENTRADAS TOTALES]]-[@[SALIDAS TOTALES]]

Añadir coste unitario e importe inventariado

Si lo necesitas puedes añadir una columna para el coste unitario y calcular el importe que tienes en inventario.

Para ello solo tienes que añadir una columna contigua a la de stock, escribe el nombre del encabezado. Verás como automáticamente esa columna se incorpora a tu tabla.

Establece el precio para cada uno de los productos.

Añade una columna más y en el encabezado escribe importe inventariado.

En esta columna debes multiplicar el stock por el coste unitario para calcular de esta forma el importe de los productos en inventario. Quedando la fórmula así:

=[@STOCK]*[@[COSTO UNITARIO]]

Si quieres visualizar el coste total de tu inventario solo tienes que ir a las opciones de Diseño de la tabla y marcar la opción Fila de totales.

Crear alerta de stock en salidas

Algunos usuarios nos preguntaron cómo podían añadir una alerta que les avisara si había unidades suficientes al introducir una salida.

Si quieres añadir esta funcionalidad a tu inventario tienes toda la información en el tutorial Crear alerta de stock en salidas.

Plantilla del ejercicio

Descárgate la plantilla del tutorial aquí:

⚠ Esta plantilla es un recurso creado por Saber Programas y su uso está sujeto a derecho de autor y propiedad intelectual.


¡Puntúa este artículo!

Haz clic en las estrellas para puntuar

Promedio 4.7 / 5. Recuento de votos 6

2 Comments

  1. Wilfredo Pacheco Reyes
  2. jdc

Haz un comentario

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies