如何用公式计算出红球数量？

楼主天南地北	Q：如何用公式计算出红球数量？ A：假如球的个数在L2单元格，公式为：【公式一】数组公式（wcymiss提供） =SUM(FREQUENCY(ROW(INDIRECT("1:"&L2)),(ROW(1:99)6+{-1,1})^2/8-1/8)MOD(ROW(2:200),2)) 【公式二】数组公式（w83365040371提供） =SUM(TEXT(ROW(1:198)-TEXT(L2-COMBIN(ROW(2:199),2),"!0;0;0"),"0;!0;0")(MOD(ROW(1:198),3)=0)) 【公式三】普通公式 =3COMBIN(FLOOR(ROUNDUP(((1+8L2)^0.5-1)/2,),3)/3+1,2)-(MOD(ROUNDUP(((1+8L2)^0.5-1)/2,),3)=0)(COMBIN(ROUNDUP(((1+8L2)^0.5-1)/2+1,),2)-L2) 【公式四】普通公式（公式三的简化，wjc2090742提供） =3COMBIN(ROUNDUP((1+8L2)^0.5/6+1/6,),2)-(MOD((1+8L2)^0.5/2-51%,3)>2)(COMBIN(ROUNDUP(((1+8L2)^0.5+1)/2,),2)-L2) 注：由于COMBIN的缘故，当球的个数少于4时，公式三和公式四会出错【公式解释】公式都用到的数列的原理，需要对数列有一定的理解 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆* 公式一：公式的核心部分是ROW(1:99)6+{-1,1})^2/8-1/8，其目的是构造一个99行两列的数组，目的是返回序列中蓝色最后一个球的位置和红色最后一个球的位置，如下图所示然后利用FREQUENCY返回分别统计各个区间的球的数量，且FREQUENCY为左开右闭（也即大于n且小于等m)区间形式，所以分别返回<=3，>3且<=6，>6且<=15，>15且<=21…………区间的数据个数，结果返回一个1列199行的一维数组，然后统计此数组中位置为偶数的数据个数的和，即=SUM(FREQUENCY(ROW(INDIRECT("1:"&L2)),(ROW(1:99)6+{-1,1})^2/8-1/8)(MOD(ROW(1:199),2)=0))，简化为 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆* 公式二：此公式可能有点不太好理解，巧妙使用TEXT和数列，我们可以这么去理解这个公式，首选存在一列N多个乒乓球，乒乓球全部按照题目要求上好颜色，我们要做的就是从开头截取一部分球来计算红球个数，有了这样的理解我们再来分析此公式 ROW(1:198)返回198段不同颜色的球，且第N段球的个数为N个，第3N-2项为黄球，第3N-1项为蓝球，第3N项为红球，COMBIN(ROW(2:199),2)返回对应的ROW(1:198)段不同颜色球的累加个数，本例为{1;3;6;10;15;21;28;36…………;19110;19306;19503;19701}（其实COMBIN为数学中的组合函数，刚好为自然数列的前n-1的和）当这个数据与给定球的个数之差，大于0时强制显示0，小于0时显示相反数，将其结果与198段球的个数相减，也即ROW(1:198)-TEXT(L2-COMBIN(ROW(2:199),2),"!0;0;0")，当这个结果大于等于0时候显示本身，小于0也就是后面为提取到的部分强制为0，也就是公式TEXT(ROW(1:198)-TEXT(L2-COMBIN(ROW(2:199),2),"!0;0;0"),"0;!0;0")，然后求此一位数组的第3N项之和，如果还是不懂的可以尝试将公式范围缩小且球的个数减少，比如取95个球有几个红球，公式改为下面公式进行公式分步计算求助查看公式运算过程， ◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 公式三：基本上完全通过数列去解题，公式分为两部分，第一部分3COMBIN(FLOOR(ROUNDUP(((1+8L2)^0.5-1)/2,),3)/3+1,2)求大于等于给定球数的3的倍数的红球数量，运用到了一元二次方程的求根公式和一些简单常用函数，公式第二部分是修正因为第一部分向上舍入后可能导致红球个数偏多的数据，也就是可能刚好最后一个球是红色的时候，下面一个球还有可能是红球的可能，在此不做详细解释，因为使用了COMBIN，当球的个数小于4个时，公式三和公式四均返回错误值，也就是不存在红球时返回错误值，没有进行修正，而且公式三没有进行简化方便各位理解。 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 公式四：公式三的简化，应该好理解，略过。以上解释仅仅是个人对公式的解读，如有更通俗简单的解释，欢迎公式提供者和其他会员跟帖，也感谢wcymiss和w83365040371两位高手提供的公式和wjc2090742简化的公式，思路都很棒。附件提供了5000以内球的个数的验证，请查看！如何用公式计算出红球数量？（附件）.rar
2楼童心	谢谢老师**！
3楼 wjc2090742	我已经不碰excel好久了，因为弦月的情面，保驾护航而已，基本上一周也就带不动了。相禹老师在EP也指点了很多，以后多多帮忙，这娃简单，只管虐或被虐就是了。
4楼魔魔	=SUM(N(MOD(ROUND(SQRT(2*ROW(OFFSET(A1,,,L2))),),3)=0))
5楼魔魔	=SUM(N(MOD(ROUNDUP((1+8*ROW(OFFSET(A1,,,M2)))^0.5/2-0.5,),3)=0)) 这个是用师兄自己的公式来写的，总之思路是一样的。
6楼 wjc2090742	除了122333的序列新算法之外，4楼的思路是着着实实的虐到我们了。1楼的4个公式，都是正向的思考，用数学方法或数组构建，都未像魔魔那样将数学和数组构造结合，逆向思维。
7楼 w83365040371	恭喜小胖老师
8楼魔魔	谢谢w.........，wjc.....（数字省略懒得打了)这样夸我，我好开心，我会努力的