☰

在 Excel 中从订单中减去销售额

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
在 Excel 中从订单中减去销售额

在过去的几个小时里，我一直在尝试寻找与我遇到的问题类似的问题，现在我的耳朵里冒出了烟！

我有两张纸，一张是命令第二个销售量。

我需要自动帮助减去从命令基于销售量。

我正在尝试开发一个可以贯穿每次销售的脚本，如果地点和物品完全相同，从订购数量中减去销售数量，得出“新到期”数量。

会有无订单的销售，这不是问题，但如果这些结果可以转到不同的选项卡就好了。以下是新手术语中所需内容的摘要。

Sheet1是卖，Sheet2是命令。

如果Sheet1 C2&F2Sheet2与（中的任何行相同A2，D2是相关字段）减去Sheet1 G2， 从Sheet2 F2。

我添加了一个示例，您可以在此处查看如果满足多个条件则减去 https://app.box.com/s/2m8nfjo8lieh5mfb9wgspy73f1bvzp0e

在结果选项卡上，所有以黄色突出显示的内容都是会发生变化的内容。未以黄色突出显示的商品没有销售。绿色和红色格式仅供参考，如果太难则不需要。（理想情况下）H 列说明，如果可能的话，一旦订单数量达到 0，脚本将继续在工作表中搜索具有相同条件的下一个订单并减去剩余数量。任何方法都会有所帮助，我目前在周末手动执行此操作，通常每周销售 150 多次，非常耗时。

简单来说，我尝试让脚本遍历每个销售行，如果工厂和零件编号相同，则从订单表中减去数量。我将根据它们的执行时间对订单表进行排序，以便从正确的顺序中删除。

谢谢你的帮助！

答案1

我认为您不需要 VBA。

看看 SUMIFS()

我无法查看您的屏幕截图，因为我所在组织的反向代理不允许访问 app.box，但我假设：

  • Sheet1 的“C”列包含位置。
  • Sheet1 的“F”列包含项目。
  • Sheet1 的“G”列包含销售数量。
  • Sheet2 的“A”列包含位置。
  • Sheet2 的“D”列包含项目。
  • Sheet2 的“G”列包含订购数量。
  • Sheet2 的“H”列包含到期数量

在 Sheet2 中，第 2 行列出的产品和地点的总销量为：

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

因此，Sheet2!H2 使用的公式就是从订购数量中减去这个数字：

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

除非订单已完成，并且同一地点和商品的后续订单至少已部分完成，则总销售量可能大于第 2 行中的订单，并且订购量 - 销售量将小于零！但这是很容易防范的事情……

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

但这没有考虑当前行以上的任何订单！我们得到的是 MIN(0, ThisOrderQuantity- LocationItemQtySold)，而我们应该得到的是 MIN(0, ThisOrderQuantity+ PreviouslyOrderedQuantities- LocationItemQtySold)。Sheet2 中当前行以上所有订单的总数为

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

... 嗯... 这会在第 2 行中断。我们希望某些东西能够适用于所有行，包括第一行。所以让我们在第 2 行保护自己。

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

但当我们只想要同一地点和商品的先前订单时，这些就是所有先前的订单。我们需要另一个 SUMIFS()。对于 Sheet2 第 10 行，这将是

=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))

好的，现在我们可以将它们合并了。对于 H2，可一直拖动到列的下方，公式如下：

=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)))

但如果您对某个地点/物品有三个订单，但只有第一个订单已完成，那么第三个订单的“到期”数量将是该订单加上第二个订单的数量！我们需要确保，如果所有之前的订单都尚未完全完成，这不会搞砸我们的“到期”数量。因此，我们应该添加一个 MAX 函数，以便到期数量永远不会大于订购数量。

=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))))

将其粘贴到 H2 中，将该公式拖到 H 的其余部分，调试我所做的（因为，剧透，我没有），然后就完成了！无需 VBA。

（警告：您提到了“无订单销售”的问题。请考虑在这种情况下，如果您之后有订单，该订单将立即执行！）

2017 年 2 月 3 日更新：修复了 MIN 和 MAX 的问题；它应该是 MAX 和 MIN。

相关内容