第五期函高第七讲作业A组上交贴

pureshadow

第五期函高第七讲作业A组上交贴

上交作业设置了回贴仅楼主可见，所以不必用回复可见贴
上交方式随意，只贴公式或上传附件都可以
7月23日（星期二）18：00之前。。。（你们懂的。。。）

飞雨飘

1. =COUNTIF(OFFSET($G$14,-(A9=101)*5,,5),">"&G9)+1

复制代码

1. =SUM(N(COUNTIF(OFFSET(A24:A35,,ROW(1:7)),"<100000")>2))

复制代码

1. =OFFSET(A43,SMALL(IF(COUNTIF(OFFSET($A$43,,,ROW($A$1:$A$13)),$A$43:$A$55)=1,ROW($A$1:$A$13)),6-ROW(INDIRECT("1:"&SUM(1/COUNTIF($A$43:$A$55,$A$43:$A$55))))),)

复制代码

liuho1

1：{=RANK(G9,OFFSET($G$8,SMALL(IF($A$9:$A$18=A9,ROW($1:$10)),1),,COUNTIF($A$9:$A$18,A9),))}； 下拉
2：{=SUM(N(COUNTIF(OFFSET(A23,ROW(1:12),1,1,7),"<100000")>=3))}
3：{=T(OFFSET(A42,LARGE(IF(MATCH(A43:A55,A43:A55,)=ROW(1:13),ROW(1:13)),ROW(INDIRECT("1:"&SUM(1/COUNTIF(A43:A55,A43:A55))))),))}

qingfeng_qf

1.

1. =SUM(N(N(OFFSET($G$8,($A$9:$A$18=A9)*ROW($1:$10),))>G9))+1

复制代码

2.

1. =SUM(N(MMULT(N(N(OFFSET(A23,ROW(1:12),COLUMN(A:G)))<100000),ROW(1:7)^0)>=3))

复制代码

3.

1. =OFFSET($A$42,MAX((COUNTIF($E$42:E42,$A$43:$A$55)=0)*ROW($1:$13)),)
   
2. 
   
3. =T(OFFSET(A42,LARGE(IF(FREQUENCY(MATCH(A43:A55,A43:A55,0),MATCH(A43:A55,A43:A55,0)),ROW(1:14),0),ROW(INDIRECT("1:"&SUM(1/COUNTIF(A43:A55,A43:A55))))),))
   

复制代码

耿培侠

=SUM(N(N(OFFSET($G$8,($A$9:$A$18=A9)*ROW($1:$10),))>G9))+1
=SUM(N(MMULT(N(N(OFFSET(A23,ROW(1:12),COLUMN(A:G)))<100000),ROW(1:7)^0)>=3))

dongqing1998

先写几个公式交上：

第一题：=SUM(N(N(OFFSET(G$8,(A$9:A$18&C9=A9&C9)*ROW($1:$10),))>G9))+1
第二题：=SUM(N(COUNTIF(OFFSET(B23,ROW(1:12),,,7),"<="&10^5)>2))
第三题：=T(OFFSET(A41,-SMALL(-(MATCH(A43:A55,A43:A55,)=ROW(1:13))*ROW(2:14),ROW(1:13)),))

zjcat35

A09：zjcat35        

macky591

第五期第7讲作业【函高A05】ma.rar (12.35 KB, 下载次数: 93)

2012-7-20 08:57 上传

点击文件名下载附件

zhh823543

A02.zhh823543交作业
1.

1. =RANK(G9,OFFSET(G$8,MATCH(A9,A$9:A$18,),,COUNTIF(A$9:A$18,A9)))

复制代码

2.

1. =SUM(N(COUNTIF(OFFSET(B23,ROW(1:12),,,7),"<100000")>2))

复制代码

3.

1. =T(OFFSET(A43,SMALL(IF(COUNTIF(OFFSET(A43,,,ROW(1:13)),A43:A55)=1,ROW(1:13)-1,12),SUM(1/COUNTIF(A43:A55,A43:A55))+1-ROW(INDIRECT("1:"&SUM(1/COUNTIF(A43:A55,A43:A55))))),))

复制代码

穗百合

第1题

1. =RANK(G9,OFFSET(G$8,MATCH(A9,A$9:A$18,),,COUNTIF(A$9:A$18,A9),))

复制代码

第2题

1. =SUM(N(MMULT(SUBTOTAL(2,OFFSET(A23,ROW(1:12),(B24:H35<100000)*COLUMN(A:G),,)),ROW(1:7)^0)>=3))

复制代码

第3题

1. =T(OFFSET(A42,LARGE((MATCH(A43:A55,A43:A55,)=ROW(1:13))*ROW(1:13),ROW(INDIRECT("1:"&SUM(N(MATCH(A43:A55,A43:A55,)=ROW(1:13)))))),))

复制代码