大家好,我是温家老三。
今天我们来说一下EXCEL的制表与公式,以PMC最经常使用的物料模拟表为例子。
我们可以知道,每颗物料的下单逻辑,以及安排进料的逻辑。
当然了,还有我最喜欢的函数公式介绍。
一、下单
我们先简单介绍一下下单的逻辑:
库存+在途PO-备料需求-安全库存=需下单数量。
库存:公司的库存数量。
在途PO:在供应商手上的PO数量。
备料需求:由备料的机型数量,乘以BOM(物料清单)用量,汇总而来。
安全库存:手工录入,根据交期长短、损耗量、通用性、单价等结合考虑。
在下单时,我们要考虑较多的因素,比如下面:
L/T(交货周期)。
MPQ(最小包装数量)。
MOQ(供应商最少接单数量)。
单价(有的物料会涉及到国外进口,请注意单价要统一换算为人民币或相同的货币)。
安全库存(这个根据经验自行设定,可以随时调整)。
代替物料(假如代替物料有库存与PO,则原料号可以不下单或不提货)。
BOM次数(BOM指物料清单,这个BOM次数,不是指系统中所有的BOM次数,是指最近1年或2年,有下单备料的机型,可以大致知道此物料的通用性与专用性)。
下单总数(可以是历史2年左右的数据,以便知道下单总数量,作为参考)。
下单次数(与下单总数的数据同源,以便在知悉总数量的同时,知悉下单次数)。
上面的所有数据,都是为了下单的数量考量。
知悉交期、包装、价格、代替料、物料通用性等等,
以便下单时,既能够满足生产需求,又不造成呆料的产生。
二、进料
安排进料的逻辑则是:
库存-生产需求=提货数量。
生产需求:在备料的机型中,筛选出即将确定要生产的机型数量。
为什么要这样作区分?因为下单不等于马上生产。
所以,我们只把要生产的数量提进来就可以,以免产生库存金额与供应商的账期。
在这里,要考虑价格,物料的大小,交货的周期等,以便选择最合适的数量与进料日期。
三、公式
在这个模拟表中,我们经常用这几个公式,SUMIF,VLOOKUP,SUMPRODUCT,COUNTIF等等。
库存、在途PO、BOM用量等用SUMIF。
L/T、MPQ、MOQ、单价、采购回复交期、供应商等,用VLOOKUP。
备料需求与生产需求用SUMPRODUCT。
BOM次数用COUNTIF。
下单总数、下单次数没有用公式,用数据透视表,更快更方便。
以库存为例子,SUMIF求和:
=SUMIF(示例!A:A,@A:A,示例!C:C)
中间的第二是本表(@A:A),一般以物料编码为唯一的比照。
第一与第三是外表,第一是物料编码(示例!A:A),与第二相对应,第三是库存的数量(示例!C:C)。
这样的话,就把外表的相同编码,数量汇总进来了。
这里的注意点是:
需区分仓库地点与仓位,仓位是否良品或不良品(只计算良品),仓库地点是本仓库,还是外面的加工厂。
假如有2个或以上的仓库地点,就要区分开,然后再相加。
以单价为例子,VLOOKUP查找对应的数据:
=VLOOKUP(@A:A,示例!F:H,3,0)。
第一为本表,同样以物料编码为唯一比照(@A:A)。
第二与第三为外表,第二是外表的物料编码到单价(示例!F:H),
第三是物料编码到单价第几位数字,从F到H,是3,所以填写为3。
第四是逻辑值,我一般填0。
这样的话,就把单价链接进去了。
这里要注意几点:
日期:一定要从新到旧排序,这样的话,链接的才是最新的单价。
单价的单位:一定要计算为个数,因为有的单价,是以包为单位,例如100或1000为单位。
单价的货币:有的编码是国产的,有的是进口的,会导致有的价格为人民币,有的为港币,有的为美金。所以,需要转换成相同的货币单位。
含税或不含税:注意要取含税价格。
以备料需求为例子,SUMPRODUCT多条件求和。
=SUMPRODUCT($Z$1:$AF$1,Z3:AF3)
Z1:AF1,上面的Z1到AF1,代表基准数字(备料数量),所以,必须要有锁定的符号(绝对引用)。
Z3:AF3,它是由一个个不同机型的BOM用量,代表从Z3到AF3开始,一直可相乘到第几千或几万行(我们按实际即可)。
备料数量乘以用量,即为该机型的需求数,再一个个机型相加,即为所有机型的备料需求。
以BOM次数为例子,COUNTIF条件计数。
=COUNTIF(AB3:AG3,">0")
AB3:AG3,即AB3到AG3,为机型的BOM用量。
>0,指条件,用量大于0,即计算为1次。
这里要注意的是,不能有库存的数字,否则出来的数据是错的。
我是这样操作的,把原表,复制到新表中。
然后,把库存去掉,把需求去掉。
这样,就出来单纯的BOM。
用COUNTIF计算好后,
再复制粘贴到原表中。
以下单总数、下单次数为例子,数据透视表计算求和与次数。
先勾选好想要的数据,比如D:G,
然后在EXCEL表的左上角,点击:插入,在左边,点击:数据透视表。
选择:表格与区域,勾选:新工作表,点击:确定。
把物料编码,拖到行,把MC下单数量,拖到值。
值字数设置,可以设为求和或计数。
这样的话,下单总数,与下单次数,分别都出来了。
再用VLOOKUP或SUMIF,就可以链接过去啦。
好了,这次的小分享,就到这里了。
祝大家五一节快乐。