2013年1月21日 星期一

Excel-找出區間排名(INT, SUMPRODUCT)

在 Excel 中有一個數列(如下圖),如果想要依據區間列出各自的排名,該如何處理?

【準備工作】

選取儲存格B1:B26,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。

【輸入公式】

(1) 列出區間

儲存格C2:=INT(B2/10)*10 & "-" & INT(B2/10)*10+9

使用 INT 函數取得不大於的最大整數,INT(B2/10) 可以取得數值的 10 位數。

複製儲存格C2,往下各列貼上。

 

(2) 分區排名

儲存格D2:=SUMPRODUCT((INT(數值/10)=INT(B2/10))*(數值>B2))+1

INT(數值/10)=INT(B2/10):求得數值中 10 位數和儲存格B2相同的陣列。

數值>B2:取得數值中大於儲存格B2的陣列。

利用 SUMPRODUCT 函數計算合於以上二式的 True/Fasle 陣列之和(True=1、Fasle = 0)。

複製儲存格D2,往下各列貼上。

 

(3) 統計個數

儲存格G2:=SUMPRODUCT(--(INT(數值/10)=ROW(5:5)))

INT(數值/10)=ROW(5:5):找出數值中 10 位數為 5 者。

公式中的「--」,可以將 True/Fasle 陣列,轉換為 1/0 陣列。

複製儲存格G2,往下各列貼上。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

沒有留言:

張貼留言

好康東東