合并相同文本

JLxiangwei

这个题用技巧来做很简单，大家用函数来试试
不能定义名称，不用辅助单元格
用公式返回内存数组
当然能容错最好

chenfannihao

学习学习

QQ9947

来个长长的：
=IF(ROW(1:15)>=SUM(N(A1:A16<>A2:A17)),"",MMULT(SMALL(IF(A1:A16<>A2:A17,ROW(2:17),18),ROW(1:15)+{0,1}),{-1;1})&T(OFFSET(A1,SMALL(IF(A1:A15<>A2:A16,ROW(2:16),17),ROW(1:15)),)))

阿童木

1. =FREQUENCY(ROW(A$2:A$16),IF(A$2:A$16<>A$3:A$17,ROW(A$2:A$16)))&T(OFFSET(A1,SMALL(IF(A$2:A$16<>$A$3:$A$17,ROW($A$2:$A$16)),ROW(A$1:A$16))-1,))

复制代码

蝶·舞

=TEXT(MMULT(SMALL(IF(A2:A17<>A1:A16,ROW(1:16),99),ROW(1:15)+{0,1}),{-1;1})&T(OFFSET(A1,SMALL(IF(A2:A17<>A1:A16,ROW(1:16),99),ROW(1:15)),)),)

wuxingai

=FREQUENCY(ROW(2:16),IF(A2:A15<>A3:A16,ROW(2:15)))&T(OFFSET(A1,SMALL(IF(A2:A16<>A1:A15,ROW(1:15)),ROW(INDIRECT("1:"&COUNT(IF(A2:A16<>A1:A15,ROW(2:16)))))),))
好长,我缩不短了

chenhh803

=LOOKUP(SMALL(IF(A2:A16<>A3:A17,ROW(2:16),17),ROW(1:15)),ROW(2:17),TEXT(FREQUENCY(ROW(2:16),(A2:A16<>A3:A17)*ROW(2:16)),"0;;")&A2:A17)
头版再简化的
=LOOKUP(SMALL(IF(A2:A16<>A3:A17,ROW(2:16),17),ROW(1:15)),ROW(2:17),TEXT(FREQUENCY(ROW(2:16),(A2:A16<>A3:A17)*ROW(2:16))&A2:A17,))

2009fulei

5555555555555

bluexuemei

1. =LOOKUP(ROW(INDIRECT("1:"&COUNT(IF(A2:A16<>A3:A17,ROW(A2:A16))))),ROW(INDIRECT("1:"&COUNT(IF(A2:A16<>A3:A17,ROW(A2:A16))))),FREQUENCY(ROW(A2:A16),IF(A2:A16<>A3:A17,ROW(A2:A16))))&T(INDIRECT("A"&SMALL(IF(A2:A16<>A3:A17,ROW(A2:A16)),ROW(INDIRECT("1:"&COUNT(IF(A2:A16<>A3:A17,ROW(A2:A16))))))))

复制代码

ljh_xh

本人纯属学习一下！