【函高201501】第5讲D组作业上交贴

小妖

交作业了

蝶·舞

D05：蝶·舞
第一题

1. =IF(T(INDIRECT("A"&{7,10,13,16}))="",N(INDIRECT("A"&{7,10,13,16})),T(INDIRECT("A"&{7,10,13,16})))

复制代码

第二题

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})

复制代码

第三题

1. =SUM(SUMIFS(INDIRECT({1,2,3}&"!H:H"),INDIRECT({1,2,3}&"!F:F"),A31,INDIRECT({1,2,3}&"!G:G"),B31))

复制代码

sry660

D14：sry660
第一题

1. =INDIRECT("A"&ROW(7:10)+(ROW(1:4)-1)*2)

复制代码

1. =IF(N(INDIRECT("A"&ROW(7:10)+(ROW(1:4)-1)*2))>0,N(INDIRECT("A"&ROW(7:10)+(ROW(1:4)-1)*2)),"")&T(INDIRECT("A"&ROW(7:10)+(ROW(1:4)-1)*2))

复制代码

1. =INDEX(A7:A16,ROW(1:4)*3-2)

复制代码

1. =CHAR(CODE(IF(1,INDIRECT("A"&ROW(7:10)+(ROW(1:4)-1)*2))))

复制代码

第二题

1. =SUM(SUMIF(INDIRECT(ROW($1:$3)&"!A:A"),$A22,INDIRECT(ROW($1:$3)&"!C:C")))/SUM(COUNTIF(INDIRECT(ROW($1:$3)&"!A:A"),$A22))

复制代码

1. =SUM(SUMIF(INDIRECT(ROW($1:$3)&"!A:A"),$A22,INDIRECT(ROW($1:$3)&"!C:C")))/COUNT(0/SUMIF(INDIRECT(ROW($1:$3)&"!A:A"),$A22,INDIRECT(ROW($1:$3)&"!C:C")))

复制代码

第三题

1. =SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!H:H"),INDIRECT(ROW($1:$3)&"!F:F"),$A31,INDIRECT(ROW($1:$3)&"!G:G"),$B31))

复制代码

满满滴memory

学号：D04
答案：
第一题：

1. =IF(T(INDIRECT("a"&((ROW(1:4)-1)*3+1+6)))<>"",T(INDIRECT("a"&((ROW(1:4)-1)*3+1+6))),N(INDIRECT("a"&((ROW(1:4)-1)*3+1+6))))

复制代码

第二题：

1. =SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!c:c"),INDIRECT(ROW($1:$3)&"!a:a"),A22))/COUNT(0/SUMIFS(INDIRECT(ROW($1:$3)&"!c:c"),INDIRECT(ROW($1:$3)&"!a:a"),A22))

复制代码

第三题：

1. =SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!h:h"),INDIRECT(ROW($1:$3)&"!f:f"),A31,INDIRECT(ROW($1:$3)&"!g:g"),B31))

复制代码

yqnh

D12 yqnh 上次第五课作业，请老师指导

1. 1、=INDIRECT("A"&ROW(1:4)*3+4)
   
2. 2、=SUM(SUMIF(INDIRECT(ROW($1:$3)&"!a3:a5"),A22,INDIRECT(ROW($1:$3)&"!C3:C5")))/SUM(COUNTIF(INDIRECT(ROW($1:$3)&"!a3:a5"),A22))
   
3. 3、=SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!H:H"),INDIRECT(ROW($1:$3)&"!F:F"),A31,INDIRECT(ROW($1:$3)&"!G:G"),B31))

复制代码

rocky1051408133

第一题：

1. =INDIRECT("a"&3*ROW($1:$4)+4)

复制代码

第二题：
第三题：

1. =SUM(N(SUMIFS(INDIRECT(ROW($1:$3)&"!H:H"),INDIRECT(ROW($1:$3)&"!f:f"),Sheet4!A31,INDIRECT(ROW($1:$3)&"!g:g"),Sheet4!B31)))

复制代码

C.Deny

D09：C.Deny上交第五讲作业
1.

1. =IF(N(INDIRECT("A"&ROW(1:4)*3+4))=0,T(INDIRECT("A"&ROW(1:4)*3+4)),N(INDIRECT("A"&ROW(1:4)*3+4)))

复制代码

2.

1. =MMULT({1,1,1},SUMIF(INDIRECT({1;2;3}&"!a:a"),A22,INDIRECT({1;2;3}&"!c3",)))/SUM(COUNTIF(INDIRECT({1,2,3}&"!a:a"),A22))

复制代码

3.

1. =SUM(SUMIFS(INDIRECT({1;2;3}&"!H:H"),INDIRECT({1;2;3}&"!F:F"),A31,INDIRECT({1;2;3}&"!G:G"),B31))

复制代码

孤单无所谓

D03
1.

1. =INDIRECT("a"&6+{1;4;7;10}+INT(RAND()))

复制代码

2.

1. =MMULT(SUMIF(INDIRECT({1,2,3}&"!A3:A5"),A22:A25,INDIRECT({1,2,3}&"!c3")),{1;1;1})/MMULT(COUNTIF(INDIRECT({1,2,3}&"!A3:A5"),A22:A25),{1;1;1})

复制代码

3.

1. =SUM((N(INDIRECT({1,2,3}&"!f"&ROW($$3:$$20)))=A31)*(T(INDIRECT({1,2,3}&"!g"&ROW($$3:$$20)))=B31)*N(INDIRECT({1,2,3}&"!h"&ROW($$3:$$20))))

复制代码

一杯清荼

题三：

1. =SUM(SUMIFS(INDIRECT(ROW($$1:$$3)&"!h3:h20"),INDIRECT(ROW($$1:$$3)&"!f3:f20"),A31,INDIRECT(ROW($$1:$$3)&"!g3:g20"),B31))

复制代码

笑眼晴天

1. D02      1，
   
2. =IF(T(INDIRECT("A"&{7;10;13;16}))="",N(INDIRECT("A"&{7;10;13;16})),T(INDIRECT("A"&{7;10;13;16})))
   
3. 2，
   
4. =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})
   
5. 3，
   
6. =SUM(SUMIFS(INDIRECT({1;2;3}&"!H:H"),INDIRECT({1;2;3}&"!F:F"),A31,INDIRECT({1;2;3}&"!G:G"),B31))

复制代码