本帖最后由 tyxh0916 于 2012-6-2 20:39 编辑
第一题- =INDEX(A7:A16,N(IF(1,ROW(1:4)*3-2)))
复制代码 第二题- =MMULT(SUMIF(INDIRECT({1,2,3}&"!A:A"),A22:A25,INDIRECT({1,2,3}&"!B:B")),{1;1;1})/MMULT(COUNTIF(INDIRECT({1,2,3}&"!A:A"),A22:A25),{1;1;1})
- =MMULT(SUMIF(INDIRECT({1,2,3}&"!A:A"),A22:A25,INDIRECT({1,2,3}&"!C:C")),{1;1;1})/MMULT(COUNTIF(INDIRECT({1,2,3}&"!A:A"),A22:A25),{1;1;1})
复制代码 第三题
- =SUM(DSUM(INDIRECT(ROW($1:$3)&"!F2:H15"),3,$A$30:B31))-SUM($C$30:C30)
- =SUM((N(OFFSET(INDIRECT({1,2,3}&"!F2"),ROW($1:$20),))=A31)*(T(OFFSET(INDIRECT({1,2,3}&"!G2"),ROW($1:$20),))=B31)*N(OFFSET(INDIRECT({1,2,3}&"!H2"),ROW($1:$20),)))
- =SUM(SUMIFS((INDIRECT({1,2,3}&"!H:H")),INDIRECT({1,2,3}&"!F:F"),A31,INDIRECT({1,2,3}&"!G:G"),B31))
复制代码
|