Excel公式遇'-0XX'格式内容失效的修复问询

问题概述 #

基于Stack Overflow帖子构建的Excel公式原本运行正常，但遇到-0XX格式的内容时，对应单元格无法显示内容。原表格输出列内容格式分为三类：「day - hour」、「day YYY-XXX」（X、Y可为字母或数字）或空值，要求在不修改原始输出列的前提下解决该问题。

原公式 #

=LET(
     _data, B3:L16,
     _removenulls, FILTER(_data,TAKE(_data,,1)<>""),
     _condition, INDEX(_removenulls,,9),
     _output, TEXTAFTER(TAKE(_removenulls,,-1)," ",,,,,TAKE(_removenulls,,-1)),
     _day, TEXTAFTER(SORT(VLOOKUP(TAKE(_removenulls,,1),{"Ma","1.Ma";"Di","2.D1";"Wo","3.Wo";"Do","4.Do";"Vr","5.Vr"},2,0)),"."),
     _unicon, UNIQUE(TOCOL(_condition,1)),
     _uniday, {"Ma"\"Di"\"Wo"\"Do"\"Vr"\"Za"},
     _rows, ROWS(_unicon),
     _cols, COLUMNS(_uniday),
     _databody, MAKEARRAY(_rows,_cols,LAMBDA(r,c,INDEX(TEXTJOIN(CHAR(10),1,FILTER(_output,(INDEX(_uniday,c)=_day)*(INDEX(_unicon,r)=_condition),"")),1))),
     _Calc,VSTACK(HSTACK("",_uniday),HSTACK(_unicon,IFERROR(IF(--LEFT(TEXTAFTER(_databody,"-")),"Change "&CHAR(10)&_databody,""),_databody))),
     _Calc)

问题根源 #

问题出在公式的_Calc部分：

IFERROR(IF(--LEFT(TEXTAFTER(_databody,"-")),"Change "&CHAR(10)&_databody,""),_databody)

当内容为631-0XP时，TEXTAFTER(_databody,"-")返回0XP，LEFT(...)取第一个字符0，--"0"会将其转换为数字0，而Excel中IF判断里0被视为逻辑假，因此返回空值，导致单元格无输出。而518-321这类内容，LEFT(...)取到5，--"5"得到5（逻辑真），所以正常显示"Change "前缀。

解决方案 #

修改判断逻辑，不再依赖数字转换，而是直接判断内容中是否包含-字符（因为只有「day YYY-XXX」格式含-，「day - hour」格式不含），以此来决定是否添加"Change "前缀。

修改后的_Calc部分：

_Calc,VSTACK(HSTACK("",_uniday),HSTACK(_unicon,IF(ISNUMBER(SEARCH("-",_databody)),"Change "&CHAR(10)&_databody,_databody))),

完整修改后公式 #

=LET(
     _data, B3:L16,
     _removenulls, FILTER(_data,TAKE(_data,,1)<>""),
     _condition, INDEX(_removenulls,,9),
     _output, TEXTAFTER(TAKE(_removenulls,,-1)," ",,,,,TAKE(_removenulls,,-1)),
     _day, TEXTAFTER(SORT(VLOOKUP(TAKE(_removenulls,,1),{"Ma","1.Ma";"Di","2.D1";"Wo","3.Wo";"Do","4.Do";"Vr","5.Vr"},2,0)),"."),
     _unicon, UNIQUE(TOCOL(_condition,1)),
     _uniday, {"Ma"\"Di"\"Wo"\"Do"\"Vr"\"Za"},
     _rows, ROWS(_unicon),
     _cols, COLUMNS(_uniday),
     _databody, MAKEARRAY(_rows,_cols,LAMBDA(r,c,INDEX(TEXTJOIN(CHAR(10),1,FILTER(_output,(INDEX(_uniday,c)=_day)*(INDEX(_unicon,r)=_condition),"")),1))),
     _Calc,VSTACK(HSTACK("",_uniday),HSTACK(_unicon,IF(ISNUMBER(SEARCH("-",_databody)),"Change "&CHAR(10)&_databody,_databody))),
     _Calc)

测试验证 #

初始数据 #

修改后实际结果 #

该结果与期望结果完全一致，成功解决了-0XX格式内容无法显示的问题，同时保留了"Change"标识，且无需修改原始输出列。

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