close
不重複排名的作法
Step 1. 製造不重複的數列:
Column C Non-Repeated Total Count : = $B2++(1000-ROW())/10000
隨著列數越來越大，其遞增的純小數則越來越小，例如:0.995,0.994,0.993,....等等
Step 2. 排序/排名公式:
- 法I:Column D TopValue = Rank(C2,$C$2:$C$8,0)
| A | B | C | D | |
| 1 | Type | Total Count | NonRepeat TotalCount | Top |
| 2 | A | 207 | 207.0995 | 1 |
| 3 | B | 90 | 90.0994 | 3 |
| 4 | C | 134 | 134.0993 | 2 |
| 5 | D | 15 | 15.0992 | 6 |
| 6 | E | 57 | 57.0991 | 4 |
| 7 | F | 16 | 16.099 | 5 |
| 8 | G | 5 | 5.0989 | 7 |
- 法II: Column J Large_Score : = LARGE($C$2:$C$8,$H2)
| H | I | J | |
| 1 | Top No. | Type | Large_Score |
| 2 | 1 | A | 207.0995 |
| 3 | 2 | C | 134.0993 |
| 4 | 3 | B | 90.0994 |
| 5 | 4 | E | 57.0991 |
| 6 | 5 | F | 16.099 |
| 7 | 6 | D | 15.0992 |
| 8 | 7 | G | 5.0989 |
Step 3. 找出指定值符合參考陣列中的相對應位置: =MATCH(Assign_value,Lookup_Array,完全符合條件值)
找出特定值(Assign_value)在參考陣列(Lookup_Array)中完全符合時對應的相對位置:
- Column K Relative_ArrayNo = MATCH(J2,$C$2:$C$8,0)
Step 4. 取出絕對位址Index : =ADDRESS(Row_index,Col_index)
Row_index=1,2,...,N =>等同於 第1列, 第2列,..., 第N列
Col_index =1,2,...,M=> 等同於第A欄, 第B欄,..., 第M欄
取出特定欄位的絕對位址:
- Column L Index_Type = ADDRESS($K2+1,1)
Step 5. 取出對應(絕對)位址的儲存格內容:
Column M Type_Getter: =INDIRECT($L2)
Check: 與I欄人工判對的Type排名相同
| H | I | J | K | L | M | |
| 1 | Top No. | Type | Large_Score | Relative_ArrayNo | Index_Type | Type_Getter |
| 2 | 1 | A | 207.0995 | 1 | $A$2 | A |
| 3 | 2 | C | 134.0993 | 3 | $A$4 | C |
| 4 | 3 | B | 90.0994 | 2 | $A$3 | B |
| 5 | 4 | E | 57.0991 | 5 | $A$6 | E |
| 6 | 5 | F | 16.099 | 6 | $A$7 | F |
| 7 | 6 | D | 15.0992 | 4 | $A$5 | D |
| 8 | 7 | G | 5.0989 | 7 | $A$8 | G |
文章標籤
全站熱搜
留言列表
心情分享 (1)
