基于可变条件数组的Excel筛选函数及2×2网格输出优化

需求概述 #

优化现有Excel函数，实现无需手动修改公式即可动态调整筛选条件，优先采用非VBA方案；需基于两列条件（B、C列，值为High/Med/Low）对可动态增减的条目范围分组，最终将4组筛选结果输出为对称2×2网格，每个网格单元格固定为3列的条目块，无条目时保持布局对称，避免手动调整。

一、动态筛选优化 #

原问题 #

原固定条件筛选公式需手动修改条件逻辑，无法适配用户输入的动态条件对（例如Group1对应High,High or High,Med or Med,High）：

=IFERROR(CHOOSECOLS(FILTER('sheet'!$B$2:$AC$34,(('sheet'!$E$2:$E$34="High")*('sheet'!$F$2:$F$34="High"))+(('sheet'!$E$2:$E$34="Med")*('sheet'!$F$2:$F$34="High"))+(('sheet'!$E$2:$E$34="High")*('sheet'!$E$2:$E$34="Med"))),2),"Nothing Found")

优化实现 #

通过解析用户输入的条件对字符串，生成匹配数组实现动态筛选：

1. 解析条件对：将用户输入的条件字符串（如"High,High or High,Med or Med,High"）拆分并整理为条件组合数组：

   =SUBSTITUTE(TEXTSPLIT(条件单元格,," or "),", ","")
   

2. 动态筛选：用XMATCH匹配大表中B、C列的组合值与解析后的条件对，结合FILTER实现动态筛选，同时使用大范围区域自动适配条目增减：

   =FILTER(sheet!$A$2:$A$1000,ISNUMBER(XMATCH(sheet!$B$2:$B$1000&sheet!$C$2:$C$1000,SUBSTITUTE(TEXTSPLIT(条件单元格,," or "),", ",""))),"")
   

二、对称2×2网格输出优化 #

原问题 #

原手动设置的=IFERROR(WRAPCOLS(F4#,6),"")无法自动适配条目增减，且无法维持2×2网格的对称布局，无条目时单元格大小会错乱。

优化实现 #

通过计算所有组的最大行数，生成统一高度的空白填充数组，结合VSTACK/HSTACK构建对称布局：

1. 计算最大行数：统计每个组的条目数，按3列换行规则计算每组所需行数，取最大值作为所有网格的统一高度：

   =MAX(MAP(条件组单元格区域,LAMBDA(m,ROUNDUP(ROWS(FILTER(sheet!$A$2:$A$1000,ISNUMBER(XMATCH(sheet!$B$2:$B$1000&sheet!$C$2:$C$1000,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",""))))/3,0))))
   

2. 构建对称布局：基于最大行数生成空白填充行，将每个组的筛选结果按3列换行后，与空白行拼接保证高度一致，再通过VSTACK/HSTACK组合成2×2网格。

补充：最终完整实现公式 #

已整合上述逻辑，实现无需手动修改的完整公式，同时适配动态条件与对称布局：

=IFERROR( VSTACK( VSTACK( HSTACK( HSTACK("","Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))), HSTACK("Not Urgent",MAKEARRAY(1,2,LAMBDA(x,y,"")))), HSTACK( VSTACK("Important",MAKEARRAY(MAX(MAP(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),LAMBDA(z,MAX(ROUNDUP(ROWS(TEXTSPLIT(z,,","))/3,0)))))-1,1,LAMBDA(x,y,""))), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),1,1),,","),3)),1), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),1,2),,","),3)),1)) ), HSTACK( VSTACK("Not Important",MAKEARRAY(MAX(MAP(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),LAMBDA(z,MAX(ROUNDUP(ROWS(TEXTSPLIT(z,,","))/3,0)))))-1,1,LAMBDA(x,y,""))), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),2,1),,","),3)),1), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),2,2),,","),3)),1) ) ), "")

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