excel多个单元格去重复、去零值后合并到一个单元格,并用逗号隔开?
如:有10个单元格a5:a14。其中a6=4,a8=5,a10=6,a11=5,a12=4,a14=8;a5、a7、a9、a13都是空格。a15输入函数公式:a15=CO...
如:有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可能都是空格,可能都有数据,哪个单元格是空格或数据都不确定。
展开
2个回答
展开全部
在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))
=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))
展开全部
要逗号间隔,比较麻烦,先写个得到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)
=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、#等特殊符号
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询
广告 您可能关注的内容 |