函初【201401】练习题十三【先开帖，后评分】

zhyoucheng

1. f列： =INDIRECT("a"&MOD(ROW(),3)+3)
   
2. g列：=IF(COUNTIF($F$12:F12,F12)=1,"空调","电脑")
   
3. h列：=IF(COUNTIF($F$12:F12,F12)=1,B3,INDIRECT("c"&MOD(ROW(),3)+3))

复制代码

笨熊猫

1. =OFFSET($A$2,ISEVEN(COLUMN())*(MOD(ROW(A3),3)+1),(COLUMN()>6)*INT((ROW(A1)+2)/3))

复制代码

hongqiqqq

1. 姓名：=INDEX(A$3:A$5,MOD(ROW(),3)+1)
   
2. 商品：=INDEX(B$2:C$2,ROUNDUP(ROW(A1)/3,))
   
3. 销量：=OFFSET(A$2,MOD(ROW(),3)+1,ROUNDUP((ROW(A1))/3,))

复制代码

紫金锤

1. f12=CHOOSE(MOD(COLUMN(),3)+1,OFFSET($A$2,MOD(ROW(),3)+1,0),OFFSET($A$2,0,INT(ROW()/3)-3),OFFSET($A$2,MOD(ROW(),3)+1,INT(ROW()/3)-3))

复制代码

右拉下拉

siguoxiang

=SUM(N((MID(A2,ROW($1:$99),1))>="吖"))

siguoxiang

是我发错了吧

cleverzhzhf

剑走偏锋，不用函数操作

雪舞子

谢谢冠军老师出题,重在参与:

姓名列:

1. =OFFSET(A3,-3*(ROW()>14),)

复制代码

商品列:

1. =OFFSET($B$2,,ROW()>14)

复制代码

销量列:

1. =INDEX($B$3:$C$5,MATCH(F12,$A$3:$A$5,),MATCH(G12,$B$2:$C$2,))

复制代码

E小影

=OFFSET($A$3,MOD(ROW(A1)-1,COUNTA(A:A)-2),)
=INDEX($B$2:$C$2,INT(ROW(A1)/(COUNTA(A:A)-1))+1)
=OFFSET($A$3,MOD(ROW(A1)-1,COUNTA(A:A)-2),INT(ROW(A1)/(COUNTA(A:A)-1))+1)

云子

1. =INDEX($A$2:$C$5,MATCH(F12,$A$2:$A$5,),MATCH(G12,$A$2:$C$2,))

复制代码