【小测一题】~~

许冠杰

mmlzh 发表于 2019-11-12 10:25
中国式排名折腾，烧火车也没有排出来
财务部        陳佳霖        89
        馮晴瑄        89

第三名，第一是89,89，第二是86，第三是75~~~~前辈。

mmlzh

模拟结果看不懂 楊郁婷100表明是排第2，那李彥志就是排第3了，为毛奖励是0？

许冠杰

mmlzh 发表于 2019-11-12 10:39
模拟结果看不懂 楊郁婷100表明是排第2，那李彥志就是排第3了，为毛奖励是0？

模拟结果有误，李彦志是50元。前辈抱歉。

mmlzh

许冠杰 发表于 2019-11-12 10:49
模拟结果有误，李彦志是50元。前辈抱歉。

那模拟结果改改吧(综合部)李玲也应该是50
500+火车也少见了


=LOOKUP(COUNT(0/((MATCH(LOOKUP(ROW(1:38),IF(B$2:B$39<>"",ROW(1:38)))&D$2:D$39,LOOKUP(ROW(1:38),IF(B$2:B$39<>"",ROW(1:38)))&D$2:D$39,)=ROW($1:$38))*IF(MAX(IF(RIGHT("("&LOOKUP(ROW(1:38),IF(B$2:B$39<>"",ROW(1:38)),B$2:B$39)&")"&TRIM(C$2:C$39),LEN(TRIM(E7)))=TRIM(E7),LOOKUP(ROW($1:$38),IF(B$2:B$39<>"",ROW($1:$38)))))=LOOKUP(ROW($1:$38),IF(B$2:B$39<>"",ROW($1:$38))),D$2:D$39,)>INDEX(D:D,MAX(IF(RIGHT("("&LOOKUP(ROW(1:38),IF(B$2:B$39<>"",ROW(1:38)),B$2:B$39)&")"&TRIM(C$2:C$39),LEN(TRIM(E7)))=TRIM(E7),ROW($2:$39)))))),{0,1,2,3},{200,100,50,0})

micch

376个字符，真是崩溃。

话说那个综合部李玲，不是第三名，应该有50的奖励吗，两个结果不同，公式都不好意思贴了

1. =LOOKUP(FREQUENCY(IF(FREQUENCY(IF(LOOKUP(A$2:A$39,IF(B$2:B$39<>"",A$2:A$39))=LOOKUP(MATCH("*"&TRIM(E7),"("&LOOKUP(A$2:A$39,IF(B$2:B$39<>"",A$2:A$39),B$2:B$39)&")"&TRIM(C$2:C$39),),IF(B$2:B$39<>"",A$2:A$39)),D$2:D$39),ROW($1:$99)),-ROW($1:$100)),-INDEX(D:D,1+MATCH("*"&TRIM(E7),"("&LOOKUP(A$2:A$39,IF(B$2:B$39<>"",A$2:A$39),B$2:B$39)&")"&TRIM(C$2:C$39),))),{1;2;3;4},F$2:F$5)

复制代码

许冠杰

mmlzh 发表于 2019-11-12 10:52
那模拟结果改改吧(综合部)李玲也应该是50
500+火车也少见了

既然前辈做出来了，那就火车也给大家学习学习吧，主要是思路借鉴一下。

许冠杰

micch 发表于 2019-11-12 10:56
376个字符，真是崩溃。

话说那个综合部李玲，不是第三名，应该有50的奖励吗

模拟结果有误，已在主题层更新。

sbdk007

看着老师们费脑就好

许冠杰

sbdk007 发表于 2019-11-12 11:00
有两个好像不符合你的规则

模拟数据有误哈，您的是正确的。

我走我流

776的长火车，。，，，
=IFNA(LOOKUP(VLOOKUP("*"&TRIM(CLEAN(E7)),IF({1,0},"("&LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39)&")"&TRIM(CLEAN($C$2:$C$39)),$D$2:$D$39),2,),LARGE(TEXT(FREQUENCY(OFFSET($D$1,MATCH(LOOKUP(1,-FIND(TRIM(CLEAN(E7)),"("&LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39)&")"&TRIM(CLEAN($C$2:$C$39))),LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39)),LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39),),,COUNT(0/(LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39)=LOOKUP(1,-FIND(TRIM(CLEAN(E7)),"("&LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39)&")"&TRIM(CLEAN($C$2:$C$39))),LOOKUP(ROW($2:$39),IF($B$2:$B$39<>"",ROW($2:$39)),$B$2:$B$39))))),ROW($1:$100)),"[>1]1")*ROW($1:$101),{3,2,1}),{50,100,200}),)
稍微精简下
=IFNA(LOOKUP(VLOOKUP("*"&TRIM(CLEAN(E7)),CHOOSE(COLUMN(A:C),"("&LOOKUP(ROW($2:$39),IF(B$2:B$39<>"",ROW($2:$39)),B$2:B$39)&")"&TRIM(CLEAN(C$2:C$39)),D$2:D$39),2,),LARGE(TEXT(FREQUENCY(IF(LOOKUP(ROW($2:$39),IF(B$2:B$39<>"",ROW($2:$39)),B$2:B$39)=LOOKUP(1,-FIND(TRIM(CLEAN(E7)),"("&LOOKUP(ROW($2:$39),IF(B$2:B$39<>"",ROW($2:$39)),B$2:B$39)&")"&TRIM(CLEAN(C$2:C$39))),LOOKUP(ROW($2:$39),IF(B$2:B$39<>"",ROW($2:$39)),B$2:B$39)),D$2:D$39),ROW($1:$100)),"[>1]1")*ROW($1:$101),{3,2,1}),50*{1,2,4}),)