[开_157]名单增加（已总结）

张文洲

提供一种Office365解决思路，虽然是102字符，但是公式的逻辑清晰，通用性很强，一个公式直接扩展出整个结果区域

1. =LET(X,UNIQUE(B2:B27),XLOOKUP(SEQUENCE(30,7,1,1),SCAN(0,X,LAMBDA(A,B,A+COUNTIF(B2:B27,B)+K1)),X,"",1))

复制代码

Meteor-渣渣

重在参与
先占个楼

补充内容 (2022-4-22 23:47):
=LOOKUP(A2:A31*7+COLUMN(A:G)-8,MMULT(N(ROW(1:33)>COLUMN(A:AE)),(B2:B32<>B1:B31)*K1+1),B2:B3)&""
区域数组，95字符

补充内容 (2022-5-26 22:03):

1. =1+1

复制代码

minchar

L2=LET(x,B2:B31,y,UNIQUE(x),XLOOKUP(SEQUENCE(30,7,,),XMATCH(y,x,,-1)+OFFSET(A2,,,COUNTA(y),)*K1,y,"",1))

梦幻小丑

1. =INDEX(B:B,SMALL(A2:A31+99^(COLUMN(A:Z)>K1*(B2:B31<>B3:B32)+1),ROW()*7-25+COLUMN()))&""

复制代码

一步001

l2=IFERROR(INDEX($B:$B,SMALL(IF(ROUNDUP((IFNA(COLUMN(INDIRECT("a1:"&CHAR(97+$K$1)&"1"))^0-{0,1},0)+COLUMN(INDIRECT("a1:"&CHAR(97+$K$1)&"1"))^0*(MATCH($B$2:$B$31,$B$2:$B$31,)+1=ROW($B$2:$B$31)))/2,0)*ROW($B$2:$B$31),ROUNDUP((IFNA(COLUMN(INDIRECT("a1:"&CHAR(97+$K$1)&"1"))^0-{0,1},0)+COLUMN(INDIRECT("a1:"&CHAR(97+$K$1)&"1"))^0*(MATCH($B$2:$B$31,$B$2:$B$31,)+1=ROW($B$2:$B$31)))/2,0)*ROW($B$2:$B$31),FALSE),(ROW(A1)-1)*7+COLUMN(A1))),"")   数组三键,右拉下拉

qwer401245

1. =LET(X,UNIQUE(OFFSET($B$1,1,,SUM(($B$2:$B$31<>"")*1))),XLOOKUP(COLUMN(A:A)+(ROW(1:1)-1)*7,XLOOKUP(X,$B$2:$B$31,ROW($A$1:$A$30),,,-1)+SEQUENCE(COUNTA(X))*$K$1,X,"",1))

复制代码

cinlo

干脆就一条吧，86字符：

1. =INDEX(B:B,SMALL((COLUMN(A:K)>(B2:B31<>B1:B30)*K1+1)*32+ROW(),ROW()*7+COLUMN()-25))&""

复制代码

三秋之叶

解法一（2013版本）：112字符

1. =INDEX(B:B,SMALL(IF(B2:B298<>B3:B299>=TRANSPOSE(ROW(INDIRECT("1:"&K1+1))>1),ROW(2:298)),COLUMN()+7*ROW()-25))&""

复制代码

解法二（2013版本）：97字符

1. =INDEX(B:B,SMALL(IF(B2:B298=B3:B299<=(OFFSET(A2,,,,K1+1)=A2),ROW(2:298)),COLUMN()+7*ROW()-25))&""

复制代码

解法三（M365版本）：89字符

1. =INDEX(B:B,SMALL(IF(B2:B298<>B3:B299>=(SEQUENCE(,K1+1)>1),ROW(2:298)),SEQUENCE(30,7)))&""

复制代码

tqlld

先占位

1. =IFERROR(INDEX($B:$B,MID(CONCAT(IF(MATCH($B$2:$B$31,$B:$B,)=ROW($2:$31),REPT(TEXT(ROW($2:$31),"00"),$K$1+1),TEXT(ROW($2:$31),"00"))),COLUMN(A1)*2+$A2*14-15,2)),"")

复制代码

补充内容 (2022-5-6 16:42):
=IFERROR(INDEX($B:$B,MID(CONCAT(REPT(TEXT(ROW($2:$31),"00"),(MATCH($B$2:$B$31,$B:$B,)=ROW($2:$31))*$K$1+1)),COLUMN(A1)*2+$A2*14-15,2)),"")

博爱风雨同舟

=IFERROR(INDEX(FILTERXML("<q><w>"&LET(X,B2:B31,SUBSTITUTE(TRIM(CONCAT(REPT(X&"/",(COUNTIF(X,X)+K1)*(MATCH(X,X,0)=ROW(1:30))))),"/","</w><w>"))&"</w></q>","//w"),SEQUENCE(30,7)),"")