Excel嵌套IF构建评估矩阵遇问题，求无VBA替代方案

我要在Excel里制作一款ITIL评估工具，基于工作表内的ITILAssessmentTbl表，将各项标准关联到对应维度（例如标准1关联组织、标准2关联工具等）。标准共分为6个层级，但并非所有维度都覆盖全部层级。我设计了矩阵来展示标准、层级及用户自定义类别，但不希望矩阵中显示空白，因此尝试用嵌套IF+TEXTJOIN公式判断对应层级与类别下是否存在标准，无内容则跳转至下一层级。但该公式仅在A列生效，B列及以后无法正常运行。因宏安全问题无法使用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]], "")),""))))))

解决方案 #

方案1：Excel 365/2021及以上（动态数组支持） #

利用LET、XLOOKUP和FILTER函数简化逻辑，避免重复计算，且横向拖动可适配所有列：

=LET(
    levels, D$5:I$5,
    matchLevel, XLOOKUP(TRUE, BYCOL(levels, LAMBDA(l, COUNTIFS(ITILAssessmentTbl[Key], l, ITILAssessmentTbl[Secondary Dimension], $C7)>0)), levels, ""),
    TEXTJOIN("/", TRUE, FILTER(ITILAssessmentTbl[ITIL Criteria], (ITILAssessmentTbl[Key]=matchLevel)*(ITILAssessmentTbl[Secondary Dimension]=$C7), ""))
)

• 逻辑：先遍历所有层级，找到第一个存在对应维度标准的层级，再筛选该层级下的标准并合并。
• 优势：可读性强，计算效率高，无需数组输入，横向拖动自动适配B列及以后单元格。

方案2：兼容旧版Excel（无动态数组） #

用INDEX+AGGREGATE替代嵌套IF，仅需一次筛选计算，按Ctrl+Shift+Enter作为数组公式输入：

=TEXTJOIN("/",TRUE,IF(ITILAssessmentTbl[Key]=INDEX(D$5:I$5,AGGREGATE(15,6,COLUMN(D$5:I$5)-COLUMN(D$5)+1/(COUNTIFS(ITILAssessmentTbl[Key],D$5:I$5,ITILAssessmentTbl[Secondary Dimension],$C7)>0),1))*(ITILAssessmentTbl[Secondary Dimension]=$C7),ITILAssessmentTbl[ITIL Criteria],""))

• 逻辑：通过AGGREGATE定位第一个符合条件的层级，再用INDEX获取层级Key，最后筛选并合并对应标准。

额外优化建议 #

• 减少重复计算：原公式多次重复调用TEXTJOIN，以上方案仅做一次筛选合并，大幅提升计算性能。
• 规范引用：确保$C7为维度行的相对引用，D$5:I$5为层级标题的绝对列引用，保证横向拖动时公式适配正确。
• 命名范围：可将D$5:I$5定义为命名范围Levels，进一步提升公式可读性。

内容的提问来源于stack exchange，提问作者Lawrence Forster