给了一个数据求数值在范围内求对应的得分
如下图建立表个,B1输入公式,向下复制。
=LOOKUP(A1,D$1:D$9,E$1:E$9)
设定业绩最好的人为120分,最差的为60分。
计算出所有人员的业绩平均值B。
将业绩最好的员工业绩值-平均值B,得到C。
业绩最差的员工业绩值-平均值B,得到D。
对于任一员工,其业绩值-平均值B,得到E,若E为正数,其得分为90+[(E/C)*30)]
若E为负数,其得分为90+[(E/D)*30)]
由此方法即可得出所有员工在[60,120]得分区间的考核得分。
根据表的情况来看,不能只是输入一个体重值,而是需要输入3个值:体重,性别,年级,由此我编写了一个函数,使用过程中需要注意以下几点:
1、调用格式:在单元格中输入=BMIScore(25,"F",1),其中25是体重,第二个参数是性别,可输入F或M,第三个参数是年级,用1表示初一,2表示初二
2、代码需要放到excelVBA窗口中,在excel中按alt+F11可打开VBA窗体
3、插入/模块,新创建一个模块,然后将代码复制到该模块中,在此模块中的函数才能在excel中像其他函数一样使用。
以下为具体代码,如果格式不太好看,你可以再见最后的图片:
' 体重指数单项评分函数
Function BMIScore(weight As Double, sex As String, class As Integer) As Integer
Dim result As Integer
Select Case sex
Case "F": ' 性别=女
Select Case class
Case 1: ' 初一
Select Case weight
Case Is <= 14.7
result = 80
Case 14.8 To 21.7
result = 100
Case 21.8 To 24.4
result = 80
Case Is >= 24.5
result = 60
End Select
Case 2: ' 初二
Select Case weight
Case Is <= 15.2
result = 80
Case 15.3 To 22.2
result = 100
Case 22.3 To 24.8
result = 80
Case Is >= 24.9
result = 60
End Select
End Select
Case "M": ' 性别=男
Select Case class
Case 1: ' 初一
Select Case weight
Case Is <= 15.4
result = 80
Case 15.5 To 22.1
result = 100
Case 22.2 To 24.9
result = 80
Case Is >= 25
result = 60
End Select
Case 2: ' 初二
Select Case weight
Case Is <= 15.6
result = 80
Case 15.7 To 22.5
result = 100
Case 22.6 To 25.2
result = 80
Case Is >= 25.3
result = 60
End Select
End Select
End Select
BMIScore = result
End Function
在 Excel 2007 中,可以用以下公式满足题述要求(以所拟表为例):
1. 得分公式:=IF(AND(AND(D2>=15.5,D2<=22.1),B2="男",C2="初一"),100,IF(AND(AND(D2>=15.7,D2<=22.5),B2="男",C2="初二"),100,IF(AND(AND(D2>=14.8,D2<=21.7),B2="女",C2="初一"),100,IF(AND(AND(D2>=15.3,D2<=22.2),B2="女",C2="初二"),100,IF(AND(D2<=15.4,B2="男",C2="初一"),80,IF(AND(D2<=15.6,B2="男",C2="初二"),80,IF(AND(D2<=14.7,B2="女",C2="初一"),80,IF(AND(D2<=14.7,B2="女",C2="初二"),80,IF(AND(D2<=15.2,B2="女",C2="初二"),80,IF(AND(AND(D2>=22.2,D2<=24.9),B2="男",C2="初一"),80,IF(AND(AND(D2>=22.6,D2<=25.2),B2="男",C2="初二"),80,IF(AND(AND(D2>=21.8,D2<=24.4),B2="女",C2="初一"),80,IF(AND(AND(D2>=22.3,D2<=24.8),B2="女",C2="初二"),80,IF(AND(D2>=25,B2="男",C2="初一"),60,IF(AND(D2>=25.3,B2="男",C2="初一"),60,IF(AND(D2>=24.5,B2="女",C2="初一"),60,IF(AND(D2>=24.9,B2="女",C2="初二"),60)))))))))))))))))
2. 等级公式:=IF(AND(AND(D2>=15.5,D2<=22.1),B2="男",C2="初一"),"正常",IF(AND(AND(D2>=15.7,D2<=22.5),B2="男",C2="初二"),"正常",IF(AND(AND(D2>=14.8,D2<=21.7),B2="女",C2="初一"),"正常",IF(AND(AND(D2>=15.3,D2<=22.2),B2="女",C2="初二"),"正常",IF(AND(D2<=15.4,B2="男",C2="初一"),"低体重",IF(AND(D2<=15.6,B2="男",C2="初二"),"低体重",IF(AND(D2<=14.7,B2="女",C2="初一"),"低体重",IF(AND(D2<=14.7,B2="女",C2="初二"),"低体重",IF(AND(D2<=15.2,B2="女",C2="初二"),"超重",IF(AND(AND(D2>=22.2,D2<=24.9),B2="男",C2="初一"),"超重",IF(AND(AND(D2>=22.6,D2<=25.2),B2="男",C2="初二"),"超重",IF(AND(AND(D2>=21.8,D2<=24.4),B2="女",C2="初一"),"超重",IF(AND(AND(D2>=22.3,D2<=24.8),B2="女",C2="初二"),"超重",IF(AND(D2>=25,B2="男",C2="初一"),"肥胖",IF(AND(D2>=25.3,B2="男",C2="初一"),"肥胖",IF(AND(D2>=24.5,B2="女",C2="初一"),"肥胖",IF(AND(D2>=24.9,B2="女",C2="初二"),"肥胖")))))))))))))))))
注1:上述“得分公式”与“等级公式”结构完全相同,只是因为“得分”与“等级”的对应关系有重复项,所以采用了这种方式
注2:这是个“吓人”的公式,利用 IF 函数应对这么多的条件,的确够“吓人”
公式的结果如下图示:
公式结果的图示
给了一个数据求数值在范围内求对应的得分
1、调用格式:在单元格中输入=BMIScore(25,"F",1),其中25是体重,第二个参数是性别,可输入F或M,第三个参数是年级,用1表示初一,2表示初二 2、代码需要放到excelVBA窗口中,在excel中按alt+F11可打开VBA窗体 3、插入\/模块,新创建一个模块,然后将代码复制到该模块中,在此模块中的函数才能...
excel查找一个单元格内的值在另一区域相同值,并返回相同值后一列同行...
可以使用if这个函数,找到相同进行数据返回即可。给出数字的起始位置和长度,提取某特定字符串后的数字。示例公式:=-lookup(0,-mid(a1,find("ABC",a1)+LEN("ABC"),row(1:99))) 公式结果为提取A1单元格中字符串ABC之后的数字。
怎么统计一个表格中数据出现的次数啊?
在一个空白单元格中输入“=COUNTIF(A1:A10,A1)”(假设需要统计的列为A列,数据范围为A1到A10),并按下回车键。这个公式将会计算A列中第一个单元格出现的次数,并在这个单元格中显示该数值在整个列中出现的次数。如果需要统计其他数值,可以将公式中的“A1”改为其他单元格地址。复制这个公式到其他...
excel,A1...A1000中各对应一个数值,求在范围1(A列中)对应的数求平均值...
在E1中输入公式:=SUMPRODUCT(($A$1:$A$10000>=C1)*($A$1:$A$10000<=D1)*$B$1:$B$10000)\/(COUNTIF($A$1:$A$10000,"<="&D1)-COUNTIF($A$1:$A$10000,"<"&C1))下拉复制E1即可,如果数据超过10000行,根据需要修改上面的公式 ...
...一组数据中各数值的相对位置,它改变了一个数据在该组中的位置,是否...
【错误】标准分数也称作标准化值或Z分数,它是变量值与其平均数的离差除以标准差后的值,用以测定某一个数据在该组数据中的相对位置。其计算公式为:标准分数最大的用途是可以把数据中的均值,标准差考虑在内,可以判定一组数据中不同数据在组中的位置,但不能改变其位置。
excel中怎么查找一个值在表格中的位置
value,lookup_array,match_type),参数lookup_value是需要在数据表(lookup_array)中查找的值;参数lookup_array是可能包含有所要查找数值的连续的单元格区域;参数match_type为1时,查找小于或等于lookup_value的最大数值,lookup_array必须按升序排列,该参数为0时,查找等于lookup_value的第一个数值,...
EXCEL函数 如何在一窜数据中,取中间的某一个数值?如取12345中"3",使 ...
如下所示:1、MID(A1,3,1) ,三个参数中:A1是存放这串数据的单元格。2、3是开始提取的位置,1是要提取字符位数。Microsoft Excel为Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。
Excel实现出现范围内符合要求的数值,在另一个单元格显示对应数据。
Excel可以通过使用条件格式、筛选或者公式来实现当某个单元格的数值在特定范围内时,在另一个单元格显示对应的数据。首先,我们可以使用“IF”函数结合逻辑判断来达成这个目的。例如,假设我们有一列数据在A列,我们想在B列显示A列中数值在5到10之间的对应数据。那么我们可以在B1单元格中输入...
excel中如何把同一名称的数值在另一个表格中累计
2、然后在每月销量单元格下方输入前三个月的销量,如下图所示。3、接着在累计销量单元格中输入公式:==SUM($B$2:B2)(=SUM(每月销量:每月销量),如下图所示。4、然后将鼠标移到累计销量单元格右下方,待鼠标变成”+“号后,双击。5、这时可以看到,从4月份开始输入数据就可自动累计前面几个月...
如何在Excel中输入一个数值在区域内得到相应数量的数据,例如输入10得...
比如你要输入10000个=randbetween(200,600),可以在A1输入randbetween(200,600),然后在名称栏输和A1:A10000,回车,再按CTRL+D就可以了。图中标识的就是名称栏。
愈腾得治: 你对应范围的薪资包没给,如果一个区域对应一个薪资包可以用if函数. =if(A2<1000,2310,if(A2<10100,2640,""))这种函数套进去.函数最多可以套7层受限制 还可以通过函数查找匹配 这样就可以了
息烽县13128157392: Excel如何判断在某个值范围内,取相对应的值 - ?
愈腾得治: 设E列为需要判定列,则公式为 =IF(E1<75000,0,IF(AND(E1>75000,E1<81000),100,IF(AND(E1>81000,E1<87480),200,IF(AND(E1>87480,E1<94478.25),300,IF(AND(E1>94478.25,E1<102036.75),400,IF(AND(E1>102036.75,E1<110199.38),...
息烽县13128157392: Excel 查找某个值所在的范围,并引用对应的数据 - ?
愈腾得治: 摆在面前的是三列数据,要通过第三列的数据来查找左边两列的数据.很显然这个如果只用vlookup()函数是不能实现的.如果可以知道查询值在第三列中的行号,再通过行号来作为索引,找到对应的第一列和第二列当中的值,那我们的目的...
息烽县13128157392: excel如何对A列数值范围内的数据求对应B列数据的平均值 - ?
愈腾得治: 可以用公式: =AVERAGEIFS(B1:B17,A1:A17,">=1",A1:A17,"<=2")
息烽县13128157392: excel中把值在特定范围内的数值求和,怎么办?(举例如下)急急急!答的好再加分! - ?
愈腾得治: 100-300之间:=SUMIF(A2:A10,"<=300",B2:B10)-SUMIF(A2:A10,"<100",B2:B10)300-500之间:=SUMIF(A2:A10,...
息烽县13128157392: excel自动求和问题:在一单元格内输入数据范围,在另一格内自动求其和 - ?
愈腾得治: =SUM(INDIRECT(A31))
息烽县13128157392: 高分求解答,在Excel中怎么求指定数字范围内的和 - ?
愈腾得治: 用sumproduct>=99%的 =sumproduct((G2:G14>=0.99)*F2:F14)98~98.9 =sumproduct((G2:G14>=0.98)*(G2:G14<0.99)*F2:F14) 余类推
息烽县13128157392: 【在线等】怎样用excel求出在某一数值范围内的数字的个数?
愈腾得治: 本可以用frequency函数,但是显得不易理解那就用countif,如数据在A1到A10,统计5-10之间的数字个数:B1输入:=countif(A1:A10,">5")-countif(A1:A10,">10")其他类推祝你成功!
息烽县13128157392: 如何在excel中求出对应范围的数据如x≤30 误差在± 10% - ?
愈腾得治: X<=30 本来就是范围,又怎么设置范围呢? 如果你需要求30 正负10% 就用高级筛选,将一个字段名设置两个条件: 一个条件是>=27 再一个是<=33 筛选就OK了
息烽县13128157392: 已知平均数在excel工作表中求随机数,随机数需要控制在一定范围数值内,求公式? - ?
愈腾得治: 取随机数在ab之间:=rand()+(b-a),ab分别代表两个数据