前段时间到一家外企上EXCEL课，该企业很多学员都遇到了一个按他们话说“每个月都需要花半个月时间”去解决的一个问题——分配订单。

我和EXCEL打交道这么些年，也遇到过很多类似这样要花大量的时间才能解决的问题，但最后发现其中多半都是因为最初的解决思路和方式不对而造成的。

所以我们今天一起来看看这样一个问题：利用EXCEL自动处理订单分配。

【问题描述】

林老师，有个问题，我们目前需要花大量的手工时间，把下面这种发运需求计划（1）按open订单数量（2）的要求生成（3）这样的分摊结果，请问excel是否有公式可以直接把需求分摊到open订单里面呢？谢谢！

1.需求计划

3.分摊出来的结果

【林屹老师回复】

其实这个问题看起来很简单，似乎就是小学生算算术——1个订单填满了又继续填充下一个订单——但仔细看看，其实不是简单粗暴地填充订单那样简单，这填充订单时还需要考虑订单号（1）和日期（2）的逻辑关系。所以我想也正因为如此，这个两个维度的分配难题才困扰了Isa Yi学员这么久。

其实我们换个思维，要让表（1）和表（2）变得有关系，最好的办法是建立一张“二维表”，把这两个维度都包含进去，如下图所示：

将数量放在二维表外侧，时间和订单号放在二维表内侧，这是方便稍后进行的带表头的数据透视操作。

接下来，我们要利用公式进行数据自动填充，为了避免循环引用，这里用3个公式进行填充——

【公式一】在表格的第一个单元格C3中录入公式：

=MAX(MIN($A3,C$1),0)

如下图蓝色部分：

【公式二】在表格的第一行第二个单元格D3录入公式并向右填充：

=MAX(MIN($A3-SUM($C3:C3),D$1),0)

如下图红色部分：

【公式三】在表格的第二行第一个单元格C4录入公式并向下向右填充：

=IF(C$1>SUM(C$3:C3),MAX(MIN($A4-SUM(B4:$C4),C$1-SUM(C$3:C3)),0),0)

如下图绿色部分：

这3个公式具体函数语法我就不赘述了，都是很简单的函数语法，我给大家大致翻译一下：将A列的订单号数据按第1行日期数据要求填充至右侧表格中，当填满一个日期就继续向右填充，若填不满则用下一个订单号数据继续填充。

这里用三个部分的函数进行分别填充，是为了不循环引用数据。如果大家有更好的更为方便的公式，也请告诉我，谢谢！

另外说一句：这个二维表的数据区域如果要扩大，可以直接拖动红色和绿色部分进行公式填充。

接下来，我们要把这张二维表变成表（3）那样的一维表，可以利用数据透视表帮助我们实现，具体步骤如下：

首先，在此表中任一单元格依次按下Alt、D、P三键，弹出“数据透视表向导——步骤1”窗口，在选择“多重合并计算数据区域”，并继续点击“下一步”，如下图所示：

在“数据透视表向导——步骤2a”中选择“创建单页字段”，并继续下一步，如下图：

在“数据透视表向导——步骤2b”中的选定区域引用二维表中B2:F5区域，注意不要选数字区域，继续点击下一步。

最后选择数据透视表显示位置在“新工作表”，点击“完成”。

通过向导新建的数据透视表如下图所示：

双击F8单元格的最终总计的合计数：303

这时就自动生成了一个所有数据的一维明细表Sheet1，如下图：

最后将此一维明细数据表进行优化操作，删除0值和多余列即可。

这个方法就是将两个要求建立关联的表格进行组合，然后在二维表下进行公式填充，最后通过数据透视表使之变成我们需要的二维表。

希望上述方法对美国普惠公司Isa Yi能有所帮助。如果大家有更好的方法和操作请告诉我，让我学习，共同进步。

如果有需要的学员，可以通过下面地址或点击【阅读原文】进行本例文件下载：

https://yunpan.cn/cHvBwsxaN3GDC （提取码：8724）

最后祝大家国庆愉快！