一、数学运算符和运算符的优先级
所谓数学函数,顾名思义就是在公式中为了进行数学运算所设计出来的函数。其实要在Excel中进行数学运算并不是所有的情况下都用数学函数,有些情况下可以用基本的数学运算符就可以完成。公式中所能用的的数学运算符包括我们日常当中的四则运算,即数学运算符有:
加(+)、减(-)、乘(*)、除(/)、百分比(%)和乘幂(^)。在Excel中对运算符的优先级进行了设置,运算符优先级从高到低的顺序是:负号、百分比、乘幂、乘/除、加/减。我们也可以用括号来强制改变上述运算符的运算顺序,括号里面的运算是优先级最高的,在所有的优先级之外单独进行运算。在Excel中没有数学运算中的的中括号和大括号,统统都是用圆括号来表示,通过圆括号不同的嵌套关系来进行相应的优先级计算。在Excel中,如果初学者不确定某个数学公式的优先顺序,可以用我们上堂课讲的公式审核工具中的“公式求值”,可以显示每一步的运算步骤。当你写完了一个数学运算公式,不确定公式以怎样的运算顺序进行计算,比较安全和保险的做法是人为的给公式添加一些括号,以自己要求的优先级进行计算。给公式添加括号的还有一个优点是便于公式的阅读,可以很清楚看到每一步运算的顺序和过程。
二、常用的数学函数
(一)求和和汇总相关的函数
1、SUM
工具栏中有一个自动求和工具“∑”,使用这个工具可以自动生成一个自动给求和函数SUM函数,它的语法:SUM(nember1,number2,……),括号内参数为选取需要求和对象所在的单元格区域,该区域可以为连续区域(连续区域用“:”连接),也可以为不连续区域(不连续区域用“,”连接)。
括号内参数也可以为数字、逻辑值和数字的文本表达式。如果参数为数组或引用,只有其中的数字将被计算。数字或引用中的空白单元格、逻辑值、文本或错误值将被忽略。
括号内参数的个数在Excel2003版中为30个,在Excel2007版中为255个。
2、SUMIF
SUMIF为条件求和函数公式,根据指定条件对若干单元格求和。
它的语法:SUMIF(range,criteria,sum_range)。Range为用于条件判断的单元格区域,含义是要对条件所在区域进行选定;Criteria表示所要设定的条件,为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本,条件必须用半角的双引号引起来;Sum_range是需要求和的实际单元格区域,注意的是条件区域和需要求和的区域大小是完全一致的,该区域可以省略,如果省略,表示对条件判断的单元格区域进行求和。
3、SUMPRODUCT
SUMPRODUCT是一个非常重要的函数,从字面看,SUM表示求和,PRODUCT表示乘法运算。表示在两个或多个数组,将数组间对应的元素分别相乘后,将它们的乘积求和。
它的语法:SUMPRODUCT(array1,array2,array3,……),Array1,array2,array3,……为2到30个数组,其相应的元素需要相乘并求和(非数值型的数组元素作为0处理),数组参数必须具有相同的维数,否则,SUMPRODUCT函数将返回错误值#VALUE!。
这个函数通常被用来多条件的求和或多条件的计数。
4、SUBTOTAL
SUBTOTAL函数为分类汇总函数,计算口径有很多种方式,决定该函数不同的计算方式为该函数的第一参数。
它的语法:SUBTOTAL(function_num,ref1,ref2,……),function_numw为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
Function_num(包含隐藏值) | Function_num(忽略隐藏值) | 函数 | 备注 |
1 | 101 | AVERAGE | 平均值 |
2 | 102 | COUNT | 个数 |
3 | 103 | COUNTA | 非空单元格的个数 |
4 | 104 | MAX | 最大值 |
5 | 105 | MIN | 最小值 |
6 | 106 | PRODUCT | 相乘 |
7 | 107 | STDEV |
|
8 | 108 | STDEVP |
|
9 | 109 | SUM | 求和 |
10 | 110 | VAR |
|
11 | 111 | VARP |
|
该函数比较多的应用在多维引用中的汇总计算,如果单纯的计算如求最大值、平均值等可以用其他的函数如MAX、AVERAGE函数代替。
(二)舍入和取整相关的函数
1、INT/TRUNC
(1)INT
将数字向下舍入到最接近的整数(对于正数,只保留整数;对于负数是按负数减小的方向舍入取整)。
语法:INT(number),number为需要进行向下取整的实数。
(2)TRUNC
将数字的小数部分截去,返回整数。
语法:TRUNC(number,num_digits),Number需要截尾取整的数字;Num_digits用于指定取整精度的数字,默认值为0。
函数TRUNC和函数INT类似,都是返回整数。函数TRUNC直接去除数字的小数部分,而函数INT则是依照给定数的小数部分的值,将其四舍五入到最接近的整数。函数INT和函数TRUNC在处理负数时有所不同。
2、ROUNDUP/ROUNDDOWN
(1)ROUNDUP
远离零值,向上(绝对值增大的方向)舍入数字。
语法:ROUNDUP(number,num_digits),Number为需要向上舍入的任意实数;Num_digits四舍五入后的数字的位数,若Num_digits大于0,则向上舍入到指定的小数位,若Num_digits等于0,则向上舍入到最接近的整数,若Num_digits小于0,则在小数点左侧向上进行舍入。
函数ROUNDUP和函数ROUND功能类似,不同之处在于函数ROUNDUP总是向上舍入数字。
(2)ROUNDDOWN
靠近零值,向下(绝对值减小的方向)舍入数字。
语法:ROUNDDOWN(number,num_digits),Number为需要向下舍入的任意实数;Num_digits四舍五入后数字的位数,若Num_digits大于0,则向下舍入到指定的小数位,若num_digits等于0,则向下舍入到最接近的整数,若num_digits小于0,则在小数点左侧向下进行舍入。
3、CEILING/FLOOR
(1)CEILING
将参数Number向上舍入(沿绝对值增大的方向)为最接近的significance的倍数。
语法:CEILING(number,significance),Number 要四舍五入的数值,如果该参数为非数值型,CEILING返回错误值#VALUE!;Significance是需要四舍五入的乘数。注意Number和 Significance符号要相同。
(2)FLOOR
将参数Number沿绝对值减小的方向向下舍入,使其等于最接近significance的倍数。
语法:FLOOR(number,significance),Number所要四舍五入的数值;Significance为基数。注意Number和Significance符号要相同。
4、EVEN/ODD
(1)EVEN
返回沿绝对值增大方向取整后最接近的偶数。使用该函数可以处理那些成对出现的对象。
语法:EVEN(number),Number是将进行四舍五入的数值,不论该参数正负号如何,函数都向远离零的方向舍入,如果Number恰好是偶数,则无需进行任何舍入处理。
(2)ODD
返回对指定数值进行向上(沿绝对值增大方向)舍入后的奇数。
语法:ODD(number),Number是将进行四舍五入的数值,不论正负号如何,数值都朝着远离0的方向舍入,如果number恰好是奇数,则不须进行任何舍入处理。
5、ROUND
返回某个数字按指定位数取整后的数字。
语法:ROUND(number,num_digits),Number需要进行四舍五入的数字;Num_digits指定的位数,按此位数进行四舍五入,如果num_digits大于0,则四舍五入到指定的小数位,如果num_digits等于0,则四舍五入到最接近的整数,如果num_digits小于0,则在小数点左侧进行四舍五入。
我们对舍入取整函数进行归纳总结:
序号 | 函数名称 | 四舍五入 | 取值方向 | 位数可控 |
1 | INT | 否 | 数值减小 | 否 |
2 | TRUNC | 否 | 绝对值减小 | 是 |
3 | ROUNDUP | 否 | 绝对值增大 | 是 |
4 | ROUNDDOWN | 否 | 绝对值减小 | 是 |
5 | CEILING | 否 | 绝对值增大 | 是 |
6 | FLOOR | 否 | 绝对值减小 | 是 |
7 | EVEN | 否 | 绝对值增大 | 否 |
8 | ODD | 否 | 绝对值增大 | 否 |
9 | ROUND | 是 | 绝对值四舍五入 | 是 |
(三)随机函数
1、RAND
返回大于等于0及小于1的均匀分布随机数,每次计算工作表时都将返回一个新的数值。
语法:RAND(),若要生成a与b之间的随机实数,请使用RAND()*(b-a)+a;如果要使用函数RAND生成一随机数,并且使之不随单元格计算而改变,可以在编辑栏中输入“=RAND()”,保持编辑状态,然后按F9,将公式永久地改为随机数。
2、RANDBETWEEN
返回到位于两个指定数之间的一个随机数,每次计算工作表时都将返回一个新的数值。
语法:RANDBETWEEN(bottom,top),Bottom函数RANDBETWEEN将返回的最小整数;Top函数RANDBETWEEN将返回的最大整数
(四)与除法相关的函数
1、MOD
返回两数相除的余数。结果的正负号与除数相同。
语法:MOD(number,divisor),Number为被除数,Divisor为除数。函数MOD可以借用函数INT来表示:MOD(n,d)=n-d*INT(n/d)
2、QUOTIENT
返回两数相除的商。
语法:QUOTIENT(number,divisor),Number为被除数,Divisor为除数。函数QUOTIENT可以借用函数INT来表示:QUOTIENT(n,d)=INT(n/d)。
(五)与符号相关的函数
1、ABS
返回数字的绝对值。绝对值没有符号。
语法:ABS(number),Number为需要计算其绝对值实数。
2、SIGN
返回数字的符号。当数字为正数时返回1,为零时返回0,为负数时返回-1。
语法:SIGN(number),Number为任意实数。
(六)公倍和公约相关的函数
1、GCD
返回两个或多个整数的最大公约数,最大公约数是能分别将Number1和Number2除尽的最大整数。
语法:GCD(number1,number2,……), number1,number2,……为1到29个数值,如果数值为非整数,则截尾取整。如果参数为非数值型,则函数GCD返回错误值#VALUE!;如果参数小于零,则函数GCD返回错误值#NUM!;任何参数都能被1整除。
2、LCM
返回整数的最小公倍数。最小公倍数是所有整数参数number1,number2等等的最小正整数倍数。用函数LCM可以将分母补贴的分数相加。
语法:LCM(number1,number2,……),number1,number2,……是要计算最小公倍数的1到29个参数。如果参数不是整数,则截尾取整。如果参数为非数值型,则函数LCM返回错误值#VALUE!;如果参数小于零,则函数LCM返回错误值#NUM!
(七)概率计算函数
1、FACT
返回数的阶乘,一个数的阶乘等于1*2*3*……*该数。
语法:FACT(number),Number要计算其阶乘的非负数。如果输入的Number不是整数,则截尾取整。
2、PERMUT
返回从给定数目的对象集合中选取的若干对象的排列数。排列为有内部顺序的对象或事件的任意集合或子集。排列与组合不同,组合的内部顺序无意义。此函数可用于彩票抽奖的概率计算。
语法:PERMUT(number,number_chosen),Number表示对象个数的整数;number_chosen表示每个排列中对象个数的整数。这两个参数将被截尾取整,如果这两个参数为非数值型,函数PERMUT返回错误值#VALUE!;如果Number<=0或number_chosen<0,则函数PERMUT返回错误值#NUM!;如果Number<number_chosen,则函数PERMUT返回错误值#NUM!。排列数的计算公式如下:Pk,n=n!/(n-k)!
3、COMBIN
计算从给定数目的对象集合中提取若干对象的组合数。利用函数COMBIN可以确定一组对象所有可能的组合数。
语法:COMBIN(number,number_chosen),Number表示对象的总数量;number_chosen为每一组合中对象的数量。这两个参数将被截尾取整,如果这两个参数为非数值型,函数COMBIN返回错误值#VALUE!;如果Number<0、number_chosen<0或Number<number_chosen,则函数COMBIN返回错误值#NUM!。不论其内部顺序,对象组合是对象整体的任意集合或子集。组合与排列不同,排列数与对象内部顺序有关。
(八)对数计算函数
1、LOG
按所指定的底数,返回一个数的对数。
语法:LOG(number,base),Number为用于计算对数的正实数;Base为对数的底数,如果省略底数,假定其值为10.
2、LOG10
返回以10为底的对数。Number用于常用对数计算的正实数。
语法:LOG10(number),
3、LN
返回一个数的自然对数。自然对数以常数项e(2.71828182845904)为底。
语法:LN(number)用于计算其自然对数的正实数。LN函数是EXP函数的反函数。
(九)三角函数
1、DEGREES/RADIANS
(1)DEGREES
将弧度转换为度。
语法:DEGREES(angle),Angle待转换的弧度角。
(2)RADIANS
将角度转换为弧度。
语法:RADIANS(angle),Angle为需要转换成弧度的角度。
2、SIN/COS/TAN
(1)SIN
返回给定角度的正弦值。
语法:SIN(number),Number为需要求正弦的角度,以弧度表示。如果参数的单位是度,则可以乘以 PI()/180 或使用 RADIANS 函数将其转换为弧度。
(2)COS
返回给定角度的余弦值。
语法:COS(number),Number 为需要求余弦的角度,以弧度表示。如果参数的单位是度,则可以乘以 PI()/180 或使用 RADIANS 函数将其转换成弧度。
(3)TAN
返回给定角度的正切值。
语法:TAN(number),Number为要求正切的角度,以弧度表示。如果参数的单位是度,则可以乘以 PI()/180 或使用 RADIANS 函数将其转换为弧度。
3、ASIN/ACOS/ATAN
(1)ASIN
返回参数的反正弦值。反正弦值为一个角度,该角度的正弦值即等于此函数的 number 参数。返回的角度值将以弧度表示,范围为 -pi/2 到 pi/2。
语法:ASIN(number),Number角度的正弦值,必须介于 -1 到 1 之间。若要用度表示反正弦值,请将结果再乘以 180/PI( ) 或用 DEGREES 函数表示。
(2)ACOS
返回数字的反余弦值。反余弦值是角度,它的余弦值为数字。返回的角度值以弧度表示,范围是 0 到 pi。
语法:ACOS(number),Number角度的余弦值,必须介于 -1 到 1 之间。如果要用度表示反余弦值,请将结果再乘以 180/PI() 或用 DEGREES 函数。
(3)ATAN
返回反正切值。反正切值为角度,其正切值即等于 number 参数值。返回的角度值将以弧度表示,范围为 -pi/2 到 pi/2。
语法:ATAN (number),Number角度的正切值。若要用度表示反正切值,请将结果再乘以 180/PI( ) 或使用 DEGREES 函数。
(十)矩阵相关的函数
1、MMULT
返回两数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同,矩阵的列数与 array2 的列数相同。
语法:MMULT(array1,array2),Array1, array2是要进行矩阵乘法运算的两个数组。Array1 的列数必须与 array2 的行数相同,而且两个数组中都只能包含数值。Array1 和 array2 可以是单元格区域、数组常量或引用。如果单元格是空白单元格或含有文本字符串,或是 array1 的行数与 array2 的列数不相等时,则函数 MMULT 返回错误值 #VALUE!。对于返回结果为数组的公式,必须以数组公式的形式输入。
2、MINVERSE
返回数组矩阵的逆距阵。
语法:MINVERSE(array),Array是具有相等行数和列数的数值数组。
说明:
(1)Array 可以是单元格区域,例如 A1:C3;数组常量如{1,2,3;4,5,6;7,8,9};或区域和数组常量的名称。
(2)如果在 Array 中单元格是空白单元格或包含文字,则函数 MINVERSE 返回错误值 #VALUE!。
(3)如果 Array 的行和列的数目不相等,则函数 MINVERSE 也返回错误值 #VALUE!。
(4)对于返回结果为数组的公式,必须以数组公式的形式输入。
(5)与求行列式的值一样,求解矩阵的逆常被用于求解多元联立方程组。矩阵和它的逆矩阵相乘为单位矩阵:对角线的值为 1,其他值为 0。
(6)下面是计算二阶方阵逆的示例。假设 A1:B2 中包含以字母 a、b、c 和 d 表示的四个任意的数,则下表表示矩阵 A1:B2 的逆矩阵:
| 第 A 列 | 第 B 列 |
第一行 | d/(a*d-b*c) | b/(b*c-a*d) |
第二行 | c/(b*c-a*d) | a/(a*d-b*c) |
(7)函数 MINVERSE 的精确度可达十六位有效数字,因此运算结果因位数的取舍可能会导致小的误差。
(8)对于一些不能求逆的矩阵,函数 MINVERSE 将返回错误值 #NUM!。不能求逆的矩阵的行列式值为零。