【85期】6列最多连续空行【已开贴】

5blessyou

上一个长的：

1. =MAX(MOD(LARGE((ROW(1:30)-MOD(LARGE((A1:T30<>"")*ROW(1:30)+COLUMN(A:T)*100,(20-COLUMN(A:T))*30+SUBTOTAL(3,OFFSET(A1:A30,,COLUMN(A:T)-1))+1-SUBTOTAL(3,OFFSET(A1,,COLUMN(A:T)-1,ROW(1:30)))),100))+ROW(1:30)*100,ROW(1:30)*20-14),100))

复制代码

缩短点的

1. =MAX(MOD(LARGE((ROW(1:30)-MOD(LARGE((A1:T30<>"")*ROW(1:30)+COLUMN(A:T)/1%,601-COLUMN(A:T)*30+SUBTOTAL(3,OFFSET(A1,ROW(1:30),COLUMN(A:T)-1,30))),100))+ROW(1:30)/1%,ROW(1:30)*20-14),100))

复制代码

公式很早就写了，放着没有动，本想着是否能够简化，现在差点看不懂自己写的公式了

写上一个解题思路吧！要想6列最多连续空行，需要构造一个数组，来计算当前空是第几个连续空行，然后求每行的第6大值，最后求每行第6大值得最大值，难点就在数组构造，此题第一反应是用功能强大的MM，貌似行不通，只能走其他路，俺的构造太复杂了，等开贴看各位大侠的！

fjrlgj

161字符
=MAX((MMULT((SUBTOTAL(3,OFFSET(U1,MOD(ROW(1:900),30),-COLUMN(A:T),ROW(30:929)/30))>0)+(30-MOD(ROW(1:900),30)<ROW(1:900)/30),ROW(1:20)^0)<15)*INT(ROW(30:929)/30))

xgg2001

1. =MAX(MOD(SMALL(ROW(1:30)*101-MOD(LOOKUP(COLUMN(A:T)/1%+ROW(1:30),SMALL(COLUMN(A:T)/1%+LEN(A1:T30)*ROW(1:30),ROW(1:600))),100),ROW(1:30)*20-5),100))

复制代码

wcymiss

开贴，请勿再修改答案。

我的公式：

1. 
   
2. =MAX(MOD(SMALL(ROW(1:30)*32-INT(LOG(MMULT(N(ROW(2:31)>COLUMN(A:AD)),(A1:T30<>"")*10^ROW(1:30))+1)),ROW(1:30)*20-5),31))

复制代码

拟评分：

wcymiss

本题有两个主体思路：
一、通过改变offset的行偏移、行高、列偏移，来构造30*30*20个区域，然后从中找寻满足6列为空的同行偏移、行高的区域，再找寻最大行高值。
      本方法难点在于：row、column两个参数，如何构造三个变量的组合。lg_cai 的《两位数》http://club.excelhome.net/thread-149253-1-1.html和willin2000的《比比谁的地盘大》http://club.excelhome.net/thread-374378-1-1.html里面都给出了方法：
      需要满足x*y*z三变量的组合，可以把x*y变为一个变量，然后用int和mod取值，可以得出x*y的组合。
      对于此题，可以构造(mod(row(1:900)-1,30)+1)* (int((row(1:900)-1)/30)+1) *column(a:t) ，即可得到x(1:30)*y(1:30)*z(1:20)的18000种组合。
      3楼、5楼、6楼、12楼都使用了此法，但细节处理又有所不同。主要是对于offset取值后，超出a1:t30区域的部分的容错部分不同。
      3楼是在mmult外面单独加了判断；
      5楼是直接在构造offset的行高参数时加了判断去掉了不需要的值；
      6楼则是将900行数据用index+n+if转为30*30的矩阵然后再加判断；
      12楼构造老练，将与运算改为或运算，mod与int首尾互相对应，省去了-1+1的处理，简化了不少字符。
      需要特殊说明的是：3楼的30.1打了一个很危险的擦边球，理论上说，30.1是不正确的，因为int(ROW(1:900)/30.1)<>int((row(1:900)-1)/30)，但是其公式最后的“+1”很好的弥补了30.1的错误，使得公式对于本题仍然正确。

二、主体思路二，则是计算出每个单元格向上连续空单元格的个数，然后逐行取出第6大的进行比较再取一个最大值。
                                      
       2楼、7楼、8楼、11楼、13楼和我的公式，都是用的这个构造。
       11楼用的large+subtotal构造，公式引用了A31:T:60区域，当此区域有数据时会影响公式结果。由于题目对此未做要求，且对整体思路影响不大，故不扣分。
       7楼、8楼、13楼都用的lookup，7楼多了一个判断，13楼公式则是7楼和8楼的简化版本。len(a1:t30) ，如同上期2^一样，想常人所不能想，非常难得。
       2楼和我公式基本相同，借鉴了wangg913的《监控“异常”情况连续统计》http://club.excelhome.net/thread-625490-1-1.html
wddn版主的公式，通过10^后，用mmult进行数组累加，最后用int+log还原数值，可以得到上述用lookup构造的数组。这样公式可以构造得比较短。 这个思路用得比较少，可能难以想到。

hjj0451

学习INT(LOG...)相较于LEN对于科学计数的处理，不用它处理的话就是这个意思(9e99以内)：
=MAX(MOD(SMALL(ROW(1:30)*32+20-RIGHT((MMULT(N(ROW(2:31)>COLUMN(A:AD)),LEN(A1:T30)*10^ROW(1:30))+1)*10^20,2),ROW(1:30)*20-5),31))
3楼和13楼比较直观。俺没想到取各行第15小的值。

hjj0451

楼主的MMULT不用关注数字的准确大小，要的只是位数，初一看似乎被15给束缚住了。借地方发一下这个INT-LOG：

jon_wjj

想了好久都没想出来
这个一定要认真学习学习

lyb200

看起来，头晕。留个记号。