女侠 香川群子 为高难问题提供了绝妙思路和经典公式！请大家参照学习！祝国庆快乐！！

aaaaaa123459

香川群子 发表于 2011-10-6 07:34
上传附件，按楼主排名要求，我做的手工操作。

使用辅助列，在一个工作表内完成，【没有用到排序处理方 ...

谢谢仙姑，附件已看，正在学习

aaaaaa123459

香川群子 发表于 2011-10-5 22:32
看了你的手工排名过程，发现问

  
对仙姑125楼指示的回复！

㈠首先关于无重复名次时每段应有多少人？

        这个绝对是我不该有的失误，虽然对问题的本质没有影响，只是问题外的统计，但这是个常识，我犯这个错误真是有点不好意思，可能因为昨天花了几个小时模拟了9遍，脑子麻木了，就直接用180－60得出＝120，忘了端点有两个，还应加1，即为180－60+1＝121人。五段总人数在理论上应当是121×5＝605人。
       仙姑说得非常对，对这一点我没有任何异议！谢谢仙姑的指正！
㈡在存在重复名次时理应多少人？
       这个不一定，如果存在同名次的多人，理论上人数[60，180]区间中的每段的人数应当在121上下徘徊，可以大于、可以小于，也可以等于121人，而等于的情况在实际中很少，这是可以实证的，所以仙姑把各段人数与121对比等值现实意义不大。
      问题当然出在端名及端点前一名次的人数的重复上，举几个典例说明问题：（前提规则是按rank()排名的名次取人）
     第一种情况如：（理论上可以出现的一种情况，即没有理由避免可能）
     …58（58）、58(59)、58(60)、58(61)、58(62)、58(63)、58(64)、58(65)、58(66)、58(67)、68（68）…、179（179）、180(180)、181(181)、…………。
       说明一下，我引用的定义可能与仙姑不同，我的定义是括号外的是rank()排名，可以重复，而括号内的是精确排名，不会重复。不知道我这样定义违反行规否！
     这种情况在区间[60，180]取的人数如上面红色标识的，理应是
    180－68+1＝113人，与121差8人，差别出现于从实际上（59）名开始的重复人数。即上端点前一名次重复数（蓝色标志9人，1个名次重复，同名次多出9－1＝8人。即上端点前一名次占用了区间中8个名次，所以造成区间人数减少。当然减少的人数也可以用第一个进入区间的名次减少上端点名次得到，如68－60＝8 人。）。
        这是每段人数小于121人的合理性。
     再说第二种情况如：（也是两样的理由，只是理论上可以出现的一种情况，也没有理由避免可能）
……59（59）、60(60)、……、179(179)、179(180)、179(181)、179(182)、179(183)、180(184)、180(185)、180(186)、180(187)、180(188)、180(189)、180(190)、180(191)、192(192)、…………。
       这种情况下，在区间[60，180]之间的人数也是红色标志的，理应是：191－60+1＝132人，比121多出11人，问题同样出在下端点及前一名次的重复人数上（斜体加粗标识13人，两个名次重复，同名次多出13－2＝11人。减少的人数也等于上端点名次的最后一个的实际名次191减去下端点180，即191－180＝11人。）。
      这是每段人数大于121的合理性，
      不好意思、有人找，有点事，一会再来分析其它的……暂时再见！
      好了，接着来，
㈢关于每班50人以后是否参与语文排名
       我非常赞赏全部学生都参与语文排名，虽然现实意义不大，但在思想上是一种平等的尊重，所以我手工操作时也让他们排名了，只不过是无效排名，
       关于不让50人以后的学生参与语文排名，是学校的意思，但直到目前，我也没有办法做到用公式把50人以后的学生记录删除掉，如果能用公式一次性把每班总分序50人以后的记录删除掉，将会减少异议，极大的提高效率，在标准上是绝对统一的，剩下的就好操作了，问题的关键之一就在这里，不知仙姑能否用公式做到把每班总分序50人以后的记录一次性删除掉。这是一个很大的难点。如果不能做到标准统一，全部学生参与语文排名将对结果产生干扰，这也正是我手动模拟了9次，没有不同之后，才敢确认上传附件的原因。至少9次机会都一样，我相信我的规则在一定意义上是严格的。
       好在仙姑也能接受每班总分序50人以后不参与排名，下面的就是技术标准的统一问题了。如果能提供一种思路在公式中把50人以后因素排除了就好了。这个技术上的难关需要克服。
㈣关于手工模拟端点名次取人有无规则
       我至少认为，是有规则的，即上端点名次（60名）仅取 最后一人，下端点名次（180名）也只取第一人。你可以从我的手工模拟表中看到我就是这样操作的，不知道会不会给公式的编辑带来巨大的难题。不知这算不算规则，但我至少把这个当成规则来手工操作的。
      [我的思路是：在你设计公式名次中加入行因素的基础上（这个技术瓶颈你设想的非常好噢！），也在公式中要关注名次整数为60时取最大，而名次整数为180时取最小。  不知我的思路有用没有，能否加入到公式的设计中。]
     
      最后奢望，能把去掉每班总分序50人后的记录技术加入到综合公式中。
    （这个难点我也正在想办法，因为这是标准统一的关键瓶颈。只是因为水平有限，不能如仙姑一样神思泉涌，手到擒来，很多时候，我想的都是最笨的办法，而且现在就连最笨的方法法还没有找到！）
         如果不能，尽量在两种情况下能给出两个综合公式：
         ⑴ 全部学生都参与语文排名后的结果，好象仙姑这个公式已经很好了，如同仙姑在127楼里说的那样。                     
         ⑵只让每班总分序50人内的学生参与语文排名，这个我在手工模拟时也是一个技术难题，如果想绝对手工，就得一一对证了，连sumproduct()函数也不能用了，那得花好几天的时间，而且也避免不了在劳累之余发生失误，失败在所难免，所以我也是不得以用了sumproduct()函数，目前还没有想到如果规避50人以后参与语文排名的方法，请仙姑帮助解决。
      旁边有同事说话干扰，出于礼貌，又不能不回答，思路有点不清了，暂时先到这里，如有失误，请详察并指正！谢谢 ！
     我希望我和与我同样水平的人在仙姑这样的高手指教下，极大的提高水平，谢谢如仙姑般乐于奉献的侠客。谢谢您的帮助！

aaaaaa123459

谢谢你，仙姑（穿越电视剧“宫”中用语），
昨晚22：00多，在一块不错的几位同事非得让我去打双升，盛情难却，只好陪着打到24：00左右，之后又编辑了一下版主所给公式的解析，回头看到仙姑的回帖，已经非常瞌睡了，所以没有及时回复，等我脑子清醒下，我给仙姑分析一下125楼的看法，仙姑如有兴趣，可以看 一下，我对版主公式的解析，由于瞌睡，我感觉可以也有失误，请仙姑指正：
     网址：http://club.excelhome.net/thread-770788-8-1.html   第79楼，谢谢！
     一会我把我的意思给仙姑讲述一下，请批阅！

香川群子

又看了一遍你的手工模拟，
确认实际上你在做段内语文排名时，
各班总分>50名同学的语文成绩，仍然是参与段内语文排名的。
只不过是在排序时把他们放到最后而已。

如果是这样的规则，则我前面给出的公式，就应该是正解了。

1. =SUM((班级=A2)*(总分+(1766-ROW($2:$1765))%%%>=LARGE(IF(班级=A2,总分+(1766-ROW($2:$1765))%%%),50))*(语文+(1766-ROW($2:$1765))%%%<=LARGE(IF(段名=OFFSET(段名,MATCH(A2,班级,)-1,0,1),语文+(1766-ROW($2:$1765))%%%),60))*(语文+(1766-ROW($2:$1765))%%%>=LARGE(IF(段名=OFFSET(段名,MATCH(A2,班级,)-1,0,1),语文+(1766-ROW($2:$1765))%%%),180)))

复制代码

以你手工排序以后的03表数据做了计算验证，
这样一来，和你的模拟结果就几乎一致了……

仍有差异，但这差异，是你对=60名、=180名进行手工调整所致。
（这样的调整也是不应该的。）

呵呵。


因此，我认为我对本帖的公式答复，已经是正确答案了。

数据和楼主的模拟有差异，是楼主的不合理错误操作引起的。

实际上楼主今后可以放心地、直接使用我的公式，而不需要去做手工排序。排序只会引起错误和混乱。

香川群子

上传附件，按楼主排名要求，我做的手工操作。

使用辅助列，在一个工作表内完成，【没有用到排序处理方法】。


如果使用了排序，则【排序后的结果】，一定会和根据原始数据计算【得到的结果】产生【不同】。
原因在于【同分学生】的【排名位置】会有差异。


所以，【用函数对原始数据直接计算的结果，则不能进行排序操作来验证结果。】



呵呵，还是请楼主看一下附件吧。

香川群子

看了你的手工排名过程，发现问题很严重。


你的操作流程存在规则问题，使得结果必然产生技术错误。

首先，取段内语文分数排名[60-180]名的话，各班人数合计应该是121人(含两个端点人数各1人)
即，人数=60、61、、、179、180=180-60+1=121人，而不是120人。

除非你不要60名，或者不要180名，才会是120人。

然而，你的手工模拟结果，合计人数出入很大：
A段合计 120人，少1人。
B段合计 121人，正好。
C段合计 120人，少1人。
D段合计 119人，少2人。
E段合计 115人，少6人。
总计入围人数 595人，比5段总计 605人少10人。


原因么，我想，在于你对各班正好排名=60和=180的手工调整，是没有准确规律的，造成了总人数的误差。


实际上，解决问题的方法，就是明确规则，按规则处理，而不应该以手工判断来调整。



至于规则，我认为：
1. 本来班内总分<=50名，只能作为入围必要条件去用，
而不能用于在段内语文分数排名时，排除那些总分>50名的同学的语文成绩→这回造成误差，实际上也不公平。

当然，如果楼主坚持认为他们的成绩无效，不能参与段内语文分数排名，作为规则我也还能接受。


2.  段内语文分数排名，如果是同分同名次排序
（如：1、2、2(3)、4、……58、58(59)、58(60)、58(61)、……、179、179(180)、179(181)、182……）
则你最后入围条件，只应该按分数，而不应该按名次。

即，按照我第一次给出的公式结果。


3.  段内语文分数排名，如果需要按60名、180名只取1人来操作，
则必须事先做精确排名
（如：1、2、3(2)、4、……58、59(58)、60(58)、61(58)、……、179、180(179)、181(179)、182……）

即，按照我第二次给出的公式结果。



4. 如果你坚持要按照你目前手工操作的方式去做，
那么，你首先需要再次明确各班端点名次的取舍规则。

否则，任何函数将无法得到你所需要的结果。因为和你的人工判断有关。
并且，很有可能和排序先后有关。

aaaaaa123459

好了，在模拟了9遍都是同样结果的情况下，我的手动模拟过程附件准备上传了，请各位大侠，特别是仙女支招吧！谢谢了。

aaaaaa123459

今天模拟了3个小时，好象A段结果与原来有差异，不知原因何在，可惜原来的模拟过程丢失了，好象记得shaowu459 也得出过和我原来模拟同样的结果，（如01班24人），现在怎么也得不到，抽时间把我的新模拟结果发上去，请大家查阅和帮忙。

香川群子

119楼加了附件，也添加了新的公式：

1. =SUM((班级=A2)*(总分+(1766-ROW($2:$1765))%%%>=LARGE(IF(班级=A2,总分+(1766-ROW($2:$1765))%%%),50))
   
2. *((语文+(1766-ROW($2:$1765))%%%<=LARGE(IF((段名=OFFSET(段名,MATCH(A2,班级,)-1,0,1))*(总分+(1766-ROW($2:$1765))%%%>=LOOKUP(班级,$A$2:$A$33,$D$2:$D$33)),语文+(1766-ROW($2:$1765))%%%),60)))*((语文+(1766-ROW($2:$1765))%%%>=LARGE(IF((段名=OFFSET(段名,MATCH(A2,班级,)-1,0,1))*(总分+(1766-ROW($2:$1765))%%%>=LOOKUP(班级,$A$2:$A$33,$D$2:$D$33)),语文+(1766-ROW($2:$1765))%%%),180))))

复制代码

不过，LOOKUP(班级,$A$2:$A$33,$D$2:$D$33)的用法，目前还没有想到可以用数组计算代替的方法。

aaaaaa123459

    昨天晚上，在版主休息之后，我仍在想这个公式怎么解决，思绪有点乱，我看了看以前的帖子，发现香川群子在线，并且知道她也在为我的一个问题发愁，需要我整理一个手动操作的附件传上去，虽然我现在头脑有点乱，整理附件是明天的事了，但我高兴极了，想向她讨教这个公式，但我没有打招呼的权利，发了帖子和消息，人家也看不到，眼睁睁地看着她下线了，真是失意得很，看看时间快24点了，困意一阵阵迎面袭来……
    我来到了一个在水一方的所在，举目望去，山重水复，望断（尽）天涯路，悠然间，我听到了神喻的声音，很确切，就是神的暗示，“车到山前必有路”，我兴奋极了，因为我一直在寻找一个公式的意义和我的附件，我想也许神会给我启示，果然神很明晰我的意思，只问了我一句，便点破了我的关键所在，“不要丧失了你的目标，想清楚来是干什么来的！”，我脑中灵光一现，“向量寻求……”，对就是向量，我太高兴了，然后思路自然地继上来了，版主的公式彻底解决了。
    但我还有疑惑，“那为什么，……”，“不用解释，”，神说，“我告诉你，就四个字，时间、实践”，我还是不明白，神接着喻示“玄之又玄，众妙之门 ，自在各取所需，否则你怎么理解，你怎么知道版主的前世今生……”，我顿悟了，但有点渊源还是需要追问的，我赶紧问，“那版主是谁？”，“噢，你心中所问的版主就是智多星在世”，我明白了，原来chrisfang版主又可以叫做智多星（版主）的，“那香川群子呢？”，“她呀，那是七仙女下凡”，啊，真的吗，我高兴极了，应当真是这样，怪不得香川群子那么心灵手巧呢，香川群子才气很高，因为是七仙女的缘故，香川群子以后会越来越漂亮呢！“那还有……”，“一切皆缘分，刚才说了，玄之又玄，众妙之门，天机不可泄露的”，我心想，已经泄露了两个了，多几个又何妨啊，但我还是忍住没有问，因为神还在暗示我下面的问题，就在一瞬间，我明白了怎样迅速求出excel2010中16384列的列标求法，也明白了怎样以年份（比如今年2011）为参数得出古历的干支纪年来，我发现，神只所以能够创世，绝对有其创世的理由与能力，这不是一般人能够理解的，人们倒是可以在神的喻示下做得更好，其它的问题，好像神认为我可以解决，至少应该在人们的帮助中得到解决，神又开始天机不可泄露了，我感觉我必须问一个终结性的问题了，“听你的语言，好象地球上的任何人都可以得到你的喻示，那您究竟是哪国的神呢！”，神的笑声传来，“神就是神，没有内外，所谓的国别、民族和等级是你们人间虚构的，在天国一切都祥和，终极自由、平等的……”，我知道神在为天国作广告，即便如此，绝大多数人还是留恋人世的，当然与地狱相比，人们对天国的向往不言而喻，我的思想不能跑得太远，因为神还在喻示我一些神理（不知道与禅理是什么关系），“神在内心，不在外界，神即信心，信心即神……”
    “天上太阳红啊红彤彤呃，心中的太阳是毛泽东哎……”，学校的红歌又开始了，我的梦中神思给红歌的风吹散了，散落到无边的天际之中，再也找不回来了，……，唯一的好处就是及时的打断梦境，使得我对梦中的情景记忆犹新，我看了看时间，早上06：00了，赶紧打开电脑，上到EH论坛，回复了几个网友，接着写下这遍神文。
    是不是巧合，以后还有没有这样的好事，我不知道，但我知道的是，这个梦不是虚构的，而且在没有任何人的解释下，我对几个问题的明晰是真实存在的。
    你信与不信，神就在那里，不弃不离，信心即神，我真信了！如温总理所言：“信心比黄金更重要”！
    下面在补课之前，我来说明版主的公式的意义和为香川群子做好附件，当然我不会耽误学生，在我学到的任何东西中的精神（精华），我都会把其中的科学哲理告诉学生，让他们为祖国的辉煌作为自己应有的贡献，亦象周总理所语，“为中华之崛起而努力……”