[2014函中]第四讲作业E组提交贴

76868850

1、
   1.1=COUNTIF(B3:B10,">10")
   1.2.1=COUNTIF(C3:C10,">1500")-COUNTIF(C3:C10,">3000")
   1.2.2=COUNTIFS(C3:C10,">1500",C3:C10,"<3000")
2、
  2.1=SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
   2.2=COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")
3、
  =AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
   =AVERAGE(IF((B33:B56="*一*")*(C33:C56="男"),D33:D56))数组三键结束
4、关于重复的部分用COUNTIF(A61:A70,A61:A70)求出，再用1/重复的个数，等于平均值，最后用SUM求和，得出结果。

ldxhzy

第1题：
      1： =SUMPRODUCT(--(B3:B10>10))
      2：=SUMPRODUCT(--(C3:C10>=1500),--(C3:C10<=3000))

第2题：
      1：=SUMPRODUCT(--(B19:B28=B19),--(C19:C28>5000))
      2：=COUNTIFS(B19:B28,B19,C19:C28,">5000")

第3题：
      1：=SUMPRODUCT(--(LEFT(B33:B56,2)="初一"),--(C33:C56="男"),D33:D56)/SUMPRODUCT(--(LEFT(B33:B56,2)="初一"),--(C33:C56="男"))
      2：=AVERAGEIFS(D33:D56,B33:B56,"初一*",C33:C56,"男")

第4题：
     将区域内各具体值出现的次数取倒数后相加，这样，若某个值出现 N 次，将会有 N 个 1/N 相加，其结果为 1 ，那么整个区域内有多少个不同值，就会有多少个 1 相加，其结果正好等于不同值的个数。

一杯清荼

题一：
        人数在10人以上的部门数量:

1. =COUNTIF(B3:B10,">=10")

复制代码

1. =COUNT(0/(B3:B10>=10))

复制代码

费用在1500-3000间的部门数量:

1. SUM(COUNTIF(C3:C10,{">=1500",">=3000"})*{1,-1})

复制代码

1. =SUM((C3:C10>={1500,3000})*{1,-1})

复制代码

1. =COUNTIFS(C3:C10,">=1500",C3:C10,"<=3000")

复制代码

题三：

1. =SUMIFS(D33:D56,B33:B56,"?一*",C33:C56,"男")/COUNTIFS(B33:B56,"?一*",C33:C56,"男")

复制代码

1. =AVERAGE(IF((SEARCH("一*",B33:B56&"一")=2)*(C33:C56="男"),D33:D56))

复制代码

1. =AVERAGEIFS(D33:D56,B33:B56,"?一*",C33:C56,"男")

复制代码

1. =AVERAGE(IFERROR(SEARCH("?一*男",B33:B56&C33:C56)*D33:D56,1>2))

复制代码

题4：

1. countif得出部门的重复个数，然后运用1来平均等分部门的重复个数，也就是不管部门重复的个数是多少，用1等分后，重复的部门相加后其总数是1.

复制代码

题2：

1. COUNTIFS(B19:B28,"事业部1",C19:C28,">=5000")

复制代码

1. =COUNTIFS(B19:B28,"*1",C19:C28,">=5000")

复制代码

1. =SUM((B19:B28="事业部1")*(C19:C28>=5000))

复制代码

yqnh

E11:yqnh 上交第四课作业，请学委老师指点，祝老师中秋快乐！
1.1

1. =COUNTIF(B3:B10,">10")

复制代码

1.2

1. =COUNTIF(C3:C10,">=1500")-COUNTIF(C3:C10,">3000")

复制代码

2

1. =COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")
   
2. =SUM((B19:B28="事业部1")*(C19:C28>5000))（数组）

复制代码

3

1. =AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
   
2. =SUMIFS(D33:D56,C33:C56,"男",B33:B56,"*一*")/COUNTIFS(B33:B56,"*一*",C33:C56,"男")

复制代码

4

1. 1、主要通过COUNTIF(A61:A70,A61:A70)计算每个部门总的个数
   
2. 2、通过将1/COUNTIF(A61:A70,A61:A70)计算各个部门占各自部门的份额
   
3. 3、通过=SUM(1/COUNTIF(A61:A70,A61:A70))将各自的份额求和得出不重复个数

复制代码

meililin

第一题
1问

1. =COUNTIF(B3:B10,">10")

复制代码

1. =SUMPRODUCT((B3:B10>10)*1)

复制代码

2问

1. =COUNTIFS(C3:C10,">1500",C3:C10,"<3000")

复制代码

1. =SUMPRODUCT((C3:C10>1500)*(C3:C10<3000))

复制代码

第二题

1. =COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")

复制代码

1. =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))

复制代码

第三题

1. =AVERAGEIFS(D33:D56,B33:B56,"?一*",C33:C56,"男")

复制代码

1. =SUMPRODUCT((MID(B33:B56,2,1)="一")*(C33:C56="男")*(D33:D56))/COUNTIFS(B33:B56,"?一*",C33:C56,"男")

复制代码

第四题
计算原理：
*COUNTIF(A61:A70,A61:A70)此部分为数组公式，需三键结束，结果为一列十行的一组数，
*该数组中第一个值为A61:A70区域内值等于A61值的个数2，依次类推第十个值为，A61:A70区域内值等于A70值的个数2，即得到{2;2;3;1;1;1;3;3;2;2}
这组数是成组出现的，如果某个值重复N次，则该数组中会出现N个N，
则利用：1/N+1/N+……1/N=1。N个1/N重复的数，求和会得到1，那么最终求和就得到了不重复个数。

tt89004808

1. 1.=COUNTIF(B3:B10,">10")
   
2.    =COUNTIFS(C3:C10,">1500",C3:C10,"<3000")
   
3. 2.=COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")
   
4.    =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
   
5. 3.=AVERAGEIFS(D33:D56,C33:C56,"男",B33:B56,"初一*")
   
6.    =SUMIFS(D33:D56,C33:C56,"男",B33:B56,"初一*")/COUNTIFS(C33:C56,"男",B33:B56,"初一*")

复制代码

4.使用COUNTIF可计算每个部门出现的次数，再除以1，最后用SUM进行数组运算求和，如果A部门出现1次结果为1/1，B部门出现两次，第一次和第二次得出的结果均为1/2，三个数相加正好等于2不重复的次数

满坛皆为吾师

习题一
计算人数：

1. =SUM(N(B3:B10>10))
   
2. =COUNTIF(B3:B10,">10")
   
3. =SUMPRODUCT((B3:B10>10)*1)
   
4. =SUM(-(-TEXT(B3:B10-10,"!1;!0")))

复制代码

部门数量：

1. =COUNTIFS(C3:C10,">=1500",C3:C10,"<3000")
   
2. =SUMPRODUCT((C3:C10>1500)*(C3:C10<3000))
   
3. =SUM((LOOKUP(C3:C10*1%,{0,0;15,1;30,0})))
   
4. =SUM(--TEXT(C3:C10-1500,"[<0]!0;[<1500]1;!0"))

复制代码

习题二

1. =COUNTIFS(B19:B28,"*1",C19:C28,">5000")
   
2. =SUMPRODUCT((B19:B28=B19)*(C19:C28>5000))
   
3. =SUM(GESTEP(--C19:C28,5000)*(B19:B28=B19))
   
4. =SUM(LOOKUP(--MID(B19:B28&C19:C28,4,5),{-10,0;15000,1;20000,0}))

复制代码

习题三

1. =AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,C33)
   
2. =AVERAGE(IF(LEFT(B33:B56,2)&C33:C56="初一男",D33:D56))
   
3. =SUMIFS(D33:D56,B33:B56,"*一*",C33:C56,C33)/COUNTIFS(B33:B56,"*一*",C33:C56,C33)
   
4. =SUM(IFERROR(SEARCH("*一*男",B33:B56&C33:C56),)*D33:D56)/COUNT(SEARCH("*一*男",B33:B56&C33:C56))

复制代码

习题四

1. =SUM(1/COUNTIF(A61:A70,A61:A70))
   
2. 答：1/{计数}
   
3. 当{计数}中有=1的值,即表示无重复值，=1/1
   
4. 当{计数}中有>1的值,即表示有重复值，=1/n
   
5. 然后以sum求和区域,无重复的值计1,重复值合计为n/n=1，总数即为不重复数。

复制代码

ANDY0393

1.1

1. =COUNTIF(B3:B10,">10")
   
2. =SUMPRODUCT(N(B3:B10>10))
   
3. 数组公式：
   
4. =COUNT(0/(B3:B10>10))
   
5. =MMULT(TRANSPOSE(ROW(3:10))^0,N(B3:B10>10))

复制代码

1.2

1. =SUMPRODUCT((C3:C10>=1500)*(C3:C10<=3000))
   
2. 数组公式：
   
3. =SUM(COUNTIF(C3:C10,">"&{15,30}/1%)*{1,-1})
   
4. =COUNT(0/((C3:C10>=1500)*(C3:C10<=3000)))
   
5. =MMULT(TRANSPOSE(ROW(3:10))^0,(C3:C10>=1500)*(C3:C10<=3000))

复制代码

2

1. =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
   
2. 数组公式：
   
3. =COUNT(0/((B19:B28="事业部1")*(C19:C28>5000)))
   
4. =MMULT(TRANSPOSE(ROW(19:28))^0,(B19:B28="事业部1")*(C19:C28>5000))

复制代码

3

1. =AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
   
2. 数组公式：
   
3. =AVERAGE(IF(ISNUMBER(FIND("一",B33:B56))*(C33:C56="男"),D33:D56))
   
4. =AVERAGE(IF(ISNUMBER(SEARCH("*一*男*",B33:B56&C33:C56)),D33:D56))

复制代码

4

1. COUNTIF(A61:A70,A61:A70)部分根据条件区域返回区域内各单元格部门在区域出现次数，之后对出现次数计算倒数，根据n个1/n相加得到无限近似于或等于1的原理，得到区域内不重复个数

复制代码

xiaox0124

1. =COUNTIF(B3:B10,">10")
   
2. =COUNTIF(C3:C10,">1500")-COUNTIF(C3:C10,">3000")   =COUNTIFS(C3:C10,">1500",C3:C10,"<3000")
   
3. =SUMPRODUCT((B19:B28=B19)*(C19:C28>5000))  =COUNTIFS(B19:B28,"="&B19,C19:C28,">5000")
   
4. =AVERAGEIFS(D33:D56,B33:B56,"=*一*",C33:C56,"="&C33)  =SUMIFS(D33:D56,B33:B56,"=*一*",C33:C56,"="&C33)/COUNTIFS(B33:B56,"=*一*",C33:C56,"="&C33)
   
5. 每个部门被分解为N个（1/N)

复制代码