任务二　函数的使用

任务描述

日常工作中有时我们需要计算大量的数据信息,如果不采取有效的计算方法,这将是一件很令人头疼的事情。Excel为我们提供了丰富的常用函数功能,用户通过使用这些函数就能对复杂数据进行计算。函数是由电子表格预先定义,执行计算、分析等处理数据任务的特殊公式,用于对一个或多个执行运算的数据进行指定的计算。参与运算的数据称为函数的参数,可以是数字、文本、逻辑值、数组、常量、公式、其他函数或单元格引用。

任务分析

在Excel中使用函数能够使我们的工作更加简单轻松,如果要对函数运用自如,需要熟悉Excel提供的常用函数及其具体用法。

知识链接

每个函数都由函数名和变量组成,其中函数名表示将执行的操作,变量表示函数将作用的数值所在的单元格地址,通常是一个单元格区域,也可以是更为复杂的内容。在公式中合理地使用函数,可以完成如求和、逻辑判断和财务分析等众多的数据处理功能。

1.函数的书写格式

函数由函数名和参数组成,其一般格式为

函数名用来描述函数的功能,通常用大写字母表示。参数可以是单元格引用、数字、公式或其他函数。例如,SUM(Number1,Number2,Number3,…)是一个求和函数,其中“SUM”是函数名,“Number1,Number2,Number3,…”是函数参数,且参数用一对括号“()”括起来。

在输入函数时,需要注意以下语法规则。

①函数必须以等号“=”开始,如“=MAX(A1:B5)”。

②当函数的参数个数多于1时,需要用逗号“,”作为分隔符。

③函数的参数须用括号“()”括起来。

④函数的参数如果是文本,则需要用英文双引号“＂＂”括起来。

⑤函数的参数可以是定义好的单元格或单元格区域名、数组、单元格引用、数值、公式或其他函数。

2.函数的分类

①财务函数:可以进行一般的财务计算。例如,确定贷款的支付额、投资的未来值或净现值以及债券或息票的价值。

②时间和日期函数:可以在公式中分析和处理日期值和时间值。

③数学和三角函数:可以处理简单和复杂的数学计算。

④统计函数:用于对数据进行统计分析。

⑤查找和引用函数:在工作表中查找特定的数值或引用的单元格。

⑥数据库函数:分析工作表中的数值是否符合特定条件。

⑦文本函数:可以在公式中处理字符串。

⑧逻辑函数:可以进行真假值判断,或者进行复合检验。

⑨信息函数:用于确定存储在单元格中的数据的类型。

⑩工程函数:用于工程分析。

3.函数的输入

使用函数时,应首先在单元格中输入“=”号,进入公式编辑状态,然后再输入函数名称,函数名称后紧跟着输入一对括号,括号内为一个或多个参数,参数之间需要用逗号进行分隔。在工作表中输入函数的方法主要有手工输入和使用“函数向导”两种。具体方法及操作步骤如下。

(1)手工输入函数。单击需要输入函数的单元格,然后依次输入等号、函数名、左括号、具体参数和右括号,最后单击“编辑栏”中的“输入”按钮或按“Enter”键,此时在输入函数的单元格中将显示公式的运算结果。

(2)使用“函数向导”。如果不能确定函数的拼写或参数,可以使用“函数向导”插入函数。具体操作步骤如下。

①单击要插入函数的单元格,单击“编辑栏”左侧的“插入函数”按钮,或者单击“公式”→“函数库”→“插入函数”按钮。

②弹出“插入函数”对话框,在“选择函数”列表框中选择合适的函数,如图4-23所示。

图4-23　插入函数对话框

③单击“确定”按钮,弹出“函数参数”对话框,如图4-24所示。

图4-24　函数参数对话框

④单击按钮,在工作表中拖动鼠标选择需要参与计算的单元格区域。选择好后,单击按钮,返回“函数参数”对话框。单击“确定”按钮,完成公式的插入,在对应单元格中返回计算结果。

4.常用函数的使用

Excel 2010提供了200多个函数,根据函数的实际功能分成几大类型,用户按照具体应用选择合适的函数,下面介绍一些常用的函数。

(1)数学和三角函数

使用数学和三角函数,可以对单元格内的数据进行一些简单的数学计算。例如,对选定单元格区域中的数值求和,或对数值进行四舍五入等。表4-1列出了常见的数学和三角函数。

表4-1　数学和三角函数

①SUM函数。SUM函数是求和函数,用于求出指定参数的总和。其函数格式为

【说明】其中SUM是函数名,参数number 1,number 2可以是单元格引用、单元格区域、函数或数值。

②RAND函数。RAND函数是随机函数,该函数产生的值是介于[0,1)之间的随机小数,其函数格式为

用户可以用公式:“=RAND()*(b-a)+a”产生介于[a,b)之间的随机数,如果产生的随机数要包括b,则在括号中加1,即公式改为“=RAND()*(b-a+1)+a”。

【说明】当使用RAND函数产生随机数时,每次工作表计算的结果都不一样。

(2)统计函数

统计函数是用于对数据区域进行统计分析的函数,主要功能包括统计某个区域数值的平均值、最大值、最小值,对数据进行相关概率分布统计和线性回归分析等操作。表4-2列出了常见的统计函数。

表4-2　统计函数

①AVERAGE和AVERAGEA函数——计算平均值。AVERAGE函数用于计算所选区域中所有单元格的平均值。其语法形式为

其中number1,number2,…为要计算平均值的参数(1～30个),这些参数可以是数字或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格,则忽略其值。但是,如果单元格包含零值,则计算在内。AVERAGEA函数则是用于计算所选区域中所有非空单元格的平均值的函数,用法跟AVERAGE函数一样。

②COUNT和COUNTA函数——求单元格个数。COUNT函数用于统计参数列表中含有数值数据的单元格个数。其语法形式为

其中value1,value2,…为包含或引用各种类型数据的参数(1～30个)。但只有数字类型的数据才被计数。COUNT函数在计数时,可以把数字、空值、逻辑值、日期或以文字代表的数计算进去。但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用,那么只统计数组或引用中的数字,数组或引用中的空单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值,应当使用COUNTA函数,其用法跟COUNT函数一样。

③MAX和MIN函数——求最大值和最小值。这两个函数用来求解数据集的极值,即最大值、最小值。函数的用法非常简单,语法形式为

其中number1,number2,…为需要找出最大数值的1～30个数值,如果参数为数组或引用,那么数组或引用中的空白单元格、逻辑值或文本将被忽略。因此,如果逻辑值和文本不能被忽略,则使用带A的函数MAXA或MINA进行计算。

④RANK函数——排序函数。RANK函数用于返回一个数字在指定参数列表中的排位。数字的排位是其大小与指定参数列表中其他值排序后所处的位置(如果所指定的参数列表已经排过序,则数字的排位就是它当前的位置)。其函数格式为

【说明】参数“Number”是需要找到排位的数字;参数“Ref”是包含一组数字的单元格区域引用或一组数字,且在指定的参数范围内非数值型数据将被忽略;参数“Order”是一数字,指明排位的方式,如果Order值为0或省略,Microsoft Excel将Ref按照降序排列,如果Order不为零,Microsoft Excel将Ref按照升序排列。

⑤COUNTIF函数——指定求和函数。COUNTIF函数用于计算指定参数中满足特定条件的单元格个数。其函数格式为

【说明】参数range用于指明需要计算满足条件的单元格区域。参数criteria为特定条件,可以是具体的数值、表达式或文本。

⑥FREQUENCY函数——频率分布统计函数。FREQUENCY函数用于对一列垂直的数组(或数值)进行分段,计算出该数组(或数值)落在每个分段区间的数据个数。其函数格式为

【说明】参数Data_array为一数组或对一组数值的引用,用来计算频率。如果该参数指定的数组或引用不包含任何数值,则FREQUENCY函数返回零数组。

参数Bins_array为一数组或对数组区域的引用,即设置对Data_array参数进行频率统计的各分段区域的分段点。如果该参数不包含任何数值,则FREQUENCY函数返回Data_array参数中数据元素的个数。

另外,在指定参数Bins_array的分段点时应遵循下列规律:

假设Bins_array参数分别设为A 1,A 2,A 3,…,A n。则其对应的分段区间应为

即分段点应为每个分段区间的最大值,且分段点个数比分段区间个数少1。

(3)逻辑函数

逻辑函数也称为条件函数,用户使用逻辑函数可以对指定参数进行真假判断以及复合检验,表4-3列出常见的逻辑函数。

IF函数是条件选择函数,其根据Logical_test(条件表达式)参数的值判断真假,返回不同的计算结果。其函数格式为

【说明】Logical_test参数指定可进行真假值判断的表达式。

Value_if_true参数指定当Logical_test值为“真”时的返回值,省略时返回“TRUE”。

Value_if_false参数指定当Logical_test值为“假”时的返回值,省略时返回“FALSE”。

表4-3　逻辑函数

(4)文本函数

使用文本函数,用户可以在公式或函数中处理字符串。表4-4列出了常见的文本函数。

表4-4　文本函数

(5)日期和时间函数

使用日期和时间函数可以对日期时间型数据进行处理。表4-5列出了常见的日期和时间函数。

表4-5　日期和时间函数

(6)数据库函数

数据库函数用于对存储在数据清单或数据库中的数据进行统计分析,使用数据库函数可以在数据清单中计算满足一定条件的数据的值。数据库函数的共同特征如下。

①每个数据库函数都有三个参数:Database、Field和Criteria。

②除了GETPIVOTDATA函数之处,其他每个数据库函数都以字母D开头。

③如果将函数名的字母D去掉,其与统计函数中函数名一样。例如,将DCOUNT函数的字母D去掉,就是统计函数中的计数函数COUNT。表4-6列出了常见的数据库函数。

表4-6　数据库函数

数据库函数的语法格式:函数名(Database,Field,Criteria)。

例如,DAVERAGE(Database,Field,Criteria),每个数据库函数都具有相同的这三个参数。这三个参数的含义如下。

①Database:指构成数据清单或数据库的单元格区域,包含字段名。数据库是包含一组相关数据的数据清单,其中包含相关信息的行为记录,包含数据的列为字段。数据清单的第一行包含着每一列的列标题,即字段名。

②Field:用于指定函数所使用的数据列。数据清单中的数据列必须在第一行具有列标题。Field可以是文本,即两端带引号的字段名,如“性别”或“数据结构”;另外,Field也可以是代表数据列在数据清单中所在位置的数字,如1表示第一列,2表示第二列等。

③Criteria:为一组包含给定条件的单元格区域。可以为Criteria参数指定任意区域,只要它至少包含一个列标题和列标题下方用于设定条件的单元格区域。

(7)财务函数

EXCEL提供了许多财务函数,这些函数大体上可分为四类:投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。这些函数为财务分析提供了极大的便利。利用这些函数,可以进行一般的财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值等。表4-7列出了常用的投资计算财务函数。

表4-7　投资计算财务函数

在财务函数中有两个常用的变量:f和b,其中f为年付息次数,如果按年支付,则f=1;按半年期支付,则f=2;按季支付,则f=4。b为日计数基准类型,如果日计数基准为“US(NASD)30/360”,则b=0或省略;如果日计数基准为“实际天数/实际天数”,则b=1;如果日计数基准为“实际天数/360”,则b=2;如果日计数基准为“实际天数/365”,则b=3;如果日计数基准为“欧洲30/360”,则b=4。下面简要介绍表4-7中所列出的财务函数。

①PMT函数。PMT函数的格式为

该函数基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。其中,r为各期利率,是一固定值;np为总投资(或贷款)期,即该项投资(或贷款)的付款期总数;pv为现值,是一系列未来付款当前值的累积和,也称为本金;fv为未来值,是在最后一次付款后希望得到的现金余额,如果省略fv,则假设其值为零(如一笔贷款的未来值即为零);t为0或1,用以指定各期的付款时间是在期初还是期末,如果省略t,则假设其值为零。

例如,需要10个月付清的年利率为8%的10 000元贷款的月支额为PMT(8%/12,10,10 000),则计算结果为-1 037.03。对于同一笔贷款,如果支付期限在每期的期初,则支付额应为PMT(8%/12,10,10 000,0,1),其计算结果为-1 030.16。

②PV函数。PV函数的格式为

该函数用于计算某项投资的年金现值,年金现值就是未来各期年金现在价值的总和。如果投资回收的当前价值大于投资的价值,则这项投资是有收益的。

例如,借入方的借入款即为贷出方贷款的现值。其中r为各期利率,如果按10%的年利率借入一笔贷款来购买住房,并按月偿还贷款,则月利率为10%/12(即0.83%)。可以在公式中输入10%/12、0.83%或0.008 3作为r的值;np为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,对于一笔4年期按月偿还的住房贷款,共有4×12(即48)个偿还期次,可以在公式中输入48作为np的值;p为各期应付(或得到)的金额,其数值在整个年金期间(或投资期内)保持不变,通常p包括本金和利息,但不包括其他费用及税款。例如,10 000元的年利率为12%的四年期住房贷款的月偿还额为263.33,可以在公式中输入263.33作为p的值;fv为未来值,是在最后一次支付后希望得到的现金余额,如果省略fv,则假设其值为零(一笔贷款的未来值即为零)。

③FV函数。FV函数的格式为

该函数基于固定利率及等额分期付款方式,用来返回某项投资的未来值。其中r为各期利率,是一固定值;np为总投资(或贷款)期,即该项投资(或贷款)的付款期总数;p为各期所应付给(或得到)的金额,其数值在整个年金期间(或投资期内)保持不变,通常p包括本金和利息,但不包括其他费用及税款;pv为现值,或一系列未来付款当前值的累积和,也称为本金,如果省略pv,则假设其值为零;t为数字0或1,用以指定各期的付款时间是在期初还是期末,如果省略t,则假设其值为零。

例如,FV(0.6%,12,-200,-500,1)的计算结果为3 032.90;FV(0.9%,10,-1 000)的计算结果为10 414.87;FV(11.5%/12,30,-2 000,1)的计算结果为69 796.52。④NPV函数。NPV函数的格式为

该函数基于一系列现金流和固定的各期贴现率,用来返回一项投资的净现值。投资的净现值是指未来各期支出(负值)和收入(正值)的当前值的总和。其中,r为各期贴现率,是一固定值;v 1,v 2,…代表1～29笔支出及收入的参数值,v 1,v 2,…所属各期间的长度必须相等,而且支付及收入的时间都发生在期末,NPV按次序使用v 1,v 2,…来注释现金流的次序,所以一定要保证支出和收入的数额按正确的顺序输入。如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式,则都会计算在内;如果参数是错误值或不能转化为数值的文字,则被忽略;如果参数是一个数组或引用,只有其中的数值部分被计算在内,数组或引用中的空白单元格、逻辑值、文字及错误值均被忽略。(https://www.daowen.com)

例如,假设第一年投资8 000元,而未来三年中各年的收入分别为2 000、3 300和5 100元。假定每年的贴现率是10%,则投资的净现值是NPV(10%,-8 000,2 000,3 300,5 800),其计算结果为8 208.98元。

(8)查找函数

Excel中的查找函数有很多,在实际工作中会经常用到的查找函数有MATCH()、LOOKUP()、HLOOKUP()和VLOOKUP(),这些查找函数不仅仅具有查对的功能,还能根据查找的结果和参数的设定得到我们需要的数值。特别是当这几个函数配合使用,并以逻辑函数IF()来辅助时,用户就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。

LOOKUP()、HLOOKUP()和VLOOKUP()函数的功能都是在数组或表格中查找指定的数值,并按照函数参数设定的值返回表格或数组当前列(行)中指定行(列)处的数值。

由于LOOKUP()函数是在单行(列)区域查找数值,并返回第二个单行(列)区域中相同位置的数值,或是在数组的第一行(列)中查找数值,返回最后一行(列)相同位置处的数值,其适用范围具有比较大的局限性。在实际的应用中,我们通常使用更加灵活的HLOOKUP()和VLOOKUP()函数。

HLOOKUP()和VLOOKUP()的作用类似,其区别:是HLOOKUP()是在表格或数组的首行查找数值,返回表格或数组当前列中指定行的数值;而VLOOKUP()是在表格或数组的首列查找数值,并返回表格或数组当前行中指定列的数值。这里所说的表格是按单元格地址设定的一个表格区域,如A2:E8。VLOOKUP()函数的格式如下:

各参数说明如下。

①lookup_value——为需要在表格数组第一列中查找的数值。其中,数组用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式;数组区域共用一个公式;数组常量是用作参数的一组常量。Lookup_value可以为数值或引用。若lookup_value小于table_array第一列中的最小值,VLOOKUP将返回错误值“#N/A”。

②table_array——为两列或多列数据。请使用对区域的引用或区域名称。table_array第一列中的值是由lookup_value搜索的值,这些值可以是文本、数字或逻辑值,不区分大小写。

③col_index_num——为table_array中待返回的匹配值的列序号。当col_index_num为1时,返回table_array第一列中的数值;当col_index_num为2时,返回table_array第二列中的数值,以此类推。如果col_index_num小于1,VLOOKUP返回错误值“#VALUE!”;如果col_index_num大于table_array的列数,VLOOKUP返回错误值“#REF!”。

④range_lookup——为一逻辑值。当range_lookup为TRUE或被省略时,要求table_array第一行的数据必须升序排列,否则会得到错误的结果,同时表示待查找内容与查找内容近似匹配就可以了,如果不能精确匹配的话,则函数返回小于lookup_value的最大数值;当range_lookup为FALSE时,不需要table_array的数值进行排序,并要求待查找内容与查找内容精确匹配,如果没有找到则函数返回“#N/A”。

任务设计

1.数学函数和统计函数的应用

“数学函数和统计函数应用”工作表中的数据如图4-25所示,请使用相应函数计算总销售金额、最高销售金额、最低销售金额和平均销售金额,统计单价超过3 000元的销售记录条数,统计销售数量小于20、在20到30之间、在30到40之间以及大于40的记录各有几条。操作步骤如下。

图4-25　“产品销售表”工作表

(1)求总销售金额(SUM函数)

①单击I3单元格,单击“编辑栏”左侧的“插入函数”按钮。

②弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“SUM”函数。

③单击“确定”按钮,弹出“函数参数”对话框,单击Number1文本框右侧按钮,在工作表中拖动鼠标选择参与计算的H3:H26单元格区域。选择好后,单击按钮,返回“函数参数”对话框。

④单击“确定”按钮,完成公式的插入,在I3单元格中计算出总销售金额。

(2)求最高销售金额(MAX函数)

①单击I5单元格,单击“编辑栏”左侧的“插入函数”按钮。

②弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“MAX”函数。

④单击“确定”按钮,完成公式的插入,在I5单元格中计算出最高销售金额。

(3)求最低销售金额(MIN函数)

①单击I7单元格,单击“编辑栏”左侧的“插入函数”按钮。

②弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“MIN”函数。

④单击“确定”按钮,完成公式的插入,在I7单元格中计算出最低销售金额。

(4)求平均销售金额(AVERAGE函数)

①单击I9单元格,单击“编辑栏”左侧的“插入函数”按钮。

②弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“AVERAGE”函数。

④单击“确定”按钮,完成公式的插入,在I9单元格中计算出平均销售金额。

(5)统计单价超过3 000元的销售记录条数(COUNTIF函数)

①单击I12单元格,单击“编辑栏”左侧的“插入函数”按钮。

②弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“COUNTIF”函数。

③单击“确定”按钮,弹出“函数参数”对话框,按图4-26所示输入各参数值。

图4-26　COUNTIF函数的参数值

④单击“确定”按钮,完成公式的插入,在I12单元格中计算出单价超过3 000元的销售记录条数为6条。

(6)统计销售数量分布在不同数据段的记录数(FREQUENCY函数)

①建立分段点,根据分段区间分别在单元格I16:I18中输入19,29,39。

②选定存放统计结果的单元格区域J16:J19。由于FREQUENCY函数根据分段区间统计的结果有多个,因此需要选择多个单元格来存放输出结果,且选定的单元格个数比分段点个数多1。单击“编辑栏”左侧的“插入函数”按钮。

③弹出“插入函数”对话框,单击“选择类别”文本框右侧下拉按钮,选择“统计”命令。在“选择函数”列表框中选择“FREQUENCY”函数。

④单击“确定”按钮,弹出“函数参数”对话框,按图4-27所示输入各参数值。

图4-27　FREQUENCY函数的参数值

⑤同时按“Ctrl”+“Shift”+“Enter”快捷键,在单元格区域J16:J19统计出销售数量分布在不同数据段的记录数。

完成计算后的工作表数据统计结果如图4-28所示。

图4-28　“产品销售表”的计算结果

2.逻辑函数、文本函数、日期和时间函数的应用

打开“其他函数应用”工作表,其数据如图4-29所示。根据A列空气污染指数,在B列对应的单元格中使用IF函数计算其空气质量状况:空气污染指数201～300的为“不佳”,空气污染指数101～200的为“普通”,空气污染指数51～100的为“良”,空气污染指数小于50的为“优”;根据D列员工的身份证号码,在E列计算每位员工的出生年月日;根据F列每位员工的工作日期,在G列计算每位员工的工龄。其操作步骤如下。

图4-29　“其他函数应用”工作表

(1)统计空气质量状况(IF函数)

①选择单元格B2,使其成为活动单元格,在“编辑栏”中输入公式:=IF(A2＞200,＂不佳＂,IF(A2＞100,＂普通＂,IF(A2＞50,＂良＂,＂优＂)))。

②按“Enter”键,在B2单元格计算出空气质量状况为“不佳”。

③选定B2单元格,拖动填充柄到B13单元格,计算其他空气污染指数对应的空气质量状况。

(2)计算出生年月日(MID函数)

①选择单元格E2,使其成为活动单元格,在“编辑栏”中输入公式:=MID(D2,7,8)。

②按“Enter”键,在E2单元格计算出的出生年月为“19780620”。

③选定E2单元格,拖动填充柄到E13单元格,计算其他身份证号码对应的出生年月。

(3)计算工龄(YEAR函数)

①选择单元格G2,使其成为活动单元格,在“编辑栏”中输入公式:=YEAR(NOW())-YEAR(F2)。

②按“Enter”键,在G2单元格计算出的工龄为“12”。

③选定G2单元格,拖动填充柄到G13单元格,计算其他工作日期对应的工龄。

完成计算后的“其他函数应用”工作表如图4-30所示。

图4-30　其他函数的应用结果

3.数据库函数的应用

对“数据库函数”工作表中的产品销售数据进行计算,先根据“产品”分别统计出“三星”“SONY爱立信”两种产品的平均销售金额,要求条件区域建立在J2:K3单元格区域,计算结果存放在J4:K4单元格区域中;然后根据“产品”和“型号”统计出型号为“P990c”的“SONY爱立信产品”的销售记录有几条,要求条件区域建立在J6:K7单元格区域,计算结果放在J8单元格中,其操作步骤如下。

(1)建立条件区域

按照任务要求,在J2:K3单元格区域和J6:K7单元格区域建立条件,如图4-31所示。