本帖最后由 wjc2090742 于 2012-6-5 15:33 编辑
A组03号wjc2090742作业
1、数组公式- =INDEX(A:A,N(IF(1,{7,10,13,16})))
复制代码- =IF(LEN(T(INDIRECT("a"&{7,10,13,16}))),T(INDIRECT("a"&{7,10,13,16})),N(INDIRECT("a"&{7,10,13,16})))
复制代码 2、B22:C25多单元格数组公式- =MMULT(SUMIF(INDIRECT({1,2,3,1,2,3}&"!a:a"),A22:A25,INDIRECT({1,2,3,1,2,3}&"!c"&{2,2,2,3,3,3},)),N(ROW(1:6)*{1,-1}<{4,-3}))/MMULT(COUNTIF(INDIRECT({1,2,3}&"!a:a"),A22:A25),{1;1;1})
复制代码 3、07及以上版本- =SUM(SUMIFS(INDIRECT({1,2,3}&"!h:h"),INDIRECT({1,2,3}&"!f:f"),A31,INDIRECT({1,2,3}&"!g:g"),B31))
复制代码 数组公式- =SUM((N(INDIRECT({1,2,3}&"!f"&ROW($3:$15)))&T(INDIRECT({1,2,3}&"!g"&ROW($3:$15)))=A31&B31)*N(INDIRECT({1,2,3}&"!h"&ROW($3:$15))))
复制代码 |