Excel 2013 - “太多参数”消息到IFAND公式 [英] Excel 2013 - "too many arguments" message to IFAND formula

问题描述

我正在创建一个决定工具，其中是/否回应将触发后续问题或决定。我创建了一个具有13个参数的公式，其中每个IF具有两个AND条件（如果否 - 如果是，则为true）。
我不是专家，试图遵循几个教程和帮助网站的指导。
这是我的公式：

  = IF（（AND（J7 =计划 - M7 =否）），是E出，Schedule LE，
 IF（（AND（J7 =计划 - 离职PM，M7 =否）） （和（J7 =计划 - 客户驱动关闭，M7 =否）， ），时间表OT，CE将工作分配给LE，
 IF（（AND（J7 =计划 - 培训，M7 =否）），Schedule OT，LE需要（$（$（J7 =计划 - 客户活动，M7 =否）），时间表OT，CE将工作分配给LE，
 IF（（AND（J7 =计划外 -  Sick call in，M7 =否）），是E出，调度LE，
 IF（（AND（J7 = $（$）（（AND（J7 =计划外 - 紧急响应/修复，M7 =），M7 =否），Schedule OT否）），请求是否为代码3？，可以由计划资源处理吗？，
 IF（（AND（J7 =Unplanned  -  Customer Ev （（AND（J7 =无计划 - 呼入，M7 =否）），执行转移中的工作 OT（OT），
 IF（（AND（J7 =计划外 - 天气，M7 =否））可以从一次转移中分配资源？ ，可以CE或ACE成为额外的HC，
 IF（（和（J7 =计划外关闭工作，M7 =否）），时刻表OT，LE调度时间（）（）（）（）（）（）（）（）（））））））））））） ）））
  

我做错了什么？请帮助!!!

解决方案

您的if语句的问题是您正在尝试做两件以上的事情。 IF公式有三个部分

• 逻辑测试


• 如果逻辑为真，该怎么办li>
  
• 如果逻辑为假，该怎么办？

在公式布局方面如下所示：

  = IF（逻辑测试，如果为True，执行什么操作）
 
  = IF1（Logical test1，如果True1，IF2（逻辑测试2，如果True2，如果False2怎么办，该怎么办））
  
 
 
检查IF2的唯一方法是如果第一个if的逻辑测试返回false。
 
 
 
现在，如果我们看看你的第一个if语句，忽略关闭支架方式在te结束。我们有：
  IF（（AND（J7 =计划 - 转换/度假覆盖，M7 =否）） ，是E出，Schedule LE，
  
逻辑测试是： p $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ c> 

逻辑测试没有什么技术上的错误，但你不需要在（）语句的周围，消除它们可能会使事情更容易阅读并且保持括号计数，我将刚刚使用：

  AND（J7 =计划 - 转换/度假覆盖 ，M7 =否
  

我刚刚注意到的逻辑测试的另一件事是，每一个他们检查M7是否是否，而不是放置和检查每个if语句总是检查相同的结果，你可以将M7检查方法放在if公式中，然后检查J7的每个后续值或者只是真正的或只是第一个if的假路声明：

  = IF（M7NO，M7为是时该怎么办，
 IF（J7 =计划转移/度假覆盖，..... 
  

你的什么如果真正的部分是：

 是E出吗？
  / pre> 
 
 
如果假的部分是，你该怎么办：
  Schedule LE
  
这两个结果中的任何一个都不会出现。这个问题是在你做什么之后，如果假的部分你再放一个昏迷。 IF公式不知道如何处理任何事情，除了第二个和这个昏迷之后，假结果是该if语句的第三个逗号。
 
 
 
我会推荐你开始构建一个简单的if语句，没有任何嵌套，并让它为一个案例工作。当你有一个这样的工作，在第二种情况下折腾并嵌套IF语句并使其工作。一旦你得到它在第三种情况下工作，并使其工作。希望大概在第3或第4种情况下会出现一种模式。按照模式，直到你的所有案件被覆盖。如果您没有看到模式，那么只需添加一个案例，直到您覆盖所有内容。
 
 
 
您的IF语句面临的一个问题是您需要一种方法来确定逻辑检查是否为真时是否显示语句1或语句2，并假定您的逻辑检查为false时，您想要继续进行下一个IF公式检查。下面我试图通过添加嵌入式IF来检查其他列的值来决定在决策中做什么来猜测if语句的部分过程。我已经提到细胞AA，AB，AC等，使它们更容易发现：
  = IF（M7  - （7=计划班/休假期间，IF（AA7 =，是E出，Schedule LE） ，
 IF（J7 =计划 - 离班PM，IF（AB7 =可以预定的资源在班班内完成工作吗？，CE重新分配WO到新班LE  - 不需要OT ），
 IF（J7 =计划 - 客户驱动关闭，IF（AC7 =，计划表OT，CE将工作分配给LE），
 IF（J7 =计划 - 培训，IF（AD7 =，Schedule OT，LE需要接受OT训练吗），
 IF（J7 =计划 - 客户事件，IF（AE7 = 日程表OT，CE将工作分配给LE），
 IF（J7 =计划外 - 进入电话，IF（AF7 =，是E out？，计划LE） ）））））））
  
除了你的决策树或电子表格外，很难帮助你随着这个过程。再次，我建议从一个基本的IF语句开始，然后从那里建立。 
 
 
 
另一件可能帮助您的事情是在纸上画出决策树。第一个问题是你的第一个如果公式。每个问题只能有两个结果。对或错。所以你画一条真线，在真线的最后，你要么显示文字，要么提出另一个问题。为虚假做同样的事情。如果你提出另一个问题，那就是另外一个IF公式，并且你从这个问题中抽出两行。保持这个过程，直到你没有任何道德问题。 
 
 
 
  
 
 
 
搜索 
 
I am creating a decision tool where yes/no responses will trigger a follow up question or decision.  I created a formula with 13 arguments where each IF has two AND conditions (if no - it is true, if yes - It is false).
I am no expert and tried to follow the guidance from several tutorials and help sites.
This is my formula:
=IF((AND(J7="Planned - Shift / Vacation Coverage", M7="No")), "Is an E out?", "Schedule LE", 
IF((AND(J7="Planned - Off-shift PMs", M7="No")), "Can the scheduled resources complete work within shift?", "CE reassigns WOs to new shift LE - No OT needed", 
IF((AND(J7="Planned - Customer driven shutdown", M7="No")), "Schedules OT", "CE assigns work to LEs", 
IF((AND(J7="Planned - Training", M7="No")), "Schedules OT", "Does LE need to get trained in OT?", 
IF((AND(J7="Planned - Customer event", M7="No")), "Schedules OT", "CE assigns work to LE", 
IF((AND(J7="Unplanned - Sick call in", M7="No")), "Is an E out?", "Schedules LE", 
IF((AND(J7="Unplanned - Escorting", M7="No")), "Schedule OT", "Move scheduled work to next day/week", 
IF((AND(J7="Unplanned - Emergency response/repair", M7="No")), "Is the request a code 3?", "Can this be handled by scheduled resources?", 
IF((AND(J7="Unplanned - Customer Event", M7="No")), "Schedules OT", "Executes work within shift", 
IF((AND(J7="Unplanned - Call in", M7="No")), "OT will not be paid", "Schedules OT", 
IF((AND(J7="Unplanned - Weather", M7="No")), "Can resources be allocated from the shift in the first instance?", "Can CE or ACE become the additional HC?", 
IF((AND(J7="Planned - Off premises work", M7="No")), "Schedules OT", "LE schedules time off premises within shift", 
IF((AND(J7="Administrative work", M7="No")), "Schedules OT", "Executes within shift")))))))))))))
What did I do wrong???  Please HELP!!!
解决方案
The problem with your if statement is you are trying to do more than two things.  An IF formula has three parts


logical test
What to do if logical is true
What to do if logical is false


in terms of a formula layout it looks like this:
=IF(Logical Test, What to do if True, What to do if false)
notice that between the () there are only 3 things each separated by a total of two comas.  Now in order to nest IF formulas, you need to put follow up IF formulas in the what to do if True or the what to do if False.  You can put one in each if you like.  Each subsequent IF formula will have its one what to do if true/false parts.  I will add 1 and 2 to the IF formula for demonstration purposes, it is not proper syntax:
=IF1(Logical test1, What to do if True1, IF2(Logical test2, What to do if True2, What to do if False2))
The only way IF2 is checked, is if the logical test in in the first if returns false.

Now if we look at your first if statement, ignoring the close bracket way at te end. We have:
IF((AND(J7="Planned - Shift / Vacation Coverage", M7="No")), "Is an E out?", "Schedule LE", 
The logical test is:
(AND(J7="Planned - Shift / Vacation Coverage", M7="No")
Nothing technically wrong with the logical test, but you do not need the () around the and statement.  eliminating them may make things easier to read and keep tract of the bracket count.  I would have just used:
AND(J7="Planned - Shift / Vacation Coverage", M7="No"
Another thing I just noted with your logical test, is that every single one of them is checking M7 to see if it is NO.  Rather than placing an and check with each if statement always checking the same result, you could put the M7 check way out front in an if formula, and then check each subsequent value for J7 either just the true or just the false path of the first if statement:
=IF(M7<>"NO","What to do when M7 is yes",
IF(J7="Planned - Shift / Vacation Coverage",.....
Your what to do if true part is:
"Is an E out?"
Your what to do if false part is:
"Schedule LE"
nothing wrong with either of those two results being presented.  The problem is right after your what to do if false part you put another coma.  The IF formula does not know how to deal with anything beyond that the second and this coma after the false result is the 3rd comma for that if statement.

I would recommend you start building a simple if statement without any nesting and get it to work for one case.  When you have this working for one case, toss in your second case and nest the IF statement and get it working.  once you get it working toss in your third case and get it working.  Hopefully by about the 3rd or 4th case a pattern will emerge.  follow the pattern up until you have all your cases covered.  if you do not see the pattern, then just add on one case at a time until you have everything covered.

one of the problems you are facing with your IF statements is you need a way to determine whether statement 1 or statement 2 should be displayed when your logical check is true,  and assume when your logical check is false you want to move on to your next IF formula check.  Below I have attempted to guess at a partial process of your if statements by adding an embedded IF to check the value of some other column to decided what to do in your decision making.  I have referred to cell AA, AB, AC etc to make them easier to spot:
=IF(M7<>"NO","What to do when M7 is yes",
IF(J7="Planned - Shift / Vacation Coverage",IF(AA7="", "Is an E out?", "Schedule LE"),
IF(J7="Planned - Off-shift PMs", IF(AB7="","Can the scheduled resources complete work within shift?", "CE reassigns WOs to new shift LE - No OT needed"),
IF(J7="Planned - Customer driven shutdown",IF(AC7="","Schedules OT", "CE assigns work to LEs"),
IF(J7="Planned - Training", IF(AD7="","Schedules OT", "Does LE need to get trained in OT?"),
IF(J7="Planned - Customer event",IF(AE7="", "Schedules OT", "CE assigns work to LE"),
IF(J7="Unplanned - Sick call in", IF(AF7="","Is an E out?", "Schedules LE"))))))))
With out seing your decision tree or spreadsheet it is difficult to help you out with the process.  Again I would recommend starting off small with a basic IF statement and then building from there.  

Another thing that may help you out is to draw out a decision tree on paper.  The first question is your first if formula.  Each question can only have two results.  True or False.  So you draw a true line and at the end of the true line you either display text, or ask another question.  Do the same for the false line.  If you ask another question, that is another IF formula, and your draw two line from that question.  Keep this process up until you have no mor questions.  



Search for Binarry Decision Tree to learn more about this topic.  The following decision tree example is not binary as the first choice has more than tow possible results.  Similar to what it appears you are trying to do.  As such, the IF statement cannot work with the three or more choices at the top level.  The decision tree would need to be modified to make it work with the IF statement.



                        
这篇关于Excel 2013  - “太多参数”消息到IFAND公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！