Passei as últimas horas tentando encontrar algo semelhante ao problema que estou tendo e atualmente estou saindo fumaça pelos ouvidos!
Eu tenho duas folhas, uma compedidoso segundo comvendas.
Preciso de ajuda automaticamentesubtraindodepedidosbaseado emvendas.
Estou tentando desenvolver um script que seja executado em todas as vendas e selocalizaçãoeitemsão exatamente iguais, subtraia a quantidade vendida da quantidade encomendada para obter uma "nova quantidade vencida".
Haverá vendas sem pedido, o que não é problema, mas seria bom se esses resultados pudessem ir para uma guia diferente. Abaixo está um resumo do que é exigido nos termos n00b.
Sheet1évendido, Sheet2éordem.
SeSheet1 C2&F2são iguais a qualquer linha em Sheet2(A2,D2são campos correlacionados) subtrairSheet1 G2, deSheet2 F2.
Eu adicionei um exemplo que você pode ver aquiSubtraia se várias condições forem atendidas https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e
Na aba de resultados tudo destacado em amarelo é o que teria mudado. Itens não destacados em amarelo não houve venda. A formatação verde e vermelha foi para referência e não é necessária se for muito difícil. (seria ideal) coluna H, está ilustrando, se isso for possível, uma vez que a quantidade do pedido chega a 0, para o script continuar pesquisando na planilha o próximo pedido com os mesmos critérios e subtrair a quantidade restante. Qualquer coisa vai ajudar, atualmente estou fazendo isso manualmente no fim de semana e geralmente mais de 150 vendas por semana e consome muito tempo.
Em termos simples, estou tentando fazer com que um script percorra cada linha de venda e subtraia a quantidade da folha de pedidos se a planta e o número da peça forem iguais. Classificarei a folha de pedidos com base em quando eles o fizerem, para que ela seja removida do pedido correto.
Obrigado por qualquer ajuda!
Responder1
Eu não acho que você precisa do VBA.
Veja SUMIFS()
Não tenho permissão para ver suas capturas de tela porque o proxy reverso da minha organização não permite acesso ao app.box, mas presumo:
- A coluna "C" da Planilha1 contém locais.
- A coluna "F" da Planilha1 contém itens.
- A coluna "G" da Planilha1 contém a quantidade vendida.
- A coluna "A" da Planilha2 contém locais.
- A coluna "D" da Planilha2 contém itens.
- A coluna "G" da Planilha2 contém a quantidade solicitada.
- A coluna "H" da planilha 2 contém a quantidade devida
Enquanto estiver na planilha 2, a quantidade total vendida para o item e local listado na linha 2 é:
=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))
Portanto, a fórmula usada para Sheet2!H2 seria simplesmente subtrair isso da quantidade solicitada:
=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))
Exceto que se o pedido for atendido e um pedido subsequente para o mesmo local e item tiver sido pelo menos parcialmente atendido, o total vendido poderá ser maior que o pedido na linha 2, e Pedido - Vendido será menor que zero! Mas isso é algo fácil de se proteger...
=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))
Mas isso não leva em consideração pedidos acima da linha atual! Temos MIN(0, ThisOrderQuantity- LocationItemQtySold) quando deveríamos ter MIN(0, ThisOrderQuantity+ PreviouslyOrderedQuantities- LocationItemQtySold) O total de todos os pedidos acima da linha atual na Planilha2 é
=OFFSET(G2,0,0,ROW()-2, 1)
... bem... isso irá quebrar na linha 2. Queremos algo que funcione em todas as linhas, incluindo a primeira. Então vamos nos proteger na linha 2.
=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))
mas isso são todos os pedidos anteriores, quando queremos apenas pedidos anteriores para o mesmo local e item. Precisamos de outro SUMIFS(). Para a linha 10 da Plan22, isso seria
=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))
Ok, agora podemos combiná-los. Para H2, arrastável por toda a coluna, a fórmula seria:
=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)))
Exceto que se você tiver três pedidos por local/item e apenas o primeiro for atendido, a quantidade "devida" desse terceiro será aquele pedido mais a quantidade do pedido do segundo! Precisamos ter certeza de que, se todos os pedidos anteriores ainda não estiverem completamente atendidos, isso não prejudicará nossa quantidade "devida". Portanto, devemos adicionar uma função MAX para que o valor devido nunca seja maior do que o solicitado.
=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))))
Cole isso em H2, arraste essa fórmula para baixo no resto de H, depure o que fiz (porque, spoiler, não fiz) e pronto! Não é necessário VBA.
(Aviso: você mencionou um "não problema" de vendas sem pedidos. Considere que, nesse caso, se vocêmais tardetiver um pedido, esse pedido será atendido instantaneamente!)
Atualização 3 de fevereiro de 2017: Corrigido problema com MIN e MAX; deveria ter sido MAX e MIN.