Question

求EXCEL公式，高手能帮弄一下吗？谢谢！

Answer 1

如果使用office2019，可以在E5编辑公式：=TEXTJOIN(,TRUE,IF(C$2:$C$11=LEFT(D5),$B$2:$B$11,""))&TEXTJOIN(,TRUE,IF(C$2:$C$11=MID(D5,2,1),$B$2:$B$11,""))&TEXTJOIN(,TRUE,IF(MID(D5,2,1)=RIGHT(D5),"",IF(C$2:$C$11=MID(D5,2,1),$B$2:$B$11,"")))

然后同时按住Shift和Ctrl键，再按一下Enter键，在上面的公式的头尾会自动加上左大括号“{”和右大括号“}”。

选中E5单元格，下拉复制即可。

如果使用其它版本的office，E5中的公式由下面的代替：

=IF(C$2=VALUE(LEFT(D5)),B$2,"")&IF(C$3=VALUE(LEFT(D5)),B$3,"")&IF(C$4=VALUE(LEFT(D5)),B$4,"")&IF(C$5=VALUE(LEFT(D5)),B$5,"")&IF(C$6=VALUE(LEFT(D5)),B$6,"")&IF(C$7=VALUE(LEFT(D5)),B$7,"")&IF(C$8=VALUE(LEFT(D5)),B$8,"")&IF(C$9=VALUE(LEFT(D5)),B$9,"")&IF(C$10=VALUE(LEFT(D5)),B$10,"")&IF(C$11=VALUE(LEFT(D5)),B$11,"")&IF(C$2=VALUE(MID(D5,2,1)),B$2,"")&IF(C$3=VALUE(MID(D5,2,1)),B$3,"")&IF(C$4=VALUE(MID(D5,2,1)),B$4,"")&IF(C$5=VALUE(MID(D5,2,1)),B$5,"")&IF(C$6=VALUE(MID(D5,2,1)),B$6,"")&IF(C$7=VALUE(MID(D5,2,1)),B$7,"")&IF(C$8=VALUE(MID(D5,2,1)),B$8,"")&IF(C$9=VALUE(MID(D5,2,1)),B$9,"")&IF(C$10=VALUE(MID(D5,2,1)),B$10,"")&IF(C$11=VALUE(MID(D5,2,1)),B$11,"")&IF(RIGHT(D5)=MID(D5,2,1),"",IF(C$2=VALUE(RIGHT(D5)),B$2,"")&IF(C$3=VALUE(RIGHT(D5)),B$3,"")&IF(C$4=VALUE(RIGHT(D5)),B$4,"")&IF(C$5=VALUE(RIGHT(D5)),B$5,"")&IF(C$6=VALUE(RIGHT(D5)),B$6,"")&IF(C$7=VALUE(RIGHT(D5)),B$7,"")&IF(C$8=VALUE(RIGHT(D5)),B$8,"")&IF(C$9=VALUE(RIGHT(D5)),B$9,"")&IF(C$10=VALUE(RIGHT(D5)),B$10,"")&IF(C$11=VALUE(RIGHT(D5)),B$11,""))

效果：

C2可以用公式：=ROW($A$11)-ROW($A$2)+1-SUMPRODUCT(ISERROR(FIND($B2,$A$2:$A$11,1))*1,ROW(A$2:A$11)/ROW(A$2:A$11))

Answer 2

1. 如果不注重顺序，可以用这个公式：

输入公式后同时按ctrl+shift+回车：

=TEXTJOIN(,1,IF(D$2:$D$11=0,$C$2:$C$11,""))&TEXTJOIN(,1,IF(D$2:$D$11=3,$C$2:$C$11,""))&TEXTJOIN(,1,IF(D$2:$D$11=6,$C$2:$C$11,""))

2.加个辅助列，可以省事

=IF(OR(D2=0,D2=3,D2=6),1,0)

输入公式后同时按ctrl+shift+回车：=TEXTJOIN(,1,IF(E$2:$E$11=1,$C$2:$C$11,""))