Excel中SUBSTITUTE+TEXTJOIN组合公式的and替换失效问题咨询

问题根源 #

你的IF公式返回的单引号单元格（看似空值）会被COUNTA统计为非空单元格，导致公式里MAX(1,COUNTA(区域)-1)计算出的替换位置远大于实际有效内容的逗号数量，自然替换不到最后一个逗号。

两种解决办法 #

办法1：修正统计有效内容的公式 #

把原公式里的COUNTA换成COUNTIF，精准统计有活动名称的单元格数量（排除单引号单元格）：

=SUBSTITUTE(TEXTJOIN(", ",TRUE,你的16个目标单元格区域),", "," and ",MAX(1,COUNTIF(你的16个目标单元格区域,"<>''")-1))

原理：COUNTIF(区域,"<>''")会只统计内容不是单引号的单元格，得到真实的有效活动数量，这样替换位置就会对应到最后一个逗号。

办法2：优化IF公式，直接返回空值 #

把原来返回单引号的IF公式改成返回空文本，这样TEXTJOIN的TRUE参数会自动忽略这些空单元格，COUNTA也能正确统计有效内容数量：
原IF公式（示例）：

=IF(条件单元格=1,活动名称单元格,"'")

改成：

=IF(条件单元格=1,活动名称单元格,"")

之后用你原来的公式就能正常生效：

=SUBSTITUTE(TEXTJOIN(", ",TRUE,你的16个目标单元格区域),", "," and ",MAX(1,COUNTA(你的16个目标单元格区域)-1))

两种办法都能解决问题，推荐办法2，更简洁，后续维护也方便。

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