[發問] 關於排名(禮拜六要用了>"<)

kevenchin

不好意思 請教各位達人
  
在排名上同時符合以下三點要素
函數該如何寫
  
1.80分以上 才有名次

2.如果有4人參賽 則取2個名次(2個名次不是2個人)
5人參賽取3個名次
9人參賽取5個名次.....以此類推
例如 3個人參賽 則取2個名次 1-2
6個人參賽 則取3個名次 1-2-3
  
3.遇到同分時 例如有兩個第1名時 不要跳過去
例如 11個人參賽 有兩個第1名時
不要是 1-1-3-4-5-6 (2不見了)
而是要 1-1-2-3-4-5-6 (2有了 而且重複的第1名並沒有剝奪掉第6名的得獎)

kevenchin

對了
這是我目前網路上四處找找的結果
但是無法完全符合上述 三點要素
https://drive.google.com/file/d/0BzOjqeEr9rhvS09rSG5kOXpmX28/view?usp=sharing

Hsieh

回復 1# kevenchin

   取得名次的規則看不出來，A4以輸入方式

C2=IF(ISERROR(MATCH(B2,$C$1:C1,)),B2,"")
D2=IF(B2>79,IF(RANK(B2,$C$2:$C$28)>$A$4,"",RANK(B2,$C$2:$C$28)),"")
向下複製

下載 (11.04 KB)

2016-3-21 12:16

hcm19522

http://blog.xuite.net/hcm19522/twblog/393599677

ML089

i2 =IF(H2<80,"",TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="&LOOKUP(COUNT(H$2:H$99),{4,5,9},{2,3,5})&"];"))
陣列公式輸入，先按ctrl+shift不放，在按enter 以三鍵齊按方式輸入公式

條件一、80分以上 才有名次
公式: =IF(H2<80,"", ...)

條件二、如果有4人參賽 則取2個名次(2個名次不是2個人)
5人參賽取3個名次
9人參賽取5個名次.....以此類推
例如 3個人參賽 則取2個名次 1-2
6個人參賽 則取3個名次 1-2-3
公式: LOOKUP(COUNT(H$2:H$99),{4,5,9},{2,3,5})

條件三、遇到同分時 例如有兩個第1名時 不要跳過去
例如 11個人參賽 有兩個第1名時
不要是 1-1-3-4-5-6 (2不見了)
而是要 1-1-2-3-4-5-6 (2有了 而且重複的第1名並沒有剝奪掉第6名的得獎)
公式: TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="& 取幾名 &"];"))

kevenchin

感謝各位大大的幫忙
  
不過 ML089 大大
請問 為什麼 如果 某一組 只有 1個 2個 或是 3個人
也就是 4個以上 才可以正常運作呢

kevenchin

=IF(H2<80,"",TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="&LOOKUP(COUNT(H$2:H$99),{1,2,3,4,5,6,7,8,9,10},{1,1,2,2,3,3,4,4,5,5})&"];"))
  
仔細看了 ML089 大大 特別在下方每一個我需要的條件詳細的註解
  
呵呵 學到東西了 學到東西了 ^^
  
感恩~感恩~感恩.....orz

kevenchin

=IF(I2=1,"第一名",IF(I2=2,"第二名",IF(I2=3,"第三名",IF(I2=4,"第四名",IF(I2=5,"第五名","")))))
本來這個 在
I2是 =IF(H2>=80,IF(RANK(H2,$H$2:$H$11)<=5,RANK(H2,$H$2:$H$11)),"")
的時候 是正常的
現在卻失效了
  
因為有時候利用Word合併列印 印獎狀 的時候
需要用到中文名次第一名 第二名 ....... 第五名

ML089

回復 8# kevenchin


    Text函數回傳的值是文字，"1","2","3",
將你原來的公式中的1,2,3數字改為文字"1","2","3"即可。

我公式中的Text函數也可直接將1,2,3改為輸出為"第一名","第二名","第三名"
我到公司查一下再補給你。

範例
=TEXT(ROW(),"[DBnum1]第0名;")

將公式中TEXT函數外加另一個TEXT函數如下
=IF(H2<80,"",TEXT(TEXT(..., ...),"[DBnum1]第0名"))

ML089

=LOOKUP(COUNT(H$2:H$99),{1,2,3,4,5,6,7,8,9,10},{1,1,2,2,3,3,4,4,5,5})       
也可以簡化為
=LOOKUP(COUNT(H$2:H$99),{1,3,5,7,9},{1,2,3,4,5})