数学与三角函数应用技巧 – 第3页

Excel 应用SUMPRODUCT函数计算数组间元素乘积之和

SUMPRODUCT函数的功能是，在给定的几组数组中，将数组间对应的元素相乘，并计算乘积之和。其语法如下：

SUMPRODUCT(array1,array2,array3,...)

其中参数array1、array2、array3、…为2~255个数组，其相应元素需要进行相乘并求和。

【典型案例】某商场在某一天将某两类产品A和B拿出来搞促销，以带动其他产品的销售。结束促销后，商场要统计这两类产品的销售总额，本例的原始数据如图13-83所示。

步骤1：在F2单元格中输入公式“=SUMPRODUCT（（A2：A10=”A”）*（C2：C10）*（D2：D10））”，然后按组合键“Ctrl+Shift+Enter”，计算产品类型为A类的产品的销售总额。

步骤2：在F3单元格中输入公式“=SUMPRODUCT（（A2：A10=”B”）*（C2：C10）*（D2：D10））”，然后按组合键“Ctrl+Shift+Enter”，计算产品类型为B类的产品的销售总额。计算结果如图13-84所示。

图13-83　原始数据

图13-84　计算结果

说明：上例中所返回的乘积之和，与以数组形式输入的公式“=SUM（A2：B4*C2：D4）”计算的结果相同。使用数组公式可以为类似于SUMPRODUCT函数的计算提供更为通用的解法，如使用公式“=SUM（A2：B4^2）”并按组合键“Ctrl+Shift+Enter”可以计算A2：B4中所有元素的平方和。

【使用指南】数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值“#VALUE！”。函数SUMPRODUCT将非数值型的数组元素作为0处理。

Excel 应用SUMIFS函数对某一区域内满足多重条件的单元格求和

SUMIFS函数的功能是对某一区域内满足多重条件的单元格进行求和。SUMIFS和SUMIF的参数顺序不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。如果要复制和编辑这些相似函数，需要确保按正确顺序放置参数。SUMIFS函数的语法如下：

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

其中参数sum_range表示要求和的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。参数criteria_range1、criteria_range2、…表示计算关联条件的1~127个区域。参数criteria1、criteria2、…表示数字、表达式、单元格引用或文本形式的1~127个条件，用于定义要对哪些单元格进行求和。

【典型案例】现有某地区周一至周四的上、下午的雨水、平均温度和平均风速的测量值，本例中要对平均温度至少为20℃且平均风速小于10km/h的这些天的总降雨量求和。本例的原始数据如图13-81所示。

在A9单元格中输入公式“=SUMIFS（B2：E3，B4：E5，”>=20″，B6：E7，”<10″）”，对平均温度至少为20℃且平均风速小于10km/h的这些天的总降雨量求和，结果如图13-82所示。

【使用指南】只有当sum_range中的每一单元格满足为其指定的所有关联条件时，才对这些单元格进行求和。sum_range中包含TRUE的单元格计算为1；sum_range中包含FALSE的单元格计算为0。与SUMIF函数中的区域和条件参数不同的是，SUMIFS中每个criteria_range的大小和形状必须与sum_range相同。可以在条件中使用通配符问号（？）和星号（*）。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，则在字符前键入波形符（~）。

图13-81　原始数据

图13-82　计算结果

Excel 应用SUMIF函数按给定条件对指定单元格求和

SUMIF函数的功能是按照给定条件对指定的单元格进行求和。其语法如下：

SUMIF(range,criteria,sum_range)

参数range是要根据条件计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。参数criteria为确定对哪些单元格相加的条件，其形式可以为数字、表达式或文本。参数sum_range为要相加的实际单元格（如果区域内的相关单元格符合条件）。如果省略参数sum_range，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。

【背景知识】参数sum_range与区域的大小和形状可以不同。相加的实际单元格通过以下方法确定：使用sum_range中左上角的单元格作为起始单元格，然后包括与区域大小和形状相对应的单元格，如表13-3所示。

表13-3　确定相加的实际单元格

【典型案例】某班级六名男同学分成两组，进行一分钟定点投篮比赛。A组成员有张辉、徐鑫和郑明涛，B组成员有王明、毛志强和李卫卫。比赛结束后，又来两名同学，分别是李波和王赐，也进行了定点一分钟投篮。现在要计算A组和B组的进球总数及其他人员的进球总数。本例的原始数据如图13-79所示。

步骤1：在D2单元格中输入公式“=SUMIF（A2：A9，”A*”，B2：B9）”，计算A组同学的进球总数。

步骤2：在D3单元格中输入公式“=SUMIF（A2：A9，”B*”，B2：B9）”，计算B组同学的进球总数。

步骤3：在D4单元格中输入公式“=SUM（B2：B9）-SUMIF（A2：A9，”a*”，B2：B9）-SUMIF（A2：A9，”b*”，B2：B9）”，计算其他同学的进球总数。计算结果如图13-80所示。

图13-79　原始数据

图13-80　计算结果

【使用指南】SUMIF函数的主要进行有条件的求和，可以在条件中使用通配符问号（？）和星号（*）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

Excel 应用SUM函数求和

SUM函数的功能是计算某一单元格区域中所有数字之和。其语法如下：

SUM(number1,number2,...)

其中参数number，number2，…是要对其求和的1~255个参数。

【典型案例】某地发生水灾，当地一家公司为发生水灾的地方组织捐款，计算这个公司三个分公司的捐款数额，以及公司总的捐款数额。本例的原始数据如图13-77所示。

图13-77　原始数据

步骤1：在B6单元格中输入公式“=SUM（B2：B5）”，计算公司一部的捐款总额。

步骤2：在D6单元格中输入公式“=SUM（D2：D5）”，计算公司二部的捐款总额。

步骤3：在F6单元格中输入公式“=SUM（F2：F5）”，计算公司三部的捐款总额。

步骤4：在G2单元格中输入公式“=SUM（B6，D6，F6）”，计算公司总捐款额，最终结果如图13-78所示。

图13-78　计算捐款数额

【使用指南】SUM函数的用途比较广泛。在学校中可以求学生的总成绩，在会计部门可以求账务的总和等。对SUM函数来说，直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数为错误值或为不能转换为数字的文本，将会导致错误。

Excel 应用SUBTOTAL函数计算列表或数据库中的分类汇总

SUBTOTAL函数的功能是计算列表或数据库中的分类汇总。通常可以使用“数据”选项卡“大纲”组中的“分类汇总”命令来创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。SUBTOTAL函数的语法如下：

SUBTOTAL(function_num,ref1,ref2,...)

参数function_num为1~11（包含隐藏值）或101~111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。参数ref1、ref2为要进行分类汇总计算的1~254个区域或引用。如果在ref1、ref2等中有其他的分类汇总（嵌套分类汇总），则会忽略这些嵌套分类汇总以避免重复计算。

【背景知识】当参数function_num为从1~11的常数时，SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值，当要对列表中的隐藏和非隐藏数字进行分类汇总时，须使用这些常数。当参数function_num为从101~111的常数时，SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值。当只对列表中的非隐藏数字进行分类汇总时，则使用这些常数。

SUBTOTAL函数的参数数值和对应的函数如表13-2所示。

表13-2　SUBTOTAL函数的参数数值和对应的函数

【典型案例】已知某班级的期末考试成绩，现在来计算学生的总分和平均分。本例的原始数据如图13-74所示。

图13-74　原始数据

步骤1：在E2单元格中输入公式“=SUBTOTAL（9，B2：D2）”，计算第一位同学的总分，然后使用自动填充功能来计算其他同学的总分，如图13-75所示。

步骤2：在F2单元格中输入公式“=SUBTOTAL（1，B2：D2）”，计算第一位同学的平均分，然后使用自动填充功能来计算其他同学的平均分，如图13-76所示。

【使用指南】上面例子中介绍了SUBTOTAL函数中相当于SUM、AVERAGE函数功能部分的运算。当在实际运算中应用到其他参数时，原理是相同的，视具体情况作相应的参数调整即可。SUBTOTAL函数忽略任何不包括在筛选结果中的行，不论使用什么参数function_num值。

图13-75　计算总分

图13-76　计算平均分

SUBTOTAL函数适用于数据列或垂直区域。不适用于数据行或水平区域。当参数function_num大于或等于101时需要分类汇总某个水平区域时，如SUBTOTAL（109，B2：G2），则隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。如果所指定的某一引用为三维引用，函数SUBTOTAL将返回错误值“#VALUE！”。

Excel 应用SQRT函数计算正平方根

SQRT函数的功能是计算正数的平方根。其语法如下：

SQRT(number)

其中参数number为要计算平方根的数。

【典型案例】已知圆的面积，求圆的半径。本例的原始数据如图13-72所示。

在B2单元格中输入公式“=SQRT（A2/PI（））”，计算第一个圆的半径，然后使用自动填充功能来计算其他圆的半径，如图13-73所示。

图13-72　原始数据

图13-73　计算圆的半径

【使用指南】对SQRT函数来说，如果参数number为负值，SQRT函数返回错误值“#NUM！”。

Excel 应用SIGN函数计算数字的符号

SIGN函数功能是返回数字的符号。当数字为正数时返回1，为零时返回0，为负数时返回-1。其语法如下：

SIGN(number)

其中参数number为任意实数。

【典型案例】某班级进行了一次随堂小考，现在抽出了七位同学的成绩，判断他们的成绩是否及格。本例的原始数据如图13-70所示。

在C2单元格中输入公式“=IF（SIGN（B2-60）>=0，”及格”，”不及格”）”，判断学号为A001的同学成绩是否及格，然后使用自动填充功能来判断其他同学的成绩是否及格，如图13-71所示。

图13-70　原始数据

图13-71　判断成绩是否及格

【使用指南】SIGN函数除了用于在数学中返回数字的符号外，还可以进行某些判断，如判断某种产品的长度是否达标等。

Excel 应用SERIESSUM函数计算基于公式的幂级数之和

SERIESSUM函数的功能是计算基于幂级数展开式的幂级数之和。许多函数可由幂级数展开式近似地得到。其语法如下：

SERIESSUM(x,n,m,coefficients)

其中参数x为幂级数的输入值。参数n为参数x的首项乘幂。参数m为级数中每一项的乘幂n的步长增加值。参数coefficients为一系列与参数x各级乘幂相乘的系数。参数coefficients值的数目决定了幂级数的项数。如果参数coefficients中有三个值，幂级数中将有三项。

【背景知识】该函数提到一个概念——幂级数。幂级数形式上是个无穷多项式，通常依变量x的升幂顺序来表示。幂级数在微积分中是一个重要的题材，许多重要的函数可表示成幂级数，而幂级数全体也代表了相当广泛的函数类别。

【典型案例】根据幂级数展开式求解函数y=sinx在x=π/3的近似函数值，并将该近似值与直接求解的结果进行比较，其中sinx的级数展开式为：sinx=x-x³/3！+x⁵/5！-x⁷/7！+…。本例的原始数据如图13-67所示。

步骤1：在B8单元格中输入公式“=SIN（PI（）/3）”，计算y=sinx在x=π/3的准确结果，如图13-68所示。

图13-67　原始数据

图13-68　计算y=sinx在x=π/3的准确结果

步骤2：在B9单元格中输入公式“=SERIESSUM（B2，1，2，B3：B6）”，计算y=sinx在x=π/3的级数展开数值，如图13-69所示。

图13-69　计算函数的级数值

【使用指南】SERIESSUM函数主要用来求解函数在某一点的近似值。对该函数来说，如果任一参数为非数值型，SERIESSUM函数将会返回错误值“#VALUE！”。

Excel 应用ROUND、ROUNDDOWN和ROUNDUP函数按位数进行舍入

ROUND函数的功能计算某个数字按指定位数取整后的数字；ROUNDDOWN函数的功能是向靠近零值的方向向下（绝对值减小的方向）舍入数字；ROUNDUP函数的功能是向远离零值的方向向上舍入数字。其语法如下：

ROUND(number,num_digits)
ROUNDDOWN(number,num_digits)
ROUNDUP(number,num_digits)

其中参数number为需要舍入的任意实数，参数num_digits为四舍五入后的数字的位数。

【典型案例】某网通经营商对家庭用的座机的收费标准如下，每月的座机费为18元，打电话时间在三分钟以内，收费均为0.22元，超过三分钟后，每分钟的通话费用为0.1元，并按整数计算。本例中来计算某家庭在一个月内的电话总费用。本例的原始数据如图13-64所示。

步骤1：在C2单元格中输入公式“=IF（B2<=3，0.22，0.22+ROUNDUP（（B2-3），0）*0.1）”，计算第一个电话所使用的费用，然后使用自动填充功能来计算其他电话的通话费用，如图13-65所示。

图13-64　原始数据

图13-65　计算电话费

步骤2：在D2单元格中输入公式“=18+SUM（C2：C6）”，计算本月的电话总费用，如图13-66所示。

图13-66　计算总费用

【使用指南】对上面的三个函数来说，拥有相同的参数。如果参数num_digits大于0，则四舍五入到指定的小数位。如果参数num_digits等于0，则四舍五入到最接近的整数。如果参数num_digits小于0，则在小数点左侧进行四舍五入。

Excel 应用ROMAN函数将阿拉伯数字转换为罗马数字

ROMAN函数的功能是将阿拉伯数字转换为文本形式的罗马数字。其语法如下：

ROMAN(number,form)

其中参数number为需要转换的阿拉伯数字，参数form为一数字，用于指定所需的罗马数字类型。罗马数字的样式范围可以从经典到简化，随着参数form值的增加趋于简单。

【背景知识】在ROMAN函数中，参数form的取值及罗马数字的类型如表13-1所示。

表13-1　参数form的取值及罗马数字的类型

【典型案例】将给定的数字按不同的格式转换为罗马数字。最终结果如图13-63所示。

图13-63　将阿拉伯数字转换为罗马数字

【使用指南】对ROMAN函数来说：如果数字为负，则ROMAN函数将返回错误值“#VALUE！”。如果数字大于3999，则ROMAN函数也将返回错误值“#VALUE！”。