Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
123
返回列表 发新帖
楼主: 水吉果果

[习题] 【函数初级201203班】第三讲B组作业上交帖(已开贴)

[复制链接]
发表于 2012-12-27 10:02 | 显示全部楼层
本帖最后由 Lemxd 于 2012-12-27 10:03 编辑
作业一:=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

评分

参与人数 1金币 +5 收起 理由
ws278106483 + 5 很给力!

查看全部评分

回复

使用道具 举报

发表于 2012-12-27 15:26 | 显示全部楼层
【函初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,"男"),)
复制代码
回复

使用道具 举报

发表于 2013-1-17 15:19 | 显示全部楼层
函初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)

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-11-1 07:13 , Processed in 0.265108 second(s), 8 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表