1. ABS-求绝对值
1)概述
语法 | ABS(number) | 返回指定数字的绝对值 绝对值是指没有正负符号的数值 |
---|---|---|
参数 | number | 需要求出绝对值的任意实数 |
返回值 | number | - |
2)示例
公式 | 结果 |
---|---|
ABS(-1.5) | 1.5 |
ABS(0) | 0 |
ABS(2.5) | 2.5 |
3)视频教程:ABS 函数
2. AVERAGE
AVERAGE(number1,number2,…):返回指定数据的平均值。 Number1,number2…:用于计算平均值的参数。
注: 参数必须是数字,或是含有数字的名称,数组或引用。 如果数组或引用参数中含有文字,逻辑值,或空白单元格,这些值将被忽略;但是,单元格中的零值则参与计算。
示例:
如果 A1:A6 被命名为“ages”,分别等于 10,23,14,24,33 及 25,则:
AVERAGE(A1:A6)等于 21.5。
AVERAGE(ages)等于 21.5。
如果还有一个年龄为 18 的,求所有年龄的平均值为:
AVERAGE(A1:A6,18)等于 21。
(即对数组:10,23,14,24,33,25,18 进行取平均值运算)
视频教程:average 函数
3. BITNOT
BITNOT(int):将一个十进制整数进行二进制取反运算。 int:需要进行转换的十进制数。
示例:
BITNOT(3)等于 -4。
BITNOT(12)等于 -13。
4. BITOPERATION
BITOPERATIOIN(int,int,op):位运算,返回两个整数根据 op 进行位运算后的结果。 int:十进制整数。 op:位运算操作符,支持"&"(与),"|"(或),"^"(异或),"<<"(左移),">>"(右移)。
示例:
BITOPERATION(4,2,"&")等于 0。(表示 4 与 2 进行"与"运算。)
BITOPERATION(4,2,"|")等于 6。(表示 4 与 2 进行"或"运算。)
BITOPERATION(4,2,"^")等于 6。(表示 4 与 2 进行"异或"运算。)
BITOPERATION(4,2,"<<")等于 16。(表示 4 按位左移 2 位。4 的二进制为:100,左移 2 位变成:10000;即十进制 16。)
BITOPERATION(4,2,">>")等于 1。(表示 4 按位右移 2 位。4 的二进制 100 右移 2 为变成:1;即十进制 1。)
BITOPERATION(4,2,"^~")等于 -7。(表示 4 与 2 进行"同或"运算。)
5. CEILING
CEILING(number):将参数 number 沿绝对值增大的方向,舍入为最接近的整数
示例:
CEILING(-2.5)等于 -3。
CEILING(0.5)等于 1。
6. COMBIN
COMBIN(number,number_chosen):返回若干个指定对象的组合数。函数中的 number 指对象总数,number_chosen 指对象总数中某一组合的数量。该函数与数学表达式为 Cnk 功能相同, 数学表达式中的"n"指对象总数。,"k"指在对象总数中某一组合的数量。
注: Number 与 number_chosen 必须是非负整数,且 Number>=number_chosen,否则返回*VALUE?。 对象组合是对象总体的子集。与排列不同的是,组合不涉及对象内部排序。 假设 number=n,number_chosen= k,则 COMBIN(n,k)=Cnk=n!/(k!(n-k)!)。
示例:
COMBIN(5,2)等于 10。
意思是 5 个元素进行排列组合,每组 2 个元素,共有 10 种排法。如下图所示:
7. COUNT
COUNT(value1,value2,…):计算数组或数据区域中所含数字项的个数。 Value1,value2,…:可包含任何类型数据的参数,但此函数只将数字类型的数据计算在内。 备注:数字、日期或以文字代表的数字参数将被计算在内;但无法转换成数字的错误值或文本值参数将忽略不计。 如果数组或引用参数中包含可解析文本值、逻辑值、零值或空白单元格,这些值都将参与计算,而不可解析的文本值忽略不计。
注: 数字、日期或以文字代表的数字参数将被计算在内;但无法转换成数字的错误值或文本值参数将忽略不计。 如果数组或引用参数中包含可解析文本值、逻辑值、零值或空白单元格,这些值都将参与计算,而不可解析的文本值忽略不计。
COUNT(0,0,0,0,0,0)等于6。
视频教程:count 函数
8. DECIMAL
1)概述
语法 | DECIMAL(number) | 返回number的大数类型,常用于精确计算,函数入参会进行trim处理(去除字符串的首尾空格)。 |
---|---|---|
参数 | number | 任意实数 |
返回值 | number | - |
2)示例
公式 | 结果 |
---|---|
DECIMAL(number) | 1.5 |
DECIMAL(0) | 0 |
DECIMAL(2.5) | 2.5 |
9. EVEN
EVEN(number):返回沿绝对值增大方向取整后最接近的偶数。使用该函数可以处理那些成对出现的对象。 number:所要取整的数值。
注: 不论正负号如何,数值都朝着远离 0 的方向舍入。如果 number 恰好是偶数,则不须进行任何舍入处理。
示例:
EVEN(1.5)等于 2。
EVEN(3)等于 4。
EVEN(2)等于 2。
EVEN(-1)等于 -2。
10. EXP
EXP(number):返回 e 的 n 次幂。常数 e 为自然对数的底数,等于 2.71828182845904。 Number:为任意实数,作为常数 e 的指数。
注: 如果要返回其他常数作为底数的幂,可以使用指数运算符(^)。例如:在 4^2 中,4 是底数,而2是指数。 EXP 函数与 LN 函数互为反函数。
示例:
EXP(0)等于 1。
EXP(3)等于 20.08553692。
EXP(LN(2))等于 2。
11. FACT
FACT(number):返回数的阶乘,一个数的阶乘等于 0!*1*2*3*...*number(其中0!=1)。 number:要计算其阶乘的非负数。如果输入的 number不是整数,则截尾取整。
示例:
FACT(1)等于 1 。
FACT(1.9)等于 FACT(1) 等于 1。
FACT(5)等于 1*2*3*4*5 等于 120。
12. FLOOR
FLOOR(number):将参数number去尾舍入,即舍弃掉参数 number 后面的小数部分的数值。
注: 当number为非数值型的参数时,函数 FLOOR 返回 0。
示例:
FLOOR(2.5)等于 2。
FLOOR(-3.5)等于 -3。
FLOOR(0.143,0.03)等于 0。
FLOOR(hello)等于 0。
13. INT
INT(number):返回数字下舍入(数值减小的方向)后最接近的整数值。 Number:需要下舍入为整数的实数。
示例:
INT(4.8)等于 4。
INT(-4.8)等于 -5。
INT(4.3)等于 4。
公式 INT(A1) 将返回 A1 单元格中的一个正实数的整数数部分
14. LN
LN(number):返回一个数的自然对数。自然对数以常数项 e(2.71828182845904)为底。 number:是用于计算其自然对数的正实数。
示例:
LN(86)等于 4.45437。
LN(2.7182818)等于 1。
LN(EXP(3))等于 3。
15. LOG
LOG(number,base):按指定的任意底数,返回数值的对数。Number:需要求对数的正实数。 Base:对数的底数。缺省默认值为 10。
示例:
LOG(16,2)等于 4。
LOG(10)等于 1。
LOG(24,3)等于 2.892789261。
16. LOG10
LOG10(number):LOG10(number):返回以 10 为底的对数。number:用于常用对数计算的正实数。
示例:
LOG10(86)等于 1.934498451。
LOG10(10)等于 1。
17. MAX
MAX(number1,number2,…):返回参数列表中的最大值。 Number1,number2,…:1 到 30 个需要找出最大值的参数。
注1:参数可以是数字、空白单元格、逻辑值或数字的文本表达式。 如果数组或引用参数中包含可解析文本值,逻辑值,零值或空白单元格,这些值都将参与计算,而不可解析的文本值忽略不计。 如果参数中没有任何数字,MAX 将返回 0。
注2:MAX 函数计算最大值时,若最大值的后四位小数位为 9999(如7.909999),则对最大值进行四舍五入,返回7.91。可通过嵌套 SUMPRECISE() 函数避免四舍五入,获取精确的最大值,如 SUMPRECISE(MAX(5.19999,5.1)) 返回 5.19999 。
示例:
MAX(0.1,0,1.2)等于 1.2。
视频教程:MAX 函数
18. MIN
MIN(number1,number2,…):返回参数列表中的最小值。 Number1,number2,…:1 到 30 个需要找出最小值的参数。
注1: 若参数中没有数字,函数 MIN 将返回 0。 参数应为数字、空白单元格、逻辑值或是表示数值的文本串。如果参数是错误值时,MIN 将返回错误信息。 如果数组或引用参数中包含可解析文本值,逻辑值,零值或空白单元格,这些值都将参与计算,而不可解析的文本值忽略不计。
注2:MIN 函数计算最小值时,若最小值的后四位小数位为 9999(如2.909999),则对最小值进行四舍五入,返回2.91。可通过嵌套 SUMPRECISE() 函数避免四舍五入,获取精确的最小值,如 SUMPRECISE(MAX(2.19999,2.2)) 返回 2.19999 。
示例:
如果 B1:B4 包含 3,6,9,12,则:
MIN(B1:B4)等于 3。
MIN(B1:B4,0)等于 0。
视频教程:MIN 函数
19. MOD
MOD(number,divisor):返回两数相除的余数。结果的正负号与除数相同。 number:为被除数。 divisor:为除数。
示例:
MOD(3, 2)等于 1 。
MOD(-3, 2)等于 1 。
MOD(3, -2)等于 -1 。
MOD(-3, -2)等于 -1。
注:小数的情况比较特殊。例:mod(49.5, 5.5)=4,并非我们的期望值 0,我们可以采取其变通的方法:mod(49.5*10, 5.5*10)=0;mod(49.6, 5.5)=4,并非我们的期望值 0.1,同理:mod(49.6*10, 5.5*10)/10=0.1 //将其先取整再求余!
20. ODD
ODD(number):是将一个不是奇数的数值向上舍入为最接近的奇数。 number:是要舍入求奇的数值。
注:不论正负号如何,数值都朝着远离0的方向舍入。如果 number恰好是奇数,则不须进行任何舍入处理。
示例:
ODD(1.5)等于 3。
ODD(3)等于 3。
ODD(2)等于 3。
ODD(-1)等于 -1。
21. PI
PI:是一个数学常量,函数返回精确到 15 位的数值 3.141592653589793。
示例:
SIN(PI()/2)等于 1。( 计算圆的面积的公式: S=PI()*(r^2),其中 S 为圆的面积,R 为圆的半径。)
22. POWER
POWER(number,power):返回指定数字的乘幂。 Number:底数,可以为任意实数。 Power:指数。参数 number 按照该指数次幂乘方。
注: 可以使用符号“^”代替 POWER,如: POWER(5,2) 等于 5^2。
示例:
POWER(6,2)等于 36。(6^2=6*6)
POWER(14,5)等于 537824。(14^5=14*14*14*14*14)
POWER(4,2/3)等于 2.519842100。(=3√(4^2)即3√16)
POWER(3,-2.3)等于 0.079913677。(3√(1/9))
23. PRODUCT
PRODUCT(number1,number2, ...):将所有以参数形式给出的数相乘,并返回乘积值。 number1, number2, ...:为 1 到 n个需要相乘的数字参数。(参数个数的上限为 30) 。
示例:
PRODUCT(3,4)等于 12。(3*4)
PRODUCT(3,4,5)等于 60。(3*4*5)
24. PROMOTION
PROMOTION(value1,value2):返回 value2 在 value 1上提升的比例.
示例:
PROMOTION(12, 14)等于 0.166666666(即提升了 16.6666666%.)。=(14-12)/12
PROMOTION(-12, 14)等于 2.166666666(即提升了 216.6666666%)。=(14-(-12))/12
25. RAND
RAND():返回一个随机数。数值位于区域[0,1],每计算一次工作表,函数都会返回一个新的随机数值。
注: 要生成一个位于 a 和 b 之间的随机数,可以使用公式 =RAND()*(b-a)+a。
示例:
RAND()*60(生成一个大于等于0,小于60的随机数。)
RAND()*19(生成一个大于等于0,小于19的随机数。)
RAND()*50(生成一个大于等于0,小于50的随机数。)
其他变形生成方法:
1)生成 A 与 B 之间的随机数字(A≤随机数<B)
单元格中输入公式:RAND()*(B-A)+A
例如:生成 1 到 10 之间的随机数字,输入:=RAND()*9+1
2)生成 A 与 B 之间的随机整数(A≤随机数<B)
单元格中输入公式:=INT(RAND()*(B-A)+A)
例如生成 1 到 10 之间的随机整数,输入:=INT(RAND()*9+1)
其余数字,将鼠标置于该格子右下角,变为十字时,向下拖拉即可。
3)生成 A 与 B 之间的随机整数(A≤随机数≤B)
单元格中输入公式:RANDBETWEEN(A,B) 即可。
例如生成 1 到 10 之间的随机整数,输入:=RANDBETWEEN(1,10)
如想得到1位随机小数,则输入:=RANDBETWEEN(1,100)/10,2 位、3 位依此类推=RANDBETWEEN(1,1000)/100、=RANDBETWEEN(1,10000)/1000
26. RANDBETWEEN
RANDBETWEEN(value1,value2):返回 value1 和 value2 之间的一个随机整数。
示例:
RANDBETWEEN(12.333, 13.233)只会返回 13。
RANDBETWEEN(11.2, 13.3)有可能返回 12 或者 13。
27. ROUND
ROUND(number,num_digits):返回某个数字按指定位数舍入后的数字。number:需要进行舍入的数字。
如果 num_digits 大于 0,则舍入到指定的小数位。
如果 num_digits 等于 0,则舍入到最接近的整数。
如果 num_digits 小于 0,则在小数点左侧进行舍入。
因浮点数存在精度计算丢失问题,导致计算结果里可能带上 9999,0000 这些,因此加入第三个参数来控制是否需要去除9999,0000,false 表示需要过滤 9999,0000这些数据,true 表示保留,参数为空则默认为 false。
注1:2020-07-08 及之后版本的 JAR 包才会有第三个参数。
注2:2020-07-08 及之后版本的 JAR 包 number 参数支持字符串。
示例:
ROUND(2.15, 1)等于 2.2。
ROUND(2.149, 1)等于 2.1。
ROUND(-1.475, 2)等于 -1.48。
ROUND(21.5, -1)等于 20。
ROUND(1.99999999, 8) 等于2。
ROUND(1.99999999, 8, true) 等于 1.99999999。
视频教程:Round 函数
28. ROUND5
ROUND5(number,num_digits):这个是四舍五入,奇数进位偶数不进位。number:需要进行舍入的数字。num_digits:指定的位数,按此位数进行舍入。
如果 num_digits 大于 0,则舍入到指定的小数位。
如果 num_digits 等于 0,则舍入到最接近的整数。
如果 num_digits 小于 0,则在小数点左侧进行舍入。
因浮点数存在精度计算丢失问题,导致计算结果里可能带上 9999,0000 这些,因此加入第三个参数来控制是否需要去除9999,0000,false 表示需要过滤 9999,0000这些数据,true 表示保留,参数为空则默认为 false。
注1:2020-07-08 及之后版本的 JAR 包才会有第三个参数。
注2:2020-07-08 及之后版本的 JAR 包 number 参数支持字符串。
示例:
ROUND5(2.125, 2)等于 2.12。
ROUND5(2.135, 2) 等于 2.14。
ROUND5(1262.135, -1)等于1260。
ROUND5(1.99999999, 8) 等于2。
ROUND5(1.99999999, 8, true) 等于 1.99999999 。
29. ROUNDDOWN
ROUNDDOWN(number,num_digits):靠近零值,向下(绝对值减小的方向)舍入数字。 number:为需要向下舍入的任意实数。 num_digits:舍入后的数字的位数。
注: 函数 ROUNDDOWN 和函数 ROUND 功能相似,不同之处在于函数 ROUNDDOWN 总是向下舍入数字。
示例:
ROUNDDOWN(3.2, 0)等于 3 。
ROUNDDOWN(76.9,0)等于 76。
ROUNDDOWN(3.14159, 3)等于 3.141 。
ROUNDDOWN(-3.14159, 1)等于 -3.1 。
ROUNDDOWN(31415.92654, -2)等于 31,400。
30. ROUNDUP
ROUNDUP(number,num_digits):远离零值,向上(绝对值增大的方向)舍入数字。 number:为需要向上舍入的任意实数。 num_digits:舍入后的数字的位数。 备注:函数 ROUNDUP 和函数 ROUND 功能相似,不同之处在于函数 ROUNDUP总是向上舍入数字。
示例:
ROUNDUP(3.2,0)等于 4
ROUNDUP(76.9,0)等于 77
ROUNDUP(3.14159, 3)等于 3.142
ROUNDUP(-3.14159, 1)等于 -3.2
ROUNDUP(31415.92654, -2)等于 31,500
31. SIGN
SIGN(number):返回数字的符号。当数字为正数时返回 1,为零时返回 0,为负数时返回 -1。 Number:为任意实数。
示例:
SIGN(10)等于 1。
SIGN(4-4)等于 0 。
SIGN(-0.00001)等于 -1。
32. SQRT
SQRT(number):返回一个正数的平方根。 Number:要求平方根的数。
注: Number 必须是一个正数,否则函数返回错误信息 NAN。
示例:
SQRT(64)等于 8。
SQRT(-64)返回 NAN。
33. SUM
1)概述
语法 | SUM(number1,number2,…) | 求一个指定单元格区域中所有数字之和。 |
---|---|---|
参数 | Number1,number2,… | 1 到 30 个参数或指定单元格区域中所有数字。 |
返回值 | number | - |
2)注意事项
函数将直接键入参数中的数值、逻辑值及文本表达式计算在内。 若参数是数组或引用,则只有数组或单元格引用中的数值进行计算。
当对精度有要求时,比如整数位大于16位时,需要使用 SUMPRECISE 函数。
3)示例
公式 | 结果 |
---|---|
SUM(70,80) | 150 |
SUM("70",80,TRUE) | 151 逻辑值“TRUE”作为1来计算;“FALSE”作为 0 计算;文本“70”作为 70 来计算。 |
SUM(A1:A5) | 对 A1 到 A5 之间的格子中的数值求和。 |
SUM(A1:A5,50) | 对 A1 到 A5 之间的格子中的数值和数值 50 求和。 |
4)视频教程:SUM 函数
34. SUMPRECISE
1)概述
语法 | SUMPRECISE(number1,number2,…) | 当对精度有要求时,比如整数位大于16位时,求一个指定单元格区域中所有数字之和,需要使用这个函数 |
---|---|---|
参数 | Number1,number2,… | 1 到 30 个参数或指定单元格区域中所有数字。 |
返回值 | number | - |
2)注意事项
函数将直接键入参数中的数值、逻辑值及文本表达式计算在内。 若参数是数组或引用,则只有数组或单元格引用中的数值进行计算。
3)示例
公式 | 结果 |
---|---|
SUMPRECISE(59300000000000000000.23,59300000000000003400.23) | 118600000000000003400.46 |
35. SUMSQ
SUMSQ(number1,number2, ...):返回所有参数的平方和。 number1, number2, ...:为 n 个需要求平方和的参数(n 的上限为 30),也可以使用数组或对数组的引用来代替以逗号分隔的参数。
示例:
SUMSQ(3, 4)等于 25。(3^2+4^2=9+16)
36. TOBINARY
1)概述
语法 | TOBINARY(int) | 将一个十进制整型数转换成二进制表示的字符串。 |
---|---|---|
参数 | int | 表示需要进行转换的十进制整数。 |
返回值 | String | - |
2)注意事项
int 参数支持范围:0 ~ Integer.MAX_VALUE。
3)示例
公式 | 结果 | 备注 |
---|---|---|
TOBINARY(10) | 1010 | - |
TOBINARY(20) | 10100 | - |
37. TOHEX
TOHEX(int):将一个十进制整型数转换成十六进制表示的字符串。 int:表示需要进行转换的十进制整数。
示例:
TOHEX(15)等于"f"。
TOHEX(20)等于"14"。
38. TOOCTAL
1)概述
语法 | TOOCTAL(int) | 将一个十进制整型数转换成八进制表示的字符串。 |
---|---|---|
参数 | int | 表示需要进行转换的十进制整数。 |
返回值 | String | - |
2)注意事项
int 参数支持范围:0 ~ Integer.MAX_VALUE。
3)示例
公式 | 结果 | 备注 |
---|---|---|
TOOCTAL(10) | 12 | - |
TOOCTAL(20) | 24 | - |
39. TRUNC
TRUNC(number,num_digits):取整。将数字的小数部分截去,返回整数。 number:需要截尾取整的数字。 num_digits:用于指定取整精度的数字。
示例:
TRUNC(8.9)等于 8。
TRUNC(-8.9)等于 -8。
TRUNC(PI())等于 3。
40. WEIGHTEDAVERAGE
WEIGHTEDAVERAGE(cell1,cell2):返回指定单元格数据的加权平均值。
加权平均值:是不同比重数据的平均数,即将各数值乘以相应的权数,然后加总求和得到总体值,再除以权数和得到的值。
参数 | 含义 | 参数格式 |
---|---|---|
cell1 | 数值单元格 | 单元格名称,例如:A1:A4 |
cell2 | 权数单元格 | 单元格名称,例如:B1:B4 |
示例:
报表中, A1,A2,A3,A4 单元格值分别为 10,9,8,7;B1,B2,B3,B4 单元格值分别为 0.2,0.1,0.3,0.4
求加权平均值:WEIGHTEDAVERAGE(A1:A4,B1:B4) 等于 8.1,即(10*0.2+9*0.1+8*0.3+7*0.4)/(0.2+0.1+0.3+0.4)=8.1。
注:求加权平均值时涉及到的所有单元格中数据必须都是数值型。
41. GCD-求1-255个数的最大公约数
1)概述
语法 | GCD(number1,number2,number3,........) | 求1-255个数的最大公约数 |
---|---|---|
参数 | number | 可以是具体的数字、参数、表格区域、可转义为数字的字符串; |
2)注意事项
参数最终值、表格区域最终值必须为数字或可以转义为数字的字符串
number元素个数为1-255个,超过255个时,取前255个元素,0个时返回错误#VALUE
注:支持导出Excel保留公式。
注:支持 11.0.5 以及之后的版本使用。
3)示例
GCD()等于#VALUE
GCD(16,12)等于4
42. LCM-求1-255个数的最小公倍数
1)概述
语法 | LCM(number1,number2,number3,........) | 求1-255个数的最小公倍数 |
---|---|---|
参数 | number | 可以是具体的数字、参数、表格区域; |
2)注意事项
参数最终值、表格区域最终值必须为数字或可以转义为数字的字符串,若存在非数字元素,则自动忽略该元素
number元素个数为1-255个,超过255个时,取前255个元素,0个时返回错误#VALUE
注1:支持导出Excel保留公式。
注2:支持 11.0.5 以及之后的版本使用。
3)示例
LCM()等于#VALUE
LCM(4,5)等于20