Excel按权重产生随机数怎么编辑公式答:取整数:=IF(RAND()<=0.05,RANDBETWEEN(6,9),IF(RAND()>=0.95,RANDBETWEEN(20,23),RANDBETWEEN(10,19)))如果要小数:=IF(RAND()<=0.05,RANDBETWEEN(6,9)+RAND(),IF(RAND()>=0.95,RANDBETWEEN(20,23)+RAND(),RANDBETWEEN(10,19)+RAND()))函数RAND():生成[0,1)区间(前闭后开...
excel中,如何利用函数对5个单元格中不为空的那个单元格里的值进行公式...答:3,0.3,0.3,0.2,0.2,0.2,0.2,0.2}))还可进一步简化为:=SUMPRODUCT((B3:F3*50%+G3:K3*30%+L3:P3*20%)*{1,2,3,4,5})然后下拉填充公式即可。注:上述公式有一个好处,在权重指标下的难度系数的得分可以有几个,例如张三,他在KPI权重下,难度系数3为50,、难度系统4为30。
取所有数的平均值作为基本值,权重3分,高出2%加0.1分,最多加2分,每低...答:假定,数值在A列,最终得分在B列,在B1中输入公式:=IF(ISERROR(AVERAGE(A:A)),"",IF(OR(QUOTIENT((A1-AVERAGE(A:A))/AVERAGE(A:A),0.02)*0.1>2,QUOTIENT((A1-AVERAGE(A:A))/AVERAGE(A:A),0.02)*0.1<-2),LOOKUP(QUOTIENT((A1-AVERAGE(A:A))/AVERAGE(A:A),0.02)*0.1,...