分組排名問題

~~~~~~~~~~~~~~~~~~~~~~~~~~

我必須依照不同組別,分別計算各組的排名。如下圖所示:

 

RANK01.jpg 

公式該怎麼設才好?

[那你就用Rank函數一組一組處理,不就得了]

是呀!這個我知道。我是在想,能不能用一個公式處理完全部的計算?

~~~~~~~~~~~~~~~~~~~~~~~~~~

 

唉!原來「」才是成長的動力。

 

這個問題其實並不是很困難,如果你注意到Rank函數中有一個絕對參照的範圍位址(用以決定排名的數值資料之全部範圍),問題的答案就呼之欲出了。因為當排名問題被加上分組條件後,這個原本不會改變的絕對參照範圍位址卻會隨著組別而改變。

RANK02.jpg 

 

想要在Excel裡以函數傳回變動的範圍位址,您必須動用Offset函數來處理,它可以依照所給予的位移量(數值),定出範圍位址的起始點及大小。Offset函數的基本語法是:

Offset(基準點位址,列位移量,欄位移量,[高度],[寬度])

 

原則上,您必須依賴“基準點位址、列位移量、欄位移量”等前3個引數的值來設定範圍位址的起始位置,而目標則是各組別的第1個資料的位置(以範例檔為例來說就是B2B10儲存格)。當我們將B1儲存格設定為基本點時,相對的列位移量就是19,而這2個數值正好是組別代號(甲、乙)資料是在所有組別資料數列中的第幾個,因此我們可以使用Match函數來求解。於是公式前半段成為:

   Offset(基準點位址,列位移量,欄位移量

   Offset($B$1,Match(A2,$A$2:$A$16,0),0

 

由於Match函數在搜尋資料位置時,當搜尋對象出現多個可能解時,只會傳回所找到的第1個資料的位置值,因此Match(A2,$A$2:$A$16,0,0)  Match(A3,$A$2:$A$16,0,0)的傳回值都會是1,您不用擔心會發生起始點移動的可能性。

至於目標範圍的高度,則是組別資料的資料個數,因此我們必須使用Countif函數來計算各組的資料個數。依此公式後半段成為:

   [高度],[寬度])

   Countif($A$2:$A$16,A2),1)

 

了解之後,將成型的Offset函數內容代入原有的Rank函數之中,就成為

   =RANK(B2,

     OFFSET($B$1,MATCH(A2,$A$2:$A$16,0),0,

              COUNTIF($A$2:$A$16,A2),1)

     )

 

RANK03.jpg  

 

 

arrow
arrow
    全站熱搜

    OfficeDr 發表在 痞客邦 留言(1) 人氣()