Excel实战技巧与提高

Excel实战技巧与提高第2页序言Excel用户水平的5个层次刚刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式…+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手初级用户中级用户高级用户专家第3页知识结构Excel数据处理与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析第4页提纲一、认识Excel二、数据处理三、函数与公式四、高级图表第5页打开文件专业修复的第三方软件：RecoveryforExcel第6页认识界面标题栏菜单栏工具栏行号列号标签栏状态栏??第7页自定义工具栏1、“视图”----“工具栏”----“自定义”2、“工具”----“自定义”？第8页认识选项第9页认识选项（续）第10页认识选项（续）第11页认识选项（续）第12页提纲一、认识Excel二、数据处理三、函数与公式四、高级图表第13页数据处理1、自动填充2、定义名称3、数据有效性4、自定义格式5、条件格式6、分类汇总7、合并计算8、数据透视表第14页1、自动填充等差填充日期填充文字填充特定填充为什么无法填充？第15页2、定义名称1、插入名称。“插入”-“名称”-“定义”2、使用名称框命名3、指定名称。“插入”-“名称”-“指定”第16页2、定义名称（续）全局定义与局部定义：1、默认情况下，为全局定义2、局部定义命名方法：工作表名称+半角感叹号+名称命名规则：1、可用数字与字母组合，但不能以数字开头；2、不能以R、C、r、c作为名称，如R、C在R1C1引用样式中表示行、列；3、名称中不能包含空格，可用下划线或点号代替；4、不能使用除了下换线、点号和反斜杠（\）意外的其他符号。允许使用问号，但不能作为名称的开头；5、名称字符不能超过255个；6、字母不区分大小写。第17页3、数据有效性菜单“数据”—“有效性”第18页3、数据有效性（续）第19页3、数据有效性（续）停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。第20页4、自定义格式可为4种类型的数值指定不同的格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式格式代码组成结构：注：没有特别指定条件值的时候，默认条件值为0第21页4、自定义格式（续）#,##0.00；[红色]-#,##0.00；[绿色]G/通用格式；““”@“””显示为原始数值说明1,023.401023.4正数，显示为带千分号、两位小数-1,023.00-1023负数，显示为带千分号、两位小数、红色00零值，显示为绿色“等于”等于文本，显示为文本两侧加双引号[100]#,##0.00；[100][红色]#,##0.00；[绿色]G/通用格式；“******”思考：第22页4、自定义格式（续）实战练习：编制设备编号，不足10位，编号前补0，并在10位编号前统一加“No：”。第23页5、条件格式格式—条件格式第24页5、条件格式（续）实战：防止与检查重复输入的数据1、空表格防止数据重复输入2、重复出现数据做标示3、第二次重复以后才标示第25页6、合并计算例：统计报销的金额/人次第26页7、分类汇总可手动创建分级显示和自动生成分级显示。注意：1、一个工作表内在同行方向上或列方向上只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系第27页8、数据透视表初步的数据统计与分析第28页提纲一、认识Excel二、数据处理三、函数与公式四、高级图表第29页什么是函数？什么是公式？公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式第30页解读函数的参数=IF(A10,SUM(B1:G1),””)函数的结构嵌套函数函数名称以逗号分隔的3个参数思考：哪些函数不带参数？？第31页查看公式计算结果使用公式审核工具分布求值以=IF(A10,SUM(B1:G1),””)为例，执行“公式求值”12第32页查看公式计算结果（续）345第33页错误类型常见的错误值及含义错误值类型含义#####当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误。第34页函数与公式1、文本/数字/日期/财务类函数讲解2、实战案例综合应用第35页文本处理函数用途：连接“&”前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&-EABX500120ABX500120-E思考：如何把零件号后面的-E去掉？？1、连接符&第36页文本处理函数2、大小写字母转换Lower、Upper、Proper所有大写字母转为小写字母所有小写字母转为大写字母字符串首字母转为大写字母=LOWER(“ILOVEEXCEL”)=“iloveexcel”=UPPER(“iloveexcel”)=“ILOVEEXCEL”=UPPER(“I爱excel”)=“I爱Excel”第37页文本处理函数3、全角半角函数Widechar、Asc半角字母转为全角字母全角字母转为半角字母=WIDECHAR(“Excel”)=“Ｅｘｃｅｌ”=ASC(“我爱Ｅｘｃｅｌ”)=“我爱Excel”第38页文本处理函数4、生成A-ZCharChar(65)=AChar(66)=BChar(67)=C……思考：如何快速填充A-Z序号Code(“A”)=65Char(97)=aChar(98)=bChar(99)=c第39页文本处理函数5、提取字符Left、Right、Mid注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数身份证号地区出生日期性别=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012员工代码社保号姓名=left(a1,10)=mid(a1,11,3)0002337084李虎0002337084李虎0000656246丁世明0000656246丁世明为什么是3而不是2？为什么不用Right函数？第40页数字计算函数1、计算余数Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0……应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？第41页数字计算函数2、取整函数IntTrunc=Int(3.2)返回不大于3.2的最大整数3=Int(-3.2)返回不大于-3.2的最大整数-4=Trunc(3.2)返回截去小数部分后的整数3=Trunc(-3.2)返回截去小数部分后的整数-3第42页数字计算函数3、四舍五入Round数值公式显示结果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200区别第43页数字计算函数3、向上取整Roundup、向下取整Rounddown数值RoundupRounddown公式显示结果公式显示结果123.456=rounddown(A3,0)123=roundup(A3,0)124123.456=rounddown(A3,2)123.45=roundup(A3,2)123.461234.56=rounddown(A3,-2)1200=roundup(A3,-2)1300第44页日期与时间函数1、4个基础日期函数特殊函数：Today()，读取计算机设置的今日日期，无参数日期年月日=Year(a1)=Month(a1)=Day(a1)2009-6-162009616年月日日期=DATE(A1,B1,C1)20096162009-6-16第45页日期与时间函数实战练习：快速计算退休日期（假设：男60退休、女55退休）姓名性别出生日期部门退休日期艾笑贻女1955-6-20综合部安文博男1961-7-31综合部白露女1979-5-13综合部谢子坚男1973-10-19财务部卜娟芳女1978-12-29财务部蔡星媛女1959-7-10财务部仓晟义男1978-11-20信息部曹昱廷男1965-2-14信息部第46页日期与时间函数2、Datedif函数用途：计算两个日期之间的天数、月数或年数。注：此函数在Excel帮助文件中没有。结构：Datedif(start_date,end_date,unit)unit代码函数返回值y时间段中的整年数m时间段中的整月数d时间段中的天数md忽略日期中的年和月，计算天数差ym忽略日期中的年和日，计算月数差yd忽略日期中的年，计算天数差第47页日期与时间函数2、Datedif函数(续)开始日期结束日期公式显示结果2000-6-152009-6-16=datedif(A2,B2,y)92000-6-172009-6-16=datedif(A3,B3,y)82008-6-152009-6-16=datedif(A4,B4,m)122008-6-172009-6-16=datedif(A5,B5,m)112008-6-152009-6-16=datedif(A6,B6,d)3662008-6-172009-6-16=datedif(A7,B7,d)3642008-3-152009-6-16=datedif(A8,B8,md)12008-3-172009-6-16=datedif(A9,B9,md)302008-3-152009-6-16=datedif(A10,B10,ym)32008-6-172009-6-16=datedif(A11,B11,ym)112008-6-152009-6-16=datedif(A12,B12,yd)12008-6-172009-6-16=datedif(A13,B13,yd)364第48页财务函数概念解释：函数含义公式rate利率固定值nper周期FV终值FV(rate,nper,pmt,[pv],[type])PV现值PV(rate,nper,pmt,[fv],[type])PMT期付金额PMT(rate,nper,pv,[fv],[type])type期初or期末期初为1，期末为0。如省略，则为0第49页财务函数案例1：分10个月付清年利率为8%的10000元贷款，计算月支付金额。=PTM（rate，nper，pv，[fv]，[type]）=PTM（8%/12，10，10000）=-1037.03案例2：需以年利率5%存款15年，达到存款总额1,500,000，计算每月存款金额。=PTM（rate，nper，pv，[fv]，[type]）=PTM（5%/12，15，0，1500000）=-5611.9第50页财务函数案例3：某人将10000元投入一项事业，年回报率6%，计算3年后的累积金额。=FV（rate，nper，pmt，[pv]，[type]）=PTM（6%，3，0，-10000）=11910.16案例4：两年后需要大笔支出，计划从现在起，每月初存入2000元，年利率2.25%，按月计息（2.25%/12），计算两年后帐户金额。=FV（ra