如何按照斯-诺-克比赛规则计算得分进行排名？

楼主 rongjun	【题目】：如B2:H9单元格区域为某次斯-诺-克桌球比赛各选手的得球统计，如何按照斯-诺-克比赛规则计算得分进行排名？【说明】： 1、计分规则：红球1分、黄色球2分、绿色球3分、棕色球4分、蓝色球5分、粉色球6分、黑色球7分； 2、得分=得球个数*对应色球分数； 3、个人总分等于其各色球得分之和； 4、按总分从高到低排名。【要求】：可以使用Excel的任意功能求解，方法不限，但应具备通用性。【评分】： 1、使用非函数解法，得4技能分； 2、使用辅助区域或定义名称函数解法，得2～4技能分； 3、不定义名称、不使用辅助区域的函数解法，得5～6技能分。 4、精彩答案可再获1～3技能分。【答题期限】：2010-5-25日0:00截稿（以回帖时间为准），欢迎大家踊跃参与。如何按照斯诺克比赛规则计算得分进行排名？——题目.rar
2楼 xiongkehua2008	I2=SUM(N(MMULT(FIND(LEFT(B2:H2),"红黄绿棕蓝粉黑")TEXT(SUBSTITUTE(MID(B2:H2,3,9),"个",),"0;;;!0"),{1;1;1;1;1;1;1})<MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")TEXT(SUBSTITUTE(MID(B$2:H$9,3,9),"个",),"0;;;!0"),{1;1;1;1;1;1;1})))+1 数组公式！如何按照斯诺克比赛规则计算得分进行排名？——题目.rar
3楼棉花糖	=MATCH(ROW(1:8),MOD(LARGE(MMULT((0&MIDB(B2:H9,5,2))(MATCH(LEFT(B2:H9),{"";"红";"黄";"绿";"棕";"蓝";"粉";"黑"},)-1),ROW(1:7)^0)10+ROW(1:8),ROW(1:8)),10),) 不考虑名次相同的，否者公式会长很多。如果名次相同的公式 =MMULT(--(MMULT((0&MIDB(B2:H9,5,2))(MATCH(LEFT(B2:H9),{"";"红";"黄";"绿";"棕";"蓝";"粉";"黑"},)-1),ROW(1:7)^0)<TRANSPOSE(MMULT((0&MIDB(B2:H9,5,2))(MATCH(LEFT(B2:H9),{"";"红";"黄";"绿";"棕";"蓝";"粉";"黑"},)-1),ROW(1:7)^0))),ROW(1:8)^0)+1
4楼 syz105729913	如何按照斯诺克比赛规则计算得分进行排名？——题目.rar 参与一下，用了辅助列
5楼 wenshui2006	唉,,,公式很长,,,,, I2:I9=MATCH(MMULT(FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑")(0&MID(B2:H9,3,--TEXT(LEN(B2:H9)-3,"[<0]!0;0"))),ROW(1:7)^0),LARGE(MMULT(FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑")(0&MID(B2:H9,3,--TEXT(LEN(B2:H9)-3,"[<0]!0;0"))),ROW(1:7)^0),ROW(1:8)),) 有个更长的,,,中规中矩的写法,,, I2:I9=MATCH(MMULT(CHOOSE(MATCH(IF(LEFT(B2:H9,2)<>"",LEFT(B2:H9,2),),{"红球","黄球","绿球","棕球","蓝球","粉球","黑球",0},),1,2,3,4,5,6,7,0)(0&MID(B2:H9,3,--TEXT(LEN(B2:H9)-3,"[<0]!0;0"))),ROW(1:7)^0),LARGE(MMULT(CHOOSE(MATCH(IF(LEFT(B2:H9,2)<>"",LEFT(B2:H9,2),),{"红球","黄球","绿球","棕球","蓝球","粉球","黑球",0},),1,2,3,4,5,6,7,0)(0&MID(B2:H9,3,--TEXT(LEN(B2:H9)-3,"[<0]!0;0"))),ROW(1:7)^0),ROW(1:8)),) 等公布答案时学习一下高手的公式,,,
6楼 arjoe	数组公式利用斯－诺－克的球数不超过99个，所以用midb 函数 =SUM(N(SUM(FIND(LEFT(B2:H2),"红黄绿棕蓝粉黑")TEXT(MIDB(B2:H2,5,2),"0;;;!0"))<MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")TEXT(MIDB(B$2:H$9,5,2),"0;;;!0"),ROW(1:7)^0)))+1 多区域数组公式 =MATCH(MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")TEXT(MIDB(B$2:H$9,5,2),"0;;;!0"),ROW(1:7)^0),LARGE(MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")TEXT(MIDB(B$2:H$9,5,2),"0;;;!0"),ROW(1:7)^0),ROW(1:8)),) 如何按照斯诺克比赛规则计算得分进行排名？——题目.rar
7楼杏花雨V翟	=SUM((MMULT(IF($B$2:$H$9="",0,FIND(LEFT($B$2:$H$9),"红黄绿棕蓝粉黑")MIDB($B$2:$H$9,FIND("个",$B$2:$H$9),2)),ROW(1:7)^0)>SUM(IF(B2:H2="",0,FIND(LEFT(B2:H2),"红黄绿棕蓝粉黑")MIDB(B2:H2,FIND("个",B2:H2),2))))*1)+1 幸亏没有长度限制，要不这公式真的太长了
8楼 mosforyou	真不知道不显示内容的帖子是怎么回的？如何按照斯诺克比赛规则计算得分进行排名？——题目.rar
9楼 amulee	先来个VBA的 Sub 排名() '定义变量 Dim i&, j%, arrYS, arrFS, arrJG, Temp Dim RegEx As Object Set RegEx = CreateObject("VBSCRIPT.REGEXP") '初始赋值 arrYS = Range("B2:H" & Range("A1048576").End(xlUp).Row) ReDim arrJG(1 To UBound(arrYS)) ReDim arrFS(1 To UBound(arrYS)) RegEx.Global = True RegEx.Pattern = "[^\d]" '计算分数 For i = 1 To UBound(arrYS) For j = 1 To UBound(arrYS, 2) Temp = arrYS(i, j) If Len(Temp) > 0 Then arrFS(i) = arrFS(i) + InStr(1, "红黄绿棕蓝粉黑", Left(Temp, 1)) * RegEx.Replace(Temp, "") End If Next j Next i '计算排名 Set Temp = Range("I2").Resize(UBound(arrFS), 1) Range("I2").Resize(UBound(arrFS), 1) = Application.Transpose(arrFS) For i = 1 To UBound(arrYS) arrJG(i) = WorksheetFunction.Rank(arrFS(i), Temp) Next i Range("I2").Resize(UBound(arrFS), 1) = Application.Transpose(arrJG) End Sub 辅助列法：辅助列J区域数组公式： =MMULT(LOOKUP(IF(B2:H9<>"",--MID(B2:H9,3,LEN(B2:H9)-3),),ROW($1:$99)-1)FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑"),ROW(1:7)^0) I2公式如下，然后下拉： =RANK(J2,J$2:J$9) 不用辅助列，区域数组公式： =MATCH(MMULT(LOOKUP(IF(B2:H9<>"",--MID(B2:H9,3,LEN(B2:H9)-3),),ROW($1:$99)-1)FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑"),ROW(1:7)^0),LARGE(MMULT(LOOKUP(IF(B2:H9<>"",--MID(B2:H9,3,LEN(B2:H9)-3),),ROW($1:$99)-1)*FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑"),ROW(1:7)^0),ROW(1:8)),) 如何按照斯诺克比赛规则计算得分进行排名？.rar
10楼 chrisfang	I2单元格内输入数组公式并向下复制：公式1：没有重复排名 =MATCH(ROW(),--RIGHT(LARGE(MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")SUBSTITUTE(0&MID(B$2:H$9,3,3),"个",""),ROW($1:$7)^0)10+ROW($2:$9),ROW($1:$8))),0) 公式2：RANK式排名 =MATCH(SUM(FIND(LEFT(B2:H2),"红黄绿棕蓝粉黑")SUBSTITUTE(0&MID(B2:H2,3,3),"个","")),LARGE(MMULT(FIND(LEFT(B$2:H$9),"红黄绿棕蓝粉黑")SUBSTITUTE(0&MID(B$2:H$9,3,3),"个",""),ROW($1:$7)^0),ROW($1:$8)),0)
11楼 kevinchengcw	看到了,也参与一下如何按照斯诺克比赛规则计算得分进行排名？——题目.rar
12楼 donghan	既然没有字数和版本的要求，发一个2007版函数的：I2:I9输入多单元格数组公式（没做简化）： =MMULT(--(TRANSPOSE(MMULT(IFERROR(CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2),0),ROW(1:7)^0))>MMULT(IFERROR(CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2),0),ROW(1:7)^0)),ROW(1:8)^0)+1 2003版公式： =MMULT(--(TRANSPOSE(MMULT(IF(ISERROR(CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2)),0,CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2)),ROW(1:7)^0))>MMULT(IF(ISERROR(CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2)),0,CHOOSE(MATCH(LEFT(B2:H9),{"粉","黄","黑","蓝","红","绿","棕"},),6,2,7,5,1,3,4)MIDB(B2:H9,5,2)),ROW(1:7)^0)),ROW(1:8)^0)+1
13楼 rongjun	我的答案： 1、普通数组公式： =SUM(N(MMULT((0&MIDB($B$2:$H$9,5,2))FIND(LEFT($B$2:$H$9),"红黄绿棕蓝粉黑"),ROW($1:$7)^0)>SUM((0&MIDB(B2:H2,5,2))FIND(LEFT(B2:H2),"红黄绿棕蓝粉黑"))))+1 2、内存数组： =MATCH(MMULT((0&MIDB(B2:H9,5,2))FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑"),ROW(1:7)^0),LARGE(MMULT((0&MIDB(B2:H9,5,2))FIND(LEFT(B2:H9),"红黄绿棕蓝粉黑"),ROW(1:7)^0),ROW(1:8)),) 如何按照斯诺克比赛规则计算得分进行排名？——答案.rar
14楼 bpbp111522	luguo xuexi
15楼 mosforyou	题目要求： 1、计分规则：红球1分、黄色球2分、绿色球3分、棕色球4分、蓝色球5分、粉色球6分、黑色球7分； 2、得分=得球个数*对应色球分数； 3、个人总分等于其各色球得分之和； 4、按总分从高到低排名。看来只有我一个人理解错了题目的本意！不但笨，而且浪费脑细胞！
16楼 rongjun	1楼不是已经给出图片示例了吗？是按照总分排名，而不是排序。不过这么长的公式你都能做出来，由此可见你的函数有一定的功底，但是必须注意审题，免得徒劳无功。答题时有什么题意不明的地方也可以直接跟帖提出。
17楼 amulee	学习荣版的解答，不愧为高手啊
18楼 arjoe	比起楼主的公式，还是差了一大截啊