【函高201501】第五讲B组作业上交贴[开贴]

笨笨四

B组的朋友们：
开秀答案了！符合题设、答案正确的多一种方法多一个奖励！
格式：
B+学号：
1。（法1~法N：）
2。（法1~法N：）
3。（法1~法N：）
4。（法1~法N：）

1032446692

B08：
1.
法1：

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

复制代码

法2：

1. =INDIRECT("r"&ROW(1:4)*3+4&"c1",)

复制代码

2.

1. =AVERAGE(IFERROR(AVERAGEIF(INDIRECT(ROW($$$$1:$$$$3)&"!A:A"),A22,INDIRECT(ROW($$$$1:$$$$3)&"!C:C")),FALSE))

复制代码

3.

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

复制代码

cleverzhzhf

B07：
1.法1：

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

复制代码

法2：

1. =INDIRECT("A"&{1,4,7,10}+6)

复制代码

2.

1. =MMULT(SUMIFS(INDIRECT(COLUMN(A:C)&"!C3:C5"),INDIRECT(COLUMN(A:C)&"!A3:A5"),A22:A25),{1;1;1})/MMULT(COUNTIFS(INDIRECT(COLUMN(A:C)&"!A3:A5"),A22:A25),{1;1;1})

复制代码

3.

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

复制代码

Excel学徒123

1. 作业1
   
2. =IFERROR(1/N(INDIRECT("A"&ROW(1:4)*3+4))^-1,T(INDIRECT("A"&ROW(1:4)*3+4)))

复制代码

1. 作业2
   
2. =MMULT(TRANSPOSE(SUMIF(INDIRECT(ROW(1:3)&"!A3:A5"),TRANSPOSE(A22:A25),INDIRECT(ROW(1:3)&"!C3:C5"))),{1;1;1})/MMULT(TRANSPOSE(COUNTIF(INDIRECT(ROW(1:3)&"!A3:A5"),TRANSPOSE(A22:A25))),{1;1;1})

复制代码

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

复制代码

xiaopang223

B01

1.=IFERROR(1/N(INDIRECT("A"&ROW(1:4)*3+4))^-1,T(INDIRECT("A"&ROW(1:4)*3+4)))
其他方法：=IF(1,INDIRECT("A"&ROW(1:4)*3+4))
=IF(N(INDIRECT("A"&ROW(1:4)*3+4)),N(INDIRECT("A"&ROW(1:4)*3+4)),"")&T(INDIRECT("A"&ROW(1:4)*3+4))
感觉后面两个方法都对。
2.=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")))
有点赶时间，写的公式巨丑
3.=SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!H:H"),INDIRECT(ROW($1:$3)&"!F:F"),A31,INDIRECT(ROW($1:$3)&"!G:G"),B31))
感觉求和是不是要比求平均好做一点啊。
交作业来晚了，学委手下留情啊！

胖子李

B10：胖子李

第五讲作业

第一题：

1. =INDIRECT("A"&ROW(A1:A4)*3-2+6)

复制代码

第二题：

1. =AVERAGE(IF(ISNUMBER(1/SUMIF(INDIRECT(ROW($1:$3)&"!a:a"),A22,INDIRECT(ROW($1:$3)&"!C:C"))),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))

复制代码

回答完毕，请老师批示！

雨后的风

B05:
1、

1. =INDEX(INDIRECT("a:a"),N(IF(1,ROW(1:4)*3-2+6)),)

复制代码

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

复制代码

2、

1. =MMULT(SUMIF(INDIRECT(COLUMN(A:C)&"!a:a"),A22:A25,INDIRECT(COLUMN(A:C)&"!c:c")),{1;1;1})/MMULT(COUNTIF(INDIRECT(COLUMN(A:C)&"!A:A"),A22:A25),{1;1;1})

复制代码

3、

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

复制代码

车仁静

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

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

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

chh0926

感谢老师分享经验，感谢学委辛苦评分
一

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

复制代码

二

1. =TRANSPOSE(MMULT({1,1,1},SUMIF(INDIRECT(ROW($1:$3)&"!a3:a5"),TRANSPOSE(A22:A25),INDIRECT(ROW($1:$3)&"!c3:c5")))/MMULT({1,1,1},COUNTIF(INDIRECT(ROW(1:3)&"!a3:a5"),TRANSPOSE(A22:A25))))

复制代码

三

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

复制代码

仅落恩

不会做
2.=MMULT(TRANSPOSE(SUMIF(INDIRECT(ROW(1:3)&"!A3:A5"),TRANSPOSE(A22:A25),INDIRECT(ROW(1:3)&"!C3:C5"))),{1;1;1})/MMULT(TRANSPOSE(COUNTIF(INDIRECT(ROW(1:3)&"!A3:A5"),TRANSPOSE(A22:A25))),{1;1;1})
3.=SUM(SUMIFS(INDIRECT(ROW($1:$3)&"!H:H"),INDIRECT(ROW($1:$3)&"!F:F"),$A31,INDIRECT(ROW($1:$3)&"!G:G"),$B31))