Excel实战技巧与提高

会计学1Excel实战技巧与提高序言Excel用户水平的5个层次刚刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式…+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手

初级用户

中级用户

高级用户

专家第1页/共78页知识结构Excel数据处理与操作Excel函数与公式Excel图表与图形ExcelVBA与宏Excel数据分析第2页/共78页提纲一、认识Excel

二、数据处理三、函数与公式四、高级图表第3页/共78页打开文件专业修复的第三方软件：RecoveryforExcel第4页/共78页认识界面标题栏菜单栏工具栏行号列号标签栏状态栏??第5页/共78页自定义工具栏1、“视图”----“工具栏”----“自定义”

2、“工具”----“自定义”？第6页/共78页认识选项第7页/共78页认识选项（续）第8页/共78页认识选项（续）第9页/共78页认识选项（续）第10页/共78页提纲一、认识Excel二、数据处理

三、函数与公式四、高级图表第11页/共78页数据处理1、自动填充2、定义名称3、数据有效性4、自定义格式5、条件格式6、分类汇总7、合并计算8、数据透视表第12页/共78页1、自动填充等差填充日期填充文字填充特定填充为什么无法填充？第13页/共78页2、定义名称1、插入名称。“插入”-“名称”-“定义”2、使用名称框命名3、指定名称。“插入”-“名称”-“指定”第14页/共78页2、定义名称（续）全局定义与局部定义：1、默认情况下，为全局定义2、局部定义命名方法：工作表名称+半角感叹号+名称命名规则：1、可用数字与字母组合，但不能以数字开头；2、不能以R、C、r、c作为名称，如R、C在R1C1引用样式中表示行、列；3、名称中不能包含空格，可用下划线或点号代替；4、不能使用除了下换线、点号和反斜杠（\）意外的其他符号。允许使用问号，但不能作为名称的开头；5、名称字符不能超过255个；6、字母不区分大小写。第15页/共78页3、数据有效性第16页/共78页3、数据有效性（续）停止不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。警告可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。信息可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。第17页/共78页3、数据有效性（续）菜单“数据”—“有效性”第18页/共78页4、自定义格式

可为4种类型的数值指定不同的格式：正数、负数、零值和文本。“大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式格式代码组成结构：注：没有特别指定条件值的时候，默认条件值为0第19页/共78页4、自定义格式（续）#,##0.00；[红色]-#,##0.00；[绿色]G/通用格式；“*******”显示为原始数值说明1,023.401023.4正数，显示为带千分号、两位小数-1,023.00-1023负数，显示为带千分号、两位小数、红色00零值，显示为绿色*******等于文本，显示为*******[>100]#,##0.00；[<100][红色]#,##0.00；[绿色]G/通用格式；"“"@"”"思考：第20页/共78页4、自定义格式（续）实战练习：编制设备编号，不足10位，编号前补0，并在10位编号前统一加“No：”。第21页/共78页5、条件格式格式—条件格式第22页/共78页5、条件格式（续）实战：防止与检查重复输入的数据1、空表格防止数据重复输入2、重复出现数据做标示3、第二次重复以后才标示第23页/共78页6、合并计算例：统计报销的金额/人次第24页/共78页7、分类汇总可手动创建分级显示和自动生成分级显示。注意：1、一个工作表内在同行方向上或列方向上只能创建一个分级显示；2、在同一个分级显示中，最多允许有8个层次关系第25页/共78页8、数据透视表初步的数据统计与分析第26页/共78页提纲一、认识Excel二、数据处理三、函数与公式

四、高级图表第27页/共78页什么是函数？什么是公式？

公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。

函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。序号公式说明1=15*3+20*2包含常量运算的公式2=A1*3+A2*2包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*2)包含函数的公式第28页/共78页解读函数的参数=IF(A1>0,SUM(B1:G1),””)函数的结构嵌套函数函数名称以逗号分隔的3个参数思考：哪些函数不带参数？？第29页/共78页查看公式计算结果使用公式审核工具分布求值

以=IF(A1>0,SUM(B1:G1),””)为例，执行“公式求值”12第30页/共78页查看公式计算结果（续）345第31页/共78页错误类型常见的错误值及含义错误值类型含义#####当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误。第32页/共78页函数与公式1、文本/数字/日期/财务类函数讲解2、实战案例综合应用第33页/共78页文本处理函数用途：连接“&”前后的字符、字段A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&"-E"ABX500120ABX500120-E思考：如何把零件号后面的-E去掉？？1、连接符&第34页/共78页文本处理函数2、大小写字母转换Lower、Upper、Proper所有大写字母转为小写字母所有小写字母转为大写字母字符串首字母转为大写字母=LOWER(“ILOVEEXCEL”)=“iloveexcel”=UPPER(“iloveexcel”)=“ILOVEEXCEL”=UPPER(“I爱excel”)=“I爱Excel”第35页/共78页文本处理函数3、全角半角函数Widechar、Asc半角字母转为全角字母全角字母转为半角字母=WIDECHAR(“Excel”)=“Ｅｘｃｅｌ”=ASC(“我爱Ｅｘｃｅｌ”)=“我爱Excel”第36页/共78页文本处理函数4、生成A-ZCharChar(65)=AChar(66)=BChar(67)=C……思考：如何快速填充A-Z序号Code(“A”)=65Char(97)=aChar(98)=bChar(99)=c第37页/共78页文本处理函数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函数？第38页/共78页数字计算函数1、计算余数Mod(3,2)=1Mod(15,4)=3Mod(21,7)=0……应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？第39页/共78页数字计算函数2、取整函数IntTrunc=Int(3.2)返回不大于3.2的最大整数3=Int(-3.2)返回不大于-3.2的最大整数-4=Trunc(3.2)返回截去小数部分后的整数3=Trunc(-3.2)返回截去小数部分后的整数-3第40页/共78页数字计算函数3、四舍五入Round数值公式显示结果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200

区别第41页/共78页数字计算函数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第42页/共78页日期与时间函数1、4个基础日期函数特殊函数：Today()，读取计算机设置的今日日期，无参数日期年月日

=Year(a1)=Month(a1)=Day(a1)2009-6-162009616年月日日期

=DATE(A1,B1,C1)20096162009-6-16第43页/共78页日期与时间函数实战练习：

快速计算退休日期（假设：男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信息部

第44页/共78页日期与时间函数2、Datedif函数用途：计算两个日期之间的天数、月数或年数。注：此函数在Excel帮助文件中没有。结构：Datedif(start_date,end_date,unit)unit代码函数返回值"y"时间段中的整年数"m"时间段中的整月数"d"时间段中的天数"md"忽略日期中的年和月，计算天数差"ym"忽略日期中的年和日，计算月数差"yd"忽略日期中的年，计算天数差第45页/共78页日期与时间函数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第46页/共78页财务函数概念解释：函数含义公式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第47页/共78页财务函数案例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第48页/共78页财务函数案例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第49页/共78页财务函数案例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不合适第50页/共78页函数与公式1、文本/数字/日期/财务类函数讲解2、案例综合应用第51页/共78页1、相对单元格与绝对单元格

公式所在单元格与公式所引用单元格的位置关系称为单元格引用的相对性。A1样式R1C1样式特征$A$1R1C1绝对引用，向右向下复制公式不改变引用关系A$1R1C[*]行绝对列相对混合引用，向下复制公式不改变引用关系$A1R[*]C1行相对列绝对混合引用，向右复制公式不改变引用关系A1R[*]C[*]相对引用，向右向下复制公式均改变引用关系练习：制作九九乘法表第52页/共78页2、工作表的引用1、引用其他工作表的数据

=工作表名称！目标单元格例：=sheet2!A12、引用已打开的工作簿的数据

=[工作簿名称]工作表名称！目标单元格例：=[Book2.xls]sheet1!$A$13、引用未打开的工作簿的数据

=‘文件路径[工作簿名称]工作表名称’！目标单元格例：=‘D:\Excel案例[Book2.xls]sheet1’!$A$1练习：将多张工作表的数据汇总到一张工作表第53页/共78页3、IF函数用途：按指定的条件计算满足条件选项A列B列C列D列=If（C列<B列,"缺件隐患","正常")件号安全库存现库存备注ABX46001010080缺件隐患AFE450030100160正常ALA5001404060正常EUH10025012082缺件隐患第54页/共78页IF函数实例例2：对月出勤超过22天的员工做“全勤”备注,低于22天的显示实际出勤天数A列B列C列D列=If(C列>=22,"全勤",C列)序号姓名出勤备注1张三20202李四23全勤3王五21214贾六22全勤=If（C1>=22,"全勤",C1）第55页/共78页4、LOOKUP函数1.VLOOKUP、HLOOKUP函数用途：按列/行的方式查询目标值。语法格式：=VLOOKUP（需查找值，范围，相对列，假）2.LOOKUP函数用途：模糊及区间查询语法格式：=LOOKUP(需查找值，对应值区域，目标值区域）！LOOKUP函数要求分级区间是升序排列。第56页/共78页5、SUMIF、COUNTIF函数按照一定的条件进行统计计算。语法：=COUNTIF(条件区域，条件）

=SUMIF(条件区域，条件，值区域）第57页/共78页6、SUMPRODUCT函数返回相应的数组或乘积之和。

语法：=SUMPRODUCT（(array1）*(array2）*……）第58页/共78页7、初级数组公式

数组公式是用于建立可以产生多个结果或对可以存放在行或列中的一组参数进行运算的单个公式。

特点：可以执行多重运算，返回一组数据结果。输入公式后，同时按【Ctrl+Shift+Enter】，公式就会成为数组公式。注：数组公式的外面会自动加{}予以区分。