ifs公式简写

zxc554657357

请大家帮看看能不能简化一下，我觉得我这写的太长了，关于ifs和随机值，

ps:如果H列也可以简化的话，也请帮忙改一下，谢谢各位啦，

part98

=IF(IFNA(MATCH(H2,$A$2:$A$8,),0)<5,INDEX($C$2:$C$11,RANDBETWEEN(1,5)),INDEX($C$2:$C$11,RANDBETWEEN(6,10)))

沈默00

=IF(OR(COUNT(FIND($H2,$A$2:$A$5)),$H2=""),INDEX($C$2:$C$6,RANDBETWEEN(1,ROWS($C$2:$C$6))),IF(COUNT(FIND($H2,$A$6:$A$8)),INDEX($C$7:$C$11,RANDBETWEEN(1,ROWS($C$7:$C$11)))))

jerrike

1. =INDEX($C$2:$C$11,RANDBETWEEN(1,5)+IFS(OR(H2="",COUNTIF($A$2:$A$5,H2)),,COUNTIF($A$6:$A$8,H2),5))

复制代码

zxc554657357

感谢大家帮助

zxc554657357

part98 发表于 2024-8-14 11:27
=IF(IFNA(MATCH(H2,$A$2:$A$8,),0)

感谢，不好意思，您能再帮我看下附件吗，我有个条件写错了，就是如果是空值，只从c2，c3之间选其一，

zxc554657357

jerrike 发表于 2024-8-14 14:31

感谢，不好意思，您能再帮我看下附件吗，我有个条件写错了，就是如果是空值，只从c2，c3之间选其一，

zxc554657357

沈默00 发表于 2024-8-14 14:12
=IF(OR(COUNT(FIND($H2,$A$2:$A$5)),$H2=""),INDEX($C$2:$C$6,RANDBETWEEN(1,ROWS($C$2:$C$6))),IF(COUNT ...

感谢，不好意思，您能再帮我看下附件吗，我有个条件写错了，就是如果是空值，只从c2，c3之间选其一，

jerrike

zxc554657357 发表于 2024-8-14 16:47
感谢，不好意思，您能再帮我看下附件吗，我有个条件写错了，就是如果是空值，只从c2，c3之间选其一，

1. =INDEX($C$2:$C$11,IFS(H2="",RANDBETWEEN(1,2),COUNTIF($A$2:$A$5,H2),RANDBETWEEN(1,5),COUNTIF($A$6:$A$8,H2),RANDBETWEEN(6,10)))

复制代码

我的城市你来过

=LET(a,REGEXP(CONCAT(FILTER($A$2:$A$8,$A$2:$A$8=$H2)),"[例特殊]+"),b,"结果"&RANDBETWEEN(1,5),IFERROR(IF(a="特殊","特殊"&RANDBETWEEN(6,10),b),b))