空值不参与合并，求助

学了忘忘了学

我想将红框的数据处理成左边那样，但是用函数（=$J$1&J2&"+"&$K$1&K2&"+"&$L$1&L2）合并后会有许多空格（即产生右边那样的结果）。例如1500这列，奖金和任务是0，我需要的结果是基础1500即可，而不是基础1500+奖金+任务

我这是2007版本，用不了TEXTJOIN函数哦，谢谢~

我的城市你来过

=TEXTJOIN("+",,IF(J2:L2<>"",$J$1:$L$1,"")&J2:L2)

ww87725244

=$J$1&$J2&IF(K2="","","+"&$K$1&K2)&IF($L2="","","+"&$L$1&L2)

13570449347

=LET(jPart, IF(J2<>"", J1&J2&"+", ""),kPart, IF(K2<>"", K1&K2&"+", ""),lPart, IF(L2<>"", L1&L2, ""),result, CONCATENATE(jPart, kPart, lPart),IF(RIGHT(result)="+", LEFT(result, LEN(result)-1), result))

如果你的excel连LET都不支持的话就用下面这个吧，不过建议之后可以升级版本了

=MID(CONCATENATE(IF(J2<>"", $J$1&J2&"+", ""),IF(K2<>"", $K$1&K2&"+", ""),IF(L2<>"", $L$1&L2, "")),1,LEN(CONCATENATE(IF(J2<>"", $J$1&J2&"+", ""),IF(K2<>"", $K$1&K2&"+", ""),IF(L2<>"", $L$1&L2, ""))) - IF(RIGHT(CONCATENATE(IF(J2<>"", $J$1&J2&"+", ""),IF(K2<>"", $K$1&K2&"+", ""),IF(L2<>"", $L$1&L2, ""))) = "+", 1, 0))

hcm19522

=SUMIFS($D$2:$D$18,$B$2:$B$18,J$1,$C$2:$C$18,$C2)

秃头发师

=IF(J2<>"",$J$1&J2,"")&IF(K2<>"",IF(J2<>"","+","")&$K$1&K2,"")&IF(L2<>"",IF(AND(J2="",K2=""),"","+")&$L$1&L2,"")

象山海鲜

1. =REPLACE(TEXT(J2,"+"&J$1&0)&TEXT(K2,"+"&K$1&0)&TEXT(L2,"+"&L$1&L2),1,1,)

复制代码

jisijie630

=IF(MATCH("*"&MID(A2,3,9),A:A,)=ROW(),IF(J2="","",J$1)&J2&IF(K2="","",K$1)&K2&IF(L2="","",L$1)&L2,"")