人力资源,人事,HR报表大全28-人力资源常用函数与公式
时间：2025-05-14
人力资源常用函数与公式
函数ABS说明返回绝对值
语法
"=ABS
(I2)"=
示例38
COUNT返回参数的个数
"=COUNT
(K4º£)=L16±¡
20
COUNTA
返回参数组中非空
值的数目
)="=COUNTA(K4º£L16±¡
"=COUNTBLANK(K4:L16)"=
22
COUNTBLANK计算指定单元格区
域中空白单元格的数目COUNTIF
计算给定区域内满足特定条件的单元格的数目。
4
"=CONUNTIF(L5:L16,L5)"=
2
EXP
求e 的 n 次幂
"=EXP(G28)"=
403.4288
=e=e
FACT
n2.302585
求数的阶乘
"=FACT(I25)"=
24
=N!=4!
LN
求一个数的自然对数
=LogeN=Loge10
"=LN(I28)"=2.302585
LOG 求一个数的对数
=LOGMN=LOG39
"=LOG(I31,J31)"=
2
LOG10 求以 10 为底的对数
"=LOG10(I34)"=
2
=LOG10N=LOG10100
PRODUCT 求乘积值
"=PRODUCT(I37,J37,K37,L37)"=
"=SIGN(I40)"=
"=SIGN(I42)"=
30
=A´B´C´LN=1´2´3´5=
SIGN
求数字的符号A > 0A = 0
10-1
"=SIGN(I44)"=
A < 0
人力资源常用函数与公式
SQRT
求正平方根
" =SQRT ( I 47 )" =
4
=SUM
N
返回某一单元格区域中所有数字之和
= å X = A 1 + A2 + L + B1 L + B N =
" = SUM ( L 50 : M 67 )" =
SUMIF
根据指定条件对若干单元格求和
= åi =1 X = Ai =1 + Bi =1 + L + N i =1 =SUMPRODUCT 并返回乘积之和=
" = SUM ( K 50 :K 67 ,K 50 ,L 50 :M 67 )" =" =SUMPRODUCT( L50:L67,M 50:M67 )"=" = SUMSQ ( L 50 :M 67 )" =
å
( A ´ B ) = ( A1 ´ B 1 ) + ( A 2 ´ B 2 ) + L =
SUMSQ
求所有参数的平方和
2 = å X 2 = A12 + A22 + L + AN =
SUMX2MY2
求两数组中对应数值的平方差之和=
å (AN 1
2
- B2
)=
" = SUMX 2 MY 2( L 50 :L67 ,M 50 :M 67 )" =" =SUMX2PY 2( L50:L67,M 50:M67 )" =
SUMX2PY2
求两数组中对应数值的平方和之和=
å (AN 1
2
+ B2 ) =
SUMXMY2
求两数组中对应数值之差的平方和 " =SUMXMY2( L50:L67,M 50:M 67 )" ==
å1
N
(A
- B
)2
"=SQRT(I47)"=
4
SUM 返回某一单元格区域中所有数字之和
=åX=A1+A2+L+B1L+BN=
SUMIF 根据指定条件对若干单元格求和
"=SUM(L50:M67)"=
=åi=1X=Ai=1+Bi=1+L+Ni=1=
SUMPRODUCT 并返回乘积之和
=
"=SUM(K50:K67,K50,L50:M67)"="=SUMPRODUCT(L50:L67,M50:M67)"=
"=SUMSQ(L50:M67)"=
å(
2
A´B)=(A1´B1)+(A2´B2)+L=
SUMSQ 求所有参数的平方和
=åX=A+A+L+A=
21
22
2N
SUMX2MY2 求两数组中对应数值的平方差之和
=
"=SUMX2MY2(L50:L67,M50:M67)"=
"=SUMX2PY2(L50:L67,M50:M67)"=
å(A
1
N
2
-B2
)=
SUMX2PY2 求两数组中对应数值的平方和之和
=
å(A
1
N
2
+B2)=
SUMXMY2 求两数组中对应数值之差的平方和"=SUMXMY2(L50:L67,M50:M67)"=
=
å
1
N
(A
-B
)2
人力资源常用函数与公式
-38 编号 1 2 3 4 5 6 7 8 9 10 天数 21 19 18 17 23 28 19 21 29 30
6
4
10
9
3
100
1
2
3
5
10 0 -10
-38
6
4
10
93
100
12100-10
35
人力资源常用函数与公式
16
3938.22
=
648.46
221661.5
166701.6
-154701
488104.1
44781.14
组别AB190.53142.12288.43163.17347.3763.162175.8166.331100.01144.75246.32126.33373.69138.963105.27126.33186.32121.06360.0173.69190.53142.12288.43163.17247.3763.161175.8166.333100.01144.75246.32126.33273.69138.961
105.27
126.33
0:M67)"
…… 此处隐藏：102字，全部文档内容请下载后查看。喜欢就下载吧 ……
人力资源,人事,HR报表大全28-人力资源常用函数与公式.doc
将本文的Word文档下载到电脑
