在 Excel 中有一個數值清單,其中的數值有重覆的清況(如下圖),如何在其中找出指定第N大的數值?

在下圖中,最大值是 15,有 3 個,接下來是 14,有 1 個,接下來跳至 12,有 2 個。通常我們會使用 LARGE 或是 SMALL 來找第 N大或第 N 小的數值,但是這兩個函數均不會忽略重覆的數。該如何解決?

Excel-在含有重覆的數字清單中找尋第N大的數(LARGE,FREQUENCY,陣列公式)


【公式設計與解析】

儲存格C2:=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),
$A$2:$A$25),ROW(1:1)),0)

複製儲存格C2,貼至儲存格C2:C16。

(1) FREQUENCY($A$2:$A$25,$A$2:$A$25)

FREQUENCY  函數用以計算一個值範圍內的某些數值出現的次數,並傳回一個數值陣列。

語法:FREQUENCY(data_array, bins_array)

  • Data_array:要計算頻率的一組數值的陣列或參照。

  • Bins_array:是區間的陣列或參照,用以將 data_array 中的值分組。

  • (2) IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),$A$2:$A$25)

這個公式只是要讓出現次數對應原數值陣列而已。

(3) LARGE(第(2)式,ROW(1:1))

利用 LARGE 取出第 N 個,ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(4) IFERROR(第(3)式,0)

將第(3)式傳回值若有錯誤訊息時,改顯示為 0。


【特別說明】

若儲存格C2改為陣列公式:{=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,
$A$2:$A$25),$A$2:$A$25),ROW(1:1)),0)}

其結果也是相同。FREQUENCY 函數本身即以陣列形式運算。

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()