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

26759761@qq.com

1、作业统一贴公式(需要过程的直接录屏).
2、跟帖不要重复占楼,要修改直接在原楼层编辑
3、评分标准:答案正确+15/+15,个别优秀的+1-5分.不贴公式直接上附件的,评分标准上,经验BB各扣5分.即+10/+10

hsl215

一
1、

1. =COUNTIF(B3:B10,">10")
   
2. =SUMPRODUCT(N(B3:B10>10))
   
3. =SUM(N(B3:B10>10))

复制代码

2、

1. =COUNTIF(C3:C10,">=1500")-COUNTIF(C3:C10,">3000")
   
2. =SUMPRODUCT((C3:C10>=1500)*(C3:C10<3000))
   
3. =SUM((C3:C10>=1500)*(C3:C10<3000))

复制代码

二

1. =COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")
   
2. =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000)*1)
   
3. =SUM((B19:B28="事业部1")*(C19:C28>5000)*1)

复制代码

三

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

复制代码

四、用COUNTIF(A61:A70,A61:A70)统计出每个部门的人数，每个部门有几个则统计出的个数就是几，如：行政部有两个则统计的结果为2，用1除统计出来的 个数，结果就是几 个几分 之一相加等于1，最终将所有部门的相加就是不重复的个数了
=SUMPRODUCT(1/COUNTIF(A61:A70,A61:A70))→=SUMPRODUCT(1/{2;2;3;1;1;1;3;3;2;2})→=SUMPRODUCT({0.5;0.5;0.333333333333333;1;1;1;0.333333333333333;0.333333333333333;0.5;0.5})→6

1. =SUMPRODUCT(1/COUNTIF(A61:A70,A61:A70))

复制代码

滴答滴

1、
人数在10人以上的部门数量
普通公式:

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

复制代码

数组公式：

1. =SUM(N(B3:B10>10))

复制代码

费用在1500-3000间的部门数量
普通公式：

1. =COUNTIF(C3:C10,">1500")-COUNTIF(C3:C10,">3000")
   
2. =SUMPRODUCT((C3:C10>=1500)*(C3:C10<=3000))

复制代码

数组公式：

1. =COUNT(0/((C3:C10>=1500)*(C3:C10<=3000)))
   
2. =SUM(N((C3:C10>=1500)*(C3:C10<=3000)))

复制代码

高版本函数:

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

复制代码

上面公式可以转化为（数组公式)：

1. =COUNT(IF((C3:C10>=1500)*(C3:C10<=3000),1))

复制代码

2、
普通公式：

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

复制代码

数组公式：

1. =COUNT(0/((B19:B28="事业部1")*(C19:C28>5000)))
   
2. =SUM(N((B19:B28="事业部1")*(C19:C28>5000)))

复制代码

高版本函数:

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

复制代码

转化方法同上一题。
3、
普通公式：

1. =SUMPRODUCT((B33:B56>"初二）")*(C33:C56="男"),D33:D56)/SUMPRODUCT((B33:B56>"初二）")*(C33:C56="男"))

复制代码

数组公式：

1. =AVERAGE(IF((B33:B56>"初二）")*(C33:C56="男"),D33:D56))

复制代码

高版本函数：

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

复制代码

结论：形如

countif/countifs可以等效于count(if(条件,1))，其中countifs的多条件统计，if中的条件改为多条件连乘;
sumif/sumifs可以等效于sum(条件*求和区域),其中sumifs的多条件求和，条件改为多条件连乘；
averageif/averageifs可以等效于average(if(条件,求平均区域)),其中averageifs的多条件求平均，条件改为多条件连乘。
特别注意，以上的条件countif/sumif/averageif类函数支持文本通配符,而转化后的形式中用“=”做条件判定时，不支持文本通配符,可考虑用其它方式转化(比如文本比较，或者截取对应位置判定，或者查找指定字符）

4、
=SUM(1/COUNTIF(A61:A70,A61:A70))
此为求区域不重复数目的经典公式，countif(range,range)会统计区域内每一个单元格的数量，并得到同原来区域形状大小的内存数组。一般我们针对单列或者单行使用。
比如某个单元格统计数量为n，则每个该单元格都会得到n，形如
{…;n…;n…;…;n…;…;}这样的形式，然后做1/n求倒数，则得到{…;1/n…;1/n…;…;1/n…;…;}的数据序列，其中1/n的总数目为n个
（*注:数组中不支持1/n这样的数学运算写法，此为说明方便，实际代表1/n之后的结果)
数学原理可知

n个1/n相加，相当于n*(1/n)=1
每组相同的单元格都得数为1，最终实现了去重效果。
与之类似公式还可以这样写
=SUM(N(MATCH(A61:A70,A61:A70,)=ROW(1:10)))
此公式，首先利用match精确查找，找到每个元素首次出现位置，因为match函数特性，只返回首次重现的位置，所以重复的元素返回的位置相同，然后再与全部的位置进行比较，统计出所有的不重复的位置个数，就得到了所有不重复元素个数。此公式比前面的countif方便的地方在于可以用于数组统计去重个数，而countif只支持单元格区域引用（直接引用或者通过offset、indirec等间接引用)，不支持数组，但是countif支持多行多列区域，使用MATCH函数时的指定区域必须是单行或者单列；
查找的指定内容也必须在指定区域存在，否则会显示“#N/A”错误。
但是对于多列的合并去重计数（或者是多条件统计）
可以用形如
match(a1:a100&b1:b100,a1:a100&b1:b100,)=row(1:100)来匹配然后统计(为了避免内容混淆多列之间还应多&一个比较特殊的字符，如"@"之类……)
比如前面第三题也可以写作

1. =AVERAGE(IF(ISLOGICAL(MATCH(A33:A56&"初一*班男",A33:A56&B33:B56&C33:C56,)=ROW(1:24)),D33:D56))

复制代码

sinowonder

1、=COUNTIF(B3:B10,">10")
     =COUNTIFS(C3:C10,">1500",C3:C10,"<3000")

2、=COUNTIFS(B19:B28,B19,C19:C28,">5000")
     {=SUM((B19:B28=B19)*(C19:C28>5000))｝

3、=AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
    ｛=SUM((FIND("一",B33:B56&"一")=2)*(C33:C56="男")*D33:D56)/SUM((FIND("一",B33:B56&"一")=2)*(C33:C56="男"))｝
   
4、｛=SUM(1/COUNTIF(A61:A70,A61:A70))｝
      a、先通过countif计算每个部门在数组中的出现频率,如一个部门出现2次，则计数为2;
      b、通过1除于出现频率将重复项折算成占比，如一个部门出现2次，则每出现1次折算为0.5;
      c、通过SUM合计将折算后数值加总，因为重复项通过第二步后加总为1,故重复项只计1次。

XMergeD

下面不写数组公式的为普通公式
1
第一问

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

复制代码

第二问
数组

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

复制代码

1. =FREQUENCY(C3:C10%/15,{2,1})

复制代码

2

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

复制代码

数组

1. =COUNT(0/((B19:B28="事业部1")*(C19:C28%>50)))

复制代码

3

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

复制代码

数组

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

复制代码

4先分别统计E61:E70各部门（假设为变量n）在整个部门中的个数，然后取其倒数，得到n部门占总部门的1/n。最后对这组数据求和（利用n个1/n=1的原理）

天龙九部

一

1. COUNTIF(B3:B10,">10")
   
2. SUM(COUNTIF(C3:C10,{">1500";">3000"})*{1;-1})
   
3. COUNTIFS(C3:C10,">1500",C3:C10,"<3000")

复制代码

二

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

复制代码

三

1. AVERAGEIFS(D33:D56,C33:C56,C33,A33:A56,"<m")
   
2. SUMIFS(D33:D56,C33:C56,C33,A33:A56,"<m")/COUNTIFS(C33:C56,C33,A33:A56,"<m")
   
3. SUMPRODUCT((A33:A56<A45)*(C33:C56=C33)*D33:D56)/SUMPRODUCT((C33:C56=C33)*(A33:A56<A45))

复制代码

四,countif第二个参数为统计条件,即每个部门都统计一次,重复几次,就统计几次,再求倒数. 相同部门的倒数之和为1,sum求出的就是不重的了

瑾瑄

1.(1)=COUNTIF(B3:B10,"">10"")
    ={COUNT(0/(B3:B10>10))}
    ={SUM(N(3:B10>10))}"
  (2). =SUM(COUNTIF(C3:C10,">="&{1500,3000})*{1,-1})
     =COUNTIFS(C3:C10,">=1500",C3:C10,"<3000")
     =INDEX(FREQUENCY(C3:C10,{1500,3000}),2)
2.={MMULT(TRANSPOSE((B19:B28=B19)*(C19:C28>5000)),ROW(19:28)^0)}
  =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
3.=SUMIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")/COUNTIFS(B33:B56,"*一*",C33:C56,"男")
=AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
={AVERAGE(IF((MID(B33:B56,2,1)="一")*(C33:C56="男"),D33:D56))}
4.论述题
(1)用COUNTIF(A61:A70,A61:A70) 统计每个数值出现的次数
(2)用1/COUNTIF(A61:A70,A61:A70)如果重复数值是有1个,就有一个1/1, 有2个,就有一个1/2,其它数据依此类推.
(3)用SUM(1/COUNTIF(A61:A70,A61:A70)),一个1/1相加就是1,两个1/2相加也就是1.依此类推可以求出非重复项的个数.
也可以用=SUM(N(MATCH(A61:A70,A61:A70,)=ROW(61:70)-60))
        =COUNT(1/FREQUENCY(MATCH(A61:A70,A61:A70,),MATCH(A61:A70,A61:A70,)))
        =SUM(N(FREQUENCY(CODE(A61:A70),CODE(A61:A70))>0))

eaglexs

E04:eaglexs交作业：

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

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

复制代码

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

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

复制代码

第二题：
事业部1业绩在5000以上的人数

1. =SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
   
2. =SUM((B19:B28="事业部1")*(C19:C28>5000))
   
3. =COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")

复制代码

第三题：
一年级男生平均值

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

复制代码

第四题：
不重复个数公式原理
COUNTIF(A61:A70,A61:A70)可以计算出所有数据出现的次数，如“行政部”出现2次，就有2个2，“人事部”出现1次，就是1个1。用1除以出现的次数后，“行政部”就是2个0.5，合计后就是1，而“人事部”是1个1。用SUM对全部数据求和后得出所有不重复的个数

十五的月亮

1. 1、=COUNTIF(B3:B10,">10")
   
2. =COUNTIF(C3:C10,">=1500")-COUNTIF(C3:C10,">3000")
   
3. 2、=COUNTIFS(B19:B28,"事业部1",C19:C28,">5000")
   
4. =SUM((B19:B28="事业部1")*(C19:C28>5000))
   
5. 3、=AVERAGEIFS(D33:D56,B33:B56,"初一*",C33:C56,"男")
   
6. =SUMIFS(D33:D56,B33:B56,"初一*",C33:C56,"男")/COUNTIFS(B33:B56,"初一*",C33:C56,"男")
   
7. 4、1个的就是1，多个的占了几个的几分之一，求和后相同的是1。
   

复制代码

chh0926

哇，都是勤快人啊
第一题
1=COUNTIF(B3:B10,">"&10)
2=COUNTIFS(C3:C10,">="&1500,C3:C10,"<="&3000)
第二题
1=COUNTIFS(B19:B28,"事业部1",C19:C28,">"&5000)
2=SUMPRODUCT((B19:B28="事业部1")*(C19:C28>5000))
第三题
1=AVERAGEIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")
2==SUMIFS(D33:D56,B33:B56,"*一*",C33:C56,"男")/COUNTIFS(B33:B56,"*一*",C33:C56,"男")
第四题
原理就是n*(1/n)=1
首先用countif统计出每个单元格出现的次数
将次数取倒数，就是将单元格根据其重复次数变成1/n，只出现一次的还是1
直接求和就可以了，出现n次的单元格每个数值是1/n，求和后还是1，这样对总体求和后就可以将重复问题忽略了，相当于是统计所有单元格但降低重复单元格权重。