Excel使用技巧及函数.ppt
Media Partners Internationalno 1 IT Day IT Day（第（第（第（第3 3期）期）期）期）Excel公式函数应用 2005.9.19Media Partners Internationalno 2内容提要内容提要内容提要内容提要基础知识公式函数的分类介绍 函数的综合运用财务公式Media Partners Internationalno 3第一部分第一部分第一部分第一部分 ExcelExcel基础知识基础知识基础知识基础知识Excel用户界面绘图层上的对象工作表及公式的保护基本操作Media Partners Internationalno 4ExcelExcel用户界面用户界面用户界面用户界面菜单 及 键盘快捷键快捷菜单:右击鼠标时显示智能标记任务窗格:一般在excel的右侧显示(帮助)对话框:例如”工具”选项”拖放:工具栏的拖放 单元格的拖放对象和单元格的选择:Ctrl Shift Ctrl+Shift+F8数据输入:值(包括日期或时间)文本 布尔值 公式 Media Partners Internationalno 5绘图层上的对象绘图层上的对象绘图层上的对象绘图层上的对象图形图示图表Media Partners Internationalno 6公式及单元格的保护公式及单元格的保护公式及单元格的保护公式及单元格的保护步骤:1.选中要保护单元格2.”格式”单元格”3.”单元格”中的”保护”,清除”锁定”,按”确定”4.”工具”保护”保护单元格”5.输入密码,选择所有用户权限,确定Media Partners Internationalno 7保护工作簿结构保护工作簿结构保护工作簿结构保护工作簿结构当一个工作表被保护工作簿结构后,就不能添加和删除工作表了;“工具”保护”保护工作簿”选择了”窗口”,连窗口的大小也无法修改Media Partners Internationalno 8第二部分第二部分第二部分第二部分 公式公式公式公式公式基础知识名称的引用Media Partners Internationalno 9公式中的运算符公式中的运算符公式中的运算符公式中的运算符+加号-减号/除号*乘号%百分号&文本连接符 求幂=逻辑比较(等于)逻辑比较(大于)=逻辑比较(大于或等于)=逻辑比较(小于或等于)逻辑比较(不等于)Media Partners Internationalno 10引用运算符引用运算符引用运算符引用运算符:(冒号)范围运算符,(逗号)联合运算符 (空格)交叉运算符Media Partners Internationalno 11运算符优先级运算符优先级运算符优先级运算符优先级(越上面优先级越高)(括号可以改变优先级)-负号%百分号 求幂*和/乘和除+和-加和减&文本连接符=,=,比较Media Partners Internationalno 12公式举例公式举例公式举例公式举例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 Media Partners Internationalno 13隐藏公式隐藏公式隐藏公式隐藏公式步骤1.选中要保护公式的单元格2.”格式”单元格”保护”3.”隐藏”,确定4.”工具”保护”保护工作表”设置密码Media Partners Internationalno 14ExcelExcel错误值错误值错误值错误值#DIV/0!除数为0(或除数为空单元格)#NAME?不能识别的名称或公式#N/A 查找未匹配或不能使用的数据#NULL!不允许交叉但交叉在一起的域#NUM!与值有关:应该正的出现了负数#REF!引用了无效单元格(已经删除了)#VALUE!公式包括错误形式的变量对象Media Partners Internationalno 15其他错误其他错误其他错误其他错误循环引用:例如:在A1中使用公式 =A1+1会提示出错:Media Partners Internationalno 16引用引用引用引用单元格的引用单元格的引用（F4快速切换）相对：A1 绝对：$A$1 行绝对：A$1 列绝对：$A1引用其他工作表=sheet2!A1引用其他工作簿打开的时候：=book1.xlssheet1!A1 关闭的时候：=C:Documents and Settingsusers桌面 book1.xlssheet1!A1Media Partners Internationalno 17名称的使用名称的使用名称的使用名称的使用步骤:1.选中要创建名称的单元格或范围2.”插入”名称”定义”3.输入名称4.只需输入名称,就能使用单元格或某一范围Media Partners Internationalno 18第三部分第三部分第三部分第三部分 函数函数函数函数函数的概念(与公式的区别)函数的分类常用函数的介绍函数的综合使用Media Partners Internationalno 19函数的功能函数的功能函数的功能函数的功能(与公式的区别与公式的区别与公式的区别与公式的区别)简化公式 =(A1+A2+A3+A4+A5)/5 =AVERAGE(A1:A5)实现公式无法实现的计算 =MAX(A1:A5)提高编辑速度 =PROPER(A1)判断功能Media Partners Internationalno 20函数的种类函数的种类函数的种类函数的种类财务函数(PMT)日期及时间函数数学及三角函数统计函数查看和引用函数数据库函数文本函数逻辑函数信息函数工程函数用户自定义函数Media Partners Internationalno 21文本函数文本函数文本函数文本函数判断两个字符串是否相等EXACT连接多个文本或单元格&把值文本化TEXT 把货币变为文本 DOLLAR重复REPT删除多余空格和非打印字符TRIM CLEAN计算字符串中的字符数LEN改变大小写UPPERLOWERPROPERMedia Partners Internationalno 22文本函数文本函数文本函数文本函数从字符串中提取字符 LEFTRIGHTMID替换文本SUBSTITUTEREPLACE在字符串中查找和搜索FIND/SEARCHMedia Partners Internationalno 23文本函数举例文本函数举例文本函数举例文本函数举例=rept(A1,3)结果:将A1单元格中的内容重复3次=trim(“Hello World “)结果:Hello World=UPPER(LEFT(A1)&RIGHT(LOWER(A1),LEN(A1)-1)结果:首字母大写,其余小写=SUBSTITUTE(“YEAR 2004”,”2004”,”2005”)结果:YEAR 2005=REPLACE(“Apple Chen”,6,”_”)结果:Apple_Chen=FIND(“m”,”Mm”,1)结果:2=SEARCH(“m”,”Mm”,1)结果:1Media Partners Internationalno 24复合文本函数复合文本函数复合文本函数复合文本函数计算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,”“,”)Media Partners Internationalno 25复合文本函数复合文本函数复合文本函数复合文本函数从身份证中提取生日=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,男,女)Media Partners Internationalno 26加载宏录制加载宏录制加载宏录制加载宏录制“工具”宏”录制新宏”“工具”宏”宏”执行宏”Media Partners Internationalno 27计数求和函数计数求和函数计数求和函数计数求和函数基本函数函数举例高级计数函数Media Partners Internationalno 28基本计数求和函数基本计数求和函数基本计数求和函数基本计数求和函数CountCountaCountblankSumSbutotalSumsqsumproductMedia Partners Internationalno 29基本计数求和函数举例基本计数求和函数举例基本计数求和函数举例基本计数求和函数举例计算名为DATA的范围内的单元格数量 =ROWS(DATA)*COLUMNS(DATA)计算sheet1中空单元格的数量 =COUNTBLANK(SHEET1!1:65536)统计范围DATA中出错的单元格数量 =SUM(IF(ISERROR(DATA),1)Media Partners Internationalno 30高级统计函数及举例高级统计函数及举例高级统计函数及举例高级统计函数及举例COUNTIF =COUNTIF(SHEET1!1:35536,100)AND,ORMODE =MODE(A1:A10)统计出现频率最高的值出现的次数 =COUNTIF(A1:A10,MODE(A1:A10)Media Partners Internationalno 31查找函数查找函数查找函数查找函数CHOOSEHLOOKUPINDEXLOOKUPMATCHOFFSETVLOOKUPMedia Partners Internationalno 32综合使用综合使用综合使用综合使用MATCH,INDEXMATCH,INDEXMATCH(lookup_value,lookup_array,match_type)INDEX(array,row_num,column_num)INDEX(array,MATCH(lookup_value,lookup_array,match_type)Media Partners Internationalno 33第四部分第四部分第四部分第四部分 财务公式财务公式财务公式财务公式基本财务公式简单贴现复杂贴现利率转换自定义利率转换本金与利息折旧问题Media Partners Internationalno 34ExcelExcel基本财务函数基本财务函数基本财务函数基本财务函数现值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)Media Partners Internationalno 35基本财务公式的举例基本财务公式的举例基本财务公式的举例基本财务公式的举例如果年息为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)Media Partners Internationalno 36贴现问题贴现问题贴现问题贴现问题概念:可以把贴现看出是反向的累加.他不是把一个现值累计成一个期值,而是要把一个期值确定出当前价值.和累计一样,可能涉及到3个货币价值参数:PV,FV,PMT.如果只是涉及到2个参数,称为简单的贴现,如果涉及到3个,我们称为复杂的贴现问题.Media Partners Internationalno 37 简单贴现实例简单贴现实例简单贴现实例简单贴现实例如果贴现率为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)Media Partners Internationalno 38复杂贴现实例复杂贴现实例复杂贴现实例复杂贴现实例如果贴现率为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)Media Partners Internationalno 39利率转换利率转换利率转换利率转换名义利率年度实际利率阶段实际利率例:月息为1%,年息是多少?=(1+0.01)12-1Media Partners Internationalno 40使用财务函数加载利率转换使用财务函数加载利率转换使用财务函数加载利率转换使用财务函数加载利率转换使用Excel的插入函数对话框,将光盘中的inserestconversion.xla加载,就能使用其中的自定义利率转换函数了.Excel自带利率转换共有10种不同的方法,使用复杂烦琐,自编函数可以轻松实现自带函数的使用Media Partners Internationalno 41本金和利息问题本金和利息问题本金和利息问题本金和利息问题需要支付利息部分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)Media Partners Internationalno 42本金利息实例本金利息实例本金利息实例本金利息实例一项为期3年的贷款(按月还款),金额20000,年息8%,最终支付贷款利息和本金是多少?月利息:=IPMT(8%/12,36,36,20000,0,0)月本金:=PPMT(8%/12,36,36,20000,0,0)一抵押贷款250,000元,期限10年,名义利息5.6%,按月复利,按月分期偿还.贷款第一年应该支付多少利息和本金:本金:=CUMIPMT(Nomx_Effx(5.6%*12),10*12,250000,1,12,0)利息:=CUMPRINC(Nomx_Effx(5.6%*12),10*12,250000,1,12,0)验算:=PMT(Nomx_Effx(5.6%*12),10*12,250000,0,0)*12 即本金+利息Media Partners Internationalno 43折旧函数折旧函数折旧函数折旧函数SLNDBDDBSYDVDB(可变递减余额,使用双递减余额法或指定的其他方法计算任何阶段的资产折旧)Media Partners Internationalno 44谢谢您的参加谢谢您的参加谢谢您的参加谢谢您的参加,请填写反馈意见请填写反馈意见请填写反馈意见请填写反馈意见!
