Excel动态图表在仓库管理中的应用

来源：本站 | 发布日期：2023-03-24

一、前言

笔者所在公司专业生产各类减速电机、电机。减速电机作为一种动力输出装置,广泛地应用在各行各业,它们基本是按照客户的要求进行定制(如减速比、功率、安装方式等要求),所以减速电机行业的生产模式基本为按订单装配(Make-To-Order)、小批量生产。由于产品类别繁多,生产所需要的零部件种类可达数千种之多。在工厂内部的物料配送基本以手工＿拣选、配料至装配线的方式来进行。以笔者所在的公司为例,用于减速电机装配的物料多达7,000多种,订单的平均订货数量不到3台,按照年销售量100,000台来计算,全年需要处理超过30,000个生产工单,假设每个生产工单需要拣选的物料种类为10种,那么,仓库的拣选工人每年需要拣选物料超过300,000次。

减速电机的零部件基本都存放在货架上,货架的侧视图如图1。

以上货架侧视图为普通的搁板货,019:货架编号;横向的1/2/3/4…20:在货架水平方向的位置;纵向的5/4/3/2/1:由高到低的层数。以红色框为例,这个框对应的货位是:019-20-05。

从图1可以看出,物料摆放在不同高度的货架上,每层的净高是45cm,在不需要其它机具配合的情况下,工人通常可以非常方便地取到第3层的物料,4层及以上的物料就需要配合梯子来取料,所以物料摆放的层数是否合理,对拣件的效率会产生很大影响。

另外,物料通常可以分为专用物料和通用物料。专用物料是指专用于某些系列成品的物料,通用物料是指为多个系列成品共用的物料。并且,每条装配线所能装配的成品系列也是相对固定的。因此,专用物料具体摆放在哪一个货架,这个货架离装配线的距离是否最近,也将会对拣选的行走距离产生很大影响。

综上所述,物料摆放在合适的货架、合适的货位,将会有效地提高拣选效率。这个对于专业的管理人员来说,是非常容易理解的,大部分人也能意识到这个问题并通过各种方法来不断地改善、提高物料摆放位置的合理性。

图1

二、热力图的制作

笔者先前的做法是通过从ERP系统中导出的一段时间内的历史拣选数据,在Excel表中通过数据透视表对每个货位的拣选频率进行统计,根据汇总的次数、拣选的总重量(拣选数量X零部件单位重量)、零部件的单位重量、零部件所用于的成品系列等因素来检查目前的摆放货位是否合理,并据此来调整物料的摆放货位。但由于数据量大,所要的考虑的因素较多,经常会在数据透视表中不断地调整字段、排序、筛选,效率不高,还经常容易出错。

在偶然间看到了ExcelPro刘万祥老师关于热力地图(HeatMap)制作方法的介绍(注:热力地图是一种图形化的数据表现形式,在一个二维地图上以变化的颜色代表数值大小,一个与之非常类似的图是TreeM ap。热力地图也指用于表现某个专题的分布图),他将中国地图上的各个省份根据其(GDP值进行自动填色,通过颜色的深浅来直观地表现各个省份的GDP值,给人以耳目一新的感觉。笔者由此想到,何不将货架的侧视图画出来,再按各个货位的拣件频率来对每个货位进行颜色填充,拣件频率越高、颜色越深,拣件频率越低、颜色越浅。

参照刘万祥老师的方法,笔者制作完成了货位拣件频率热力图,如图2。

图2

图3

图2中,颜色越深,表示在过去一段时间内从这个货位拣选的次数越多,颜色对照的拣选次数可以参考图2表格下方的图例。

通过热力图,仓库的管理的人员可以一目了然地看到各个货位在过去一定时间段内的拣件次数,对于不合理的货位及时地进行调整。

以下为货位拣件频率热力图的具体制作方法。

1. 准备货架侧视图模型

在一个工厂(仓库)中,通常会有多个结构相同的货架,所以我们只需要画出一个货架结构图,通过动态调整货架编号的方式来检查所有(结构相同的)货架。

在Excel中插入多个矩形,按照实际货照的结构进行布局(如图3),一个矩形对应一个货位。

以笔者所在公司为例,货位的编码格式为:AAA-BB-CC,这是一个三维的编码方式,其中:AAA表示货架编号,BB表示纵向的库位,CC表示横向的层数。例如:019-01-02就是019号货架,01库位,第2层。

在此,笔者根据公司的实际货位,将各个矩形框以BB-CC的格式进行命名,从01-01、01-02、01-03…….命名到50-05。如图4。

需要注意的是,这一步骤非常简单但很费时间,请仔细检查所有命名的名称是否正确,这将影响到后续的宏代码是否能顺利执行

2. 设置分档阈值和图例

根据自己的需要对颜色进行分档,建议不要太多(笔者分为7档)。

图4

在F列输入各档区间的下限值,在K列输入颜色号:colorl～color7,在B列填充由浅到深的颜色,代表数值由小到大,并分别命名为colorl～color7 (对K列的名称一致)。如图7。

3. 设置查找公式

图5

在B21单元格内随机输入一个货位号,在D21单元格内用VLOOKUP函数从源数据中取值,以取得B21单元格货位的拣件次数,在F21单元格内同样使用VLOOKUP函数根据D21单元格的拣选数值来取到对应的颜色值。如图8、。

以下为各个单元格的具体公式:

D21:=vlookup(A1&”一“&B21,源数据,columns,0)

注:由于B21单元格并非完整的货位号,所以需要将A1单元格的货架编号(AAA)与B21的货位编号(BB-CC)作文本连接,以取得正确的值;源数据为源数据存放的单元格区域。

图5

F21:=vlookup(D21,F 11:K 17,6,1)

注:此处由于进行地的是模糊查找,所以最后的查找参数为1。

4. 设置列表框并实现动态选择

插入一个表单控件-列表框,并对列表框进行格式设置如下(光标移至控件上、右击鼠标),如图9。

A.数据源区域为填写货架编号的单元格区域

B.单元格链接为点击控件后返回值所存放的单元格

并将控件的选定类型设为“单选”。

同时,为了实现动态,在A1单元格输入公式:=INDEX(A,B)

A:为货架编号存放的单元格区域

图9

B:为控件的单元格链接

5. 编写自动填色的宏代码

以上全部设置完成后,就可以开始编写宏代码了。

6. 将宏代码指定给列表框

将编写完成的宏代码指定给列表框。

方法:右击列表框控表→指定宏→选择第5步编写的宏代码→点击“确定”。如图10。

图1 0

以上全部完成后,就可以通过选择列表框上的货架编号来自动地对每个货位进行颜色填充,通过颜色的深浅来判断货位的摆放是否合理。

三、完成后的效果

填充后的效果,如图11。

从图11可以一眼就看到货位019-03-05的颜色很深,对照图例知道这个货位共拣件501-1000次,说明这个货位设置不合理,影响拣件效率,应该进行调整。同时也可以看到货位019-16-03的颜色较浅,对照图例可以知道这个货位共拣件6-50次,那么第一判断就是这将这两个货位上的物料进行对调。对调后的拣件效率得以马上提高。