[第83期]舞林大会之交换舞伴

bluexuemei

方法一:F3:F14区域数组右拉至G3:G14,144字符,

1. =T(INDIRECT("A"&MOD(SMALL(IF($B2:$B25=F2,MATCH($C2:$C25,IF($B2:$B25=F2,$C2:$C25,COUNTA(A2:A25)/2+1-$C2:$C25),)/1%+ROW(2:25),5^7),ROW()-2),100)))

复制代码

方法二:F3数组公式右拉下拉,160字符,

1. =INDIRECT("A"&MOD(SMALL(IF($B$2:$B$25=F$2,MATCH($C$2:$C$25,IF($B$2:$B$25=F$2,$C$2:$C$25,COUNTA(A$2:A$25)/2+1-$C$2:$C$25),)/1%+ROW($2:$25),5^7),ROW(A1)),100))&""
   

复制代码

一嘟噜钥匙

181字符

1. =INDEX(A2:A25,RIGHT(SMALL(MATCH(IF(B2:B25=F2,MAX(C2:C25)+1-C2:C25,C2:C25),IF(B2:B25=F2,MAX(C2:C25)+1-C2:C25,C2:C25),)*10^6+IF(B2:B25=G2,10^4)+ROW(1:24),(ROW(1:12)-1)*2+{1,2}),2))&""

复制代码

　多单元格数组

减了几个字符，176字符

1. =INDEX(A2:A25,RIGHT(SMALL(MATCH(IF(B2:B25=F2,MAX(C2:C25)+1-C2:C25,C2:C25),IF(B2:B25=F2,MAX(C2:C25)+1-C2:C25,C2:C25),)/1%%+(B2:B25=G2)/1%+ROW(1:24),(ROW(1:12)-1)*2+{1,2}),2))&""

复制代码

pssrgss123456

重发一个，单元格公式一样，右拉下拉，185字，但顺序不完全相同，可以吗？




你一个人占三个楼层,大家都象你一样不乱套了?!

ff8feng

公式为F3:G14多单元格数组公式，一次性输入
=T(OFFSET(A1,RIGHT(SMALL(MATCH(ABS(C2:C25-(B2:B25=G2)*MAX(C2:C25+1)),ABS(C2:C25-(B2:B25=G2)*MAX(C2:C25+1)),)/1%%+(B2:B25=G2)/1%+ROW(1:24),2*ROW(1:12)-1+{0,1}),2),))

tslx8129

=INDEX(A2:A25,MID(INDEX(MID(TEXT(ROW(1:24),"00")&TEXT(MATCH(IF(IF(B2:B25=F2,G2,F2)&COUNTA(A2:A25)/2+1-C2:C25=H2&COUNTA(A2:A25)/2+1,"",IF(B2:B25=F2,G2,F2)&COUNTA(A2:A25)/2+1-C2:C25),B2:B25&C2:C25,),"00")&TEXT(ROW(1:24),"00"),1+2*(B2:B25=G2),4),SMALL(((ROW(1:24)<MATCH(MID(F2&G2&"",IF(B2:B25="",3,IF(B2:B25=F2,2,1)),1)&IF(C2:C25="","",COUNTA(A2:A25)/2+1-C2:C25),B2:B25&C2:C25,))*ROW(1:24)=0)*24+(ROW(1:24)<MATCH(MID(F2&G2&"",IF(B2:B25="",3,IF(B2:B25=F2,2,1)),1)&IF(C2:C25="","",COUNTA(A2:A25)/2+1-C2:C25),B2:B25&C2:C25,))*ROW(1:24),ROW(1:24))),{1,3},2))&""

lg_cai

=INDIRECT("A"&MOD(SMALL((MATCH(($B$2:$B$25=F$2)*$C$2:$C$25,IF(ROW($1:$25)<25,MOD(-1^($B$2:$B$25<>F$2)*$C$2:$C$25,MAX($C$2:$C$25)+1),),)+($C$2:$C$25>0))*26+ROW($2:$25),ROW()-2),26))&""

=OFFSET($A$1,1/MOD(SMALL(MATCH(($B$2:$B$25=F$2)*$C$2:$C$25,IF(ROW($1:$25)<25,MOD(-1^($B$2:$B$25<>F$2)*$C$2:$C$25,MAX($C$2:$C$25)+1),),)+1/ROW($2:$25),ROW()-2),1)-1,)&""

wjh619993018

1. =INDEX(A2:A25,RIGHT(SMALL(TEXT(MATCH((B2:B25=G2)*12+C2:C25,COUNT(A2:C25)/2+13-(B2:B25=G2)*12-TEXT(C2:C25,"0;;21"),),"[<"&ROW(1:24)&"];"&ROW(1:24))/1%%+(B2:B25=G2)/1%+ROW(1:24),ROW(1:12)*2-{1,0}),2))&""

复制代码

hjj0451

只准引用源数据（A1:C25,F2:G2)，可否引用其中的一部分比如A1:A25.、F2?不是很清楚，请说明谢谢。题目的要求是不是公式中只能出现A1:C25和F2:G2这两种引用范围？比如A1:A25=F2这样的都是不满足要求？

cleverzhzhf

参与一下~
F3格：

1. =IF(INDEX($B$1:$B$25,MIN(--TEXT(ISERROR(MATCH("*"&$A$2:$A$25&"*",$F$2:$F2&$G$2:$G2,))*ROW($2:$25),"0;;25")))=F$2,INDEX($A$1:$A$25,MIN(--TEXT(ISERROR(MATCH("*"&$A$2:$A$25&"*",$F$2:$F2&$G$2:$G2,))*ROW($2:$25),"0;;25"))),INDEX($A$1:$A$25,MAX(ROW($A$1:$A$25)*($B$1:$B$25=F$2)*($C$1:$C$25=MAX($C$1:$C$25)+1-INDEX($C$1:$C$25,MIN(--TEXT(ISERROR(MATCH("*"&$A$2:$A$25&"*",$F$2:$F2&$G$2:$G2,))*ROW($2:$25),"0;;25")))))))

复制代码

410字符，数组公式。不过不容错。
继续思考~~~

hzhb14796

=INDEX($A$2:$A$25,MATCH(1*RIGHT(SMALL(IF(FREQUENCY(($B$2:$B$25=D2)*($C$2:$C$25),IF(($B$2:$B$25=D2)*($C$2:$C$25),$C$2:$C$25,MAX($C$2:$C$25)+1-$C$2:$C$25)),IF(($B$2:$B$25=D2)*($C$2:$C$25),$C$2:$C$25,MAX($C$2:$C$25)+1-$C$2:$C$25),999)+ROW(1:25)*100,ROW(1:25)),2),IF(B2:B25<>"",$C$2:$C$25*($B$2:$B$25=D2),99),0))&""
超长公式

减下字符
=INDEX($A2:$A25,MATCH(1*RIGHT(SMALL(IF(FREQUENCY($C2:$C25,IF($B2:$B25=F2,$C2:$C25,MAX($C2:$C25)+1-$C2:$C25)),IF($B2:$B25=F2,$C2:$C25,MAX($C2:$C25)+1-$C2:$C25),999)+ROW(1:25)*100,ROW(1:25)),2),IF(B2:B25="",99,$C2:$C25*($B2:$B25=F2)),))&""