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

wp8680

在F3输入=OFFSET(A$1,SMALL(IF(B$2:B$25=F$2,ROW($1:$24),30),ROW(A1)),)&""
在G3输入=IF(ROW()>COUNTA($A$2:$A$25)/2+2,"",OFFSET(A$1,MATCH((COUNTA($A$2:$A$25)/2+1-SUMIF(A$1:A$25,F3,C$1:C$25))&G$2,C$2:C$25&B$2:B$25,0),)

均三键回车下拉。

oyzhjr

费了九牛二虎之力，可算做出来了，
不过公式超了，先占位，以后修改。等结贴时看下高手们的解答吧。
如果不按次序长度为211个字符
=IF(ROW()-2>COUNTA(A$2:A$25)/2,"",IF(F$2="男",VLOOKUP(ROW()-2,IF($B$2:$B$25=$F$2,IF({1,0},$C$2:$C$25,$A$2:$A$25)),2,),VLOOKUP(COUNTA($A$2:$A$25)/2-ROW()+3,IF($B$2:$B$25=$G$2,IF({1,0},$C$2:$C$25,$A$2:$A$25)),2,)))
如果按次序，长度为382个字符
=IF(ROW()>COUNTA($A$2:$A$25)/2+2,"",VLOOKUP(INDEX(ABS(($B$2:$B$25="女")*(-COUNTA($A$2:$A$25)/2-1)+$C$2:$C$25),SMALL(IF(MATCH(ABS(($B$2:$B$25="女")*(-COUNTA($A$2:$A$25)/2-1)+$C$2:$C$25),ABS(($B$2:$B$25="女")*(-COUNTA($A$2:$A$25)/2-1)+$C$2:$C$25),)=ROW($1:$24),ROW($1:$24),4^8),ROW()-2))&H$2,IF({1,0},ABS(($B$2:$B$25="女")*(-COUNTA($A$2:$A$25)/2-1)+$C$2:$C$25)&$B$2:$B$25,$A$2:$A$25),2,))

汗一个，函数都差点超限。

wcymiss

1. =T(OFFSET(A1,RIGHT(SMALL(MATCH((C2:C25-MAX(C2:C25+1)*(B2:B25=F2))^2,(C2:C25-MAX(C2:C25+1)*(B2:B25=F2))^2,)/1%%+(B2:B25=G2)/1%+ROW(1:24),ROW()*2-{5,4}),2),))

复制代码

F3:G14多单元数组公式。长度156

chenhh803

呵呵,也做一个能出结果的,多单元格区域数组公式:
=INDEX(A2:A25,RIGHT(SMALL(MATCH(ABS(C2:C25-(B2:B25="女")*(COUNT(C2:C25)/2+1)),ABS(C2:C25-(B2:B25="女")*(COUNT(C2:C25)/2+1)),)/1%%+(B2:B25="女")/1%+ROW(1:24),ROW(1:12)*2-{1,0}),2))&""
优化一下:
=INDEX(A2:A25,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),ROW()*2-{5,4}),2))&""
再简一点:
=INDEX(A2:A25,RIGHT(SMALL(MATCH((C2:C25-(B2:B25=G2)*MAX(C2:C25+1))^2,(C2:C25-(B2:B25=G2)*MAX(C2:C25+1))^2,)/1%%+(B2:B25=G2)/1%+ROW(1:24),ROW()*2-{5,4}),2))&""

ggsmart

F3:G14=INDEX(A2:A25,--MID(RIGHT(LARGE(-IF((B2:B25=F2)*C2:C25+TRANSPOSE((B2:B25=G2)*C2:C25)=MAX(C2:C25)+1,IF(ROW(1:24)<COLUMN(A:X),ROW(1:24),COLUMN(A:X))&TEXT(ROW(1:24),"00")&TEXT(COLUMN(A:X),"00"),992424),ROW(1:12)),4),{1,3},2))&""

chunlin1wang

F3:

1. 
   
2. =INDEX($A$2:$A$25,INDEX(IF($B$2:$B$25=F$2,ROW($1:$24),MATCH($C$2:$C$25+($B$2:$B$25=$F$2)%,IF($C$2:$C$25,MAX($C$2:$C$25)+1-$C$2:$C$25+($B$2:$B$25=$G$2)%,),)),MIN(IF(COUNTIF($F$2:$G2,$A$2:$A$25),24,ROW($1:$24)))))&""

复制代码

F3:G3 勉强190字符

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

复制代码

F3:G3 176

1. =INDEX(A$2:A$25,INDEX(MATCH(-1^(B$2:B$25=F$2:G$2)*(C$2:C$25-(B$2:B$25=G$2)*(MAX(C$2:C$25)+1)),(B$2:B$25=G$2)*(MAX(C$2:C$25)+1)-C$2:C$25,),MATCH(,COUNTIF(F$2:G2,A$2:A$25),)))&""

复制代码

snowpeak

F3:G3=IF(ROW(A1)>MAX($C$2:$C$25),"",
INDEX($A$1:$A$25,IF(OFFSET($B$1,MIN(IF(COUNTIF($F$2:G2,$A$2:$A$25),"",ROW($A$2:$A$25)))-1,)=$F$2:$G$2,
MIN(IF(COUNTIF($F$2:G2,$A$2:$A$25),"",ROW($A$2:$A$25))),
MMULT(TRANSPOSE(ROW($A$2:$A$25)),(MAX($C$2:$C$25)-OFFSET($C$1,MIN(IF(COUNTIF($F$2:G2,$A$2:$A$25),"",ROW($A$2:$A$25)))-1,)+1=$C$2:$C$25)*($B$2:$B$25=$F$2:$G$2))
)))--公式长度远远超限{:soso_e110:}

F3=IF(ROW(A1)>MAX($C$2:$C$25),"",OFFSET($A$1,IF(OFFSET($B$1,MIN(IF(COUNTIF($F$2:$G2,$A$2:$A$25),"",ROW($A$2:$A$25)))-1,)=F$2,
MIN(IF(COUNTIF($F$2:$G2,$A$2:$A$25),"",ROW($A$2:$A$25))),
MAX(ROW($A$2:$A$25)*(MAX($C$2:$C$25)-OFFSET($C$1,MIN(IF(COUNTIF($F$2:$G2,$A$2:$A$25),"",ROW($A$2:$A$25)))-1,)+1=$C$2:$C$25)*($B$2:$B$25=F$2))
)-1,)) --稍微短些

叫我史栋

.....................错了错了斑竹，没按原来的顺序，请斑竹忽略

pssrgss123456

两格公式不一样行吗？我还从未参加过答题。

pssrgss123456

公式中有一个小错，重发帖。