【函数初级201203班】第三讲F组作业上交帖（已开贴）

gdleafchy

作业一

1. =COUNTIF(E12:G12,">60")
   

复制代码

作业二

1. =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
2. =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")

复制代码

作业三

1. =RANK(H12,$H$12:$H$21)

复制代码

作业四

1. =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
   

复制代码

作业伍

1. =SUMPRODUCT((A12:A21=1)*(C12:C21="男")*D12:D21)/SUMPRODUCT((A12:A21=1)*(C12:C21="男"))
   
2. 
   
3. =AVERAGEIFS(D12:D21,A12:A21,"=1",C12:C21,"=男")
   

复制代码

yt70345

作业一：COUNTIF(E12:G12,">60")

yt70345

作业三：RANK(H12,$H$12:$H$21)

yt70345

【函初201203班】-F27-yt70345
作业一：COUNTIF(E12:G12,">60")
作业三：RANK(H12,$H$12:$H$21)

蚊子588

【函初201203班]- F25-蚊子588
01

1. =SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))

复制代码

03

1. =RANK(H12,A$12:$H$21)

复制代码

04

1. =SUMPRODUCT((A12:A21=1)*D12:D21)/COUNTIF(A12:A21,1)

复制代码

想致富

【函初201203班]- F22-想致富
作业一

1. =SUMPRODUCT((B12:B21=A25)*(E12:G21>60))
   

复制代码

作业二

1. =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
   
2. =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
3. 
   

复制代码

作业三

1. =RANK(H12,$H$12:$H$21)
   

复制代码

作业四

1. =SUMPRODUCT(($A$12:$A$21=A12)*1,($H$12:$H$21>H12)*1)+1
   

复制代码

作业五

1. =ROUND(AVERAGEIFS(D12:D21,A12:A21,1,C12:C21,"男"),0)
   
2. 
   

复制代码

小草白云

作业1：

• =SUMPRODUCT((B12:B21=A25)*(E12:G12>=60))
  

2.    =SUMPRODUCT((B12:B21=A25)*((E12:E21>60)+(F12:F21>60)+(G12:G21>60)))
3.    =COUNT(OFFSET(B12,MATCH(A25,B12:B21,0)-1,4,1,3),">60")
作业2：

• =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
  

复制代码

• =COUNTIFS(A12:A21,1,E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
  

复制代码

作业3：=RANK(H12,$H$12:$H$21,0)
复制代码
作业4：

• 
  
  
  
  • =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>=H12))
  • 作业5：
    
  
  
  
  
  • =ROUND(AVERAGEIFS(D12:D21,C12:C21,"男",A12:A21,1),0)
    
  
  复制代码
  
  
  
  • =ROUND((SUMIFS(D12:D21,C12:C21,"男",A12:A21,1)/COUNTIFS(C12:C21,"男",A12:A21,1)),0)
    
  
  复制代码
  

201203函初F30：小草白云

pxexcel

不听课，作业还是真难。