思路与分.docx
《思路与分.docx》由会员分享，可在线阅读，更多相关《思路与分.docx（23页珍藏版）》请在冰点文库上搜索。
思路与分
Windows用户
仓储作业高效思路EXCEL培训专用教材
前言
物流工作十多年来，从快递工作到第三方物流和企业物流管理,尤其是在仓库作业中，我有很多提高效率和准确率的革新，而这些革新中EXCEL给了我很大的帮助，让我十分受益。
因为EXCEL让我受益匪浅，所以，我很乐意跟大家分享EXCEL那些对我们工作有帮助的功能，尤其是从事物流工作或以后可能会从事物流工作的人，应当需要熟练运用EXCEL的一些功能。
EXCEL是一种办公软件，对我来讲，它是很好的工具，它不能代替其他软件和工具，但其他软件和工具也不能代替EXCEL。
各有所长，各有所短。
没有好与坏，只有适合之说。
能解决实际问题的就是好工具!
EXCEL的特点，就是灵活性比较高，受限止少,是大家常用的办公软件,EXCEL可以通过函数和VBA制作许多的小程序，进行系统性的操作。
EXCEL不仅可以进行各种运算制作各种图表，还能绘制许多图形。
可以用它分析数据，还能以很多形式去体现数据，功能十分强大！
EXCEL的缺点就是存储数据有限，数据量大计算过程较多时，反应速度会比较慢。
所以，我们用EXCEL，就要扬长避短。
我认为，作为一个物流工作人员，尤其是从事仓库管理工作，如果不懂点EXCEL操作，可能会过度依赖现有的系统软件等条件,不能灵活机动,与时俱进,导致许多作业的浪费。
工作中，我们需要用到EXCEL对数据进行统计，分析，计算，这样可以开拓很多思路，不会用EXCEL，就意味着很多思路没有打开，自然就会做得不够好。
当然，即便是EXCEL用得很好的人，没有物流实战经验和管理经验，也不一定就能把仓库管理得好，故实践方能出真知。
作为仓库管理人员,如果仓库没有自动分拣和电子拣货系统,但你要有自动分拣和电子拣货系统的思路；没动自动化的立体仓库,但你要有自动化立体仓库的思路,做到闭着眼睛也能配出货品；没有RFID射频技术,但你要有RFID高效扫描的思路；没有飞机大炮,就把小米加步枪的潜力挖掘起来充分利用.
……
日积月累,非常有幸的是能借助物流沙龙这个平台,将经验和大家分享,为了能让大家有更多的认识,我将十多年工作中用EXCEL解决的种种问题和如何提高操作效率的应用表格汇编于本册，第一部分为常用函数应用示例（截图）,EXCEL函数不熟悉的人员需要先学习第一部分后再看第二部分案例。
由于我一开始EXCEL尚不精通，故资料中定有一些表格会有烦琐之处。
如有不值一看的表,还请一带而过。
无论读者是EXCEL初,中,高级或在不同的物流仓储管理领域,只希望大家能从中各取所需,通过一些案例打开更多的工作思路，在工作中不断的加以优化。
叶剑
2014年7月24日
作者简介:
叶剑,部队优秀”四会教练员”,从快递员,仓管员,客服员,调度做起,是具有丰富的物流现场实战和管理经验的物流师,历任快递公司,第三方物流公司主管,目前就职于绫致时装任仓储部经理.在从事快递,第三方物流,企业仓库管理的近15年中,凭借清晰的工作思路,在企业没有WMS的情况下,借助EXCEL,提高效率和准确率,降低成本的革新案例达一百多项,为服务的企业节约物流操作成本达上千万元,成为许多院校,电商仓储企业和物流沙龙受欢迎的讲师和顾问.
第二部分十年工作中的应用实例
01．在KPI统计表中，求满足多种条件的值
思路说明:
在我们使用”SUMIF”函数对满足条件的数据进行求和时,只能是满足一种条件,而如何同时满足”品牌=2,月份=6,出发地=上海,到达地=杭州,运输方式=中轮仓来货,包装=中箱”,方法一是将每满足一个条件,为一个”a”,一旦六个条件全部满足,即为六个”a”,这个时候再通过SUMIF去计算aaaaaa,第16行和第24行满足条件,值分别为16和50,相加后等于66.
思路说明:
方法一,是用一个a代替一个条件，每满足一个条件即为一个a，如果不用a代替，我们可以直接将条件相加，也就是品牌，月份，出发地，到达地，运输方式，包装直接相加。
那”品牌=2,月份=6,出发地=上海,到达地=杭州,运输方式=中轮仓来货,包装=中箱”的相加结果=CONCATENATE（B5,B6,B7,B8,B9,B10）=B5&B6&B7&B8&B9&B10=”26上海杭州中转仓来货中箱”,第16行和第24行满足条件,值分别为16和50,相加后等于66.
说明：
SUMPRODUCT，可以直接输入需要满足的条件和计算范围，直接求出满足条件的值。
即如果“品牌=2,月份=6,出发地=上海,到达地=杭州,运输方式=中轮仓来货,包装=中箱”，在包装数量（J15：
J32）中求得满足条件的和是66，（B15:
B32=B5）的意义就是在B15到B32之间有哪些单元格的值等于B5,即条件是品牌是等于2，同时每个条件之间用*连接，是指同时具备，最后（）内是计算求和的单元格区域，此方法相对简单。
注意：
无论使用何中方法，均需要先用MONTH从日期中提取对应的月份。
上述方法中，第二种方法应用较多，数据量较大时，第三种方法电脑反应会慢一些。
02．从相对规则的信息中提取所要的信息数据
如下表，系统中导出了每批货品的交货地点，地点中有中文和英文的地址以及品牌等信息。
如果我们只需要中文城市名，那么就需要去掉其它不需要的信息。
用函数公式，可以得到我们想要的结果：
分步操作：
先将所有各地区中的“JJ”全部去除，然后将所有“零售”字样全部去除，再将“J&J”全部去除，这个时候剩下的字段为“左英文，右汉字”，用字符数减去字符串数就知道有几个汉字，用RIGHT函数提取出汉字即可。
求字段的字符数,公式为lenb,求字段的字符串数公式为len.
03．从工作量统计表中提取每一个人的工作量
服装物流工作中，完成某一项操作任务可能会由几个人同时完成，如果需要计算某一个人的产值，用这项任务的产值除以人数即可。
而在工作记录时，会把几个人的名字记录在一起，在这种情况下，我们可以用“筛选”，来一个个计算某个人做了多少。
由于“筛选”的范围有限，为了操作的便利，我们可以采用函数公式来直观的计算每一个人的工作量：
说明：
上表中“检查录入是否正确”是在文员录入人员姓名和操作人数时对其进行提醒，以减少误录或录错，“错误”表示文员录入有误，需进行检查修改。
表中G4的内容是“张三”，“*”&G4&”*”的结果是“*张三*”，代表的是包含“张三”的值，只要单元格内有“张三”，就参与计算。
如果是G4&”*”，结果就是“张三*”，代表的就是以“张三”开头的值。
04．工作量分值与绩效考核
假设一项工作需要1个工人1个小时来完成，我们可以称这项工作的标准工时为1，同时我们可以设定这项工作的值为60分，那么这项工作如果2个人来完成，每人将获得30分，如果是3个人来完成，每人将获得20分。
如果一项工作需要10个工时来完成，那么这项工作的值就是600分。
如果1个人完成了这项工作，那么他将获得600分，如果这件事是3个人来完成，那么每人将获得200分。
至于工作人员实际用了多少时间，不会改变这项工作的值。
如果甲花了8个小时完成，甲每小时的产值就是600/8，如果乙花了12个小时完成，乙每小时产值就是600/12。
那么可以证明甲的工作效率比乙高，甲可以在单位有效时间内创造比乙多的价值。
这里的甲和乙，可以代表不同的员工，也可以代表不同的工作小组。
通过这种方法，我们不仅可以算出每个员工每天、每周、每月获得的工作值，还可以看出每个员工的工作效率。
如果一个员工即使到岗上班，而一天当中什么工作都没有做，那么他获得的值就是“0”，如果工作量越多，工作效率超高，那么他获得的值就越高。
我们统计工作量的目的是为了绩效考核，做到赏罚分明。
当我们统计出每个人的工作量后，可以计算出他在组内和部门内的排名，列于前几名在奖励范围内的，对其给予奖励；列于后几名且工作量低于部门和组内工作量平均值80%的，在扣罚范围内，则给予扣罚。
对部门来讲，人数越多，参与奖励和扣罚的人员就越多，比如说奖励人数为总人数的30％，按比例来分享部门的奖金，扣罚人数为总人数的20％，按比例来承担部门的扣罚金。
对于奖励和扣罚金额会与当月的部门总工作量进行挂钩，工作量越多代表当月工作任务艰巨，奖罚力度就越大，但总体还是重奖轻罚。
对于淡季，工作量较少，工作不饱和的状态，可以取消工作量的考核。
（见下表：
）
以上绩效考核表，需要判断：
1，本月工作量是否在考核范围？
由于本月日均款数为31，大于标准25，即达标，则本月进行考核。
2，本月的奖罚金额各是多少？
31位于30-35之间，则越库组的奖励为200，扣罚为50，拼箱组的奖励为300，扣罚为60。
3，本月有多少人参与奖励与扣罚？
越库有7人，奖2人，罚1人。
拼箱有19人，奖5人，罚3人。
4，奖罚的比例？
奖2人，分别为60%与40%，罚1人，直接为100%。
奖5人，分别为第1名33%，第2名25%，第3名19%，第4名15%，第5名8%。
罚3人，倒数第1名承担50%，倒数第2名承担30%，倒数第3名承担20%。
5，组内过关值，部门过关值？
平均值的80%。
6，计算每个人在组内及部门的排名，是否列入奖罚范围，是否低于过关值，从而计算是否奖罚，以及奖多少罚多少？
绩效考核除考核部门员工工作量的多少之外，还可以考核员工工作是否按时完成。
下表是通过一张配货单的工作量推算出的需要时间（分钟），理论推算出的时间是普通员工一般情况下都可以按时完成的，对于经常超时的员工，需要进行培训或谈心教育，并结合奖惩措施。
见下表示例：
可以看出张三用了30分钟，王五用了50分钟，均超出了规定的时间。
05．考勤异常情况提醒
目前各公司普遍施行指纹考勤，偶尔会出现打不上，或者出现重复打卡，即有2个上班时间或2个下班时间（这个不影响考勤，上班通常以先打的记录为准，下班通常以后打的记录为准），如果没有打卡记录，又没有请假证明，则表示当天没有出勤，也就是旷工。
一直以来人事从考勤机导出数据后，都是一个一个的去看，是否是正常出勤，有没有旷工，迟到或早退，部门人员多的，会花费很大的精力。
（说明一下，正常上班时间是8:
30，下班是17:
00）
试想如果有异常情况的，比如说旷工，早退，迟到，请假，则自动用一种颜色体现，那复核的工作会变得十分的轻松。
步骤一：
对于有异常的，用文字体现出来。
步骤二：
通过条件格式设置，对于出现有异常情况的，用颜色体现出来。
参见下方示意图:
通过公式从打卡记录中提取每个人的上下班时间，并判断是“缺勤”，还是“异常”，缺勤是指当天没有上班记录，也没有下班记录，异常是指缺上班记录或缺下班记录。
06．根据打卡记录生成考勤表
指纹考勤机导出的数据，供各部门进行考勤核对，但是导出的数据无法直观的看出是否迟到，早退，请假，加班，所以需要通过加工转换成与考勤表相似的格式，这样核对起来就方便多了。
上表是考勤机导出的数据，一是无法直接看出哪一天是正常工作，哪一天周末加班，也无法看出哪些人请了假，哪些人加了班，加了多长时间的班。
在初始数据中，区分好哪些人需要在正常工作日计算加班，可以插入一列，录入“1”的是需要计算的，不录的则表示不需要计算。
要获得最终的结果，我们需要分几步走，如下表，可以先根据考勤日期，匹配出星期几，从而判断是正常工作日还是周末，因为周末加班是上几个小时就计算几个小时，而平时加班是从17:
30才开始算起。
这样第一步，可以计算出各人平时和周末的加班时间，公式含义：
如果是周六日，则用下班时间减去上班时间减去中午休息的1小时，如果不是周六日，同时如果有人需要计算平时加班，且下班时间大于17:
30，则用下班时间减17:
30来计算出平时加班时间。
第二步，将计算出来的加班时间进行取整，因为最小计算加班时间单位是0.25H，所以小15分钟的不计加班，大于15小于30分钟的小0.25H，大于30分钟小于45分钟的计0.5H，大于45分钟的则计为0.75H。
第三步，再判断一下正常工作日的时候，有没有人请假，如果没有上下班记录的，则算全天请假，上班时间小于8:
30的算上午请假，下班时间小于17:
00的算下午请假。
有了此表后，再去与各部门的考勤表核对就会方便很多。
07．根据考勤机打卡记录统计加班人数
人事从考勤机导出数据后，需要统计出哪些部门的人员加班超过21:
00，面对庞大的数据，如何通过函数公式，让结果直接生成呢？
操作方法：
先通过函数公式计算出哪些人加班超过了21:
00，没有超过21:
00的不体现。
再将21:
00下班的数据，转换为“１”，以便进行统计。
最后再用SUMIF函数统计出各部门的人数，得到想要的结果。
08．考勤统计表的系统性制作
部门对所有员工每日的出勤，请假，加班情况进行记录，从而可以从记录的数据中进行各种统计汇总．
见示例：
Sheet（统计）：
详细记录每天的人员出勤情况，当天的班值，有无人员请假，离职，加班等休息，并记录事由和时长。
说明，在录入日期后，会跳出相应的日期，班值（如正常班为1，周末班为2，国定假日为3），工资结算月。
Sheet（请假）：
得出所有的请假情况，体现所有人员的请假离职记录，及只要在统计表中有记录，此表就会呈现。
Sheet（加班）：
求每天的加班情况，输入日期，即可从统计表中获取当天的加班信息，打印供加班人员签字确认。
Sheet（汇总）：
是部门所有人员全年的请假明细，根据请假表汇总了每个员工每月各请了什么假，请了多少时间。
Sheet（工时）：
求每个人每个月的工时，从统计表中汇总出每个员工每月的在岗工作时间。
每月考勤，sheet（５月，６月，７月．．．）：
自动生成每个月考勤表，根据统计表信息，直接生成考勤表，直观反应部门每人每天的出勤情况。
09．将已知信息录入到指定表格
每款货品发到每个地区都会有不同的单据号。
以往我们每一款的单据号都要一一录入，刚开始几个地区录入也就一分钟左右，随着发货地区的增多，到了几十个地区，单据的录入就比较花费时间，且容易出错。
所以，考虑了现在直接关联数据的方法：
注意：
上表中的常州，绵阳，十堰，川北等城市，分别属于：
无锡，成都，武汉等地区，但匹配单据时，然按原城市进行匹配。
同时注意单据号原先是在C列，引到D列之后E列，是为了用VLOOKUP进行匹配。
10．数据透视的应用
通常情况下，在我们的工作表中会有大量的重复信息。
比如说部门人员信息表中，每个人的姓名不一样，但是会出现性别相同，省份相同。
在我们统计一个部门人员来自哪些省份时，就会用到数据透视表。
为了统计结果的直观性，我们也可以不采用数据透视表，而是采用函数公式，可以直接看到哪些信息是重复出现的，以及已经产生了多少不同的信息。
下表是一个数据透视的示例：
注意：
在用COUNTIF计数时，计数的区域是本单元格以上的区域，表示截止到本元格，本元格的数值出现了几次。
只要出现超过1次的，均不体现。
从而，最后的函数公式的计算，相当于筛选D列单元格是1的时候，A列单元格的值。
11．每周工作量的汇总
从每日工作量数据的统计表中，将每周或每月的工作量汇总到一个表，可以做一个固定的统计表格式，这样只要按日统计，结果会自动的在周报或月报上体现，如下表示例：
表中每个周期是8行，其中7行分别是周一到周日的工作量记录，第8行是这7日的工作量合计，统计信息时可以像流水账一样的记录下去，在它的右侧是汇总信息的格式，其中公式=INDIRECT（ADDRESS（$M6*8+6,4））中M6的值是1，代表第1周，那么行号就是1*8+6，6是因为左侧统计表是从第6行开始的，4是表示ONLY箱数是在统计表中的第4列，那么第4列，第14行”D14”的结果就是ONLY第一周合计的操作箱数2752，以此思路将各周的合计汇总在一起。
12．工作计划的”7要素”
在我们制定一项任务的工作计划时，需要考虑这项工作任务需要什么人，什么设备，什么物料，涉及哪些单证，采用什么方法，需要多大的场地，如何布置，需要多少时间完成。
这就是我们常说的七要素“人，机，料，法，环，能，信”，所以对于经常性的工作任务，我们可以将以往的经验数据进行汇总编辑保存，以方便日后需要时对数据进行查阅和计算：
此表中的越库产值计算，是通过输入影响工作量的相关参数，计算出这项工作需要花费的时间，单位为分钟。
在此表中输入查询项目“越库”，在下方会自动体现影响越库时效的参数有：
越库箱数，越库地区，越库颜色数，单箱重量，分配地区，托盘数，加箱件数，加箱数，单件体积，随之在右侧单元格输入相应的值，并可以计算出所需要的工时。
且输入“越库”之后，同时会出现“越库”这项工作，需要的人员，设备，物料，场地及相关的信息和操作流程。
13．仓储费用计算
第三方物流公司,在代客户保管货品收取仓储费时，有的是按客户存放货物量乘以存放天数进行收费，可以订一个收费标准，比如按1立方的货物存放1天的费用为0.5元来计算.
说明:
方法一的思路,以张三客户为例,期初（2月1日）有50方的货品,但由于2月2日发出了5.5方,也就是说50方的货品,只存放了1天.收费为50*1*0.5=25.到了2月7日又发出了10方,也就是说44.5方的货品存放了5天.收费为44.5*5*0.5=111.25,以此方法类推,算出期初货品当月的仓库费用.再加上”月中”入库货品的费用,即得到总费用.
说明:
方法二的思路,是以期初货品的存储费＋期中入库货品的存储费－当中发出货品的存储费＝当月存储总费用.
比如说张三在2月2日有5.5方的货品发出,这5.5方货品后续有26天没有存放在仓库,即5.5*26*0.5=71.5无需收取.2月7日发出了10方,后续有21天没有存放在仓库,即10*21*0.5=105无需收取,以此类推,减掉所有发出货品未储存期间的仓储费.
注:
两种方法计算的结果完全相同!
14．简易库存账的制作
我们在做仓库管理时，通常会有两个流水账，一是入库流水账，可以查出仓库从始至今进了哪些货品；另一个是出库流水账，可以查出仓库从始至今发出去了哪些货品。
如果把这两个流水账整合到一起，就可以知道仓库还有哪些货品，以及仓库还剩多少货品。
通常库存帐涉及的几个表：
物品信息表：
与货品有关的所有信息，越详细越好。
以服装举例，包括：
服装名称，服装代码，对应的条码，服装上店日期，服装的价格（成本价、批发价、零售价等），服装的体积，服装的重量，服装的包装规格，服装附件信息等。
入库统计表：
详细记录货品的入库信息，什么时间？
什么货品？
从什么地方来？
入库数量？
箱数？
入库单号？
等
出库统计表：
详细记录货品的出库信息，什么时间？
什么货品？
发到什么地方？
出库数量？
箱数？
发货单号？
等
库位信息表：
体现库房有多少库位？
每个库位的编号，每个库位的容量，哪些货位已用，哪些库位未用，已用库位存放货品的数量等？
简易的库存账制作示例：
212305025这款货品，在10月末（11月初）仍有55件的库存，之后入库了5次，数量分别为（10，10，10，5，10）共计45件，出库4次，数量分别为（10，5，8，25）共计48件，目前的数量（期末）=55+45-48=52件。
15．第三方物流台账
作为一家第三方物流公司，在为客户提供仓储服务时，仓库里存放来自不同客户的不同类型不同名称的货品，通过做账从而知道什么客户在什么时间存放了什么货品存放在哪里存放了多少?
在什么时间发运了什么货品发往哪里发走了多少?
累计发运了多少次货品,货量多少?
做账时用最简捷的方法去识别货品，且方便记录，“编号”就是最好的方法之一，最简易的货品编号，就是由“客户编号＋产品序列号”组成。
Sheet（客户信息）:
先将客户进行编号，用编号来区分不同的客户，比如说陕西喜莱坞实业有限公司的编号是A03，那么A03就是陕西喜莱坞实业有限公司。
每个客户都有一个编号，且编号不可以重复出现，是唯一的，新增客户的编号，按序递增。
Sheet（库存）:
在做货品库存账时，录入客户的名称即跳出客户信息“编号”，同时这家客户只要录入新增的“产品名称”，即会计算出，是这家客户的第几件商品，也就是产品序号。
由“客户编号”＋“产品序号”即得到“货品编号”。
中间可以用“－”进行连接。
有了“货品编号”后，在做“入库流水账”与“出库流水账”时，均以货品编号为准，同时做任何一家客户的“库存查询”“每日进出库查询”“客户进出库查询”，均以“货品编号”为索引。
Sheet（入库）:
记录每个客户每天的入库明细.
Sheet（出库）:
记录每个客户每天的的发货明细.
Sheet（库存查询）:
查询每个客户的当天库存情况.
16．仓库可视化管理
在EXCEL库存表中，设定相关函数公式，不仅可以实时反应库房库存量多少，库位的使用情况，还可以根据发货单据信息，体现出此张单据哪些库位需要发货，发多少，并提示行走路径（类似电子拣货系统）。
请看示例：
上表中的“平面效果图”：
每个库位会随着库房货品的出入库，货量的减少或增加而相应“变色”，蓝色体现的库位，代表着这些库位空闲，尚没有放货。
黄色体现的库位，代表有货，但此库位货量小于30。
如果有新货入库，某个空闲的库位被使用，则颜色相应会变成绿色，表示该库位已被使用。
红色同时有黑字体现的，是表示正在操作的配货单此库位需要取货，并体现取货量。
作为管理者可以通过类似表格的制作，清晰的了解仓库的布局，库存情况，空间利用率，不到库房也知道哪些库位货放得很满，哪些库位空闲，直观的展现每张配货单的取货点和取货量，以更好的做好协调工作和计划。
（上图左侧）出库/流水账：
所有出库信息的统计。
（上图右侧）配货单：
输入单据号，即可从“出库”中匹配这张单据需要配发的货品，并匹配各个货品所在的库位，同时会在“平面效果图”中通过“红底黑字”体现。
17．如何做到库存货品的先进先出
在配发货品时，可以根据货品入库时间（或生产时间）的先后，将放在不同库位，不同入库日期（生产日期）的相同货品，实现先进先出。
也就是说，在配发货品时，要先将先入库的货品配发出去。
如果前一批不够配发，不足的数量再从下一批配发，把后到的货品先留存下来。
下表可以看出，根据当天的订单量计算出了哪些库位哪些日期入库的货品需要发出，是全部发出，还是发出一部分。
上表中3PA0097-1这款货品，需要配发的数量是55+40=95，按照先进先出原则，先从前几批入库的货品当中进行配发，直到满足95，表中可以看出，当从3月2日入库的货品中拿出40时，已经满足了95，当批的货品还余下了10，可以留着下次再发。
由于已经满足数量，这之后入库的货品，就动不到了。
函数表达：
如果该批之前入库货品的数量小于需求量，则该批之前入库的货品全部出库，如果该批之前入库货品的数量大于需求量，则从该批中发出尚还欠缺的数量。
18．如何判断货品是否按先进先出发货
下表是零配件发货记录的表格,根据发货记录来判断每个零件是否按先进先出发货.其中”标签序列号”中有入库日期的信息,可以通过函数公式提取出入库日期.判断货品是否按先进先出,主要是看发此批零件时,发货的零件当时库存的最早日期是不是早于实发零件的入库日期.如下表中发料员jiangs在发1028169零件时,配发的零件入库日期为2014年05月04日,而当时库存最早日期为2014年04月17日,这就说明发jiangs此次没有按先进先出发货.
19．大宗货位的自动选择
酒类的电商仓库，存放来自不同酒厂的酒，除种类不同有白酒，红酒，黄酒，啤酒之分外，还有不同的品牌和系列，以及酒精度之分。
为了满足需求，通常每种酒的库存少至几十箱，多至上千箱，于是为了节约仓库空间，通常是采用重型立体货架进行存储，作为大宗库位区，在拣货位缺货时，再从大宗货位取下，补到拣货位。
上面是一个大宗库位的示意图，Z01是某一排货架通过的编号，Z01-A01-11就是指大宗货架01通道左侧第1区间的第1格的第1层货位。
同时，下方显示的库位，表示目前尚未存放货品，还处于“闲置”的库位。
每天都有来自
升级会员 