Continuamos con el curso de excel, esta vez veremos la formula para calcular existencias de un almacén, el calculo de inventarios es algo muy solicitado y buscado, así que pon mucha atención.
Aquí la idea es básicamente que al registrar un movimiento de entrada o salida, automáticamente se actualice el inventario
Se que dependiendo del nivel que tengas en este momento del uso de las formulas y funciones de excel, es muy probable que veas muy difícil el poder llevar un control automatizado de las existencias de un almacén, pero se por experiencia, que esa creencia esta muy relacionada con malas practicas a la hora de realizar los registros de entradas y salidas, básicamente me refiero a que en la mayoría de los casos se registran los movimientos de la manera incorrecta, finalmente ese error provoca muchos dolores de cabeza.
Bien, para una mayor comprensión, te dejo este vídeo donde explico todo el proceso de las formulas para calcular la existencia.
Link de descarga al final del post
Ejemplos:
Agregar una hoja para los movimientos de cada día, imagina, si el almacén trabaja los 365 días del año, al final de 1 solo año tendrás 365 hojas en tu libro, ahora imagina que te piden un reporte de todos los movimientos en el año de un proveedor o cliente!!!.
Se que de inicio la idea es buena, quien querría deliberadamente complicarse la vida y trabajar horas extras para tener un reporte que cumple ciertos criterios, pero finalmente es un error total el llevar registros de esa manera, igual es un error manejarlo con una hoja para cada semana, cada mes, para cada cliente, proveedor, etc.
Bueno, después de este rollo te estarás preguntando, y cual es la forma correcta de registrar…?
La forma correcta de diseñar una base de datos.
En realidad es mas simple de lo que te puedes imaginar, simplemente tienes que usar la primera fila para indicar los nombres de campo, cada campo sera usado para plasmar un dato especifico de cada registro, se debe usar una columna para cada campo.
No es buena idea inventarse formatos complicados, combinando celdas, poner campos encimados, etc, simplemente poner los campos en linea usando de preferencia la primera fila.
Tal como se ve en la imagen, es la forma correcta de estructurar una base de datos, así de simple.
En ocasiones me dicen, Nolberto, yo lo tengo que separar en hojas porque mis productos estas separados por categorías, así que necesito llevar una hoja para cada categoría, pues no, no hace falta una hoja para cada categoría o cada tipo, ni por días, semana, cliente, etc.
La solución es muy sencilla, si tus productos son identificados por categorías, agrega una columna en la base de datos para indicar la categoría de cada producto que se registra en las entradas y salidas, si necesitas obtener reportes diarios, semanales, mensuales, trimestrales, anuales o cualquier rango de fechas, solo hace falta una columna para poner la fecha, tal como se ve en la imagen, en otras clases ya vimos como hacer consolidados tomando como base la fecha, también vimos como generar reportes en otras hojas usando filtro avanzado.
Así que no hay pretextos para llevar bien una base de datos que finalmente te ayude a generar reportes de una manera fácil y sin mayores complicaciones.
Parece que ya me extendí un poco, seguro estas pensando… y la formula para calcular la existencia!!.. vamos.
1.- Identificar datos clave para poder calcular existencia.
Esto es sumamente importante, como podrías hacer un calculo si no conoces los componentes que intervienen en ese proceso, en este caso es muy simple, tenemos primero el nombre o código del producto, la cantidad de cada movimiento o registro y el tipo de movimiento ( Entrada, Salida ).
Ahora, es muy importante que los campos que identifican a esos datos estén bien definidos en la primera fila de la base de datos, uno por cada columna, esto tiene una razón muy simple que de hecho aplica para cualquier lenguaje de programación, en este caso particular seria para las formulas, que prácticamente todas trabajan con una orientación basada en la cuadricula de la tabla u hoja de calculo, principalmente las formulas de búsqueda y referencia, así como las funciones matriciales como SUMAPRODUCTO, esto es, hacen búsquedas por filas y columnas, devuelven datos que pueden estar en esa misma fila o columna, pero por lo regular suelen devolver resultados de la intersección de una fila y columna.
Se que esto ultimo puede parecer algo complicado, pero no te preocupes, sigue leyendo y veras que realmente no es tan difícil, una vez que lo asimiles te sorprenderás de las cosas que podrás hacer.
2.- La formula para sumar las entradas al almacén.
Bien, llego la hora, veremos esa formula que en un instante te dará la suma de las entradas, tomando los datos de una base de datos donde se registran todos los movimientos, entradas y salidas, de todos los productos, de todos los días , de todos los clientes y proveedores, de todos los meses, de todos los años… todo en una sola hoja.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Entrada")*(almcantidad))
Si observas la formula, estamos usando SUMAPRODUCTO, esa función suma los productos de rangos o matrices, en este caso el rango que realmente suma es el rango de la columna de cantidad, pero a su vez usamos condiciones para que esa suma se pueda dar, aqui son dos condiciones, la primera es que debe contemplar solo los registros que en su columna de Articulo tengan en nombre del Articulo1, la segunda condición es que en la columna de Tipo ( Entrada, Salida ), diga Entrada.
Esa comparación se puede observar en la formula ( $B5=almarticulo ) y ( almtipomov=”Entrada” ), los nombres almarticulo y almtipomov son nombres de rango que hemos dado a las columnas de articulo y tipo de movimiento.
En este caso la lógica es muy simple, cuando la formula encuentra u registro que tiene el nombre del articulo buscado y que ademas es una entrada, suma el valor que hay en la columna de cantidad en esa misma fila, de ese modo sumara todos los registros que cumplan esas condiciones y así de simple se obtiene la suma de las entradas.
3.- La formula para sumar las salidas.
Realmente aquí es el mismo cuento, simplemente se cambia el criterio del tipo de movimiento por “Salida”.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Salida")*(almcantidad))
Como se ve en la imagen, no hay ningún misterio, es prácticamente la misma formula excepto por el criterio en la comparación de la columna del tipo de movimiento.
4.- Formula para calcular la existencia.
Teniendo ya las entradas y salidas, resulta mas que sencillo calcular la existencia, solo debemos restar el resultado de la suma de entradas menos el resultado de la suma de salidas.
Pero también podridos hacer todo el calculo en una sola celda.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Entrada")*(almcantidad))-SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Salida")*(almcantidad))
Si ven esa gran formula, en realidad son las dos formulas anteriores, solo que en la misma celda se resta una de la otra ( Entradas menos Salidas ).
Eso es Todo…?
Si, como has visto, finalmente no es tan difícil calcular la existencia de un almacén, esto gracias al poder de la función SUMAPRODUCTO, esta es una función matricial, esto es porque internamente procesa los datos de matrices completas, en este caso procesa las columnas que intervienen en el calculo de la existencia, haciendo comparaciones y sumando cuando esas comparaciones son positivas, todo en una misma celda.
Descargar Excel para Calcular Existencia
Descargar Calculadora de Existencias v2
Descargar Fórmula de Inventario en Excel
Si el articulo te ha gustado, no lo pienses, comenta y comparte con tus amigos en las redes sociales.
Isidro Orta Coto dice
Hola Nolberto, la finalidad supongo que sea el cálculo de la existencia, le digo ésto porque existen varias situaciones que pueden darse, ejemplo:
Salidas pueden ser por ventas, mermas, roturas, faltantes por extravío o sustracción, etcétera, en esos casos cómo se controlarán? y serían a precio de compra.
Cuando compras un mismo producto a varios proveedores el precio unitario puede ser diferente, habría que promediarlos para que al momento de la salida sea uniforme, cómo se controlarán?
Cómo ve se complica la situación, si tienes alguna respuesta te agradecería me avisaras, saludos cordiales.
Leoncio Agreda dice
Quisiera tratar con usted lo referente a una fórmula matemática para el cálculo del monto del inventario final. Es un sistema de creación propia y que he llamado el “Método del Activo Parcial”, mediante el cual obtenemos el monto de la utilidad neta y por consiguiente: la utilidad bruta, costo de venta e inventario final. Aquí se utiliza la suma de las cuentas de activo contenidas en el libro mayor y a partir de este se inicia el cálculo. Si le interesa puede contactarme con los datos de mi correo electrónico. Este método aun no es conocido por otras personas. Vea que podemos hacer. Un saludo afectuoso.
Nolberto Lopez dice
Saludos Leoncio.
Puede ir a la pagina de contacto para hacer su planteamiento, puede adjuntar un archivo de ejemplo donde se pueda ver el avance que tiene y especificar en que parte necesita ayuda.
Cristian Ortega dice
Hola… gracias por la orientacion.
Quisiera saber si puedo enviar un documento para poder resolver ciertas exigencias que se estan solicitando.
Dicho documento es de un almacen donde estoy laborando y antes era todo a pie y en mi caso quiero automatizar ciertas cosas para hacerlo eficiente y eficaz.
Se podria brinda el correo electronico para enviarle el documento?
Gracias de antemano.
Nolberto Lopez dice
Saludos Cristian.
Puede enviarlo a [email protected]
Angel dice
Buen articulo.
Vale tambien decir, que la formula “buscarv” alguna veces es inutil y mas cuando hay varios datos. Con lo cual la funcion “buscarproducto” es la correcta para calcular, extraer sumatorias en base a condiciones.
Slds!.
Rafael Larez dice
quisiera saber como o que tipo de formula debo hacer o introducir para llevar un controlo de pago de deudas , soy honesto en decir que no entiendo ni papa.agradezco su valiosa ayuda
Richard dice
Buenos dias, yo tengo un rango de fichas ejemplo. 1700-1800 en otro archivo se van utilizando esas fichas ejem. 1701 para un registro 1702 para otro y asi como puedo saber en el archivo donde tengo los rangos cuantas fichas se van utilizando para que cuando le queden 20 fichas poder llevarle mas a la persona. gracias.
EDUARDO JIMENEZ dice
Hola… me gusta su forma de explicar… en mi caso tengo un problema que no encuentro solucion. Le agradeceria mucho cuando pueda ayudarme…
Solo manejo 2 columnas:
Columna1: Cantidad. (A2:A31), Columna 2: Producto. (B2:B31)
Uso 30 filas (A2:B31) de las cuales no todas tienen informacion.
Mi pregunta es, que formula usar para que en una celda especifica (D9), al introducir un producto como busqueda, me identifique y sume los productos que se parecen y al mismo tiempo me de el total del producto que tengo de las 2 columnas, en otra celda especifica(E9).
Ejemplo:
Cantidad Producto
2 Leche
1 Pan
2 Arroz
1 Leche
4 Pan
En una celda (D2) escribo: Pan, y en otra (E2) me de la suma total de lo que tengo, que en este caso seria: 5.
Que automaticamente encuentre que datos se parecen y los sume.
Le agradezco la informacion. Gracias…
Angel dice
Estimado, este articulo te da esa solución revisa nuevamente ma logica matricial de sumaproducto.
Edison dice
Hola Nolberto. Tengo un contratiempo con excel manejo el stock con entradas y salidas = existencias. El problema que tengo actualmente es que me permite seguir descontando cuando llego a 0 en existencias, dando números negativos y quiero saber si existe alguna formula o macro que me impida descontar productos que no tengo. Saludos
Felipe González Lopez dice
Buenas tardes, si necesito tener el inventario por lotes?, ya que he batalla do por esa cuestión.
manuel dice
Buenas tardes:
Alguien podria ayudarme a crear la formula siguiente:
Tengo venta en soles de enero a octubre 2017 , pero hay 3 meses que no se vendio nada ejemplo(marzo, julio octubre).
¿ como sacar la formula donde indique cuantos meses se trabajaron y cual es su promedio?
jose manuel dice
Nolberto necesitaría tu email para poderte consultar una duda respecto a excel
Nolberto Lopez dice
Saludos Jose.
Para ello puede ir a la sección de contacto desde el menú superior de la pagina.
Jesus Miranda Heredia dice
Jesús Miranda H. Agradeceré sus consejos, estoy realizando un inventario de repuestos de generadores en el formato tengo celdas de: código, articulo, existencia, entrada, salida(en celdas separadas) y existencia total , ya tengo las existencias necesito ayuda con las formulas para la actualización automática de la existencia total al registrar la salida o ingreso del repuesto
Atentamente J. Miranda
saludos
fernando areas dice
hola, no me aparece en mi excel almarticulo, almtipomov ni almcantidad?
como hago qu me aparezcan?
Nolberto Lopez dice
Saludos Fernando.
Esos son rangos definidos por el usuario, es decir, uno mismo les da ese nombre.
Para hacer eso solo debe seleccionar una celda o un rango que comprende mas de una celda, luego en el cuadro que esta justo arriba de la columna A se teclea el nombre que desea darle a ese rango, de tal forma que luego le sea mas sencillo referirse a el desde alguna formula como en el ejemplo.
En el truco numero 5 de la siguiente pagina puede ver una forma de definir rangos de manera rapida, eso aplica por ejemplo cuando desea dar nombres de rango a todas las columnas de una tabla de datos.
https://formulasexcel.com/10-trucos-en-excel/
Pamela dice
y si ya tengo mis existencias iniciales como podría calcular las existencias finales?
Luis Huerta dice
NECESITO AYUDA APARA HACER UN INVENTARIO, TENGO LOS DATOS PERO NO SE COMO ESTRUCTURARLO
Nolberto Lopez dice
Es precisamente lo que se explica en el ejemplo de esta pagina.
Armando Pérez dice
A parte de ser feliz como dice la lectora Gabriela, yo pienso que estas muy satisfecho porque con tus conocimientos que compartes ya tienes o aumentan mas personas con conocimientos al respecto y gracias a ti. gracias Norberto
Veronica dice
Buenas tardes Sr,
Consulta por favor si me puede ayudar.
Ejemplo: un articulo con varios lotes.
Ya tengo registros de movimientos de ese articulo.
Quisiera en una hoja dentro del libro que una formula me sume y/o cuente cada lote. . . Cual seria la formula …sumar si.? sumar conjunto?
Ose que llame con el articulo tal y me diga tiene tanta cantidad de tantos lotes… espero poder explicarme, gracias por la orientacion que me pueda dar.
Saludos.
Veronica
Nolberto Lopez dice
Saludos Veronica.
Precisamente es lo que se explica en este ejemplo, no importa si el registro es por piezas, kilos, metros, lotes, etc., siempre y cuando haya una columna que identifique el articulo y otra la cantidad en cada registro.
Ahora, si la unidad de medida se tiene que desglosar, ejemplo: cada lote tiene 100 unidades y desea obtener el total de unidades, simplemente se multiplica el total de lotes que le da la formula de este ejemplo por la cantidad de unidades que tiene cada lote.
Roberto Venegas dice
Le pido de favor su ayuda para sacar la existencia de mi inventario con la fómula Sumar.Si.Conjunto o Sumar.Si, cómo quedaría?
Nolberto Lopez dice
El ejemplo de esta pagina es para eso.
Lore dice
Hola
Tengo un excel con entrada y salida entregándole un stock del producto. Lo que necesito es colocaren otra ojalá excel con cada producto que tengo y que me dé el total de stock que hay, independiente si entra o salen productos en la otra hoja.
Nolberto Lopez dice
Saludos Lore.
Es justamente lo que se explica en este ejemplo, incluso hay un vídeo y descarga del ejercicio.
mary peña dice
buenas tardes
haber si me explico tengo una tabla de mis salidas de almacen y quiero que cuando un trabajador agote el material que solicita para terminar una casa que aparezca una leyenda de que ya fue agotado ese material osea que no se le puede dar mas puesto que ya se dio el material que necesitaba por ejemplo cemento, y esto lo hago diferenciándolo las viviendas por lote y manzanas, espero me haya entendido y me pueda guiar.
gracias
MAXIMO dice
Excelente explicación en los ejemplos, son de mucha ayuda en la elaboración de tablas en Excel. Gracias
Mayuri tovar dice
Muy buena la explicación
Mario Herrera dice
Muy Útil Nolberto Gracias por tu ayuda
eddy sosa olivos dice
Gracias por estos tips , que son de una gran ayuda en exel
GABRIELA dice
TU DEBES SER UNA PERSONA FELIZ PORQUE COMPARTES TUS CONOCIMIENTOS Y A LA VEZ SACAS DE LA IGNORANCIA A MUCHAS PERSONAS. MUCHAS GRACIAS.
danilo efren dice
Excelente ejemplo
gracias por compartirlo
danilo
Carlos Quevedo dice
Muchas gracias,los tips son una gran ayuda para comprender
el leguaje en Excel. Una vez mas gracias