學不完．教不停．用不盡

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

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

【公式設計與解析】

儲存格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 函數本身即以陣列形式運算。