物流管理工作中的EXCEL应用

1、思路与EXCEL物流十多年工作中解决各种问题的实例叶剑仓储作业高效思路EXCEL培训专用教材前言物流工作十多年来，从快递工作到第三方物流和企业物流管理,尤其是在仓库作业中，我 有很多提高效率和准确率的革新，而这些革新中EXCEL给了我很大的帮助，让我十分受益。 因为EXCEL让我受益匪浅，所以，我很乐意跟大家分享EXCEL那些对我们工作有帮助的功能， 尤其是从事物流工作或以后可能会从事物流工作的人，应当需要熟练运用EXCEL的一些功能。 EXCEL是一种办公软件，对我来讲，它是很好的工具，它不能代替其他软件和工具，但其他软 件和工具也不能代替EXCEL。各有所长，各有所短。没有好与坏，只有适合

2、之说。能解决实际 问题的就是好工具！EXCEL的特点，就是灵活性比较高，受限止少，是大家常用的办公软件,EXCEL可以通过函 数和VBA制作许多的小程序，进行系统性的操作。EXCEL不仅可以进行各种运算制作各种图 表，还能绘制许多图形。可以用它分析数据，还能以很多形式去体现数据，功能十分强大！EXCEL的缺点就是存储数据有限，数据量大计算过程较多时，反应速度会比较慢。所以， 我们用EXCEL，就要扬长避短。我认为，作为一个物流工作人员，尤其是从事仓库管理工作，如果不懂点EXCEL操作， 可能会过度依赖现有的系统软件等条件,不能灵活机动，与时俱进,导致许多作业的浪费。工作 中，我们需要用到EXC

3、EL对数据进行统计，分析，计算，这样可以开拓很多思路，不会用EXCEL， 就意味着很多思路没有打开，自然就会做得不够好。当然，即便是EXCEL用得很好的人，没 有物流实战经验和管理经验，也不一定就能把仓库管理得好，故实践方能出真知。作为仓库管 理人员，如果仓库没有自动分拣和电子拣货系统，但你要有自动分拣和电子拣货系统的思路；没 动自动化的立体仓库但你要有自动化立体仓库的思路做到闭着眼睛也能配出货品；没有RFID 射频技术，但你要有RFID高效扫描的思路；没有飞机大炮，就把小米加步枪的潜力挖掘起来充分 利用.日积月累，非常有幸的是能借助物流沙龙这个平台，将经验和大家分享，为了能让大家有更 多的认

4、识，我将十多年工作中用EXCEL解决的种种问题和如何提高操作效率的应用表格汇编于 本册，第一部分为常用函数应用示例（截图）,EXCEL函数不熟悉的人员需要先学习第一部分后再 看第二部分案例。由于我一开始EXCEL尚不精通，故资料中定有一些表格会有烦琐之处。如 有不值一看的表,还请一带而过。无论读者是EXCEL初，中，高级或在不同的物流仓储管理领域， 只希望大家能从中各取所需，通过一些案例打开更多的工作思路，在工作中不断的加以优化。56r2014年7月24日目录 TOC o 1-5 h z HYPERLINK l bookmark13 o Current Document 目录3 HYPERLI

5、NK l bookmark60 o Current Document 第一部分EXCEL基础及常见函数应用示例401.了解 EXCEL 表格5 HYPERLINK l bookmark63 o Current Document 02.运算及引用符号5 HYPERLINK l bookmark66 o Current Document 03.加减乘除的基本计算604.“$”的应用6 HYPERLINK l bookmark69 o Current Document 05.年，月，日的计算7 HYPERLINK l bookmark84 o Current Document 06.时，分，秒的计算

6、7 HYPERLINK l bookmark90 o Current Document 07.数据统计中的应用（求各种值一图例）8 HYPERLINK l bookmark147 o Current Document 08.字段信息的提取与编辑13 HYPERLINK l bookmark162 o Current Document 09“IF”函数的广范应用15 HYPERLINK l bookmark198 o Current Document 文字格式的转换18 HYPERLINK l bookmark202 o Current Document 信息的查找与匹配19 HYPERLINK

7、 l bookmark227 o Current Document 12“INDEX”在信息查询与提取中的应用及说明21第二部分十年工作中的应用实例.错误!未定义书签。01 在KPI统计表中，求满足多种条件的值错误!未定义书签。02 .从相对规则的信息中提取所要的信息数据错误!未定义书签。03 .从工作量统计表中提取每一个人的工作量错误!未定义书签。04 .工作量分值与绩效考核.错误!未定义书签。05 .考勤异常情况提醒.错误!未定义书签。06 .根据打卡记录生成考勤表错误!未定义书签。07 .根据考勤机打卡记录统计加班人数错误!未定义书签。08 .考勤统计表的系统性制作错误!未定义书签。09

8、 .将已知信息录入到指定表格错误!未定义书签。10.数据透视的应用错误!未定义书签。.每周工作量的汇总错误!未定义书签。.工作计划的”7要素”.错误!未定义书签。.仓储费用计算错误!未定义书签。.简易库存账的制作错误!未定义书签。.第三方物流台账错误!未定义书签。.仓库可视化管理错误!未定义书签。.如何做到库存货品的先进先出错误!未定义书签。.如何判断货品是否按先进先出发货错误!未定义书签。.大宗货位的自动选择错误!未定义书签。20.服装线上与线下入库货品库位分配错误!未定义书签。.服装退货整理与箱单打印错误!未定义书签。.退货上架货品堆码指定托盘号错误!未定义书签。.箱唛连续打印的VBA方法

9、错误!未定义书签。.自动化分拣思路之服装越库操作错误!未定义书签。.根据已拣货品量求剩余量错误!未定义书签。.货品完全匹配条件的分拣错误!未定义书签。. RFID条件下的高效分拣思路错误!未定义书签。. RFID思路之整托货品越库分配的计算（精典案例）错误!未定义书签。.发货包装（拼箱）自动计算错误!未定义书签。.库内短驳加箱（零箱）拼箱标签的制作错误!未定义书签。.根据整箱规格检查播种或拣货过程的准确性错误!未定义书签。.播种后如何提高点数装箱的效率与准确率?错误!未定义书签。.播种单格式高效读报方式的转换错误!未定义书签。.分批入库货品的配发错误!未定义书签。.黄金包装需求量计算思路错误!

10、未定义书签。.不同配比条件下的黄金包发货量计算错误!未定义书签。.电商操作与物流发货思路错误!未定义书签。.电商拣货流程优化错误!未定义书签。.电商分区拣货再合单的操作思路错误!未定义书签。40.电商两地发货模式的单据审核错误!未定义书签。.电商拣货路径优化与距离计算错误!未定义书签。.亚马逊电商导出数据的整理错误!未定义书签。.第三方物流（企业物流）发货方式及承运商自动选择思路.错误!未定义书签。.两家货运公司价格与时效对比参考错误!未定义书签。.上海市内快递编号法分拣错误!未定义书签。.上海同城快递“蜘蛛网中转班车运行设计错误!未定义书签。.全国办事处仓库分布图.错误!未定义书签。.上海地

11、区店铺分布图错误!未定义书签。.长三角地带物流中心选址分析错误!未定义书签。50.服装的标准化物流错误!未定义书签。51 .不同规格的包装以满足不同需求的发货错误!未定义书签。仓储现场管理心得错误!未定义书签。作者简介：叶剑，部队优秀”四会教练员”，从快递员，仓管员,客服员，调度做起是具有丰富的物流现场实战和管理经 验的物流师,历任快递公司，第三方物流公司主管,目前就职于绫致时装任仓储部经理.在从事快递，第三方物流, 企业仓库管理的近15年中，凭借清晰的工作思路，在企业没有WMS的情况下，借助EXCEL,提高效率和准确率, 降低成本的革新案例达一百多项，为服务的企业节约物流操作成本达上千万元，

12、成为许多院校,电商仓储企业 和物流沙龙受欢迎的讲师和顾问.第一部分EXCEL基础及常见函数应用示例在第一部分中，Excel函数基础应用及说明都以精简截图的方式表达，希望让读者在入门的时候能够对 Excel函数功能有更直观的了解。01.了解 EXCELS02,运算及引用符号算术运算符比较走算符名称算木运算符功能名秣比较送算符功能加号+加等号等于减号减大于号大于斜杠/除小干号=大于等于小干等于号=小于等于不等于号不等于引用运算符名称引用送算符功能冒号-引用单元格已域逗号合并多个单元格区域空格取两个单元格区域的交集小括号()括号内为函数和公式的计算范围大括号数组公式时引用各注m ct rl+shif

13、t+ent er= ( )03.加减乘除的基本计算ACDEFGh i姓名语堂数学英语总分总址汁食表达式1总分计贫表达式w张朵朵T88075233=E2+CZ+U2=SUM (E2:D2)王亚尼 叶凡958877906979Z4L25T=B3+C3+IJ3二丽+匚4+胞=SUM (E3:D3)二SUM (E4:D4)说明:如果想在某7本月收入200支出135支出220支出340藕余 105有余汁食表达式1=k7-BT-CT-D7强余计真表达式w=AT-SUM CB7：D7D个单元格j体现我 们需要菌结果,及 需要求的值直接 在这个单元格内输81000200150300350=AS-B&-CS-D

14、S=AS-SUM CBS:DS9人相应的公式即与10单价 （元/EG?忌重量（KG）忘价格怠伊格计算表达式。比如说，求张荣 矣的总研，直接在112.56. S22=A11*E11晚这个单元格内输123.6518=A12*B12A : =B+C2tD2,或13者用函数14总数量莓箱教量箱戮箱数表达式SUM CB2:D2A15/B151&200625=A16/B16IT18如果计食性质相同，可以在第一个单兀格由输入公式后，选中下拉即可-04.“$”的应用ABCDE1，固定符的说明2单价成/附2.534采购批次亟星（EG）偷格计算表达式5第一次20|50=B$2+B5在沃内

15、输入公式后，将沔选中往 下拉，如果没有在口前面加上 R莎能R.il下巧6第二次3075=E$2*E67第三决2050=B$2+B78第四次40LOD=B$2+BS91011甲甲=M112巳乙被有加$的下拉结果13丙丙=A1314丁T15甲小11IS甲=U1117甲=AS11加$的下拉结果18甲=AS1115甲=A$H单元格的位置，由列坐标和行坐标组合确定，列坐标用英文字母表示，行坐标用阿拉伯数字表示，如第1 列的表达式是“A”，第2列的表达式是“B”，以此类推。第1行的表达式是“1”，第2行的表达式是“2”，以此 类推。第2列的第2行位置就是单元格B2,第1列的第11行位置就是单元格A11，在

16、我们用函数输入公式 时，A1就是表示A1这个单元格的值,A11就是表示A11这个单元格的值，如果在某个单元格内输入“=A11”， 那么A11单元格里面是什么，这个单元格将体现什么。如上表中，A11单元格内是一个“甲字，在B11中 输入了= A11，结果也就显示了“甲”字。在选中B11这个单元格往下拉时，相应的单元格B12,B13,B14,分别就 有了结果A12,A13,A14。但如果在公式中的行号前面加“$”，再往下拉时，仍是11,也就是说“$”已经对11 进行了锁定。要注意的是，将选中输入公式的单元格，如果公式中的单元格行号没有网锁定，向下拖动时 会依次增加，列号不变。如果列号没有用$锁定，

17、则往右拖动时，列号依次增加，行号不变。05.年，月，日的计算A甘D甘G日期年月日年计苴表达式月计算表达式日计垃表诂式对W年11月w日河12112=N0HTli(A2)=DAY 姐 J2012-11-211二 TEAR 如5CIHTH (A3)=DAY 如)12-11-211二TEARCA*觇HTH(A4)=DAY CA4二 TEAEW0=HOHTJ (A4)2012-11-2二DATE (BE, C2, D2J求某个日期的年份，月份和号，分别用:YEAR,MONTH,DAY.AB1求当天日期求现在时间2=TODAY 0=m()在EXCEL任意单元格内输入此“公式”，回车后就会体现当天的日期/当

18、时的时间。ABC1开始上班日期放假日期求上班天数求工作日天敏（不计罔六日）22012-6-32012-11-1716T1203=B2-A2=NETWOEKDAYS(A2JB2)用结束日期减开始日期，即可得到它们之间相隔的天数。然求当中的工作日，则需要用函数NETWORKDAYS.ABCDEFGHIJK日期屋期 t第几天】中国将星期一 作为第一天星期中国西方LL月即日2WEEEDAY 姐2)=VUJ0KUP (J2,G：H_，ZD)1L1BU月以日3WEEKDAY 2)=VL00KUP22U月象日4WEEKDAY CA七 2)四=VL00KUP33U月踣日5=WEEKDAY 胰,2五=VL00K

19、UF 族,G:H_.ZD)4s4U月雹日7=WEEKDAY g 2)日=VL00KUF 晒,G:H,幻 D)5五5函6六6五0日期屋期 t第凡天】西方将星期日 作为第一天星期7B7六91120 日3=?YEEKBAT W, 1)=WL00KUF 独,J: )10U月功日4=WEEKBAT (AS, 1)=WL00KUF 但I。J：K 2 0)11U月双日5=WEEKBAT 化勺 1)=WL00KUF (EllJ：K 2 0)12LL月23日&=WEEKDAy CA10, 1)五nVLQDKUPUl% J：K13LL月四日1WEEKE蛆 CAIL 1)日nVLQDKUPUlM J：K如果要知道某

20、一天是星期几？可以先通过WEEKDAY函数求出所在周的第几天,然后再去匹配第几天是星期 几，在计算时需要注意中西方一周的第一天是不同的。06.时，分，秒的计算ABcDEFG1时间时分秒时计竟表达式分计贫表达式秒计算表达式222:13:232213Z3=JiDUR(A)二MINUTE CAS)=SECONJ 02)3Z:18:342IB34二血UK (A3)顼INUTE CAS)second g求某个时间的时，分，秒，可以直接用函数:HOUR,MINUTE,SECOND.ABCDE1开始时间结束时间所用时间所用时间分钟）备注：计算所用分214:3013:001:3090钟时，单元格格式3=B2-

21、A2*S0+MNUTECC2)设置为“常规.如果计算某项工作开始到结束，用了多少分钟，可以先用结束时间减开始时间，得到用了多少小时多少分 钟，然后用小时数x60 +分钟分即可。07.数据统计中的应用（求各种值一图例）ABCDE求公约薮与公信数2表达式3敷值求敷值中的最大公约裁4=GCD(A4：A6J424求数值中的最小公倍裁144=LCM(A4;A6)516636尤其求最大公约数，可以确定最小需求单位，在物流中意义很大。ABcABc1数值绝对值绝对值计算表达式2-2.3Z. 337878二邮境4-8866二59S99=ABS(A!51一求回周率一表达式23- 141592654=PI()直接输

22、入二PI（）,就得到圆周AECD求正鱼教=如门钱f8792BT薮学成螭让到即会邕有几名3=COU11TIFCC2:C8,=如门张正二68B768求成绒中达到即会的有多小9=COUNTIF CB2:DS, %=如门许四海的688T求成簸中“8T研”闵有冬少5COUNTIF CB2:D8, W)李五洲95gggz影永正88g?97求姓霰的学生有几个2二匚DUNTIFCA3：妁，“钺虹)钱永正88879TCOUNTIF，求在什么范围，满足“什么”条件的数值有几个，计数所用。AEC表达式1表达式芝AEC表达式1表达式芝121 AVERAGE平均值6.GBeBBBT=5UE TOTAL 邮:衅U)=AV

23、ERAGE12wsurjT计算包含数 字的单元格数&=SUE TOTAL 已衅H)=comrr如3:瞄-33CQUNTAi-+算非空白 的单元格数8=SWTCiTAL ,衅3;衅IL)=CCIUB7ACA$3；U11)4M.AX计篁最大值14=SWTQUL(% U3；U11)=MWi 妣3；临11)L45MIN计篁最小值-4=5UTOTAL A$3；U11)家6 PRODUCT if 参数的乘料Z1TT28=SWTDTAL(6, U3：U1L)=FRODVCT OS3：MU)ASC7 STDEV估苴基于给 定样本的标准偏差8.041558T=SUTTOTAL (7, U3：U1L)=SrDEV

24、(U3： UU)-43 STDEVP估算基于给 定样本总体的标准偏差T.340905Z招WTCITAL 侣，A$3：U1L)=SrDEYF CA$3：U11)9 SUM求和40=SWTDTAL，AS3：UU)=SUM (A$3：A$11)SUBTOTAL的妙用SUBTOTAL函数，具有多种函数的功能，在计算范围前加“1,2,3,4,5,6,7,8,9”结果各不一样。A ECDEFG求乎方和与平方根表诂式1表达式艺求数茹i的平方和25=3伽归加l;A5)=A4*A4+A5*A53求数茹z的平方和50=SW5Q(AB;A10=Afi* AS+阳* 炯+Al Q*A.1Q4数蛆2求D3单元格的平方根

25、5=SQRT (D3)=SQRT CSUMSQ (A4:A5)=S9RT t25)341。sSUMSQ,所有数值平方的和；SQRT，求某个数值的平方根。AECDEFG库位货品名输数量也可以=SUHIF CASCIO, AS, C2：CL0)表达式12AjOIOI 1牛仔舞2求A0W1库位上有多少数是的衣服48=SUNIFW：M0, “AOWr CADCi)3AjOIOI短麟1C4AJ0101撕4裤16求仓库内裤类服装有多少50WUMIF CB2: E10,咛裤* C2:C10)5AJ0101西装206AJ0201衬衫1E求食库内牛仔类服装有多小25=SUMIF CB2: BIO, 牛仔:+,

26、C2: CIO)7AJ0201西装薜228AJ0201袜子15求包库内有几种牛仔类服装2=C0UHTIT (E0:E1D, %:牛仔* 门9AJ0201牛仔裙2310AJ0301ZTV-TLj56求AC!叫库位上有多少种衣服4=C0UBTI?妃 Al CL AOlOr)111213年堪班蛆人数表达式114一年卸T班)33求一年颌有多少学生95=SUIUF(A14;A2%一FjCL4;I：EE)15一年斐二（班）3216一年魏三（班）3C17二年勰一班）41也可以Cl 1LJTE f A 1 /I ATI A -| /I pi A18二年级二班）4019二年皱三由）40bUMlrAH .Az1斗

27、L J.斗.LZZJ20一班）4021三年必二班）4122三年级三三）40SUMIF是求满足条件的和，如求一年级有多少学生，及在A14:A22年级范围内有多少是等于A14”一年级 的，如果是“一年级”，那么后面的“人数”相加即得到所求的值。AECDEFG1表达式计登区域（*3：CT）求计算区域内数值的个数8=C0VNT (A$3:CJT)中国23仅计耸数字格式1求计萱区域内录入苴字的单元格个数11=C0VNTA(M3:C$T)家跄江苏88包括数宇和文字，以及“空格”3果求计篁区域内未录入数值的单元格个数4=COVBTBLWK(A$3;CfT)2301未录入任何宣字及空格健8求计篁区域内数值是“

28、跆”的个数=匚口伽邛队$3：匚$九华扩）9仅计算单元格内是1011求计耸区域内有几列3=C0LUNHS(A3：CT)12求计算区域内有几行5=RJ0WSCA3:C7)131415求计耸区域内出现最多的数值23=M0BECA3:C7)=COUNTIF(A$3:C$7f 2计算范围if甘足的条件语言表达：求虹;:CT范围凯 有几个单元格内的值是“路”注意：COUNT，COUNTA，COUNTIF 的区别。所在的单元格是在第几行所在的单元格是在第几列ABCDE_ IG求单元格所在列和行以及印W与SLUM哺其它应隹十=QLUW()=C0LWCE3)=EDW ()=m (er)2=COL1JMNO=C0

29、LUMN(B7)99二 EOT 0=m(B9D104t1314151617181920212Z1二BOW (AL)1=EOW (BL)1=COLUniNCAl)1=C0LUNNCA3)1=RCIW1: 1)1=cqlca；a)JE2=ROW 32)神Ld毛=x2=EOW (B2)2=COLUNNCB1)2=C0LUNNCB4)2二ROW（2：2）2=COLUHNCB:B)AECDEFGHIJ库位AO101AJ01CI1AOlCilAOLOlAO201AJ0201M3E01AEI201jkoaoi2品名称牛仔褂短辉西装衬衫西装裤袜子牛仔裙夹克3数量10162015Z2152358456求Amci

30、i库位有几 种衣服4=C0UNTIF Cl: 1; AOLOrj7求AJ01D1库位有多 少件衣服4S=SVMIF (1:1, A010L3:3)COUNTIF与SUMIF的区别，一是计数，一是求和AE甘甘EE被除教除数求佰计算表达式ID33=LNT您/昭）职整数4HETIJTG叫性巳1)向上舍入到的倍数3.4HEILDTG (A2/E2, 0. 1)向上舍入到. 1的倍数3.G=CEIEnTG (A2/E2. 0.3)向上舍入到口.3的倍数G=CEILEffG (A2/B2, 3)向上舍入到3的倍数5=CEILLffG (A2/E2, 5)向上舍入到日的倍数3. 34=CEILLffG (A

31、Z/B2, 0.01)向上舍入奎m.m到倍数3=FLOOR CA2/B2, 1)向下舍入到1的倍数103. 33二FL0CIKCA2/斑，口. 01D向下舍入童D.（H的借数113=FLOORCA2/E2,3)向下舍入到3的倍数120二 FL0DRCA2F 跛，5)向下舍入到5的倍数133. 34=R0UBDUT g昭 2 )向上舍入，小数保留Z位li3.334=R0UEDU?(A2/E2, 3)向上舍入，小数保留3位154=R0UMDUT(A2/E2, 0)向上舍入，小数保留口位1G3.3=R0Uim1)四舍五入，小数保留1位173. 33=R0UHDD 叶向下舍入，小数保留2位183.33

32、3ROUITODOWWBS)向下舍入，小数保留3位193=rRUBC g计睥巳0)不保留小数203.3=rRUBC CU2/BS2, 1)保留1位小数213.3333=TRUBC CU2/BS2, 4)保部位小数Z23.3333333=TRUBC CU2/BS2, 7)保留T位小数AE第几大第几小数值返回数值中倒数第之小的数宇326E32返回数值中第3丈的数字G679655861068MIN （计算范围）=SMALL （计算范围,1）MAX （计算范围）=LARGE （计算范围,1）A甘CDEF1同批货品相联发货的间隔天数23物品代玛发货体租俯）发货日期间隔天数4A00015. 5涓L日05A

33、00021. 8狷7日0SA00013. 62月8日77AOC021. 52月阳2BA00012. 22 月 10329A000132 月 173710=$B$L “是“，5；=TF 饵4=M建是。*二张正三67是=TF“是“，I= TF(B5XM建是 W许四海55=1F CBBX$E$1/是。I= IF(BBX6S 是 W李五洲S3是=IF CB7MBJ1,心= IF(BT=E“是。5：彩永正33=IF CBSMBSl. 是“，s：=IF (B 8X6口建是“，5：10姓名成蛰冀断不及格判断不及M表这式1凳断不及路表达式211王二小76二IFCBILCI斑 1,不岐檎严)二不及格12嫌一波6

34、0二IF(B也&L 不及格13张正三67二工FCB13CJ斑1,“外戒格，门二IF(B13&L 不及格14许四海55不及格二邛CB14J斑1,“小诚韬二二邛(514&口，“不及格15李五洲83二邛卬15$斑1,“外发格,门二邛康15=60,”是”)如果王二小的成绩B3大于等于60，即显示“是”，否则什么也不显示。= IF(B11=90,也=?口，D2=90X 是二“否门3钱一波87928T否IP (MO (B3H即，C3K90. D3=90X 是L 否门4张止二688768否IP (MO (B4H即，匚4=如，D4=90X 是二否门5许四海99688T否IF (MO 5=90,匚5K90, D

35、5=90X 是二否门6李五洲959992IP (AUT 6=90,匚尤即,D6=90X 是二否门7彩永正888T9T否IT(MD 7)=90,匚7=3口，D7=90X 是二舌勺AND，如果三门课程的成绩同时达到90,即”是”全优生，否则不是，即为否”.AECEF1蛀名语交数学英语有设有某一科 达到如分判断计算表达式2王二小76B095有=LF (0R(B2=g0; C2=90;即),奇/门3跋一波B792BT有=LFDR(E3=gCi, C3=90?明=即)，有，门4张正二S8BTB8血伽颁,皿 =如，D4X印)，“有)5许四海99688T有=IJ (0(55=90, 05=90,肪=印)，有

36、D6李五洲95999Z有二LFDRCEEXmO, 06=90,昵=即)，“有7彩永正888T9T有=ITCiR(ET=mCi, 07=90, DT=90), “有了)OR,有任一门课程达到90分，即为满足条件。AECDEFGzb姓名成墙评定评定计算表达式11评定标准I口 | 土为良 如1优1王二小75及格=LOOKUPCB2,日)钱一波90优=LOOKUPCB3, $G53：SH$6)张正三67装格=LOOKUP CM, $G$3：SH$6)许四海55差=LOOKUPCB5, $G$3：SH$6)率五洲33良=LOOKUPCB6, $G$3：SH$6)彩永正33差=LOOKUPCB7, $G$

37、3：$H$6)评定计算表达式W9茨格=IT (02=90, “优 IF (班X8Q “艮L“及格L “差*)10优=IF (B3=90, “优L IF (E3X8CV 艮L IFW3X6山“及格L “差门)11茂格=17“优L IF艮L“及格L “差“)12差=17 缶5X9L 优，IF CB5X8CV 良七 1F(BSX胞及格L 差13良二IF(56=90,优，IF(26=80,良 J IF (说二胞及格L 差L)差二IF (57=90,“优L IF (27=80,良L (57=60,及格不差门)上表以“王二小”为例，成绩为76,使用“IF”判断，由于不满90,所以不是“优”，由于不满80,

38、所以也不是“良”, 接下为大于60，所以评定为“及格”。而用“LOOKUP”函数，76位于60-80之间，匹配结果为“及格”。AcDYF1如果工作满5年,给予奖励2姓名入职日期考核日期考核时的工龄求工薛的表达式1王二小2007-1-22013-1-15=YEAR(C4-B4)-1900哦一波010-1-192013-1-12=rEAR(C5-B5)-1900张正三2008-5-32013-1-14=rEAR(C6-B6)-1900许四海200-2-92013-1-13=HiR(C7-B7)-1900李五洲|：107-7-152013-1-15=YEAR(C8-B8)-1900彩永正2008-2

39、-g2013-1-14=YEAR(C9-Bg)-190010钱永正2011-3-52013-1-11=rEAR(CIO-Bl 0)-19001112是否奖厨表达式1表达式213奖=1F (D4=S建奖七J=IF (HOT 04旬噗七“门14=IF (D5X5,“奖二=IF (HOT (D5=5, “奖，5=IF (HOT 06=5,“奖“，“J=IF QIOT (Dg,噗七“门19=IF (DWX5, 奖二 A=IF (H0T(D105X 芟,kJ先判断两个日期之间相隔的年份，注意公式的使用，而不是YEAR(C4)-YEAR(B4)。再判断当工龄大于等于5年时，给予奖励，否则没有。或者说如果工

40、龄不小于5年时，给予奖励。1 4 =IF (WT (C4DATE(VEA1 04), M0HHCB4), DAY BO)1 4 =IF (WT (C4DATE(VEA1 04), M0HHCB4), DAY BO)YEAZ DTEAUg TE崩O-YEAKBl)-D15 =IF (WOT (C5-CDATE (YEA1 (C5), MOHTHtBS), DAY YEAH (C5J-TEAE(B5 TE此g)-YTAk阿)T)1G =IF (WT (CB-CDATE (YEAH (C&)7 MOHTHCBB), DAY ). YEAH (C6 )-TEAE G6D; TEARCETEAR CEB

41、IT=IF QTOT (C7DATE (YEAH (CT)7 MONTHCB7); DAY tB7 YEAJ (CT)-EAEG7J; HJkRCC7)-YEAKCB7)-l J=IF (WT (Ca-CDATE (YEAH (CS), MOHTHCBB), DAY tB8 YEAH (C8 J-ZEAEQSX nARCC8)-YEAKCBB)-lD=IF QTOT (C9DATE (YEA1 (C9), MONTH CEQ), DAY YEAZ (C9 J-TEAE G9X TEAR勇TIARGBET=IF (HOT (C10DkTE CCEJCCIO), MOUTH (BIO), DAI

42、), YEAL (ClOj-YEARtBlO), YEAR (C10)-rEAE(B10)-l JABCDrTT甘1如果工作漏5年,给予奖励3姓名入职日期考核日期考裱时的工靛4王二小2007-1-22013-1-155跋一波2010-1-192013-1-126张正三200S-5-32013-1-147许四海2009-2-92013-1-13XS李五洲200T-7-152013-1-159彩永正Z008-2-92013-1-1410钱永正2011-3-52013-1-1111、求工靛的表达式 恨作为思路多考钱永正的入职日期是2011-3-5，虽然2013减2011等于2,但是由于2013-3-

43、5小于2013-1-1，所以结果 就需要减去1，即钱永正的工龄满1年，不满2年。A箱教：BCD EFG52棍据箱数自动跳出需要打印的箱号3虚拟箱号打印箱号表也式1打印箱号表达式2公式可以直接下拉，且不需要虚拟箱号表达式3逐一输入较烦琐411=IF 辟m)1=1E （m。；】）=$辟L BOW （1； 1）严）=邛。夺E$L L心）5EZ=IF 辟A5=$B$L$A5, m)Z=邛（m 版;况脚虬ew但；V, 5）=邛也夺E$L %心）633=IF 球A6C=$E$L$A6, )3=IF 知卅中：；）：=$邸L ROW （3:3）, 5）=以沁睥,3,744=IF CMTC=$E心，$赋_ V4

44、EF 010(4:4X51, RXL 5)=IF4C=E$1, 4, “门855=IF CU8d$E$l,$A8, s)5=IF CH0W(5：5)=SBftE0W 伯，=1?(5=$1, 5, 5)?6=IF CUgd$E$l,$N,二IF OW(6:6)=$BftEOW 侣:盯“门10T=IF tMlCK=$E$l,$Al(, eJ=IF OW(7：7)=$EftEOW (7:7), “)118=IF I3A1L =$1,111, J=IF 0(8：8)=$51, (8:8)129=IF 性F=IF CJOE 臼：WO$E$1_.EOW9.9), s)1310=IF Ml30$掰 1,$A

45、l3, 5)=IF IM (10:10)=551? ROW (10:10),li11=IF CjA14=SB$l, tkll f)=IF 澜N Q 1 ; 11)夺陆$1函口。1 ; L1)D1512=TF=IF W (12-.12) =做L m (12:12),)备注；RO1（1! 10 = 1 .0（10:10） = 10 用ROVT（1:1）代替 1,是因为在下校复 制时，可以随之增加口同样C0LUWU：A）代替1,也是往右拉复制时，可以随之增加。= IF（$A9=$B$1,$A9,）在 B1（即$日$1冲输入需要打印的箱数“5”，由于A9中的值“6”大于“5”，所以结果为” 而不是“6

46、”，”即不体现任何值。文字格式的转换1AECDE人民币与美元格式23价格人民币格式表达式美元格式表达式41008L 008. 00=IOT 如)*1,008-00二：DOLLAR g539993, 999. 00=RJIB (妣)=D0LLAE(A5)AEC1转换大小与字斑2I love jack And jones34zl love jach azid j oites=L0如R伯2)全部小与56I LOVE JACI AND JONES=UPPER(A?)全部大写78I Love Jack And. Joilbe=PK0PER(A2)首字母大写ABC1转挨成指定的格式241Z86 41266

47、.00元二TEXT (A2, U.即元勺 4L288二TEXT (A2,Cl)J41,即&元/每小时二TEXT (A2, *礼元/每小时,41. 2昉元/年小时二TEXT *见口元小门每小时bWIH3 年 01145二TEXT(A2, yyy?年mm月 dd日“)2013-1-14二TEXT (A2, yyyjm-dd)13-1-14二TEXT(A2,13-01-14二TEXT (A2, bmin-dd)信息的查找与匹配AEC5甘1TY7FT姓名性别年靛语文数学英语卷入查询姓名:王二小王二小上海男1S768Q我一莅江苏玄19079287上海=VLOOKUP, SA:幻 0)张正二浙江男20B8

48、ST明性别男=VLOOKUP, SA: $G, M 0)许四海安徵男1B99B887年箫1S=VLOOEUP (SJSl, SA： $G, 4, 0)李五会福建女19959992语文成会76=VLOOKUP$A: $G, 5, 0)彩永正北京男20889TS7教学成鼓60=VLBOKUF 性J$1, $A:邮,&, 0)钱永正男17888TS7英语成鼓95=VLBOKUF(XT$1, $A: $G, T, 0)10犒入查询姓名：张正三1112浙江VLOOKUPJflO,幻 Ci)13性别男VLBOKUPMlO, $妃$鸟 3,0)年龄20=VLO0KUP(SJ$10, U：SG? 4,0)15

49、语文成莹68=VIB0KUP(J$10,16教学成最87=VLO0EUF(SJ$10, $A：$G, 6,0)IT英语成登6S=VLB0KUF(3J$10, $妃$0 L0)VLOOKUP，查找匹配函数，在数据首列搜索某个值，匹配首列往右“N冽所对应的值。 关键词：首列。ABCDE1盍品2苹果需要查技的货品=香莓3梨子4香蕉查我赁品所在序列3=MATCH(D幻妃叫 0)5芦籍e桔子查找西瓜所在序列8=MATCH(“ 西瓜 A2;A9, 口)7葡萄8橙子9西瓜MATCH，在区域里A2:A9,找出所求值，从A2起算所在的序列号,。AECDEFGHI1贵品苹果梨子芦柑桔子葡萄橙子西瓜13需要查找的孟

50、品：香斐45查找贵品所在序列367查技西瓜所在序列8二 MATCH(西瓜 LB1：I1,CI)选定区域很重要，如区域设定为B1:I1，即从B1算起，B1作为第1列，香蕉则是在第3列。AEcDET甘TJ 1黄品苹果梨子香蕉芦柑桔子橙子西瓜偷格3.5巳82.53. &2. 2641.5产地:JS台浅坊海口江西湖南上诲成都江苏查询货品梨子6查询项目价格产地7查询结果2.8设坊=IHLEK (B2 :13, NATOf (C6? A2:A3, Oj.MkTCHtBS.Bl :11? 0D)t=IHBEX (2:13, MAM (B6, AZ: A3, OMkTCHCjS, Bl :11, OB=HLO

51、OKUF (BS, 1:3,七口)1112整子的价貉2.2=HL001CUF(:偌子,1：*口）HLOOKUP，与VLOOKUP用法相同，区别在于搜索的值一个是在首行，一个是在首歹hE2单元格内是芦柑*在蛇底范围中，芦柑”位于第4行在范国中，第4行是& 6ABCDEFAs m nn价格2苹果3. 5查询货品芦柚有2种计算公式3梨于2. 8贵品价格3. 6=INDEK (B2:E9JMATCH压22:A9, 0) 4香蕉2. 5=VLOOKUP2,。)一5芦细3. 66桔子27葡萄6e橙子4g西瓜1. 5货品是在九列，价格是在E列从A列数，第2列是E列匹配跛单元格内的芦柑”价格是M 6ABCDE

52、F1AOiOiAO 102A01C3AOL04AJ01O&2EQ101D01Q2B0103EQL04B0105B010&3C0101CO 102C01C3C0104C0105C010&4D0101DO 102DO 103D0104DQ105D010&5E0101E0102E0103E0104E0105E010&67AO101=OFFSET0/0)=M8B0101=OFFSET WL 1,0)=A1向下1格9ADI 02=OFFSET (USl, 口，1)HI向启1格10C0103=OFFSET (USL 2,2)SI向下隔再向右Z格11E0101=OFFSET 席A31, 4,0)=A1向下4

53、格12AO106=OFFSET 衅 1, 0,5)XI向右5格13C0105=OFFSET2,4)=A1向下格向右4格14E0106=OFFSET 修魅L 4,5)=A1向下4榕向右5格OFFSET,从某个单元格起，向下N歹土再向右N列的单元格的值。AECDEFG型号10fZO*sor40*AD00145655966ADQ0462524232B0125T9T97979B21459&33Q65CQQEO6&54565SC0024街32196D0140崩6667BS10信息查街11查询型号A000412查询祝耐30#13产忌价裙42=INEE1 CB2:E8, MATCH 康 11, A2:A8,

54、Cl), MATCH (Bl 2, El: El, )14=OFFSET (Al, NATCH (El 1.A2： AS, Oj.HHCHtElS, Bl：!,)15=IHBEK CB2:E8,巳 3 J1&=OFFSET (Al, 2, 3)注意发现上表中，INDEX与OFFSET的区别。ABCDEFGH第几行第几列单元格地址32tBt3=ALBEE 35 娅BZ)11Ml=ALBEE 35 霍，B3)41=ATEEES5 (M, B4)14$D$1=ADDRESS 瞿，B5)32=ADDRESS g E&? ID1或省略绝对引用11All=kDBRESS (AL BT? 2)2绝对行号,相

55、对列标411A4=ALBEE S3 g B8? 3)3相对行号,绝对列标14DI=ALBEE S3 g 由，4)4相对引用ADDRESS，生成指定单元格的位置，ADDRESS(指定的行号，指定的列号)INDIRECT 通常与 ADDRESS 结合使用，注意 INDIRECT(F3)与 INDIRECT(F3”)的区别。AECDEF_GH1入库日期客户名称货品编号规格入库数星重星计算企式1：可以下拉复制一套式2:不可以下拉复制，逐一输入入库日期=IHEIRECT OiDBRESS (1? W Cl :1)IBLIRECT CADDKESS1, 1门客=IBD1RECT (ADERESS (1,蹒

56、也 E)=IBLIRECT CALDKESS (1, )货品辐号INDIRECT (ADDRESS 州)=IHLIRECT (ADDKESS (1, 3)INDIRECT (ADDRESS (1：曲脚:4)=IHLIRECT (ADDKESS (1, 4)入库教星=IHBIRECT (ADDRESS1 HOT 侣：S)=INLIRECT (JJJDKESS Clr 5)重量INDIRECT (ADDRESS1,皿拙(&:胡)=INLIBECT CALDKESS1, 6)10以上通常是耕横向的数值变为巍向的数值时使用，相当于选择性粘贴“中的“倒置备注：ADDRESS1)二色1ADDRESS (1, R0W(3: 3n=ADDRSS (1, 3)=C1ADDRESS确定行数，再确定列数行数星数宇，列数是英立字母注意：INDIRECT(ADDR