如何处理超过100个IF语句 [英] How to cope with more than 100 IF statements

问题描述

我使用MS Excel 2007中的复选框数组创建一个清单。基于复选框的数量，有2 ^ 7（= 128）种可能的组合，可以选择复选框。每个选择的复选框的组合给出了独特的结果。

对于每个条件成功测试，并在工作表中的单元格中给出结果，最有效的方法是什么？

示例代码：

  = if（and（chkbox1 = true，chkbox2 = true，chkbox3 = false，chkbox4 = false，chkbox5 = true，chkbox6 = false，chkbox7 = true），Unique outcome，if（and（chkbox8 = true，chkbox9 ............. ）））））））））
  

我尝试的方法是使用IF语句。我试图绕过嵌套的限制，通过创建14个命名范围，每个大约有9个嵌套的IF，并在电子表格中有效的嵌套IF中编译14个命名范围。不幸的是，处理器正在努力计算这个结果（即计算机是冻结）。

• 将减少嵌套IF在命名范围内（但增加电子表格中嵌套IF的数量）会对效率产生影响吗？


• 最好在单元格中使用嵌套IF，而不是在命名范围内使用？ li>
  
• VLOOKUP会工作吗？

目前，我正在考虑使用VBA代码，这将是最有效的方法来处理许多条件。

解决方案

字符串 1 或 0 ，然后为每个可能的二进制值创建一个查找表，并为结果（或行而不是列）创建另一个列。 p>

I am creating an checklist using an array of checkboxes in MS Excel 2007. Based on the number of checkboxes, there are 2^7 (=128) possible combinations in which the checkboxes can be selected. Each combination of checkboxes selected gives a unique outcome.

What would be the most efficient way to successfully test for each condition, and give a result in a cell in a worksheet?

Example code:

=if(and(chkbox1=true,chkbox2=true,chkbox3=false,chkbox4=false,chkbox5=true,chkbox6=false,chkbox7=true),"Unique outcome",if(and(chkbox8=true,chkbox9.............)))))))))

The method I have (unsuccessfully) attempted is to use IF statements. I have attempted to bypass the nested IF limit by creating 14 named ranges with approximately nine nested IFs each, and compile the 14 named ranges in a valid nested IF within the spreadsheet. Unfortunately, the processor is struggling to calculate the outcome of this (i.e. computer is "freezing").

• Would reducing the number of nested IFs in the named ranges (but increasing the number of nested IFs in the spreadsheet) have an influence on efficiency?
• Would it be better to use nested IFs in cells rather than named ranges?
• Would VLOOKUP work?

At the moment, I am contemplating using VBA code as people have said that it would be the most efficient way to approach numerous conditions. Does anyone have any suggestions or recommendations?

解决方案

String together a 1 or 0 for each TRUE or FALSE result then create a lookup table with a column for each possible binary value and another column for the outcomes (or rows rather than columns).

这篇关于如何处理超过100个IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！