Excel公式Vlookup和Sumifs组合，解决库存分配问题

模拟工作中出现的一个场景，收到客户的物品需求数量，如左表所示，我们已有的物品库存如右表所示：

现在需要根据需求和库存数量，进行分配

苹果的总需求是完全可以满足的，那分配的数量，自然就是对应需求的数量

那香蕉的总需求数量是130，库存，只有80，如何进行分配呢?

我们的数据有很多，如何设置一个公式进行快速的分配？

1、按需求比例分配

首先，我们可以计算出来，每个客户商品的分配系数

也就是总库存数量除以总需求的数量，并且上限为1

所以，我们可以输入公式：

=MIN(1,VLOOKUP(B2,G:H,2,0)/SUMIFS(C:C,B:B,B2))

 

然后再使用需求乘以对应的分配系数即可：

=C2*D2

 

2、从上至下分配

如果我们的分配规则是，优先满足第一客户，那就是要从上至下进行分配了

例如，我们要对中间的数据进行分配

需求是15

那我们首先要知道该商品对应的总库存，对应是100

然后还要知道，前面已经分配走了多少数量，那我们知道前面是40

然后要让需求数量15，和总库存减去已分配库存100-40=60，进行对比

取小值，那么 这里是15

基于上述的逻辑，所以我们需要使用的公式是：

=MIN(C2,VLOOKUP(B2,F:G,2,0)-SUMIFS($D$1:D1,$B$1:B1,B2))

用VLOOKUP公式，查找匹配总库存

用SUMIFS公式，计算累计已经分配的库存

再用MIN公式，和需求数量进行对比分配

非常巧秒的就可以将所有的库存进行了分配，数量充足时，会全部进行安排

数量不足时，优先满足上面的，再往下安排

 评论：

1.香蕉要60的全给了，要70的给20，你是不知道甲方的怒火，分分钟换供应商！计算的没毛病，但是逻辑不对

2.数量不够时，如何计算采购量？