☰

合并单独起作用但不能一起使用的 Excel 公式

tag-icon • tag-icon microsoft-excel worksheet-function
合并单独起作用但不能一起使用的 Excel 公式

我在 Excel 中遇到了很多麻烦。我创建了 2 个单独的公式，它们可以单独使用，但却不知道如何将它们组合起来。单独的公式如下：

IF($N$19=" "," ",IF(AND($N$19>=(--"5:00 AM"),$N$19<(--"5:30 AM"),$O$8="Optimized"),"Meal 4", IF($O$8="Traditional"," ", IF(AND($O$8="Optimized",C11="Meal 4"),"Meal 3",IF(AND($O$8="Optimized",C11="Meal 3"),"Meal 2", IF(AND($O$8="Optimized",C11="Meal 2"),"Meal 1",

和

IF(NOT(OR($O$8="Traditional",$O$8="Optimized",$O$8="5/2")), IF(AND($T$6=5, $U$6="AM"), "Meal 1", IF(AND($T$8=5, $U$8="AM"),"Meal 2", IF(AND($T$10=5, $U$10="AM"), "Meal 3", IF(AND($T$12=5, $U$12="AM"), "Meal 4", " ")))))

当我将它们放在一起如下所示时，我收到 #VALUE 错误：

=IF($N$19=" "," ",IF(AND($N$19>=(--"5:00 AM"),$N$19<(--"5:30 AM"),$O$8="Optimized"),"Meal 4", IF($O$8="Traditional"," ", IF(AND($O$8="Optimized",C11="Meal 4"),"Meal 3",IF(AND($O$8="Optimized",C11="Meal 3"),"Meal 2", IF(AND($O$8="Optimized",C11="Meal 2"),"Meal 1", IF(NOT(OR($O$8="Traditional",$O$8="Optimized",$O$8="5/2")), IF(AND($T$6=5, $U$6="AM"), "Meal 1", IF(AND($T$8=5, $U$8="AM"),"Meal 2", IF(AND($T$10=5, $U$10="AM"), "Meal 3", IF(AND($T$12=5, $U$12="AM"), "Meal 4", " ")))))))))))

在我看来，如果公式第一部分的条件不满足，那么公式的第二部分应该会生效，并且像它自己一样发挥作用。但事实并非如此，我认为这些条件以某种方式互相干扰，但我是新手，而且很笨，所以我搞不懂。

如有任何意见我将不胜感激！

答案1

嵌套的if逻辑表明该**MISS**位置可能缺少了某些东西。

=IF($N$19=" "," ",
   IF(AND($N$19>=(--"5:00 AM"),$N$19<(--"5:30 AM"),$O$8="Optimized"),"Meal 4", 
     IF($O$8="Traditional"," ", 
       IF(AND($O$8="Optimized",C11="Meal 4"),"Meal 3",
         IF(AND($O$8="Optimized",C11="Meal 3"),"Meal 2", 
           IF(AND($O$8="Optimized",C11="Meal 2"),"Meal 1", 
             IF(NOT(OR($O$8="Traditional",$O$8="Optimized",$O$8="5/2")), **MISS**,
               IF(AND($T$6=5, $U$6="AM"), "Meal 1", 
                 IF(AND($T$8=5, $U$8="AM"),"Meal 2", 
                   IF(AND($T$10=5, $U$10="AM"), "Meal 3", 
                     IF(AND($T$12=5, $U$12="AM"), "Meal 4", " ")
                   )
                 )
               )
             )
           )
         )
       )
     )
   )
 )

您能否确认这是预期的逻辑？

相关内容