Excel 使用多个条件公式

Excel 使用多个条件公式

要求 :

获取考虑年龄和性别变量的一项体能评估函数的点值。示例参数如下:

在此处输入图片描述

以下是我目前所写的内容:

=(IF(pushup<=22,"0",IF(AND(pushup>=23,pushup<=49),"1",IF(pushup>=50,"2"))))

*pushup 是输入俯卧撑次数的单元格,性别和年龄应该在哪里输入?

答案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 分,所以您可以忽略低于“一般”最低值的俯卧撑,所以这个公式根本不会查看您的标准表的该列。

其次,可以重塑公式以根据跨越每个阈值给出分数。例如逻辑是:

  1. 如果俯卧撑次数大于或等于23次,则给一分。

  2. 如果俯卧撑次数大于或等于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})))

答案3

1] 从“定义名称”创建 2 个新的范围名称 >>

  1. '>> 名称:Male>> 指:=$B$3:$D$5>> 确定
  2. '>> 名称:Female>> 指:=$B$9:$D$11>> 确定

2] 在“Point”中I2,将数组(Ctrl+Shift+Enter)公式复制下来:

=MATCH(H2,INDEX(IFERROR(0+LEFT(INDIRECT(F2,0),FIND("-",INDIRECT(F2,0)&"-")-1),0),MATCH(G2,{0,30,50})))-1

3]在“状态”中J2,复制公式:

=LOOKUP(I2,B$14:B$16,A$14:A$16)

在此处输入图片描述

相关内容