Excel实战技巧93： 根据以往业绩分配销售任务
学习Excel技术，关注微信公众号：
excelperfect
在很多情况下，我们往往会根据以往的历史数据来制订计划，例如根据以往的销售业绩来分配今年的销售任务。在chandoo.org论坛中，看到了一个类似的示例，特整理分享于此。
基本原理
先以一下简单的示例讲解分配的基本原理，如下图1所示，统计了3个商店橙子的历史销售量。现在，总店有批发了200公斤橙子，如何合理地分配给各个商店？
图1
先计算出各商店销售量的百分比，如下图2所示。
图2
然后，按销售百分比进行分配，即由待销售的量乘以销售量百分比，结果如下图3所示。
图3
示例：按条件进行分配
如下图4所示的工作表数据。
图4
可以看到在4个区中有14个商店，每个商店或者是OPEN状态、或者是OPEN1状态。
先以一组数据入手。编号为1的商店位于N区域且状态为OPEN，其销售量为100，而工作表中位于N区域且状态为OPEN的销售量总和为600，即100+100+100+150+150，如下图5中突出显示的部分。
从工作表上方的数据表中，我们知道现在需要分配的位于N区域且状态为OPEN的销售量为200，即单元格C4中的数值。
图5
因此，我们需要将200分配给5家商店，而这5家商店的销售量总和是600。可以使用SUMIFS函数求得N区域且状态为OPEN的商店销售总和：
=SUMIFS(E11:E24,C11:C24,C11,D11:D24,D11)
因此，商店1的销售比例为：
100/600=16.67%
在工作表中使用公式为：
=E11/ SUMIFS(E11:E24,C11:C24,C11,D11:D24,D11)
接着，使用INDEX/MATCH函数组合来从工作表上方的表中获取满足条件的数值，此处为200。公式为：
=INDEX(C4:D7,MATCH(D11,B4:B7,0),MATCH(C11,C3:D3,0))
此时，就可以使用公式进行数量分配了，即分配的销售量：
=待分配的销售量*实际销售量/总销售量
在工作表中的公式为：
=INDEX(C4:D7,MATCH(D11,B4:B7,0),MATCH(C11,C3:D3,0))*E11/SUMIFS(E11:E24,C11:C24,C11,D11:D24,D11)
对于N区域且状态为OPEN的5家商店来说，其分配的销售量如下图6所示。
图6
将上述公式输入到单元格F11中，并下拉至单元格F24，完成销售量分配，如下图7所示。
图7
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
完美Excel社群2020.11.16动态
#VBA# Excel编程周末速成班第12课：使用自定义公式编程
主要内容：在公式中创建单元格引用；使用绝对和相对单元格引用；在公式中引用命名区域；将公式链接到其他工作表和工作簿；在Excel公式中使用运算符；避免循环引用；控制公式计算。