EXCEL财务应用函数

EXCEL中的财务函数应用及超链接的建立山东理工大学管理学院孙芳EXCEL中的财务函数应用及超链接的建立EXCEL中的财务函数应用及超链接的建立课程提纲1.超链接2.EXCEL函数及其应用（重点）EXCEL中的财务函数应用及超链接的建立第一课超链接•工作表单元间的相互关联（公式位置的引用）•程序间的相互跳转（超链接或指定宏）EXCEL中的财务函数应用及超链接的建立1.公式位置的引用•Excel的查找与引用的灵活应用对于减少重复数据的录入是大有裨益的。•1、引用的作用•在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。EXCEL中的财务函数应用及超链接的建立•2、引用的含义•关于引用需要了解如下几种情况的含义：•外部引用--不同工作簿中的单元格的引用称为外部引用。三维地址的引用：从另外一个工作簿文件的不同工作表中引用单元格。表示方式为：[工作簿.xls]工作表！单元格或区域•远程引用--引用其它程序中的数据称为远程引用。EXCEL中的财务函数应用及超链接的建立相对地址引用：当把一个含有单元格地址的公式复制到一个新的位置或用该公式填充一个范围时，公式中单元地址随着改变。行复制、列复制绝对地址引用：当把一个含有单元格地址的公式复制到一个新的位置或用该公式填充一个范围时，公式中单元地址保持不变。如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。混合地址引用EXCEL中的财务函数应用及超链接的建立•3、立体表计算举例：在财务分析中，要构建比率分析模型，其中资产管理比率中有一项叫存货周转率：存货周转率=主营业务成本/平均存货成本•立体表数据汇总计算表示方法：表名1:表名N!引用单元EXCEL中的财务函数应用及超链接的建立2.程序间的相互跳转1、超链接载体可以是文本、图片、自选图形等。链接目标可以是工作表、工作簿、其他文件、网络上的文件甚至是电子邮件。•链接到工作表的某一位置：①插入|名称|定义②插入|超链接③设置•链接到其他文件上：插入|超链接或者：①复制原文本②编辑|粘贴为超链接比较：选择性粘贴|粘贴链接•=HYPERLINK(“网页地址”，“将要被显示的名称”)EXCEL中的财务函数应用及超链接的建立•撤销超链接的方法•回退•右键|取消超级链接•取消比较多的超级链接：•ALT+F8，在SUB与ENDSUB之间输入Cells.hyperlinks.delete保存到创建的宏后返回EXCEL，工具|宏|执行EXCEL中的财务函数应用及超链接的建立指定宏•录制宏•指定宏EXCEL中的财务函数应用及超链接的建立第二课EXCEL常用函数介绍•数学函数的基本语法及运用•文本函数的基本语法及运用•子字符串函数的基本语法及运用•逻辑函数的基本语法及运用•查找和引用函数的基本语法及运用•财务函数的基本语法及运用EXCEL中的财务函数应用及超链接的建立•对于这各类函数，要求尽可能能熟悉其语法形式，了解其功能，掌握各参数的含义，又因为函数间可以嵌套使用，在实践中运用非常灵活。EXCEL中的财务函数应用及超链接的建立函数基础•EXCEL函数基本上由三部分组成，即函数名称、括号和参数，其表示形式为•函数名称（参数1，参数2……）•其中，函数名称是指函数的含义，如SUM求和，AVERAGE求平均值，括号即括住参数的符号，即括号中包含所有参数，参数是告诉EXCEL所欲执行的目标单元或数值，参数应用逗号隔开。EXCEL中的财务函数应用及超链接的建立函数的使用有两种方式•直接在公式中输入函数。这种方法是选择单元格，输入=，然后按照函数的语法直接键入。这种方法适用于用户对函数非常熟悉，清楚的了解函数的结构及其参数的意义的情形。•使用插入菜单的函数命令。这种方法是通过函数向导完成函数的输入，适用于用户对函数的功能或结构不太熟悉或函数本身过于复杂等情况。EXCEL中的财务函数应用及超链接的建立数学函数的基本语法及运用•函数的基本组成•函数名称（参数1，参数2，……）•常用的数学函数主要有：•Sum()、sumif()用于分类汇总、ABS()返回参数的绝对值、sign()用于判断参数的正负还是0，分别用1，-1，0表示、INT()取整（小于等于参数）、MOD()求余、SQRT()求正数的平方根、round()四舍五入,例：=ROUND(355.26,-2)、rand()产生0-1之间的随机数EXCEL中的财务函数应用及超链接的建立•例：假设要投资一项产品的研发，预计销售量、单价均为某一范围内的随机数，成本固定（简化模型），试判断是否值得投资。(生成图表)•Frequency(data_array,bins_array)以一列垂直数组返回某个区域中数据的频率分布。•Count()：在计数时将把数字、空值、逻辑值、日期或文字代表的数计算进去，但错误值或其他无法转化成数字的文字则被忽略。但是，如果参数是一个数组或引用，那么只统计其中的数字，数组或引用的空单元格、逻辑值、文字或错误值都将被忽略。•Counta():返回参数列表中非空值的单元格个数。利用它可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值，则使用COUNT（）。例：数据的拆分与合并字符串函数EXCEL中的财务函数应用及超链接的建立逻辑函数的基本语法及使用•用来判断指定的条件是否成立。•比较运算符：=,,,=,=,•IF（逻辑表达式，真时值，假时值）、AND（）、OR（）、NOT（）、SUMIF（）、COUNTIF（）。EXCEL中的财务函数应用及超链接的建立IF()逻辑判断函数•格式：if(逻辑表达式，value-if-true,value-if-false)•功能：执行真假值判断，根据逻辑条件的真假值，返回不同的结果。•最多可嵌套7层。•例题：对某公司的销售业绩进行分级评价。销售额大于100万元，销售评价为A级；70---100万元，销售评价为B级；=70万元，销售评价为C级。EXCEL中的财务函数应用及超链接的建立SUMIF（）•格式：SUMIF(range,criteria,Sum-range)•作用：根据指定条件对若干单元格求和Range：用于条件判断的单元格区域•Criteria：判断哪些单元格将被相加求和的条件，其形式可以为数字，表达式，或文本，如32，“32”，“32”,”apples”EXCEL中的财务函数应用及超链接的建立Countif()•格式：Countif(range,criteria)•作用：根据条件统计符合条件的数字或字符串出现的次数•若要根据某一条件返回两个备选项中的某一值，用IF()•若要计算基于一个文本字符串或某范围内的一个数值的总和，可用SUMIF()EXCEL中的财务函数应用及超链接的建立AND()•所有参数的计算结果为TRUE时，返回TRUE；只要有一个参数的计算结果为FALSE，即返回FALSE。•AND函数的一种常见用途就是扩大用于执行逻辑检验的其他函数的效用。例如，IF函数用于执行逻辑检验，它在检验的计算结果为TRUE时返回一个值，在检验的计算结果为FALSE时返回另一个值。通过将AND函数用作IF函数的logical_test参数，可以检验多个不同的条件，而不仅仅是一个条件。AND(logical1,[logical2],...)最多可包含255个条件。•参数的计算结果必须是逻辑值（如TRUE或FALSE），而参数必须是包含逻辑值的数组或引用。•如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。•如果指定的单元格区域未包含逻辑值，则AND函数将返回错误值#VALUE!。EXCEL中的财务函数应用及超链接的建立OR()•在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。•语法•OR(logical1,logical2,...)•Logical1,logical2,...是1到255个需要进行测试的条件，测试结果可以为TRUE或FALSE。•注解•参数必须能计算为逻辑值，如TRUE或FALSE，或者为包含逻辑值的数组或引用。EXCEL中的财务函数应用及超链接的建立NOT()•对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT。•语法•NOT(logical)•Logical可以计算出TRUE或FALSE的值或表达式。•注解•如果logical为FALSE，NOT返回Yes；如果logical为TRUE，NOT返回No。EXCEL中的财务函数应用及超链接的建立•例：已知各月份的销售明细记录，试统计各种产品的销售情况。（数据透视表、SUMIF（）、分类汇总）EXCEL中的财务函数应用及超链接的建立第三课文本函数的基本语法及使用•数字形式的文本的输入方法：•公式输入法•在该数字串前加上单引号•value（）将文本转化成数字、text（）用指定的格式将数字转换为一个文本字符串、len（）用来测出一个文本字符串的长度、trim（）将前导空格、后随空格、字符间多于一个的空格滤去、exact（）用于比较两个字符串是否完全匹配，是一个条件函数，返回值为true或falseEXCEL中的财务函数应用及超链接的建立子字符串函数的基本语法及使用•子字符串函数用来解决一个字符串与其本身某个部分的关系问题，或一个较短的字符串与一个较长的字符串之间的关系问题。•right（）用于指定字符串中提取右边N个字符、left（）用于指定字符串中提取左边N个字符、mid（）用于从指定字符串中第M个字符开始提取N个字符、find（字符串1，字符串2，M）用于在字符串2中，从第M个字符开始寻找字符串1，并返回位置数、rept（字符串，M）用于将指定字符串重复M次以组成一个新字符串、substitute(字符串，旧子符串，新字符串，M,N)其中字符串中包含N个旧子字符串，执行此操作后，将用新子字符串替换字符串中的第M个旧字符串。EXCEL中的财务函数应用及超链接的建立例：数据的分分合合•数据的拆分与提取•数据的合并•数据的合并计算EXCEL中的财务函数应用及超链接的建立数据的拆分与提取•例：想利用邮件合并功能给每位客户发送贺卡或邮件，需要把通讯地址和邮政编码分开。•数据|分列•Left()、right()、mid():分别从左、右和中间提取单元格的文本字符。•LEN()返回某一单元格的字符总个数EXCEL中的财务函数应用及超链接的建立数据的合并•例：客户资料中有客户的身份证信息，现在想要根据身份证号把客户出生年月日的信息提取出来，在客户生日时给他意外的惊喜，以提高客户的满意度和忠诚度。两种方法：1、&2、concatenate()功能：将几个文本字符串合并为一个文本字符串。也可以用&运算符代替函数CONCATENATE实现文本项的合并。•从身份证信息中提取顾客的性别信息（在第17位）=if(mod(mid(身份证号，17，1)，2)=1，“男”，“女”)EXCEL中的财务函数应用及超链接的建立数据的合并计算•例：资本成本是指企业为筹集和使用资金而付出的代价。在筹资决策中，比较各个方案的综合资本成本，选择综合资本成本小者进行投资。一般的做法是计算出每种筹资方式的资金成本，然后再根据该种资金占总资金的比重计算出加权平均资金成本。但是如果我们改用数组，就可以只键入一个公式来完成这些运算。数组公式录入+sum():ctrl+shift+回车sumproduct（）：返回若干组彼此对应元素乘积之和。EXCEL中的财务函数应用及超链接的建立数组：•数组就是单元的集合或是一组处理的值集合。•可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。•数组公式可以看成是有多重数值