EXCEL公式, 让D7 显示出C15:C35 (可能会出现空值)中不重复的值并用空格分开.

Question

如果C15=10, C16=11, C17:C35为空白,则D7显示为10 11(中间有空格). 如果C15=10, C16=11, C17=15, C18=10, C19:C35为空白, 则D7显示为10 11 15. 也就是D7显示出C15:C35中不重复值并用空格分开, 如果C15:C35有空格也不影响,

Answer 1

如果确定要放在一个单元格里，复制下面的公式
=SUBSTITUTE(IF(COUNTIF(C15:C15,C15)>1,""," "&C15)&IF(COUNTIF(C15:C16,C16)>1,""," "&C16)&IF(COUNTIF(C15:C17,C17)>1,""," "&C17)&IF(COUNTIF(C15:C18,C18)>1,""," "&C18)&IF(COUNTIF(C15:C19,C19)>1,""," "&C19)&IF(COUNTIF(C15:C20,C20)>1,""," "&C20)&IF(COUNTIF(C15:C21,C21)>1,""," "&C21)&IF(COUNTIF(C15:C22,C22)>1,""," "&C22)&IF(COUNTIF(C15:C23,C23)>1,""," "&C23)&IF(COUNTIF(C15:C24,C24)>1,""," "&C24)&IF(COUNTIF(C15:C25,C25)>1,""," "&C25)&IF(COUNTIF(C15:C26,C26)>1,""," "&C26)&IF(COUNTIF(C15:C27,C27)>1,""," "&C27)&IF(COUNTIF(C15:C28,C28)>1,""," "&C28)&IF(COUNTIF(C15:C29,C29)>1,""," "&C29)&IF(COUNTIF(C15:C30,C30)>1,""," "&C30)&IF(COUNTIF(C15:C31,C31)>1,""," "&C31)&IF(COUNTIF(C15:C32,C32)>1,""," "&C32)&IF(COUNTIF(C15:C33,C33)>1,""," "&C33)&IF(COUNTIF(C15:C34,C34)>1,""," "&C34)&IF(COUNTIF(C15:C35,C35)>1,""," "&C35)," ","",1)