《Excel在财务中的应用》课件-案例实训

表操作与技巧数据透视表函数与公式12430001020304050607图表56VBA目录Excel在财务中的应用实训篇表操作与技巧数据透视表函数与公式124300010203041Excel的内容2VBA编程VBA编程透视表透视表函数、图表函数、图表基本操作基本操作Excel的内容2VBA编程VBA编程透视表透视表函数、图表场景1Excel+计算器场景2Excel取代计算器场景3单元格表间运算场景4Vlookup+sumif场景6Excel+数据库场景5高级公式+透视表…...我们处于哪个场景？场景1场景2场景3场景4场景6场景5…...我们处于哪个场景3初级用户中级用户高级用户专家新手您希望成为哪个级别？初级用户中级用户高级用户专家新手您希望成为哪个级别？4No.1目标不少于30个函数；精绘一幅动态图表；学一天VBA入门；No.2方法表格——函数——图表——VBANo.3目标.“信息化+核算+税务+管理”!给自己树立一个目标！No.1目标No.2方法No.3目标给自己树立一个目标！5教学工具：启用宏；双击A1单元格；教学工具：启用宏；双击A1单元格；6自动评分纠错；双击答案提示；自动评分纠错；双击答案提示；7答题区提示答题区提示8案例特征：原理+应用，多项知识点计算各科室的平均工作津贴单纯的表格VSSUM+AVERAGEIF+单元格区域名称定义与引用+工作表保护的表格案例特征：原理+应用，多项知识点计算各科室的平均工作津贴单纯9学习目标：函数与公式不少于30个；学习目标：函数与公式不少于30个；10学习目标：数据透视表，善用数据透视表11学习目标：数据透视表，善用数据透视表11学习目标：精绘动态图表——让你的图表会说话学习目标：精绘动态图表——让你的图表会说话12拓展提升：用一天时间学习VBA入门3讲，能实现数据自动化处理;12讲,能编制出日常办公小程序；人机对话的语言；是非计算机专业的工作人员最容易学习的语言；首先，介绍7个常用的VBA词汇；拓展提升：用一天时间学习VBA入门3讲，能实现数据自动化处理13实训目的掌握FIND函数的应用；掌握相对引用、混合引用、绝对引用的f4快捷键的应用掌握IFERROR函数的应用；掌握双击“+”快速复制公式到末行的技巧；Find函数在招生分析工作中的应用（原理篇）实训目的掌握FIND函数的应用；Find函数在招生分析工作中14实训要求制作一个能依据专业关键字的招生查询工具，即在Ｈ4单元格录入专业名称关键字（如会计），即可按专业查询，只要能达到预期效果即可。实训要求制作一个能依据专业关键字的招生查询工具，即在Ｈ4单元15实训资料072018年招生专业分析——FIND函数.xlsm实训资料072018年招生专业分析——FIND函数.xls16操作向导用FIND函数查找字符串（专业名称）的位置；用F4快捷键进行快速的单元格引用方式切换；用IFERROR函数不显示出错的信息；=IFERROR(FIND($H$4,D5,1),"")双击“+”快速复制公式到末行；操作向导用FIND函数查找字符串（专业名称）的位置；17FIND函数FIND(find_text,within_text,[start_num])FIND函数FIND(find_text,within_t18说明函数FIND和FINDB用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。函数FIND始终将每个字符（不管是单字节还是双字节）按1计数。说明函数FIND和FINDB用于在第二个文本串中定位19语法FIND(find_text,within_text,[start_num])find_text必需。要查找的文本。within_text必需。包含要查找文本的文本。start_num可选。指定开始进行查找的字符。within_text中的首字符是编号为1的字符。如果省略start_num，则假定其值为1。语法FIND(find_text,within_text,20备注FIND和FINDB区分大小写，并且不允许使用通配符。如果您不希望执行区分大小写的搜索或使用通配符，则可以使用SEARCH

和SEARCHB函数。Find_text不能包含任何通配符。如果within_text中没有find_text，则FIND和FINDB返回错误值#VALUE!。备注FIND和FINDB区分大小写，并且不允许使用通配21数据MiriamMcGovern=FIND("M",A2)单元格A2中第一个“M”的位置1=FIND("m",A2)单元格A2中第一个“M”的位置6（所以，它是区分大小写的）=FIND("M",A2,3)从单元格A2的第三个字符开始查找第一个“M”的位置8数据MiriamMcGovern22一、实训目的掌握TRIM函数的应用掌握SUBSTITUTE函数的应用脏数据的清理——清除数据中空格（原理篇）一、实训目的掌握TRIM函数的应用脏数据的清理——清除数据中23二、实训要求*承兑21987329付苏州石湖地产有限公司12*承兑21987329付苏州石湖地产有限公司13请截取类似文本中承兑号码（字符中有“”文本）。二、实训要求*承兑21987329付苏州石湖地24三、实训资料08trim函数与substitue函数在数据清理中的应用(原理篇）.xlsm三、实训资料08trim函数与substitue函数在数据25四、操作向导用TRIM函数清除空格用SUBSTITUTE替换空格数据清理后的截取承兑号码找到"兑"字的位置根据承兑号码长度进行截取四、操作向导用TRIM函数清除空格26TRIM函数除了单词之间的单个空格之外，移除文本中的所有空格。对于从另一个可能含有不规则间距的应用程序收到的文本，可以使用TRIM。比如用财务软件中导出的数据。TRIM函数除了单词之间的单个空格之外，移除文本中的所有空格27语法TRIM(text)=TRIM("FirstQuarterEarnings")从公式的文本中移除前导空格和尾随空格(FirstQuarterEarnings)语法TRIM(text)28SUBSTITUTE函数在文本字符串中用new_text替换old_text。SUBSTITUTE函数在文本字符串中用new_text29语法SUBSTITUTE(text,old_text,new_text,[instance_num])Instance_num可选。指定要用new_text替换old_text的事件。如果指定了instance_num，则只有满足要求的old_text被替换。否则，文本中出现的所有old_text都会更改为new_text。如果Ａ14的值为：*承兑21987329付苏州石湖地产有限公司13=SUBSTITUTE(A14,"","")*承兑21987329付苏州石湖地产有限公司13语法SUBSTITUTE(text,old_text,n30一、实训资料一份农村“三资”清查的工作簿，它由28张不同的工作表组成。CTRL+SHIFT+PAGEUP键Ctrl+Shift+Pageup(Pagedown)的应用

一、实训资料一份农村“三资”清查的工作簿，它由28张不同的工31二、实训目标掌握CTRL+”+”;CTRL+”-”的应用；理解CTRL+ENTER的应用掌握CTRL+PAGEUP(DOWN)的应用；（上下切换工作表）掌握CTRL+SHIFT+PAGEUP(DOWN)的应用；(连选多张工作表）二、实训目标掌握CTRL+”+”;CTRL+”-”的应用；32三、实训要求1.快速地检查第3行与第4行是否空行；2.用CTRL+”-”删除第3行；3.用CTRL+”+”插入1行；4.连选N张工作表，统一在A7单元格中录入单位信息；三、实训要求1.快速地检查第3行与第4行是否空行；33一、实训要求本次操作是通过条件格式中的新建规则，利用公式对某学校的教师考核表中符合规则（如教学业绩分大于600分，专业建设分大于100分，科研分大于100分，育人分大于50分）的教师信息用淡绿色标出。条件格式——新建规则的操作一、实训要求本次操作是通过条件格式中的新建规则，利用公式对某34二、实训目的条件格式中的新建规则的建立、管理与清除；理解AND函数的应用；掌握F4单元格引用切换键的应用；掌握CTRL+SHIFT+箭头快捷键的应用；二、实训目的条件格式中的新建规则的建立、管理与清除；35三、操作向导好，本次课就讲解到这里。请您到网站下载相关的学习资料，及时完成课后作业，理解并巩固所学的知识与技能。再见！三、操作向导好，本次课就讲解到这里。请您到网站下载相关的学习36TRUNC(number,[num_digits])Number必需。需要截尾取整的数字。num_digits可选。用于指定取整精度的数字。num_digits的默认值为0（零）。TRUNC函数TRUNC(number,[num_digits])Num37与INT函数的区别TRUNC和INT的相似之处在于两者都返回整数。TRUNC删除数字的小数部分。INT根据数字小数部分的值将该数字向下舍入为最接近的整数。与INT函数的区别TRUNC和INT的相似之处在于两者38测试TRUNC(-4.3)返回-4INT(-4.3)返回-5因为-5是更小的数字。=TRUNC(8.9)将8.9截尾取整(8)=TRUNC(-8.9)将负数截尾取整并返回整数部分(-8)=TRUNC(0.45)将0和1之间的数字截尾取整，并返回整数部分(0)。测试TRUNC(-4.3)39实训目的〇掌握数据分列的操作〇掌握数据分列线的设置〇掌握CTRL+1快捷方式的应用数据分列的操作实训目的〇掌握数据分列的操作数据分列的操作40实训资料江南公司的某员工的信用卡流水记录，如图11-31所示。为了便于对交易金额进行分类统计，需要对E列中的交易金额、币符、收支类型进行分离，该如何操作呢？信用卡流水记录实训资料江南公司的某员工的信用卡流水记录，如图11-31所示41操作向导一、分析数据特征对需要分列的单元格中的数据进行分析，发现它们均有“/”。因此，可以根据“/”进行分离。二、选择数据分列功能三、设置分隔符号操作向导一、分析数据特征42《Excel在财务中的应用》课件——案例实训43《Excel在财务中的应用》课件——案例实训44同步训练为了日后统计收支类型金额的需要，我们需要对分列到F列的收支类型与币种信息分离，仍可使用分列功能。从图11-35中可以看出，由于数据具有规律性，因此可以“固定宽度”中的分列线进行数据分列，分列后的数据如图11-35所示。设置分列线分列后的收支类型数据同步训练为了日后统计收支类型金额的需要，我们需要对分列到F列45《Excel在财务中的应用》课件——案例实训46同步训练为了让数值显示更规范，需要将小数设置为2位数。选择E列，按CTRL+1键打开设置单元格格式窗口，选择数值，将小数位设置为2位数，单击“确定”。如图11-37所示。同步训练为了让数值显示更规范，需要将小数设置为2位数。选择E47同步训练请对江南公司的银行流水记录中的电子工作表中的金额栏中的数据进行大写与小写金额进行分离。如图11-38所示。银行流水记录同步训练请对江南公司的银行流水记录中的电子工作表中的金额栏中48操作提示：1.在D列之前用快捷方式CTRL+”+”插入一列；2.请将C列的大写金额与小写金额用数据分列功能分离出来；3.用CTRL+H键打开查找替换窗口将“元”字删除，按CTRL+1打开设置单元格式窗口将小数位保留2位数；操作提示：1.在D列之前用快捷方式CTRL+”+”插入一列；49实训目的○掌握CTRL+A、CTRL+H、F4等快捷操作；○掌握绝对引用、混合引用、相对引用在工作中的应用；○掌握ROUND函数的应用；○掌握嵌套公式的编辑技巧；单元格的引用实训目的○掌握CTRL+A、CTRL+H、F4等快捷操作；单50实训资料根据东南公司的2014年度到2018年度的营业收入分别计算出以2014年度为基准的定基动态比率与环比动态比率。相关数据如图11-39所示。东南公司的销售数据实训资料根据东南公司的2014年度到2018年度的营业收入分51操作向导一、不同的引用方式（一）单元格引用单元格的引用包括相对引用、绝对引用和混合引用三种。操作向导一、不同的引用方式521、相对引用公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置，被引用单元格的列标和行标前不加任何标示性符号。1、相对引用公式中的相对单元格引用（例如A1）是基于包含532、绝对引用公式中的绝对单元格引用（例如$A$1）不论包含公式的单元格处在什么位置，公式中所引用的单元格位置都是其工作表的确切位置。单元格的绝对引用通过在行标和列标前加一个美元符号“$”来表示，如$A$1、$B$2，以此类推。2、绝对引用公式中的绝对单元格引用（例如$A$1）不论包543、混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、B$1等形式。3、混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。554、不同引用之间的快速切换在Excel中输入公式时，只要正确使用F4键，就能快速地对单元格从相对引用到绝对引用、混合引用方式进行切换。比如在某单元格输入公式“=AVERAGE(B4：B8)”。选中数据区域B4：B8，按下F4键，该公式内容变为“=AVERAGE($B$4：$B$8)”，相对引用变成了绝对引用。再按一次F4键，它将切换到另一种引用方式。4、不同引用之间的快速切换在Excel中输入公式时，只要正确56（二）引用同一工作簿中的其它工作表引用同一工作簿中的其它工作表时格式如下：被引用的工作表！被引用的单元格。“=Sheet1!A8”（二）引用同一工作簿中的其它工作表引用同一工作簿中的其它工作57（三）引用其它工作簿中的工作表引用不同工作簿中的其它工作表时格式如下：[被引用的工作簿名称]被引用的工作表！被引用的单元格“=[Book2]Sheet2!$F$9”。（三）引用其它工作簿中的工作表引用不同工作簿中的其它工作58用CTRL+H键快速的查找与替换空格的字符将表中含有空格的字符串全部清理完毕，清理后的数据便于计算。如图11-41所示。用CTRL+H键快速的查找与替换空格的字符59采用绝对引用方式计算定基动态比率选择F5单元格，在单元格中录入“=F5/F5”，然后，选择分母中的“F5”，按住F4键，将相对引用（F5）快速的切换为绝对引用$F$5方式，按ENTER键回车，完成基期指标的计算。采用绝对引用方式计算定基动态比率选择F5单元格，在单元格中录60通过ROUND函数保留两位小数。通过ROUND函数保留两位小数。61以相对引用方式计算环比动态比率选择F5单元格，在单元格中录入“=ROUND(E5/F5,2)”，按ENTER键回车，然后选择E7单元格将光标变为“+”的时候，将公式向左一直复制到B7单元格，完成了环比动态比率的计算。如图11-44所示：以相对引用方式计算环比动态比率选择F5单元格，在单元格中录入62同步训练编制复利现值系数表编制复利终值系数表同步训练编制复利现值系数表63实训目的〇掌握用快捷键清除单元格格式的方法〇掌握CTRL+1、CTRL+A、ALT+E+A+F键的应用〇掌握行高列宽的便捷调整方式Excel快捷键的应用（一）实训目的〇掌握用快捷键清除单元格格式的方法Excel快捷键的64实训资料江南公司的财务人员在网络上复制了一份个人所得税税率表，会计人员计划清除表格当中的数据格式。然后将字体设置为“华文中宋”，字号12，加粗，B列自动调整，A列，C列，D列的列宽为10，字体的颜色默认。实训资料江南公司的财务人员在网络上复制了一份个人所得税税率表65操作向导一、用CTRL+A选择单元格数据区域二、用ALT+E+A+F快捷键清除格式三、用CTRL+1键设置单元格格式四、用CTRL键连选不连续的单元格区域五、快速地调整行高与列宽操作向导一、用CTRL+A选择单元格数据区域66实训目的〇掌握CTRL+"-"或CTRL+"+"键增加或删除行与列的操作〇掌握CTRL+C、CTRL+ALT+V键的操作〇掌握CTRL+1进行单元格格式的自定义操作〇掌握SHIFT+箭头键的操作；〇掌握CTRL+SHIFT+箭头键的操作；〇掌握F5功能定位键的操作；Excel快捷键的应用（二）实训目的〇掌握CTRL+"-"或CTRL+"+"键增加或删除67实训资料请对江南公司的建筑安装支出的EXCEL流水记录表进行格式排版优化，要求用快捷键删除无关的行与列；清除原有的数据格式，表格中不充许中空格出现，日期采用年月日格式，金额要求显示两位数，凭证号不足3位数的，用“0”补足。实训资料请对江南公司的建筑安装支出的EXCEL流水记录表进行68操作向导一、用CTRL+"-"键删除行或列二、用ALT+E+A+F清除格式三、用F5键打开定位窗口，填充单元格操作向导一、用CTRL+"-"键删除行或列69CTRT+ENTRE填充空白单元格CTRT+ENTRE填充空白单元格70四、用CTRL+1键设置单元格格式四、用CTRL+1键设置单元格格式71凭证号的长度设置凭证号的长度设置72SHIFT+→组合键选择单元格SHIFT+→组合键选择单元格73快速地选择含有公式的单元格

选择含有公式的单元格CTRL+ALT+V选择性粘贴快速地选择含有公式的单元格

选择含有公式的单元格74实训目的〇掌握CTRL+F、CTRL+H快捷键的操作〇掌握通配符的应用Excel快捷键的应用（三）实训目的〇掌握CTRL+F、CTRL+H快捷键的操作Exc75实训资料江南公司的飞扬下载了《管理会计应用指引第802号——管理会计信息系统》，他准备在该文件当中查找含有“激励”两个字的条款。同时将B列中的问号与空号全部删除，这时该如何操作呢？实训资料江南公司的飞扬下载了《管理会计应用指引第802号——76操作向导用CTRL+F键打开查找和替换窗口操作向导用CTRL+F键打开查找和替换窗口77用“~”与通配符组合查找与替换“*”与“?”用“~”与通配符组合查找与替换“*”与“?”78同步训练替换功能不仅可以替换到特定的字符，对于有规律的字符，即使不相同也可以批量替换。如何批量删除型号列中“—”横杠及之后的内容呢？同步训练替换功能不仅可以替换到特定的字符，对于有规律的字符，79实训目的〇掌握CTRL+PAGEDOWN、CTRL+PAGEUP快捷键的操作〇掌握CTRL+SHIFT+PAGEDOWN、CTRL+SHIFT+PAGEUP快捷键的操作Excel快捷键的应用（四）实训目的〇掌握CTRL+PAGEDOWN、CTRL+PAGE80操作向导在Excel的操作中，CTRL+PAGEDOWN快捷键是向下翻页功能，SHIFT键具备连选功能，所以二合一，它将具备连续翻页的功能。操作向导在Excel的操作中，CTRL+PAGEDOWN快81一、按CTRL+PAGEDOWN键向下切换工作表二、按CTRL+SHIFT+PAGEDOWN向下连选多张工作表一、按CTRL+PAGEDOWN键向下切换工作表82按CTRL+SHIFT+PAGEDOWN向下连选多张工作表按CTRL+SHIFT+PAGEDOWN向下连选多张工作表83同步训练请打开农村“三资”清查表，用上述快捷键方查询农村资金、资产、资源的清查表。同步训练请打开农村“三资”清查表，用上述快捷键方查询农村资金84对于递延年金现值的计算经常会被如何正确确定递延期所困绕！递延年金现值的3种计算对于递延年金现值的计算递延年金现值的3种计算85讨论题目:某企业2019年年初投资一个项目，预计从第三年起连续五年每年年末可以获得净收益20万元，年利率5%；要求:计算这个递延年金项目年净收益的现值？判断：经营期是几年？讨论题目:86关键词汇第三年起连续五年每年年末第三年起，相当于第二年年末，所以经营期确定为2+5＝7年关键词汇第三年起连续五年每年年末87测试根据递延年金现值计算原理，下列正确的选项有（）。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函数计算测试根据递延年金现值计算原理，下列正确的选项有（）。88回顾回顾89任务要求双击工作表(对象)任一单元格或指定单元格(对象)；系统将自动(判断)对表格区域(对象)增加边框(属性)，且进行灰白(属性)间隔进行排版(循环)。Excel排版代码的阅读与应用任务要求双击工作表(对象)任一单元格或指定单元格(对象)；E90效果如下效果如下91学vba就这么简单！——模式语式sub程序()dimPas

对象类型setp=dimnas变量类型n=range(“a65536”).end(xlup).rowforn2=起始值to终值step1if

判断表达式then是命令esle否命令endifnextendsub学vba就这么简单！——模式语式sub程序()92明确对象DimwsAsWorksheetSetws=ActiveSheet明确对象DimwsAsWorksheet93声明一个变量Dimr2AsIntegerr2=0Dimcc2AsIntegercc2=0R2CC2声明一个变量Dimr2AsIntegerR2CC294判断表格宽度DimcAsIntegerc=ws.Range("bb1").End(xlToLeft).Column判断表格宽度DimcAsInteger95循环与判断语句求最大行Forc2=1TocStep1R=ws.Cells(65536,c2).End(xlUp).RowIfR>r2Thenr2=REndIfNextc2R2循环与判断语句求最大行R296循环与判断语句求最大列ForRR2=1Tor2Step1c=ws.Cells(RR2,54).End(xlToLeft).ColumnIfc>cc2Thencc2=cEndIfNextRR2CC2循环与判断语句求最大列CC297声明一个单元格区域对象DimrngAsRangeSetrng=Range(Cells(1,1),Cells(r2,cc2))声明一个单元格区域对象DimrngAsRange98用WITH语句简化代码Withrng.Borders.LineStyle=0.RowHeight=18.Font.Name="宋体".Columns.AutoFit'.Rows.AutoFit.Font.Size=10.Font.ColorIndex=1.Borders.LineStyle=1EndWith用WITH语句简化代码Withrng99用WITH语句简化代码WithRange(Cells(1，1),Cells(1,cc2)).BorderAroundxlContinuous,xlMedium'中等.Interior.ColorIndex=50'绿色.Font.ColorIndex=2'白色.HorizontalAlignment=xlHAlignCenter'居中.Borders(xlEdgeBottom).Weight=xlMediumEndWith用WITH语句简化代码WithRange(Cells(1，100'将表格区域的左右两边的边框线取消WithRange(Cells(1,1),Cells(r2,cc2)).Borders(xlEdgeLeft).LineStyle=xlLineStyleNone.Borders(xlEdgeRight).LineStyle=xlLineStyleNoneEndwith'将表格区域的左右两边的边框线取消WithRange(C101从第2行开始，先灰后白进行单元格底纹排版ForRR=2Tor2Step2Range(Cells(RR,1),Cells(RR,cc2)).Interior.Color=RGB(220,220,220)Range(Cells(RR+1,1),Cells(RR+1,cc2)).Interior.ColorIndex=2'白色Next从第2行开始，先灰后白进行单元格底纹排版ForRR=2102/tool/rgbschemes/.Interior.Color=RGB(220,220,220)/tool/rg103.Interior.ColorIndex=2'白色.Interior.ColorIndex=2'白色104交流内容Excel财务建模认知篇——初见财务模型Excel财务建模基础篇——函数与快捷键Excel财务建模工具篇——数据分析工具Excel财务建模控件篇——控件工具应用Excel财务建模VBA篇——程序化数据处理Excel财务建模交流内容Excel财务建模认知篇——初见财务模型Excel财105关于财务建模同行课件人民大学张瑞君：“进行动态决策”河南财政李爱红：动态输入输出Excel财务建模认知篇——初识财务模型关于财务建模同行课件Excel财务建模认知篇——初识财务模型106要“动态输入——动态输出”要“动态输入——动态输出”107要“熟悉Excel函数与快捷键”知乎讨论“保证快捷键和公式烂熟于心，可实现全键盘操作的程度”“一定要让你的关键结论(Output)可以自动的根据关键假设(Input)的变化而发生变化。要“熟悉Excel函数与快捷键”知乎讨论108要“能满足实际工作的决策分析与演示要求”培训机构要“能满足实际工作的决策分析与演示要求”培训机构109要“在课堂中应用财务模型，进行推演分析”要“在课堂中应用财务模型，进行推演分析”110“自制的模型，才能表达自己的思想！”DIY财务模型函数+控件+分析工具+VBA“自制的模型，才能表达自己的思想！”DIY财务模型111VBA与Excel财务模型VBA与Excel财务模型112VBA与Excel非财务模型VBA与Excel非财务模型113资金管理岗SWOT分析设备取得方式决策珠宝城项目收入成本利润预测表珠宝城项目现金流量预测表珠宝城项目净现值计算表税金预算表财务费用预算明细表2019年预计利润表长期资产预测表资产负债项目预测表资金需求测算表融资金额计算表预计资产负债表Excel财务建模基础篇——函数与快捷键资金管理岗SWOT分析资产负债项目预测表Excel财务建模基114营运管理岗增长率预测表销售额预算表各季度销售额预算表采购总额预算采购费用各季度预算试算表管理费用预算试算表管理费用预算表销售费用预算表销售费用预算试算表营业成本预算表采购资金弹性区间预测表时尚系列产品保本分析表传统系列产品保本分析表保利分析表边际贡献敏感性分析表营运管理岗增长率预测表营业成本预算表115成本管理岗资源类型归类作业类型归类珠宝城资源费用预测表作业分配率预计表作业成本预计表项目收益预计表（作业成本法）成本项目分类成本预算（完全成本法）项目收益计算表两种方法差异分析表成本管理岗资源类型归类116绩效管理岗投资资本回报率计算表自由现金流计算表净资产收益率计算表息税前利润计算表总资产周转率计算表资产负债率计算表财务指标分析综合业绩报告经营分析报告销售收入同比变化分析报告销售收入完成情况分析报告营业总成本分析表绩效评价表-公司层面绩效评价表-直营管理部绩效评价表-产品开发部绩效评价表-物流配送部奖金系数计算表绩效管理岗投资资本回报率计算表销售收入同比变化分析报告117应用场景1格式2位小数?完整小数位?%?复制后的表样?数据分列?…应用场景1格式2位小数?118应用场景2查询在众多的报表中查找?数据清理?数据转置?…应用场景2查询在众多的报表中查找?119应用场景3引用直接引?一对一精确引用?数据区域模糊引用?单条件引用?多条件引用?静态引用范围?动态引用范围?应用场景3引用直接引?120应用场景4计算求和?求平均?求计数?求最大（小）值?求目标值?求方差?求现值；终值?求加权平均?单变量求解规划求解回归分析财务函数SUMPRODUCT应用场景4计算求和?单变量求解121应用场景5校验总分的合计数?应用场景5校验总分的合计数?122《Excel在财务中的应用》课件——案例实训123应用场景6决策战略管理?投融资管理?预算管理?营运管理?成本管理?绩效管理?风险管理?应用场景6决策战略管理?124财务建模中Excel的高频应用快捷操作查询和引用函数逻辑函数统计函数财务函数工具应用CTRL+1VLOOKUPIFAVERAGEPV数据分列CTRL+SHIFT+%SUMIFIFERRORSUMPRODUCTFV单变量求解F4SUMIFSMAXIRR回归分析ATL+"="F5MINNPV散点图CTRL+"+"与"-"COUNTRATE规划求解CTRL+HNPER方差数组IPMT公式的复制PPMTCTRL+SHIFT+→PMTALT+E+A+F转置自动调整列宽立方根；平方根平方战略管理投融资管理预算管理营运管理成本管理绩效管理风险管理财务建模中Excel的高频应用快捷操作查询和引用函数逻辑函数125立方根；求Ｎ次方；

立方根；求Ｎ次方；

126Excel财务建模工具篇——数据分析工具Excel财务建模工具篇——数据分析工具127单变量求解单变量求解128规划求解规划求解129回归分析Y=a+bXab建立预测模型Y=-1328677283+1142656493x。回归分析Y=a+bXab建立预测模型Y=-132867728130散点图散点图131单（双）因素模拟运算单（双）因素模拟运算132方案方案133《Excel在财务中的应用》课件——案例实训134利润敏感性分析模型制作步骤单因素变动分析计算敏感系数多因素变动分析利润敏感性分析模型制作步骤单因素变动分析135《Excel在财务中的应用》课件——案例实训136《Excel在财务中的应用》课件——案例实训137《Excel在财务中的应用》课件——案例实训138《Excel在财务中的应用》课件——案例实训139《Excel在财务中的应用》课件——案例实训140《Excel在财务中的应用》课件——案例实训141《Excel在财务中的应用》课件——案例实训142经济批量模型制作步骤经济批量模型制作步骤143报表分析模型制作步骤报表分析模型制作步骤144VBA制作的财务模型Excel财务建模VBA篇——零基础学VBAVBA制作的财务模型Excel财务建模VBA篇——零基础学V145VBA制作的非财务模型VBA制作的非财务模型146难学吗？一页Ａ4的内容；讲述ExcelVba中的基础知识完成学习后，你能阅读、修改一般的Vba代码，具备Vba编程的基本能力。难学吗？一页Ａ4的内容；147学会这些，对付一般的数据处理，没问题！学会这些，对付一般的数据处理，没问题！148一个经典的编程结构Sub程序名（）对象：dim对象名as对象类型变量：dim变量名as变量类型判断：if条件then执行命令endif循环：for……next引用工作表函数：application.worksheetfunction.工作表函数方法：对象.动作EndSub一个经典的编程结构Sub程序名（）149VBA编程模型sub程序()dimPas对象类型setp=dimnas变量类型n=range(“a65536”).end(xlup).rowforn2=起始值to终值step1if判断表达式then是命令esle否命令endifnextendsubVBA编程模型sub程序()150目标掌握分期付息accrint函数与到期付息accrintm函数的应用区别；掌握Ｆ4快捷键的应用，理解绝对引用，混合引用；相对引用；能依据财务管理中的基本知识进行列式计算应计利息；掌握单元格名称的定义与公式引用；掌握两个日期间隔天数的快速计算；掌握特殊字符的录入技巧；在Ａ6单元格中录入“②票面利率“掌握round等系列函数的应用；掌握向小数点左位舍位的方法；掌握mround函数的应用；掌握accrint与accrintm函数的应用；ACCRINT函数目标掌握分期付息accrint函数与到期付息accrintm151语法ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method])语法ACCRINT(issue,first_interes152重要使用DATE函数输入日期或者将日期作为其他公式或函数的结果输入。使用函数DATE(2008,5,23)输入2008年5月23日。如果日期以文本形式输入，则会出错。重要使用DATE函数输入日期153参数Issue必需。有价证券的发行日。First_interest必需。有价证券的首次计息日。Settlement必需。有价证券的结算日。有价证券结算日是在发行日之后，有价证券卖给购买者的日期。Rate必需。有价证券的年息票利率。Par必需。证券的票面值。如果省略此参数，则ACCRINT使用￥10,000。参数Issue必需。有价证券的发行日。154Frequency必需。年付息次数。如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4Frequency必需。年付息次数。155Basis可选。使用的日计数基准类型。0或省略US(NASD)30/3601 实际/实际2 实际/3603 实际/3654 欧洲30/360Basis可选。使用的日计数基准类型。156《Excel在财务中的应用》课件——案例实训157《Excel在财务中的应用》课件——案例实训158《Excel在财务中的应用》课件——案例实训159calc_method可选。一个逻辑值，指定当结算日期晚于首次计息日期时用于计算总应计利息的方法。如果值为TRUE(1)，则返回从发行日到结算日的总应计利息。如果值为FALSE(0)，则返回从首次计息日到结算日的应计利息。如果不输入此参数，则默认为TRUE。calc_method可选。一个逻辑值，指定当结算日期晚于160备注issue、first_interest、settlement、frequency和basis将被截尾取整。如果issue、first_interest或settlement不是有效日期，则ACCRINT将返回#VALUE!错误值。如果rate≤0或par≤0，则ACCRINT将返回#NUM!错误值。如果frequency不是数字1、2或4，则ACCRINT将返回#NUM!错误值。如果basis<0或basis>4，则ACCRINT将返回#NUM!错误值。如果issue≥settlement，则ACCRINT将返回#NUM!错误值。备注issue、first_interest、settlem161一、实训目的能自定义快速访问工具栏；能重新排序快速访问工具栏排序；掌握alt+1;alt+2;alt+3三个个快速访问栏快捷键自定义与应用；alt+1;alt+2;alt+3快捷键自定义（原理篇）一、实训目的能自定义快速访问工具栏；alt+1;alt+2;162二、实训要求根据自己的工作习惯，将字体的粗体；字体的着色；清除格式添加到快速访问工具栏，并依次排序，形成ALT+1;ALT+2;ALT+3快捷键。二、实训要求根据自己的工作习惯，将字体的粗体；字体的着色；清163三、实训资料20180912ctrlshift箭头快捷键的应用.xlsm三、实训资料20180912ctrlshift箭头快捷键164ALT+1;ALT+2;ALT+3ALT+1;ALT+2;ALT+3165一、实训目的掌握if函数的应用；掌握嵌套公式的编制；AND函数的应用一、实训目的掌握if函数的应用；AND函数的应用166二、实训要求根据职位与工龄补贴发放表，计算出各员工的工龄工资。1、职位=经理（1）工龄>=10年600元（2）工龄6--9年400元（3）工龄2--5年180元（4）工龄<2年0元2、职位=科长（1）工龄>=10年300元（2）工龄2--9年200元（3）工龄<2年0元3、职位=职员（1）工龄>=10年100元（2）工龄2--9年50元（3）工龄<2年0元二、实训要求根据职位与工龄补贴发放表，计算出各员工的工龄工资167三、实训资料datedif函数的基本应用——工龄工资的计算.xlsm三、实训资料datedif函数的基本应用——工龄工资的计算.168示例=AND(A2>1,A2<100)如果A2大于1并且小于100，则显示TRUE；否则显示FALSE。=IF(AND(A2<A3,A2<100),A2,"数值超出范围")如果单元格A2中的值小于A3并且小于100，则显示此值；否则显示消息“数值超出范围”。=IF(AND(A3>1,A3<100),A3,"数值超出范围")如果单元格A3中的值大于1并且小于100，则显示此值；否则显示一条消息。AND函数示例=AND(A2>1,A2<100)AND函数169奖金计算单元格E14中的公式为=IF(AND(B14>=$B$7,C14>=$B$5),B14*$B$8,0)如果总销售量大于或等于(>=)销售目标，并且帐户额大于或等于(>=)帐户目标额，则以奖金百分比乘以总销售额；否则返回0。奖金计算单元格E14中的公式为170五、拓展阅读=IF(AND(职位="经理",工龄_年>=10),600,IF(AND(职位="经理",工龄_年>=6,工龄_年<=9),400,IF(AND(职位="经理",工龄_年>=2,工龄_年<=5),180,IF(AND(职位="经理",工龄_年<2),0,IF(AND(职位="科长",工龄_年>=10),300,IF(AND(职位="科长",工龄_年>=2,工龄_年<=9),200,IF(AND(职位="科长",工龄_年<2),0,IF(AND(职位="职员",工龄_年>=10),100,IF(AND(职位="职员",工龄_年>=2,工龄_年<=9),50,0)))))))))1、职位=经理（1）工龄>=10年600元（2）工龄6--9年400元（3）工龄2--5年180元（4）工龄<2年0元2、职位=科长（1）工龄>=10年300元（2）工龄2--9年200元（3）工龄<2年0元3、职位=职员（1）工龄>=10年100元（2）工龄2--9年50元（3）工龄<2年0元五、拓展阅读=IF(AND(职位="经理",工龄_年>=10171一、实训目的掌握countif函数的应用；掌握averageif函数的应用；掌握通配符在函数中的应用；Averageif函数应用——统计各人员的津贴均值（原理篇）一、实训目的掌握countif函数的应用；Averageif172二、实训要求完成各职称平均工作业绩及人数的统计。要求直接引用列的方式进行。完成指定的工作任务要求；掌握运算符在函数中的应用及差错分析；完成指定的工作任务要求；二、实训要求完成各职称平均工作业绩及人数的统计。要求直接引用173三、实训资料Averageif+Countif函数（按职称统计各人员的津贴均值及人数）.xlsm三、实训资料Averageif+Countif函数（按职称统174AVERAGEIF函数返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。AVERAGEIF函数返回某个区域内满足给定条件的所有单元175语法AVERAGEIF(range,criteria,[average_range])Range必需。要计算平均值的一个或多个单元格，其中包含数字或包含数字的名称、数组或引用。Criteria必需。形式为数字、表达式、单元格引用或文本的条件，用来定义将计算平均值的单元格。条件可以表示为32、"32"、">32"、"苹果"或B4。Average_range可选。计算平均值的实际单元格组。如果省略，则使用range。语法AVERAGEIF(range,criteria,[176备注如果average_range中的单元格为空单元格，AVERAGEIF将忽略它。如果range为空值或文本值，AVERAGEIF将返回错误值#DIV0!。如果条件中的单元格为空单元格，AVERAGEIF就会将其视为0值。如果区域中没有满足条件的单元格，AVERAGEIF将返回错误值#DIV/0!。备注如果average_range中的单元格为空单元格，177通配符，即问号(?)和星号(*)问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符(~)。=AVERAGEIF(A2:A6,"=*西部",B2:B6)求西部和中西部地区的所有利润的平均值。=AVERAGEIF(A2:A6,"<>*（新办事处）",B2:B6)求新办事处以外所有地区的所有利润的平均值。通配符，即问号(?)和星号(*)问号匹配任意单个字符；178五、拓展阅读=AVERAGEIF(B2:B5,"<23000")=AVERAGEIF(A2:A5,"<250000")=AVERAGEIF(A2:A5,"<95000")=AVERAGEIF(A2:A5,">250000",B2:B5)五、拓展阅读=AVERAGEIF(B2:B5,"<23000179一、实训目的掌握average函数的应用；掌握average函数的多个单元格区域，多个数据的平均计算；掌握零值、空值、文本对计算结果的影响；average函数的应用（原理篇）一、实训目的掌握average函数的应用；average函数180二、实训要求对有底纹的单元格进行计算；二、实训要求对有底纹的单元格进行计算；181三、实训资料20180907average函数的应用.xlsm三、实训资料20180907average函数的应用.xls182AVERAGE函数返回参数的平均值（算术平均值）。如果范围A1:A20包含数字=AVERAGE(A1:A20)将返回这些数字的平均值。=AVERAGE(A1:A20，5)将返回这些数字与5的平均值。AVERAGE函数返回参数的平均值（算术平均值）。183语法AVERAGE(number1,[number2],...)Number1必需。要计算平均值的第一个数字、单元格引用或单元格区域。Number2,...可选。要计算平均值的其他数字、单元格引用或单元格区域，最多可包含255个。语法AVERAGE(number1,[number2],184备注参数可以是数字或者是包含数字的名称、单元格区域或单元格引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；包含零值的单元格将被计算在内。如果参数为错误值或为不能转换为数字的文本，将会导致错误。备注参数可以是数字或者是包含数字的名称、单元格区域或单元格引185提示:当对单元格中的数值求平均值时应牢记空单元格与含零值单元格的区别。提示:当对单元格中的数值求平均值时186一、实训目的掌握COUNTIF函数的应用；掌握F4快捷键的应用；掌握SUM函数的应用；掌握公式快速填允方式的应用；掌握对指定区域的分类统计；COUNTIF函数的基本应用（原理篇）一、实训目的掌握COUNTIF函数的应用；COUNTIF函数187二、实训要求根据排班表，按排班类别分类统计每类各类班别的次数，并计算出每位员工的上班次数；请对B4：AF37单元格区域中的出班安排按照“日”，“夜”，“中”，“体”，“值”进行分类统计，统计出全体人员的各类出班次数。=COUNTIF($B$4:$AF$37,""日"")二、实训要求根据排班表，按排班类别分类统计每类各类班别的次数188三、实训资料countif函数的基本应用——值班次数统计（原理篇）.xlsm三、实训资料countif函数的基本应用——值班次数统计（原189COUNTIF函数COUNTIF是一个统计函数，用于统计满足某个条件的单元格的数量；=COUNTIF(要检查哪些区域?要查找哪些内容?)例如：=COUNTIF(A2:A5,"London")=COUNTIF(A2:A5,A4)COUNTIF函数COUNTIF是一个统计函数，用于统计190语法=COUNTIF(A2:A5,"苹果")=COUNTIF(A2:A5,A4)=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)=COUNTIF(B2:B5,">55")=COUNTIF(B2:B5,"<>"&B4)=COUNTIF(B2:B5,"<>75")语法=COUNTIF(A2:A5,"苹果")191=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")统计单元格B2到B5中值大于(>)或等于(=)32且小于(<)或等于(=)85的单元格的数量。=COUNTIF(B2:B5,">=32")-COUNTIF192=COUNTIF(A2:A5,"*")统计单元格A2到A5中包含任何文本的单元格的数量。通配符星号(*)用于匹配任意字符。=COUNTIF(A2:A5,"*")统计单元格A2到193=COUNTIF(A2:A5,"?????es")统计单元格A2到A5中正好为7个字符且以字母“es”结尾的单元格的数量。通配符问号(?)用于匹配单个字符。=COUNTIF(A2:A5,"?????es")统计单元格194一、实训目的掌握datedif函数的应用；掌握if函数的应用；掌握嵌套公式的编制；掌握单元格名称的定义与引用；掌握datedif函数的“"Ｄ”设置；掌握VLOOKUP函数的模糊引用；Datedif函数的应用—工龄账龄的计算一、实训目的掌握datedif函数的应用；Datedif函数195二、实训要求请对职位；入职时间；工龄；截止日期进行单元格名称定义；并在公式计算时引用；根据工龄计算截止日期，请计算出各员工的工龄，以年为计算单位；根据职位与工龄补贴发放表，计算出各员工的工龄工资。请根据指定的方式计算两个间隔日期的天数；请根据信用期表对每位客户的账龄进行分析；二、实训要求请对职位；入职时间；工龄；截止日期进行单元格名称196三、实训资料datedif函数的基本应用——工龄工资的计算.xlsm三、实训资料datedif函数的基本应用——工龄工资的计算.197语法DATEDIF(start_date,end_date,unit)隐藏函数DATEDIF语法DATEDIF(start_date,end_date,198参数Start_date为一个日期，它代表时间段内的第一个日期或起始日期。End_date为一个日期，它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。参数Start_date为一个日期，它代表时间段内的第一个199Unit返回"Y"时间段中的整年个数。"M"时间段中的整月个数。"D"时间段中的天个数。Unit返回"Y"时间段中的整年个数。200"MD"start_date与end_date日期中单纯天数的差，忽略日期中的月MD记为:忽略月(M)的天(D)"YM"start_date与end_date日期中单纯月数的差，忽略日期中的年YM记为:忽略年(Y)的月(M)"YD"start_date与end_date日期中包含月份的天数的差，只忽略日期中的年YD记为:忽略年(Y)的天(D)--不忽略月份"MD"start_date与end_date日期中201示例=DATEDIF("2001/1/1","2003/1/1","Y")结果为:2(即时间段中有两个整年)=DATEDIF("2001/6/1","2002/8/15","D")结果为:440(即在2001年6月1日和2002年8月15日之间有440天)示例=DATEDIF("2001/1/1","2003/1/202示例=DATEDIF("2001/6/1","2002/8/15","YD")结果为:75(即在6月1日与8月15日之间有75天，忽略日期中的年)示例=DATEDIF("2001/6/1","2002/8/203示例=DATEDIF("2001/6/1","2002/8/15","MD")结果为:14(即开始日期1和结束日期15之间的差，忽略日期中的年和月)示例=DATEDIF("2001/6/1","2002/8/204五、拓展阅读

=DATEDIF(B2,TODAY(),"y")=DATEDIF(B2,TODAY(),"ym")=DATEDIF(B2,TODAY(),"md")=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"日"五、拓展阅读

=DATEDIF(B2,TODAY(),"y"205掌握嵌套公式的编制技巧=IF(AND(职位="经理",工龄_年>=10),600,IF(AND(职位="经理",工龄_年>=6,工龄_年<=9),400,IF(AND(职位="经理",工龄_年>=2,工龄_年<=5),180,IF(AND(职位="经理",工龄_年<2),0,IF(AND(职位="科长",工龄_年>=10),300,IF(AND(职位="科长",工龄_年>=2,工龄_年<=9),200,IF(AND(职位="科长",工龄_年<2),0,IF(AND(职位="职员",工龄_年>=10),100,IF(AND(职位="职员",工龄_年>=2,工龄_年<=9),50,0)))))))))1、职位=经理（1）工龄>=10年600元（2）工龄6--9年400元（3）工龄2--5年180元（4）工龄<2年0元2、职位=科长（1）工龄>=10年300元（2）工龄2--9年200元（3）工龄<2年0元3、职位=职员（1）工龄>=10年100元（2）工龄2--9年50元（3）工龄<2年0元掌握嵌套公式的编制技巧=IF(AND(职位="经理",工龄_206一、实训目的掌握VLOOKUP函数的应用；掌握EXACT函数的应用；掌握快速填充公式方式的应用；掌握F4快捷键的应用；Exact函数的应用（原理篇）一、实训目的掌握VLOOKUP函数的应用；Exact函数的应207二、实训要求根据表1，在表2中根据姓名引用表1的信箱，然后采用函数进行校验.查找出电子邮箱不一致的记录。请根据比较的数据，比较大小写；比较是否有空格等数据不一致的内容；二、实训要求根据表1，在表2中根据姓名引用表1的信箱，然后采208三、实训资料exact函数的基本应用（原理篇）.xlsm三、实训资料exact函数的基本应用（原理篇）.xlsm209EXACT函数比较两个文本字符串，如果它们完全相同则返回TRUE，否则返回FALSE。函数EXACT区分大小写，但忽略格式上的差异。操作向导EXACT函数比较两个文本字符串，如果它们完全相同操作向导210语法EXACT(text1,text2)Text1必需。第一个文本字符串。text2必需。第二个文本字符串。语法EXACT(text1,text2)211wordword公式说明结果=EXACT(A2,B2)测试第一行中的两个字符串是否完全相同TRUEwordword公式说明结果212Wordword=EXACT(A3,B3)测试第二行中的两个字符串是否完全相同（A3中的"W"为大写形式）FALSEWordword=EXACT(213wordword=EXACT(A4,B4)测试第三行中的两个字符串是否完全相同（A4的"w"和"ord"之间包含一个空格）FALSEwordword=EXACT214实训目的掌握数据验证序列的设置；掌握VLOOKUP,MATCH函数的应用；掌握match函数参数的设置；掌握动态折线图的制作与美化；Match函数——动态折线图的绘制（原理篇）实训目的掌握数据验证序列的设置；Match函数——动态折线图215二、实训要求利用数据验证，建立销售区域的序列；利用MATCH函数确定销售区域的相对位置；利用VLOOKUP函数引用指定区域各月的销售数据；绘制折线图；平滑线；加趋势线；制作动态图表。请比较Match_type参数1，0，-1对值对排序的影响。二、实训要求利用数据验证，建立销售区域的序列；216三、实训资料VLOOKUPMATCH函数的基本应用——动态图表的绘制.xlsm三、实训资料VLOOKUPMATCH函数的基本应用——动态217MATCH函数使用MATCH函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。如果A1:A3区域中包含值5、25和38，那么公式=MATCH(25,A1:A3,0)返回数字2因为25是该区域中的第二项。MATCH函数使用MATCH函数在范围单元格中搜索特定218语法MATCH(lookup_value,lookup_array,[match_type])lookup_value必需。要在lookup_array中匹配的值。lookup_array必需。要搜索的单元格区域。match_type可选。数字-1、0或1。match_type参数指定Excel如何将lookup_value与lookup_array中的值匹配。此参数的默认值为1。语法MATCH(lookup_value,lookup_a219Match_type1或省略MATCH查找小于或等于lookup_value的最大值。lookup_array参数中的值必须以升序排序，例如：...-2,-1,0,1,2,...,A-Z,FALSE,TRUE。Match_type1或省略MATCH查找小于或等于220Match_type0MATCH查找完全等于lookup_value的第一个值。lookup_array参数中的值可按任何顺序排列。Match_type0MATCH查找完全等于look221Matc