第5章使用数学和统计函数处理数据
数学函数和统计函数用于数据的处理和统计分析，例如求和、求平均值、数值取整、四舍五入、计数、求最值等。
本章首先介绍数学函数和统计函数的常用应用——计数、求和与求均值，然后系统地介绍统计函数的语法、功能和应用实例，最后系统地介绍数学和三角函数的语法、功能和应用实例。


视频讲解


5.1计数、求和与平均
5.1.1计数与求和函数

在Excel数据处理中，经常需要统计满足指定条件的数据的个数和数据之和，例如统计不及格学生人数； 统计某商品的销售额、统计某部门员工工资之和等。
在Excel中，计数公式用于统计给定单元格区域内满足指定条件的单元格个数； 求和公式用于统计给定单元格区域内满足指定条件的单元格的数值之和。
Excel提供了丰富的工作表函数，用于记数与求和，如表51所示。


表51Excel记数与求和函数



函数说明

COUNT返回单元格区域中包含数值的单元格个数
COUNTA返回单元格区域中非空的单元格个数
COUNTBLANK返回单元格区域中空值的单元格个数
COUNTIF返回单元格区域中满足单个指定条件的单元格个数
COUNTIFS返回单元格区域中满足多个指定条件的单元格个数
FREQUENCY返回数值在单元格区域内出现的频率，仅用于多单元格数组公式
续表


函数说明

SUM返回单元格区域中的数值和
SUMIF返回单元格区域中满足单个指定条件的数值和
SUMIFS返回单元格区域中满足多个指定条件的数值和
SUMPRODUCT返回多个单元格区域的元素之间的乘积后求和







5.1.2COUNT、COUNTA和COUNTBLANK函数
COUNT、COUNTA和COUNTBLANK函数用于统计单元格个数。其语法为： 

 COUNT(value1, ［value2］, …)

 COUNTA(value1, ［value2］, …)

 COUNTBLANK(range)

其中，参数value为单元格引用、区域或常量值，参数range为单元格区域。3个函数的具体功能如下： 
 COUNT函数统计指定单元格区域或参数列表中数字、日期或代表数字的文本(不包括不能转换为数字的文本或错误值)的个数。
 COUNTA函数统计指定单元格区域中不为空的单元格(包括错误值和空文本(""))的个数。
 COUNTBLANK函数统计指定单元格区域中空白单元格的个数。
例如，给定名称引用为data的单元格区域，则： 
(1) 公式“=COUNTBLANK(data)”统计data数据区域中空值单元格的个数。
(2) 公式“=COUNTA(data)”统计data数据区域中非空单元格的个数。
(3) 公式“=COUNT(data)”统计data数据区域中数字数据(包括日期以及代表数字的文本)单元格的个数。
(4) 数组公式“{=SUM(IF(ISTEXT(data),1))}”统计data数据区域中文本数据单元格的个数。
(5) 数组公式“{=SUM(IF(ISNONTEXT(data),1))}”统计data数据区域中非文本数据单元格的个数。
(6) 数组公式“{=SUM(IF(ISLOGICAL(data),1))}”统计data数据区域中逻辑数据单元格的个数。
(7) 数组公式“{=SUM(IF(ISERROR(data),1))}”统计data数据区域中错误单元格的个数。
【说明】
(1) ISTEXT信息函数用于判断单元格是否为文本； ISNONTEXT信息函数用于判断单元格是否为非文本； ISLOGICAL函数用于判断单元格是否为逻辑类型值。
(2) ISERROR信息函数用于判断单元格是否为公式错误信息(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!)。
(3) 用户也可以使用ISERR信息函数判断单元格是否为除“#N/A”以外的任何错误提示信息，或者使用ISNA信息函数判断指定值是否为错误信息“#N/A”。
(4) 用户还可以使用COUNTIF函数统计特定类型错误的单元格的个数。例如“=COUNTIF(Data,"#DIV/0!")”统计data数据区域中错误提示信息为“#DIV/0!”(公式中除法运算的分母为0)的单元格的个数。
5.1.3COUNTIF和COUNTIFS函数
COUNTIF和COUNTIFS函数用于根据条件统计单元格个数。其语法为： 

COUNTIF(range, criteria)

COUNTIFS(criteria_range1, criteria1, ［criteria_range2, criteria2］…)

其中，参数range是单元格或单元格区域，参数criteria是条件。
COUNTIFS函数可指定多个区域和条件。
条件criteria用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串或函数。例如，条件可以表示为3.14159、"<50"、D6、"200062"、"Mary"或者TODAY()等。
在条件中可以使用通配符，即问号(?)和星号(*)。其中，?(问号)匹配任意单个字符，*(星号)匹配任意字符串。例如，"张*"匹配所有张姓的名字。如果要查找实际的问号或者星号，在字符前输入波形符(~)。
例如，假设单元格区域C3：C69(命名为grades)中存放着65名学生的百分制成绩，则： 
(1) 公式“=COUNTIF(grades,100)”统计数据区域grades中100分的学生人数。
(2) 公式“=COUNTIF(grades,"<60")”统计数据区域grades中不及格的学生人数。
(3) 公式“=COUNTIFS(grades,">=75",grades,"<=85")”统计数据区域grades中75~85分的学生人数。
【注意】
任何文本条件或者任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。如果条件为数字，则无须使用双引号。
【例51】学生成绩分段统计(COUNT、COUNTIF和COUNTIFS函数)。在“fl51学生成绩(分段统计COUNT).xlsx”的B3：B67单元格区域中(名称为classes)包含了65名学生的班级信息，C3：C67单元格区域中(名称为grades)包含了学生的大学计算机考试成绩，利用COUNT、COUNTIF和COUNTIFS函数完成如下操作，结果如图51所示。


图51学生成绩分段统计结果(COUNT)


(1) 统计40分以下、40~49分、50~59分、60~69分、70~79分、80~89分、90~99分以及100分各分数段的学生人数。
(2) 计算各分数段所占的百分比。
(3) 统计各班不及格的学生人数。

【参考步骤】
(1) 统计各分数段的学生人数。在F2单元格中输入公式“=COUNTIF(grades,"<40")”,在F3单元格中输入公式“=COUNTIFS(grades,">=40",grades,"<=49")”，在F4至F8单元格中输入类似公式，在F9单元格中输入公式“=COUNTIF(grades,100)”。
(2) 计算各分数段所占的百分比。在G2单元格中输入公式“=F2/COUNT(grades)”，并向下拖曳填充至G9单元格。设置数据的百分比显示格式，保留整数部分。
(3) 统计各班不及格的学生人数。在J2单元格中输入公式“=COUNTIFS(grades,"<60",classes,"一班")”，在J3和J4单元格中输入类似公式。
5.1.4FREQUENCY函数
用户除了可以使用公式统计数据的分布频率外，还可以使用FREQUENCY函数、数据透视表(参见本书第13章)、数据分析工具库(参见本书第14章)统计数据的分布频率。
FREQUENCY函数根据分段点计算并返回指定区域的数据的频率分布。例如，统计学生的成绩分布、统计员工的年龄分布、统计不同工资段的员工分布等。其语法为：  

FREQUENCY(data_array, bins_array)

其中，参数data_array是为其计算频率的数据区域； 参数bins_array是对data_array中的数值进行分段的数据区域。
FREQUENCY是数组函数，其参数data_array和bins_array均为数组(单元格区域)； 返回结果为数组。
例如，假设数据区域grades中存放学生成绩，单元格区域F2：F3中存放分段点数据(59、84)，则公式“=FREQUENCY(grades,F2：F3)”返回grades数据区域中对应于0~59、60~84、85以上3个区段的频率数组。


图52学生成绩分段统计结果(FREQUENCY)


【例52】学生成绩分段统计(FREQUENCY函数)。在“fl52学生成绩(分段统计FREQUENCY).xlsx”的C3：C67单元格区域中(名称为grades)包含了各班级大学计算机的考试成绩信息，利用FREQUENCY函数统计40分以下、40~49分、50~59分、60~69分、70~79分、80~89分、90~99分以及100分各分数段的学生人数，结果如图52所示。
【参考步骤】
(1) 准备成绩分段点。为了使用FREQUENCY函数统计数值在区域内出现的频率，在H2：H8单元格区域中输入整理后的成绩分段点。
(2) 统计各分数段的学生人数。选择结果数据区域F2：F9，在编辑栏中输入公式“=FREQUENCY(grades, H2：H8)”，按Ctrl+Shift+Enter键确认数组公式的输入。
【说明】
FREQUENCY函数返回的数组中的元素个数比bins_array(分段区间数组)中的元素个数多1个，多出来的那个元素表示最高区间之上的数值个数。
5.1.5SUM函数
SUM函数用于计算所有参数的数值之和。其语法为： 

SUM(number1,［number2］,…)

其中，参数number可以是单元格区域、单元格引用、数组、常量、公式或者另一个函数的结果。参数的个数最多为255个。
在使用SUM函数求和时，如果参数是数组或者引用，则只计算其中的数字，数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数是常量，则数字文本自动转换为数值，TRUE转换为1，FALSE转换为0。如果参数为错误值，或者不能转换为数值，则会导致错误。
例如，假设A1：A5单元格区域中分别存放着数据"张三"、80、75、TRUE、FALSE。则： 
(1) 公式“=SUM(A1：A5)”的结果为155(将80和75相加，忽略"张三"、TRUE和FALSE)。
(2) 公式“=SUM(A1：A5,5,FALSE)”的结果为160(将80、75、5和由FALSE转换成的0相加)。
(3) 公式“=SUM("5",15,TRUE)”的结果为21(将"5"转换成的5、将TRUE转换成1，与15相加)。
(4) 公式“=SUM("a",15,RUE)”的结果为参数值错误#VALUE!。
5.1.6SUMIF和SUMIFS函数
SUMIF和SUMIFS函数用于根据条件求和。其语法为： 

SUMIF(range, criteria, ［sum_range］)

SUMIFS(sum_range, criteria_range1, criteria1, ［criteria_range2, criteria2］, …)

其中，参数range是区域(条件区域/求和区域)，criteria_range是条件区域，criteria是条件，sum_range是求和区域。
SUMIF函数如果没有指定参数sum_range，则参数range同时用于条件区域和求和区域。
SUMIFS函数可指定多个区域和条件，条件的形式同COUNTIF函数。
例如，假定数据区域dept(B2：B17)中存放部门信息，数据区域gender(C2：C17)中存放性别信息，数据区域payment(D2：D17)中存放工资信息，则： 
(1) 公式“=SUM(payment)”计算全体员工工资之和。
(2) 公式“=SUMIF(dept,"开发部",payment)”计算开发部员工工资之和。
(3) 公式“=SUMIF(payment,">1000")”计算工资高于1000的员工工资之和。
(4) 公式“=SUMIFS(payment,dept,"开发部",gender,"男")”计算开发部男职员工资之和。该公式等价于以下公式： 

{=SUM((dept="开发部")*(gender="男")*payment)}

{=SUM(((B2：B17="开发部")*(C2：C17="男"))*D2：D17)}

=SUMPRODUCT((dept="开发部")*(gender="男")*payment)

=SUMPRODUCT((B2：B17="开发部")*(C2：C17="男")*D2：D17)
(5) 对于计算类似于“开发部以及技术部员工工资之和”的问题，则一般采用以下公式实现： 

{=SUM(((dept="开发部")+(dept="技术部"))*payment)}

=SUMPRODUCT(((dept="开发部")+(dept="技术部"))*payment)
5.1.7SUMPRODUCT和SUMSQ函数
Excel求和函数还包括SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2和SUMXMY2。其语法为： 

SUMPRODUCT(array1, ［array2］, ［array3］, …)

SUMSQ(number1, ［number2］, …)

SUMX2MY2(array_x, array_y)

SUMX2PY2(array_x, array_y)

SUMXMY2(array_x, array_y)

其中，参数number为数值，参数array为单元格区域(数组)。SUMPRODUCT和SUMSQ函数的参数个数最多为255个。
SUMPRODUCT返回多个数组的元素之间的乘积后求和。数组中的非数值元素作为0处理。如果array1、array2等的元素数目不同，则会导致错误值#VALUE!。
SUMSQ返回各参数值的平方之和。当参数为单元格引用时，忽略其中的非数值单元格； 常量参数自动转换为数值； 如果参数为错误值或为不能转换为数字的文本，将会导致错误值#VALUE!。
SUMX2MY2返回两数组中对应数值的平方差之和∑(x2-y2)； SUMX2PY2返回两数组中对应数值的平方和之和∑(x2+y2)； SUMXMY2返回两数组中对应元素之差的平方和∑(x-y)2。数组中的非数值元素作为0处理。如果array_x和array_y的元素数目不同，则会导致错误值#N/A。

假设数组(单元格区域)内容如图53所示，则： 


图53数组元素求和



(1) 公式“=SUMSQ(A1，B1)”的结果为17，即A12+B12=12+42=17。
(2) 公式“=SUMPRODUCT(A1：A3,B1：B3)”的结果为32，即A1*B1+A2*B2+A3*B3=1*4+2*5+3*6=32。
(3) 公式“=SUMX2MY2(A1：A3,B1：B3)”的结果为-63，即(A12-B12)+(A22-B22)+(A32-B32)=(12-42)+(22-52)+(32-62)=-63。
(4) 公式“=SUMX2PY2(A1：A3,B1：B3)”的结果为91，即(A12+B12)+(A22+B22)+(A32+B32)=(12+42)+(22+52)+(32+62)=91。
(5) 公式“=SUMXMY2(A1：A3,B1：B3)”的结果为27，即(A1-B1)2+(A2-B2)2+(A3-B3)2=(1-4)2+(2-5)2+(3-6)2=27。
(6) 数组公式“{=SUM((A1：A3*B1：B3))}”等同于公式“=SUMPRODUCT(A1：A3,B1：B3)”，结果均为32。



图54销售业绩累计和统计


【例53】销售业绩累计和统计(SUM函数)。在“fl53销售业绩(累计和统计).xlsx”中包含2019年按月销售业绩，计算销售业绩累计和，结果如图54所示。
【参考步骤】
在单元格C3中输入公式“=SUM(B$3：B3)”，双击单元格C3右下角的填充句柄，将公式自动填充至单元格C14。
【说明】
在公式中使用了混合引用B$3，保证填充的公式从B$3单元格开始累计。
【例54】职工工资表求和统计。在“fl54职工工资表(求和统计).xlsx”中包含了某公司15名职工的姓名、部门、性别和工资信息，请利用SUM、SUMIF、SUMIFS和SUMPRODUCT函数以及数组公式按要求完成如下操作，结果如图55所示。
(1) 统计全体员工工资之和(SUM函数)。
(2) 统计开发部工资之和(SUMIF函数)。
(3) 统计开发部男职员工资之和(SUMIFS函数)。
(4) 使用数组公式(SUM配合*运算)统计开发部男职员工资之和、开发部以及技术部工资之和。
(5) 统计开发部男职员工资之和、开发部以及技术部工资之和(SUMPRODUCT函数)。


图55职工工资表求和统计


【参考步骤】
(1) 统计全体员工工资之和(SUM函数)。在单元格G3中输入公式“=SUM(D3：D17)”。
(2) 统计开发部工资之和(SUMIF函数)。在单元格G4中输入公式“=SUMIF(B3：B17,"开发部",D3：D17)”。

(3) 统计开发部男职员工资之和(方法1：SUMIFS函数)。在单元格G5中输入公式“=SUMIFS(D3：D17,B3：B17,"开发部",C3：C17,"男")”。
(4) 统计开发部男职员工资之和(方法2： 使用数组公式、SUM配合*运算)。在单元格G6中输入数组公式“{=SUM(((B3：B17="开发部")*(C3：C17="男"))*D3：D17)}”。
(5) 统计开发部男职员工资之和(方法3：SUMPRODUCT函数)。在单元格G7中输入公式“=SUMPRODUCT((B3：B17="开发部")*(C3：C17="男")*D3：D17)”。
(6) 统计开发部职员以及技术部职员工资之和(方法1： 使用数组公式、SUM配合+运算)。在单元格G8中输入数组公式“{=SUM(((B3：B17="开发部")+(B3：B17="技术部"))*D3：D17)}”。
(7) 统计开发部职员以及技术部职员工资之和(方法2：SUMPRODUCT函数)。在单元格G9中输入公式“=SUMPRODUCT(((B3：B17="开发部")+(B3：B17="技术部"))*D3：D17)”。
【例55】商品采购求和统计。在“fl55商品采购(求和统计SUMPRODUCT).xlsx”中存放着77种商品的销售信息，利用SUMPRODUCT函数以及数组公式按要求完成如下操作： 
(1) 使用SUMPRODUCT函数统计库存商品总金额与订购商品总金额。
(2) 使用数组公式(SUM配合*运算)统计库存商品总金额与订购商品总金额。
结果如图56所示。


图56商品采购求和统计


【参考步骤】
(1) 统计库存商品总金额(方法1：SUMPRODUCT函数)。在单元格H2中输入公式“=SUMPRODUCT(D2：D78,E2：E78)”。
(2) 统计订购商品总金额(方法1：SUMPRODUCT函数)。在单元格I2中输入公式“=SUMPRODUCT(D2：D78,F2：F78)”。
(3) 统计库存商品总金额(方法2： 数组运算)。在单元格H3中输入数组公式“{=SUM(D2：D78*E2：E78)}”。
(4) 统计订购商品总金额(方法2： 数组运算)。在单元格I3中输入数组公式“{=SUM(D2：D78*F2：F78)}”。
5.1.8AVERAGE、AVERAGEIF和AVERAGEIFS函数
AVERAGE、AVERAGEA、AVERAGEIF和AVERAGEIFS函数用于求平均值。其语法为： 

AVERAGE(number1,［number2］,…)

AVERAGEA(value1, ［value2］, …)

AVERAGEIF(range, criteria, ［average_range］)

AVERAGEIFS(average_range, criteria_range1, criteria1, ［criteria_range2, criteria2］, …)

其中，参数number和value可以是单元格区域、单元格引用、数组、常量、公式或者另一个函数的结果，参数的个数最多为255个。参数range是区域(条件区域/求平均值区域)，参数criteria_range是条件区域，参数criteria是条件，参数average_range是求平均值区域。
AVERAGEIF函数如果没有指定参数average_range，则参数range同时用于条件区域和求平均值区域。AVERAGEIFS函数可指定多个区域和条件，条件的形式同COUNTIF函数。
常量参数中的数字文本自动转换为数值，TRUE转换为1，FALSE转换为0。
AVERAGE函数忽略单元格区域、单元格、数组参数中的文本、逻辑值； 而AVERAGEA函数则包括单元格区域、单元格、数组参数中的文本、逻辑值，文本转换为0，TRUE转换为1，FALSE转换为0。
如果参数为错误值，或者不能转换为数值，则会导致错误。
例如，假定数据区域dept(B2：B17)中存放部门信息，数据区域gender(C2：C17)中存放性别信息，数据区域payment(D2：D17)中存放工资信息，则： 
(1) 公式“=AVERAGE(payment)”计算全体职员的平均工资。
(2) 公式“=AVERAGEIF(dept,"开发部",payment)”计算开发部职员的平均工资。
(3) 公式“=AVERAGEIFS(payment,dept,"开发部",sex,"男")”计算开发部男职员的平均工资。
【例56】职工工资表求平均值。在“fl56职工工资表(平均AVERAGE).xlsx”中包含了某公司15名职工的姓名、部门、性别、职称和工资信息，请利用AVERAGE、AVERAGEIF和AVERAGEIFS函数按要求完成如下操作，结果如图57所示。
(1) 统计全体职员的平均工资(AVERAGE函数)。
(2) 统计开发部职员的平均工资(AVERAGEIF函数)。
(3) 统计开发部男职员的平均工资(AVERAGEIFS函数)。


图57职工工资表求平均值


【参考步骤】
(1) 统计全体职员的平均工资。在单元格H3中输入公式“=AVERAGE(E3：E17)”。
(2) 统计开发部职员的平均工资。在单元格H4中输入公式“=AVERAGEIF(B3：B17,"开发部",E3：E17)”。
(3) 统计开发部男职员的平均工资。在单元格H5中输入公式“=AVERAGEIFS(E3：E17,B3：B17,"开发部",C3：C17,"男")”。
5.1.9TRIMMEAN函数
TRIMMEAN函数用于计算排除数据集顶部和底部尾数中数据点的百分比后取得的平均值。其语法为： 

TRIMMEAN(array,percent)
其中，参数array是数值单元格区域(数组)； 参数percent用于指定排除数据点的百分比。例如，percent=0.1，则30个数据点的10%(即0.1)等于3个数据点，然后自动向下舍入到最接近的2的倍数，即排除两个点(最大值和最小值)。如果percent<0或者percent>1，则TRIMMEAN函数返回错误值#NUM!。
例如，假设单元格区域B3：G3中存放6个裁判的打分，则计算去掉最高和最低分后选手的平均得分的公式为“=TRIMMEAN(B3：G3,2/COUNT(B3：G3))”，也可以使用公式“=(SUM(B3：G3)-MAX(B3：G3)-MIN(B3：G3))/(COUNTA(B3：G3)-2)”。
【例57】运动员平均得分(TRIMMEAN、SUM、MAX、MIN和COUNT函数)。在“fl57运动员成绩(平均值TRIMMEAN).xlsx”中存放着15名体操运动员某次比赛时6个裁判所给的打分信息，计算去掉最高和最低分后各选手的平均得分。具体要求如下，结果如图58所示。
(1) 使用TRIMMEAN函数完成操作。
(2) 使用SUM、MAX、MIN和COUNT函数完成操作。


图58运动员成绩(平均值TRIMMEAN)


【参考步骤】
(1) 方法1： 使用TRIMMEAN函数计算平均得分。在单元格H3中输入公式“=TRIMMEAN(B3：G3,2/COUNT(B3：G3))”，并填充至单元格H17。
(2) 方法2： 使用SUM、MAX、MIN和COUNT函数计算平均得分。在单元格I3中输入公式“=(SUM(B3：G3)-MAX(B3：G3)-MIN(B3：G3))/(COUNT(B3：G3)-2)”，并填充至单元格I17。


视频讲解


5.2统计函数
除了5.1节介绍的统计函数之外，Excel还提供了求最值、排名、中值、众数统计、分类汇总等统计函数。有关专业的统计函数，例如各种统计分布函数、各种统计检验函数，本书没有展开阐述。
5.2.1MAX、MIN、MAXA和MINA函数
MAX、MIN、MAXA和MINA函数用于返回最大值或最小值。其语法为： 

MAX(number1, ［number2］, …)

MIN(number1, ［number2］, …)

MAXA(value1,［value2］,…)

MINA(value1,［value2］,…)

其中，参数number可以是数字，也可以是包含数字的名称、数组或者引用。参数的个数最多为255个。参数value可以是数值； 包含数值的名称、数组或者引用； 数字文本； 逻辑值TRUE或者FALSE。
常量参数中的数字文本自动转换为数值，TRUE转换为1，FALSE转换为0。
MAX和MIN函数在求值时忽略单元格区域、单元格、数组参数中的文本和逻辑值； 而MAXA和MINA函数则包括单元格区域、单元格、数组参数中的文本和逻辑值，其中文本转换为0，TRUE转换为1，FALSE转换为0。
如果参数为错误值，或不能转换为数值，则会导致错误。
例如，假设单元格区域A1：A6中分别存放着数据"张三"、89、75、54、68、TRUE，则： 
(1) 公式“=MAX(A1：A6)”的结果为89。
(2) 公式“=MIN(A1：A6)”的结果为54。
(3) 公式“=MAXA(A1：A6,"95")”的结果为95。
(4) 公式“=MINA(A1：A6)”的结果为0。
(5) 公式“=MAX("a",15,TRUE)”的结果为参数值错误#VALUE!。
5.2.2LARGE和SMALL函数
LARGE和SMALL函数用于返回指定单元格区域(或数组)中第position个最大值和最小值。其语法为： 

LARGE(range, position)

SMALL(range, position)

其中，参数range为包含数据的单元格区域(或数组)，参数position为排序位置。
例如，给定名称引用为data的数据区域，则： 
(1) 公式“=LARGE(data,1)”返回最大值。
(2) 公式“=LARGE(data,10)”返回第10个最大值。
(3) 公式“=SMALL(data,1)”返回最小值。
(4) 公式“=SMALL(data,2)”返回第2个最小值(倒数第2)。
(5) 数组公式“{=SUM(LARGE(data,{1,2,3,4,5,6,7,8,9,10}))}”求10个最大数之和。
(6) 数组公式“{=SUM(SMALL(data,ROW(1： 10)))}”求10个最小数之和。
【说明】
ROW函数返回引用的行号，例如公式“=ROW(A1)”的结果为1； 公式“=ROW(1：1)”的结果为1； 公式“{=ROW(1：10)}”的结果为数组{1；2；3；4；5；6；7；8；9；10}。
5.2.3RANK、RANK.AVG和RANK.EQ函数
RANK、RANK.AVG和RANK.EQ函数用于返回指定数值在单元格区域(或数组)中的顺序位置。其语法为： 

RANK(number, ref, ［order］)

RANK.AVG(number, ref, ［order］)

RANK.EQ(number, ref, ［order］)

其中，参数number是数值； ref是单元格区域(或数组)； 可选参数order为排序方式(默认0为降序，非零值为升序)。如果number在range中不存在，则结果为#N/A。
如果存在多个相同的值，则RANK.AVG将排名的平均值赋予重复值，RANK.EQ和RANK则赋予重复值相同的排名。例如对于数值“12、11、11、10”，RANK.AVG的排名依次为1、2.5、2.5、4； 而使用RANK.EQ和RANK的排名依次为1、2、2、4。
例如，给定名称引用为data的单元格区域，则： 
(1) 公式“=RANK(80,data)”返回80在区域data中的排名(从高到低降序)。
(2) 公式“=RANK(80,data,1)”返回80在区域data中的排名(从低到高升序)。
【例58】班级排名和年级排名(统计函数RANK)。在“fl58统计函数(排名).xlsx”中记录着一班和二班学生语文、数学、英语3门主课期末考试的成绩，请根据总分统计每位学生的班级排名和年级排名。结果如图59所示。



图59一班和二班学生3门主课的年级和班级排名


【参考步骤】
(1) 计算每个班每位学生的总分。分别在H3和H14单元格中输入公式“=SUM(E3：G3)”和“=SUM(E14：G14)”，并分别填充至H10和H21单元格。
(2) 统计一班学生的班级排名。在I3单元格中输入公式“=RANK(H3,$H$3：$H$10)”，并填充至I10单元格。
(3) 统计二班学生的班级排名。在I14单元格中输入公式“=RANK(H14,$H$14：$H$21)”，并填充至I21单元格。
(4) 统计一班学生的年级排名(方法1)。在J3单元格中输入公式“=RANK(H3,$H$3：$H$21)”，并填充至J10单元格。
(5) 统计二班学生的年级排名(方法1)。在J14单元格中输入公式“=RANK(H14,$H$3：$H$21)”，并填充至J21单元格。
(6) 统计一班学生的年级排名(方法2)。在K3单元格中输入公式“=RANK(H3,($H$3：$H$10,$H$14：$H$21))”，并填充至K10单元格。
(7) 统计二班学生的年级排名(方法2)。在K14单元格中输入公式“=RANK(H14,($H$3：$H$10,$H$14：$H$21))”，并填充至K21单元格。
5.2.4PERCENTRANK、PERCENTRANK.INC和
PERCENTRANK.EXC函数
PERCENTRANK、PERCENTRANK.INC和PERCENTRANK.EXC函数用于计算百分比排序。其语法为： 

PERCENTRANK(array, x, ［significance］)

PERCENTRANK.INC(array, x, ［significance］)

PERCENTRANK.EXC(array, x, ［significance］)

其中，参数array是数值单元格区域(或数组)； 参数x是数值； 可选参数significance用于指定百分比的有效数字位数(默认为3，即0.xxx)。
PERCENTRANK和PERCENTRANK.INC函数返回指定数值在单元格区域(或者数组)中的百分比排位(包含0和1)； PERCENTRANK.EXC函数返回指定数值在单元格区域(或者数组)中的百分比排位(不包含0和1)。例如对于数值“12、11、11、10”，PERCENTRANK.EXC的百分比排名依次为80%、40%、40%、20%； 而使用PERCENTRANK和PERCENTRANK.INC的百分比排名依次为100%、33%、33%、0%。
例如，假定单元格区域C3：C87中存放着学生的语文成绩，则公式“=PERCENTRANK(C3：C87,C3)”，返回C3单元格中的语文成绩的排名百分比。
5.2.5PERCENTILE和QUARTILE函数
PERCENTILE和QUARTILE函数用于指定百分比对应的数组。其语法为： 

PERCENTILE(array, k)

PERCENTILE.INC(array, k)

PERCENTILE.EXC(array, k)

QUARTILE(array, quart)

QUARTILE.INC(array, quart)

QUARTILE.EXC(array, quart)

其中，参数array是数值单元格区域(或数组)； 参数k是百分比； 参数quart的取值为从0至4，分别对应0%(最小值)、25%(第1个四分位)、50%(中分位数)、75%(第3个四分位)、100%(最大值)。
PERCENTILE和PERCENTILE.INC函数返回指定百分比(位置，包含0和1)在单元格区域(或者数组)中对应的数值(百分位数)； PERCENTILE.EXC函数返回指定百分比(位置，不包含0和1)在单元格区域(或者数组)中对应的数值(百分位数)。例如，假设data为“12、11、11、10”，则PERCENTILE.EXC(data,80%)的结果为12； 而使用PERCENTILE(data,80%)和PERCENTILE.INC(data,80%)的结果为11.4。
QUARTILE和QUARTILE.INC函数返回单元格区域(或者数组)中对应的四分位数值(包含0和1)； QUARTILE.EXC函数返回单元格区域(或者数组)中对应的四分位数值(不包含0和1)。例如，假设data为“12、11、11、10”，则QUARTILE.EXC(data,1)的结果为10.25； 而使用QUARTILE(data,1)和QUARTILE.INC(data,1)的结果为10.75。
例如，假定单元格区域C3：C87中存放着学生的语文成绩，则： 
(1) 公式“=PERCENTILE($C$3：$C$87,80%)”返回80%位置的语文成绩。
(2) 公式“=QUARTILE(C3：C87,1)”返回第1个四分位(25%)的语文成绩。
5.2.6MEDIAN和MODE函数
MEDIAN和MODE函数用于返回单元格区域(或数组)中的中值(位于中间的数，如果参数集合中包含偶数个数字，则取中间两个数的平均值)和众数(出现频率最多的数)。其语法为： 

MEDIAN(number1, ［number2］, …)

MODE(number1, ［number2］,…)

其中，参数number可以是单元格区域、单元格引用、数组、常量、公式或者另一个函数的结果。参数的个数最多为255个。
例如，假定单元格区域C3：C87中存放着学生的成绩，则： 
(1) 公式“=MEDIAN(C3：C87)”返回单元格区域C3：C87中的中值。
(2) 公式“=MODE(C3：C87)”返回单元格区域C3：C87中的众数。
【例59】学生成绩排名统计。在“fl59学生成绩(排名统计).xlsx”中包含16名学生大学计算机的成绩信息，利用RANK、PERCENTRANK、MAX、MIN、AVERAGE、MEDIAN、MODE、PERCENTILE、QUARTILE、LARGE和SMALL函数以及数组公式按要求完成如下操作： 
(1) 统计学生成绩的排名(RANK函数)。
(2) 统计学生成绩的百分比排名(PERCENTRANK函数)。
(3) 统计学生成绩的最高分、最低分、平均分、中值、众数(MAX、MIN、AVERAGE、MEDIAN和MODE函数)。
(4) 统计80%位置的成绩以及各四分位的成绩(PERCENTILE和QUARTILE函数)。
(5) 统计学生成绩的前3名和末3名(LARGE和SMALL函数以及数组公式)。
结果如图510所示。


图510学生成绩(排名统计)


【参考步骤】
(1) 统计学生成绩的排名。在单元格D3中输入公式“=RANK(C3,$C$3：$C$18)”，并向下填充至单元格D18。
(2) 统计学生成绩的百分比排名。在单元格E3中输入公式“=PERCENTRANK($C$3：$C$18,C3)”，并向下填充至单元格E18。
(3) 统计学生成绩的最高分、最低分、平均分、中值、众数。在单元格H2、H3、H4、H5、H6中分别输入公式“=MAX($C$3：$C$18)”“=MIN($C$3：$C$18)”“=AVERAGE($C$3：$C$18)”“=MEDIAN($C$3：$C$18)”“=MODE($C$3：$C$18)”。
(4) 统计80%位置的成绩以及各四分位的成绩。在单元格H7、H8、H9、H10、H11、H12中分别输入公式“=PERCENTILE($C$3：$C$18,80%)”“=QUARTILE($C$3：$C$18,0)”“=QUARTILE($C$3：$C$18,1)”“=QUARTILE($C$3：$C$18,2)”“=QUARTILE($C$3：$C$18,3)”“=QUARTILE($C$3：$C$18,4)”。
(5) 统计学生成绩的前3名和末3名。选择单元格区域J2：J4，输入数组公式“{=LARGE($C$3：$C$18,{1；2；3})}”； 在J5：J7中输入数组公式“{=SMALL($C$3：$C$18,{1；2；3})}”。
5.2.7STDEV和VAR函数
Excel还提供了若干个用于计算样本或者总体的标准偏差和方差的函数。其语法为： 

STDEV(number1, ［number2］,…):根据样本估计标准偏差。

STDEV.S(number1, ［number2］,…):根据样本估计标准偏差。

STDEV.P(number1, ［number2］,…):根据总体计算标准偏差。

VAR(number1, ［number2］,…):根据样本估计方差。

VAR.S(number1, ［number2］,…):根据样本估计方差。

VAR.P(number1, ［number2］,…):根据总体计算方差。

上述函数均忽略样本或者总体中的逻辑值和文本，如果要包含非数值，则可以使用函数STDEVA、STDEVPA、VARA、VARPA。
5.2.8SUBTOTAL函数
SUBTOTAL函数主要用于列表或者数据库中的分类汇总，根据传递的参数类型返回不同的分类汇总值。其语法为： 

SUBTOTAL(function_num, ref1, ［ref2］,…)

其中，参数function_num指定使用何种函数进行分类汇总计算，1为AVERAGE、2为COUNT、3为COUNTA、4为MAX、5为MIN、6为PRODUCT、7为STDEV、8为STDEVP、9为SUM、10为VAR、11为VARP； 101到111的功能同1到11，但忽略隐藏值。
参数ref1是要对其进行分类汇总计算的第一个命名区域或者引用。可选参数ref2是要对其进行分类汇总计算的第2个命名区域或者引用。最多可以有254个命名区域或者引用。
例如，公式“=SUBTOTAL(9,A2：A5)”等价于“=SUM(A2：A5)”； “=SUBTOTAL(1,A2：A5)”等价于“=AVERAGE(A2：A5)”。
【例510】员工年龄薪酬信息统计。在“fl510统计函数(工资年龄薪酬).xlsx”中记录着员工的出生日期和薪酬情况，利用统计函数(FREQUENCY、RANK、PERCENTRANK、MAX、MIN、LARGE、SMALL、AVERAGE、SUBTOTAL、MEDIAN、MODE、VAR和STDEV)以及日期与时间函数(DATEDIF、YEAR、NOW或TODAY)完成如下操作，结果如图511所示。


图511员工年龄薪酬信息统计结果


(1) 在G1单元格中显示系统当前日期。
(2) 根据出生日期分别计算员工的当年年龄(不管生日是否已过)和实足年龄(从出生到计算时为止共经历的周年数或生日数)。
(3) 统计员工实足年龄的分布情况。
(4) 计算员工的薪酬排名、薪酬百分比排名。
(5) 统计最高薪酬、第二高薪酬、最低薪酬、倒数第二低薪酬、平均薪酬、中间薪酬以及出现次数最多的薪酬。
(6) 统计薪酬的方差和标准偏差。

【参考步骤】
(1) 显示当前日期。在G1单元格中输入公式“=TODAY()”。
(2) 计算每位员工的当年年龄。在C3单元格中输入公式“=YEAR(NOW())-YEAR(B3)”，并向下填充至C15单元格。
(3) 计算每位员工的实足年龄。在D3单元格中输入公式“=DATEDIF(B3,$G$1,"Y")”，并向下填充至D15单元格。
(4) 重新整理实足年龄段。为了使用FREQUENCY函数统计数值在区域内出现的频率，在C18：C21数据区域中输入整理后的年龄段。
(5) 利用频率统计函数统计员工实足年龄的分布情况。选择数据区域B18：B22，在编辑栏中输入公式“=FREQUENCY(D3：D15,C18：C21)”，然后按Ctrl+Shift+Enter键锁定数组公式。
(6) 计算员工的薪酬排名。在F3单元格中输入公式“=RANK(E3,$E$3：$E$15)”，并向下填充至F15单元格。
(7) 计算员工的薪酬百分比排名。在G3单元格中输入公式“=PERCENTRANK($E$3：$E$15,E3)”，并向下填充至G15单元格，设置其格式为百分比样式，保留显示到整数部分。
(8) 统计最高薪酬。在F18单元格中输入公式“=MAX(E3：E15)”。当然，用户也可以利用公式“=LARGE(E3：E15,1)”或者“=SMALL(E3：E15,13)”完成相同功能。
(9) 统计第二高薪酬。在F19单元格中输入公式“=LARGE(E3：E15,2)”。
(10) 统计最低薪酬。在F20单元格中输入公式“=MIN(E3：E15)”。当然，用户也可以利用公式“=SMALL(E3：E15,1)”或者“=LARGE(E3：E15,13)”完成相同功能。
(11) 统计倒数第二低薪酬。在F21单元格中输入公式“=SMALL(E3：E15,2)”。
(12) 统计出现次数最多的薪酬。在F22单元格中输入公式“=MODE(E3：E15)”。
(13) 统计平均薪酬。在H18单元格中输入公式“=AVERAGE(E3：E15)”。当然，用户也可以利用公式“=SUBTOTAL(1,E3：E15)”完成相同功能。
(14) 统计中间薪酬。在H19单元格中输入公式“=MEDIAN(E3：E15)”。
(15) 统计薪酬的方差和标准偏差。在H20单元格中输入公式“=VAR(E3：E15)”； 在H21单元格中输入公式“=STDEV(E3：E15)”。
【说明】
RANK函数对重复数值的排位相同，但重复数值将影响后续数值的排位。本例中“钱军军”和“裘石梅”的薪酬排名并列第3，则排位4空缺，“陈默金”的排位为5。

【拓展】
(1) 请读者思考，为什么需要重新整理分数段？这其中有什么规律可循？请回忆函数COUNTIF(统计范围,条件)是如何完成指定范围内满足条件的记录个数的统计功能的？体会一下借助一个数组公式(FREQUENCY函数)就能轻松地统计出各年龄段的人数分布的妙处。
(2) 如果区域中数据点的个数为n，则函数LARGE(array,1)返回最大值(即MAX(array))，函数LARGE(array,n)返回最小值(即MIN(array))。
(3) 请尝试利用SMALL函数统计第二高薪酬(提示：“=SMALL(E3：E15,12)”)，以及利用LARGE函数统计倒数第二低薪酬(提示：“=LARGE(E3：E15,12)”)。推而广之，对于有n个数据的数据区域，如何利用LARGE函数计算其第k个最小值？如何利用SMALL函数计算其第k个最大值？


视频讲解


5.3数学函数和三角函数
除了5.1节介绍的数学函数之外，Excel还提供了四舍五入、取整、乘法、阶乘、平方根、整除、取余、指数、对数、绝对值、判断正负数、最大公约数、最小公倍数、组合数、排列数、矩阵(二维数组)运算、随机值等数学函数，以及PI、RADIANS、DEGREES、SIN、COS、TAN、ASIN、ACOS、ATAN等三角函数。
5.3.1舍入和取整函数
在进行数值计算时往往会产生小数，使用数学函数可以对数值进行舍入和取整操作。
1. ROUND、ROUNDDOWN和ROUNDUP函数
ROUND、ROUNDDOWN和ROUNDUP函数用于把数值舍入到指定小数位数。其语法为： 

ROUND(number, num_digits) 

ROUNDDOWN(number, num_digits) 

ROUNDUP(number, num_digits)

其中，参数number是数值。参数num_digits是小数位数，如果为正整数，则保留num_digits位小数； 如果为0，则取整； 如果为负数，则舍入到小数点左侧num_digits位。
ROUND将数字四舍五入到指定的位数； ROUNDDOWN将数字朝着零的方向(沿绝对值减小的方向)舍入到指定的位数； ROUNDUP将数字朝着远离零的方向(沿绝对值增大的方向)舍入到指定的位数。
例如，假定单元格A1中包含12.3156，单元格A2中包含-12.3156，则： 
(1) 公式“=ROUND(A1,2)”的结果为12.32。
(2) 公式“=ROUND(A1,0)”的结果为12。
(3) 公式“=ROUND(A2,-1)”的结果为-10。
(4) 公式“=ROUNDDOWN(A1,2)”的结果为12.31。
(5) 公式“=ROUNDDOWN(A2,2)”的结果为-12.31。
(6) 公式“=ROUNDUP(A1,1)”的结果为12.4。
(7) 公式“=ROUNDUP(A2,1)”的结果为-12.4。
2. MROUND、FLOOR和CEILING函数
MROUND、FLOOR和CEILING函数用于将数值舍入到指定基数的倍数。其语法为： 

MROUND(number, multiple)

FLOOR(number, multiple)

CEILING(number, multiple)

其中，参数number是数值； 参数multiple是基数，number舍入到multiple的倍数。
MROUND将数字舍入到指定基数的倍数，如果数值number除以基数的余数大于或等于基数的一半，则向远离零的方向舍入，否则舍弃余数； FLOOR将数字朝着零的方向(沿绝对值减小的方向)舍入到指定基数的倍数； CEILING将数字朝着远离零的方向(沿绝对值增大的方向)舍入到指定基数的倍数。
例如，假定单元格A1中包含17，单元格A2中包含19，则： 
(1) 公式“=MROUND(A1,5)”的结果为15。
(2) 公式“=MROUND(A2,5)”的结果为20。
(3) 公式“=FLOOR(A1,5)”的结果为15。
(4) 公式“=FLOOR(A2,5)”的结果为15。
(5) 公式“=CEILING(A1,5)”的结果为20。
(6) 公式“=CEILING(A2,5)”的结果为20。
3. TRUNC和INT函数
TRUNC和INT函数用于截去数值的小数部分。其语法为： 

TRUNC(number, ［num_digits］)

INT(number)

其中，参数number是数值； 可选参数num_digits(默认值为0)是小数位数，如果num_digits为正整数，则保留num_digits位小数，如果为0，则取整； 如果为负数，则舍入到小数点左侧num_digits位。
TRUNC将保留指定的位数，截去剩余的部分； INT将数字向下舍入到最接近的整数。
例如，假定单元格A1中包含15.625，单元格A2中包含-15.625，则： 
(1) 公式“=TRUNC(A1,2)”的结果为15.62。
(2) 公式“=TRUNC(A1)”的结果为15。
(3) 公式“=TRUNC(A2,-1)”的结果为-10。
(4) 公式“=INT(A1)”的结果为15。
(5) 公式“=INT(A2)”的结果为-16。
4. EVEN和ODD函数
EVEN和ODD函数用于把数值向上舍入到最接近的偶数或奇数。其语法为： 

EVEN(number)

ODD(number)

其中，参数number是数值。EVEN和ODD函数将数字朝着远离零的方向(沿绝对值增大的方向)舍入到最接近的偶数或者奇数。
例如，假定单元格A1中包含3.1，单元格A2中包含-3.1，则： 
(1) 公式“=EVEN(A1)”的结果为4。
(2) 公式“=EVEN(A2)”的结果为-4。
(3) 公式“=ODD(A1)”的结果为5。
(4) 公式“=ODD(A2)”的结果为-5。



图512数学函数应用示例(预订车辆)


【例511】预订车辆(数学函数ROUNDUP和CEILING)。在“fl511数学函数(班车数量).xlsx”中为希望小学的春游活动预订车辆信息，假定每辆车额定乘载30人。结果如图512所示。
【参考步骤】
(1) 计算班车数量(方法1)。在C3单元格中输入公式“=ROUNDUP(B3/30,0)”，并向下填充至C7单元格。
(2) 计算班车数量(方法2)。在D3单元格中输入公式“=CEILING(B3,30)/30”，并向下填充至D7单元格。
5.3.2常用数学函数
1. PRODUCT、FACT和SQRT函数

PRODUCT、FACT和SQRT分别对应于乘积、阶乘和平方根运算。其语法为： 

PRODUCT(number1, ［number2］, …)

FACT(number)

SQRT(number)

其中，参数number是数值。
PRODUCT函数返回参数的乘积； FACT返回number的阶乘； SQRT返回number的平方根。
例如，假定单元格区域A1：A3中包含数据2、4、6，则： 
(1) 公式“=PRODUCT(A1：A3)”的结果为48。
(2) 公式“=FACT(5)”的结果为120。
(3) 公式“=SQRT(16)”的结果为4。
2. QUOTIENT和MOD函数
QUOTIENT和MOD函数用于数学整除和取余运算。其语法为： 

QUOTIENT(numerator, denominator)

MOD(numerator, denominator)

其中，参数numerator为被除数； 参数denominator是除数。
QUOTIENT函数返回除法的整数部分； MOD函数返回除法的余数部分。
例如： 
(1) 公式“=QUOTIENT(7,2)”的结果为3。
(2) 公式“=MOD(7,2)”的结果为1。
3. POWER、EXP、LOG、LOG10和LN函数
Excel中用于指数和对数的数学函数包括POWER、EXP、LOG、LOG10和LN。其语法为： 

POWER(number, power)

EXP(number)

LOG(number, ［base］)

LOG10(number)

LN(number)

其中，参数number是数值，power是幂，可选参数base为对数的底数(默认值为10)。
POWER函数返回number的power次方； EXP返回e(自然对数的底数，2.71828182845904)的number次方； LOG函数返回以base为底的number的对数； LOG10函数返回以10为底的number的对数； LN函数返回以e为底的number的对数。例如： 
(1) 公式“=POWER(2,10)”的结果为1024。
(2) 公式“=POWER(27,1/3)”的结果为3。
(3) 公式“=EXP(1)”的结果为2.718281828。
(4) 公式“=LOG(8,2)”的结果为3。
(5) 公式“=LOG10(1E6)”的结果为6。
(6) 公式“=LN(EXP(5))”的结果为5。
4. ABS和SIGN函数
ABS和SIGN函数用于求数值的绝对值和判断正负数。其语法为： 

ABS(number)

SIGN(number)

其中，参数number为数值。ABS函数返回number的绝对值； SIGN判断number的正负数，number为正时返回1，为零时返回0，为负时返回-1。例如： 
(1) 公式“=ABS(-1.23)”的结果为1.23。
(2) 公式“=SIGN(12)”的结果为1。
(3) 公式“=SIGN(0)”的结果为0。
(4) 公式“=SIGN(-12)”的结果为-1。
5. GCD和LCM函数
GCD和LCM函数用于求最大公约数和最小公倍数。其语法为： 

GCD(number1, ［number2］, …)

LCM(number1, ［number2］, …)

其中，参数number为数值。GCD返回所有参数值的最大公约数(Greatest Common Divisor)； LCM返回所有参数值的最小公倍数(Least Common Multiple)。例如： 
(1) 公式“=GCD(45,27)”的结果为9。
(2) 公式“=LCM(45,27)”的结果为135。
6. COMBIN和PERMUT函数
COMBIN函数用于求组合数或二项系数，PERMUT函数用于求排列数。其语法为： 

COMBIN(number, number_chosen)

PERMUT(number, number_chosen)

其中，参数number和number_chosen均为数值。
COMBIN函数返回组合数或二项系数，计算从给定数目的对象集合number中提取若干对象number_chosen的组合数。
PERMUT函数返回从给定元素数目的集合number中选取若干元素number_chosen的排列数，排列为有内部顺序的对象或事件的任意集合或子集。排列与组合不同，组合的内部顺序无意义。此函数可用于彩票抽奖的概率计算。
例如： 
(1) 公式“=COMBIN(5,3)”的结果为10(从5个不同元素中每次取出3个不同元素的组合总数C35=5!3!×2!=10)。
(2) 公式“=PERMUT(5,3)”的结果为60(从5个不同元素中每次取出3个不同元素的排列总数A35=5!2!=60)。
7. MINVERSE、MMULT和TRANPOSE函数
Excel中用于矩阵(二维数组)运算的函数包括MINVERSE、MMULT、TRANPOSE等。其语法为： 

MINVERSE(array)

MMULT(array1, array2)

TRANPOSE(array3)

其中，参数array为行、列数相等的二维数组(矩阵)； 参数array1的行数和array2的列数相同； 参数array3是数组。
MINVERSE函数返回矩阵的逆； MMULT函数返回两个数组矩阵的乘积； TRANPOSE函数返回数组的转置。



图513矩阵运算示例(求解三元一次方程)


【例512】求解三元一次方程(矩阵运算MINVERSE、MMULT函数)。在“fl512数学函数(三元一次方程).xlsx”中求解三元一次方程，结果如图513所示。三元一次方程为： 
2x+5y-3z=42

x+8y+2z=30

3x+2y+6z=56
【参考步骤】
(1) 在A7：C9以及E7：E9单元格区域中输入方程系数矩阵和方程式的值。
(2) 计算方程式系数矩阵的逆。选中A12：C14单元格区域，输入数组公式“{=MINVERSE(A7：C9)}”。
(3) 求解三元一次方程。选中B17：B19单元格区域，输入数组公式“{=MMULT(A12：C14,E7：E9)}”，将方程式系数矩阵的逆与方程式的值矩阵相乘，得到三元一次方程的解。
5.3.3随机函数
RAND和RANDBETWEEN函数用于生成随机值，常用于产生测试数据。其语法为： 

RAND()

RANDBETWEEN(bottom, top)

其中，参数bottom和top分别为RANDBETWEEN函数将返回的最小整数和最大整数。
RAND函数返回大于或等于0且小于1的均匀分布随机实数。RANDBETWEEN函数返回一个位于两个指定数之间的随机整数。
每次计算工作表时都将返回一个新的随机实数，在单元格公式编辑状态下按F9功能键，可将公式转变为结果值，即永久性地改为随机数； 也可通过选择性粘贴值的方法实现。
使用公式“=RAND()*(b-a)+a”可以生成a与b之间的随机实数。
例如： 
(1) 公式“=RAND()”随机生成［0,1)的实数n，即0≤n<1。
(2) 公式“=RAND()*(240-150)+150”随机生成［150,240)的实数。
(3) 公式“=RANDBETWEEN(1,100)”随机生成［1,100］的整数。
【例513】使用随机函数生成学生信息测试数据。在“fl513学生信息表.xlsx”的A2：A201区域中包含了某班200个学生的学号信息，按要求完成如下操作，结果如图514所示。
(1) 请利用随机函数生成全班学生的身高(150.0~240.0cm，保留一位小数)、成绩(0~100的整数)、月消费(0.0~1000.0，保留一位小数)。
(2) 调整全班学生的成绩(开根号乘以10，四舍五入到整数部分)，填入C2：C201数据区域。


图514随机函数应用示例(学生信息)


【参考步骤】
(1) 生成学生身高信息(保留一位小数)。在B2单元格中输入公式“=ROUND(RAND()*(240-150)+150,1)”，按Enter键确认后向下填充至B201。
(2) 生成学生成绩信息。在C2单元格中输入公式“=RANDBETWEEN(0,100)”，按Enter键确认后向下填充至C201。
(3) 生成学生月消费信息(保留一位小数)。在E2单元格中输入公式“=ROUND(RAND()*1000,1)”，按Enter键确认后向下填充至E201。
(4) 调整学生的成绩(保留到整数部分)。在D2单元格中输入公式“=ROUND(SQRT(C2)*10,0)”，按Enter键确认后向下填充至D201。
5.3.4三角函数
1. PI、RADIANS和DEGREES函数

PI函数返回圆周率； RADIANS函数把角度转换为弧度； DEGREES函数把弧度转换为角度。其语法为： 

PI()

RADIANS(d)

DEGREES(r)

其中，参数r是弧度，d是角度。
例如： 
(1) 公式“=PI()”的结果为3.141592654。
(2) 公式“=RADIANS(180)”的结果为3.141592654。
(3) 公式“=DEGREES(PI()/2)”的结果为90。
2. SIN、COS等函数
Excel中提供的三角函数包括SIN、COS、TAN、ASIN、ACOS、ATAN等，对应于数学上的三角函数。例如： 
(1) 公式“=SIN(PI()/2)”的结果为1。
(2) 公式“=COS(RADIANS(60))”的结果为0.5。
【例514】绘制三角函数图像。在“fl514正弦函数和余弦函数.xlsx”中同时绘制正弦函数和余弦函数，最终结果如图515所示。


图515正弦函数和余弦函数


【参考步骤】
(1) 采用尽量简洁、快速的方法在单元格区域A2：A14中输入x的值(角度，一个周期的值，等差数列0~360，公差或称步长为30)。
(2) 计算SIN函数和COS函数的值。在B2单元格中输入公式“=SIN(A2/360*2*PI())”，在C2单元格中输入公式“=COS(A2/360*2*PI())”，并向下填充至B14和C14单元格。
(3) 绘制图表。选择A1：C14的单元格区域，单击“插入”选项卡，选择“图表”组中“散点图”的子类型“带直线和数据标记的散点图”。
【拓展】
请读者思考，在SIN和COS函数中为什么使用“A2/360*2*PI()”？目的是什么？如何绘制余弦函数、正切函数等其他数学和三角函数？



图516计算分段函数

【例515】分段函数求值。利用数学和三角函数(ROUND、SQRT、ABS、EXP、LN、PI、SIN)、随机函数RAND、条件判断函数IF、逻辑函数(AND和OR)计算“fl515分段函数.xlsx”中当x取值为-10到10的随机实数时分段函数y的值，要求使用两种方法实现： 一种方法先判断-1≤x<2条件，第二种方法先判断x<－1或x≥2条件。结果均显示两位小数，如图516所示。
y=sinx+2x+e4-(x+1)3-1≤x<2

ln(|x2-x|)-2π(x-1)7xx<－1或x≥2

【参考步骤】
(1) 生成-10到10的随机实数(保留两位小数)。在A2单元格中输入公式“=ROUND(RAND()*20-10,2)”，并向下填充至A42单元格。
(2) 利用IF和AND函数计算分段函数y的值。在B2单元格中输入公式“=IF(AND(A2>=-1,A2<2),SIN(A2)+2*SQRT(A2+EXP(4))-(A2+1)^3,LN(ABS(A2^2-A2))-2*PI()*(A2-1)/7/A2)”，并向下填充至B42单元格。利用增加小数位数或减少小数位数使计算结果显示两位小数。
(3) 利用IF和OR函数计算分段函数y的值。在C2单元格中输入公式“=IF(OR(A2<-1,A2>=2),LN(ABS(A2^2-A2))-2*PI()*(A2-1)/7/A2,SIN(A2)+2*SQRT(A2+EXP(4))-(A2+1)^3)”，并向下填充至C42单元格。利用增加小数位数或减少小数位数使计算结果显示两位小数。
【说明】
(1) 因为x是随机生成的，所以本例同时使用AND和OR函数计算分段函数的值，目的是使读者更清晰地判断计算方法和结果的正确性。
(2) 本例练习Excel中数学和三角函数的使用方法。注意，在书写Excel表达式时乘号不能省略，例如a乘以b应写为a*b。在表达式中括号必须成对出现，而且只能使用圆括号，圆括号可以嵌套使用。表达式从左到右在同一个基准上书写，无高低、大小之分。
习题
一、 单选题
1. 在Excel中，公式“=COUNT("计算机",2015,"100",TRUE,#N/A,)”的结果为。

A. 4B. 5C. 6D. 2115
2. 在Excel中，若单元格区域A1：A50中存放着某班级50名学生的语文成绩，计算该班语文第2名成绩的公式为。
A. =LARGE(A1：A50,49)B. =SMALL(A1：A50,2)
C.  =SMALL(A1：A50,49)D. =MAX(A1：A50,2)
3. 在Excel中，若单元格区域A1：A50中存放着某班级50名学生的语文成绩，计算该班语文倒数第3名成绩的公式为。
A. =LARGE(A1：A50,3)B. =SMALL(A1：A50,48)
C.  =MIN(A1：A50,3)D. =LARGE(A1：A50,48)
4. 在Excel中，使用INT(取整函数)实现单元格A1中的小数(例如123.486)四舍五入保留一位小数(结果为123.5)的公式为。
A. =INT(A1*100+0.05)/10B. =INT(A1*10+0.5)/10
C. =INT(A1*10+0.5)/100D. =INT(A1*100+0.05)/100
5. 在Excel中，假设A1：A100单元格区域中存放着某班级语文摸底考试的成绩，以下统计80~89分的人数的公式中，除了，其他方法均可以实现所要求的功能。
A. =COUNTIFS(A1：A100,"<90",A1：A100,">=80")
B. {=SUM((A1：A100>=80)*(A1：A100<=89))}
C. =SUM((A1：A100>=80)*(A1：A100<=89))
D. =SUM(COUNTIF(A1：A100,">="&{80,90})*{1,-1})
6. 在Excel中，假设A1：A100单元格区域中存放着某班级英语摸底考试的成绩，统计大于或等于90分(存放在B1单元格中)的人数的公式为。
A. {=COUNT(0/(A1：A100>B1))}B. =COUNTIF(A1：A100,">"&B1)
C. {=SUM(IF(A1：A100>B1,1))}D. =SUM(IF(A1：A100>B1,1))
7. 在Excel中，假设A1：A10单元格区域中存放着若干大于1的整数，则以下统计该单元格区域中偶数个数的公式中错误的是。
A. {=COUNT(1/MOD(A1：A10-1,2))}
B. =SUMPRODUCT((MOD(A1：A10,2)=0)*1)
C. =SUMPRODUCT(MOD(A1：A10,2)=0)
D. {=SUM(MOD(A1：A10+1,2))}
8. 在Excel中，假设A1：A10单元格区域中存放着若干大于1的整数，则以下统计该单元格区域中奇数个数的公式中错误的是。
A. {=COUNT(1/MOD(A1：A10,2))}
B. {=SUM(MOD(A1：A10,2))}
C. =SUMPRODUCT(MOD(A1：A10,2))
D. =SUMPRODUCT(MOD(A1：A10,2)=1)
9. 在Excel中，假设A1：A100单元格区域中存放着若干学生的姓名(假设不存在同名同姓的情况)，则以下统计该单元格区域中不同学生人数的公式中错误的是。
A. {=SUM(1/COUNTIF(A1：A100,A1：A100))}
B. {=SUM(COUNTIF(A1：A100,A1：A100))}
C. {=SUM(--(1/COUNTIF(A1：A100,A1：A100)))}
D. =SUMPRODUCT(1/COUNTIF(A1：A100,A1：A100))
10. 在Excel中，假设A1：A100单元格区域中存放着某班级英语摸底考试的成绩，以下统计80~89分的人数的公式中，除了，其他方法均可以实现所要求的功能。

A. =COUNTIF(A1：A100,">=80")-COUNTIF(A1：A100,">89")
B. =SUMPRODUCT((A1：A100>=80)*(A1：A100<=89))
C. =FREQUENCY(A1：A100,89)-FREQUENCY(A1：A100,79)
D. =FREQUENCY(A1：A100,90)-FREQUENCY(A1：A100,80)
二、 填空题
1. 在Excel中，将单元格A1中小数(例如1.2345678)显示为百分比形式并且保留两位小数(例如123.46%)的公式为。
2. 在Excel中，使用INT(取整函数)实现单元格A1中的小数(例如123.486)四舍五入保留两位小数(结果为123.49)的公式为。
3. 在Excel中，若要对单元格A3至B7、D3至E7这两个单元格区域中的数据求平均数，并将所得结果置于A1中，则应在A1中输入公式。
4. 在Excel中，假设A1单元格内容为“计算机”，A2单元格内容为数值2020，A3单元格内容为数字文本字符串“2020”，则COUNT(A1：A3)的结果为，COUNTA(A1：A3)的结果为。
5. 在Excel中，SUM(10,"1",TRUE,FALSE)的结果为，SUMSQ(2, 3)的结果为。
6. 在Excel中，INT(2.8)的结果为，INT(-12.8)的结果为，TRUNC(29.9)的结果为，ROUND(99.6,0)的结果为，ROUND(-243.19,-2)的结果为。
7. 在Excel中，EVEN(67.2)的结果为，ODD(56)的结果为。
8. 在Excel中，QUOTIENT(15,2)的结果为，MOD(15,2)的结果为，GCD(4,6)的结果为，LCM(4,6)的结果为。
9. 在Excel中，SIGN(ABS(-10))的结果为，FACT(4)的结果为，FACTDOUBLE(4)的结果为。
10. 在Excel中，POWER(5,SQRT(9))的结果为，LN(EXP(LOG(16,2)))的结果为。
11. 在Excel中，DEGREES(PI()/4)的结果为，COS(RADIANS(-60))的结果为，DEGREES(ASIN(1))的结果为。
12. 在Excel中，PERMUT(5,2)的结果为，COMBIN(5,2)的结果为。
13. 在Excel中，运算表达式可以产生-100~100(包含-100和100)的随机整数。
14. 在Excel中，假设矩阵A为，矩阵B为，则矩阵A行列式的值MDETERM(A1：B2)为，矩阵B的逆矩阵MINVERSE(D1：E2)为，矩阵A和B的乘积MMULT(A1：B2,D1：E2)的结果为，矩阵A和B元素的乘积之和SUMPRODUCT(A1：B2,D1：E2)的结果为，矩阵A和B元素的平方之差的平方和SUMXMY2(A1：B2,D1：E2)的结果为，矩阵A和B元素的平方差之和SUMX2MY2(A1：B2,D1：E2)的结果为，矩阵A和B元素的平方和之和SUMX2PY2(A1：B2,D1：E2)的结果为。表达式PRODUCT(A1：B2,D1：E2)的结果为。
15. 数学表达式ln(|x2-x+2|)-π(6x-e)cosx-sin8x+3x+e5的Excel表达式为。
16. 在Excel中，生成-100~100的随机实数(保留一位小数)的公式为。
17. 在Excel中，若数据区域A1：A50中存放着某班级50名学生的语文成绩，利用LARGE函数计算该班语文倒数第3名的成绩的公式为，利用SMALL函数计算该班语文第3名的成绩的公式为。
18. 在Excel中，假设A1：A10单元格区域中存放着某跳水运动员第一轮比赛得到的10个成绩，则利用TRIMMEAN函数去掉一个最高分和一个最低分后计算该运动员平均得分的公式为。
三、 思考题
1. Excel提供了哪些常用的数学与三角函数？
2. 如何使用Excel函数实现角度和弧度的相互转换？
3. 如何使用Excel函数生成a与b之间的随机实数？
4. 在Excel中使用函数生成a与b之间的随机整数有哪几种方法？
5. 在书写Excel表达式时有哪些注意事项？
6. Excel提供了哪些常用的统计函数？
7. 如何利用Excel函数统计成绩数据区域B1：B50中的及格人数？
8. 如何利用Excel函数统计成绩数据区域B1：B50中60~90分的学生人数？