答案1
我确信这会对你有帮助:
怎么运行的:
创建一个如上所示的表,并在列中输入此公式 类别。
=IF(AND([@Gender]="M",[@Age]<30),"M30",IF(AND([@Gender]="M",[@Age]>=23,[@Age]<=49),"M49",IF(AND([@Gender]="M",[@Age]>=50),"M50",IF(AND([@Gender]="F",[@Age]<30),"F30",IF(AND([@Gender]="F",[@Age]>=23,[@Age]<=49),"F49",IF(AND([@Gender]="F",[@Age]>=50),"F50",""))))))
怎么运行的:
在单元格 S36 中输入此数组 (CSE) 公式,最后用 Ctrl+Shift+Enter并填充,然后添加新列积分&地位。
{=IFERROR(INDEX($S$27:$W$33,SMALL(IF((INDEX($S$27:$W$33,,2)="M"),MATCH(ROW($S$27:$W$33),ROW($S$27:$W$33)),""),ROWS(S36:$S$36)),COLUMNS($A$1:A1)),"")}
单元格 X36 中的公式:
=(IF(AND($W36="M30",$V36<=22),0,IF(AND($W36="M30",$V36>=23,$V36<=49),1,IF(AND($W36="M30",$V36>=50),2,(IF(AND($W36="M49",$V36<=17),0,IF(AND($W36="M49",$V36>=18,$V36<=40),1,IF(AND($W36="M49",$V36>=27),2,(IF(AND($W36="M50",$V36<=9),0,IF(AND($W36="M50",$V36>=9,$V36<=26),1,IF(AND($W36="M50",$V36>=27),2,""))))))))))))
单元格 Y36 中的最终公式:
=IF($X36=0,"Poor",IF($X36=1,"Average",IF($X36=2,"Good","No data")))
注意:
- 为了女性数据使用上面显示的数组(CSE)公式,并记得将“M”替换为“F”。
- 然后使用上面显示的公式计算积分和状态。
- 根据需要调整公式中的单元格引用。
答案2
对于 Excel 2010,您可以对男性使用以下公式:
=(G2>=VALUE(LEFT(INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1),FIND("-",INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1))-1)))+(G2>=INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),2))
您的标准表放在 A1 中,您的俯卧撑放在 G2 中,您的年龄放在 F2 中。
以下公式适用于女性:
=(G9>=VALUE(LEFT(INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),1),FIND("-",INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),1))-1)))+(G9>=INDEX($C$9:$D$11,MATCH(F9,$A$9:$A$11,0),2))
您的条件表放在 A1 中,您的俯卧撑放在 G9 中,您的年龄放在 F9 中。如下所示:
首先,因为您为较差、一般和良好给出了 0、1 和 2 分,所以您可以忽略低于“一般”最低值的俯卧撑,所以这个公式根本不会查看您的标准表的该列。
其次,可以重塑公式以根据跨越每个阈值给出分数。例如逻辑是:
如果俯卧撑次数大于或等于23次,则给一分。
如果俯卧撑次数大于或等于50次,则再加一分。
这使您可以忽略“平均值”的上限,因此公式只需使用以下命令解析出“平均值”的下限:
VALUE(LEFT(INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1),FIND("-",INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1))-1))
选择INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),1)
要读取条件表中的哪一行F2作为年龄类别,通过搜索A 栏对于 F2,然后告诉 INDEX 查看该行是否符合以下条件C3:D5。然后它读取左侧直到“-”,然后使用 VALUE 将其转换为数字。
因为Good阈值不是文本,所以你不需要所有这些操作,因此你可以做一个简单的:+(G2>=INDEX($C$3:$D$5,MATCH(F2,$A$3:$A$5,0),2))
添加第二点。
最后,除非你必须坚持你的标准表设计,否则你可以使用以下命令使其变得更容易:
然后你的公式就变成这样:
=SUM(--(H2>=INDEX($C$2:$D$7,MATCH(F2&G2,$A$2:$A$7&$B$2:$B$7,0),{1,2})))