☰

Restar ventas del pedido en excel

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
Restar ventas del pedido en excel

¡He pasado las últimas horas tratando de encontrar algo similar al problema que estoy teniendo y actualmente me sale humo de los oídos!

Tengo dos hojas, una conpedidosel segundo conventas.

necesito ayuda automaticamenterestandodepedidosResidencia enventas.

Estoy tratando de desarrollar un script que se ejecute en cada venta y, siubicaciónyartículoson exactamente iguales, reste la cantidad vendida de la cantidad pedida para obtener una cantidad "nueva a pagar".

Habrá ventas sin pedido, lo cual no es un problema, pero sería bueno si esos resultados pudieran ir a una pestaña diferente. A continuación se muestra un resumen de lo que se requiere en términos n00b.

Sheet1esvendido, Sheet2esorden.

SiSheet1 C2&F2son iguales que cualquier fila en Sheet2(A2,D2son campos correlacionados) restarSheet1 G2, deSheet2 F2.

He añadido un ejemplo que puedes ver aquí.Restar si se cumplen varias condiciones https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e

En la pestaña de resultados todo lo resaltado en amarillo es lo que habría cambiado. Los artículos no resaltados en amarillo no tuvieron venta. El formato verde y rojo fue como referencia y no es necesario si es difícil. (sería ideal) la columna H ilustra, si esto es posible, una vez que la cantidad del pedido llega a 0, el script sigue buscando la hoja para el siguiente pedido con el mismo criterio y resta la cantidad restante. Cualquier cosa ayudará, actualmente estoy haciendo esto manualmente durante el fin de semana y, en general, tengo más de 150 ventas por semana y lleva mucho tiempo.

En términos simples, estoy tratando de que un script revise cada fila de venta y reste la cantidad de la hoja de pedido si la planta y el número de pieza son los mismos. Ordenaré la hoja de pedidos según cuándo lo hagan para que se elimine del orden correcto.

¡Gracias por cualquier ayuda!

Respuesta1

No creo que necesites VBA.

Mira SUMIFS()

No puedo ver sus capturas de pantalla porque el proxy inverso de mi organización no permite el acceso a app.box, pero supongo:

  • La columna "C" de la Hoja1 contiene ubicaciones.
  • La columna "F" de la Hoja1 contiene elementos.
  • La columna "G" de la Hoja 1 contiene la cantidad vendida.
  • La columna "A" de la Hoja2 contiene ubicaciones.
  • La columna "D" de Sheet2 contiene elementos.
  • La columna "G" de la Hoja2 contiene la cantidad pedida.
  • La columna "H" de la Hoja 2 contiene la cantidad adeudada

Mientras que en la hoja 2, la cantidad total vendida para el artículo y la ubicación que figuran en la fila 2 es:

=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

Entonces, la fórmula utilizada para Sheet2!H2 sería simplemente restar esto de la cantidad pedida:

=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

Excepto que si el pedido se completa y un pedido posterior para la misma ubicación y artículo se ha completado al menos parcialmente, el total vendido podría ser mayor que el pedido en la fila 2, y Pedido - Vendido sería menor que cero. Pero eso es algo fácil de evitar...

=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

¡Pero eso no tiene en cuenta ningún pedido por encima de la línea actual! Tenemos MIN(0, ThisOrderQuantity- LocationItemQtySold) cuando deberíamos tener MIN(0, ThisOrderQuantity+ PreviouslyOrderedQuantities- LocationItemQtySold) El total de todos los pedidos por encima de la fila actual en la Hoja2 son

=OFFSET(G2,0,0,ROW()-2, 1)

... bueno... eso se romperá en la fila 2. Queremos algo que funcione en todas las filas, incluida la primera. Así que protejámonos en la fila 2.

=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))

pero esos son todos los pedidos anteriores cuando solo queremos pedidos anteriores para la misma ubicación y artículo. Necesitamos otro SUMIFS(). Para la fila 10 de la Hoja 2, esto sería

=IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A10,OFFSET(D:D,0,0,ROW()-2,1),D10))

Bien, ahora podemos combinarlos. Para H2, que se puede arrastrar hasta el final de la columna, la fórmula sería:

=MAX(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

Excepto que si tiene tres pedidos para ubicación/artículo y solo se completa el primero, la cantidad "vencida" del tercero será ese pedido más la cantidad del segundo. Necesitamos asegurarnos de que si todos los pedidos anteriores aún no se han completado por completo, eso no arruine nuestra cantidad "vencida". Entonces deberíamos agregar una función MAX para que lo adeudado nunca sea mayor que lo ordenado.

=MIN(G2,MIN(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))))

Pega eso en H2, arrastra esa fórmula hacia el resto de H, depura lo que he hecho (porque, spoiler, no lo he hecho), ¡y listo! No se requiere VBA.

(Advertencia: usted mencionó un "no problema" de ventas sin pedidos. Tenga en cuenta que en tal caso, simás tardetiene un pedido, ¡ese pedido se completará instantáneamente!)

Actualización 3 de febrero de 2017: problema solucionado con MIN y MAX; debería haber sido MAX y MIN.

información relacionada