EXCEL在财务中的应用.pptx

《EXCEL在财务中的应用.pptx》由会员分享，可在线阅读，更多相关《EXCEL在财务中的应用.pptx（68页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel在财务中的应用我们先来认识Excel软件最好的老师是谁?请打开Microsoft Office Excel 任何版本1请点击Excel右上角“?”-“必须联网”2认识Excel窗体结构系统按钮主菜单栏常用工具栏编辑栏格式设定工具栏程序标题栏工作簿控制按钮垂直滚动条水平滚动条工作表标签工作表格区状态栏行标列标工作簿标签添加按钮我们先来解决几个问题?当使用您Excel2010以上的版本，给Excel2010以下版本使用者传文件时，是否会经常碰到对方无法打开所传送的文件?Q1：当您在使用Excel想给您的知识产权如何上锁保护?Q2:如何让您的下属在您设计的图表里进行机械式的操作?Q3:如何

2、给工具栏里增添更多的功能?Q4:修改默认文件保存路径?Q5:Excel选项操作1、录入按Enter键后移动所选内容:默认”向下“、其他三个”向上、向右、向左”。2、滚动条的设置。3、显示行与列标。4、在单元格显示公式而非计算结果。5、显示网格线及网格线颜色。6、默认的字体及字号。工作簿基本操作隐藏取消隐藏工作表标签颜色保护工作簿查看代码移动或复制工作簿重命名插入在工作簿名在工作簿名称称”sheet”处点击鼠标处点击鼠标右键。右键。1234567自定义名称功能创建定义名称的方法一、使用公式栏左边的名称框二、使用“定义名称”对话框三、用行或列标志创建名称四、定义三维名称五、定义外部名称六、常量定义

3、名称定义名称的使用方法减少输入的工作量减少输入的工作量在一个公式中出现多次相同的字段在一个公式中出现多次相同的字段 超出某些公式的嵌套超出某些公式的嵌套EXCELEXCEL函数中使用函数中使用自定义自定义名称名称七、动态定义名称单元格基本操作文字方向自动换行粘贴选项批注使用正正90度度负90度度0度度横排自横排自动换行行竖排自排自动换行行 粘粘贴公式公式 粘粘贴数数值 转置置 粘粘贴批注批注 粘粘贴格式格式 在批注中插入在批注中插入图片片 批注批注隐藏藏 显示示 编辑 数值类型-自定义自定义提供了许多选项以便将数字显示为百分比、货币、日期等。如果这些内置格式无法满足您的需要，您可以自定义内置数

4、字格式以便创建自己的数字格式。0（零）如果数字的位数少于格式中的零的个数，则此数字占位符会显示无效零。例如，如果键入 8.9，但希望将其显示为 8.90，请使用格式#.00。#此数字占位符所遵循的规则与 0（零）相同。但是，如果所键入数字的小数点任一侧的位数小于格式中#符号的个数，则 Excel 不会显示多余的零。例如，如果自定义格式为#.#，而在单元格中键入了 8.9，则会显示数字 8.9。?此数字占位符所遵循的规则与 0（零）相同。但 Excel 会为小数点任一侧的无效零添加空格，以便使列中的小数点对齐。例如，自定义格式 0.0?将列中数字 8.9 和 88.99 的小数点对齐。（句点）此

5、数字占位符在数字中显示小数点。m 将月显示为不带前导零的数字。mm 根据需要将月显示为带前导零的数字。mmm将月显示为缩写形式（Jan 到 Dec）。mmmm将月显示为完整名称（January 到 December）。mmmmm 将月显示为单个字母（J 到 D）。d将日显示为不带前导零的数字。dd 根据需要将日显示为带前导零的数字。ddd将日显示为缩写形式（Sun 到 Sat）。dddd将日显示为完整名称（Sunday 到 Saturday）。yy将年显示为两位数字。yyyy将年显示为四位数字。数值类型1.巧巧变变文本文本为为数字数字在工作中，发现一些通过文本文件或其它财务软件的数据导入Exc

6、el中后居然是以文本形式存在的(数字默认是右对齐，而文本是左对齐的)。2.将数字将数字设为设为文本格式文本格式Excel都会将数字保存为数字数据。若要使Excel将类似零件号码的数字解释为文本，首先应首先应将空白单元格设置成文本格式将空白单元格设置成文本格式，然后键入数字然后键入数字。操作:在空白的单元格中填人数字1，然后选中这个单元格，执行“复制”命令，然后再选中所要转换的范围，选择“选择性粘贴”中的“乘”，它们都变为数字了。方法是:1.选择含有要设置成文本格式的数字单元格;2.单击“格式单元格”命令，然后单击“数字”选项卡（）;3.在“分类”列表中，单击“文本”，然后再单击“确定”;4.单

7、击每个单元格，按F2键，然后再按Enter键重新输入数据文本导入Excel1、在记事本中输入格式化文本，每个数据项之间会被空格隔开，也可以用逗号、分号、Tab键作为分隔符。2.在Excel中打开刚才保存的文本文件，出现“文本导入向导-3步骤之1”对话框，选择“分隔符号”，单击“下一步”;3.在“文本导人向导-3步骤之2”对话框中选择文本数据项分隔符号，Excel提供了Tab键、分号、逗号以及空格等供你选择。4.在“文本导人向导-3步骤之3”对话框中，你可以设置数据的类型，单击“完成”按钮即可。常规按键使用按键名称按键功能Shift+ctrl+*选择当前数据区域 Ctrl+tab可在打开的工作簿

8、间切换Ctrl+Shift+Home 将选定区域扩展到工作表的开始处F4可以重复前一次操作Ctrl+(或Ctrl+)取消隐藏选定范围内所有隐藏的行或列Ctrl+Alt+Shift+F9重新检查相关公式并计算所有单元格Ctrl+Shift+O选择所有包含批注的单元格超级链接超级链接是指从一个网页指向一个目标的连接关系,这个目标可以是另一个网页,也可以是相同网页上的不同位置，还可以是一个图片,一个电子邮件地址,一个文件,甚至是一个应用程序.而在一个网页中被用来超链接的对象,可以是一段文本或者是一个图片.当浏览者单击已经链接的文字或图片后,链接目标将显示在浏览器上,并且根据目标的类型来打开或运行。这

9、是一种跟一般链接不一样的链接方式，能带给我们更方便和快捷的服务现有文件或网页本文档中的位置条件格式开始开始样式式条件格式条件格式突出突出显示示单元格元格规则项目目选取取规则数据条数据条色色阶图标集集单元格样式套用表格格式Excel提供了自动格式化的功能，它可以根据预设的格式，将我们制作的报表格式化，产生美观的报表，也就是表格的自动套用。相对引用绝对引用混合引用快速填充快速填充:默认情况下处于打开状态并在感知一种模式时自动填充您的数据,2013版新增功能。1.单击“文件”“选项”。2.单击“高级”，确保“自动快速填充”框被选中。截取功能合并功能Ctrl+E数据快速填充如何启用？功能?快捷操作?“

10、MID”“&”Excel插件的应用图表的应用动态列表与数据透视表数据分析与处理与Word，PPT的结合使用Vlookup/Hlookup 查找查找函数函数 功能：给出首列的值，返回其他指定列的值。功能：给出首列的值，返回其他指定列的值。格式：格式：=VLOOKUP(已知条件，所在区域，查找的列号，匹配类型已知条件，所在区域，查找的列号，匹配类型)注意：给出首列的值必须是选择数据区域里的第一列注意：给出首列的值必须是选择数据区域里的第一列。跨。跨表引用，跨工作表引用，跨工作薄引用薄引用MIDTEXTSUBSTITUTEIF 条件条件函数函数 功能：判断条件是否满足，如果满足给出一个值，不满足给出

11、另功能：判断条件是否满足，如果满足给出一个值，不满足给出另一个值。一个值。格式：格式：=IF(判断的条件，条件成立的值，条件不成立的值判断的条件，条件成立的值，条件不成立的值)注意：可以嵌套最多注意：可以嵌套最多64个个IF。SUMIF/SUMIFS 条件条件求和求和 功能：功能：对多个满足条件的单元格求和。可结合数组使用。对多个满足条件的单元格求和。可结合数组使用。格式：格式：=SUMIFS(求和的区域，条件求和的区域，条件1的区域，条件的区域，条件1，条件，条件2的区域，条件的区域，条件2，)注意：各个参数的前后顺序。注意：各个参数的前后顺序。COUNTIF/COUNTIFS 条件条件计数

12、计数 功能：统计满足条件的单元格数目。功能：统计满足条件的单元格数目。格式：格式：=COUNTIFS(条件条件1的区域，条件的区域，条件1，条件，条件2的区域，条件的区域，条件2，)注意：各个参数的前后顺序。注意：各个参数的前后顺序。RANK 排位排位函数函数 功能：当前数据在一组数据中的排名功能：当前数据在一组数据中的排名。格式：格式：=RANK(比较的数值，所在的区域，降序或升级比较的数值，所在的区域，降序或升级)注意：单元格的引用，区域使用定义名称或绝对引用。注意：单元格的引用，区域使用定义名称或绝对引用。DAYTODAYNOWSUBTOTAL分类汇总功能功能：返回列表或数据库中的分类汇

13、总。格式：格式：=SUBTOTAL(比较的数值，所在的区域，降序或升级比较的数值，所在的区域，降序或升级)注意：单元格的引用，区域使用定义名称或绝对引用注意：单元格的引用，区域使用定义名称或绝对引用Function_num（包含隐藏值）包含隐藏值）Function_num（忽略隐藏值）忽略隐藏值）函数函数1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP常用的财务函数投资计算函数折旧计算函数偿还率计算函数债券及其他金融函数一、Excel在现金核算中的

14、应用 手工会计核算下，出纳应按经济业务发生的时间先后顺序，随时登记日记账，并做到日清月结，即每日现金日记账都应结出结存额，（每日小计），以便同实有库存现金进行核对，账款相符；每月应该结账，与会计进行账簿记录的核对。Excel在会计核算和管理中的应用实务二、数据库 1、定义“范围名称”实例：同时打开工作簿“日记账与会计科目”与“公司基本情况”，并进行窗口重排（水平）下一步日记账与会计科目公司基本情况窗口重排切换到“日记账与会计科目”工作表上一步下一步在此键入“name”打开引用位置上一步下一步点击返回，并“确定”选择“公司基本情况”工作表的d2单元上一步下一步输入“=name”n说明：当输入=n

15、ame回车后，H1变成了公司名称。n这样做有什么好处呢？n提示：当更换公司名称时怎么做？上一步 2、concatenate()函数用途：将几个单元格的数据合并成一个单元格数据。语法：concatenate(text1,text2)说明：将数个字符串连成一个字符串。是一个文本类的 函数例：打开“日记账及会计科目”工作表在F6单元内，插入Concatenate函数上一步下一步上一步下一步选中B:E整列设置单元格格式上一步下一步会计所需的格式上一步 3、自动显示会计科目录入科目代码，自动显示会计科目范例：打开“日记账和会计科目”工作表,先依照上例定义范围名称“会计科目”，引用位置为“会计科目！$B$

16、4:$C$146”下一步打开“日记账和会计科目”工作表选中H6单元，插入IF函数上一步下一步G6=“”选择函数VLOOLUP()“”上一步下一步G6插入名称粘贴科目名称2上一步下一步n说明：H6单元格的公式“=IF(G6=”“,”“,VLOOKUP(G6,会计科目,2,1)”，表示当G6单元格为空格时，H6单元格也显示空格，如果G6单元格为非空格时，则以G6单元格的数据去引用“会计科目”这个范围名称，找出完全符合G6单元格的数据，并回传第二行的值到H6单元格。上一步 4、插入批注将重要事宜提请使用者注意选中H6单元下一步在批注上点击鼠标右键，可设置其格式。如字体、字号、颜色、线条等上一步 5、

17、自动显示异常信息有借必有贷，借贷必相等，我们可利用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

18、，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）C

19、ost:资产原值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:资产原值Salvag

20、e:资产在折旧期末的价值（资产净值）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个月的折

21、旧值 年限总和折旧法函数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

22、贷款,则每月支付额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

23、年。请计算其每次还款的金额。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,8000)=-292.45 请计算1：如果贷款10万元买房,年利率为4%,5年还清,第1个月的利息为多少?第1年的利息为多少？本金函数PPMT()PPMT(rate,per,nper,p

24、v,fv,type)其中:Ppmt_本金;pv_现值;fv_未来值 例1：2000元，年利率为10%的两年期贷款的第一个月的本金支付额Ppmt(10%/12,1,2*12,2000)=-75.62 例2:20000元的年利率8%的十年期贷款的最后一年的本金支付额Ppmt(8%,10,10,20000)=-27598.05 请计算：如果贷款10万元买房,年利率为4%,5年还清,第1个月的本金支付额为多少?第1年的本金支付额为多少？注意三者关系为：PMT()=PPMT()+IPMT()利率函数NPER()RATE(nper,pmt,pv,fv,type,guess)说明：guess为预期利率（估计值），若省略，则假设该值为10%例1：金额为8000元的4年期贷款，月支付额为200元，该笔贷款的利率为RATE(4*12,-200,8000)=0.77%月利为0.77/12=9.24%期数函数NPER()NPER(rate,pmt,pv,fv,type)返回某项投资（或贷款）的总期数 例1：金额为36000元的贷款，年利率为8%，每年年末支付金额为9016元，计算需要多少年支付完。NPER(8%,9016,-36000)=-5年