☰

`IF` 函数的“参数过多”

tag-icon • tag-icon microsoft-excel
`IF` 函数的“参数过多”

我想基于 3 个班次 00、08、16 使用 excel 自动分配 3 个函数 A、F、D 到每个班次，并基于上一个自动分配的班次使用函数来避免再次重复该函数，但在下面的粗体参数之后，它显示参数太多。有人知道其他公式可以解决吗？谢谢！

=如果(Sheet1!C2="00",如果(B36="00D",INDEX({"00A","00F"},RANDBETWEEN(1,2)),如果(B36="00F",INDEX({"00A","00D"},RANDBETWEEN(1,2)),"00A")),如果(Sheet1!C2="08",如果(B36="08D",INDEX({"08A","08F"},RANDBETWEEN(1,2)),如果(B36="08F",INDEX({"08A","08D"},RANDBETWEEM(1,2)),"08A"))),如果 (Sheet1!C2="16",如果 (B36="16D",INDEX({"16A","16F"},RANDBETWEEN(1,2)),如果 (B36="16F",INDEX({"16A","16D"},RANDBETWEEM(1,2)),"16A"))))

答案1

我喜欢像这样深入地展开：

=IF(Sheet1!C2="00",
 IF(B36="00D",INDEX({"00A","00F"},RANDBETWEEN(1,2)),
 IF(B36="00F", INDEX({"00A","00D"}, RANDBETWEEN(1,2)),"00A")),
 IF(Sheet1!C2="08",
IF(B36="08D",INDEX({"08A","08F"},RANDBETWEEN(1,2)),
IF(B36="08F",INDEX({"08A","08D"},RANDBETWEEN(1,2)),"08A")),
IF(Sheet1!C2="16",
IF(B36="16D",INDEX({"16A","16F"},RANDBETWEEN(1,2)),
IF(B36="16F",INDEX({"16A","16D"},RANDBETWEEN(1,2)),"16A")),
"")))

这样做很容易看到失误)后"08A"))

但我们可以稍微简化一下这个等式：

=IF(LEFT(B36,2)=C2,C2&IF(OR(RIGHT(B36)={"D","F"}),CHOOSE(RANDBETWEEN(1,2),"A",IF(RIGHT(B36)="D","F","D")),"A"),C2&"A")

在此处输入图片描述

答案2

但我注意到你的代码无法得到B36=00A的情况，C36有机会显示00A

=IF(LEFT(B36,2)=C2,C2&IF(OR(RIGHT(B36)={"D","F"}),CHOOSE(RANDBETWEEN(1,2),"A",IF(RIGHT(B36)="D","F","D")),"A"),C2&"A")

相关内容