學不完．教不停．用不盡

在 Excel 中的資料範圍內有最大值和最小值，如果要找出第一個最小值、最後一個最小值、第一個最大值、最後一個最大值，該如何處理呢？(參考下圖)

(1) 第一個最小值

儲存格E2：{=ADDRESS(MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}

這是陣列公式，輸入完請按 Ctrl+Shfit+Enter 鍵。

公式中「IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")」，

可得陣列：{2,"","","","","",8,9,"","","","","","","","","","","","",}

MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),""))

=MIN({2,"","","","","",8,9,"","","","","","","","","","","","",})

=2

所以，=ADDRESS(MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))

=ADDRESS(2,2))

=$B$2

儲存格F2：=OFFSET(INDIRECT(E2),0,-1,,)

利用儲存格E2所得的位址，透過OFFSET函數找到編號，參數中的-1指往左邊位移。

儲存格G2：=INDIRECT(E2)

利用儲存格E2所得的位址，透過INDIRECT函數找到該位址的內容。

(2) 最後一個最小值

儲存格E3：{=ADDRESS(MAX(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}

同理可求得最後一個最小值，只將第一個MIN函數修改為MAX即可。

(3) 第一個最大值

儲存格E4：{ADDRESS(MIN(IF(B2:B21=MAX(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}

(4) 最後一個最大值

儲存格E5：{=ADDRESS(MAX(IF(B2:B21=MAX(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}