第2章 数学和三角函数

Excel 2007提供了一些常用的数学和三角函数。用户在使用Excel进行运算时，如果遇到数学运算，可以适当地使用相应的数学函数。

2.1 SUM 2003 2007 2010返回某一单元格区域中所有数字之和

格式 SUM(number1，number2，...)

参数 number1, number2, ... 表示要对其求和的1到255个参数n。

应用举例 SUM函数的几种用法

Excel数据位置：CDROM\02\2.1\2.1.xls

注意点说明

直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。

如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。

如果参数为错误值或为不能转换为数字的文本，将会导致错误。

案例1 求图书订购价格总和（SUM）

⊙ 源文件：CDROM\02\2.1\案例1.xls

在“参考价格”工作表中存放各种图书的价格，在“订购表”工作表中存放每个订购者的订购书目，需要计算所有订购人员的图书总价。

打开光盘中的数据文件，在单元格F2中输入如下数组公式：

=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)

按下【Ctrl+Shift+Enter】组合键后，将返回第一个订购者的总价格，然后将公式向下填充，完成所有人员图书总价的计算，结果如图2-1所示。

公式说明

在本例公式中，利用订购书目与“参考价格”表中的书目进行对比，组成一个包含逻辑值TURE和FALSE的数组，然后用此数组与单价相乘，可以得到一个已订购图书的单价和0组成的新数组。将此数组汇总即得到最后的总价。

案例提示

1.使用本公式时，每个订购书目中的书名排序可以不与“参考价格”中的顺序一致，SUM函数也可以正确的找到对应的单价并相加。

2.在公式中，B2:E2 区域必须相对引用，让公式在填充时可以引用不同订购的信息；而引用“参考价格”表时则需要绝对引用或者混合引用，将引用中表示行的地址固定，否则将引用错位，公式产生错误结果。

3.如果书名是英文名，那么本公式不区分大小写，即“SKY”和“Sky”不会被当做两本书处理。

案例参考函数

COUNT 统计函数，见第5章5.4节。

COUNTA 统计函数，见第5章5.5节。

2.2 SUMIFS 2007 2010对区域中满足多个条件的单元格求和

格式 SUMIFS(sum_range,criteria_rangel,criteria,[criteria_range2, criteria2],…)

参数

sum_range 表示对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用。忽略空白和文本值。

criteria_range1 在其中计算关联条件的第一个区域。

criteria1 条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。

criteria_range2,criteria2,… 附加的区域及其关联条件，最多允许127个区域或条件对。

应用举例 SUMIFS函数的几种用法

Excel数据位置：CDROM\02\2.2\2.2.xlsx

注意点说明

仅在 sum_range 参数中的单元格满足所有相应的指定条件时，才对该单元格求和。例如，假设一个公式中包含两个criteria_range参数。如果criteria_range1的第一个单元格满足criteria1，而criteria_range2的第一个单元格满足critera2，则sum_range的第一个单元格计入总和中。对于指定区域中的其余单元格，依此类推。

sum_range中包含TRUE的单元格计算为1；sum_range中包含FALSE的单元格计算为0。

与 SUMIF 函数中的区域和条件参数不同，SUMIFS 函数中每个criteria_range参数包含的行数和列数必须与sum_range参数相同。

用户可以在条件中使用通配符，即问号（?）和星号（*）。问号匹配任一单个字符；星号匹配任一字符序列。如果要查找实际的问号或星号，请在字符前键入波形符（～）。

案例2 只汇总60到80分的成绩（SUMIFS）

⊙ 源文件：CDROM\02\2.2\案例2.xlsx

打开光盘中的数据文件，在单元格D2中输入如下公式：

=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80")

按下【Enter】键后，将汇总60到80分的成绩，结果如图2-2所示。

公式说明

SUMIFS函数用于多条件求和，根据需要可以设置1到127个条件，本例中设置了两个条件。其中第一参数表示实际求和的区域，第二参数和第四参数表示条件区域，第三参数和第五参数表示条件。区域B2:B10出现了三次，第一次代表实际求和区域，第二次、第三次代表条件区域。

案例提示

1.SUMIFS函数和SUMIF函数都是条件求和函数，SUMIFS相比SUMIF函数除了可以设置多条件以外，还有另外两点与SUMIF函数不同。其一， SUMIFS函数的实际求和区域是第一个参数，而SUMIF函数的实际求和区域则是最后一个参数，如果省略第三参数，那么求和区域就是第一参数。其二，SUMIF函数的求和区域可以简写，而SUMIFS函数的求和区域必须和条件格式大小一致。

2.如果参数中设置了多个条件，那么函数的运算机制是同时符合所有条件的数据才求和。

3.求和时，如果第一参数中有TRUE，则当做1计算，而FALSE当做0计算。

案例3 汇总三年级二班人员迟到次数（SUMIFS）

⊙ 源文件：CDROM\02\2.2\案例3.xlsx

打开光盘中的数据文件，在单元格F2中输入如下公式：

=SUMIFS(D2:D10,B2:B10, "三年级",C2:C10, "二班")

按下【Enter】键后，将返回三年级二班人员迟到次数，结果如图2-3所示。

公式说明

本公式中实际求和区域是 D2:D10，而条件区域分别是 B2:B10 和C2:C10。根据需要还可以添加更多的条件区域。

SUM函数本身就有多条件求和的功能，但SUMIFS函数在多条件求和时可以使用普通公式完成。

案例提示

1.本公式中的第三参数和第五参数都省略了等号。只有大于、小于、不等号或者大于等于、小于等于是不可以省略的。

2.公式中三个区域的大小必须一致，否则将产生错误结果，这和SUMIF函数是有所区别的。

3.本例也可以改用SUM函数的数组形式替代SUMIFS函数，数组公式如下：

=SUM(D2:D10*(B2:B10="三年级")*(C2:C10="二班"))

4.如果是对一年级迟到次数大于20次的进行统计，公式可以修改为：

=SUMIFS(D2:D10,B2:B10, "一年级", D2:D10, ">20")

2.3 SUMPRODUCT 2003 2007 2010返回数组间对应的元素乘积之和

格式 SUMPRODUCT(array1,array2,array3,…)

参数 array1,array2,array3,… 表示2到255个数组，其相应元素需要进行相乘并求和。

应用举例 SUMPRODUCT函数的几种用法

Excel数据位置：CDROM\02\2.3\2.3.xls

注意点说明

数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。

函数SUMPRODUCT将非数值型的数组元素作为0处理。

案例4 汇总一班人员获奖次数（SUMPRODUCT）

⊙ 源文件：CDROM\02\2.3\案例4.xls

打开光盘中的数据文件，在单元格E2中输入如下公式：

=SUMPRODUCT((B2:B11="一班")*C2:C11)

按下【Enter】键后，将返回一班人员获奖次数，结果如图2-4所示。

公式说明

本公式中SUMPRODUCT函数有两个数组参数：“B2:B11=＂一班＂”和“C2:C11”。根据本函数的特点，可以将两个数组通过乘号连接成一个参数，从而不管两个数组由数值组成还是由逻辑值组成，都可以不需要转换而直接得到运算结果。

案例提示

1.本例中也可以将两个数组分成两个参数，但第一参数需要利用“*1”或者其他方式将逻辑值转换成数值：

=SUMPRODUCT((B2:B11="一班")*1,C2:C11)

=SUMPRODUCT(N(B2:B11="一班"),C2:C11)

=SUMPRODUCT(--(B2:B11="一班"),C2:C11)

2.如果用SUM函数来运算，必须以数组形式录入公式：

=SUM((B2:B11="一班")*C2:C11)

=SUM(if(B2:B11="一班",C2:C11))

案例参考函数

MMULT 数学和三角函数，见第2章2.4节。

PRODUCT 数学和三角函数，见第2章2.10节。

SUM 数学和三角函数，见第2章2.1节。

2.4 MMULT 2003 2007 2010返回两个数组的矩阵乘积

格式 MMULT(array1,array2)

参数 array1,array2 表示要进行矩阵乘法运算的两个数组。

应用举例 MMULT函数的几种用法

Excel数据位置：CDROM\02\2.4\2.4.xls

注意点说明

array1的列数必须与array2的行数相同，而且两个数组中都只能包含数值。

array1和array2可以是单元格区域、数组常量或引用。

当任意单元格为空（或包含文字）以及array1的列数与array2的行数不相等时，MMULT返回错误值#VALUE!。

对于返回结果为数组的公式，必须以数组公式的形式输入。

案例5 求第一名人员最多有几次（MMULT）

⊙ 源文件：CDROM\02\2.4\案例5.xls

打开光盘中的数据文件，在单元格D2中输入如下公式：

=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))

按下【Ctrl+Shift+Enter】组合键后，将返回所有获第一名的人员中次数最多的人的次数，结果如图2-5所示。

公式说明

本例利用“B2:B7=TRANSPOSE(B2:B7)”产生一个包含TRUE和FALSE且具有6行6列的二维数组，然后利用函数N将其中的逻辑值转换成数值1和0。MMULT函数对这个二维数组的每行相加，得到一个6行1列的一维数组，最后通过MAX函数提取最大值。

案例提示

1.在本公式中MMULT函数的第一参数是一个6行6列的数组，第二参数是一个全部由1组成的1列6行的数组，MMULT函数将两个数组进行矩阵乘积后就得到一个1列6行的数组，这个数组包括B2:B7区域中每个人员的出现次数。其中“张千”次数最多，有3次。

2.对于本题中直接对引用区域进行运算的，也可用COUNTIF函数完成，公式如下：

=MAX(COUNTIF(B2:B7,B2:B7))

但是，如果条件不是直接引用单元格，就必须使用 MMULT 函数，如图2-6所示。

案例6 在不同班级有同名前提下计算学生人数（MMULT）

⊙ 源文件：CDROM\02\2.4\案例6.xls

同一学生参与了多种比赛项目，而不同班级又存在同名的学生。现需要计算表中参赛学生的人数，同名不同班的学生不能当做一个计算。

打开光盘中的数据文件，在单元格F2输入如下数组公式：

=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE (A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))

按下【Ctrl+Shift+Enter】组合键后，将返回不重复的学生人数。其中不同学校或者不同班级的同名学生不按照一个计算，结果如图2-7所示。

公式说明

本例公式用于计算两个条件区域中的不重复数据个数。

案例提示

1.公式中有两处使用了的“A2:A17&B2:B17&C2:C17”，它们的顺序必须一致。

2.对内存数组计算不重复数据个数只有 MMLUT 函数可以胜任， COUNTIF函数的第一参数不能使用数组，必须是区域引用。

案例7 计算前进中学参赛人数（MMULT）

⊙ 源文件：CDROM\02\2.4\案例7.xlsx

打开光盘中的数据文件，在单元格F2中输入如下数组公式：

=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE (A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))

按下【Ctrl+Shift+Enter】组合键后，将返回前进中学的参赛人数，同一人参与多项目比赛仅算一次，结果如图2-8所示。

公式说明

本例公式使用了一个条件“（A2:A17="前进中学"）”，但是也因此而产生了很多0值，当A1:A17区域中不等于“前进中学”时就会产生0值。为了消除“1/0”所产生的错误值，以利于求和，在公式中使用了 IFERROR函数排除错误，将所有错误值都当做0处理，最后用SUM函数汇总前进中学的参赛人员的不重复个数。

案例提示

1.在公式中，0值不能做除数，它会产生一个“#DIV/0!”的错误。而SUM函数遇到这种值是无法求和的。消除错误值通常都用IFERROR函数，表示如果产生了错误，就可以按照用户设定的值进行处理，而不是原错误值进行运算。

2.本例中不能使用数组来完成多个学校的参赛人员求和。“A2:A17= "前进中学"”如果写成“A2:A17={"前进中学","天天中学"}”将无法取得结果。

案例8 计算成绩在60到80分之间合计数与个数（MMULT）

⊙ 源文件：CDROM\02\2.4\案例8.xls

计算成绩在60到80分之间的合计数与个数。

打开光盘中的数据文件，在单元格D1处输入如下数组公式用于计算60～80之间的合计：

=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11), ROW(2:11)^0)

在单元格D2处输入以下数组公式，用于计算60～80之间的个数：

=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)

按下【Ctrl+Shift+Enter】组合键后，两个公式分别返回成绩在 60到80分之间的合计数与个数，结果如图2-9所示。

公式说明

本例中表达式“(B2:B11>60)*(B2:B11<80)”可计算符合两个条件的数据个数，形成一个1列10行的数组，与MMULT函数的第二参数进行矩阵运算即可返回符合条件的个数。

再将该表达式改为“(B2:B11>60)*(B2:B11<80)*B2:B11”则成了提取符合条件的数据，不符合条件的按0处理。得到的同样是一个1列10行的数组，与第二参数进行矩阵乘积后返回符合条件的数据合计。

案例提示

在这种运算中，TRANSPOSE函数用于转置数组的方向。它在公式中的位置也很重要。在本例中，TRANSPOSE函数不能放到第二个参数中，也不能放在第一参数任意条件中，例如：

=MMULT((B2:B11>60)*(B2:B11<80)*(TRANSPOSE(B2:B11)), ROW(2:11)^0)

案例参考函数

MDETERM 数学和三角函数，见第2章2.11节

MINVERSE 数学和三角函数，见第2章2.12节。

TRANSPOSE 查找和引用函数，返回转置单元格区域。

格式 TRANSPOSE(array)

参数 array 为需要进行转置的数组或工作表中的单元格区域。数组的转置就是将数组的第一行作为新数组的第一列，数组的第二行作为新数组的第二列，以此类推。

应用举例 TRANSPOSE函数的几种用法

Excel数据位置：CDROM\02\2.4\扩展1.xls

注意点说明

在使用TRANSPOSE函数时，必须提前选择转置的单元格区域大小，保证新表格的列数等于源表格的行数，新表格的行数等于源表格的列数。

2.5 RAND 2003 2007 2010返回一个随机值

格式 RAND()

参数 该函数没有参数。

应用举例 RAND函数的几种用法

Excel数据位置：CDROM\02\2.5\2.5.xls

注意点说明

如果要使用函数RAND生成一个随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。

案例9 将20个学生的考位随机排列（RAND）

⊙ 源文件：CDROM\02\2.5\案例9.xls

将图2-10中A列的10个学生随机地排列在10个考位中。

打开光盘中的数据文件，在 H2:H11 区域建立一个辅助区，并在辅助区输入以下公式：

=RAND()

然后选择E2:F11区域，输入以下数组公式：

=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))

按下【Ctrl+Shift+Enter】组合键后，将在E2:E11区域出现10个学生的姓名，姓名的位置是随机出现的，按下 F9 键可以刷新所有人名的排位。

最后选择F2:F11区域，输入以下数组公式：

=VLOOKUP(E2:E11,$A$2:$B$11,2,0)

按下【Ctrl+Shift+Enter】组合键后，F2:F11区域将产生E列姓名对应的学号，结果如图2-11所示。

公式说明

本例中利用 RAND 函数在辅助区产生不重复的随机数，然后通过RANK函数产生1到10之间的不重复随机序列，将此序列作为INDEX函数的参数来实现随机地引用数据。LOOKUP 函数在本例中的作用是根据姓名查找对应的学号。

案例提示

本例也可以利用一个公式完成E2:F10区域中所有单元格的数据引用。

=INDEX((A2:B11,B2:B11),RANK(H2:H11,H2:H11),{1,2})

选择 E2:F10 区域，然后输入公式，按下【Ctrl+Shift+Enter】组合键即可。

案例10 将三个学校植树人员随机分组（RAND）

⊙ 源文件：CDROM\02\2.5\案例10.xls

三个学校各派 10 人参与植树活动，植树时三人一组，要求随机地在三校中各抽出一人组成一个组。

打开光盘中的数据文件，在 G2:G11 区域建立一个辅助区，并输入以下公式：

=RAND()

然后在单元格E2中，输入以下公式：

=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"&OFFSET(B$1,RANK(G2,G$2:G$11),)&":"&OFFSET(C$1,RANK(G2,G$2:G$11),)

按下【Enter】键后，将返回第一组的三人组合，然后将公式向下填充至单元格E11，结果如图2-12所示。

公式说明

本例中仍是利用RAND函数产生10个不重复的随机数作为辅助区来生成1到10的不重复序列。最后通过不重复的序列作为OFFSET函数的行偏移量来完成随机取数，且用文本连接函数“&”将三个人员姓名连接成一个组合。

案例提示

本例也可以用INDEX函数来完成，公式如下：

=INDEX(A:A,RANK(G2,G$2:G$11)+1)& ":"&INDEX(B:B,RANK(G2, G$2:G$11)+1)& ":"&INDEX(C:C,RANK(G2,G$2:G$11)+1)

2.6 EVEN 2003 2007 2010返回沿绝对值增大方向取整后最接近的偶数

格式 EVEN(number)

参数 number 表示进行四舍五入的数值。

应用举例 EVEN函数的几种用法

Excel数据位置：CDROM\ 02\2.6\2.6.xls

注意点说明

如果number为非数值参数，则EVEN返回错误值#VALUE!。

不论 number 的正负号如何，函数都向远离零的方向舍入，如果number恰好是偶数，则无须进行任何舍入处理。

案例11 统计参考人数（EVEN）

⊙ 源文件：CDROM\02\2.6\案例11.xls

工作表存放了参考人员的资料，数据排列与教室中的座次一致，但由于部分学生缺考，工作表中也有部分单元格留空，现需统计参考人员数量。

打开光盘中的数据文件，在单元格L2中输入以下公式：

=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>""))

按下【Enter】键后，将返回 A1:J12 区域中参考人员数量，结果如图2-13所示。

公式说明

本例中存放人名的单元格有三个特点：在偶数列；行号除以3的余数是1；非空。根据这三个条件就可以计算参考人员个数。

本例中 SUMPRODUCT 函数的第一条件是“(EVEN(COLUMN(A1:J12))= COLUMN(A1:J12))”，表示列号为偶数；第二条件是“(MOD(ROW (A1:J12),3)=1)”，表示行号除以3余数为1；第三条件是“(A1:J12<>"")”，表示单元格中有数据。三个条件同时满足时就按1计算，最后汇总则可以计算出总人数。

案例提示

本例也可以改EVEN函数为ODD函数，公式如下：

=SUMPRODUCT((ODD(COLUMN(A1:J12))<>COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>""))

案例参考函数

INT 数学和三角函数，见第2章2.7节。

CEILING 数学和三角函数，见第2章2.8节。

2.7 INT 2003 2007 2010返回参数的整数部分

格式 INT(number)

参数 number 表示需要进行向下摄入取整的实数。

应用举例 INT函数的几种用法

Excel数据位置：CDROM\02\2.7\2.7.xls

注意点说明

参数不能是一个单元格区域。

如果参数为非数值型，则函数INT返回错误值 #VALUE!。

案例12 成绩表的格式转换（INT）

⊙ 源文件：CDROM\02\2.7\案例12.xls

将成绩表按指定格式进行转换。

打开光盘中的数据文件，在单元格F2、G2、H2中分别输入以下公式：

=INDEX(A:A,INT((ROW(A6))/3))

=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1)

=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1)

选择F2:H2区域向下填充到F13:H13区域，结果如图2-14所示。

公式说明

本例中单元格F2的公式通过ROW函数产生一个以2开始的自然数序列，再利用INT函数将该序列与3的比值取整来产生每个数字重复出现三次的序列“{2;2;2;3;3;3;4;4;4……}”。利用这个序列作为INDEX函数的参数即可将A列中的每个姓名取出三次。

单元格G2的公式因为需要横向取数，故将INDEX函数的第一参数固定为1，第二参数通过MOD函数结果的变化来产生动态引用。

单元格H2的公式在填充时需要有行列间变化，即综合了前面两个公式的动态取数特点，分别将MOD函数和INT函数在两个参数中实现横向和纵向的变化来达到动态取数。

案例提示

1.因三个公式引用的数据起始行不同，三个公式中ROW函数引用的行号也不尽相同。

2.本例主要展示INT函数生成不同的序列，而事实上在INDEX函数的参数中是不需要INT函数来协助取整的，INDEX函数本身就有取整功能。

案例参考函数

FLOOR 数学和三角函数，见第2章2.13节。

MOD 数学和三角函数，见第2章2.14节。