1、IT Day（第3期）,Excel公式函数应用2005.9.19,内容提要,基础知识 公式 函数的分类介绍函数的综合运用 财务公式,第一部分 Excel基础知识,Excel用户界面 绘图层上的对象 工作表及公式的保护 基本操作,Excel用户界面,菜单 及 键盘快捷键 快捷菜单:右击鼠标时显示 智能标记 任务窗格:一般在excel的右侧显示(帮助) 对话框: 例如”工具”选项” 拖放: 工具栏的拖放单元格的拖放 对象和单元格的选择:Ctrl Shift Ctrl+Shift+ F8 数据输入:值(包括日期或时间)文本布尔值公式,绘图层上的对象,图形 图示 图表,公式及单元格的保护,步骤: 1.
2、选中要保护单元格 2.”格式”单元格” 3.”单元格”中的”保护”,清除”锁定”,按”确定” 4.”工具”保护”保护单元格” 5.输入密码,选择所有用户权限,确定,保护工作簿结构,当一个工作表被保护工作簿结构后,就不能添加和删除工作表了; “工具”保护”保护工作簿” 选择了”窗口”,连窗口的大小也无法修改,第二部分 公式,公式基础知识 名称的引用,公式中的运算符,+ 加号 - 减号 / 除号 * 乘号 % 百分号 & 文本连接符 求幂 = 逻辑比较(等于) 逻辑比较(大于) = 逻辑比较(大于或等于)逻辑比较(不等于),引用运算符,: (冒号) 范围运算符 , (逗号) 联合运算符(空格) 交
3、叉运算符,运算符优先级,(越上面优先级越高) (括号可以改变优先级) - 负号 % 百分号 求幂 *和/ 乘和除 +和- 加和减 & 文本连接符 =,=,比较,公式举例,100-10结果:100-10 =100-10结果:90 =“100-“&“10“结果: 100-10 =“100-“&“10“&“=“ & 100-10结果：100-10=90 216(1/3)结果：6 =10010结果：TRUE =OR(A1100,A2100)结果：如果单元格A1或A2的值100，返回TRUE,隐藏公式,步骤 1.选中要保护公式的单元格 2.”格式”单元格”保护” 3.”隐藏”,确定 4.”工具”保护”保
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
5、:Documents and Settingsusers桌面 book1.xlssheet1!A1,名称的使用,步骤: 1.选中要创建名称的单元格或范围 2.”插入”名称”定义” 3.输入名称4.只需输入名称,就能使用单元格或某一范围,第三部分 函数,函数的概念(与公式的区别) 函数的分类 常用函数的介绍 函数的综合使用,函数的功能(与公式的区别),简化公式=(A1+A2+A3+A4+A5)/5=AVERAGE(A1:A5) 实现公式无法实现的计算=MAX(A1:A5) 提高编辑速度=PROPER(A1) 判断功能,函数的种类,财务函数 (PMT) 日期及时间函数 数学及三角函数 统计函数 查
6、看和引用函数 数据库函数 文本函数 逻辑函数 信息函数 工程函数 用户自定义函数,文本函数,判断两个字符串是否相等EXACT 连接多个文本或单元格& 把值文本化TEXT把货币变为文本 DOLLAR 重复REPT 删除多余空格和非打印字符TRIM CLEAN 计算字符串中的字符数LEN 改变大小写UPPERLOWERPROPER,文本函数,从字符串中提取字符LEFTRIGHTMID 替换文本SUBSTITUTEREPLACE 在字符串中查找和搜索FIND/SEARCH,文本函数举例,=rept(A1,3)结果:将A1单元格中的内容重复3次 =trim(“ Hello World “)结果:Hel
7、lo 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)结果:1,复合文本函数,计算A1中出现B的次数（无论大小写）=LEN(A1)-LEN(SUBSTITUTE(A1,”B”,”),”b”,”) 计算A1中出现B1中内容的频率(大小写)=(LEN(A1
8、)-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(
9、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,“男“,“女“),加载宏录制,“工具”宏”录制新宏” “工具”宏”宏”执行宏”,计数求和函数,基本函数 函数举例 高级计数函数,基本计数求和函数,Count Counta Countblank Sum Sbutotal Sumsq sumproduct,基本计数求和函数举例,计算名为DATA的范围内的单元格数量=ROWS
10、(DATA)*COLUMNS(DATA) 计算sheet1中空单元格的数量=COUNTBLANK(SHEET1!1:65536) 统计范围DATA中出错的单元格数量=SUM(IF(ISERROR(DATA),1),高级统计函数及举例,COUNTIF=COUNTIF(SHEET1!1:35536,100) AND,OR MODE=MODE(A1:A10)统计出现频率最高的值出现的次数=COUNTIF(A1:A10, MODE(A1:A10),查找函数,CHOOSE HLOOKUP INDEX LOOKUP MATCH OFFSET VLOOKUP,综合使用MATCH,INDEX,MATCH(lo
11、okup_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,gu
12、ess) 周期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),贴现问题,概念:可以把贴现看出是反
13、向的累加.他不是把一个现值累计成一个期值,而是要把一个期值确定出当前价值.和累计一样,可能涉及到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),复杂贴现实例
14、,如果贴现率为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%,年息是
15、多少?=(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,np
16、er,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%,按月复利,按月分期偿还.贷款第一年应该支付多少利息和本金: 本金:=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 即本金+利息,折旧函数,SLN DB DDB SYD VDB(可变递减余额,使用双递减余额法或指定的其他方法计算任何阶段的资产折旧),谢谢您的参加,请填写反馈意见!,
