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