b2=IF(ROW(A1)>IF(COLUMN(A1)=COUNTIF($A:$A,1),COUNT(OFFSET($A$1,LOOKUP(1,0/($A$2:$A$16=1),ROW($2:$16))-1,,100)),SMALL(IF($A$2:$A$16=1,ROW($2:$16)),COLUMN(B1))-SMALL(IF($A$2:$A$16=1,ROW($2:$16)),COLUMN(A1))),"",INDEX($A:$A,SMALL(IF($A$2:$A$16=1,ROW($2:$16)),COLUMN(A1))+ROW(A1)-1))数组公式,同时按ctrl+shift+回车三键结束,下拉右拉
追问
大神,按你的输入还是达不到效果,可以把黄色部分清零变空白吗?