EXCEL在财务中的应用.pptx

Excel在财务中的应用,我们先来认识Excel软件最好的老师是谁?,请打开Microsoft Office Excel 任何版本,1,请点击Excel右上角“?”-“必须联网”,2,认识Excel窗体结构,系统按钮,主菜单栏,常用工具栏,编辑栏,格式设定工具栏,程序标题栏,工作簿控制按钮,垂直滚动条,水平滚动条,工作表标签,工作表格区,状态栏,行标,列标,工作簿标签添加按钮,我们先来解决几个问题?,当使用您Excel2010以上的版本，给Excel2010以下版本使用者传文件时，是否会经常碰到对方无法打开所传送的文件?,Q1：,当您在使用Excel想给您的知识产权如何上锁保护?,Q2:,如何让您的下属在您设计的图表里进行机械式的操作?,Q3:,如何给工具栏里增添更多的功能?,Q4:,修改默认文件保存路径?,Q5:,Excel选项操作,1、录入按Enter键后移动所选内容:默认”向下“、其他三个”向上、向右、向左”。,2、滚动条的设置。,3、显示行与列标。,4、在单元格显示公式而非计算结果。,5、显示网格线及网格线颜色。,6、默认的字体及字号。,工作簿基本操作,隐藏取消隐藏,工作表标签颜色,保护工作簿,查看代码,移动或复制工作簿,重命名,插入,在工作簿名称”sheet”处点击鼠标右键。,1,2,3,4,5,6,7,自定义名称功能,创建定义名称的方法,一、使用公式栏左边的名称框,二、使用“定义名称”对话框,三、用行或列标志创建名称,四、定义三维名称,五、定义外部名称,六、常量定义名称,定义名称的使用方法,减少输入的工作量,在一个公式中出现多次相同的字段,超出某些公式的嵌套,EXCEL函数中使用自定义名称,七、动态定义名称,单元格基本操作,文字方向,自动换行,粘贴选项,批注使用,正90度,负90度,0度,横排自动换行,竖排自动换行,粘贴公式,粘贴数值,转置,粘贴批注,粘贴格式,在批注中插入图片,批注隐藏显示编辑,数值类型-自定义,提供了许多选项以便将数字显示为百分比、货币、日期等。如果这些内置格式无法满足您的需要，您可以自定义内置数字格式以便创建自己的数字格式。,数值类型,1.巧变文本为数字,在工作中，发现一些通过文本文件或其它财务软件的数据导入Excel中后居然是以文本形式存在的(数字默认是右对齐，而文本是左对齐的)。,2.将数字设为文本格式,Excel都会将数字保存为数字数据。若要使Excel将类似零件号码的数字解释为文本，首先应将空白单元格设置成文本格式，然后键入数字。,操作:在空白的单元格中填人数字1，然后选中这个单元格，执行“复制”命令，然后再选中所要转换的范围，选择“选择性粘贴”中的“乘”，它们都变为数字了。,方法是: 1.选择含有要设置成文本格式的数字单元格; 2.单击“格式单元格”命令，然后单击“数字”选项卡（）; 3.在“分类”列表中，单击“文本”，然后再单击“确定”; 4.单击每个单元格，按F2键，然后再按Enter键重新输入数据,文本导入Excel,1、在记事本中输入格式化文本，每个数据项之间会被空格隔开，也可以用逗号、分号、Tab键作为分隔符。,2.在Excel中打开刚才保存的文本文件，出现“文本导入向导-3步骤之1”对话框，选择“分隔符号”，单击“下一步”;,3.在“文本导人向导-3步骤之2”对话框中选择文本数据项分隔符号，Excel提供了Tab键、分号、逗号以及空格等供你选择。,4.在“文本导人向导-3步骤之3”对话框中，你可以设置数据的类型，单击“完成”按钮即可 。,常规按键使用,超级链接,超级链接是指从一个网页指向一个目标的连接关系,这个目标可以是另一个网页,也可以是相同网页上的不同位置，还可以是一个图片,一个电子邮件地址,一个文件,甚至是一个应用程序.而在一个网页中被用来超链接的对象,可以是一段文本或者是一个图片.当浏览者单击已经链接的文字或图片后,链接目标将显示在浏览器上,并且根据目标的类型来打开或运行。这是一种跟一般链接不一样的链接方式，能带给我们更方便和快捷的服务,现有文件或网页,本文档中的位置,条件格式,开始,样式,条件格式,突出显示单元格规则,项目选取规则,数据条,色阶,图标集,单元格样式,套用表格格式,Excel提供了自动格式化的功能，它可以根据预设的格式，将我们制作的报表格式化，产生美观的报表，也就是表格的自动套用。,相对引用绝对引用混合引用,快速填充,快速填充:默认情况下处于打开状态并在感知一种模式时自动填充您的数据,2013版新增功能。,单击“文件”“选项”。,2.单击“高级”，确保“自动快速填充”框被选中。,截取功能,合并功能,Ctrl+E,数据快速填充,如何启用？,功能?,快捷操作?,“MID”,“&”,Excel插件的应用,图表的应用,动态列表与数据透视表,数据分析与处理,与Word，PPT的结合使用,Vlookup/Hlookup,查找函数 功能：给出首列的值，返回其他指定列的值。 格式：=VLOOKUP(已知条件，所在区域，查找的列号，匹配类型) 注意：给出首列的值必须是选择数据区域里的第一列。跨表引用，跨工作薄引用,MID,TEXT,SUBSTITUTE,IF,条件函数 功能：判断条件是否满足，如果满足给出一个值，不满足给出另一个值。 格式：=IF(判断的条件，条件成立的值，条件不成立的值) 注意：可以嵌套最多64个IF。,SUMIF/SUMIFS,条件求和 功能：对多个满足条件的单元格求和。可结合数组使用。 格式：=SUMIFS(求和的区域，条件1的区域，条件1，条件2的区域，条件2，) 注意：各个参数的前后顺序。,COUNTIF/COUNTIFS,条件计数 功能：统计满足条件的单元格数目。 格式：=COUNTIFS(条件1的区域，条件1，条件2的区域，条件2，) 注意：各个参数的前后顺序。,RANK,排位函数 功能：当前数据在一组数据中的排名。 格式：=RANK(比较的数值，所在的区域，降序或升级) 注意：单元格的引用，区域使用定义名称或绝对引用。,DAY,TODAY,NOW,SUBTOTAL,分类汇总 功能：返回列表或数据库中的分类汇总。 格式：=SUBTOTAL(比较的数值，所在的区域，降序或升级) 注意：单元格的引用，区域使用定义名称或绝对引用,常用的财务函数,投资计算函数 折旧计算函数 偿还率计算函数 债券及其他金融函数,一、Excel在现金核算中的应用,手工会计核算下，出纳应按经济业务发生的时间先后顺序，随时登记日记账，并做到日清月结，即每日现金日记账都应结出结存额，（每日小计），以便同实有库存现金进行核对，账款相符；每月应该结账，与会计进行账簿记录的核对。,Excel在会计核算和管理中的应用实务,二、数据库,1、定义“范围名称” 实例：同时打开工作簿“日记账与会计科目”与“公司基本情况”，并进行窗口重排（水平）,下一步,日记账与会计科目,公司基本情况,窗口重排,切换到“日记账与会计科目”工作表,上一步,下一步,在此键入“name”,打开引用位置,上一步,下一步,点击返回， 并“确定”,选择“公司基本情况” 工作表的d2单元,上一步,下一步,输入“=name”,说明：当输入=name回车后，H1变成了公司名称。 这样做有什么好处呢？ 提示：当更换公司名称时怎么做？,上一步,2、concatenate()函数 用途：将几个单元格的数据合并成一个单元格数据。 语法：concatenate(text1,text2) 说明：将数个字符串连成一个字符串。是一个文本类的 函数 例：打开“日记账及会计科目”工作表,在F6单元内，插入 Concatenate函数,上一步,下一步,上一步,下一步,选中B:E整列 设置单元格格式,上一步,下一步,会计所需的格式,上一步,3、自动显示会计科目 录入科目代码，自动显示会计科目 范例：打开“日记账和会计科目”工作表,先依照上例定义范围名称“会计科目”，引用位置为“会计科目！$B$4:$C$146”,下一步,打开“日记账和会计科目”工作表,选中H6单元， 插入IF函数,上一步,下一步,G6=“”,选择函数VLOOLUP(),“”,上一步,下一步,G6,插入名称粘贴科目名称,2,上一步,下一步,说明：H6单元格的公式“=IF(G6=”“,”“,VLOOKUP(G6,会计科目,2,1)”，表示当G6单元格为空格时，H6单元格也显示空格，如果G6单元格为非空格时，则以G6单元格的数据去引用“会计科目”这个范围名称，找出完全符合G6单元格的数据，并回传第二行的值到H6单元格。,上一步,4、插入批注 将重要事宜提请使用者注意,选中H6单元,下一步,在批注上点击 鼠标右键，可 设置其格式。 如字体、字号、 颜色、线条等,上一步,5、自动显示异常信息 有借必有贷，借贷必相等，我们可利用IF（）函数和SUM（）函数，提醒借贷是否平衡 范例：打开“日记账和会计科目”工作表 在C3中输入函数： =IF（SUM（I:I）=SUM（J:J）,“”,“借贷不平衡”）,6、冻结窗口 窗口冻结窗口 范例：打开“日记账和会计科目”工作表 激活工作表的第6行，执行窗口冻结窗口,下一步,被固定,上一步,直线折旧法函数SLN(),SLN（cost,salvage,life） Cost:资产原值 Salvage:资产在折旧期末的价值（资产净值） Life：折旧期限 例：某企业购买了一辆价值30000元的卡车，其折旧年限为10年，残值为7500元，则每年的折旧额为 SLN（30000，7500，10）=2250元,余额递减法函数DB(),DB（cost,salvage,life,period,month） Cost:资产原值 Salvage:资产在折旧期末的价值（资产净值） Life：折旧期限 Period:为需要计算折旧期末的价值，必须使用与life相同的单位 Month:第一年的月份数，如省略，则假设为12 例：某企业购买了一台新机器。价值为1000000元，使用期限为6年。残值为100000元。 DB(1000000,100000,6,1)=319000元 其余，略，可用绝对引用公式,双倍余额递减法函数DDB(),DDB（cost,salvage,life,period,factor） Cost:资产原值 Salvage:资产在折旧期末的价值（资产净值） Life：折旧期限 Period:为需要计算折旧期末的价值，必须使用与life相同的单位 factor:为余额递减速率。如果factor被省略，则假设为2 例：某企业购买了一台新机器。价值为2400元，使用期限为10年。残值为300元。 DB(2400,300,10,1)=480元 DB(2400,300,10,10)=22.12元 其余，略，可用绝对引用公式,可变余额递减法函数VDB(),VDB（cost,salvage,life,start_period,end_period,factor,no_switch） Cost:资产原值 Salvage:资产在折旧期末的价值（资产净值） Life：折旧期限 Start_period:为进行折旧计算的起始期次，必须使用与life相同的单位 End_period:为进行折旧计算的截止期次，必须使用与life相同的单位 No_switch为一逻辑值，指定当折旧值大于余额递减计算值时，是否转到直线折旧法。 TURE，则不转换，FALSE或省略，则要转换 例：某企业购买了一台新机器。价值为2400元，使用期限为10年。残值为300元。 VDB(2400,300,10,0,1)=480元, VDB(2400,300,120,0,1=40元 VDB(2400,300,120,6,18)=396.31，是第6到18个月的折旧值,年限总和折旧法函数SYD(),SYD（cost,salvage,life, per） Cost:资产原值 Salvage:资产在折旧期末的价值（资产净值） Life：折旧期限 Per:期间，其单位与LIFE相同 例：某企业购买了一台辆卡车，价值为30000，使用期限为10年。残值为7500元。 第1年的折旧额为：SYD(3000,7500,10,1)=4090.91元, 第5年的折旧值为：SYD(3000,7500,10,5)=2454.55元,年金函数PMT(),PMT(rate,nper,pv,fv,type) 其中:pmt_本金和利息; pv_现值;fv_未来值 例1:10个月付清的年利率为8%的10000贷款,则每月支付额 pmt(8%/12,10,10000)=-1037.03 例2:如果需要以按月定额存款方式在18年中存款50000元,假设存款年利率为6%,则月存款额 Pmt(6%/12,18*12,0,50000)=-129.08 例3:如果以12%的利率贷出5000元,并希望对方在5个月内还清,则每月所得款数 Pmt(12%/12,5,-5000)=1030.20 请计算1:如果贷款10万元买房,年利率为4%,5年还清,每月的还款额为多少?第1年的还款额为多少？ 计算2：甲公司2004年1月1日从工商银行借入100万元，年利率为6.4%，每年还款次数为2次，分别是6月底和年底等额偿还，期限为4年。请计算其每次还款的金额。143660.7621,利息函数IPMT(),IPMT(rate,per,nper,pv,fv,type) 其中:Ipmt_利息; pv_现值;fv_未来值 例1：三年期，本金8000，年利率10%的银行贷款的第1个月的利息 ipmt(0.1%/12,1,3*12,8000)=-66.67 例2:三年期，本金8000，年利率10%,且按年支付的银行贷款的第3年的利息 ipmt(0.1%,3,3