第三节-公式与函数的应用

1、精选优质文档-倾情为你奉上第三节公式与函数的应用一、公式的应用（）（一）公式的概念及其构成例如：=68+B2*3-SUM（C1:C5）包含：=、运算体、运算符（公式总是以等号“=”开始）1.运算体是指能够运算的数据或者数据所在单元格的地址名称、函数等。上例中68、B2、SUM（C1:C5）2.运算符是使Excel自动执行特定运算的符号。Excel中,运算符主要有四种类型:算术运算符、比较运算符、文本运算符、引用运算符。（1）算术运算符:可以完成基本的数学运算,包括: “+”（加）、“-”（减）、“*”（乘）、“/”（除）、“”（乘方）等,运算的结果为数值。（2）比较运算符:可以比较两个同类型的

2、数据（都是数值或都是字符或都是日期）,包括: “=” （等于）、“>” （大于）、“<”（小于）、“>=”（大于等于）、“<=” （小于等于）、“<>” （不等于）,运算的结果为逻辑值TRUE或FALSE。（3）文本运算符:“&”（连接运算符）,用于把前后两个字符串连接在一起,生成一个字符串。算术运算符和文本运算符优于比较运算。（4）引用运算符:是Excel特有的运算符,用于单元格引用,可以将单元格区域合并运算,包括:区域运算符“:”（冒号）:产生对包括在两个引用之间的所有单元格的引用,上例中SUM（C1:C5）。联合运算符“,”（逗号）:将多个引用

3、合并为一个引用,即取多个区域的并集如SUM （A1:A5,C1:C5）。交叉运算符“ ”（空格）:产生对多个引用共有的单元格的引用,即多个区域的交集,如SUM （A1:B5 A4:D9）,相当于SUM （A4:B5）。（二）公式的创建与修改1.公式的创建手动输入、移动点击输入编辑栏显示公式 单元格显示计算结果【链接】报表管理模块中公式的录入，需要在“显示公式”或“格式”状态下。2.公式的编辑快捷键：F2（三）公式的运算次序1.优先级次相同的运算符：从左至右运算2.多个优先级次不同的运算符：从高至低运算比如小括号、乘号等优先级较高。（四）公式运算结果的显示1.查看公式中某步骤的运算结果快捷键：F

4、9（查看）、“Esc或Ctrl+Z”（恢复）【提示】先按F2进入公式编辑状态，再按F9查看。2.公式默认显示方式的改变快捷键：Ctrl+（数字1左边的按键）功能区：公式显示公式3.将公式运算结果转换为数值复制选择性粘贴【例题单选题】下列属于区域运算符的是（）。A.“/”B.“&”C.“:”D.“,” 正确答案C答案解析选项A为算术运算符；选项B为文本运算符；选项D为联合运算符。二、单元格的引用（）例如：=68+B2*3-SUM（C1:C5）（一）引用的类型1.相对引用：Excel默认使用的单元格引用2.绝对引用例如：=68+$B$2*3-SUM（C1:C5）3.混合引用例如：=68+$

5、B2*3-SUM（C1:C5）【例题·实务操作题】如图所示，计算学生的总成绩。（二）输入单元格引用1.在列标和行标前直接输入“$”符号。2.输入完单元格地址以后，重复按“F4”键选择合适的引用类型。【例题·实务操作题】把图中D2的单元格公式改为“=$B$2+$C$2”,然后将公式复制到单元格D3中,D3中的值与D2相同,引用地址没有改变。（三）跨工作表单元格引用工作表名!数据源所在单元格地址例如：=68+Sheet1!$B$2*3-SUM（C1:C5）（四）跨工作簿单元格引用工作簿名工作表名!数据源所在单元格地址例如：=68+Book2Sheet1!$B$2*3-SUM（C

6、1:C5）三、函数的应用（）基本格式：函数名（参数序列）函数只能出现在公式中。除中文外都必须使用英文半角字符,参数无大小写之分。【提示】重点把握参数的含义和运用。（一）常用函数1.统计函数（1）MAXMAX（number1，number2，）用于返回数值参数中的最大值，忽略参数中的逻辑值和文本。（2）MINMIN（number1，number2，）用于返回数值参数中的最小值，忽略参数中的逻辑值和文本。（3）SUMSUM（number1，number2，）用于计算单元格区域中所有数值的和。（4）SUMIFSUMIF（range，criteria，sum_range）用于对满足条件的单元格求和。R

7、ange代表要进行计算的的单元格区域（包括条件判断区）Criteria数字、表达式或文本形式定义的条件，条件要用双引号引起来sum_range用于求和计算的实际单元格，如果省略，则使用Range定义的区域（5）AVERAGEAVERAGE（number1，number2，）用于返回参数的算术平均值。（6）AVERAGEIFAVERAGEIF（range，criteria，sum_range）用于返回某个区域内满足给定条件的所有单元格的算术平均值。（7）COUNTCOUNT（va1ue1，va1ue2，）用于计算包含数字的单元格以及参数列表中数字的个数。（8）COUNTIFCOUNTIF（ran

8、ge，criteria）用于对区域中满足单个指定条件的单元格进行计数，条件表达式同样要用引号引起来。2、文本函数（1）LENLEN（text）用于返回文本字符串中的字符数。（2）RIGHTRIGHT（text，num_chars）用于从文本字符串中最后一个字符开始返回指定个数的字符。（3）MIDMID（text，start_num，num_chars）用于返回文本字符串中从指定位置开始的指定数目的字符。（4）LEFTLEFT（text，num_chars）用于返回文本字符串中第一个字符开始至指定个数的字符。3、逻辑函数IFIF（1ogica1_test，va1ue_if_true，va1ue_

9、if_fa1se）用于判断“1ogica1_test”的内容是否为真，如果为真则返回“va1ue_if_true”，如果为假则返回“va1ue_if_fa1se”的内容。4、查找与引用函数（1）LOOKUPLOOKUP 函数可返回一行或一列区域中或者数组中的某个值，LOOKUP 函数具有矢量和数组两种语法形式： 矢量形式的 LOOKUP矢量形式的 LOOKUP 在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。矢量形式的语法LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value 是 LOOKUP 在

10、第一个矢量中指定要搜索的值（或值的仅似值）。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。Lookup vector 是一个仅包含一行或一列的区域，这一区域实际上就是前面指定Lookup_value的搜索范围。lookup vector 中的值可以是文本、数字或逻辑值。result_vector是与Lookup vector区域相对应的行或列，这两个区域的单元格是相互对应的，也就是说，在Lookup vector区域中找到一个值，在result_vector区中相对位置上就有一值与之对应（这个值就是我们公式运行的结果）如果在Lookup vector区域中

11、 找不到 lookup_value值，系统会匹配 lookup vector 中小于或等于 lookup_value 的最大值。如果lookup vector 单元中的所有数据都大于lookup_value给定值，则 LOOKUP 会返回 #N/A 错误值。重要说明：首先，lookup vector 中的值必须按升序排列，否则，LOOKUP 返回的值可能不正确，大写和小写文本是等效的。其次，Result_vector 是一个仅包含一行或一列的区域。它的大小必须与 lookup vector 相同。再次，lookup vector区域不能全为大于lookup_value指定值的单元。数组形式的

12、LOOKUP数组形式的 LOOKUP 在数组的第一行或列中查找指定值，然后返回该数组的最后一行或列中相同位置处的值。如果要匹配的值位于数组的第一行或列中，请使用这种形式的 LOOKUP。数组形式的语法LOOKUP(lookup_value,array)Lookup_value 是 LOOKUP 需要在第一行或第一列中搜索的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。 如果 LOOKUP 找不到 lookup_value，它会使用该数组第一行或第一列中小于或等于 lookup_value 的最大值。如果 lookup_value 小于第一行或列（取决于

13、数组维度）中的最小值，则 LOOKUP 会返回 #N/A 错误值。Array 是一个单元格区域，其中包含要与 lookup_value 进行比较的文本、数字或逻辑值。如果 array 所覆盖区域的宽度大于高度（列多于行），则 LOOKUP 会在第一行中搜索 lookup_value。如果 array 所覆盖的区域是正方形或者高度大于宽度（行多于列），则 LOOKUP 会在第一列中进行搜索。也就是说，LOOKUP在查找给定值是时，总是以数据最多的向方找，行列数相等时，以列为查找依据。array 中的值必须按升序顺序排列。（2）INDEXINDEX（array，row_num，co1umn_num

14、）用于返回表格或数组中的元素值，此元素由行序号和列序号的索引值给定。Array代表单元格区域或数组常量；row_num表示指定的行序号；co1umn_num表示指定的列序号；注意：此处的行序号参数和列序号参数是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。（3）MATCHMATCH（1ookup_va1ue，1ookup_array，match_type）用于在单元格区域中（一般为行或列）搜索指定项，然后返回该项在单元格区域中的相对位置。1ookup_va1ue代表需要在数据表中查找的数值；1ookup_array表示可能包含所要查找的数值的连续单元格区域；match_t

15、ype表示查找方式的值（-1，0，1），。如果match_type为-1，表示查找大于或等于1ookup_va1ue的最小值，这时1ookup_array区域中的数值必须按降序排列。如果match_type为0，表示查找等于1ookup_va1ue的第一个数值，这时1ookup_array区域中的数值可按任意顺序排列。如果match_type为1，表示查找小于或等于1ookup_va1ue的最大值，这时1ookup_array区域中的数值必须按升序排列。5.日期与时间函数（1）YEARYEAR（seria1_number）用于返回某日期对应的年份。（2）MONTHMONTH（seria1_nu

16、mber）用于返回某日期对应的月份，介于1到12之间。（3）DAYDAY（seria1_number）用于返回某日期对应的天数，介于1到31之间。（4）NOWNOW（）用于返回当前电脑系统的日期和时间，按F9可刷新当前值，该函数不需要参数。（二）基本财务函数1、SLNSLN（cost，sa1vage，1ife）用于返回某项资产以直线法（比如平均年限法、工作量法等）计提的每一期的折旧值。cost指固定资产原值，sa1vage指固定资产的残值，1ife指固定资产的折旧期数（也称固定资产的使用寿命）。2、DDBDDB（cost，sa1vage，1ife，period，factor）用于使用双倍余额递

17、减法计算一项固定资产在给定期间内的折旧值。cost指固定资产原值，sa1vage指固定资产的残值，1ife指固定资产的折旧期数（即固定资产寿命，可以是年，也可以是月），period指需要计算折旧值的期间（第几年或第几个月），period必须使用与1ife相同的单位（同为年或同为月），factor是可选参数，指余额递减速率，如果factor被省略，则默认为2，即使用双倍余额递减法。3、SYDSYD（cost，sa1vage，1ife，per）用于返回某项资产按年数总和折旧法计算的在第“per”期的折旧值。cost指固定资产原值，sa1vage指固定资产的残值，1ife指固定资产的折旧期数，per指第几期，其单位必须与1ife相同。（三）补充函数（非大纲要求）（1）ROUND（四舍五入函数）Round(number,num_digits)用于返回给定数值的四舍五入后的值。（2）INT（取整函数）Int(number)用于返回给定数值的整数部分。（3）VLOOKUP（水平查找函数）VLOOKUP（1oo