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

Lemxd

作业一：=SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))

作业二：方法一：=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")

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

作业四：=SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12)*1)+1

作业五：方法一：=ROUND(SUMIF(C12:C21,"男",D12:D21)/COUNTIF(C12:C21,"男"),0)
               方法二：=SUMPRODUCT(MOD(A12:A21,2)*(C12:C21="男")*D12:D21)/SUMPRODUCT(MOD(A12:A21,2)*(C12:C21="男"))

函初三+B08+lemxd

平常心2011

【函初201203班】-B35-平常心2011

1. 作业一
   
2. =SUM(COUNTIFS(B12:B21,A25,E12:E21,">=60"),COUNTIFS(B12:B21,A25,F12:F21,">=60"),COUNTIFS(B12:B21,A25,G12:G21,">=60"))
   
3. 作业二
   
4. =SUM(COUNTIFS(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60"))
   
5. =SUMPRODUCT((A12:A21=1)*(E12:E21>=60)*(F12:F21>=60)*(G12:G21>=60))
   
6. 作业三
   
7. =SUMPRODUCT((H$12:H$21>H12)*1)+1
   
8. 作业四
   
9. =SUMPRODUCT(($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
   
10. 作业五
    
11. =ROUND(AVERAGEIFS(D12:D21,A12:A21,"1",C12:C21,"男"),)
    
12. =ROUND(SUMPRODUCT(D12:D21*(A12:A21=1)*(C12:C21="男"))/COUNTIFS(A12:A21,"1",C12:C21,"男"),)
    

复制代码

深蓝色的海洋

函初201203班]- B12-深蓝色的海洋
作业一
         =SUMPRODUCT((B12:B21=A25)*(E12:G21>59))
         =SUMPRODUCT((B12:B21=A25)*(E12:G21>=60))
作业二
         =SUMPRODUCT((A12:A21=A12)*(E12:E21>60)*(F12:F21>60)*(G12:G21>60))
         =COUNTIFS(A12:A21,"1",E12:E21,">=60",F12:F21,">=60",G12:G21,">=60")
作业三
        =RANK(H12,$H$12:$H$21)
        =SUMPRODUCT(--($H$12:$H$21>H12))+1
作业四
        =SUMPRODUCT(--($A$12:$A$21=A12)*($H$12:$H$21>H12))+1
作业五
       =SUMPRODUCT((A12:A21=A12)*(C12:C21=C12)*(D12:D21))/SUMPRODUCT((A12:A21=A12)*(C12:C21=C12))
      =SUMIFS(D12:D21,A12:A21,A12,C12:C21,C12)/COUNTIFS(A12:A21,A12,C12:C21,C12)