[第28期]时间归类统计[已总结]

leeyong

已经通过短消息发送了,占位!!!!!!!!!!!!!!!!!!!!!!

---

QUOTE:

思路正确，但显得复杂了，每个单元格最大运算量达 1440*7*6=60480 ，所以计算速度很慢，且未作零值处理，不符合要求3，故不评分，谢谢参与。

QUOTE:

在2007-10-10 20:48:30，leeyong给您发送的消息！ 
消息标题：第28期]时间归类统计
--------------------------------------------------------------------------------

c16单元格数组公式:

=INDEX(FREQUENCY(LARGE(({"谷","平","峰","平","峰","平","谷"}=C$15)*(ROW($1:$1440)/10>{0,30,45,69,102,126,132})*(ROW($1:$1440)/10<={30,45,69,102,126,132,144})*(IF(ROW($1:$1440)<=$A16*1440,1440)+ROW($1:$1440)),ROW($1:$1440)),($A16:$B16+IF($A16>=$B16,{0,1}))*1440),2)/60

这个公式好慢哦,恰好260字符!!!太难了,只好这样将就了

[此贴子已经被guangyp于2007-10-26 13:34:24编辑过]

guangyp

答先放上我的答案，待评分结束后再写总结：

[此贴子已经被作者于2007-10-31 19:23:02编辑过]

jjw530

QUOTE:

我也没注意到要求消除零值，零值确实有必要消除吗？如果是的话，可以再加上text函数转化，比原先再多大约13个字符左右，达到179个字符。

我怎么就没想到TEXT函数屏蔽零值呢！！！！！！！！！！！！！！！！！！！！！！！！

郁闷！！！！！！！！！！

chrisfang

QUOTE:

以下是引用guangyp在2007-10-24 21:30:31的发言：

先放上我的答案，待评分结束后再写总结：

呵呵，当时就想到用MOD来解决跨越24小时的问题应该更简短些，但事情太多没时间细想了，就用了个偷懒的办法，也怪关版主留的字符限制实在太宽裕。其实捷径就只有半步之遥。

现在看了关版主的答案，觉得我们的结合起来公式可以更短些（*30来减少几个小数点的办法你也想出来了，真是够抠门的，哈哈）：

frequency的解法150个字符：=TEXT(SUM(({2;3;1;3;1;3;2}=COLUMN(A:A))*FREQUENCY(MOD(ROW(INDIRECT($A16*1440+1&":"&($B16+($B16<=$A16))*1440)),1440),{10;15;23;34;42;44}*30)/60),"[>0]")

lookup的解法153个字符：=TEXT(SUM((LOOKUP(MOD(ROW(INDIRECT(($A16*1440)&":"&(($B16+($B16<=$A16))*1440-1))),1440),30*{0,10,15,23,34,42,44},{2,3,1,3,1,3,2})=COLUMN(A:A))/60),"[>];")

QUOTE:

---lookup的解法153个字符..的公式

0:00开始时公式会出错. 需要用+1 -1处理一下.  -willin2000

呵呵，willin2000说的没错，边界又没考虑进去[em04]，另外还发现多了4个括号，所以一来一去，lookup的公式反而少了2个字符，可以改为151个字符的：

=TEXT(SUM((LOOKUP(MOD(ROW(INDIRECT($A16*1440+1&":"&($B16+($B16<=$A16))*1440))-1,1440),30*{0,10,15,23,34,42,44},{2,3,1,3,1,3,2})=COLUMN(A:A))/60),"[>];")

QUOTE:

frequency的解法可以再抠门一点，再少两字符，呵呵：

=TEXT(SUM(({4;5;3;5;3;5;4}=COLUMN())*FREQUENCY(MOD(ROW(INDIRECT($A16*1440+1&":"&($B16+($B16<=$A16))*1440)),1440),{10;15;23;34;42;44}*30)/60),"[>];")

                                                                                  ----guangyp

呵呵，lookup公式同理可用{4;5;3;5;3;5;4}=COLUMN()

                                                                                 ——chrisfang

[此贴子已经被作者于2007-10-26 16:14:56编辑过]

laoyebin

不会吧,我的公式本来和版主的差不多,但我在2007版本里会出现错误值,一直没想到办法去掉

看来又吃了版本的亏,真郁闷

QUOTE:

2007里用TEXT(.......,"[>];")(2003里也可以),        不要用TEXT(.......,"[>0]")

TEXT(.....,"G/通用格式;;")两个版本也都能用. 不会引起兼容性问题.                                              -willin2000

原来是这样,谢谢willin兄指点

[此贴子已经被作者于2007-10-26 15:29:17编辑过]

guangyp

为了大家在十.一的假期中好好想一下竞赛题目，所以出了道考点较多的题，难度比较大。看到大家的投票均在困难以上，下面详细谈一下解题时的一些要点。

一、解题思路

本期的答案从归类主函数方法方面来看，有两种思路，一种为 Frequency 函数思路（以下简称为F思路），另一种为 Lookup 函数思路 （以下简称为L思路），由于不能使用辅助单元格，所以它们都需要构建三个数组，第一个为时间序列数组，第二个为分段点数组，第三个为类别数组，请看下面的思路分解图：

由图示可以看出，两种思路构建的内存数组有所不同，原因是Frequency统计的原则为：[某分段点]<统计值<=[比某分段点稍大的分段点] 的个数；Lookup统计的原则为：在分段点数组中找到小于或等于统计值的对应数据。要注意的是，Frequency对分段点数组没有排序的要求，而Lookup对分段点数组要按升序来排序的，Excel中数据的升序排序原则是“...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE”，文本不区分大小写。

接下来谈谈各种数组的构建。

二、时间序列数组

产生时间分段点数组当然要用到构建内存数组的大功臣Row函数了，它比Column的官衔还要大上一级，因为它能产生的内存元素个数多达65536个，column嘛，只能是256个，就算是在Excel2007中，还是Row大。

注意到Row需要的参数为整行，所以要把先时间分段点转化一下才能产生数组。

首先要得到起始行和终止行的行号。大家都知道行号为一个整数，而输入的起如和截止时间均为小数，数值是以天为单位的，如1900-1-0 的数值为0，1900-1-1 的数值为1.....，依此类推，呵呵，这就是所谓的日期序列了。由于统计的最小的精度为分，为1天为24小时，1小时为60分，所以要把输入的时间转化为以分为单位的数据，就得乘以24*60=1440，这就是众多答案中出现1440这个数值的原因了。

得到了起始行和终止行的行号后，接下来要用它们来获得数组，偶最先想到的是Indirect这个函数了。记得山菊花老师在论坛上曾很形像地描述过这个函数，偶就不多说了。后来做题目的扩展答案时又想到用Offset这个函数，原因是使用Indirect的话在做扩展二时公式很容易就超过7层函数嵌套的限制，并且它支持浮点误差，即 截止时间 - 起始时间 时很容易产生浮点误差，这也是有部分公式有瑕疵的原因。

最后把它们嵌套在一起就行了，在跨天统计时，为使时间序列数值不至于超出分段点，故均在最外层嵌套多一个Mod函数，即公式的基础模型为：

QUOTE:

时间序列数组 =Mod( Row( Indirect( 起始时间*1440+1 ：截止时间*1440 ) ) , 1440 )

或：

QUOTE:

时间序列数组 =Mod( Row( Offset( A1, 0, 0, ( 截止时间 - 起始时间 )*1440 )+起始时间*1440 , 1440 )

注意到F思路和L思路统计的原则不同，还要做对模型做一些小调整，这可以看一下14楼chrisfang兄的贴子讨论，这里就不再重复，也请大家好好想一下，以便能更好的提高。

三、时间分段点数组和类别数组

这两个数组是有对应关系的，根据F思路和L思路统计原则不同，很容易得知构建的数组就是上面的图示中的表格，这里只谈一下创建数组的公式长度方面考虑思路而已，要说明的是，长度并不代表运算速度。以下只拿F思路的数组来讲述数组的创建方法，L思路与其是一样的。

1、时间分段点数组

时间分段点数组的单元当然要跟时间序列数组的单位要一致，故时间分段点数组初步考虑是这样写的：

QUOTE:

时间分段点数组 ={"5:00";"7:30";"11:30";"17:00";"21:00";"22:00"} *1440

F思路比L思路的分段点少一个的原因是Frequency函数统计时最后会多出一个数组元素，这个元素在本题理解为22:00--24:00的统计结果。从字符长度来看，可以直接写成*1440的结果数组，即：

QUOTE:

时间分段点数组 ={300;450;690;1020;1260;1320}

再抠门一些，就写成这样了：

QUOTE:

时间分段点数组 ={10;15;23;34;42;44}*30

这时用归类函数即可得到统计结果数组，公式模型为：

QUOTE:

统计结果数组 =Frequency( 时间序列数组, 时间分段点数组 )

2、类别数组

类别数组是为了能区分出统计结果数组中各个元素所对应的类别，所以很容易知道：

QUOTE:

类别数组 ={"谷";"平";"峰";"平";"峰";"平";"谷"}

所以类别判断数组的初步建立公式为：

QUOTE:

类别判断数组 =（ {"谷";"平";"峰";"平";"峰";"平";"谷"}=X 所在单元格）

考虑到字符长度问题，把文本的双引号尽量省去，可以变为：

QUOTE:

类别判断数组 =( Mid("谷平峰平峰平谷",Row($1:$7),1)=X 所在单元格)

上面的类别判断数组公式是利用公式往右移动时，X值的内容化而作的，进一步考虑，公式往右移动时，列号也是改变的，所以我们如果把“峰，谷，平”看成列号“3，4，5”的话，就可以用更少的字符来表示了，即：

QUOTE:

类别数组 ={4;5;3;5;3;5;4}

QUOTE:

类别判断数组 =( {4;5;3;5;3;5;4}=Column() )

最后用Sum来汇总结果，即：F思路结果 =Sum( 类别判断数组 * 统计结果数组 ) ，展开来写即为：

QUOTE:

F思路结果 =Sum( 类别判断数组 *  Frequency( 时间序列数组, 时间分段点数组 ) )

用这种方法来分析L思路，也可以得到它的公式模型：

QUOTE:

L思路结果 =Sum( N( Lookup( 时间序列数组, 时间分段点数组, 类别数组 ) = Column() ) )

上述公式的N函数把逻辑值T、F转化为数值1、0，这样Sum才能进行求和，这里其实是使用了Lookup 的向量方式，为省字符，可以使用Lookup 的数组方式，即把 时间分段点数组和类别数组 组合为一个二维数组，即：

QUOTE:

L思路结果 =Sum( N( Lookup( 时间序列数组, 时间分段点及类别二维数组 ) = Column() ) )

四、公式排错

通过以上的计算，知道结果只有正值和零值两种情况，为了避逸出现 IF(核心公式=0,"",核心公式)  这样的超长公式，我们可以考虑用Text 函数来排错，这样可使核心公式只出现一次，从而提高了运算速度。

Text 函数的功能很强大，主要在于第二参数即格式参数的灵活性，大多数能用自定义单元格格式实现的功能，Txet也能实现，想一下，如果用是用自定义单元格格式来隐藏零值，你会怎么用？当然最容易的是按自定义单元格格式的简单形式“正数格式;负数格式;零值格式;文本格式” 来控制了，即公式为：

QUOTE:

最终结果 =Text( 思路结果, "G/通用格式;;;" )

其实自定义单元格格式的具体形式为“[大于条件值]的格式;[小于条件值]的格式;[等于条件值]的格式;文本格式”，只不过在没特别指定条件值时，默认的条件值为0，所以才得到上面大家常知道的简单形式。用这种形式来控制的公式为：

QUOTE:

最终结果 =Text( 思路结果, "[>0];;;" )

公式进一步简化，可得到：

QUOTE:

最终结果 =Text( 思路结果, "[>];" )

Text第二参数有版本的兼容性问题，请参看15楼的讨论，同时也多谢Willin2000版主的测试。

本题总结至此完毕，谢谢参与答题的各位会员以及阅读过本贴的朋友们！

[此贴子已经被作者于2007-11-2 20:10:02编辑过]