Question

excel多个单元格去重复、去零值后合并到一个单元格，并用逗号隔开？

如：有10个单元格a5:a14。其中a6=4，a8=5，a10=6，a11=5，a12=4，a14=8；a5、a7、a9、a13都是空格。a15输入函数公式：a15=CONCATENATE(A5,IF(OR(A6="",COUNTIF(A5:A5,A6)),"",IF(A5="","",",")&A6),IF(OR(A7="",COUNTIF(A5:A6,A7)),"",IF(A6="","",",")&A7),IF(OR(A8="",COUNTIF(A5:A7,A8)),"",IF(A7="","",",")&A8),IF(OR(A9="",COUNTIF(A5:A8,A9)),"",IF(A8="","",",")&A9),IF(OR(A10="",COUNTIF(A5:A9,A10)),"",IF(A9="","",",")&A10),IF(OR(A11="",COUNTIF(A5:A10,A11)),"",IF(A10="","",",")&A11),IF(OR(A12="",COUNTIF(A5:A11,A12)),"",IF(A11="","",",")&A12),IF(OR(A13="",COUNTIF(A5:A12,A13)),"",IF(A12="","",",")&A13),IF(OR(A14="",COUNTIF(A5:A13,A14)),"",IF(A13="","",",")&A14)) 结果为4568 我想要的结果是4，5，6，8 请问怎么修改公式？补充：a5:a14可能都是空格，可能都有数据，哪个单元格是空格或数据都不确定。

Answer 1

在a15输入下面的公式试试。
=CONCATENATE(A5,IF(OR(A6="",COUNTIF(A5:A5,A6)),"",IF(A5="","",",")&"，
"&A6),IF(OR(A7="",COUNTIF(A5:A6,A7)),"",IF(A6="","",",")&"，
"&A7),IF(OR(A8="",COUNTIF(A5:A7,A8)),"",IF(A7="","",",")&"，
"&A8),IF(OR(A9="",COUNTIF(A5:A8,A9)),"",IF(A8="","",",")&"，
"&A9),IF(OR(A10="",COUNTIF(A5:A9,A10)),"",IF(A9="","",",")&"，
"&A10),IF(OR(A11="",COUNTIF(A5:A10,A11)),"",IF(A10="","",",")&"，
"&A11),IF(OR(A12="",COUNTIF(A5:A11,A12)),"",IF(A11="","",",")&"，
"&A12),IF(OR(A13="",COUNTIF(A5:A12,A13)),"",IF(A12="","",",")&"，
"&A13),IF(OR(A14="",COUNTIF(A5:A13,A14)),"",IF(A13="","",",")&"，
"&A14))

追问

谢谢！结果为：，4，5，6，8 第一个逗号怎么去掉？

追答

从新改了下。你试试：
=CONCATENATE(A5,IF(OR(A6="",COUNTIF(A5:A5,A6)),"",IF(COUNTA(A5:A5),"，","")&A6),IF(OR(A7="",COUNTIF(A5:A6,A7)),"",IF(COUNTA(A5:A6),"，","")&A7),IF(OR(A8="",COUNTIF(A5:A7,A8)),"",IF(COUNTA(A5:A7),"，","")&A8),IF(OR(A9="",COUNTIF(A5:A8,A9)),"",IF(COUNTA(A5:A8),"，","")&A9),IF(OR(A10="",COUNTIF(A5:A9,A10)),"",IF(COUNTA(A5:A9),"，","")&A10),IF(OR(A11="",COUNTIF(A5:A10,A11)),"",IF(COUNTA(A5:A10),"，","")&A11),IF(OR(A12="",COUNTIF(A5:A11,A12)),"",IF(COUNTA(A5:A11),"，","")&A12),IF(OR(A13="",COUNTIF(A5:A12,A13)),"",IF(COUNTA(A5:A12),"，","")&A13),IF(OR(A14="",COUNTIF(A5:A13,A14)),"",IF(COUNTA(A5:A13),"，","")&A14))

Answer 2

要逗号间隔，比较麻烦，先写个得到4568的公式吧

=SUBSTITUTE(SUMPRODUCT((COUNTIF(A5:A14,ROW($1:$9))>0)*(ROW($1:$9)*10^(10-ROW($1:$9)))),0,"")

有逗号的公式来了
=TEXT(--SUBSTITUTE(SUMPRODUCT((COUNTIF(A5:A14,ROW($1:$9))>0)*(ROW($1:$9)*10^(10-ROW($1:$9)))),0,""),REPT("0!，",LEN(SUBSTITUTE(SUMPRODUCT((COUNTIF(A5:A14,ROW($1:$9))>0)*(ROW($1:$9)*10^(10-ROW($1:$9)))),0,""))-1)&0)

追问

多谢了，公式可用，间隔符用其他符号也行，能改一下公式吗？

追答

把公式中的"0!，"中的逗号改成其他间隔符就行了----但不要能用0、#等特殊符号

追问

请问：为什么输入大于或等于10的数据公式就不好用了呢？

追答

因为该公式仅针对一位数设计

追问

哦，那能否帮忙弄一个2位数以下的公式吗？先谢谢啦！

追答

不可以。没空
凉着去