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

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 初,中,高级或在不同的物流仓储管理领域,只希望大家能从中各取所需,通过一些案例打开更多的工作思路，在工作中不断的加以优化。56r2014 年7月24 日目录目录 .3第一部分EXCEL 基础及常见函数应用示例 .

5、501.了解 EXCEL表格 .502.运算及引用符号 .503.加减乘除的基本计算 .604.“的$”应用 .605.年，月，日的计算 .706.时，分，秒的计算 .707.数据统计中的应用（求各种值图例）.808.字段信息的提取与编辑 .1309.“ IF函数”的广范应用 .1510.文字格式的转换 .1811.信息的查找与匹配 .1812.“ INDEX”在信息查询与提取中的应用及说明.21第二部分十年工作中的应用实例 .错误 ! 未定义书签。01在 KPI 统计表中，求满足多种条件的值.错误 ! 未定义书签。02从相对规则的信息中提取所要的信息数据.错误 ! 未定义书签。03从工作量统

6、计表中提取每一个人的工作量.错误 ! 未定义书签。04工作量分值与绩效考核 .错误 ! 未定义书签。05考勤异常情况提醒 .错误 ! 未定义书签。06根据打卡记录生成考勤表.错误 ! 未定义书签。07根据考勤机打卡记录统计加班人数.错误 ! 未定义书签。08考勤统计表的系统性制作.错误 ! 未定义书签。09将已知信息录入到指定表格.错误 ! 未定义书签。10数据透视的应用 .错误 ! 未定义书签。11每周工作量的汇总 .错误 ! 未定义书签。12工作计划的 ”7要素 ”.错误 ! 未定义书签。13仓储费用计算 .错误 ! 未定义书签。14简易库存账的制作 .错误 ! 未定义书签。15第三方物流

7、台账 .错误 ! 未定义书签。16仓库可视化管理 .错误 ! 未定义书签。17如何做到库存货品的先进先出 .错误 ! 未定义书签。18如何判断货品是否按先进先出发货.错误 ! 未定义书签。19大宗货位的自动选择 .错误 ! 未定义书签。20服装线上与线下入库货品库位分配.错误 ! 未定义书签。21服装退货整理与箱单打印.错误 ! 未定义书签。22退货上架货品堆码指定托盘号 .错误 ! 未定义书签。23箱唛连续打印的 VBA 方法 .错误 ! 未定义书签。24自动化分拣思路之服装越库操作.错误 ! 未定义书签。25根据已拣货品量求剩余量.错误 ! 未定义书签。26货品完全匹配条件的分拣.错误 !

8、 未定义书签。27 RFID条件下的高效分拣思路 .错误 ! 未定义书签。28 RFID思路之整托货品越库分配的计算（精典案例）. 错误 ! 未定义书签。29发货包装（拼箱）自动计算. 错误 ! 未定义书签。30库内短驳加箱(零箱 )拼箱标签的制作. 错误 ! 未定义书签。31根据整箱规格检查播种或拣货过程的准确性. 错误 ! 未定义书签。32播种后如何提高点数装箱的效率与准确率? . 错误 ! 未定义书签。33播种单格式高效读报方式的转换. 错误 ! 未定义书签。34分批入库货品的配发. 错误 ! 未定义书签。35黄金包装需求量计算思路. 错误 ! 未定义书签。36不同配比条件下的黄金包发货

9、量计算. 错误 ! 未定义书签。37电商操作与物流发货思路. 错误 ! 未定义书签。38电商拣货流程优化. 错误 ! 未定义书签。39电商分区拣货再合单的操作思路. 错误 ! 未定义书签。40电商两地发货模式的单据审核. 错误 ! 未定义书签。41电商拣货路径优化与距离计算. 错误 ! 未定义书签。42亚马逊电商导出数据的整理. 错误 ! 未定义书签。43第三方物流（企业物流）发货方式及承运商自动选择思路. 错误 ! 未定义书签。44两家货运公司价格与时效对比参考. 错误 ! 未定义书签。45上海市内快递编号法分拣. 错误 ! 未定义书签。46上海同城快递“蜘蛛网”中转班车运行设计. 错误 !

10、 未定义书签。47全国办事处仓库分布图. 错误 ! 未定义书签。48上海地区店铺分布图. 错误 ! 未定义书签。49长三角地带物流中心选址分析. 错误 ! 未定义书签。50服装的标准化物流. 错误 ! 未定义书签。51不同规格的包装以满足不同需求的发货. 错误 ! 未定义书签。仓储现场管理心得.错误 ! 未定义书签。作者简介 :叶剑 ,部队优秀”四会教练员”,从快递员 ,仓管员 ,客服员 ,调度做起 ,是具有丰富的物流现场实战和管理经验的物流师,历任快递公司,第三方物流公司主管,目前就职于绫致时装任仓储部经理.在从事快递,第三方物流 ,企业仓库管理的近15 年中 ,凭借清晰的工作思路,在企业没

11、有WMS的情况下,借助EXCEL,提高效率和准确率 ,降低成本的革新案例达一百多项,为服务的企业节约物流操作成本达上千万元,成为许多院校,电商仓储企业和物流沙龙受欢迎的讲师和顾问.第一部分EXCEL 基础及常见函数应用示例在第一部分中， Excel 函数基础应用及说明都以精简截图的方式表达，希望让读者在入门的时候能够对 Excel 函数功能有更直观的了解。01. 了解 EXCEL表格2. 运算及引用符号3. 加减乘除的基本计算4. “$”的应用单元格的位置，由列坐标和行坐标组合确定，列坐标用英文字母表示，行坐标用阿拉伯数字表示，如第1列的表达式是“ A”，第 2 列的表达式是“ B”，以此类推

12、。第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。但如果在公式中的行号前面加“$

13、”，再往下拉时，仍是11，也就是说“ $”已经对 11 进行了锁定。要注意的是，将选中输入公式的单元格，如果公式中的单元格行号没有用$锁定，向下拖动时会依次增加，列号不变。如果列号没有用$锁定，则往右拖动时，列号依次增加，行号不变。5. 年，月，日的计算求某个日期的年份，月份和号，分别用：YEAR,MONTH,DAY.在 EXCEL任意单元格内输入此“公式”，回车后就会体现当天的日期/ 当时的时间。用结束日期减开始日期，即可得到它们之间相隔的天数。然求当中的工作日，则需要用函数NETWORKDAYS.如果要知道某一天是星期几？可以先通过 WEEKDAY函数求出所在周的第几天，然后再去匹配第几天

14、是星期几，在计算时需要注意中西方一周的第一天是不同的。6. 时，分，秒的计算求某个时间的时，分，秒，可以直接用函数：HOUR,MINUTE,SECOND.如果计算某项工作开始到结束，用了多少分钟，可以先用结束时间减开始时间，得到用了多少小时多少分钟，然后用小时数× 60分钟分即可。7. 数据统计中的应用（求各种值图例）尤其求最大公约数，可以确定最小需求单位，在物流中意义很大。直接输入 =PI()，就得到圆周率。MAX，求一组数值中的最大值；MIN ，求一组数值中的最小值。SUM，常用的求和函数，直接引用，即可求某个范围所有数值的和。COUNT 与 COUNTA都是计数，区别在于COU

15、NTA可以将文本格式的值进行计数，而COUNT 只对数值进行计数。AVERAGE与AVERAGEA都是求平均值，但AVERAGE只是针对数值，用数值的和除以数值的个数，而得到平均值。但AVERAGEA，则是用数值的和除以所有参与的（包含文本）的人数，而得到的平均值。INT，取整，不保留任何小数。ROUNDUP，向上舍入，可按需求保留小数位数，“ 0”即是不保留。MOD ，求被除数与除数的余数。此例在仓库管理中计算入库上架库位和货品盘点中应用较多。RANK 用于求某个值，在某个范围的大小序列。B2 单元格是王二小的成绩，在B2:B7（ 6个人的成绩）中排名第 5。注意计算排名与倒数排名公式的区别

16、，在后面加上“1”的作用。=SUMPRODUCT(A2:A13=”南大 ”)*(B2:B13= ”大一 ”)*(D2:D13)解释：求符合多种条件的和，即当学校为“南大”时(A2:A13=”南大 ”)，班级为“大一”时有多少人参加（计算范围D2:D13），“ * ”为连接多种满足条件的符号。(B2:B13=”大一 ”)，注意在使用 SUMPRODUCT时，“，”与“ * ”的区别，如上 SUMPRODUCT(B2:B9,C2:C9)，其结果就是从 B2*C2 一直加到 B9*C9。COUNTIF，求在什么范围，满足“什么”条件的数值有几个，计数所用。SUBTOTAL函数，具有多种函数的功能，在

17、计算范围前加“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）解释：上表公式，是计算某个产品本次的发货日期减去上一次的发货日期，得到间隔的发货时

18、间。其中嵌用了：INDEX,LARGE,IF,ROW.在 D4 单元格输入上面公式，然后下拉即可， 是同时按： ctrl, shift, enter生成。8. 字段信息的提取与编辑注意： &与 CONCATENATE的区别。“ & ”的输入，是按住shift 键同时按上行数字键“7”即可。注意： LEN与 LENB的区别，及字符与字符串的区别，一个文字是一个字符串，一字符串可能是一个字符，也可能是两个字符，一个数字或字母只有一个字符，但一个汉字却有两个字符。小窍门求字段中有几个汉字 ：如果在上表中，用计算出来的字符数减去计算出来的字符串数，就可以知道字段中有几个汉字了。LEFT

19、(A5,3),是求 A5 单元格中左侧3 位的字段。 A5 单元格内是“江苏省泰州市”，左侧 3 位是“江苏省” 。MID 是从字段中间提出信息的函数，一个人的出生年份，在身份证号码中，是从第7 位，向后 4 位。上表中 =RIGHT（A4，1），A4 中的值是“中华人民共和国”，“中华人民共和国”的右侧第1 位，是“国” 。FIND，查找某个字符串在某个字段中的第几位。REPT，将需要重复的字段，重复“N”次。REPLACE，将字段中第几位指定往后几位的字段，替换成相应的内容。SUBSTITUTE，将字段中，符合替换的字段，替换成相应的内容。9. “IF”函数的广范应用IF，如果满足条件，即

20、为结果“A”，否则就为结果“B”。=IF(B3>=60,”是 ”,”) 如果王二小的成绩B3 大于等于60，即显示“是” ，否则什么也不显示。=IF(B11<60,”不及格 ”,”)如果王二小的成绩B11 小于 60，即显示“不及格” ，否则什么也不显示。“”表示不满足条件时，不显示任何信息。AND,如果三门课程的成绩同时达到90,即 ”是 ”全优生 ,否则不是 ,即为 ”否 ”.OR,有任一门课程达到90 分 ,即为满足条件。上表以“王二小”为例，成绩为 76，使用“ IF”判断，由于不满 90，所以不是“优” ，由于不满 80，所以也不是“良” ，接下为大于 60，所以评定为“

21、及格” 。而用“ 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. 信息的查