Conocer Dika Ops

¿Cómo puedo llevar el control de stock en Excel sin errores?

Cómo puedo llevar el control de stock en Excel: un listado de artículos, un registro de movimientos, fórmulas de stock disponible y alertas de pedido.

Actualizado 9 min de lectura Software empresarial
Portátil sobre un sofá gris mostrando un panel de análisis con gráficos y un mapa del mundo

Para llevar el control de stock en Excel, usa dos hojas. La primera es un listado de artículos con una fila por cada producto. La segunda es un registro de movimientos con una fila por cada entrada, venta, traspaso o ajuste. Una fórmula suma el registro de cada artículo para obtener el stock disponible, y una segunda fórmula marca los artículos que han llegado a su punto de pedido. Este montaje sirve para unos cientos de productos y unos miles de movimientos al año, y no necesita complementos ni una base de datos.

El punto débil de la mayoría de hojas de stock no son las fórmulas, sino la costumbre de escribir una cantidad nueva encima de la anterior. Así nadie puede explicar de dónde sale una diferencia. Un registro de movimientos evita ese problema, porque cada cambio queda guardado como una fila con fecha y motivo. En esta guía verás cómo montar las dos hojas, qué fórmulas usar y cómo comprobar los resultados. Para calcular el punto de pedido en sí, nuestra calculadora de punto de pedido lo obtiene a partir de la demanda y del plazo de entrega.

Por qué un registro de movimientos gana al recuento a mano

Una sola columna llamada "stock" parece más sencilla, pero oculta el historial. Cuando la cifra es errónea, no sabes si faltó un albarán, si una venta se introdujo dos veces o si un artículo dañado nunca se dio de baja. Un registro guarda cada suceso, así que puedes rastrear una diferencia hasta la fila que la causó.

El registro también te da informes más útiles. Puedes ver cuántas unidades salieron del almacén el mes pasado, qué artículos rotaron más y cuáles no se movieron en absoluto. Esa información es el punto de partida para decidir pedidos y para detectar stock muerto.

Paso 1: crea el listado de artículos

Crea una hoja llamada Artículos. Da a cada producto una fila y un código único. Los códigos son el vínculo entre las dos hojas, así que deben ser cortos, fijos y no reutilizarse nunca para otro producto.

ColumnaQué contieneEjemplo
A: Código de artículoCódigo único que no cambiaCAB-200
B: NombreEl nombre que usa tu equipo en la estanteríaJuego de cables, 2 m
C: UnidadLa unidad en la que cuentasunidad
D: UbicaciónDónde se guarda el stockEstantería 4
E: Punto de pedidoStock disponible al que conviene pedir80
F: Stock disponibleFórmula descrita en el paso 3166
G: EstadoFórmula descrita en el paso 4OK

Paso 2: registra cada movimiento

Crea una segunda hoja llamada Movimientos. Cada fila recoge un suceso, y una fila no se edita una vez escrita. Si te equivocas, añade una fila de corrección con el motivo en la columna de notas. Estas columnas bastan para la mayoría de operaciones pequeñas.

  1. Columna A, Fecha: el día en que se movió la mercancía, no el día en que escribiste la fila.
  2. Columna B, Código de artículo: un código de la hoja Artículos. Usa una lista desplegable con validación de datos para que una errata no cree un artículo nuevo.
  3. Columna C, Tipo: ENT para las entradas y SAL para las salidas, mermas y bajas.
  4. Columna D, Cantidad: siempre un número positivo, de modo que el tipo indique a la fórmula en qué sentido se mueve.
  5. Columna E, Cantidad con signo: una fórmula, =SI(C2="SAL";-D2;D2), que convierte cada fila en una suma o en una resta.
  6. Columna F, Referencia y notas: el albarán, el número de pedido o el motivo, para poder comprobar cualquier fila.

Registra los movimientos el mismo día en que ocurren, no en un lote al final de la semana. Una semana de salidas sin anotar es la causa más frecuente de que la hoja y la estantería no coincidan.

Paso 3: calcula el stock disponible

En la hoja Artículos, en la celda F2, suma todas las cantidades con signo del artículo de la primera fila. Copia la fórmula hacia abajo. En Excel en español, las funciones se llaman SI y SUMAR.SI.CONJUNTO, y los argumentos se separan con punto y coma. La fórmula es:

=SUMAR.SI.CONJUNTO(Movimientos!$E:$E;Movimientos!$B:$B;A2)

Así se suman las cantidades con signo de todos los movimientos del artículo de la celda A2, de modo que las entradas suman y las salidas restan. Si partes de un recuento inicial, introdúcelo como una fila de entrada con fecha del primer día del periodo, para que cada cifra venga del registro.

Paso 4: marca los artículos que hay que pedir

En la celda G2, usa una fórmula que compare el stock disponible con el punto de pedido:

=SI(F2<=E2;"Pedir ya";"OK")

Aplica formato condicional a la columna Estado para que las filas "Pedir ya" aparezcan en color. Después filtra o ordena la lista cada mañana. El punto de pedido de la columna E debe salir de la demanda y del plazo de entrega, no de una estimación a ojo. La calculadora de punto de pedido da la cifra con los pasos escritos, y puedes pegar el resultado en la columna E.

Paso 5: compara la hoja con la estantería

Una fórmula solo suma lo que alguien ha introducido. Cuenta el stock con un ciclo regular para encontrar dónde difieren la hoja y la estantería. Cuenta unos pocos artículos cada semana en lugar de todo el almacén una vez al año. Cuando los recuentos no coincidan, registra un ajuste con la cantidad contada y un motivo, y después averigua por qué se produjo la diferencia.

Persona comprobando el inventario con una tableta junto a unas estanterías
Un recuento periódico compara la hoja con lo que hay realmente en la estantería.

Nuestra guía sobre cómo hacer un inventario físico paso a paso describe un método que puedes combinar con la hoja. Si quieres saber qué artículos merecen más atención, el artículo sobre métodos de gestión de inventario explica el análisis ABC y otros enfoques.

Problema habitualCausa probableSolución
El stock disponible sale negativoUna salida se registró antes que su entrada, o se escribió mal un códigoRevisa el orden de fechas y el código, y corrige con una fila nueva
La hoja marca más que la estanteríaUn artículo dañado o devuelto nunca se registró como salidaRegistra la baja o la devolución con motivo y vuelve a contar el artículo
Desaparecen artículos del listadoSe cambió un código o se borró una filaNo cambies ni borres códigos; desactiva el artículo
Los totales fallan tras ordenarLas fórmulas apuntaban a filas que se movieronUsa referencias de columna completa en SUMAR.SI.CONJUNTO y mantén el listado como una tabla

Un ejemplo práctico

Tomemos el artículo CAB-200, con un recuento inicial de 120 unidades el 1 de septiembre. El registro muestra una entrada de 200 unidades el 3 de septiembre, una salida de 150 unidades el 12 de septiembre y una baja de 4 unidades dañadas el 20 de septiembre. Las cantidades con signo son +120, +200, -150 y -4, y la fórmula devuelve 166 unidades disponibles. Si el punto de pedido es 80, el estado sigue en OK. Si después se registrara una salida de 90 unidades, el stock bajaría a 76 y el estado pasaría a "Pedir ya". Es un ejemplo con las cifras indicadas, no un dato medido.

Cuándo Excel deja de ser suficiente

Un libro bien mantenido sirve bien a un equipo pequeño. Empieza a costar cuando varias personas lo actualizan a la vez, cuando el stock está en más de un almacén o cuando la hoja debe conectarse con pedidos de venta, facturas y compras. En ese punto, las entradas duplicadas y las actualizaciones tardías se convierten en un coste habitual. El stock también debe enlazar con la contabilidad, para que el valor del stock y el coste de lo vendido cuadren.

En Dika Ops, el stock, las ventas, las compras y la contabilidad viven en un mismo registro, de modo que una salida actualiza el stock y las cuentas a la vez. El escaneo de códigos de barras en el inventario y en el almacén sustituye la escritura por una lectura. Si ya notas los límites de la hoja, podemos revisar el proceso contigo y mostrarte cómo un sistema gestionaría los mismos movimientos. Puedes hablar con nosotros sobre tu operación, y nuestro equipo puede conectarla con los datos que ya tienes a través de nuestro servicio de integración y automatización. Si antes quieres ordenar las ubicaciones, lee cómo gestionar tu almacén sin perder stock.

Preguntas frecuentes

¿Puedo llevar el stock en Excel gratis?

Sí. Excel y las aplicaciones gratuitas de hojas de cálculo pueden manejar un listado de artículos y un registro de movimientos con las fórmulas de esta guía. El coste real está en el tiempo que dedicas a introducir los datos y a mantener la hoja exacta, no en el programa.

¿Cuántos artículos puede manejar una hoja de stock en Excel?

No hay un límite pequeño y fijo para este tipo de hoja, pero el rendimiento baja cuando el registro de movimientos crece hasta decenas de miles de filas y muchas fórmulas se recalculan a la vez. Archiva los años anteriores en un archivo aparte y mantén el periodo en curso en el archivo de trabajo.

¿Conviene usar una tabla de Excel o un rango?

Usa una tabla de Excel para el listado de artículos y para el registro de movimientos, de modo que las filas nuevas amplíen las fórmulas automáticamente. Las referencias de columna completa en SUMAR.SI.CONJUNTO también funcionan bien, pero evita colocar totales o notas en las columnas que leen las fórmulas.

¿Cómo controlo el stock que está en dos ubicaciones?

Añade una columna de ubicación al registro de movimientos y filtra la fórmula por ubicación además de por código de artículo. Mantén el listado con una fila por producto e informa del stock por ubicación con un filtro o una tabla dinámica.

¿Con qué frecuencia debo actualizar la hoja?

Registra cada movimiento el día en que ocurre. Cuenta cada semana una muestra de artículos y todo el stock al menos una vez al año, o con más frecuencia si el valor es alto o los artículos son fáciles de perder. Compara los recuentos con la hoja y corrige las diferencias con filas de ajuste.

Siguiente paso

Empieza con los diez artículos que más problemas te dan. Crea la hoja Artículos para ellos, registra una semana de movimientos y compara el resultado con un recuento el viernes. Calcula los puntos de pedido con la calculadora de punto de pedido. Cuando la lista crezca, compara la hoja con los sistemas de gestión de almacenes para ver qué necesita una operación mayor. Puedes conocer más sobre nuestro equipo en la página de inicio de Dika Design.

¿Tiene un proyecto en mente? Hablemos de ello.

Cuéntenos qué quiere mejorar. Le responderemos con un siguiente paso claro.