林屹网站_林屹博客

林屹 认证讲师
OFFICE 互联网+大数据 EXCEL PPT 培训专家
http://officelinyi.jiangshi.org收藏讲师购买讲师服务

林屹:回复美国普惠公司Isa Yi:关于利用EXCEL自动处理订单分配的问题

关键词:[电脑技能] 浏览:2433 发布日期:2016-01-20 网页收藏

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

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

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


    【问题描述】

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

    1.需求计划

    50

    Qct,2015

    50

    Dec,2015

    106

    Jan,2016

    97

    Feb,2016



    2.Open 订单数量




    4500021688/00020

    175

    4500021688/00030

    85

    4500024950/00010

    43

    3.分摊出来的结果

    4500021688/00020

    50

    Qct,2015

    4500021688/00020

    50

    Dec,2015

    4500021688/00020

    75

    Jan,2016

    4500021688/00030

    31

    Jan,2016

    4500021688/00030

    54

    Feb,2016

    4500024950/00010

    43

    Feb,2016

    【林屹老师回复】

    其实这个问题看起来很简单,似乎就是小学生算算术——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能有所帮助。如果大家有更好的方法和操作请告诉我,让我学习,共同进步。


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

    http://yunpan.cn/cHvBwsxaN3GDC (提取码:8724)


    最后祝大家国庆愉快!

企业会员在线交易流程