Excel使用技巧及函数ppt课件

ITDay（第3期）,Excel公式函数应用2005.9.19,内容提要,基础知识公式函数的分类介绍函数的综合运用财务公式,第一部分Excel基础知识,Excel用户界面绘图层上的对象工作表及公式的保护基本操作,Excel用户界面,菜单及键盘快捷键快捷菜单:右击鼠标时显示智能标记任务窗格:一般在excel的右侧显示(帮助)对话框:例如”工具”选项”拖放:工具栏的拖放单元格的拖放对象和单元格的选择:CtrlShiftCtrl+Shift+F8数据输入:值(包括日期或时间)文本布尔值公式,绘图层上的对象,图形图示图表,公式及单元格的保护,步骤:1.选中要保护单元格2.”格式”单元格”3.”单元格”中的”保护”,清除”锁定”,按”确定”4.”工具”保护”保护单元格”5.输入密码,选择所有用户权限,确定,保护工作簿结构,当一个工作表被保护工作簿结构后,就不能添加和删除工作表了;“工具”保护”保护工作簿”选择了”窗口”,连窗口的大小也无法修改,第二部分公式,公式基础知识名称的引用,公式中的运算符,+加号-减号/除号*乘号%百分号&文本连接符求幂=逻辑比较(等于)逻辑比较(大于)=逻辑比较(大于或等于)逻辑比较(不等于),引用运算符,:(冒号)范围运算符,(逗号)联合运算符(空格)交叉运算符,运算符优先级,(越上面优先级越高)(括号可以改变优先级)-负号%百分号求幂*和/乘和除+和-加和减&文本连接符=,=,比较,公式举例,100-10结果:100-10=100-10结果:90=100-&10结果:100-10=100-&10&=&100-10结果：100-10=90216(1/3)结果：6=10010结果：TRUE=OR(A1100,A2100)结果：如果单元格A1或A2的值100，返回TRUE,隐藏公式,步骤1.选中要保护公式的单元格2.”格式”单元格”保护”3.”隐藏”,确定4.”工具”保护”保护工作表”设置密码,Excel错误值,#DIV/0!除数为0(或除数为空单元格)#NAME?不能识别的名称或公式#N/A查找未匹配或不能使用的数据#NULL!不允许交叉但交叉在一起的域#NUM!与值有关:应该正的出现了负数#REF!引用了无效单元格(已经删除了)#VALUE!公式包括错误形式的变量对象,其他错误,循环引用:例如:在A1中使用公式=A1+1会提示出错:,引用,单元格的引用（F4快速切换）相对：A1绝对：$A$1行绝对：A$1列绝对：$A1引用其他工作表=sheet2!A1引用其他工作簿打开的时候：=book1.xlssheet1!A1关闭的时候：=C:DocumentsandSettingsusers桌面book1.xlssheet1!A1,名称的使用,步骤:1.选中要创建名称的单元格或范围2.”插入”名称”定义”3.输入名称4.只需输入名称,就能使用单元格或某一范围,第三部分函数,函数的概念(与公式的区别)函数的分类常用函数的介绍函数的综合使用,函数的功能(与公式的区别),简化公式=(A1+A2+A3+A4+A5)/5=AVERAGE(A1:A5)实现公式无法实现的计算=MAX(A1:A5)提高编辑速度=PROPER(A1)判断功能,函数的种类,财务函数(PMT)日期及时间函数数学及三角函数统计函数查看和引用函数数据库函数文本函数逻辑函数信息函数工程函数用户自定义函数,文本函数,判断两个字符串是否相等EXACT连接多个文本或单元格&把值文本化TEXT把货币变为文本DOLLAR重复REPT删除多余空格和非打印字符TRIMCLEAN计算字符串中的字符数LEN改变大小写UPPERLOWERPROPER,文本函数,从字符串中提取字符LEFTRIGHTMID替换文本SUBSTITUTEREPLACE在字符串中查找和搜索FIND/SEARCH,文本函数举例,=rept(A1,3)结果:将A1单元格中的内容重复3次=trim(“HelloWorld“)结果:HelloWorld=UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1)结果:首字母大写,其余小写=SUBSTITUTE(“YEAR2004”,”2004”,”2005”)结果:YEAR2005=REPLACE(“AppleChen”,6,”_”)结果:Apple_Chen=FIND(“m”,”Mm”,1)结果:2=SEARCH(“m”,”Mm”,1)结果:1,复合文本函数,计算A1中出现B的次数（无论大小写）=LEN(A1)-LEN(SUBSTITUTE(A1,”B”,”),”b”,”)计算A1中出现B1中内容的频率(大小写)=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),”)/LEN(B1)提取A1中第一个词=LEFT(A1,FIND(“”,A1)-1)提取A1中除第一个以外的词=RIGHT(A1,LEN(A1)-FIND(“”,A1,1)提取A1中最后一个词=RIGHT(A1,LEN(A1)-FIND(“*”,SBUSTITUTE(A1,”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”“,”)=IF(ISERR(FIND(“”,A1,RIGHT(A1,LEN(A1)-FIND(“*”,SBUSTITUTE(A1,”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”“,”),复合文本函数,从身份证中提取生日=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)从身份证中提取性别=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1),2)=1,男,女),加载宏录制,“工具”宏”录制新宏”“工具”宏”宏”执行宏”,计数求和函数,基本函数函数举例高级计数函数,基本计数求和函数,CountCountaCountblankSumSbutotalSumsqsumproduct,基本计数求和函数举例,计算名为DATA的范围内的单元格数量=ROWS(DATA)*COLUMNS(DATA)计算sheet1中空单元格的数量=COUNTBLANK(SHEET1!1:65536)统计范围DATA中出错的单元格数量=SUM(IF(ISERROR(DATA),1),高级统计函数及举例,COUNTIF=COUNTIF(SHEET1!1:35536,100)AND,ORMODE=MODE(A1:A10)统计出现频率最高的值出现的次数=COUNTIF(A1:A10,MODE(A1:A10),查找函数,CHOOSEHLOOKUPINDEXLOOKUPMATCHOFFSETVLOOKUP,综合使用MATCH,INDEX,MATCH(lookup_value,lookup_array,match_type)INDEX(array,row_num,column_num)INDEX(array,MATCH(lookup_value,lookup_array,match_type),第四部分财务公式,基本财务公式简单贴现复杂贴现利率转换自定义利率转换本金与利息折旧问题,Excel基本财务函数,现值PV(rate,nper,pmt,fv,type)期值FV(rate,nper,pmt,pv,type)支付PMT(rate,nper,pmt,pv,type)利率RATE(nper,pmt,pv,fv,type,guess)周期NPER(rate,pmt,pv,fv,type),基本财务公式的举例,如果年息为7%，3年后1000元变多少？使用FV(rate,nper,pmt,pv,type)=FV(7%,3,0,-1000,0)如果想在8年中让1000元成为2000，平均年增长率多少？使用RATE(nper,pmt,pv,fv,type,guess)=RATE(8,0,-1000,2000,0)我的帐户中有10573.45元,已存入12个月,月息1%最初存入多少钱?使用PV(rate,nper,pmt,fv,type)=PV(1%,12,0,10573.45,0),贴现问题,概念:可以把贴现看出是反向的累加.他不是把一个现值累计成一个期值,而是要把一个期值确定出当前价值.和累计一样,可能涉及到3个货币价值参数:PV,FV,PMT.如果只是涉及到2个参数,称为简单的贴现,如果涉及到3个,我们称为复杂的贴现问题.,简单贴现实例,如果贴现率为6.5%年,5年的25000元的接受权的现值是多少?=PV(6.5%,5,0,25000,0)一房产的当前价值是2,000,000元,可以出租5年,购买者已经付了1,850,000元,假定不考虑升值,他的贴现率是多少?使用RATE(nper,pmt,pv,fv,type,guess)=RATE(5,0,-1850000,2000000,0),复杂贴现实例,如果贴现率为0.75%/月,在一房产中得到25000的产出,估计5年中具备2,000,000的价值,需要每月付多少钱?使用PV(rate,nper,pmt,fv,type)=PV(0.75%,60,25000,5000000,1)验算:=RATE(60,25000,PV,5000000,1)分期偿还:一笔期限为10年,利息为0.5%/月的200,000元的贷款如何偿还?使用PMT(rate,nper,pmt,pv,type)=PMT(0.5%,120,200000,0,0)验算:=PV(0.5%,120,PMT,0,0),利率转换,名义利率年度实际利率阶段实际利率例:月息为1%,年息是多少?=(1+0.01)12-1,使用财务函数加载利率转换,使用Excel的插入函数对话框,将光盘中的inserestconversion.xla加载,就能使用其中的自定义利率转换函数了.Excel自带利率转换共有10种不同的方法,使用复杂烦琐,自编函数可以轻松实现自带函数的使用,本金和利息问题,需要支付利息部分IPMT(rate,per,nper,pv,fv,type)需要支付本金部分PPMT(rate,per,nper,pv,fv,type)连续阶段中:CUMIPMT(rate,nper,pv,start_period,end_period,type)CUMPRINC(rate,nper,pv,start_period,end_period,type),本金利息实例,一项为期3年的贷款(按月还款),金额20000,年息8%,最终支付贷款利息和本金是多少?月利息:=IPMT(8%/12,36,36,20000,0,0)月本金:=PPMT(8%/12,36,36,20000,0,0)一抵押贷款250,000元,期限10年,名义利息5.6%,按月复利,按月分期偿还.贷款第一年应该支付多