[已解决]怎么输入

OYXM727

我想在sheet1竖排输入
在sheet2表变成想的横排输出
遇到重复的就在下一列自动输出

最佳答案

月排行榜 / 总排行榜

baksy

2014-9-19 13:59

A2公式，三键回车，横拉到C2，下拉；
=IF(COLUMN()<4,IF(ROW()-1>COUNT(IF(MATCH(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,)=ROW($2:$5)-1,1)),"",INDEX(Sheet1!A$1:A$5,SMALL(IF(MATCH(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,)=ROW($2:$5)-1,ROW($2:$5)),ROW(A1)))),I10)
D2公式，三键回车，横拉、下拉；
=IF(COUNT(IF((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B$5=$B2),1))<COLUMN(B2),"",INDEX(Sheet1!$C$1:$C$5,SMALL(IF((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B$5=$B2),ROW($A$2:$A$5)),COLUMN(B2))))

跳转到最佳答案楼层

这儿有肥猫

附件？

baksy

A2公式，三键回车，横拉到C2，下拉；
=IF(COLUMN()<4,IF(ROW()-1>COUNT(IF(MATCH(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,)=ROW($2:$5)-1,1)),"",INDEX(Sheet1!A$1:A$5,SMALL(IF(MATCH(Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,Sheet1!$A$2:$A$5&Sheet1!$B$2:$B$5,)=ROW($2:$5)-1,ROW($2:$5)),ROW(A1)))),I10)
D2公式，三键回车，横拉、下拉；
=IF(COUNT(IF((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B$5=$B2),1))<COLUMN(B2),"",INDEX(Sheet1!$C$1:$C$5,SMALL(IF((Sheet1!$A$2:$A$5=$A2)*(Sheet1!$B$2:$B$5=$B2),ROW($A$2:$A$5)),COLUMN(B2))))

baksy

请参考。