2011年4月19日 星期二

Excel-範圍內的最大值和最小值

在 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))}

沒有留言:

張貼留言

好康東東