[答疑解惑]函数公式解释专用帖

wudingyong

看了大半天了，有些理解，对于原来用and和or为什么不能返回数组运算有了认识，随着内容贴的越来越深，感觉公式的功能真是强大，很多以为只能程序贴才能解决的东西原来公式也能解决。我是一名教师，我们的行来领域用数组公式可能最多的就是多条件统计，那我已经懂了。原来我都是通过合并列使多条件变为少条件或单条件来求和或统计，受益了。另外请详解一下FREQUENCY函数。它为什么不能返回一个确定的数统计，而是返回一上限数字统计呢，我感觉有点像用IF函数求各分数段的人数。
说明一下，附件是我先想问的，后来自己懂了，对该贴没有意义，因为加上的，无法删除。
附件无意义，用sumif()就可以了。

[ 本帖最后由 wudingyong 于 2009-8-5 10:53 编辑 ]

gouweicao78

选B1:B3输入公式
=FREQUENCY(A2:A22,{60;85}-0.01)
然后按CTRL+SHIFT+ENTER结束，即可得到结果。B1为不及格人数，B2为大于等于60小于85的人数，B3为大于等于85的人数。

关于该函数的详解，请参阅：http://club.excelhome.net/thread-467836-1-1.html解读FREQUENCY函数

[ 本帖最后由 gouweicao78 于 2009-8-5 21:57 编辑 ]

ggtang

版主，这是你给我写的数组公式，最后面的（-1，1）*24中的（-1，1）没能理解，能解释一下吗？还有就是date()里面的day参数是不是应该为1而不是0呀？？
=SUM(TEXT(IF({1,0},B4:B13+C4:C13,D4:D13+E4:E13),"[>"&DATE(H3,I3+1,0)&"]"""&DATE(H3,I3+1,0)&""";[<"&DATE(H3,I3,0)&"]"""&DATE(H3,I3,0)&"""")*{-1,1})*24

[ 本帖最后由 ggtang 于 2009-8-13 20:15 编辑 ]

gouweicao78

原帖由 ggtang 于 2009-8-13 19:41 发表
版主，这是你给我写的数组公式，最后面的（-1，1）*24中的（-1，1）没能理解，能解释一下吗？还有就是date()里面的day参数是不是应该为1而不是0呀？？
=SUM(TEXT(IF({1,0},B4:B13+C4:C13,D4:D13+E4:E13),"[>"&DATE( ...

SUM({15,20}*{-1,1})→SUM({-15,20})→=20-15=5
TEXT产生2列的数组，*{-1,1}就是用后一列减去前一列。

另DATE中的day参数确实应该用1，因为已加了时间，2009-8-1的时间是2009-8-1 0:00，而2009-7-31的时间是2009-7-31 0:00，如果用0作day则会产生月末1天的统计误差。

zhw952

请教一下这个公式：
  H6=OFFSET(F$5,MATCH(,COUNTIF(H$5:H5,F$6:F$210),),)

gouweicao78

COUNTIF(H$5:H5,F$6:F$210)——公式从H6开始，统计H$5:H5中包含F6:F210的个数，因为H5没有包含，因此返回的都是0。
MATCH(,COUNTIF,)===MATCH(0,COUNTIF,0)——在COUNTIF返回的数组中精确查找第一个0的位置，也就是第一个H$5:H5未出现过的F6:F210的记录的位置。
OFFSET(F$5,MATCH,0)——从F5单元格偏移match行，Match由上面公式得到。

因此，第一步就在H6单元格得到第一个未曾出现在H$5:H5的记录，那么到了H7，已经有出现H6的记录COUNTIF返回的就不再是0，从而MATCH找到的第一个0将是下一个未曾出现过的F列的记录。

由此，在H列不重复地将F6:F210的内容一一列出。

woshizbpixie

这是求>2 <5的数字个数问题，我水平很低，望得到您的解释，谢谢
=SUM(COUNTIF(A1:A6,{">2",">=5"})*{1,-1})，请问{">2",">=5"}里面为什么需要加引号呢？另外大括号内的数组一般不是用；隔开吗，这里怎么用，呢

[ 本帖最后由 woshizbpixie 于 2009-8-18 08:30 编辑 ]

gouweicao78

COUNTIF(A1:A6,">2")——统计大于2的个数，假设结果为4个，
COUNTIF(A1:A6,">=5")——统计大于等于5的个数，假设结果为2个
COUNTIF(A1:A6,{">2",">=5"})——分别统计大于2、大于等于5的个数，得到{4,2}
{4,2}*{1,-1}就是4*1,2*-1，即{4,-2}
SUM({4,-2})——也就是4-2=2

从这个过程可以看出，公式利用常量数组作为COUNTIF的统计条件，统计大于2的个数（包含大于等于5）并扣除大于等于5的个数，也即是大于2且小于5的个数。

常量数组可以使用逗号间隔列、分号间隔行。比如{1,2,3}是3列，相当于A1:C1，{1;2;3}是3行，相当于A1:A3。

e1e598

{=MAX(SUM(IF(OFFSET($A$2,1,0,COUNTA($A:$A),6)=OFFSET(J$2,1,0,COUNTA($A:$A),1),1)))}   这个公式是什么意思?2行是空的呀为什么引用它呀 能详细说明一下整个公式的含义吗?    (问题解决) 十分感谢版主的热情 万分感谢!!

[ 本帖最后由 e1e598 于 2009-8-20 13:57 编辑 ]

gouweicao78

请给压缩后的附件或原帖链接，而不是图片。
依我看，公式是错误的。SUM得到单值，MAX就等于废了。

补充回答：
1、OFFSET($A$2,1,0,COUNTA($A:$A),6)
表示从A2单元格开始，向下偏移1行（到A3），向右偏移0列，高度为COUNTA(A:A)也就是A列多少个记录，宽度为6。
从附件中的数据来说，就是引用了A3:F32单元格区域，是一个动态引用，随着A列数据增加而自动引用这些数据。
同理，OFFSET(J$2……就是引用J3:J32
2、比较：A3:F32=J3:J32，如果相等就返回1，并用SUM求和。

楼主公式中，MAX是多余的。
可以改用：
=SUMPRODUCT(N(OFFSET($A$3,0,0,COUNTA($A:$A),6)=OFFSET(J$3,0,0,COUNTA($A:$A),1)))
普通公式，无需按CTRL+SHIFT+ENTER结束，且解决了第2行删除问题。