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

wlc520113000

=OFFSET(A$3,MOD(ROW(A1)-1,3),)
=OFFSET(B$2,,INT((ROW(A1)-1)/3))
=OFFSET($B$3,MOD(ROW(A1)-1,3),INT((ROW(A1)-1)/3))

仅落恩

1. F12=INDEX($A$3:$A$5,MOD(ROW()-12,3)+1)

复制代码

1. G12=INDEX($B$2:$C$2,ROUNDUP(ROW(A1)/3,0))

复制代码

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

复制代码

eling5476

这个要是用透视表来做估计省了很多费心事

=INDEX(A:A,MOD(ROW(A3),3)+3)

OFFSET($B$1,1,INT(ROW(A3)/3)-1)

=SUM(($A$3:$A$5=F12)*IF(G12="空调",$B$3:$B$5,$C$3:$C$5))

chengyingjun

f12=INDEX(A$3:A$5,MOD(ROW(),3)+1)
g12=INDEX(B$2:C$2,,INT((ROW()-9)/3))
h12=SUMPRODUCT((A$3:A$5=F12)*(B$2:C$2=G12)*B$3:C$5)