《Excel在财务中的应用》ppt课件——案例实训.pptx

《《Excel在财务中的应用》ppt课件——案例实训.pptx》由会员分享，可在线阅读，更多相关《《Excel在财务中的应用》ppt课件——案例实训.pptx（278页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、表表操作与技巧操作与技巧数据透视表数据透视表函数与公式函数与公式12430001020304050607图表图表56VBA目录目录Excel 在财务中的应用 实训篇ExcelExcel的内容的内容2VBA编程VBA编程编程透视表透视表透视表函数、图表函数、图表函数、图表基本操作基本操作基本操作场景1Excel+计算器场景2Excel取代计算器场景3单元格表间运算场景4Vlookup+sumif场景6Excel+数据库场景5高级公式+透视表.我们处于哪个场景？我们处于哪个场景？初级用户中级用户高级用户专家新手您希望成为哪个级别？您希望成为哪个级别？No.1 目标目标不少于不少于30个函数；精绘一

2、幅动态图表；学一天个函数；精绘一幅动态图表；学一天VBA入门；入门；No.2 方法方法表格表格函数函数图表图表VBANo.3目标目标 .“信息化信息化+核算核算+税务税务+管理管理”!给自己树立一个目标！给自己树立一个目标！教学工具：启用宏；双击教学工具：启用宏；双击A1A1单元格；单元格；自动评分纠错；双击答案提示；自动评分纠错；双击答案提示；答题区提示答题区提示案例特征：原理案例特征：原理+应用，多项知识点应用，多项知识点计算各科室的平均工作津贴计算各科室的平均工作津贴单纯的表格VSSUM+AVERAGEIF+单元格区域名称定义与引用+工作表保护的表格学习目标：函数与公式不少于30个；学习

3、目标：n数据透视表，善用数据透视表11学习目标：精绘动态图表让你的图表会说话拓展提升：用一天时间学习VBA入门3讲，能实现数据自动化处理;12讲,能编制出日常办公小程序；人机对话的语言；是非计算机专业的工作人员最容易学习的语言；首先，介绍7个常用的VBA词汇；实训目的实训目的l掌握FIND函数的应用；l掌握相对引用、混合引用、绝对引用的f4快捷键的应用l掌握IFERROR函数的应用；l掌握双击“+”快速复制公式到末行的技巧；Find函数在招生分析工作中的应用（原理篇）函数在招生分析工作中的应用（原理篇）实训要求实训要求l制作一个能依据专业关键字的招生查询工具，即在4单元格录入专业名称关键字（如

4、会计），即可按专业查询，只要能达到预期效果即可。实训资料实训资料l07 2018年招生专业分析FIND函数.xlsm操作向导操作向导l用FIND函数查找字符串（专业名称）的位置；l用F4快捷键进行快速的单元格引用方式切换；l用IFERROR函数不显示出错的信息；l=IFERROR(FIND($H$4,D5,1),)l双击“+”快速复制公式到末行；FIND函数函数nFIND(find_text,within_text,start_num)说明说明l函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。l函数 F

5、IND 始终将每个字符（不管是单字节还是双字节）按 1 计数。语法语法lFIND(find_text,within_text,start_num)find_text 必需。要查找的文本。within_text 必需。包含要查找文本的文本。start_num 可选。指定开始进行查找的字符。within_text 中的首字符是编号为 1 的字符。如果省略 start_num，则假定其值为 1。备注备注lFIND 和 FINDB 区分大小写，并且不允许使用通配符。如果您不希望执行区分大小写的搜索或使用通配符，则可以使用 SEARCH 和 SEARCHB 函数。Find_text 不能包含任何通配符。

6、如果 within_text 中没有 find_text，则 FIND 和 FINDB 返回 错误值#VALUE!。数据数据lMiriam McGovern=FIND(M,A2)n单元格 A2 中第一个“M”的位置n1=FIND(m,A2)n单元格 A2 中第一个“M”的位置n6（所以，它是区分大小写的）=FIND(M,A2,3)n从单元格 A2 的第三个字符开始查找第一个“M”的位置n8一、实训目的一、实训目的l掌握TRIM函数的应用l掌握SUBSTITUTE函数的应用脏数据的清理脏数据的清理清除数据中空格（原理篇）清除数据中空格（原理篇）二、实训要求二、实训要求l *承兑 21987329

7、 付苏州石湖地产有限公司 12l*承兑 21987329 付苏州石湖地产有限公司 13请截取类似文本中承兑号码（字符中有“”文本）。三、实训资料三、实训资料l08 trim函数与substitue函数在数据清理中的应用(原理篇）.xlsm四、操作向导四、操作向导l用TRIM函数清除空格l用SUBSTITUTE替换空格l数据清理后的截取承兑号码找到兑字的位置根据承兑号码长度进行截取TRIM函数函数l除了单词之间的单个空格之外，移除文本中的所有空格。对于从另一个可能含有不规则间距的应用程序收到的文本，可以使用 TRIM。比如用财务软件中导出的数据。语法语法lTRIM(text)=TRIM(Firs

8、t Quarter Earnings)从公式的文本中移除前导空格和尾随空格(First Quarter Earnings)SUBSTITUTE 函数函数l在文本字符串中用 new_text 替换 old_text。语法语法lSUBSTITUTE(text,old_text,new_text,instance_num)Instance_num 可选。指定要用 new_text 替换 old_text 的事件。如果指定了 instance_num，则只有满足要求的 old_text 被替换。否则，文本中出现的所有 old_text 都会更改为 new_text。l 如果14的值为：*承兑 2198

9、7329 付苏州石湖地产有限公司 13=SUBSTITUTE(A14,)*承兑21987329付苏州石湖地产有限公司13一、实训资料一、实训资料l一份农村“三资”清查的工作簿，它由28张不同的工作表组成。lCTRL+SHIFT+PAGEUP键Ctrl+Shift+Pageup(Pagedown)Ctrl+Shift+Pageup(Pagedown)的应用的应用二、实训目标二、实训目标l掌握CTRL+”+”;CTRL+”-”的应用；l理解CTRL+ENTER的应用l掌握CTRL+PAGEUP(DOWN)的应用；（上下切换工作表）l掌握CTRL+SHIFT+PAGEUP(DOWN)的应用；(连选多

10、张工作表）三、实训要求三、实训要求l1.快速地检查第3行与第4行是否空行；l2.用CTRL+”-”删除第3行；l3.用CTRL+”+”插入1行；l4.连选N张工作表，统一在A7单元格中录入单位信息；一、实训要求一、实训要求l本次操作是通过条件格式中的新建规则，利用公式对某学校的教师考核表中符合规则（如教学业绩分大于600分，专业建设分大于100分，科研分大于100分，育人分大于50分）的教师信息用淡绿色标出。条件格式条件格式新建规则的操作新建规则的操作二、实训目的二、实训目的l条件格式中的新建规则的建立、管理与清除；l理解AND函数的应用；l掌握F4单元格引用切换键的应用；l掌握CTRL+SH

11、IFT+箭头快捷键的应用；三、操作向导三、操作向导l好，本次课就讲解到这里。请您到网站下载相关的学习资料，及时完成课后作业，理解并巩固所学的知识与技能。再见！TRUNC(number,num_digits)nNumberNumber必需。必需。需要截尾取整的数字。需要截尾取整的数字。nnum_digits num_digits 可选。可选。用于指定取整精度的数字。用于指定取整精度的数字。nnum_digits num_digits 的默认值为的默认值为 0 0（零）。（零）。TRUNC 函数与INT函数的区别nTRUNC TRUNC 和和 INT INT 的相似之处在于两者都返回整数。的相似之

12、处在于两者都返回整数。nTRUNC TRUNC 删除数字的小数部分。删除数字的小数部分。nINT INT 根据数字小数部分的值将该数字向下舍入为最接近的整数。根据数字小数部分的值将该数字向下舍入为最接近的整数。测试nTRUNC(-4.3)TRUNC(-4.3)l返回返回 -4-4nINT(-4.3)INT(-4.3)返回返回 -5-5l因为因为 -5-5 是更小的数字。是更小的数字。n=TRUNC(8.9)=TRUNC(8.9)l将将 8.9 8.9 截尾取整截尾取整 (8)(8)n=TRUNC(-8.9)=TRUNC(-8.9)l将负数截尾取整并返回整数部分将负数截尾取整并返回整数部分 (-

13、8)(-8)n=TRUNC(0.45)=TRUNC(0.45)l将将 0 0 和和 1 1 之间的数字截尾取整，并返回整数部分之间的数字截尾取整，并返回整数部分 (0)(0)。实训目的实训目的n掌握数据分列的操作n掌握数据分列线的设置n掌握CTRL+1快捷方式的应用数据分列的操作数据分列的操作实训资料实训资料n江南公司的某员工的信用卡流水记录，如图11-31所示。为了便于对交易金额进行分类统计，需要对E列中的交易金额、币符、收支类型进行分离，该如何操作呢？n信用卡流水记录操作向导操作向导n一、分析数据特征l对需要分列的单元格中的数据进行分析，发现它们均有“/”。因此，可以根据“/”进行分离。n

14、二、选择数据分列功能n三、设置分隔符号同步训练同步训练n为了日后统计收支类型金额的需要，我们需要对分列到F列的收支类型与币种信息分离，仍可使用分列功能。从图11-35中可以看出，由于数据具有规律性，因此可以“固定宽度”中的分列线进行数据分列，分列后的数据如图11-35所示。n设置分列线n分列后的收支类型数据同步训练同步训练n为了让数值显示更规范，需要将小数设置为2位数。选择E列，按CTRL+1键打开设置单元格格式窗口，选择数值，将小数位设置为2位数，单击“确定”。如图11-37所示。同步训练同步训练n请对江南公司的银行流水记录中的电子工作表中的金额栏中的数据进行大写与小写金额进行分离。如图11

15、-38所示。n银行流水记录操作提示：操作提示：n1.在D列之前用快捷方式CTRL+”+”插入一列；n2.请将C列的大写金额与小写金额用数据分列功能分离出来；n3.用CTRL+H键打开查找替换窗口将“元”字删除，按CTRL+1打开设置单元格式窗口将小数位保留2位数；实训目的实训目的n掌握CTRL+A、CTRL+H、F4等快捷操作；n掌握绝对引用、混合引用、相对引用在工作中的应用；n掌握ROUND函数的应用；n掌握嵌套公式的编辑技巧；单元格的引用单元格的引用实训资料实训资料n根据东南公司的2014年度到2018年度的营业收入分别计算出以2014年度为基准的定基动态比率与环比动态比率。相关数据如图1

16、1-39所示。n东南公司的销售数据操作向导操作向导n一、不同的引用方式n（一）单元格引用n单元格的引用包括相对引用、绝对引用和混合引用三种。1、相对引用、相对引用 n公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置，被引用单元格的列标和行标前不加任何标示性符号。2、绝对引用、绝对引用 n公式中的绝对单元格引用（例如$A$1）不论包含公式的单元格处在什么位置，公式中所引用的单元格位置都是其工作表的确切位置。单元格的绝对引用通过在行标和列标前加一个美元符号“$”来表示，如$A$1、$B$2，以此类推。3、混合引用、混合引用n混合引用具有绝对列和相对行，或是绝对行和相

17、对列。绝对引用列采用$A1、$B1 等形式，绝对引用行采用 A$1、B$1 等形式。4、不同引用之间的快速切换、不同引用之间的快速切换n在Excel中输入公式时，只要正确使用F4键，就能快速地对单元格从相对引用到绝对引用、混合引用方式进行切换。l比如在某单元格输入公式“=AVERAGE(B4：B8)”。选中数据区域B4：B8，按下F4键，该公式内容变为“=AVERAGE($B$4：$B$8)”，相对引用变成了绝对引用。再按一次F4键，它将切换到另一种引用方式。（二）引用同一工作簿中的其它工作表（二）引用同一工作簿中的其它工作表n引用同一工作簿中的其它工作表时格式如下：l被引用的工作表！被引用的

18、单元格。n“=Sheet1!A8”（三）引用其它工作簿中的工作表（三）引用其它工作簿中的工作表n 引用不同工作簿中的其它工作表时格式如下：l被引用的工作簿名称被引用的工作表！被引用的单元格l“=Book2Sheet2!$F$9”。n用用CTRL+H键快速的查找与替换空格的字符键快速的查找与替换空格的字符将表中含有空格的字符串全部清理完毕，清理后的数据便于计算。如图11-41所示。采用绝对引用方式计算定基动态比率采用绝对引用方式计算定基动态比率l选择F5单元格，在单元格中录入“=F5/F5”，然后，选择分母中的“F5”，按住F4键，将相对引用（F5）快速的切换为绝对引用$F$5方式，按ENTER

19、键回车，完成基期指标的计算。n通过通过ROUND函数保留两位小数。函数保留两位小数。以相对引用方式计算环比动态比率以相对引用方式计算环比动态比率n选择F5单元格，在单元格中录入“=ROUND(E5/F5,2)”，按ENTER键回车，然后选择E7单元格将光标变为“+”的时候，将公式向左一直复制到B7单元格，完成了环比动态比率的计算。如图11-44所示：同步训练同步训练n编制复利现值系数表n编制复利终值系数表实训目的实训目的n掌握用快捷键清除单元格格式的方法n掌握CTRL+1CTRL+1、C CTRL+ATRL+A、ALT+E+A+FALT+E+A+F键的应用n掌握行高列宽的便捷调整方式Excel

20、快捷键的应用（一）快捷键的应用（一）实训资料实训资料n江南公司的财务人员在网络上复制了一份个人所得税税率表，会计人员计划清除表格当中的数据格式。然后将字体设置为“华文中宋”，字号12，加粗，B列自动调整，A列，C列，D列的列宽为10，字体的颜色默认。操作向导操作向导n一、用CTRL+CTRL+A A选择单元格数据区域n二、用ALT+E+A+FALT+E+A+F快捷键清除格式n三、用CTRL+1CTRL+1键设置单元格格式n四、用CTRLCTRL键连选不连续的单元格区域n五、快速地调整行高与列宽实训目的实训目的n掌握CTRL+-或CTRL+键增加或删除行与列的操作n掌握CTRL+C、CTRL+A

21、LT+V键的操作n掌握CTRL+1进行单元格格式的自定义操作n掌握SHIFT+箭头键的操作；n掌握CTRL+SHIFT+箭头键的操作；n掌握F5功能定位键的操作；Excel快捷键的应用（二）快捷键的应用（二）实训资料实训资料n请对江南公司的建筑安装支出的EXCEL流水记录表进行格式排版优化，要求用快捷键删除无关的行与列；清除原有的数据格式，表格中不充许中空格出现，日期采用年月日格式，金额要求显示两位数，凭证号不足3位数的，用“0”补足。操作向导操作向导n一、用CTRL+-键删除行或列n二、用ALT+E+A+F清除格式n三、用F5键打开定位窗口，填充单元格nCTRT+ENTRE填充空白单元格填充

22、空白单元格n四、用四、用CTRL+1键设置单元格格式键设置单元格格式n凭证号的长度设置凭证号的长度设置nSHIFT+组合键选择单元格组合键选择单元格快速地选择含有公式的单元格n选择含有公式的单元格nCTRL+ALT+V选择性粘贴实训目的实训目的n掌握CTRL+F、CTRL+H快捷键的操作 n掌握通配符的应用Excel快捷键的应用（三）快捷键的应用（三）实训资料实训资料n江南公司的飞扬下载了管理会计应用指引第802号管理会计信息系统，他准备在该文件当中查找含有“激励”两个字的条款。同时将B列中的问号与空号全部删除，这时该如何操作呢？操作向导操作向导n用CTRL+F键打开查找和替换窗口n用用“”与

23、通配符组合查找与替换与通配符组合查找与替换“*”与与“?”同步训练同步训练n替换功能不仅可以替换到特定的字符，对于有规律的字符，即使不相同也可以批量替换。如何批量删除型号列中“”横杠及之后的内容呢？实训目的实训目的n掌握CTRL+PAGEDOWN、CTRL+PAGEUP快捷键的操作n掌握CTRL+SHIFT+PAGEDOWN、CTRL+SHIFT+PAGEUP快捷键的操作Excel快捷键的应用（四）快捷键的应用（四）操作向导操作向导l在Excel的操作中，CTRL+PAGEDOWN 快捷键是向下翻页功能，SHIFT键具备连选功能，所以二合一，它将具备连续翻页的功能。n一、按CTRL+PAGED

24、OWN键向下切换工作表n二、按CTRL+SHIFT+PAGEDOWN向下连选多张工作表n按按CTRL+SHIFT+PAGEDOWN向下连选多张工作表向下连选多张工作表同步训练同步训练n请打开农村“三资”清查表，用上述快捷键方查询农村资金、资产、资源的清查表。对于递延年金现值的计算经常会被如何正确确定递延期所困绕！递延年金现值的递延年金现值的3种计算种计算讨论讨论题目:某企业2019年年初投资一个项目，预计从第三年起连续五年每年年末可以获得净收益20万元，年利率5%；要求:计算这个递延年金项目年净收益的现值？判断：经营期是几年？关键词汇关键词汇第三年起连续五年每年年末第三年起，相当于第二年年末，

25、所以经营期确定为2+57年测试测试根据递延年金现值计算原理，下列正确的选项有（）。A、先计算递延期年金现值，再向前折现即=PV(5%,2,PV(5%,5,20,0)B、先计算递延期年金终值，再向前折现即=PV(5%,2+5,FV(5%,5,20,0)C、先计算经营期2+5的年金现值，再减去2年递延期年金现值即=-(PV(5%,2+5,20,0)-PV(5%,2,20,0)D、应该使用FV函数计算回顾回顾任务要求任务要求l双击工作表(对象)任一单元格或指定单元格(对象)；l系统将自动(判断)对表格区域(对象)增加边框(属性)，且进行灰白(属性)间隔进行排版(循环)。Excel排版代码的阅读与应用

26、排版代码的阅读与应用效果如下效果如下学学vba vba 就这么简单！就这么简单！模式语式模式语式lsub 程序()dim P as 对象类型nset p=dim n as 变量类型nn=range(“a65536”).end(xlup).rowfor n2=起始值 to 终值 step 1nif 判断表达式thenn是命令neslen否命令nend ifnextlend sub明确对象明确对象lDim ws As WorksheetlSet ws=ActiveSheet声明一个变量声明一个变量lDim r2 As Integerr2=0lDim cc2 As Integercc2=0R2CC2

27、判断表格宽度判断表格宽度lDim c As Integerlc=ws.Range(bb1).End(xlToLeft).Column循环与判断语句循环与判断语句l求最大行lFor c2=1 To c Step 1 lR=ws.Cells(65536,c2).End(xlUp).Row nIf R r2 Thennr2=R nEnd IflNext c2R2循环与判断语句循环与判断语句l求最大列lFor RR2=1 To r2 Step 1c=ws.Cells(RR2,54).End(xlToLeft).ColumnnIf c cc2 Thenncc2=c nEnd IflNext RR2CC2

28、声明一个单元格区域对象声明一个单元格区域对象lDim rng As Range lSet rng=Range(Cells(1,1),Cells(r2,cc2)用用WITH语句简化代码语句简化代码lWith rng.Borders.LineStyle=0.RowHeight=18.Font.Name=宋体.Columns.AutoFit.Rows.AutoFit.Font.Size=10.Font.ColorIndex=1.Borders.LineStyle=1lEnd With用用WITH语句简化代码语句简化代码lWith Range(Cells(1，1),Cells(1,cc2).Borde

29、rAround xlContinuous,xlMedium 中等.Interior.ColorIndex=50 绿色.Font.ColorIndex=2 白色.HorizontalAlignment=xlHAlignCenter 居中.Borders(xlEdgeBottom).Weight=xlMediumlEnd With将表格区域的左右两边的边框线取消将表格区域的左右两边的边框线取消l With Range(Cells(1,1),Cells(r2,cc2).Borders(xlEdgeLeft).LineStyle=xlLineStyleNone.Borders(xlEdgeRight)

30、.LineStyle=xlLineStyleNonelEnd with从第从第2行开始，先灰后白进行单元格底纹排版行开始，先灰后白进行单元格底纹排版lFor RR=2To r2 Step 2 Range(Cells(RR,1),Cells(RR,cc2).Interior.Color=RGB(220,220,220)Range(Cells(RR+1,1),Cells(RR+1,cc2).Interior.ColorIndex=2 白色lNexthttps:/ 白色交流内容交流内容nExcel财务建模认知篇初见财务模型nExcel财务建模基础篇函数与快捷键nExcel财务建模工具篇数据分析工具n

31、Excel财务建模控件篇控件工具应用nExcel财务建模VBA篇程序化数据处理ExcelExcel财务建模财务建模关于财务建模关于财务建模n同行课件l人民大学张瑞君：“进行动态决策”l河南财政李爱红：动态输入输出ExcelExcel财务建模认知篇财务建模认知篇初识财务模型初识财务模型要要“动态输入动态输入动态输出动态输出”要要“熟悉熟悉ExcelExcel函数与快捷键函数与快捷键”n知乎讨论l“保证快捷键和公式烂熟于心，可实现全键盘操作的程度”l“一定要让你的关键结论(Output)可以自动的根据关键假设(Input)的变化而发生变化。要要“能满足实际工作的决策分析与演示要求能满足实际工作的决

32、策分析与演示要求”n培训机构要要“在课堂中应用财务模型，进行推演分析在课堂中应用财务模型，进行推演分析”“自制的模型，才能表达自己的思想！自制的模型，才能表达自己的思想！”nDIY财务模型l函数+控件+分析工具+VBAVBAVBA与与ExcelExcel财务模型财务模型VBAVBA与与ExcelExcel非财务模型非财务模型资金管理岗资金管理岗nSWOT分析n设备取得方式决策n珠宝城项目收入成本利润预测表n珠宝城项目现金流量预测表n珠宝城项目净现值计算表n税金预算表n财务费用预算明细表n2019年预计利润表n长期资产预测表n资产负债项目预测表n资金需求测算表n融资金额计算表n预计资产负债表Ex

33、celExcel财务建模基础篇财务建模基础篇函数与快捷键函数与快捷键营运管理岗营运管理岗n增长率预测表n销售额预算表n各季度销售额预算表n采购总额预算n采购费用各季度预算试算表n管理费用预算试算表n管理费用预算表n销售费用预算表n销售费用预算试算表n营业成本预算表n采购资金弹性区间预测表n时尚系列产品保本分析表n传统系列产品保本分析表n保利分析表n边际贡献n敏感性分析表成本管理岗成本管理岗n资源类型归类n作业类型归类n珠宝城资源费用预测表n作业分配率预计表n作业成本预计表n项目收益预计表（作业成本法）n成本项目分类n成本预算（完全成本法）n项目收益计算表n两种方法差异分析表绩效管理岗绩效管理岗

34、n投资资本回报率计算表n自由现金流计算表n净资产收益率计算表n息税前利润计算表n总资产周转率计算表n资产负债率计算表n财务指标分析n综合业绩报告n经营分析报告n销售收入同比变化分析报告n销售收入完成情况分析报告n营业总成本分析表n绩效评价表-公司层面n绩效评价表-直营管理部n绩效评价表-产品开发部n绩效评价表-物流配送部n奖金系数计算表应用场景应用场景1 1格式格式2位小数?完整小数位?%?复制后的表样?数据分列?应用场景应用场景2 2查询查询在众多的报表中查找?数据清理?数据转置?应用场景应用场景3 3引用引用直接引?一对一精确引用?数据区域模糊引用?单条件引用?多条件引用?静态引用范围?动

35、态引用范围?应用场景应用场景4 4计算计算求和?求平均?求计数?求最大（小）值?求目标值?求方差?求现值；终值?求加权平均?单变量求解规划求解回归分析财务函数SUMPRODUCT应用场景应用场景5 5校验校验总分的合计数?应用场景应用场景6 6决策决策战略管理?投融资管理?预算管理?营运管理?成本管理?绩效管理?风险管理?财务建模中财务建模中ExcelExcel的高频应用的高频应用快捷操作快捷操作查询和引用函数和引用函数 逻辑函数函数统计函数函数财务函数函数工具工具应用用CTRL+1VLOOKUPIFAVERAGEPV数据分列CTRL+SHIFT+%SUMIFIFERRORSUMPRODUCT

36、FV单变量求解F4SUMIFSMAXIRR回归分析ATL+=F5MINNPV散点图CTRL+与-COUNTRATE规划求解CTRL+HNPER方差数组IPMT公式的复制PPMTCTRL+SHIFT+PMTALT+E+A+F转置自动调整列宽立方根；平方根平方战略管理投融资管理预算管理营运管理成本管理绩效管理风险管理立方根；求次方；立方根；求次方；ExcelExcel财务建模工具篇财务建模工具篇数据分析工具数据分析工具单变量求解单变量求解规划求解规划求解回归分析回归分析Y=a+bXab建立预测模型Y=-1328677283+1142656493x。散点图散点图单（双）因素模拟运算单（双）因素模拟运

37、算方案方案利润敏感性分析模型制作步骤利润敏感性分析模型制作步骤l单因素变动分析l计算敏感系数l多因素变动分析经济批量模型制作步骤经济批量模型制作步骤报表分析模型制作步骤报表分析模型制作步骤VBAVBA制作的财务模型制作的财务模型ExcelExcel财务建模财务建模VBAVBA篇篇零基础学零基础学VBAVBAVBAVBA制作的非财务模型制作的非财务模型难学吗？难学吗？l一页一页4 4的内容；的内容；l讲述讲述Excel VbaExcel Vba中的基础知识中的基础知识l完成学习后，你能阅读、修改一般的完成学习后，你能阅读、修改一般的VbaVba代码，具备代码，具备VbaVba编程的基本能编程的基

38、本能力。力。学会这些，对付一般的数据处理，没问题！学会这些，对付一般的数据处理，没问题！一个经典的编程结构一个经典的编程结构nSub 程序名（）l对象：dim 对象名as 对象类型l变量：dim 变量名as 变量类型l判断：if 条件then 执行命令 end ifl循环：for nextl引用工作表函数：application.worksheetfunction.工作表函数l方法：对象.动作nEnd SubVBAVBA编程模型编程模型sub sub 程序程序()()dim P as dim P as 对象类型对象类型set p=dim n as dim n as 变量类型变量类型n=rang

39、e(“a65536”).end(xlup).rowfor n2=for n2=起始值起始值 to to 终值终值 step 1 step 1if 判断表达式thenn是命令neslen否命令end ifnextnextend subend sub目标目标n掌握分期付息accrint函数与到期付息accrintm函数的应用区别；n掌握4快捷键的应用，理解绝对引用，混合引用；相对引用；n能依据财务管理中的基本知识进行列式计算应计利息；n掌握单元格名称的定义与公式引用；n掌握两个日期间隔天数的快速计算；n掌握特殊字符的录入技巧；在6单元格中录入“票面利率“n掌握round等系列函数的应用；n掌握向小

40、数点左位舍位的方法；n掌握mround函数的应用；n掌握accrint与accrintm函数的应用；ACCRINT 函数函数语法语法lACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)重要重要 l使用 DATE 函数输入日期或者将日期作为其他公式或函数的结果输入。l使用函数 DATE(2008,5,23)输入 2008 年 5 月 23 日。如果日期以文本形式输入，则会出错。参参数数lIssue必需。有价证券的发行日。lFirst_interest 必需。有价证券的首次计息日。lSettle

41、ment 必需。有价证券的结算日。有价证券结算日是在发行日之后，有价证券卖给购买者的日期。lRate必需。有价证券的年息票利率。lPar必需。证券的票面值。如果省略此参数，则 ACCRINT 使用￥10,000。Frequencyl必需。年付息次数。l如果按年支付，frequency=1；l按半年期支付，frequency=2；l按季支付，frequency=4Basis l可选。使用的日计数基准类型。l0 或省略US(NASD)30/360l1 实际/实际l2 实际/360l3 实际/365l4 欧洲 30/360calc_methodl可选。一个逻辑值，指定当结算日期晚于首次计息日期时用于

42、计算总应计利息的方法。l如果值为 TRUE(1)，则返回从发行日到结算日的总应计利息。l如果值为 FALSE(0)，则返回从首次计息日到结算日的应计利息。l如果不输入此参数，则默认为 TRUE。备注备注lissue、first_interest、settlement、frequency 和 basis 将被截尾取整。l如果 issue、first_interest 或 settlement 不是有效日期，则 ACCRINT 将返回#VALUE!错误值。l如果 rate 0 或 par 0，则 ACCRINT 将返回#NUM!错误值。l如果 frequency 不是数字 1、2 或 4，则 AC

43、CRINT 将返回#NUM!错误值。l如果 basis 4，则 ACCRINT 将返回#NUM!错误值。l如果 issue settlement，则 ACCRINT 将返回#NUM!错误值。一、一、实训目的实训目的l能自定义快速访问工具栏；l能重新排序快速访问工具栏排序；l掌握alt+1;alt+2;alt+3三个个快速访问栏快捷键自定义与应用；alt+1;alt+2;alt+3快捷键自定义（原理篇）快捷键自定义（原理篇）二、实训要求二、实训要求l根据自己的工作习惯，将字体的粗体；字体的着色；清除格式添加到快速访问工具栏，并依次排序，形成ALT+1;ALT+2;ALT+3快捷键。三、实训资料三

44、、实训资料l20180912ctrl shift 箭头快捷键的应用.xlsmALT+1;ALT+2;ALT+3ALT+1;ALT+2;ALT+3一、实训目的一、实训目的l掌握if函数的应用；l掌握嵌套公式的编制；AND 函数的应用函数的应用二、实训要求二、实训要求l根据职位与工龄补贴发放表，计算出各员工的工龄工资。1、职位=经理 （1）工龄=10年 600元 （2）工龄6-9年 400元 （3）工龄2-5年 180元 （4）工龄=10年 300元 （2）工龄2-9年 200元 （3）工龄=10年 100元 （2）工龄2-9年 50元 （3）工龄1,A2100)如果 A2 大于 1 并且小于 1

45、00，则显示 TRUE；否则显示 FALSE。l=IF(AND(A2A3,A21,A3=$B$7,C14=$B$5),B14*$B$8,0)如果总销售量大于或等于(=)销售目标，并且帐户额大于或等于(=)帐户目标额，则以奖金百分比乘以总销售额；否则返回 0。五、拓展阅读五、拓展阅读l=IF(AND(职位=经理,工龄_年=10),600,IF(AND(职位=经理,工龄_年=6,工龄_年=2,工龄_年=5),180,IF(AND(职位=经理,工龄_年=10),300,IF(AND(职位=科长,工龄_年=2,工龄_年=9),200,IF(AND(职位=科长,工龄_年=10),100,IF(AND(职

46、位=职员,工龄_年=2,工龄_年=10年 600元 （2）工龄6-9年 400元 （3）工龄2-5年 180元 （4）工龄=10年 300元 （2）工龄2-9年 200元 （3）工龄=10年 100元 （2）工龄2-9年 50元 （3）工龄32、苹果 或 B4。Average_range可选。计算平均值的实际单元格组。如果省略，则使用 range。备注备注l如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。l如果 range 为空值或文本值，AVERAGEIF 将返回错误值#DIV0!。l如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值

47、。l如果区域中没有满足条件的单元格，AVERAGEIF 将返回错误值#DIV/0!。通配符，即问号通配符，即问号(?)和星号和星号(*)l问号匹配任意单个字符；星号匹配任意一串字符。l如果要查找实际的问号或星号，请在字符前键入波形符()。=AVERAGEIF(A2:A6,=*西部,B2:B6)n求西部和中西部地区的所有利润的平均值。=AVERAGEIF(A2:A6,*（新办事处）,B2:B6)n求新办事处以外所有地区的所有利润的平均值。五、拓展阅读五、拓展阅读l=AVERAGEIF(B2:B5,23000)l=AVERAGEIF(A2:A5,250000)l=AVERAGEIF(A2:A5,2

48、50000,B2:B5)一、实训目的一、实训目的l掌握average函数的应用；l掌握average函数的多个单元格区域，多个数据的平均计算；l掌握零值、空值、文本对计算结果的影响；average函数的应用（原理篇）函数的应用（原理篇）二、实训要求二、实训要求l对有底纹的单元格进行计算；三、实训资料三、实训资料l20180907average函数的应用.xlsmAVERAGE 函数函数l返回参数的平均值（算术平均值）。l如果范围A1:A20 包含数字l=AVERAGE(A1:A20)将返回这些数字的平均值。l=AVERAGE(A1:A20，5)将返回这些数字与5的平均值。语法语法lAVERAG

49、E(number1,number2,.)Number1必需。要计算平均值的第一个数字、单元格引用或单元格区域。Number2,.可选。要计算平均值的其他数字、单元格引用或单元格区域，最多可包含 255 个。备注备注l参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。l逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；包含零值的单元格将被计算在内。l如果参数为错误值或为不能转换为数字的文本，将会导致错误。提示提示:l当对单元格中的数值求平均值时应牢记空单元格与含零值单元格的区别。一、实训目的一、实训目的l掌握CO

50、UNTIF函数的应用；l掌握F4快捷键的应用；l掌握SUM函数的应用；l掌握公式快速填允方式的应用；l掌握对指定区域的分类统计；COUNTIF函数的基本应用（原理篇）函数的基本应用（原理篇）二、实训要求二、实训要求l根据排班表，按排班类别分类统计每类各类班别的次数，并计算出每位员工的上班次数；l请对B4：AF37单元格区域中的出班安排按照“日”，“夜”，“中”，“体”，“值”进行分类统计，统计出全体人员的各类出班次数。l=COUNTIF($B$4:$AF$37,日)三、实训资料三、实训资料lcountif函数的基本应用值班次数统计（原理篇）.xlsmCOUNTIF 函数函数lCOUNTIF 是