本帖最后由 Zaezhong 于 2013-1-11 14:53 编辑
[开_141]按条件取整数问题点评 本题大家在理解题目的意思存在较大的偏差，导致最后的结果多少都有问题了，至于验证的附件请到30楼下载，已经全部列举了9260种组合。由于当三个数是如{0,6,10}这样的组合时（一个数为0，另外两个的比值为0.6）返回的结果没有说明可以返回{0,1,3}抑或{0,2,2}，为此只要符合其中一直效果即可，至于0.6来历后面会说明。
先分析下题目说明，先要求将3个数分别计算总和的比重，再乘以4倍，如果这个比值小于1，那么就转换为1，如果为0就是等于0，换句话说最后结果只要不是0，那么最少就是1，这样就解释了为什么源数据中的28行结果是{1,2,1}。
考虑问题的本身是占比而不是数据的实际大小。其中楼主的后续的跟帖说明由于只有楼主可见，不做要求，仅仅以 1楼的说明为准。
从答题情况看只有一解（粗体部分可以省略） =IF(A3:C3,IF(COUNT(0/A3:C3)*(MEDIAN(A3:C3)>=MAX(A3:C3)*0.6)=2,2,(5-COUNT(0/A3:C3)-(MEDIAN(A3:C3)=MAX(A3:C3))*({0,1,2}<(B3=C3)+1))^(RANK(A3:C3,A3:C3)=1)),)公式说明以其中一个为例，该公式对于 {0,6,10}这种情况返回 {0,2,2}说明。
通过前面第一步的题目分析知道了只要非 0就最少可以得到 1，所以使用 IF(A3:C3,待讨论的结果 ,)，再次判断是否是类似于 {0,6,10}这种一个值为 0，另外两个比值刚好等于 0.6的情况，如果是返回 {0,2,2}，这里有一个 0.6，在此通过计算来说明，因为是对占全部总和的比重的 4倍，我们假设三个数分别是 0,X,Y（其中 X<Y），该组合情况下最后结果只需要四舍五入，这样可以得到 X/(X+Y)=1.5/4，得到 X/Y=0.6（这里的 1.5是一个分界点，因为最后的结果中最大值对应的值不会小于中值对应的值，所以该部分的结果只可以是 {0,1,3}或者 {0,2,2}，这两种情况的一个中间态就是 1.5）。至于
COUNT(0/A3:C3)*(MEDIAN(A3:C3)>=MAX(A3:C3)*0.6)=2是(COUNT(0/A3:C3)=2)*(MEDIAN(A3:C3)>=MAX(A3:C3)*0.6)的简化形式，其中COUNT(0/A3:C3)限制只有2个数非零，MEDIAN(A3:C3)>=MAX(A3:C3)*0.6限制两个非零数的比值，只要大于（或者大于等于）0.6就显示为2。
公式的核心部分在于(5-COUNT(0/A3:C3)-(MEDIAN(A3:C3)=MAX(A3:C3))*({0,1,2}<(B3=C3)+1))^(RANK(A3:C3,A3:C3)=1)其中的复杂判断也在于此。到此再分析下最后可能返回的结果。
假设三个数分别是A,B,C，（其中A<=B<=C后续分析全部基于该关系，其对应的返回值分别为A1,B1,C1）那么可能的结果有{0,0,4}，{0,1,3}，{0,2,2}，{1,1,2}这样4种情况。其中最大值C的返回值（记为C1）跟0的个数存在直接的关系，零越多，C1就可能越大，为什么使用可能就是因为还需要判断中值B与C*0.6的关系，如果没有零值，那么最大无论如何也是为2。所以公式使用了5-COUNT(0/A3:C3)，这是一个最大的值，但是最后具体是多少还需要看后面部分判断，题目要求如果B=C，那么最后一个加1，其实这说明了C1最多是2，由于4这个值的限制，如果A=0，那么那么只能是{0,2,2}也不存在最后一个加1的情况，而且这种情况已经包含在了前面的IF判断中，那么只有A>0，由于A非零，根据前面分析最少就是1，所以余下的只有3了，所以合理的组合也只有{1,1,2}了。公式使用了(MEDIAN(A3:C3)=MAX(A3:C3))来表示B=C，这样表示可以比(COUNTIF(A3:C3,MAX(A3:C3))=2)字符数更少，后面的判断部分({0,1,2}<(B3=C3)+1))^(RANK(A3:C3,A3:C3)=1)。为什么使用{0,1,2}<(B3=C3)+1？符号“<”的右边部分可能返回1或者2两种情况，如果B=C表示成公式就是B3=C3，那么B1=C1-1，{0,1,2}<(B3=C3)+1部分的结果为{TRUE,TRUE,FALSE}。说到此，大家就会有疑问了，上面的A<=B<=C的情况只是数据组合的一般情况，但是可能事实并非如此，该疑问暂时留到最后再来回答。(RANK(A3:C3,A3:C3)=1)的作用最大值对应的的返回TRUE否则FALSE，也即非最大值的非零全部为1，其实看似很长的公式只是判断了一直情况，也就是B=C且A>0。如果没有B=C，那么后面的(MEDIAN(A3:C3)=MAX(A3:C3))*({0,1,2}<(B3=C3)+1)肯定为0，这样就没有体现作用了。这里RANK的使用跟COUNTIF(A3:C3,”>”&A3:C3)的使用类似，其中的区别在正式竞赛区函数第91期的总结附件已经有详细说明http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=930654&pid=6462656在此不再赘述。
再反过头来回答下前面的疑问，为此举种情况如{8,1,8}或者{8,8,1}（最小值位置三取其一只有3种情况，排除前面说明的余下两种，这两种情况的一个共同点是第一个数据就是最大值，另一个最大值就通过RANK(A3:C3,A3:C3)=1来限定），具体的数据不会影响最后结果，这两种情况下{0,1,2}<(B3=C3)+1部分的结果均为{TRUE,FALSE,FALSE}，由于{0,1,2}<(B3=C3)+1部分的结果中的TRUE肯定在左侧且为连续。该结果的第一个一定是TRUE，所以整体通过(5-COUNT(0/A3:C3)-(MEDIAN(A3:C3)=MAX(A3:C3))*({0,1,2}<(B3=C3)+1))来表示真数（该部分结果如下图无颜色填充部分），其中真数是递增的，这里所谓的递增是指最大值对应值，不包括非最大值。最后通过RANK部分使得非最大值对应的值返回1
综上，由于本题的总和是4，所以所有的情况不多，如果数据较大问题将复杂得多，可惜大家没有提供直接通过相关四舍五入方面的答案，较多参与者主要没有对A3:C3/SUM(A3:C3)*4部分列入公式考虑范围之内。原公式=IF(A3:C3,IF(COUNT(0/A3:C3)*(MEDIAN(A3:C3)>=MAX(A3:C3)*0.6)=2,2,IF(RANK(A3:C3,A3:C3)=1,5-COUNT(0/A3:C3)-(MEDIAN(A3:C3)=MAX(A3:C3))*({0,1,2}<(B3=C3)+1),1)),)，适当简化后就是层顶公式。
除10楼，其他具体评分通过已点评说明。其中一些答案没有四舍五入，当一个为0，其余两个比值大于0.6时全部显示0，1，3的单独采用不同的规则，如果公式存在返回值相加不等于4的情况全部算错，仅仅评参与分。
|
楼主: vanpeise
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2013-1-6 19:58
楼主
发表于 2013-1-7 09:36
