物流管理 工作中的EXCEL应用1

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