高级Excel应用与提高.ppt

1、OFFICE系列培训之系列培训之-高级高级ExcelExcel应用与提高应用与提高 李李 虎虎2013.32013.3高级高级ExcelExcel应用与提高应用与提高caun_第2页提纲一、数据处理一、数据处理 二、工作表处理二、工作表处理三、函数与公式三、函数与公式四、高级图表四、高级图表 高级高级ExcelExcel应用与提高应用与提高caun_第3页一、数据输入一、数据输入1、普通输入方法2、使用填充柄3、双击鼠标左键4、两端对齐、两端对齐5、自定义数据序列、自定义数据序列6、导入数据、导入数据7、自定义格式8、数据有效性、数据有效性不要把时间浪费在“打字”上高级高级ExcelExcel

2、应用与提高应用与提高caun_第4页二、数据筛选二、数据筛选1、自动筛选2、高级筛选3、条件格式、条件格式4、自定义格式让需要的数据主动显现出来高级高级ExcelExcel应用与提高应用与提高caun_第5页附加：条件格式的高级应用附加：条件格式的高级应用实战：防止与检查重复输入的数据 1、空表格防止数据重复输入 2、重复出现数据做标示 3、第二次重复以后才标示 高级高级ExcelExcel应用与提高应用与提高caun_第6页三、定义名称三、定义名称1、插入名称。“插入”-“名称”-“定义”2、使用名称框命名、使用名称框命名3、指定名称。“插入”-“名称”-“指定”高级高级ExcelExcel

3、应用与提高应用与提高caun_第7页四、绝对引用四、绝对引用 公式所在单元格与公式所引用单元格的位置关系位置关系称为单元格引用的相对性。A1A1样式样式R1C1R1C1样式样式特征特征$A$1R1C1绝对引用，向右向下复制公式不改变引用关系A$1R1C*行绝对列相对混合引用，向下复制公式不改变引用关系$A1R*C1行相对列绝对混合引用，向右复制公式不改变引用关系A1R*C*相对引用，向右向下复制公式均改变引用关系高级高级ExcelExcel应用与提高应用与提高caun_第8页五、合并计算五、合并计算例：统计报销的金额/人次高级高级ExcelExcel应用与提高应用与提高caun_第9页六、分类

4、汇总六、分类汇总可手动创建分级显示和自动生成分级显示。注意：注意：1、一个工作表内在同行方向上或列方向上只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系 高级高级ExcelExcel应用与提高应用与提高caun_第10页七、数据透视表七、数据透视表初步的数据统计与分析高级高级ExcelExcel应用与提高应用与提高caun_第11页小结小结从不同角度解读数据的含义高级高级ExcelExcel应用与提高应用与提高caun_第12页提纲一、数据处理一、数据处理 二、工作表处理二、工作表处理三、函数与公式三、函数与公式四、高级图表四、高级图表 高级高级ExcelExcel应用与提

5、高应用与提高caun_第13页一、保护一、保护高级高级ExcelExcel应用与提高应用与提高caun_第14页二、引用二、引用1、引用其他工作表的数据、引用其他工作表的数据 =工作表名称工作表名称！目标单元格目标单元格 例：例：=sheet2!A12、引用已打开的工作簿的数据、引用已打开的工作簿的数据 =工作簿名称工作簿名称工作表名称工作表名称！目标单元格目标单元格 例：例：=Book2.xlssheet1!$A$13、引用未打开的工作簿的数据、引用未打开的工作簿的数据 =文件路径文件路径工作簿名称工作簿名称工作表名称工作表名称！目标单元格目标单元格 例：例：=D:Excel案例案例Book

6、2.xlssheet1!$A$1多张同类型表格，多张同类型表格，如何快速汇总？如何快速汇总？高级高级ExcelExcel应用与提高应用与提高caun_第15页三、友好表格三、友好表格1、不让别人动你的表格；2、让填表人按你的需求填写。高级高级ExcelExcel应用与提高应用与提高caun_第16页复杂统计表单设计 小结小结高级高级ExcelExcel应用与提高应用与提高caun_第17页提纲一、数据处理一、数据处理 二、工作表处理二、工作表处理三、函数与公式三、函数与公式四、高级图表四、高级图表 高级高级ExcelExcel应用与提高应用与提高caun_第18页什么是函数？什么是公式？公式是

7、公式是以以“=”为引导，通过运算符按照一定的顺序组合为引导，通过运算符按照一定的顺序组合进行数据运算处理的进行数据运算处理的等式等式。函数是函数是按照特定的算法执行计算的产生一个或一组结果按照特定的算法执行计算的产生一个或一组结果的预定义的的预定义的特殊公式特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式高级高级ExcelExcel应用与提高应用与提高caun_第19页解读函数的参数=IF(A10,SUM(B1:G1),”)函数的结构嵌套函数函数名称以逗号分隔的3个

8、参数 思考：哪些函数不带参数？高级高级ExcelExcel应用与提高应用与提高caun_第20页错误类型常见的错误值及含义常见的错误值及含义错误值类型含义#当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域

9、的交点时，出现错误。高级高级ExcelExcel应用与提高应用与提高caun_第21页文本处理函数 用途：连接“&”前后的字符、字段A A列列B B列列C C列列ABX500120ABX500120-E-E=A1A1&B1B1ABX500120-EABX500120-EA A列列B B列列ABX500120ABX500120=A1A1&-E-E ABX500120ABX500120-E思考：如何把零件号后面的-E去掉？1、连接符&高级高级ExcelExcel应用与提高应用与提高caun_第22页文本处理函数2、大小写字母转换 Lower、Upper、Proper所有大写字母转为小写字母所有大写

10、字母转为小写字母所有小写字母转为大写字母所有小写字母转为大写字母字符串首字母转为大写字母字符串首字母转为大写字母=LOWER(“I LOVE EXCEL”)=“i love excel”=UPPER(“i love excel”)=“I LOVE EXCEL”=UPPER(“I 爱 excel”)=“I 爱 Excel”高级高级ExcelExcel应用与提高应用与提高caun_第23页文本处理函数3、全角半角函数 Widechar、Asc 半角字母转为全角字母半角字母转为全角字母全角字母转为半角字母全角字母转为半角字母=WIDECHAR(“Excel”)=“”=ASC(“我爱”)=“我爱Exc

11、el”高级高级ExcelExcel应用与提高应用与提高caun_第24页文本处理函数4、生成A-Z Char Char(65)=A Char(66)=B Char(67)=C 思考：如何快速填充A-Z序号Code(“A”)=65Char(97)=aChar(98)=bChar(99)=c高级高级ExcelExcel应用与提高应用与提高caun_第25页文本处理函数 5、提取字符Left、Right、Mid 注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数身份证号身份证号地区地区出生日期出生日期性别性别=left(=left(a1,6a1,6)=mid(=mid(a1,7,6a1,

12、7,6)=right(=right(a1,1a1,1)3201027810014923201027810014923201023201027810017810012 2员工代码员工代码社保号社保号姓名姓名=left(=left(a1,10a1,10)=mid(=mid(a1,11,3a1,11,3)00023370950002337095李明李明00023370950002337095李明李明00006562460000656246李小明李小明00006562460000656246李小明李小明为什么是3而不是2？为什么不用Right函数？高级高级ExcelExcel应用与提高应用与提高cau

13、n_第26页数字计算函数数字计算函数1、计算余数 Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？高级高级ExcelExcel应用与提高应用与提高caun_第27页数字计算函数2、取整函数 Int Trunc=Int(3.2)返回不大于3.2的最大整数3=Int(-3.2)返回不大于-3.2的最大整数-4=Trunc(3.2)返回截去小数部分后的整数3=Trunc(-3.2)返回截去小数部分后的整数-3高级高级ExcelExcel应用与提高应用与提高caun_第28页数字计算函数3、四舍五入Roun

14、d数值数值公式公式显示结果显示结果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 区别区别高级高级ExcelExcel应用与提高应用与提高caun_第29页数字计算函数3、向上取整Roundup、向下取整Rounddown数值数值RoundupRoundupRounddownRounddown公式公式显示结果显示结果公式公式显示结果显示结果123.456123

15、.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,-2)=rounddown(A3,-2)12001200=roundup(A3,-2)=roundup(A3,-2)13001300高级高级ExcelExcel应用与提高应用与提高caun_第30

16、页日期与时间函数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高级高级ExcelExcel应用与提高应用与提高caun_第31页日期与时间函数日期与时间函数实战练习：快速计算退休日期（假设：男60退休、女55退休）姓名姓名性别

17、性别出生日期出生日期部门部门退休日期退休日期艾笑贻艾笑贻女女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信息部信息部高级高级ExcelExcel应用与提

18、高应用与提高caun_第32页日期与时间函数日期与时间函数2、Datedif函数 用途：计算两个日期之间的天数、月数或年数。注：此函数在Excel帮助文件中没有。结构：Datedif(start_date,end_date,unit)unitunit代码代码函数返回值函数返回值yy时间段中的整年数时间段中的整年数mm时间段中的整月数时间段中的整月数dd时间段中的天数时间段中的天数mdmd忽略日期中的年和月，计算天数差忽略日期中的年和月，计算天数差ymym忽略日期中的年和日，计算月数差忽略日期中的年和日，计算月数差ydyd忽略日期中的年，计算天数差忽略日期中的年，计算天数差高级高级ExcelEx

19、cel应用与提高应用与提高caun_第33页日期与时间函数日期与时间函数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)364

20、2008-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高级高级ExcelExcel应用与提高应用与提高caun_第34页财务函数财务函数概念解释：函数函数含义含义公式公式raterate

21、利率利率固定值固定值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高级高级ExcelExcel应用与提高应用与提高caun_第35页财务函数财务函数

22、案例案例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高级高级ExcelExcel应用与提高应用与提高caun_第36页财务函数财务函数案例案例3：某人将10000元投入一项事业，年回报率6%，计算3年后的累积金额。=FV（rate，nper，pmt，pv，type

23、）=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高级高级ExcelExcel应用与提高应用与提高caun_第37页财务函数财务函数案例案例5：某人拟在5年后获得10000元，投资报酬率10%，计算现在应该投入的金额。=PV（rate，nper，pmt，fv，type）=PV（10%，5，0，10000）=-6209.21案例案例6

24、：某人余购买一项养老保险，购买成本为60000元，可在20年内每月回报500元，投资报酬率8%，计算是否合适。=PV（rate，nper，pmt，fv，type）=PV（8%/12，20*12，500）=-59777.15 不合适不合适高级高级ExcelExcel应用与提高应用与提高caun_第38页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）高级高级ExcelExc

25、el应用与提高应用与提高caun_第40页LOOKUP函数函数1.VLOOKUP、HLOOKUP函数 用途：按列/行的方式查询目标值。语法格式：=VLOOKUP（需查找值，范围，相对列，假）2.LOOKUP函数 用途：模糊及区间查询语法格式：=LOOKUP(需查找值，对应值区域，目标值区域）！LOOKUP函数要求分级区间是升序排列。高级高级ExcelExcel应用与提高应用与提高caun_第41页SUMIF、COUNTIF函数函数按照一定的条件进行统计计算。语法：=COUNTIF(条件区域，条件）=SUMIF(条件区域，条件，值区域）高级高级ExcelExcel应用与提高应用与提高caun_第

26、42页SUMPRODUCT函数函数返回相应的数组或乘积之和。语法：=SUMPRODUCT（(array1）*(array2）*）高级高级ExcelExcel应用与提高应用与提高caun_第43页数组公式数组公式 数组公式数组公式是用于建立可以产生多个结果或对可以存放在行或列中的一组参数进行运算的单个公式。特点：特点：可以执行多重运算，返回一组数据结果。输入公式后，同时按输入公式后，同时按【Ctrl+Shift+EnterCtrl+Shift+Enter】，公式就会成为数组公式。，公式就会成为数组公式。注：数组公式的外面会自动加注：数组公式的外面会自动加 予以区分。予以区分。高级高级ExcelE

27、xcel应用与提高应用与提高caun_第44页提纲一、数据处理一、数据处理 二、工作表处理二、工作表处理三、函数与公式三、函数与公式四、高级图表四、高级图表 高级高级ExcelExcel应用与提高应用与提高caun_第45页EXCEL图表类型 面积图 XY（散点）图 条形图 曲面图 柱形图 气泡图 折线图 股价图 饼图 圆柱图 圆环图 圆锥图 雷达图 棱锥图共有共有7373个子类型的图表类型。个子类型的图表类型。高级高级ExcelExcel应用与提高应用与提高caun_第46页EXCEL常用图表类型1、柱形图、柱形图高级高级ExcelExcel应用与提高应用与提高caun_第47页EXCEL常

28、用图表类型1-1、柱形图的变形、柱形图的变形高级高级ExcelExcel应用与提高应用与提高caun_第48页高级图表堆积柱形图高级高级ExcelExcel应用与提高应用与提高caun_第49页高级图表薪酬云梯图高级高级ExcelExcel应用与提高应用与提高caun_第50页EXCEL常用图表类型2、条形图、条形图高级高级ExcelExcel应用与提高应用与提高caun_第51页高级图表人口金字塔高级高级ExcelExcel应用与提高应用与提高caun_第52页EXCEL常用图表类型3、饼图、饼图高级高级ExcelExcel应用与提高应用与提高caun_第53页EXCEL常用图表类型3-1、

29、饼图的变形、饼图的变形高级高级ExcelExcel应用与提高应用与提高caun_第54页EXCEL常用图表类型4、折线图、折线图高级高级ExcelExcel应用与提高应用与提高caun_第55页EXCEL常用图表类型5、XY（散点）图（散点）图高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作1、商业周刊、华尔街日报、纽约时报、财经2、咨询公司第56页一、常见的专业图表高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作 “给我给我10页纸的报告，必须有页纸的报告，必须有9页是数据和图表分析，还有页是数据和图表分析，还有1页是封面。页是封面。”财经财经杂志

30、招聘图表编辑的要求：杂志招聘图表编辑的要求：精通Illustrator、Freehand、Coreldraw、Photoshop、3D max 等图形、图像处理软件，能制作专业水平的商业图表。第57页一、专业图表的特点高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作1.专业的外观专业的外观他们的图表都制作精良，显得专业协调。我们或许不知道它专业在哪里，但有一点可以确定，那就是在他们的图表中很少能看到Office 软件中默认的颜色、字体和布局。而这些都正是构成专业外观的重要方面。2.简洁的类型简洁的类型他们的图表都只使用一些最基本的图表类型，绝不复杂。不需要多余的解释，任何

31、人都能看懂图表的意思，真正起到了图表的沟通作用。第58页二、专业图表的特点高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作3.明确的观点明确的观点他们在图表的标题中明白无误地直陈观点，不需要读者再去猜测制图者的意思，确保信息传递的高效率，不会出现偏差。4.完美的细节完美的细节请注意他们对每一个图表元素的处理，几乎达到完美的程度。一丝不苟之中透露出百分百的严谨，好像这不是一份图表，而是一件艺术品。在很大程度上，正是这些无微不至的细节处理，才体现出图表的专业性。而这往往是我们普通图表不会注意到的地方。第59页二、专业图表的特点高级高级ExcelExcel应用与提高应用与提高c

32、aun_专业图表制作第60页二、专业图表的特点高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作第61页三、如何制作专业图表高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作1.使用专业品质的配色使用专业品质的配色 一幅图表给人的印象，很大程度上是由其配色决定的。专业的图表往往是因为它具有专业品质的配色，通过配色透露出一种专业精神。普通用户制作图表，往往会受到Excel默认颜色的限制。而默认颜色要么糟糕不已，要么因长期使用而审美疲劳。推荐拾色软件：ColorPix。第62页三、如何制作专业图表高级高级ExcelExcel应用与提高应用与提高caun_专业

33、图表制作2.使用科学人性化的布局使用科学人性化的布局第63页三、如何制作专业图表布局存在的不足：标题不够突出、信息量不足、绘图区占据了过大的面积、绘图区的四周浪费了很多地方、空间利用率不高，特别是图例在绘图区右侧，阅读视线左右跳跃，需要长距离检索、翻译。高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作2.使用科学人性化的布局（续）使用科学人性化的布局（续）第64页三、如何制作专业图表高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作3.使用更为简洁醒目的字体使用更为简洁醒目的字体商业图表非常重视字体的选择，因为字体会直接影响到图表的专业水准和个性风格。

34、商业图表多选用无衬线类字体。第65页三、如何制作专业图表高级高级ExcelExcel应用与提高应用与提高caun_专业图表制作4.注意图表的细节处理注意图表的细节处理 体现商业图表专业性的地方，是对于图表细节的处理。对每一个图表元素的处理，几乎达到完美的程度。一丝不苟之中透露出百分百的严谨，好像这不是一份图表，而是一件艺术品。正是这些无微不至的细节处理，才体现出图表的专业性。而这往往是我们普通人不会注意到的地方。第66页三、如何制作专业图表高级高级ExcelExcel应用与提高应用与提高caun_第67页谢谢高级高级ExcelExcel应用与提高应用与提高caun_第68页李李 虎虎手机：邮箱： 上汽培训中心南京教学点南京东华企业管理培训中心 用新新创造培训价值用心心服务企业发展特约高级培训师特约高级培训师 南京教学地址：南京市红山路130号培训业务联系：025-86916941 黄/代老师