Excel实战技巧与提高.ppt

Excel实战技巧与提高技巧与提高第2页序言Excel用户水平的5个层次刚刚开始接触开始接触 基本掌握常用功能基本掌握常用功能熟熟练使用常用功能使用常用功能+数据透数据透视表表+公式公式+数数组公式公式+VBA简单编程程Excel技能技能+专业知知识+行行业经验新手新手 初级用户初级用户 中级用户中级用户 高级用户高级用户 专家专家 第3页知识结构Excel数据数据处理理与操作与操作Excel函数与公式函数与公式Excel图表与表与图形形Excel VBA与宏与宏Excel 数据分析数据分析第4页提纲一、一、认识Excel 二、数据二、数据处理理 三、函数与公式三、函数与公式 四、高四、高级图表表第5页打开文件专业修复的第三方软件：Recovery for Excel第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.40 1,023.40 1023.41023.4正数，显示为带千分号、两位小数正数，显示为带千分号、两位小数-1,023.00-1,023.00-1023-1023负数，显示为带千分号、两位小数、红色负数，显示为带千分号、两位小数、红色0 00 0零值，显示为绿色零值，显示为绿色“等于等于”等于等于文本，显示为文本两侧加双引号文本，显示为文本两侧加双引号100#,#0.00；0,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 A列列B B列列C C列列ABX500120ABX500120-E-E=A1A1&B1B1ABX500120-EABX500120-EA A列列B B列列ABX500120ABX500120=A1A1&-E-E ABX500120ABX500120-E思考：如何把零件号后面的-E去掉？1、连接符&第36页文本处理函数2、大小写字母转换 Lower、Upper、Proper所有大写字母所有大写字母转为小写字母小写字母所有小写字母所有小写字母转为大写字母大写字母字符串首字母字符串首字母转为大写字母大写字母=LOWER(“I LOVE EXCEL”)=“i love excel”=UPPER(“i love excel”)=“I LOVE EXCEL”=UPPER(“I 爱 excel”)=“I 爱 Excel”第37页文本处理函数3、全角半角函数 Widechar、Asc 半角字母半角字母转为全角字母全角字母全角字母全角字母转为半角字母半角字母=WIDECHAR(“Excel”)=“”=ASC(“我爱”)=“我爱Excel”第38页文本处理函数4、生成A-Z Char Char(65)=A Char(66)=B Char(67)=C 思考：如何快速填充A-Z序号Code(“A”)=65Char(97)=aChar(98)=bChar(99)=c第39页文本处理函数 5、提取字符Left、Right、Mid 注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数身份证号身份证号地区地区出生日期出生日期性别性别=left(=left(a1,6a1,6)=mid(=mid(a1,7,6a1,7,6)=right(=right(a1,1a1,1)3201027810014923201027810014923201023201027810017810012 2员工代码员工代码社保号社保号姓名姓名=left(=left(a1,10a1,10)=mid(=mid(a1,11,3a1,11,3)00023370840002337084李虎李虎00023370840002337084李虎李虎00006562460000656246丁世明丁世明00006562460000656246丁世明丁世明为什么是3而不是2？为什么不用Right函数？第40页数字计算函数1、计算余数 Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？第41页数字计算函数2、取整函数 Int Trunc=Int(3.2)返回不大于3.2的最大整数3=Int(-3.2)返回不大于-3.2的最大整数-4=Trunc(3.2)返回截去小数部分后的整数3=Trunc(-3.2)返回截去小数部分后的整数-3第42页数字计算函数3、四舍五入Round数值数值公式公式显示结果显示结果123.456123.456=round(A2,0)=round(A2,0)123123123.456123.456=round(A3,2)=round(A3,2)123.46123.461234.561234.56=round(A4,-2)=round(A4,-2)12001200 区区别第43页数字计算函数3、向上取整Roundup、向下取整Rounddown数值数值RoundupRoundupRounddownRounddown公式公式显示结果显示结果公式公式显示结果显示结果123.456123.456=rounddown(A3,0)=rounddown(A3,0)123123=roundup(A3,0)=roundup(A3,0)124124123.456123.456=rounddown(A3,2)=rounddown(A3,2)123.45123.45=roundup(A3,2)=roundup(A3,2)123.46123.461234.561234.56=rounddown(A3,-=rounddown(A3,-2)2)12001200=roundup(A3,-2)=roundup(A3,-2)13001300第44页日期与时间函数1、4个基础日期函数特殊函数：特殊函数：Today()，读取计算机设置的今日日期，无参数日期日期年年月月日日=YearYear(a1)(a1)=MonthMonth(a1)(a1)=DayDay(a1)(a1)2009-6-162009-6-16200920096 61616年年月月日日日期日期=DATEDATE(A1,B1,C1)(A1,B1,C1)200920096 616162009-6-162009-6-16第45页日期与日期与时间函数函数实战练习：快速计算退休日期（假设：男60退休、女55退休）姓名姓名性别性别出生日期出生日期部门部门退休日期退休日期艾笑贻艾笑贻女女1955-6-201955-6-20综合部综合部安文博安文博男男1961-7-311961-7-31综合部综合部白露白露女女1979-5-131979-5-13综合部综合部谢子坚谢子坚男男1973-10-191973-10-19财务部财务部卜娟芳卜娟芳女女1978-12-291978-12-29财务部财务部蔡星媛蔡星媛女女1959-7-101959-7-10财务部财务部仓晟义仓晟义男男1978-11-201978-11-20信息部信息部曹昱廷曹昱廷男男1965-2-141965-2-14信息部信息部第46页日期与日期与时间函数函数2、Datedif函数 用途：计算两个日期之间的天数、月数或年数。注：此函数在Excel帮助文件中没有。结构：Datedif(start_date,end_date,unit)unitunit代码代码函数返回值函数返回值yy时间段中的整年数时间段中的整年数mm时间段中的整月数时间段中的整月数dd时间段中的天数时间段中的天数mdmd忽略日期中的年和月，计算天数差忽略日期中的年和月，计算天数差ymym忽略日期中的年和日，计算月数差忽略日期中的年和日，计算月数差ydyd忽略日期中的年，计算天数差忽略日期中的年，计算天数差第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页财务函数函数概念解释：函数函数含义含义公式公式raterate利率利率固定值固定值npernper周期周期FVFV终值终值FV(rate,nper,pmt,pv,type)FV(rate,nper,pmt,pv,type)PVPV现值现值PV(rate,nper,pmt,fv,type)PV(rate,nper,pmt,fv,type)PMTPMT期付金额期付金额PMT(rate,nper,pv,fv,type)PMT(rate,nper,pv,fv,type)typetype期初期初oror期末期末期初为期初为1 1，期末为，期末为0 0。如省略，则为。如省略，则为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（rate，nper，pmt，pv，type）=FV（2.25%/12，24，-2000，0，1）=49141.34第51页财务函数函数案例案例5：某人拟在5年后获得10000元，投资报酬率10%，计算现在应该投入的金额。=PV（rate，nper，pmt，fv，type）=PV（10%，5，0，10000）=-6209.21案例案例6：某人余购买一项养老保险，购买成本为60000元，可在20年内每月回报500元，投资报酬率8%，计算是否合适。=PV（rate，nper，pmt，fv，type）=PV（8%/12，20*12，500）=-59777.15 不合适不合适第52页函数与公式1、文本/数字/日期/财务类函数讲解2、案例、案例综合合应用用第53页1、相对单元格与绝对单元格 公式所在单元格与公式所引用单元格的位置关系位置关系称为单元格引用的相对性。A1A1样式样式R1C1R1C1样式样式特征特征$A$1R1C1绝对引用，向右向下复制公式不改变引用关系A$1R1C*行绝对列相对混合引用，向下复制公式不改变引用关系$A1R*C1行相对列绝对混合引用，向右复制公式不改变引用关系A1R*C*相对引用，向右向下复制公式均改变引用关系练习：制作九九乘法表第54页2、工作表的引用1、引用其他工作表的数据、引用其他工作表的数据 =工作表名称工作表名称！目目标单元格元格 例：例：=sheet2!A12、引用已打开的工作簿的数据、引用已打开的工作簿的数据 =工作簿名称工作簿名称工作表名称工作表名称！目目标单元格元格 例：例：=Book2.xlssheet1!$A$13、引用未打开的工作簿的数据、引用未打开的工作簿的数据 =文件路径文件路径工作簿名称工作簿名称工作表名称工作表名称！目目标单元格元格 例：例：=D:Excel案例案例Book2.xlssheet1!$A$1练习：将多张工作表的数据汇总到一张工作表 第55页3、IF函数用途：按指定的条件计算满足条件选项 A A列列B B列列C C列列D D列列 =If=If（C C列列B=22,=22,全勤全勤,C,C列列)序号序号姓名姓名出勤出勤备注备注1张三20202李四23全勤3王五21214贾六22全勤=If（C1=22,全勤,C1）第57页4、LOOKUP函数1.VLOOKUP、HLOOKUP函数 用途：按列/行的方式查询目标值。语法格式：=VLOOKUP（需查找值，范围，相对列，假）2.LOOKUP函数 用途：模糊及区间查询语法格式：=LOOKUP(需查找值，对应值区域，目标值区域）！LOOKUP函数要求分级区间是升序排列。第58页5、SUMIF、COUNTIF函数按照一定的条件进行统计计算。语法：=COUNTIF(条件区域，条件）=SUMIF(条件区域，条件，值区域）第59页6、SUMPRODUCT函数返回相应的数组或乘积之和。语法：=SUMPRODUCT（(array1）*(array2）*）第60页7、初级数组公式 数数组公式公式是用于建立可以产生多个结果或对可以存放在行或列中的一组参数进行运算的单个公式。特点：特点：可以执行多重运算，返回一组数据结果。输入公式后，同时按输入公式后，同时按【Ctrl+Shift+EnterCtrl+Shift+Enter】，公式就会成为数组公式。，公式就会成为数组公式。注：数组公式的外面会自动加注：数组公式的外面会自动加 予以区分。予以区分。第61页综合应用案例分析第62页提纲一、一、认识Excel 二、数据二、数据处理理 三、函数与公式三、函数与公式 四、高四、高级图表表第63页EXCEL图表类型 面积图 XY（散点）图 条形图 曲面图 柱形图 气泡图 折线图 股价图 饼图 圆柱图 圆环图 圆锥图 雷达图 棱锥图共有共有7373个子类型的图表类型。个子类型的图表类型。第64页EXCEL常用图表类型1、柱形、柱形图第65页EXCEL常用图表类型1-1、柱形、柱形图的的变形形第66页高级图表薪酬云梯图第67页EXCEL常用图表类型2、条形、条形图第68页高级图表人口金字塔第69页高级图表进程甘特图第70页EXCEL常用图表类型3、饼图第71页EXCEL常用图表类型3-1、饼图的的变形形第72页高级图表双饼图第73页EXCEL常用图表类型4、折、折线图第74页EXCEL常用图表类型5、XY（散点）（散点）图第75页EXCEL常用图表类型5-1、XY（散点）（散点）图变形形第76页谢谢