1、(完整word)物流管理 工作中的EXCEL应用1思路与EXCEL物流十多年工作中解决各种问题的实例叶剑仓储作业高效思路EXCEL培训专用教材前言物流工作十多年来,从快递工作到第三方物流和企业物流管理,尤其是在仓库作业中,我有很多提高效率和准确率的革新,而这些革新中EXCEL给了我很大的帮助,让我十分受益.因为EXCEL让我受益匪浅,所以,我很乐意跟大家分享EXCEL那些对我们工作有帮助的功能,尤其是从事物流工作或以后可能会从事物流工作的人,应当需要熟练运用EXCEL的一些功能。 EXCEL是一种办公软件,对我来讲,它是很好的工具,它不能代替其他软件和工具,但其他软件和工具也不能代替EXCEL
2、.各有所长,各有所短。没有好与坏,只有适合之说。能解决实际问题的就是好工具!EXCEL的特点,就是灵活性比较高,受限止少,是大家常用的办公软件,EXCEL可以通过函数和VBA制作许多的小程序,进行系统性的操作.EXCEL不仅可以进行各种运算制作各种图表,还能绘制许多图形。可以用它分析数据,还能以很多形式去体现数据,功能十分强大!EXCEL的缺点就是存储数据有限,数据量大计算过程较多时,反应速度会比较慢。所以,我们用EXCEL,就要扬长避短.我认为,作为一个物流工作人员,尤其是从事仓库管理工作,如果不懂点EXCEL操作,可能会过度依赖现有的系统软件等条件,不能灵活机动,与时俱进,导致许多作业的浪
3、费.工作中,我们需要用到EXCEL对数据进行统计,分析,计算,这样可以开拓很多思路,不会用EXCEL,就意味着很多思路没有打开,自然就会做得不够好。当然,即便是EXCEL用得很好的人,没有物流实战经验和管理经验,也不一定就能把仓库管理得好,故实践方能出真知.作为仓库管理人员,如果仓库没有自动分拣和电子拣货系统,但你要有自动分拣和电子拣货系统的思路;没动自动化的立体仓库,但你要有自动化立体仓库的思路,做到闭着眼睛也能配出货品;没有RFID射频技术,但你要有RFID高效扫描的思路;没有飞机大炮,就把小米加步枪的潜力挖掘起来充分利用。日积月累,非常有幸的是能借助物流沙龙这个平台,将经验和大家分享,为
4、了能让大家有更多的认识,我将十多年工作中用EXCEL解决的种种问题和如何提高操作效率的应用表格汇编于本册,第一部分为常用函数应用示例(截图),EXCEL函数不熟悉的人员需要先学习第一部分后再看第二部分案例。由于我一开始EXCEL尚不精通,故资料中定有一些表格会有烦琐之处.如有不值一看的表,还请一带而过。无论读者是EXCEL初,中,高级或在不同的物流仓储管理领域,只希望大家能从中各取所需,通过一些案例打开更多的工作思路,在工作中不断的加以优化。56r 2014年7月24日目录目录3第一部分 EXCEL基础及常见函数应用示例501.了解EXCEL表格502.运算及引用符号503.加减乘除的基本计算
5、604.“的应用605。年,月,日的计算706。时,分,秒的计算707.数据统计中的应用(求各种值图例)808。字段信息的提取与编辑1309。“IF”函数的广范应用1510。文字格式的转换1811。信息的查找与匹配1812。“INDEX”在信息查询与提取中的应用及说明21第二部分 十年工作中的应用实例2301在KPI统计表中,求满足多种条件的值2302从相对规则的信息中提取所要的信息数据2603从工作量统计表中提取每一个人的工作量2704工作量分值与绩效考核2705考勤异常情况提醒2906根据打卡记录生成考勤表3107根据考勤机打卡记录统计加班人数3308考勤统计表的系统性制作3609将已知信
6、息录入到指定表格4010数据透视的应用4111每周工作量的汇总4212工作计划的7要素4313仓储费用计算4514简易库存账的制作4715第三方物流台账4816仓库可视化管理5117如何做到库存货品的先进先出5418如何判断货品是否按先进先出发货5519大宗货位的自动选择5620服装线上与线下入库货品库位分配5821服装退货整理与箱单打印6122退货上架货品堆码指定托盘号6323箱唛连续打印的VBA方法6524自动化分拣思路之服装越库操作6725根据已拣货品量求剩余量6926货品完全匹配条件的分拣7027RFID条件下的高效分拣思路7128RFID思路之整托货品越库分配的计算(精典案例)762
7、9发货包装(拼箱)自动计算8230库内短驳加箱(零箱)拼箱标签的制作8431根据整箱规格检查播种或拣货过程的准确性8532播种后如何提高点数装箱的效率与准确率?8533播种单格式高效读报方式的转换8634分批入库货品的配发9135黄金包装需求量计算思路9336不同配比条件下的黄金包发货量计算9537电商操作与物流发货思路9938电商拣货流程优化10339电商分区拣货再合单的操作思路11140电商两地发货模式的单据审核11441电商拣货路径优化与距离计算11942亚马逊电商导出数据的整理12743第三方物流(企业物流)发货方式及承运商自动选择思路13144两家货运公司价格与时效对比参考13245
8、上海市内快递编号法分拣13446上海同城快递“蜘蛛网”中转班车运行设计13647全国办事处仓库分布图13848上海地区店铺分布图13949长三角地带物流中心选址分析14150服装的标准化物流14251不同规格的包装以满足不同需求的发货146仓储现场管理心得147作者简介:叶剑,部队优秀”四会教练员”,从快递员,仓管员,客服员,调度做起,是具有丰富的物流现场实战和管理经验的物流师,历任快递公司,第三方物流公司主管,目前就职于绫致时装任仓储部经理。在从事快递,第三方物流,企业仓库管理的近15年中,凭借清晰的工作思路,在企业没有WMS的情况下,借助EXCEL,提高效率和准确率,降低成本的革新案例达一
9、百多项,为服务的企业节约物流操作成本达上千万元,成为许多院校,电商仓储企业和物流沙龙受欢迎的讲师和顾问。第一部分 EXCEL基础及常见函数应用示例在第一部分中,Excel函数基础应用及说明都以精简截图的方式表达,希望让读者在入门的时候能够对Excel函数功能有更直观的了解。01。了解EXCEL表格02.运算及引用符号03.加减乘除的基本计算04.“”的应用单元格的位置,由列坐标和行坐标组合确定,列坐标用英文字母表示,行坐标用阿拉伯数字表示,如第1列的表达式是“A,第2列的表达式是“B”,以此类推。第1行的表达式是“1”,第2行的表达式是“2”,以此类推。第2列的第2行位置就是单元格B2,第1列
10、的第11行位置就是单元格A11,在我们用函数输入公式时,A1就是表示A1这个单元格的值,A11就是表示A11这个单元格的值,如果在某个单元格内输入“=A11”,那么A11单元格里面是什么,这个单元格将体现什么。如上表中,A11单元格内是一个“甲”字,在B11中输入了=A11,结果也就显示了“甲”字。在选中B11这个单元格往下拉时,相应的单元格B12,B13,B14,分别就有了结果A12,A13,A14。但如果在公式中的行号前面加“$”,再往下拉时,仍是11,也就是说“”已经对11进行了锁定.要注意的是,将选中输入公式的单元格,如果公式中的单元格行号没有用$锁定,向下拖动时会依次增加,列号不变。
11、如果列号没有用锁定,则往右拖动时,列号依次增加,行号不变.05。年,月,日的计算求某个日期的年份,月份和号,分别用:YEAR,MONTH,DAY。在EXCEL任意单元格内输入此“公式”,回车后就会体现当天的日期/当时的时间。用结束日期减开始日期,即可得到它们之间相隔的天数。然求当中的工作日,则需要用函数NETWORKDAYS。如果要知道某一天是星期几?可以先通过WEEKDAY函数求出所在周的第几天,然后再去匹配第几天是星期几,在计算时需要注意中西方一周的第一天是不同的.06。时,分,秒的计算求某个时间的时,分,秒,可以直接用函数:HOUR,MINUTE,SECOND。如果计算某项工作开始到结束
12、,用了多少分钟,可以先用结束时间减开始时间,得到用了多少小时多少分钟,然后用小时数60分钟分即可。 07.数据统计中的应用(求各种值图例)尤其求最大公约数,可以确定最小需求单位,在物流中意义很大。 直接输入=PI(),就得到圆周率。MAX,求一组数值中的最大值;MIN,求一组数值中的最小值。SUM,常用的求和函数,直接引用,即可求某个范围所有数值的和。COUNT与COUNTA都是计数,区别在于COUNTA可以将文本格式的值进行计数,而COUNT只对数值进行计数。AVERAGE与AVERAGEA都是求平均值,但AVERAGE只是针对数值,用数值的和除以数值的个数,而得到平均值。但AVERAGEA
13、,则是用数值的和除以所有参与的(包含文本)的人数,而得到的平均值。INT,取整,不保留任何小数。ROUNDUP,向上舍入,可按需求保留小数位数,“0”即是不保留。MOD,求被除数与除数的余数.此例在仓库管理中计算入库上架库位和货品盘点中应用较多。RANK用于求某个值,在某个范围的大小序列。B2单元格是王二小的成绩,在B2:B7(6个人的成绩)中排名第5。注意计算排名与倒数排名公式的区别,在后面加上“1”的作用。=SUMPRODUCT((A2:A13=”南大”)(B2:B13=大一”)(D2:D13)) 解释:求符合多种条件的和,即当学校为“南大”时(A2:A13=”南大”),班级为“大一”时(
14、B2:B13=”大一”),有多少人参加(计算范围D2:D13),“*为连接多种满足条件的符号。注意在使用SUMPRODUCT时,“,”与“*”的区别,如上SUMPRODUCT(B2:B9,C2:C9),其结果就是从B2*C2一直加到B9C9。COUNTIF,求在什么范围,满足“什么”条件的数值有几个,计数所用。SUBTOTAL函数,具有多种函数的功能,在计算范围前加“1,2,3,4,5,6,7,8,9”结果各不一样。SUMSQ,所有数值平方的和;SQRT,求某个数值的平方根.SUMIF是求满足条件的和,如求一年级有多少学生,及在A14:A22年级范围内有多少是等于A14一年级”的,如果是“一年
15、级”,那么后面的“人数”相加即得到所求的值。注意:COUNT,COUNTA,COUNTIF的区别。COUNTIF与SUMIF的区别,一是计数,一是求和。MIN(计算范围)SMALL(计算范围,1)MAX(计算范围)LARGE(计算范围,1)解释:上表公式,是计算某个产品本次的发货日期减去上一次的发货日期,得到间隔的发货时间。其中嵌用了:INDEX,LARGE,IF,ROW.在D4单元格输入上面公式,然后下拉即可, 是同时按:ctrl, shift, enter 生成。08.字段信息的提取与编辑注意:与CONCATENATE的区别.“&”的输入,是按住shift键同时按上行数字键“7即可。注意:
16、LEN与LENB的区别,及字符与字符串的区别,一个文字是一个字符串,一字符串可能是一个字符,也可能是两个字符,一个数字或字母只有一个字符,但一个汉字却有两个字符。小窍门求字段中有几个汉字:如果在上表中,用计算出来的字符数减去计算出来的字符串数,就可以知道字段中有几个汉字了.LEFT(A5,3),是求A5单元格中左侧3位的字段。A5单元格内是“江苏省泰州市”,左侧3位是“江苏省”.MID是从字段中间提出信息的函数,一个人的出生年份,在身份证号码中,是从第7位,向后4位。上表中=RIGHT(A4,1),A4中的值是“中华人民共和国”,“中华人民共和国”的右侧第1位,是“国。FIND,查找某个字符串
17、在某个字段中的第几位.REPT,将需要重复的字段,重复“N”次.REPLACE,将字段中第几位指定往后几位的字段,替换成相应的内容。SUBSTITUTE,将字段中,符合替换的字段,替换成相应的内容.09。“IF”函数的广范应用IF,如果满足条件,即为结果“A”,否则就为结果“B”。=IF(B3=60,”是”,”) 如果王二小的成绩B3大于等于60,即显示“是,否则什么也不显示。=IF(B1160,不及格”,”) 如果王二小的成绩B11小于60,即显示“不及格”,否则什么也不显示。“”表示不满足条件时,不显示任何信息.AND,如果三门课程的成绩同时达到90,即”是”全优生,否则不是,即为否”.O
18、R,有任一门课程达到90分,即为满足条件。上表以“王二小”为例,成绩为76,使用“IF”判断,由于不满90,所以不是“优,由于不满80,所以也不是“良”,接下为大于60,所以评定为“及格”。而用“LOOKUP函数,76位于6080之间,匹配结果为“及格”。先判断两个日期之间相隔的年份,注意公式的使用,而不是YEAR(C4)YEAR(B4).再判断当工龄大于等于5年时,给予奖励,否则没有。或者说如果工龄不小于5年时,给予奖励。钱永正的入职日期是20113-5,虽然2013减2011等于2,但是由于20133-5小于2013-1-1,所以结果就需要减去1,即钱永正的工龄满1年,不满2年。=IF(A
19、9=B$1,A9,”),在B1(即$B$1)中输入需要打印的箱数“5”,由于A9中的值“6”大于“5”,所以结果为”,而不是“6”, ”即不体现任何值。箱数输入“2,则体现2箱的打印内容。10.文字格式的转换11.信息的查找与匹配VLOOKUP,查找匹配函数,在数据首列搜索某个值,匹配首列往右“N”列所对应的值。关键词:首列.MATCH,在区域里A2:A9,找出所求值,从A2起算所在的序列号,。选定区域很重要,如区域设定为B1:I1,即从B1算起,B1作为第1列,香蕉则是在第3列。HLOOKUP,与VLOOKUP用法相同,区别在于搜索的值一个是在首行,一个是在首列.OFFSET,从某个单元格起
20、,向下N列,再向右N列的单元格的值.注意发现上表中,INDEX与OFFSET的区别。ADDRESS,生成指定单元格的位置,ADDRESS(指定的行号,指定的列号)INDIRECT通常与ADDRESS结合使用,注意INDIRECT(F3)与INDIRECT(“F3)的区别。注意:INDIRECT(ADDRESS(1,1))的结果,为INDIRECT(“A1”)。12.“INDEX在信息查询与提取中的应用及说明说明:通过MATCH函数可以计算出所求值所在的“行数”和“列数,表中“梨子”在第3列,而“价格”和“产地”分别在第2行和第3行,因此,通过INDEX查找,第2行第3列的值是“2。8”,第3行第3列的值是“潍坊。上表是根据仓库的温度和干湿表温差求库房内的相对湿度.通过INDEX函数直接计算出结果。通常情况下,查找信息,会用“筛选”来操作,比如在“分配地区”中筛选“上海”,可以得知有哪些箱号的货品分配给上海。但对于经常性的操作,“筛选”就不太方便,而通过函数公式设置好后,查询任一地区的信息就会自动生成,十分方便.图示说明: