《财务管理函数》PPT课件

公式及函数的高级应用数组公式及其应用一、数组公式涵义数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。数组参数：数组公式中使用的两组或多组数据，它可以是一个数据区域也可以是数组常量。注意：公式中的每个数组参数必须有相同的行与列。二、数组公式的输入、编辑与删除（一）数组公式的输入1、输入步骤：（1）选定单元格或单元区域；（2）输入数组公式；（3）同时按下“Ctrl+Shift+Enter”组合键。2、输入数组常量的方法数组常量：直接键入的数值数组。（1）选取单元区域（2）在公式编辑栏中输入数组公式（3）同时按下“Ctrl+Shift+Enter”组合键注意：直接在公式输入数值，必须用“{}”括住；不同列的数值用逗号隔开；不同行的数值用分号隔开。2、输入数组公式的方法（1）选取单元区域（2）在公式编辑栏中输入数组公式（3）同时按下“Ctrl+Shift+Enter”组合键（二）编辑数组公式编辑步骤：1、在数组区域中单击任一单元格；2、单击公式编辑栏，当编辑栏被激活时，“{}”在数组公式中消失；3、编辑数组公式内容；4、修改完后，按“Ctrl+Shift+Enter”组合键（三）删除数组公式步骤：1、选定存放数组公式的所有单元格；2、按下Delete键二、数组公式的应用（一）计算两个数据区域的乘积（二）计算多个数据区域的和（三）同时对多个数据区域进行相同的计算一、AND函数、OR函数、NOT函数（一）AND函数1、 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示逻辑与，当所有条件都满足时，AND函数返回TRUE，否则返回FALSE。2、格式：=AND（条件1，条件2，…条件n）（二）OR函数1、表示逻辑或，只要有一个条件都满足时，该函数返回TRUE，当所有条件都不满足时才返回FALSE。2、格式：=OR（条件1，条件2，…条件n）（三）NOT函数1、只有一个逻辑参数，可以计算出TRUE或FALSE的逻辑值或逻辑表达式。2、格式：=NOT（条件）注意：以上三个函数一般与IF函数结合使用IF函数格式：IF（logical-test,value-if-true,value-if-false）其中：logical-test：为条件；value-if-true：条件为真时执行该参数；value-if-false：条件为假时执行该参数。如：=IF（TRUE，”开始”,“结束”）=IF（A1>60，”及格”,“不及格”）案例某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%。IF(AND(C5>300000,D5/C5<1%),15%,10%)等价于IF((C5>300000)*(D5/C52<1%),15%,10%)IF(OR(C5>300000,D5/C5<1%),15%,10%)等价于IF((C5>300000)+(D5/C52<1%),15%,10%)*=AND+=OR仅限于非数组公式中。二、SUM、SUMIF、SUMPRODUCT、DSUM（一）无条件求和SUM函数1、目的：对指定的若干数值或单元格求和。2、格式：=SUM（参数1，参数2，…参数N）3、若对连续的一行或一列数据进行求和时，可用工具栏中的“”按纽实现。SUM的特殊用法1、SUM（A2：A4*B2：B4）<数组输入>=SUMPRODUCT（A2：A4，B2：B4）2、SUM+IF：用于多条件求和（二）条件求和SUMIF函数1、目的：根据指定条件对若干单元格求和。常用于分类汇总计算。2、格式：=SUMIF（range，criteria,sum_range）range：用于条件判断的单元区域；criteria：确定哪些单元格将被相加求和的条件；sum-range：需要求和的实际单元格。（三）SUMPRODUCT函数1、目的：在给定的几组数组中，将数组间对应的元素相乘并返回乘积之和。2、格式：=SUMPRODUCT（array1，array2，array3，…）3、注意：数组必须具有相同的维数。假设:A2=3,B2=2,C2=5,D2=4SUMPRODUCT(A2:B2,C2:D2)=23SUMPRODUCT(A2:B2)=5SUMPRODUCT应用于多条件统计方面公式1=sumproduct（（A1:A7>10)*(A1:A7<20))公式2=sumproduct（（A1:A7>10)+(A1:A7<20))注意：1.“*”，一方面表示“且”关系，另一方面也起一个乘的作用，即将逻辑值运算成数值；or："+"2.逻辑值的运算如：FLASE+TRUE=1，FLASE*TRUE=0即在数值运算中FLASE相当于0，TRUE相当于1解释：A1:A7>10，用数组公式，返回一组逻辑值；（A1:A7>10)*(A1:A7<20），数组公式，返回一组0、1表示的值；sumproduct（（A1:A7>10)*(A1:A7<20))=sumproduct（{0，1，1，1，1，0，0}）当表示成：SUMPRODUCT（（A1：A7>10）,(A1:A7<20)）此时，结果为0。改为：SUMPRODUCT（0+（A1：A7>10）,0+(A1:A7<20)）0+：表示“强制将逻辑值进行转换”（四）DSUM函数1、目的：返回数据清单或数据库的列中满足指定条件的数字之和。2、格式：=DSUM(database,field,criteria)3、参数说明：Database  数据清单或数据库。Field  指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，也可以是代表数据清单中数据列位置的数字：1表示第一列。Criteria  为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。求和 总结 初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf 1.连续区域求和：自动求和2.不连续区域求和：SUM3.基于单条件对数字求和：SUMIF4.基于多个条件对数字求和：SUM+IF（须用数组公式确定）、SUMPRODUCT5、基于存储在其他单元区域中的条件，对数字求和：DSUM三、查找与引用函数（一）LOOKUP、HLOOKUP、VLOOKUP1.LOOKUP返回向量（单行区域或单列区域）或数组中的数值。向量形式：是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；=LOOKUP(lookup_value,lookup_vector,result_vector）Lookup_vector的数值必须按升序排序数组形式：在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。=LOOKUP(lookup_value,array)注意：1.array的数值必须按升序排序2.如果数组区域中的列数多于行数，LOOKUP在第一行查找lookup_value。3.如果数组为正方形，或者区域中的行数多于列数）则在第一列查找lookup_value。数组形式与函数HLOOKUP和函数VLOOKUP非常相似。不同之处在于函数HLOOKUP在第一行查找lookup_value，函数VLOOKUP在第一列查找，而函数LOOKUP则按照数组的维数查找。最好使用HLOOKUP或VLOOKUP来替代函数LOOKUP的数组形式2、VLOOKUP功能：用于搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行列号，再进一步返回选定单元格的值。格式：VLOOKUP（LOOKUP-VALUE，TABLE-ARRAY，COL-INDEX-NUM，RANGE-LOOKUP）其中：LOOKUP-VALUE：需要在数据表第一列中查找的数值；TABLE-ARRAY：需要在其中查找数据的数据表；COL-INDEX-NUM：第二参数中待返回的匹配值的序列号；RANGE-LOOKUP：逻辑值，指明该函数返回时是精确匹配（FALSE）还是近似匹配（TRUE或省略）。注意：range_lookup为TRUE，则table_array的第一列中的数值必须按升序排列；为FALSE，table_array不必进行排序。HLOOKUP：与VLOOKUP含义相同，只是LOOKUP-VALUE是在第一行。（二）MATCH函数1、目的：返回在指定方式下与指定数值匹配的数组中元素的相应位置。2、格式：=MATCH（lookup-value，lookup-array，match-type）Lookup_value  为需要在数据表中查找的数值。Lookup_array  可能包含所要查找的数值的连续单元格区域。Match_type  为数字-1、0或1。其中：1：查找小于或等于lookup_value的最大数值。Lookup_array必须按升序排列0：查找等于lookup_value的第一个数值。Lookup_array可以按任何顺序排列。-1：查找大于或等于lookup_value的最小数值。Lookup_array必须按降序排列。如果省略match_type，则假设为1。（二）INDEX函数1、目的：返回表格或区域中的数值或对数值的引用。2、格式一（返回值为数值或数组）=INDEX（array，row-num,column-num）array：单元区域或数组常量；row-num：数组中某行的行号，函数从该行返回数值；若省略，则必须有column-numcolumn-num：数组中某列的列号，函数从该列返回数值；若省略，则必须有row-num注意：1、若同时使用row-num和column-num，该函数将返回row-num和column-num交叉处单元格的数值；2、若将row-num或column-num设置为0，则该函数返回整个行或列的数组数值。但这时，须按数组公式的形式输入。如：INDEX({1,2;3,4},2,2)等于4如果作为数组公式输入，则：INDEX({1,2;3,4},0,2)等于{2;4}格式二：（返回值为引用）=INDEX(reference,row_num,column_num,area_num)其中：1、Reference  对一个或多个单元格区域的引用；如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num或column_num分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,,column_num)。2、Row_num  引用中某行的行序号，函数从该行返回一个引用。3、Column_num  引用中某列的列序号，函数从该列返回一个引用4、Area_num  选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX使用区域1。如果引用描述单元格为(A1:B4,D1:E4,G1:H4)，则area_num1为区域A1:B4、area_num2为区域D1:E4、而area_num3为区域G1:H4。例8INDEX(Fruit,2,3)等于引用C3，内容为38INDEX((A1:C6,A8:C11),2,2,2)等于引用B9，内容为$3.55SUM(INDEX(Stock,0,3,1))等于SUM(C1:C11)等于216SUM(B2:INDEX(Fruit,5,2))等于SUM(B2:B6)等于2.42INDEX+MATCHINDEX(D2:D7,MATCH(1,(A10=B2:B7)*(B10=C2:C7),0))1=TRUE*TRUE，即两个条件同时满足公式必须是数组确定，即Ctrl+Shift+Enter四、数据库函数（一）DGET函数目的：从数据清单或数据库中提取符合条件且唯一存在的值。如果没有满足条件的记录，则DGET将返回错误值#VALUE!。如果有多个记录满足条件，则DGET将返回错误值#NUM!。格式：=DGET（database,field,criteria）其中：database：构成数据清单或数据库的单元区域；Field：双引号括住的列标签或该列在列表中的数字；Criteria：为一组包含给定条件的单元格区域（二）DMAX功能：返回数据清单或数据库的列中满足指定条件的最大数值。格式：=DMAX(DatabaseRange,FieldName,CriteriaRange)DMIN与DMAX相反。（三）DSUM（四）DCOUNT目的：返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数。格式：=DCOUNT(database,field,criteria)参数Field为可选项。如果省略，则函数将返回数据库中满足条件的所有记录数。（五）DCOUNTA：返回数据库或数据清单的列中满足指定条件并且包含数字的非空单元格个数。COUNTIF函数功能：计算区域中满足给定条件的单元格的个数格式：COUNTIF(range,criteria)其中：Range  为需要计算其中满足条件的单元格数目的单元格区域。Criteria  为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。计数函数总结1、根据条件计数：COUNTIF、DCOUNT、DCOUNTA2、区域中空白单元格的个数：COUNTBLANK3、列表中含数字或文本的单元格个数：COUNTA4、统计列表中包含数字的单元格的个数：COUNT文本函数1、EXACT功能：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则，返回FALSE。函数EXACT能区分大小写，但忽略格式上的差异。格式：=EXACT(text1,text2)2、FIND功能：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号。格式：=FIND(find_text,within_text,start_num)3、FINDB功能：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并基于每个字符所使用的字节数从within_text的首字符开始返回find_text的起始位置编号。此函数用于双字节字符4、LEFT功能：基于所指定的字符数返回文本字符串中的第一个或前几个字符。格式：=LEFT(text,num_chars)如果num_chars大于文本长度，则LEFT返回所有文本。RIGHT函数则返回最后一个或多个字符5、LEN功能：返回文本字符串中的字符数格式：=LEN(text)6、MID功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。格式：=MID(text,start_num,num_chars)带星号“*”的函数要安装了“ 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 工具箱”之后才能使用。