cantidades de acuerdo a requerimiento y existencias

Con un poco de imaginación, las fórmulas de Excel se pueden anidar para resolver necesidades complejas. El siguiente es un caso que encontré hace algún tiempo.

Como siempre, para que sigas este tutorial he preparado el siguiente archivo que explica cuál es la situación que queremos resolver. Da click en el ícono de Excel o en el nombre del archivo para descargarlo:

Ubicaciones de almacen.xlsx

Archivo de ejemplo para determinar las cantidades en base a requerimiento y existencias.

Como puedes ver en el archivo, se trata del surtimiento de material de un almacén a una línea de producción. Concretamente, la situación es la siguiente:

Un almacén debe surtir material a una línea de producción. Para ello, utilizan una lista de requerimientos que se captura en las columnas I y J. El almacenista utiliza las cantidades cantidades capturadas en la columna J para determinar la cantidad de material a mover de cada ubicación.

Las existencias de material en cada ubicación están dadas en la columna C. Se necesita una fórmula en la columna D que determine la cantidad a mover de cada ubicación. La fórmula debe considerar la cantidad de material disponible en cada ubicación y la cantidad total requerida. Esto significa que la fórmula no debe devolver más de lo que hay en cada ubicación ni tampoco más de lo necesario para completar el requerimiento.

Lleva un rato dar con la fórmula correcta, ¡pero te invito a que lo intentes! Será un gran ejercicio para que pruebes tus habilidades con fórmulas de Excel medianamente complejas. Cuando lo hayas intentado, puedes dar click en la siguiente sección para mostrar la respuesta.

El primero paso para encontrar la fórmula, está en diferenciar dos casos:

La primera fila en la que se encuentra un determinado material

En la primera fila en la que se encuentra el material, lo que debemos hacer es determinar si la existencia en esa ubicación es mayor o igual al requerimiento, en cuyo caso la fórmula deber devolver el requerimiento. En caso contrario la fórmula debe devolver la existencia.

Las demás filas

Si el material ya ha sido encontrado en una fila anterior, lo que debemos hacer es determinar si la diferencia entre el requerimiento y la cantidad a mover en las ubicaciones anteriores es menor o igual a la existencia en la ubicación actual. En este caso, la fórmula debe devolver la diferencia; en caso contrario, la fórmula debe devolver la existencia de la ubicación actual.

Piensa detenidamente y verás que con estas dos observaciones la fórmula casi se revela sola. Y a continuación la mostramos en todo su esplendor. En la celda D2 la fórmula sería:

=SI(CONTAR.SI($A$2:A2,A2)=1,SI(C2-BUSCARV(A2,I:J,2,0)>=0,BUSCARV(A2,I:J,2,0),C2),SI(BUSCARV(A2,I:J,2,0)-SUMAR.SI($A1:A$2,A2,$D1:D$2)>=C2,C2, BUSCARV(A2,I:J,2,0)-SUMAR.SI($A1:A$2,A2,$D1:D$2)))

Sí, es una fórmula un poco larga, pero es bastante efectiva. Te invito a que juegues un poco con las cantidades en existencia y con las cantidades en stock para que veas que en todos los casos, la fórmula devuelve el resutlado correcto. También puedes ver que si se agregan más líneas a los requerimientos de material o si se agregan más ubicaciones de material con sus existencias, la fórmula también responde correctamente. Aún más, la fórmula no falla si se ingresan materiales en filas no contiguas. Y observa que todo esto lo hemos hecho con dos sencillas observaciones que resumen la situación general.

¿Notaste algo extraño con esta fórmula? Vuelve a revisarla y verás que la fórmula incluye referencias circulares (Por ejemplo, la fórmula en la celda D2 incluye una referencia al valor de la celda D2) pero aún así no recibimos ningún error ni advertencia. ¿Sabes a qué se debe?

¿Te ha gustado?

¡Compárteme!

Comentarios: 1

#1
Fernando (sábado, 07 marzo 2020 20:53)

Este caso es excelente. La verdad no logré dar con la respuesta correcta pero estuve muy cerca.