在Excel中使用多个嵌套的IF-函数，这是正确的解决方案吗？

Question

我试图用Excel创建一个使用一套标准的评估工具。用户可以通过将每个标准标记为一个维度来定制该工具，例如标准1与组织有关，项目2涉及到工具等。标准有6个级别，但并非所有被标记的维度都有每个级别的标准项。这是在工作表中的表(ITILAssessmentTbl)中指定的。

为了便于理解和使用，我决定使用一个矩阵，列出标准、级别和用户特定类别(例如工具)。然而，在每个类别下，不会有每个级别1-6的标准项目。我不想在矩阵中显示空白(见图1)，因此尝试使用嵌套的IF公式来标识级别和标头下是否存在标准项，如果没有，则移到下一个级别。

下面粘贴的公式对A列有效，但对B列无效。

我想保持在Excel (没有VBA)，因为它有一些安全问题的宏，我无法上传一个空白的工作簿。有更好的方法嵌套IF()-函数吗？

=IF(TEXTJOIN("/", TRUE, IF((($D$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($D$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($E$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($E$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($F$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($F$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($G$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($G$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($H$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($H$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),IF(TEXTJOIN("/", TRUE, IF((($I$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], ""))<>"",TEXTJOIN("/", TRUE, IF((($I$5=ITILAssessmentTbl[[Key]:[Key]])*($C7=ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]])), ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]], "")),""))))))

​

​

​

​

​

​

Answer 1

首先，请用=IF(x * y, ...)替换=IF(AND(x,y), ...)。它不会改变行为，但会增加可读性。

接下来，你做了一些奇怪的事情:你检查一个条件。如果遇到这种情况，就将一些文本粘合在一起，然后检查粘贴的文本是否为空。

为什么？如果出于某种原因，“某些文本”是空的，那么使用文本的长度(比如IF(LEN(ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]])>0, ...) )可以更好地检查“某些文本”，这似乎是有意义的。

Answer 2

您有O365，所以您也可以访问LET函数，这将改变您的Excel寿命。我做的咨询工作通常集中在人类能力的极限上，从复杂性的角度来看，你已经创造了一个公式，它超出了复杂性的范围。让我们先用LET函数来解决这个问题。通过使用LET函数和alt-Enter来创建不中断的返回，这是公式的精确复制：

=LET(
key, ITILAssessmentTbl[[Key]:[Key]],
secDim, ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]],
ITIL, ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]],
tj_D, TEXTJOIN("/", TRUE, IF((($D$5=key)*($C7=secDIM)), ITIL, "")),
tj_E, TEXTJOIN("/", TRUE, IF((($E$5=key)*($C7=secDIM)), ITIL, "")),
tj_F, TEXTJOIN("/", TRUE, IF((($F$5=key)*($C7=secDIM)), ITIL, "")),
tj_G, TEXTJOIN("/", TRUE, IF((($G$5=key)*($C7=secDIM)), ITIL, "")),
tj_H, TEXTJOIN("/", TRUE, IF((($H$5=key)*($C7=secDIM)), ITIL, "")),
tj_I, TEXTJOIN("/", TRUE, IF((($I$5=key)*($C7=secDIM)), ITIL, "")),
IF(tj_D<>"",tj_D,IF(tj_E<>"",tj_E,IF(tj_F<>"",tj_F,
IF(tj_G<>"",tj_G,IF(tj_H<>"",tj_H,IF(tj_I<>"",tj_I,""))))))
)

我们现在已经将嵌套IFs的逻辑与收集数据的复杂性分开了。现在更容易发现：

• 精巧地键入TEXTJOIN函数，因为它们对于一个字符都是相同的；
• 是IF函数嵌套结构中的逻辑错误。

我将使用"pop“来表示”填充“，意思是在下面的伪代码中”不是空白“。所以"Dpop“的意思是"D不是空白”。我们可以看到，逻辑是：

IF Dpop THEN return textjoin D
ELSEIF Epop THEN return textjoin E
ELSEIF Fpop THEN return textjoin F
ELSEIF Gpop THEN return textjoin G
ELSEIF Hpop THEN return textjoin H
ELSEIF Ipop THEN return textjoin I
ELSE return ""
ENDIF

所以现在你可以“平静地审视你的逻辑”，看看它是否是你想要的。如果嵌套的IF()s是正确嵌套的，那么您就知道问题在别处。

有几个想法：

• 不确定您是否希望或需要进行列引用( [[Key]:[Key]]等)。您可能实际上只想要ITILAssessmentTbl[Key]。这适用于您的所有3列references.
• You都可以访问IFS()语句，这与LET()一样，可以更改所有内容。

嵌套IF()s将成为一个函数中的单个IF-ELSEIF-ELSE_IF.ENDIF：IFS(tj_D<>"",tj_D,tj_E<>"",tj_E,tj_F<>"",tj_F,tj_G<>"",tj_G,tj_H<>"",tj_H,tj_I<>"",tj_I,TRUE,"")

情况就会好起来。使用LET和IFS的整个功能如下：

=LET(
key, ITILAssessmentTbl[[Key]:[Key]],
secDim, ITILAssessmentTbl[[Secondary Dimension]:[Secondary Dimension]],
ITIL, ITILAssessmentTbl[[ITIL Criteria]:[ITIL Criteria]],
tj_D, TEXTJOIN("/", TRUE, IF((($D$5=key)*($C7=secDIM)), ITIL, "")),
tj_E, TEXTJOIN("/", TRUE, IF((($E$5=key)*($C7=secDIM)), ITIL, "")),
tj_F, TEXTJOIN("/", TRUE, IF((($F$5=key)*($C7=secDIM)), ITIL, "")),
tj_G, TEXTJOIN("/", TRUE, IF((($G$5=key)*($C7=secDIM)), ITIL, "")),
tj_H, TEXTJOIN("/", TRUE, IF((($H$5=key)*($C7=secDIM)), ITIL, "")),
tj_I, TEXTJOIN("/", TRUE, IF((($I$5=key)*($C7=secDIM)), ITIL, "")),
IFS(
tj_D<>"",tj_D,
tj_E<>"",tj_E,
tj_F<>"",tj_F,
tj_G<>"",tj_G,
tj_H<>"",tj_H,
tj_I<>"",tj_I,
TRUE,"")
)