Excel实战技巧与提高.ppt

OFFICE系列培训之-,Excel实战技巧与提高,李 虎,2009.6,第2页,序言,Excel用户水平的5个层次,第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/通用格式；“ “ ”“ ” ”,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页,什么是函数？什么是公式？,公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。 函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。,第30页,解读函数的参数,=IF(A10,SUM(B1:G1),” ”),函数的结构,嵌套函数,函数名称,以逗号分隔的3个参数,思考：哪些函数不带参数？,第31页,查看公式计算结果,使用公式审核工具分布求值 以=IF(A10,SUM(B1:G1),” ”)为例，执行“公式求值”,1,2,第32页,查看公式计算结果（续）,3,4,5,第33页,错误类型,常见的错误值及含义,第34页,函数与公式,1、文本/数字/日期/财务类函数讲解 2、实战案例综合应用,第35页,文本处理函数,用途：连接“&”前后的字符、字段,思考：如何把零件号后面的-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”)=65,Char(97)=a Char(98)=b Char(99)=c,第39页,文本处理函数,5、提取字符Left 、 Right 、Mid,注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数,为什么是3而不是2？ 为什么不用Right函数？,第40页,数字计算函数,1、计算余数,Mod(3,2)=1 Mod(15,4)=3 Mod(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,区别,第43页,数字计算函数,3、向上取整Roundup、向下取整Rounddown,第44页,日期与时间函数,1、4个基础日期函数,特殊函数： Today()，读取计算机设置的今日日期，无参数,第45页,日期与时间函数,实战练习： 快速计算退休日期（假设：男60退休、女55退休）,第46页,日期与时间函数,2、Datedif函数 用途：计算两个日期之间的天数、月数或年数。,注：此函数在Excel帮助文件中没有。结构：Datedif(start_date,end_date,unit),第47页,日期与时间函数,2、Datedif函数(续),第48页,财务函数,概念解释：,第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、相对单元格与绝对单元格,公式所在单元格与公式所引用单元格的位置关系称为单元格引用的相对性。,练习：制作九九乘法表,第54页,2、工作表的引用,1、引用其他工作表的数据 =工作表名称！目标单元格 例：=sheet2!A1 2、引用已打开的工作簿的数据 =工作簿名称工作表名称！目标单元格 例： =Book2.xlssheet1!$A$1 3、引用未打开的工作簿的数据 =文件路径工作簿名称工作表名称！目标单元格 例： =D:Excel案例Book2.xlssheet1!$A$1,练习：将多张工作表的数据汇总到一张工作表,第55页,3、IF函数,用途：按指定的条件计算满足条件选项,第56页,IF函数实例,例2：对月出勤超过22天的员工做“全勤”备注,低于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+Enter】，公式就会成为数组公式。 注：数组公式的外面会自动加 予以区分。,第61页,综合应用,案例分析,第62页,提纲,一、认识Excel 二、数据处理 三、函数与公式 四、高级图表,第63页,EXCEL图表类型,面积图 XY（散点）图 条形图 曲面图 柱形图 气泡图 折线图 股价图 饼图 圆柱图 圆环图 圆锥图 雷达图 棱锥图,共有73个子类型的图表类型。,第64页,EXCEL常用图表类型,1、柱形图,第65页,EXCEL常用图表类型,1-1、柱形图的变形,第66页,高级图表,薪酬云梯图,第67页,EXCEL常用图表类型,2、条形图,第68页,高级图表,人口金字塔,第69页,高级图表,进程甘特